Parametrizing Queries Using Jinja

What is Jinja Templating?

Jinja Templating is a web template engine for Python. It uses a text-based template language that can be used to generate both markup and source code. The inclusion of Jinja increases flexibility in Preset functionality and features multiple use cases, such as:

• Implement access controls based on the currently logged in user’s data (user_id, username, user_email, user_roles, etc).

• Apply dashboard filters directly on the dataset's inner query. 

• Use a filter component to filter a query when the name of a filter column doesn't match one in the current query.

• Apply filter constraints to dashboards via the URL.

• Dynamically modify specific elements of your SQL query (such as calculations, aggregations, etc) based on dashboard filter conditions.

• Advanced controls over operators.

• Personalized dashboards.

On Preset, you can use Jinja on SQL Lab, Chart Controls, Virtual Datasets and also on Row Level Security filters to:

• Add pre-defined macros to your query to return dynamic data.

• Execute logic statements (such as if, for, etc).

Macros

Jinja macros are declared inside double curly brackets ({{ }}). Parameters can be passed inside the parenthesis:

{{ $macro_name($parameters) }}

These are the available macros:

• current_username()

• current_user_id()

• current_user_email()

• current_user_roles()

• current_user_rls_rules()

• url_param()

• filter_values()

• get_filters()

• get_time_filter()

• metric()

• dataset()

By default, the result returned from these macros is added to the cache computation, to avoid sharing cache across users that would get different macro results. You can disable this behavior by passing add_to_cache_keys=False as a parameter (supported by all macros).

Let's take a closer look on them:

Current Username

The current_username() macro returns the logged in user's username value. This value is global (the same across all Workspaces), and can't be modified.

Example: 

SELECT * FROM sales WHERE employee = ' {{ current_username() }} '

Current User ID

Similar to the current_username() macro, the current_user_id() returns the logged in user's ID value. Note that the ID is local to the Workspace (a user could have distinct IDs on each Workspace), and also can't be modified.

Example:

SELECT * FROM sales WHERE employee_id = {{ current_user_id() }}

Current User Email

The current_user_email() macro is another alternative to retrieve user-specific information through Jinja. It returns the email address of the logged-in user:

SELECT * FROM sales WHERE employee = '{{ current_user_email() }}'

Current User Roles

The current_user_roles() macro returns a list of roles the user is added to:

SELECT '{{ current_user_roles()|tojson }}' as current_user_roles

Current User RLS Rules

The current_user_rls_rules() macro returns a list of RLS rules currently applied for the user in the dataset:

SELECT '{{ current_user_rls_rules()|tojson }}' as current_user_roles

URL Parameter

The url_param($param_name) macro would fetch parameter values from the dashboard URL. When using this macro, you need to specify the parameter name. Multiple parameters can be specified in the URL using &.

Example:

• Dashboard URL: https://abcd1234.us2a.app.preset.io/superset/dashboard/1/?location=US&month=8

SELECT * FROM "Vehicle Sales"
WHERE country  = '{{ url_param('location') }}' -- location is the parameter name specified in the URL
AND month = {{ url_param('month') }} -- month is the parameter name specified in the URL

Filter Values

The filter_values($column_name) macro return information from a dashboard filter. To use it, it's required to specify the column name powering the filter. The result would be a list of all applied values (["first_value_applied", "second_value_applied", ...]). It's possible to return only a particular position (for example, use [0] to return the first value applied), or use |where_in to return comma-separated values with quotes. 

Example:

SELECT * FROM "Vehicle Sales"
WHERE country = {{ filter_values('country')[0] }}
-- country is the name of the column used on the dashboard filter.

This macro supports additional parameters:

• remove_filter (boolean): When set to true, marks the filter as processed. This is useful when you’re using the filter value in the inner query and don’t want it to also get added in the outer query. Default value = false.

• default (string): Specifies a default return value for the macro, in case there isn’t any filter applied. The string is returned in a list, so for example {{ filter_values('country', default="USA") }}  returns [“USA”].

Get Filters

