Google BigQueryによる大規模データ分析基盤の構築ガイド

COLUMN コラム

2025年03月14日
Google BigQueryによる大規模データ分析基盤の構築ガイド
- ＩＴ系

なぜBigQueryが大規模データ分析の標準となったのか

Google BigQueryは、ペタバイト規模のデータを数秒から数十秒で分析できるサーバーレスデータウェアハウスだ。従来のオンプレミスのデータウェアハウスやHadoopベースのシステムと比較して、インフラ管理が不要で、使った分だけ課金されるという特徴があります。

筆者が以前関わったプロジェクトでは、Redshift上で30分以上かかっていたクエリがBigQueryでは15秒で完了した。もちろん単純比較はできないが、BigQueryの列指向ストレージとDremelエンジンの威力を実感した瞬間だった。本記事では、BigQueryを用いた分析基盤の構築方法を、設計から運用最適化まで一貫して解説します。

データセットとテーブル設計の基本

データセットの設計方針

BigQueryにおけるデータセットは、テーブルやビューをまとめる論理的なコンテナだ。環境別やドメイン別にデータセットを分割するのが一般的なプラクティスです。

-- データセットの作成（ロケーション指定が重要）
CREATE SCHEMA IF NOT EXISTS `my-project.analytics_production`
OPTIONS(
  location = 'asia-northeast1',
  description = '本番環境の分析用データセット',
  default_table_expiration_days = NULL,
  labels = [('env', 'production'), ('team', 'data')]
);

CREATE SCHEMA IF NOT EXISTS `my-project.analytics_staging`
OPTIONS(
  location = 'asia-northeast1',
  description = 'ステージング環境の分析用データセット',
  default_table_expiration_days = 30
);

パーティションとクラスタリング

BigQueryのコスト最適化で最も重要なのが、パーティションとクラスタリングの適切な設定だ。これを怠ると、全テーブルスキャンが頻発し、コストが予想外に膨らむことになります。

-- パーティション＋クラスタリングテーブルの作成
CREATE OR REPLACE TABLE `my-project.analytics_production.user_events`
PARTITION BY DATE(event_timestamp)
CLUSTER BY user_id, event_type
AS (
  SELECT
    event_id,
    user_id,
    event_type,
    event_timestamp,
    event_properties,
    session_id
  FROM `my-project.raw_data.events`
);

-- パーティションフィルタの必須化（コスト防止策）
ALTER TABLE `my-project.analytics_production.user_events`
SET OPTIONS (
  require_partition_filter = true
);

require_partition_filter = trueの設定は非常に重要です。これにより、パーティションフィルタのないクエリは実行時にエラーとなり、意図しないフルスキャンを防止できます。

ETLパイプラインの構築

スケジュールクエリによるデータ変換

BigQueryにはスケジュールクエリ機能が組み込まれており、軽量なETL処理であれば外部ツールなしで実現できます。

-- 日次集計テーブルの更新（スケジュールクエリとして登録）
CREATE OR REPLACE TABLE `my-project.analytics_production.daily_metrics`
PARTITION BY metric_date
AS
WITH daily_stats AS (
  SELECT
    DATE(event_timestamp) AS metric_date,
    COUNT(DISTINCT user_id) AS dau,
    COUNT(*) AS total_events,
    COUNTIF(event_type = 'purchase') AS purchase_count,
    SUM(IF(event_type = 'purchase',
      CAST(JSON_VALUE(event_properties, '$.amount') AS FLOAT64), 0)
    ) AS total_revenue
  FROM `my-project.analytics_production.user_events`
  WHERE DATE(event_timestamp) = CURRENT_DATE() - 1
  GROUP BY metric_date
)
SELECT * FROM daily_stats;

外部データソースとの連携

BigQueryはCloud StorageのファイルをフェデレーテッドクエリやBigQuery Data Transfer Serviceを通じて直接参照できます。CSVやParquetファイルを事前にロードすることなく分析できるのは大きな利点だ。

-- Cloud Storage上のParquetファイルを外部テーブルとして参照
CREATE OR REPLACE EXTERNAL TABLE `my-project.raw_data.external_logs`
WITH PARTITION COLUMNS (
  dt DATE
)
OPTIONS (
  format = 'PARQUET',
  uris = ['gs://my-bucket/logs/*'],
  hive_partition_uri_prefix = 'gs://my-bucket/logs/'
);

コスト管理とモニタリング

BigQueryは従量課金モデルのため、クエリのスキャン量に応じてコストが発生します。以下の施策でコストを管理しましょう。

カスタムコスト管理：プロジェクト単位やユーザー単位でクエリの日次上限を設定する

INFORMATION_SCHEMAの活用：過去のクエリ実行履歴からコストの高いクエリを特定する

マテリアライズドビュー：頻繁に実行される集計クエリを事前計算しておく

BI Engineの活用：ダッシュボード用の高頻度クエリをメモリ内キャッシュで高速化する

-- 過去7日間のコストが高いクエリTop10を特定
SELECT
  user_email,
  query,
  total_bytes_processed / POW(1024, 4) AS tb_processed,
  total_bytes_processed / POW(1024, 4) * 6.0 AS estimated_cost_usd,
  creation_time
FROM `region-asia-northeast1`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND statement_type != 'SCRIPT'
  AND total_bytes_processed > 0
ORDER BY total_bytes_processed DESC
LIMIT 10;

まとめ：BigQuery運用の勘所

BigQueryによるデータ分析基盤の構築において最も重要なのは、設計段階でのパーティション戦略とコスト管理の仕組みづくりだ。スモールスタートで始めて、データ量の増加に合わせて最適化を進めていくのが現実的なアプローチです。特にパーティションフィルタの強制とINFORMATION_SCHEMAによるクエリ監視は、初日から実施すべき必須施策だ。