はじめに

BigQueryのコストは「読んだデータ量（スキャン量）」で決まります。ですから、コストを下げたければ「無駄に大量スキャンしているクエリ」を見つけて直すのが王道です。

ところが、これを人手だけでやろうとするとなかなか大変です。

  • そもそも、どのクエリが高コストなのかが見えにくい
  • 高コストなクエリほど、長くて複雑で、読むのに時間がかかる
  • 「どこを直せば軽くなるか」を判断するには、それなりの経験が要る

特に中小ECの現場では、SQLを書ける人が一人しかいない、あるいは外注に頼んだクエリの中身を誰も把握していない、というケースが珍しくありません。そうなると、毎月の請求額がじわじわ増えても、手を入れられないまま放置されがちです。

そこで最近よく使っているのが、**「高コストなクエリをまず機械的に特定し、その改善案をAIにレビューさせる」**という進め方です。

この記事では、その流れを具体的に紹介します。AIに丸投げするのではなく、最後は必ず自分で検算する、という現実的なやり方も合わせて書いておきます。

ステップ1：高コストなクエリをINFORMATION_SCHEMAで特定する

まずは「直す対象」を決めます。感覚で「あのクエリが重そう」と探すのではなく、実際の課金データから機械的に洗い出します。

BigQueryには INFORMATION_SCHEMA.JOBS というメタデータのビューがあり、ここに過去に実行されたジョブのスキャン量や実行時間が記録されています。これを集計すれば、「どのクエリがどれだけ読んでいるか」が一目で分かります。

過去7日間で、スキャン量の多かったクエリ上位を出すクエリは次のようになります。

-- 直近7日間で、スキャン量（課金対象バイト）の多いクエリ上位20件
SELECT
  user_email,
  job_id,
  -- 課金対象バイトをGB換算
  ROUND(total_bytes_billed / POW(1024, 3), 2) AS billed_gb,
  -- おおよそのコスト（1TBあたり$6.25と仮定。料金は要確認）
  ROUND(total_bytes_billed / POW(1024, 4) * 6.25, 4) AS approx_usd,
  total_slot_ms,
  creation_time,
  -- クエリ本文は長いので先頭だけ表示
  SUBSTR(query, 0, 200) AS query_head
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY'
  AND statement_type != 'SCRIPT'
  AND total_bytes_billed > 0
ORDER BY
  total_bytes_billed DESC
LIMIT 20;

region-us の部分は、自分のデータセットがあるリージョンに合わせて書き換えてください（東京なら region-asia-northeast1 など）。

⚠️ 注意

INFORMATION_SCHEMA.JOBS を参照する権限（bigquery.jobs.listAll など）が必要です。また、表示される approx_usd はあくまで概算です。実際の料金は契約形態・リージョン・時期で変わるので、必ず公式の最新料金で確認してください。

このクエリを実行すると、「特定の1本が全体のスキャン量の大半を占めている」というケースがよくあります。まずはそのトップ数件に狙いを定めます。コストは一部の重いクエリに集中していることが多いので、全部を直す必要はありません。

似た切り口で、「同じパターンのクエリが何度も実行されてスキャン量を積み上げている」ものを探すのも有効です。

-- 繰り返し実行されているクエリを、合計スキャン量で集計する
SELECT
  -- クエリ本文の先頭を「種類」のキーとして使う
  SUBSTR(query, 0, 100) AS query_pattern,
  COUNT(*) AS run_count,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4), 3) AS total_billed_tb
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
  AND statement_type != 'SCRIPT'
GROUP BY
  query_pattern
ORDER BY
  total_billed_tb DESC
LIMIT 20;

「1回あたりは軽いけれど、毎時実行していて月単位では一番高い」といったクエリは、この集計でないと見つかりません。

ステップ2：特定したクエリをAIにレビューさせる

直す対象が決まったら、そのクエリの全文をAI（Claude Codeなど）に渡してレビューさせます。

ポイントは、ただ「速くして」と頼むのではなく、コンテキストと観点を具体的に指定することです。BigQueryのコストはスキャン量で決まる、という前提をAIに共有しておくと、的外れな提案が減ります。

プロンプトの例

あなたはBigQueryのコスト最適化に詳しいエンジニアです。
以下のSQLをレビューし、スキャン量（課金対象バイト）を減らす観点で改善案を出してください。

# 前提
- BigQueryはオンデマンド課金で、クエリが読んだバイト数に課金される
- テーブル orders は created_at で日次パーティションされている
- このクエリは毎日1回、定期実行している

# 見てほしい観点
1. SELECT * など、不要な列を読んでいないか
2. パーティション列（created_at）で範囲を絞れているか
3. WHERE句がパーティションプルーニングを効かせられているか
4. 同じ集計を二重に走らせていないか

# 改善後は、変更点と「なぜスキャン量が減るか」を箇条書きで説明してください。

---
（ここに対象のSQL全文を貼る）

AIは、人間が見落としがちな点を素早く拾ってくれます。たとえば次のような指摘です。

  • SELECT * をやめて、後続で使っている5列だけにする
  • WHERE DATE(created_at) = '2026-07-19' を WHERE created_at >= '2026-07-19' AND created_at < '2026-07-20' に書き換える（関数でパーティション列を包むとプルーニングが効かないことがあるため）
  • サブクエリで同じ大テーブルを2回読んでいる箇所を、1回のスキャンにまとめる

