Bigquery Slot Cost Optimizer

google/skills/skills/cloud/bigquery-slot-cost-optimizer

作者 google4b940dd4b5f5759794cc257266cb8cb7524b7a66無授權條款24K 個星標收錄於 2026年11月20日更新於 2026年11月20日儲存庫今天更新

Analyzes Google Cloud BigQuery slot consumption, query costs, and execution bottlenecks using INFORMATION_SCHEMA. Use when diagnosing slow BigQuery queries, slot starvation, high on-demand query costs, unpartitioned table scans, or join performance issues. Don't use for generic BigQuery administration (use bigquery-basics), BigQuery ML (use bigquery-ai-ml), or DataFrame operations (use bigquery-bigframes).

AI 產生的概覽

分析 BigQuery 槽位用量、查詢成本與執行瓶頸,並提出最佳化建議。

功能
執行 Python 腳本,從 BigQuery 的 INFORMATION_SCHEMA.JOBS_BY_PROJECT 擷取歷史查詢遙測資料,辨識槽位爭用、笛卡兒連接與未分割資料表全表掃描等問題。腳本依使用者提供的隨需掃描單價與槽位小時單價計算槽位小時數與成本估算,並輸出表格或 JSON 報告。決策樹會把每類問題導向對應的修復手冊,內含診斷 SQL、改寫模式,以及改寫後查詢的試跑驗證。
適用情境
適用於診斷 BigQuery 查詢過慢或費用過高、槽位不足與排隊、記憶體溢寫、連接造成的資料列暴增,以及未分割資料表的全表掃描。不適用於一般 BigQuery 管理、BigQuery ML 或 DataFrame 操作。
執行需求
需要 Google Cloud SDK 與 google-cloud-bigquery Python 用戶端、已驗證的 gcloud/ADC 工作階段、有效的帳單帳戶、已啟用的 BigQuery API,以及 bigquery.jobUser 和 bigquery.resourceViewer 兩個 IAM 角色。需連網存取 BigQuery,並透過命令列參數傳入區域與價格費率;另提供離線模擬資料模式。附帶 scripts/slot_analyzer.py 腳本。

BigQuery slot and cost optimizer

This skill equips AI agents and cloud engineers with procedural heuristics to analyze BigQuery resource consumption, calculate slot hours, identify slot contention and queueing, mitigate Cartesian joins, and optimize unpartitioned table scans.

Trigger conditions and intent mapping

Activate this skill whenever the user asks to:

  • "Optimize BigQuery query performance or reduce slot usage"
  • "Find the most expensive queries in BigQuery"
  • "Diagnose BigQuery slot contention or queueing"
  • "Fix slow running BigQuery jobs or memory spillage"
  • "Detect Cartesian joins or row count explosions in BigQuery"
  • "Identify unpartitioned table scans or missing partition filters"

Prerequisites and environment setup

Before executing this skill, ensure the environment is configured with the necessary SDKs, permissions, and billing:

  1. Cloud SDK and client library installation:

  2. Project, billing, and regional selection:

    • Set the active project:

      bash
      gcloud config set project <PROJECT_ID>
    • Important: the target Google Cloud project must have an active Cloud Billing account attached.

    • Regional selection: specify the target BigQuery dataset location or execution region, as BigQuery INFORMATION_SCHEMA views are strictly region-scoped (for example, multi-regions like region-us or region-eu, or single regions like region-us-central1). Querying the wrong region returns empty job telemetry. Pass the matching region via --region (the script automatically normalizes location names like us-central1 to region-us-central1). For valid location identifiers, see BigQuery locations.

  3. API enablement:

    • Enable the BigQuery API on the project:

      bash
      gcloud services enable bigquery.googleapis.com
  4. Authentication setup:

    • Authenticate the local gcloud environment and configure Application Default Credentials (ADC):

      bash
      gcloud auth logingcloud auth application-default login
  5. IAM roles and permissions:

    • The executing principal requires the following minimum IAM roles:
      • roles/bigquery.jobUser: grants permission to run queries and analyze telemetry.
      • roles/bigquery.resourceViewer: grants read-only access to query metadata in INFORMATION_SCHEMA.JOBS_BY_PROJECT and capacity reservations.
  6. Pricing reference:

    • Cost estimates in this skill are for planning purposes. Before running scripts/slot_analyzer.py, retrieve live BigQuery billing rates at runtime from official Google Cloud BigQuery Pricing (and consult BigQuery editions introduction for edition capabilities) after considering user-specific parameters such as target region, chosen edition (Standard, Enterprise, Enterprise Plus), and commitment tier (Pay-as-you-go, 1-year, 3-year). Pass these runtime-fetched rates explicitly via --ondemand-rate <USD_PER_TIB> and --slot-hour-rate <USD_PER_SLOT_HOUR>.

Diagnostic execution workflow

Execute automated telemetry extraction

Run scripts/slot_analyzer.py to pull and analyze historical query telemetry from INFORMATION_SCHEMA.JOBS_BY_PROJECT, passing the runtime-retrieved pricing rates for your specific region, edition, and commitment tier:

