ALTER MATERIALIZED VIEW¶
Alters a materialized view in the current/specified schema. Supported actions include:
Renaming the materialized view.
Suspending and resuming use and maintenance of the materialized view.
Clustering the materialized view.
Suspending and resuming reclustering of the materialized view.
Dropping clustering of the materialized view.
For more details, see Working with Materialized Views.
- See also:
CREATE MATERIALIZED VIEW , DROP MATERIALIZED VIEW , SHOW MATERIALIZED VIEWS , DESCRIBE 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 formdb_name.schema_name.object_name
orschema_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 viewV1
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:Failure during expansion of view 'MV1': SQL compilation error: Materialized View MV1 is invalid. Invalidation reason: Marked Materialized View as invalid manually.
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;
Cluster a materialized view:
ALTER MATERIALIZED VIEW my_mv CLUSTER BY(i);
Suspend clustering of a materialized view, but not use of the view:
ALTER MATERIALIZED VIEW my_mv SUSPEND RECLUSTER;
Resume clustering of a materialized view:
ALTER MATERIALIZED VIEW my_mv RESUME RECLUSTER;
Suspend all use and automatic maintenance of the specified materialized view:
ALTER MATERIALIZED VIEW my_mv SUSPEND;
Resume all use and automatic maintenance of the specified materialized view:
ALTER MATERIALIZED VIEW my_mv RESUME;
Stop clustering a materialized view:
ALTER MATERIALIZED VIEW my_mv DROP CLUSTERING KEY;
Modify the view to be a secure view:
ALTER MATERIALIZED VIEW mv1 SET SECURE;
Add or replace the comment for a materialized view:
ALTER MATERIALIZED VIEW mv1 SET COMMENT = 'Sample view';