Макросы Jinja Templates
SQL Lab and Explore поддерживает шаблоны Jinja в запросах. При помощи шаблонов Jinja код Python можно встраивать в виртуальные наборы данных и в пользовательский SQL в фильтрах и элементах управления метриками в Исследовании. По умолчанию в контексте Jinja доступны следующие переменные:
columns: столбцы, по которым следует группировать в запросе.filter: фильтры, примененные в запросе.from_dttm: начальноеdatetimeзначение из выбранного диапазона времени (Noneесли не определено).to_dttm: конечноеdatetimeзначение из выбранного диапазона времени (Noneесли не определено).groupby: столбцы, по которым следует группировать в запросе.metrics: агрегатные выражения в запросе.row_limit: ограничение строки запроса.row_offset: смещение строки запроса.table_columns: столбцы, доступные в наборе данных.time_column: временной столбец запроса (None, если не определен).time_grain: выбранный интервал времени (None, если не определен).
Например, чтобы добавить диапазон времени в виртуальный набор данных, вы можете написать следующее:
SELECT * from tbl where dttm_col > '{{ from_dttm }}' and dttm_col < '{{ to_dttm }}'
Вы также можете использовать логику Jinja, чтобы сделать ваш запрос устойчивым к очистке фильтра временного диапазона:
SELECT *
FROM tbl
WHERE (
{% if from_dttm is not none %}
dttm_col > '{{ from_dttm }}' AND
{% endif %}
{% if to_dttm is not none %}
dttm_col < '{{ to_dttm }}' AND
{% endif %}
true
)
Обратите внимание, что параметры Jinja вызываются в двойных скобках в запросе и без в логических блоках.
Доступные макросы
В этом разделе мы рассмотрим предопределенные макросы Jinja в Superset.
Текущее имя пользователя
Макрос {{ current_username() }} возвращает имя пользователя, вошедшего в систему в данный момент.
Если в вашей конфигурации Superset включено кэширование, то значение по умолчанию username будет использоваться Superset при вычислении ключа кэша. Ключ кэша — это уникальный идентификатор, который определяет, будет ли попадание в кэш в будущем, и Superset сможет получить кэшированные данные.
Вы можете отключить включение username значения в вычисление ключа кеша, добавив в свой код Jinja следующий параметр:
{{ current_username(add_to_cache_keys=False) }}
Текущий идентификатор пользователя
Макрос {{ current_user_id() }} возвращает user_id текущего пользователя, вошедшего в систему.
Если в вашей конфигурации Superset включено кэширование, то значение по умолчанию user_id будет использоваться Superset при вычислении ключа кэша. Ключ кэша — это уникальный идентификатор, который определяет, будет ли попадание в кэш в будущем, и Superset сможет получить кэшированные данные.
Вы можете отключить включение user_id значения в вычисление ключа кеша, добавив в свой код Jinja следующий параметр:
{{ current_user_id(add_to_cache_keys=False) }}
Пользовательские параметры URL
Макрос {{ url_param('custom_variable') }} позволяет вам определять произвольные параметры URL и ссылаться на них в коде SQL.
Пример:
- Вы пишете следующий запрос в SQL Lab:
SELECT count(*)
FROM ORDERS
WHERE country_code = '{{ url_param('countrycode') }}'
- Вы размещаете Superset в домене
www.sdp.sberdata.ruи отправляете своему коллеге в России следующий URL-адрес SQL Lab,www.sdp.sberdata.ru/superset/sqllab?countrycode=RUа своему коллеге в Белоруссии — следующий URL-адрес SQL Labwww.sdp.sberdata.ru/superset/sqllab?countrycode=BY
Для вашего коллеги в России запрос SQL Lab будет отображаться следующим образом:
SELECT count(*)
FROM ORDERS
WHERE country_code = 'RU'
Для вашего коллеги в Белоруссии запрос SQL Lab будет отображаться следующим образом:
SELECT count(*)
FROM ORDERS
WHERE country_code = 'BY'
Явное включение значений в ключ кэша
Функция {{ cache_key_wrapper() }} явно указывает Superset добавить значение в накопленный список значений, используемых при вычислении ключа кэша.
Эта функция необходима только в том случае, если вы хотите обернуть возвращаемые значения собственной пользовательской функции в ключ кэша. С дополнительной информацией можно ознакомиться по ссылке.
Обратите внимание, что эта функция обеспечивает кэширование значений и в user_id вызовах функций и (если у вас включено кэширование). usernamecurrent_user_id()current_username()
Значения фильтра
Вы можете получить значение для определенного фильтра в виде списка, используя {{ filter_values() }}.
Это полезно, если:
Вы хотите использовать компонент фильтра для фильтрации запроса, в котором имя столбца компонента фильтра не соответствует имени в операторе выбора.
Вы хотите иметь возможность фильтрации внутри основного запроса для повышения производительности.
Пример:
SELECT action, count(*) as times
FROM logs
WHERE
action in {{ filter_values('action_type')|where_in }}
GROUP BY action
Здесь where_in фильтр преобразует список значений из filter_values('action_type') в строку, подходящую для IN выражения.
Фильтры для определенного столбца
Макрос {{ get_filters() }}возвращает фильтры, примененные к данному столбцу. Помимо возврата значений (аналогично тому, как filter_values()), get_filters() макрос возвращает оператор, указанный в пользовательском интерфейсе исследования.
Это полезно, если:
Вы хотите обрабатывать больше, чем оператор IN в своем предложении SQL.
Вы хотите обрабатывать генерацию пользовательских условий SQL для фильтра.
Вы хотите иметь возможность фильтровать внутри основного запроса для ускорения.
Раскрыть пример
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 {{ filter.get('val')|where_in }}
{%- 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
Датасеты
С помощью dataset макроса можно запрашивать физические и виртуальные датасеты. Это полезно, если вы определили вычисляемые столбцы и метрики в своих датасетах и хотите повторно использовать их в запросах SQL Lab.
Чтобы использовать макрос, сначала вам нужно найти id датасета. Это можно сделать, перейдя к представлению, показывающему все наборы данных, наведя указатель мыши на интересующий вас датасет и просмотрев его URL-адрес. Например, если URL-адрес набора данных – www.sdp.sberdata.ru/explore/?dataset_type=table&dataset_id=42, его идентификатор – 42.
Получив идентификатор, вы можете запросить его, как если бы это была таблица:
SELECT * FROM {{ dataset(42) }} LIMIT 10
Если вы хотите выбрать определения метрик, помимо столбцов, вам необходимо передать дополнительный аргумент ключевого слова:
SELECT * FROM {{ dataset(42, include_metrics=True) }} LIMIT 10
Поскольку метрики являются агрегатами, результирующее выражение SQL будет сгруппировано по всем столбцам, не являющимся метриками. Вместо этого вы можете указать подмножество столбцов для группировки:
SELECT * FROM {{ dataset(42, include_metrics=True, columns=["ds", "category"]) }} LIMIT 10