0001 ---
0002 layout: global
0003 title: ALTER TABLE
0004 displayTitle: ALTER TABLE
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 `ALTER TABLE` statement changes the schema or properties of a table.
0025
0026 ### RENAME
0027
0028 `ALTER TABLE RENAME TO` statement changes the table name of an existing table in the database.
0029
0030 #### Syntax
0031
0032 ```sql
0033 ALTER TABLE table_identifier RENAME TO table_identifier
0034
0035 ALTER TABLE table_identifier partition_spec RENAME TO partition_spec
0036 ```
0037
0038 #### Parameters
0039
0040 * **table_identifier**
0041
0042 Specifies a table name, which may be optionally qualified with a database name.
0043
0044 **Syntax:** `[ database_name. ] table_name`
0045
0046 * **partition_spec**
0047
0048 Partition to be renamed.
0049
0050 **Syntax:** `PARTITION ( partition_col_name = partition_col_val [ , ... ] )`
0051
0052 ### ADD COLUMNS
0053
0054 `ALTER TABLE ADD COLUMNS` statement adds mentioned columns to an existing table.
0055
0056 #### Syntax
0057
0058 ```sql
0059 ALTER TABLE table_identifier ADD COLUMNS ( col_spec [ , ... ] )
0060 ```
0061
0062 #### Parameters
0063
0064 * **table_identifier**
0065
0066 Specifies a table name, which may be optionally qualified with a database name.
0067
0068 **Syntax:** `[ database_name. ] table_name`
0069
0070 * **COLUMNS ( col_spec )**
0071
0072 Specifies the columns to be added.
0073
0074 ### ALTER OR CHANGE COLUMN
0075
0076 `ALTER TABLE ALTER COLUMN` or `ALTER TABLE CHANGE COLUMN` statement changes column's definition.
0077
0078 #### Syntax
0079
0080 ```sql
0081 ALTER TABLE table_identifier { ALTER | CHANGE } [ COLUMN ] col_spec alterColumnAction
0082 ```
0083
0084 #### Parameters
0085
0086 * **table_identifier**
0087
0088 Specifies a table name, which may be optionally qualified with a database name.
0089
0090 **Syntax:** `[ database_name. ] table_name`
0091
0092 * **COLUMNS ( col_spec )**
0093
0094 Specifies the column to be altered or be changed.
0095
0096 * **alterColumnAction**
0097
0098 Change column's definition.
0099
0100 ### ADD AND DROP PARTITION
0101
0102 #### ADD PARTITION
0103
0104 `ALTER TABLE ADD` statement adds partition to the partitioned table.
0105
0106 ##### Syntax
0107
0108 ```sql
0109 ALTER TABLE table_identifier ADD [IF NOT EXISTS]
0110 ( partition_spec [ partition_spec ... ] )
0111 ```
0112
0113 ##### Parameters
0114
0115 * **table_identifier**
0116
0117 Specifies a table name, which may be optionally qualified with a database name.
0118
0119 **Syntax:** `[ database_name. ] table_name`
0120
0121 * **partition_spec**
0122
0123 Partition to be added.
0124
0125 **Syntax:** `PARTITION ( partition_col_name = partition_col_val [ , ... ] )`
0126
0127 #### DROP PARTITION
0128
0129 `ALTER TABLE DROP` statement drops the partition of the table.
0130
0131 ##### Syntax
0132
0133 ```sql
0134 ALTER TABLE table_identifier DROP [ IF EXISTS ] partition_spec [PURGE]
0135 ```
0136
0137 ##### Parameters
0138
0139 * **table_identifier**
0140
0141 Specifies a table name, which may be optionally qualified with a database name.
0142
0143 **Syntax:** `[ database_name. ] table_name`
0144
0145 * **partition_spec**
0146
0147 Partition to be dropped.
0148
0149 **Syntax:** `PARTITION ( partition_col_name = partition_col_val [ , ... ] )`
0150
0151 ### SET AND UNSET
0152
0153 #### SET TABLE PROPERTIES
0154
0155 `ALTER TABLE SET` command is used for setting the table properties. If a particular property was already set,
0156 this overrides the old value with the new one.
0157
0158 `ALTER TABLE UNSET` is used to drop the table property.
0159
0160 ##### Syntax
0161
0162 ```sql
0163 -- Set Table Properties
0164 ALTER TABLE table_identifier SET TBLPROPERTIES ( key1 = val1, key2 = val2, ... )
0165
0166 -- Unset Table Properties
0167 ALTER TABLE table_identifier UNSET TBLPROPERTIES [ IF EXISTS ] ( key1, key2, ... )
0168 ```
0169
0170 #### SET SERDE
0171
0172 `ALTER TABLE SET` command is used for setting the SERDE or SERDE properties in Hive tables. If a particular property was already set, this overrides the old value with the new one.
0173
0174 ##### Syntax
0175
0176 ```sql
0177 -- Set SERDE Properties
0178 ALTER TABLE table_identifier [ partition_spec ]
0179 SET SERDEPROPERTIES ( key1 = val1, key2 = val2, ... )
0180
0181 ALTER TABLE table_identifier [ partition_spec ] SET SERDE serde_class_name
0182 [ WITH SERDEPROPERTIES ( key1 = val1, key2 = val2, ... ) ]
0183 ```
0184
0185 #### SET LOCATION And SET FILE FORMAT
0186
0187 `ALTER TABLE SET` command can also be used for changing the file location and file format for
0188 existing tables.
0189
0190 ##### Syntax
0191
0192 ```sql
0193 -- Changing File Format
0194 ALTER TABLE table_identifier [ partition_spec ] SET FILEFORMAT file_format
0195
0196 -- Changing File Location
0197 ALTER TABLE table_identifier [ partition_spec ] SET LOCATION 'new_location'
0198 ```
0199
0200 #### Parameters
0201
0202 * **table_identifier**
0203
0204 Specifies a table name, which may be optionally qualified with a database name.
0205
0206 **Syntax:** `[ database_name. ] table_name`
0207
0208 * **partition_spec**
0209
0210 Specifies the partition on which the property has to be set.
0211
0212 **Syntax:** `PARTITION ( partition_col_name = partition_col_val [ , ... ] )`
0213
0214 * **SERDEPROPERTIES ( key1 = val1, key2 = val2, ... )**
0215
0216 Specifies the SERDE properties to be set.
0217
0218 ### Examples
0219
0220 ```sql
0221 -- RENAME table
0222 DESC student;
0223 +-----------------------+---------+-------+
0224 | col_name|data_type|comment|
0225 +-----------------------+---------+-------+
0226 | name| string| NULL|
0227 | rollno| int| NULL|
0228 | age| int| NULL|
0229 |# Partition Information| | |
0230 | # col_name|data_type|comment|
0231 | age| int| NULL|
0232 +-----------------------+---------+-------+
0233
0234 ALTER TABLE Student RENAME TO StudentInfo;
0235
0236 -- After Renaming the table
0237 DESC StudentInfo;
0238 +-----------------------+---------+-------+
0239 | col_name|data_type|comment|
0240 +-----------------------+---------+-------+
0241 | name| string| NULL|
0242 | rollno| int| NULL|
0243 | age| int| NULL|
0244 |# Partition Information| | |
0245 | # col_name|data_type|comment|
0246 | age| int| NULL|
0247 +-----------------------+---------+-------+
0248
0249 -- RENAME partition
0250
0251 SHOW PARTITIONS StudentInfo;
0252 +---------+
0253 |partition|
0254 +---------+
0255 | age=10|
0256 | age=11|
0257 | age=12|
0258 +---------+
0259
0260 ALTER TABLE default.StudentInfo PARTITION (age='10') RENAME TO PARTITION (age='15');
0261
0262 -- After renaming Partition
0263 SHOW PARTITIONS StudentInfo;
0264 +---------+
0265 |partition|
0266 +---------+
0267 | age=11|
0268 | age=12|
0269 | age=15|
0270 +---------+
0271
0272 -- Add new columns to a table
0273 DESC StudentInfo;
0274 +-----------------------+---------+-------+
0275 | col_name|data_type|comment|
0276 +-----------------------+---------+-------+
0277 | name| string| NULL|
0278 | rollno| int| NULL|
0279 | age| int| NULL|
0280 |# Partition Information| | |
0281 | # col_name|data_type|comment|
0282 | age| int| NULL|
0283 +-----------------------+---------+-------+
0284
0285 ALTER TABLE StudentInfo ADD columns (LastName string, DOB timestamp);
0286
0287 -- After Adding New columns to the table
0288 DESC StudentInfo;
0289 +-----------------------+---------+-------+
0290 | col_name|data_type|comment|
0291 +-----------------------+---------+-------+
0292 | name| string| NULL|
0293 | rollno| int| NULL|
0294 | LastName| string| NULL|
0295 | DOB|timestamp| NULL|
0296 | age| int| NULL|
0297 |# Partition Information| | |
0298 | # col_name|data_type|comment|
0299 | age| int| NULL|
0300 +-----------------------+---------+-------+
0301
0302 -- Add a new partition to a table
0303 SHOW PARTITIONS StudentInfo;
0304 +---------+
0305 |partition|
0306 +---------+
0307 | age=11|
0308 | age=12|
0309 | age=15|
0310 +---------+
0311
0312 ALTER TABLE StudentInfo ADD IF NOT EXISTS PARTITION (age=18);
0313
0314 -- After adding a new partition to the table
0315 SHOW PARTITIONS StudentInfo;
0316 +---------+
0317 |partition|
0318 +---------+
0319 | age=11|
0320 | age=12|
0321 | age=15|
0322 | age=18|
0323 +---------+
0324
0325 -- Drop a partition from the table
0326 SHOW PARTITIONS StudentInfo;
0327 +---------+
0328 |partition|
0329 +---------+
0330 | age=11|
0331 | age=12|
0332 | age=15|
0333 | age=18|
0334 +---------+
0335
0336 ALTER TABLE StudentInfo DROP IF EXISTS PARTITION (age=18);
0337
0338 -- After dropping the partition of the table
0339 SHOW PARTITIONS StudentInfo;
0340 +---------+
0341 |partition|
0342 +---------+
0343 | age=11|
0344 | age=12|
0345 | age=15|
0346 +---------+
0347
0348 -- Adding multiple partitions to the table
0349 SHOW PARTITIONS StudentInfo;
0350 +---------+
0351 |partition|
0352 +---------+
0353 | age=11|
0354 | age=12|
0355 | age=15|
0356 +---------+
0357
0358 ALTER TABLE StudentInfo ADD IF NOT EXISTS PARTITION (age=18) PARTITION (age=20);
0359
0360 -- After adding multiple partitions to the table
0361 SHOW PARTITIONS StudentInfo;
0362 +---------+
0363 |partition|
0364 +---------+
0365 | age=11|
0366 | age=12|
0367 | age=15|
0368 | age=18|
0369 | age=20|
0370 +---------+
0371
0372 -- ALTER OR CHANGE COLUMNS
0373 DESC StudentInfo;
0374 +-----------------------+---------+-------+
0375 | col_name|data_type|comment|
0376 +-----------------------+---------+-------+
0377 | name| string| NULL|
0378 | rollno| int| NULL|
0379 | LastName| string| NULL|
0380 | DOB|timestamp| NULL|
0381 | age| int| NULL|
0382 |# Partition Information| | |
0383 | # col_name|data_type|comment|
0384 | age| int| NULL|
0385 +-----------------------+---------+-------+
0386
0387 ALTER TABLE StudentInfo ALTER COLUMN name COMMENT "new comment";
0388
0389 --After ALTER or CHANGE COLUMNS
0390 DESC StudentInfo;
0391 +-----------------------+---------+-----------+
0392 | col_name|data_type| comment|
0393 +-----------------------+---------+-----------+
0394 | name| string|new comment|
0395 | rollno| int| NULL|
0396 | LastName| string| NULL|
0397 | DOB|timestamp| NULL|
0398 | age| int| NULL|
0399 |# Partition Information| | |
0400 | # col_name|data_type| comment|
0401 | age| int| NULL|
0402 +-----------------------+---------+-----------+
0403
0404 -- Change the fileformat
0405 ALTER TABLE loc_orc SET fileformat orc;
0406
0407 ALTER TABLE p1 partition (month=2, day=2) SET fileformat parquet;
0408
0409 -- Change the file Location
0410 ALTER TABLE dbx.tab1 PARTITION (a='1', b='2') SET LOCATION '/path/to/part/ways'
0411
0412 -- SET SERDE/ SERDE Properties
0413 ALTER TABLE test_tab SET SERDE 'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe';
0414
0415 ALTER TABLE dbx.tab1 SET SERDE 'org.apache.hadoop' WITH SERDEPROPERTIES ('k' = 'v', 'kay' = 'vee')
0416
0417 -- SET TABLE PROPERTIES
0418 ALTER TABLE dbx.tab1 SET TBLPROPERTIES ('winner' = 'loser');
0419
0420 -- SET TABLE COMMENT Using SET PROPERTIES
0421 ALTER TABLE dbx.tab1 SET TBLPROPERTIES ('comment' = 'A table comment.');
0422
0423 -- Alter TABLE COMMENT Using SET PROPERTIES
0424 ALTER TABLE dbx.tab1 SET TBLPROPERTIES ('comment' = 'This is a new comment.');
0425
0426 -- DROP TABLE PROPERTIES
0427 ALTER TABLE dbx.tab1 UNSET TBLPROPERTIES ('winner');
0428 ```
0429
0430 ### Related Statements
0431
0432 * [CREATE TABLE](sql-ref-syntax-ddl-create-table.html)
0433 * [DROP TABLE](sql-ref-syntax-ddl-drop-table.html)