plan-4ging

2026/03/25

backend #bigquery#dwh

BigQuery とは

Google Cloud が提供するフルマネージドのクラウドデータウェアハウス（DWH）
サーバーレスで動作し、大規模データに対して標準 SQL でクエリを実行できる

【RDB（PostgreSQL・MySQL）】
　→ アプリケーションのトランザクション処理（OLTP）が得意
　→ 小〜中規模データをリアルタイムで読み書き

【BigQuery】
　→ 大量データの分析・集計処理（OLAP）が得意
　→ 億件・TB 規模のデータを数秒〜数十秒でクエリ
　→ サーバー管理不要・スケールを意識しなくてよい

主な特徴

サーバーレス

インスタンス、インデックス、チューニングが不要
クエリを投げるだけで動く

列指向ストレージ（Columnar Storage）

必要な列だけ読み込む設計のため集計クエリが高速
コスト管理にも直結する

スケーラビリティ

データ量が巨大になっても速度が落ちない

標準 SQL 対応

既存の SQL 知識がそのまま使える

RDB との違い

アーキテクチャ

RDBBigQuery
アーキテクチャ行指向（Row-oriented）列指向（Columnar）
用途OLTP（トランザクション処理）OLAP（分析・集計処理）
スケール垂直スケール（スペックアップ）自動スケール
インデックス必要（設計が重要）不要（パーティション・クラスタで代替）
操作INSERT / UPDATE / DELETE が得意大量 INSERT・集計が得意
レイテンシミリ秒単位秒〜数十秒単位
コストサーバー固定費スキャン量に応じた従量課金
管理必要不要（フルマネージド）

基本概念

プロジェクト・データセット・テーブルから構成

GCP プロジェクト
　└ データセット（RDB の「データベース」に相当）
　　　└ テーブル
　　　└ ビュー
　　　└ マテリアライズドビュー

# テーブルの完全修飾名
`project_id.dataset_name.table_name`

# 例
`my-project.analytics.events`

操作例

標準 SQL（GoogleSQL） を使う
PostgreSQL・MySQL とほぼ同じ構文で書けるが、独自の関数・構文も存在

-- 集計クエリ
SELECT
  event_name,
  COUNT(*)                              AS event_count,
  COUNT(DISTINCT user_id)               AS unique_users,
  DATE_TRUNC(event_date, MONTH)         AS month
FROM `my-project.analytics.user_events`
WHERE event_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY event_name, month
ORDER BY month DESC, event_count DESC;

