Tweet
Tweet

Slide 1

Slide 1 text

BigQueryからCloudSQLへの データパイプライン構築 2024/10/10 酒とゲームとインフラとGCP

Slide 2

Slide 2 text

© Commune Inc. All rights reserved ⾃⼰紹介 2 コミューン株式会社 中野 慎太郎 (バックエンドエンジニア) 好きな酒：⽇本酒 🍶 好きな飲酒頻度：毎⽇ 好きなゲーム：パワプロ 特技：フランス語 󰏃 DELF B2 (仏検 準⼀級 くらいらしい) → 信じてもらえません ; ;

Slide 3

Slide 3 text

© Commune Inc. All rights reserved ⽬次 - ⾃⼰紹介 - プロダクト紹介 - データパイプライン全体像と課題 - 改善のための調査 - ⾃前でパイプライン構築 - 気になっているGoogle Cloud のサービス

Slide 4

Slide 4 text

© Commune Inc. All rights reserved 最近した飲酒の話 4

Slide 5

Slide 5 text

© Commune Inc. All rights reserved 新潟県にて、「越後⻑岡 酒の陣」に⾏ってまいりました 5

Slide 6

Slide 6 text

© Commune Inc. All rights reserved ついでに⻑岡駅のぽんしゅ舘にも⽴ち寄ってみたり 6

Slide 7

Slide 7 text

© Commune Inc. All rights reserved 最後は⾦沢で甲羅焼き⾷べて帰ってきました 7

Slide 8

Slide 8 text

© Commune Inc. All rights reserved 本題... 8

Slide 9

Slide 9 text

© Commune Inc. All rights reserved プロダクトの紹介（ざっくりと） 9

Slide 10

Slide 10 text

© Commune Inc. All rights reserved 10 チームの生産性を圧倒的に向上し、カスタマーサクセス組織に成果をもたらす 顧客に関するあらゆる重要な 情報や健康状態を一元管理 テックタッチの工数で ハイタッチの効果を実現 顧客の状態に応じた個別最適な アクションを実行管理

Slide 11

Slide 11 text

© Commune Inc. All rights reserved SuccessHubで実現すること 顧客情報の集約・可視化 → タスク発行 → アクション実行・管理 →成果の創出までを一気通貫で実現 11 変化の検知 情報の集約・可視化 自動でタスク発行 手動でタスク発行 手動でアクション実行 自動でアクション実行 アクションマネジメントの実現 成果の創出

Slide 12

Slide 12 text

© Commune Inc. All rights reserved アカウント一覧機能 契約内容, サービス利用状況, コミュニケーション履歴など、複数のデータソースをまたぐ情報を一括で表示。任意の過去の 日付との差分表示も可能です。また表示する情報はフルカスタマイズが可能なので、自社に最適な運用を実現できます。 12 ● 商流（直販 / 代理店） ● オンボーディング完了予定日 ● 予算策定時期 ● Weeklyアクティブ率 ● 利用用途 ● 契約更新回数 ● 発行済みID数 etc. 表示する情報の例 フルカスタマイズ可能

Slide 13

Slide 13 text

© Commune Inc. All rights reserved 既存のデータパイプライン全体像と課題 13

Slide 14

Slide 14 text

© 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

Slide 15

Slide 15 text

© 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 この部分のロードが遅延しておりボトル ネックになってそう

Slide 16

Slide 16 text

© 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 二つのリソースを参照してデータを抽 出するロジックが複雑すぎる

Slide 17

Slide 17 text

© Commune Inc. All rights reserved Cloud Trace を使ってボトルネックを調査してみた 17 新しいJobを開始する際に1.3s ~ 1.8s 時間がかかる クエリの実行結果は比較的高速で返ってきているが ...

Slide 18

Slide 18 text

© Commune Inc. All rights reserved クエリのチューニングを⾏っても避けられないレイテンシを改善したい + 複雑なクエリへの対応などアプリの拡張に柔軟に対応したい → BigQuery から CloudSQL (MySQL) へデータを転送すれば解決するのでは 💡 しかし、CloudSQL から BigQuery への転送はいくらでも例が⾒つかるが 逆はあまり⾒たことがない... 18

