创建视图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_temp 相关联。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.

备注

一个笔记本中定义的临时视图在其他笔记本中不可见。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_statement 检索到的列数相同。The column names must be unique with the same number of columns retrieved by select_statement. 如果未指定列列表,则视图架构是 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

定义视图的 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;