-
Print
-
DarkLight
-
PDF
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
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
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.
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
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.