聯合查詢簡介

本頁面將介紹如何使用聯合查詢，並提供從 BigQuery 查詢 Spanner、AlloyDB 和 Cloud SQL 資料的指南。

您可以使用聯合查詢將查詢陳述式傳送至 AlloyDB、Spanner 或 Cloud SQL 資料庫，並以臨時資料表的形式取得結果。聯合查詢會使用 BigQuery Connection API，與 AlloyDB、Spanner 或 Cloud SQL 建立連線。在查詢中，您可以使用 EXTERNAL_QUERY 函式，以該資料庫的 SQL 方言，將查詢陳述式傳送至外部資料庫。結果會轉換為 GoogleSQL 資料類型。

支援的資料儲存庫

您可以使用聯合查詢搭配下列資料儲存庫：

工作流程

  • 找出包含要查詢資料來源的 Google Cloud 專案。
  • bigquery.admin 使用者在 BigQuery 中建立連線資源。
  • 管理員使用者將使用連線資源的權限授予使用者 B。
    • 如果管理員和使用者 B 是同一人，則無須授予權限。
  • 使用者 B 使用新的 EXTERNAL_QUERY SQL 函式在 BigQuery 中編寫查詢。

聯合查詢的替代方案：外部資料表和資料集

如要查詢 Bigtable、Spanner、Cloud Storage、Google 雲端硬碟和 Salesforce Data Cloud 等營運資料庫，也可以使用外部資料表和資料集。您可以使用外部資料集和資料表檢視資料表及其結構定義，並查詢資料表，不必使用 EXTERNAL_QUERY SQL 函式。您不必將資料帶回 BigQuery，而且可以使用 BigQuery 語法，不必以 SQL 的特定 SQL 資料庫方言撰寫程式碼。

支援的地區

如需支援的地點清單，請參閱以下各節：

AlloyDB 和 Cloud SQL

只有同時支援外部資料來源和 BigQuery 的地區，才能使用聯合查詢。

您可以建立連線，並根據下列規則跨地區執行聯合式查詢：

單一地區

BigQuery 單一地區只能查詢相同地區中的資源。

舉例來說，如果資料集位於 us-east4，您可以查詢位於 us-east4 的 Cloud SQL 執行個體或 AlloyDB 執行個體。查詢處理位置是 BigQuery 單一區域。

多地區

BigQuery 的多地區可以查詢相同大型地理區域 (美國、歐盟) 中的任何資料來源地區。Cloud SQL 執行個體無法使用多區域位置，因為這些位置僅用於備份。

  • 在 BigQuery US 多地區執行的查詢，可以查詢美國地理區域中的任何單一地區，例如 us-central1、us-east4 或 us-west2。

  • 在 BigQuery EU 多地區執行的查詢，可以查詢歐盟成員國的任何單一地區，例如 europe-north1 或 europe-west3。

  • 查詢的執行位置必須與連線資源的位置相同。舉例來說，從美國多區域執行的查詢必須使用位於美國多區域的連線。

查詢效能取決於資料集與外部資料來源之間的鄰近程度。舉例來說，在美國多區域的資料集和 us-central1 的 Cloud SQL 執行個體之間進行聯合查詢時，速度很快。不過，如果您在美國多區域和 us-east4 的 Cloud SQL 執行個體之間執行相同的查詢，效能可能會降低。

查詢處理位置是多地區位置，即 US 或 EU。

Spanner

Spanner 支援區域和多區域設定。BigQuery 單一地區/多地區可以查詢任何支援的 Spanner 地區中的 Spanner 執行個體。詳情請參閱跨區域查詢。

資料類型對應關係

執行聯合查詢時，外部資料來源中的資料會轉換為 GoogleSQL 類型。詳情請參閱 Cloud SQL 聯合式查詢。

