Создание шаблонов SQL

Войти

Создание шаблонов SQL Lab

Шаблоны Джинджа(Jinja)

SQL Lab и режиме исследования(Explore) в Fincomtech Analytics поддерживают шаблонизацию Jinja в запросах. Чтобы включить шаблонизацию, необходимо установить ENABLE_TEMPLATE_PROCESSING флаг функции в файле конфигурации superset_config.py.

Если шаблонизация включена, код Python можно встраивать в виртуальные наборы данных и пользовательский SQL в элементах управления фильтрами и метриками в Explore. По умолчанию в контексте Jinja доступны следующие переменные:

columns: столбцы, по которым нужно сгруппировать данные в запросе
filter: фильтры, применяемые в запросе
from_dttm: начальное datetime значение из выбранного диапазона времени (None если не определено) (устарело в версии 5.0, вместо этого используйте get_time_filter)
to_dttm: конечное datetime значение из выбранного диапазона времени (None если не определено). (устарело, начиная с версии 5.0, используйте get_time_filter вместо этого)
groupby: столбцы, по которым группируются данные в запросе (устарело)
metrics: агрегатные выражения в запросе
row_limit: ограничение количества строк в запросе
row_offset: смещение строк в запросе
table_columns: столбцы, доступные в наборе данных
time_column: временной столбец в запросе (None если не определено)
time_grain: выбранное временное разрешение (None если не определено)

Например, чтобы добавить временной диапазон в виртуальный набор данных, можно написать следующее:

sqlSELECT *
FROM tbl
WHERE dttm_col > '{{ from_dttm }}' and dttm_col < '{{ to_dttm }}'

Вы также можете использовать логику Jinja, чтобы сделать запрос устойчивым к удалению фильтра timerange:

sqlSELECT *
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 %}
    1 = 1
)

1 = 1 в конце гарантирует, что значение будет присутствовать в предложении WHERE, даже если фильтр времени не установлен. Во многих системах управления базами данных это можно заменить на true.

Обратите внимание: параметры Jinja вызываются в двойных скобках в запросе и в одинарных скобках в логических блоках.

Чтобы добавить пользовательские функции в контекст Jinja, необходимо переопределить контекст Jinja по умолчанию в вашей среде, указав JINJA_CONTEXT_ADDONS в конфигурации Fincomtech Analytics (superset_config.py). Объекты, на которые есть ссылки в этом словаре, доступны пользователям там, где доступен контекст Jinja.

pythonJINJA_CONTEXT_ADDONS = {
    'my_crazy_macro': lambda x: x*2,
}

Значения по умолчанию для шаблонов Jinja можно указать в меню Parameters в пользовательском интерфейсе SQL Lab. В пользовательском интерфейсе можно задать набор параметров в формате JSON:

json{
  "my_table": "foo"
}

Параметры становятся доступными в вашем SQL-запросе (например: SELECT * FROM {{ my_table }}) благодаря использованию синтаксиса шаблонов Jinja. Параметры шаблона SQL Lab хранятся в наборе данных как TEMPLATE PARAMETERS.

Существует специальный параметр _filters, который можно использовать для тестирования фильтров, применяемых в шаблонах Jinja.

json{
  "_filters": [
    {
      "col": "action_type",
      "op": "IN",
      "val": ["sell", "buy"]
    }
  ]
}

Пример запроса:

sqlSELECT action, count(*) as times
FROM logs
WHERE action in {{ filter_values('action_type')|where_in }}
GROUP BY action

Обратите внимание, что _filters не сохраняется вместе с набором данных. Он используется только в пользовательском интерфейсе SQL Lab.

Пользовательские обработчики шаблонов

Помимо шаблонов Jinja по умолчанию, SQL Lab в Fincomtech Analytics также поддерживает пользовательские обработчики шаблонов, которые можно настроить с помощью CUSTOM_TEMPLATE_PROCESSORS в файле конфигурации. Значения в этом словаре переопределяют обработчики шаблонов Jinja по умолчанию для указанной базы данных.

В приведенном ниже примере настраивается пользовательский обработчик шаблонов Presto, который реализует собственную логику обработки макросов с помощью регулярных выражений. Он использует макрос $ вместо {{ }} в шаблонах Jinja.

При настройке с помощью CUSTOM_TEMPLATE_PROCESSORS шаблон SQL в базе данных Presto обрабатывается с помощью пользовательского шаблона, а не шаблона по умолчанию.

