sql_preamble

Usage

explore: explore_name {
  sql_preamble: SQL STATEMENT  ;;
}

Definition

The sql_preamble parameter specifies a SQL statement that executes before queries in the Explore are run. The primary use for sql_preamble is to create user-defined functions (UDFs) on Google BigQuery. Most database dialects let you install UDFs directly on the database server, but Google BigQuery is stateless, so sql_preamble provides this functionality.

Using Liquid in sql_preamble

The sql_preamble parameter supports Liquid and user attributes, which enables dynamic SQL generation for use cases such as setting session variables, selecting different warehouses or databases, implementing partition pruning in BigQuery, and allowing for flexible data filtering by interacting with the underlying database more directly.

The following Liquid use patterns are supported in the sql_preamble parameter:

• _filters['view_name.field_name']
• {% date_start date_filter_name %}
• {% date_end date_filter_name %}
• {% condition filter_name %} sql_or_lookml_reference {% endcondition %}
• {% if %} sql_or_lookml_reference {% else %} other_value {% endif %}
• {% parameter parameter_name %}
• parameter_name._parameter_value
• _user_attributes['name_of_attribute']
• _localization['localization_key']
• _model._name
• _explore._name
• _explore._dashboard_url
• _query._query_timezone
• view_name._in_query
• view_name.field_name._in_query
• view_name.field_name._is_selected
• view_name.field_name._is_filtered
• _view._name

The following Liquid use patterns are not supported in sql_preamble:

• value
• rendered_value
• filterable_value
• link
• linked_value
• _field._name

Examples

Using sql_preamble to add a temporary median

Create a user-defined function (UDF) on Google BigQuery that computes a median by using the Google BigQuery CREATE TEMP FUNCTION statement, and inserts the median function into the Google BigQuery SQL before each query in the Explore is run.

explore: salary {
  sql_preamble:
    CREATE TEMP FUNCTION MEDIAN(a_num ARRAY<FLOAT64>)
    RETURNS FLOAT64 AS ((
       SELECT
          AVG(num)
        FROM (
          SELECT
            row_number() OVER (ORDER BY num) -1 as rn
            , num
          FROM UNNEST(a_num) num
        )
        WHERE
          rn = TRUNC(ARRAY_LENGTH(a_num)/2)
            OR (
             MOD(ARRAY_LENGTH(a_num), 2) = 0 AND
              rn = TRUNC(ARRAY_LENGTH(a_num)/2)-1 )
    ));
  ;;
}

Using Liquid in sql_preamble to localize the table name

Use Liquid in sql_preamble to create a temporary table that's named based on the user's selected language, so that a single LookML model can adapt to different internal naming conventions:

explore: orders {
  sql_preamble: 
    WITH {{ _localization['storage_table_name'] }} AS (select * from users);;
  ...
}

Instead of using a hardcoded name like mytable for the temporary table, this code will replace the Liquid variable with the actual string that's defined in the locale strings file for the key storage_table_name. See Using locale in Liquid variables for more information.

The Looker query that follows the preamble would then reference this dynamically named table. For example, the SQL query would use FROM {{ _localization['storage_table_name'] }}.