-- ウィンドウ関数
SELECT
  user_id,
  event_name,
  event_date,
  -- ユーザーごとの通算イベント数
  COUNT(*) OVER (
    PARTITION BY user_id
    ORDER BY event_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_events,
  -- 直前のイベントとの差分（日数）
  DATE_DIFF(
    event_date,
    LAG(event_date) OVER (PARTITION BY user_id ORDER BY event_date),
    DAY
  ) AS days_since_last_event
FROM `my-project.analytics.user_events`;

BigQuery 独自構文

-- ARRAY_AGG：複数行を配列に集約する
SELECT
  user_id,
  ARRAY_AGG(event_name ORDER BY event_date) AS event_sequence
FROM `my-project.analytics.user_events`
GROUP BY user_id;

-- STRUCT：複数の値を構造体にまとめる
SELECT
  user_id,
  STRUCT(
    COUNT(*)              AS total_events,
    MIN(event_date)       AS first_event_date,
    MAX(event_date)       AS last_event_date
  ) AS user_summary
FROM `my-project.analytics.user_events`
GROUP BY user_id;

-- UNNEST：配列を行に展開する
SELECT
  user_id,
  event
FROM `my-project.analytics.users`,
UNNEST(event_list) AS event;  -- event_list が ARRAY 型の場合

-- WITH 句（CTE）：複雑なクエリを整理する
WITH
daily_events AS (
  SELECT
    event_date,
    event_name,
    COUNT(*) AS cnt
  FROM `my-project.analytics.user_events`
  GROUP BY event_date, event_name
),
top_events AS (
  SELECT
    event_date,
    event_name,
    cnt,
    RANK() OVER (PARTITION BY event_date ORDER BY cnt DESC) AS rank
  FROM daily_events
)
SELECT * FROM top_events WHERE rank <= 3;

-- MERGE（UPSERT）：存在すれば更新・なければ挿入
MERGE `my-project.analytics.users` AS T
USING `my-project.staging.users_update` AS S
ON T.user_id = S.user_id
WHEN MATCHED THEN
  UPDATE SET T.name = S.name, T.updated_at = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN
  INSERT (user_id, name, created_at)
  VALUES (S.user_id, S.name, CURRENT_TIMESTAMP());

コスト管理

クエリ前にスキャン量を確認する

クエリを実行する前に右上の「実行ボタン」横に想定スキャン量が表示される
実行前に必ず確認する習慣をつける

SELECT *は避ける（全列スキャン回避）

-- NG
SELECT * FROM `my-project.analytics.user_events`
WHERE event_date = '2024-01-01';

-- OK：必要な列だけ指定する（スキャン量が大幅に削減）
SELECT user_id, event_name
FROM `my-project.analytics.user_events`
WHERE event_date = '2024-01-01';

クエリのコスト見積もり（bqコマンド）

# bq コマンドで実行前にスキャン量を確認する（--dry_run オプション）
bq query \
  --use_legacy_sql=false \
  --dry_run \
  'SELECT user_id, event_name
   FROM `my-project.analytics.user_events`
   WHERE event_date = "2024-01-01"'

INFORMATION_SCHEMAでクエリ履歴を監視する

-- 過去のクエリ履歴とコストを確認する
SELECT
  job_id,
  user_email,
  query,
  total_bytes_processed,
  ROUND(total_bytes_processed / POW(1024, 4) * 6.25, 4) AS estimated_cost_usd,
  total_slot_ms,
  creation_time,
  end_time,
  TIMESTAMP_DIFF(end_time, creation_time, SECOND)       AS duration_sec
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE
  creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY'
ORDER BY total_bytes_processed DESC
LIMIT 20;

コスト削減のための設定をしておく

カスタムクォータの設定

プロジェクト全体・ユーザーごとに1日のスキャン量の上限を設定
誤って巨大なクエリを実行してもコストが青天井にならない

最大請求バイト数の設定

bqコマンドで最大スキャン量を指定する

bq query \
  --maximum_bytes_billed=1073741824 \  # 1GB 以上スキャンしようとするとエラー
  --use_legacy_sql=false \
  'SELECT ...'

データ取り込み

取り込み方法の比較

方法遅延用途
バッチ読み込み（GCS）数分〜定期的な大量データ取り込み
ストリーミングインサート数秒リアルタイム・少量データ
Storage Write API数秒リアルタイム・大量データ
BigQuery Data Transferスケジュール設定他 GCP サービス・SaaS からの定期転送

活用事例

① アプリのログ・イベント分析
　アプリの行動ログ・購買ログを BigQuery に貯めて分析
　→ Pub/Sub → Dataflow → BigQuery のパイプラインなど

② KPI ダッシュボード
　Looker Studio・Tableau・Metabase と接続して
　売上・DAU・コンバージョン率などを可視化

③ データ基盤（DWH）の中核
　複数のマイクロサービスの DB を BigQuery に集約
　→ サービスをまたいだ横断的な分析が可能になる

④ 機械学習データの準備（特徴量エンジニアリング）
　BigQuery ML で BigQuery 上で直接モデルを学習・推論

⑤ 定期バッチ集計
　Cloud Scheduler + Cloud Functions で毎日集計を実行
　→ 集計結果を RDB に書き戻してアプリから参照する

RDB と BigQuery の役割分担

アプリケーション
　↓ トランザクション
PostgreSQL（RDB）
　↓ CDC（Debezium）または定期エクスポート
BigQuery（DWH）
　↓ SQL 分析
Looker Studio（ダッシュボード）

→ アプリの読み書き：PostgreSQL
→ 分析・集計：BigQuery
→ 役割を分離することで本番 DB に分析クエリの負荷をかけない

注意点

UPDATE/DELETEが遅い

BigQuery は更新に向いていない設計

ストリーミングインサート直後はクエリで見えないことがある

数秒のバッファがある
※ 厳密なリアルタイム整合性が必要な処理には向かない

RDB なしでの運用

下記理由から非推奨

トランザクション・整合性の問題

  • BigQuery はトランザクション処理（ACID）が RDB と比べて非常に弱く、複数テーブルをまたぐ整合性保証が難しい
  • 在庫・残高・予約など「同時に複数のリクエストが同じデータを更新する」処理は BigQuery には向かない
  • INSERT / UPDATE / DELETE の同時実行制御が RDB のように機能しない

パフォーマンスの問題

  • 単一レコードの取得（WHERE id = 1 のような点検索）が RDB と比べて著しく遅い（数秒〜数十秒かかる）
  • アプリケーションの API レスポンスに BigQuery を直接使うとタイムアウト・UX の悪化が発生する
  • 低レイテンシ（ミリ秒単位）が必要なリアルタイム処理には根本的に向いていない

コストの問題

  • アプリからの頻繁なクエリ（毎リクエストで SELECT する用途）はスキャン量が積み上がり RDB より高額になるケースがある

設計・運用の問題

  • 頻繁な UPDATE が必要なデータ（ユーザー情報・商品情報など）の管理が難しい
  • ORM が BigQuery をサポートしていないケースがあり、アプリ開発の生産性が下がる

参考

Back