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 database global_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_nameSyntax: [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;