KEMBAR78
Create Materialized View | PDF | Computers
Scribd
Upload
50 views2 pages

Create Materialized View

The document demonstrates how to create and query a materialized view in Snowflake. It prepares sample data, creates a materialized view on the sample data grouped by year, updates the underlying data, and shows that the materialized view reflects the update.

Uploaded by

Divya Bhatia
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
50 views2 pages

Create Materialized View

The document demonstrates how to create and query a materialized view in Snowflake. It prepares sample data, creates a materialized view on the sample data grouped by year, updates the underlying data, and shows that the materialized view reflects the update.

Uploaded by

Divya Bhatia
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 2

-- Remove caching just to have a fair test -- Part 1

ALTER SESSION SET USE_CACHED_RESULT=FALSE; -- disable global caching


ALTER warehouse compute_wh suspend;
ALTER warehouse compute_wh resume;

-- Prepare table
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

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

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

SHOW MATERIALIZED VIEWS;

-- Query view
SELECT * FROM ORDERS_MV
ORDER BY YEAR;

-- UPDATE or DELETE values


UPDATE ORDERS
SET O_CLERK='Clerk#99900000'
WHERE O_ORDERDATE='1992-01-01'
-- Test updated data --
-- Example statement 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);

-- Query view
SELECT * FROM ORDERS_MV
ORDER BY YEAR;

SHOW MATERIALIZED VIEWS;

You might also like