配額與限制

  • 跨區域聯合查詢。如果 BigQuery 查詢處理位置和外部資料來源位置不同，就會視為跨區域查詢。每個專案每日可執行高達 1 TB 的跨地區查詢。以下是跨區域查詢的範例。
    • Cloud SQL 執行個體位於 us-west1，而 BigQuery 連線則位於美國多區域位置。BigQuery 查詢處理位置為 US。
  • 配額。使用者應控制外部資料來源 (例如 Cloud SQL 或 AlloyDB) 中的查詢配額，聯合式查詢沒有額外的配額設定。如要確實隔離工作負載，建議您僅查詢資料庫唯讀備用資源。
  • 允許的計費位元組上限。這個欄位不支援聯合式查詢。系統無法事先估算實際執行聯合式查詢之後會產生費用的位元組數。
  • 連線數量。聯合式查詢最多可以建立 10 個不重複的連線。
  • Cloud SQL MySQL 和 PostgreSQL。須遵守配額和限制。

限制

聯邦查詢必須遵循以下限制：

  • 效能。聯合式查詢的執行速度可能低於僅查詢 BigQuery 儲存空間。BigQuery 必須等待來源資料庫執行外部查詢，並暫時將資料從外部資料來源移至 BigQuery。此外，來源資料庫可能不適合用於較為複雜的分析查詢。

    查詢效能也會因資料集與外部資料來源的距離而異。詳情請參閱「支援的區域」。

  • 聯合查詢是唯讀查詢。在來源資料庫中執行的外部查詢必須為唯讀。因此不支援 DML 或 DDL 陳述式。

  • 不支援的資料類型。如果外部查詢含有 BigQuery 不支援的資料類型，查詢作業就會立即失敗。您可以將不支援的資料類型轉換為支援的其他資料類型。

  • 客戶自行管理的加密金鑰 (CMEK)。您必須分別為 BigQuery 和外部資料來源設定 CMEK。如果您將來源資料庫設定為使用 CMEK，但 BigQuery 未使用，則包含聯合查詢結果的暫時資料表會以 Google-owned and Google-managed encryption key加密。

定價

  • 如果您採用以量計價模式，透過 BigQuery 執行聯合查詢時，系統會針對外部查詢傳回的位元組數向您收費。詳情請參閱「以量計價的分析定價」。

  • 如果您使用 BigQuery 版本，系統會根據您使用的運算單元數量收費。詳情請參閱「容量運算定價」。

SQL 下推

聯盟查詢會受到稱為 SQL 下推的最佳化技術影響。這類函式會將篩選等作業委派給外部資料來源，而不是在 BigQuery 中執行，藉此提升查詢效能。減少從外部資料來源傳輸的資料量，可縮短查詢執行時間並降低成本。SQL 下推包括資料欄修剪 (SELECT 子句) 和篩選器下推 (WHERE 子句)。

使用 EXTERNAL_QUERY 函式時，SQL 下推作業會重寫原始查詢。在下列範例中，EXTERNAL_QUERY 函式用於與 Cloud SQL 資料庫通訊：

SELECT COUNT(*)
FROM (
  SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID", "select * from operations_table")
  )
WHERE a = 'Y' AND b NOT IN ('COMPLETE','CANCELLED');

將 CONNECTION_ID 替換為 BigQuery 連線的 ID。

如果沒有 SQL 下推，系統會將下列查詢傳送至 Cloud SQL：

SELECT *
FROM operations_table

執行這項查詢時，即使只需要部分資料列和資料欄，系統仍會將整個資料表傳回 BigQuery。

使用 SQL 下推功能時，系統會將下列查詢傳送至 Cloud SQL：

SELECT `a`, `b`
FROM (
  SELECT * FROM operations_table) t
WHERE ((`a` = 'Y') AND (NOT `b` IN ('COMPLETE', 'CANCELLED')))

執行這項查詢時，系統只會將兩個資料欄和符合篩選條件述詞的資料列傳回 BigQuery。

使用 Spanner 外部資料集執行聯合查詢時，也會套用 SQL 下推。

您可以在查詢計畫中檢查已套用的下推 (如有)。

限制

SQL 下推作業有各種限制，視外部資料來源和查詢資料的方式而異。

