排解具體化檢視表問題

本文有助於排解 BigQuery 中具體化檢視表的常見問題，包括建立具體化檢視表時發生錯誤、重新整理失敗，以及查詢效能不如預期。

診斷工作流程

調查具體化檢視區塊的問題時，請按照下列診斷步驟找出根本原因：

1. 驗證資料表類型和中繼資料。確認目標資料表是具體化檢視區塊，並檢查其設定選項：

   SELECT
    table_name,
    table_type
   FROM
    `PROJECT_ID.DATASET`.INFORMATION_SCHEMA.TABLES
   WHERE
    table_name = 'MATERIALIZED_VIEW';

   更改下列內容：

   • PROJECT_ID：包含具體化檢視區塊的專案。
   • DATASET：包含具體化檢視區塊的資料集。
   • MATERIALIZED_VIEW：具體化檢視區塊的名稱。

   如要檢查 enable_refresh、refresh_interval_minutes 和 max_staleness 等設定選項，請查詢 INFORMATION_SCHEMA.TABLE_OPTIONS 檢視畫面：

   SELECT
    table_name,
    option_name,
    option_value
   FROM
    `PROJECT_ID.DATASET`.INFORMATION_SCHEMA.TABLE_OPTIONS
   WHERE
    table_name = 'MATERIALIZED_VIEW';

2. 查看上次重新整理的狀態。查詢 INFORMATION_SCHEMA.MATERIALIZED_VIEWS 檢視區塊，檢查檢視區塊上次重新整理的時間，以及上次自動重新整理是否發生錯誤：

   SELECT
    table_name,
    last_refresh_time,
    refresh_watermark,
    last_refresh_status
   FROM
    `PROJECT_ID.DATASET`.INFORMATION_SCHEMA.MATERIALIZED_VIEWS
   WHERE
    table_name = 'MATERIALIZED_VIEW';

   如果 last_refresh_status 不是 NULL，表示上次自動重新整理作業失敗。如果 last_refresh_time 是 NULL 或舊版，具體化檢視區塊從未成功完成重新整理，或重新整理失敗。

3. 檢查重新整理工作記錄和錯誤。查詢 INFORMATION_SCHEMA.JOBS_BY_PROJECT 檢視區塊，檢查最近的自動重新整理工作：

   SELECT
    job_id,
    creation_time,
    end_time,
    state,
    error_result.reason AS error_reason,
    error_result.message AS error_message,
    total_slot_ms,
    total_bytes_processed
   FROM
    `region-REGION`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
   WHERE
    job_id LIKE '%materialized_view_refresh_%'
    AND creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
   ORDER BY
    creation_time DESC
   LIMIT 50;

   將 REGION 替換為資料集的區域，例如 us 或 europe-west3。

4. 檢查查詢執行作業和智慧型微調統計資料。如果查詢的執行速度比預期慢，請檢查工作統計資料中的 materialized_view_statistics 欄位，確認查詢最佳化工具是否使用了具體化檢視區塊：

   SELECT
    job_id,
    total_slot_ms,
    total_bytes_billed,
    materialized_view_statistics
   FROM
    `region-REGION`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
   WHERE
    job_id = 'JOB_ID';

   將 JOB_ID 替換為查詢工作 ID。

排解具體化檢視表建立錯誤

本節說明建立具體化檢視區塊時可能會遇到的錯誤，以及錯誤原因和解決步驟。

不支援的 SQL 運算子或語法

錯誤訊息：

Unsupported operator in materialized view: KEYWORD

或

Materialized view queries do not support FEATURE

原因：

增量具體化檢視表支援受限的 SQL 語法子集，可進行增量維護和智慧型調整。如果定義具體化檢視區塊的查詢包含不支援的功能 (例如下列功能)，您可能會遇到這個錯誤：

• 非確定性函式 (例如 CURRENT_TIMESTAMP()、RAND() 或 SESSION_USER())
• 使用 OVER() 的分析窗型函式
• ORDER BY 或 LIMIT 子句
• DISTINCT 不匯總
• WHERE 或 SELECT 子句中的子查詢
• 使用者定義的函式 (UDF)

解決方法：

• 請參閱不支援的 SQL 功能清單。

