UNPIVOTをdbt_utilsで行う

1. はじめに

横持ちのデータを縦持ちに変換することは度々あることだと思います。今回はdbt_utilsを使用してささっと変換処理を行いたいと思います。

*注意 キャスト後のデータ型はvarhcarです

2. 概要

先述した通りdbt_utilsを使っていきます。横持ちを縦持ちに変換するためにdbt_utils内のUNPIVOTを使います。

• 横持ちと縦持ちのイメージ

• 横持ち

date	size	color	status
2017-01-01	S	red	complete
2017-03-01	S	red	processing

• 縦持ち

date	status	field_name	value
2017-01-01	complete	size	S
2017-01-01	complete	color	red
2017-03-01	processing	size	S
2017-03-01	processing	color	red

3. 手順

3-1. 必要なパッケージをインストール

1. dbtプロジェクト配下にpackages.ymlを作成します
2. 作成したpackages.ymlに下記を追加します

packages:
  - package: dbt-labs/dbt_utils
    version: 1.1.0

3-2. 使用するデータを作成

1. seeds 配下にpivot_sample.csvファイルを作成し、下記をコピぺします

year, code, uriage, genka, amount, unit, target
2022, ITEM-1, 200, 50  ,20, KG ,実績
2022, ITEM-2, 400, 100 ,30, KG ,予算

2. dbt seed -s pivot_sampleを実行し、サンプルデータを作成します
   作成したサンプルデータは下記になります。
   

3-3. models配下にunpivotを書く

1. unpivot_sample.sqlを作成し、sqlを書きます。(書く.. 1行でいいだと...)

{{ dbt_utils.unpivot(ref('pivot_sample'), exclude=['year_month', 'code', 'unit', 'target']) }}

• 参照するテーブルとunpivotから除外するカラムを各々選択します。

• そのほかのパラメータに関しては下記を参考にしてください。

Args:
relation: The Relation to unpivot.
cast_to: The data type to cast the unpivoted values to, default is varchar
exclude: A list of columns to exclude from the unpivot operation but keep in the resulting table.
remove: A list of columns to remove from the resulting table.
field_name: column name in the resulting table for field
value_name: column name in the resulting table for value

3-4. 実行と結果の確認

1. dbt runを実行します
2. 出力された結果を確認します
   

4. 感想

1行で横持ちのテーブルを縦持ちに変換できることにすごく、すごく感動しました。ほかのmacorsも触って必要な時に使えるようにしたいです。

5. 参考

• https://github.com/dbt-labs/dbt-utils/blob/main/macros/sql/unpivot.sql

6. 余談

普段Snowflakeを使っているので、Snowflakeでも同じようにUNPIVOTを行ってみます。
下記のコードがdbtで行った処理と同じものになります。

select
    *
from
    D_HARATO_DB.DBT.PIVOT_SAMPLE
    unpivot(value for field_name in (uriage, genka, amount))
;