Google BigQuery ERD Import?

Hi David! Thanks for reaching out in the community. Our importer isn't built specifically for a Google BigQuery import but BigQuery runs off of ANSI compliant SQL so it is very possible that you will be able to successfully import. To our knowledge we don't know if anyone has tried it. Would you mind giving it a go and then updating this post for our other users?

Sure which variant of SQL should I choose in the prompt?

I would try with SQL Server. Again it's possible it may not import but thanks for giving it a try!

I tried with the SQL Server template with no luck. First I got an error saying the I needed the dataset name for INFORMATION_SCHEMA.TABLES but really the issue is that BigQuery doesn't use have a INFORMATION_SCHEMA.TABLE_CONSTRAINTS table to join on.

Do you have an example output? I can probably write a script to get the necessary info.

Here's an example output:

postgresqlquizbowlpublicdatabase_migrationsapplied2timestamp with time zone
postgresqlquizbowlpublicdatabase_migrationsname1character varying
postgresqlquizbowlpublicload_data_versionsapplied3timestamp with time zone
postgresqlquizbowlpublicload_data_versionschecksum2character varying
postgresqlquizbowlpublicload_data_versionsname1character varying
postgresqlquizbowlpublicevent_teamcreate_date4timestamp with time zone
postgresqlquizbowlpublicevent_teamcreate_user_id3integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpublicevent_teamteam_id2integerPRIMARY KEY
postgresqlquizbowlpublicevent_teamteam_id2integerFOREIGN KEYpublicteamteam_id
postgresqlquizbowlpublicevent_teamevent_id1integerPRIMARY KEY
postgresqlquizbowlpublicevent_teamevent_id1integerFOREIGN KEYpubliceventevent_id
postgresqlquizbowlpublicevent_questioncreate_date9timestamp with time zone
postgresqlquizbowlpublicevent_questioncreate_user_id8integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpublicevent_questionclose_timestamp7timestamp with time zone
postgresqlquizbowlpublicevent_questionstart_timestamp6timestamp with time zone
postgresqlquizbowlpublicevent_questionsequence5integer
postgresqlquizbowlpublicevent_questionround_number4integer
postgresqlquizbowlpublicevent_questionquestion_id3integerFOREIGN KEYpublicquestionquestion_id
postgresqlquizbowlpublicevent_questionevent_id2integerFOREIGN KEYpubliceventevent_id
postgresqlquizbowlpublicevent_questionevent_question_id1integerPRIMARY KEY
postgresqlquizbowlpublicschoollast_mod_date10timestamp with time zone
postgresqlquizbowlpublicschoollast_mod_user_id9integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpublicschoolcreate_date8timestamp with time zone
postgresqlquizbowlpublicschoolcreate_user_id7integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpublicschoolstate_abbrev6character varying
postgresqlquizbowlpublicschoolcity5USER-DEFINED
postgresqlquizbowlpublicschoolnickname4USER-DEFINED
postgresqlquizbowlpublicschoolmascot3USER-DEFINED
postgresqlquizbowlpublicschoolname2USER-DEFINEDUNIQUE
postgresqlquizbowlpublicschoolschool_id1integerPRIMARY KEY
postgresqlquizbowlpublicsessionexpires3integer
postgresqlquizbowlpublicsessionsession_data2text
postgresqlquizbowlpublicsessionsession_id1character varyingPRIMARY KEY
postgresqlquizbowlpublicslidelast_mod_date7timestamp with time zone
postgresqlquizbowlpublicslidelast_mod_user_id6integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpublicslidecreate_date5timestamp with time zone
postgresqlquizbowlpublicslidecreate_user_id4integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpublicslideurl3USER-DEFINED
postgresqlquizbowlpublicslidename2USER-DEFINED
postgresqlquizbowlpublicslideslide_id1integerPRIMARY KEY
postgresqlquizbowlpublicevent_logcreate_date6timestamp with time zone
postgresqlquizbowlpublicevent_logcreate_user_id5integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpublicevent_logdata4text
postgresqlquizbowlpublicevent_logtype3USER-DEFINED
postgresqlquizbowlpublicevent_logevent_id2integerFOREIGN KEYpubliceventevent_id
postgresqlquizbowlpublicevent_logevent_log_id1integerPRIMARY KEY
postgresqlquizbowlpublicquestion_typedescription3text
postgresqlquizbowlpublicquestion_typename2USER-DEFINED
postgresqlquizbowlpublicquestion_typequestion_type_value1character varyingPRIMARY KEY
postgresqlquizbowlpublicuser_logincreate_date6timestamp with time zone
postgresqlquizbowlpublicuser_logincreate_user_id5integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpublicuser_loginuser_agent4text
postgresqlquizbowlpublicuser_loginsession_id3character varying
postgresqlquizbowlpublicuser_loginlogin_ip2cidr
postgresqlquizbowlpublicuser_loginuser_login_id1integerPRIMARY KEY
postgresqlquizbowlpublicversioned_schemasha13character varying40
postgresqlquizbowlpublicversioned_schemarevision2integer
postgresqlquizbowlpublicversioned_schemaname1character varying
postgresqlquizbowlpublicsubmissionlast_mod_date10timestamp with time zone
postgresqlquizbowlpublicsubmissionlast_mod_user_id9integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpublicsubmissioncreate_date8timestamp with time zone
postgresqlquizbowlpublicsubmissioncreate_user_id7integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpublicsubmissionpoints6integer
postgresqlquizbowlpublicsubmissionis_correct5boolean
postgresqlquizbowlpublicsubmissiontime_to_answer4numeric
postgresqlquizbowlpublicsubmissionanswer3USER-DEFINED
postgresqlquizbowlpublicsubmissionevent_question_id2integerFOREIGN KEYpublicevent_questionevent_question_id
postgresqlquizbowlpublicsubmissionsubmission_id1integerPRIMARY KEY
postgresqlquizbowlpubliceventlast_mod_date8timestamp with time zone
postgresqlquizbowlpubliceventlast_mod_user_id7integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpubliceventcreate_date6timestamp with time zone
postgresqlquizbowlpubliceventcreate_user_id5integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpubliceventend_time4timestamp with time zone
postgresqlquizbowlpubliceventstart_time3timestamp with time zone
postgresqlquizbowlpubliceventname2USER-DEFINED
postgresqlquizbowlpubliceventevent_id1integerPRIMARY KEY
postgresqlquizbowlpublicquestionlast_mod_date13timestamp with time zone
postgresqlquizbowlpublicquestionlast_mod_user_id12integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpublicquestioncreate_date11timestamp with time zone
postgresqlquizbowlpublicquestioncreate_user_id10integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpublicquestionlevel_id9character varying
postgresqlquizbowlpublicquestionpoints8integer
postgresqlquizbowlpublicquestionexplanation7USER-DEFINED
postgresqlquizbowlpublicquestionquestion_type_value6character varyingFOREIGN KEYpublicquestion_typequestion_type_value
postgresqlquizbowlpublicquestionanswer_value5USER-DEFINED
postgresqlquizbowlpublicquestionanswer4USER-DEFINED
postgresqlquizbowlpublicquestionoptions3text
postgresqlquizbowlpublicquestionquestion2USER-DEFINED
postgresqlquizbowlpublicquestionquestion_id1integerPRIMARY KEY
postgresqlquizbowlpublicteamlast_mod_date8timestamp with time zone
postgresqlquizbowlpublicteamlast_mod_user_id7integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpublicteamcreate_date6timestamp with time zone
postgresqlquizbowlpublicteamcreate_user_id5integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpublicteammascot4USER-DEFINED
postgresqlquizbowlpublicteamname3USER-DEFINEDUNIQUE
postgresqlquizbowlpublicteamschool_id2integerFOREIGN KEYpublicschoolschool_id
postgresqlquizbowlpublicteamteam_id1integerPRIMARY KEY
postgresqlquizbowlpublicuser_accountlast_mod_date12timestamp with time zone
postgresqlquizbowlpublicuser_accountlast_mod_user_id11integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpublicuser_accountcreate_date10timestamp with time zone
postgresqlquizbowlpublicuser_accountcreate_user_id9integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpublicuser_accountis_admin8boolean
postgresqlquizbowlpublicuser_accountpassword7character varying
postgresqlquizbowlpublicuser_accountlast_name6USER-DEFINED
postgresqlquizbowlpublicuser_accountfirst_name5USER-DEFINED
postgresqlquizbowlpublicuser_accountemail4USER-DEFINEDUNIQUE
postgresqlquizbowlpublicuser_accountschool_id3integerFOREIGN KEYpublicschoolschool_id
postgresqlquizbowlpublicuser_accountteam_id2integerFOREIGN KEYpublicteamteam_id
postgresqlquizbowlpublicuser_accountuser_id1integerPRIMARY KEY

