Create an ERD from Big Query dataset tables (Hack)
Observe a diagram of the architecture of a system or a database can help us very quickly locate where we are within the “big picture”.
Recently I started learning big query and the data I was querying was publicly available on the cloud marketplace. Since this were databases that I was not familiar with I would have to use metadata to understand the names of columns, tables and relationships.
It was taking me a long time to write queries because I didn’t know where the data was and also, by not being able to see the big picture I couldn’t discover if what I wanted to access was actually easier to access if I went through a different path. So I decided to generate an ERD(Entities Relational Diagram).
After fighting with the GUI for some 30 minutes and searching the web for how could I generate an ERD from a Big Query dataset I arrived to the conclusion that this feature was not yet supported. I needed to find an alternative way of building the ERD myself.
Maybe using DBeaver(SQL Client) I thought, but to do that I would have to create the tables and then extract them as ERD or use their GUI to create the ERD manually from the scratch. My second thought was Umlet it’s a great tool that I used many times for diagrams but the syntax was a bit convoluted. I didn’t want to be typing for too long.
Just as I was losing hope I came across Quick DBD. I click on the “Try The App” button and in less than 60 seconds I saw that this could be my tool of choice for this task. The syntax to draw the tables and relationships was super easy so I went back to big query to extract the metadata and to my surprise the modifications I had to do were minimal. This is what I did step by step.
1- Got the names of all the tables in the dataset
2- The column name and the datatype of each column in each table
For this step I crafted a few queries and I executed them together.
3- Copy the results to the clipboard and port them to QuickDBD
For this step I would just Ctrl + Click on each of the “View Results” links on the side and then in each tab I would copy to clipboard, close the tab and then I would go to QDBD with the data.
With the data of this table then I would go to QDBD and click on the “Try The App” button on the top of the page.
Once at the app I would paste the data on the left edit area.
To fix this errors the effort is minimal. What needs to be done is:
- remove the first row and type the name of the table
- untabify.
To untabify I needed to go to Edit->Untabify
Once those 2 steps were done the table displayed on the right side.
4- Relationships
To do the relationships it is not difficult. I just used the drag function from the foreign key to the desired Id but also I could just simply type it on the editor on the side.
Note the syntax in the editor side also is very easy if we prefer to type it manually. user_id INT64 FK >- users.id
5- Export as image
The last step is to export the ERD as an image. A save option is available but for that registration is required.
I hope you found this interesting, if so please leave some claps and follow Javing. Since very recently I also have a Youtube channel. I need to do a lot of improvement and actually do editing on the videos since both the audio and the image is not so good, hopefully I will improve that with time. This video explains the same thing but unfortunately due to an issue with the screen brightness is not possible to see the database tables.