bash
# General analysis passing live regional pricing rates fetched from BigQuery pricingpython3 scripts/slot_analyzer.py --project-id <PROJECT_ID> --days 7 \  --ondemand-rate <USD_PER_TIB> --slot-hour-rate <USD_PER_SLOT_HOUR> --format table
# Output structured JSON for programmatically parsing recommendationspython3 scripts/slot_analyzer.py --project-id <PROJECT_ID> --days 7 \  --ondemand-rate <USD_PER_TIB> --slot-hour-rate <USD_PER_SLOT_HOUR> --format json
# Offline verification mode using synthetic or extracted telemetrypython3 scripts/slot_analyzer.py --mock-data-file path/to/extracted_telemetry.json \  --ondemand-rate <USD_PER_TIB> --slot-hour-rate <USD_PER_SLOT_HOUR> --format table
# Dry-run mode to inspect regional SQL querypython3 scripts/slot_analyzer.py --project-id <PROJECT_ID> --region region-us --dry-run

Run python3 scripts/slot_analyzer.py --help to inspect all supported CLI flags, focus modes (--mode), and required pricing rate arguments (--ondemand-rate per TiB and --slot-hour-rate per slot-hour).

Metric interpretation and decision tree

Evaluate the telemetry output using the following decision rules. CRITICAL MANDATE: After classifying the query issue using the decision tree below, you MUST immediately call view_file on references/remediation_playbooks.md [blocked] to read and execute the corresponding remediation playbook (Rule SLOT-001, Rule JOIN-001, or Rule PART-001) and include all mandatory diagnostic SQL queries and 4-step checklists in your response.

[Query Telemetry Analyzed]       |       +---> If wait_ratio_avg > 0.40 OR slot_contention == TRUE       |     --> Classify as slot contention and queueing (Rule SLOT-001)       |     --> MANDATORY: Read Rule SLOT-001 in references/remediation_playbooks.md       |       +---> If shuffle_output_bytes_spilled > 0 OR records_written > 10 * records_read       |     --> Classify as Cartesian join (Rule JOIN-001)       |     --> MANDATORY: Read Rule JOIN-001 in references/remediation_playbooks.md       |       +---> If total_bytes_billed > 10 GB AND no date/partition filters       |     --> Classify as unpartitioned scan (Rule PART-001)       |     --> MANDATORY: Read Rule PART-001 in references/remediation_playbooks.md       |       +---> Otherwise             --> Check BI Engine, search indexes, or materialized view opportunities             --> MANDATORY: Read references/optimization_rules.md

Remediation playbooks and architectural reference links

To minimize token consumption in SKILL.md, concrete remediation playbooks (Rule SLOT-001, Rule JOIN-001, Rule PART-001), diagnostic SQL queries, and DDL rewrite patterns are housed in references/:

  • Rule SLOT-001 (Slot contention and queueing): Rule SLOT-001 playbook [blocked]
  • Rule JOIN-001 (Cartesian and exploding joins): Rule JOIN-001 playbook [blocked]
  • Rule PART-001 (Unpartitioned scans and partition pruning): Rule PART-001 playbook [blocked]
  • Table partitioning, multi-column clustering, and BI Engine: optimization rules [blocked]
  • BigQuery search indexes and materialized views: optimization rules [blocked]

Verification and validation protocol

Before finalizing query rewrites:

Dry-run validation

Validate query syntax and calculate estimated bytes scanned without incurring cost:

python
from google.cloud import bigqueryclient = bigquery.Client()job_config = bigquery.QueryJobConfig(dry_run=True, use_query_cache=False)query_job = client.query(optimized_sql, job_config=job_config)print(f"Scanned bytes: {query_job.total_bytes_processed / (1024**3):.2f} GB")

Offline and dry-run validation

  • Offline mock telemetry verification: validate heuristic classification, slot contention detection, Cartesian join identification, and cost estimation offline using synthetic or extracted JSON telemetry payloads (--mock-data-file):

    bash
    python3 scripts/slot_analyzer.py --mock-data-file path/to/extracted_telemetry.json \  --ondemand-rate <USD_PER_TIB> --slot-hour-rate <USD_PER_SLOT_HOUR> --format table
  • CLI dry-run inspection: verify regional SQL query formation and script execution without contacting BigQuery or incurring costs:

    bash
    python3 scripts/slot_analyzer.py --project-id <PROJECT_ID> --region region-us --dry-run

來源與署名

來源:google/skills位於skills/cloud/bigquery-slot-cost-optimizer提交4b940dd

授權條款: 無授權條款

內容歸原作者所有。SourceWeft 從公開儲存庫中收錄這些內容。

在 README 中新增徽章

向讀者展示該技能已收錄於 SourceWeft,徽章連結到本頁。

「Listed on SourceWeft」徽章預覽
[![Listed on SourceWeft](https://sourceweft.com/badge/skills/gh-google-skills-bigquery-slot-cost-optimizer.svg)](https://sourceweft.com/skills/gh-google-skills-bigquery-slot-cost-optimizer?utm_source=readme&utm_medium=badge)

檢舉或申請下架