【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だけで機械学習モデルを構築しよう]（次回予定）