AlloyDB 連携クエリで、BigQueryからプロジェクトを跨いだ先のAlloyDBに繋いでクエリを発行できるようにする
BigQuery と AlloyDB を連携させる方法はいくつかあります。
どれを選ぶと良いかは BigQuery の使われ方にもよりますが、いずれの手段も AlloyDB に保存されているデータを BigQuery で直接クエリすることができます。今回紹介する AlloyDB 連携クエリを使う方法は 1. の接続を前提としたものになります。
前提条件
- BigQuery と AlloyDB がそれぞれ別の Google Cloud プロジェクトに存在すること
- BigQuery と AlloyDB は同一ロケーションに存在すること
- BigQuery が有効化されていること
- BigQuery 利用者はデータ分析のためだけに SQL を組み立ててクエリを実行していること
- AlloyDB インスタンスが作成済みであること
- AlloyDB はプライベート IP による接続のみ許可していること
連携方法の選定
BigQuery と AlloyDB が別の Google Cloud プロジェクトに存在し、且つ Alloy DB の接続がプライベート IP のみの場合、Datastream による BigQuery へのデータ連携がしづらくなります。具体的には Datastream で構成する場合、以下のように Compute Engine を配置して VPC ネットワークピアリングを経由する構成にする必要があります。
ユアマイスターでは類似の構成を AlloyDB ではなく Cloud SQL で構築した経験がありますが、この構成は連携設定をするだけであるにも関わらず構築すべきリソースが多く、中継する Compute Engine のメンテナンスもする必要があるため運用コストがやや高いというデメリットがあります。
前提条件を変えずにもう少し手軽に連携する方法を Gemini の Deep Research に模索してもらった結果、 AlloyDB for PostgreSQL による外部接続を構築し、その上でAlloyDB 連携クエリを発行する方法が一番適していることが判明しました。AlloyDB for PostgreSQL による AlloyDB インスタンスの指定は以下のフォーマットでできます。
//alloydb.googleapis.com/projects/{PROJECT_ID}/locations/{REGION_ID}/clusters/{CLUSTER_NAME}/instances/{INSTANCE_ID}
これによりプロジェクトを跨いだ接続が可能だということが分かりました。但し、未検証ではありますが BigQuery と AlloyDB が同一ロケーションにあったから実現できる可能性があります（参考までに BigQuery → Cloud SQL の外部接続ではロケーションが同一である必要があります）。別々のロケーションだと接続できないかもしれませんのでご注意ください。
設定手順
基本的には AlloyDB for PostgreSQL に接続するのドキュメント通りに設定します。注意すべきは IAM の設定で、今回はクロスプロジェクトですので AlloyDB for PostgreSQL による外部接続構築後に自動作成されるサービスアカウント
service-{PROJECT_NUMBER}@gcp-sa-bigqueryconnection.iam.gserviceaccount.com
に付与する AlloyDB へのアクセス権
alloydb.instances.connect
を、 AlloyDB のあるプロジェクトにて実施する必要があります。この点だけご注意ください。
連携クエリ例
BigQuery Studio にて、 EXTERNAL_QUERY 関数を実行することで実現できます。
SELECT
u.id,
u.name,
rq.first_order_date
FROM
mydataset.users AS u
LEFT OUTER JOIN EXTERNAL_QUERY('projects/{BIGQUERY_PROJECT_ID}/locations/{REIGON_ID}/connections/{CONNECTION_NAME}', 'SELECT user_id, MIN(order_date) AS first_order_date FROM orders GROUP BY user_id') AS rq ON rq.user_id = u.id
GROUP BY 1, 2, 3
EXTERNAL_QUERY の実行結果は AS で別名にした上で BigQuery 上のデータセットと JOIN できるため、使い勝手が大変良いです。
まとめ
プロジェクトを跨いだ BigQuery と AlloyDB の連携は手間がかかるイメージを持っていましたが、手順内容と注意点を考慮して進めた結果、思いの外スムーズに実現することができました。
