AlloyDB 聯合查詢

資料分析師可以使用聯合查詢，從 BigQuery 查詢 PostgreSQL 適用的 AlloyDB 資料。

BigQuery AlloyDB 連結可讓 BigQuery 即時查詢 AlloyDB 中的資料，而且無須複製或移動資料。

事前準備

查詢資料

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

假設您在 BigQuery 中儲存客戶資料表，同時在 AlloyDB 中儲存銷售資料表，並希望透過單一查詢彙整這兩個資料表。以下範例會對名為 orders 的 AlloyDB 資料表執行聯合查詢，並將結果與名為 mydataset.customers 的 BigQuery 資料表彙整。

查詢範例包括 3 個部分：

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

  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. 在「Query」分頁中，輸入下列查詢：

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

後續步驟