2022-12-30

Jinjaとマクロ

Data Engineering

dbt

はじめに

dbtでは、Jinjaやマクロを利用して柔軟なSQLのコードを記述することができます。この記事ではJinjaとマクロの記法や使い方を説明します。

Jinja

Jinjaとはテンプレートエンジンの一種で、SQL以外にもHTMLやCSSなど様々なテキストファイルの記述に利用できます。Jinjaはテンプレートエンジンなので最終的には文字列を生成します。

Jinjaでは以下の3種類の記法があります。

{% ... %}: 制御構文
forやifなどのように処理の流れを制御します。
{{ ... }}: 式
式の結果を文字列として出力します。
{# ... #}: コメント
人がプログラムを読みやすくなるようにコメントを書くことができます。最終的なSQLに出力されません。

if 構文

Jinjaでは以下のようにif構文を記述します。

{% if kenny.sick %}
    Kenny is sick.
{% elif kenny.dead %}
    You killed Kenny!  You bastard!!!
{% else %}
    Kenny looks okay --- so far
{% endif %}

if構文を1行で書く場合は以下のようになります。

{{ env if env is defined else 'dev' }}

for 構文

Jinjaでは以下のようにfor構文を記述します。

{% for col in columuns %}
  {{ col }} ,
{% endfor %}

{% for key, val in dict %}
  {{ key }}, {{ val }}
{% endfor %}

for構文中では特殊な変数を使うことができます。

変数	説明
loop.index	The current iteration of the loop. (1 indexed)
loop.index0	The current iteration of the loop. (0 indexed)
loop.revindex	The number of iterations from the end of the loop (1 indexed)
loop.revindex0	The number of iterations from the end of the loop (0 indexed)
loop.first	True if first iteration.
loop.last	True if last iteration.
loop.length	The number of items in the sequence.
loop.cycle	A helper function to cycle between a list of sequences. See the explanation below.
loop.depth	Indicates how deep in a recursive loop the rendering currently is. Starts at level 1
loop.depth0	Indicates how deep in a recursive loop the rendering currently is. Starts at level 0
loop.previtem	The item from the previous iteration of the loop. Undefined during the first iteration.
loop.nextitem	The item from the following iteration of the loop. Undefined during the last iteration.
loop.changed(*val)	True if previously called with a different value (or not called at all).

dbt 独自の Jinja 関数

dbtには独自のJinja関数が定義されています。よく使われるconfig()やref()、source()の他に以下の関数があります。

dbt Jinja functions
adapter	execute	run_query
as_bool	flags	run_started_at
as_native	fromjson	schema
as_number	fromyaml	schemas
as_text	graph	selected_resources
builtins	invocation_id	set
config	log	source
cross-database macros	model	statement blocks
dbt_project.yml Context	modules	target
dbt_version	on-run-end Context	this
debug	print	tojson
dispatch	profiles.yml Context	toyaml
doc	project_name	var
env_var	ref	zip
exceptions	return

Jinja 記法例

以下のような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

上記のSQLはJinjaのforループや変数を用いて以下のように記述することができます。

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

forループの最後にカンマを入れないようにするには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

マクロ

マクロは再利用可能なコードを作成する機能です。dbt_project.ymlのmacro-pathsで指定したディレクトリ（デフォルトではmacros）にSQLファイルを配置し、その中にマクロを記述します。

Jinjaとマクロ

はじめに

Jinja

if 構文

for 構文

dbt 独自の Jinja 関数

Jinja 記法例

マクロ

参考

dbtのDAG

dbtのテスト

Ryusei Kakujo