Jinja Templating
  • 06 Jul 2022
  • 4 Minutes to read
  • Contributors
  • Dark
    Light

Jinja Templating

  • Dark
    Light

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
Use Case: Retrieve Username

Entered SQL:

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

Result:

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
Use Case: Custom URL Parameters

Entered SQL:

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

Scenario:

  • You're hosting Superset at the domain www.example.com
  • You send your co-worker in Spain the SQL Lab URL www.example.com/superset/sqllab?countrycode=ES
  • You send your co-worker in the USA the SQL Lab URL www.example.com/superset/sqllab?countrycode=US

For your coworker in Spain, the SQL Lab query will be rendered as:

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

For your coworker in the USA, the SQL Lab query will be rendered as:

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.
Use Case: Filter Values
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.

Use Case: Boolean Filter Values
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.
Use Case: Filters for a Specific Column
 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 external-link_10x10

Jinja URL Parameters external-link_10x10

Using a Jinja Template to Create Filters in Array Elements external-link_10x10


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.

Jinja1b

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.

Jinja3@2x


Was this article helpful?

What's Next