STEP 17：BigQuery入門

📋 このステップで学ぶこと

BigQueryとは何か、Redshiftとの違い
データセットとテーブルの作成
SQLでのクエリ実行（標準SQL）
BigQueryの料金体系と無料枠
公開データセットを使った実践演習

🎯 このステップのゴール

このステップを終えると、BigQueryでデータセットとテーブルを作成し、SQLでデータ分析ができるようになります。Googleの強力なDWHを体験しましょう！

🎯 1. BigQueryとは？

BigQueryの基本

BigQuery（ビッグクエリ）は、Googleが提供するフルマネージドのサーバーレスデータウェアハウスです。

💡 例え話：レストランで注文する

【従来のDWH（Redshift）= 自分でキッチンを持つ】
1. キッチン（サーバー）を借りる
2. 調理器具（CPU、メモリ）を揃える
3. 料理人（処理能力）を雇う
4. 食材（データ）を仕入れて調理
→ キッチンの維持費が毎月かかる
→ 忙しくない時も固定費がかかる

【BigQuery = レストランで注文する】
1. レストラン（Google）に行く
2. メニュー（SQL）を見て注文
3. シェフ（Googleの分散処理）が調理
4. 料理（結果）が運ばれてくる
→ 食べた分だけ支払う（従量課金）
→ キッチンの管理は不要！

【具体例】
「1年分の売上データから月別集計を出して」
・従来：サーバー起動→処理→5分待つ→結果
・BigQuery：SQLを書く→数秒で結果！

📝 BigQueryの特徴

超高速：数億行のデータでも数秒で集計（Googleの分散処理技術）
サーバーレス：サーバー管理不要、SQLを書くだけ
大容量対応：ペタバイト級（100万GB以上）でも処理可能
従量課金：使った分だけ支払い、毎月1TBまで無料
標準SQL：学習コストが低い、他のDBと互換性が高い

BigQueryの内部アーキテクチャ

💡 例え話：超巨大な図書館

【BigQueryの仕組み = 超効率的な図書館】

┌─────────────────────────────────────────────────────┐
│                BigQueryの内部構造                    │
├─────────────────────────────────────────────────────┤
│                                                     │
│  あなた（ユーザー）                                  │
│      ↓ SQL「2025年1月の売上を教えて」               │
│                                                     │
│  📋 受付（クエリエンジン）                          │
│      ↓ 「このリクエストを処理するには…」          │
│                                                     │
│  👥 1000人の司書（ワーカー）に指示                   │
│      ↓ 「あなたはA棚、あなたはB棚…」              │
│                                                     │
│  📚 カラムナーストレージ（本棚）                    │
│  ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐                  │
│  │日付 │ │金額 │ │商品 │ │顧客 │  ← 列ごとに保存   │
│  │棚   │ │棚   │ │棚   │ │棚   │                  │
│  └─────┘ └─────┘ └─────┘ └─────┘                  │
│      ↓ 必要な棚（カラム）だけ読む                   │
│                                                     │
│  📊 結果を集約して返答                              │
│      「2025年1月の売上は1億2345万円です」           │
│                                                     │
└─────────────────────────────────────────────────────┘

【ポイント】
・1000人の司書が同時に作業 → 超高速
・必要な棚だけ見る → 無駄なスキャンなし
・カラムナー（列指向）→ 集計に最適化

Redshift vs BigQuery 比較

💡 例え話：マイカー vs タクシー（再び）

【Redshift = マイカー（レンタカー長期契約）】
・毎月固定費がかかる（クラスター課金）
・乗らない日も駐車場代がかかる
・車のメンテナンスは自分で管理
・毎日通勤するなら便利

【BigQuery = タクシー（高級タクシー）】
・乗った距離だけ支払い（従量課金）
・乗らない日は0円
・車のメンテナンスは会社任せ
・たまに乗るなら超お得

【Athena = 普通のタクシー】
・BigQueryより少し安い（$5/TB）
・速度はBigQueryより遅め
・AWSエコシステム向け

📊 Redshift vs BigQuery vs Athena 比較

