2025.04.08 コラム
【テックコラム】BigQuery テーブルスナップショットをデータセット単位で設定する方法
はじめに
こんにちは！DataCurrent の奥村です。
BigQuery を使っていると、データのバックアップや履歴管理が必要になることがあるかと思います。そんなときに便利なのが「テーブルスナップショット」です。（テーブルスナップショットに関してはこちらのコラムもご参考ください）本記事では、スケジュールクエリだけでデータセット単位のスナップショットを設定する方法をご紹介します。
背景
BigQuery の公式機能では、テーブルごとにスナップショットを設定する必要があります。バックアップを取りたいテーブルが複数ある場合、とても手間がかかるのが課題です。一応 Google Cloud のドキュメントにデータセット毎にテーブルスナップショットを作成する方法が公開されていますが、 Cloud Scheduler や Pub/Sub などを組み合わせた比較的大規模な構成が必要になります。今回はもっとシンプルに構築する方法をご紹介します。
スナップショットの作成手順
まず、通常のテーブルスナップショットの作成方法に関して簡単に説明します。
方法としては「コンソール」「SQL」「bq コマンド」「API」などがあります。
例えば「SQL」「bq コマンド」は以下のようになります。
SQL:
CREATE SNAPSHOT TABLE `SNAPSHOT_PROJECT_ID.SNAPSHOT_DATASET_NAME.SNAPSHOT_NAME` CLONE `TABLE_PROJECT_ID.TABLE_DATASET_NAME.TABLE_NAME` OPTIONS (expiration_timestamp = TIMESTAMP 'TIMESTAMP_VALUE');
bq コマンド:
bq cp --snapshot --no_clobber --expiration=86400 \ TABLE_PROJECT_ID:TABLE_DATASET_NAME.TABLE_NAME \ SNAPSHOT_PROJECT_ID:SNAPSHOT_DATASET_NAME.SNAPSHOT_NAME
データセット単位のテーブルスナップショットの作成手順
通常データセット毎にスナップショットを作成する場合の流れは以下のようになるかと思います。
- 対象データセットのテーブル一覧を取得する
- 一覧のテーブルに対してスナップショットを作成する SQL 作成する
- 作成した SQL をスケジュールクエリに設定する
今回のポイントは1と2の手順を INFORMATION_SCHEMA を使用し、頑張って1つの SQL で実装することです。これにより1つのスケジュールクエリを設定するだけでデータセットにある複数のテーブルのスナップショットをまとめて作成することができます。
今回作成するデータセット毎のスナップショットの作成は下記のような要件を例として作成していきたいと思います。
- 日次作成：毎日スナップショットを自動作成
- 対象テーブル：データセットにあるビュー以外の全テーブルが対象（データセット「 test」）
- 保存先：バックアップ用データセットにすべてのスナップショットを作成（データセット「 snapshot」）
- 一意の命名：元テーブル名＋作成日（例: test_table_20250321）
- 保持期限：30日後に自動削除
サンプルSQL:
-- バックアップ元と先のデータセットを定義
DECLARE source_dataset_name STRING DEFAULT 'test';
DECLARE dest_dataset_name STRING DEFAULT 'snapshot';
-- スナップショットの有効期限：作成日から30日後
DECLARE expiration_date DATE DEFAULT DATE_ADD(CURRENT_DATE(), INTERVAL 30 DAY);
-- 当日の年月日を文字列（YYYYMMDD）形式で取得
DECLARE today STRING DEFAULT FORMAT_DATE('%Y%m%d', CURRENT_DATE());
-- 対象データセット内の全ベーステーブル一覧を格納する配列
DECLARE table_list ARRAY<STRING>;
DECLARE query STRING;
-- INFORMATION_SCHEMA を使用して全てのベーステーブル名を配列に取得
SET query = FORMAT(
"SELECT ARRAY_AGG(table_name) FROM `%s`.INFORMATION_SCHEMA.TABLES WHERE table_type = 'BASE TABLE'",
source_dataset_name
);
EXECUTE IMMEDIATE query INTO table_list;
-- 各テーブルごとにスナップショットを作成
FOR rec IN (SELECT table_name FROM UNNEST(table_list) AS table_name) DO
EXECUTE IMMEDIATE FORMAT(
"CREATE SNAPSHOT TABLE `%s.%s_%s` CLONE `%s.%s` OPTIONS (expiration_timestamp = TIMESTAMP '%s')",
dest_dataset_name, rec.table_name, today,
source_dataset_name, rec.table_name,
FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', expiration_date)
);
END FOR;
上記のような SQL を日時で実行するようにスケジュールクエリに設定するだけです。
まとめ
通常、大規模な構成が必要になる BigQuery のデータセット毎のテーブルスナップショットを SQL を工夫することで簡単に実装できました。この方法を活用することで、データのバックアップなどが楽になれば幸いです。ぜひ試してみてください！
最後に
自社に専門人材がいない、リソースが足りない等の課題をお持ちの方に、エンジニア領域の支援サービス（Data Engineer Hub）をご提供しています。 お困りごとございましたら是非お気軽にご相談ください。
本記事に関するお問い合わせは下記にて承ります。
株式会社DataCurrent
info@datacurrent.co.jp