Snowflake
Tips_snowflake
データアナリティクス

Snowflakeのタイムトラベルの使い方

18分11秒
Snowflakeのタイムトラベルの使い方

Snowflakeのタイムトラベルの使い方についてサンプルを交えながら解説します

AkimasaKajitaniです。

Snowflakeを知っているみなさまは、「誤ってデータを削除したとしてもタイムトラベルという機能でいつでも復帰できます」と聞いたことがあるかもしれません。とはいえ、使い方を知らないといざというときに何をどうしたいいのかわからないかと思います。今回は、転ばぬ先の杖として、実際にどのようなものか試してみたいと思います。

 

ざっくりとしたタイムトラベルの定義

タイムトラベルの考え方としては、あらかじめ決められたデータ保持期間の間、削除されたデータに対してSQL文でデータを取得できる、というものです。これは、決められた期間の間は物理的にデータが削除されていない、ということを意味します。その分、ストレージに対する課金が発生するのでコストには十分注意する必要があります。

 

データ保持期間について

データ保持期間はデフォルトでは1日（24時間）です。最大で90日まで延長することができますが、使用しているエディションに依存します。Standard Editionでは最大1日、Enterprise Editionでは最大90日となります。いずれのエディションでも0にすることが可能です（実質タイムトラベルしない、という設定です）。

 

データ保持期間を過ぎるとどうなるのか？

データ保持期間を過ぎたデータは削除され、タイムトラベルではアクセスできなくなります。それでも復帰したいデータがある、ということであれば、フェイルセーフという機能で復帰することが可能ですが、これはSnowflake社に個別にお願いする必要があります（自力ではアクセスできない機能です）。

 

タイムトラベルは実際どのように使うのか？

大きく分けると2つのことができます。１つは、UNDROPコマンドを使い、削除されたテーブル・スキーマ・データベースを復元することです。２つ目は、過去の特定の時点のデータ抽出です。これにより、SELECTからのINSERT INTOもしくはCLONEでデータの復帰が可能です。手軽にデータを復帰するにはCLONEですが、ケースによってはSELECTしてINSERT INTOするのが有効な場合があります。

それぞれの内容について見ていきたいと思います。 

 

UNDROPコマンドによる復旧

誤ってテーブルなどをdrop（削除）してしまった場合は、UNDROPコマンドで復帰できます。これは構文も簡単です。

 

例えば、testdatabaseというデータベース内に、testschemaというスキーマがあり、その中にtesttableというテーブルがあるとします。ここで、間違えて以下のSQLコマンドを発行し実行したとします。 

DROP TABLE testtable;

 

この場合は、UNDROPコマンドで復旧が可能です。具体的なコマンドは以下のとおりです。 

UNDROP TABLE testtable;

 

復帰するものがデータベースの場合は、上のコマンドの「TABLE」と記載ある部分を「DATABASE」としますし、スキーマの場合は「TABLE」の部分を「SCHEMA」と変更するだけです。

 

データベース復帰の例として、testdatabaseというデータベースを以下コマンドで削除した場合、

DROP DATABASE testdatabase;

これに対して、復旧する場合は以下のコマンドとなります。

UNDROP DATABASE testdatabase;

 

スキーマの復旧例としてTestschemaというスキーマを以下コマンドで削除した場合、

DROP SCHEMA testschema;

復帰する場合は、

UNDROP SCHEMA testschema;

となります。

特定の時点のデータへの復旧

DELETE文による一部データの削除やINSERT、UPDATEなどデータを挿入、上書きした場合は、細かく各コマンドを元に戻すという方法ではなく、指定した時点のデータを取得し保存する、という方法になります。つまり、SELECTする場合にWHERE句で指定するように、AT句もしくはBEFORE句で特定のタイミングを指定する形になります。

 

AT句の場合は、指定したタイムスタンプ（日時）か、指定した秒数前のデータを取得するという形になります。BEFORE句の場合は、SQL履歴のIDを使って、指定SQL実行直前の時点のデータを取得します。

 

指定した時間に復帰したい場合

指定した日時のデータを取得したい場合、SELECT文に以下のように記載します。

AT(timestamp => '2024-05-03 06:00:00 -0700'::timestamp_tz);

 

時刻指定の場合、厄介なのが、デフォルトで時刻がロサンゼルスタイムになっていることです。この場合、

SELECT current_timestamp();

