使用 Jinja 改进 SQL 代码-未完纪

Back to guides

Jinja

Advanced

简介

本指南从 SQL 中一种常见模式出发，使用 Jinja 改进代码。

如果要跟着操作，请将链接中的 CSV 放入 dbt 项目的 seeds/ 目录，然后执行 dbt seed。 this CSV

逐步修改模型时，建议同时打开编译后的 SQL，检查 Jinja 实际生成了什么：

使用 dbt 界面：点击编译按钮，在右侧面板查看编译后的 SQL。
使用 dbt v1：在命令行运行 dbt compile，然后打开 target/compiled/{project name}/ 下的编译 SQL 文件。可以在代码编辑器中分屏，同时查看两个文件。

先编写不使用 Jinja 的 SQL

假设数据模型中一个 order 可以有多个 payments。每笔支付的 payment_method 可以是 bank_transfer、credit_card 或 gift_card，因此每个订单可能使用多种 payment_methods。

从分析角度看，需要知道每个订单通过各种 payment_method 分别支付了多少。在 dbt 项目中创建名为 order_payment_method_amounts 的模型，使用以下 SQL：

models/order_payment_method_amounts.sql

select
order_id,
sum(case when payment_method = 'bank_transfer' then amount end) as bank_transfer_amount,
sum(case when payment_method = 'credit_card' then amount end) as credit_card_amount,
sum(case when payment_method = 'gift_card' then amount end) as gift_card_amount,
sum(amount) as total_amount
from {{ ref('raw_payments') }}
group by 1

Report incorrect code

每种支付方式的金额计算 SQL 存在重复，维护困难，原因包括：

逻辑或字段名发生变化时，需要修改三个位置。
这些代码通常通过复制粘贴生成，容易出错。
其他分析人员审核代码时，往往只会快速扫过重复代码，因此更难发现错误。

接下来使用 Jinja 整理代码，让它更加符合 DRY（Don’t Repeat Yourself，不要重复自己）原则。

用 for 循环替换模型中重复的 SQL

这里可以用 for 循环替换重复代码。下面的代码会编译为同一条查询，但更容易维护。

/models/order_payment_method_amounts.sql

select
order_id,
{% for payment_method in ["bank_transfer", "credit_card", "gift_card"] %}
sum(case when payment_method = '{{payment_method}}' then amount end) as {{payment_method}}_amount,
{% endfor %}
sum(amount) as total_amount
from {{ ref('raw_payments') }}
group by 1

Report incorrect code

在模型顶部设置变量

建议在模型顶部定义变量，改善可读性，并在需要时于多个位置引用同一列表。这种做法借鉴了其他编程语言。

/models/order_payment_method_amounts.sql

{% set payment_methods = ["bank_transfer", "credit_card", "gift_card"] %}

select
order_id,
{% for payment_method in payment_methods %}
sum(case when payment_method = '{{payment_method}}' then amount end) as {{payment_method}}_amount,
{% endfor %}
sum(amount) as total_amount
from {{ ref('raw_payments') }}
group by 1

Report incorrect code

用 loop.last 避免尾随逗号

上面的查询把最后一列放在 for 循环之外，但并非总能这样做。如果循环最后一次迭代生成的是最后一列，就必须确保末尾没有多余逗号。

通常可以结合 if 语句和 Jinja 变量 loop.last，避免加入多余逗号：

/models/order_payment_method_amounts.sql

{% set payment_methods = ["bank_transfer", "credit_card", "gift_card"] %}

select
order_id,
{% for payment_method in payment_methods %}
sum(case when payment_method = '{{payment_method}}' then amount end) as {{payment_method}}_amount
{% if not loop.last %},{% endif %}
{% endfor %}
from {{ ref('raw_payments') }}
group by 1

Report incorrect code

另一种写法是 {{ "," if not loop.last }}。

用空白控制整理编译结果

如果一直检查 target/compiled 目录中的代码，可能已经发现生成结果有很多空白：

target/compiled/jaffle_shop/order_payment_method_amounts.sql



select
order_id,

sum(case when payment_method = 'bank_transfer' then amount end) as bank_transfer_amount
,

sum(case when payment_method = 'credit_card' then amount end) as credit_card_amount
,

sum(case when payment_method = 'gift_card' then amount end) as gift_card_amount


from raw_jaffle_shop.payments
group by 1

Report incorrect code

可以用空白控制整理代码： whitespace control

models/order_payment_method_amounts.sql

{%- set payment_methods = ["bank_transfer", "credit_card", "gift_card"] -%}

select
order_id,
{%- for payment_method in payment_methods %}
sum(case when payment_method = '{{payment_method}}' then amount end) as {{payment_method}}_amount
{%- if not loop.last %},{% endif -%}
{% endfor %}
from {{ ref('raw_payments') }}
group by 1

Report incorrect code

正确使用空白控制往往需要反复尝试。建议优先保证模型源码的可读性，再将编译结果的美化作为额外完善。

用宏返回支付方式

目前支付方式列表硬编码在模型中。其他模型可能也需要这个列表。使用变量是一个不错的方案，但为了演示，本教程改用宏。 variable

Jinja 宏是可以多次调用的代码片段，类似于 Python 函数。当多个模型重复相同代码时，宏尤其有用。 Macros

