The Use Case
Some use cases call for the table you are querying from to be dynamic, based on what the user selects on the front end. For example, you could have multiple schemas that are identical in structure. We often encounter this when working with GA Premium data, where we can see tables such as myproject:00001.ga_sessions_
, myproject:00002.ga_sessions_
, and myproject:00003.ga_sessions_
. The 00001
, 00002
, and 00003
prefixes could represent data for different countries, traffic sources, etc.
Using a parameter that we plug in to the sql_table_name
of our view (read about the actual parameter
parameter on the parameter
documentation page), we can apply a filter to Looks and dashboards that allows users to select the table they will query from (selecting a specific country
table for a countries
view) — with a user-friendly name!
The Code
This is how we define the parameter that becomes the front-end filter for end users to select the table to query. We will apply it to our sql_table_name
parameter:
view: countries {
sql_table_name: {% parameter tablename %};;
parameter: tablename {
type: unquoted
allowed_value: {
label: "UK"
value: "00001.ga_sessions"
}
allowed_value: {
label: "Germany"
value: "00002.ga_sessions"
}
}
}
Referencing Derived Tables
We can use a similar approach to dynamically reference derived tables in our sql_table_name
parameter. Since we use the LookML syntax ${view_name.SQL_TABLE_NAME}
to reference derived tables in SQL parameters, we need to use slightly different Liquid syntax:
view: my_view {
sql_table_name:
{% if tablename._parameter_value == 'derived table name' %} #If the parameter value = 'derived table name'
${derived_table_name.SQL_TABLE_NAME} #Then reference it with ${view_name.SQL_TABLE_NAME} syntax
{% elsif tablename._parameter_value == 'another table' %} #Otherwise 'another table' option is selected, reference this table instead
schema.table_name
-- Add as many of these elsif clauses as you want, one for each parameter option.
{% else %}
schema.table_name #Use schema.table_name as the default if no selection is made
{% endif %} ;;
parameter: tablename {
type: unquoted
allowed_value: {
label: "derived table"
value: "derived table name"
}
allowed_value: {
label: "another table"
value: "schema.table_name"
}
# add as many allowed_values as you have tables
}
}
Here we are using the Liquid variable _parameter_value
, which allows us to reference the selected value of a parameter and ensures the passing of special characters like brackets. This is necessary for referencing PDTs with the ${view_name.SQL_TABLE_NAME}
syntax, while normal, non-derived tables will use the schema.table_name
format.