【Snowflake】タイムトラベル機能でバックアップリカバリの実践

はじめに

最近Snowflakeのタイムトラベル機能に救われたので感謝の意味で記事を書きます。
この機能があるおかげでデータメンテナンスでやらかしてしまった場合も簡単にリカバリができ、バックアップリカバリの手間が省けるだけでなく運用における精神的負担が減っていると感じます。

当記事で分かるようになること

・Snowflakeタイムトラベル機能の概要
・タイムトラベル機能を使った過去データの参照とリストア方法
・タイムトラベル可能期間の設定方法

タイムトラベル機能の概要

・DBやスキーマ、テーブルの特定時点断面を参照・リストア可能
・デフォルトでは1日、Enterprise以上では設定変更により90日までタイムトラベル可能

例えば以下のようなケースで使用することができます。（実際のSQLも後述します。）
　ケース１．Aテーブルをお昼の時点にリストアしたい
　ケース２．Bテーブルの昨日と今日のデータの差分を見たい

通常のDBであればDBAにお願いしてバックアップからリストアしてトランザクションログを使ってロールフォワードして・・という作業なしで、利用者がいつでもSQLで一瞬で過去のデータを参照できます。とても便利です。

技術的背景（注意：半分ポエムです。読み飛ばしてください）

SnowflakeはCloud技術をとても上手に利用したデータ基盤だと思っています。
Snowflakeのデータは裏側ではAWSの場合はAmazon S3に保管されています。
S3にはオブジェクトロック機能があり事前定義されたリテンション期日まではイミュータブル（オブジェクトのバージョンの削除を防止。追加のみ可能）に管理することができます。
Snowflakeのテーブルデータなども同様に設定した期間内はバージョンを保持してくれており、タイムトラベル機能によりある特定時点断面のデータを覗き見ることができます。
でも更新するたびにバージョン残してたらストレージの消費がすごいことになるのでは？とも思うところですが、変更の都度テーブル全体が保持されるわけではありません。Snowflakeのデータは約16MBのマイクロパーティションという単位でバージョン管理されており、データ更新時には該当する行が格納されているマイクロパーティションが更新・バージョン管理されるため、ストレージの増分も変更されたマイクロパーティションのバージョン分だけです。あらかじめ設定された期間が終了すると古いバージョン分のDISKは開放されていきます。

（準備）実践のためのデータ作成

タイムトラベルを行うためのテーブルを準備します。

-- テスト用テーブル作成
CREATE OR REPLACE TABLE TABLE_A (
    col1 int,
    col2 timestamp default current_timestamp()
);
-- 適当に3件データ投入
INSERT INTO TABLE_A (col1) VALUES (1);
INSERT INTO TABLE_A (col1) VALUES (2);
INSERT INTO TABLE_A (col1) VALUES (3);
-- 確認
SELECT * FROM TABLE_A;

上記を実行すると以下のような状態になります。

実践 ケース１．Aテーブルを特定時点の状態にリストアしたい

まずはうっかりデータ削除をしてみます。

DELETE FROM TABLE_A;

この状態でSELECTしても何も結果は返ってきません（0件であることがわかります。）

ここからタイムトラベル機能を使ってDELETE前のデータを確認します。

ケース１－１．特定のSQL直前の状態を確認する

DELETE FROM TABLE_AのQuery IDを特定し、その直前状態を確認します

-- Query IDの特定
SET query_id = 
(SELECT query_id FROM 
TABLE(information_schema.query_history_by_session(result_limit=>50)) 
WHERE query_text LIKE 'DELETE FROM TABLE_A%' ORDER BY start_time LIMIT 1);
-- 確認したQueryID以前のデータを確認
SELECT * FROM TABLE_A BEFORE (statement => $query_id);

DELETE前のデータが確認できます。

ケース１－２．特定時刻の状態を確認する

ケース１－１のようにQuery IDを特定しなくても戻す時間がわかる場合は時間指定も可能です。

-- 特定時刻のテーブルを参照する
SELECT * FROM TABLE_A AT(TIMESTAMP => '2022-11-26 17:09'::timestamp_tz);

同様にDELETE前のデータが確認できます、

ケース１－３．特定時刻の状態に戻す

テーブルを戻すには以下の3ステップを行います。
1-3-1.テーブルの特定時点の状態からcloneを作成する（TABLE_A_BAK）
1-3-2.cloneで作成したテーブルと戻したいテーブルを入れ替え
1-3-3.不要なテーブルの削除

それでは実践していきます。

1-3-1.テーブルの特定時点の状態からcloneを作成する（TABLE_A_BAK）

-- TABLE_Aの戻したい時点からTABLE_A_BAKをclone作成
CREATE OR REPLACE TABLE TABLE_A_BAK CLONE TABLE_A AT(TIMESTAMP => '2022-11-26 17:09'::timestamp_tz);
-- 期待通りのテーブルができたことを確認
SELECT * FROM TABLE_A_BAK;

