UN EDUCATION:BigQueryとSQLのばあい

0. まず理解すべき全体像

• 対象範囲：Google Cloudが提供するフルマネージド型の分析データウェアハウス「BigQuery」について、データモデリング、クエリ性能、コスト最適化、OLTPとの役割分担、AI・グラフ分析までを含めた活用戦略全般を扱います。

• 中心的な問い：従来のRDB（Cloud SQL等）との違いを踏まえ、BigQueryで「どれだけのデータを読み、どれだけデータを移動させ、どれだけの計算資源を消費するか」を制御しながら、高性能・低コストで分析できるデータ基盤をどのように設計するか。また、Conversational AnalyticsやBigQuery Graphなどの新しい分析手法を、どのように既存のデータ基盤へ組み込むか。

• 理解の中心軸：次の3軸で捉えると理解しやすくなります。

  1. 列指向ストレージとストレージ／コンピュート分離

  2. ARRAY / STRUCT、パーティショニング、クラスタリングによるデータ配置とデータ移動の最適化

  3. Business Glossary、Verified Queries、Agent Instructionsを用いたセマンティクス付きAI分析

BigQueryを理解するうえでは、「JOINは悪い」「ネストすれば速い」と単純化するのではなく、スキャン量・shuffle・slot利用量という別々のコスト要因を区別することが重要です。

1. コア・メンタルモデル

Mental Model 1：ネスト（ARRAY / STRUCT）と非正規化モデル

🟢 位置づけ：
BigQueryの分散処理特性を活かすための代表的なデータモデリング手法。

🔴 核心：
1対Nの関係を持つ関連データを毎回JOINで結合するのではなく、STRUCTやARRAY<STRUCT>として1つの行の中に保持することで、関連データを同じ処理単位に局所化する考え方です。

BigQueryでは、JOINやGROUP BYに伴うデータの再配置・通信、すなわちshuffleが性能低下の主要因になる場合があります。ネスト・反復フィールドを利用すると、関連データを同じslot内で処理しやすくなり、shuffleや一部のJOIN処理を減らせます。Google Cloudも、階層的で頻繁に一緒に参照されるデータではnested / repeated fieldsによる非正規化を推奨しています。

🔴 何を説明できるか：
GA4のevent_paramsのように、1つのイベントに対して可変個数の属性を保持するデータがARRAY<STRUCT>で表現される理由や、受注ヘッダと明細のような親子関係を同一テーブル内に保持することで、JOINやGROUP BYに伴うshuffleを削減できる理由を説明できます。

ただし、ネスト化＝必ずスキャン量が減る、という意味ではありません。オンデマンド課金では読み取ったデータ量が重要であり、性能改善と課金削減は分けて評価する必要があります。

🔴 適用条件・限界：
特に有効なのは、次の条件を満たすデータです。

• 親子などの階層的な関係がある

• 関連データを頻繁に一緒に分析する

• 1:N関係のカーディナリティが極端に高くない

• JOINやGROUP BYによるshuffleが大きな負荷になっている

一方、すでに最適化されたスター・スキーマでは、さらに非正規化しても性能改善が限定的な場合があります。また、高カーディナリティの反復データでは、ARRAYが適さない場合もあります。

更新頻度も重要な判断材料です。頻繁に変更されるマスタデータを大きなネスト構造へ埋め込むと、更新や再構築の運用コストが増える場合があります。

🔴 具体例：
注文ヘッダと注文明細を別々のテーブルに保持し、分析時に毎回JOINする代わりに、注文ごとの明細を

ARRAY<STRUCT<
  product_id STRING,
  quantity INT64,
  price NUMERIC
>>

として注文行の内部に保持します。

分析時にはUNNEST()を用いて明細を展開できます。

SELECT
  order_id,
  SUM(item.quantity * item.price) AS sales
FROM
  nested_orders,
  UNNEST(items) AS item
GROUP BY
  order_id;

この設計では、親子関係を同一行に保持しながら、必要に応じて分析時に展開できます。

🔵 主な参照ソース：

• Google Cloud「Use nested and repeated fields」

• Google Cloud「Specify nested and repeated columns in table schemas」

Mental Model 2：OLTPとOLAPのワークロード分離モデル

🟢 位置づけ：
業務システムと分析基盤を適切に役割分担するためのアーキテクチャ上の基本原則。

🔴 核心：
Cloud SQLなどのOLTPデータベースとBigQueryなどのOLAP基盤は、得意とする処理が異なります。

OLTPは、

• 個別レコードの参照

