CREATE MATERIALIZED VIEW Syntax in Snowflake
Materialized views helps improve performance by pre-calculating and storing the results.
CREATE [ OR REPLACE ] [ SECURE ] MATERIALIZED VIEW [ IF NOT EXISTS ] <name>
( <column_list> )
[ COMMENT = '<string_literal>' ]
[ CLUSTER BY ( <expr1> [, <expr2> ... ] ) ]
create materialized view mymv
select col1, col2 from mytable;
Few things to note:
- View definitions are not updated if the schema of the underlying source table is changed so that the view definition becomes invalid
- If a source table for a materialized view is dropped, querying the view returns the following error: Materialized View <view_name> is invalid.
- A schema cannot contain a table and view with the same name. CREATE [ MATERIALIZED ] VIEW produces an error if a table with the same name already exists in the schema.
- The select_statement cannot contain a HAVING clause.
- The select_statement cannot contain an ORDER BY clause.
- If you include a CLUSTER BY clause for the materialized view, then you must include the column_list clause.
- To view information about a secure view, you must use the role that owns the view.
- By design, the SHOW VIEWS command does not provide information about secure materialized views. To display information about materialized views, use the following commands: DESCRIBE MATERIALIZED VIEW, SHOW MATERIALIZED VIEWS
Enjoy great content like this and a lot more !
Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds