bigquery-slot-cost-optimizer

BigQuery のスロットとコストの最適化

このスキルは、BigQuery のリソース消費を分析し、スロット時間を算出し、スロットの競合やキューイングを特定し、直積結合を緩和し、パーティション分割されていないテーブルのスキャンを最適化するための手順に基づくヒューリスティックを、AI エージェントやクラウドエンジニアに提供します。

トリガー条件と意図のマッピング

次のような依頼があった場合は、このスキルを有効にします。

• 「BigQuery クエリのパフォーマンスを最適化する、またはスロットの使用量を削減する」
• 「BigQuery で最もコストのかかるクエリを見つける」
• 「BigQuery のスロット競合やキューイングを診断する」
• 「実行に時間がかかる BigQuery ジョブやメモリのスピルを解決する」
• 「BigQuery の直積結合や行数の急増を検出する」
• 「パーティション分割されていないテーブルのスキャンやパーティションフィルターの不足を特定する」

前提条件と環境設定

このスキルを実行する前に、必要な SDK、権限、課金設定が環境に整っていることを確認してください。

1. Cloud SDK とクライアントライブラリのインストール：

   • Google Cloud CLI をインストールします：Google Cloud SDK のインストールガイド

   • BigQuery Python クライアントをインストールします。

     pip install google-cloud-bigquery
     

2. プロジェクト、課金、リージョンの選択：

   • アクティブなプロジェクトを設定します。

     gcloud config set project <PROJECT_ID>
     

   • 重要：対象の Google Cloud プロジェクトには、有効な Cloud Billing アカウントが関連付けられている必要があります。

   • リージョンの選択：対象の BigQuery データセットのロケーションまたは実行リージョンを指定してください。BigQuery の INFORMATION_SCHEMA ビューはリージョンの範囲に厳密に限定されるためです（例：region-us や region-eu などのマルチリージョン、または region-us-central1 などの単一リージョン）。誤ったリージョンをクエリすると、ジョブのテレメトリが空になります。一致するリージョンを --region で渡してください（スクリプトは us-central1 のようなロケーション名を region-us-central1 に自動的に正規化します）。有効なロケーション識別子については、BigQuery のロケーションを参照してください。

3. API の有効化：

   • プロジェクトで BigQuery API を有効にします。

     gcloud services enable bigquery.googleapis.com
     

4. 認証の設定：

   • ローカルの gcloud 環境で認証し、アプリケーションのデフォルト認証情報（ADC）を設定します。

     gcloud auth login
     gcloud auth application-default login
     

5. IAM ロールと権限：

   • 実行主体には、最低限次の IAM ロールが必要です。
     • roles/bigquery.jobUser：クエリの実行とテレメトリの分析を許可します。
     • roles/bigquery.resourceViewer：INFORMATION_SCHEMA.JOBS_BY_PROJECT のクエリメタデータと容量予約への読み取り専用アクセスを許可します。

6. 料金の参照先：

   • このスキルのコスト見積もりは計画用です。scripts/slot_analyzer.py を実行する前に、対象リージョン、選択したエディション（Standard、Enterprise、Enterprise Plus）、コミットメント階層（Pay-as-you-go、1-year、3-year）など、ユーザー固有のパラメーターを考慮したうえで、公式のGoogle Cloud BigQuery の料金から実行時の最新料金を取得してください（各エディションの機能についてはBigQuery エディションの概要も参照してください）。実行時に取得した料金を --ondemand-rate <USD_PER_TIB> と --slot-hour-rate <USD_PER_SLOT_HOUR> で明示的に渡してください。

診断の実行ワークフロー

テレメトリ抽出の自動実行

scripts/slot_analyzer.py を実行して、実行時に取得した対象リージョン、エディション、コミットメント階層の料金を渡し、INFORMATION_SCHEMA.JOBS_BY_PROJECT から過去のクエリテレメトリを取得して分析します。

# General analysis passing live regional pricing rates fetched from BigQuery pricing
python3 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 recommendations
python3 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 telemetry
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

# Dry-run mode to inspect regional SQL query
python3 scripts/slot_analyzer.py --project-id <PROJECT_ID> --region region-us --dry-run

python3 scripts/slot_analyzer.py --help を実行して、サポートされるすべての CLI フラグ、フォーカスモード（--mode）、および必須の料金引数（TiB あたりの --ondemand-rate とスロット時間あたりの --slot-hour-rate）を確認します。

メトリクスの解釈と判断ツリー

以下の判断ルールを使用して、テレメトリーの出力を評価します。重大な指示：以下の判断ツリーを使用してクエリの問題を分類した後、直ちに view_file を呼び出して references/remediation_playbooks.md を読み、対応する修復手順書（Rule SLOT-001、Rule JOIN-001、または Rule PART-001）を実行し、必須の診断 SQL クエリと4段階のチェックリストをすべて回答に含めてください。

[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

修復手順書とアーキテクチャの参照リンク

SKILL.md のトークン消費を最小限に抑えるため、具体的な修復手順書（Rule SLOT-001、Rule JOIN-001、Rule PART-001）、診断 SQL クエリ、DDL 書き換えパターンは references/ に格納されています。

• Rule SLOT-001（スロットの競合とキューイング）：Rule SLOT-001 の手順書
• Rule JOIN-001（直積結合と結果が膨張する結合）：Rule JOIN-001 の手順書
• Rule PART-001（パーティション分割されていないスキャンとパーティションのプルーニング）：Rule PART-001 の手順書
• テーブルのパーティション分割、複数列のクラスタリング、BI Engine：最適化ルール
• BigQuery の検索インデックスとマテリアライズドビュー：最適化ルール

検証およびバリデーションの手順

クエリを書き換える前に、次の手順を実施してください。

ドライランによるバリデーション

コストを発生させずにクエリ構文を検証し、スキャンされる推定バイト数を算出します。

from google.cloud import bigquery
client = 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")

オフラインおよびドライランによるバリデーション

• オフラインのモックテレメトリー検証：合成または抽出した JSON テレメトリーペイロード（--mock-data-file）を使用して、ヒューリスティックによる分類、スロット競合の検出、直積結合の特定、コストの推定をオフラインで検証します。

  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 のドライラン検査：BigQuery に接続したりコストを発生させたりせずに、リージョンごとの SQL クエリの構成とスクリプトの実行を検証します。

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