CREATE VIEW | Snowflake Documentation (original) (raw)

Creates a new view in the current/specified schema, based on a query of one or more existing tables (or any other valid query expression).

This command supports the following variants:

See also:

ALTER VIEW , DROP VIEW , SHOW VIEWS , DESCRIBE VIEW

CREATE OR ALTER

Syntax

CREATE [ OR REPLACE ] [ SECURE ] [ { [ { LOCAL | GLOBAL } ] TEMP | TEMPORARY | VOLATILE } ] [ RECURSIVE ] VIEW [ IF NOT EXISTS ] [ ( ) ] [ [ WITH ] MASKING POLICY [ USING ( , , ... ) ] [ WITH ] PROJECTION POLICY [ WITH ] TAG ( = '' [ , = '' , ... ] ) ] [ , [ ... ] ] [ [ WITH ] ROW ACCESS POLICY ON ( [ , ... ] ) ] [ [ WITH ] TAG ( = '' [ , = '' , ... ] ) ] [ CHANGE_TRACKING = { TRUE | FALSE } ] [ COPY GRANTS ] [ COMMENT = '' ] [ [ WITH ] ROW ACCESS POLICY ON ( [ , ... ] ) ] [ [ WITH ] AGGREGATION POLICY [ ENTITY KEY ( [ , ... ] ) ] ] [ [ WITH ] JOIN POLICY [ ALLOWED JOIN KEYS ( [ , ... ] ) ] ] [ [ WITH ] TAG ( = '' [ , = '' , ... ] ) ] AS

Variant syntax

CREATE OR ALTER VIEW

Creates a new view if it doesn’t already exist, or updates the properties of an existing view to match those defined in the statement. A CREATE OR ALTER VIEW statement follows the syntax rules of a CREATE VIEW statement and has the same limitations as anALTER VIEW statement.

The CREATE OR ALTER VIEW command doesn’t support changing a view definition once a view is created. This limitation is inherited from the ALTER VIEW command.

The following modifications are supported:

For more information, see CREATE OR ALTER VIEW usage notes and CREATE OR ALTER .

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

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.

_selectstatement_

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 theVIEWS 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)

_columnlist_

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;

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;

Comments are particularly helpful when column names are cryptic.

To view comments, use DESCRIBE VIEW.

MASKING POLICY = _policyname_

Specifies the masking policy to set on a column.

USING ( _colname_ , _condcol1_ ... )

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 normalmasking policy.

PROJECTION POLICY _policyname_

Specifies the projection policy to set on a column.

CHANGE_TRACKING = { TRUE | FALSE }

Specifies whether to 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 notinherit 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 = '_stringliteral_'

Specifies a comment for the view.

Default: No value

ROW ACCESS POLICY _policyname_ ON ( _colname_ [ , _colname_ ... ] )

Specifies the row access policy to set on a view.

AGGREGATION POLICY _policyname_ [ ENTITY KEY ( _colname_ [ , _colname_ ... ] ) ]

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, seeImplementing entity-level privacy with aggregation policies.

JOIN POLICY _policyname_ [ ALLOWED JOIN KEYS ( _colname_ [ , ... ] ) ]

Specifies the join policy to set on a view.

Use the optional ALLOWED JOIN KEYS parameter to define which columns are allowed to be used as joining columns when this policy is in effect. For more information, see Join policies.

This parameter is not supported by the CREATE OR ALTER variant syntax.

TAG ( _tagname_ = '_tagvalue_' [ , _tagname_ = '_tagvalue_' , ... ] )

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 operation must have the followingprivileges at a minimum:

Privilege Object Notes
CREATE VIEW Schema Required to create a new view.
SELECT Table, external table, view Required on any tables and/or views queried in the view definition.
APPLY Masking policy, row access policy, tag Required only when applying a masking policy, row access policy, object tags, or any combination of thesegovernance features when creating views.
OWNERSHIP View A role must be granted or inherit the OWNERSHIP privilege on the object to create a temporary object that has the same name as the object that already exists in the schema. Required to execute a CREATE OR ALTER VIEW statement for an existing view. OWNERSHIP is a special privilege on an object that is automatically granted to the role that created the object, but can also be transferred using the GRANT OWNERSHIP command to a different role by the owning role (or any role with the MANAGE GRANTS privilege). Note that in a managed access schema, only the schema owner (i.e. the role with the OWNERSHIP privilege on the schema) or a role with the MANAGE GRANTS privilege can grant or revoke privileges on objects in the schema, including future grants.

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 onsecurable objects, see Overview of Access Control.

General usage notes

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

Porting notes

CREATE OR ALTER VIEW usage notes

Examples

Basic examples

Create a view in the current schema, with a comment, that selects all the rows from a table:

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

SHOW VIEWS;

+---------------------------------+-------------------+----------+---------------+-------------+----------+-----------+--------------------------------------------------------------------------+ | created_on | name | reserved | database_name | schema_name | owner | comment | text | |---------------------------------+-------------------+----------+---------------+-------------+----------+-----------+--------------------------------------------------------------------------| | Thu, 19 Jan 2017 15:00:37 -0800 | MYVIEW | | MYTEST1 | PUBLIC | SYSADMIN | Test view | CREATE VIEW myview COMMENT='Test view' AS SELECT col1, col2 FROM mytable | +---------------------------------+-------------------+----------+---------------+-------------+----------+-----------+--------------------------------------------------------------------------+

