如何使用Jinja定义dbt宏

dbt宏在dbt框架内的工作方式与传统编程中的函数类似。它允许用户将特定的、通常是重复的SQL逻辑封装到可调用的命名单元中，就像在其他编程语言中用函数来避免重复代码一样；dbt宏定义特定业务的SQL逻辑，然后在dbt项目中需要的地方调用该宏函数实现代码重用。数据工程师利用宏在数据开发项目中维护更干净、更模块化的代码，因此，我们可以把dbt宏看作：为SQL提供强大的、类似函数的强大功能!

dbt 宏简介

dbt宏使用Jinja引擎，这是Python web框架中广泛采用的模板引擎。使用Jinja可以将占位符和逻辑直接嵌入到SQL代码中，使其具有动态性和模块化。在dbt宏中可以定义带有参数的SQL块，然后可以在整个dbt项目中使用不同的参数重用该SQL块。

为什么要使用Jinja宏?

• 可重用性: 一次定义复杂逻辑，然后跨多个模型使用它。
• 可维护性: 集中逻辑以简化更新并减少错误。
• 灵活性: 在宏中组合SQL和Python代码来处理复杂的数据转换。

简单宏示例

下面示例实现计算百分比逻辑，当分母为0直接返回0，反之，对两数相除的结果乘以100转为百分比值：

{% macro calculate_percentage(numerator, denominator) %}CASE WHEN {{ denominator }} = 0 THEN 0ELSE ({{ numerator }} / {{ denominator }} * 100)END
{% endmacro %}

在你的DBT模型中(例如，models/my_model.sql)，你现在可以调用这个宏:

SELECTorder_id,{{ calculate_percentage('total_revenue', 'total_cost') }} AS profit_margin
FROMorders

这个SQL查询使用calculate_percentage宏计算每个订单的利润率。

Jinja 引擎介绍

通过将dbt宏与Jinja模板引擎一起使用，SQL代码变得更加可重用、可维护和模块化。它让数据开发工作避免冗余、提升一致性、并简化开发过程。Jinja为宏提供了构建块，因此深入理解Jinja语法是编写高质量宏的关键。接下来，让我们通过创建dbt宏示例学习Jinja语法。

变量和占位符

• 变量

  语法： {% set var_name ... %}

在dbt宏中使用Jinja时，可以使用set将值赋给变量，从而可以在宏中引用或重用变量。在下面的示例中，定义了一个名为partners的变量，其中包含代理产品的三个伙伴的列表。在本文后面回顾for循环时，我们将利用这个变量。

示例：

{% set partners =  ['costco', 'sams_club', 'target'] %}

• 占位符

​ 语法：{{...}}

双花括号之间的任何内容都将被计算并替换为实际值。在dbt中，这种语法最常用于函数ref()和source()。在下面的示例中，引用了users关系模型。

select organization_id, min(created_at) as first_user_created_atfrom {{ ref('users') }}
group by organization_id;

ref(‘users’)是一个dbt函数，它被替换为dbt项目中users模型对应的关系：可能为视图、表或物化视图等。当上面的代码被编译时，生成的代码大致如下：

select organization_id, min(created_at) as first_user_created_atfrom dbt.users
group by organization_id;

注释

