27 Dec 19 · npack · #Snowflake ·   Bookmark   ×

CREATE MATERIALIZED VIEW Syntax in Snowflake

Materialized views helps improve performance by pre-calculating and storing the results.

Syntax

CREATE [ OR REPLACE ] [ SECURE ] MATERIALIZED VIEW [ IF NOT EXISTS ] <name>
( <column_list> )
[ COMMENT = '<string_literal>' ]
[ CLUSTER BY ( <expr1> [, <expr2> ... ] ) ]
AS <select_statement>

Example:

create materialized view mymv
comment='Test view'
as
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

npack

posted on 27 Dec 19

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

Copied