項目	Redshift（AWS）	BigQuery（GCP）	Athena（AWS）
アーキテクチャ	クラスター型	サーバーレス	サーバーレス
料金	時間課金 ($0.25〜/時間)	従量課金 ($6.25/TB)	従量課金 ($5/TB)
速度	高速	超高速	やや遅い
無料枠	2ヶ月間	毎月1TB	なし
得意分野	長時間処理	アドホック分析	S3直接クエリ
連携	AWS全般	GA4, Looker	S3, Glue

💡 どれを選ぶべき？

【BigQueryがおすすめな場面】
・サーバー管理をしたくない
・たまにしか分析しない（コスト削減）
・超大量データ（TB〜PB級）を扱う
・Googleサービス（GA4、Firebase）と連携したい
・最速で結果が欲しい

【Redshiftがおすすめな場面】
・既にAWSを使っている
・24時間常にクエリが走る
・PostgreSQL互換が必要
・コストを予測可能にしたい

【Athenaがおすすめな場面】
・S3のデータを直接分析したい
・インフラ管理したくない（AWS内で）
・BigQueryより安く済ませたい

📊 2. BigQueryの基本構造

階層構造の理解

BigQueryは、3層の階層構造でデータを管理します。

💡 例え話：会社のファイル管理

【BigQueryの階層 = 会社のファイル管理】

🏢 会社（Project）= プロジェクト
 └── 📁 部署（Dataset）= データセット
      └── 📄 ファイル（Table）= テーブル

【具体例】
🏢 my-project-12345（会社 = プロジェクト）
 ├── 📁 sales_data（営業部 = データセット）
 │    ├── 📄 orders（受注ファイル = テーブル）
 │    ├── 📄 customers（顧客ファイル = テーブル）
 │    └── 📄 products（商品ファイル = テーブル）
 │
 └── 📁 marketing_data（マーケ部 = データセット）
      ├── 📄 campaigns（キャンペーン = テーブル）
      └── 📄 analytics（アクセス解析 = テーブル）

【ポイント】
・プロジェクト：課金単位、アクセス制御の基本単位
・データセット：テーブルをグループ化、リージョンを設定
・テーブル：実際のデータを格納

1️⃣ プロジェクト

GCPの最上位単位。課金やアクセス制御の基本単位。1つのプロジェクトに複数のデータセットを作成できます。

2️⃣ データセット

テーブルをまとめるフォルダのようなもの。リージョンを設定し、関連するテーブルをグループ化します。

3️⃣ テーブル

実際のデータを格納する場所。カラムナーストレージ形式で保存され、高速にクエリできます。

BigQueryのリージョン

🌏 主なリージョン

asia-northeast1：東京（日本からのアクセスに最適）
asia-northeast3：ソウル
us-central1：アイオワ（米国中央）
europe-west2：ロンドン
US：米国マルチリージョン（公開データセットはここが多い）

💡 ポイント：日本からアクセスするなら東京リージョンがおすすめ。レイテンシ（遅延）が小さくなります。ただし、公開データセットとJOINする場合は同じリージョンにする必要があります。

🚀 3. BigQueryを使ってみよう

STEP 1: GCPコンソールにアクセス

Google Cloud Consoleにアクセス
左上のメニュー（☰）から「BigQuery」を選択
BigQuery Studioが開きます

📝 BigQuery Studioとは？

BigQueryのWebベースの統合開発環境です。ブラウザだけでSQLを書いて、データを分析できます。インストール不要で、どこからでもアクセス可能！

STEP 2: データセットの作成

📝 データセット作成手順

左側のナビゲーションで、プロジェクト名の右にある「︙」（縦3点）をクリック
「データセットを作成」を選択
以下の情報を入力：
- データセットID：my_first_dataset
- データのロケーション：asia-northeast1（東京）
- デフォルトのテーブルの有効期限：なし（デフォルト）
「データセットを作成」ボタンをクリック

STEP 3: テーブルの作成（GUIで）

📝 テーブル作成手順

作成したデータセット（my_first_dataset）をクリック
「テーブルを作成」をクリック
以下の情報を入力：
- テーブルの作成元：空のテーブル
- テーブル名：customers
- スキーマ：「フィールドを追加」で以下を設定

