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 orderived table. Note thatSQL_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 thesql_trigger parameter used withdatagroups.

Scoping and naming

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

LookML fields and sets havefull names andshort names:

• Full names are of the form<view>.<field-name | set-name>. The left side indicates the scope, which is the view that contains the field or set. The right side specifies the particular field or set name.
• Short names simply take the form<field-name | set-name>, with no separatingperiod. Looker expands short names into full names by using the scope in which they are used.

Following 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 thedimension: customer_address declaration, 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 theinclude parameter.
2. View B must be joined to view A in one or more Explores. See ourWorking with joins in LookML page to learn about joins.

SQL dialect

Looker supportsmany 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 theSQL 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 asSQL 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 blocks

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 withsql_ expect a SQL expression of some form. Examples are:sql_always_where,sql_on, andsql_table_name. The most common LookML parameter for SQL blocks issql, used in dimension and measure field definitions to specify 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 theSQL dialect used by the database.

Example SQL blocks for dimensions and measures

Following are examples of SQL blocks for dimensions and measures. TheLookML substitution operator ($) can make thesesql declarations appear deceptively unlike SQL. However, after substitution has occurred, the resulting string is pure SQL, which Looker injects into theSELECT 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, SQL blocks can use functions supported by the underlying database (such as the MySQL functionsCONCAT andDATEDIFF in this example).

Example SQL block with a correlated subselect

You can place any SQL statement in a field's SQL block, including a correlated subselect. The following is an example:

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. The following is an example:

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 theorders.created_date dimension within another field, you can use the syntax${orders.created_date::date} to ensure that thecreated_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:

• In a reference to a string field, you can use::string.
• In a reference to a number field, you can use::string and::number.
• In a reference to a date or time field, you can use::string,::date, and::datetime.
  References using::string and::date return data in the query time zone, while references using::datetime return data in the database time zone.
• In a reference to a yesno field, you can use::string,::number, and::boolean.
  Field references using the::boolean type are not available for database dialects that do not support the Boolean data type.
• In a reference to a location field, you can use::latitude and::longitude.

Using LookML field type references with date fields

As an example, suppose you have anenrollment_month dimension and agraduation_month dimension, both of which were created withindimension groups oftype: time. In this example, theenrollment_month dimension is produced by the following dimension group oftype: time:

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

Similarly, thegraduation_month dimension is created by the following dimension group oftype: time:

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

Using theenrollment_month andgraduation_month dimensions, you can calculate how many months or years passed between a student's enrollment and graduation by creating adimension group oftype: duration. However, because some date fields are cast as strings in the SQL that Looker generates, setting theenrollment_month andgraduation_month dimensions as the values forsql_start andsql_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 dimension group oftype: duration, referencing theraw timeframes from theenrollment andgraduation dimension groups in thesql_start andsql_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 calledDuration Enrolled, with individual dimensionsMonths Enrolled andYears Enrolled.

A simpler alternative to using theraw timeframe in a dimension group oftype: duration is to specify the::date or::datetime reference type for the fields referenced in thesql_start andsql_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 aDuration Enrolled dimension group, but using the::date reference allows theenrollment_month andgraduation_month dimensions to be used without using araw 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 oftype: duration, see thedimension_group parameter documentation page.

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

LookML constants

Theconstant parameter lets you 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 constantcity with the value"Okayama" as follows:

constant: city {  value: "Okayama"}

Thecity constant can then be referenced throughout your project using the syntax@{city}. For example, you can use thecity constant with thelabel parameter in theusers Explore:

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

Looker then displaysOkayama Users in both theExplore menu and in the title of the Explore, rather than the defaultUsers.

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