[Snowflake] Types of tables

1. Different table types

• Permanent : 영구테이블, default 값, 가장 일반적인 유형, time travel 보존기간 포함(0 - 90일), Fail Safe 포함
• Transient : 일시테이블, time travel 보존기간 포함(0 - 1일), Fail Safe 미포함
• Temporary : 임시테이블, time travel 보존기간 포함(0 - 1일), Fail Safe 미포함, 세션에만 존재(세션이 종료되면 테이블도 없어짐)

 

 

2. Permanent tables & databases

• 데이터 베이스 전환

USE OUR_FIRST_DB;

 

• 테이블 생성

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

 

•   데이터 베이스 생성

CREATE OR REPLACE DATABASE PDB;

 

• 데이터베이스 확인 : 영구테이블인 경우 options 컬럼이 null, retention_time 컬럼이 1

SHOW DATABASES;

 

• 테이블 확인

SHOW TABLES;

 

• 테이블 메트릭스 확인(자세한 정보)

SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS;

 

• 정렬된 테이블 매트릭스 확인

SELECT  ID, 
        TABLE_NAME, 
TABLE_SCHEMA,
        TABLE_CATALOG,
ACTIVE_BYTES / (1024*1024*1024) AS ACTIVE_STORAGE_USED_GB,
TIME_TRAVEL_BYTES / (1024*1024*1024) AS TIME_TRAVEL_STORAGE_USED_GB,
FAILSAFE_BYTES / (1024*1024*1024) AS FAILSAFE_STORAGE_USED_GB,
        IS_TRANSIENT,
        DELETED,
        TABLE_CREATED,
        TABLE_DROPPED,
        TABLE_ENTERED_FAILSAFE
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS
--WHERE TABLE_CATALOG ='PDB'
WHERE TABLE_DROPPED is not null
ORDER BY FAILSAFE_BYTES DESC;

 

3. Transient tables & databases

• 데이터베이스 생성

CREATE OR REPLACE DATABASE TDB;

 

• 테이블 생성(transient)

CREATE OR REPLACE TRANSIENT TABLE TDB.public.customers_transient (
   id int,
   first_name string,
  last_name string,
  email string,
  gender string,
  Job string,
  Phone string);

 

• 데이터 삽입

INSERT INTO TDB.public.customers_transient
SELECT t1.* FROM OUR_FIRST_DB.public.customers t1
CROSS JOIN (SELECT * FROM OUR_FIRST_DB.public.customers) t2;

 

• 테이블 확인

SHOW TABLES;

 

• 테이블 매트릭스 확인

SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS;

 

• 정렬된 테이블 매트릭스 확인

SELECT  ID, 
        TABLE_NAME, 
TABLE_SCHEMA,
        TABLE_CATALOG,
ACTIVE_BYTES,
TIME_TRAVEL_BYTES / (1024*1024*1024) AS TIME_TRAVEL_STORAGE_USED_GB,
FAILSAFE_BYTES / (1024*1024*1024) AS FAILSAFE_STORAGE_USED_GB,
        IS_TRANSIENT,
        DELETED,
        TABLE_CREATED,
        TABLE_DROPPED,
        TABLE_ENTERED_FAILSAFE
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS
WHERE TABLE_CATALOG ='TDB'
ORDER BY TABLE_CREATED DESC;

 

• retention time 0으로 변경

ALTER TABLE TDB.public.customers_transient
SET DATA_RETENTION_TIME_IN_DAYS  = 0;

 

• 테이블 삭제

DROP TABLE TDB.public.customers_transient;

 

• 테이블 복원

UNDROP TABLE TDB.public.customers_transient;

 

• 테이블 확인

SHOW TABLES;

 

• 스키마 생성(transient)

CREATE OR REPLACE TRANSIENT SCHEMA TRANSIENT_SCHEMA;

 

• 스키마 확인

SHOW SCHEMAS;

 

• 테이블 생성

CREATE OR REPLACE TABLE TDB.TRANSIENT_SCHEMA.new_table (
   id int,
   first_name string,
  last_name string,
  email string,
  gender string,
  Job string,
  Phone string);

 

•   retention time 2로 변경 - 파라미터 값이 적절하지 않다는 오류 발생(transient 이기 때문)

ALTER TABLE TDB.TRANSIENT_SCHEMA.new_table
SET DATA_RETENTION_TIME_IN_DAYS  = 2;

 

• 테이블 확인

SHOW TABLES;

 

4. Temporary tables & databases

• 데이터베이스 변경

USE DATABASE PDB;

 

• 테이블 생성(permanent)

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

 

• 데이터 삽입

INSERT INTO PDB.public.customers
SELECT t1.* FROM OUR_FIRST_DB.public.customers t1;

 

• 데이터 확인

SELECT * FROM PDB.public.customers;

 

• 테이블 생성(temporary) - permanent테이블과 동일한 이름

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

 

• 테이블 확인 - 동일한 이름의 temporary 테이블을 생성했기 때문에 현재 세션에서는 테이블이 비어있게 됨

SELECT * FROM PDB.public.customers;

 

• 테이블 생성(temporary) - 새로운 이름

CREATE OR REPLACE TEMPORARY TABLE PDB.public.temp_table (
   id int,
   first_name string,
  last_name string,
  email string,
  gender string,
  Job string,
  Phone string);

 

• 데이터 삽입

INSERT INTO PDB.public.temp_table;

 

• 데이터 확인

SELECT * FROM PDB.public.customers;

 

• 데이터 확인

SELECT * FROM PDB.public.temp_table;

 

• 테이블 확인

SHOW TABLES;

 

 

 

 

 

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