0001 ---
0002 layout: global
0003 title: ALTER VIEW
0004 displayTitle: ALTER VIEW
0005 license: |
0006 Licensed to the Apache Software Foundation (ASF) under one or more
0007 contributor license agreements. See the NOTICE file distributed with
0008 this work for additional information regarding copyright ownership.
0009 The ASF licenses this file to You under the Apache License, Version 2.0
0010 (the "License"); you may not use this file except in compliance with
0011 the License. You may obtain a copy of the License at
0012
0013 http://www.apache.org/licenses/LICENSE-2.0
0014
0015 Unless required by applicable law or agreed to in writing, software
0016 distributed under the License is distributed on an "AS IS" BASIS,
0017 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0018 See the License for the specific language governing permissions and
0019 limitations under the License.
0020 ---
0021
0022 ### Description
0023
0024 The `ALTER VIEW` statement can alter metadata associated with the view. It can change the definition of the view, change
0025 the name of a view to a different name, set and unset the metadata of the view by setting `TBLPROPERTIES`.
0026
0027 #### RENAME View
0028 Renames the existing view. If the new view name already exists in the source database, a `TableAlreadyExistsException` is thrown. This operation
0029 does not support moving the views across databases.
0030
0031 #### Syntax
0032 ```sql
0033 ALTER VIEW view_identifier RENAME TO view_identifier
0034 ```
0035
0036 #### Parameters
0037 * **view_identifier**
0038
0039 Specifies a view name, which may be optionally qualified with a database name.
0040
0041 **Syntax:** `[ database_name. ] view_name`
0042
0043 #### SET View Properties
0044 Set one or more properties of an existing view. The properties are the key value pairs. If the properties' keys exist,
0045 the values are replaced with the new values. If the properties' keys do not exist, the key value pairs are added into
0046 the properties.
0047
0048 #### Syntax
0049 ```sql
0050 ALTER VIEW view_identifier SET TBLPROPERTIES ( property_key = property_val [ , ... ] )
0051 ```
0052
0053 #### Parameters
0054 * **view_identifier**
0055
0056 Specifies a view name, which may be optionally qualified with a database name.
0057
0058 **Syntax:** `[ database_name. ] view_name`
0059
0060 * **property_key**
0061
0062 Specifies the property key. The key may consists of multiple parts separated by dot.
0063
0064 **Syntax:** `[ key_part1 ] [ .key_part2 ] [ ... ]`
0065
0066 #### UNSET View Properties
0067 Drop one or more properties of an existing view. If the specified keys do not exist, an exception is thrown. Use
0068 `IF EXISTS` to avoid the exception.
0069
0070 #### Syntax
0071 ```sql
0072 ALTER VIEW view_identifier UNSET TBLPROPERTIES [ IF EXISTS ] ( property_key [ , ... ] )
0073 ```
0074
0075 #### Parameters
0076 * **view_identifier**
0077
0078 Specifies a view name, which may be optionally qualified with a database name.
0079
0080 **Syntax:** `[ database_name. ] view_name`
0081
0082 * **property_key**
0083
0084 Specifies the property key. The key may consists of multiple parts separated by dot.
0085
0086 **Syntax:** `[ key_part1 ] [ .key_part2 ] [ ... ]`
0087
0088 #### ALTER View AS SELECT
0089 `ALTER VIEW view_identifier AS SELECT` statement changes the definition of a view. The `SELECT` statement must be valid,
0090 and the `view_identifier` must exist.
0091
0092 #### Syntax
0093 ```sql
0094 ALTER VIEW view_identifier AS select_statement
0095 ```
0096
0097 Note that `ALTER VIEW` statement does not support `SET SERDE` or `SET SERDEPROPERTIES` properties.
0098
0099 #### Parameters
0100 * **view_identifier**
0101
0102 Specifies a view name, which may be optionally qualified with a database name.
0103
0104 **Syntax:** `[ database_name. ] view_name`
0105
0106 * **select_statement**
0107
0108 Specifies the definition of the view. Check [select_statement](sql-ref-syntax-qry-select.html) for details.
0109
0110 ### Examples
0111
0112 ```sql
0113 -- Rename only changes the view name.
0114 -- The source and target databases of the view have to be the same.
0115 -- Use qualified or unqualified name for the source and target view.
0116 ALTER VIEW tempdb1.v1 RENAME TO tempdb1.v2;
0117
0118 -- Verify that the new view is created.
0119 DESCRIBE TABLE EXTENDED tempdb1.v2;
0120 +----------------------------+----------+-------+
0121 | col_name|data_type |comment|
0122 +----------------------------+----------+-------+
0123 | c1| int| null|
0124 | c2| string| null|
0125 | | | |
0126 |# Detailed Table Information| | |
0127 | Database| tempdb1| |
0128 | Table| v2| |
0129 +----------------------------+----------+-------+
0130
0131 -- Before ALTER VIEW SET TBLPROPERTIES
0132 DESC TABLE EXTENDED tempdb1.v2;
0133 +----------------------------+----------+-------+
0134 | col_name| data_type|comment|
0135 +----------------------------+----------+-------+
0136 | c1| int| null|
0137 | c2| string| null|
0138 | | | |
0139 |# Detailed Table Information| | |
0140 | Database| tempdb1| |
0141 | Table| v2| |
0142 | Table Properties| [....]| |
0143 +----------------------------+----------+-------+
0144
0145 -- Set properties in TBLPROPERTIES
0146 ALTER VIEW tempdb1.v2 SET TBLPROPERTIES ('created.by.user' = "John", 'created.date' = '01-01-2001' );
0147
0148 -- Use `DESCRIBE TABLE EXTENDED tempdb1.v2` to verify
0149 DESC TABLE EXTENDED tempdb1.v2;
0150 +----------------------------+-----------------------------------------------------+-------+
0151 | col_name| data_type|comment|
0152 +----------------------------+-----------------------------------------------------+-------+
0153 | c1| int| null|
0154 | c2| string| null|
0155 | | | |
0156 |# Detailed Table Information| | |
0157 | Database| tempdb1| |
0158 | Table| v2| |
0159 | Table Properties|[created.by.user=John, created.date=01-01-2001, ....]| |
0160 +----------------------------+-----------------------------------------------------+-------+
0161
0162 -- Remove the key `created.by.user` and `created.date` from `TBLPROPERTIES`
0163 ALTER VIEW tempdb1.v2 UNSET TBLPROPERTIES ('created.by.user', 'created.date');
0164
0165 --Use `DESC TABLE EXTENDED tempdb1.v2` to verify the changes
0166 DESC TABLE EXTENDED tempdb1.v2;
0167 +----------------------------+----------+-------+
0168 | col_name| data_type|comment|
0169 +----------------------------+----------+-------+
0170 | c1| int| null|
0171 | c2| string| null|
0172 | | | |
0173 |# Detailed Table Information| | |
0174 | Database| tempdb1| |
0175 | Table| v2| |
0176 | Table Properties| [....]| |
0177 +----------------------------+----------+-------+
0178
0179 -- Change the view definition
0180 ALTER VIEW tempdb1.v2 AS SELECT * FROM tempdb1.v1;
0181
0182 -- Use `DESC TABLE EXTENDED` to verify
0183 DESC TABLE EXTENDED tempdb1.v2;
0184 +----------------------------+---------------------------+-------+
0185 | col_name| data_type|comment|
0186 +----------------------------+---------------------------+-------+
0187 | c1| int| null|
0188 | c2| string| null|
0189 | | | |
0190 |# Detailed Table Information| | |
0191 | Database| tempdb1| |
0192 | Table| v2| |
0193 | Type| VIEW| |
0194 | View Text| select * from tempdb1.v1| |
0195 | View Original Text| select * from tempdb1.v1| |
0196 +----------------------------+---------------------------+-------+
0197 ```
0198
0199 ### Related Statements
0200
0201 * [describe-table](sql-ref-syntax-aux-describe-table.html)
0202 * [create-view](sql-ref-syntax-ddl-create-view.html)
0203 * [drop-view](sql-ref-syntax-ddl-drop-view.html)
0204 * [show-views](sql-ref-syntax-aux-show-views.html)
