Spanner 聯合查詢

資料分析師可以使用聯合查詢，從 BigQuery 查詢 Spanner 中的資料。

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

您可以透過下列兩種方式查詢 Spanner 資料：

• 建立 Spanner 外部資料集。
• 使用 EXTERNAL_QUERY 函式。

使用外部資料集

查詢 Spanner 資料表最簡單的方法是建立外部資料集。建立外部資料集後，對應 Spanner 資料庫中的資料表就會顯示在 BigQuery 中，您可以在查詢中使用這些資料表，例如聯結、聯集或子查詢。不過，系統不會將資料從 Spanner 移至 BigQuery 儲存空間。

建立外部資料集後，您不需要建立連線即可查詢 Spanner 資料。

使用 EXTERNAL_QUERY 函式

與其他聯邦資料庫一樣，您也可以使用 EXTERNAL_QUERY 函式查詢 Spanner 資料。如果您想查詢使用 PostgreSQL 方言的 Spanner 資料庫，或想進一步控管連線參數，這項功能可能非常實用。

事前準備

• 確認 BigQuery 管理員已建立 Spanner 連線，並與您共用。請參閱「 選擇合適的連結」。
• 如要取得查詢 Spanner 執行個體所需的權限，請要求管理員授予您 BigQuery 連線使用者 (roles/bigquery.connectionUser) Identity and Access Management (IAM) 角色。此外，您還需要請管理員授予下列其中一項權限：
  • 如果您是精細存取權控管使用者，則需要存取資料庫角色，該角色必須對查詢中的所有 Spanner 結構定義物件具備 SELECT 權限。
  • 如果您不是精細存取權控管使用者，則需要 Cloud Spanner 資料庫讀取者 (roles/spanner.databaseReader) IAM 角色。

  如要瞭解如何授予 IAM 角色，請參閱「 管理專案、資料夾和機構的存取權」。如要瞭解精細的存取權控管機制，請參閱「關於精細的存取權控管機制」。

選擇合適的連線方式

如果您是 Spanner 精細存取權控管使用者，使用 EXTERNAL_QUERY 函式執行聯邦查詢時，必須使用指定資料庫角色的 Spanner 連線。然後，您透過這項連線執行的所有查詢都會使用該資料庫角色。

如果您使用的連線未指定資料庫角色，則必須具備「事前準備」一節中列出的 IAM 角色。

查詢資料

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

視資料庫指定的方言而定，以 GoogleSQL 或 PostgreSQL 撰寫 Spanner 查詢。

下列範例會對名為 orders 的 Spanner 資料庫執行聯合查詢，並將結果與名為 mydataset.customers 的 BigQuery 資料表聯結。

