BigQueryのクエリのパフォーマンス最適化をClaude Codeに行なわせる

背景: データ基盤の運用ではクエリのパフォーマンス最適化と向き合わないといけない機会が多い

• データ基盤の運用をしていると、コストやパフォーマンスの相談が日々あります
  • 自分が書いたクエリの場合、クエリ自体に勘所があるし、パフォーマンスの勘所も分かっているので何とかできることも多いでしょう
• しかし、他人が書いたクエリの場合や普段自分が見ないデータだったり、クエリの書き方も自分と違っているなど、勘所が分からない場合も多いでしょう
  • じっくり見れば分かるかもしれませんが、それ以外の業務もあるため、長い時間をかけているわけにはいきません

課題: クエリ改善のヒント、実行情報は見るのが難しい

• BigQueryは実行情報を返してくれます
  • RDBMSでいうところのEXPLAIN的な情報を返してくれます
  • JOINやウィンドウ関数など、クエリのどこで時間やスロットがかかっているかを解析して表示します
• しかし、この実行情報はあまり人間に優しくはありません
  • 少なくとも、自分はあまり得意ではないです...
  • ref: BigQuery の Execution Plan を体感&可視化で理解してパフォーマンスチューニングする #GoogleCloud - Qiita
  • 最近は少しマシになった説があるけど、簡単とはまだ言えないかな...
• 実行情報のみならず、BigQueryのクエリ最適化のプラックティスも合わせて頭の中に入れておく必要があり、BigQueryに詳しくない人にはやはり難易度が高いです
  • ref: クエリ計算を最適化する  |  BigQuery  |  Google Cloud

解決方法: 実行情報とクエリ最適化の紐付けをClaude Codeにさせる

• 難しいポイントを整理すると、以下の通り
  • 実行情報を理解するのが難しい
  • 実行情報を具体的なクエリ最適化のプラックティスに当てはめるのが難しい
  • 試行錯誤をしながら、元のクエリ結果を壊さないのが難しい
    • クエリ結果の行や列、その中身が修正前と変わらないことを担保しないといけない
• 「こういうのはLLM Agentにやらせるといいのでは?」と思ったので、Claude Codeにやらせることにした

工夫ポイント1: 実行情報をINFORMATION_SCHEMA.JOBS_BY_PROJECTから取得する

• 実行情報はWebブラウザから見ることもできますが、これはLLMにとっては扱いにくい情報です
  • 可視化されて人間には分かりやすい形にはなっているが、LLMはこれを解析しないといけないため
• Webブラウザで見るのと同等の情報をBigQueryはINFORMATION_SCHEMA.JOBS_BY_PROJECTは返してくれるので、これを利用する
  • 以下のクエリを使って、実行時間の大半を締める支配的なステージ名を絞り込む

bq query --use_legacy_sql=false --format=pretty --parameter="job_id:STRING:<JOB_ID>" "
  SELECT
    stage.name as stage_name,
    CAST(stage.slot_ms AS INT64) as slot_ms,
    ROUND(100.0 * stage.slot_ms / SUM(stage.slot_ms) OVER(), 1) as pct_of_total
  FROM region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT,
       UNNEST(job_stages) AS stage
  WHERE job_id = @job_id
    AND creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  ORDER BY stage.slot_ms DESC
  LIMIT 5
"

工夫ポイント2: ステージ内のステップと実際のコードを紐付ける

• ステージ内はステップというさらに細かい形に分かれるが、その中ではkindという情報を持っているので、その情報を紐付ける

bq query --use_legacy_sql=false --format=pretty --parameter="job_id:STRING:<JOB_ID>" "
  SELECT
    stage.name,
    CASE
      WHEN EXISTS(SELECT 1 FROM UNNEST(stage.steps) AS step WHERE step.kind = 'READ') THEN 'READ処理'
      WHEN EXISTS(SELECT 1 FROM UNNEST(stage.steps) AS step WHERE step.kind = 'WRITE') THEN 'WRITE処理'
      WHEN EXISTS(SELECT 1 FROM UNNEST(stage.steps) AS step WHERE step.kind = 'COMPUTE') THEN 'COMPUTE処理'
      WHEN EXISTS(SELECT 1 FROM UNNEST(stage.steps) AS step WHERE step.kind = 'FILTER') THEN 'FILTER処理'
      WHEN EXISTS(SELECT 1 FROM UNNEST(stage.steps) AS step WHERE step.kind = 'JOIN') THEN 'JOIN処理'
      WHEN EXISTS(SELECT 1 FROM UNNEST(stage.steps) AS step WHERE step.kind = 'AGGREGATE') THEN 'AGGREGATE処理'
      WHEN EXISTS(SELECT 1 FROM UNNEST(stage.steps) AS step WHERE step.kind = 'ANALYTIC') THEN 'ANALYTIC処理'
      WHEN EXISTS(SELECT 1 FROM UNNEST(stage.steps) AS step WHERE step.kind = 'SORT') THEN 'SORT処理'
      WHEN EXISTS(SELECT 1 FROM UNNEST(stage.steps) AS step WHERE step.kind = 'LIMIT') THEN 'LIMIT処理'
      ELSE 'その他'
    END as primary_operation
  FROM region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT,
       UNNEST(job_stages) AS stage
  WHERE job_id = @job_id
    AND creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  ORDER BY stage.slot_ms DESC
  LIMIT 5
