前回までの記事
はじめに
これまでの記事でdbt環境を整備するところは説明しきれたかと思いますので、今回の記事では実際にTreasureData(以降td)で実行しているworkflowをdbt側に移行した時の話を書ければと思います。
前回までは実装説明メインでしたが、今回は体験記的な内容になります。
移行フロー
下記の流れでdbtへの移行作業を実施しました。これからそれぞれに関してポイントを説明できればと思います。
- 移行するtd workflowの選定
- クエリをdbt様式に書き換え
- テーブル比較
- dbtへの切替
1. 移行するtd workflowの選定
まずはじめにどのtd workflowをdbtに移行するかを決める必要があります。
前提としてdbtはELTのTを行うためのツールなため、ELに相当する部分に関するtd workflowは移行対象外になります(そもそもクエリじゃないと思うのでdbtに移行できなくて当然ですが)。
また、一回で全てをdbt側に移行する必要はないとも思っていますが、あるクエリAをdbtに移行するのであればその上流と下流のクエリも一緒にdbtに移行することがポイントとして挙げられるかと思います。
なぜならば、クエリAを参照している(つまり下流)クエリBをdbtに移行しないで実行順番を保証しようとすると、td側にクエリAを残す必要がでてきて、dbtとtdでクエリAの2重管理が発生するためよくありません。
もちろん実行順番を保証しない場合はクエリBだけtd側に残せばいいという考えもあるかと思いますが、クエリの管理観点や更新失敗が発生した際のハンドリングなども含めて考えるとdbt側に寄せたほうがベターかと思います。
他の観点として、移行作業の裏側では別の人がtd側で新規クエリの追加や既存クエリの変更などを行うため、移行作業は一気呵成に行う必要があることも考慮に入れる必要があります。
私はtd workflowの90%くらいを一度にdbtに移行したのですが、諸々含め1ヶ月くらいかかったため、毎週td側での変更を(git管理していないため)定例で確認して追従する作業を行うことになりました(td側での作業と一緒にdbt側も書き換えてくれというお願いをして嫌がられないならその限りではない)。
2. クエリをdbt様式に書き換え
これはタイトル通りです。
ポイントとしては、このタイミングではテーブル名をrefを使って書くなどの最低限の変更にとどめておくべきことだとおもいます。
そうでないとtd側との比較をするレビューや後続の実行確認が大変になります。
何度も出てくる共通処理を上流の一箇所にまとめたり、クエリを高速化したりなどは移行が完了した後でゆっくりやることを強くお勧めします(クエリの実行時間がかかりすぎる場合などはその限りではない)。
クエリのフォーマットもレビューが終わってからかけたほうがいいです。
他の細かいポイントとしては、dbtは別データセットであっても同じモデル(テーブル)名を許容しない作りになっているので、同一のモデル名がある場合は工夫が必要なことや、
テーブルAの生成クエリ内でテーブルBを参照しているかつテーブルBの生成クエリ内でテーブルAを参照しているなど閉ループが発生するとエラーになるため、クエリ側で閉ループが発生しないように工夫(ex. 一つのクエリにまとめるなど)する必要があります。
3. テーブル比較
これはtd workflowで更新したテーブルとdbtで更新したテーブルを比較確認するという内容になります。
前段でtdのクエリとdbtのクエリの内容が一致しているのであれば、結果的に生成されるテーブルが一致しないわけないのでは？と思われるかと思いますが、最終的に生成されるテーブルが変わっているとダッシュボードにも影響が出るので確認してから移行したいとお願いされたので対応しております(依頼がなければやらなくていいと思います)。
具体やったこととしては
select time以外カラム from 本番テーブル
except
select time以外カラム from 開発テーブル
select time以外カラム from 開発テーブル
except
select time以外カラム from 本番テーブル
上記の二つのクエリを実行して、両方ともレコードが返ってこなければ完全一致しているとみなしました(timeカラムはクエリ内で定義していない場合テーブル生成時刻になるため除いています)。
とはいえ、exceptはそれなりに重い計算なので、大きいテーブルでメモリリミットに引っかかるテーブルに関しては前日、先週、先月などテーブルの粒度に合わせて絞り込みをした上で比較しました。
比較結果に関しての感想から先に書いてしまうと「想定外にテーブルが一致しなくて調査にとても時間がかかりました」です。
ここから具体あった一致しない要因について説明していこうと思います。
実行タイミングの違いによる差分
実行タイミングが異なると、リアルタイムでデータが取り込まれるテーブルなどを参照してるクエリ関しては一致しないですし、now()などを使っていると処理内容によっては差分が生じます。
特に、td_scheduled_timeはdbtで実行する場合は実行時点のunixtimeになりますが、td workflowではスケジュール設定されたunixtimeになるので注意が必要です。
同時タイミングのrow_numberの順番が保証されない
下記のようなrow_number()を使った書き方をしばしばするかと思いますが、
row_number() over (partition by user_id order by datetime) as rn
例えば、あるuser_idにおいて同じdatetimeに複数のレコードが出ていた場合rnは実行のたびに結果が変わります。
後続処理でそのrnを使ってjoinなどをすると実行のたびにテーブル自体が変わってきます。
この問題はシンプルにorder byで設定すべきカラムが足りてないという話なのでそこさえ修正すれば結果は一意になります。
また、似た話としてmaxby(minby)でもmax(min)に相当するレコードが複数存在すると出力される結果がランダムになり、実行のたびに結果が変わるので注意が必要です。
dbtで実行すると有効数字が変わる
文字通りではあるのですが、dbt(つまりpresto api)で実行した場合とtdで実行した場合で有効数字が変わることがあるらしいです(詳細調査まではしていません)。
具体で言うと、sum(watch_min) / 60.0 as watch_hourでテーブルの数字が異なっていました。
そもそもtd workflow側の実行順番がおかしい
dbtは必ず上流から下流へ実行してくれるわけですが、td側で上流更新していないのに下流を先に更新していると結果が不一致になります。
蛇足ですが、こういう設定ミスを見ると、実行順番を考える必要なくクエリを書くだけでいいのはdbtの大きなメリットだと感じます。
4. dbtへの切替
諸々確認が終わったら、いよいよ実際にtd workflowからdbt実行への切り替えを行いました。
具体作業としては下記になります。
- 移行対象のtd workflowの当日分の処理が終わっていることを確認
- 差分更新テーブルのバックアップ
- masterにブランチをマージ
- dbtの更新の実行
- 移行対象のtd workflowの停止
dbt更新で問題があった時に備えて差分更新のテーブルはバックアップしておき、問題が発生したらバックアップに差し替えた上、masterをマージ前に戻すだけで済む運用にしていました。
また、しょうもないところですが、dbt移行日以降の数日はダッシュボードに影響が出る可能性があるよということを周知した上で、影響があっても問題ない時期を調整したりもしました。
まとめ
今回の記事では、dbt移行の具体作業をポイント込みでまとめました。
そして、今回の記事をもってTreasureData x dbtは一旦完結になります。
ネクストステップとして、
- testを作り込む
- TreasureDataから別のDWHへの移行を見据えてprestoやtd特有の関数をmacroに置き換える
など色々あるので、ぜひここからより良いものを作っていただけますと幸いです。