【BigQuery】過去データの再作成が超絶楽になる！ループ処理でシャーディングテーブルを一気に作成する方法

Advent Calendar 2025

こんにちは、エニグモの嘉松です。普段はデータ活用推進室にて、データ分析・データ活用の推進やMAツールを用いたCRM施策などを担当しています。

本記事はEnigmo Advent Calendar 2025の最終日（25日目）の記事です。1ヶ月間にわたり様々なテーマで繋いできたバトンも、いよいよ今回が最終回となります！

最終回は、データ分析・データ活用の裏側を支える技術にフォーカスし、BigQueryに関する（少しディープな）知見を共有します。

時点データとは？

データ分析において、現時点のデータだけでなく「過去のある時点」のデータを保持しておくことは極めて重要です。例えば、ユーザーの注文回数、会員ランク、保有ポイント数、メール購読の有無などが挙げられます。

これらの時点データを毎月1日などのタイミングでスナップショットとして蓄積しておくことで、「過去と現在の比較」や「特定の期間における推移」といった分析が容易になり、分析の幅は劇的に広がります。

しかし、過去に遡ってこれらのデータを作成しようとすると、なかなかの手間が発生します。例えば月次データを5年分作成する場合では60回のクエリ実行が必要となります。

そこで今回は、BigQueryの手続き型言語（Procedural language）を使い、ループ処理で過去分のシャーディングテーブルを一気に作成する方法をご紹介します。

BigQueryのシャーディングテーブルとは？

table_YYYYMMDD という命名規則に基づき、物理的にテーブルを分割して管理する手法です。 例えば、user_summary_20251201 のようにテーブル名の末尾に日付を付与します。

シャーディングを行うことで、必要な期間のデータだけをスキャン対象にできるため、処理に必要なデータ量およびクエリ費用を大幅に抑えることが可能です。

シャーディングテーブル作成の処理フロー

今回の処理の流れは以下の通りです。

  1. 指定した「開始年月」から「現在」まで、1ヶ月ごとにループさせる。
  2. 各月ごとに集計クエリを実行し、table_YYYYMMDD 形式のテーブルを作成（または置換）する。
  3. 処理対象が現在を超えたらループを終了する。
START_MONTH (2022-01-01)
    ↓
[ LOOP開始 ]
    ↓
1回目: 対象 2022-01-01 → CREATE TABLE dataset.table_20220101
2回目: 対象 2022-02-01 → CREATE TABLE dataset.table_20220201
    ...
終了: 対象が「今月」を超えたら LEAVE

サンプルコード

以下は、ループ処理を用いて過去テーブルを作成するスクリプトです。

-- 1. 変数の宣言と初期化
DECLARE START_MONTH DATE DEFAULT DATE '2022-01-01'; -- 開始日を指定
DECLARE CURRENT_MONTH DATE;
DECLARE yyyymmdd STRING;
DECLARE LOOP_CNT INT64 DEFAULT 0;

-- 2. ループ処理の開始
LOOP
  -- 処理対象年月をセット（開始月からLOOP_CNT分だけ月を加算）
  SET CURRENT_MONTH = DATE_ADD(START_MONTH, INTERVAL LOOP_CNT MONTH);

  -- 3. 終了判定：処理対象年月が「今月」を超えたらループを抜ける
  IF CURRENT_MONTH > DATE_TRUNC(CURRENT_DATE('Asia/Tokyo'), MONTH) THEN
    LEAVE;
  END IF;

  -- テーブル接尾辞用にYYYYMMDD形式の文字列を作成
  SET yyyymmdd = FORMAT_DATE("%Y%m%d", CURRENT_MONTH);
  
  -- 4. 動的SQLの生成と実行
  -- EXECUTE IMMEDIATE FORMAT() でSQLを動的に組み立てて実行します
  EXECUTE IMMEDIATE FORMAT("""
    -- ここに実行したいDDL（テーブル作成）を記述
    CREATE OR REPLACE TABLE `your-project.your_dataset.user_summary_%s` AS
    SELECT
      user_id,
      -- 注文回数
      count(*) as purchase_count
    FROM
      `your-project.source_dataset.transactions`
    WHERE
      -- 基準日（CURRENT_MONTH）以前の注文データに絞り込み
      DATE(created_at, 'Asia/Tokyo') < '%s'
    GROUP BY
      1
  """, yyyymmdd, CAST(CURRENT_MONTH AS STRING));

  -- 5. カウンタを進める
  SET LOOP_CNT = LOOP_CNT + 1;

END LOOP;

サンプルコードの解説

実装のポイントは以下の3点です。

1. LOOP と LEAVE による制御

BigQueryの手続き型言語には FOR 文もありますが、日付を柔軟に加算しながら処理したい場合は LOOP が適しています。無限ループを防ぐため、必ず IF ... THEN LEAVE; END IF; による脱出条件を記述しましょう。今回は DATE_TRUNC を使い、実行時の年月を超えた時点で停止するように設定しています。

2. EXECUTE IMMEDIATE による動的SQLの実行

通常のSQL文には変数を直接埋め込むことができない箇所（テーブル名など）があります。そのため、クエリ全体を文字列として組み立てて実行する EXECUTE IMMEDIATE を使用します。 FORMAT() 関数を用いると、%s を使って変数値を流し込めるため、文字列結合（||）を繰り返すよりも可読性が高く、メンテナンスもしやすくなります。

3. 文字列のクォート扱いに注意

ここが最も重要なポイントです。動的SQLの中で日付をリテラルとして扱いたい場合、%s の周りをシングルクォートで囲む必要があります。

  • NG: DATE(created_at, 'Asia/Tokyo') < %s

  • 展開後: ... < 2022-01-01 （数値の引き算として処理されてしまう）

  • OK: DATE(created_at, 'Asia/Tokyo') < '%s'

  • 展開後: ... < '2022-01-01' （正しい日付文字列として認識される）

ループ処理活用のススメ

今回はシャーディングテーブルの作成を例に挙げましたが、このループ処理のテクニックは「API制限を回避するために1日ずつ処理する」「リソース枯渇を避けるために重たいクエリを分割実行する」といったシーンでも非常に有効です。

手作業による「温かみのある運用」から卒業し、スマートで快適なデータ基盤ライフを送りましょう！

25日間の感謝を込めて

これにて Enigmo Advent Calendar 2025 は全25記事のバトンが繋がり、無事完走となります！

今年は様々な職域のメンバーが、それぞれの視点から技術や知見を共有してくれました。これらの記事が、皆様の日々の業務や課題解決のヒントとなれば望外の喜びです。

来たる2026年も、エニグモはBUYMAをはじめとするサービスを通じて新しい価値を創造してまいります。どうぞよろしくお願いいたします。

株式会社エニグモ すべての求人一覧 hrmos.co