这个宏只返回支付方式列表：

/macros/get_payment_methods.sql

{% macro get_payment_methods() %}
{{ return(["bank_transfer", "credit_card", "gift_card"]) }}
{% endmacro %}

Report incorrect code

这里需要注意：

宏通常接收参数，后面会看到例子。目前虽然没有参数，仍要保留通常放置参数的空括号，即 get_payment_methods()。
这里使用 return 函数返回列表；没有这个函数，宏会返回字符串。 return

有了支付方式宏，就可以这样更新模型：

models/order_payment_method_amounts.sql

{%- set payment_methods = get_payment_methods() -%}

select
order_id,
{%- for payment_method in payment_methods %}
sum(case when payment_method = '{{payment_method}}' then amount end) as {{payment_method}}_amount
{%- if not loop.last %},{% endif -%}
{% endfor %}
from {{ ref('raw_payments') }}
group by 1

Report incorrect code

调用宏时没有再加花括号，因为当前已经处于 Jinja 语句内部，无需重复加括号。

动态获取支付方式列表

到目前为止，可能的支付方式仍然是硬编码的。新增 payment_method 或重命名现有方式时，都必须更新列表。

不过，任何时候都可以运行下面的查询，了解实际支付中使用了哪些 payment_methods：

select distinct
payment_method
from {{ ref('raw_payments') }}
order by 1

Report incorrect code

statement 提供了运行查询并将结果返回 Jinja 上下文的方式。这样，payment_methods 列表就能根据数据库中的实际数据设置，而不是硬编码。 Statements

使用 statement 最简单的方式是调用 run_query 宏。第一个版本先用 log 函数将结果输出到命令行，检查数据库返回了什么。 run_query log

macros/get_payment_methods.sql

{% macro get_payment_methods() %}

{% set payment_methods_query %}
select distinct
payment_method
from {{ ref('raw_payments') }}
order by 1
{% endset %}

{% set results = run_query(payment_methods_query) %}

{{ log(results, info=True) }}

{{ return([]) }}

{% endmacro %}

Report incorrect code

命令行返回如下内容：

| column         | data_type |
| -------------- | --------- |
| payment_method | Text      |

Report incorrect code

这实际上是一张 Agate 表。要得到支付方式列表，还需要进一步转换。 Agate table

{% macro get_payment_methods() %}

{% set payment_methods_query %}
select distinct
payment_method
from {{ ref('raw_payments') }}
order by 1
{% endset %}

{% set results = run_query(payment_methods_query) %}

{% if execute %}
{# Return the first column #}
{% set results_list = results.columns[0].values() %}
{% else %}
{% set results_list = [] %}
{% endif %}

{{ return(results_list) }}

{% endmacro %}

Report incorrect code

这里有几个容易混淆的地方：

原文说明使用 execute 变量，使代码在 dbt 的解析阶段能够正常处理，否则会报错。具体来说，应在 execute 为真时访问查询结果；解析阶段 execute 为假，代码走回退分支，不能理解为解析阶段执行数据库查询。 execute
使用 Agate 方法将结果列转换为列表。

模型源码无需修改，因为已经通过宏获取支付方式列表。此后底层数据模型新增的 payment_methods 会自动被 dbt 模型处理。

编写模块化宏

如果项目其他地方也需要类似模式，可以将逻辑拆成两个宏：一个通用地返回某个关系中的一列，另一个使用正确参数调用前者，取得支付方式列表。

macros/get_payment_methods.sql

{% macro get_column_values(column_name, relation) %}

{% set relation_query %}
select distinct
{{ column_name }}
from {{ relation }}
order by 1
{% endset %}

{% set results = run_query(relation_query) %}

{% if execute %}
{# Return the first column #}
{% set results_list = results.columns[0].values() %}
{% else %}
{% set results_list = [] %}
{% endif %}

{{ return(results_list) }}

{% endmacro %}


{% macro get_payment_methods() %}

{{ return(get_column_values('payment_method', ref('raw_payments'))) }}

{% endmacro %}

Report incorrect code

使用软件包中的宏

宏让分析人员能够在 SQL 中应用软件工程原则。宏还可以跨项目共享，这使它更加强大。

dbt-utils 包已经提供了许多有用的宏。例如，可以用其中的 get_column_values 替代前面自己编写的同名宏，节省大量时间；当然，编写过程也帮助我们理解了原理。 dbt-utils package get_column_values

在项目中安装 dbt-utils 包，安装方法见链接，然后更新模型，改用包中的宏： dbt-utils here

models/order_payment_method_amounts.sql

{%- set payment_methods = dbt_utils.get_column_values(
    table=ref('raw_payments'),
    column='payment_method'
) -%}

select
order_id,
{%- for payment_method in payment_methods %}
sum(case when payment_method = '{{payment_method}}' then amount end) as {{payment_method}}_amount
{%- if not loop.last %},{% endif -%}
{% endfor %}
from {{ ref('raw_payments') }}
group by 1

Report incorrect code

此后可以删除前面步骤创建的宏。如果你觉得某个问题很可能已有他人解决，可以先检查 dbt-utils 中是否已经分享了相关代码。 dbt-utils

文章版权归作者所有，未经允许请勿转载。

THE END