CREATE VIEW¶

Syntax¶

CREATE [ OR REPLACE ] [ SECURE ] [ { [ { LOCAL | GLOBAL } ] TEMP | TEMPORARY | VOLATILE } ] [ RECURSIVE ] VIEW [ IF NOT EXISTS ] <name>
  [ ( <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> [ ... ] ]
  [ [ WITH ] ROW ACCESS POLICY <policy_name> ON ( <col_name> [ , <col_name> ... ] ) ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
  [ CHANGE_TRACKING = { TRUE | FALSE } ]
  [ COPY GRANTS ]
  [ 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> ... ] ) ] ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
  AS <select_statement>

Variant syntax¶

CREATE OR ALTER [ SECURE ] [ { [ { LOCAL | GLOBAL } ] TEMP | TEMPORARY | VOLATILE } ] [ RECURSIVE ] VIEW <name>
  [ ( <column_list> ) ]
  [ CHANGE_TRACKING =  { TRUE | FALSE } ]
  [ COMMENT = '<string_literal>' ]
  AS <select_statement>

Required parameters¶

name

Specifies the identifier for the view; must be unique for the schema in which the view is created.

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. Can be on one or more source tables or any other valid SELECT statement. This query serves as the text/definition for the view and is displayed in the SHOW VIEWS output and the VIEWS Information Schema view.

Optional parameters¶

SECURE

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

Default: No value (view is not secure)

{ [ { LOCAL | GLOBAL } ] TEMP | TEMPORARY | VOLATILE }

Specifies that the view persists only for the duration of the session that you created it in. A temporary view and all its contents are dropped at the end of the session.

The synonyms and abbreviations for TEMPORARY (e.g. GLOBAL TEMPORARY) are provided for compatibility with other databases (e.g. to prevent errors when migrating CREATE VIEW statements). Views created with any of these keywords appear and behave identically to a view created with the TEMPORARY keyword.

Default: No value. If a view is not declared as TEMPORARY, the view is permanent.

If you want to avoid unexpected conflicts, avoid naming temporary views after views that already exist in the schema.

If you created a temporary view with the same name as another view in the schema, all queries and operations used on the view only affect the temporary view in the session, until you drop the temporary view. If you drop the view, you drop the temporary view, and not the view that already exists in the schema.

RECURSIVE

Specifies that the view can refer to itself using recursive syntax without necessarily using a CTE (common table expression). For more information about recursive views in general, and the RECURSIVE keyword in particular, see Recursive Views (Non-materialized Views Only) and the recursive view examples below.

Default: No value (view is not recursive, or is recursive only by using a CTE)

column_list

If you want to change the name of a column or add a comment to a column in the new view, include a column list that specifies the column names and (if needed) comments about the columns. (You do not need to specify the data types of the columns.)

If any of the columns in the view are based on expressions (not just simple column names), then you must supply a column name for each column in the view. For example, the column names are required in the following case:

CREATE VIEW v1 (pre_tax_profit, taxes, after_tax_profit) AS
    SELECT revenue - cost, (revenue - cost) * tax_rate, (revenue - cost) * (1.0 - tax_rate)
    FROM table1;

Copy

You can specify an optional comment for each column. For example:

CREATE VIEW v1 (pre_tax_profit COMMENT 'revenue minus cost',
                taxes COMMENT 'assumes taxes are a fixed percentage of profit',
                after_tax_profit)
    AS
    SELECT revenue - cost, (revenue - cost) * tax_rate, (revenue - cost) * (1.0 - tax_rate)
    FROM table1;

Copy

Comments are particularly helpful when column names are cryptic.

To view comments, use DESCRIBE VIEW.

MASKING POLICY = policy_name

Specifies the masking policy to set on a column.

USING ( col_name , cond_col_1 ... )

Specifies the arguments to pass into the conditional masking policy SQL expression.

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.

The additional columns specify the columns to evaluate to determine whether to mask or tokenize the data in each row of the query result when a query is made on the first column.

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.

CHANGE_TRACKING = { TRUE | FALSE }

Specifies whether to enable change tracking on the view.

• TRUE enables change tracking on the view. This setting adds a pair of hidden columns to the source table and begins storing change tracking metadata in the columns. These columns consume a small amount of storage.

  The change-tracking metadata can be queried using the CHANGES clause for SELECT statements, or by creating and querying one or more streams on the table.

• FALSE does not enable change tracking on the view.

COPY GRANTS

Retains the access permissions from the original view when a new view is created using the OR REPLACE clause.

The 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 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.

Note that the operation to copy grants occurs atomically with the CREATE VIEW statement (i.e. within the same transaction).

Default: No value (grants are not copied)

COMMENT = 'string_literal'

Specifies a comment for the view.

Default: No value

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

Specifies the row access policy to set on a view.

AGGREGATION POLICY policy_name [ ENTITY KEY ( col_name [ , col_name ... ] ) ]

Specifies the aggregation policy to set on a view.

Use the optional ENTITY KEY parameter to define which columns uniquely identity an entity within the view. For more information, see Implementing entity-level privacy with aggregation policies.

TAG ( tag_name = 'tag_value' [ , tag_name = 'tag_value' , ... ] )

Specifies the tag name and the tag string value.

The tag value is always a string, and the maximum number of characters for the tag value is 256.

For information about specifying tags in a statement, see Tag quotas for objects and columns.

Access control requirements¶

