BigQueryからCloudSQLへの データパイプライン構築

2024年10月10日（木）に開催された「第23回 酒とゲームとインフラとGCP」にコミューン株式会社バックエンドエンジニアの中野 慎太郎さんが登壇した際の資料です。ぜひ、ご覧ください。

リンク集
・コミューン株式会社コーポレートサイト：https://communeinc.com/ja
・採用情報：https://communeinc.com/ja/careers
・会社情報　エンジニアVer：https://www.notion.so/commmune/ver0b895a0187ef4954a2d5182afd82082d
・Commune Engineer Blog：https://tech.commune.co.jp/
・Xアカウント（Commune Developers）：https://twitter.com/CommuneDev
・Youtube：https://www.youtube.com/@CommuneDevelopers-f8h

Transcript

1. BigQueryからCloudSQLへの データパイプライン構築 2024/10/10 酒とゲームとインフラとGCP
2. © Commune Inc. All rights reserved ⾃⼰紹介 2 コミューン株式会社 中野 慎太郎 (バックエンドエンジニア) 好きな酒：⽇本酒 🍶 好きな飲酒頻度：毎⽇ 好きなゲーム：パワプロ 特技：フランス語 󰏃 DELF B2 (仏検 準⼀級 くらいらしい) → 信じてもらえません ; ;
3. © Commune Inc. All rights reserved ⽬次 - ⾃⼰紹介 - プロダクト紹介 - データパイプライン全体像と課題 - 改善のための調査 - ⾃前でパイプライン構築 - 気になっているGoogle Cloud のサービス
4. © Commune Inc. All rights reserved 最近した飲酒の話 4
5. © Commune Inc. All rights reserved 新潟県にて、「越後⻑岡 酒の陣」に⾏ってまいりました 5
6. © Commune Inc. All rights reserved ついでに⻑岡駅のぽんしゅ舘にも⽴ち寄ってみたり 6
7. © Commune Inc. All rights reserved 最後は⾦沢で甲羅焼き⾷べて帰ってきました 7
8. © Commune Inc. All rights reserved 本題... 8
9. © Commune Inc. All rights reserved プロダクトの紹介（ざっくりと） 9
10. © Commune Inc. All rights reserved 10 チームの生産性を圧倒的に向上し、カスタマーサクセス組織に成果をもたらす 顧客に関するあらゆる重要な 情報や健康状態を一元管理 テックタッチの工数で ハイタッチの効果を実現 顧客の状態に応じた個別最適な アクションを実行管理
11. © Commune Inc. All rights reserved SuccessHubで実現すること 顧客情報の集約・可視化 → タスク発行 → アクション実行・管理 →成果の創出までを一気通貫で実現 11 変化の検知 情報の集約・可視化 自動でタスク発行 手動でタスク発行 手動でアクション実行 自動でアクション実行 アクションマネジメントの実現 成果の創出
12. © Commune Inc. All rights reserved アカウント一覧機能 契約内容, サービス利用状況, コミュニケーション履歴など、複数のデータソースをまたぐ情報を一括で表示。任意の過去の 日付との差分表示も可能です。また表示する情報はフルカスタマイズが可能なので、自社に最適な運用を実現できます。 12 • 商流（直販 / 代理店） • オンボーディング完了予定日 • 予算策定時期 • Weeklyアクティブ率 • 利用用途 • 契約更新回数 • 発行済みID数 etc. 表示する情報の例 フルカスタマイズ可能
13. © Commune Inc. All rights reserved 既存のデータパイプライン全体像と課題 13
14. © Commune Inc. All rights reserved データ基盤の概要全体図 14 Multitenancy App platforms ETL data pipeline Cloud Run BigQuery Formatted data Client 1 Client 2 Client 3 Data Resources Client 1 Client 2 Client 3 Cloud SQL BigQuery Original Data data build data linking data connecting Google Cloud Join with Federated Query
15. © Commune Inc. All rights reserved データ基盤の概要全体図 15 Multitenancy App platforms ETL data pipeline Cloud Run BigQuery Formatted data Client 1 Client 2 Client 3 Data Resources Client 1 Client 2 Client 3 Cloud SQL BigQuery Original Data data build data linking data connecting Google Cloud Join with Federated Query この部分のロードが遅延しておりボトル ネックになってそう
16. © Commune Inc. All rights reserved データ基盤の概要全体図 16 Multitenancy App platforms ETL data pipeline Cloud Run BigQuery Formatted data Client 1 Client 2 Client 3 Data Resources Client 1 Client 2 Client 3 Cloud SQL BigQuery Original Data data build data linking data connecting Google Cloud Join with Federated Query 二つのリソースを参照してデータを抽 出するロジックが複雑すぎる
17. © Commune Inc. All rights reserved Cloud Trace を使ってボトルネックを調査してみた 17 新しいJobを開始する際に1.3s ~ 1.8s 時間がかかる クエリの実行結果は比較的高速で返ってきているが ...
18. © Commune Inc. All rights reserved クエリのチューニングを⾏っても避けられないレイテンシを改善したい + 複雑なクエリへの対応などアプリの拡張に柔軟に対応したい → BigQuery から CloudSQL (MySQL) へデータを転送すれば解決するのでは 💡 しかし、CloudSQL から BigQuery への転送はいくらでも例が⾒つかるが 逆はあまり⾒たことがない... 18
19. © Commune Inc. All rights reserved 何かいいツールないかな... 19
20. © Commune Inc. All rights reserved ちょうど良いツールがなかったので...⾃前で実装 & Cloud Run jobs で実⾏!! 20 メリット - 固有の処理などが必要になったときに自由にカスタマイズができる - サーバーの稼働は最低限で済むので比較的料金が安い デメリット - 実装したコードを自分たちでメンテしなければいけないので工数がかかる - CI/CD 周りも新規で必要 - SQL サーバー側の負荷がやや⼼配
21. © Commune Inc. All rights reserved ちなみに、このようなフローのことを Reverse ETL と⾔うらしいです Gemini に聞いてみました 21 メリット - 固有の処理などが必要になったときに自由にカスタマイズができる - サーバーの稼働は最低限で済むので比較的料金が安い デメリット - 実装したコードを自分たちでメンテしなければいけないので工数がかかる - CI/CD 周りも新規で必要 - SQL サーバー側の負荷がやや⼼配
22. © Commune Inc. All rights reserved Reverse ETL するぞ 22
23. © Commune Inc. All rights reserved データ基盤の概要全体図 23 Multitenancy App platforms ETL data pipeline Cloud Run BigQuery Formatted data Client 1 Client 2 Client 3 Data Resources Client 1 Client 2 Client 3 Cloud SQL BigQuery Original Data data build data linking data connecting Google Cloud Cloud Run Jobs Cloud Scheduler 定期実行jobを作成してBigQueryの 最新の状態をCloudSQL側に転送
24. © Commune Inc. All rights reserved Node.js & TypeScript でのシンプルな実装イメージ 24 - @google-cloud/bigquery npmパッケージを使って、BQ データ取得 → データのコンバー ト等 → MySQL側 にINSERT - Cloud Run jobs にデプロイし、 Cloud Schedulerをトリガーとし て実⾏する
25. © Commune Inc. All rights reserved Node.js & TypeScript でのシンプルな実装イメージ 25 {{CLOUD_RUN_TASK_ATTEMPT}} 再実行数のカウントも取得可能 {{CLOUD_RUN_TASK_INDEX}} task ごとに発行される index を環境変数として参 照できるのでそれを、テナントIDなどに割り当てる ことが可能
26. © Commune Inc. All rights reserved CI/CD周り Cloud Run Jobs の設定イメージ 26 - Cloud Build でビルド   → Artifact Registry に push - gcloud run jobs deploy コマン ドでデプロイ
27. © Commune Inc. All rights reserved CI/CD周り Cloud Run Jobs の設定イメージ 27 -parallelism タスクの並列実行数（0 ~ 100 の間で設定可能 ※CPU数によっても変動） -tasks タスクの総数（1 ジョブにつき 10000タスクまで 実行可能） -task-timeout タスクのタイムアウト（最大24時間まで設定可 能） -max-retries 最大リトライ数（0 ~ 10 の間で設定可能）
28. © Commune Inc. All rights reserved 28 結果 - データ表⽰のためのAPIレスポンスが 2.0s → 1.1s と 約 1s 速くなった - アプリからBigQueryを直参照しなくなったのでこれからやりたい機能開発も やりやすくなった
29. © Commune Inc. All rights reserved 余談：気になってきているGoogle Cloud のサービス 29
30. © Commune Inc. All rights reserved Cloud Spanner 30
31. © Commune Inc. All rights reserved Cloud Spanner 31 - Cloud Spanner は、エンタープライズ クラスで唯⼀、グローバルに分散され、強整合性を備えた データベース サービスです。リレーショナル データベースの構造と⾮リレーショナル データベー スの⽔平スケーラビリティを兼ね備え、クラウドに特化した設計となっています。スケーラビリ ティは通常、⾮リレーショナル データベースや NoSQL データベースと関連して語られるものです が、Cloud Spanner はスケーラビリティをトランザクション、SQL クエリ、リレーショナル構造 と組み合わせている点でユニークなデータベースといえます。 引⽤：https://cloud.google.com/blog/ja/topics/developers-practitioners/what-cloud-spanner
32. © Commune Inc. All rights reserved 32
33. © Commune Inc. All rights reserved Cloud Spanner BQの Export data to Spanner (Reverse ETL) 機能を使えばもっと楽にできたのでは...?? 33 - (まだPreviewの機能だが) 簡単な設定とクエリで⾼速にリバースETLを実 現できる - 我々開発チームとしては、Spannerへの移⾏⾃体が⼤変&今回やりたいこ とだけでは移⾏への理由が弱かったため⾒送り 😢 - 全く関係ないが、Spanner Graph も我々のプロダクトとの親和性が⾼そ うで気になっている 👀
34. コミューンの会社情報 エンジニア向け会社情報 Xアカウント (Commune Developers) https://commmune.notio n.site/ver-0b895a0187ef4 954a2d5182afd82082d 34
35. © Commune Inc. All rights reserved