语法：{#...#}

{#和#}之间的任何内容都是注释，不会包含在最终的SQL中。通常只有在需要记录有关Jinja逻辑的特定内容时才需要这样做。应该优先描述dbt项目中宏逻辑、输入参数描述，而不是代码注释。

示例：

{# 这里内容仅在宏源文件中出现，编译后的内容不包括注释信息 #}

if 语句

语法：{% if... %} … {% endif %}

这种语法用于简单的条件(if语句)来添加/删除SQL行。例如，在下面的dbt宏示例中，只有在满足条件时才会将where语句添加到查询中。在这种情况下，条件是模型正在以增量方式运行。如果模型没有以增量方式运行，where语句将被排除在查询之外。

{% if is_incremental() %}where last_update_at > (select max(last_update_at) from {{ this }}){% endif %}

使用Jinja可以构建非常复杂的逻辑，但建议尽量保持简单易读。大多数使用dbt的人都是数据分析师，他们需要了解SQL查询是如何工作的，并且可能没有时间学习Jinja中所有复杂的逻辑运算符。

loop 循环

语法： {% for... %}

我们还可以使用Jinja for循环遍历列表或序列。这对dbt宏中非常有用，可以实现在SQL中很难处理的逻辑。在下面的dbt宏示例中，我们使用partners变量创建合作伙伴列表。对于每个合作伙伴，单独存储在各自的schema中，但是销售表的列完全相同。我们需要把这些表合并在一起，以得到一个完整的合作伙伴销售名单。

{% set partners =  ['costco', 'sams_club', 'target'] %}{% for partner in partners %}select *,'{{ partner }}' as partnerfrom {{ source(partner, 'sales') }}{# this adds union all to all selects except for the last one #}{% if not loop.last %} union all {% endif %}{% endfor %}

编译结果大致如下文：

select *, 'costco' as partner
from costco.sales union all select *, 'sams_club' as partner
from sams_club.sales union all select *, 'target' as partner
from target.sales 

我们看到在模型文件中直接使用for循环，使得代码量减少很多。如果还有其他表(如：客户、库存等)需要合并，只要稍微调整下上述代码即可实现重用。

宏示例实战

下面再通过两个示例加强对宏的认识，提升编写宏函数能力。

生成动态条件

Jinja是生成动态SQL的强大工具。下面是一个基于jinja的宏例子，它根据提供的条件生成一个WHERE子句:

{% macro generate_where_clause(column_name, value) %}{% if value is not none %}WHERE {{ column_name }} = '{{ value }}'{% else %}-- No condition{% endif %}
{% endmacro %}

在模型中使用宏：

SELECT*
FROM{{ ref('customer_data') }}
{{ generate_where_clause('customer_id', customer_id) }};

这种方法简化了SQL生成，并在查询中提供了更大的灵活性。

动态日期范围

假设您需要根据动态日期范围筛选记录，例如“最近7天”或“当前月份”。你可以创建一个宏来处理这个问题:

{% macro dynamic_date_filter(date_column, period) %}{% if period == 'last_7_days' %}{{ date_column }} BETWEEN CURRENT_DATE - INTERVAL '7 days' AND CURRENT_DATE{% elif period == 'current_month' %}{{ date_column }} BETWEEN DATE_TRUNC('month', CURRENT_DATE) AND CURRENT_DATE{% else %}1=1 -- No filter{% endif %}
{% endmacro %}

该宏接受日期列和周期作为参数，返回适当的SQL筛选器。下面代码展示如何在模型中使用宏：

SELECTuser_id,order_date,total_amount
FROMsales
WHERE{{ dynamic_date_filter('order_date', 'last_7_days') }}

该查询过滤销售表，只返回最近7天的记录。

宏文档

除了宏代码中的注释外，还应该在宏描述文件中添加宏及和输入参数的描述。就像在模型描述文件中对列进行描述文档一样，宏文档使用相同的语法。您可以在macros文件夹中创建macros.yml文件，以便有专门的地方描述宏文档。下面是一个示例宏描述文件，它定义了两个宏的文档:

version: 2macros:- name: cents_to_dollarsdescription: A macro to convert cents to dollarsarguments:- name: column_nametype: stringdescription: The name of the column you want to convert- name: precisiontype: integerdescription: Number of decimal places. Defaults to 2.- name: from_jsondescription: A macro to extract a nested column value out of a JSON column.arguments:- name: json_column_nametype: variantdescription: The name of the JSON column that contains the nested column.- name: nested_column_nametype: stringdescription: The name of the nested column to be extracted from the JSON column. 

注意事项：在编写宏之后需要在模型中使用它以确保它按预期工作。由于宏本质上是模板，因此需要从模型或另一个宏调用它们以查看最终的SQL输出。

最佳实践；

dbt宏的主要目标是使SQL代码更易于维护并减少冗余。随着对dbt和Jinja的使用经验不断积累，创建宏的过程将更顺畅自然，从而让你能够专注于复杂数据转换和模型业务实现。虽然Jinja宏功能强大，但以下最佳实践仍值得借鉴:

• 保持简单: 避免创建难以调试或过于复杂的宏。将复杂的逻辑分解为更小的、可重用的宏。
• 文档化宏: 给宏添加注释说明它们的用途和使用场景，通过文档描述参数、数据类型及意义。
• 测试宏: 在部署到生产环境之前，要对宏进行彻底测试，以确保它的行为符合预期。
• 版本控制: 在版本控制中跟踪对宏的更改；
• 重复利用Jinja内置的宏函数、知名第三方已有宏函数，后续文章会详细介绍。

总结

掌握 DBT Core 里的 Jinja 宏能够极大地提升数据工程师的工作效率。通过创建可重复使用的代码片段，能够对复杂的逻辑进行标准化处理，减少代码重复现象，同时维护干净、高效的数据模型。我们可以从尝试简单的宏着手，随着对语法越来越熟悉，逐步编写更为复杂的宏。

期待您的真诚反馈，更多内容请阅读数据分析工程专栏。