"

• 「どこがボトルネックだったらどういう最適化をするべきか」を事前にリストアップ
  • Input段階のボトルネック → データ読み込み最適化
    • パーティション絞り込み: WHERE句での日付/地域等の限定
    • クラスタリング活用: JOIN/GROUP BYキーでの事前ソート
    • 列選択最適化: SELECT *を避けて必要列のみ
  • Join段階のボトルネック → 結合処理最適化
    • JOIN前データ削減: 事前フィルタリング/集約で行数削減
    • JOIN順序最適化: 小テーブル→大テーブルの順序
    • EXISTS/IN変換: 相関サブクエリから効率的な形式へ
  • Aggregate/Sort段階のボトルネック → 集約処理最適化
    • 段階的集約: 複数CTEでの事前集約
    • LIMIT早期適用: TOP-N処理での不要計算回避
    • ウィンドウ関数最適化: PARTITION BY句の最適化
• 完全ではないとは思うけど、そこそこのカバレッジはあるはずなので、ひとまずこれでよかろう...

工夫ポイント3: 最適化後にクエリの実行結果が変わらないことを保証する

• LLM Agentに最適化をさせるので、元々やっていた処理を途中で省略してパフォーマンスをよくなったと見せかけるズルをする可能性があります
  • 実際、LLM Agentはやらかします
  • そういうのを人間がイチイチ確認するのはダルいです
• 修正前と修正後でクエリの実行結果が変わらないことを担保する必要があります
  • 実行後のテーブルはBIT_XOR(FARM_FINGERPRINT(TO_JSON_STRING(t)))のような形で簡単にチェックサムできるため、修正前後でチェックサムが一致していることを確認させるステップをフローに取り込みます
  • re: BigQueryでチェックサムをする方法 - 株式会社ししまろ
• LLM Agentにクエリ修正をさせる場合、こういったガードレールを敷いた状態で最適化させるのが賢明です
  • ref: Claude CodeでBigQueryのクエリを実行する際のガードレールを設計する - yasuhisa's blog
  • ref: dbt開発 with Claude Codeのためのガードレール設計 - Speaker Deck

最適化実例

ここに登場するいくつかの例を実際に最適化させてみました。

実例1: ウィンドウ関数を使う前にデータを絞り込む

次のクエリは10人の最も古い Stack Overflow ユーザーとそのランキングを、最も古いアカウントを最も低いランクとして一覧表示します。全てのデータにDENSE_RANKでランクを付与してから古い10件に絞り込むため、無駄が多いです。

SELECT
id,
reputation,
creation_date,
DENSE_RANK() OVER (ORDER BY creation_date) AS user_rank
FROM bigquery-public-data.stackoverflow.users
ORDER BY user_rank ASC
LIMIT 10;

試行錯誤の途中でClaude Codeが勝手にDENSE_RANK()をROW_NUMBER()に置き換えようとしていましたが*1、チェックサムがガードレールとして機能して、結果の同一性を担保できる形になりました。

Claude Codeによるパフォーマンス最適化のレポート(クリックで開きます)

# BigQuery最適化レポート

## 実行サマリー
- **元クエリファイル**: .claude/tmp/bad_query.sql
- **元ジョブID**: bqjob_r57c7ca16887bcc4b_00000199b6ed18e3_1
- **元スロット時間**: 127821ms
- **最適化後ジョブID**: bqjob_r63dd3f9b2d0c552d_00000199b6f2c669_1
- **最適化後スロット時間**: 40933ms
- **最終改善率**: 3.12x
- **目標達成**: ✅ 達成（2倍以上の改善目標に対して3.12倍を達成）

