User Guide Getting Started Help Center Documentation Community Training
New LookML
Old LookML
New LookML
Looker
  
English
Français
Deutsch
日本語
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 with 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 Subselect

In a field’s sql block you can place any SQL statement, including a correlated subselect. 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 subselect 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 } }

LookML Field Type References

When you reference an existing LookML field within another field, you can instruct Looker to treat the referenced field as a specific data type by using a double colon (::) followed by the desired type. For example, if you reference the orders.created_date dimension within another field, you can use the syntax ${orders.created_date::date} to ensure that the created_date field will be treated as a date field in the SQL that Looker generates, rather than being cast as a string.

The data type you can use in a reference depends on the data type of the original field you are referencing. For example, if you are referencing a string field, the only data type you can specify is ::string. Here is the full list of allowed field type references you can use for each type of field:

Using LookML Field Type References with Date Fields

As an example, suppose you have an enrollment_month dimension and a graduation_month dimension, both of which were created within dimension groups of type: time. In this example, the enrollment_month dimension is produced by the following type: time dimension group:

dimension_group: enrollment { type: time timeframes: [time, date, week, month, year, raw] sql: ${TABLE}.enrollment_date ;; }

Similarly, the graduation_month dimension is created by the following type: time dimension group:

dimension_group: graduation { type: time timeframes: [time, date, week, month, year, raw] sql: ${TABLE}.graduation_date ;; }

Using the enrollment_month and graduation_month dimensions, you can calculate how many months or years passed between a student’s enrollment and graduation by creating a dimension group of type: duration. However, because some date fields are cast as strings in the SQL that Looker generates, setting the enrollment_month and graduation_month dimensions as the values for sql_start and sql_end can result in an error.

To avoid an error resulting from these time fields being cast as strings, one option is to create a type: duration dimension group, referencing the raw timeframes from the enrollment and graduation dimension groups in the sql_start and sql_end parameters:

dimension_group: enrolled { type: duration intervals: [month, year] sql_start: ${enrollment_raw} ;; sql_end: ${graduation_raw} ;; }

In the Explore UI, this generates a dimension group called Duration Enrolled, with individual dimensions Months Enrolled and Years Enrolled.

A simpler alternative to using the raw timeframe in a type: duration dimension group is to specify the ::date or ::datetime reference type for the fields referenced in the sql_start and sql_end parameters.

dimension_group: enrolled { type: duration intervals: [month, year] sql_start: ${enrollment_month::date} ;; sql_end: ${graduation_month::date} ;; }

The LookML in this example also creates a Duration Enrolled dimension group, but using the ::date reference allows the enrollment_month and graduation_month dimensions to be used without using a raw timeframe or casting them as strings with SQL.

For an additional example of how LookML field type references can be used to create custom dimension groups of type: duration, see the documentation page for the dimension_group parameter.

This syntax is not available with measures of type: list, which cannot be referenced as of Looker 6.8.

LookML Constants

The constant parameter allows you to specify a constant you can use throughout a LookML project. With LookML constants, you can define a value once and reference it in any part of your project where strings are accepted, thus reducing repetition in your LookML code.

Constants must be declared within a project manifest file, and the value for a constant must be a string. For example, you can define a constant city with the value "Okayama" as follows:

project_name: "project name" constant: city { value: "Okayama" }

The city constant can then be referenced throughout your project using the syntax @{city}. For example, you can use the city constant with the label parameter in the users Explore:

explore: users { label: "@{city} Users" }

Looker then displays Okayama Users in both the Explore menu and in the title of the Explore, rather than the default Users.

For more information and examples of how you can use LookML constants to write reusable code, see the constant parameter documentation page.

Top