[Snowflake] Materialized Views

1. Understand materialized 

• 자주 쿼리되고, 처리해야할 시간이 긴 view를 가진 경우(사용자는 안좋은 경험을 갖게됨, 더 많은 컴퓨팅 리소스가 소비됨)
• materialized view를 만들기 위해 SELECT문을 사용
• 결과는 별도의 테이블에 저장되며, 기본 테이블에 따라 자동으로 업데이트됨

 

 

 

2. Using materialized views

• 일반 select문과 view와 materialized view를 비교하는데 공정한 환경을 만들기 위해 글로벌 캐싱 제거

ALTER SESSION SET USE_CACHED_RESULT=FALSE; -- disable global caching
ALTER warehouse compute_wh suspend;
ALTER warehouse compute_wh resume;

 

• 데이터베이스 생성

CREATE OR REPLACE TRANSIENT DATABASE ORDERS;

 

• 스키마 생성

CREATE OR REPLACE SCHEMA TPCH_SF100;

 

• 테이블 생성

CREATE OR REPLACE TABLE TPCH_SF100.ORDERS AS
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.ORDERS;

 

• 테이블 확인

SELECT * FROM ORDERS LIMIT 100;

 

• view 예제 

SELECT
YEAR(O_ORDERDATE) AS YEAR,
MAX(O_COMMENT) AS MAX_COMMENT,
MIN(O_COMMENT) AS MIN_COMMENT,
MAX(O_CLERK) AS MAX_CLERK,
MIN(O_CLERK) AS MIN_CLERK
FROM ORDERS.TPCH_SF100.ORDERS
GROUP BY YEAR(O_ORDERDATE)
ORDER BY YEAR(O_ORDERDATE);

 

• materialized view 생성  

CREATE OR REPLACE MATERIALIZED VIEW ORDERS_MV
AS 
SELECT
YEAR(O_ORDERDATE) AS YEAR,
MAX(O_COMMENT) AS MAX_COMMENT,
MIN(O_COMMENT) AS MIN_COMMENT,
MAX(O_CLERK) AS MAX_CLERK,
MIN(O_CLERK) AS MIN_CLERK
FROM ORDERS.TPCH_SF100.ORDERS
GROUP BY YEAR(O_ORDERDATE);

 

• materialized view 확인

SHOW MATERIALIZED VIEWS;

 

• materialized view를 통한 조회

SELECT * FROM ORDERS_MV
ORDER BY YEAR;

 

3. Refresh materialized views

• 일반 select문과 view와 materialized view를 비교하는데 공정한 환경을 만들기 위해 글로벌 캐싱 제거

ALTER SESSION SET USE_CACHED_RESULT=FALSE; -- disable global caching
ALTER warehouse compute_wh suspend;
ALTER warehouse compute_wh resume;

 

• 데이터베이스 생성

CREATE OR REPLACE TRANSIENT DATABASE ORDERS;

 

• 스키마 생성

CREATE OR REPLACE SCHEMA TPCH_SF100;

 

• 테이블 생성

CREATE OR REPLACE TABLE TPCH_SF100.ORDERS AS
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.ORDERS;

 

• 테이블 확인

SELECT * FROM ORDERS LIMIT 100;

 

• view 예제 

SELECT
YEAR(O_ORDERDATE) AS YEAR,
MAX(O_COMMENT) AS MAX_COMMENT,
MIN(O_COMMENT) AS MIN_COMMENT,
MAX(O_CLERK) AS MAX_CLERK,
MIN(O_CLERK) AS MIN_CLERK
FROM ORDERS.TPCH_SF100.ORDERS
GROUP BY YEAR(O_ORDERDATE)
ORDER BY YEAR(O_ORDERDATE);

 

• materialized view 생성  

CREATE OR REPLACE MATERIALIZED VIEW ORDERS_MV
AS 
SELECT
YEAR(O_ORDERDATE) AS YEAR,
MAX(O_COMMENT) AS MAX_COMMENT,
MIN(O_COMMENT) AS MIN_COMMENT,
MAX(O_CLERK) AS MAX_CLERK,
MIN(O_CLERK) AS MIN_CLERK
FROM ORDERS.TPCH_SF100.ORDERS
GROUP BY YEAR(O_ORDERDATE);

 

• materialized view 확인

SHOW MATERIALIZED VIEWS;

 

• materialized view를 통한 조회

SELECT * FROM ORDERS_MV
ORDER BY YEAR;

 

• 데이터 변경

UPDATE ORDERS
SET O_CLERK='Clerk#99900000' 
WHERE O_ORDERDATE='1992-01-01';

 

• materialized view를 통한 조회

SELECT * FROM ORDERS_MV
ORDER BY YEAR;

 

4. Maintenance costs

• materialized view 확인

SHOW MATERIALIZED VIEWS;

 

• materialized view history 확인

select * from table(information_schema.materialized_view_refresh_history());

 

5. When to use materialized views

• view를 사용하는데, 시간이 오래 걸리고, 같은 view를 자주 사용하는 경우
• 기본 데이터가 자주 변경되지 않으며, 다소 불규칙적일 때
• 유지보수 비용 및 task나 streams를 대안으로 활용함을 고려

 

 

 

6. Limitations

• enterprise edition 이상 버전에서만 사용 가
• materialized view 안의 select문에 join이 포함되어 있으면 작동하지 않음
• UDFs 사용 불가
• HAVING 절 사용불가
• ORDER BY 절 사용불가
• LIMIT 사용불가
• 집계함수의 양이 제한되어 있음

APPROX_COUNT_DISTINCT
AVG
BITAND_AGG
BITOR_AGG
BITXOR_AGG
COUNT
MIN
MAX
STDDEV
STDDEV_POP
STDDEV_SAMP
SUM
VARIANCE(VARIANCE_SAMP, VAR_SAMP)
VARIANCE_POP

 

 

 

 

 

 

 

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