SQL 模板

Jinja 模板

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

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

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

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

Jinja 模板的默认值可以通过 SQL Lab 用户界面中的 Parameters 菜单指定。在 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:
    """当前日期作为字符串返回。"""
    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):
    """自定义的Presto模板处理器。"""

    engine = "presto"

    def process_template(self, sql: str, **kwargs) -> str:
        """使用正则表达式处理带有$风格宏的SQL模板。"""
        # 添加自定义宏函数。
        macros = {
            "DATE": partial(DATE, datetime.utcnow())
        }  # type: Dict[str, Any]
        # 使用上下文和kwargs中定义的宏进行更新。
        macros.update(self.context)
        macros.update(kwargs)

        def replacer(match):
            """将$风格的宏扩展为相应的函数调用。"""
            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 配置中启用了缓存，那么默认情况下，username 值将用于 Superset 计算缓存键。缓存键是一个唯一标识符，用于确定未来是否存在缓存命中，并且 Superset 可以检索缓存数据。

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

{{ current_username(add_to_cache_keys=False) }}

当前用户ID

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

如果您在 Superset 配置中启用了缓存，那么默认情况下，账户 id 值将用于 Superset 计算缓存键。缓存键是一个唯一标识符，用于确定未来是否存在缓存命中，并且 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 将一个值添加到用于计算缓存键的累积值列表中。

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

请注意，此函数支持在启用了缓存的情况下，current_user_id() 和 current_username() 函数调用中 user_id 和 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() 宏还返回在 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

    {# 渲染一个空行 #}
    {%- 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 方法进行自定义时间格式化（查看有效格式代码的文档）。定义后将忽略 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实验室中检索度量语法
跨数据集重用度量

这个宏避免了复制/粘贴，允许用户在数据集层集中定义度量。

dataset_id参数是可选的，如果不提供，Superset将使用上下文中的当前数据集（例如，在使用此宏的图表构建器中，默认情况下将在驱动图表的数据集中搜索macro_key）。该参数可以在SQL实验室中使用，或者在从另一个数据集获取度量时使用。

Jinja 模板​

可用的宏​

Jinja 模板

可用的宏