Looker Community

Looker ERD Generator

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!

8 Likes

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

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

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

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.

1 Like

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.

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.

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.

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)

1 Like