[Snowflake] AT句とBEFORE句で時間旅行をする #SnowflakeDB

Snowflakeでは、更新・削除してしまったテーブルやデータベース、スキーマに対してAT句・BEFORE句を用いてクエリを行うことができます。

今回はAT句とBEFORE句の違いや実際にどのようにクエリを書けばいいのかを確認します。

AT句とBEFORE句とは？

SQLのFROM句でタイムスタンプやクエリIDなどと共にAT句やBEFORE句を指定することで、現在では変更されている対象のテーブルの過去時点のデータにクエリを投げることができます。

例) 2022年6月8日12時時点のテーブルにアクセス （注：タイムトラベルの最長期間は90日です）

select *
from my_table at(timestamp => '2022-06-08 12:00:00'::timestamp)

AT句とBEFORE句の違い

ともに現在は変更されてしまっている過去時点のデータにクエリを投げることのできるAT句とBEFORE句ですが、具体的にどのような違いがあるのでしょうか？

公式ドキュメントによると

• AT キーワードは、指定されたパラメーターに等しいタイムスタンプを持つステートメント、またはトランザクションによる変更がリクエストに含まれることを指定します。
• BEFORE キーワードは、要求が指定されたパラメーターの直前のポイントを参照することを指定します。

と書かれています。

さらに公式ドキュメントの例のところに

テーブルから履歴データを選択しますが、指定したトランザクションによる変更は含まれません。

