ContextQMD
Back to Spark

CREATE VIEW

docs/sql-ref-syntax-ddl-create-view.md

4.1.13.2 KB
Original Source

Description

Views are based on the result-set of an SQL query. CREATE VIEW constructs a virtual table that has no physical data therefore other operations like ALTER VIEW and DROP VIEW only change metadata.

Syntax

sql
CREATE [ OR REPLACE ] [ [ GLOBAL ] TEMPORARY ] VIEW [ IF NOT EXISTS ] view_identifier
    create_view_clauses AS query

Parameters

  • OR REPLACE

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

  • [ GLOBAL ] TEMPORARY

    TEMPORARY views are session-scoped and will be dropped when session ends because it skips persisting the definition in the underlying metastore, if any. GLOBAL TEMPORARY views are tied to a system preserved temporary database global_temp.

  • IF NOT EXISTS

    Creates a view if it does not exist. This clause is not supported for TEMPORARY views yet.

  • view_identifier

    Specifies a view name, which may be optionally qualified with a database name.

    Syntax: [ database_name. ] view_name

  • create_view_clauses

    These clauses are optional and order insensitive. It can be of following formats.

    • [ ( column_name [ COMMENT column_comment ], ... ) ] to specify column-level comments.

    • [ COMMENT view_comment ] to specify view-level comments.

    • [ TBLPROPERTIES ( property_name = property_value [ , ... ] ) ] to add metadata key-value pairs.

    • [ WITH SCHEMA { BINDING | COMPENSATION | [ TYPE ] EVOLUTION } ] to specify how the view reacts to schema changes

      This clause is not supported for TEMPORARY views.

      • BINDING - The view can tolerate only type changes in the underlying schema requiring safe up-casts.
      • COMPENSATION - The view can tolerate type changes in the underlying schema requiring casts. Runtime casting errors may occur.
      • TYPE EVOLUTION - The view will adapt to any type changes in the underlying schema.
      • EVOLUTION - For views defined without a column lists any schema changes are adapted by the view, including, for queries with SELECT * dropped or added columns. If the view is defined with a column list, the clause is interpreted as TYPE EVOLUTION.

      The default is WITH SCHEMA COMPENSATION.

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

Examples

sql
-- 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 global temporary view `subscribed_movies`.
CREATE GLOBAL TEMPORARY VIEW 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;

-- Create a view filtering the `orders` table which will adjust to schema changes in `orders`.
CREATE OR REPLACE VIEW open_orders WITH SCHEMA EVOLUTION
    AS SELECT * FROM orders WHERE status = 'open';