A RetroSearch Logo

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Search Query:

Showing content from https://docs.snowflake.com/en/developer-guide/python-connector/../../sql-reference/sql/create-view below:

Website Navigation


CREATE VIEW | Snowflake Documentation

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

This command supports the following variants:

See also:

ALTER VIEW , DROP VIEW , SHOW VIEWS , DESCRIBE VIEW

CREATE OR ALTER <object>

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>' , ... ] ) ]
  [ WITH CONTACT ( <purpose> = <contact_name> [ , <purpose> = <contact_name> ... ] ) ]
  [ 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 ] JOIN POLICY <policy_name> [ ALLOWED JOIN KEYS ( <col_name> [ , ... ] ) ] ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
  AS <select_statement>

Copy

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 an ALTER 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 <object>.

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

Copy

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.

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.

JOIN POLICY policy_name [ ALLOWED JOIN KEYS ( col_name [ , ... ] ) ]

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 ( 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 quota for objects.

WITH CONTACT ( purpose = contact [ , purpose = contact ...] )

Associate the new object with one or more contacts.

Access control requirements

A role used to execute this operation must have the following privileges 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 these governance features when creating views.

OWNERSHIP

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

General usage notes 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 |
+---------------------------------+-------------------+----------+---------------+-------------+----------+-----------+--------------------------------------------------------------------------+

Copy

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';

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy

CREATE OR ALTER VIEW examples Basic example

Create a table my_table with one column:

CREATE OR ALTER TABLE my_table(a INT);

Copy

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;

Copy

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;

Copy

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;

Copy

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;

Copy

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4