What would the header be for the columns?

I don't believe there should be a header as row and column names are specified in the provided csv.

Where is this csv? 

I'm referring to the output above. This forum doesn't allow for an actual attachment but that text is in CSV format.

Ah I see. I guess to be more clear explicitly what are those column names?

I'm trying to re-write the query for BigQuery and I want to ensure what I am pulling is correct.

Here is the select statement for the postgresql output above. Please let me know if this isn't what you are looking for!

SELECT 'postgresql' AS dbmst.table_catalogt.table_schemat.table_namec.column_namec.ordinal_positionc.data_typec.character_maximum_lengthn.constraint_typek2.table_schemak2.table_namek2.column_name

I was hoping for a translation but I guess I can dig through the tables and figure out what equivalents should amount to.

Hi David D how did you get on with this?  We are super keen on the same feature or workaround.  Did you figure it out?

Hello! Are there any updates to this? They recently released INFORMATION_SCHEMA capabilities [in beta]: https://cloud.google.com/bigquery/docs/information-schema-intro

Does this make the workaround usable?

Hi there

There are no updates with this on our end. However if you give it a go would you mind updating this post for our other users? Thanks in advance for your help!

Are there any plans to implement this integration with BigQuery? It is very much needed

Edit: I tried it using sqlserver and still doesn't work. As mentioned above there are several columns that don't exist in INFORMATION_SCHEMA