タイムトラベルしてDELETE前のテーブルが作成できた

1-3-2.cloneで作成したテーブルと戻したいテーブルを入れ替え

現時点での両テーブルの件数を確認すると以下の通りです。

SELECT 'TABLE_A' TABLE_NAME,COUNT(1) FROM TABLE_A
UNION ALL
SELECT 'TABLE_A_BAK' TABLE_NAME,COUNT(1) FROM TABLE_A_BAK;

TABLE_A_BAKとTABLE_Aの入れ替え（SWAP）を実施します

-- TABLE_A_BAKとTABLE_Aの入れ替え
ALTER TABLE TABLE_A_BAK SWAP WITH TABLE_A;

再度両テーブルの件数を確認すると入れ替わっていることがわかります。

注意！
cloneで作成したテーブルはそれ以前の履歴を保持しているわけではないので
そこからさらにタイムトラベルで戻る、ということはできません。
必要な場合は次のテーブル削除は必要な保持期間が過ぎてから行いましょう。

1-3-3.不要なテーブルの削除

不要となったTABLE_A_BAKはDROPしておきましょう

DROP TABLE TABLE_A_BAK;

実践 ケース２．Bテーブルの昨日と今日のデータの差分を見たい

先ほどのテーブルに3行足します。

INSERT INTO TABLE_A (col1) VALUES (4);
INSERT INTO TABLE_A (col1) VALUES (5);
INSERT INTO TABLE_A (col1) VALUES (6);
SELECT * FROM TABLE_A;

特定時点との差分を検出します。

SELECT * 
  FROM TABLE_A A_NOW
 WHERE NOT EXISTS( -- 17:50時点で存在する行を除外
       SELECT 1 
         FROM TABLE_A AT(TIMESTAMP => '2022-11-26 17:50'::timestamp_tz) A_OLD
        WHERE A_NOW.COL1 = A_OLD.COL1
 );

差分の行のみが検出されました。
このように１SQLで現在と過去のデータを簡単に比較できるのが素晴らしいところです。

設定について

・デフォルトではタイムトラベル可能な日数は1日
・Enterprise以上の場合、90日まで延長することが可能
・設定はアカウント単位やDB、スキーマ、テーブル作成時に履歴保持期間を指定可能
・上位レベルでの設定は開レベルのデフォルト値となり、下位レベルで設定した場合はそちらが優先される
（アカウントで30日にしてもテーブル作成時に14日に指定した場合は14日となる）

アカウント単位での設定、確認方法

-- タイムトラベル可能期間を32日に変更
ALTER ACCOUNT SET DATA_RETENTION_TIME_IN_DAYS = 32;
-- アカウントパラメータの確認
SHOW PARAMETERS LIKE 'DATA_RETENTION_TIME_IN_DAYS' IN ACCOUNT;

DB単位での設定、確認方法

-- 設定
ALTER DATABASE TESTDB SET DATA_RETENTION_TIME_IN_DAYS=90;
-- 確認
SELECT DATABASE_NAME,RETENTION_TIME 
  FROM INFORMATION_SCHEMA.DATABASES 
 WHERE DATABASE_NAME = 'TESTDB';

SCHEMA単位での設定、確認方法

-- 設定
ALTER SCHEMA TEST_SCHEMA SET DATA_RETENTION_TIME_IN_DAYS=60;
-- 確認
SELECT SCHEMA_NAME,RETENTION_TIME 
  FROM INFORMATION_SCHEMA.SCHEMATA 
 WHERE SCHEMA_NAME = 'TEST_SCHEMA';

TABLE単位での設定、確認方法

 -- 設定
ALTER TABLE TABLE_A SET DATA_RETENTION_TIME_IN_DAYS=14;
-- 確認
SELECT TABLE_NAME,RETENTION_TIME 
  FROM INFORMATION_SCHEMA.TABLES 
 WHERE TABLE_NAME = 'TABLE_A';

おまけ

誤ってdropしたDBやテーブルはタイムトラベル機能だけでは戻せないのですがundropを使用することで元に戻すことができます。

DROP TABLE TABLE_A;   -- TABLE_AをDROP
UNDROP TABLE TABLE_A; -- TABLE_AをUNDROPで戻す
SELECT * FROM TABLE_A;
-- 何事もなかったかのようにTABLE_Aのデータが表示される

おわりに

上でさらっと使いましたがCLONEやSWAPも他のDBにはない便利機能です。（CLONEは特に）
Snowflakeの便利機能は多いのでまた紹介していきたいと思います。

参考URL（Snowflake公式ドキュメント）

https://docs.snowflake.com/ja/user-guide/data-time-travel.html
https://docs.snowflake.com/ja/sql-reference/sql/alter-table.html