Categories:

Table, View, & Sequence DDL

CREATE VIEW

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

Syntax

CREATE [ OR REPLACE ] [ SECURE ] [ RECURSIVE ] VIEW [ IF NOT EXISTS ] <name>
  [ ( <column_list> ) ]
  [ COPY GRANTS ]
  [ 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)

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 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 do not want the column names in the view to be the same as the column names of the underlying table, you may include a column list in which you specify the column names. (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 (x, x_times_2) AS SELECT x, x * 2 FROM table1;
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

Usage Notes

  • View definitions are not dynamic, i.e. 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 any column is subsequently dropped from the table.

    In either of these scenarios, querying the view returns a column mismatch 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.

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

Porting Notes

  • Some vendors support the FORCE keyword:

    CREATE OR REPLACE FORCE VIEW ...
    

    Snowflake accepts the FORCE keyword, but does not support it. In other words, you will not get a syntax error if you use this keyword, but using FORCE does not force the server to create 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.

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 view_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 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          |
+----------------------------+-------------+------------+-----------------------------+----------------------------+