Slide 19

Slide 19 text

© Commune Inc. All rights reserved 何かいいツールないかな... 19

Slide 20

Slide 20 text

© Commune Inc. All rights reserved ちょうど良いツールがなかったので...⾃前で実装 & Cloud Run jobs で実⾏!! 20 メリット - 固有の処理などが必要になったときに自由にカスタマイズができる - サーバーの稼働は最低限で済むので比較的料金が安い デメリット - 実装したコードを自分たちでメンテしなければいけないので工数がかかる - CI/CD 周りも新規で必要 - SQL サーバー側の負荷がやや⼼配

Slide 21

Slide 21 text

© Commune Inc. All rights reserved ちなみに、このようなフローのことを Reverse ETL と⾔うらしいです Gemini に聞いてみました 21 メリット - 固有の処理などが必要になったときに自由にカスタマイズができる - サーバーの稼働は最低限で済むので比較的料金が安い デメリット - 実装したコードを自分たちでメンテしなければいけないので工数がかかる - CI/CD 周りも新規で必要 - SQL サーバー側の負荷がやや⼼配

Slide 22

Slide 22 text

© Commune Inc. All rights reserved Reverse ETL するぞ 22

Slide 23

Slide 23 text

© 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側に転送

Slide 24

Slide 24 text

© Commune Inc. All rights reserved Node.js & TypeScript でのシンプルな実装イメージ 24 - @google-cloud/bigquery npmパッケージを使って、BQ データ取得 → データのコンバー ト等 → MySQL側 にINSERT - Cloud Run jobs にデプロイし、 Cloud Schedulerをトリガーとし て実⾏する

Slide 25

Slide 25 text

© Commune Inc. All rights reserved Node.js & TypeScript でのシンプルな実装イメージ 25 {{CLOUD_RUN_TASK_ATTEMPT}} 再実行数のカウントも取得可能 {{CLOUD_RUN_TASK_INDEX}} task ごとに発行される index を環境変数として参 照できるのでそれを、テナントIDなどに割り当てる ことが可能

Slide 26

Slide 26 text

© Commune Inc. All rights reserved CI/CD周り Cloud Run Jobs の設定イメージ 26 - Cloud Build でビルド   → Artifact Registry に push - gcloud run jobs deploy コマン ドでデプロイ

Slide 27

Slide 27 text

© 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 の間で設定可能）

Slide 28

Slide 28 text

© Commune Inc. All rights reserved 28 結果 - データ表⽰のためのAPIレスポンスが 2.0s → 1.1s と 約 1s 速くなった - アプリからBigQueryを直参照しなくなったのでこれからやりたい機能開発も やりやすくなった

Slide 29

Slide 29 text

© Commune Inc. All rights reserved 余談：気になってきているGoogle Cloud のサービス 29

Slide 30

Slide 30 text

© Commune Inc. All rights reserved Cloud Spanner 30

Slide 31

Slide 31 text

© 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

Slide 32

Slide 32 text

© Commune Inc. All rights reserved 32

Slide 33

Slide 33 text

© Commune Inc. All rights reserved Cloud Spanner BQの Export data to Spanner (Reverse ETL) 機能を使えばもっと楽にできたのでは...?? 33 - (まだPreviewの機能だが) 簡単な設定とクエリで⾼速にリバースETLを実 現できる - 我々開発チームとしては、Spannerへの移⾏⾃体が⼤変&今回やりたいこ とだけでは移⾏への理由が弱かったため⾒送り 😢 - 全く関係ないが、Spanner Graph も我々のプロダクトとの親和性が⾼そ うで気になっている 👀

Slide 34

Slide 34 text

コミューンの会社情報 エンジニア向け会社情報 Xアカウント (Commune Developers) https://commmune.notio n.site/ver-0b895a0187ef4 954a2d5182afd82082d 34

Slide 35

Slide 35 text

© Commune Inc. All rights reserved