Snowflakeのタイムトラベル関連でよく使いそうなコマンドを試してみた #SnowflakeDB

※本エントリは、Snowflakeをより使いこなそう！ Advent Calendar 2021の2日目の記事となります。

さがらです。

Snowflakeのタイムトラベル機能で、よく使いそうなコマンドをまとめてみます。

タイムトラベルとは？

テーブル、スキーマ、データベース、について定義された期間の間の任意の時点で履歴データにアクセスできる機能です。対象のデータが変更されていようと削除されていようと、コマンド１つでアクセスして復元できてしまうのが強みの機能ですね。

弊社でも説明しているブログがすでにありますので、こちらもぜひご覧ください。

タイムトラベルの設定を確認・変更する

まず前提として、Snowflakeはデフォルトで全オブジェクトに最大1日までのタイムトラベルが設定されています。

しかしEnterprise以上のエディションで契約を頂いている場合は、最大90日までタイムトラベルの期間を設定できます。

アカウントでのタイムトラベルのデフォルト値を確認・変更する

まず、アカウントレベルでのタイムトラベルのデフォルト値を確認するには、以下のクエリを実行します。 タイムトラベルの設定値はどのオブジェクトでも共通してDATA_RETENTION_TIME_IN_DAYSです。

SHOW PARAMETERS like '%DATA_RETENTION_TIME_IN_DAYS%' in account;

次に、このアカウントレベルで設定されているデフォルト値を変更するには、以下のクエリを実行します。（この例では5日に変更してみます。）

※注意：例えばこの設定を90日にしてしまうと、不要なオブジェクトまで90日間保持し、ストレージコストが急増するリスクがあります。アカウントレベルの設定は慎重に変更しましょう。

-- ACCOUNTADMINロールでないと変更できません。
USE ROLE ACCOUNTADMIN;
ALTER ACCOUNT SET DATA_RETENTION_TIME_IN_DAYS=5;

各オブジェクトレベルでの設定値を確認・変更する

各オブジェクトに設定されているタイムトラベルの設定値を確認するには、以下のクエリを実行します。 表示された結果のうち、retention_timeが各オブジェクトの設定値を表しています。

-- アクセス権を持つデータベースの一覧を表示する
SHOW DATABASES;
-- timetravel_testというDB上のテーブルの一覧を表示する
SHOW TABLES in DATABASE timetravel_test;

各オブジェクトに設定されているタイムトラベルの設定値を変更するには、以下のクエリを実行します。 CREATEコマンドで新しくオブジェクトを作成するときはもちろん、作成したあともALTERコマンドで変更可能です。

CREATE TABLE sample_table (no int) DATA_RETENTION_TIME_IN_DAYS=90;

ALTER TABLE sample_table SET DATA_RETENTION_TIME_IN_DAYS=90;

タイムトラベルの機能を用いてリカバリする

次は、実際にリカバリを行うときのクエリについて説明します。

DROPしたオブジェクトの復元

対象のオブジェクトを一度DROPして、そのオブジェクトをタイムトラベル保持期間内に復元したい場合は、UNDROPコマンドを使用すればOKです。

-- データベースの復元
UNDROP DATABASE <name>;
-- スキーマの復元
UNDROP SCHEMA <name>;
-- テーブルの復元
UNDROP TABLE <name>;

以前の状態のテーブルに対してSELECT文を発行する

以前の状態のテーブルに対してSELECT文を発行するためには、3種類の方法があります。

• 1つ目：タイムスタンプを指定する方法

-- 標準時からマイナス7時間とした上で、2015年5月1日16時20分時点のmy_tableのデータを取得する
SELECT * FROM my_table AT(timestamp => 'Fri, 01 May 2015 16:20:00 -0700'::timestamp_tz);

• 2つ目：「～分前」という時間を直接指定する

-- 10分前の時点のmy_tableのデータを取得する。offsetには秒単位で入れるため、「60秒×10」で10分を表す
SELECT * FROM my_table AT((offset => -60*10);

• 3つ目：クエリIDを指定し、そのクエリが実行される前の状態にする

-- 指定したクエリIDのクエリが実行される前の状態の、my_tableのデータを取得する
SELECT * FROM my_table BEFORE(statement => '<クエリID>');

クローンを用いた復元

対象のオブジェクトをタイムトラベルで取得し、クローンとして保持することも可能です。この方法も3つあり、タイムスタンプを指定する方法、「～分前」という時間を直接指定する方法、クエリIDを指定する方法、3つあります。

クエリの書き方は前述の以前の状態にオブジェクトを戻す方法と似ています。以下にはoffsetを使って1時間前に戻す方法を記載しております。

-- 60分前の状態のmy_schemaのデータを、restored_schemaと命名して保持する
CREATE SCHEMA restored_schema CLONE my_schema AT(offset => -60*60);

またクローンだけでは元のオブジェクト名から名称が変わってしまうため、元のオブジェクト名で一定時間前の状態のオブジェクトに復元するとき一連のクエリサンプルも記載しておきます。（例として、publicスキーマを1時間前の状態に戻すクエリを記載。）

-- １．復元したい状態のpublicスキーマをクローンとして作成する。ここでは1時間前のpublicスキーマをクローンする
CREATE SCHEMA public_clone CLONE public AT(offset => -60*60);

-- ２．既存のpublicスキーマの削除
DROP SCHEMA public;

-- ３．public_cloneを元に、publicをクローンとして作成
CREATE SCHEMA public CLONE public_clone;

次回

Snowflakeをより使いこなそう！ Advent Calendar 2021、次回の3日目では、「Snowflakeでviewが何から作られているかを知るGET_OBJECT_REFERENCESを試してみた」というタイトルで執筆します。お楽しみに！