排查查询问题

本文档旨在帮助您排查与运行查询相关的常见问题，例如确定查询速度缓慢的原因，或针对失败查询返回的常见错误提供解决步骤。

排查查询速度缓慢问题

在排查查询性能缓慢问题时，请考虑以下常见原因：

  1. 查看 Google Cloud Service Health 页面，了解可能影响查询性能的已知 BigQuery 服务中断情况。

  2. 在作业详情页面上查看查询的作业时间轴，了解查询的每个阶段的运行时长。

    • 如果大部分所用时间是由于创建时间较长，请与 Cloud Customer Care 团队联系以获取帮助。

    • 如果大部分所用时间是由于执行时间较长，请查看查询性能数据分析。查询性能数据分析可告知您查询运行时间是否超过平均执行时间，并针对可能的原因提供建议。可能的原因包括查询槽争用或 Shuffle 配额不足。如需详细了解每种查询性能问题及其可能的解决方法，请参阅解读查询性能数据分析。

  3. 查看查询作业详情页面中处理的字节数，了解是否高于预期。为此，您可以将当前查询处理的字节数与在可接受的时间量内完成的其他查询作业进行比较。如果两个查询处理的字节数存在较大差异，则可能是由于数据量较大而导致查询速度缓慢。如需了解如何优化查询以处理大量数据，请参阅优化查询计算。

    您还可以使用 INFORMATION_SCHEMA.JOBS 视图搜索成本最高的查询，从而确定项目中处理大量数据的查询。

如果您仍然找不到原因来解释查询性能低于预期的原因，请与 Cloud Customer Care 团队联系以获取帮助。

Avro 架构解析

错误字符串：Cannot skip stream

加载具有不同架构的多个 Avro 文件时，可能会发生此错误，从而导致架构解析问题，并导致导入作业在某个随机文件失败。

如需解决此错误，请确保加载作业中按字母排序的最后一个文件包含不同架构的超集（并集）。这是基于 Avro 如何处理架构解析的要求。

冲突的并发查询

错误字符串：Concurrent jobs in the same session are not allowed

如果在一个会话中并行运行多个查询，就可能会发生此错误，因为这是不受支持的。请参阅会话限制。

冲突的 DML 语句

错误字符串：Could not serialize access to table due to concurrent update

如果在同一表上并发运行的多个变更型数据操纵语言 (DML) 语句彼此冲突，或者表在变更型 DML 语句执行期间被截断，就可能会发生此错误。如需了解详情，请参阅 DML 语句冲突。

如需解决此错误，请运行影响单个表的 DML 操作，以避免重叠。

相关子查询

错误字符串：Correlated subqueries that reference other tables are not supported unless they can be de-correlated

如果您的查询包含引用子查询外部的列（称为相关性列）的子查询，则可能会发生此错误。系统会使用效率低下的嵌套执行策略来计算相关子查询，在这种策略中，系统会在生成相关性列的外部查询中针对每一行来计算子查询。有时，BigQuery 可以在内部使用相关子查询重写查询，以便更高效地执行查询。当 BigQuery 无法充分优化查询时，就会发生相关子查询错误。

如需解决此错误，请尝试以下操作：

  • 从子查询中移除任何 ORDER BY、LIMIT、EXISTS、NOT EXISTS 或 IN 子句。
  • 使用多语句查询创建要在子查询中引用的临时表。
  • 重写查询，以改用 CROSS JOIN。

列级访问权限控制权限不足

错误字符串：Requires raw access permissions on the read columns to execute the DML statements

当您尝试执行 DML DELETE、UPDATE 或 MERGE 语句时，如果您对使用列级访问权限控制来限制列级访问的扫描列没有 Fine-Grained Reader 权限，则会出现此错误。如需了解详情，请参阅列级访问权限控制对写入的影响。

计划查询的凭据无效

错误字符串：

  • Error code: INVALID_USERID
  • Error code 5: Authentication failure: User Id not found
  • PERMISSION_DENIED: BigQuery: Permission denied while getting Drive credentials

如果计划的查询因凭据过期而失败，就可能会发生此错误，尤其是在查询 Google 云端硬盘数据时。

如需解决此错误，请更新计划查询的凭据。

服务账号凭据无效

错误字符串：HttpError 403 when requesting returned: The caller does not have permission

当您尝试使用服务账号设置计划查询时，可能会出现此错误。如需解决此错误，请参阅授权和权限问题中的问题排查步骤。

快照时间无效

错误字符串：Invalid snapshot time

如果您尝试查询数据集时间旅行窗口之外的历史数据，就可能会发生此错误。如需解决此错误，请更改查询以访问数据集时间旅行窗口内的历史数据。

如果查询中使用的某个表在查询开始后被删除并重新创建，也可能会发生此错误。检查是否存在执行此操作的计划查询或应用与失败查询同时运行。如果存在，请尝试将执行删除和重新创建操作的进程移到与读取该表的查询不冲突的时间运行。

作业已存在

错误字符串：Already Exists: Job <job name>

如果查询作业必须对大型数组进行求值从而导致创建查询作业的时间超过平均耗时，就可能会发生此错误。例如，具有 WHERE 子句（如 WHERE column IN (<2000+ elements array>)）的查询。

要解决此错误，请按照以下步骤操作：

未找到作业

错误字符串：Job not found

响应未为 location 字段指定任何值的 getQueryResults 调用时，可能会发生此错误。如果是这种情况，请重试调用并提供 location 值。

如需了解详情，请参阅避免对相同的通用表表达式 (CTE) 进行多次求值。

查询超出执行时间限制

错误字符串：Query fails due to reaching the execution time limit

如果您的查询达到了查询执行时间限制，请使用类似于以下示例的查询查询 INFORMATION_SCHEMA.JOBS 视图，检查之前运行的查询的执行时间：

SELECT TIMESTAMP_DIFF(end_time, start_time, SECOND) AS runtime_in_seconds
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE statement_type = 'QUERY'
AND query = "my query string";

如果之前运行的查询花费的时间大幅减少，请使用查询性能数据分析来确定并解决根本问题。

查询响应内容过大

错误字符串：responseTooLarge

如果查询结果超过响应大小上限，就会发生此错误。

如需解决此错误，请按照为 responseTooLarge 错误消息提供的指南操作。

DML 语句过多

错误字符串：Too many DML statements outstanding against <table-name>, limit is 20

如果队列中单个表有超过 20 个 DML 语句处于 PENDING 状态，则会出现此错误。如果您针对单个表提交 DML 作业的速度快于 BigQuery 的处理速度，通常会发生此错误。

一种可能的解决方案是将多个较小的 DML 操作合并为一组，形成较大但数量更少的作业。将较小的作业组合为较大的作业时，运行较大作业的开销会被分摊，执行速度会更快。 整合影响相同数据的 DML 语句通常会提高 DML 作业的效率，并且会降低超出队列大小配额限制的可能性。如需详细了解如何优化 DML 操作，请参阅更新或插入单行的 DML 语句。

提高 DML 效率的其他解决方案包括对表进行分区或聚簇。如需了解详情，请参阅最佳做法。

用户没有权限

错误字符串：

  • Access Denied: Project [project_id]: User does not have bigquery.jobs.create permission in project [project_id].
  • User does not have permission to query table project-id:dataset.table.

在运行查询时，如果您对从中运行查询的项目没有 bigquery.jobs.create 权限，则会发生此错误，无论您对包含数据的项目拥有哪些权限。您还必须对查询引用的所有表和视图拥有 bigquery.tables.getData 权限。

如果表不存在于查询区域（例如 asia-south1）中，也可能会发生此错误。如需查询视图，您还需要对所有底层表和视图拥有此权限。如需详细了解所需权限，请参阅运行查询。

解决此错误时，请考虑以下事项：

  • 服务账号：服务账号必须对用于运行它们的项目拥有 bigquery.jobs.create 权限。

  • 自定义角色：自定义 IAM 角色必须在相关角色中明确包含 bigquery.jobs.create 权限。

  • 共享数据集：在单独的项目中使用共享数据集时，您可能仍需要在该项目中拥有 bigquery.jobs.create 权限才能在该数据集中运行查询或作业。

授予访问表的权限

如需向某个主体授予访问表的权限，请按以下步骤操作：

  1. 转到 BigQuery 页面。

    转到 BigQuery

  2. 在探索器中，找到您需要访问的表，选择 查看操作，选择共享，然后点击管理权限。

  3. 在添加主账号中，输入您要添加的用户、群组、网域或服务账号的名称。

  4. 在分配角色中，选择 bigquery.jobs.create 权限。或者，您也可以在执行查询的项目中授予 roles/bigquery.jobUser 角色，以获得所需权限。

  5. 点击保存。

超出资源数问题

