BigQueryにDBデータを集約する
やりたいこと
複数のサービスをGCP上で運用しており、
各サービスのデータストアにはCloud SQLを使用しています。
各サービスのDBのデータ集計用のGCPプロジェクト（Data Store）のBigqueryに集約したいという要望がありました。
そのとき実践した連携方法を共有します。
概要図としては以下です。
前提
- gcloudコマンドがインストールされていること（bqコマンドも内包されている)
- 以下の権限を持ったアカウントでbqコマンドを叩けること
- 転送元の権限が BigQuery 管理者 であること
- 転送先の権限が データセットへの書き込み権限を持つこと
手順
以下の手順を連携したいサービスごとに行う必要があります。
繰り返し作業になるのでshellスクリプトを定義しておくと作業しやすいかと思います。
- 各サービスのGCPプロジェクトBigQueryにCloud SQLへの接続を追加
- 各サービスのbigqueryにviewを作成（連携したいテーブル分行う）
- 転送設定を転送先のbigqueryに反映（連携したいテーブル分行う）
各サービスのGCPプロジェクトBigQueryにCloud SQLへの接続を追加
BigQueryの管理画面または、bqコマンドで設定できます。
今回は管理画面から操作する形で追加しました。
各サービスのBig Queryにviewを作成
前段階で、DBを外部接続することで、EXTERNAL_QUERY関数を使って、Big Query上からSQLを実行することができます。
以下はusersテーブルに対してSQLを実行した例です。
CONNECTION_IDには前段階で設定したものを使用します。
-
viewとは
https://cloud.google.com/bigquery/docs/views-intro?hl=ja
ビューは SQL クエリによって定義される仮想テーブルです。ビューの作成後は、テーブルをクエリするのと同じ方法でビューをクエリします。ユーザーがビューをクエリすると、クエリ結果には、ビューを定義するクエリで指定されたテーブルとフィールドのデータのみが含まれます。
DBのデータをviewとして定義することで、その後の工程でもDBを直接参照せず、データを参照することができます。
SELECT * FROM EXTERNAL_QUERY(
"projects/PROJECT_ID/location/LOCATION/connections/CONNECTION_ID", """
SELECT
id
, name
, organization_id
, created_at
, updated_at
FROM users
""");
転送設定を転送先のBigQueryに反映
以下のようなコマンドをテーブルごとに打つことで、定期的にBig Queryにデータを同期することができます。
bq query \
--project_id="data-store" \
--display_name="project a" \
--destination_table=project_a.$table \
--schedule="every 1 hours" \
--replace \
--location=asia-northeast1 \
--nouse_legacy_sql \
最後に連携先BigQueryの「スケジューリングされたクエリ」にこのように表記していれば完成です。
Discussion