🐷
Snowflake タイムトラベルについて
Snowflakeの特徴的な機能であるタイムトラベルの動作を見ていきます。
タイムトラベルとは
概要
ドキュメントより抜粋
Snowflake Time Travelでは、定義された期間内の任意の時点で履歴データ
（つまり、変更または削除されたデータ）にアクセスできます。
Time Travelは、次のタスクを実行するための強力なツールとして機能します。
・誤ってまたは意図的に削除された可能性のあるデータ関連オブジェクト（テーブル、スキーマ、およびデータベース）の復元。
・過去の重要なポイントからのデータの複製とバックアップ。
・指定された期間のデータ使用量/操作の分析。
データ保持期間
ドキュメントより抜粋
Snowflake Time Travelの重要な要素は、データ保持期間です。
データの削除またはデータを含むオブジェクトの削除など、テーブル内のデータが変更されると、
Snowflakeは更新前のデータの状態を保持します。
データ保持期間は、この履歴データが保持される日数を指定するため、
Time Travel操作（SELECT、CREATE ... CLONE、UNDROP）をデータに対して実行できます。
標準の保持期間は1日（24時間）であり、すべてのSnowflakeアカウントで自動的に有効になります。
Snowflake Standard Editionの場合、アカウントおよびオブジェクトレベル
（つまり、データベース、スキーマ、およびテーブル）
で保持期間を0（またはデフォルトの1日に戻す）に設定できます。
Snowflake Enterprise Edition（またはそれ以上）の場合：
一時データベース、スキーマ、およびテーブルの場合、保持期間を0
（またはデフォルトの1日に戻す）に設定できます。
同じことが仮テーブルにも当てはまります。
永続データベース、スキーマ、およびテーブルの場合、
保持期間は0から90日までの任意の値に設定できます。
Snowflakeタイムトラベルの動作
環境準備
検証用のテーブル作成
CREATE OR REPLACE TABLE OUR_FIRST_DB.public.test (
id int,
first_name string,
last_name string,
email string,
gender string,
Job string,
Phone string)
検証用ファイルフォーマット作成
CREATE OR REPLACE FILE FORMAT MANAGE_DB.file_formats.csv_file
type = csv
field_delimiter = ','
skip_header = 1
検証用外部ステージ作成
CREATE OR REPLACE STAGE MANAGE_DB.external_stages.time_travel_stage
URL = 's3://data-snowflake-fundamentals/time-travel/'
file_format = MANAGE_DB.file_formats.csv_file;
テストテーブルにデータをロード
COPY INTO OUR_FIRST_DB.public.test
from @MANAGE_DB.external_stages.time_travel_stage
files = ('customers.csv')
SELECT * FROM OUR_FIRST_DB.public.test;
タイムトラベルで過去の履歴データにアクセスする方法
タイムトラベルでデータを遡る手法として下記3つあります。
- TIMESTAMP
- OFFSET （現在時刻との秒単位の時差）
- STATEMENT （ステートメントの識別子。例：クエリ ID）
TIMESTAMPを使って履歴データにアクセスする方法
タイムゾーンをセット
タイムゾーンをUTCにセットします。（もしかしたらこれは不要かも・・・）
ALTER SESSION SET TIMEZONE ='UTC'
現在時刻を取得
SELECT CURRENT_TIMESTAMP
カラムのデータを書き換える
UPDATE OUR_FIRST_DB.public.testSET Job = 'Data Scientist';
データ確認
SELECT * FROM OUR_FIRST_DB.public.test;
履歴データにアクセスしてみる
SELECT * FROM OUR_FIRST_DB.public.test before (timestamp => '2022-10-28 00:45:35.894'::timestamp)
書き換える前のデータ（履歴データ）にアクセスできました。
OFFSETを使って履歴データにアクセスする方法
カラムのデータを書き換える
UPDATE OUR_FIRST_DB.public.test SET FIRST_NAME = 'Joyen';
もう一度テーブルを確認するとFIRST_NAMEカラムのフィールド値がすべて'Joyen'に変更しています。
履歴データにアクセスしてみる
コマンド説明
OFFSETオプションは現在時刻との秒単位の時差を扱います。
下記コマンド例で説明すると、20分前のテーブル履歴情報を取得しています。
SELECT * FROM OUR_FIRST_DB.public.test at (OFFSET => -60*20)
STATEMENTを使って履歴データにアクセスする方法
カラムのデータを書き換える
今回はEMAILのデータを全てNULLに書き換えます。
UPDATE OUR_FIRST_DB.public.test SET EMAIL = null;
履歴データにアクセスしてみる
まずは操作ミス（UPDATE句）のクエリIDを確認する。
下記図の右上にある「履歴を開く」
対象の操作ミスしたクエリのIDをクリックします
クエリの詳細画面に遷移します。
画面左のクエリIDをコピーします。
コマンド説明
statementに'クエリID'を渡します。
ここで指定したクエリIDが実行される前の履歴データにアクセスします。
SELECT * FROM OUR_FIRST_DB.public.test before (statement => '01a7ec2a-0000-93d7-0000-0000a6652175')
無事履歴データにアクセスできました。
EMAILのデータもUPDATE前に戻っています。
履歴データからテーブルにリストアする
CREATE OR REPLACE TABLE OUR_FIRST_DB.public.test as SELECT * FROM OUR_FIRST_DB.public.test before (statement => '01a7ec2a-0000-93d7-0000-0000a6652175')
SELECT * FROM OUR_FIRST_DB.public.test
Discussion