User Guide Getting Started Help Center Documentation Community Training
New LookML
Old LookML
New LookML
Looker
  
English
日本語
sql (for Derived Tables)

This page refers to the sql parameter that is part of a derived table.

sql can also be used as part of a field, as described on this page.

Usage

view: my_derived_table {
  derived_table: {
    sql:
      SELECT *
      FROM events
      WHERE type NOT IN ('test', 'staff') ;;
    …
  }
}

Hierarchy

sql

Default Value

None

Accepts

A SQL block

Definition

sql allows you to specify the SQL that will be used to generate a derived table, and can be any legal SQL query. It should be written in raw SQL, and should not reference Looker views or fields. For a more complete understanding of derived tables, see our documentation here.

In addition to referencing normal database tables, you can also reference LookML views or derived tables in sql. To do so, use:

${view_or_derived_table_name.SQL_TABLE_NAME} AS view_or_derived_table_name

The SQL_TABLE_NAME in this reference is a literal string; you do not need to replace it with anything. For example, if you wanted to reference a derived table named key_customer in your key_customer_order_facts derived table, you might have something like this:

view: key_customer_order_facts { derived_table: { sql: SELECT customer_id, COUNT( * ) AS lifetime_orders FROM order INNER JOIN ${key_customer.SQL_TABLE_NAME} AS key_customer ON order.customer_id = key_customer.id ;; } }

Liquid Variables with sql

You can also use Liquid variables with the sql parameter. Liquid variables let you access data such as the values in a field, data about the field, and filters applied to the field.

The date_start and date_end Liquid variables are very useful for database dialects that partition data into multiple tables by date, such as BigQuery. See this Community topic for an in-depth explanation.

The _in_query, _is_selected, and _is_filtered Liquid variables in particular can add some interesting functionality to derived tables. They return true or false based on whether a field or filter has been included in a query. There are some intricacies that should be considered to properly use these true/false values; see the Liquid variable page for more information.

For example, this derived table changes the database table that it queries based on which fields the user has selected:

view: dynamic_order_counts { derived_table: { sql: SELECT period, number_of_orders FROM {% if dates.reporting_date._in_query %} daily_orders {% elsif dates.reporting_week._in_query %} weekly_orders {% else %} monthly_orders {% endif %} ;; } }

Examples

Create a customer_order_facts derived table:

view: customer_order_facts { derived_table: { sql: SELECT customer_id, COUNT( * ) AS lifetime_orders FROM order ;; } }

Common Challenges

Tables Referenced By sql Must Be Accessible From The Current Connection

views that contain a derived table are referenced by an explore or join parameter, which in turn is referenced by a model. The model determines which database connection is used (see connection for model). Database connections themselves are defined in the Admin section of Looker. When you reference a table in the sql parameter, the table needs to be accessible within the associated connection.

Use Raw SQL With sql, Not Looker Field References

The SQL that you write into a derived table sql parameter should be raw SQL, referencing the underlying columns and tables from your database. It should not reference Looker field names or view names.

Top