Jinja Templating

Overview

Templating is the ability to add programmatic capabilities to the SQL Language. Preset's SQL Lab supports query templating via the Jinja Framework, which is a web template engine for the Python programing Language.

The inclusion of Jinja increases flexibility in Preset filter functionality and features multiple use cases, such as:

• Displaying only the currently logged in user’s data.
• 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 via Preset dashboard URL.
• If table contains an array, then you can search for entries with a particular value in the array.
• Ability to apply OR statements in a filter for two different columns (e.g., ‘name’ = “Matthew” OR ‘age’ = 12)
• Personalized dashboards.
• Control access to data using Row Level access based on User_id.
• Investigate access to Superset Metadata by User_id.

Pre-defined Jinja Macros

Current Username

The {{ current_username() }} macro returns the username of the currently logged in user.

• Macro: {{ current_username() }}
• Parameters: None
• Returns: String

SELECT * FROM jinja_username_demo WHERE "username" = '{{ current_username() }}'

SELECT * FROM jinja_username_demo WHERE "username" = 'test@gmail.com'

Current User ID

The {{ current_user_id() }} macro returns the user_id of the currently logged in user.

• Macro: {{ current_user_id() }}
• Parameters: None
• Returns: Integer

Custom URL Parameters

The {{ url_param('custom_variable') }} macro lets you define arbitrary URL
parameters and reference them in your SQL code.

• Macro: {{ url_param('custom_variable') }}
• Parameters: Takes in string with the name of parameter passed into URL. Pass specified parameter in query into URL such as url/?reg=North+America.
• Returns: String

SELECT count(*)
FROM ORDERS
WHERE country_code = '{{ url_param('countrycode') }}'

SELECT count(*)
FROM ORDERS
WHERE country_code = 'ES'

SELECT count(*)
FROM ORDERS
WHERE country_code = 'US'

Filter Values

You can retrieve the value for a specific filter as a list using the {{ filter_values() }} macro.

This is useful if:

• You want to use a filter component to filter a query where the name of filter component column doesn't match the one in the select statement.
• You want to have the ability to filter inside the main query to increase speed.

SELECT action, count(*) as times
FROM logs
WHERE
    action in ({{ "'" + "','".join(filter_values('action_type')) + "'" }})
GROUP BY action

Additionally, if NULL values need to be included with a filter, an additional OR clause may need to be added to the WHERE clause to include this case.

You can use this feature to reference the start & end datetimes from a time filter using:

• Start datetime value: {{ from_dttm }}
• End datetime value: {{ to_dttm }}

Boolean Values

Boolean filter values can also be used for filtering. However, the extra single quotes added to the Filter Values Jinja needs to be removed.

SELECT name, count(*)
FROM "Slack Users"
WHERE
    is_bot in ({{",".join(filter_values('bot_filter'))}})
GROUP BY name

For this Jinja templated filter to operate, the values in column bot_filter of the dataset populating the filter has to be strings, not booleans.

Filters for a Specific Column

The {{ get_filters() }} macro returns the filters applied to a given column. In addition to
returning the values (similar to how filter_values() does), the get_filters() macro
returns the operator specified in the Explore UI.

This is useful if:

• You want to handle more than the IN operator in your SQL clause.
• You want to handle generating custom SQL conditions for a filter.
• You want to have the ability to filter inside the main query for speed purposes.

 WITH RECURSIVE
    superiors(employee_id, manager_id, full_name, level, lineage) AS (
    SELECT
        employee_id,
        manager_id,
        full_name,
    1 as level,
    employee_id as lineage
    FROM
        employees
    WHERE
    1=1
    {# Render a blank line #}
    {%- for filter in get_filters('full_name', remove_filter=True) -%}
    {%- if filter.get('op') == 'IN' -%}
        AND
        full_name IN ( {{ "'" + "', '".join(filter.get('val')) + "'" }} )
    {%- endif -%}
    {%- if filter.get('op') == 'LIKE' -%}
        AND
        full_name LIKE {{ "'" + filter.get('val') + "'" }}
    {%- endif -%}
    {%- endfor -%}
    UNION ALL
        SELECT
            e.employee_id,
            e.manager_id,
            e.full_name,
    s.level + 1 as level,
    s.lineage
        FROM
            employees e,
        superiors s
        WHERE s.manager_id = e.employee_id
    )
    SELECT
    employee_id, manager_id, full_name, level, lineage
    FROM
    superiors
    order by lineage, level

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

Using Template Parameters in SQL Lab

The Template Parameters feature enables you to assign a set of parameters as JSON and make them available in your SQL using the Jinja Framework.

In the example below, we will highlight the rows containing {{date_start}} and {{date_end}}, then select the ellipsis icon. In the sub-menu, select Parameters.

The Template Parameters panel will appear. In this panel, you can define a JSON parameter to represent the highlighted SQL code. In the graphic below, we are using "dates" to define the starting & ending dates.