Lucidchart offers preset SQL statements for importing schema data into from various sql databases but does not offer a solution for Google BigQuery datasets.
Can someone help me figure out a solution for this?
Solved
SQL for Importing Google BigQuery DBs for Lucidchart
+1Best answer by mattjkenney
Thank you
I was able to use the MySQL version after adding the database name to the table names and removing some columns that were not available in BigQuery tables:
SELECT 'mysql' dbms,t.TABLE_SCHEMA,t.TABLE_NAME,c.COLUMN_NAME,c.ORDINAL_POSITION,c.DATA_TYPE,n.CONSTRAINT_TYPE FROM ['insert full database path'].INFORMATION_SCHEMA.TABLES t LEFT JOIN ['insert full database path'].INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_SCHEMA=c.TABLE_SCHEMA AND t.TABLE_NAME=c.TABLE_NAME LEFT JOIN ['insert full database path'].INFORMATION_SCHEMA.KEY_COLUMN_USAGE k ON c.TABLE_SCHEMA=k.TABLE_SCHEMA AND c.TABLE_NAME=k.TABLE_NAME AND c.COLUMN_NAME=k.COLUMN_NAME LEFT JOIN ['insert full database path'].INFORMATION_SCHEMA.TABLE_CONSTRAINTS n ON k.CONSTRAINT_SCHEMA=n.CONSTRAINT_SCHEMA AND k.CONSTRAINT_NAME=n.CONSTRAINT_NAME AND k.TABLE_SCHEMA=n.TABLE_SCHEMA AND k.TABLE_NAME=n.TABLE_NAME WHERE t.TABLE_TYPE='BASE TABLE' AND t.TABLE_SCHEMA NOT IN('INFORMATION_SCHEMA','mysql','performance_schema');BigQuery is actually quite intuitive. The noted errors helped guide me through all the needed corrections.
+21- Lucid Legend Level 9
- 601 replies
Hi
Would you like to try Zapier / Tray.io for this use case ?
If not try out the belowo process:
Extract Schema Information - from your BigQuery datasets
It should be SQL commands like SELECT * FROM INFORMATION_SCHEMA.TABLES to get a list of tables and their corresponding columns
use the EXPORT DATA or bq extract command to export the data from your SQL queries to a file
Then you can import it into Lucidchart by CSV and JSON files
Hope this helps - Happy to help further !!
Thank you very much and have a fantastic day!
Warm regards
+1Thank you
I was able to use the MySQL version after adding the database name to the table names and removing some columns that were not available in BigQuery tables:
SELECT 'mysql' dbms,t.TABLE_SCHEMA,t.TABLE_NAME,c.COLUMN_NAME,c.ORDINAL_POSITION,c.DATA_TYPE,n.CONSTRAINT_TYPE FROM ['insert full database path'].INFORMATION_SCHEMA.TABLES t LEFT JOIN ['insert full database path'].INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_SCHEMA=c.TABLE_SCHEMA AND t.TABLE_NAME=c.TABLE_NAME LEFT JOIN ['insert full database path'].INFORMATION_SCHEMA.KEY_COLUMN_USAGE k ON c.TABLE_SCHEMA=k.TABLE_SCHEMA AND c.TABLE_NAME=k.TABLE_NAME AND c.COLUMN_NAME=k.COLUMN_NAME LEFT JOIN ['insert full database path'].INFORMATION_SCHEMA.TABLE_CONSTRAINTS n ON k.CONSTRAINT_SCHEMA=n.CONSTRAINT_SCHEMA AND k.CONSTRAINT_NAME=n.CONSTRAINT_NAME AND k.TABLE_SCHEMA=n.TABLE_SCHEMA AND k.TABLE_NAME=n.TABLE_NAME WHERE t.TABLE_TYPE='BASE TABLE' AND t.TABLE_SCHEMA NOT IN('INFORMATION_SCHEMA','mysql','performance_schema');BigQuery is actually quite intuitive. The noted errors helped guide me through all the needed corrections.
+20Hi! I’m Micah from Lucid’s community team. 👋 I thought you, and any others who might come across this topic in the future, might be interested in Lucid’s Data Trusted User Group. This group is made up of knowledgeable users who regularly analyze and visualize data in their work. As part of this group, you’ll have the opportunity to connect directly with Lucid’s product team, gain early access to beta features, and share insights about your needs, shaping the future of Lucid’s data solutions 🎉 You can learn more and request to join here. Thanks!
1 person likes this
Reply
Create an account in the community
A Lucid account is required to interact with the Community, and your participation is subject to the Supplemental Lucid Community Terms. You may not participate in the Community if you are under age 18. You will be redirected to the Lucid app to create an account.
Log in to the community
A Lucid account is required to interact with the Community, and your participation is subject to the Supplemental Lucid Community Terms. You may not participate in the Community if you are under age 18. You will be redirected to the Lucid app to log in.
Log in with Lucid
or
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