pythondef DATE(
    ts: datetime, day_offset: SupportsInt = 0, hour_offset: SupportsInt = 0
) -> str:
    """Current day as a string."""
    day_offset, hour_offset = int(day_offset), int(hour_offset)
    offset_day = (ts + timedelta(days=day_offset, hours=hour_offset)).date()
    return str(offset_day)

class CustomPrestoTemplateProcessor(PrestoTemplateProcessor):
    """A custom presto template processor."""

    engine = "presto"

    def process_template(self, sql: str, **kwargs) -> str:
        """Processes a sql template with $ style macro using regex."""
        # Add custom macros functions.
        macros = {
            "DATE": partial(DATE, datetime.utcnow())
        }  # type: Dict[str, Any]
        # Update with macros defined in context and kwargs.
        macros.update(self.context)
        macros.update(kwargs)

        def replacer(match):
            """Expand $ style macros with corresponding function calls."""
            macro_name, args_str = match.groups()
            args = [a.strip() for a in args_str.split(",")]
            if args == [""]:
                args = []
            f = macros[macro_name[1:]]
            return f(*args)

        macro_names = ["$" + name for name in macros.keys()]
        pattern = r"(%s)\s*\(([^()]*)\)" % "|".join(map(re.escape, macro_names))
        return re.sub(pattern, replacer, sql)

CUSTOM_TEMPLATE_PROCESSORS = {
    CustomPrestoTemplateProcessor.engine: CustomPrestoTemplateProcessor
}

SQL Lab также включает функцию проверки запросов в реальном времени с подключаемыми бэкендами. Вы можете настроить, какая реализация проверки будет использоваться с каким ядром базы данных, добавив в файл конфигурации следующий блок:

pythonFEATURE_FLAGS = {
    'SQL_VALIDATORS_BY_ENGINE': {
        'presto': 'PrestoDBSQLValidator',
    }
}

Доступные макросы

В этом разделе мы рассмотрим предустановленные макросы Jinja в Fincomtech Analytics.

Текущее имя пользователя

Макрос {{ current_username() }} возвращает username текущего пользователя, вошедшего в систему.

Если в конфигурации Fincomtech Analytics включено кэширование, то по умолчанию при вычислении ключа кэша Fincomtech Analytics будет использовать значение username. Ключ кэша — это уникальный идентификатор, который определяет, будет ли в будущем достигнут кэш и сможет ли система получить кэшированные данные.

Вы можете отключить включение значения username в расчет ключа кэша, добавив в код Jinja следующий параметр:
{{ current_username(add_to_cache_keys=False) }}

Текущий идентификатор пользователя

Макрос {{ current_user_id() }} возвращает идентификатор учетной записи пользователя, вошедшего в систему в данный момент.

Если в конфигурации Fincomtech Analytics включено кэширование, то по умолчанию при вычислении ключа кэша id система будет использовать значение account. Вы можете отключить включение значения account id в расчет ключа кэша:
{{ current_user_id(add_to_cache_keys=False) }}

Адрес электронной почты текущего пользователя

Макрос {{ current_user_email() }} возвращает адрес электронной почты пользователя.

Если включено кэширование, Fincomtech Analytics будет использовать email в ключе кэша. Отключить это можно так:
{{ current_user_email(add_to_cache_keys=False) }}

Текущие роли пользователя

Макрос {{ current_user_roles() }} возвращает массив ролей вошедшего в систему пользователя.

Если включено кэширование, Fincomtech Analytics будет использовать значение roles в ключе кэша. Отключить это можно так:
{{ current_user_roles(add_to_cache_keys=False) }}

Вы можете преобразовать массив в JSON, добавив |tojson:
{{ current_user_roles()|tojson }}

Пример использования с фильтром |where_in в SQL-запросе:

sqlSELECT * FROM users WHERE role IN {{ current_user_roles()|where_in }}

Если роли ['admin', 'viewer'], запрос будет:

sqlSELECT * FROM users WHERE role IN ('admin', 'viewer')

Текущие пользовательские правила RLS

Макрос {{ current_user_rls_rules() }} возвращает массив правил RLS (Row Level Security), применяемых к текущему набору данных для вошедшего в систему пользователя.

Пользовательские параметры URL

Макрос {{ url_param('custom_variable') }} позволяет задавать произвольные параметры URL и ссылаться на них в коде SQL.

Вот конкретный пример:
В SQL Lab вы пишете следующий запрос:

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

