排解具體化檢視表問題

本文將協助您排解 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 間隔，藉此建立非增量型 materialized view：

  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：具體化檢視區塊的名稱。

  非增量 materialized view 支援更多 SQL 查詢，但一律會執行完整重新整理，且不支援智慧型微調。

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

使用 CDC 基礎資料表時，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. 將具體化檢視區塊的 max_staleness 選項設為至少是基本資料表 max_staleness 值的兩倍。舉例來說，如果基礎 CDC 資料表的 max_staleness 值為 15 分鐘，請將 materialized view 的 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 子句的具體化檢視區。
• 如要針對非分區資料表建立分區檢視區塊，請使用 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 materialized view 最多支援 10 個基本資料表之間的聯結。

解決方法：

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

建立 materialized view 時超出資源上限

錯誤訊息：

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 會執行初始完整重新整理，以填入檢視表。如果基礎資料表包含大量未經分割的資料，或是檢視區塊產生高基數的中間匯總，初始重新整理作業可能會超出時段記憶體或查詢限制。

解決方法：

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

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

問題：

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

原因：

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

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

解決方法：

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

參照 INFORMATION_SCHEMA 檢視區塊

錯誤訊息：

Illegal operation on INFORMATION_SCHEMA view: PROJECT_ID:DATASET_OR_REGION.INFORMATION_SCHEMA.VIEW_NAME

視您查詢的 INFORMATION_SCHEMA 檢視畫面和指定的選項而定，您可能會收到一般具體化檢視畫面驗證錯誤，例如：

Unsupported operator in materialized view: KEYWORD

或

Materialized view queries do not support FEATURE

或

Materialized views cannot reference logical views.

原因：

您無法建立直接或間接參照 INFORMATION_SCHEMA 檢視區塊的增量或非增量 materialized view。INFORMATION_SCHEMA 檢視區塊是系統產生的中繼資料檢視區塊，而非支援的基礎資料表，因此 BigQuery 無法附加具體化檢視區塊中繼資料，也無法追蹤基礎資料表的變更。

由於 BigQuery 會在查詢計畫期間，將 INFORMATION_SCHEMA 檢視區塊擴展為基礎內部檢視區塊和系統資料表定義，因此建立失敗時，您可能會觀察到下列行為：

• 在 Illegal operation on INFORMATION_SCHEMA view 錯誤訊息中，VIEW_NAME 可能會顯示內部系統資料表名稱 (例如 _HASH_JOBS_DELETE)，而不是您在查詢中指定的 INFORMATION_SCHEMA 檢視區塊名稱。
• 如果是遞增式 materialized view，BigQuery 會先驗證 INFORMATION_SCHEMA view 的擴充內部 SQL 定義，再檢查基礎資料表支援。如果該內部定義包含遞增式具體化檢視表不支援的 SQL 語法或函式，查詢會先失敗並顯示「unsupported SQL operator or syntax」(不支援的 SQL 運算子或語法) 錯誤。

解決方法：

• 直接查詢 INFORMATION_SCHEMA 檢視畫面，或在 INFORMATION_SCHEMA 檢視畫面上建立標準邏輯檢視畫面 (如果不需要預先計算的儲存空間)。
• 如要具體化 INFORMATION_SCHEMA 中繼資料 (例如保留歷來中繼資料或提升查詢效能)，請使用 CREATE TABLE AS SELECT 陳述式或排程查詢，將查詢結果寫入標準 BigQuery 資料表。然後直接查詢該標準資料表，或在該資料表上建立具體化檢視區塊。

疑難排解重新整理問題

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

基礎資料表結構定義變更 (invalidQuery)

問題：

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

原因：

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

解決方法：

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

1. 使用 CREATE OR REPLACE MATERIALIZED VIEW 陳述式重新建立 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. 如果您執行批次 ETL 管道，定期執行 DML 陳述式或截斷資料，請停用自動重新整理，並在 ETL 管道結尾呼叫 BQ.REFRESH_MATERIALIZED_VIEW。詳情請參閱「自動重新整理」。

重新整理逾時的工作

問題：

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

原因：

隨著基本資料表變大，重新整理期間處理的資料量也會增加。 如果具體化檢視查詢未篩選資料列，或檢視因完全失效而無法執行增量更新，每次重新整理都需要完整掃描基礎資料表，這可能會耗盡時段時間。

解決方法：

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

重複的重新整理訊息

訊息：

Materialized view is already being refreshed.

原因：

如果 JOIN materialized view 中的基礎資料表同時更新，或是在自動重新整理作業進行期間觸發手動重新整理，BigQuery 會偵測到並行重新整理作業，並取消重複的工作。

解決方法：

這是正常現象，而且是暫時性的。系統會停止重複的工作，避免多餘的處理程序，且不會向您收取重複重新整理的費用。您無須採取任何行動。

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

問題：

針對含有高速串流資料的基礎資料表發出的查詢，不會立即顯示在具體化檢視區塊中，或查詢會回溯至基礎資料表。

原因：

使用 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	基礎資料表中的分區已過期。	確認基本資料表和具體化檢視表的分割區到期設定一致，然後重新整理檢視表。
BASE_TABLE_PARTITION_EXPIRATION_CHANGE	已修改基礎資料表的分區到期時間長度。	重新整理具體化檢視，重新對齊分割區到期中繼資料。
BASE_TABLE_INCOMPATIBLE_METADATA_CHANGE	基礎資料表發生中繼資料變更 (例如修改結構定義)。	使用 CREATE OR REPLACE MATERIALIZED VIEW 重新建立 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 子句中指定檢視區塊名稱，直接查詢非累加式具體化檢視區塊。
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
  ...

後續步驟

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