[Snowflake] Time Travel

1. Using time travel

• 테이블 생성

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);

 

• file format 생성

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;

 

• 스테이지 확인

LIST @MANAGE_DB.external_stages.time_travel_stage;

 

• 데이터 로드

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;

example 1 : time travel 함수를 사용하여 이전 데이터로 돌아가기

• 데이터 변경

UPDATE OUR_FIRST_DB.public.test
SET FIRST_NAME = 'Joyen';

 

• Time travel 함수 사용해서 이전 시간의 데이터로 돌아가기 1(Enterprise 이상 에디션 : 90일 time travel, Standard 에디션 : 1일 time travel

SELECT * FROM OUR_FIRST_DB.public.test at (OFFSET => -60*1.5);

 

• Time travel 함수 사용해서 특정 시간의 데이터로 돌아가기 2

SELECT * FROM OUR_FIRST_DB.public.test before (timestamp => '2021-04-15 17:47:50.581'::timestamp);

example 2 : 타임스탬프를 기록해서 해당 시간의 데이터로 돌아가기

• 테이블 생성

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);

 

• 데이터 로드

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;

 

• 세션 시간대 UTC로 통일

ALTER SESSION SET TIMEZONE ='UTC';

 

• 현재 타임스탬프 보기

SELECT CURRENT_TIMESTAMP;

 

• 데이터 변경

UPDATE OUR_FIRST_DB.public.test
SET Job = 'Data Scientist';

 

• 타임스탬프로 해당 시간의 데이터로 돌아가기 

SELECT * FROM OUR_FIRST_DB.public.test before (timestamp => '2023-02-08T01:30:32.69Z'::timestamp);

example 3 : Query ID를 사용해서 이전 데이터로 돌아가기

• 테이블 생성

CREATE OR REPLACE TABLE OUR_FIRST_DB.public.test (
   id int,
   first_name string,
  last_name string,
  email string,
  gender string,
  Phone string,
  Job string);

 

• 데이터 로드

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;

 

• 데이터 변경 (해당 Query ID 복사)

UPDATE OUR_FIRST_DB.public.test
SET EMAIL = null;

 

• Query ID를 사용해서 해당 쿼리 이전 데이터로 돌아가기

SELECT * FROM OUR_FIRST_DB.public.test before (statement => '01aa2ee4-0000-191b-0000-455500041116');

 

2. Restoring data

• 테이블 생성

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);

 

• 데이터 로드

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;

 

• 데이터 변경 1

UPDATE OUR_FIRST_DB.public.test
SET LAST_NAME = 'Tyson';

 

• 데이터 변경 2

UPDATE OUR_FIRST_DB.public.test
SET JOB = 'Data Analyst';

 

• time travel로 이전 데이터로 이동

SELECT * FROM OUR_FIRST_DB.public.test before (statement => '019b9eea-0500-845a-0043-4d830007402a');

example 1 : 좋지 않은 방법(두 번의 데이터 변경이 모두 반영되지 않음)

• 테이블 생성

CREATE OR REPLACE TABLE OUR_FIRST_DB.public.test as
SELECT * FROM OUR_FIRST_DB.public.test before (statement => '019b9eea-0500-845a-0043-4d830007402a');

 

• 데이터 확인

SELECT * FROM OUR_FIRST_DB.public.test;

 

• 테이블 생성

CREATE OR REPLACE TABLE OUR_FIRST_DB.public.test as
SELECT * FROM OUR_FIRST_DB.public.test before (statement => '019b9eea-0500-8473-0043-4d830007307a');

example 2 : 좋은 방법 (backup 테이블을 생성 후 기존 테이블을 지운 뒤 INSERT)

• backup 테이블 생성

CREATE OR REPLACE TABLE OUR_FIRST_DB.public.test_backup as
SELECT * FROM OUR_FIRST_DB.public.test before (statement => '019b9ef0-0500-8473-0043-4d830007309a');

 

• 테이블 삭제

TRUNCATE OUR_FIRST_DB.public.test;

 

• backup 테이블의 데이터를 새로운 테이블에 삽입

INSERT INTO OUR_FIRST_DB.public.test
SELECT * FROM OUR_FIRST_DB.public.test_backup;

 