とすることで、現在の時間を確認できるため、上記SQLコマンドで現在の時間を参考にしながら時間を指定するのが簡単かもしれません。 

もしくは、

ALTER SESSION SET TIMEZONE = 'Asia/Tokyo';

で一時的にタイムゾーンを日本に変更することができます。この場合は、以下のような表記で日本時間をそのまま利用できます。

AT(timestamp => '2024-05-03 22:18:00'::timestamp_tz);

 

サンプルとして、testtableを2024-05-03 06:00:00（ロサンゼルス時間）の時点のデータを取得する場合は以下のようなSQL文で確認できます。

SELECT * FROM testtable AT(timestamp => '2024-05-03 06:00:00 -0700'::timestamp_tz);

 

問題なければ、CLONEで新規テーブル（ここではtesttable_cloneとします）にデータをコピーするのが良いと思います。

CREATE TABLE testtable_clone CLONE testtable AT(timestamp => '2024-05-03 06:00:00 -0700'::timestamp_tz);

 

この場合、戻したいデータのテーブル名がtesttable_cloneとなってしまっているため、元のテーブルに戻したい場合は、スワップすると早いです。

ALTER TABLE testtable SWAP WITH testtable_clone;

 

指定した秒数前のデータに復旧したい場合

AT句の場合で、指定した秒数前に復旧したい場合、

AT(offset => -[復帰前の秒数])

と記載します。例えば、10分前に戻りたいような場合は、

AT(offset => -60*10)

などと記載するとわかりやすいです。 

上記例では、

CREATE TABLE testtable_clone CLONE testtable AT(offset => -60*10);

と記載します。

 

誤ってTRUNCATEしたような場合

誤ってTRUNCATEしたような場合は、テーブル自体は残っているのでTRUNCATEする前のデータをそのまま書き戻すのが手っ取り早いです。つまり、SELECTしてINSERT INTO します。

 

例として、以下のようなSQLで誤ってtesttableテーブルを削除したとします。

TRUNCATE testtable;

 

この場合は、10分前の状態に復旧したい場合は、以下のようなSQL文になります。

INSERT INTO testtable SELECT * FROM testtable AT(offset => -60*10);

 

CREATE OR REPLACE TABLEしたテーブルのデータが抜き出せません

テーブル定義を置き換えたような場合、置換後のオブジェクトは以前にあったオブジェクト異なるものとして認識されるため、直接SELECTすることができません。この場合は少し工夫が必要です。

 

基本的な考え方として、RAPLACE TABLEしたテーブルの名前をリネームすることで、元のテーブルをUNDROPできるようになります。UNDROPすればREPLACE前のテーブルが復活します。気をつけないといけないポイントとしては、リネームする代わりにDROPしてからUNDROPすると、結局REPLACE TABLEした空っぽのテーブルが復活するだけということです。

 

例として、testtableを誤ってREPLACE TABLEしたとします。この場合は以下の手順で復旧できます。

ALTER TABLE testtable RENAME TO testable_renamed;
UNDROP testable;

 

リネームしたtestable_renamedは不要なものなので、DROPしても問題ありません。

 

参考資料

 

サンプルコード

USE ROLE SYSADMIN;
USE WAREHOUSE designer;
----- ■■■■■ 準備 ■■■■■
----- DB作成
CREATE DATABASE SAMPLE_TIMETRAVEL_DB;

USE DATABASE SAMPLE_TIMETRAVEL_DB;

----- TABLE作成
CREATE TABLE SAMPLE_TT_TABLE (
    id INTEGER,
    name STRING,
    age INTEGER
);
----- サンプルテーブル作成
INSERT INTO SAMPLE_TT_TABLE (id, name, age) VALUES (1, 'Alice', 25);
INSERT INTO SAMPLE_TT_TABLE (id, name, age) VALUES (2, 'Bob', 30);
INSERT INTO SAMPLE_TT_TABLE (id, name, age) VALUES (3, 'Charlie', 35);
INSERT INTO SAMPLE_TT_TABLE (id, name, age) VALUES (4, 'David', 40);
INSERT INTO SAMPLE_TT_TABLE (id, name, age) VALUES (5, 'Eve', 45);
INSERT INTO SAMPLE_TT_TABLE (id, name, age) VALUES (6, 'Frank', 50);
INSERT INTO SAMPLE_TT_TABLE (id, name, age) VALUES (7, 'Grace', 55);
INSERT INTO SAMPLE_TT_TABLE (id, name, age) VALUES (8, 'Hannah', 60);
INSERT INTO SAMPLE_TT_TABLE (id, name, age) VALUES (9, 'Ivy', 65);
INSERT INTO SAMPLE_TT_TABLE (id, name, age) VALUES (10, 'Jack', 70);

