執行 PostgreSQL 聯合查詢

資料分析師可以直接從 BigQuery 即時查詢 PostgreSQL 中的關聯資料，不必複製或移動資料。

BigQuery PostgreSQL 聯合查詢會使用 BigQuery Connection API 連線至 PostgreSQL 資料庫引擎。接著，您可以使用 EXTERNAL_QUERY 函式，將 SQL 查詢直接傳送至 PostgreSQL 執行，結果會直接串流傳回。

限制

PostgreSQL 聯合查詢有下列限制：

  • 您只能透過 EXTERNAL_QUERY 函式執行唯讀查詢。不支援資料定義語言 (DDL) 和 data manipulation language (DML) 陳述式。
  • 只能透過 EXTERNAL_QUERY 函式存取聯合。不支援建立直接對應至 PostgreSQL 資料表的 BigQuery 外部資料表或永久資料集。
  • 聯合查詢的延遲時間可能明顯高於直接在 PostgreSQL 執行查詢。
  • 基礎連線服務可能會讓閒置連線進入休眠狀態。在閒置一段時間後，使用 PostgreSQL 連線的第一個查詢可能需要超過一分鐘才能執行，因為連接器需要初始化。
  • 不支援 DNS 對接。您必須使用虛擬私有雲網路中的 IP 位址或可完整解析的主機名稱連線。
  • PostgreSQL SQL 查詢必須為包含函式結果或運算式的任何資料欄 (例如 SELECT COUNT(*) AS total_count) 指定明確的資料欄別名。未加上別名的運算式會產生含有特殊字元的資料欄名稱，這在 BigQuery 資料表結構定義中無效。
  • 如果查詢專案中的 Compute Engine API 使用量受到 VPC Service Controls 限制，且連線使用網路連結，聯合查詢就會失敗。請務必設定適當的 VPC Service Controls 輸入和輸出規則。

  • PostgreSQL 不支援其他 SQL 下推。外部 BigQuery 查詢中的篩選器和匯總不會向下推送至 EXTERNAL_QUERY 函式中的內部查詢。您必須直接在內部 PostgreSQL SQL 陳述式中指定述詞和資料欄投影，限制透過網路傳輸的資料列和資料欄。

  • 僅支援 PostgreSQL 資料庫使用者名稱和密碼驗證。不支援第三方識別資訊提供者、Kerberos 和雲端服務供應商 IAM 驗證。

  • 如果查詢選取 16 位元整數資料欄 (int2、smallint、smallserial) 或單精度浮點數資料欄 (float4、real)，CREATE TABLE ... AS SELECT (CTAS) 陳述式會失敗，並顯示 Unsupported type INT32 或 Unsupported type FLOAT 錯誤。如要將查詢結果儲存至新的 BigQuery 資料表，請在查詢中明確將整數資料欄轉換為 INT64，並將浮點數資料欄轉換為 FLOAT64：

    CREATE TABLE mydataset.mytable AS
SELECT
CAST(small_col AS INT64) AS small_col,
CAST(real_col AS FLOAT64) AS real_col,
...
FROM EXTERNAL_QUERY(...);

    使用 INSERT INTO <table> SELECT ... 插入現有資料表時，不需手動轉換即可成功，因為 BigQuery 會自動擴大 INT32 和 FLOAT 值，以符合目標資料表的 INT64 和 FLOAT64 欄。

  • time [ without time zone ] 和 time with time zone (timetz) 都會將小數秒 (微秒) 值截斷為整數秒。如需微秒精確度，請在 EXTERNAL_QUERY 陳述式中，將資料欄格式化或轉換為 text 或 varchar。

  • 所有 PostgreSQL numeric 和 decimal 資料欄都會對應至 BIGNUMERIC(76, 38)。如果資料欄的小數位數大於 38，則會四捨五入至小數點後 38 位，而超過 BIGNUMERIC 範圍的值 (超過 38 個完整整數位數) 則會失敗，並顯示超出範圍錯誤。此外，系統不支援特殊值 NaN、Infinity 和 -Infinity，且會在執行階段失敗。如要查詢整數部分較大、小數精確度較高或特殊值的數字，請在 EXTERNAL_QUERY 陳述式中將資料欄轉換為 text。

  • 對應至 BigQuery STRING 時，固定長度的 character(n)/char(n) (bpchar) 值會保留結尾空格，最長可達 n。

  • 由於歷史上曾改用公曆，1582 年 10 月 15 日之前的日期和時間戳記會出現 10 天的日曆偏移。在 EXTERNAL_QUERY 陳述式中，將歷來日期轉換為 text，以保留確切日期。

