bigquery-troubleshooting

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

BigQuery Troubleshooting

BigQuery 故障排查

Prerequisites & Environment Setup

前置条件与环境搭建

Before running diagnostic queries or investigating incident telemetry:

Google Cloud SDK: Ensure the Google Cloud SDK is installed and configured.
Project Selection: Set the active Google Cloud project:
bash
```
gcloud config set project {project_id}
```

API Enablement: Ensure BigQuery and Cloud Monitoring APIs are enabled:

bash

gcloud services enable bigquery.googleapis.com monitoring.googleapis.com

Authentication: Authenticate the environment:
- CLI commands (
```
bq show -j
```
  ):
```
gcloud auth login
```
- SDKs and automated diagnostic scripts:
```
gcloud auth application-default login
```
- Service accounts: Set
```
GOOGLE_APPLICATION_CREDENTIALS="/path/to/key.json"
```
Billing & IAM Roles:
- Verify an active Google Cloud Billing account is attached to
```
{project_id}
```
  .
- Ensure appropriate IAM roles:
  - ```
  roles/bigquery.jobUser
```
  : Executing diagnostic queries.
- ```
roles/bigquery.resourceViewer
```
    or
```
roles/bigquery.admin
```
    : Inspecting reservation and job execution telemetry.
  - ```
  roles/monitoring.viewer
```
  : Cloud Monitoring metrics.
- ```
roles/billing.viewer
```
    : Cloud Billing reports and cost attribution.
Companion Skills Installation: This skill is part of a 3-pillar operations suite (
```
bigquery-observability
```
,
```
bigquery-optimization
```
,
```
bigquery-troubleshooting
```
). If any companion skill is not yet installed in your environment, install the full suite:
bash
```
npx skills add google/skills --skill bigquery-observability --skill bigquery-optimization --skill bigquery-troubleshooting
```
(If
bigquery-observability
is not installed, use the self-contained baseline formulas and query templates provided directly in the reference sections below).

在运行诊断查询或调查事件遥测数据之前：

Google Cloud SDK：确保已安装并配置 Google Cloud SDK。
项目选择：设置当前活跃的Google Cloud项目：
bash
```
gcloud config set project {project_id}
```

API启用：确保已启用BigQuery和Cloud Monitoring API：

bash

gcloud services enable bigquery.googleapis.com monitoring.googleapis.com

身份认证：完成环境身份认证：

CLI命令（
```
bq show -j
```
）：
```
gcloud auth login
```
SDK及自动化诊断脚本：
```
gcloud auth application-default login
```

服务账号：设置

GOOGLE_APPLICATION_CREDENTIALS="/path/to/key.json"

计费与IAM角色：
- 确认
```
{project_id}
```
  已绑定有效的Google Cloud计费账号。
- 确保已分配相应的IAM角色：
  - ```
  roles/bigquery.jobUser
```
  ：执行诊断查询。
- ```
roles/bigquery.resourceViewer
```
    或
```
roles/bigquery.admin
```
    ：查看预留资源与作业执行遥测数据。
  - ```
  roles/monitoring.viewer
```
  ：查看Cloud Monitoring指标。
- ```
roles/billing.viewer
```
    ：查看Cloud Billing报表与成本归因。
配套技能安装：本技能是三大运维工具套件（
```
bigquery-observability
```
、
```
bigquery-optimization
```
、
```
bigquery-troubleshooting
```
）的组成部分。如果你的环境中尚未安装任何配套技能，请安装完整套件：
bash
```
npx skills add google/skills --skill bigquery-observability --skill bigquery-optimization --skill bigquery-troubleshooting
```
（如果未安装
bigquery-observability
，可直接使用下方参考章节中提供的独立基线公式与查询模板。）

Workflow

工作流

