Cloud SQL と BigQuery の同期における Datastream の活用

ツール導入前の課題

JX通信社の「FASTALERT」は、日本国内外の緊急情報をリアルタイムで配信するサービスです。災害情報や事故、事件、気象警報など、幅広い分野の緊急情報を網羅しており、長年にわたって膨大な災害データを蓄積しています。
システム上、「いつ・どこで・何が起きたか」という災害事象データが位置情報とともに Cloud SQL に蓄積されています。
これまで、社内のデータ基盤では Cloud SQL から BigQuery への同期の仕組みを構築していましたが、リアルタイム同期ではなく、Cloud Composer (Airflow) を利用した Daily または Hourly のバッチタスクを使用していました。スケジュールタスクで Cloud SQL 連携クエリ によりデータを取得し、BigQuery に保存する方式です。
しかし、この方法には以下の課題があります。

• データの品質
  バッチ処理の実行タイミングや取得範囲設定によって、BigQuery と CloudSQL のデータに差分が生じます。

  • 過去の更新分が反映されない：たとえば取得範囲を「7日」と設定した場合、7日以上前のデータに更新があっても、それは BigQuery に反映されません。
  • また、バッチの実行間隔を Daily に設定すると、データの反映に最大1日の遅延が生じる可能性があります。

• 実装コスト
  テーブルごとに ETL（データの抽出・変換・ロード）と転送パイプライン設定（DAG）を実装する必要があり、設定コストがかさみます。

どのような状態を目指していたか

• 過去の特定の時期や地域で発生した災害事象データを簡単に取得できるようになる
• BigQuery でデータの推移や統計情報を視覚的に分析しやすくなる
• 本番 DB にアクセスすることなく、負荷をかけずにデータを取得できる

• Cloud SQL 連携クエリ

• データ反映のタイムラグ
• 実装・運用コスト
• 本番 DB への負荷

• サーバーレスで使いやすい
• Cloud SQL でのデータ変更がほぼリアルタイムで BigQuery に反映される
• CDC（Change Data Capture）データを使うため、本番 DB への負荷がない

改善したかった課題はどれくらい解決されたか

• 従来はデータベースにアクセスする必要があったデータにBigQueryから簡単にアクセスできるようになりました。
• 特に、リアルタイムでデータを同期できる点は大きな利点であり、これによりデータの可用性と分析効率が大幅に向上しました。

どのような成果が得られたか

• 過去の特定の時期や地域で発生した事象を簡単に取得できるようになりました。
• BigQuery でデータの推移や統計情報を視覚的に分析しやすくなりました。
• 本番 DB にアクセスすることなく、負荷をかけずにデータを取得できるようになりました。

ネットワーク構成

Datastream では、同期元のデータベースがパブリック IP アドレスからの接続を受け入れるように構成されている必要があります。しかし、FASTALERT の DB（Cloud SQL）はプライベートネットワーク内にあり、Datastream から直接読み取ることができません。

転送費用の読みが甘かった

なるべく低コストで運用したいため、事前にデータ行数が多いテーブルを転送対象から外しましたが、実際に運用してみると、想定よりも多い CDC（Change Data Capture）データの処理費用が発生していることがわかりました。レコード数が少ないにも関わらず転送量が大きいテーブルがあることが判明しました。このテーブルは変更が非常に頻繁で、大量の変更データが発生していたことが原因でした。

同期テーブルの設定

公式のドキュメント に従って設定してみましたが、Primary Key に関する記載に漏れがありました。
BigQuery の CDC は、Primary Key が前提条件となっています。転送先のテーブルに Primary Key が設定されていないとエラーが発生します。
また、max_staleness の設定も、手動で作り直したテーブルでは自前で設定しなければなりません。設定されていない場合、予想より多くのクエリスキャン量が発生し、かなり無駄な費用が発生してしまいました。

課題に関して、こちらのブログで詳細を書いています。

• プライベートネットワーク内の Cloud SQL に接続する場合は、リバースプロキシサーバの構成を工夫する必要があります。
• 転送費用を抑えるためには、行数だけでなく、変更頻度も考慮して転送対象テーブルを選定する必要があります。
• 同期テーブルのパーティション分割を手動で行う際は、Primary Key と max_staleness の設定を忘れないようにしてください。