CREATE VIEWCREATE VIEW
基于 SQL 查询的结果集构造没有物理数据的虚拟表。Constructs a virtual table that has no physical data based on the result-set of a SQL query. ALTER VIEW
和 DROP VIEW
仅更改元数据。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 REPLACEOR REPLACE
如果已存在具有相同名称的视图,则会替换该视图。If a view of same name already exists, it is replaced.
[ GLOBAL ] TEMPORARY[ GLOBAL ] TEMPORARY
临时视图是会话范围内的,在会话终止时会被删除,因为它会跳过在基础元存储中持久保存定义(如果有)的操作。TEMPORARY views are session-scoped and is dropped when session ends because it skips persisting the definition in the underlying metastore, if any. 全局临时视图与系统保留的临时数据库
global_temp
关联。GLOBAL TEMPORARY views are tied to a system preserved temporary databaseglobal_temp
.IF NOT EXISTSIF NOT EXISTS
创建一个视图(如果不存在)。Creates a view if it does not exist.
view_identifierview_identifier
视图名称,可选择使用数据库名称进行限定。A view name, optionally qualified with a database name.
语法:
[database_name.] view_name
Syntax:[database_name.] view_name
create_view_clausescreate_view_clauses
这些子句是可选的,不区分顺序。These clauses are optional and order insensitive. 可采用以下格式。It can be of following formats.
[ ( column_name [ COMMENT column_comment ], ... ) ]
,用于指定列级注释。[ ( column_name [ COMMENT column_comment ], ... ) ]
to specify column-level comments.[ COMMENT view_comment ]
,用于指定视图级别的注释。[ COMMENT view_comment ]
to specify view-level comments.[ TBLPROPERTIES ( property_name = property_value [ , ... ] ) ]
,用于添加元数据键值对。[ TBLPROPERTIES ( property_name = property_value [ , ... ] ) ]
to add metadata key-value pairs.
查询 从基表或其他视图构造视图的 SELECT 语句。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;