К примеру Вы размещаете Fincomtech Analytics на домене https://analitic.fincom.tech отправляете коллегам ссылки:

Россия:http://analitic.fincom.tech/sqllab/p/countrycode=RU
Белоруссия: http://analitic.fincom.tech/sqllab/p/countrycode=BY

Для коллеги из Белоруссии запрос преобразуется в:

sqlSELECT count(*) FROM ORDERS WHERE country_code = 'BY'

Явное включение значений в ключ кэша

Функция {{ cache_key_wrapper() }} явно указывает Fincomtech Analytics добавить значение в накопленный список значений, используемых при вычислении ключа кэша.
Эта функция нужна только в том случае, если вы хотите обернуть возвращаемые значения собственной пользовательской функции в ключ кэша. Обратите внимание, что эта функция обеспечивает кэширование значений user_id и username в вызовах функций current_user_id() и current_username() (если у вас включено кэширование).

Значения фильтров

Вы можете получить список значений для определенного фильтра с помощью {{ filter_values() }}.
Это полезно, если вы хотите фильтровать запрос по столбцу, имя которого отличается от имени в операторе выбора, или для повышения производительности через фильтрацию во внутреннем запросе.

Пример:

sqlSELECT action, count(*) as times
FROM logs
WHERE
    action in {{ filter_values('action_type')|where_in }}
GROUP BY action

Фильтры для конкретного столбца

Макрос {{ get_filters() }} возвращает фильтры, применённые к заданному столбцу. Помимо значений (как filter_values()), макрос get_filters() возвращает оператор, указанный в интерфейсе Explore.

Пример использования для сложной логики фильтрации:

sqlWITH RECURSIVE superiors(...) AS (
    SELECT ...
    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 ... FROM superiors

Временной Фильтр

Макрос {{ get_time_filter() }} возвращает временной фильтр, применённый к определённому столбцу. Это позволяет применять временные фильтры внутри виртуального набора данных (внутреннего запроса) для оптимизации производительности.

Макрос принимает следующие параметры:

column: Имя временного столбца.
default: Значение по умолчанию (например, "Last week").
target_type: Целевой временной тип БД (TIMESTAMP, DATE).
strftime: Формат времени (Python strftime).
remove_filter: Если true, фильтр удаляется из внешнего запроса.

Тип возвращаемого значения имеет свойства: from_expr, to_expr, time_range.

Пример использования таблицы logs из хранилища метаданных Fincomtech Analytics:

sql{% set time_filter = get_time_filter("dttm", remove_filter=True) %}
{% set from_expr = time_filter.from_expr %}
{% set to_expr = time_filter.to_expr %}
{% set time_range = time_filter.time_range %}
SELECT
  *,
  '{{ time_range }}' as time_range
FROM logs
{% if from_expr or to_expr %}WHERE 1 = 1
{% if from_expr %}AND dttm >= {{ from_expr }}{% endif %}
{% if to_expr %}AND dttm < {{ to_expr }}{% endif %}
{% endif %}

Использование параметра default:

sql{% set time_filter = get_time_filter("dttm", default="Last week", remove_filter=True) %}
SELECT
  *,
  '{{ time_filter.time_range }}' as time_range
FROM logs
WHERE
  dttm >= {{ time_filter.from_expr }}
  AND dttm < {{ time_filter.to_expr }}

Наборы данных

Физические и виртуальные наборы данных можно запрашивать с помощью макроса dataset. Это удобно для повторного использования определений метрик и вычисляемых столбцов.

Чтобы использовать макрос, найдите ID набора данных в его URL (например, dataset_id=42).
Запрос данных:

sqlSELECT * FROM {{ dataset(42) }} LIMIT 10

Включение метрик (агрегированных данных):

sqlSELECT * FROM {{ dataset(42, include_metrics=True) }} LIMIT 10

Группировка по подмножеству столбцов:

sqlSELECT * FROM {{ dataset(42, include_metrics=True, columns=["ds", "category"]) }} LIMIT 10

Метрики

Макрос {{ metric('metric_key', dataset_id) }} можно использовать для получения синтаксиса SQL для метрик из набора данных.

Это позволяет:

Переопределять метки метрик.
Объединять метрики в расчетах.
Использовать синтаксис метрики в SQL Lab.
Повторно использовать метрики в разных наборах данных.

Параметр dataset_id необязателен; если не указан, Fincomtech Analytics будет использовать текущий набор данных из контекста.