🔖

BigQueryにDBデータを集約する

2022/10/28に公開約2,100字

やりたいこと

複数のサービスをGCP上で運用しており、
各サービスのデータストアにはCloud SQLを使用しています。
各サービスのDBのデータ集計用のGCPプロジェクト(Data Store)のBigqueryに集約したいという要望がありました。

そのとき実践した連携方法を共有します。
概要図としては以下です。

前提

  • gcloudコマンドがインストールされていること(bqコマンドも内包されている)
  • 以下の権限を持ったアカウントでbqコマンドを叩けること
    • 転送元の権限が BigQuery 管理者 であること
    • 転送先の権限が データセットへの書き込み権限を持つこと

手順

以下の手順を連携したいサービスごとに行う必要があります。

繰り返し作業になるのでshellスクリプトを定義しておくと作業しやすいかと思います。

  • 各サービスのGCPプロジェクトBigQueryにCloud SQLへの接続を追加
  • 各サービスのbigqueryにviewを作成(連携したいテーブル分行う)
  • 転送設定を転送先のbigqueryに反映(連携したいテーブル分行う)

各サービスのGCPプロジェクトBigQueryにCloud SQLへの接続を追加

https://cloud.google.com/bigquery/docs/cloud-sql-federated-queries?hl=ja#setting-up-cloud-sql-database-connections

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

ログインするとコメントできます