## 特定されたボトルネック
- S0B: Join+: 24488ms (全体の19.2%)
- S0A: Compute: 20245ms (全体の15.8%)
- S09: Sort+: 17388ms (全体の13.6%)
- S06: Compute: 14582ms (全体の11.4%)
- S02: Aggregate: 13404ms (全体の10.5%)

### 根本原因分析:
- S09: Sort+の支配的要素: Compute (compute_pct: 100%), shuffle_mb: 481.8MB - 全レコードのソート処理
- S0A: Computeの支配的要素: shuffle_mb: 803.0MB - 大規模データシャッフル
- S0B: Join+の支配的要素: compute_pct: 4.3%, shuffle_mb: 0.0MB
- S06: Compute, S02: Aggregateもshuffle_mb: 160.6MBのデータ移動あり

### 対応するSQL箇所:
- S09: Sort+に対応: ORDER BY user_rank ASC (行7) - DENSE_RANK計算後の全レコードソート
- S0A: Computeに対応: DENSE_RANK() OVER (ORDER BY creation_date) (行5) - 全レコードに対するウィンドウ関数
- 根本原因: 全レコード(449MB)にDENSE_RANKを計算してからLIMIT 10を適用 → 不要な計算

## 適用した最適化手法
1. LIMIT早期適用（CTEパターン）: creation_dateの上位10ユニーク値のみ抽出 (期待効果: 68%削減)
   - 現状: 全レコード(449MB)にDENSE_RANK計算 → 全レコードソート → LIMIT 10
   - 最適化: CTEで上位10のcreation_dateを抽出 → 該当レコードのみDENSE_RANK計算
   - 理由: 処理対象レコードを最小限に絞り込むことで、計算量とシャッフルを削減

2. データ範囲の限定: WHERE句によるフィルタリング (期待効果: 追加削減)
   - IN句で特定のcreation_date値のみに限定
   - 不要なレコードのスキャンとウィンドウ関数適用を回避

