CREATE VIEW (Databricks SQL)

Constructs a virtual table that has no physical data based on the result-set of a SQL query. ALTER VIEW and DROP VIEW only change metadata.

Syntax

CREATE [ OR REPLACE ] [ [ GLOBAL ] TEMPORARY ] VIEW [ IF NOT EXISTS ] view_name
    [ column_list ]
    [ COMMENT view_comment ]
    [ properties ]
    AS query

column_list
   ( { column_alias [ COMMENT column_comment ] } [, ...] )

properties
   TBLPROPERTIES ( { property_key = property_value } [, ...] )

Parameters

  • OR REPLACE

    If a view of the same name already exists, it is replaced. To replace an existing view you must be its owner.

  • TEMPORARY

    TEMPORARY views are visible only to the session that created them and are dropped when the session ends.

  • IF NOT EXISTS

    Creates the view only if it does not exist. If a view by this name already exists the CREATE VIEW statement is ignored.

    You may specify at most one of IF NOT EXISTS or OR REPLACE.

  • view_name

    The name of the newly created view. A temporary view’s name must not be qualified. A the fully qualified view name must be unique.

  • column_list

    Optionally labels the columns in the query result of the view. If you provide a column list the number of column aliases must match the number of expressions in the query. In case no column list is specified aliases are derived from the body of the view.

    • column_alias

      The column aliases must be unique.

    • column_comment

      An optional STRING literal describing the column alias.

  • view_comment

    An optional STRING literal providing a view-level comments.

  • properties

    Optionally sets one or more user defined properties of the view. Properties are key value pairs.

    • property_key

      The key can consist of one or more identifiers separated by a dot, or a string literal.

      Property keys must be unique.

    • property_value

      The value for the property. The value must be a BOOLEAN, STRING, INTEGER, or DECIMAL literal.

  • AS query

    A query that constructs the view from base tables or other views.

Examples

-- Create or replace view for `experienced_employee` with comments.
CREATE OR REPLACE VIEW experienced_employee
    (id COMMENT 'Unique identification number', Name)
    COMMENT 'View for experienced employees'
    AS SELECT id, name FROM all_employee
        WHERE working_years > 5;

-- Create a temporary view `subscribed_movies` if it does not exist.
CREATE TEMPORARY VIEW IF NOT EXISTS subscribed_movies
    AS SELECT mo.member_id, mb.full_name, mo.movie_title
        FROM movies AS mo INNER JOIN members AS mb
        ON mo.member_id = mb.id;