こんにちは。ozawaです。
Snowflakeにはタイムトラベル機能と呼ばれる、あるテーブルの過去の状態を呼び出すサービスがあります。
これを行うことで、直前に間違った処理をしてしまっても、タイムトラベル機能を使うことで、ある時点の正しい状態のテーブルに戻すことができます。
ただし、タイムトラベル句を直感のごとく使用できるのは、truncateやcopy intoのようなテーブルの中身を変更する場合です。
CREATE OR REPLACEでテーブル自体を上書きした場合、UNDROP文を使って特殊なフローでテーブルを復元する必要があります。
今回は、CREATE OR REPLACEでテーブル自体を上書きした場合のタイムトラベル方法を紹介します。
一般的なタイムトラベル方法
実際に、タイムトラベルをしてみます。
場面としては、IDとNAMEというカラムを持つテーブルAにレコードをインサートしていたものの、「02 秋田」という間違った組みあわせをインサートしてしまったという想定で、タイムトラベルを使って間違う前の状態に戻してみます。
--テーブル作成
CREATE TABLE A ( ID CHAR(2), NAME VARCHAR(20) );
--レコードをインサートする
--01 北海道
INSERT INTO A (ID, NAME) VALUES ('01', '北海道');
--02 青森 と入力しようとしたが、手が滑って秋田を指定してしまった
INSERT INTO A (ID, NAME) VALUES ('02', '秋田');
ここでタイムトラベルを使用して、「02 青森」のインサート前に戻りたい場合、AT句やBEFORE句を使用します。
//タイムトラベルで「02 青森」のインサート前の状態をキャッチ
--「02 青森」のインサートが3分前くらいに実行された場合、5分前時点のテーブル状態を取得
SELECT * FROM A
AT(OFFSET => -60*5);
--「02 青森」のインサートのクエリIDを取得し、それより前のテーブル状態を取得
SELECT * FROM A
BEFORE(STATEMENT => <insert query id>);
//上記クエリ結果 - 02, 秋田が無くなった
-- ID, CHAR
-- 01, 北海道CREATE OR REPLACEした場合のタイムトラベル方法
このブログの本題です。
間違えて、CREATE OR REPLACEで上書きしてしまったテーブルを復元してみます。
場面としては、IDとNAMEというカラムを持つテーブルA作成後、IDとBIRTHDAYというカラムを持つテーブルBを作成しようとしましたが、
テーブルBではなく間違えてテーブルAを指定して上書きしてしまった想定で、タイムトラベルを使って間違う前の状態に戻してみます。
//テーブルA作成
CREATE TABLE A ( ID CHAR(2), NAME VARCHAR(20) );
//テーブルBを作成しようとしたが、寝ぼけてAを上書きしてしまった
CREATE TABLE A ( ID CHAR(2), NAME VARCHAR(20) );
//時刻を使ってテーブルAの上書き前に戻す
SELECT * FROM A (OFFSET=>-60*1);
-- Time travel data is not available for table A. The requested time is either beyond the allowed time travel period or before the object creation time.
-- エラー：指定されたタイムトラベルは、テーブルオブジェクト作成時よりも前なので、タイムトラベルができないよ
//クエリIDを使ってテーブルAの上書き前に戻す
SELECT * FROM OZAWADB.PUBLIC.A BEFORE(STATEMENT => 'xxxxxxxxxxxxxxxxxxxxx');
-- Statement xxxxxxxxxxxxxxxxxxxxxx cannot be used to specify time for time travel query.
-- エラー：クエリID xxxxxxxxxxxxxxはタイムトラベルに使うことができないクエリだよテーブルを上書きすると、上書き時点で新たな別のタイムトラベル履歴が作成されてしまうようで、上書き時点よりも昔の履歴が参照できません。
そのため、以下のようなクエリで上書き前のテーブルを参照してみます。
ソースコード
テーブルを復元するためのソースコードは以下の通りです。
今回のソースコードでは、テーブル名の変更としてRENAMEと、タイムトラベル機能としてUNDROPを使います。UNDROPによって削除したテーブルを復元できます。
--テーブル名を「A」から「A_」にリネーム
ALTER TABLE A RENAME A_;
--テーブル「A」をUNDROPして復元
UNDROP TABLE A;
//タイムトラベル成功！
SELECT * FROM A;
-- ID, CHAR
-- 01, 北海道ソースとなったトラブルシューティングはこちら
解説
一見、何やってるかわからないと思いますので、手順を解説していきます。
以下が今回のRENAMEとUNDROPを使用した状態のテーブル状態の変化です。
① RENAME
テーブルAをテーブルA_にリネームしています。
② 上書き後のテーブル状態がAから無くなる
リネームと同時に、上書き後のテーブルAというオブジェクト自体もテーブルA_に移動します。
この結果、テーブルAには、上書き後の状態が無くなっています。
③ 上書き前のテーブル状態がAの最新になる
上書き後の状態がテーブルAから無くなるため、仮にUNDROPした場合、上書き前の状態が最新となります。
④ UNDROP
削除されたテーブルとして、上書き前のものが参照されるので、上書き前のテーブルが復元されます。
これら①～④のフローによって、上書き前のテーブルが復元されるわけです。
おわりに
truestarでは、Snowflake導入検討、導入支援や環境構築まで幅広くサポート可能です。
Snowflakeに゙興味がある、導入済みだけどもっとうまく活用したい等々ありましたら、ぜひこちらから相談ください！
これまでのSnowflakeに関する記事はこちら
