【AWS経験者向け】BigQueryをハンズオン！クエリを書いてデータ分析を体験
はじめに：サーバーレスデータウェアハウスを実践で体験する
皆さん、こんにちは！「30日間でGCPをマスターするAWSエンジニアの挑戦」シリーズ、16日目へようこそ。
前回は、AWSのRedshiftとGCPのBigQueryを比較し、BigQueryのサーバーレスアーキテクチャと処理データ量課金モデルというユニークな設計思想を学びました。理論だけでなく、実際に手を動かしてみることが、BigQueryのパワフルさを体感する一番の近道です。
今日は、BigQueryの基本操作から高度な分析まで、段階的に実践していきます。AWSでRedshiftやAthenaを使った経験がある方なら、その手軽さとスケーラビリティ、そしてコスト最適化の重要性を実感していただけるはずです。
この記事で学べること：
- BigQueryコンソールの基本操作
- 公開データセットを活用した実践的なSQL分析
- コスト最適化のテクニック
- AWS AthenaとBigQueryの使い分け
- エラー対処法とベストプラクティス
BigQueryの基本操作：環境準備から始めよう
Step 1: BigQuery環境のセットアップ
-
BigQueryコンソールを開く
- GCPコンソールで「BigQuery」と検索し、BigQuery Studioを開きます
- 初回アクセス時は、利用規約への同意が求められます
-
プロジェクトの確認
- 上部のプロジェクト選択で、適切なGCPプロジェクトが選択されていることを確認
- 課金が有効になっているプロジェクトを使用してください
-
BigQuery Sandboxの活用
- 課金アカウントなしでも、BigQuery Sandboxとして月1TBまで無料で利用可能
- 学習目的であれば十分な容量です
Step 2: 公開データセットの追加
BigQueryには、分析練習に最適な豊富な公開データセットが用意されています。
-
公開データセットへのアクセス
- 左側の「エクスプローラ」パネルで「データを追加」をクリック
- 「公開データセットを探索」を選択
-
Stack Overflowデータセットの追加
データセット検索：「stackoverflow」 選択：bigquery-public-data.stackoverflow- プログラマーに馴染みのあるStack Overflowの投稿データ
- 実際のテキストデータ分析を体験できます
-
Google Analyticsサンプルデータの追加
データセット検索：「ga4」 選択：bigquery-public-data.ga4_obfuscated_sample_ecommerce- Webアナリティクスの実データサンプル
- ECサイトの行動分析を体験できます
実践ハンズオン：段階的にBigQueryをマスターする
レベル1：基本的なデータ探索
まずは、データの概要を把握することから始めましょう。
クエリ1: テーブル構造の確認
-- Stack Overflowの質問テーブルの基本情報
SELECT
column_name,
data_type,
is_nullable
FROM `bigquery-public-data.stackoverflow`.INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'posts_questions'
ORDER BY ordinal_position;
クエリ2: データのサンプリング
-- 最新の質問を10件取得（処理データ量を抑制）
SELECT
id,
title,
score,
view_count,
creation_date,
tags
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date >= '2023-01-01'
ORDER BY creation_date DESC
LIMIT 10;
💡 コスト最適化のポイント：
-
SELECT *は避け、必要なカラムのみを指定 - 日付フィルターで処理対象データを限定
-
LIMIT句でデータ量をコントロール
レベル2：集計分析とグルーピング
クエリ3: 人気プログラミング言語の分析
-- タグ別の質問数と平均スコアを分析
WITH popular_tags AS (
SELECT
tag,
COUNT(*) as question_count,
AVG(score) as avg_score,
AVG(view_count) as avg_views
FROM `bigquery-public-data.stackoverflow.posts_questions`,
UNNEST(SPLIT(tags, '|')) as tag
WHERE
creation_date >= '2023-01-01'
AND tag IN ('python', 'javascript', 'java', 'go', 'rust', 'typescript', 'react')
GROUP BY tag
)
SELECT
tag,
question_count,
ROUND(avg_score, 2) as avg_score,
ROUND(avg_views, 0) as avg_views,
ROUND(avg_score * avg_views / 1000, 2) as engagement_score
FROM popular_tags
ORDER BY question_count DESC;
技術解説：
-
UNNEST(SPLIT()): カンマ区切りの文字列を配列に変換し、行に展開 -
WITH句（CTE）: 複雑なクエリを読みやすく構造化 - 計算列:
engagement_scoreで独自の指標を算出
レベル3：時系列分析と高度な集計
クエリ4: 時系列での質問数推移
-- 月別の質問数推移（人気言語別）
SELECT
DATE_TRUNC(creation_date, MONTH) as month,
tag,
COUNT(*) as monthly_questions,
AVG(score) as avg_score
FROM `bigquery-public-data.stackoverflow.posts_questions`,
UNNEST(SPLIT(tags, '|')) as tag
WHERE
creation_date BETWEEN '2023-01-01' AND '2023-12-31'
AND tag IN ('python', 'javascript', 'react', 'nodejs')
GROUP BY month, tag
HAVING monthly_questions >= 100 -- ノイズ除去
ORDER BY month DESC, monthly_questions DESC;
クエリ5: ウィンドウ関数を活用した分析
-- ユーザー別の質問活動分析（上位投稿者）
WITH user_activity AS (
SELECT
owner_user_id,
COUNT(*) as total_questions,
AVG(score) as avg_score,
MAX(score) as max_score,
MIN(creation_date) as first_post,
MAX(creation_date) as last_post
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE
creation_date >= '2023-01-01'
AND owner_user_id IS NOT NULL
GROUP BY owner_user_id
HAVING total_questions >= 10
)
SELECT
owner_user_id,
total_questions,
ROUND(avg_score, 2) as avg_score,
max_score,
DATE_DIFF(last_post, first_post, DAY) as active_days,
ROUND(total_questions / NULLIF(DATE_DIFF(last_post, first_post, DAY), 0), 3) as questions_per_day,
ROW_NUMBER() OVER (ORDER BY total_questions DESC) as rank_by_volume,
ROW_NUMBER() OVER (ORDER BY avg_score DESC) as rank_by_quality
FROM user_activity
ORDER BY total_questions DESC
LIMIT 20;
コスト最適化：BigQueryを効率的に使うテクニック
1. クエリ実行前のコスト予測
-- ドライラン（実際の実行なし）でコスト確認
-- クエリエディタで「その他」→「クエリ設定」→「ドライラン」を有効化
2. パーティションテーブルの活用
-- 日付でパーティション化されたテーブルの効率的クエリ
SELECT
COUNT(*) as question_count,
AVG(score) as avg_score
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE
-- パーティション列での絞り込み（コスト削減）
creation_date BETWEEN '2023-06-01' AND '2023-06-30'
AND score > 0;
3. カラムナーストレージの特性を活用
-- 効率的（必要カラムのみ）
SELECT title, score, creation_date
FROM `bigquery-public-data.stackoverflow.posts_questions`
LIMIT 100;
-- 非効率（全カラムスキャン）
SELECT *
FROM `bigquery-public-data.stackoverflow.posts_questions`
LIMIT 100;
コスト削減の実例：
- 全カラム取得: 約380MB処理
- 必要カラムのみ: 約45MB処理
- 約88%のコスト削減
BigQueryの特長的な機能を体験
1. 配列とネストされたデータの処理
-- 複数タグを持つ質問の分析
SELECT
id,
title,
ARRAY_LENGTH(SPLIT(tags, '|')) as tag_count,
tags
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE
creation_date >= '2023-01-01'
AND ARRAY_LENGTH(SPLIT(tags, '|')) >= 5
ORDER BY tag_count DESC
LIMIT 10;
2. 地理空間データの処理
-- IP geolocation データの分析例
-- (注：このクエリは例示目的。実際のデータセット構造に依存)
SELECT
country_name,
COUNT(*) as user_count
FROM `bigquery-public-data.samples.gsod` -- サンプルデータセット
WHERE year = 2020
GROUP BY country_name
ORDER BY user_count DESC
LIMIT 10;
AWS AthenaとBigQueryの実践的比較
実際の使用体験を通じた比較を行いましょう。
| 観点 | AWS Athena | GCP BigQuery |
|---|---|---|
| データソース | S3上の生データ（Parquet、JSON等） | BigQuery専用ストレージ |
| クエリ速度 | データ形式と最適化に依存 | 一貫して高速 |
| データロード | 不要（S3にそのまま配置） | 事前ロードが必要 |
| スキーマ管理 | Glueカタログまたは手動定義 | 自動スキーマ検出 |
| 結果の保存 | S3に手動保存 | 自動的にテーブル保存可能 |
| コスト | $5.00/TB（クエリ実行） | $6.25/TB（データ処理） |
| 適用場面 | データレイクの探索的分析 | 継続的なデータウェアハウス分析 |
実際の選択指針：
Athenaを選ぶべき場合：
- S3に既存のデータレイクがある
- 非構造化データの探索的分析
- 既存AWSインフラとの密な連携
BigQueryを選ぶべき場合：
- 高速で安定したクエリパフォーマンスが必要
- 複雑な分析ワークロードを継続実行
- データの前処理・変換が頻繁
トラブルシューティング：よくあるエラーと対処法
1. 「Resources exceeded」エラー
エラー：Resources exceeded during query execution: Not enough resources for query planning
対処法：
-- データ量を制限する
WHERE creation_date >= CURRENT_DATE() - 30 -- 最近30日のデータのみ
LIMIT 1000 -- 結果行数を制限
2. 「Syntax error」の対処
-- 日付リテラルの正しい書き方
WHERE creation_date >= '2023-01-01' -- ✅ 正しい
WHERE creation_date >= 2023-01-01 -- ❌ エラー
3. JOINパフォーマンスの最適化
-- 効率的なJOIN（小さなテーブルを右側に）
SELECT q.title, a.body
FROM `bigquery-public-data.stackoverflow.posts_questions` q
JOIN `bigquery-public-data.stackoverflow.posts_answers` a
ON q.id = a.parent_id
WHERE q.creation_date >= '2023-01-01' -- 事前フィルタリング
LIMIT 100;
次のステップ：BigQueryの高度な機能
今回のハンズオンで基礎をマスターしたら、以下の高度な機能にもチャレンジしてみてください：
- BigQuery ML：SQLだけで機械学習モデルを構築
- BigQuery GIS：地理空間データの分析
- BigQuery BI Engine：高速ダッシュボード構築
- Data Transfer Service：AWSからの自動データ移行
- BigQuery Omni：AWS S3のデータを直接クエリ
まとめ：BigQueryの実践で得られる価値
今回のハンズオンを通じて、BigQueryの以下の特徴を実体験できたと思います：
技術面でのメリット：
- サーバーレス運用：インフラ管理不要で即座に分析開始
- 自動スケーリング：データ量に関係なく安定したパフォーマンス
- 豊富な分析関数：ウィンドウ関数、配列処理等の高度な機能
コスト面での注意点：
- 処理データ量課金：クエリ最適化の重要性
- 事前コスト予測：ドライラン機能の活用
- パーティション活用：効率的なデータアクセス
AWSエンジニアへの提案：
- データレイク（Athena）とデータウェアハウス（BigQuery）の使い分け
- マルチクラウド戦略でのBigQueryの位置づけ
- GCPエコシステム全体でのデータ活用
次回は、BigQueryの機械学習機能（BigQuery ML）を使って、SQLだけで予測モデルを構築する方法を実践します。AWSのSageMakerとは全く異なるアプローチでのML実装を体験していただけるはずです。
この記事が役に立ったという方は、ぜひ「いいね」や「ストック」をお願いします！
シリーズ記事一覧
- [【1日目】はじめの一歩！AWSエンジニアがGCPで最初にやるべきこと]
- [【2日目】GCPのIAMはAWSとどう違う？「プリンシパル」と「ロール」の理解]
- [【3日目】VPCとVPCネットワーク：GCPのネットワーク設計思想を理解する]
- [【4日目】S3とCloud Storage：オブジェクトストレージを徹底比較]
- [【5日目】RDSとCloud SQL：マネージドデータベースの運用管理の違い]
- [【6日目】EC2とCompute Engine：インスタンスの起動から課金モデルまで]
- [【7日目】1週間のまとめ：AWSとGCP、それぞれの得意なことと設計思想]
- [【8日目】EKSとGKE：Kubernetesのマネージドサービスを比較体験！]
- [【9日目】Dockerイメージをどこに置く？ECRとArtifact Registryを比較]
- [【10日目】LambdaとCloud Functions：イベント駆動型サーバーレスの実装]
- [【11日目】API GatewayとCloud Endpoints：API公開のベストプラクティス]
- [【12日目】Cloud Run：サーバーレスでコンテナを動かすGCPの独自サービスを試してみよう]
- [【13日目】AWS FargateとCloud Run：コンテナ運用モデルの根本的な違い]
- [【14日目】2週間のまとめ：GCPのコンテナ・サーバーレス技術はなぜ優れているのか？]
- [【15日目】RedshiftとBigQuery：データウェアハウスのアーキテクチャと料金体系]
- [【16日目】BigQueryをハンズオン！クエリを書いてデータ分析を体験]（この記事）
- [【17日目】BigQuery ML入門！SQLだけで機械学習モデルを構築しよう]（次回予定）