Session Title BigQueryのデータ監視 社内サービスを作った話 @hyrrot (Hirobumi Takahashi) Data Manager, Data Management Team, Merpay

Hirobumi Takahashi / @hyrrot 2011年に楽天株式会社に入社、開発者向けグ ループウェア、CI/CD基盤、データ分析基盤の開発 と運用に従事。 2022年に株式会社メルペイに入社後、 dbtなどによ る分析データの準備のシステムの開発と運用など を通じ、アナリストの皆様に最強のデータ分析環境 を享受していただくために日々奮闘している。 株式会社メルペイ Data Manager

メルカリグループのデータ基盤 ● Data Platform Team
 ○ データパイプラインを通して、データをBigQueryに届ける
 ● Data Management Team
 ○ データの利用者が「あんぜんに」「あんしんして」「かんたんに」データを活用でき るような仕組みやプロセス構築、サポートを行う
 （今回の話はこっち！）


Data Management Team ● BigQueryのデータを整え、データ利用者に届ける
 ○ たとえば、dbtを用いて生テーブルを中間テーブルに変換
 ○ dbtの定期的な実行の基盤として、Cloud Buildを始めとしたGCPのマネージ ドサービス、およびArgo Workflowsなどを利用


ある日、複数のチームから同時に来たリクエスト ● 「BigQueryテーブル内のデータが正しくないときに通知がほしい」
 
 ● チームA：
 ○ BigQueryテーブルのデータを用いて、顧客企業に対する経費精算を行って いるが、テーブルのデータが、そのクエリの前提となる条件を満たしていな いときに、それを検出したい
 ● チームB：
 ○ BigQueryテーブルのデータを用いて、お客さまに対してポイントの付与オペ レーションを行っている。テーブルが、ポイントの誤付与が発生したことを示 すデータを含む場合、それを検出したい
 
 ● 社内に、このような要求に簡単に応えるサービスはなかった


全社で利用できる、BQデータの監視システムを作ろう ● 「BigQueryテーブル内のデータが正しくないときに通知がほしい」
 ○ データの「正しさ」をそれぞれのチームに定義してもらいたい
 
 ● チームA、チームBに、以下のようなクエリを書いてもらう
 ○ データが正しい場合、0行の結果を返す
 ○ データが正しくない場合、1行以上の任意の結果を返す
 
 ● もらったクエリを定期的に実行し、以下のような場合にチームに通知
 ○ 1行以上の結果が返ってきた
 ○ クエリの実行に失敗した
 
 ● 命名：QueryMon（クエリもん）
 


QueryMon バージョン 1.0 ● Argo Workflows上で、定期的に監視ワークフローを実行
 ○ CronWorkflowがクエリを実行するService Account (SA)名を指定、Workflow内 でimpersonate
 ● クエリ結果が1行以上 or 失敗の場合、Slackに直接通知
 
 
 
 
 


QueryMon バージョン 1.0 ● 問題
 ○ 監視が行われるべきであったにもかかわらず、行われなかったことを知ること ができない
 ■ もし、このときにBQデータに問題があったら、チームは問題を見逃してし まう！
 ○ 監視設定の変更が社内のCI/CDシステムを経由し、必ずQueryMonの管理者 の承認が要求される
 ■ できれば、監視設定の権限をチームに委譲したい
 ○ Argo Workflowsが社内の別チームの管理であり、トラブルシューティング時の コミュニケーションコストが上がる


QueryMon バージョン 2.0 ● Cloud Scheduler / Cloud Pub/Sub / Cloud Functionsを利用して実装 
 ● Cloud Schedulerは以下の情報をJSONとしてトピックへpublish 
 ○ チーム名、クエリ名、クエリ、SA名、BQの実行プロジェクト、タイムアウト時間
 ● QueryMonは監視結果をDatadogに送信 
 ● 以下の場合に、DatadogがSlackに通知 
 ○ あるチーム名&クエリ名の監視結果が1件以上、あるいは失敗時 
 ○ あるチーム名&クエリ名の監視結果が一定時間存在しないとき 
 ● チーム所有のGCPプロジェクト内のリソースは、チーム内の承認のみで変更可能 
 
 
 