• 如果查詢需要更廣泛的 SQL 功能，請考慮設定 allow_non_incremental_definition = true 並定義 max_staleness 間隔，藉此建立非增量具體化檢視表：

  CREATE MATERIALIZED VIEW `PROJECT_ID.DATASET.MATERIALIZED_VIEW`
  OPTIONS (
  enable_refresh = true,
  refresh_interval_minutes = 60,
  max_staleness = INTERVAL "4" HOUR,
  allow_non_incremental_definition = true
  ) AS
  SELECT
  ...

  更改下列內容：

  • PROJECT_ID：包含具體化檢視區塊的專案。
  • DATASET：包含具體化檢視區塊的資料集。
  • MATERIALIZED_VIEW：具體化檢視區塊的名稱。

  非遞增式具體化檢視區塊支援更廣泛的 SQL 查詢集，但一律會執行完整重新整理，且不支援智慧型微調。

• 如果非增量具體化檢視區塊不支援必要的 SQL 語法，請使用邏輯檢視區塊或排程查詢，將結果寫入目的地資料表。

變更資料擷取基礎資料表的 max_staleness 無效

錯誤訊息：

Materialized view PROJECT_ID:DATASET.MATERIALIZED_VIEW has a CDC table as base table PROJECT_ID:DATASET.TABLE but does not have valid max_staleness. Materialized views over CDC tables must have max_staleness set at least 2 times the base table's max_staleness: 0-0 0 0:0:0

原因：

在變更資料擷取 (CDC) 基礎資料表上建立具體化檢視區塊時，具體化檢視區塊的 max_staleness 選項必須至少設為基礎資料表 max_staleness 值的兩倍。

解決方法：

1. 查詢 INFORMATION_SCHEMA.TABLE_OPTIONS 檢視區塊，檢查基本 CDC 資料表的 max_staleness 值。
2. 將 materialized view 的 max_staleness 選項設為至少是基礎資料表 max_staleness 值的兩倍。舉例來說，如果基本 CDC 表格的 max_staleness 值為 15 分鐘，請將具體化檢視區塊的 max_staleness 值設為至少 30 分鐘。詳情請參閱「ALTER MATERIALIZED VIEW SET OPTIONS 陳述式」一節，位於「GoogleSQL 中的資料定義語言 (DDL) 陳述式」一文。

非分區基本資料表上的分區具體化檢視表

錯誤訊息：

Partitioned incremental materialized view must be created on top of partitioned managed storage base table.

原因：

如要建立分區漸進式具體化檢視表，基礎資料表也必須分區，且具體化檢視表的分區資料欄必須與基礎資料表的分區資料欄一致。

解決方法：

• 如要對 materialized view 分區，請確保基礎資料表已分區，並設定 materialized view 使用相同的分區欄。詳情請參閱磁碟分割區對齊。
• 如果基礎資料表未經過分區，請建立不含 PARTITION BY 子句的 materialized view。
• 如要建立非分區資料表的分區檢視，請使用 allow_non_incremental_definition = true 和 max_staleness 建立非增量 materialized view。非遞增式 materialized view 不必與基本資料表對齊分區。

跨區域資料集副本為唯讀

錯誤訊息：

The dataset replica of the cross region dataset 'PROJECT_ID:DATASET' in region 'REGION' is read-only because it's not the primary replica.

原因：

使用跨區域資料集複製時，次要副本為唯讀。您無法在次要副本區域建立具體化檢視區塊。

解決方法：

在複製資料集的主要區域中建立具體化檢視表。 如需備用區域中的 materialized view，請在該區域建立 materialized view 副本。詳情請參閱「管理具體化檢視區塊副本」。

超過基礎資料表限制

錯誤訊息：

Materialized views support at most 10 source tables, query has NUMBER_OF_SOURCE_TABLES

原因：

BigQuery 具體化檢視表最多支援 10 個基本資料表之間的聯結。

解決方法：

重構定義具體化檢視區塊的查詢，以參照 10 個以下的基礎資料表。如果架構需要聯結超過 10 個資料表，請考慮將靜態或維度資料表預先聯結至中繼資料表，或使用排程查詢或 Dataform 管道。

建立具體化檢視區塊時超出資源限制

錯誤訊息：

Resources exceeded during query execution: The data accessed in this query is too large; consider accessing fewer tables, or for partitioned tables, fewer partitions.

原因：

建立具體化檢視表時，BigQuery 會執行初始完整重新整理，以填入檢視表。如果基礎資料表包含大量未經分割的資料，或檢視區塊產生高基數的中間匯總，則初始重新整理可能會超出時段記憶體或查詢限制。

解決方法：

• 在具體化檢視的 WHERE 子句中新增篩選條件，將掃描資料的範圍限制在所需子集。
• 將具體化檢視表分區與基本資料表分區對齊，以便在重新整理期間修剪分區。
• 如果使用隨選運算，建議使用BigQuery 版本，並預留專用運算單元，為大型重新整理作業提供充足的運算容量。

