bigquery-optimization

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

BigQuery Optimization Workflow

BigQuery 优化工作流

Prerequisites & Environment Setup

前置条件与环境搭建

Before executing optimization analyses, evaluating editions, or applying DDL modifications:

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 BigQuery Reservation APIs are enabled:

bash

gcloud services enable \
    bigquery.googleapis.com bigqueryreservation.googleapis.com

Authentication: Authenticate the environment:
- CLI tools and
```
bq
```
  commands:
```
gcloud auth login
```
- SDKs and automation:
```
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.admin
```
  or
```
  roles/bigquery.resourceAdmin
```
  : Reservation and capacity commitment management.
- ```
roles/bigquery.dataEditor
```
    or
```
roles/bigquery.admin
```
    : Modifying table schemas, partitioning, clustering, and storage billing models.
  - ```
  roles/bigquery.jobUser
```
  : Running evaluation queries.
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).

在执行优化分析、评估Edition或应用DDL修改之前，请完成以下准备：

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

API启用：确保已启用BigQuery和BigQuery Reservation API：

bash

gcloud services enable \
    bigquery.googleapis.com bigqueryreservation.googleapis.com

身份验证：完成环境身份验证：
- CLI工具和
```
bq
```
  命令：
```
gcloud auth login
```
- SDK与自动化场景：
```
gcloud auth application-default login
```
- 服务账号：设置
```
GOOGLE_APPLICATION_CREDENTIALS="/path/to/key.json"
```
计费与IAM角色：
- 确认
```
{project_id}
```
  已绑定有效的Google Cloud计费账号。
- 确保已分配合适的IAM角色：
  - ```
  roles/bigquery.admin
```
  或
```
  roles/bigquery.resourceAdmin
```
  ：用于预留资源和容量承诺管理。
- ```
roles/bigquery.dataEditor
```
    或
```
roles/bigquery.admin
```
    ：用于修改表结构、分区、聚簇以及存储计费模式。
  - ```
  roles/bigquery.jobUser
```
  ：用于运行评估查询。
配套Skill安装：本Skill是三大运维工具套件（
```
bigquery-observability
```
、
```
bigquery-optimization
```
、
```
bigquery-troubleshooting
```
）的组成部分。如果你的环境中尚未安装任何配套Skill，请安装完整套件：
bash
```
npx skills add google/skills --skill bigquery-observability --skill bigquery-optimization --skill bigquery-troubleshooting
```
（如果未安装
bigquery-observability
，可直接使用下方参考章节中提供的独立基准公式和查询模板。）

Workflows

工作流

Determine the optimization focus of the user's request and follow the relevant workflow:

Telemetry & Observability Baseline: For direct raw usage telemetry,
```
INFORMATION_SCHEMA
```
queries, and baseline metric calculations, consult bigquery-observability (
```
bigquery_observability
```
). If the
```
bigquery-observability
```
companion skill is not available in the active environment, all optimization guidelines, DDL templates, and decision models across this skill and its reference guides are fully self-contained.
Capacity & Editions Modeling: Evaluate the cost-efficiency of migrating workloads from On-Demand to Editions, as well as rightsizing active Edition reservations, baseline commitments, and autoscaling caps.
- Instructions: Read
```
references/capacity_planning_editions.md
```
  to provide deep links to BigQuery's built-in recommendation UIs (e.g., Slot Estimator) and guide the user through UI navigation: 1. navigate to the Slot Estimator tab, 2. select 'On-Demand' as the source to analyze historical query volume, and 3. review the Cost-Optimized Recommendations and Slot Usage Chart.
Table & Storage Optimization: Optimize storage costs from a billing model, physical layout, and lifecycle perspective.
- Billing Architecture: Read
```
references/storage_billing_models.md
```
  for guidance on evaluating aggregate compression ratios (e.g. >2:1 threshold in US) to recommend Physical vs. Logical billing, noting that the break-even ratio depends on specific regional rates and custom enterprise contracts. When providing
```
TABLE_STORAGE
```
  queries, always scope with
```
WHERE table_schema = '{dataset_id}'
```
  , use the regional dataset view, and warn that 0 rows indicates a region mismatch or lack of native tables rather than zero billable usage.
- Partitioning & Clustering Strategy: Read
```
references/table_partitioning_clustering.md
```
  to generate production DDL templates (CREATE TABLE, CTAS migrations for unpartitioned tables, and modifying clustering specifications), enforce pruning with
```
require_partition_filter = true
```
  , and manage partition limits (up to 10,000 partitions/table).
- Lifecycle Management: Read
```
references/storage_lifecycle_management.md
```
  to pinpoint inactive data and define precise Time-to-Live (TTL) partition expirations, dataset expirations, and Time Travel window reductions.
SQL Optimization: Optimize individual SQL queries to reduce slot-time and the amount of data read.
- Instructions: Follow the instructions in
```
references/sql_optimization.md
```
  to provide recommendations to the user on how to rewrite their SQL query to reduce slot-time and the amount of data read.

根据用户请求的优化重点，遵循对应的工作流：

遥测与可观测性基准： 如需直接获取原始使用遥测数据、执行
```
INFORMATION_SCHEMA
```
查询和基准指标计算，请参考bigquery-observability（
```
bigquery_observability
```
）。如果当前环境中没有
```
bigquery-observability
```
配套Skill，本Skill及其参考指南中的所有优化指南、DDL模板和决策模型都是完全自包含的，可独立使用。
容量与Edition建模： 评估工作负载从On-Demand迁移到Editions的成本效益，以及调整当前Edition预留资源、基准承诺和自动扩缩容上限的合理性。
- 操作说明： 阅读
```
references/capacity_planning_editions.md
```
  ，获取BigQuery内置推荐UI（如Slot Estimator）的深度链接，并引导用户完成UI操作：1. 导航至Slot Estimator标签页，2. 选择「On-Demand」作为数据源以分析历史查询量，3. 查看成本优化建议和槽位使用图表。
