はじめに
データアナリティクス事業本部のおざわです。前回のdbt Fundamentalsに続いて、個人的に興味をもった「Jinja, Macros, Packages」のコースを受講してみました。レベル感としてはIntermediateとなっていて、dbt Fundamentalsコースでやった内容を理解している前提のコースです。
コース概要
5章とアンケートで構成されている動画＋ハンズオンのコースです。動画は全部で２時間ほどで字幕付きです。流れとしては、各章のはじめにLearning Objectivesで重要ポイントの説明があり、そのあと動画、ハンズオンと続きます。今回はdbt Fundamentalsコースにあったような理解度テストはありませんでした。
作成するマクロは、以下のGithubリポジトリにあります。コースで扱うマクロがどんなものか見てみたい方はこちらをご確認ください。
各章の受講メモ
1. Welcome to dbt Learn, Jinja, Macros, and Packages
dbt Fundamentalsの内容は理解している前提とのことです。
2. Jinja
最初に、Jinjaがどのようなものか簡単な例と説明があります。JinjaはPython用のテンプレートエンジンであること、dbtでは主にSQLを効率的に書くために使用しますが、他にも環境によって振る舞いを変えたり、DWHの権限制御をしたりが可能と説明がありました。
JinjaはPythonの基本的なデータ型だけでなくリスト、辞書にも対応しています。
以下、Jinjaを使った簡単な例です。
{#
ここはコメントです
#}
{%- set max_count = 10 -%}
{% for i in range(max_count) %}
select {{ i }} as number {% if not loop.last %} union all {% endif %}
{% endfor %}{#と#}で囲むとコメントになります。- 制御文は
{%と%}で囲んで{% endXX %}で終了します。 {%-と-%}で囲むことで、コンパイルしたSQLに出てくる前後の空白・改行を取り除くことができます。前後どちらか一方を取り除くこともできます。
章の後半では、テーブルをピボットさせる例が出てきます。最初にSQLで実装する例を確認してから、Jinjaを使って書き直します。
SQL版
with payments as (
select * from {{ ref('stg_payments') }}
),
pivoted as (
select
order_id,
sum(case when payment_method = 'bank_transfer' then amount else 0 end) as bank_transfer_amount,
sum(case when payment_method = 'coupon' then amount else 0 end) as coupon_amount,
sum(case when payment_method = 'credit_card' then amount else 0 end) as credit_card_amount,
sum(case when payment_method = 'gift_card' then amount else 0 end) as gift_card_amount
from payments
where status = 'success'
group by 1
)
select * from pivotedマクロ版
{% set payment_methods = ['bank_tranfer', 'coupon', 'credit_card', 'gift_card'] %}
with payments as (
select * from {{ ref('stg_payments') }}
),
pivoted as (
select
order_id,
{% for method in payment_methods -%}
sum(case when payment_method = '{{ method }}' then amount else 0 end) as {{method}}_amount
{%- if not loop.last %},{% endif %}
{% endfor -%}
from payments
where status = 'success'
group by 1
)
select * from pivotedこの章で紹介された例だけを見ても、使い道がたくさんありそうです。
3. Macros
dbtのマクロは、実態としてはプロジェクトの macros フォルダにあるSQLです。マクロ自体は、前章で見たJinjaの機能になります。似たようなロジックを複数のSQLに書いている場合には、マクロとして書き直すことを検討します。
本章の例では、簡単な例としてセントで記録されている金額をドルに直すマクロを作ります。macrosフォルダのSQLファイルに対して、以下のように macro のあとにマクロの定義を書いてから endmacro で閉じます。
{% macro cents_to_dollars(col_cents, decimal_places=2) %}
round(1.0 * {{ col_cents }} / 100, {{ decimal_places }})
{% endmacro %}呼び出す際には、以下のようにします。
{% cents_to_dollars('payment_amount') %}続いてコースの冒頭で話があった「環境によって振る舞いを変える」例が示されていました。以下のようにDWHへの接続に関する情報が入っているtarget変数のnameを使います。dbt CLIであればprofiles.ymlのtarget、dbt CloudであればProjectのTarget Nameが入るので、ここを見て開発環境であればSQLの結果に3レコードだけ表示するとしています。
{% macro limit_data_in_dev(column_name, dev_days_of_data=3) %}
{% if target.name == 'dev' %}
where {{ column_name }} >= dateadd('day', - {{ dev_days_of_data }}, current_timestamp)
{% endif %}
{% endmacro %}このマクロを呼び出すようにすれば dbt run のときも有効なので、結果の表示も速くなりますね。
章の最後にアドバイスとして、マクロはDRY(Don't Repeat Yourself)なコードと読みやすさのトレードオフだよという話がありました。当然ながらSQLをベタ書きするほうがシンプルでわかりやすいです。マクロを使うとコードを汎用化できる一方、読みにくくて複雑になってしまうので、バランスを考えましょうねということでした。この辺はチームで考えておいたほうがよさそうです。
4. Packages
dbtのパッケージは、dbt hubで公開されているような、誰かが作ったモデルやマクロを自分のプロジェクトでも使用するのに使えます。インストール方法はプロジェクト直下の packages.yml に以下のようにパッケージ名を記載します
packages:
- package: fivetran/github
version: 0.1.1
- git: git@github.com:fishtown-analytics/dbt-utils.git
revision: master
- local: sub-project上のようにパッケージの指定方法にはいくつかあり、直接dbt hubを指定する方法、githubのリポジトリを指定したり、プロジェクト配下にある子プロジェクトを参照することも可能です。
パッケージのインストールには dbt deps を実行します。
5. Advanced Jinja + Macros
スキーマ権限やログなどを操作するハイレベルなマクロについて学びます。マクロの実行には dbt run-operation macro_name を実行し、引数はargsで指定します。
この章では以下の例が示されていました。
{% macro grant_select(schema=target.schema, role=target.role) %}
{% set sql %}
grant usage on schema {{ schema }} to role {{ role }};
grant select on all tables in schema {{ schema }} to role {{ role }};
grant select on all views in schema {{ schema }} to role {{ role }};
{% endset %}
{{ log('Granting select on all tables and views in schema ' ~ target.schema ~ ' to role ' ~ role, info=True) }}
{% do run_query(sql) %}
{{ log('Privileges granted', info=True) }}
{% endmacro %}上で使われているのは3章でも出てきたDWHへの接続情報が入ったtarget変数です。setとrun_queryを組み合わせて複数のクエリを実行できます。また、log関数でinfoにTrueを渡すとコマンドの実行結果でSummaryとDetailの両方にログを表示してくれます。
続いてクエリの結果を受け取って、それをJinja(マクロ)の中で使ってみるという例が出てきます。 このときにdbtの実行状態を知るJinjaのexecute変数について説明がありました。
dbt run や dbt compile が実行される際、2つのフェーズがあります。最初にプロジェクト内のファイルを読み込んでモデル、テストなどのノードなどからマニフェストを生成します。この段階ではSQLは実行されていないのでexecuteはFalseです。次のフェーズで各ノードがコンパイルされて実行する段階になるのでexecuteはTrueになります。
マクロ内でクエリの結果を使いたい時は、executeがTrueのときでないと結果が返っていないので気をつけましょうという話でした。
おわりに
以上、Jinja, Macro, Packagesを受講してみました。思ったとおり、マクロはかなり便利そうです。うまく運用すればプロジェクトを超えて資産になるコードが貯まっていきそうです。実際にチームで導入する際は、どういった基準や粒度でマクロ化するのか等ベストプラクティスも調べてみたくなりました。引き続き、dbtを触っていきたいと思います。
4
