🍣

【dbt Docs】 Building a dbt Project - Jinja & Macros

2022/03/15に公開

dbt

tech

Jinja & Macros

Jinja Template Designer Documentation (external link)
dbt Jinja context
Macro properties

Overview（概要）

dbtでは、SQLをテンプレート言語であるJinjaと組み合わせることができます。
Jinjaを使うことで、IFやForでの繰り返しなどプログラミング的にできるようになります。

SQLで制御構造（ifやforループなど）を使用する
実稼働デプロイメント用のdbtプロジェクトで環境変数を使用する
現在のターゲットに基づいてプロジェクトの構築方法を変更します。
あるクエリの結果を操作して、別のクエリを生成します。
- 支払い方法ごとに小計列を作成するために、支払い方法のリストを返します（ピボット）
- 2つのリレーションの列のリストを返し、同じ順序で列を選択して、それらを結合しやすくします。
SQLの抽象的なスニペットを再利用可能なマクロに変換します

{{ ref() }}関数もJinjaでの記法です。
Jinjaは、ｄｂｔのほぼ全てのSQLで使うことができる。

Jinjaのチュートリアルはこちら

Getting started

Jinja

Jinjaを活用するdbtモデルの例を次に示します。

/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 app_data.payments
group by 1

このクエリは、次のようにコンパイルされる

/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 app_data.payments
group by 1

式（expression) {{ ... }} : 文字列を出力するときに使う
ステートメント　　{% ... %}　　: 制御フローに使用。forループやif分、マクロの定義
コメント　{# ... #}: コメント（コンパイルされない）

dbtモデルで使用する場合、Jinjaは有効なクエリにコンパイルする必要があります。JinjaがコンパイルするSQLを確認するには：

dbt Cloud : 「Compile」ボタンをクリックして、「Compiled　SQL」を確認
dbt CLI : dbt compileを実行。target/compiled/[project_name}/にSQLが出力される。

Macros

Jinjaのマクロは、何度も再利用できます。プログラミングでの「関数」に似てます。複数のモデルの間でコードを繰り返す場合に非常に役に立ちます。マクロは、macros/ディレクトリに配置する.sqlファイルで定義されます。

macros/cents_to_dollars.sql

{% macro cents_to_dollars(column_name, precision=2) %}
    ({{ column_name }} / 100)::numeric(16, {{ precision }})
{% endmacro %}

使用方法は

mpdels/stg_payments.sql

select
  id as payment_id,
  {{ cents_to_dollars('amount') }} as amount_usd,
  ...
from app_data.payments

これは、コンパイルすると

target/compiled/models/stg_payments.sql

select
  id as payment_id,
  (amount / 100)::numeric(16, 2) as amount_usd,
  ...
from app_data.payments

Using a macro from a package

パッケージとして公開されているものもある。その中でもっとも人気のあるパッケージが dbt-utils

マクロの前にパッケージ名をつけることで使用できる。使用例は

select
  field_1,
  field_2,
  field_3,
  field_4,
  field_5,
  count(*)
from my_table
{{ dbt_utils.group_by(5) }}

FAQs

Jinjaのどの部分がdbt固有ですか？

Jinja関数リファレンスに記載。
Jinjaを作成するとき、またはマクロを作成するときに、どのドキュメントを使用する必要がありますか？
- Jinjaのテンプレートデザイナードキュメント
- Jinja関数リファレンス
- Agate's table docs:クエリの結果を操作している場合、dbtはそれをAgateテーブルとして返します。これは、テーブルで呼び出すメソッドが、JinjaやdbtではなくAgateライブラリに属していることを意味します。 (よくわからん・・・）
Jinjaで列名を引用する必要があるのはなぜですか？

文字列 'amount'をマクロに渡すには、引用符を使用する必要があります。
コンパイルされたSQLには多くのスペースと新しい行がありますが、どうすればそれを取り除くことができますか？

これは「空白制御」として知られています。 ※ほんとよくわからん・・・
Jinjaをデバッグするにはどうすればよいですか？

target/compiled/<your_project>/とlogs/dbt.logをみれます。
また、log関数を使ってコマンドラインに出力することも可能

マクロを文書化するにはどうすればよいですか？

models/schema.yml

version: 2

macros:
  - name: cents_to_dollars
    description: A macro to convert cents to dollars
    arguments:
      - name: column_name
        type: string
        description: The name of the column you want to convert
      - name: precision
        type: integer
        description: Number of decimal places. Defaults to 2.

dbt出力に非常に多くのマクロが含まれているのはなぜですか？
```
$ dbt run
```
をすると、100以上のマクロが動いている、これはdbt独自のプロジェクトが動いていてそれがカウントされているから。

dbtonic Jinja

よく書かれたpythonがpythonicであるように、よく書かれたdbtコードはdbtonicです。

Favor readability over DRY-ness

Jinjaの力を学んだら、繰り返されるすべての行をマクロに抽象化するのが一般的です。Jinjaを使用すると、他のユーザーがモデルを解釈しにくくなる可能性があることに注意してください。SQLのいくつかの行を数か所で繰り返すことを意味する場合でも、JinjaとSQLを混在させるときは読みやすさを優先することをお勧めします。すべてのモデルがマクロである場合は、再評価する価値があるかもしれません。

Leverage package macros

マクロを自分で書くときは、dbt-utilsにないかまず見てください。

Set variables at the top of a model

{% set ... %}新しい変数を作成したり、既存の変数を更新したりするために使用できます。モデルをインラインでハードコーディングするのではなく、モデルの上部に変数を設定することをお勧めします。これは、読みやすさを向上させるため、他の多くのコーディング言語から借用した手法であり、変数を2か所で参照する必要がある場合に役立ちます。

-- 🙅 This works, but can be hard to maintain as your code grows
{% for payment_methods in ["bank_transfer", "credit_card", "gift_card"] %}
...
{% endfor %}


-- ✅ This is our preferred method of setting variables
{% set payment_methods = ["bank_transfer", "credit_card", "gift_card"] %}

{% for payment_method in payment_methods %}
...
{% endfor %}