SuperAPI AI 工具集
合作ENAI 行业资讯收藏
Skill 广场bigquery-optimization

bigquery-optimization

数据与分析

查看项目

更新于 10-14 22:44

技能介绍

用途

BigQuery Optimization 是一个面向 Google BigQuery 环境的优化工作流技能，用于指导用户评估与改进 BigQuery 的容量规划、存储布局和 SQL 查询效率。它属于三支柱运维套件之一，与 bigquery-observability（可观测性）、bigquery-troubleshooting（故障排查）配套使用。

前置条件与环境设置

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

  • 安装并配置 Google Cloud SDK。
  • 设置活动项目：gcloud config set project {project_id}
  • 启用 BigQuery 与 BigQuery Reservation API：
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 Billing 账户；确保具备相应 IAM 角色：roles/bigquery.admin 或 roles/bigquery.resourceAdmin（预留与容量承诺管理）、roles/bigquery.dataEditor 或 roles/bigquery.admin（修改表结构、分区、聚簇与存储计费模型）、roles/bigquery.jobUser（运行评估查询）。
  • 配套技能安装：若环境中尚未安装配套技能，可安装完整套件：
npx skills add google/skills --skill bigquery-observability --skill bigquery-optimization --skill bigquery-troubleshooting

若未安装 bigquery-observability，本技能及其参考指南中的优化准则、DDL 模板与决策模型均为自包含，可直接使用。

主要能力与工作流

根据用户请求的优化重点，选择相应工作流：

  1. 遥测与可观测性基线：如需原始使用遥测、INFORMATION_SCHEMA 查询与基线指标计算，参考 bigquery-observability。

  2. 容量与版本建模：评估从 On-Demand 迁移到 Editions 的成本效率，以及调整活动 Edition 预留、基线承诺与自动扩缩上限。需阅读 references/capacity_planning_editions.md，提供 BigQuery 内置推荐 UI（如 Slot Estimator）的深链接，并引导用户：导航到 Slot Estimator 标签页、选择 'On-Demand' 作为分析历史查询量的来源、查看 Cost-Optimized Recommendations 与 Slot Usage Chart。

  3. 表与存储优化：从计费模型、物理布局和生命周期角度优化存储成本。

    • 计费架构：阅读 references/storage_billing_models.md，评估聚合压缩比（如美国区 >2:1 阈值）以建议 Physical 与 Logical 计费，注意盈亏平衡比取决于具体区域费率与企业合同。提供 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，定位非活跃数据并定义精确的 TTL 分区过期、数据集过期与 Time Travel 窗口缩减。
    • 加速结构：阅读 references/search_indexes.md 判断何时为 STRING 或 JSON 数据的选择性查找建议搜索索引，编写 CREATE SEARCH INDEX DDL，并说明如何验证索引覆盖与使用情况；阅读 references/materialized_views.md 判断何时为大型基表上的重复聚合或连接建议物化视图，编写 CREATE MATERIALIZED VIEW DDL，并说明如何验证智能调优是否使用它；阅读 references/bi_engine.md 判断何时为 BI 仪表板加速建议 BI Engine 与物化视图，诊断 PARTIAL 或 DISABLED BI Engine 回退原因（bi_engine_statistics），并将 BI Engine 与预连接或预聚合的物化视图结合使用。

  4. SQL 优化：优化单条 SQL 查询以减少 slot-time 与读取数据量。按 references/sql_optimization.md 中的说明，向用户提供重写 SQL 查询的建议。

执行护栏

  • 术语与成本表述：绝不承诺或保证“降低成本”或“减少支出”，始终使用“优化账单”或“提升成本效率”的表述。
  • 明确范围与区域解析：始终在回复顶部声明目标 project_id 与 region。区域解析遵循三级层次：用户提示中明确指定的区域；从上下文中的数据集或资源解析出的区域；未指定时默认 us / region-us，并明确说明假设了 us，提示用户若资源在其他区域需替换。区域格式：Cloud Console 深链接中直接使用区域标识（如 region=us、region=europe-west1）；针对 INFORMATION_SCHEMA 的 SQL 查询中使用区域数据集限定符（如 region-us、region-europe-west1）。
  • 零行结果护栏：若查询 TABLE_STORAGE 且 WHERE table_schema = '{dataset_id}' 返回 0 行，不要继续空评估或零使用评估，应视为数据集可能位于其他区域或无原生表，停止并提示用户确认数据集的区域位置。
  • 填充具体参数：生成 URL 和 SQL 查询时，始终将已知的 project_id 和 region 值直接代入代码与链接，不要留下 {project_id} 或 {location} 占位符让用户手动编辑。
  • 禁止自主购买或财务变更：绝不提供用于自主购买年度承诺、更改 Edition 层级绑定或修改存储计费模型的可执行脚本（如 bq update --storage_billing_model=... 等 gcloud 或 bq shell 命令）。始终引导用户通过 Cloud Console UI 手动执行承诺购买、预留变更与存储计费模型更新。

适用场景

适用于需要评估 BigQuery 容量与版本成本效率、优化表存储布局与生命周期、选择加速结构（搜索索引、物化视图、BI Engine）以及重写 SQL 查询以降低 slot-time 与数据读取量的场景。

限制

本技能不提供自动购买承诺或修改存储计费模型的可执行脚本；相关财务操作须由用户在 Cloud Console 手动完成。区域未指定时默认 us，可能不适用于其他区域资源。TABLE_STORAGE 查询返回 0 行时需先确认区域，不能据此判断零使用。