排查物化视图问题

本文档可帮助您排查与 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 语法，请使用逻辑视图或 预定查询将结果写入 目标表。

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 分钟，则将具体化视图 max_staleness 值设置为至少 30 分钟。如需了解详情，请参阅 GoogleSQL 中的 数据定义语言 (DDL) 语句中的“ALTER MATERIALIZED VIEW SET OPTIONS 语句”。

基于非分区基表的分区物化视图

错误消息：

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

原因：

如需创建分区增量物化视图，底层基表也必须进行分区，并且具体化视图的分区列必须与基表的分区列保持一致。

解决方法：

• 如果您希望具体化视图进行分区，请确保基表已分区，并将具体化视图配置为使用相同的分区列。如需了解详情，请参阅 分区一致。
• 如果基表未分区，请在创建具体化视图时不要使用 PARTITION BY 子句。
• 如果您需要基于非分区表的分区视图，请使用 allow_non_incremental_definition = true 和 max_staleness 创建 非增量物化视图 。 非增量物化视图不需要与基表进行分区一致。

跨区域数据集副本为只读

错误消息：

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 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 表。
• 具体化视图的 max_staleness 值必须大于底层 BigLake 基表的 max_staleness 值。
• 具体化视图可以引用 BigLake 外部表或 BigQuery 托管存储表，但不能在单个具体化视图中混合使用类型。

解决方法：

1. 确保所有底层 BigLake 基表都启用了元数据缓存。
2. 具体化视图的 max_staleness 配置为高于基表的元数据缓存时间间隔的值。例如，如果基表缓存时间间隔为 30 分钟，则将具体化视图 max_staleness 设置为至少 45 分钟，以便为刷新执行留出缓冲时间。
3. 请勿在具体化视图 定义中混合使用外部表和托管表。

排查刷新问题

本部分介绍了物化视图刷新失败和性能延迟的常见原因。

基表架构更改 (invalidQuery)

症状：

INFORMATION_SCHEMA.MATERIALIZED_VIEWS 中的 last_refresh_status 列显示 invalidQuery 错误，并且自动刷新停止运行。

原因：

如果基表的架构发生更改（例如删除具体化视图引用的列、重命名列或更改列的数据类型），则定义具体化视图的底层查询将失效。

解决方法：

BigQuery 不支持更改现有具体化视图的列架构。如需解决架构失效问题，请执行以下操作：

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 更改

症状：

物化视图刷新失败，或者针对具体化视图的查询回退到基表并运行缓慢。

原因：

以下基表操作会使现有具体化视图数据失效：

• 截断基表或基表分区 (TRUNCATE TABLE)
• 基表上的分区过期
• 对未分区表或辅助联接的基表执行 DELETE 或 MERGE 数据操纵语言 (DML) 语句

发生这些操作时，受影响的分区（或未分区表的整个具体化视图）会被标记为无效。

解决方法：

1. 手动触发刷新，以将具体化视图恢复到有效状态：

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

2. 如果您运行定期执行 DML 语句或截断数据的批处理 ETL 流水线，请停用自动刷新，并在 ETL 流水线末尾调用 BQ.REFRESH_MATERIALIZED_VIEW。如需了解详情，请参阅 自动刷新。

刷新作业超时

症状：

刷新作业在运行数小时（最多 12 小时）后失败，并显示超时错误。

原因：

随着基表的增长，刷新期间处理的数据量也会增加。 如果具体化视图查询未过滤行，或者由于完全失效而无法执行增量更新，则每次刷新都需要对基表进行完全扫描，这可能会耗尽槽时间。

解决方法：

• 在具体化视图的 WHERE 子句中添加过滤条件，以限制不必要的历史数据。
• 确保具体化视图与基表进行分区一致，以便仅增量刷新修改后的分区。
• 分配具有足够容量的槽预留，以满足刷新工作负载的需求。

重复的刷新消息

消息：

Materialized view is already being refreshed.

原因：

如果 JOIN 具体化视图中的基表同时更新，或者在自动刷新已在进行时触发手动刷新，则 BigQuery 会检测到并发刷新并取消重复的作业。

解决方法：

此行为是正常且短暂的。系统会停止重复的作业以防止冗余处理，并且您无需为重复的刷新尝试付费。 您无需采取任何行动。

流式数据（写入优化存储空间）刷新延迟

症状：

针对具有高速流式数据的基表的查询不会立即显示在具体化视图中，或者查询会回退到基表。

原因：

使用 Storage Write API 流式插入到 BigQuery 的数据最初存储在写入优化存储空间（流式缓冲区）中。具体化视图的刷新作业会在数据提交并从流式缓冲区转换为优化的列式存储空间后处理数据。

为了保持实时一致性，从具体化视图读取数据的查询会从具体化视图读取已提交的数据，并同时直接从基表流式缓冲区读取增量。

解决方法：

• 如果需要流式数据的实时读取一致性，查询规划器会自动具体化视图数据与基表增量相结合。
• 如果不需要实时一致性，并且您希望避免在每次查询时扫描流式缓冲区，请在具体化视图上设置 max_staleness（例如 max_staleness = INTERVAL "15" MINUTE）。然后，查询可以直接从预先计算的具体化视图读取，而无需进行增量处理。

排查查询性能和智能调优问题

本部分介绍了如何排查运行速度低于预期或未利用智能调优的查询。

验证智能调优使用情况

当您查询基表时，如果使用可用的具体化视图可以提高性能并降低费用，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：一个布尔值，表示查询优化器是选择具体化视图来执行 (true) 还是拒绝物化视图 (false)。
• estimated_bytes_saved：查询通过使用具体化视图避免扫描的估计字节数。
• rejected_reason：如果 chosen 为 false，则指定优化器拒绝具体化视图的原因。

如需详细了解拒绝原因和 rejected_reason 枚举，请参阅 了解具体化视图受拒的原因。

具体化视图受拒的常见原因

当 chosen 为 false 时，请检查 rejected_reason 的值以诊断原因：

rejected_reason 值	说明	解决方法
NO_DATA	具体化视图没有缓存数据，因为它尚未刷新，或者初始刷新失败。	使用 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 重新具体化视图。
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 草图。 如果查询中指定的精度参数与具体化视图中定义的精度参数不匹配，则草图合并操作会失败。

解决方法：

确保精度参数（例如 HLL_COUNT.INIT(x, 12)）在具体化视图定义以及引用或重写到该视图的查询中完全相同。

排查视图更改和架构修改问题

本部分介绍了您在修改具体化视图的架构或选项时可能会遇到的问题。

修改具体化视图架构

问题：

尝试使用 ALTER TABLE 或控制台 Google Cloud 在具体化视图中添加或修改列会导致错误，或者 修改架构 选项 不可用。

原因：

BigQuery 不支持直接修改具体化视图的列架构。

解决方法：

• 您可以使用 ALTER MATERIALIZED VIEW SET OPTIONS 语句修改具体化视图选项（例如 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
  ...

后续步骤

• 了解如何创建物化视图。
• 了解如何使用物化视图和智能调优。
• 了解如何管理和刷新物化视图。
• 了解如何具体化视图刷新和使用情况。
• 了解如何排查一般查询性能问题。