【スキーマ定義】

フィールド名       型         モード
customer_id      INTEGER    REQUIRED（必須）
name             STRING     REQUIRED
email            STRING     NULLABLE（NULL許可）
age              INTEGER    NULLABLE
country          STRING     REQUIRED
signup_date      DATE       REQUIRED

💡 データ型について

INTEGER：整数（1, 2, 3…）
STRING：文字列（”佐藤”, “Tokyo”など）
DATE：日付（2025-01-01）
FLOAT64：小数（3.14, 99.99など）
BOOLEAN：真偽値（TRUE/FALSE）
TIMESTAMP：日時（2025-01-01 12:34:56 UTC）
ARRAY：配列（複数の値を格納）
STRUCT：構造体（入れ子のデータ）

STEP 4: データの挿入

— サンプルデータを挿入
— ※ my-project-12345 はあなたのプロジェクトIDに置き換えてください
INSERT INTO `my-project-12345.my_first_dataset.customers`
  (customer_id, name, email, age, country, signup_date)
VALUES
  (1, ‘佐藤太郎’, ‘sato@example.com’, 28, ‘Japan’, ‘2024-01-15’),
  (2, ‘鈴木花子’, ‘suzuki@example.com’, 35, ‘Japan’, ‘2024-02-20’),
  (3, ‘John Smith’, ‘john@example.com’, 42, ‘USA’, ‘2024-03-10’),
  (4, ‘Maria Garcia’, ‘maria@example.com’, 31, ‘Spain’, ‘2024-04-05’),
  (5, ‘山田次郎’, ‘yamada@example.com’, 25, ‘Japan’, ‘2024-05-12’);

⚠️ 注意点

my-project-12345の部分は、あなたのプロジェクトIDに置き換えてください。プロジェクトIDは、BigQueryの左側のナビゲーションに表示されています。

STEP 5: クエリの実行

— 全データを取得
SELECT * 
FROM `my-project-12345.my_first_dataset.customers`
ORDER BY customer_id;

【実行結果】 customer_id | name | email | age | country | signup_date ————|————–|———————-|—–|———|———— 1 | 佐藤太郎 | sato@example.com | 28 | Japan | 2024-01-15 2 | 鈴木花子 | suzuki@example.com | 35 | Japan | 2024-02-20 3 | John Smith | john@example.com | 42 | USA | 2024-03-10 4 | Maria Garcia | maria@example.com | 31 | Spain | 2024-04-05 5 | 山田次郎 | yamada@example.com | 25 | Japan | 2024-05-12 ✅ 5行が返されました（0.3秒） 💰 処理データ量：0 B（キャッシュから）

💻 4. BigQueryでSQLを書こう

基本的なSELECT文

— 例1: 日本のお客様だけを取得
SELECT 
  customer_id,
  name,
  age,
  signup_date
FROM `my-project-12345.my_first_dataset.customers`
WHERE country = ‘Japan’
ORDER BY signup_date;

【実行結果】 customer_id | name | age | signup_date ————|———-|—–|———— 1 | 佐藤太郎 | 28 | 2024-01-15 2 | 鈴木花子 | 35 | 2024-02-20 5 | 山田次郎 | 25 | 2024-05-12

— 例2: 国別の顧客統計
SELECT 
  country,
  COUNT(*) AS customer_count,
  ROUND(AVG(age), 1) AS average_age,
  MIN(age) AS youngest,
  MAX(age) AS oldest
FROM `my-project-12345.my_first_dataset.customers`
GROUP BY country
ORDER BY customer_count DESC;

【実行結果】 country | customer_count | average_age | youngest | oldest ——–|—————-|————-|———-|——– Japan | 3 | 29.3 | 25 | 35 USA | 1 | 42.0 | 42 | 42 Spain | 1 | 31.0 | 31 | 31

BigQueryの便利な関数

— 例3: 日付関数を使う
SELECT 
  name,
  signup_date,
  CURRENT_DATE() AS today,
  DATE_DIFF(CURRENT_DATE(), signup_date, DAY) AS days_since_signup,
  DATE_ADD(signup_date, INTERVAL 1 YEAR) AS one_year_anniversary
