全域查詢

全域查詢可讓您執行 SQL 查詢，參照儲存在多個區域的資料。舉例來說，您可以執行全域查詢，彙整 us-central1 和 europe-central2 中的資料表。本文說明如何在專案中啟用及執行全域查詢。

事前準備

確認專案已啟用全域查詢，且您具備執行全域查詢的必要權限。

啟用全域查詢

如要為專案或組織啟用全域查詢，請使用 ALTER PROJECT SET OPTIONS 陳述式或 ALTER ORGANIZATION SET OPTIONS 陳述式變更預設設定。

  • 如要在某個區域執行全域查詢，請在執行查詢的專案中，將該區域的 enable_global_queries_execution 引數設為 true。
  • 如要允許全域查詢從某個區域複製資料，請在包含資料的專案中，將該區域的 enable_global_queries_data_access 引數設為 true。
  • 每當查詢存取遠端資料表時，系統都會檢查這些選項。
  • 全域查詢可以在一個專案中執行，並從另一個專案的其他區域提取資料。

範例：跨專案設定

以下範例說明如何執行查詢，在某個專案中存取另一個專案的資料表。

假設您有一個專案 query_project 在 us-central1 區域執行工作，而您想執行查詢，存取位於 europe-west1 區域的資料表 data_project.dataset.my_table：

SET @@location='us-central1';
SELECT
  *
FROM
  `query_project.dataset.my_table`
  JOIN `data_project.dataset.my_other_table` USING id;

如要成功執行這項全域查詢，請完成下列設定：

  1. 您必須在執行全域查詢的區域 (us-central1) 中，啟用專案 (query_project) 的全域查詢執行功能：

    ALTER PROJECT `query_project`
SET OPTIONS (
`region-us-central1.enable_global_queries_execution` = TRUE
)

  2. 您必須在包含資料的專案 (data_project) 中，為其區域 (europe-west1) 啟用透過全域查詢複製資料的功能：

    ALTER PROJECT `data_project`
SET OPTIONS (
`region-europe-west1.enable_global_queries_data_access` = TRUE
)

如要建立及使用包含遠端資料表的檢視表，也必須遵守相同原則：執行查詢的專案必須啟用 enable_global_queries_execution。

這些 ALTER PROJECT 作業必須分開執行，因為它們參照不同的專案和區域。變更可能需要幾分鐘才會生效。

必要權限

如要執行全域查詢，您必須具備 bigquery.jobs.createGlobalQuery 權限。BigQuery 管理員角色是唯一包含這項權限的預先定義角色。如要授予執行全域查詢的權限，但不授予 BigQuery 管理員角色，請按照下列步驟操作：

  1. 建立自訂角色，例如「BigQuery 全域查詢執行者」。
  2. 將 bigquery.jobs.createGlobalQuery 新增至這個角色。
  3. 將這個角色指派給選定的使用者或服務帳戶。

查詢資料

如要執行全域查詢，請編寫 SQL 查詢，就像資料位於單一位置一樣。如果查詢參照的資料儲存在多個位置，BigQuery 會嘗試執行全域查詢。如未指定執行查詢的位置，BigQuery 會根據參照資料表的位置自動選取位置。詳情請參閱「選擇位置」。查詢參照的資料如果不在所選位置，就會複製到該位置。

以下範例會以全域查詢的形式執行，將儲存在兩個不同位置的兩個不同資料集中的資料表聯結起來：

SELECT id, tr_date, product_id, price FROM us_dataset.transactions
UNION ALL
SELECT id, tr_date, product_id, price FROM europe_dataset.transactions

選擇一個位置

如要設定全域查詢的執行位置，請指定位置。決定全域查詢的執行位置時，請考量下列事項：

  • 資料落地：全域查詢會暫時將資料從一個位置複製到另一個位置。如果貴組織有資料落地規定，且不希望資料離開特定位置，請將查詢位置設為該位置。

  • 移轉費用和效能：如要盡量減少位置之間的資料移轉量，並降低查詢費用，請在儲存大部分查詢資料的區域執行查詢。

    舉例來說，假設您有一間網路商店，並將產品清單存放在 us-central1 位置，但將交易存放在 us-south1 區域。如果交易數量多於目錄中的產品數量，請在 us-south1 區域執行查詢。

  • 預留項目和運算資源：指定查詢位置，控管要由哪個區域預留項目或運算單元處理查詢。

如果您未手動指定位置，BigQuery 會根據下列條件自動決定執行位置：

  • 如果是資料修改語言 (DML) 查詢 (INSERT、UPDATE 和 DELETE 陳述式)，系統會選取目標資料表的位置做為執行位置。
  • 如果是資料定義語言 (DDL) 查詢 (例如 CREATE TABLE AS SELECT 陳述式)，系統會選取建立或修改資源的位置做為執行位置。
  • 如果查詢指定了目的地資料表，系統會選取目的地資料表的位置做為執行位置。
  • 至於其他查詢，系統會任意選取參照資料集的其中一個位置做為執行位置。

