Snowflake Time Travel

abehsu

Published in

ㄚ北的所見所聞

12 min readMay 13, 2019

功能

讓你能在定義好的時間區間內任意查詢歷史資料(被變動過或是被刪除的資料)

好處

可以還原DATA-RELATED的Object，因為他們可能會被不小心或是惡意刪除
可以從某個時間點複製或備份資料
可以分析特定區間資料的使用狀況及操作

可以用的操作

可以查詢資料的過去資訊
複製整個tables、Schemas、Databases，或是過去的某一個snapshot
還原Tables/Schemas/Databases

預設的Time Travel區間

所有版本的Time Travel功能預設都會開啟，並且Default設定為1天

標準版:最低可以被設定為0(意思就是把這個功能關閉)

企業版以上:對於Temporary或是Transient的Object可以設為0或是1,對於一般的Permanent Object可以設定0~90

注意:設為0代表取消Time Travel的功能
區間設大的話需要注意會影響到資料儲存的成本(收取Data storage的費用)

Data Lifecycle with Time Travel

對於超過Time Travel區間的資料會被Snowflake刪除:

沒辦法再被查詢
沒辦法被複製
沒辦法被還原

Access 權限

User必須要有要操作Object的OWNERSHIP權限，換句話說也必須要有Create Object的權限才能Restore

注意事項：
Restoring tables and schemas is only supported in the current schema or current database, even if a fully-qualified object name is specified.

設定方式

ADMIN ACCOUNT 可以設定Default的DATA_RETENTION_TIME_IN_DAYS參數值
也可以在Create Database/Schema/Table的時候設定

注意事項Changing the retention period for a table only applies to data updates that occur after the new retention period goes into effect. Historical data that existed prior to the change is bound by the original retention period.

查詢目前Object Default Parameter設定

USE SF_TUTS;
show parameters in database;
show parameters in schema;
show parameters in table <table_name>;

更改Objects’s parameter setting

use database sf_tuts;
ALTER DATABASE SF_TUTS set DATA_RETENTION_TIME_IN_DAYS=1;
show parameters in database;

Set up DATA_RETENTION_TIME_IN_DAYS when create Objects

use database sf_tuts;
CREATE TABLE demo (amount number) data_retention_time_in_days=90;
show parameters in table demo;

Change data and Querying Historical Data

#Original data
select * from "SF_TUTS"."PUBLIC"."EMP_BASIC";#Delete one data
DELETE FROM "SF_TUTS"."PUBLIC"."EMP_BASIC" WHERE EMAIL='adavidovitsk@sf_tuts.com';#Look at table again
//SELECT * FROM emp_basic;#Select historical data through given specified statement id
select * from emp_basic before(statement => '018c2845-02cf-95c1-0000-000063a53a75');#Select historical data from a table as of <x> minutes ago
select * from emp_basic at(offset => -60*4);#Selects historical data from a table as of the date and time represented by the specified timestampselect current_timestamp();select * from emp_basic at(timestamp => 'Mon, 13 May 2019 06:00:00 -0700'::timestamp);select * from emp_basic at(timestamp => 'Mon, 13 May 2019 06:00:00'::timestamp)select * from emp_basic at(timestamp => '2019-05-13 22:00:00 +2100'::timestamp_tz);

Cloning Historical Objects

#Clone table
create table restored_table clone emp_basic at(timestamp => 'Mon, 13 May 2019 06:00:00 -0700'::timestamp);
select * from restored_table;
show tables history in sf_tuts.public;
select * from emp_basic;#Clone Schema
create schema restored_schema clone PUBLIC at(offset => -3600);
show schemas history in sf_tuts;#Clone database
create database restored_db clone sf_tuts before(statement => '018c2845-02cf-95c1-0000-000063a53a75');
show databases history;

Dropping Object

#Drop Object
DROP TABLE restored_table;
DROP SCHEMA restored_schema;
DROP DATABASE restored_db;#注意事項
當你使用Drop Object的時候，實際上真正的資料還沒被刪除掉，真正的資料會依據你設定的Retention保留一段時間如果在Create相同名字的Object時，Snowflake是產生一個新版本的Object
（After dropping an object, creating an object with the same name does not restore the object. Instead, it creates a new version of the object. The original, dropped version is still available and can be restored.）

Listing Dropped Objects

show tables history;
show schemas history in sf_tuts;
show databases history;

Restoring Objects

undrop table restored_table;
undrop schema restored_schema;
undrop database restored_db;注意事項
1.Calling UNDROP restores the object to its most recent state before the DROP command was issued.
2.If an object with the same name already exists, UNDROP fails.

Example: Dropping and Restoring a Table Multiple Times

#Prepare 
CREATE OR REPLACE DATABASE mytestdb;CREATE OR REPLACE TABLE loaddata1(
    id NUMBER,
    name STRING
);CREATE OR REPLACE TABLE proddata1(
    id NUMBER,
    name STRING
);

SHOW TABLES HISTORY;
DROP TABLE loaddata1;
SHOW TABLES HISTORY;
create table loaddata1 (id number);
insert into loaddata1 values (1111), (2222), (3333), (4444);
SHOW TABLES HISTORY;
drop table loaddata1;
SHOW TABLES HISTORY;
create table loaddata1 (c1 varchar);
show tables history;
alter table loaddata1 rename to loaddata3;
undrop table loaddata1;
show tables history;
alter table loaddata1 rename to loaddata2;
undrop table loaddata1;
show tables history;