🐷

Snowflake タイムトラベルについて

2022/11/23に公開約4,200字

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

ログインするとコメントできます