FROM `my-project-12345.my_first_dataset.customers`
ORDER BY signup_date;

📚 BigQueryの主な関数

【集計関数】
COUNT(*), SUM(col), AVG(col), MIN(col), MAX(col)
COUNT(DISTINCT col)  — 重複を除いてカウント

【文字列関数】
CONCAT(str1, str2)   — 文字列結合
UPPER(str)           — 大文字変換
LOWER(str)           — 小文字変換
LENGTH(str)          — 文字数
SUBSTR(str, start, len)  — 部分文字列

【日付関数】
CURRENT_DATE()       — 今日の日付
CURRENT_TIMESTAMP()  — 現在日時
DATE_DIFF(date1, date2, DAY)  — 日数差
DATE_ADD(date, INTERVAL n DAY)  — 日付加算
EXTRACT(YEAR FROM date)  — 年を抽出

【条件関数】
IF(条件, true値, false値)
CASE WHEN 条件 THEN 値 ELSE 値 END
COALESCE(val1, val2)  — NULLでない最初の値
IFNULL(val, default)  — NULLなら代わりの値

【ウィンドウ関数】
ROW_NUMBER() OVER(ORDER BY col)  — 行番号
RANK() OVER(ORDER BY col)        — 順位
LAG(col) OVER(ORDER BY col)      — 前の行の値
LEAD(col) OVER(ORDER BY col)     — 次の行の値

💰 5. BigQueryの料金体系

2つの料金体系

BigQueryには、ストレージ料金とクエリ料金の2つがあります。

💡 例え話：駐車場と高速道路

【ストレージ料金 = 駐車場代】
・車（データ）を停めておく料金
・停めている間、毎月かかる
・古い車（90日以上使わないデータ）は半額に

【クエリ料金 = 高速道路代】
・走った距離（スキャンしたデータ量）で課金
・走らなければ0円
・毎月1000km（1TB）まで無料

📊 料金表

項目	内容	料金（東京リージョン）
ストレージ料金	データを保存する料金	・アクティブ：$0.023/GB/月・長期（90日未使用）：$0.016/GB/月
クエリ料金	SQLを実行する料金	$6.25/TB（処理したデータ量）毎月1TBまで無料

💡 コスト計算例

【例1：小規模な分析】
・ストレージ：10GB → 無料（10GBまで無料）
・クエリ：月50GB → 無料（1TBまで無料）
・合計：$0/月

【例2：中規模な分析】
・ストレージ：100GB → $0.023 × 100 = $2.30/月
・クエリ：月2TB → 1TB無料 + 1TB × $6.25 = $6.25/月
・合計：$8.55/月（約1,300円）

【例3：大規模な分析】
・ストレージ：1TB → $0.023 × 1000 = $23/月
・クエリ：月10TB → 1TB無料 + 9TB × $6.25 = $56.25/月
・合計：$79.25/月（約12,000円）

【比較：同等のRedshiftクラスター】
・dc2.large × 2ノード × 24時間 × 30日
・$0.25 × 2 × 24 × 30 = $360/月
→ BigQueryの方が約4.5倍安い！

コスト削減のコツ

💰 料金を抑える5つのテクニック

SELECT *を避ける：必要なカラムだけを指定（最重要！）
LIMIT句を使う：開発時は少ない行数で確認
パーティションを活用：日付でデータを分割（次のSTEPで学習）
クエリ前にコスト確認：実行前に処理データ量が右上に表示される
キャッシュを活用：同じクエリは24時間キャッシュされる（無料）

— ❌ NG例：全カラムを取得（無駄が多い）
SELECT * 
FROM `bigquery-public-data.usa_names.usa_1910_current`;
— → 処理データ量：約500MB

— ✅ OK例：必要なカラムだけ指定
SELECT name, year, number
FROM `bigquery-public-data.usa_names.usa_1910_current`
WHERE year = 2020;
— → 処理データ量：約50MB（1/10に削減！）

⚠️ コスト確認方法