The get_filters($column_name) macro returns a list of filters applied to a column, including the operator. For example: [{'op': 'IN', 'col': 'year', 'val': [2005]}] . This macro also supports the remove_filter parameter.

Get Time Filters

The get_time_filters($column_name)  macro returns the time filter applied to a specific column. This is useful if you want to handle time filters inside the virtual dataset, as by default the time filter is placed on the outer query. Supported parameters:

• column_name: Name of the temporal column. Leave undefined to reference the time range from a dashboard.

• default: The default value to fall back to if the time filter is not present, or has the value No filter

• target_type: The target temporal type as recognized by the target database (e.g. TIMESTAMP, DATE or DATETIME). If column_name is defined, the format will default to the type of the column. This is used to produce the format of the from_expr and to_expr properties of the returned TimeFilter object.

• strftime: format using the strftime method of datetime for custom time formatting. When defined, target_type is ignored.

• remove_filter: When set to true, mark the filter as processed, removing it from the outer query.

The return type has the following properties:

• from_expr: the start of the time filter (if any).

• to_expr: the end of the time filter (if any).

• time_range: Final time range.

Example:

{% set time_filter = get_time_filter("dttm", default="Last week", remove_filter=True) %}
{% set from_expr = time_filter.from_expr %}
{% set to_expr = time_filter.to_expr %}
{% set time_range = time_filter.time_range %}
SELECT
  *,
  '{{ time_range }}' as time_range
FROM logs
WHERE
dttm >= {{ from_expr }}
AND dttm < {{ to_expr }}

Metric

The metric($metric_key) macro can be used to retrieve the metric SQL syntax from a dataset. This can be useful for different purposes:

• Override the metric label in the chart level (without impacting the dataset).

• Combine multiple metrics in a calculation.

• Retrieve a metric syntax in SQL lab.

• Re-use metrics across datasets.

This macro avoids copy/paste, allowing users to centralize the metric definition in the dataset layer.

SELECT {{ metric('count') }} as count -- count is the key of the metric that is being used
FROM "Vehicle Sales"

When using this macro in the Chart Builder, by default the metric will be searched in the dataset being used. However, it’s possible to also specify the dataset ID (when using the macro in SQL Lab or in case you want to fetch a metric syntax from another dataset):

SELECT {{ metric('count', 3) }} as count -- count is the key of the metric that is being used. 3 is the dataset ID
FROM "Vehicle Sales"

Dataset

The dataset($dataset_id) macro returns the corresponding SELECT statement for a dataset. It’s useful to get a dynamic reference to a dataset definition:

SELECT * from {{ dataset(1) }}
UNION ALL
SELECT * from {{ dataset(2) }}

Filters

Filters are used to modify values. They are applied using the pipe (|) symbol and can be chained together.

Preset support Jinja builtin filters, and also custom filters:

Where In

Converts a list ([“First”, “Second”, “Third”]) into a compatible SQL syntax to filter data: (‘First’, ‘Second’, ‘Third). For example:

SELECT * FROM "Vehicle Sales"
WHERE country in {{ filter_values('country')|where_in }}

Logic Statements

Logic statements can be used to perform different actions based on validation results, and also perform iteration loops. The logic statement should be added inside curly brackets and percentage sign ({% %}). It's always required to start and end the block:

{% if something %}
     SQL syntax
{% else %}
     SQL syntax
{% endif %}

Example:

SELECT * FROM "Vehicle Sales"
WHERE country
{% if filter_values('operation')[0] == 'Equals' %} -- execute in case the first value applied to this dashboard filter is "Equals"
     = '{{ filter_values('location')[0] }}'
{% elif filter_values('operation') == 'In' %} -- in case the first condition isn't met, check if the filter value is "In"
     in {{ filter_values('location')|where_in }}
{% else %} -- execute in case none of the conditions above are met
     not in {{ filter_values('location')|where_in }}
{% endif %} -- end block

Video References

How to Apply an OR Filter using a Jinja Template

Jinja URL Parameters

Using a Jinja Template to Create Filters in Array Elements