当 BigQuery 没有足够的资源来完成查询时，会出现以下问题。

查询超出 CPU 资源

错误字符串：Query exceeded resource limits

当按需查询使用的 CPU 数量相对于扫描的数据量过多时，便会发生此错误。如需了解如何解决这些问题，请参阅排查超出资源问题。

查询超出内存资源

错误字符串：Resources exceeded during query execution: The query could not be executed in the allotted memory

对于 SELECT 语句，如果查询使用的资源过多，就会发生此错误。如需解决此错误，请参阅排查超出资源问题。

查询超出 shuffle 资源

错误字符串：Resources exceeded during query execution: Your project or organization exceeded the maximum disk and memory limit available for shuffle operations

当查询无法访问足够的 shuffle 资源时，就会发生此错误。

如需解决此错误，请预配更多槽或减少查询处理的数据量。如需详细了解执行此操作的方法，请参阅 shuffle 配额不足。

如需详细了解如何解决这些问题，请参阅排查超出资源问题。

查询过于复杂

错误字符串：Resources exceeded during query execution: Not enough resources for query planning - too many subqueries or query is too complex

当查询过于复杂时，会发生此错误。造成复杂性的主要原因是：

  • 深度嵌套或反复使用的 WITH 子句。
  • 深度嵌套或反复使用的视图。
  • 重复使用 UNION ALL 运算符。

如需解决此错误，请尝试以下方案：

  • 将查询拆分为多个查询，然后使用过程语言以共享状态按顺序运行这些查询。
  • 使用临时表而不是 WITH 子句。
  • 重写查询，以减少引用对象和比较的数量。

您可以使用 INFORMATION_SCHEMA.JOBS 视图中的 query_info.resource_warning 字段，主动监控即将达到复杂性上限的查询。以下示例会返回过去三天内资源使用量较高的查询：

SELECT
  ANY_VALUE(query) AS query,
  MAX(query_info.resource_warning) AS resource_warning
FROM
  <your_project_id>.`region-us`.INFORMATION_SCHEMA.JOBS
WHERE
  creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 DAY)
  AND query_info.resource_warning IS NOT NULL
GROUP BY
  query_info.query_hashes.normalized_literals
LIMIT
  1000

如需详细了解如何解决这些问题，请参阅排查超出资源问题。

排查超出资源数问题

对于查询作业：

如需优化查询，请尝试执行以下步骤：

  • 请尝试移除 ORDER BY 子句。
  • 如果查询使用 JOIN，请确保较大的表位于子句左侧。
  • 如果您的查询使用 FLATTEN，请确定它对于您的用例来说是否必要。 如需了解详情，请参阅嵌套重复的数据。
  • 如果查询使用 EXACT_COUNT_DISTINCT，请考虑改用 COUNT(DISTINCT)。
  • 如果查询使用 COUNT(DISTINCT <value>, <n>) 和较大的 <n> 值，请考虑改用 GROUP BY。如需了解详情，请参阅 COUNT(DISTINCT)。
  • 如果查询使用 UNIQUE，请考虑改用 GROUP BY，或子选择内的窗口函数。
  • 如果查询使用 LIMIT 子句具体化许多行，请考虑对其他列（例如 ROW_NUMBER()）进行过滤，或完全移除 LIMIT 子句以允许并行处理写入操作。
  • 如果查询使用了深层嵌套的视图和 WITH 子句，这可能会导致复杂性呈指数级增长，从而达到相应的限制。
  • 请勿将临时表替换为 WITH 子句。该子句可能必须多次重新计算，这可能会使查询变得复杂，从而导致速度缓慢。将临时结果持久保留在临时表中，有助于简化复杂性
  • 请避免使用 UNION ALL 查询。

如需了解详情，请参阅以下资源：

对于加载作业：

如果您要加载 Avro 或 Parquet 文件，请减小文件中的行大小。检查您要加载的文件格式的特定大小限制：

如果您在加载 ORC 文件时遇到此错误，请与支持团队联系。

对于 Storage API：

错误字符串：Stream memory usage exceeded

在 Storage Read API ReadRows 调用期间，某些具有高内存用量的流可能会收到 RESOURCE_EXHAUSTED 错误，并显示此消息。从宽表或具有复杂架构的表读取数据时，可能会发生这种情况。若要解决此问题，请选择较少要读取的列（使用 selected_fields 参数）或简化表架构来减小结果行大小。

后续步骤