AlloyDB 聯合查詢

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

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

事前準備

查詢資料

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

假設您在 BigQuery 中儲存客戶資料表，同時在 AlloyDB 中儲存銷售資料表，並希望透過單次查詢彙整這兩個資料表。以下範例會對名為 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. 依據 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. 前往「記錄檔探索工具」頁面。

    前往記錄檔探索工具

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

後續步驟