## 最適化後のクエリ
\`\`\`sql
WITH top_dates AS (
  SELECT DISTINCT creation_date
  FROM bigquery-public-data.stackoverflow.users
  ORDER BY creation_date ASC
  LIMIT 10
)
SELECT
  id,
  reputation,
  creation_date,
  DENSE_RANK() OVER (ORDER BY creation_date) AS user_rank
FROM bigquery-public-data.stackoverflow.users
WHERE creation_date IN (SELECT creation_date FROM top_dates)
ORDER BY user_rank ASC
LIMIT 10;
\`\`\`

## 検証結果
- **結果一致**: ✅ チェックサム一致で完全同一（-2647371489554075806）
- **性能改善**: 127821ms → 40933ms（3.12倍高速化）
- **処理時間**: 12.983秒 → 5.166秒
- **スキャン量**: 変わらず（449MB）- パーティション/クラスタリングなしのため

実例2: JOINする前にデータを絞り込む

巨大なテーブルがある場合、事前に集計をした上でJOINすることが望ましいですが、以下のクエリではJOIN後に集計をしています(commentsテーブルがデカい)。経験的にこういったクエリはアナリストの方が書いてしまうことが多いですが、データを広げてから集約することになるため、避けたいクエリの例となっています。

WITH
  users_posts AS (
  SELECT 
    user_id,
    display_name,
    reputation,
    text,
  FROM
    `bigquery-public-data`.stackoverflow.comments AS c
  JOIN
    `bigquery-public-data`.stackoverflow.users AS u
  ON
    c.user_id = u.id
  )
SELECT
  user_id,
  ANY_VALUE(display_name) AS display_name,
  ANY_VALUE(reputation) AS reputation,
  COUNT(text) AS comments_count
FROM users_posts
GROUP BY user_id
ORDER BY comments_count DESC
LIMIT 20;

Claude Codeによるパフォーマンス最適化のレポート(クリックで開きます)

# BigQuery最適化レポート

## 実行サマリー
- **元クエリファイル**: .claude/tmp/bad_query_3.sql
- **元ジョブID**: bqjob_r3918c803788d3a82_00000199b6faa69a_1
- **元スロット時間**: 363513ms
- **最終改善率**: 2.21x
- **目標達成**: ✅ 達成 (2倍以上の改善を実現)

## 特定されたボトルネック
- S01: Input: 250337ms (全体の68.9%)
- S02: Join+: 95115ms (全体の26.2%)

### 根本原因分析
- S01: Inputの支配的要素: Compute (compute_pct: 100%), shuffle_mb: 13880.8MB
  - 大量のコンピュート処理とシャッフルデータ生成が発生
  - comments テーブルの全件読み込み後、JOIN準備のためのデータ変換処理
- S02: Join+の支配的要素: Compute (compute_pct: 14%), shuffle_mb: 144.9MB
  - JOIN処理自体は比較的軽量

### 対応するSQL箇所
- S01: Inputに対応: comments テーブルの全件スキャン (行8-9)
  - FROM bigquery-public-data.stackoverflow.comments AS c
  - この段階で全てのカラムを読み込み、13.8GBのシャッフルデータを生成
- S02: Join+に対応: JOIN users u ON c.user_id = u.id (行10-13)
  - users テーブルとのJOIN処理

## 適用した最適化手法

### 第1回の試行（失敗）
- CTE削除によるクエリ単純化を試みた
- 結果: 性能が0.88倍に悪化 (413271ms)
- 原因: CTEはBigQueryオプティマイザの最適化に重要な役割を果たしていた

### 第2回の試行（成功）
1. **事前集約の最適化**: user_idごとのカウントをJOIN前に実施
   - commentsテーブルを先に集約してからusersとJOIN
   - JOIN対象レコード数を大幅削減
   
2. **不要なGROUP BYの削減**: 集約済みデータを利用することで、最終的なGROUP BYを最小化

## 最適化後のクエリ
\`\`\`sql
WITH
  comment_counts AS (
  SELECT 
    user_id,
    COUNT(text) AS comments_count
  FROM
    bigquery-public-data.stackoverflow.comments
  GROUP BY user_id
  )
SELECT
  cc.user_id,
  ANY_VALUE(u.display_name) AS display_name,
  ANY_VALUE(u.reputation) AS reputation,
  cc.comments_count
FROM comment_counts cc
JOIN
  bigquery-public-data.stackoverflow.users AS u
ON
  cc.user_id = u.id
GROUP BY cc.user_id, cc.comments_count
ORDER BY cc.comments_count DESC
LIMIT 20
\`\`\`

## 検証結果
- **結果一致**: ✅ チェックサム一致で完全同一 (2205568025513379621)
- **性能改善**: 363513ms → 164506ms (2.21倍高速化)
- **改善メカニズム**:
  - JOIN前に集約することで、JOIN対象レコード数を削減
  - 大規模テーブル(comments)を先に集約し、小規模化してからJOIN
  - シャッフルデータ量の大幅削減

実装上のその他のTips

クエリを発行した瞬間にJOB_IDを取得する

bqコマンドでクエリを発行する場合、発行したクエリのパフォーマンスを調べるにはそのクエリに対するJOB_IDが必要です。しかし、JOB_IDを取得するのは意外と面倒。

そんなときには--nosyncオプションを付けた上でbq queryを実行するとよいです。このオプションなしだと同期的にクエリ結果を返してくれて、このオプションがありだと元のクエリは非同期に実行しつつクエリに対するJOB_IDを得ることができます。

% オプションなし
% bq query --use_legacy_sql=false --use_cache=false --format=json "SELECT 1 as hoge"
[{"hoge":"1"}]

% オプションあり
% bq query --nosync --use_legacy_sql=false --use_cache=false --format=json "SELECT 1 as hoge" | jq -r '.jobReference.jobId'
bqjob_r76287c81503a46d4_00000199b826d188_1

テーブルのパーティションを指定する

Tipsというか実用上の注意点です。本当に大きいテーブルに対して試行錯誤する場合、今回のコマンドだけでなく、以下が必要になる場合が多いと思います。

• 直近一週間など期間を限定し、パーティション列を指定する
• テーブルの1%など、サンプリングを行なう

試行錯誤する間は小さいテーブルで行ない、方針が固まったら元のテーブルを見る、というやり方です。今回のカスタムコマンドではそこまでは面倒を見ないため、手動でパーティションを指定するかそれ専用のカスタムコマンドを用意 & 併用するとよいでしょう。

まとめ

• クエリのパフォーマンス最適化をLLM Agentにさせる方法を紹介しました
• クエリの実行情報はINFORMATION_SCHEMAから取得可能なため、実はLLM Agentフレンドリーです
• 実行情報と実際のクエリの情報を紐付けた上で最適化手法を適用するのはLLM Agentは得意です
• クエリ結果を変えるような修正をLLM Agentがしないように、チェックサムのようなガードレールを敷いておくと安心して任せられます

*1:マジでこういうのをやらかす...