• INSERT / UPDATE / DELETE

• トランザクション

• 高頻度かつ低遅延の処理

を中心とします。

一方BigQueryは、

• 大量データの列スキャン

• GROUP BYや集計

• JOIN

• 大規模並列処理

を得意とします。

したがって、System of Recordとしての業務DBと、分析ワークロードを担うDWHを分離することが基本的な考え方になります。

🔴 何を説明できるか：
本番の業務DBで大規模な集計処理を実行することでアプリケーションのトランザクション性能が低下する問題や、分析処理をBigQueryへ分離する理由を説明できます。

また、BigQueryではストレージとコンピュートが分離され、大規模な列指向分析に適したアーキテクチャが採用されています。

🔴 適用条件・限界：
単純に「ミリ秒ならCloud SQL、秒ならBigQuery」と区分するのではなく、処理特性で判断します。

• 多数の短いポイントルックアップやトランザクション → OLTP

• 広範囲のscan、aggregation、analytical join → BigQuery

BigQueryでもDMLやストリーミング処理は可能ですが、高頻度の単行トランザクションを主用途とするデータベースとして設計されたサービスではありません。

🔴 具体例：
アプリケーションの注文データはCloud SQLに保存し、その変更をDatastreamなどのCDC（Change Data Capture）でBigQueryへ継続的に連携します。

概念的には、

Web Application
      ↓
Cloud SQL
      ↓
 Datastream
      ↓
 BigQuery
      ↓
BI / SQL / AI

という構成です。

これにより、業務DBへの分析負荷を抑えながら、BigQuery側で大規模な集計や分析を実行できます。

Mental Model 3：セマンティックコンテキストとData Agentによる対話型分析モデル

🟢 位置づけ：
自然言語によるデータ分析を、単純な「AIによるSQL生成」から組織固有の意味体系を理解する分析基盤へ発展させる考え方。

🔴 核心：
自然言語から単にSQLを生成するだけでは、AIは組織固有の業務ルールや用語を十分理解できません。

そこでBigQueryのConversational Analyticsでは、Data Agentに対して、

• テーブル・カラムのメタデータ

• SynonymsやBusiness Terms

• Agent Instructions

• Custom Glossary / Knowledge Catalog Glossary

• Verified Queries

などを与えることができます。Verified Queriesは、以前「Golden Queries」と呼ばれていた機能です。

🔴 何を説明できるか：
例えば、組織内で、

富裕層 = 預金残高1,000万円以上

という定義がある場合、AIが「富裕層」という自然言語を独自解釈するのではなく、事前に与えたビジネス定義に基づいて質問を解釈できるようになります。

また、Verified Queryと質問が適切に一致した場合、Agentは検証済みSQLをそのまま実行できます。一致しない場合でも、Verified Queryをデータ構造やビジネスロジックを理解するための参照情報として使用できます。

🔴 適用条件・限界：
Data Agentを利用しても、自然言語分析の正しさが自動的に保証されるわけではありません。

特に、

• メタデータ不足

• 曖昧な業務定義

• 不完全なAgent Instructions

• Verified Queriesが不足している質問領域

では、誤ったSQLや解釈が発生する可能性があります。

したがって、

Glossary
   +
Metadata
   +
Agent Instructions
   +
Verified Queries
   ↓
より一貫性の高い回答

と考えるのが適切です。

🔴 具体例：
Data Agentに、

若年層 = 18歳以上29歳以下

という定義を登録し、

と質問します。

Agentは定義された年齢範囲や対象テーブルを考慮し、SQLと分析結果を生成します。

ただし、生成結果については人間による確認や、Verified Queriesによる重要指標の固定化が依然として重要です。

🔵 主な参照ソース：

• Google Cloud「Conversational analytics overview」

• Google Cloud「Create data agents」

Mental Model 4：BigQuery Graphによる関係性探索モデル

🟢 位置づけ：
BigQuery上の既存データをproperty graphとして再定義し、関係性を中心に分析するモデル。

🔴 核心：
既存のBigQueryテーブルを、

• ノード（Node）

• エッジ（Edge）

として定義し、Graph Query Language（GQL）を使って関係を辿ります。

BigQueryではCREATE PROPERTY GRAPHを使用してproperty graphを定義できます。

🔴 何を説明できるか：
従来のSQLでは複数回のJOINや再帰的な処理が必要になる、

顧客
 ↓
商品
 ↓
別の顧客
 ↓
別の商品

のような多段の関係探索を、グラフのパターンとして記述できます。

