AlloyDB 連携クエリ

データ アナリストは、連携クエリを使用して、BigQuery から AlloyDB for PostgreSQL のデータにクエリを実行できます。

BigQuery AlloyDB 連携を使用すると、データのコピーや移動を行うことなく、AlloyDB にあるデータのクエリを BigQuery からリアルタイムで実行できます。

始める前に

  • BigQuery 管理者が AlloyDB 接続を作成しており、それを共有していることを確認してください。

  • AlloyDB インスタンスをクエリするのに必要な権限を取得するには、プロジェクトで BigQuery 接続ユーザー（roles/bigquery.connectionUser）の IAM ロールを付与するように管理者へ依頼してください。ロールの付与の詳細については、アクセス権の管理をご覧ください。

    必要な権限は、カスタムロールや他の事前定義ロールから取得することもできます。

データのクエリ

GoogleSQL クエリから AlloyDB に連携クエリを送信するには、EXTERNAL_QUERY 関数を使用します。

たとえば、販売テーブルを AlloyDB に保存しながら BigQuery に顧客テーブルを保存し、1 つのクエリで 2 つのテーブルを結合するとします。次の例では、orders という名前の AlloyDB テーブルに連携クエリを実行し、mydataset.customers という名前の BigQuery テーブルに結果を結合します。

このサンプルクエリは 3 つの部分から構成されています。

  1. AlloyDB データベースで外部クエリ SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id を実行し、EXTERNAL_QUERY 関数によって各お客様の最初の注文日を取得します。

  2. 外部クエリの結果テーブルを BigQuery の顧客テーブルと customer_id で結合します。

  3. 最終的な結果セットで顧客情報と最初の注文日を選択します。

SELECT c.customer_id, c.name, rq.first_order_date
FROM mydataset.customers AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
  'us.connection_id',
  '''SELECT customer_id, MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY customer_id''') AS rq ON rq.customer_id = c.customer_id
GROUP BY c.customer_id, c.name, rq.first_order_date;

AlloyDB テーブル スキーマを表示する

EXTERNAL_QUERY 関数を使用して information_schema テーブルにクエリを実行すると、データベースのメタデータにアクセスできます。たとえば、データベース内のすべてのテーブルを一覧表示することや、テーブル スキーマを表示することが可能です。詳細については、PostgreSQL information_schema テーブルをご覧ください。

-- List all tables in a database.
SELECT * FROM EXTERNAL_QUERY("region.connection_id",
"select * from information_schema.tables;");

-- List all columns in a table.
SELECT * FROM EXTERNAL_QUERY("region.connection_id",
"select * from information_schema.columns where table_name='x';");

BigQuery 連携クエリをトラッキングする

AlloyDB に対して連携クエリを実行すると、BigQuery はクエリに次のようなコメントを追加します。

/* Federated query from BigQuery. Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID. */

クエリ使用量のログをモニタリングしている場合は、次のアノテーションを使用して、BigQuery からのクエリを特定できます。

  1. [ログ エクスプローラ] ページに移動します。

    ログ エクスプローラに移動

  2. [クエリ] タブに次のクエリを入力します。

    resource.type="alloydb.googleapis.com/Instance"
textPayload=~"Federated query from BigQuery"

  3. [クエリを実行] をクリックします。

    BigQuery 連携クエリで使用可能なレコードがある場合、クエリ結果に次のようなレコードのリストが表示されます。

    YYYY-MM-DD hh:mm:ss.millis UTC [3210064]: [4-1]
db=DATABASE, user=USER_ACCOUNT
STATEMENT: SELECT 1 FROM (SELECT FROM company_name_table) t;
/* Federated query from BigQuery.
Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID
*/

YYYY-MM-DD hh:mm:ss.millis UTC [3210532]: [2-1]
db=DATABASE, user=USER_ACCOUNT
STATEMENT: SELECT "company_id", "company type_id" FROM
(SELECT FROM company_name_table) t;
/* Federated query from BigQuery.
Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID
*/

    Cloud Logging の詳細については、Cloud Logging をご覧ください。

トラブルシューティング

このセクションでは、連携クエリを AlloyDB に送信する際に発生する可能性のあるエラーと、考えられるトラブルシューティングの解決策について説明します。

問題: データベース サーバーに接続できない: Invalid table-valued function EXTERNAL_QUERY Connect to PostgreSQL server failed: server closed the connection unexpectedly. This probably means the server terminated abnormally before or while processing the request.

解決策: AlloyDB への接続を作成するときに、有効な認証情報を使用し、すべての前提条件を満たしていることを確認します。AlloyDB への接続が作成されたとき自動で作成されるサービス アカウントに、AlloyDB クライアント（roles/alloydb.client）ロールがあるかどうかを確認します。詳細については、サービス アカウントにアクセス権を付与するをご覧ください。

次のステップ