瞭解全域查詢

如要以符合成本效益的方式執行全域查詢，請務必瞭解其執行機制。

如要使用位於不同位置的資料，必須將資料複製到同一個位置。以下是 BigQuery 執行全域查詢工作流程的抽象概念：

  1. 決定查詢必須在何處執行，可以從使用者宣告或自動執行。這個位置稱為「主要」位置，查詢參照的所有其他位置則為「遠端」。
  2. 在各個遠端區域執行子查詢，收集在主要區域完成查詢所需的資料。
  3. 將這項資料從遠端位置複製到主要位置。
  4. 將資料儲存在主要位置的臨時資料表，保留 24 小時。如果子查詢結果小於 10 GiB，系統可能會快取資料以供重複使用，避免在區域之間移轉資料。
  5. 使用在主要位置收集的所有資料，執行最終查詢。
  6. 傳回查詢結果。

BigQuery 會盡量減少在區域之間移轉的資料量。請見如下範例：

SET @@location = 'EU';
SELECT
  t1.col1, t2.col2
FROM
  eu_dataset.table1 t1
  JOIN us_dataset.table2 t2 using col3
WHERE
  t2.col4 = 'ABC'

BigQuery 不需要將資料表 t2 的所有資料從美國複製到歐盟。只要移轉要求的資料欄 (col2 和 col3) 和符合 WHERE 條件 (t2.col4 = 'ABC') 的資料列即可。不過，這些機制 (稱為「下推」) 取決於查詢結構，有時移轉的資料量可能很大。建議您先對一小部分資料測試全域查詢，確認資料只在必要時才會轉移。

觀測能力

如要監控全域查詢並檢查各區域的執行情況，請使用下列方法：

工作記錄

如要查看傳送至遠端區域的查詢文字，請查看工作記錄。遠端工作的 ID 與原始查詢相同，但會加上 _xregion 後置字串。

Jobs REST API

呼叫 jobs.get 方法時，傳回的 Job 資源會在 JobStatistics 物件中包含下列欄位：

  • statistics.globalQueryRemoteRegions：字串陣列，代表全域查詢存取資料的遠端區域。只有主要執行區域中的父項全域查詢工作，才會填入這個欄位。如果是子項全域查詢工作和單一區域查詢，這個欄位會留空。
  • statistics.parentGlobalQueryJob：JobReference 物件 (projectId、jobId、location)，用於識別父項全域查詢工作。只有在遠端區域中代表全域查詢執行的子項全域查詢工作 (遠端子查詢和跨區域複製工作)，才會填入這個欄位。如果是父項全域查詢工作和單一區域查詢，則不會設定這個值。

稽核記錄

在 Cloud 稽核記錄中，BigQueryAuditMetadata 物件的 JobStats 物件包含下列欄位：

  • jobStats.globalQueryRemoteRegions：字串陣列，代表查詢存取的遠端區域。只有主要執行區域中的父項全域查詢工作，才會填入這個欄位。
  • jobStats.parentGlobalQueryJobId：父項全域查詢工作的工作 ID。在遠端區域執行的子項工作會填入這個欄位。
  • jobStats.parentGlobalQueryJobLocation：父項全域查詢工作的所在位置。在遠端區域執行的子項工作會填入這個欄位。

尋找全域查詢的遠端子項工作

如要找出與父項全域查詢相關聯的所有遠端子項工作，您可以使用記錄檔分析或匯出的記錄檔接收器資料集，查詢稽核記錄：

SELECT
  timestamp,
  proto_payload.audit_log.resource_name AS resource_name,
  JSON_VALUE(proto_payload.audit_log.metadata.jobChange.job.jobConfig.queryConfig.query) AS query
FROM
  `PROJECT_ID.LOG_DATASET._AllLogs`
WHERE
  JSON_VALUE(proto_payload.audit_log.metadata.jobChange.job.jobStats.parentGlobalQueryJobId) = 'PARENT_JOB_ID';

更改下列內容：

  • PROJECT_ID：您的 Google Cloud 專案 ID。
  • LOG_DATASET：記錄檔分析的 BigQuery 連結資料集，或記錄檔接收器目的地資料集。
  • PARENT_JOB_ID：父項全域查詢工作的工作 ID。

INFORMATION_SCHEMA 檢視表

您可以查詢 INFORMATION_SCHEMA.JOBS 和 INFORMATION_SCHEMA.JOBS_TIMELINE 檢視表，檢查全域查詢的中繼資料。global_query_info 欄包含下列欄位：

  • parent_job_id：主要區域中父項全域查詢工作的 ID。系統會在遠端區域執行的子查詢和複製工作中填入這個欄位。
  • parent_job_location：父項全域查詢工作的所在位置。
  • remote_regions：查詢參照的遠端區域陣列。這個欄位會填入主要區域中父項全域查詢工作的資訊。

