🤖
dbtのstagingモデルをちょっと自動生成する
この記事の内容はBigQueryを使用しているユーザーが対象となっています
はじめに
dbtのベストプラクティスでは、ソースと1対1で対応するstagingモデルを作成することが推奨されています。ソースの変更に伴い、後続のモデルが全てぶっ壊れるみたいな事故を防ぐためにも、stagingモデルを噛ませることは重要です。
stagingモデルは、ほとんどソースとしての位置づけで、ソースに簡単な修正や加工を行ったものです。逆に、ソースの状態をなるべく維持した方が良いので、不必要な修正や加工を行うことは避けるぺきです。
stagingで行う処理の一例として、
- テーブル名の修正
- カラム名の修正
- カラムのデータ型の修正
- カラムの構造（STRUCT）化
- JSONのパース
- 数値IDへのラベル付与
- タイムゾーン（UTC、JST）の統一
- 重複データの削除
- データの誤りの修正
のような処理を行うことが考えられます。
つまり、staging層で行うデータ加工は、一定のルールに則って行われるものが大半であり、ルールに則るということは、自動化しやすいタスクでもあります。
そこで、ある程度stagingテーブルのクエリを自動で生成できるようにしたい！というのが、本記事のモチベーションです。
良さそうなパッケージがあるよ
まず、良さそうなdbtパッケージがあるので、それの紹介をします。
codegenというパッケージで、ソースのテーブル名を選択して実行すると、stagingテーブルを生成するクエリをログで出力してくれるというものです。
例えば、
というように実行すると、
のように、テーブル内のカラムを読み取って、stagingテーブルの形としてログを出力してくれます。これをコピペしてstagingモデルを作成すれば、サクッとstagingモデルを作成することができます。1カラムずつコピペするよりは遥かにマシですね。
出力されるクエリを確認すると、
- with句でソースの読み込みを行って、
- 全てのカラムをselect句で出力する
という内容になっています。
モデルが自動で作成される訳ではないですが、クエリがログとして吐き出されるので、これをコピペするだけでモデルを作成することができます。
でも、他の加工も行いたい
個人的に、先ほど紹介したcodegenでは、2つ使いづらい点がありました。
- 複数のstagingモデル作成時に1回ずつコマンドを実行して、コピペしていく必要がある
- staging層で行う処理など、複雑なことはできない
1つ目について、
stagingモデルは、ソーステーブル1つずつに対して作成するものなので、作成時には数十個単位で作成しなければならないケースが多いと思います。
なので、1モデルずつコマンドを実行して、コードをコピペする運用だと少し手間がかかるなと思いました。
2つ目について、
codegenで生成されるSQLクエリは、全カラムをselect句で出力するだけのシンプルなものです。冒頭でも述べたように、staging層はある程度行う処理が固定化できるので、カラムのデータ型などに応じてルールベースで処理をかけたいと思いました。
例えば、実際にあった例だと、
- datetime型のカラムをtimestamp型に変換する処理
- timestam型を+9時間するUTC → JST変換の処理
のような処理は、全てのtimestampカラムに対して行いたいので、できれば自動化したいです。
そこで、BigQueryのを使って、似たようなことを実現してみました。
クエリの全体感
まず、最終的なクエリはこちらです。
また、これを実行した結果はこんな感じになります。
出力されるカラムは、以下の通りです。
- table_name：テーブルの名称
- query：クエリ全体（コピペして使う）
- file_name：stagingモデルの名称（コピペして使う）
ファイル名とクエリが、データセット内の全テーブルに対して一括で出力されるので、あとはコピペしてポチポチするだけでokです。
また、今回は、
- datetime型のカラムをtimestamp型に変換する処理
- timestam型を+9時間するUTC → JST変換の処理
を自動で行っています。データ型に基づいてルールベースで一括で行える処理は、自動化することが可能です。
それでは、クエリの解説を簡単にしていきます。
クエリの詳細
テーブル名・カラム名の取得
まず、stagingモデルを作成するにあたっては、データセット内に存在する、テーブル名と、そのテーブルのカラム名を取得する必要があります。これはBigQueryのを使えば、一瞬で実装することができます。
テーブル名・カラム名の一覧取得は、以下のクエリで出来ます。後ほど、データ型の修正を行うため、データ型も同時に出力しています。
データ型の修正
続いて、データ型の修正を行います。
データ型の修正は、ルール化できる場合のみ行うことができます。
今回は、DATETIME型とTIMESTAMP型が入り混じっている、というケースに対応するため、全てのDATETIME型をTIMESTAMP型にCASTするという処理をかけています。
case文で、データ型がDATETIMEのカラムに対して、cast関数を適用する（ための文字列を出力する）という処理をかけています。
カラム名を含む文字列の出力は、順番に結合していってもいいのですが、ここではformat関数を使っています。
TIMESTAMPのタイムゾーンを一括で変換
続いて、TIMESTAMPのタイムゾーンをUTC→JSTに一括で変換する処理をしています。
対象とするデータセットのTIMESTAMPのタイムゾーンが全てUTCであるような場合は多いと思いますが、staging層でJSTに一括変換しておくと、後続のモデルで使いやすくなります。
このような単純な処理は、ルールベースで自動化できるので、自動化してしまいましょう。
クエリの出力
最後に、変換したカラムをクエリとして出力します。
のwith句では、上記で行ってきた変換処理を行う文字列を、テーブルごとに集約し、カラムを縦に連結しています。このカラムを縦に連結したものを、後ほどselect句に出力します。
続いて、のwith句では、度々登場している関数を使って、これまで生成してきた文字列を1つにまとめています。関数は何でもできるので、自動化タスクの強い味方です。
ここでは、インデントの調整をしているため、クエリ上は可読性が悪くなっています。最終的な出力結果と見比べながらインデントの微調整を行ってください。
こののwith句を出力すれば、アウトプットが得られます。あとはコピペでポチポチしてください。
おわり
今回紹介する内容は以上になります。
stagingモデルをちょっとだけ自動化することが出来ました。
他にも、staging層でやっておいた方が良い処理は色々あると思うので、ルールベースで捌けるものは色々応用ができるのでは、と思っています。良い処理があれば教えてください。
それでは、最後まで読んでいただきありがとうございました。
END