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

nVector

posted on 27 Dec 19