[bigquery]繰り返し処理(LOOP)でパーティションテーブルを一気に作成する方法

bigqueryのコンソール上のみでパーティションテーブルを作成して、日付ループ処理を記述することで、過去分のパーティション情報などを一気に作成するコードの紹介をします。

本番環境などでは、pythonなどのプログラミングを使って、sqlをforループさせて過去パーティション情報を作成することが多いと思うのですが、テスト環境＆bigqueryコンソール上のみでこの作業をやる方法がないかを探してみたのですが、どこにも前例がなさそうでしたので、サンプルコードを書いてみました。

-- ループ項目の定義
DECLARE from_date DATE;
-- 変数に値を設定
SET from_date = DATE('2022-01-01'); -- 処理を開始する日付を指定する


-- パーティションテーブルの項目変更などを行う場合は、一旦DROPする
-- DROP TABLE IF EXISTS dataset.table;

--　パーティション付きのテーブルの箱を事前に作成
CREATE OR REPLACE TABLE dataset.table
(
  target_date	DATE
)
PARTITION BY _PARTITIONDATE
OPTIONS(require_partition_filter=true);


-- 処理をLOOPさせることで、一定期間のレコードを一気にINSERTさせる
LOOP

　　　　　　　　--　LOOPを抜ける条件を設定（この例では処理日前日まで繰り返す）
    IF from_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) 
        THEN
            LEAVE;

    ELSE

    -- ↓繰り返し処理の記述（以下ではfrom_dateをパーティションとtarget_dateという項目に設定しているだけ）

        INSERT INTO dataset.table
        (
            _PARTITIONTIME,
            target_date
        )
        SELECT 
            CAST(from_date AS TIMESTAMP) AS _PARTITIONDATE -- パーティションの日付設定
            , from_date AS target_date
        ;

    -- ↑繰り返し処理の記述

    END IF;

    -- 繰り返し作成する期間を指定
    SET from_date = DATE_ADD(from_date, INTERVAL 1 DAY); 

END LOOP;

上記の繰り返し処理の記述の部分を任意のコードに変更すれば使えると思います。

※前提として、CREATEやINSERTなどのDML権限が必要になる為、その辺りの権限が付与されてない場合は、分析基盤を管理している担当に相談が必要です。