Cloud Schedulerで別のプロジェクトの Pub/Subトピックにpublish ● Cloud Schedulerは、Pub/Subトピックにpublishするジョブを
 定義できるが、この種のジョブは異なるプロジェクトへの
 publishをサポートしていない
 
 ● Pub/SubのHTTPS (REST) APIを呼ぶことで解決
 
 
 
 


Cloud Schedulerで別のプロジェクトの Pub/Subトピックにpublish ● publish APIは、リクエストのbodyに、publishするデータを
 BASE64エンコードして渡す必要がある
 ● Cloud SchedulerのUIで、BASE64エンコードされた設定を
 直接管理するのは困難
 ● Terraformを利用して設定
 
 
 
 
 
 
 { "team": "team1", "query_name": "query_name1", "query": "SELECT 1 LIMIT 0", "bq_executor_project_id": "some-project", "impersonate_service_account": "sa@some-project.iam.gserviceaccount.com", "timeout_seconds": 60 } publishされるデータ 
 BASE64エンコード Cloud Scheduler UI上で 
 publish API のbodyに指定するデータ 


Cloud Schedulerで別のプロジェクトの Pub/Subトピックにpublish resource "google_cloud_scheduler_job" "job" { // ...省略... http_target { // ... 省略... body = base64encode(jsonencode({ messages = [{ data = base64encode(jsonencode({ # Set team name "team" : "team1", # Set query name "query_name" : "query1", # Write a query "query" : "SELECT 1 LIMIT 0", # Set a executor project "bq_executor_project_id" : "some-project", # Set impersonate service account "impersonate_service_account" : "sa@some-project.iam.gserviceaccount.com", "timeout_seconds" : 60 }))}]})) } }

Datadogのmonitor設定 ● querymon.monitor_result.returned_row_count というmetricに、クエリの実行結果の 返ってきた行数を格納
 ● 以下のようなMonitorのクエリで、1行以上のデータが返ってきたことを検出
 
 
 
 
 ● Alert Conditionで、metricが一定時間存在しない場合にalertを発生させるように設 定
 
 max(last_1h):max:querymon.monitor_result.returned_row_count{env:dev,query_name:query_name1,team:team1} > 0
 クエリ名
 チーム名


QueryMon バージョン2.0 ● 1.0 と比べて良くなったところ
 ○ 監視が行われなかったことの検出が可能に
 ○ チームが監視の設定を自分たちで変更することが可能に
 ○ Datadogがサポートする、Slack以外の通知先も設定できる（たとえば、 PagerDutyを利用して電話をかけることもできる）
 ○ Argo Workflowsへの依存をなくし、コミュニケーションコストが軽減
 
 ● まだ解決されていない問題点
 ○ チームが所有してないSAで任意のクエリを実行できてしまう


チームが所有してないSAで任意のクエリを 実行できてしまう ● 権限さえあれば、QueryMonのPub/Subトピックに任意のデータをpublishできてしまう
 ● publishされるデータ内で、クエリを実行するSA名と、実行するクエリを自由に指定することができる
 ● →自分のチームの責任範囲外のSAで、任意のクエリを実行されることを拒否できない
 
 ● リスク軽減策
 ○ トピックのpublish権限を厳格に管理（申請されたSAのみが利用可能とする）
 ○ QueryMonがテーブルのデータそのものを扱わない
 ■ ”maxResults”パラメータに0を指定してBQ query APIを呼ぶと、クエリの成功/失敗およ び結果の行数のみが得られ、データそのものは得られない
 →QueryMonの仕様上問題ない


まとめ ● BQのデータが正しくないことを検出したい、という複数のチームの要件を叶えるた め、BQのデータを監視する仕組みを作り、社内に展開した
 
 
 


No content