在 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 螢幕截圖，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（Don't Repeat Yourself）

建立名為 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 建立電商資料模型