BigLake 資料表和中繼資料快取的問題

症狀：

建立或重新整理BigLake 外部資料表的具體化檢視表時會失敗。

原因：

外部資料表的具體化檢視表有特定的架構需求：

• 具體化檢視表僅支援啟用中繼資料快取的 BigLake 資料表。
• materialized view 的 max_staleness 值必須大於基礎 BigLake 基礎資料表的 max_staleness 值。
• 具體化檢視表可以參照 BigLake 外部資料表或 BigQuery 代管儲存空間資料表，但無法在單一具體化檢視表中混合使用類型。

解決方法：

1. 確認所有基礎 BigLake 基礎資料表都已啟用中繼資料快取。
2. 將 materialized view 上的 max_staleness 設定為高於基礎資料表的 metadata 快取間隔。舉例來說，如果基礎資料表的快取間隔為 30 分鐘，請將 materialized view 的 max_staleness 設為至少 45 分鐘，以便為重新整理作業執行預留緩衝時間。
3. 請勿在具體化檢視定義中混用外部資料表和代管資料表。

排解重新整理問題

本節說明具體化檢視區塊重新整理失敗和效能延遲的常見原因。

基礎資料表結構定義異動 (invalidQuery)

症狀：

INFORMATION_SCHEMA.MATERIALIZED_VIEWS 中的「last_refresh_status」欄會顯示 invalidQuery 錯誤，且自動重新整理作業會停止執行。

原因：

如果基礎資料表的結構定義變更 (例如捨棄具體化檢視表參照的資料欄、重新命名資料欄，或變更資料欄的資料類型)，定義具體化檢視表的基礎查詢就會失效。

解決方法：

BigQuery 不支援變更現有 materialized view 的資料欄結構定義。如要解決結構定義失效問題，請按照下列步驟操作：

1. 使用 CREATE OR REPLACE MATERIALIZED VIEW 陳述式重新建立具體化檢視表：

   CREATE OR REPLACE MATERIALIZED VIEW `PROJECT_ID.DATASET.MATERIALIZED_VIEW`
   OPTIONS (
    enable_refresh = true,
    refresh_interval_minutes = 30
   ) AS
   SELECT
    ...

   更改下列內容：

   • PROJECT_ID：包含具體化檢視區塊的專案。
   • DATASET：包含具體化檢視區塊的資料集。
   • MATERIALIZED_VIEW：具體化檢視區塊的名稱。

2. 確認新定義與更新後的基礎資料表結構定義相符。

基礎資料表分區到期、截斷或 DML 變更

症狀：

materialized view 無法重新整理，或針對 materialized view 執行的查詢會改用基礎資料表，導致執行速度緩慢。

原因：

下列基礎資料表作業會使現有的 materialized view 資料失效：

• 截斷基礎資料表或基礎資料表分區 (TRUNCATE TABLE)
• 基礎資料表的分區到期時間
• 對未分區的資料表或次要聯結的基礎資料表執行 DELETE 或 MERGE 資料操縱語言 (DML) 陳述式

發生這些作業時，受影響的資料分割 (或未經資料分割的資料表所對應的整個具體化檢視區塊) 會標示為無效。

解決方法：

1. 手動觸發重新整理，將 materialized view 還原為有效狀態：

   CALL BQ.REFRESH_MATERIALIZED_VIEW('PROJECT_ID.DATASET.MATERIALIZED_VIEW');

2. 如果您執行會定期執行 DML 陳述式或截斷資料的批次 ETL 管道，請停用自動重新整理，並在 ETL 管道結尾呼叫 BQ.REFRESH_MATERIALIZED_VIEW。詳情請參閱「自動重新整理」。

重新整理逾時的工作

症狀：

執行數小時 (最多 12 小時) 後，重新整理作業會因逾時錯誤而失敗。

原因：

基本資料表越大，重新整理時處理的資料量就越多。如果具體化檢視區塊查詢未篩選資料列，或檢視區塊因完全失效而無法執行增量更新，每次重新整理時，系統都必須完整掃描基礎資料表，這可能會耗盡時段時間。

解決方法：

• 在具體化檢視的 WHERE 子句中新增篩選條件，限制不必要的歷史資料。
• 請確認 materialized view 與基礎資料表的分區對齊，這樣系統只會以遞增方式重新整理經過修改的分區。
• 指派容量充足的運算單元保留項目，以容納重新整理工作負載。

重複的重新整理訊息

訊息：

Materialized view is already being refreshed.