支援的 PostgreSQL 環境和版本

查詢聯合支援下列部署環境中的 PostgreSQL 資料庫：

  • 可透過公開 IP 位址或網路連結 (透過 Cloud VPN 或 Cloud Interconnect) 存取的雲端託管或地端部署 PostgreSQL 資料庫
  • 在 Compute Engine 或其他基礎架構上自行託管的 PostgreSQL 執行個體
  • PostgreSQL 16 (建議) 和 PostgreSQL 15

事前準備

確認 BigQuery 管理員已建立 PostgreSQL 連線，並與您共用。

必要的角色

如要取得查詢 PostgreSQL 所需的權限，請要求管理員授予您專案的 BigQuery 連線使用者 (roles/bigquery.connectionUser) IAM 角色。如要進一步瞭解如何授予角色，請參閱「管理專案、資料夾和組織的存取權」。

您或許也能透過自訂角色或其他預先定義的角色，取得必要權限。

查詢資料

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

以下範例是聯合查詢，會將 BigQuery 中名為 mydataset.customers 的客戶記錄，與 PostgreSQL 中的交易銷售訂單彙整：

SELECT
  c.customer_id,
  c.name,
  fo.first_order_date
FROM
  mydataset.customers AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
  'projects/my-project/locations/us-central1/connections/postgres_prod_conn',
  '''SELECT customer_id, MIN(order_date) AS first_order_date
     FROM orders
     GROUP BY customer_id''') AS fo
  ON fo.customer_id = c.customer_id
GROUP BY
  c.customer_id,
  c.name,
  fo.first_order_date;

查看 PostgreSQL 資料表結構定義

您可以使用 EXTERNAL_QUERY 查詢 PostgreSQL information_schema 資料表，擷取資料庫中繼資料：

列出結構定義中的所有資料表

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  '''SELECT table_schema, table_name, table_type
     FROM information_schema.tables
     WHERE table_schema = 'SCHEMA_NAME'
     ORDER BY table_name''');

查看資料表中的資料欄和資料類型

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  '''SELECT column_name, data_type, is_nullable
     FROM information_schema.columns
     WHERE table_schema = 'SCHEMA_NAME' AND table_name = 'TABLE_NAME'
     ORDER BY ordinal_position''');

資料類型對應

執行聯合查詢時，BigQuery 會根據下表，將 PostgreSQL 資料轉換為 GoogleSQL 資料類型：

PostgreSQL 資料類型 BigQuery 資料類型
布林值類型
bool、boolean BOOL
數值類型
int2、smallint、smallserial INT64 (互動式查詢；請參閱CREATE TABLE ... AS SELECT的限制)
int4、integer、serial INT64
int8、bigint、bigserial INT64
numeric、decimal BIGNUMERIC(76, 38)
float4、real FLOAT64 (互動式查詢；請參閱CREATE TABLE ... AS SELECT的限制)
float8、double precision FLOAT64
日期和時間類型
date DATE
timestamp [ without time zone ] DATETIME
timestamp with time zone、timestamptz TIMESTAMP
time [ without time zone ] TIME
time with time zone、timetz TIME
字元和文字類型
character(n)、char(n)、bpchar STRING
character varying(n)、varchar(n) STRING
text STRING

定價

執行聯合查詢的費用取決於下列三項因素：

  • 在 PostgreSQL 執行查詢的運算費用 (由 PostgreSQL 資料庫供應商或託管環境收取)
  • 將查詢結果從 PostgreSQL 轉移至 BigQuery 的頻寬費用 (資料庫代管服務供應商收取的輸出網路費用)
  • 在 BigQuery 執行查詢的運算費用

任何 PostgreSQL 運算和輸出費用，都會由 PostgreSQL 供應商或基礎架構主機直接收取，而非 Google。為限制頻寬成本，建議您在 EXTERNAL_QUERY 陳述式中編寫查詢，排除所有不需要用來計算最終結果的資料欄和資料列。

在 BigQuery 執行聯合查詢不會產生額外費用。如要進一步瞭解 BigQuery 定價，請參閱「定價」一文。

如果連線使用 Google Cloud 網路元件，可能也適用下列定價：

後續步驟