home User Guide Getting Started Help Center Documentation Community Training Certification
menu
close
settings
Looker keyboard_arrow_down
language keyboard_arrow_down
English
Français
Deutsch
日本語
search
print
sql (for derived tables)

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

You can also use sql as part of a field, as described on the sql (for fields) parameter documentation 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. You can use any legal SQL query in the sql parameter, as long as the SQL query is written in raw SQL and does not reference Looker fields. For a more complete understanding of derived tables, see the Derived tables in Looker documentation page.

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 GROUP BY 1 ;; } }

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 Help Center 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:

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 %} GROUP BY 1 ;; } }

Examples

Create a customer_order_facts derived table:

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

Things to consider

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 the connection parameter documentation page). 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.

Using _filters['view_name.field_name'] in PDT definitions requires sql_quote

Using the _filters['view_name.field_name'] Liquid variable in the sql parameter for a derived table without the Liquid filter sql_quote returns the following deprecation warning:

Using "_filters[]" in Derived Table SQL without "sql_quote" is discouraged.

This warning applies to _filters['view_name.field_name'] references in derived table SQL where the value is rendered in SQL and the filter returns a string value. For example:

{{ _filters['view_name.field_name'] }}

and

{% assign foo = _filters['view_name.field_name']  %} foo

To avoid the deprecation warning, append the Liquid filter | sql_quote to the Liquid variable declaration. In the examples above, to avoid the warning, the Liquid variable syntax should be:

{{ _filters['view_name.field_name'] | sql_quote }}

and

{% assign foo = _filters['view_name.field_name'] | sql_quote %} foo

You can also use sql_quote with arrays to quote multiple values. For example:

{{ _filters['view_name.field_name'] | split:"," | sql_quote | join:"," }}

Top