The next example is the same as the previous example, except the view is secure:

CREATE OR REPLACE SECURE VIEW myview COMMENT='Test secure view' AS SELECT col1, col2 FROM mytable;

SELECT is_secure FROM information_schema.views WHERE table_name = 'MYVIEW';

The following shows two ways of creating recursive views:

First, create and load the table:

CREATE OR REPLACE TABLE employees (title VARCHAR, employee_ID INTEGER, manager_ID INTEGER);

INSERT INTO employees (title, employee_ID, manager_ID) VALUES ('President', 1, NULL), -- The President has no manager. ('Vice President Engineering', 10, 1), ('Programmer', 100, 10), ('QA Engineer', 101, 10), ('Vice President HR', 20, 1), ('Health Insurance Analyst', 200, 20);

Create a view using a recursive CTE, and then query the view.

CREATE VIEW employee_hierarchy (title, employee_ID, manager_ID, "MGR_EMP_ID (SHOULD BE SAME)", "MGR TITLE") AS ( WITH RECURSIVE employee_hierarchy_cte (title, employee_ID, manager_ID, "MGR_EMP_ID (SHOULD BE SAME)", "MGR TITLE") AS ( -- Start at the top of the hierarchy ... SELECT title, employee_ID, manager_ID, NULL AS "MGR_EMP_ID (SHOULD BE SAME)", 'President' AS "MGR TITLE" FROM employees WHERE title = 'President' UNION ALL -- ... and work our way down one level at a time. SELECT employees.title, employees.employee_ID, employees.manager_ID, employee_hierarchy_cte.employee_id AS "MGR_EMP_ID (SHOULD BE SAME)", employee_hierarchy_cte.title AS "MGR TITLE" FROM employees INNER JOIN employee_hierarchy_cte WHERE employee_hierarchy_cte.employee_ID = employees.manager_ID ) SELECT * FROM employee_hierarchy_cte );

SELECT * FROM employee_hierarchy ORDER BY employee_ID; +----------------------------+-------------+------------+-----------------------------+----------------------------+ | TITLE | EMPLOYEE_ID | MANAGER_ID | MGR_EMP_ID (SHOULD BE SAME) | MGR TITLE | |----------------------------+-------------+------------+-----------------------------+----------------------------| | President | 1 | NULL | NULL | President | | Vice President Engineering | 10 | 1 | 1 | President | | Vice President HR | 20 | 1 | 1 | President | | Programmer | 100 | 10 | 10 | Vice President Engineering | | QA Engineer | 101 | 10 | 10 | Vice President Engineering | | Health Insurance Analyst | 200 | 20 | 20 | Vice President HR | +----------------------------+-------------+------------+-----------------------------+----------------------------+

Create a view using the keyword RECURSIVE, and then query the view.

CREATE RECURSIVE VIEW employee_hierarchy_02 (title, employee_ID, manager_ID, "MGR_EMP_ID (SHOULD BE SAME)", "MGR TITLE") AS ( -- Start at the top of the hierarchy ... SELECT title, employee_ID, manager_ID, NULL AS "MGR_EMP_ID (SHOULD BE SAME)", 'President' AS "MGR TITLE" FROM employees WHERE title = 'President' UNION ALL -- ... and work our way down one level at a time. SELECT employees.title, employees.employee_ID, employees.manager_ID, employee_hierarchy_02.employee_id AS "MGR_EMP_ID (SHOULD BE SAME)", employee_hierarchy_02.title AS "MGR TITLE" FROM employees INNER JOIN employee_hierarchy_02 WHERE employee_hierarchy_02.employee_ID = employees.manager_ID );

SELECT * FROM employee_hierarchy_02 ORDER BY employee_ID; +----------------------------+-------------+------------+-----------------------------+----------------------------+ | TITLE | EMPLOYEE_ID | MANAGER_ID | MGR_EMP_ID (SHOULD BE SAME) | MGR TITLE | |----------------------------+-------------+------------+-----------------------------+----------------------------| | President | 1 | NULL | NULL | President | | Vice President Engineering | 10 | 1 | 1 | President | | Vice President HR | 20 | 1 | 1 | President | | Programmer | 100 | 10 | 10 | Vice President Engineering | | QA Engineer | 101 | 10 | 10 | Vice President Engineering | | Health Insurance Analyst | 200 | 20 | 20 | Vice President HR | +----------------------------+-------------+------------+-----------------------------+----------------------------+

CREATE OR ALTER VIEW examples

Basic example

Create a table my_table with one column:

CREATE OR ALTER TABLE my_table(a INT);

Create a view named v2 that selects column a from table my_table:

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

Create or alter view v2. Add or update the COMMENT and CHANGE_TRACKING properties for the view:

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

Create or alter view v2 to add a comment to a column:

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

Unset a property previously set on view

The absence of a previously set property in the CREATE OR ALTER VIEW statement results in unsetting it. In the following example, unset the COMMENT property for the view v2 from the previous example:

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