@yoshitake_l 氏が X で共有した、BigQuery のデータ構造変更による劇的な改善結果が注目を集めています。
BigQuery でデータの持ち方を変えるだけで、クエリ処理速度を 3 倍に、クエリコストを 25% 削減できたので共有。試したクエリは、1:N の 2 つのテーブルの N 側を集計し、1 側と JOIN するシンプルなもの。使ったのは、ARRAY と STRUCT というデータ構造です。
「データの持ち方を変えるだけ」で速度 3 倍・コスト 25% 削減。SQL のチューニングではなく、テーブル設計の変更でこの結果を得ています。本記事では、なぜ ARRAY/STRUCT が JOIN より高速でコストが低いのか、その技術的な仕組みと実践方法を解説します。
BigQuery の課金と速度の問題を理解するには、まず分散処理の仕組みを知る必要があります。
BigQuery の JOIN 処理の流れ:
1. テーブル A を複数のスロット（ワーカーノード）に分散読み込み
2. テーブル B を複数のスロットに分散読み込み
3. JOIN キーに基づいて、データを適切なスロットに「再配置」
→ これが「シャッフル」
4. 各スロットでマッチング処理を実行
5. 結果を統合
問題:
ステップ 3 のシャッフルが最大のボトルネック
├── スロット間のネットワーク通信が発生
├── 大量の中間データが移動
└── 通信待ちの間、スロットが遊休状態になる
BigQuery のオンデマンド課金は「スキャンしたバイト数」に比例します。JOIN では両方のテーブルのキー列と必要列をすべてスキャンするため、スキャン量が増えます。さらに、JOIN に必要なシャッフル処理が実行時間を大幅に伸ばします。
@yoshitake_l 氏が試したのは「1:N の 2 つのテーブルの N 側を集計し、1 側と JOIN する」クエリです。これは典型的な親子関係のデータ構造です。
典型的な 1:N テーブル構造:
orders テーブル（1 側）:
order_id | customer_name | order_date
---------+--------------+-----------
1001 | 田中太郎 | 2026-03-01
1002 | 鈴木花子 | 2026-03-02
order_items テーブル（N 側）:
item_id | order_id | product | amount
--------+---------+---------+-------
1 | 1001 | 商品 A | 3000
2 | 1001 | 商品 B | 5000
3 | 1001 | 商品 C | 2000
4 | 1002 | 商品 D | 8000
5 | 1002 | 商品 E | 1000
よくあるクエリ:
SELECT o.order_id, o.customer_name,
SUM(i.amount) AS total_amount,
COUNT(i.item_id) AS item_count
FROM orders o
JOIN order_items i ON o.order_id = i.order_id
GROUP BY o.order_id, o.customer_name
このクエリでは、orders テーブルと order_items テーブルの両方をフルスキャンし、order_id でシャッフルして結合します。データ量が増えるほど、シャッフルのコストが支配的になります。
BigQuery は、列にネストされたデータ構造を格納できます。
BigQuery のデータ型:
STRUCT（構造体）:
複数のフィールドを 1 つのカラムにまとめる
→ SQL の「行」のようなもの
例: STRUCT<product STRING, amount INT64>
ARRAY（配列）:
同じ型の値を複数格納する
→ 1 つのセルに複数の値を持てる
例: ARRAY<INT64>
ARRAY<STRUCT>（配列×構造体）:
構造体の配列 = 「テーブルの中にテーブル」
→ 1:N の関係を 1 行にまとめられる
例: ARRAY<STRUCT<product STRING, amount INT64>>
1:N の 2 テーブルを、ARRAY を使った 1 テーブルに変換します。
-- 変換前: 2 つのフラットテーブル
-- orders テーブル + order_items テーブル（JOIN が必要）
-- 変換後: ARRAY<STRUCT> で 1 テーブルに統合
CREATE TABLE orders_nested AS
SELECT
o.order_id,
o.customer_name,
o.order_date,
ARRAY_AGG(
STRUCT(i.item_id, i.product, i.amount)
) AS items -- N 側を ARRAY<STRUCT> として格納
FROM orders o
JOIN order_items i ON o.order_id = i.order_id
GROUP BY o.order_id, o.customer_name, o.order_date変換後のテーブル構造はこうなります。
orders_nested テーブル:
order_id | customer_name | order_date | items（ARRAY<STRUCT>）
---------+--------------+-----------+------------------------
1001 | 田中太郎 | 2026-03-01 | [{1, "商品A", 3000},
| | | {2, "商品B", 5000},
| | | {3, "商品C", 2000}]
1002 | 鈴木花子 | 2026-03-02 | [{4, "商品D", 8000},
| | | {5, "商品E", 1000}]
JOIN が不要になり、UNNEST で配列を展開するだけで同じ結果が得られます。
-- 変換後: JOIN なし、UNNEST で展開
SELECT
order_id,
customer_name,
SUM(item.amount) AS total_amount,
COUNT(*) AS item_count
FROM orders_nested,
UNNEST(items) AS item
GROUP BY order_id, customer_name最大の要因はシャッフルの排除です。
JOIN ありの場合:
スロット A: orders の一部を読む
スロット B: order_items の一部を読む
→ order_id でシャッフル（ネットワーク通信）
→ マッチング処理
→ 結果統合
ARRAY/STRUCT の場合:
スロット A: orders_nested の一部を読む
→ そのスロット内で UNNEST して集計（ローカル処理）
→ 結果統合
シャッフルが発生しない
→ ネットワーク通信ゼロ
→ スロットの遊休時間ゼロ
Google 公式ドキュメントも明確にこの点を述べています。
Denormalization localizes the data to individual slots, so that execution can be done in parallel. （非正規化によってデータが個別スロットに局在化され、並列実行が可能になる）
--- Use nested and repeated fields | BigQuery | Google Cloud
BigQuery は列指向（カラム型）ストレージを採用しています。クエリで参照した列だけをスキャンする仕組みです。
列指向ストレージと ARRAY/STRUCT:
フラットテーブルの場合:
orders テーブル → order_id, customer_name をスキャン
order_items テーブル → order_id, amount をスキャン
→ 2 テーブル分のスキャンが発生
ARRAY<STRUCT> の場合:
orders_nested テーブル → order_id, customer_name,
items.amount だけをスキャン
→ 1 テーブルの必要列のみ
さらに:
ARRAY<STRUCT> 内の個別フィールドを指定できる
→ items.amount だけ読んで items.product は読まない
→ 不要なサブフィールドのスキャンを回避
ARRAY の中のサブフィールドも列指向で格納されるため、items.amount だけを参照すれば items.product はスキャンされません。これがスキャン量（= コスト）の削減に直結します。
1:N の関係をネスト化すると、テーブルの行数が劇的に減ります。
行数の比較:
フラットテーブル:
orders: 100 万行
order_items: 500 万行（平均 5 アイテム/注文）
→ 合計 600 万行をスキャン
ARRAY<STRUCT>:
orders_nested: 100 万行（items は配列として格納）
→ 100 万行のみスキャン
行数が 6 分の 1 に削減
→ メタデータのオーバーヘッドも減少
→ 行ごとの JOIN キー重複もゼロ
Towards Data Science の実例では、テーブルサイズ 35.5% 削減、行数 93.1% 削減という結果が報告されています。
BigQuery のオンデマンド課金は「スキャンしたバイト数 × $5/TB」です。ARRAY/STRUCT でスキャン量が減れば、そのまま課金額が減ります。
| 比較項目 | フラット + JOIN | ARRAY/STRUCT |
|---|---|---|
| スキャンテーブル数 | 2 テーブル | 1 テーブル |
| JOIN キー列のスキャン | 両テーブルで必要 | 不要 |
| N 側のキー重複 | N 行分のキーをスキャン | なし |
| サブフィールド選択 | テーブル単位 | フィールド単位 |
公開されているベンチマーク結果をまとめます。
| ソース | フラット + JOIN | ARRAY/STRUCT | 改善率 |
|---|---|---|---|
| Google 公式ブログ | 1.88 GB スキャン | 1.28 GB スキャン | 32% 削減 |
| Google 公式ブログ | 実行時間ベースライン | 約 60% 高速化 | 60% 改善 |
| Towards Data Science | 427.88 GB / $2.67 | 112.28 GB / $0.70 | 74% 削減 |
| @yoshitake_l 氏 | ベースライン | 3 倍高速、25% コスト減 | ツイートの結果 |
@yoshitake_l 氏の「速度 3 倍・コスト 25% 削減」は、上記のベンチマーク群と比較しても控えめな方です。データの特性（1:N の比率、カラム数、データ量）によっては、さらに大きな改善が見込めます。
有効なケース:
1. 1:N の親子関係:
受注ヘッダー + 受注明細
ユーザー + 行動ログ
商品 + レビュー
2. 常に一緒にクエリされるデータ:
注文の集計で必ず明細を参照する
ユーザー分析で行動履歴を参照する
3. N 側のデータ量が多い:
1 注文に 5〜50 の明細がある
1 ユーザーに数百のイベントがある
4. 10 GB 以上のテーブル:
小さなテーブルではメリットが薄い
大きいほど効果が大きい
不向きなケース:
1. 独立したエンティティの結合:
顧客テーブル × 商品テーブル（N:M）
→ 親子関係ではないため ARRAY 化できない
2. 頻繁に個別行を更新する:
BigQuery は UPDATE が高コスト
N 側を個別に更新するワークロードには不向き
3. N 側を単独でクエリする:
明細テーブルだけを集計したい場合
→ ネスト構造だと親テーブル全体を読む必要がある
4. N 側のデータ量が極端に大きい:
1 親に対して数万件の子レコード
→ 1 行が巨大になり、別の問題が発生
-- ステップ 1: 変換先テーブルを作成
CREATE OR REPLACE TABLE dataset.orders_nested AS
SELECT
o.order_id,
o.customer_name,
o.order_date,
ARRAY_AGG(
STRUCT(
i.item_id AS item_id,
i.product AS product,
i.amount AS amount
)
) AS items
FROM dataset.orders o
JOIN dataset.order_items i ON o.order_id = i.order_id
GROUP BY o.order_id, o.customer_name, o.order_date;
-- ステップ 2: 変換後のクエリ例（集計）
SELECT
order_id,
customer_name,
(SELECT SUM(item.amount) FROM UNNEST(items) AS item) AS total,
ARRAY_LENGTH(items) AS item_count
FROM dataset.orders_nested;
-- ステップ 3: 条件付きフィルタリング
SELECT
order_id,
customer_name,
item.product,
item.amount
FROM dataset.orders_nested,
UNNEST(items) AS item
WHERE item.amount > 5000;Google Analytics 4 の BigQuery エクスポートは、ARRAY を標準で採用しています。event_params フィールドがその代表例です。
-- GA4 の event_params は ARRAY<STRUCT> 形式
SELECT
event_name,
(SELECT value.string_value
FROM UNNEST(event_params)
WHERE key = 'page_location') AS page_url,
(SELECT value.int_value
FROM UNNEST(event_params)
WHERE key = 'engagement_time_msec') AS engagement_ms
FROM `project.analytics_XXXXXX.events_*`
WHERE _TABLE_SUFFIX = '20260306'Google が GA4 のデータ形式に ARRAY を選んだこと自体が、この設計パターンの有効性を示しています。
RDB（リレーショナルデータベース）の世界では「正規化」が基本原則です。データの重複を排除し、テーブルを分割して JOIN で結合する。これは OLTP（トランザクション処理）では正しい設計です。
しかし BigQuery のような分析基盤（OLAP）では、正規化の前提が崩れます。
OLTP（RDB）と OLAP（BigQuery）の違い:
OLTP（MySQL, PostgreSQL 等）:
├── 行単位の読み書きが主
├── UPDATE が頻繁
├── トランザクションの整合性が重要
├── JOIN は B-Tree インデックスで高速
└── → 正規化が正解
OLAP（BigQuery）:
├── 列単位の大量スキャンが主
├── INSERT のみ（UPDATE は高コスト）
├── 分析クエリの速度とコストが重要
├── JOIN はシャッフルを伴い高コスト
└── → 非正規化（ARRAY/STRUCT）が正解
Google 公式ドキュメントも明確に非正規化を推奨しています。
The recommended way to denormalize data in BigQuery is to use nested and repeated fields. （BigQuery でデータを非正規化する推奨方法は、ネストされた繰り返しフィールドを使用すること）
ARRAY/STRUCT 化は、ETL（データ取り込み）パイプラインの設計にも影響します。
ETL 設計の変更:
従来:
ソースDB → orders テーブルに INSERT
ソースDB → order_items テーブルに INSERT
→ 分析時に JOIN
ARRAY/STRUCT 化:
ソースDB → 中間テーブルに INSERT
→ スケジュールクエリで ARRAY_AGG して
orders_nested テーブルに変換
→ 分析時は JOIN 不要
一度 ETL パイプラインを変更すれば、その後のすべての分析クエリが高速化・低コスト化されます。初期の移行コストに対して、長期的なリターンが大きい投資です。
- JOIN を消すことが最大の最適化: BigQuery では JOIN のシャッフル処理がボトルネック。ARRAY/STRUCT で 1:N を 1 テーブルにまとめれば、シャッフルがゼロになる
- 速度 3 倍・コスト 25% 削減は控えめな結果: Google 公式ブログでは 60% 高速化・32% スキャン削減、Towards Data Science では 74% コスト削減の報告もある。データ特性次第でさらに改善可能
- 3 つの技術的理由: (1) シャッフル排除（スロット間通信ゼロ）、(2) 列指向ストレージの最大活用（サブフィールド単位のスキャン制御）、(3) 行数の大幅削減（メタデータオーバーヘッド減少）
- Google 公式が非正規化を推奨: 「BigQuery でデータを非正規化する推奨方法は、ネストされた繰り返しフィールドを使用すること」と明記。GA4 のデータ形式が ARRAY を標準採用していること自体が証明
- RDB の常識を捨てる: OLTP では正規化が正解だが、OLAP（BigQuery）では非正規化が正解。SQL のチューニングより先に「データの持ち方」を見直すべき
- 適用判断が重要: 1:N の親子関係、常に一緒にクエリされるデータ、10 GB 以上のテーブルが有効。独立エンティティの N:M 結合や頻繁な個別行更新には不向き
- @yoshitake_l 氏のポスト
- Use nested and repeated fields | BigQuery | Google Cloud
- BigQuery Explained: Working with JOINs, nested and repeated data | Google Cloud Blog
- BigQuery Efficiency: How I Reduced My Table Size by 35.5% and Rows by 93.1% | Towards Data Science
- BigQuery の ARRAY と STRUCT を理解して使いこなす | G-gen Tech Blog
- BigQuery Nested Fields vs Flat Tables: When to Use Each | GCP Study Hub
- The Power of ARRAYs and STRUCTs in BigQuery | Moldstud