原因：

如果JOIN具體化檢視中的基礎資料表同時更新，或是在自動重新整理正在進行時觸發手動重新整理，BigQuery 會偵測到並行重新整理，並取消重複的工作。

解決方法：

這是正常現象，而且是暫時性的。系統會停止重複的工作，避免處理冗餘資料，且不會針對重複的重新整理嘗試收費。您無須採取任何行動。

串流資料 (寫入最佳化儲存空間) 延遲重新整理

症狀：

針對含有高速串流資料的基礎資料表進行查詢時，materialized view 不會立即顯示查詢結果，或查詢會回溯至基礎資料表。

原因：

使用 Storage Write API 串流至 BigQuery 的資料，一開始會儲存在最適合寫入作業的儲存空間 (串流緩衝區)。materialized view 的重新整理作業會在資料提交並從串流緩衝區轉換為最佳化直欄式儲存空間後，處理資料。

為維持即時一致性，從具體化檢視表讀取資料的查詢會從具體化檢視表讀取已提交的資料，並同時直接從基礎資料表串流緩衝區讀取差異。

解決方法：

• 如果需要串流資料的即時讀取一致性，查詢規劃工具會自動將具體化檢視區塊資料與基本資料表差異合併。
• 如果不需要即時一致性，且想避免在每次查詢時掃描串流緩衝區，請在具體化檢視區塊中設定 max_staleness (例如 max_staleness = INTERVAL "15" MINUTE)。這樣一來，查詢就能直接從預先計算的具體化檢視區塊讀取資料，不必進行差異處理。

排解查詢效能問題及智慧調整

本節說明如何排解查詢執行速度低於預期，或未充分運用智慧微調功能的相關問題。

確認智慧調整功能的使用情況

查詢基礎資料表時，如果使用可用的 materialized view 能提升效能並降低成本，BigQuery 就會透過智慧微調功能自動改寫查詢。

如要檢查查詢是否使用具體化檢視區塊，請檢查查詢工作詳細資料中的 materialized_view_statistics 欄位，或查詢 INFORMATION_SCHEMA.JOBS_BY_PROJECT 檢視區塊：

SELECT
  job_id,
  total_slot_ms,
  total_bytes_billed,
  mv.table_reference.dataset_id,
  mv.table_reference.table_id,
  mv.chosen,
  mv.rejected_reason
FROM
  `region-REGION`.INFORMATION_SCHEMA.JOBS_BY_PROJECT,
  UNNEST(materialized_view_statistics.materialized_view) AS mv
WHERE
  job_id = 'JOB_ID';

更改下列內容：

• REGION：資料集所在的區域 (例如 us 或 europe-west3)。
• JOB_ID：查詢工作 ID。

在 materialized_view_statistics 物件中，materialized_view 陣列中的每個項目都包含下列欄位：

• table_reference：識別具體化檢視候選項目。
• chosen：布林值，指出查詢最佳化工具是否選取 materialized view 來執行 (true)，或拒絕選取 (false)。
• estimated_bytes_saved：查詢使用 materialized view 後，估計可避免掃描的位元組數。
• rejected_reason：如果 chosen 為 false，則指定最佳化工具拒絕具體化檢視的原因。

如要進一步瞭解遭拒原因和 rejected_reason 列舉，請參閱「瞭解具體化檢視區塊遭拒的原因」。

實體化檢視區塊遭拒的常見原因

如果 chosen 為 false，請檢查 rejected_reason 的值，診斷原因：

