Spanner 聯合查詢

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

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

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

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

使用外部資料集

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

如果您建立外部資料集，就不需要建立連線來查詢 Spanner 資料。

使用 EXTERNAL_QUERY 函式

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

事前準備

• 請確認您的 BigQuery 管理員已建立 Spanner 連線，並與您共用。請參閱「 選擇合適的連線」一文。
• 如要取得查詢 Spanner 執行個體所需的權限，請要求管理員授予您「BigQuery 連線使用者」(roles/bigquery.connectionUser) 身分與存取權管理 (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 查詢使用資料提升功能，請在建立查詢所需的連線時啟用這項功能。

並行讀取資料

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

不過，這個選項只適用於符合下列任一條件的要求：

• 執行計畫中的第一個運算子是 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 資料表結構定義

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

不過，您也可以在不定義外部資料集的情況下查看結構定義。您也可以使用 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 的資料類型對應。