SELECT c.customer_id, c.name, rq.first_order_date
FROM mydataset.customers AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
  'my-project.us.example-db',
  '''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;

Spanner Data Boost

Data Boost 是全代管的無伺服器功能，可為支援的 Spanner 工作負載提供獨立運算資源。Data Boost 可讓您執行分析查詢和資料匯出作業，對已佈建 Spanner 執行個體的現有工作負載幾乎沒有影響。Data Boost 可讓您使用獨立的運算能力 (與佈建的執行個體分開)，執行聯合查詢，避免影響 Spanner 上的現有工作負載。當您執行複雜的臨時查詢，或想處理大量資料，但不想影響現有的 Spanner 工作負載時，Data Boost 最能發揮效用。使用 Data Boost 執行聯邦查詢可大幅降低 CPU 消耗量，在某些情況下，還能縮短查詢延遲時間。

事前準備

如要取得啟用 Data Boost 存取權所需的權限，請要求管理員授予您 Spanner 資料庫的「Cloud Spanner 資料庫讀取者 (使用 Data Boost)」 (roles/spanner.databaseReaderWithDataBoost) IAM 角色。如要進一步瞭解如何授予角色，請參閱「管理專案、資料夾和機構的存取權」。

這個預先定義的角色具備 spanner.databases.useDataBoost權限，這是啟用 Data Boost 存取權的必要條件。

您或許還可透過自訂角色或其他預先定義的角色取得這項權限。

啟用 Data Boost

使用外部資料集時，系統一律會使用 Data Boost，因此不需要手動啟用。

如要在 EXTERNAL_QUERY 查詢中使用 Data Boost，您必須在建立查詢使用的連線時啟用這項功能。

並行讀取資料

Spanner 可以將特定查詢分成較小的片段 (稱為「分區」)，並平行擷取分區。詳情請參閱 Spanner 說明文件中的「平行讀取資料」。

不過，這項選項僅適用於符合下列任一條件的查詢：

• 執行計畫中的第一個運算子是 distributed union 運算子。

• 執行計畫中沒有 distributed union 運算子。

其他查詢則會傳回錯誤。如要查看 Spanner 查詢的查詢執行計畫，請參閱「瞭解 Spanner 如何執行查詢」。

使用外部資料集執行聯合查詢時，系統一律會使用「平行讀取資料」選項。

如要在使用 EXTERNAL_QUERY 時啟用平行讀取，請在建立連線時啟用這項功能。

管理查詢執行優先順序

使用 EXTERNAL_QUERY 函式執行聯合查詢時，您可以指定 query_execution_priority 選項，為個別查詢指派優先順序 (high、medium 或 low)：

SELECT *
FROM EXTERNAL_QUERY(
  'my-project.us.example-db',
  '''SELECT customer_id, MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY customer_id''',
  '{"query_execution_priority":"high"}');

預設優先順序為 medium。

優先順序為 high 的查詢會與交易流量競爭。 優先順序為 low 的查詢會盡量執行，但可能會遭到背景負載 (例如排定的備份作業) 搶占。

使用外部資料集執行聯合查詢時，所有查詢一律具有 medium 優先順序。

查看 Spanner 資料表結構定義

如果您使用外部資料集，BigQuery Studio 會直接顯示 Spanner 資料表，您也可以查看這些資料表的結構定義。

不過，您也可以在不定義外部資料集的情況下查看結構定義。您也可以使用 EXTERNAL_QUERY 函式查詢 information_schema 檢視區塊，藉此存取資料庫中繼資料。以下範例會傳回資料表 MyTable 中資料欄的相關資訊：

SELECT *
FROM EXTERNAL_QUERY(
  'my-project.us.example-db',
  '''SELECT t.column_name, t.spanner_type, t.is_nullable
    FROM information_schema.columns AS t
    WHERE
      t.table_catalog = ''
      AND t.table_schema = ''
     AND t.table_name = 'MyTable'
    ORDER BY t.ordinal_position
  ''');

SELECT * from EXTERNAL_QUERY(
 'my-project.us.postgresql.example-db',
  '''SELECT t.column_name, t.data_type, t.is_nullable
    FROM information_schema.columns AS t
    WHERE
      t.table_schema = 'public' and t.table_name='MyTable'
    ORDER BY t.ordinal_position
  ''');

詳情請參閱 Spanner 說明文件中的下列資訊結構定義參考資料：

• GoogleSQL 資訊架構
• PostgreSQL 資訊結構定義

定價

• 在 BigQuery 端，系統會套用標準的聯合查詢定價。
• 在 Spanner 端，查詢會套用 Spanner 定價。

跨區域查詢

BigQuery 支援聯合查詢，其中 Spanner 執行個體和 BigQuery 資料集位於不同區域。這類查詢會產生額外的 Spanner 資料移轉費用。詳情請參閱 Spanner 定價。

在預先發布期間，資料移轉不會產生費用，但您可以透過下列 SKU 查看用量：

• 網路區域內跨可用區資料移轉輸出
• 免費的執行個體網路跨區域資料輸出至同一個大洲
• 免費將執行個體網路跨區域資料轉出至其他大洲

資料移轉費用會根據您執行查詢的 BigQuery 區域，以及具備讀取/寫入或唯讀副本的最近 Spanner 區域計算。

如果是 BigQuery 多區域設定 (US 或 EU)，從 Spanner 移轉資料的費用計算方式如下：

• BigQuery US 多區域：Spanner 區域 us-central1
• BigQuery EU 多區域：Spanner 區域 europe-west1

例如：

• BigQuery (US 多區域) 和 Spanner (us-central1)：在同一區域內移轉資料時，系統會收取費用。
• BigQuery (US 多區域) 和 Spanner (us-west4)： 在同一洲內的不同區域之間移轉資料時，系統會收取費用。

疑難排解

本節說明如何排解將聯邦查詢傳送至 Spanner 時可能發生的問題。

問題：查詢無法進行根分割。

解決方法：如果您設定連線以平行讀取資料，查詢執行計畫中的第一個運算子必須是分散式聯集，或者執行計畫不得有任何分散式聯集。如要解決這個錯誤，請查看查詢執行計畫並重新編寫查詢。詳情請參閱「瞭解 Spanner 如何執行查詢」。

問題：已超過期限。

解決方法：選取「平行讀取資料」選項，並重新編寫查詢，使其可進行根分割。詳情請參閱「瞭解 Spanner 如何執行查詢」。

後續步驟

• 瞭解如何建立 Spanner 外部資料集
• 瞭解聯合查詢。
• 瞭解 Spanner 到 BigQuery 的資料類型對應。