SQL 模板

Jinja 模板

SQL Lab 和 Explore 在查询中支持 Jinja 模板。要启用模板，需要在 superset_config.py 中启用 ENABLE_TEMPLATE_PROCESSING 功能标志。启用模板后，python 代码可以嵌入虚拟数据集和 Explore 中的过滤器和指标控件中的自定义 SQL 中。默认情况下，以下变量在 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）

例如，要将时间范围添加到虚拟数据集，您可以编写以下内容

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

末尾的 1 = 1 确保即使未设置时间过滤器，WHERE 子句中也存在值。对于许多数据库引擎，这可以替换为 true。

请注意，Jinja 参数在查询中使用双括号调用，在逻辑块中使用单括号调用。

要将自定义功能添加到 Jinja 上下文，您需要通过在您的环境中定义 JINJA_CONTEXT_ADDONS 来重载默认的 Jinja 上下文（superset_config.py）。在此字典中引用的对象可供用户在 Jinja 上下文可用的地方使用。

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

Jinja 模板的默认值可以通过 SQL Lab 用户界面中的“参数”菜单指定。在 UI 中，您可以将一组参数指定为 JSON

{
  "my_table": "foo"
}

参数通过使用 Jinja 模板语法在您的 SQL 中可用（例如：SELECT * FROM {{ my_table }}）。SQL Lab 模板参数与数据集一起存储为 TEMPLATE PARAMETERS。

有一个特殊的 _filters 参数，可用于测试 Jinja 模板中使用的过滤器。

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

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

请注意，_filters 不会与数据集一起存储。它仅在 SQL Lab UI 中使用。

除了默认的 Jinja 模板之外，SQL Lab 还通过在您的 Superset 配置中设置 CUSTOM_TEMPLATE_PROCESSORS 来支持自定义模板处理器。此字典中的值将覆盖指定数据库引擎的默认 Jinja 模板处理器。以下示例配置了一个自定义 Presto 模板处理器，该处理器使用正则表达式解析实现了自己的宏模板处理逻辑。它使用 $ 风格的宏，而不是 Jinja 模板中的 {{ }} 风格。

通过使用 CUSTOM_TEMPLATE_PROCESSORS 配置它，Presto 数据库上的 SQL 模板将由自定义模板处理器处理，而不是默认的模板处理器。

def 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 还包括一个带有可插拔后端的实时查询验证功能。您可以通过将类似以下代码块添加到您的配置文件中来配置使用哪个数据库引擎使用哪个验证实现

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

可用的验证器和名称可以在 sql_validators 中找到。

可用宏

在本节中，我们将逐步介绍 Superset 中预定义的 Jinja 宏。

当前用户名

{{ current_username() }} 宏返回当前登录用户的 username。

如果您在 Superset 配置中启用了缓存，则默认情况下 Superset 在计算缓存键时将使用 username 值。缓存键是唯一的标识符，用于确定将来是否存在缓存命中，以及 Superset 是否可以检索缓存数据。

您可以通过在 Jinja 代码中添加以下参数来禁用将 username 值包含在缓存键的计算中

{{ current_username(add_to_cache_keys=False) }}

当前用户 ID

{{ current_user_id() }} 宏返回当前登录用户的帐户 ID。

如果您在 Superset 配置中启用了缓存，则默认情况下 Superset 在计算缓存键时将使用帐户 id 值。缓存键是唯一的标识符，用于确定将来是否存在缓存命中，以及 Superset 是否可以检索缓存数据。

您可以通过在 Jinja 代码中添加以下参数来禁用将帐户 id 值包含在缓存键的计算中

{{ current_user_id(add_to_cache_keys=False) }}

当前用户电子邮件

{{ current_user_email() }} 宏返回当前登录用户的电子邮件地址。

如果您在 Superset 配置中启用了缓存，则默认情况下 Superset 在计算缓存键时将使用电子邮件地址值。缓存键是唯一的标识符，用于确定将来是否存在缓存命中，以及 Superset 是否可以检索缓存数据。

您可以通过在 Jinja 代码中添加以下参数来禁用将电子邮件值包含在缓存键的计算中

{{ current_user_email(add_to_cache_keys=False) }}

自定义 URL 参数

{{ url_param('custom_variable') }} 宏允许您定义任意 URL 参数并在 SQL 代码中引用它们。

以下是一个具体的例子

• 您在 SQL Lab 中编写以下查询

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

• 您在域名 www.example.com 上托管 Superset，并将以下 SQL Lab URL www.example.com/superset/sqllab?countrycode=ES 发送给您在西班牙的同事，并将以下 SQL Lab URL www.example.com/superset/sqllab?countrycode=US 发送给您在美国的同事

