[dbt] データモデルの開発が便利になる「Package」を使ってみた(dbt_utils)

パケ写詐欺には気をつけよう
2021.02.10

大阪オフィスの玉井です。

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自体も、これ以外にもたくさんあるので、それらも試してブログにしたいと思います。