CREATE MATERIALIZED VIEW¶

基于对现有表的查询，在当前/指定的架构中创建新的物化视图，并在视图中填充数据。

For more details, see Working with Materialized Views.

See also:

ALTER MATERIALIZED VIEW , DROP MATERIALIZED VIEW , SHOW MATERIALIZED VIEWS , DESCRIBE MATERIALIZED VIEW

语法

CREATE [ OR REPLACE ] [ SECURE ] [ INTERACTIVE ] MATERIALIZED VIEW [ IF NOT EXISTS ] <name>
  [ COPY GRANTS ]
  ( <column_list> )
  [ <col1> [ WITH ] MASKING POLICY <policy_name> [ USING ( <col1> , <cond_col1> , ... ) ]
           [ WITH ] PROJECTION POLICY <policy_name>
           [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
  [ , <col2> [ ... ] ]
  [ COMMENT = '<string_literal>' ]
  [ [ WITH ] ROW ACCESS POLICY <policy_name> ON ( <col_name> [ , <col_name> ... ] ) ]
  [ [ WITH ] AGGREGATION POLICY <policy_name> [ ENTITY KEY ( <col_name> [ , <col_name> ... ] ) ] ]
  [ CLUSTER BY ( <expr1> [, <expr2> ... ] ) ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
  [ WITH CONTACT ( <purpose> = <contact_name> [ , <purpose> = <contact_name> ... ] ) ]
  AS <select_statement>

必填参数

name

指定视图的标识符；对于在其中创建视图的架构来说，此标识符必须是唯一的。

In addition, the 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.

select_statement

Specifies the query used to create the view. This query serves as the text/definition for the view. This query is displayed in the output of SHOW VIEWS and SHOW MATERIALIZED VIEWS.

There are limitations on the select_statement. For details, see:

• 使用说明.
• Limitations on Creating Materialized Views.

可选参数

column_list

如果不希望视图中的列名与基础表的列名相同，则可以添加一个列的列表，在该列表中指定列名。（无需指定列的数据类型。）

如果添加物化视图的 CLUSTER BY 子句，则必须添加列名列表。

MASKING POLICY = policy_name

Specifies the masking policy to set on a column.

USING ( col_name , cond_col_1 ... )

指定要传递到条件掩码策略 SQL 表达式的实参。

The first column in the list specifies the column for the policy conditions to mask or tokenize the data and must match the column to which the masking policy is set.

附加列指定要评估的列，以确定在对第一列进行查询时是否对查询结果的每行中的数据进行掩码处理或标记化。

If the USING clause is omitted, Snowflake treats the conditional masking policy as a normal masking policy.

PROJECTION POLICY policy_name

Specifies the projection policy to set on a column.

string_literal

指定视图的注释。字符串字面量应放在单引号内。（字符串字面量不应包含单引号，除非它们被转义。）

默认：无值。

INTERACTIVE

Creates an interactive materialized view, which is optimized for low-latency queries on interactive tables. An interactive materialized view must be based on a single interactive table. After you create the interactive materialized view, you must add both the materialized view and its underlying base table to the interactive warehouse.

For more information, see Materialized view support for interactive tables.

默认值：无值（创建标准物化视图）

SECURE

Specifies that the view is secure. For more information about secure views, see Working with Secure Views.

默认：无值（视图不是安全视图）

COPY GRANTS

If you are replacing an existing view by using the OR REPLACE clause, then the replacement view retains the access permissions from the original view. This parameter copies all privileges, except OWNERSHIP, from the existing view to the new view. The new view does not inherit any future grants defined for the object type in the schema. By default, the role that executes the CREATE MATERIALIZED VIEW statement owns the new view.

If the parameter is not included in the CREATE VIEW statement, then the new view does not inherit any explicit access privileges granted on the original view but does inherit any future grants defined for the object type in the schema.

请注意，复制授权的操作在 CREATE VIEW 语句中（即在同一事务中）自动发生。

默认：无值（未复制授权）。

ROW ACCESS POLICY policy_name ON ( col_name [ , col_name ... ] )

Specifies the row access policy to set on the materialized view.

AGGREGATION POLICY policy_name

Specifies the aggregation policy to set on the materialized view.

expr#

指定要对物化视图进行聚类的表达式。通常，每个表达式都是物化视图中列的名称。

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

WITH DATA METRIC FUNCTION dmf_name ON ( col_name [ , col_name ... ] ) [ , dmf_binding ... ]

Associates one or more data metric functions (DMFs) with the materialized view at creation time. The DMF begins running on the schedule configured for the materialized view as soon as it is created.

You can attach multiple DMF bindings by separating them with commas. Each binding accepts the same properties as ALTER TABLE … ADD DATA METRIC FUNCTION, including EXECUTE AS ROLE, ANOMALY_DETECTION, SENSITIVITY, DATA_QUALITY_NOTIFICATION, and EXPECTATION.

For a description of each property, see Data metric function actions.

For usage guidance and examples, see Attach DMFs at creation time.

Default: No value (no DMF is associated with the materialized view)

使用说明

• Creating a materialized view requires CREATE MATERIALIZED VIEW privilege on the schema, and SELECT privilege on the base table. For more information about privileges and materialized views, see Privileges on a Materialized View’s Schema.

• If you specify the CURRENT_DATABASE or CURRENT_SCHEMA function in the definition of the view, the function returns the database or schema that contains the view, not the database or schema in use for the session.

• When you choose a name for the materialized view, note that 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.

• When specifying the select_statement, note the following:

  • 不能指定 HAVING 子句或 ORDER BY 子句。

  • If you include a CLUSTER BY clause for the materialized view, you must include the column_list clause.

  • If you refer to the base table more than once in the select_statement, use the same qualifier for all references for the base table.

    For example, don’t use a mix of base_table, schema.base_table, and database.schema.base_table in the same select_statement. Instead, choose one of these forms (e.g. database.schema.base_table), and use this consistently throughout the select_statement.

  • Do not query stream objects in the SELECT statement. Streams are not designed to serve as source objects for views or materialized views.

• Some column names are not allowed in materialized views. If a column name is not allowed, you can define an alias for the column. For details, see Handling Column Names That Are Not Allowed in Materialized Views.

• If the materialized view queries external tables, you must refresh the file-level metadata for the external tables to reflect changes in the referenced cloud storage location, including new, updated, and removed files.

  You can refresh the metadata for an external table automatically using the event notification service for your cloud storage service or manually using ALTER EXTERNAL TABLE … REFRESH statements.

• Materialized views have a number of other restrictions. For details, see Limitations on Creating Materialized Views and Limitations on Working With Materialized Views.

• 创建交互式物化视图（使用 INTERACTIVE 关键字）时：

  • The materialized view must be based on a single interactive table. You can’t create an interactive materialized view based on a standard table.
  • 与标准物化视图一样，交互式物化视图也不支持连接。
  • After creating the interactive materialized view, you must add both the materialized view and its underlying base table to the interactive warehouse using ALTER WAREHOUSE … ADD TABLES.
  • You can’t use an interactive materialized view as the source for another materialized view or a dynamic table.

  For more information, see Materialized view support for interactive tables.

• View definitions are not updated if the schema of the underlying source table is changed so that the view definition becomes invalid. For example:

  • 从基表创建视图，然后从该基表中删除列。
  • 删除物化视图的基表。

在这些情况下，查询视图将返回一个错误，其中包括视图失效的原因。例如：

Failure during expansion of view 'MV1':
  SQL compilation error: Materialized View MV1 is invalid.
  Invalidation reason: DDL Statement was executed on the base table 'MY_INVENTORY'.
  Marked Materialized View as invalid.

发生这种情况时，您可以执行以下操作：

• If the base table has been dropped and this is within the data retention period for Time Travel, you can undrop the base table to make the materialized view valid again.

• 使用 CREATE OR REPLACE MATERIALIZED VIEW 命令重新创建视图。

• 关于元数据：

  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.

• Using OR REPLACE is the equivalent of using DROP MATERIALIZED VIEW on the existing materialized view and then creating a new view with the same name.

  CREATE OR REPLACE <object> statements are atomic. That is, when an object is replaced, the old object is deleted and the new object is created in a single transaction.

这意味着与 CREATE OR REPLACE MATERIALIZED VIEW 操作并发的任何查询都使用旧的或新的物化视图版本。

• The OR REPLACE and IF NOT EXISTS clauses are mutually exclusive. They can’t both be used in the same statement.
• When creating a materialized view with a masking policy on one or more materialized view columns, or a row access policy added to the materialized view, use the POLICY_CONTEXT function to simulate a query on the column(s) protected by a masking policy and the materialized view protected by a row access policy.

示例

在当前架构中创建一个带注释的物化视图，从表中选择所有行：

Copy codeExpand code block

CREATE MATERIALIZED VIEW mymv
    COMMENT='Test view'
    AS
    SELECT col1, col2 FROM mytable;

Show lessSee more

Scroll to top

基于交互式表创建交互式物化视图，然后将该物化视图及其基表都添加到交互式仓库中：

Copy codeExpand code block

CREATE INTERACTIVE MATERIALIZED VIEW IF NOT EXISTS mv_summary
    AS
    SELECT SUM(quantity) AS total_quantity, SUM(net_paid) AS total_net_paid
    FROM my_interactive_table
    WHERE call_center_id = 52;

ALTER WAREHOUSE interactive_wh ADD TABLES (mv_summary, my_interactive_table);

Show lessSee more

Scroll to top

For more examples, see the examples in Working with Materialized Views.