BigQueryクエリが遅い時の最適化：パーティション／クラスタリング／JOIN戦略で10倍速にする実践手順
目次 クリックで開く
BigQueryで「数分待ってもクエリが返らない」「コストが想定の10倍かかる」現象は、ほぼ全てパーティション設計・クラスタリング・JOIN戦略の3つで解決できます。本記事では Query Plan Visualizer と Slot Estimator を使った診断手順、典型的な遅延パターン5つの最適化テクニックを実SQL付きで解説します。
最短診断：原因の見極めチェックリスト
| 原因カテゴリ | 確認ポイント |
|---|---|
| フルスキャン | WHERE句にパーティションキー指定なし。1TBのテーブルを毎回フルスキャン → 月20万円超。 |
| 非効率なJOIN | 巨大テーブル同士をJOINしてからWHERE。先にWHEREで絞り込んでJOINするのが正解。 |
| クラスタリング未設定 | 高頻度フィルタ列にクラスタリング未設定でI/O過剰。 |
| Window関数の過用 | ROW_NUMBER() OVER (PARTITION BY …) を多用してShuffleが発生。 |
| UDFの非ベクタ化 | JavaScript UDFを大量行に適用 → SQLネイティブ関数に置換可能なケース多数。 |
解決手順（推奨実行順）
- Query Plan Visualizer で Stage 別の Slot 消費・ Shuffle 量を確認。
- テーブルメタデータ（INFORMATION_SCHEMA.PARTITIONS）でパーティション切られているか確認。
- 未パーティション → 日付列でパーティション再作成（CREATE TABLE … PARTITION BY DATE(timestamp)）。
- JOIN前のWHERE適用、サブクエリで絞り込んでからJOIN。
- 頻出フィルタ列にCLUSTER BY を追加（ALTER TABLE … SET OPTIONS）。
- BI Engine（無料枠1GB）を有効化してダッシュボード系クエリを高速化。
よくある質問
パーティション分割の最適粒度は？
日次パーティションが最も柔軟です。月次は粒度粗すぎ、時次は管理コスト高。
クラスタリング列は何個まで指定できる？
最大4列。フィルタ頻度の高い順に指定。
MATERIALIZED VIEW は使うべき？
ダッシュボード用集計テーブルとして極めて有効。自動再計算され、追加コストもメインテーブルより安価。
Snowflake と比較した場合のコスト感は？
BigQueryはオンデマンド課金、Snowflakeはコンピュート時間課金。スパイク型ワークロードはBigQueryが安い、24h実行ならSnowflakeが安いケース多。
Reserved Slots（定額）への移行タイミングは？
月額課金が$2,000を超えたら検討。BIエンジン+定額の組合せで30〜50%削減できるケースが多いです。
参考：Aurant Technologies 実プロジェクトのLooker Studio実装
本記事のテーマを実装段階まで進める際の参考として、Aurant Technologies が支援した複数の実案件で構築した Looker Studio ダッシュボードの一例をご紹介します。数値・社名・部門名はマスキングしていますが、実際に運用されている可視化です。
データ分析・BI
Looker Studio・Tableau・BigQueryを活用したBIダッシュボード構築から、データ基盤整備・KPI設計まで対応。経営判断をデータで支援します。