表与存储优化： 从计费模式、物理布局和生命周期角度优化存储成本。
- 计费架构： 阅读
```
references/storage_billing_models.md
```
  ，获取评估总压缩比（例如美国区域的2:1阈值）的指导，以推荐物理计费还是逻辑计费，请注意盈亏平衡比取决于具体的区域费率和企业定制合同。提供
```
TABLE_STORAGE
```
  查询时，始终使用
```
WHERE table_schema = '{dataset_id}'
```
  限定范围，使用区域数据集视图，并提示用户：返回0行结果表示区域不匹配或没有原生表，而非计费使用量为零。
- 分区与聚簇策略： 阅读
```
references/table_partitioning_clustering.md
```
  ，生成生产级DDL模板（CREATE TABLE、非分区表的CTAS迁移、修改聚簇规范），通过
```
require_partition_filter = true
```
  强制分区裁剪，以及管理分区上限（单表最多10,000个分区）。
- 生命周期管理： 阅读
```
references/storage_lifecycle_management.md
```
  ，定位非活跃数据，定义精准的Time-to-Live（TTL）分区过期策略、数据集过期策略，以及缩短Time Travel窗口。
SQL优化： 优化单个SQL查询，以减少槽位时间和数据读取量。
- 操作说明： 遵循
```
references/sql_optimization.md
```
  中的说明，为用户提供SQL查询重写建议，以减少槽位时间和数据读取量。

Execution Guardrails

执行约束

Terminology & Cost Framing: Never promise or guarantee "cost-reduction" or "reducing expenditure." Always frame recommendations using the terminology "optimizing your bill" or "improving cost-efficiency."
Explicit Scope Framing & Region Resolution: Always state the target
```
project_id
```
and
```
region
```
at the very top of your response so the user immediately knows the exact scope being evaluated. Follow this 3-tier resolution hierarchy:
1. Explicit Region: Use the region specified in the user's prompt (e.g.,
```
europe-west1
```
  ).
2. Contextual Region: Resolve the region from the specific dataset or resource mentioned in the context.
3. Unspecified Fallback: Default to
```
us
```
  /
```
region-us
```
  , explicitly state that
```
us
```
  was assumed as the default, and instruct the user to substitute their region if their resources reside elsewhere. Region Formatting: In Cloud Console deep links, use the region identifier directly (e.g.,
```
region=us
```
  ,
```
region=europe-west1
```
  ). In SQL queries against
```
INFORMATION_SCHEMA
```
  , use the regional dataset qualifier (e.g.,
```
region-us
```
  ,
```
region-europe-west1
```
  ).
Zero-Row Result Guard: If querying
```
TABLE_STORAGE
```
with
```
WHERE table_schema = '{dataset_id}'
```
returns 0 rows, do not proceed with an empty or zero-usage evaluation. Treat this as an indicator that the dataset may reside in a different region or have no native tables; stop and prompt the user to confirm the dataset's regional location.
Populate Concrete Parameters: When generating URLs and SQL queries, always substitute known
```
project_id
```
and
```
region
```
values directly into the code and links. Never leave literal
```
{project_id}
```
or
```
{location}
```
placeholders for the user to manually edit.
No Autonomous Purchasing or Financial Mutations: Never provide the user with executable scripts (e.g.,
```
gcloud
```
or
```
bq
```
shell commands like
```
bq update --storage_billing_model=...
```
) designed to autonomously purchase annual commitments, alter edition tier bindings, or mutate storage billing models. Always guide the user to execute commitment purchases, reservation changes, and storage billing model updates manually via the Cloud Console UI.

术语与成本表述： 绝对不要承诺或保证「成本降低」或「减少支出」。始终使用**「优化你的账单」或「提升成本效益」**的表述来给出建议。
明确范围界定与区域解析： 始终在回复的最开头说明目标
```
project_id
```
和
```
region
```
，让用户立即知道评估的准确范围。遵循以下三级解析层级：
1. 显式指定区域： 使用用户提示中指定的区域（例如
```
europe-west1
```
  ）。
2. 上下文推断区域： 从上下文中提到的特定数据集或资源解析出区域。
3. 未指定时的默认值： 默认使用
```
us
```
  /
```
region-us
```
  ，明确说明已默认采用
```
us
```
  区域，并提示用户如果资源位于其他区域请替换为对应区域。区域格式： 在Cloud Console深度链接中，直接使用区域标识符（例如
```
region=us
```
  、
```
region=europe-west1
```
  ）。在针对
```
INFORMATION_SCHEMA
```
  的SQL查询中，使用区域数据集限定符（例如
```
region-us
```
  、
```
region-europe-west1
```
  ）。
零行结果防护： 如果使用
```
WHERE table_schema = '{dataset_id}'
```
查询
```
TABLE_STORAGE
```
返回0行，不要继续进行空或零使用量的评估。应将此视为数据集可能位于其他区域或没有原生表的信号；停止操作并提示用户确认数据集的区域位置。
填充具体参数： 生成URL和SQL查询时，始终将已知的
```
project_id
```
和
```
region
```
值直接代入代码和链接中。绝对不要留下字面量
```
{project_id}
```
或
```
{location}
```
占位符让用户手动编辑。
禁止自动采购或财务变更： 绝对不要向用户提供可自动执行的脚本（例如
```
gcloud
```
或
```
bq
```
shell命令，如
```
bq update --storage_billing_model=...
```
），用于自动购买年度承诺、更改Edition层级绑定或修改存储计费模式。始终引导用户通过Cloud Console UI手动执行承诺购买、预留资源变更和存储计费模式更新。