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)'를 공부한 내용을 바탕으로 작성하였습니다.
'Snowflake > Snowflake - The Complete Masterclass' 카테고리의 다른 글
[Snowflake] Types of tables (0) | 2023.02.09 |
---|---|
[Snowflake] Fail Safe (0) | 2023.02.08 |
[Snowflake] Snowpipe (0) | 2023.02.07 |
[Snowflake] Loading from AWS (0) | 2023.02.06 |
[Snowflake] Performance optimization (0) | 2023.02.03 |