BigQueryの白魔術師がdbtという最強クラスの触媒を用いていい感じに世界を救う方法
本記事はeureka Advent Calendar 2023 8日目の記事です。
と同時にBigQuery Advent Calendar 2023 8日目、dbt Advent Calendar 2023 8日目にも寄稿させていただいております。まさかのAdvent Calendarの掛け持ち、かつ同日リリースという暴挙を行っています。筆を取る時間が確保できなかったのでお許しください。
何者？
あらきと申します。エウレカでデータに関する業務を担当させていただいております。
少し前のeureka Advent Calendarでredashの断捨離を実施したので、方法を共有しますという記事を書いた人です。
私事ですが今年を振り返ると会社としても新しい環境になったこともあり、よりData EngineeringやAnalytics Engineeringに関するテーマの比重が多い一年でした。Data Engineerとして、あるいはAnalytics Engineerの職責として、日々Pairsのデータと向き合っております。
まずはじめに
さて、みなさまはBigQuery StandardSQLの黒魔術というのをご存知でしょうか？
今から遡ること5年前のeureka Advent Calendar 2018で公開したBigqueryStandardSQLの黒魔術ってなに！？記してみました！という記事です。
今回は上記の続編を意識した上で最近の弊社の取り組みをまとめたTipsを紹介します。
と、その前に
弊社のデータ業務についての近況について簡単に紹介しておきます。
弊社は2021年にデータ環境を一新しました。
データ環境を刷新した恩恵は十二分に受けておりまして、弊社データ人材の生産性も格段に向上し、様々な取り組みやビジネスサイドの要求に容易に対応できるようになった一方で、新データ環境リリース移行、データ利活用の取り組みが指数関数的に増加しています。
そのため、日常座臥にデータにただ触れているだけでは太刀打ちできないケースが徐々に増えつつあります。
黒魔術を使った攻めのデータ利活用はこれまでのエウレカのデータ組織で高度に洗練されてきた領域ではありますが、昨今は黒魔術の他に後方支援やヒーラーの役割としての白魔術および白魔術師の重要性が見直されてきています。
もう少し最近の言葉を用いて説明すると、Data Analyticsの効率を最大化し、意思決定まで最短で接続するために、Engineering(データに限らず、あらゆる技術領域)とAnalytics(Data Analystだけでなく、ビジネスサイド全体のデータ利活用)を円滑に取り持つような役割が重要となる、と言えばわかりやすいでしょうか？
私の印象ですが、Analytics Engineerの職責は白魔術師のような立ち回りを勝手にイメージしています。
💊回復魔法: データを補完する
まずは基礎的な白魔術である回復魔法を紹介します。
SAFE関数
BigQueryの関数の中には、CASTのミスやゼロ除算でエラーとなり、計算ミスが発生したり、最悪クエリ実行に失敗するケースがあります。
アドホックでSQLを書いている際はこうしたエラーにすぐ気付くので、エラー原因を特定し、SQLを書き直して対処することができますが、dbtで既に運用しているmodelでこの類のエラーが発生するとちょっと困ります。
特に、運用して数ヶ月、数年という時間が経過した後に突然エラーとなる事案はデータ管理者泣かせとなります(泣いています)。
データ管理者目線、突然のクエリ起因のエラーは可能な限り減らし、運用コストを最小化することが重要です。
今回のケースは、SAFE関数の出番となります。
それぞれ詠唱し直してみます。
⚠️注意⚠️
SAFE関数はエラーの回避にとても役に立ちますが、本当にエラーを回避して良いか否かの判断は別になります。
エラーを正しく検知したい場合は、SAFE関数ではなくdbtで適切なテストを定義するなど、日々の運用業務の範疇でエラー検知をするべきだと私は考えています。
回復魔法も使いすぎると毒になるということをお忘れなきよう。
COALESCE
とあるテーブルを左結合(LEFT JOIN)した際に右側のテーブルの指標が左側のテーブルに結合できない場合、指標の結果はNULLを返します。
この状態でBIツールを使って可視化すると、指標にNULLが存在しているので、グラフの見栄えが悪い状態になります。
データに明るい人ならばNULLの理解はあるので問題ありませんが、そうではない方々に対してはNULLは何やねん？となりますので、NULLを表示するのではなく、適切な値に回復してあげましょう。
BigQueryでは2パターンの方法があります。私は以前はIFNULLを多用していましたが、最近SQLFluffのルールに則ってCOALESCEを使うようにしています。
Use COALESCE instead. COALESCE is universally supported, whereas Redshift doesn’t support IFNULL and BigQuery doesn’t support NVL. Additionally, COALESCE is more flexible and accepts an arbitrary number of arguments.
機械翻訳:
代わりにCOALESCEを使用してください。RedshiftはIFNULLをサポートしておらず、BigQueryはNVLをサポートしていません。さらに、COALESCEはより柔軟で、任意の数の引数を受け付けます。
このように、集計や結合などで失われてしまった指標の値を回復する際に非常に便利な魔法です。
以下の用途で私はよく利用しています。
- COUNTの結果がNULLの場合に0に補完する。
- SAFE_CASTを使って文字列から別のデータ型に置換した結果がNULLの場合に別の値に置換する。
- 論理演算の結果がNULLの場合にFALSEに補完する。
回復魔法は地味な白魔術ではありますが、Marts Layerでこの対応を行うことでBIツール使用時に追加でデータ加工を行う手間を省くことができます。
また、ほとんどのBIツールはコード管理ができないので、dbtにロジックを残す意味もあります。BIツールから計算ロジックをリバースエンジニアリングするのはとても工数のかかる作業になるので、コード化するメリットは十分あります。
🧪解毒魔法: UDFの毒を抜く
黒魔術の氷魔法 :定数化でUDFを紹介しましたが、UDFは便利な反面、以下の課題を抱えていました。
一時的なUDF
- SQLを書く際、毎回同じ詠唱(CREATE TEMPORARY FUNCTION…)をしなければならないので面倒。
- クエリの書き手の流派が異なるので、UDFの書き方や定義がバラバラでレビューの負荷が高い。
- よく使うUDFであるにも関わらず、コード化や永続化UDFにリファクタリングしないまま運用していたので、共通化しなければならない箇所が潜んでいる。
永続化したUDF
- BigQueryのデータソースが複数あるため、永続化したUDFがどこにあるのかわからない。
- 異なるデータソースで同じUDF名で命名する必要があるが、UDFの処理が異なることがある。そのため、間違いに気付かずにUDFを使用してしまうことがあった。
これらの毒(=課題)をdbtという触媒を用いて解毒をしてみます。
例えば、黒魔術でも紹介のあった年齢計算のUDFは以下のようにmacroとして登録し、dbt docsやData Catalog上で定義も参照できるようにしています。
Before:
After:
ちなみに弊社ではデータ環境をdbtに移行した際に全永続化UDFと一部のmodelで共通していたロジックをmacroの管理下に置くようにしました。
macroで管理することで、以下の恩恵を受けることになるのでオススメです。
- dbt、BigQueryともに再利用可能なリソースとなるため、車輪の再発明を防ぐことができる。
- dbt(Jinja template)の記法でUDFを呼び出せる。
- BigQueryにUDFは存在しているので、dbtを使わずにアドホックにSQLを書く際も困らない。
- dbt docsやData Catalog上でUDFの存在や定義を随時確認できる。
💪バフ魔法: Buffing Calendar table
黒魔術で紹介したGENERATE_DATE_ARRAYを用いたカレンダーですが、現在ではバフをかけまくっていてトンデモナイ進化を遂げています。
Before:
After:
日付のないデータを補完するための従来の利用の他、
- 年次集計、月次集計、週次集計の計算ロジックの時短
- 月初日、月末日の計算処理の時短
- 曜日や平日、休日の分析の強化
といった恩恵があるので利用シーンが益々増えています。
当然、こちらのクエリはdbtでしっかり管理しています。これまでサブクエリで詠唱して利用していましたが、dbtを触媒にさらなる高速詠唱を可能にしました。
また、日付に関する関数や集計処理が苦手なメンバーに対して、カレンダーテーブルを用いることで複雑な集計処理を簡単にするメリットもあります。
謝辞: 祝日判定UDF
祝日判定のロジックについては内製で作ってもいいかなと考え始めていたところ、BigQuery Advent Calendarにてna0 san(@na0fu3y)の祝日判定UDFを知ることとなり、利用させていただいておりました。
それから数年後、データ環境の移行に伴い、GCPのRegionをUSからTokyoに移行したことにより、UDFが使えなくなって困っていました。
Xでお気持ち表明していたところ、なんと爆速でTokyo Region対応してくださいました。感謝しかありません。
この場を借りて御礼を申し上げます。
♻️転移魔法: PIVOT & UNPIVOT
PIVOT、UNPIVOTは2021年3月ごろにBigQueryに実装された構文です。
この構文がない場合でもPIVOT、UNPIVOTライクな処理はクエリの書き方を工夫すれば実現はできましたが複雑なクエリとなるので、我々白魔術師のみならず、Data Analystも待望の魔術と言っても過言ではありません。
PIVOT
行動ログを集計する際に頭を悩ませるのは集計後のレコード数です。
レコード数が多すぎて、BIツールでパフォーマンスがうまく引き出せない、あるいは処理ができずに泣く泣くレコード数を減らすように条件を変更することもあります。
レコード数やフィールド(ディメンション)数にも依存しますが、元のデータをPIVOTすることでBIツール側の負荷を軽減する可能性があります。
Before:
After:
⚠️注意⚠️
BIツール側の負荷を軽減する可能性があります、と言いましたが、残念ながら定石ではなく、クエリロジック、データ構造などに強く依存しますのでケースバイケースです。
実際にパフォーマンスチューニングを行う際はクエリプランを精査した上で作戦を考えたり、あるいは要件の交渉(抽出件数を減らす、カーディナリティを落として集計する、など)をする必要があります。
先日シェアしたこちらのDashboardもBigQueryのクエリパフォーマンスやTableauの描画処理の限界に悩まされていました。
このDashboardでPIVOTのアプローチを採用した箇所と、PIVOTでは解決しなかった箇所がありました。
PIVOTは数あるパフォーマンスチューニングの一つの魔術として認識いただけると幸いです。
UNPIVOT
Google FormsやSurvayMonkeyといったフォームデータやアンケートデータはPivot tableの形式で提供されることがほとんどです。
Pivot table形式のデータ構造はSQLやBIツールで集計した際に不都合が多く発生するため、UNPIVOTして取り扱いやすいデータ構造に変換してあげると集計作業が捗ります。
実際のアンケートデータはもっと複雑なデータ構造をしているケースが多いため、この例のように簡単にはいきませんが、単一回答のデータ構造であればUNPIVOTで対処が可能となります。
🔍解析魔法: INFORMATION_SCHEMA
BigQueryで利用されているクエリ状況を把握したり、テーブルのメタデータを取得するための方法はBigQuery audit logsを設定する方法と、INFORMATION _SCHEMAを参照する方法の二つがあります。
BigQueryの公式ドキュメントに記載の通り一長一短あるので、要件に応じて選択すると良いかと思います。
今回はINFORMATION_SCHEMAを使った解析魔法を紹介します。
クエリ警察👮♀️
BigQuery audit logsには特定のクエリに関しての情報(実行時間、消費スロットなど)が確認できないため、弊社は日々のクエリチェック要件を満たす後者を採用しています。
クエリチェック要件はごくごくシンプルに以下の項目を設定し、運用しています。
- 高額なクエリ
- 実行時間が長いクエリ
- 実行回数が現実的ではないクエリ
- スロット消費が激しいクエリ
INFORMATION_SCHEMA.JOBSビューの実行にはbigquery.jobs.listAll権限が必要なので、利用する際はお忘れなきよう。私はよく忘れます。
こちらのクエリは一般化したものなので、プロジェクト名とリージョン名を指定すればすぐに動作するものとなっています。
弊社で運用しているクエリは上記のクエリよりも魔改造工夫を施しています。
クエリはdbtで管理し、Looker Studioで可視化しています。
実際のレポートを一部紹介します。
以下が実際のLineageですが、前出のカレンダーテーブルを使って日付単位で欠損データがないようにケアをしたり、金額を日本円に換算するためにFXレートのデータを掛け合わせていたりと、追加で機能追加をしています。
唯一の困りポイントは、dbtから直接INFORMATION_SCHEMAを呼び出すことができない点です。
上記Lineageのとおり、仕方なくsourceに登録した上でmodelを作成しているので正直イケていません。
この方法だと、INFORMATION_SCHEMAの突然の仕様変更に対応する必要がありますし、このsourceの定義自体が冗長なのでメンテナンスしたくありません。
他に良い方法を知っているよ！という他国の白魔術師の方はいらっしゃいませんか？ぜひ教えていただきたいです。
Description Checker
Data Catalogの導入を検討する前に、一度現在のメタデータの状況を把握するニーズが発生するかと思います。
例えば一覧でテーブル名、フィールド名に紐付くDescriptionを確認したいケースです。
この対応に一々BigQueryのブラウザ上から確認するのは面倒極まりないので、以下のクエリで簡単に一覧を取得できます。
上記クエリはDescriptionの棚卸し用途で緊急的に用意したものなので、dbtには登録せず、サクッとGoogle SheetsのData Connectorでシートに表示して棚卸しを実施しています。
以上のように、BigQueryに関するあらゆることはINFORMATION_SCHEMAに存在しています。
我々白魔術師は適宜、INFORMATION_SCHEMAから必要な情報を具現化しています。便利ですね。
✨聖魔法: ephemeral model
dbt modelを数多く作成していると、時折サブクエリが多い巨大なSQL(以下、クソデカSQLと呼称します)を詠唱することがあります。
(実際はもっとたくさんのsub queryがあるイメージです。)
このクエリ構造のままでmodelにした場合、以下のようになります。
クソデカSQLの最大のデメリットは、コード量に比例してクエリの可読性が悪くなります。
(感覚値としては、クエリ整形して100行を超えるコード量になるとレビューが面倒に、1000行を超えるととてもつらいレビューになるのを経験しています。)
コード量が増えれば当然、sub query一つ一つもそれなりに肥大化し、ロジックも複雑になるはずです。
このクソデカSQLの課題を打開するためによく使われる手法としては、個々のsub queryを別のmodel(多くのケースではView model)に外出しする方法です。
これで万事解決かと思いきや、この解決策はあくまでdbt目線でのお話なので、別の視点で新たな課題が発生します。
この例では外出ししたsub queryのmodelをIntermediate Layerに配置しましたが、BigQuery目線、新たに3つのViewが生成されることになります。
この3 Viewは元々のクソデカSQLにあったsub queryであるため、Marts Layerにある最終成果物であるmodelに関心のないBigQuery利用者には不要であるように見えます。
このsub queryを別のmodelと共通化する、というチームプレーができれば話は別なのですが、次の話をするために、あえてmodel作成者は独立している前提で話を進めます。
- 肥大化したsub queryを外部に出してdbtで別のmodelとして管理したい。
- BigQuery上には不要なテーブルやViewを残したくない。
という場合に、ephemeral modelが特効になります。
⚠️注意⚠️
dbtの公式ドキュメントにも記載がありますが、ephemeral modelには向き不向きがあります。要件に合わせて使い分けるようにすると良いです。
Pros:
You can still write reusable logic
Ephemeral models can help keep your data warehouse clean by reducing clutter (also consider splitting your models across multiple schemas by using custom schemas).Cons:
You cannot select directly from this model.
Operations (e.g. macros called via dbt run-operation cannot ref() ephemeral nodes)
Overuse of ephemeral materialization can also make queries harder to debug.機械翻訳:
長所:
再利用可能なロジックを書くことができる。
ephemeral modelはData Warehouseの乱雑さを減らし、綺麗に保つことができる。 (カスタムスキーマを使用して複数のスキーマにmodelを分割することも検討してください)短所:
直接modelを選択できない。
dbt run-operationで呼び出されるmacroはephemeral modelを参照できない。
ephemeral modelを使いすぎるとデバッグが困難になる。
余談
ephemeral modelはmodelを隠すので秘匿魔法なのでは？というツッコミがありそうですが、sub queryのViewを消し去る様を見てドラクエのニフラム、あるいはウィザードリィのターンアンデットを想起したので、独断と偏見で聖魔法に分類しました。
😇蘇生魔法: Incremental modelの復活
エラーや障害によって失われたデータを復活させたい！という要望はデータ管理上よくあります。
特に、dbtのmodelの中で圧倒的に複雑で難解なIncremental modelを復旧させるのにはコツが要ります。
dbt run — full-refresh
一番原始的かつ簡単な方法は公式ドキュメントの通り、dbt run — full-refreshを詠唱することです。
$ dbt run --full-refreshしかし、この方法には、
- ピンポイントに復活させたい箇所を指定できない。
- 正常なデータも作り直してしまうので、処理に無駄が生じる。
- 冪等性が担保されていないmodelの場合、データが変化してしまう可能性がある。
- 全データを洗い替えるので、当然その分のコスト(お金、時間)がかかってしまう。
- コスト削減したいがためにIncremental modelにしたのに、本末転倒となる。
という罠があります。何も考えずに安易に詠唱するとコストアラートが止まらなくなったり、Quotasの限界突破をしてしまい泣きます。私は大いに泣かされました。
Dagsterを用いたbackfill
Incremental modelに対して、復活させたいデータを指定しつつ、コストを抑えるためにはbackfillという方法でアプローチするのが良い方法かと思います。
ただし、現在(dbt version 1.7)ではdbtにはbackfillの機能は実装されていないため、弊社ではdbtを管理しているDagsterというOrchestration Toolでbackfillを実施しています。
backfill設定方法に関してはDagsterの公式ドキュメントをご覧ください。
DagsterはGUIが提供されていますので、弊社のようにdbt Coreで運用していていてもCLIでコマンドを実行することなくbackfillを実施することが可能となっています。
ということで、蘇生魔法に関しては遂に詠唱破棄に至ることに成功しています。
今回のテーマからはスコープ外でしたが、Dagsterに関する情報も機会があれば紹介したいと思います。
まとめ
最後まで読んでいただき、誠にありがとうございました。
一昨年、昨年と時間をかけてデータ環境を整備した結果、以前のように黒魔術の「攻め」を駆使して事業貢献を行うBI Teamのパフォーマンスは格段に向上しました。
そのインパクトと同様に、白魔術の「守り」の領域も効率化し、そして重要視されています。
引き続き、「攻め」と「守り」のバランスを保ちつつ、データ業務に勤しむ所存です。
余談ですが、私、冒頭で白魔術師を名乗りましたが、黒魔術も多少イケます。本記事の内容に関する質問やBigQuery、dbt、Dagsterをテーマにディスカッションしたいよ、という方はお気軽にご連絡ください。
Wantedly: Kazuya Araki
We are hiring!!
現在エウレカでは、白魔術師もとい、Data Analyst、Data Engineerを募集しています。
ビジネスサイドのデータ利活用であったり、AI関連のデータ環境の整備といった新しいテーマに取り組んだりと、かなりエキサイティングな内容が盛りだくさんですので、少しでも興味がありましたら下記よりご応募お待ちしております！
