KARTE Signals の複雑 SQL を支える dbt の活用
Posted on
概要
本記事では dbt (data build tool) を本番環境のサービスで使った事例を主に Web サービス開発者向けに紹介します。プレイドでは KARTE Signals Connector (以下、Signals コネクタ) の SQL を生成するエンジンとして dbt を使っています。dbt を使う前には機能を実現するための SQL が大きく複雑なために機能の追加や変更が困難な状態に陥っていました。dbt を使うことで SQL をシンプルな複数のファイルに分割して、自動テストも導入できるようになりました。機能の変更や保守がしやすくなり、開発速度と品質が向上しました。
背景
dbt を使うに至った背景
今回の記事の背景として Signals コネクタを紹介します。KARTE Signals にはダッシュボードとコネクタという2つの機能があります。Signals ダッシュボードは広告効果を表示・分析するためのツールですが、今回はスコープ外なので詳しく紹介しません。Signals コネクタは 1st Party Data を使って広告効果を根本的に改善するサービスです。Signals コネクタには主に3つの機能があります。1つ目は失われたコンバージョンの補完です。これまでコンバージョン情報の計測のために使っていた 3rd Party Cookie の利用が Intelligent Tracking Prevention (ITP) により阻害され、広告媒体のコンバージョン情報の欠損が発生します。この欠損したコンバージョンを広告媒体の Conversion API を利用して補完します。2つ目はターゲティングです。KARTE で取得する行動データや自社の CRM データを活用することでより高精度にターゲティングを実現します。3つ目は入札最適化の精度改善です。Value Based Bidding (VBB) という入札最適化によって目的広告費用対効果やコンバージョン値の最大化を目指します。
広告API用データ作成の問題
広告配信媒体は、その広告媒体の API ごとに必要なデータの内容やフォーマットが異なります。つまり、広告媒体 API の違いを吸収するように似たような機能の複雑な SQL を複数用意する必要があります。この複雑な SQL の開発と管理を楽にするために dbt を使っています。具体的には「複数の SQL テンプレートファイルと変数から1つの SQL を dbt が合成(compile)」します。Signals コネクタには dbt を使った SQL 合成の機能が組み込んであります。Signals コネクタではクライアントがデータフォーマットの違いを意識することはほとんどありません。管理画面上からいくつかのパラメータ設定をするだけで広告媒体 API に対してデータを送ることが出来ます。
dbt
dbt とは何か？
dbt はデータレイクもしくはデータウェアハウスと呼ばれるデータプラットフォームに格納されたデータの分析をサポートするツールです。 具体的には、オンラインエディタで SQL を書いてデータプラットフォームで実行したり、Git で SQL ファイルを管理したり、CI/CD でビルドやテスト、デプロイなどができます。
なぜ dbt を選んだのか？
dbt も含めて複数の SQL テンプレートファイルから1つの SQL を合成する方法はいくつかあります。たとえば、プログラミング言語と SQL フレームワークを使って SQL を生成したり、単純な SQL テンプレートに書く、ビジュアルエディタや統合開発環境を使うなどの方法があります。Signals チームでは初期に SQL 書くメンバーがプログラマではなかったのでプログラミング言語による SQL 生成は選択しませんでした。また、検証時に作成した SQL 文を活用したいという要望もありました。dbt はこれらの要求を満たしているツールの1つでした。OSS(dbt Core)としてソースコードが提供されているのとSaaSオンラインエディタ(dbt Cloud)を利用できるのが dbt の良いところです。
開発と運用
dbt をどのように使っているか？
dbt を使う前には KARTE 内のクエリエディタ機能で巨大な BigQuery SQL を書いていました。現在は dbt を使って動的に SQL を生成しています。テンプレートとなる SQL を書くときには dbt Cloud というオンライン開発環境で作業します。エディタ上で SQL のビルドや実行、テストデータを使ったテストもできます。dbt では Git を使ってテンプレートファイルを管理します。Git ブランチ切り替えや GitHub Pull Request 作成も dbt Cloud から操作できます。
dbt を使う前後でどのように SQL 開発環境が変わったのかを説明します。
| dbt 使用前 | dbt 使用後 | |
|---|---|---|
| SQL ファイル数(1機能あたり) | 1 | 5 (平均) |
| SQL 行数(1ファイルあたり) | 300 - 500 | 20 - 60 |
| テストデータ作成 | 困難 | 容易 |
| 自動テスト | なし | あり |
| SQL クエリ管理 | データベース | Git |
| クエリエディタ | 独自 (機能少) | dbt Cloud (機能多) |
SQL ファイルの分割によってコードの見通しが良くなったことで、クエリを改善できて SQL の合計行数も少なくなっています。SQL を実行してエラーが出たときにテストデータに問題があるのかクエリ内容の問題なのか切り分けるのが難しかった問題も、小さな SQL に分割することによって判断しやすくなりました。SQL の自動テストについては後述します。
dbt 関連サーバ構成
dbt の SQL 合成は GraphQL API を通じて行うようにしました。dbt Cloud の API (REST API) を使うことも検討したのですが、BigQuery の権限管理の要件を満たすことができずに自前で dbt API を作成して運用しています。
Signals の構成サーバのうち dbt に関係している部分だけを抜き出した構成図
Signals サーバと dbt API サーバが GraphQL で通信します。dbt API サーバ自体はステートレスなのでデータベースを持たず dbt Core を通じて Git Repository (ワーキングディレクトリ)内の SQL ファイルを参照します。
dbt を使った SQL 合成機能の開発フロー
Signals チームにおける SQL 合成機能の開発フローを紹介します。ここでは新しい機能(モデル)を追加する場合のフローを例とします。SQL 開発自体は dbt Cloud のみでほとんど作業が完結します。
- Git の main ブランチから開発ブランチを作成する
- seeds ディレクトリにテストデータ(csvファイル)を入れる(dbt seed で BigQuery にアップロードされる)
- models 以下に新しいモデルのディレクトリを作成する
- SQL ファイルを書いてビルドする(dbt build で BigQuery を使って SQL を実行する)
- SQL が完成したら git commit して Pull Request を作る
- CI による自動テストと担当者によるコードレビューを行う
- PR をマージ後に GraphQL サーバとクライアントを自動生成して git commit して PR を作成する
- CI によるテストとコードレビュー
- PR をマージするとクライアントが自動リリースされる
自動ビルドと自動テスト
CI でビルドを実行して SQL が正しいかどうかを確認します。BigQuery と接続することで合成した SQL が文法として正しいかどうかだけではなく、テストデータ自体のフォーマットや合成した SQL を実行して結果が取得できるかも確認します。テストとして特定の SQL 文を実行して結果の整合性を確認することもできます。
自動でビルドとテストを行えるようになったお陰で CI の時点で SQL の文法ミスや変数設定時の誤植などを減らすことができるようになりました。SQL ファイルを小さいファイルに分割したことによって個々の機能が明確になり、レビューも容易になりました。
自動テストではテストデータや個別の SQL 実行後のスキーマが正しいかどうかを検証します。自動テストによって SQL 修正時に特定の項目を出力してなかったりカラム名を間違っていたりするような単純なミスを見つけやすくなりました。
GraphQL サーバとクライアントのコード生成スクリプト
GraphQL サーバ (Python) と GraphQL クライアント (node.js) のコードをそれぞれスクリプトで生成しています。dbt Cloud を使って作成した SQL ファイルが models ディレクトリ以下に置いてあるものとします。コード生成スクリプトを実行すると GraphQL サーバと GraphQL クライアントのコードを生成します。
サーバ側のコード生成のポイントは SQL ファイルを読んで変数名を抽出しているところです。変数を GraphQL の引数として渡せるようにしています。変数を自動抽出することで変数名の間違いや抜け漏れを減らすことができます。
クライアント側のコード生成には GraphQL Code Generator を使っています。GraphQL Code Generator は GraphQL スキーマと GraphQL ドキュメントから TypeScript などの言語による GraphQL クライアントを生成します。GraphQL スキーマはサーバ側のコードから自動生成します。スクリプトで自動生成した GraphQL ドキュメントと組み合わせると GraphQL クライアントを生成できます。
現状の課題と展望
dbt の問題点
dbt も万能ではなく、いくつか扱いづらいことがありました。使ってみて気づいた課題を共有します。
- dbt compile が遅い
SQL の生成に使っている dbt compile に数秒程度かかります。複数ファイルをマージして変数展開している部分よりも BigQuery に接続しているところで時間がかかっているようです。頻繁に dbt compile を実行しないようにアプリケーションの実装を工夫することで問題を回避しています。 - ファイル名や参照名のユニークネス
dbt ではモデルという概念で生成される SQL を表し、複数のモデルを1つの dbt プロジェクトで扱えます。しかし、ファイル名や参照(ref)名をプロジェクト内でユニークにしなければならないという制限があります。複数のモデルでそれぞれ異なるファイル名をつける必要があります。似たような機能を提供してほぼ同じ内容の SQL ファイルに固有の名前を要求されるので、モデルが増えるたびにファイル名の重複を気にする必要があります。必要に応じて命名規則を定めた方が良いかもしれません。
今後の展望
- SQL 合成機能以外も活用する
dbt の本来の使い方である継続的なデータ分析のサポートの機能も使ってみたいと思います。たとえば source freshness を設定すると入力データが正しく更新されているか監視することができます。この機能を使えばサービス品質を向上するのに使えるはずです。 - 他の機能にも dbt を展開する
KARTE やその他のプロダクトでも dbt を使える場所に展開することを検討しています。普及活動の一環として社内の勉強会で dbt の使い方や活用事例を何度か紹介しました。
まとめ
KARTE Signals コネクタでは dbt Cloud で開発した SQL を独自の dbt GraphQL API を通じて本番環境で利用しています。dbt を導入することで SQL の開発や保守がやりやすくなりました。最大500行以上あった複雑な SQL を数十行のファイル群に分割することで見通しが良くなり、レビューや動作確認、テストデータの作成が容易になりました。また自動ビルドや自動テストを導入することで SQL の記述の問題に早く気付けるので、開発とデバッグの速度も向上しました。
CX（顧客体験）プラットフォーム「KARTE」を運営するPLAIDでは、データを活用しプロダクトを改善していくエンジニアを募集しています。
詳しくは、弊社採用ページをご覧ください。