BigQueryのテーブルスナップショットとは
スナップショットとタイムトラベルの違い
-
BigQueryのテーブルスナップショット
特定の時点でのテーブル（ベーステーブル）の内容が保持される機能。- 有効期限が設定でき、スナップショットを作成してから特定の期間が過ぎたら削除される。
という設定もできる。 - 読み取り専用なので、スナップショットのテーブルデータをDMLで変更または、スキーマをDDLで変更しようとするとエラーが発生する。
- 有効期限が設定でき、スナップショットを作成してから特定の期間が過ぎたら削除される。
-
BigQueryのタイムトラベル
BigQueryで変更または、削除されたテーブルにアクセスすることができる。
過去7日間以内の任意の時点のデータにアクセスできる。
スナップショットのメリット
- 7日間を超えてデータの状態を維持することができる。
- ストレージ費用を最小減に抑えることができる。
スナップショットのストレージ費用は、
ベーステーブルに存在しなくなったデータや、
ベーステーブルで変更されたスナップショットに対してのみ課金されるため、
ベーステーブルをコピーして保持するよりも、スナップショットで保持しておいた方がストレージ費用がすくなくなる。
--https://cloud.google.com/bigquery/docs/table-snapshots-intro?hl=ja#storage_costs
※ストレージ費用のイメージ
実際に業務でどういった時に使用している？
基本的に、毎日前日分の各マスタ系テーブルをスナップショットでバックアップを取るようにしている。
また、各マスタのスナップショットをワークフローで組んで、
決まったスケジュールでスナップショットを一気に作成するように実装している。
実際に使用しているクエリ
drop snapshot table if exists -- 前日分のスナップショットテーブルがもしあれば削除する。（重複しないようにしている）
`スナップショットテーブル名$yesterday_%Y%m%d$`
;
create snapshot table -- 前日分のスナップショットテーブルを作成する。
`スナップショットテーブル名$yesterday_%Y%m%d$`
clone -- どのベーステーブルをクローン（コピー）して、スナップショットするか選択する。
`ベーステーブル名`
for
system_time as of parse_timestamp('%Y-%m-%d %H:%M:%S', '$now$', 'Asia/Tokyo')
;
■上記で使用している変数(ETLツールで使用している変数）
-
$yesterday_%Y%m%d$・・・1日前の日付を指す変数 -
$now$・・・現在の時間を指す変数
■上記のクエリの補足
-
for system_time as of 時間で、指定された時点で最新だったテーブル定義と行を返す。
参照：FOR SYSTEM_TIME AS OFについて -
parse_timestampで、タイムスタンプの文字列表現をTIMESTAMPオブジェクトに変更できる。
参照：PARSE_TIMESTAMPについて
おまけ
スナップショット機能がリリースされる以前のバックアップの取り方
BigQueryにスナップショット機能が実装される前のクエリは、下記のようにしていた。
※record_dateでパーティショニングすることで、特定の日のバックアップを取得するためのクエリリソースの消費を抑えている。
begin transaction;
/* 日付関連UDF */
create temp function today() as (
date('$today_%Y-%m-%d$')
);
/* 初回・全量洗い替え用、クラスタリングのみ実施 */
-- create or replace table
-- `バックアップを保存する用のテーブル名`
-- partition by
-- record_date
/* TEMPテーブルを生成 */
create temp table 一時テーブル名 as
select
*
from
`バックアップをとりたいテーブル名`
where
1=1
qualify
row_number() over(partition by id order by updated_at desc) = 1 -- id毎に同じ更新データが無いか重複チェック
/* 最後にTEMPテーブルの内容をINSERT */
insert `バックアップを保存する用のテーブル名`
select
*
from
一時テーブル名
where
1=1
and record_date = today() --前日差分取得
;
/* TEMPテーブルを削除しておく */
drop table 一時テーブル名;
commit transaction;
業務内で実際に行ったスナップショットの活用事例
BigQueryでは1つのテーブルのパーティションの上限が4000となっている。
ある日、定期実行しているワークフロー内のテーブルのパーティション数が4000以上となり、
エラーが発生してしまったのだが、そこでスナップショットを活用することができた。
パーティション数の上限が超えてしまうことに対して行った対策として、
パーティションを削除して、クラスタリングを実装するということを行った。
1)既存テーブルをDROPする
パーティショニングの上限に到達してしまったため、既存のテーブルを削除した。
2)スナップショットからクラスタリングを実装したテーブルを作成する
パーティションやクラスタリングが実装されていないテーブルをスナップショットとして保持していたので、そのテーブルにクラスタリングを実装したテーブルを再作成する。
create or replace table
`再作成したいテーブル(パーティション数の上限に到達してしまっていたテーブル)`
cluster by
date -- クラスタリングの基準にしたいカラム
as
select
*
from
`スナップショットテーブル`
3)ワークフローを再実行する
上記のように、スナップショットを活用して、クラスタリングを実装したテーブルを再作成したら、
もう一度ワークフローを実行して、最新のデータを追加する。
以上の手順でスナップショットを活用して、ワークフローの保守運用に活かすことができた。