Skip to content

Instantly share code, notes, and snippets.


Scott Hoover githoov

View GitHub Profile
githoov /
Last active Jul 17, 2021
A Quick Walkthrough of Looker and Accessing its APIs

Looker Overview

As a reminder, Looker has three core components: first, it provides a physical modeling layer (LookML), which one uses to abstract SQL generation and define complex transforms; second, Looker consumes the LookML model and generates dialect-specific SQL, which it then issues to the database via JDBC; third, it provides a web-based UI which acts as both a LookML IDE as well as the primary means of exploring data.

The LookML Model

Let's consider a simple schema containing two tables, job and warehouse, which we'll use as an example. In Looker, our representation of these tables would look like this:

job definition

- view: job
  sql_table_name: public.job

Redshift Tables Used

pg_table_def, stl_query, stl_querytext, stl_tr_conflict, stl_explain, stl_alert_event_log, stl_ddltext, stl_scan, stl_save, stl_hashjoin, stl_hash, stl_plan_info, stl_return, and information_schema.table_constraints.

Queries to Extract Features

  • execution time
select (endtime - starttime) as execution_time_in_ms 
from stl_query 
where query = QUERY_ID;
View index.html
<!DOCTYPE html>
<html lang="en">
<meta charset="utf-8">
<title>Tree Example</title>
.node {
View data.json
{"links": [{"type": "", "target": "Bulletproof Arcade Limited", "source": "PlayFab, Inc."}, {"type": "", "target": "Fluffy Fairy Games GmbH", "source": "PlayFab, Inc."}, {"type": "", "target": "Hubspot, Inc.", "source": "Amplitude Analytics"}, {"type": "", "target": "Bee Square S.L.", "source": "PlayFab, Inc."}, {"type": "", "target": "Starbreeze", "source": "PlayFab, Inc."}, {"type": "", "target": "Seismic Games", "source": "PlayFab, Inc."}, {"type": "", "target": "BoomTV Inc", "source": "PlayFab, Inc."}, {"type": "", "target": "Integrated Direct Marketing", "source": "ThinkSmart Data Services LLC."}, {"type": "", "target": "Integrated Direct Marketing", "source": "Square, Inc."}, {"type": "", "target": "Turner Broadcasting System, Inc.", "source": "Turner Broadcasting System, Inc."}, {"type": "", "target": "Cimpress USA Incorporated", "source": "Cimpress USA Incorporated"}, {"type": "", "target": "Slice & Co", "source": "Kustomer, Inc."}, {"type": "", "target": "Datascan Technologies LLC", "source": "Datasc
View index.html
<script src="//"></script>
var links = [
"target":"PlayFab, Inc.",
"source":"Bulletproof Arcade Limited"
View flare.json
"name": "snowhouse",
"children": [
"name": "snowhouse_import",
"children": [
{"children": [{"size": 14336, "name": "ACCOUNT_ETL"}, {"size": 0, "name": "ACCOUNT_INVITATION_ETL"}, {"size": 0, "name": "ACCOUNT_INVITATION_RAW"}, {"size": 2108928, "name": "ACCOUNT_RAW"}, {"size": 11019776, "name": "AUTHN_EVENT_ETL"}, {"size": 984576, "name": "AUTHN_EVENT_RAW"}, {"size": 20779008, "name": "CLIENT_TELEMETRY"}, {"size": 9216, "name": "CONFIGURATION_ETL"}, {"size": 36352, "name": "CONFIGURATION_ID_TABLE_ETL"}, {"size": 36974592, "name": "CONFIGURATION_ID_TABLE_RAW"}, {"size": 13059072, "name": "CONFIGURATION_RAW"}, {"size": 3584, "name": "CONFIGURATION_STRING_ETL"}, {"size": 525824, "name": "CONFIGURATION_STRING_RAW"}, {"size": 348672, "name": "CONSTRAINT_ETL"}, {"size": 758784, "name": "CONSTRAINT_RAW"}, {"size": 112128, "name": "COUNTING_NODE_ETL"}, {"size": 196286464, "name": "COUNTING_NODE_RAW"}, {"size": 70144, "name": "DATABASE_ETL"}, {"size": 48640, "name": "DATABASE_RAW"}, {"size": 35044864,
column_name table_name
foo accounts
bar accounts
baz users
column_name table_name ...
foo accounts ...
bar accounts ...
baz users ...