Create View

Important

This documentation has been retired and might not be updated. The products, services, or technologies mentioned in this content are no longer supported. See CREATE VIEW.

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

Define 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

TEMPORARY skips persisting the view definition in the underlying metastore, if any. If GLOBAL is specified, the 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. You must use the qualified name to access the global temporary view.

Note

A temporary view defined in a notebook is not visible in other notebooks. See Notebook isolation.

(col_name1 [COMMENT col_comment1], ...)

A column list that defines the view 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

Metadata key-value pairs.

AS select_statement

A SELECT statement that defines the view. The statement can select from base tables or the other views.

Important

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

Examples

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

-- Create or replace 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;

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