Scope & Symptom Identification: Identify the primary symptom, target
```
project_id
```
,
```
region
```
,
```
reservation_id
```
, or
```
job_id
```
, and domain (Performance, Compute Cost, or Storage Cost). If the request falls outside incident diagnosis or asks for a sibling domain, follow Routing Boundaries below.
Telemetry Tool Selection: Follow the tool-selection guidance in bigquery-observability (
```
bigquery_observability
```
) to select the appropriate telemetry interface (REST API
```
bq show --location={location} -j {project_id}:{job_id}
```
for single-job stage bottlenecks vs.
```
INFORMATION_SCHEMA
```
for system-wide factors). Diagnostic workflows, symptom-to-cause mappings, key tables/fields, CLI triage commands, and remediation levers are fully defined in this skill. For pre-composed SQL query templates and full schema dictionaries, consult
```
bigquery-observability
```
.
Open-Ended Triage (Stage 1 Baseline Scan & Conversational Gate): When the user inquiry is open-ended or vague (e.g. "Why is BigQuery slow today?" or "Why did my bill spike?"), execute a bounded high-level baseline scan to isolate the affected domain before drilling into deep-dive diagnostics:
- Bounded Initial Scan: Follow the baseline scan guidance in the corresponding domain reference under Domain References. Ensure initial queries are strictly bounded (e.g. 7-day Period-over-Period with partition and job-type filters; for unspecified cost spikes, scan the 3 primary vectors: On-Demand TiB, Capacity slot-hours, and Storage GiB) to keep diagnostic telemetry overhead minimal.
- Conversational Gate: Factually summarize high-level baseline findings first and propose 2–3 focused drill-down options rather than dumping downstream sub-vector queries unsolicited.
Domain Deep Dive & Comparative Analysis: Execute the step-by-step diagnostic workflow defined in the corresponding domain reference file listed under Domain References below, then run the corresponding query from bigquery-observability following its
```
INFORMATION_SCHEMA
```
best practices to isolate the root cause via comparative analysis against a normal baseline.

范围与症状识别：明确主要症状、目标
```
project_id
```
、
```
region
```
、
```
reservation_id
```
或
```
job_id
```
，以及问题领域（性能、计算成本或存储成本）。如果请求不属于事件诊断范畴或涉及其他相关领域，请遵循下方的路由边界规则。
遥测工具选择：参考bigquery-observability （
```
bigquery_observability
```
）中的工具选择指南，选择合适的遥测接口（单作业阶段瓶颈使用REST API
```
bq show --location={location} -j {project_id}:{job_id}
```
，系统层面因素使用
```
INFORMATION_SCHEMA
```
）。诊断工作流、症状到根因的映射、关键表/字段、CLI排查命令与修复手段均在本技能中完整定义。如需预编写的SQL查询模板与完整schema字典，请查阅
```
bigquery-observability
```
。
开放式排查（第一阶段：基线扫描与对话门槛）：当用户的问题是开放式或模糊的（例如*"为什么今天BigQuery这么慢？"* 或*"为什么我的账单飙升了？"*），先执行有边界的高层级基线扫描，定位受影响的领域，再深入进行诊断：
- 有边界初始扫描：遵循领域参考中对应领域参考文档的基线扫描指南。确保初始查询有严格的边界限制（例如带分区和作业类型过滤的7天环比分析；对于未明确的成本飙升，扫描3个主要维度：按需TiB、容量槽小时数、存储GiB），以将诊断遥测的开销降至最低。
- 对话门槛：先客观总结高层级的基线扫描结果，提出2-3个针对性的深入分析选项，而不是主动抛出大量下游子维度的查询。
领域深入分析与对比分析：执行下方领域参考中对应领域参考文件定义的分步诊断工作流，然后遵循
```
INFORMATION_SCHEMA
```
最佳实践运行bigquery-observability中的对应查询，通过与正常基线的对比分析定位根本原因。

Performance Context: The Relativity of "Slow"

性能背景："慢"是相对的

Performance is relative. Always approach performance troubleshooting as a comparative exercise: identify a comparable past execution, compare the statistics, and isolate which dimension shifted between a fast baseline and the slow execution:

Data Processed: Data volume increase, partition/cluster pruning changes, data skew, input record amplification.
Underlying Definitions: View changes, schema modifications.
System Contention: Noisy neighbors, saturated capacity (>95% slot utilization), idle slot availability, concurrent query spikes.
Configuration Changes: Slot capacity/autoscale max slots changes, expired capacity commitments, idle slot setting changes, or reservation reassignments.

