この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
大阪オフィスの玉井です。
dbtにはPackageというものがあります。プログラミングでいうライブラリみたいなものです。例えば、直接SQLで書こうとすると面倒な処理を、関数一発でできるようにするもの等があります。
今回は、一番メジャーなPackageであるdbt_utils
を使ってみました。
Packageのインストール方法
packageは、dbtプロジェクト毎に入れる形となります。ですので、プロジェクト単位で準備する必要があります。
packages.ymlを用意する
まず、プロジェクトにpackages.yml
を作成します。ここにインストールしたいPackageを記載していきます。作成場所の決まりとして、dbt_project.yml
と同じ階層に作る必要があります。
インストールしたいPackageを記述する
「どんなPackageがあるのか?」ですが、下記にまとめられています(dbt公式)。このサイトから、欲しいPackageを選びます。
今回インストールするのは、dbt_utils
です。データモデルを開発する上で、役に立つマクロやテスト等が沢山入っています。
上記もそうですが、Package毎のページにInstallationという見出しがあり、そこに従えば、Packageをインストールすることができます。dbt_utils
の場合は、packages.yml
に下記のように記述します。
packages.yml
packages:
- package: fishtown-analytics/dbt_utils
version: 0.6.4
Packageを実際にインストールする
packages.yml
に入れたいPackageを記述し終えたら、実際にインストールします。インストールするには、dbt deps
コマンドを実行します。
$ dbt deps
Running with dbt=0.18.1
Installing fishtown-analytics/dbt_utils@0.6.4
Installed from version 0.6.4
正常にインストールが完了すれば、プロジェクト下にPackage一式がインストールされます。
いくつか試してみた
dbt_utils
は非常に多くのマクロ等が組み込まれています。それぞれの使い方は、上記に載せている公式ドキュメントに一通り載っていますが、自分の環境でいくつか実際に使ってみました。
検証環境
- macOS 10.15.7 Catalina
- dbt CLI 0.19.0
- Google BigQuery
- (ちょっとだけ)Snowflake
datediff
日付の減算ができます。
「SQLでもすぐできるやん」ってなりそうですが、このマクロを使うことで、DBやDWHが変わっても、方言の差をdbt側が吸収してくれるようになります。また、開発者がいちいち「このDWHのdatediffってどう書くんだっけ…」となり、リファレンスをいちいち調べる必要も無くなります。
例として、以下のデータモデルを生成します。今年の元旦〜2月8日までの日数を算出します。
trial_1.sql
select
{{ dbt_utils.datediff("'2021-01-01'", "'2021-02-8'", 'day') }} as date_diff
from ...
BigQuery
生成されたデータモデルは下記の通り(テーブルの件数分、計算が出力されています)。
実行されたクエリは下記の通り。ちゃんとBigQueryの日付クエリが使われています。
Snowflake
今度は同じデータモデルを、Snowflakeで実行します。
生成されたデータモデルは下記の通り。
実行されたクエリは下記の通り。同じ内容のデータモデルでも、ちゃんとSnowflakeの日付計算クエリが使われていますね。
date_spine
連続した日付データを、指定した単位(日、月など)で生成できます。
例えば、下記のデータモデルは、日単位のデータを、2020年1月1日〜2021年1月1日の期間分、生成します。
trial_2.sql
{{ dbt_utils.date_spine(
datepart="day",
start_date="cast('2020-01-01' as date)",
end_date="cast('2021-01-01' as date)"
)
}}
order by date_day asc
生成されたデータモデルは下記の通り。指定した期間分、日付単位でレコードが生成されています(DATETIME
型)。
実際に実行されたクエリは下記の通り。
…長いので、下記にペーストします(多少整形しています)。複雑なクエリが流れていることがわかります。
CREATE OR REPLACE TABLE `tamai-rei`.`jaffle`.`trial_2`
OPTIONS() AS(
WITH rawdata AS(
WITH p AS(
SELECT
0 AS generated_number
UNION ALL
SELECT
1
),
unioned AS(
SELECT
p0.generated_number * pow(2, 0) + p1.generated_number * pow(2, 1) + p2.generated_number * pow(2, 2) + p3.generated_number * pow(2, 3) + p4.generated_number * pow(2, 4) + p5.generated_number * pow(2, 5) + p6.generated_number * pow(2, 6) + p7.generated_number * pow(2, 7) + p8.generated_number * pow(2, 8) + 1 AS generated_number
FROM
p AS p0 CROSS
JOIN p AS p1 CROSS
JOIN p AS p2 CROSS
JOIN p AS p3 CROSS
JOIN p AS p4 CROSS
JOIN p AS p5 CROSS
JOIN p AS p6 CROSS
JOIN p AS p7 CROSS
JOIN p AS p8
)
SELECT
*
FROM
unioned
WHERE
generated_number <= 366
ORDER BY
generated_number
),
all_periods AS(
SELECT
(datetime_add(cast(cast('2020-01-01' AS date) AS datetime), interval row_number() over(ORDER BY 1) - 1 day)) AS date_day
FROM
rawdata
),
filtered AS(
SELECT
*
FROM
all_periods
WHERE
date_day <= cast('2021-01-01' AS date)
)
SELECT
*
FROM
filtered
ORDER BY
date_day ASC
)
pivot
BIツール用のデータマートを作っている時などで、頻繁にやる処理の一つが「ピボット」ではないでしょうか。このマクロを利用すれば、ピボット処理も簡単にできます。
例として、こちらの注文テーブルをピボットします。このデータを元にして、STATUS
毎の件数を日付別に集計するという要件を想定します。
実行するデータモデルは下記の通り。dbt_utils.pivot
の中で、もう一つ、dbt_utils.get_column_values
というマクロが使われています。このマクロは、特定のカラムのユニーク値を全部取得できます。そして、そのユニーク値を使って(横に展開する)、ピボット処理を実現しています。
trial_3.sql
select
order_date,
{{ dbt_utils.pivot(
'status',
dbt_utils.get_column_values(source('jaffle','raw_order') , 'status')
) }}
from {{ source('jaffle','raw_order') }}
group by order_date
上記を実行して生成されたデータモデルは下記の通り。status
の値ごとにカラムが横展開されています。
実際に実行されたクエリは下記の通りです。
CREATE OR REPLACE TABLE `tamai-rei`.`jaffle`.`trial_3`
OPTIONS()
AS(
SELECT
order_date,
SUM(
CASE
WHEN status = 'completed' THEN 1
ELSE 0
END
) AS `completed`,
SUM(
CASE
WHEN status = 'placed' THEN 1
ELSE 0
END
) AS `placed`,
SUM(
CASE
WHEN status = 'shipped' THEN 1
ELSE 0
END
) AS `shipped`,
SUM(
CASE
WHEN status = 'returned' THEN 1
ELSE 0
END
) AS `returned`,
SUM(
CASE
WHEN status = 'return_pending' THEN 1
ELSE 0
END
) AS `return_pending`
FROM
`tamai-rei`.`jaffle`.`raw_order`
GROUP BY
order_date
)
頑張れば書けないことはないクエリですが、(手でピボットするクエリを実装した場合)ピボットするカラムのユニーク値が増えるほどCASE文が増えるため、データモデルのクエリ自体の可読性は酷い有様になるでしょう。また、ユニーク値が増えるたびに、そのデータモデル(のクエリ)のメンテナンスが必要になるため、運用も面倒になります。
ちなみにunpivot
もあります。興味のある方は是非試してみてください。
equal_rowcount
「dbt_utils」は、マクロだけでなく、テストも新しいものが追加されます。
equal_rowcount
は、2つのデータモデルの件数が一致するかどうかをテストします。
例えば、dim_customers
というデータモデルがあったとします。これは、顧客データに、いくつか分析するためのデータを付与したモデルです。ですので、元の顧客データ(rawデータ)と件数が一致していないとおかしい(モデルを生成する過程のどこかで処理を誤っている)、ということになります。それを、このテストでチェックしてみます。
テストは下記のように記述します。
stg_jafffle_shop.yml
- name: dim_customers
tests:
- dbt_utils.equal_rowcount:
compare_model: source('jaffle','raw_customer')
テストを実行してみます。合格しました。
$ dbt test --models dim_customers
Running with dbt=0.19.0
Found 8 models, 12 tests, 0 snapshots, 1 analysis, 327 macros, 0 operations, 1 seed file, 2 sources, 0 exposures
13:01:56 | Concurrency: 4 threads (target='learn')
13:01:56 |
13:01:56 | 1 of 1 START test dbt_utils_equal_rowcount_dim_customers_source_jaffle_raw_customer_ [RUN]
13:01:59 | 1 of 1 PASS dbt_utils_equal_rowcount_dim_customers_source_jaffle_raw_customer_ [PASS in 3.05s]
13:01:59 |
13:01:59 | Finished running 1 test in 4.28s.
Completed successfully
Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1
実際に実行されたテストクエリはこちら。互いの総件数を出して引き算、結果が0件だったら合格…というロジックですね。
おわりに
今回試したマクロやテストはほんの一部ですので、是非他のやつも使ってみたいです。そして、Package自体も、これ以外にもたくさんあるので、それらも試してブログにしたいと思います。