在 dbt 中高级使用 Jinja

案例研究：使用 dbt 构建电商数据模型

Susan Sun

Freelance Data Scientist

Jinja 语句：循环

Jinja 的语句用 {% %} 包裹：

循环遍历项目

模板：
```
{% for ... %} ... {% endfor %}
```

示例：

{% for order_status in order_statuses %}
  SUM(
      CASE WHEN status = '{{ order_status }}' 
        THEN order_id 
      END)
{% endfor %}

Jinja 语句：循环

重复的 SQL：

SELECT 
    user_id,
    SUM(CASE WHEN status = 'Shipped' THEN 1 ELSE 0 END) 
        AS num_orders_Shipped,
    SUM(CASE WHEN status = 'Complete' THEN 1 ELSE 0 END) 
        AS num_orders_Complete,
    SUM(CASE WHEN status = 'Processing' THEN 1 ELSE 0 END) 
        AS num_orders_Processing,        
FROM {{ ref('stg_looker__orders') }}
GROUP BY 1

Jinja 语句：循环

{% set order_statuses = ['Shipped', 'Complete', 'Processing'] %}

SELECT 
    user_id,
    -- Jinja loop
    {% for order_status in order_statuses %}
        SUM(CASE WHEN status = '{{ order_status }}' THEN 1 ELSE 0 END) 
        -- Parametrized column name
        AS num_orders_{{ order_status }}
    {% endfor %}
FROM {{ ref('stg_looker__orders') }}
GROUP BY 1

Jinja 语句：循环

dbt compile 输出：

SELECT 
    user_id,
    SUM(CASE WHEN status = 'Shipped' THEN 1 ELSE 0 END) 
        AS num_orders_Shipped,
    SUM(CASE WHEN status = 'Complete' THEN 1 ELSE 0 END) 
        AS num_orders_Complete,
    SUM(CASE WHEN status = 'Processing' THEN 1 ELSE 0 END) 
        AS num_orders_Processing,        
FROM "dbt"."main"."stg_looker__orders"
GROUP BY 1

Jinja 语句：宏

Jinja 宏：

是用 Jinja 编写的函数
可复用
存放在 /macros 文件夹

COALESCE()：

返回第一个非空值
语法：COALESCE(value1, value2, ...)
例如：COALESCE(nickname, first_name)

宏函数示例：

IDE 的截图，编辑器中打开了 `coalesce_and_round.sql` 文件，显示其宏逻辑。

Jinja 语句：宏

Jinja 的语句用 {% %} 包裹：

可复用的宏函数：

模板：
```
{% macro ... %} ... {% endmacro %}
```

示例：

{% macro coalesce_and_round(column_name, decimal_places = 2) %}
      ROUND(COALESCE({{ column_name }}, 0), {{ decimal_places }})
{% endmacro %}

Jinja 语句：宏

重复的 SQL

ROUND(COALESCE(sales_amount, 0), 2) AS sales_amount,
ROUND(COALESCE(cost_of_goods_sold, 0), 2) AS cost_of_goods_sold,

DRY（不要重复自己）

创建名为 coalesce_and_round.sql 的宏
存放在 /macros

调用：

{{ coalesce_and_round('sales_amount', 2) }} AS sales_amount,
{{ coalesce_and_round('cost_of_goods_sold', 2) }} AS cost_of_goods_sold,

控制 Jinja 空白符

如编写：

{% set traffic_source_values = ['Adwords', 'Email', 'Facebook'] %}
{% set browser_values = ['Chrome', 'Firefox', 'Safari', 'IE', 'Other'] %}

SELECT 
    user_id,
    COUNT(DISTINCT session_id) AS num_web_sessions,
...

控制 Jinja 空白符

编译结果：




SELECT 
    user_id,
    COUNT(DISTINCT session_id) AS num_web_sessions,
...

控制 Jinja 空白符

{%- ... %} 去除前面的空白。
{% ... -%} 去除后面的空白。
{%- ... -%} 两端都去除。
示例：

{%- set traffic_source_values = ['Adwords', 'Email', 'Facebook'] -%}
{%- set browser_values = ['Chrome', 'Firefox', 'Safari', 'IE', 'Other'] -%}

SELECT 
    user_id,
    COUNT(DISTINCT session_id) AS num_web_sessions,
...

开始练习吧！

案例研究：使用 dbt 构建电商数据模型