性能是相对的。性能故障排查始终应作为对比性工作开展：找到可对比的历史执行记录，对比统计数据，定位在快速基线与缓慢执行之间发生变化的维度：

数据处理量：数据量增长、分区/集群裁剪变化、数据倾斜、输入记录放大。
底层定义：视图变更、schema修改。
系统争用：噪声邻居、容量饱和（槽位利用率>95%）、空闲槽位可用性、并发查询突增。
配置变更：槽位容量/自动扩缩最大槽数变更、容量承诺到期、空闲槽位设置变更或预留资源重新分配。

Cost Context: The 4-Step Diagnostic Funnel

成本背景：四步诊断漏斗

Cost troubleshooting requires tracing physical resource consumption (Slot-Hours, TiB Billed, GiB Stored) rather than fluctuating contract rates:

Gather: Determine scope and pull 7-day PoP (or explicit MoM / 180-day) baseline metrics.
Isolate: Pinpoint whether spend surged from query volume, a single "Bully Query", BQML 50x multipliers, uncovered PAYG baselines, autoscaling bursts, 90-day storage timer resets, or physical Fail-Safe retention drain.

Explain: Correlate with administrative events (

INFORMATION_SCHEMA.RESERVATION_CHANGES

INFORMATION_SCHEMA.CAPACITY_COMMITMENT_CHANGES_BY_PROJECT

INFORMATION_SCHEMA.SCHEMATA_OPTIONS

, or actor

user_email

query_hash

Remediate: Deliver actionable levers (partition filter enforcement, query caps, commitment purchases, or Time Travel reduction).

成本故障排查需要追踪物理资源消耗（槽小时数、计费TiB、存储GiB），而非波动的合同费率：

收集：确定范围，提取7天环比基线指标（如用户明确要求，可使用月度对比/180天基线）。
定位：明确支出飙升的原因是查询量增长、单个"霸王查询"（Bully Query）、BQML 50倍计费倍率、未覆盖的按需付费（PAYG）基线、自动扩缩突增、90天存储计时器重置，还是物理Fail-Safe保留期消耗。

归因：与管理事件关联分析（

INFORMATION_SCHEMA.RESERVATION_CHANGES

、

INFORMATION_SCHEMA.CAPACITY_COMMITMENT_CHANGES_BY_PROJECT

、

INFORMATION_SCHEMA.SCHEMATA_OPTIONS

，或操作主体

user_email

query_hash

）。

修复：提供可落地的优化手段（分区过滤强制生效、查询配额限制、购买容量承诺，或缩短Time Travel时长）。

Domain References

领域参考

Performance Troubleshooting

性能故障排查

Resource Contention & Performance Slowness (
```
references/performance_resource_contention.md
```
): Diagnostic workflows for isolating single-job stage bottlenecks (
```
slot_contention
```
,
```
spill_to_disk
```
), cohort baseline comparisons (
```
normalized_literals
```
), incident window discovery, 1-second reservation slot saturation, timeframe contention comparisons, fleet performance variance, and table-level concurrency.
Capacity & Configuration Changes (
```
references/performance_config_changed.md
```
): Diagnostic workflows for auditing reservation
```
slot_capacity
```
and
```
autoscale.max_slots
```
edits, tracking active capacity commitment timelines, diagnosing reservation assignment modifications, and evaluating autoscaling headroom saturation.
Execution Graph & Query Plan Troubleshooting (
```
references/query_plan_execution_graph.md
```
): Diagnostic workflows for investigating single-job stage bottlenecks (
```
bq show
```
point-lookups), isolating slowest stages (
```
end_ms - start_ms
```
), substep intermediate variable disambiguation (
```
$1
```
,
```
$2
```
), mandatory bytes scanned vs records read corrections, and UI execution graph grounding concepts.

资源争用与性能缓慢 （
```
references/performance_resource_contention.md
```
）：提供诊断工作流，用于定位单作业阶段瓶颈（
```
slot_contention
```
、
```
spill_to_disk
```
）、同类基线对比（
```
normalized_literals
```
）、事件窗口发现、1秒级预留槽位饱和分析、时间窗口争用对比、集群性能差异分析以及表级并发分析。
容量与配置变更 （
```
references/performance_config_changed.md
```
）：提供诊断工作流，用于审计预留资源的
```
slot_capacity
```
和
```
autoscale.max_slots
```
修改、追踪有效容量承诺的时间线、诊断预留资源分配变更，以及评估自动扩缩余量饱和情况。
执行图与查询计划故障排查 （
```
references/query_plan_execution_graph.md
```
）：提供诊断工作流，用于调查单作业阶段瓶颈（
```
bq show
```
点查询）、定位最慢阶段（
```
end_ms - start_ms
```
）、子步骤中间变量歧义消除（
```
$1
```
、
```
$2
```
）、强制扫描字节数与读取记录数校正，以及UI执行图基础概念说明。

Cost Troubleshooting

成本故障排查

On-Demand Compute Costs (
```
references/cost_compute_ondemand.md
```
): Diagnostic workflows for unpartitioned runaway scans (the "Bully Query"), hidden Row-Level Security (RLS) redaction gaps, BigQuery ML (BQML) 50x model training rate multipliers, and user/service account query quotas.
Capacity (Editions) Compute Costs (
```
references/cost_compute_capacity.md
```
): Diagnostic workflows for uncovered baseline slot penalties (baseline > commitments), reservation baseline reductions triggering autoscale surges (
```
RESERVATION_BASELINE_CHANGED
```
), autoscaler thrashing from batch cron spikes, and serverless Apache Spark stored procedure slot-hours.
Storage Footprint & Retention Costs (
```
references/cost_storage.md
```
): Diagnostic workflows for historical partition 90-day timer resets (the DML trap), unpartitioned table active data traps, physical Time Travel and Fail-Safe churn on daily overwrites, and dropped table Fail-Safe drain periods.

按需计算成本（
```
references/cost_compute_ondemand.md
```
）：提供诊断工作流，用于排查无分区的失控扫描（即"霸王查询"）、隐藏的行级安全（RLS）脱敏漏洞、BigQuery ML（BQML）模型训练50倍计费倍率，以及用户/服务账号的查询配额问题。
容量版（Editions）计算成本 （
```
references/cost_compute_capacity.md
```
）：提供诊断工作流，用于排查未覆盖的基线槽位惩罚（基线 > 承诺量）、预留基线下调触发的自动扩缩突增（
```
RESERVATION_BASELINE_CHANGED
```
）、批量定时任务突增导致的自动扩缩器抖动，以及无服务器Apache Spark存储过程的槽小时消耗问题。
存储占用与保留成本（
```
references/cost_storage.md
```
）：提供诊断工作流，用于排查历史分区90天计时器重置（即DML陷阱）、无分区表的活跃数据陷阱、每日覆盖写入导致的物理Time Travel和Fail-Safe churn消耗，以及已删除表的Fail-Safe消耗期问题。

Routing Boundaries

路由边界

If a user request shifts outside incident diagnosis during troubleshooting, execute the corresponding handoff:

SQL Query Optimizations: When the user asks to optimize the SQL query (e.g., rewriting joins or eliminating
```
SELECT *
```
), hand off to
```
bigquery-optimization
```
.
Raw Telemetry & Schema Retrieval: When the user asks for standalone
```
INFORMATION_SCHEMA
```
queries without an active performance regression or incident (e.g. general telemetry queries), hand off to
```
bigquery-observability
```
.
Proactive Capacity & Storage Planning: When the user requests future reservation sizing, commitment purchasing, or storage billing model evaluations, hand off to
```
bigquery-optimization
```
.

如果在故障排查过程中，用户的请求超出了事件诊断的范畴，请执行相应的转接：

SQL查询优化：当用户要求优化SQL查询（例如重写连接或消除
```
SELECT *
```
），转接至
```
bigquery-optimization
```
。
原始遥测与schema查询：当用户需要独立的
```
INFORMATION_SCHEMA
```
查询，但不存在当前的性能下降或事件（例如通用遥测查询），转接至
```
bigquery-observability
```
。
主动容量与存储规划：当用户要求进行未来的预留资源规模估算、容量承诺购买或存储计费模式评估时，转接至
```
bigquery-optimization
```
。