Go to View Parameter List

This page refers to the sql parameter that is part of a derived_table

sql can also be used as part of a dimension, measure, filter, or dimension_group, described here

Usage

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 derived tables in sql. To do so, use:

${view_name_of_other_derived_table.SQL_TABLE_NAME} AS view_name_of_other_derived_table

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:

You should not use a closing semi-colon with the sql parameter; doing so will cause an error.

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 Discourse article 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:

Examples

Create a customer_order_facts derived table:

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. Typically the model determines which database connection is used (see connection for model), although it’s possible the connection is defined by the explore (see connection for explore). 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.