CREATE VIEW

  • Article

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

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 ] [ TEMPORARY ] VIEW [ IF NOT EXISTS ] view_name
    [ column_list ]
    [ schema_binding ]
    [ COMMENT view_comment ]
    [ TBLPROPERTIES clause ]
    AS query

schema_binding
   WITH SCHEMA { BINDING | COMPENSATION | [ TYPE ] EVOLUTION }

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

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.

    Replacing an existing view does not preserve privileges granted on the original view. Use ALTER VIEW to preserve privileges.

  • TEMPORARY

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

  • GLOBAL TEMPORARY

    Applies to: check marked yes Databricks Runtime

    GLOBAL TEMPORARY views are tied to a system preserved temporary schema global_temp.

  • 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. The fully qualified view name must be unique.

    Views created in hive_metastore can only contain alphanumeric ASCII characters and underscores (INVALID_SCHEMA_OR_RELATION_NAME).

  • schema_binding

    Applies to: check marked yes Databricks Runtime 15.3 and above

    Optionally specifies how the view adapts to changes to the schema of the query due to changes in the underlying object definitions.

    This clause is not supported for temporary views or materialized views.

    • WITH SCHEMA BINDING

      The view will become invalid if the query column-list changes except for the following conditions:

      • The column-list includes a star clause, and there are additional columns. These additional columns are ignored.
      • The type of one or more columns changed in a way that allows them to be safely cast to the original column types using implicit casting rules.

      This is the default behavior.

    • WITH SCHEMA COMPENSATION

      The view will become invalid if the query column list changes except for the following conditions:

      • The column-list includes a star clause, and there are additional columns. These additional columns are ignored.
      • The type of one or more columns changed in a way that allows them to be cast to the original column types using explicit ANSI cast rules.

    • WITH SCHEMA TYPE EVOLUTION

      The view will adopt any changes to types in the query column list into its own definition when the SQL compiler detects such a change in response to a reference to the view.

    • WITH SCHEMA EVOLUTION

      • This mode behaves like WITH SCHEMA TYPE EVOLUTION, and also adopts changes in column names or added and dropped columns if the view does not include an explicit column_list.
      • The view will only become invalid if the query can no longer be parsed, or the optional view column_list does not match the number of expressions in the query select-list anymore.

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

  • TBLPROPERTIES

    Optionally sets one or more user defined properties.

  • 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`.
> CREATE 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 with schema binding (default)
> CREATE TABLE emp(name STRING, income INT);
> CREATE VIEW emp_v WITH SCHEMA BINDING AS SELECT * FROM emp;

- The view ignores adding a column to the base table
> ALTER TABLE emp ADD COLUMN bonus SMALLINT;
> SELECT * FROM emp_v;
name  income
---- ------

-- The view tolerates narrowing the underlying type
> CREATE OR REPLACE TABLE emp(name STRING, income SMALLINT, bonus SMALLINT);
> SELECT typeof(income) FROM emp_v;
 INTEGER

- The view does not tolerate widening the underlying type
CREATE OR REPLACE TABLE emp(name STRING, income BIGINT, bonus SMALLINT);
> SELECT typeof(income) FROM emp_v;
 Error

- Create a view with SCHEMA COMPENSATION
> CREATE TABLE emp(name STRING, income SMALLINT, bonus SMALLINT);
> CREATE VIEW emp_v WITH SCHEMA COMPENSATION AS SELECT * FROM emp;

-- The view tolerates widening the underlying type but keeps its own signature fixed
CREATE OR REPLACE TABLE emp(name STRING, income INTEGER, bonus INTEGER);
> SELECT typeof(income) FROM emp_v;
 INTEGER

-- The view does not tolerate dropping a needed column
ALTER TABLE emp DROP COLUMN bonus;
> SELECT * FROM emp_v;
Error

- Create a view with SCHEMA EVOLUTION
> CREATE TABLE emp(name STRING, income SMALLINT);
> CREATE VIEW emp_v WITH SCHEMA EVOLUTION AS SELECT * FROM emp;

-- The view picks up additional columns
> ALTER TABLE emp ADD COLUMN bonus SMALLINT
> SELECT * FROM emp_v;
 name income bonus
 ---- ------ -----

-- The view picks up renamed columns as well
> ALTER TABLE emp RENAME COLUMN income TO salary SMALLINT;
> SELECT * FROM emp_v;
 name salary bonus
 ---- ------ -----

-- The view picks up changes to column types and dropped columns
> CREATE OR REPLACE TABLE emp(name STRING, salary BIGINT);
> SELECT *, typeof(salary)AS salary_type FROM emp_v;
 name salary
 ---- ------