• 데이터 확인

SELECT * FROM OUR_FIRST_DB.public.test;

 

3. UNDROP tables

• 스테이지 생성

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;

 

• 테이블 생성

CREATE OR REPLACE TABLE OUR_FIRST_DB.public.customers (
   id int,
   first_name string,
  last_name string,
  email string,
  gender string,
  Job string,
  Phone string);

 

• 데이터 로드

COPY INTO OUR_FIRST_DB.public.customers
from @MANAGE_DB.external_stages.time_travel_stage
files = ('customers.csv');

 

• 데이터 확인

SELECT * FROM OUR_FIRST_DB.public.customers;

 

 

• 데이터 삭제

DROP TABLE OUR_FIRST_DB.public.customers;

 

 

• 데이터 확인

SELECT * FROM OUR_FIRST_DB.public.customers;

 

 

• 데이터 복원

UNDROP TABLE OUR_FIRST_DB.public.customers;

 

 

• 스키마 삭제

DROP SCHEMA OUR_FIRST_DB.public;

 

 

• 데이터 확인

SELECT * FROM OUR_FIRST_DB.public.customers;

 

 

• 스키마 복원

UNDROP SCHEMA OUR_FIRST_DB.public;

 

 

• 데이터베이스 삭제

DROP DATABASE OUR_FIRST_DB;

 

 

• 데이터 확인

SELECT * FROM OUR_FIRST_DB.public.customers;

 

• 데이터베이스 복원

UNDROP DATABASE OUR_FIRST_DB;

 

 

• 데이터 변경 1

UPDATE OUR_FIRST_DB.public.customers
SET LAST_NAME = 'Tyson';

 

 

• 데이터 변경 2

UPDATE OUR_FIRST_DB.public.customers
SET JOB = 'Data Analyst';

 

 

• Query ID(데이터 변경 1)로 해당 쿼리 이전의 데이터로 이동 : 이 자체는 테이블을 삭제하고 다시 만드는 행위임

CREATE OR REPLACE TABLE OUR_FIRST_DB.public.customers as
SELECT * FROM OUR_FIRST_DB.public.customers before (statement => '01aa2fc3-0000-1921-0000-45550004233a');

 

 

• 데이터 확인

SELECT * FROM OUR_FIRST_DB.public.customers;

 

 

• 테이블 복구(테이블을 삭제하고 다시 만드는 행위라면 UNDROP이 정상적으로 실행되어야 할 것 같지만 오류 발생)

UNDROP table OUR_FIRST_DB.public.customers;

 

 

• 테이블의 이름을 변경

ALTER TABLE OUR_FIRST_DB.public.customers
RENAME TO OUR_FIRST_DB.public.customers_wrong;

 

 

• 테이블 복구

UNDROP table OUR_FIRST_DB.public.customers;

 

 

• time travel 실행 확인

SELECT * FROM OUR_FIRST_DB.public.customers before (statement => '01aa2fc3-0000-1921-0000-45550004233a');

 

 

• 테이블 확인

DESC table OUR_FIRST_DB.public.customers;

 

4. Retention time 

데이터를 보존하는 기간(보존기간이 0이라면, time travel 기능을 사용할 수 없음)

• Standard : 1일 default = 1

 

5. Time travel cost

• 일별로 그룹화된 사용 스토리지 확인

SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.STORAGE_USAGE ORDER BY USAGE_DATE DESC;

 

 

• 조금 더 자세한 정보를 알 수 있는 일별로 그룹화된 사용 스토리지 확인

SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS;

 

• time travel 스토리지 사용량 확인

SELECT  ID, 
TABLE_NAME, 
TABLE_SCHEMA,
        TABLE_CATALOG,
ACTIVE_BYTES / (1024*1024*1024) AS STORAGE_USED_GB,
TIME_TRAVEL_BYTES / (1024*1024*1024) AS TIME_TRAVEL_STORAGE_USED_GB
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS
ORDER BY STORAGE_USED_GB DESC,TIME_TRAVEL_STORAGE_USED_GB DESC;

 

 

 

 

 

Udemy의 'Snowflake - The Complete Masterclass (2023 Edition)'를 공부한 내용을 바탕으로 작성하였습니다.