クエリを実行する前に、エディタの右上に「このクエリでは○○ B を処理します」と表示されます。実行前に必ず確認しましょう！予想外に大きい場合は、WHERE句やカラム指定を見直してください。

🌍 6. 公開データセットで実践演習

BigQueryには、無料で使える公開データセットがたくさんあります！

公開データセットの追加方法

左側のナビゲーションで「+ データを追加」をクリック
「公開データセットを調べる」を選択
興味のあるデータセットを選んで「データセットを表示」

演習1: アメリカの赤ちゃんの名前ランキング

— 2020年の人気の名前TOP10（男の子）
SELECT 
  name,
  SUM(number) AS total_count
FROM `bigquery-public-data.usa_names.usa_1910_current`
WHERE year = 2020
  AND gender = ‘M’
GROUP BY name
ORDER BY total_count DESC
LIMIT 10;

演習2: 年代別の人気の名前の変遷

— 「James」という名前の人気の変遷
SELECT 
  year,
  SUM(number) AS count
FROM `bigquery-public-data.usa_names.usa_1910_current`
WHERE name = ‘James’
  AND gender = ‘M’
  AND year >= 1950
GROUP BY year
ORDER BY year;

📊 おすすめの公開データセット

usa_names：アメリカの赤ちゃんの名前（1910-現在）
covid19_open_data：COVID-19の統計データ
google_analytics_sample：Google Analytics サンプルデータ
new_york_taxi_trips：NYCタクシーの運行データ
github_repos：GitHubのリポジトリデータ
stackoverflow：Stack Overflowの投稿データ
hacker_news：Hacker Newsの投稿データ

📝 STEP 17 のまとめ

✅ このステップで学んだこと

BigQueryは、Googleのサーバーレスデータウェアハウス
Redshiftと違い、サーバー管理が不要で使った分だけ課金
プロジェクト → データセット → テーブルの3層構造
SQLエディタで標準SQLを書いて分析できる
毎月1TBのクエリが無料で実行可能
公開データセットで実践的な分析ができる

💡 重要ポイント

BigQueryは「すぐ使える」「速い」「簡単」の3拍子揃ったDWH
SELECT *を避けるのがコスト削減の最重要テクニック
クエリ実行前に処理データ量を確認する習慣をつける
同じクエリは24時間キャッシュされるので無料

🎯 次のステップの準備

次のSTEP 18では、GCSからBigQueryへのデータロードを学びます。
CSVファイルをBigQueryに効率的に取り込む方法をマスターしましょう！

📝 理解度チェック

問題 1 基礎

BigQueryとRedshiftの違いを3つ挙げてください。

【解答例】

アーキテクチャ：BigQueryはサーバーレス、Redshiftはクラスター型
料金体系：BigQueryは従量課金、Redshiftは時間課金
起動時間：BigQueryは即座、Redshiftは数分かかる

他にも「スケーリング」「SQL方言」「無料枠」などの違いも正解です。

問題 2 基礎

BigQueryの3層構造を順番に答えてください。

【解答】

プロジェクト（Project）
データセット（Dataset）
テーブル（Table）

問題 3 応用

以下のSQLを実行して、アメリカで2020年に最も人気だった女の子の名前TOP5を取得してください。

データセット：bigquery-public-data.usa_names.usa_1910_current
条件：year = 2020、gender = ‘F’

【解答】

SELECT 
  name,
  SUM(number) AS total_count
FROM `bigquery-public-data.usa_names.usa_1910_current`
WHERE year = 2020
  AND gender = ‘F’
GROUP BY name
ORDER BY total_count DESC
LIMIT 5;

【実行結果】

問題 4 応用

BigQueryのコスト削減テクニックを3つ挙げてください。

【解答例】

SELECT *を避ける：必要なカラムだけを指定する
パーティションを活用：日付でデータを分割してスキャン量を削減
クエリ前にコスト確認：実行前に処理データ量を確認する

他にも「LIMIT句を使う」「キャッシュを活用」なども正解です。

❓ よくある質問

Q1: BigQueryは本当にサーバーレスですか？

