ALTER VIEWALTER VIEW

ALTER VIEW语句可以更改与视图关联的元数据。The ALTER VIEW statement can alter metadata associated with the view. 它可以更改视图的定义,将视图的名称更改为其他名称,通过设置设置和取消设置视图的元数据 TBLPROPERTIESIt can change the definition of the view, change the name of a view to a different name, set and unset the metadata of the view by setting TBLPROPERTIES.

重命名视图RENAME View

重命名现有视图。Renames the existing view. 如果源数据库中已存在新视图名称, TableAlreadyExistsException 则会引发。If the new view name already exists in the source database, a TableAlreadyExistsException is thrown. 此操作不支持跨数据库移动视图。This operation does not support moving the views across databases.

语法Syntax

ALTER VIEW view_identifier RENAME TO view_identifier

参数Parameters

  • view_identifierview_identifier

    指定视图名称,可选择使用数据库名称进行限定。Specifies a view name, which may be optionally qualified with a database name.

    语法:[ database_name. ] view_nameSyntax: [ database_name. ] view_name

设置视图属性SET view properties

设置现有视图的一个或多个属性。Set one or more properties of an existing view. 属性是键值对。The properties are the key value pairs. 如果属性的键存在,则这些值将替换为新值。If the properties’ keys exist, the values are replaced with the new values. 如果 properties 项不存在,则会将键值对添加到属性中。If the properties’ keys do not exist, the key value pairs are added into the properties.

语法Syntax

ALTER VIEW view_identifier SET TBLPROPERTIES ( property_key = property_val [ , ... ] )

参数Parameters

  • view_identifierview_identifier

    指定视图名称,可选择使用数据库名称进行限定。Specifies a view name, which may be optionally qualified with a database name.

    语法:[ database_name. ] view_nameSyntax: [ database_name. ] view_name

  • property_keyproperty_key

    指定属性键。Specifies the property key. 该密钥可以包含用点分隔的多个部件。The key may consists of multiple parts separated by dot.

    语法:[ key_part1 ] [ .key_part2 ] [ ... ]Syntax: [ key_part1 ] [ .key_part2 ] [ ... ]

取消设置视图属性UNSET view properties

删除现有视图的一个或多个属性。Drop one or more properties of an existing view. 如果指定的项不存在,则会引发异常。If the specified keys do not exist, an exception is thrown. 使用 IF EXISTS 来避免异常。Use IF EXISTS to avoid the exception.

语法Syntax

ALTER VIEW view_identifier UNSET TBLPROPERTIES [ IF EXISTS ]  ( property_key [ , ... ] )

参数Parameters

  • view_identifierview_identifier

    指定视图名称,可选择使用数据库名称进行限定。Specifies a view name, which may be optionally qualified with a database name.

    语法:[ database_name. ] view_nameSyntax: [ database_name. ] view_name

  • property_keyproperty_key

    指定属性键。Specifies the property key. 该密钥可以包含用点分隔的多个部件。The key may consists of multiple parts separated by dot.

    语法:[ key_part1 ] [ .key_part2 ] [ ... ]Syntax: [ key_part1 ] [ .key_part2 ] [ ... ]

将视图更改为 SELECTALTER VIEW AS SELECT

ALTER VIEW view_identifier AS SELECT 语句更改视图的定义。ALTER VIEW view_identifier AS SELECT statement changes the definition of a view. SELECT语句必须有效,并且 view_identifier 必须存在。The SELECT statement must be valid, and the view_identifier must exist.

语法Syntax

ALTER VIEW view_identifier AS select_statement

请注意, ALTER VIEW 语句不支持 SET SERDESET SERDEPROPERTIES 属性。Note that ALTER VIEW statement does not support SET SERDE or SET SERDEPROPERTIES properties.

参数Parameters

  • view_identifierview_identifier

    指定视图名称,可选择使用数据库名称进行限定。Specifies a view name, which may be optionally qualified with a database name.

    语法:[ database_name. ] view_nameSyntax: [ database_name. ] view_name

  • select_statementselect_statement

    指定视图的定义。Specifies the definition of the view. 选中 " 选择 详细信息"。Check SELECT for details.

示例Examples

-- Rename only changes the view name.
-- The source and target databases of the view have to be the same.
-- Use qualified or unqualified name for the source and target view.
ALTER VIEW tempdb1.v1 RENAME TO tempdb1.v2;

-- Verify that the new view is created.
DESCRIBE TABLE EXTENDED tempdb1.v2;
+----------------------------+----------+-------+
|                    col_name|data_type |comment|
+----------------------------+----------+-------+
|                          c1|       int|   null|
|                          c2|    string|   null|
|                            |          |       |
|# Detailed Table Information|          |       |
|                    Database|   tempdb1|       |
|                       Table|        v2|       |
+----------------------------+----------+-------+

-- Before ALTER VIEW SET TBLPROPERTIES
DESC TABLE EXTENDED tempdb1.v2;
+----------------------------+----------+-------+
|                    col_name| data_type|comment|
+----------------------------+----------+-------+
|                          c1|       int|   null|
|                          c2|    string|   null|
|                            |          |       |
|# Detailed Table Information|          |       |
|                    Database|   tempdb1|       |
|                       Table|        v2|       |
|            Table Properties|    [....]|       |
+----------------------------+----------+-------+

-- Set properties in TBLPROPERTIES
ALTER VIEW tempdb1.v2 SET TBLPROPERTIES ('created.by.user' = "John", 'created.date' = '01-01-2001' );

-- Use `DESCRIBE TABLE EXTENDED tempdb1.v2` to verify
DESC TABLE EXTENDED tempdb1.v2;
+----------------------------+-----------------------------------------------------+-------+
|                    col_name|                                            data_type|comment|
+----------------------------+-----------------------------------------------------+-------+
|                          c1|                                                  int|   null|
|                          c2|                                               string|   null|
|                            |                                                     |       |
|# Detailed Table Information|                                                     |       |
|                    Database|                                              tempdb1|       |
|                       Table|                                                   v2|       |
|            Table Properties|[created.by.user=John, created.date=01-01-2001, ....]|       |
+----------------------------+-----------------------------------------------------+-------+

-- Remove the key `created.by.user` and `created.date` from `TBLPROPERTIES`
ALTER VIEW tempdb1.v2 UNSET TBLPROPERTIES ('created.by.user', 'created.date');

--Use `DESC TABLE EXTENDED tempdb1.v2` to verify the changes
DESC TABLE EXTENDED tempdb1.v2;
+----------------------------+----------+-------+
|                    col_name| data_type|comment|
+----------------------------+----------+-------+
|                          c1|       int|   null|
|                          c2|    string|   null|
|                            |          |       |
|# Detailed Table Information|          |       |
|                    Database|   tempdb1|       |
|                       Table|        v2|       |
|            Table Properties|    [....]|       |
+----------------------------+----------+-------+

-- Change the view definition
ALTER VIEW tempdb1.v2 AS SELECT * FROM tempdb1.v1;

-- Use `DESC TABLE EXTENDED` to verify
DESC TABLE EXTENDED tempdb1.v2;
+----------------------------+---------------------------+-------+
|                    col_name|                  data_type|comment|
+----------------------------+---------------------------+-------+
|                          c1|                        int|   null|
|                          c2|                     string|   null|
|                            |                           |       |
|# Detailed Table Information|                           |       |
|                    Database|                    tempdb1|       |
|                       Table|                         v2|       |
|                        Type|                       VIEW|       |
|                   View Text|   select * from tempdb1.v1|       |
|          View Original Text|   select * from tempdb1.v1|       |
+----------------------------+---------------------------+-------+