CREATE VIEW
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
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.
-
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 changesThis clause is not supported for
TEMPORARYviews.- 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 asTYPE EVOLUTION.
The default is
WITH SCHEMA COMPENSATION.
-
query A SELECT statement 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 global temporary view `subscribed_movies` if it does not exist.
CREATE GLOBAL 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;
-- 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';