排查查询问题

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

排查查询速度缓慢问题

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

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

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

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

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

  3. 查看查询作业 JobStatistics 中的 finalExecutionDurationMs 字段。查询可能已重试。finalExecutionDurationMs 字段包含此作业最终尝试执行时长（以毫秒为单位）。

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

    您还可以使用 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) 进行多次求值。

找不到位置

错误字符串：Dataset [project_id]:[dataset_id] was not found in location [region]

当您引用不存在的数据集资源，或请求中的位置与数据集的位置不匹配时，系统会返回此错误。

如需解决此问题，请在查询中指定数据集的位置，或确认数据集在同一位置可用。

查询超出执行时间限制

错误字符串：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.
  • Access Denied: User does not have permission to query table or perhaps it does not exist.

在运行查询时，如果您对从中运行查询的项目没有 bigquery.jobs.create 权限，则可能会发生这些错误，无论您对包含数据的项目拥有何种权限。

如果您的服务账号、用户或群组对查询引用的所有表和视图没有 bigquery.tables.getData 权限，您也可能会收到这些错误。如需详细了解运行查询所需的权限，请参阅所需角色。

如果表不存在于所查询的区域（例如 asia-south1）中，也可能会发生此错误。您可以通过检查数据集位置来验证区域。

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

  • 服务账号：服务账号必须对用于运行它们的项目拥有 bigquery.jobs.create 权限，并且必须对查询引用的所有表和视图拥有 bigquery.tables.getData 权限。

  • 自定义角色：自定义 IAM 角色必须在相关角色中明确包含 bigquery.jobs.create 权限，并且必须对查询引用的所有表和视图拥有 bigquery.tables.getData 权限。

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

如需授予访问表或视图的权限，请参阅授予对表或视图的访问权限。

超出资源数问题

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

查询超出 CPU 资源

错误字符串：Query exceeded resource limits

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

查询超出内存资源

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

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

查询执行期间超出资源限制

错误字符串：Resources exceeded during query execution: The query could not be executed in the allotted memory. Peak usage: [percentage]% of limit. Top memory consumer(s): ORDER BY operations.

运行 ORDER BY ... LIMIT ... OFFSET ... 查询时可能会发生这种情况。由于实现细节，排序可能会在单个计算单元上进行，如果在应用 LIMIT 和 OFFSET 之前需要处理过多行，则计算单元可能会耗尽内存，尤其是在 OFFSET 较大的情况下。

如需解决此错误，请避免在 ORDER BY...LIMIT 查询中使用较大的 OFFSET 值。或者，使用可扩展的 ROW_NUMBER() 窗口函数根据所选顺序分配排名，然后在 WHERE 子句中过滤这些排名。例如：

SELECT ...
FROM (
  SELECT ROW_NUMBER() OVER (ORDER BY ...) AS rn
  FROM ...
)
WHERE rn > @start_index AND rn <= @page_size + @start_index  -- note that row_number() starts with 1

查询超出 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 参数）或简化表架构来减小结果行大小。

排查连接问题

以下部分介绍了如何排查在尝试与 BigQuery 交互时出现的连接问题：

将 Google DNS 列入许可名单

使用 Google IP Dig 工具将 BigQuery DNS 端点 bigquery.googleapis.com 解析为单个“A”记录 IP。确保防火墙设置中未屏蔽此 IP。

一般来说，我们建议将 Google DNS 名称列入许可名单。https://www.gstatic.com/ipranges/goog.json 和 https://www.gstatic.com/ipranges/cloud.json 文件中共享的 IP 地址范围经常更改，因此我们建议您改为将 Google DNS 名称列入许可名单。以下是我们建议添加到许可名单中的常见 DNS 名称列表：

  • *.1e100.net
  • *.google.com
  • *.gstatic.com
  • *.googleapis.com
  • *.googleusercontent.com
  • *.appspot.com
  • *.gvt1.com

确定代理或防火墙丢弃数据包

如需识别客户端与 Google Front End (GFE) 之间的所有数据包跳转，请在客户端机器上运行 traceroute 命令，该命令可以突出显示丢弃面向 GFE 的数据包的服务器。以下是 traceroute 命令示例：

traceroute -T -p 443 bigquery.googleapis.com

如果问题与特定 IP 地址相关，还可以识别特定 GFE IP 地址的数据包跳数：

traceroute -T -p 443 142.250.178.138

如果存在 Google 端超时问题，您会看到请求一直到达 GFE。

如果您发现数据包从未到达 GFE，请与您的网络管理员联系，以解决此问题。

生成 PCAP 文件并分析防火墙或代理

生成数据包捕获文件 (PCAP) 并分析该文件，以确保防火墙或代理未过滤掉发往 Google IP 的数据包，并允许数据包到达 GFE。

以下是可以使用 tcpdump 工具运行的示例命令：

tcpdump -s 0 -w debug.pcap -K -n host bigquery.googleapis.com

针对间歇性连接问题设置重试

在某些情况下，GFE 负载均衡器可能会丢弃来自客户端 IP 的连接，例如，如果它检测到 DDoS 流量模式，或者如果负载均衡器实例正在缩减，这可能会导致端点 IP 被回收。如果 GFE 负载均衡器断开连接，客户端需要捕获超时请求并重试向 DNS 端点发送请求。请确保在请求最终成功之前不使用相同的 IP 地址，因为该 IP 地址可能已更改。

如果您发现 Google 端持续超时且重试不起作用的问题，请与 Cloud Customer Care 联系，并确保附上通过运行数据包捕获工具（如 tcpdump）生成的新 PCAP 文件。

后续步骤