AlloyDB 聯合查詢

BigQuery 的聯合查詢功能可讓您即時查詢儲存在 AlloyDB for PostgreSQL 的資料，不必複製或移動資料。您可以使用 EXTERNAL_QUERY 函式，將 SQL 查詢傳送至 AlloyDB，並將結果與 BigQuery 資料表彙整。

事前準備

查詢資料

如要從 GoogleSQL 查詢傳送聯合查詢至 AlloyDB，請使用 EXTERNAL_QUERY 函式。

假設您在 BigQuery 中儲存客戶資料表，在 AlloyDB 中儲存銷售資料表，並想在單一查詢中彙整這兩個資料表。下列範例會對 AlloyDB 資料表 orders 執行聯合查詢，並將結果與 BigQuery 資料表 mydataset.customers 彙整。

查詢範例包括 3 個部分：

  1. 在 AlloyDB 資料庫中執行外部查詢 SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id，透過 EXTERNAL_QUERY 函式取得每位顧客的第一筆訂單日期。

  2. 依 customer_id 將外部查詢結果資料表與 BigQuery 中的客戶資料表彙整。

  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. 前往「Logs Explorer」頁面。

    前往「Logs Explorer」

  2. 在「查詢」分頁中，輸入下列查詢：

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

  3. 點選「執行查詢」。

    如果有可用於 BigQuery 聯合查詢的記錄，「Query results」(查詢結果) 中會顯示類似下方的記錄清單。

    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) 角色。詳情請參閱「授予服務帳戶存取權」。

後續步驟