特に2つめの「パーティション列を関数で包んでしまう」アンチパターンは、書いた本人は気づきにくく、AIに渡すと一発で指摘されることが多いです。

Claude Codeを使う場合

Claude CodeのようにファイルやMCPを扱えるツールなら、ステップ1のクエリ実行から改善案の提示までを、一連の対話で回せます。「JOBSを見て高コストなクエリを出して」「その上位3本を改善案つきで提案して」と頼めば、特定とレビューをまとめて進められます。

この社内ツール的な使い方は、MCP × BigQuery × Claude Codeで聞くだけで分析できる社内ツールでも触れています。

ステップ3：before / afterのスキャン量を比較する

改善案ができたら、必ず実行前のスキャン量を比較します。

BigQueryには、クエリを実際に実行せずスキャン量だけを見積もる ドライラン があります。これを使えば、課金を発生させずに「どれだけ軽くなったか」を確認できます。

# 元のクエリのスキャン見積もり（--dry_run は実行せず見積もりだけ）
bq query --use_legacy_sql=false --dry_run < before.sql

# 改善後のクエリのスキャン見積もり
bq query --use_legacy_sql=false --dry_run < after.sql

実行すると、This query will process X bytes のように見積もりバイト数が返ってきます。エディタ（コンソール）でも、クエリを書くと右上に同じ見積もりが表示されます。

before / after を並べると、効果が数字で見えます。

以下の数値はイメージをつかむための架空の例です。実際の削減幅はクエリやデータ量によって大きく変わります。

スキャン量（例）おおよそのコスト（例）
before（SELECT *・全期間）1.2 TB約 $7.5
after（列を絞り・直近30日に限定）18 GB約 $0.11

このように、「不要な列を外す」「パーティションで期間を絞る」の2点だけで、スキャン量が桁で変わることはよくあります。逆に、見積もりが思ったほど減っていなければ、その改善案は採用しない、という判断もできます。ドライランは「直す前に効果を測る」ための物差しです。

ステップ4：AIの提案は必ず検算する

ここが一番大事です。AIの提案は便利ですが、そのまま本番に流してはいけません。

⚠️ 必ず検算してください

AIが提案したクエリは「スキャン量は減ったが、結果が変わってしまっている」ことがあります。コストだけ見て採用すると、間違ったデータで意思決定する事故につながります。改善前後で出力結果が一致するかを必ず確認してください。

検算は、難しく考えなくても次のような単純な照合で十分です。

-- before と after の結果が完全に一致するか確認する
-- どちらかにしかない行があれば、それが出力される（0行なら一致）
(
  SELECT * FROM (/* before のクエリ */)
  EXCEPT DISTINCT
  SELECT * FROM (/* after のクエリ */)
)
UNION ALL
(
  SELECT * FROM (/* after のクエリ */)
  EXCEPT DISTINCT
  SELECT * FROM (/* before のクエリ */)
);

このクエリが0行を返せば、before と after の結果は一致しています。集計値だけを比べたい場合は、両者の合計件数・合計金額を並べて突き合わせるだけでも、大きなズレには気づけます。

AIがやりがちなミスとしては、次のようなものがあります。

  • WHERE の条件を「軽くするつもり」で変えて、対象行が減ってしまう
  • LEFT JOIN を INNER JOIN に変えて、マッチしない行が消える
  • 重複排除（DISTINCT や GROUP BY）の有無が変わって件数がずれる

どれも、スキャン量は減るので一見「改善」に見えてしまうのが厄介です。だからこそ、コストの数字と結果の数字を両方確認する、という二段構えが欠かせません。

なお、コスト削減の地味だけれど確実なテクニックは、BigQueryのクエリコストを月1万円以下に抑える実践テクニックにまとめています。AIに頼る前に、まずこのあたりの基本を押さえておくと、AIの提案の良し悪しも自分で判断できるようになります。

ここで紹介した検算は、コスト削減の場面に絞った最小限のやり方です。件数・既知値との一致・別ロジックでの突合・境界のチェックまで含めて、AIが生成したSQLの正しさをより体系的に確認したい場合は、AIが生成したSQLは正しいか？BigQueryでの検証フレームワークを作ったにまとめています。

まとめ

AIにBigQueryのクエリをレビューさせてコストを削減する流れを整理します。

  1. 特定：INFORMATION_SCHEMA.JOBS で高コストなクエリを機械的に洗い出す
  2. レビュー：前提と観点を具体的に伝えて、AIに改善案を出させる
  3. 比較：ドライランで before / after のスキャン量を確認する
  4. 検算：改善前後で結果が一致するかを必ず確かめる

AIは「重いクエリを軽くするアイデア出し」を一気に加速してくれます。一方で、結果の正しさを保証してくれるわけではありません。特定と検算は人間が握り、改善案のたたき台をAIに作らせる——この役割分担が、安全にコストを下げる現実的なやり方だと感じています。

まずは自分のプロジェクトで INFORMATION_SCHEMA.JOBS を一度のぞいてみてください。「これ、こんなに読んでたのか」という1本が、たいてい見つかるはずです。

自社のクエリの棚卸しからAIレビューの仕組み化まで任せたい、あるいはBigQuery基盤全体のコストを見直したいという場合は、BigQueryデータ基盤構築サービスで対応しています。まずは現状を話してみたいという方は、お問い合わせからご連絡ください。