创建视图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

如果该视图不存在, CREATE OR REPLACE VIEW 则等效于 CREATE VIEWIf the view does not exist, CREATE OR REPLACE VIEW is equivalent to CREATE VIEW. 如果该视图存在, CREATE OR REPLACE VIEW 则等效于 ALTER VIEWIf the view does exist, CREATE OR REPLACE VIEW is equivalent to ALTER VIEW.

[GLOBAL] TEMPORARY

TEMPORARY 如果有,则跳过在基础元存储中保留视图定义。TEMPORARY skips persisting the view definition in the underlying metastore, if any. 如果 GLOBAL 指定了,则可以通过不同的会话访问该视图,并使其保持活动状态,直到应用程序结束; 否则,将在会话终止时自动删除该视图。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. 所有全局临时视图都绑定到系统保留的临时数据库 global_tempAll 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.

备注

笔记本中定义的临时视图在其他笔记本中不可见。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. 列名称必须是唯一的,其检索的列数必须相同 select_statementThe column names must be unique with the same number of columns retrieved by select_statement. 如果未指定列列表,则视图架构是的输出架构 select_statementWhen the column list is not given, the view schema is the output schema of select_statement.

TBLPROPERTIES

元数据键值对。Metadata key-value pairs.

AS select_statement

SELECT定义视图的语句。A SELECT statement that defines the view. 语句可以从基表或其他视图中进行选择。The statement can select from base tables or the other views.

重要

无法指定数据源、分区或聚类分析选项,因为视图的具体化方式与表不同。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;