Cloud SQL と BigQuery の同期における Datastream の活用
株式会社JX通信社 / mapler
メンバー / 機械学習エンジニア / 従業員規模: 11名〜50名 / エンジニア組織: 11名〜50名
| ツールの利用規模 | ツールの利用開始時期 |
|---|---|
| 10名以下 | 2024年8月 |
| ツールの利用規模 | 10名以下 |
|---|---|
| ツールの利用開始時期 | 2024年8月 |
アーキテクチャ
アーキテクチャの意図・工夫
リバースプロキシ(VPC)を利用することで、Datastream がプライベートネットワーク内の Cloud SQL にアクセスする構成図
導入の背景・解決したかった問題
導入背景
ツール導入前の課題
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 にアクセスすることなく、負荷をかけずにデータを取得できる
比較検討したサービス
比較した軸
- データ反映のタイムラグ
- 実装・運用コスト
- 本番 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 の設定も、手動で作り直したテーブルでは自前で設定しなければなりません。設定されていない場合、予想より多くのクエリスキャン量が発生し、かなり無駄な費用が発生してしまいました。
課題に関して、こちらのブログで詳細を書いています。
導入に向けた社内への説明
上長・チームへの説明
主に費用面が懸念されていたので、あらかじめ DB の容量に基づき、過去データの転送にかかる初期費用および月額転送費用を試算しました。算出された実費用を上長に報告し、承認を得て導入を決定しました。
活用方法
転送されたデータは下記の場面で利用されています
- BigQuery でのデータの推移や統計情報の視覚的な分析
- 特定期間のデータ調査
- マスターデータを機械学習での利用
よく使う機能
- Cloud SQL から BigQuery へのストリーミング同期
ツールの良い点
- サーバーレスで使いやすい。
- データをほぼリアルタイムに同期できる。
- 本番 DB にアクセスすることなく、負荷をかけずにデータを取得できる。
ツールの課題点
- (利用当時)ドキュメントが不十分でわかりにくかった部分がある(ネットワーク構成、パーティション分割の手順など)。
- (ツールの問題ではないですが)変更が非常に頻繁なテーブルでは、想定よりも多い CDC データの処理費用が発生する可能性がある。
ツールを検討されている方へ
- プライベートネットワーク内の Cloud SQL に接続する場合は、リバースプロキシサーバの構成を工夫する必要があります。
- 転送費用を抑えるためには、行数だけでなく、変更頻度も考慮して転送対象テーブルを選定する必要があります。
- 同期テーブルのパーティション分割を手動で行う際は、
Primary Keyとmax_stalenessの設定を忘れないようにしてください。
株式会社JX通信社 / mapler
メンバー / 機械学習エンジニア / 従業員規模: 11名〜50名 / エンジニア組織: 11名〜50名
よく見られているレビュー
株式会社JX通信社 / mapler
メンバー / 機械学習エンジニア / 従業員規模: 11名〜50名 / エンジニア組織: 11名〜50名
レビューしているツール
目次
- アーキテクチャ
- 導入の背景・解決したかった問題
- 活用方法