🔴 適用条件・限界：
特に有効なのは、

• Customer 360

• 不正取引検知

• レコメンデーション

• サプライチェーン

• 組織・ネットワーク分析

など、「行そのもの」よりも「行同士の関係」が重要になる分析です。

単純な売上集計や単一テーブルのフィルタリングでは、通常のGoogleSQLの方が簡潔です。

また、2026年9月時点ではBigQuery Graph関連機能の一部はPreview / Pre-GAです。本番利用時には、対応Edition、リージョン、サポート条件などを確認する必要があります。

🔴 具体例：

GRAPH my_dataset.EcommerceGraph

MATCH
  (c:Customer)-[:PURCHASED]->(p:Product)

RETURN
  c.customer_id,
  p.product_id;

BigQueryのGQLでは、最初にGRAPH句で対象property graphを指定し、その後MATCHでノードとエッジのパターンを指定します。

🔵 主な参照ソース：

• Google Cloud「Graph query overview」

• Google Cloud「GQL query statements」

• Google Cloud「GQL schema statements」

2. ソース間・設計思想上の主要論点

論点1：ネスト構造による非正規化 vs 正規化テーブル＋JOIN

🔴 争点：
1対N関係を持つデータについて、ARRAY / STRUCTを使って同一テーブル内にネストするか、複数テーブルへ分離してJOINするか。

🔴 立場A：ネスト・反復フィールドを利用する

階層的なデータを頻繁に一緒に分析する場合は、ネスト構造によって関連データを同じ処理単位へ局所化できます。

その結果、

• JOINの削減

• GROUP BYの削減

• shuffle削減

• slot利用量削減

などの性能改善が期待できます。

🔴 立場B：正規化構造を維持する

次のような場合は、正規化テーブルを維持する方が管理しやすい場合があります。

• 共通マスタを多くのデータセットから参照する

• 属性変更が多い

• repeated relationshipのカーディナリティが非常に高い

• 既存スター・スキーマですでに十分な性能が得られている

🔴 前提・条件の違い：
判断材料は単純な「静的データか動的データか」ではなく、

1. 関係が階層的か

2. 一緒にクエリされるか

3. カーディナリティ

4. 更新頻度

5. JOIN / shuffleコスト

6. 再利用性

です。

🔴 本当に対立しているか：
対立ではなく、ワークロードに応じたデータモデリング上のtrade-offです。

🔴 合意部分：
JOINやGROUP BYによるshuffleが大きなボトルネックになっている場合、ネスト・反復フィールドは有力な改善手段です。

一方、ネスト化しても必ずスキャンデータ量が減るわけではありません。

🔴 未解決点：
特定のデータセットについて、

JOINによる追加計算コスト
vs
ネストテーブルの更新・再構築コスト

のどちらが大きいか。

🔴 何が分かれば議論が前進するか：

• Query Plan

• shuffle bytes

• slot-ms

• bytes processed

• 更新件数

• ELT再構築時間

などを実測し、複数のモデリング案を比較します。

論点2：クエリ課金モデル「オンデマンド」vs「Capacity / Editions」

🔴 争点：
BigQueryの計算料金を、

• スキャンデータ量に応じて支払うオンデマンド料金

• slot-hourベースで計算能力を管理するCapacity pricing

のどちらで運用するか。

🔴 立場A：オンデマンド料金

クエリで処理されたデータ量に応じて料金が発生します。

特徴は、

• 初期導入が容易

• ワークロードが少ない場合に扱いやすい

• 利用量に直接連動する

ことです。

一方、大量のSELECT *やパーティションを無視したクエリなどにより、意図しない大量スキャンが発生するとコストが増加します。

🔴 立場B：Capacity pricing / Editions

Capacity pricingでは、slot-hourを基準として計算資源を管理します。

BigQuery Editionsでは、

• Standard

• Enterprise

• Enterprise Plus

が用意されており、autoscaling reservationを利用できます。

Capacity commitmentは必須ではありません。EnterpriseおよびEnterprise Plusでは、一定期間のcommitmentによる料金低減も選択できます。

🔴 前提・条件の違い：

料金モデルは組織人数ではなく、

• 月間bytes processed

• workload variability

• concurrency

• peak slot demand

• workload isolationの必要性

• コスト予測可能性

などによって判断します。

🔴 本当に対立しているか：
排他的な選択とは限りません。

BigQueryでは、プロジェクトやワークロードに応じてオンデマンドとEditionsを使い分けることもできます。

