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 and/or username).

• 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, 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).

Syntax

Jinja Macros

Jinja macros should be added inside double curly brackets ({{ }}). Parameters are also added inside parenthesis:

{{ macro_name(parameters) }}

These are the available macros:

• current_username()

• current_user_id()

• current_user_email()

• url_param()

• filter_values()

• metric()

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 (one user would have different 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() }}'

URL Parameter

The url_param() 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() 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 an array 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 in ({{ filter_values('country')|where_in }}) 
-- country is the name of the column used on the dashboard filter.
-- |where_in would return the results in the correct SQL syntax -> 'USA', 'Belgium', 'Canada' instead of ['USA', 'Belgium', 'Canada']

Metric

The metric() 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"

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