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_alias1 [ COMMENT column_comment1 ] } [, ...] ) ]
    [ COMMENT view_comment ]
    [ TBLPROPERTIES ( { property_name1 = property_value1 } [, ...] ) ]
    AS query

Parameters

  • OR REPLACE

    If a view of same name already exists, it is replaced.

  • TEMPORARY

    TEMPORARY views are session-scoped and are dropped when session ends because it skips persisting the definition in the underlying metastore, if any.

  • IF NOT EXISTS

    Creates a view if it does not exist.

  • view_name

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

  • column_aliasN

    Optionally labels a column in query result of the view. The column aliases must be unique. If no labels are provided the column names are derived from the query.

  • column_commentN

    An optional STRING literal commenting on the column alias.

  • view_comment

    An optional STRING literal providing a view-level comments.

  • property_nameN

    Names a metadata property.

  • property_valueN

    The value propertyN is set to.

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