#07 ： ハンズオンラボガイド「タイムトラベル機能の使用」 – Snowflake Advent Calendar 2019 #SnowflakeDB

当エントリは『Snowflake Advent Calendar 2019』の07日目のエントリです。

Snowflake Advent Calendar 2019 - Qiita
Snowflake Advent Calendar 2019 ｜ シリーズ ｜ Developers.IO

このエントリでは、Snowflakeの特徴的な機能の一つである「タイムトラベル(Time Travel)機能」について紹介します。
タイムトラベル機能により、以下が提供されます。

• DROPしたテーブルをSQL文一つで復元する
• テーブルに格納されていた、ある時点の過去データを参照する

早速この機能について、演習を通じて確認してみましょう。
なお、このModule 7では、Module 3で作成したtripsテーブルと、Module 6で作成したjson_weather_dataテーブルを使用します。

今まで実施した演習、それからこれから操作する演習で使用するSQL文は、下記ファイルに記載されています。
事前にダウンロードしてワークシートにロードの上、不足していた操作を実行しておくと良いでしょう。

• lab_scripts_free_trial.sql

Module 7: タイムトラベル機能の使用

7.1 テーブルを削除し復元する(Drop and Undrop a Table)

以前の演習で作成したテーブルを一旦DROP TABLE文で削除し、その後UNDROP TABLE文で復元します。

7.1.1 テーブルの削除

まず念の為、json_weather_data テーブルが存在することを確認します。

SELECT * FROM json_weather_data LIMIT 10;

このテーブルをDROP TABLE文を使い削除します。

DROP TABLE json_weather_data;

7.1.2 テーブル削除を確認

json_weather_data テーブルの削除を確認します。

SELECT * FROM json_weather_data LIMIT 10;

7.1.3 テーブルの復元

UNDROP TABLE文を使用して、消したjson_weather_data テーブルを復元します。

UNDROP TABLE json_weather_data;

7.1.4 テーブルの復元を確認

SELECT文で消したjson_weather_data テーブルが復元できていることを確認します。

SELECT * FROM json_weather_data LIMIT 10;

7.2 テーブルのロールバック(Roll Back a Table)

更新してしまったテーブルのデータを過去の値に戻します。

7.2.1 捜査対象の設定

7.2では、7.1と異なるテーブルを操作するので、捜査対象のデータウェアハウスやデータベース、スキーマなどを再設定します。

USE ROLE sysadmin;
USE WAREHOUSE compute_wh;
USE DATABASE citibike;
USE SCHEMA public;

7.2.2 ユーザ操作エラーの発生

まず、下記のSELECT文を実行して、内容を確認しておきます。
このSELECT文は、start_station_name(出発駅)ごとの利用者数を集計するものです。

SELECT
  start_station_name AS "station",
  COUNT(*) AS "rides"
FROM
  trips
GROUP BY 1
ORDER BY 2 DESC
LIMIT 20;

次に、tripsテーブルのstart_station_nameカラムをうっかり全て同じ値に更新してしまいます。

UPDATE trips SET start_station_name = 'oops';

7.2.3 ユーザ操作エラー状況の確認

7.2.2で実行したSEELCT文を再度実行してみます。
start_station_nameカラムを全て同じ値にしてしまったので、集計結果が1行になってしまっています。

SELECT
  start_station_name AS "station",
  COUNT(*) AS "rides"
FROM
  trips
GROUP BY 1
ORDER BY 2 DESC
LIMIT 20;

7.2.4 エラー原因のクエリID特定

このようなミスによるユーザ操作エラーは、システムから見ると正常動作のため、最悪の場合、バックアップからリストアした上で、ポイントインタイムリカバリを実行するなどの複雑な操作が必要でした。

Snowflakeでは、タイムトラベル機能を使って簡単に解決できます。まず、問題のUPDATE文をシステム上に記録された履歴(information_schema.query_history_by_sessionテーブル)から探し出し、query_id変数に格納しておきます。

SET query_id = (
  SELECT
    query_id
  FROM 
    TABLE(information_schema.query_history_by_session (result_limit => 5)) 
  WHERE
    query_text LIKE 'update%'
  ORDER BY start_time
  LIMIT 1);

7.2.5 特定したクエリIDの直前状態にテーブルを再作成

ここがタイムトラベル機能の真骨頂です。先程求めたクエリIDの直前状態にテーブルを再作成する操作を、以下のSQLで行います。
RDBではおなじみのCTAS(CREATE TABLE AS SELECT)の変形版ですね。

CREATE OR REPLACE trips
AS
(SELECT * FROM trips BEFORE(statement => $query_id));

7.2.6 テーブル復元を確認

7.2.3で実行したSELECT文を三度実行します。1行しか返ってこなくなっていたデータが復元できています。

SELECT
  start_station_name AS "station",
  COUNT(*) AS "rides"
FROM
  trips
GROUP BY 1
ORDER BY 2 DESC
LIMIT 20;

おまけ：よくありそうな質問

タイムトラベル機能に関するありがちな質問と回答を追記します。

タイムトラベル機能はどうやって実現している？

Snowflakeはデータの更新や削除の際、変更前のデータはそのまま保持しつつ(=すぐには削除せずに)、更新後のデータを追記して管理する構造になっています(細かいことを言うと単なる追記型とも異なるので、彼らは"IMMUTABLE"と表現しています)。また、それぞれのSQL文の操作を全てクエリIDとして管理しています。そこで、特定のクエリIDを基準に、変更前のデータに書き戻すという事が可能です。

タイムトラベル機能でどのくらい過去まで戻れる？

Snowflake利用時に選択したエディションによります。
Standard Editionであれば1日(24時間)で、それ以上のエディションであれば90日まで遡ることが可能です。

タイムトラベル機能を、クエリIDではなく時刻ベースでも使う事は可能か？

可能です。例えば現在から5分前(-300秒)のテーブルの状態を求めたい場合は、以下の句を伴うSQLを実行します。

SELECT * FROM trips AT (offset => -300);

詳細は下記Snowflakeのドキュメントを参照ください。

• AT | BEFORE

まとめ

ユーザのミスを帳消しにできるUNDROP TABLE文とタイムトラベル機能、とても便利ですので是非事前に使い方を身に着けておいて、いざという時に対応できるようにしておきましょう！

明日の08日目は八幡による#08 ： ハンズオンラボガイド「ロールベースのアクセス制限とアカウント管理」の予定です。お楽しみに！