User Guide Help Center Documentation User Forums Training
New LookML
Old LookML
New LookML
Looker
Incorporating SQL and Referring to LookML Objects

To write powerful LookML, you need to be able to reference existing dimensions, measures, views, or derived tables even if they are not in the current scope. You also need to reference columns in the underlying table and use your database dialect’s function calls to manipulate those values.

Substitution Operator ($)

The substitution operator, $, makes LookML code more reusable and modular, enabling you to reference other views and derived tables, columns in a SQL table, or LookML dimensions and measures. This is good for two reasons. First, you might have already worked out a really tricky dimension or measure, and you won’t need to write out all the complexity again. Second, if you change something about a dimension or measure, that change can propagate to everything else that relies on it.

There are several ways that you can use the substitution operator:

${TABLE}.column_name references a column in the table that is connected to the view you’re working on. For example:

dimension: customer_id { type: number sql: ${TABLE}.customer_id ;; }

${field_name} references a dimension or measure within the view you’re working on. For example:

measure: total_population { type: sum sql: ${population} ;; }

${view_name.field_name} references a dimension or measure from another view. For example:

dimension: lifetime_orders { type: number sql: ${user_order_facts.lifetime_orders} ;; }

${view_name.SQL_TABLE_NAME} references another view or derived table. Note that SQL_TABLE_NAME in this reference is a literal string; you do not need to replace it with anything. For example:

explore: trips { view_label: "Long Trips" # This will ensure that we only see trips that are longer than average! sql_always_where: ${trips.trip_duration}>=(SELECT tripduration FROM ${average_trip_duration.SQL_TABLE_NAME});; }

${view_name.SQL_TABLE_NAME} does not work in the SQL Runner or in the sql_trigger parameter used with datagroups.

Scoping and Naming

You can name explores, views, fields, and sets. These Looker identifiers are written without quotation marks.

LookML fields and sets have full names and short names:

Below is an example showing many forms of names and scope. This is an unrealistic group of fields, but is shown to demonstrate a variety of possible scoping expressions.

view: orders { # "orders" becomes the containing scope measure: count { # short name, equivalent to orders.count type: count } dimension: customer_id { # short name, equivalent to orders.customer_id type: number sql: ${TABLE}.customer_id ;; } dimension: customer_address { # short name, equivalent to orders.customer_address sql: ${customer.address} ;; # full name, references a field defined in the "customer" view } set: drill_fields { # short name, equivalent to orders.drill_fields fields: [ count, # short name, equivalent to orders.count customer.id # full name, references a field defined in the "customer" view ] } }

In the dimension: customer_address declaration above, note that the underlying view for the SQL block (customer) is different than the enclosing view scope (orders). This can be useful when you need to compare fields between two different views.

When a view (we’ll call it “view A”) refers to a field defined in a different view (we’ll call it “view B”), there are a few things to keep in mind:

  1. The view B file must be included in the same model as view A, using the include parameter.
  2. View B must be joined to view A in one or more explores. See our Working with Joins in LookML page to learn about joins.

SQL Dialect

Looker supports many database types, such as MySQL, Postgres, Redshift, BigQuery, and so on. Each database supports a slightly different feature set with differing function names, referred to as the SQL dialect.

LookML is designed to work with all SQL dialects, and LookML does not prefer one dialect over the other. However, you will need to include SQL code expressions (known as SQL blocks) in certain LookML parameters. With these parameters, Looker passes the SQL expression directly to your database, so you must use the SQL dialect that matches your database. For example, if you use a SQL function, it must be a function that your database supports.

SQL Block

Some LookML parameters require you to provide raw SQL expressions, so that Looker can understand how to retrieve data from your database.

LookML parameters starting with sql_ expect a SQL expression of some form. Examples are: sql_always_where, sql_on, and sql_table_name. The most common SQL block parameter is sql for dimension and measure field definitions, which specifies the SQL expression that defines the dimension or measure.

The code you specify in a SQL block can be as simple as a single field name or as complex as a correlated subselect. The content can be quite complex, accommodating almost any need you might have to express custom query logic in raw SQL. Note that the code you use in SQL blocks must match the SQL dialect used by the database.

Example SQL Blocks for Dimensions and Measures

Below are examples of SQL blocks for dimensions and measures. The LookML substitution operator ($) can make these sql declarations appear deceptively unlike SQL. However, after substitution has occurred, the resulting string is pure SQL, which Looker injects into the SELECT clause of the query.

dimension: id { primary_key: yes sql: ${TABLE}.id ;; # Specify the primary key, id } measure: average_cost { type: average value_format: "0.00" sql: ${order_items.cost} ;; # Specify the field that you want to average } dimension: name { sql: CONCAT(${first_name}, ' ', ${last_name}) ;; } dimension: days_in_inventory { type: int sql: DATEDIFF(${sold_date}, ${created_date}) ;; }

As shown in the last two dimensions above, sql blocks can use functions supported by the underlying database (such as the MySQL functions CONCAT and DATEDIFF in this example).

Example sql Block With a Correlated Sub-select

In a field’s sql block you can place any SQL statement, including a correlated sub-select. An example is below:

view: customers { dimension: id { primary_key: yes sql: ${TABLE}.id ;; } dimension: first_order_id { sql: (SELECT MIN(id) FROM orders o WHERE o.customer_id=customers.id) ;; # correlated sub-select to derive the value for "first_order_id" } }

Example sql Block for Derived Tables

Derived tables use the sql block to specify the query that derives the table. An example is below:

view: user_order_facts { derived_table: { sql: # Get the number of orders for each user SELECT user_id , COUNT(*) as lifetime_orders FROM orders GROUP BY 1 ;; } # later, dimension declarations reference the derived column(s)   dimension: lifetime_orders { type: number } }

Top