SQL 模板

Jinja 模板

SQL Lab 和 Explore 在查询中支持 Jinja 模板。要启用模板处理，需要在 superset_config.py 中启用 ENABLE_TEMPLATE_PROCESSING功能标志。当启用模板处理时，可以在虚拟数据集中嵌入 Python 代码，以及在 Explore 的过滤器(filter)和指标(metric)控件的 Custom 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 配置中定义 JINJA_CONTEXT_ADDONS（superset_config.py）来重载默认的 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 中找到。

可用的宏(Macros)

在本节中，我们将介绍 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) }}

当前用户 Email

{{ current_user_email() }} 宏返回当前登录用户的 email 地址。

如果你在 Superset 配置中启用了缓存，那么默认情况下，email 地址值将被 Superset 用于计算缓存键。缓存键是一个唯一标识符，用于确定将来是否存在缓存命中，以及 Superset 是否可以检索缓存数据。

你可以在 Jinja 代码中添加以下参数来禁用在计算缓存键时包含 email 值：

{{ 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') }}'

你将 Superset 托管在 www.example.com 域下，并给在西班牙的同事发送以下 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

数据集

可以使用 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 将使用来自上下文的当前数据集（例如，在 Chart Builder 中使用此宏时，默认情况下将在为图表提供数据的数据集中搜索 macro_key）。该参数可以在 SQL Lab 中使用，或在从另一个数据集获取指标时使用。

SQL 模板

Jinja 模板​

可用的宏(Macros)​

Jinja 模板

可用的宏(Macros)