找出主要區域中的全域查詢

如要找出在主要區域執行的所有全域查詢，並查看參照了哪些遠端區域，請在主要區域中查詢 INFORMATION_SCHEMA.JOBS 檢視表：

SELECT
  job_id,
  creation_time,
  query,
  total_bytes_billed,
  global_query_info.remote_regions
FROM
  `region-REGION`.INFORMATION_SCHEMA.JOBS
WHERE
  ARRAY_LENGTH(global_query_info.remote_regions) > 0;

將 REGION 替換為執行全域查詢的區域，例如 us-central1。

尋找全域查詢的遠端子項工作

如要找出與父項全域查詢相關聯的所有遠端子項工作，請在遠端區域查詢 INFORMATION_SCHEMA.JOBS 檢視表：

SELECT
  job_id,
  project_id,
  job_type,
  statement_type,
  total_bytes_billed,
  total_slot_ms
FROM
  `region-REMOTE_REGION`.INFORMATION_SCHEMA.JOBS
WHERE
  global_query_info.parent_job_id = 'PARENT_JOB_ID';

更改下列內容：

  • REMOTE_REGION：執行子查詢或複製工作的遠端區域，例如 europe-west1。
  • PARENT_JOB_ID：父項全域查詢工作的工作 ID。

關閉全域查詢

如要停用專案或組織的全域查詢，請使用 ALTER PROJECT SET OPTIONS statement 或 ALTER ORGANIZATION SET OPTIONS statement 變更預設設定。

  • 如要關閉某個區域的全域查詢，請將該區域的 enable_global_queries_execution 引數設為 false 或 NULL。
  • 如要禁止全域查詢從某個區域複製資料，請將該區域的 enable_global_queries_data_access 引數設為 false 或 NULL。

以下範例說明如何在專案層級停用全域查詢：

ALTER PROJECT PROJECT_ID
SET OPTIONS (
  `region-REGION.enable_global_queries_execution` = false,
  `region-REGION.enable_global_queries_data_access` = false
);

更改下列內容：

  • PROJECT_ID：要變更的專案名稱
  • REGION：要停用全域查詢的區域名稱

變更可能需要幾分鐘才會生效。

定價

全域查詢的費用包含下列部分：

  • 遠端位置中每個子查詢的運算費用，依據您在這些位置的定價模式計算
  • 最終查詢在執行區域的運算費用，依據您在該區域的定價模式計算
  • 在不同位置之間複製資料的費用，依資料複製定價計算
  • 根據儲存空間定價，將資料從遠端區域複製到主要區域並儲存 24 小時的費用

配額

如要瞭解全域查詢的配額，請參閱「查詢工作」。

限制

  • Assured Workloads 不支援全域查詢。
  • 使用區域端點時，不支援全域查詢。
  • 沙箱模式不支援全域查詢。
  • 由於資料在不同區域之間移轉需要時間，因此全域查詢的延遲時間會比單一區域查詢長。
  • 全域查詢不會以不可分割的方式執行。如果資料複製成功，但整體查詢失敗，您仍須支付資料複製費用。
  • 單一全域查詢最多可存取每個區域的 10 個遠端資料表。
  • 執行全域查詢時，在遠端區域建立的臨時資料表，只有在設定用於加密全域查詢結果 (在資料表、資料集或專案層級) 的 CMEK 金鑰為全域金鑰時，才會使用客戶自行管理的加密金鑰 (CMEK) 加密。為確保遠端臨時資料表一律受到 CMEK 保護，請為在遠端區域執行全域查詢的專案設定預設 KMS 金鑰。
  • 不支援全域授權檢視表和授權常式 (某個位置的檢視表或常式有權存取其他位置的資料集)。請改為在資料所在區域建立授權檢視表，並透過全域查詢來查詢授權檢視表。
  • 不支援全域查詢的 materialized view。
  • 您無法使用全域查詢來查詢 RANGE 類型的資料欄。
  • 您無法使用全域查詢來查詢虛擬資料欄，例如 _PARTITIONTIME。
  • 全域查詢不支援使用彈性資料欄名稱查詢資料欄。
  • 如果全域查詢參照 STRUCT 資料欄，則不會對任何遠端子查詢套用下推。如要提高效能，建議您在遠端區域建立檢視表，篩選 STRUCT 資料欄，並只傳回必要欄位做為個別資料欄。
  • 查詢的執行詳細資料和執行圖表不會顯示從遠端位置處理及轉移的位元組數。這項資訊會顯示在工作記錄中的複製工作中。全域查詢建立的複製工作，其工作 ID 會以查詢工作的工作 ID 做為前置字串。
  • 數據分析僅支援包裝在檢視表中的全域查詢，且必須設為使用檢視者的憑證。