はじめに
BigQuery は数百GB〜数十TB級のデータに対しても秒単位でクエリを返せる、Google Cloud の代表的なデータウェアハウス（DWH）サービスです。ただし、 スキャン量に応じた従量課金 という料金体系である以上、テーブル設計を誤ると、不要な全件スキャンによってクエリコストが青天井に膨らむリスクがあります。
今回は BigQuery のコストとパフォーマンスを左右する設計要素である パーティションキー と クラスタリングキー について、内部アーキテクチャの観点も交えながら、実際のハンズオン手順を整理してみたいと思います。
なお、Google Cloud を活用したシステム開発については、Udemy にて関連コースをリリースしておりますので、ご興味のある方はご覧ください。
1. BigQuery の内部アーキテクチャ：Colossus と Capacitor
パーティション・クラスタリングを理解するうえで、BigQuery がどのようにデータを物理格納しているかを押さえておくことが重要です。
BigQuery のデータは、Google 内部の分散ストレージ基盤である Colossus 上に格納されます。さらに、テーブルの各レコードはそのままの行形式ではなく、 Capacitor と呼ばれる列指向（カラムナ）のファイルフォーマットでエンコードされて保存されています。
列指向ストレージの特徴は、 「SELECT で指定された列だけを読み込めば済む」 という点にあります。例えば100列あるテーブルから3列だけを SELECT する場合、行指向DBでは全列を読む必要がありますが、Capacitor では指定3列のブロックのみを読み込めばよいため、I/O量を削減できます。これが BigQuery の高速性の根幹です。
ここに加えて、
- パーティション：テーブルを特定の列（典型的には日付列）の値に基づいて、物理的に分割する仕組み。
- クラスタリング：パーティション内において、指定列の値が近いレコードを物理的に近接配置する仕組み。
を適切に設計することで、 「列の絞り込み × 行（パーティション）の絞り込み × 物理近接性（クラスタリング）」 の三段構えでスキャン量を最小化できます。これが BigQuery における設計上のベストプラクティスです。
2. ハンズオン：パーティション・クラスタリングの効果検証
2-1. データセットの作成
Cloud Shell から、検証用のデータセットを作成します。（東京リージョンを想定）
export PROJECT_ID=$(gcloud config get-value project)
export REGION="asia-northeast1"
bq --location=${REGION} mk --dataset ${PROJECT_ID}:part_handson
2-2. パーティション・クラスタリング「なし」テーブルの作成
BigQuery Studio（SQL ワークスペース）から、100万行のサンプルデータを持つテーブルを作成します。注文日（order_date）、顧客ID、商品カテゴリ、金額を含む、典型的なトランザクションテーブルの想定です。
CREATE TABLE part_handson.orders_plain AS
SELECT
GENERATE_UUID() AS order_id,
DATE_ADD(DATE '2026-01-01', INTERVAL CAST(FLOOR(RAND()*180) AS INT64) DAY) AS order_date,
CONCAT('cust-', CAST(FLOOR(RAND()*10000) AS INT64)) AS customer_id,
['electronics','books','food','clothing','toys'][OFFSET(CAST(FLOOR(RAND()*5) AS INT64))] AS product_category,
CAST(FLOOR(RAND()*100000)+100 AS INT64) AS amount
FROM UNNEST(GENERATE_ARRAY(1, 1000000)) AS n;
2-3. パーティション・クラスタリング「あり」テーブルの作成
同じデータを、order_date でパーティション化 し、product_category でクラスタリング したテーブルとして作り直します。
CREATE TABLE part_handson.orders_optimized
PARTITION BY order_date
CLUSTER BY product_category
AS
SELECT * FROM part_handson.orders_plain;
パーティション・クラスタリングの選定指針はシンプルです。
-
パーティションキー：WHERE 句で頻繁に絞り込まれる、カーディナリティが中程度の 日付・タイムスタンプ列（または整数）。典型は
order_date、event_dateなど。 -
クラスタリングキー：パーティション内でさらに絞り込みや集計に使われる列。カーディナリティが高すぎず、フィルタやJOINキーになるもの。今回は
product_category。
2-4. スキャン量の比較
同じ条件のクエリを、両テーブルに対して実行し、スキャン量を比較します。BigQuery Studio の画面右上に、クエリ実行前に 「このクエリを実行すると ◯◯ MB が処理されます」 と表示されるので、その値を確認します。
-- キーなし：全件スキャン
SELECT COUNT(*), SUM(amount)
FROM part_handson.orders_plain
WHERE order_date = '2026-03-01'
AND product_category = 'electronics';
-- キーあり：パーティション + クラスタによる絞り込み
SELECT COUNT(*), SUM(amount)
FROM part_handson.orders_optimized
WHERE order_date = '2026-03-01'
AND product_category = 'electronics';
実行すると、 orders_plain では100万行全体に対するスキャンが走るのに対し、orders_optimized では 2026-03-01 パーティションに含まれる数千行レベルにまでスキャン範囲が絞られる ことが確認できるはずです。実測値で、スキャン量は数十〜数百分の1まで削減されます。
BigQuery の課金は「スキャンしたバイト数」に対して発生するため、 これはそのまま月次のクエリコスト削減 に直結します。
2-5. パーティションキーを「無効化」してしまう典型的なアンチパターン
注意したいのは、 パーティションキーに関数や型変換を適用すると、パーティションプルーニング（不要パーティションの除外）が効かなくなる という点です。以下は典型的なアンチパターンです。
-- order_date を文字列にキャストしているためパーティションが効かない
SELECT COUNT(*)
FROM part_handson.orders_optimized
WHERE CAST(order_date AS STRING) LIKE '2026-03%';
このクエリは結果としては正しく動作しますが、 パーティション列に CAST が掛かっている時点で BigQuery はパーティション境界を判断できず、全パーティションをスキャンしに行きます。 スキャン量は最適化なしのテーブルとほぼ同等になります。
正しい書き方は以下の通り、パーティション列を「そのままの型」で比較することです。
SELECT COUNT(*)
FROM part_handson.orders_optimized
WHERE order_date BETWEEN '2026-03-01' AND '2026-03-31';
この「パーティション列に関数を当ててはいけない」というルールは、レビュー観点としても極めて重要です。
2-6. クリーンナップ
検証が終わったら、データセットを削除しておきます。
bq rm -r -f -d ${PROJECT_ID}:part_handson
3. 設計上の落とし穴
ハンズオンを通じて効果は明らかですが、実運用ではいくつか注意点があります。
- パーティション数の上限：1テーブルあたり最大4,000パーティション。日次パーティションなら約11年分が上限。長期データには月次パーティションへの切り替え検討が必要。
- 小さすぎるパーティション：1パーティションあたりのデータ量が極端に少ない（例：数MB）場合、メタデータオーバーヘッドの影響でかえって非効率になることがある。
- クラスタリングは「ベストエフォート」：パーティション内でのソートは挿入や更新の蓄積により乱れていくため、定期的に再書き込み（あるいは BigQuery 側の自動再クラスタリング）が必要。
- 過度な多階層化：クラスタリングキーは最大4列まで指定可能だが、列を増やすほど後段の効果は逓減する。最も絞り込みに使う1〜2列に絞るのが現実的。
4. おわりに
BigQuery は「とりあえずテーブルを作って SELECT を投げれば速い」というイメージで使われがちですが、 データ量がTB級に達した瞬間、設計の良し悪しがコストとレスポンスタイムの両面で顕在化 します。
特に基幹系のトランザクションデータや IoT のセンサーデータといった、 「時間軸を持つ大量データ」 を扱う場合、パーティション・クラスタリングの適切な設計は事実上必須です。設計時点でしっかりとキーを選定しておくことで、後年の運用コストとパフォーマンスを大きく改善できます。
BigQuery を本番システムで活用される方は、ぜひ一度自テーブルのパーティション・クラスタリング設計を見直されることをお勧めします。
プロフィール
[Maruchin Tech]
AWS、Google Cloud、製造業・SCM DXを専門とするUdemy講師・技術顧問。
新卒で日産自動車に入社。その後アクセンチュア、NTTデータを経て独立。
大手製造業の基幹システム刷新やDXプロジェクトにおいて、要件定義からアーキテクチャ設計までをリード。
現在は「現場で本当に使える技術と視点」をテーマに、エンジニア教育に従事。