ALTER MATERIALIZED VIEW¶

Syntax¶

ALTER MATERIALIZED VIEW <name>
  {
  RENAME TO <new_name>                     |
  CLUSTER BY ( <expr1> [, <expr2> ... ] )  |
  DROP CLUSTERING KEY                      |
  SUSPEND RECLUSTER                        |
  RESUME RECLUSTER                         |
  SUSPEND                                  |
  RESUME                                   |
  SET {
    [ SECURE ]
    [ COMMENT = '<comment>' ]
    }                                      |
  UNSET {
    SECURE                                 |
    COMMENT
    }
  }

Parameters¶

name

Specifies the identifier of the materialized view to alter.

RENAME TO new_name

This option allows you to rename a materialized view.

The new identifier must be unique for the schema in which the view is created. The new identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (e.g. "My object"). Identifiers enclosed in double quotes are also case-sensitive. For more details, see Identifier Requirements.

You can move the object to a different database and/or schema while optionally renaming the object. To do so, specify a qualified new_name value that includes the new database and/or schema name in the form db_name.schema_name.object_name or schema_name.object_name, respectively.

Note

• The destination database and/or schema must already exist. In addition, an object with the same name cannot already exist in the new location; otherwise, the statement returns an error.

• Moving an object to a managed access schema is prohibited unless the object owner (i.e. the role that has the OWNERSHIP privilege on the object) also owns the target schema.

Note that renaming a materialized view does not update references to that view. For example, if you create a view named V1 on top of a materialized view, and then you rename the materialized view, the definition of view V1 becomes out of date.

CLUSTER BY expr#

This command clusters the materialized view. Clustering re-orders the rows in the materialized view to increase performance for queries that filter based on the clustering key expressions.

The expr# specifies an expression on which to cluster the materialized view. Typically, each expression is the name of a column in the materialized view.

For more information about clustering materialized views, see: Materialized Views and Clustering. For more information about clustering in general, see: What is Data Clustering?.

DROP CLUSTERING KEY

This command drops the clustering of the materialized view.

SUSPEND RECLUSTER

The SUSPEND RECLUSTER option suspends re-clustering of the materialized view. For more information about clustering materialized views, see Materialized Views and Clustering.

RESUME RECLUSTER

The RESUME RECLUSTER option resumes reclustering of the materialized view.

SUSPEND

The SUSPEND option suspends the maintenance (updates) and use of the materialized view. While the view is suspended, updates to the base table are not propagated to the materialized view. The materialized view itself is also inaccessible; if you attempt to use it, you get an error message similar to:

SQL compilation error: Failure during expansion of view ‘MV1’: SQL compilation error: Materialized View MV1 is invalid.

If you suspend a clustered materialized view, suspending the view implicitly suspends reclustering of that view.

RESUME

The RESUME option allows you to resume using the materialized view. It also resumes maintenance of the materialized view. If the view is clustered, it also implicitly resumes reclustering of that view.

SET ...

Specifies the property to set for the materialized view:

SECURE

This option turns the view into a secure view. For more information about secure views, see Working with Secure Views.

COMMENT = 'string_literal'

This option sets a comment for the materialized view. The comment has no effect on the behavior of the view, but can provide useful information to people who use or maintain the view.

UNSET ...

Specifies the property to unset for the materialized view:

• SECURE

• TAG tag_name [ , tag_name ... ]

• COMMENT

Usage Notes¶

• Use the ALTER VIEW command to set/unset a masking policy, row access policy, or tag on/from a materialized view.

• Regarding metadata:

  Attention

  Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata Fields in Snowflake.

Examples¶

Rename a materialized view:

ALTER MATERIALIZED VIEW table1_MV RENAME TO my_mv;

Copy

Cluster a materialized view:

ALTER MATERIALIZED VIEW my_mv CLUSTER BY(i);

Copy

Suspend clustering of a materialized view, but not use of the view:

ALTER MATERIALIZED VIEW my_mv SUSPEND RECLUSTER;

Copy

Resume clustering of a materialized view:

ALTER MATERIALIZED VIEW my_mv RESUME RECLUSTER;

Copy

Suspend all use and automatic maintenance of the specified materialized view:

ALTER MATERIALIZED VIEW my_mv SUSPEND;

Copy

Resume all use and automatic maintenance of the specified materialized view:

ALTER MATERIALIZED VIEW my_mv RESUME;

Copy

Stop clustering a materialized view:

ALTER MATERIALIZED VIEW my_mv DROP CLUSTERING KEY;

Copy

Modify the view to be a secure view:

ALTER MATERIALIZED VIEW mv1 SET SECURE;

Copy

Add or replace the comment for a materialized view:

ALTER MATERIALIZED VIEW mv1 SET COMMENT = 'Sample view';

Copy