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)

Views with Keys

Views with ALL Fields

In order to create these ERD diagrams, I created a Google Drive Colab Jupyter Notebook 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} ;;
}
}

 

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  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!

By: Jeff Huth – Bytecode IO’s Data Engineer, Architect and Analyst