Question

Looker ERD Generator

  • 12 November 2019
  • 17 replies
  • 2093 views

Userlevel 3

Looker ERD Generator


(from an Explore using the Looker API)


Have you ever wanted to create an Entity Relationship Diagram (ERD) from your Looker Model Explores?


As a Looker partner consultant, I get asked for an ERD or Data Model all of the time. Data Model diagrams provide a concise, visual way to show how the tables (or views) and fields relate to one another.


Views Only (Conceptual Data Model)

er


Views with Keys


Views with ALL Fields


In order to create these ERD diagrams, I created a Google Drive Colab Jupyter Notebook (click here) which you may COPY and modify and run. The Jupyter Notebook works best when you start at the top, read each section, and run each Python code cell snippet one-by-one from top-to-bottom.


This Jupyter Notebook uses Python3, the Looker API, and ERAlchemy to create an Entity Relationship Diagram (ERD) for a Selected Project, Model, Explore, and ERD Type.


It walks you through step-by-step to:



  1. Make a COPY of the Notebook

  2. Install Necessary Python Libraries

  3. Set Base Directory and Credentials (config.json)

  4. Client Class to Access the Looker API

  5. Get Projects (list and select a Project)

  6. Get LookML Models (list and select a Model)

  7. Select an Explore (list and select an Explore)

  8. Get Explore Details (Joins & Fields)

  9. Create an ER Model and Diagram


There are 3 ERD Types supported:



  • View Only

  • View + Keys (Primary Key and Foreign Keys)

  • View + ALL Fields (ALL dimensions, measures, filters, parameters)


Disclaimer: There is no guarantee that this will run and successfully and create an ERD diagram for you. Looker may change their API and you may create your Model Explores differently than I do.


This tool currently works if you define your Model Explore Joins using relationship and sql_on parameters with join conditions.


This tool may not work for Extended Explores and Extended Views.


LookML Model Explore


LookML code for the Explore used in the example above.


explore: sales {
view_name: sales
group_label: "Event Tickets"
label: "Sales"
join: listings {
view_label: "Listings"
type: left_outer
relationship: many_to_one
sql_on: ${sales.list_id} = ${listings.list_id} ;;
}
join: events {
view_label: "Events"
type: left_outer
relationship: many_to_one
sql_on: ${sales.event_id} = ${events.event_id} ;;
}
join: buyers {
view_label: "Buying User"
from: users
type: left_outer
relationship: many_to_one
sql_on: ${sales.buyer_id} = ${buyers.user_id} ;;
}
join: sellers {
view_label: "Selling User"
from: users
type: left_outer
relationship: many_to_one
sql_on: ${sales.seller_id} = ${sellers.user_id} ;;
}
join: venue {
view_label: "Venues"
type: left_outer
relationship: many_to_one
sql_on: ${events.venue_id} = ${venue.venue_id} ;;
}
join: categories {
view_label: "Categories"
type: left_outer
relationship: many_to_one
sql_on: ${events.cat_id} = ${categories.cat_id} ;;
}
join: date_lkp {
view_label: "Dates"
type: left_outer
relationship: many_to_one
sql_on: ${sales.date_id} = ${date_lkp.date_id} ;;
}
}

Summary


Thank your for taking the time to go through the Colab Jupyter Notebook and generate data models from your own Looker instance. I hope the code was helpful and you were able to get it to work. Please let me know in the Comments below if you have any questions or feedback. I will do my best to help you out or explain any of the the code.


Please feel free share or post any data model diagrams that you generate with your own Jupyter Notebooks; or any python code snippets for how to improve the code and make the Looker ERD Generator even better!


17 replies

Userlevel 7
Badge +1