A role used to execute this SQL command must have the following privileges at a minimum:

The USAGE privilege on the parent database and schema are required to perform operations on any object in a schema.

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

General usage notes¶

• A view definition can include an ORDER BY clause (e.g. create view v1 as select * from t1 ORDER BY column1). However, Snowflake recommends excluding the ORDER BY clause from most view definitions. If the view is used in contexts that don’t benefit from sorting, then the ORDER BY clause adds unnecessary costs. For example, when the view is used in a join, and the join column is not the same as the ORDER BY column, the extra cost to sort the view’s results is typically wasted. If you need to sort the query results, it’s usually more efficient to specify ORDER BY in the query that uses the view, rather than in the view itself.

• 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.

• The definition for a view is limited to 95KB.

• Nesting levels are limited to a maximum of 20. An attempt to create a view that is nested more than 20 times will fail.

• View definitions are not dynamic. A view is not automatically updated if the underlying sources are modified such that they no longer match the view definition, particularly when columns are dropped. For example:

  • A view is created referencing a specific column in a source table, and the column is subsequently dropped from the table.

  • A view is created using SELECT * from a table, and changes are made to the columns in the table, such as:

    • A column is dropped.

    • A column is added.

    • The column order changes.

  In these scenarios, querying the view returns a column-related error.

• If a source table for a view is dropped, querying the view returns an object does not exist error.

• A schema cannot contain a table and view with the same name. A CREATE VIEW statement produces an error if a table with the same name already exists in the schema.

• When a view is created, unqualified references to tables and other database objects are resolved in the view’s schema, not in the session’s current schema. Similarly, objects that are partially qualified (i.e. schema.object) are resolved in the view’s database, not in the session’s current database.

  The SEARCH_PATH session parameter (if present) is ignored.

• Using OR REPLACE is the equivalent of using DROP VIEW on the existing 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.

  This means that any queries concurrent with the CREATE OR REPLACE VIEW operation use either the old or new view version.

  Recreating or swapping a view drops its change data, which makes any stream on the view stale. A stale stream is unreadable.

• Using COPY GRANTS:

  • Data sharing:

    • If the existing secure view was shared to another account, the replacement view is also shared.

    • If the existing secure view was shared with your account as a data consumer, and access was further granted to other roles in the account (using GRANT IMPORTED PRIVILEGES on the parent database), access is also granted to the replacement view.

  • The SHOW GRANTS output for the replacement view lists the grantee for the copied privileges as the role that executed the CREATE VIEW statement, with the current timestamp when the statement was executed.

• When you create a view and then grant privileges on that view to a role, the role can use the view even if the role does not have privileges on the underlying table(s) that the view accesses. This means that you can create a view to give a role access to only a subset of a table. For example, you can create a view that accesses medical billing information but not medical diagnosis information in the same table. Then you can grant privileges on that view to the “accountant” role so that the accountants can look at the billing information without seeing the patient’s diagnosis.

• By design, the SHOW VIEWS command does not provide information about secure views. To view information about a secure view, you must use the VIEWS view in the Information Schema and you must use the role that owns the view.

• A recursive view must provide a column name list.

• When defining recursive views, prevent infinite recursion. The WHERE clause in the recursive view definition should enable the recursion to stop eventually, typically by running out of data after processing the last level of a hierarchy of data.

• 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.

• When creating a view with a masking policy on one or more view columns, or a row access policy added to the view, use the POLICY_CONTEXT function to simulate a query on the column(s) protected by a masking policy and the view protected by a row access policy.

• Do not create views with streams as source objects unless the same role owns both the view and source streams (i.e. the same role, or a lower role in a role hierarchy, has the OWNERSHIP privilege on the view and source streams). Instead, create views that have the objects to track as the source objects. Then, create streams on those views. For more information, see Streams on Views.

Porting notes¶

• Some vendors support the FORCE keyword:

  CREATE OR REPLACE FORCE VIEW ...
  

  Copy

  Snowflake accepts the FORCE keyword, but does not support it. In other words, you do not get a syntax error if you use this keyword, but using FORCE does not force the creation of a view if the underlying database objects (table(s) or view(s)) do not already exist. Attempting to create a view of a non-existent table or view results in an error message even if the FORCE keyword is used.

• When looking up the tables in a view, some vendors search for unqualified table names in the active schema; Snowflake searches for unqualified table names in the same schema as the view. When porting to Snowflake, consider updating views to use fully-qualified table names.

CREATE OR ALTER VIEW usage notes¶

• All limitations of the ALTER VIEW command apply.

• This command doesn not support the following:

  • Changing the definition of a view.

  • Renaming a view using the RENAME TO parameter.

  • Adding or changing tags and policies. Any existing tags and policies are preserved.

  • Converting a TEMPORARY view into a permanent view, or vice versa.

Examples¶

CREATE OR ALTER TABLE my_table(a INT);

CREATE OR ALTER VIEW v2(one)
  AS SELECT a FROM my_table;

CREATE OR ALTER VIEW v2(one)
  COMMENT = 'fff'
  CHANGE_TRACKING = true
  AS SELECT a FROM my_table;

CREATE OR ALTER VIEW v2(one COMMENT 'bar')
  COMMENT = 'foo'
  AS SELECT a FROM my_table;

CREATE OR ALTER VIEW v2(one COMMENT 'bar')
  CHANGE_TRACKING = true
  AS SELECT a FROM my_table;