rejected_reason 值	說明	解析度
NO_DATA	由於 materialized view 尚未重新整理，或初始重新整理失敗，因此沒有快取資料。	使用 CALL BQ.REFRESH_MATERIALIZED_VIEW(...) 觸發手動重新整理。
COST	查詢最佳化工具預估，查詢基礎資料表 (或從查詢快取讀取) 比查詢具體化檢視區便宜。	查看查詢篩選器和分區。如果基礎資料表查詢只掃描一小部分分區，而具體化檢視區間跨越多個分區，直接查詢基礎資料表可能會更有效率。
BASE_TABLE_DATA_CHANGE	一或多個基礎資料表中的資料變更，導致快取資料在設定的過時時間範圍外失效。	手動重新整理，或設定 max_staleness，允許查詢讀取過時資料，而不必回溯至基本資料表。
BASE_TABLE_TRUNCATED	基礎資料表遭到截斷，導致所有具體化檢視資料失效。	重新填入資料後，請重新整理具體化檢視表。
BASE_TABLE_EXPIRED_PARTITION	基礎資料表中的分區已過期。	確認基礎資料表和 materialized view 的分區到期設定一致，然後重新整理檢視表。
BASE_TABLE_PARTITION_EXPIRATION_CHANGE	已修改基礎資料表的分區到期時間長度。	重新整理具體化檢視，重新對齊分區到期中繼資料。
BASE_TABLE_INCOMPATIBLE_METADATA_CHANGE	基礎資料表發生中繼資料變更 (例如修改結構定義)。	使用 CREATE OR REPLACE MATERIALIZED VIEW 重新建立具體化檢視表。
BASE_TABLE_TOO_STALE	基礎資料表的快取中繼資料 (例如 BigLake 外部資料表) 舊於允許的門檻。	重新整理外部資料表的中繼資料快取。
BASE_TABLE_FINE_GRAINED_SECURITY_POLICY	查詢使用者在基礎資料表上，缺少資料列層級或資料欄層級存取控管政策的存取權。	確認 IAM 權限和資料政策授權。
TIME_ZONE	檢視畫面是使用與目前查詢時區不同的時區重新整理。	請確保環境和重新整理作業的時區設定一致。

系統未考量具體化檢視表 (查詢結構不符)

如果 materialized_view_statistics 中未列出具體化檢視區塊，表示查詢最佳化工具在剖析語法時，判斷查詢模式與具體化檢視區塊定義不符。

常見原因包括：

1. 匯總或篩選器不符。查詢使用的匯總函式、分組資料欄或篩選條件無法從具體化檢視中的預先計算匯總值計算得出。
   • 解決方法：在查詢和具體化檢視表定義之間，對齊匯總函式和分組。
2. 非遞增式具體化檢視表。使用「allow_non_incremental_definition = true」建立的檢視區塊不支援智慧微調。
   • 解決方法：在 FROM 子句中指定檢視名稱，直接查詢非累加 materialized view。
3. 直接查詢過時的檢視畫面。如果您直接查詢已設定 max_staleness 的具體化檢視表，查詢會傳回最多 max_staleness 的過時預先計算結果，而不會處理基礎資料表的差異。

HyperLogLog 草圖不相容錯誤

錯誤訊息：

Invalid or incompatible sketch in HLL_COUNT.MERGE_PARTIAL

原因：

使用 HLL_COUNT.INIT 和 HLL_COUNT.MERGE_PARTIAL 等近似匯總函式時，BigQuery 會使用 HyperLogLog 草圖。如果查詢中指定的精確度參數與 materialized view 中定義的精確度參數不符，草圖合併作業就會失敗。

解決方法：

請確保具體化檢視區塊定義和參照/重新編寫檢視區塊的查詢中，精確度參數 (例如 HLL_COUNT.INIT(x, 12)) 完全相同。

排解檢視畫面變更和結構定義修改問題

本節說明修改實體化檢視區塊的結構定義或選項時，可能會遇到的問題。

編輯具體化檢視表結構定義

問題：

使用 ALTER TABLE 或 Google Cloud 控制台嘗試在具體化檢視中新增或修改資料欄時，會發生錯誤，或無法使用「編輯結構定義」選項。

原因：

BigQuery 不支援直接修改具體化檢視的資料欄結構定義。

解決方法：

• 您可以使用 ALTER MATERIALIZED VIEW SET OPTIONS 陳述式修改 materialized view 選項 (例如 enable_refresh、refresh_interval_minutes 和 max_staleness)：

  ALTER MATERIALIZED VIEW `PROJECT_ID.DATASET.MATERIALIZED_VIEW`
  SET OPTIONS (
  enable_refresh = true,
  refresh_interval_minutes = 20
  );

  更改下列內容：

  • PROJECT_ID：包含具體化檢視區塊的專案。
  • DATASET：包含具體化檢視區塊的資料集。
  • MATERIALIZED_VIEW：具體化檢視區塊的名稱。

• 如要變更 SQL 查詢定義、新增資料欄或變更資料欄資料類型，請使用 CREATE OR REPLACE MATERIALIZED VIEW 重新建立檢視表：

  CREATE OR REPLACE MATERIALIZED VIEW `PROJECT_ID.DATASET.MATERIALIZED_VIEW`
  AS SELECT
  ...

後續步驟

• 瞭解如何建立具體化檢視區塊。
• 瞭解如何使用具體化檢視區塊和智慧微調。
• 瞭解如何管理及重新整理具體化檢視區塊。
• 瞭解如何監控具體化檢視的重新整理和用量。
• 瞭解如何排解一般查詢成效問題。