🔴 合意部分：
どの料金モデルを採用しても、

• 必要な列だけ読む

• 不要なパーティションを読まない

• 過剰なshuffleを避ける

といったクエリ設計は重要です。

🔴 未解決点：

月間スキャン量 × On-demand単価

と、

平均・ピークslot使用量 × Capacity料金

のどこに損益分岐点があるか。

🔴 何が分かれば議論が前進するか：

• 月間bytes processed

• slot-ms

• peak slot usage

• query concurrency

• 曜日・時間帯別の負荷

を取得し、料金モデルをシミュレーションします。

3. 深層理解度チェック

Q1：なぜGA4のBigQueryエクスポートデータでは、event_paramsがARRAY<STRUCT>として保存されているのか。データ構造とクエリ性能の観点から説明せよ。

模範回答：

GA4では1つのイベントに対して複数のパラメータが付随し、その数や種類はイベントごとに異なります。

したがって、

1 Event
 ├ parameter A
 ├ parameter B
 └ parameter C

という1:N構造をARRAY<STRUCT>として保持することは、データモデルとして自然です。

パラメータを別テーブルへ分離すると、イベントとパラメータを分析する際にJOINが必要になります。ネスト構造では、関連するパラメータを同じイベント行に保持し、UNNEST()によって必要に応じて展開できます。

これによりJOINやデータshuffleを抑え、BigQueryの分散処理に適した形で分析できます。

ただし、ネスト構造そのものが常にbytes processedを削減するわけではなく、課金削減効果は選択列やクエリ内容にも依存します。

表面的な理解で起こりやすい誤答：

「Google独自形式だから」「UNNESTを使わせるため」と考えること。

より本質的には、

ことが重要です。

Q2：Cloud SQLなどのOLTP DBとBigQueryを分離する理由を、アクセスパターンの違いから説明せよ。

模範回答：

Cloud SQLなどのOLTPデータベースは、

SELECT one row
INSERT one row
UPDATE one row
COMMIT

のような短く高頻度のトランザクションを得意とします。

一方BigQueryは列指向の分析基盤であり、

数百万〜数十億行
      ↓
必要な列をscan
      ↓
filter
      ↓
join / aggregate

のような大規模分析に適しています。

本番の業務DBで大規模集計を行うと、トランザクション処理と分析処理が同じリソースを奪い合う可能性があります。

そこで、

Cloud SQL
  ↓ CDC
BigQuery

のように分析ワークロードを分離すると、業務処理への影響を抑えながら大規模分析を実行できます。

表面的な理解で起こりやすい誤答：

BigQueryをCloud SQLの「上位版」と考え、WebアプリのトランザクションDBもBigQueryへ置き換えられると考えること。

両者は上下関係ではなく、主用途が異なります。

Q3：BigQueryのオンデマンド料金でクエリ費用が高騰している。スキャンデータ量を減らす代表的な方法を説明せよ。

模範回答：

代表的な方法は次の3つです。

1. パーティショニング

日付などの値によってテーブルをパーティション分割します。

例えば、

WHERE event_date BETWEEN '2026-09-01' AND '2026-09-07'

と指定することで、対象期間以外のパーティションを読み取らずに済みます。

これをpartition pruningと呼びます。

2. クラスタリング

頻繁にフィルタされるカラムに基づいてデータを配置します。

例えば、

WHERE customer_id = 'A123'

のような条件で、関連するstorage blocksだけを読み込める可能性があります。

3. 必要な列だけSELECTする

BigQueryは列指向ストレージなので、

SELECT *

より、

SELECT
  customer_id,
  sales

のように必要な列だけ読む方がスキャン量を抑えられます。

表面的な理解で起こりやすい誤答：

SELECT *
FROM huge_table
LIMIT 100;

とすれば100行分しか課金されないと考えること。

LIMITは結果行数を制限するものであり、一般に読み取る列データ量そのものを削減する方法ではありません。

また、

という説明も現在は正しくありません。

BigQueryはPRIMARY KEYとFOREIGN KEYをサポートします。ただし、これらの制約はNOT ENFORCEDであり、BigQuery自身が参照整合性を保証するわけではありません。クエリ最適化に利用される場合があります。

さらに、BigQueryにはSearch Indexがあり、SEARCH()や一部の=, IN, LIKE, STARTS_WITHなどを高速化できます。

したがってBigQueryでは、

Partitioning
Clustering
Search Index
PK / FK metadata

などを用途に応じて使い分けます。

Q4：Conversational Analyticsが単純な生成AIによるSQL生成と異なる点を説明せよ。