Very interesting… somehow mine fails at setting the directory even though the config file and the notebook are saved in `/content/drive/My Drive/Colab Notebooks/’


FileNotFoundError: [Errno 2] No such file or directory: '/content/drive/My Drive/Colab Notebooks'

image


Mounting was successful

Userlevel 3

Hi Dawid - I added a couple steps at the top so that you can practice navigating and listing what you see in each directory. Please take a look at those and see if you are able to navigate, directory-by-directory to the folder where you saved the Jupyter Notebook.

Kind Regards,

Jeff

Userlevel 1

Hello Jeff/Looker,


I am trying to run below step



and it is giving me the below error. (all other steps before this ran fine)




IndexError Traceback (most recent call last)

in ()

88 right_field = join_condition.strip().split(’=’)[1].strip().replace(’$’, ‘’).replace(’{’, ‘’).replace(’}’, ‘’)

89 right_field_view = right_field.strip().split(’.’)[0]

—> 90 right_field_name = right_field.strip().split(’.’)[1]

91

92 if join_relationship == ‘many_to_one’:


IndexError: list index out of range


Can you please advise if i am doing anything wrong? Do i have to manually input anything in this part of the step?


Thank you

Raman

Userlevel 3

Hi Raman,

The statement that is failing is parsing your join sql_on. That statement assumes you are using sql_on and have join conditions like ${table1.field1} = ${table2.field2} AND .... Can you let me know the SQL for the join or joins it may be failing on?

Thanks,

Jeff

Hey @JeffH I am also getting the same error. I believe it’s because of the misnaming of primary key/foreign key relationship but it’s basically like person.id = table.person_id

Jeff! Awesome work! I’ve been looking for something that does this exactly without using dbtcloud.

Userlevel 2
Badge

Was hitting a similar issue and it came down to some join conditions using values rather than keys to limit the join. E.g. ${table1.field1} = ${table2.field2} and ${table1.field2} = true. In that case the right value of the second condition is a single sting, when the code tries to split it into two strings divided by a dot (table.column) then tries to access the second item and fails/exits because splitting string ‘true’ only yields a single-item array.

Userlevel 2
Badge

Also, there’s a step of code that splits multi-condition joins based on the ‘and’ string. But, if any table or column has a name containing the substring ‘and’ the process fails. I work for a company called Handshake, so ‘handshake.column’ shows up often and screws things up haha. Got around it by adding a space(s) around the ‘and’, I imagine there’s a better regular expression that accounts for all edge cases.

Userlevel 1

great solution, but i am getting below error while installing eralchemy


pip install eralchemy


Collecting eralchemy

Downloading https://files.pythonhosted.org/packages/59/86/17072a11407857a26a36d33566c83f1cf1b2467eedffc33ed16d7f84d615/ERAlchemy-1.2.10-py2.py3-none-any.whl

Requirement already satisfied: SQLAlchemy in /usr/local/lib/python3.6/dist-packages (from eralchemy) (1.3.11)

Collecting pygraphviz

Downloading https://files.pythonhosted.org/packages/7e/b1/d6d849ddaf6f11036f9980d433f383d4c13d1ebcfc3cd09bc845bda7e433/pygraphviz-1.5.zip (117kB)

|████████████████████████████████| 122kB 7.3MB/s

Building wheels for collected packages: pygraphviz

Building wheel for pygraphviz (setup.py) … error

ERROR: Failed building wheel for pygraphviz

Running setup.py clean for pygraphviz

Failed to build pygraphviz

Installing collected packages: pygraphviz, eralchemy

Running setup.py install for pygraphviz … error

ERROR: Command errored out with exit status 1: /usr/bin/python3 -u -c ‘import sys, setuptools, tokenize; sys.argv[0] = ‘"’"’/tmp/pip-install-142gw0p1/pygraphviz/setup.py’"’"’; file=’"’"’/tmp/pip-install-142gw0p1/pygraphviz/setup.py’"’"’;f=getattr(tokenize, ‘"’"‘open’"’"’, open)(file);code=f.read().replace(’"’"’\r\n’"’"’, ‘"’"’\n’"’"’);f.close();exec(compile(code, file, ‘"’"‘exec’"’"’))’ install --record /tmp/pip-record-md72fw88/install-record.txt --single-version-externally-managed --compile Check the logs for full command output


Can someone kindly advise on this?


Thank you

Raman

I get this error when invoking the client:


HTTPError: 401 Client Error: Unauthorized for url: https://<base_url>:19999/api/3.1/projects


Not sure what I’m doing wrong, it looks like all the variables are set, and the same request works fine in Postman using the same client id and secret. It’s getting a valid bearer token. Not sure what’s going wrong here.

Userlevel 7
Badge +1

I saw this too when trying to install, the solution in this StackOverflow did the trick for me. You could also use homebrew (brew install pygraphviz)


Userlevel 3

Hi Tom - it looks like you didn’t run through all of the steps above. The <base_url> should be replaced by the CONFIG parameters in your config.json that you should have created in Google Drive and referenced when instantiating the client.

Does it have to be on Google Drive? I was hoping to run everything locally.

Userlevel 3

No - it could be local with Jupyter Notebook and virtual environment running locally. I used Google Drive because I was using a Colab Notebook (Google drive Jupyter Notebook).

This is really fantastic, Jeff. Thank you very much for posting.

Great code, Jeff! 

A little advice for those who are using this script: 

The lower() function used in 

  for join_condition in join_sql_on.lower().split('and'):

may cause problems in replace function used later on code 

    if join_relationship == 'many_to_one':
join_text = '{} *--? {}'.format(right_field_view, left_field_view).replace(join_name, join_view_name)
elif join_relationship == 'one_to_many':
join_text = '{} *--? {}'.format(left_field_view, right_field_view).replace(join_name, join_view_name)
elif join_relationship == 'one_to_one':
join_text = '{} ?--? {}'.format(right_field_view, left_field_view).replace(join_name, join_view_name)
else: # many_to_many
join_text = '{} *--* {}'.format(right_field_view, left_field_view).replace(join_name, join_view_name)
er_model = er_model + join_text + '\n'

once you have applied lower in 'left_field_view' and 'right_field_view' fields but have not applied it in 'join_name' and 'join_view_name'

Hi all! Checkout the new LookML Diagram (ERD) application now available in the marketplace!

If you have any feedback feel free to DM on twitter or linkedin @leighajarett :slight_smile:

Reply