はい、もちろん裏側にはGoogleのサーバーがあります。「サーバーレス」というのは、「ユーザーがサーバーを管理しなくていい」という意味です。Redshiftのように「何台のサーバーを起動するか」「どのスペックにするか」を考える必要がなく、Googleが自動で最適なリソースを割り当ててくれます。

Q2: 無料枠の1TBを超えたら、いきなり課金されますか？

はい、自動的に課金されます。ただし、クエリを実行する前に「このクエリは○○ B を処理します」と表示されるので、事前に確認できます。心配な場合は、プロジェクトに予算アラートを設定することをおすすめします（GCPの請求設定で可能）。

Q3: BigQueryのクエリ結果は保存されますか？

はい、24時間キャッシュされます。同じクエリを24時間以内に再実行すると、キャッシュから結果が返され、追加料金はかかりません。また、クエリ結果を新しいテーブルとして保存することもできます（その場合はストレージ料金が発生）。

Q4: AthenaとBigQueryはどちらが安いですか？

単価はAthenaの方が安いです（Athena: $5/TB、BigQuery: $6.25/TB）。ただし、BigQueryは毎月1TBの無料枠があるため、月1TB以下の利用ならBigQueryの方がお得です。また、BigQueryの方が一般的に高速です。

Q5: BigQueryで大量のINSERT文を実行すると遅いですか？

はい、INSERT文での1行ずつの挿入は推奨されません。BigQueryは大量データの一括ロードに最適化されています。データを挿入する場合は、CSVファイルをGCSにアップロードしてから、BigQueryにロードする方法が推奨されます（次のSTEPで学習します）。INSERT文は1日あたりの上限もあるので注意してください。

Q6: LIMITを付けるとコストは下がりますか？

いいえ、LIMITだけではコストは下がりません。BigQueryは「スキャンしたデータ量」で課金されるため、LIMITで結果を10行に絞っても、全データをスキャンする必要があればコストは同じです。コスト削減にはWHERE句でフィルタリングするか、パーティションを活用してください。

☁️ STEP 17: BigQuery入門

📋 このステップで学ぶこと

🎯 このステップのゴール

🎯 1. BigQueryとは？

BigQueryの基本

💡 例え話：レストランで注文する

📝 BigQueryの特徴

BigQueryの内部アーキテクチャ

💡 例え話：超巨大な図書館

Redshift vs BigQuery 比較

💡 例え話：マイカー vs タクシー（再び）

📊 Redshift vs BigQuery vs Athena 比較

💡 どれを選ぶべき？

📊 2. BigQueryの基本構造

階層構造の理解

💡 例え話：会社のファイル管理

1️⃣ プロジェクト

2️⃣ データセット

3️⃣ テーブル

BigQueryのリージョン

🌏 主なリージョン

🚀 3. BigQueryを使ってみよう

STEP 1: GCPコンソールにアクセス

📝 BigQuery Studioとは？

STEP 2: データセットの作成

📝 データセット作成手順

STEP 3: テーブルの作成（GUIで）

📝 テーブル作成手順

💡 データ型について

STEP 4: データの挿入

⚠️ 注意点

STEP 5: クエリの実行

💻 4. BigQueryでSQLを書こう

基本的なSELECT文

BigQueryの便利な関数

📚 BigQueryの主な関数

💰 5. BigQueryの料金体系

2つの料金体系

💡 例え話：駐車場と高速道路

📊 料金表

💡 コスト計算例

コスト削減のコツ

💰 料金を抑える5つのテクニック

⚠️ コスト確認方法

🌍 6. 公開データセットで実践演習

公開データセットの追加方法

演習1: アメリカの赤ちゃんの名前ランキング

演習2: 年代別の人気の名前の変遷

📊 おすすめの公開データセット

📝 STEP 17 のまとめ

✅ このステップで学んだこと

💡 重要ポイント

🎯 次のステップの準備

📝 理解度チェック

BigQueryとRedshiftの違いを3つ挙げてください。

BigQueryの3層構造を順番に答えてください。

以下のSQLを実行して、アメリカで2020年に最も人気だった女の子の名前TOP5を取得してください。

BigQueryのコスト削減テクニックを3つ挙げてください。

❓ よくある質問

学習メモ