Hey I know I am late to the party but  this works however you need to create the relations manually but that is just drag and drop.... it gets all your tables into lucidcharts not perfect but it does the job. make sure you save results to clipboard (bq) and then choose the `sqlserver` import data and voila....

hope it helps 

SELECT 'sqlserver' dbms
t.TABLE_CATALOG
t.TABLE_SCHEMA
t.TABLE_NAME
c.COLUMN_NAME
c.ORDINAL_POSITION
c.DATA_TYPE
 0 as CHARACTER_MAXIMUM_LENGTH
"" as CONSTRAINT_TYPE
"" as TABLE_SCHEMA2
"" as TABLE_NAME2
"" as COLUMN_NAME2
FROM YOUR_DATASET.INFORMATION_SCHEMA.TABLES t LEFT JOIN
YOUR_DATASET.INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_CATALOG=c.TABLE_CATALOG AND t.TABLE_SCHEMA=c.TABLE_SCHEMA AND t.TABLE_NAME=c.TABLE_NAME

does lucid have a step by step guide for importing BQ?

will the lucid diagram update to reflect changes in BQ?

Hi @terry m102 thank you for continuing this thread. We do not currently Support import from BigQuery. However you can create your own output file using the example output file posted by @maison w above. 

You can reference this article for information on how to import your database once you have created an output file. 

We're always looking for ways to improve Lucidchart and your feedback is the best way for us to do that. Would you mind adding your request for import from BigQuery to our product feedback section? Ideas submitted there are passed on to the product development team for their consideration.

FWIW Alberto's solution worked for me as well.