----- 確認用
SELECT * FROM SAMPLE_TT_TABLE;

----- ■■■■■ TimtTravel ■■■■■
----- Drop>=UNDROP
DROP TABLE SAMPLE_TT_TABLE;    ----- 削除
SELECT * FROM SAMPLE_TT_TABLE; ----- 削除されていることを確認
UNDROP TABLE SAMPLE_TT_TABLE;  ----- UNDROPで復活
SELECT * FROM SAMPLE_TT_TABLE; ----- 復活できていることを確認

----- AT句で時間指定でSELECT=>CLONEしてSWAP
TRUNCATE SAMPLE_TT_TABLE;      ----- 中身のデータを削除
SELECT * FROM SAMPLE_TT_TABLE; ----- 削除されていることを確認
SELECT current_timestamp;      ----- 今の時間を確認（ロサンゼルス時間）
SELECT * FROM SAMPLE_TT_TABLE AT(timestamp => '2024-05-03 20:05:00 -0700'::timestamp_tz);  ----- 復帰できる時間帯に変更して実行してください
CREATE TABLE SAMPLE_TT_TABLE_CLONE CLONE SAMPLE_TT_TABLE AT(timestamp => '2024-05-03 20:05:00 -0700'::timestamp_tz);   ----- SAMPLE_TT_TABLE_CLONEというテーブル名でCLONE。復帰できる時間帯に変更して実行してください
SELECT * FROM SAMPLE_TT_TABLE_CLONE; ----- CLONEテーブルが作成されていることを確認
SELECT * FROM SAMPLE_TT_TABLE;       ----- 元テーブルは空のままであることを確認
ALTER TABLE SAMPLE_TT_TABLE_CLONE SWAP WITH SAMPLE_TT_TABLE; ----- テーブルをスワップ
SELECT * FROM SAMPLE_TT_TABLE_CLONE; ----- CLONE側が空っぽの元テーブルになっていることを確認
SELECT * FROM SAMPLE_TT_TABLE;       ----- CLONEテーブルの内容になっていることを確認
DROP TABLE SAMPLE_TT_TABLE_CLONE;    ----- 不要なテーブルをDROP

----- AT句でN秒前に指定でSELECT
TRUNCATE SAMPLE_TT_TABLE;      ----- 中身のデータを削除
SELECT * FROM SAMPLE_TT_TABLE; ----- 削除されていることを確認
SELECT * FROM SAMPLE_TT_TABLE AT(offset => -60*5); ----- 復帰できる秒数前を指定してデータを抽出できることを確認
CREATE TABLE SAMPLE_TT_TABLE_CLONE2 CLONE SAMPLE_TT_TABLE AT(offset => -60*5);   ----- SAMPLE_TT_TABLE_CLONE2というテーブル名でCLONE。復帰できる時間に変更して実行してください
SELECT * FROM SAMPLE_TT_TABLE_CLONE2; ----- CLONEテーブルが作成されていることを確認

----- CREATE OR REPLACEの場
CREATE OR REPLACE TABLE SAMPLE_TT_TABLE (
    id INTEGER,
    name STRING,
    age INTEGER
); ----- TABLEを再作成
SELECT * FROM SAMPLE_TT_TABLE; ----- データが削除されていることを確認
SELECT * FROM SAMPLE_TT_TABLE AT(offset => -60*5); ----- 復帰できないことを確認
ALTER TABLE SAMPLE_TT_TABLE RENAME TO SAMPLE_TT_TABLE_RENAMED;
SELECT * FROM SAMPLE_TT_TABLE; ----- テーブルがリネームされてしまったことを確認
UNDROP TABLE SAMPLE_TT_TABLE;  ----- タイムトラベルで復活
SELECT * FROM SAMPLE_TT_TABLE; ----- 復活できていることを確認

 

 

 

Snowflakeの導入はぜひKCMEで！

製品に対する操作方法、技術的な質問などは導入前・導入後に関わらずメールにてお答えします。また、Snowflakeを用いた環境構築、開発、ヘルプデスク対応なども承りますので、お気軽にご相談ください。

Snowflakeのお問い合わせはこちら
おすすめの記事