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 上下文添加自定义功能，您需要在 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:
    """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) }}

当前用户角色

{{ current_user_roles() }} 宏返回当前登录用户的角色数组。

如果您的 Superset 配置中启用了缓存，那么在计算缓存键时，Superset 默认会使用角色值。缓存键是一个唯一标识符，用于确定未来是否存在缓存命中，以便 Superset 可以检索缓存数据。

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

{{ current_user_roles(add_to_cache_keys=False) }}

您可以通过在 Jinja 代码中添加 |tojson 来将数组 JSON 字符串化。

{{ current_user_roles()|tojson }}

您可以使用 |where_in 过滤器在 SQL 语句中使用您的角色。例如，如果 current_user_roles() 返回 ['admin', 'viewer']，则以下模板：

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

将呈现为：

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

当前用户 RLS 规则

{{ current_user_rls_rules() }} 宏返回应用于当前登录用户当前数据集的 RLS 规则数组。

如果您的 Superset 配置中启用了缓存，那么在计算缓存键时，Superset 将使用 RLS 规则列表。缓存键是一个唯一标识符，用于确定未来是否存在缓存命中，以便 Superset 可以检索缓存数据。

自定义 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 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 方法进行自定义时间格式化（请参阅文档了解有效的格式代码）。定义此项时，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 将使用来自上下文的当前数据集（例如，在图表构建器中使用此宏时，默认情况下会在支持图表的dataSet中搜索 macro_key）。该参数可以在 SQL Lab 中使用，或者从另一个数据集获取指标时使用。

可用过滤器

Superset 支持 Jinja2 模板包中的内置过滤器。还实现了自定义过滤器：

Where In 将列表解析为 SQL 兼容的语句。这对于返回数组的宏（例如 filter_values 宏）很有用。

Dashboard filter with "First", "Second" and "Third" options selected
{{ filter_values('column') }} => ["First", "Second", "Third"]
{{ filter_values('column')|where_in }} => ('First', 'Second', 'Third')

默认情况下，如果值为 null，此过滤器将返回 ()（作为字符串）。default_to_none 参数可以设置为 True 以在此情况下返回 null。

Dashboard filter without any value applied
{{ filter_values('column') }} => ()
{{ filter_values('column')|where_in(default_to_none=True) }} => None

转为日期时间

将字符串加载为 datetime 对象。这在执行日期操作时很有用。例如：

{% set from_expr = get_time_filter("dttm", strftime="%Y-%m-%d").from_expr %}
{% set to_expr = get_time_filter("dttm", strftime="%Y-%m-%d").to_expr %}
{% if (to_expr|to_datetime(format="%Y-%m-%d") - from_expr|to_datetime(format="%Y-%m-%d")).days > 100 %}
   do something
{% else %}
   do something else
{% endif %}