使用 EXTERNAL_QUERY 時的查詢同盟限制

  • SQL 下推僅適用於 SELECT * FROM T 形式的聯合查詢。
  • 僅支援資料欄修剪和篩選條件下推。具體來說，系統不支援運算、聯結、限制、排序依據和彙整下推。
  • 如要進行篩選器下推，常值必須是下列其中一種型別：BOOL、INT64、FLOAT64、STRING、DATE、DATETIME、TIMESTAMP。不支援結構體常值。
  • 只有 BigQuery 和目標資料庫都支援的函式，才會套用 SQL 函式下推。
  • SQL 下推僅支援 AlloyDB、Cloud SQL 和 Spanner。
  • SAP Datasphere 不支援 SQL 下推。

使用 Spanner 外部資料集時，查詢聯盟的限制

  • 系統支援資料欄修剪、篩選、計算和部分匯總下推。具體來說，系統不支援依匯總聯結、限制和排序。
  • 如要篩選條件下推，常值必須是下列其中一種型別：BOOL、INT64、FLOAT64、STRING、DATE、DATETIME、TIMESTAMP、BYTE 或陣列。不支援結構體常值。
  • 只有 BigQuery 和 Spanner 都支援的函式，才會套用 SQL 函式下推。

各資料來源支援的函式

下表列出各資料來源支援的 SQL 函式。SAP Datasphere 不支援任何函式。

Cloud SQL MySQL

  • 邏輯運算子： AND、OR、NOT。
  • 比較運算子： =、>、>=、<、<=、<>、IN、BETWEEN、IS NULL。
  • 算術運算子： +、-、* (僅適用於 INT64 和 FLOAT64)。

Cloud SQL PostgreSQL 和 AlloyDB

  • 邏輯運算子： AND、OR、NOT。
  • 比較運算子： =、>、>=、<、<=、<>、IN、BETWEEN、IS NULL。
  • 算術運算子： +、-、*、/ (僅適用於 INT64、FLOAT64 和 DATE 類型，但 DATE 減法除外)。

Spanner - PostgreSQL 方言

  • 邏輯運算子： AND、OR、NOT。
  • 比較運算子： =、>、>=、<、<=、<>、IN、BETWEEN、IS NULL。
  • 算術運算子： +、-、*、/ (僅適用於 INT64、FLOAT64、NUMERIC)。

Spanner - GoogleSQL 方言

  • 邏輯運算子： AND、OR、NOT。
  • 比較運算子： =、>、>=、<、<=、<>、IN、BETWEEN、IS NULL。
  • 算術運算子： +、-、*、/ (僅適用於 INT64、FLOAT64、NUMERIC)。
  • 安全算術運算子： SAFE_ADD、SAFE_SUBTRACT、SAFE_MULTIPLY、SAFE_DIVIDE (僅適用於 INT64、FLOAT64、NUMERIC)。
  • 使用外部資料集時，請注意以下事項：
    • 運算下推
    • Partial Aggregate 下推，
    • 字串函式，
    • 數學函式
    • Cast 函式，
    • 陣列函式。

使用外部資料來源中的對照順序

外部資料來源可能在資料欄上設定了排序規則 (例如不區分大小寫)。執行聯合查詢時，遠端資料庫會考量已設定的對照順序。

請參考以下範例，假設您有一個 flag 欄，且外部資料來源中含有不區分大小寫的對照順序：

SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID", "select * from operations_table where flag = 'Y'")

將 CONNECTION_ID 替換為 BigQuery 連線的 ID。

上述查詢會傳回 flag 為 y 或 Y 的資料列，因為查詢是在外部資料來源上執行。

不過，如果是與 Cloud SQL、SAP Datasphere 或 AlloyDB 資料來源的查詢聯合，如果您在主要查詢中新增篩選器，查詢會在 BigQuery 端執行，並使用預設排序規則。請參閱以下查詢：

SELECT * FROM
  (
    SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID", "select * from operations_table")
  )
WHERE flag = 'Y'

由於 BigQuery 預設會區分大小寫的排序規則，因此上述查詢只會傳回標記為 Y 的資料列，並篩除標記為 y 的資料列。如要讓 WHERE 子句不區分大小寫，請在查詢中指定排序規則：

SELECT * FROM
  (
    SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID", "select * from operations_table")
  )
WHERE COLLATE(flag, 'und:ci') = 'Y'

後續步驟