Create ViewΒΆ

CREATE [OR REPLACE] [[GLOBAL] TEMPORARY] VIEW [IF NOT EXISTS] [db_name.]view_name
    [(col_name1 [COMMENT col_comment1], ...)]
    [COMMENT table_comment]
    [TBLPROPERTIES (key1=val1, key2=val2, ...)]
    AS select_statement

Defines a logical view on one or more tables or views.

OR REPLACE
If the view does not exist, CREATE OR REPLACE VIEW is equivalent to CREATE VIEW. If the view does exist, CREATE OR REPLACE VIEW is equivalent to ALTER VIEW.
[GLOBAL] TEMPORARY
Specify TEMPORARY to skip persisting the view definition in the underlying metastore, if any. When GLOBAL is given, this view can be accessed by different sessions and kept alive until your application ends; otherwise, the temporary views are session-scoped and will be automatically dropped if the session terminates. All the global temporary views are tied to a system preserved temporary database global_temp. The database name is preserved, and thus, users are not allowed to create/use/drop this database. Contrary to the local temporary views, users always need to use the qualified name to access the global temporary view.
(col_name1 [COMMENT col_comment1], ...)
Specify the column list to define your own schema. The column names must be unique with the same number of columns retrieved by select_statement. When the column list is not given, the view schema is the output schema of select_statement.
TBLPROPERTIES
Specify your own metadata key/value pairs.
AS select_statement
Specify a SELECT statement for defining the view. The statement can select from base tables or the other views.

Attention

Note: You cannot specify datasource, partition or clustering options since a view is not materialized like tables.

Examples:

-- Creates a persistent view view_deptDetails in database1. The view definition is recorded in the underlying metastore
CREATE VIEW database1.view_deptDetails
    AS SELECT * FROM company JOIN dept ON company.dept_id = dept.id;

-- Creates or replaces a local temporary view from a persistent view with an extra filter
CREATE OR REPLACE TEMPORARY VIEW temp_DeptSFO
    AS SELECT * FROM database1.view_deptDetails WHERE loc = 'SFO';

-- Access the base tables through the temporary view
SELECT * FROM temp_DeptSFO;

-- Creates a global temp view to share the data through different sessions
CREATE GLOBAL TEMP VIEW global_DeptSJC
    AS SELECT * FROM database1.view_deptDetails WHERE loc = 'SJC';

-- Access the global temp views
SELECT * FROM global_temp.global_DeptSJC;

-- Drop the global temp view, temp view and persistent view.
DROP VIEW global_temp.global_DeptSJC;
DROP VIEW temp_DeptSFO;
DROP VIEW database1.view_deptDetails;