• 对于您在西班牙的同事来说，SQL Lab 查询将呈现为

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

• 对于您在美国的同事来说，SQL Lab 查询将呈现为

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

显式包含缓存键中的值

{{ cache_key_wrapper() }} 函数显式指示 Superset 将一个值添加到用于计算缓存键的值的累积列表中。

此函数仅在您希望将自己的自定义函数返回值包装在缓存键中时才需要。您可以在 此处 获得更多上下文。

请注意，此函数为 current_user_id() 和 current_username() 函数调用中的 user_id 和 username 值的缓存提供支持（如果您启用了缓存）。

过滤器值

您可以使用 {{ filter_values() }} 作为列表检索特定过滤器的值。

这在以下情况下很有用

• 您希望使用过滤器组件来过滤查询，其中过滤器组件列的名称与 select 语句中的列的名称不匹配
• 您希望能够在主查询中进行过滤，以提高性能

以下是一个具体的例子

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() 宏还返回 Explore UI 中指定的运算符。

这在以下情况下很有用

• 您希望在 SQL 子句中处理除 IN 运算符之外的更多运算符
• 您希望能够为过滤器生成自定义 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

时间过滤器

{{ get_time_filter() }} 宏返回应用于特定列的时间过滤器。这在您希望在虚拟数据集中处理时间过滤器时很有用，因为默认情况下时间过滤器会放置在外部查询上。这可以显着提高性能，因为许多数据库和查询引擎能够在内部查询上放置时间过滤器而不是外部查询的情况下更好地优化查询。

该宏接受以下参数

• column: 时间列的名称。保留未定义以引用来自仪表板原生时间范围过滤器的時間範圍（如果有）。
• default: 如果时间过滤器不存在或其值为 No filter，则要回退的默认值
• target_type: 目标数据库识别的目标时间类型（例如 TIMESTAMP、DATE 或 DATETIME）。如果定义了 column，则格式将默认为该列的类型。这用于生成返回的 TimeFilter 对象的 from_expr 和 to_expr 属性的格式。
• strftime: 使用 datetime 的 strftime 方法进行自定义时间格式化。(请参阅文档以了解有效的格式代码)。如果定义了 strftime，则 target_type 将被忽略。
• remove_filter: 设置为 true 时，将过滤器标记为已处理，将其从外部查询中删除。在过滤器仅应应用于内部查询时很有用。

返回类型具有以下属性

• from_expr: 时间过滤器的开始时间（如果有）
• to_expr: 时间过滤器的结束时间（如果有）
• time_range: 应用的时间范围

以下是用 Superset 元存储中的 logs 表的具体例子

{% 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 %}

假设我们正在创建一个表格图表，使用简单的 COUNT(*) 作为指标，并在 dttm 列上使用 Last week 时间过滤器，这将在 Postgres 上呈现以下查询（请注意时间过滤器的格式以及外部查询中缺少时间过滤器）

SELECT COUNT(*) AS count
FROM
  (SELECT *,
          'Last week' AS time_range
   FROM public.logs
   WHERE 1 = 1
     AND dttm >= TO_TIMESTAMP('2024-08-27 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.US')
     AND dttm < TO_TIMESTAMP('2024-09-03 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.US')) AS virtual_table
ORDER BY count DESC
LIMIT 1000;

在使用 default 参数时，模板化查询可以简化，因为端点将始终被定义（要使用固定时间范围，您也可以使用类似 default="2024-08-27 : 2024-09-03" 的内容）

{% 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 宏查询物理数据集和虚拟数据集。这在您已在数据集中定义了计算列和指标并希望在临时 SQL Lab 查询中重用这些定义时很有用。

要使用该宏，首先您需要找到数据集的 ID。这可以通过转到显示所有数据集的视图、将鼠标悬停在您感兴趣的数据集上以及查看其 URL 来完成。例如，如果数据集的 URL 是 https://superset.example.org/explore/?dataset_type=table&dataset_id=42，则其 ID 为 42。

获得 ID 后，您可以像查询表一样查询它

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

指标

{{ metric('metric_key', dataset_id) }} 宏可用于从数据集中检索指标 SQL 语法。这对于不同的目的很有用

• 覆盖图表级别的指标标签
• 将多个指标合并到一个计算中
• 在 SQL Lab 中检索指标语法
• 跨数据集重复使用指标

该宏避免了复制粘贴，使用户能够在数据集层级上集中指标定义。

dataset_id 参数是可选的，如果未提供，Superset 将使用来自上下文的当前数据集（例如，在图表构建器中使用此宏时，默认情况下，macro_key 将在为图表提供支持的数据集中搜索）。该参数可以在 SQL Lab 中使用，或者在从另一个数据集中获取指标时使用。