模範回答：

一般的な生成AIへ単純にテーブル定義を渡した場合、AIは企業固有の用語や集計ルールを十分理解していない可能性があります。

例えば、

という質問に対し、「優良顧客」が、

年間購入額100万円以上

なのか、

購入回数10回以上

なのかをAIだけでは判断できません。

Conversational AnalyticsのData Agentでは、

• Table / field metadata

• Synonyms

• Business Terms

• Glossary

• Agent Instructions

• Verified Queries

を設定できます。

Verified Queryは、組織が正しいと確認したSQLと自然言語質問を対応付ける仕組みです。

ユーザーの質問がVerified Queryに適切に一致した場合、AgentはそのSQLをそのまま実行できます。一致しない場合でも、Verified Queryをデータ構造やビジネスロジックを理解するための参照情報として利用します。

したがって、Conversational Analyticsの本質は、

自然言語
   ↓
生成AI

だけではなく、

自然言語
   ↓
Semantic Context
   +
Verified Queries
   +
Agent Instructions
   ↓
Data Agent
   ↓
SQL / 分析結果

という構造にあります。

ただし、AIによる生成結果の正しさが常に保証されるわけではありません。

重要なKPIや定型分析ではVerified Queriesを用い、それ以外の分析でも生成SQLや結果を検証することが必要です。

Q5：BigQueryにおける「スキャン量」「shuffle」「slot-ms」の違いを説明せよ。

模範回答：

この3つはBigQueryの性能とコストを理解するうえで分けて考える必要があります。

スキャン量

BigQueryがストレージから読み取ったデータ量。

オンデマンド料金では特に重要です。

代表的な削減方法は、

• 不要な列を読まない

• パーティションを絞る

• Search Indexを利用する

などです。

shuffle

JOINやGROUP BYなどの処理で、分散した計算ノード間でデータを移動する処理。

shuffleが大きいと、

• ネットワーク通信

• 中間データ

• 計算時間

が増えます。

ネスト構造やJOIN前の集約などは、shuffle削減に有効な場合があります。

slot-ms

BigQueryの計算リソースであるslotをどれだけの時間使用したかを示す指標です。

概念的には、

slot-ms
≒
使用slot数 × 実行時間

と考えることができます。

したがって、

bytes processed
≠
shuffle
≠
slot-ms

です。

例えばネスト構造によってshuffleとslot-msが大きく減っても、読み取る列やデータ量が変わらなければbytes processedはそれほど減らない場合があります。

この区別を理解することが、BigQueryの性能最適化とFinOpsの基礎になります。

4. BigQuery設計で覚えておくべき判断原則

BigQueryでは、「この機能を使えば必ず速い」という考え方ではなく、ワークロードに応じてボトルネックを判断します。

スキャン量が大きい場合

検討するもの：

Partitioning
Column pruning
Clustering
Search Index

JOIN / shuffleが大きい場合

検討するもの：

Nested / Repeated Fields
Pre-aggregation
Materialization
JOIN order / filter

slot利用量が大きい場合

検討するもの：

Query plan
Repeated CTE / subquery
JOIN strategy
Workload management
Capacity reservations

AI分析の意味解釈が不安定な場合

検討するもの：

Metadata
Glossary
Business Terms
Agent Instructions
Verified Queries

5. 最終的なメンタルモデル

BigQueryを理解する最も重要な問いは、

ではありません。

より本質的には、

を考えることです。

整理すると、

BigQuery
│
├─ Storage
│   ├─ Columnar storage
│   ├─ Partitioning
│   ├─ Clustering
│   ├─ Nested / Repeated
│   └─ Search Index
│
├─ Compute
│   ├─ Slots
│   ├─ Distributed execution
│   ├─ Shuffle
│   └─ Editions / Reservations
│
├─ Modeling
│   ├─ Denormalization
│   ├─ ARRAY
│   ├─ STRUCT
│   └─ PK / FK metadata
│
├─ Semantics
│   ├─ Metadata
│   ├─ Glossary
│   ├─ Agent Instructions
│   └─ Verified Queries
│
└─ Advanced Analytics
    ├─ BigQuery ML / AI
    ├─ Conversational Analytics
    └─ BigQuery Graph

という構造になります。

つまり、BigQueryは単なる「巨大なSQLデータベース」ではありません。

ストレージ、分散計算、データモデリング、セマンティクス、AIを統合した分析プラットフォーム

として理解すると、各機能の位置づけが整理しやすくなります。