select * from my_table before(statement => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726');

と書かれています。

というわけで、AT句は指定したタイムスタンプやクエリID自体を含んだ時点のデータを取得し、BEFORE句は指定したクエリIDを含まないそれより前のデータを取得します。

つまり、下記の順番でSQLを実行していたとします。その際にmy_tableに変更をかける前の状態のmy_tableにアクセスしたい場合、AT句ではselect *のクエリIDを指定します。BEFORE句ではupdate my_tableのクエリIDを指定します。

-- AT句はこのクエリIDを指定
select * from my_table;
-- BEFORE句はこのクエリIDを指定
update my_table set my_column = '変えちまえ'

AT句を利用してクエリを書く

実際にAT句を利用してTime Travelを行ってみます。ドキドキ

シナリオは全て共通で、Citi Bikeが提供しているシェアサイクルのデータを使用します。取り込んだテーブルの開始地点（start_station_name）コラムの値をoopsにうっかり変えてしまうというミスから回復します。

-- 開始地点の駅名を取得
select start_station_name from trips group by 1;
-- start_station_nameの値をoopsに変更
update trips set start_station_name = 'oops'

本当にstart_station_nameの値がoopsになってしまったのかを確認します。

-- start_station_nameの値を5件取得
select start_station_name from trips group by 1 limit 5;
+--------------------+                                                          
| START_STATION_NAME |
|--------------------|
| oops               |
+--------------------+

はい、残念ながら本当に全行oopsになってしまっています。

AT句で使用できるオプションは、timestamp、offset、statement、streamです。今回はstream以外の3つを試してみます。

TIMESTAMP

まずはtimestampです。何時何分何十何秒、地球が何回まわった時を指定すればいいわけですね。（違う）

公式ドキュメントによると、値は明示的にTIMESTAMPにキャストする必要があるとのこと。Snowflakeでは明示的に型を指定する場合::data_typeのように指定します。

2022年12月19日のお昼には確実にstart_station_nameにそれぞれ値が入っていたので、時間指定してテーブルを復元します。

-- 2022年12月19日 12時のテーブルを復元
create or replace table trips as
  (
  select *
  from trips at(timestamp => '2022-12-19 12:00:00'::timestamp)
  );

本当に無事に復元できているのかを確認します。

-- start_station_nameを5件取得
select start_station_name from trips group by 1 limit 5;
+--------------------------+                                                    
| START_STATION_NAME       |
|--------------------------|
| Central Park S & 6 Ave   |
| W 44 St & 5 Ave          |
| E 72 St & Park Ave       |
| Fulton St & Grand Ave    |
| Carroll St & Columbia St |
+--------------------------+

おかえりなさい、start_station_name。

information_schemaからtimestampを取得して復元する

先ほどのようにtimestampをベタ打ちで指定する方法もありますが、対象のクエリが実行された時点のデータを復元したいということもあると思います。

その場合、変数を定義するSETステートメントでinformation_schemaからクエリの開始時刻を取得した後に、AT句でその変数を指定します。

次のTime Travelのために、再度start_station_nameの値を'oops'に戻します。というわけで、先ほどAT句を実行したクエリの開始時刻を指定して再び時間旅行を行います。

-- 変数timestampにstart_station_nameの値を全てoopsにするupdate文が完了した日時をセット
set timestamp =
  (
  select end_time from table(information_schema.query_history())
  where query_text like 'update trips set start_station_name%'
  order by end_time desc limit 1
  );

-- 変数timestampを表示
select $timestamp;
+-------------------------------+                                               
| $TIMESTAMP                    |
|-------------------------------|
| 2022-12-19 05:55:49.721 -0800 |
+-------------------------------+

-- 変数timestampにセットした時間のテーブルを復元
create or replace table trips as
  (
  select *
  from trips at(timestamp => $timestamp)
  );

-- start_station_nameを5件取得
select start_station_name from trips group by 1 limit 5;
+--------------------+                                                          
| START_STATION_NAME |
|--------------------|
| oops               |
+--------------------+

無事に（!?）全行oopsになってしまっているテーブルを復元できました。

Snowflakeで変数を参照する際には$variableで参照できます。

OFFSET

現在の時刻との差を秒単位で-Nの形式で指定します。

先ほどのstart_station_nameを全てoopsにするcreate文を実行した時間と現在時刻との時差を指定してstart_station_nameを取り返します。

-- 5分前(300秒)のtripsテーブルを復元
create or replace table trips as
  (
  select *
  from trips at(offset => -5*60)
  );

-- 
select start_station_name from trips group by 1 limit 5;
+------------------------+                                                      
| START_STATION_NAME     |
|------------------------|
| W 41 St & 8 Ave        |
| Riverside Dr & W 91 St |
| W 37 St & 5 Ave        |
| Warren St & Church St  |
| Hope St & Union Ave    |
+------------------------+

STATEMENT

AT句の最後はstatementです。statementではクエリIDと呼ばれるSnowflakeアカウント内で直近14日間に実行されたすべてのクエリに付与されるIDを指定してそのクエリ時点のデータを取得します。

それでは、tripsテーブルに対してupdate文でstart_station_nameの値をすべてoopsにしてしまってから、それ以前に実行したクエリIDをAT句で指定して元の値を復元します。

-- start_station_nameを5件取得
-- このクエリのクエリID（01a916b4-0000-a767-0000-999d0003919e）を利用してテーブルを復元する
select start_station_name from trips group by 1 limit 5;
+-------------------------+                                                     
| START_STATION_NAME      |
|-------------------------|
| W 43 St & 10 Ave        |
| Lexington Ave & E 24 St |
| E 12 St & 3 Ave         |
| Broadway & W 24 St      |
| W 52 St & 6 Ave         |
+-------------------------+

-- start_station_nameの値を5件取得
update trips set start_station_name = 'oops'; 

-- start_station_nameを5件取得
select start_station_name from trips group by 1 limit 5;
+--------------------+                                                          
| START_STATION_NAME |
|--------------------|
| oops               |
+--------------------+

クエリIDの取得方法はいくつかあります。SnowflakeのUIからも確認することができます。

ホーム画面 > アクティビティ > クエリ履歴 で対象のクエリを選択すると下記画像の画面に遷移します。

クエリ履歴はコピーできるようになっているのでコピーしてクエリに貼り付けます。

-- クエリID（01a916b4-0000-a767-0000-999d0003919e）時点のデータを復元
create or replace table trips as (
  select *
  from trips at(statement => '01a916b4-0000-a767-0000-999d0003919e')
);

-- start_station_nameの値を5件取得
-- このクエリのクエリIDをこの後のSQLで変数query_idにセット
select start_station_name from trips group by 1 limit 5;
+-----------------------------------------------+                               
| START_STATION_NAME                            |
|-----------------------------------------------|
| W 13 St & 7 Ave                               |
| W 20 St & 7 Ave                               |
| Nostrand Ave & Myrtle Ave                     |
| Central Park North & Adam Clayton Powell Blvd |
| Clinton Ave & Myrtle Ave                      |
+-----------------------------------------------+

information_schemaからクエリIDを取得して復元する

クエリIDはテーブル関数のquery_historyからも取得することができます。

-- 最後にstart_station_nameの値を5件取得した際のクエリIDを変数query_idにセット
set query_id = (
  select query_id
  from table(information_schema.query_history_by_session())
  where query_text like 'select start_station_name%'
  order by 1 desc limit 1);

-- start_station_nameの値を5件取得
select start_station_name from trips group by 1 limit 5;
+--------------------+                                                          
| START_STATION_NAME |
|--------------------|
| oops               |
+--------------------+

-- 変数$query_idを表示
select $query_id;
+--------------------------------------+                                        
| $QUERY_ID                            |
|--------------------------------------|
| 01a918d9-0000-a7d1-0000-999d00036242 |
+--------------------------------------+

--　変数$query_idにセットしたクエリIDが実行された時点のデータを復元
create or replace table trips as(
  select *
  from trips at(statement => $query_id));

-- start_station_nameの値を5件取得
select start_station_name from trips group by 1 limit 5;
+------------------------------+                                                
| START_STATION_NAME           |
|------------------------------|
| Meserole Ave & Manhattan Ave |
| Emerson Pl & Myrtle Ave      |
| Duane St & Greenwich St      |
| W 45 St & 8 Ave              |
| N Henry St & Richardson St   |
+------------------------------+

BEFORE句を利用してクエリを書く

今度はBEFORE句を利用したクエリを試してみます。BEFORE句のオプションはクエリIDを指定するSTATEMENTのみです。

この記事の最初で確認した通り、BEFORE句で指定するクエリIDはそのクエリが実行される前のデータを復元します。

最後のoopsをはりきって行いましょう。

-- start_station_nameの値を5件取得
update trips set start_station_name = 'oops'; 

-- start_station_nameを5件取得
select start_station_name from trips group by 1 limit 5;
+--------------------+                                                          
| START_STATION_NAME |
|--------------------|
| oops               |
+--------------------+

無事に最後までうっかりstart_station_nameの値をoopsにすることに成功しました。データの復元もごきげんにやりましょう。

-- start_station_nameの値をoopsにしてしまったupdate文のクエリIDを変数query_idにセット
set query_id = (
  select query_id
  from table(information_schema.query_history_by_session())
  where query_text like 'update%'
  order by 1 desc limit 1);

-- 変数$query_idを表示
select $query_id;
+--------------------------------------+                                        
| $QUERY_ID                            |
|--------------------------------------|
| 01a918fa-0000-a7d1-0000-999d00036256 |
+--------------------------------------+

-- 変数$query_idにセットしたクエリIDが実行された直前のデータを復元
create or replace table trips as(
  select *
  from trips before (statement => $query_id));

-- start_station_nameの値を5件取得
select start_station_name from trips group by 1 limit 5;
+--------------------------+                                                    
| START_STATION_NAME       |
|--------------------------|
| Cleveland Pl & Spring St |
| E 33 St & 2 Ave          |
| 3 Ave & 14 St            |
| Great Jones St           |
| Broadway & W 37 St       |
+--------------------------+

まとめ

Snowflake Time Travelで使用されるAT句とBEFORE句を試しました。感覚的には実際に更新をかけてしまったクエリの直前の状態に戻してくれるBOFORE句が使いやすいかなと思いました。

しかし、クエリIDが使用できるのは14日間とSnowflakeでTime Travel期間を設定できる30日より短いため、すべてのケースで使えるわけではないので注意が必要です。