# ALTER TABLE #### 语法 ![](https://support.huaweicloud.com/cmpntguide-lts-mrs/public_sys-resources/note_3.0-zh-cn.png) name，new_name，column_name，new_column_name，table_name_\*为用户自定义参数。 1. 重命名一个表。 **ALTER TABLE** **name** **RENAME TO** **new_name** 2. 修改表的列名，为列添加注释（可选项）和属性（可选项），可参考[描述](https://support.huaweicloud.com/cmpntguide-lts-mrs/mrs_01_300130.html#mrs_01_300130__section124071829191614)查看支持的列属性。 **ALTER TABLE** **name** **ADD COLUMN** **column_name** **data_type \[ COMMENT comment \] \[ WITH ( property_name = expression \[, ...\] ) \]** 3. 删除表中名为column_name的列。 **ALTER TABLE name DROP COLUMN column_name** ![](https://support.huaweicloud.com/cmpntguide-lts-mrs/public_sys-resources/notice_3.0-zh-cn.png) - 不支持删除分区列或者分桶列。 - DROP COLUMN不支持rctext、rcbinary、rcfile格式存储的表。由于connector对不同文件格式的列访问模式不同，drop column后可能会出现查询失败的情况，例如： - 对于orc格式存储的非分区表 ，drop column后如果查询失败，需要设置Session属性： set session hive.orc_use_column_names=true; - 对于parquet格式存储的非分区表，drop column后如果查询失败，需要设置Session属性： set session hive.parquet_use_column_names=true; - 对于orc或parquet格式的分区表或事务表，drop column后无法通过设置Session属性的方式来确保查询成功。 4. 将表中列名为column_name的列重命名为new_column_name。 **ALTER TABLE name RENAME COLUMN column_name TO new_column_name** ![](https://support.huaweicloud.com/cmpntguide-lts-mrs/public_sys-resources/notice_3.0-zh-cn.png) 不支持重命名分区列或者分桶列。 5. 分区表添加分区。 **ALTER TABLE name ADD \[IF NOT EXISTS\] PARTITION partition_spec \[LOCATION 'location'\]\[ PARTITION partition_spec \[LOCATION 'location'\], ...\];** 6. 分区表删除分区。这个操作会从分区移除数据和元数据。无论表是internal table还是external table，如果ADD PARTITION时指定了分区保存路径，那么在DROP PARTITION执行后，分区所在文件夹和数据不会被删除。如果ADD PARTITION时未指定分区保存路径，分区目录将从HDFS上删除，数据会移到.Trash/Current文件夹。 **ALTER TABLE table_name DROP \[IF EXISTS\] PARTITION partition_spec\[, PARTITION partition_spec, ...\];** ![](https://support.huaweicloud.com/cmpntguide-lts-mrs/public_sys-resources/notice_3.0-zh-cn.png) 对于外接Hive数据源的场景，分区键如果是定长字符串，如char(5)，那么对应的数据如果字符串长度小于5位，则drop partition的操作就会失败。 7. 重命名分区。 **ALTER TABLE table_name PARTITION(partition_key = partition_value1) rename to partition(partition_key = partition_value2)** 8. 将table_name_1的分区转移给table_name_2。 **ALTER TABLE table_name_2** **EXCHANGE PARTITION** **(partition_spec) WITH TABLE table_name_1;** 9. 同时将table_name_1的多个分区转移给table_name_2。 **ALTER TABLE table_name_2** **EXCHANGE PARTITION** **(partition_spec, partition_spec2, ...) WITH TABLE table_name_1;** 10. 新增/修改表属性。 **ALTER TABLE table_name SET TBLPROPERTIES (property_name = property_value\[, property_name = property_value, ...\] );** ![](https://support.huaweicloud.com/cmpntguide-lts-mrs/public_sys-resources/note_3.0-zh-cn.png) TBLPROPERTIES允许用户通过键值对的方式（属性名和属性都必须是单引号或双引号包裹的字符串），添加或修改连接器支持的表属性，以Hive连接器为例： - TBLPROPERTIES ("transactional"="true") ，可能的取值为\[true,false\] - TBLPROPERTIES ("auto.purge"="true") ，可能的取值为\[true,false\] 11. 修改表的列属性。 **ALTER TABLE table_name \[PARTITION partition_spec\] CHANGE \[COLUMN\] col_old_name col_new_name column_type \[COMMENT col_comment\] \[FIRST\|AFTER column_name\] \[CASCADE\|RESTRICT\]** ![](https://support.huaweicloud.com/cmpntguide-lts-mrs/public_sys-resources/notice_3.0-zh-cn.png) - 对一个已经存在的表，修改列名、数据类型、注释、位置（\[FIRST\|AFTER column_name\] 用于指定列被修改后出现的位置）或者以上任意组合。如果语法中包含了分区子句，那么相应分区的元数据也会一起变动。CASCADE模式会让语法对表和表分区的元数据产生作用，而默认的模式为RESTRICT，对列的修改，仅对表的元数据产生作用。 - 列修改命令只能修改表/分区的元数据，而不会修改数据本身。用户应确保表/分区的实际数据布局符合元数据定义。 - 不支持更改表的分区列/桶列，也不支持更改ORC表。 12. 修改表或分区的存储位置。 **ALTER TABLE table_name \[PARTITION partition_spec\] SET LOCATION location;** ![](https://support.huaweicloud.com/cmpntguide-lts-mrs/public_sys-resources/note_3.0-zh-cn.png) - 可以使用ALTER TABLE \[PARTITION\] SET LOCATION设置表的表或分区位置。 - 在SET LOCATION命令之后，表/分区数据可能不会显示。 - SET LOCATION在创建表/分区目录时会使用给定目录路径，而不是Hive在创建表/分区时创建的默认路径。 - 该语句不会对表或分区原有数据产生影响，也不会修改原有的表或分区目录，但是新增的数据，都会保存到新指定的目录下。 13. 修改表或分区的数据文件保存格式。 **ALTER TABLE table_name \[PARTITION partition_spec\] SET FILEFORMAT file_format;** ![](https://support.huaweicloud.com/cmpntguide-lts-mrs/public_sys-resources/note_3.0-zh-cn.png) - 该操作仅会改变表或分区的元数据，对存量数据文件的文件类型变更，SQL层面无法操作，只能在外部进行操作。 - 支持的文件格式包括：AVRO、PARQUET、ORC、RCFILE、TEXTFILE和SEQUENCEFILE。 14. 修改表的存储属性，用于修改表的物理存储属性。 **ALTER TABLE table_name CLUSTERED BY (col_name, col_name, ...) \[SORTED BY (col_name, ...)\] INTO num_buckets BUCKETS;** #### 限制 - EXCHANGE PARTITION： - 被迁移的单个或多个分区，迁移前必须都是已存在的分区，并归属于来源表，且在目标表中不包含这些分区； - 该操作涉及的表需要有相同的列定义，并且有相同的分区键； - 如果表中包含索引，该操作会失败； - 来源表和目标表中任意一个为事务表时，不允许Exchange partition操作； - 对于目标表，在一次操作中，多个分区要么同时迁移成功，要么全部失败。对于来源表，操作成功后，所有迁移的分区都会被释放； - Alter table change column不支持orc格式的表。 - ALTER TABLE table_name ADD \| DROP col_name命令仅对于ORC/PARQUET存储格式的非分区表可用。 #### 示例 - 将表名从users修改为people： **ALTER TABLE** *users* **RENAME TO** *people* **;** - 在表users中增加名为zip的列： **ALTER TABLE** *users* **ADD COLUMN** *zip* **varchar;** - 从表users中删除名为zip的列： **ALTER TABLE** *users* **DROP COLUMN** *zip* **;** - 将表users中列名id更改为user_id： **ALTER TABLE** *users* **RENAME COLUMN id TO** *user_id* **;** - 修改分区操作： ``` --创建两个分区表 CREATE TABLE IF NOT EXISTS hetu_int_table5 (eid int, name String, salary String, destination String, dept String, yoj int) COMMENT 'Employee Names' partitioned by (dt timestamp,country String, year int, bonus decimal(10,3)) STORED AS TEXTFILE; CREATE TABLE IF NOT EXISTS hetu_int_table6 (eid int, name String, salary String, destination String, dept String, yoj int) COMMENT 'Employee Names' partitioned by (dt timestamp,country String, year int, bonus decimal(10,3)) STORED AS TEXTFILE; --添加分区 ALTER TABLE hetu_int_table5 ADD IF NOT EXISTS PARTITION (dt='2008-08-08 10:20:30.0', country='IN', year=2001, bonus=500.23) PARTITION (dt='2008-08-09 10:20:30.0', country='IN', year=2001, bonus=100.50) ; --查看分区 show partitions hetu_int_table5; dt | country | year | bonus ---|---|---|--------- 2008-08-09 10:20:30.000 | IN | 2001 | 100.500 2008-08-08 10:20:30.000 | IN | 2001 | 500.230 (2 rows) --删除分区 ALTER TABLE hetu_int_table5 DROP IF EXISTS PARTITION (dt=timestamp '2008-08-08 10:20:30.0', country='IN', year=2001, bonus=500.23); --查看分区 show partitions hetu_int_table5; dt | country | year | bonus ---|---|---|--------- 2008-08-09 10:20:30.000 | IN | 2001 | 100.500 (1 row) --迁移分区示例 CREATE SCHEMA part_test; CREATE TABLE hetu_exchange_partition1 (a string, b string) PARTITIONED BY (ds string); CREATE TABLE part_test.hetu_exchange_partition2 (a string, b string) PARTITIONED BY (ds string); ALTER TABLE hetu_exchange_partition1 ADD PARTITION (ds='1'); --查看分区 show partitions hetu_exchange_partition1; ds ---- 1 (1 row) show partitions part_test.hetu_exchange_partition2; ds ---- (0 rows) --迁移分区，从 T1 到 T2 ALTER TABLE part_test.hetu_exchange_partition2 EXCHANGE PARTITION (ds='1') WITH TABLE hetu_exchange_partition1; --再次查看分区，可以看到分区迁移成功 show partitions hetu_exchange_partition1; ds ---- (0 row) show partitions part_test.hetu_exchange_partition2; ds ---- 1 (1 rows) --重命名分区 CREATE TABLE IF NOT EXISTS hetu_rename_table ( eid int, name String, salary String, destination String, dept String, yoj int) COMMENT 'Employee details' partitioned by (year int) STORED AS TEXTFILE; ALTER TABLE hetu_rename_table ADD IF NOT EXISTS PARTITION (year=2001); SHOW PARTITIONS hetu_rename_table; year ------ 2001 (1 row) ALTER TABLE hetu_rename_table PARTITION (year=2001) rename to partition (year=2020); SHOW PARTITIONS hetu_rename_table; year ------ 2020 (1 row) --修改分区表 create table altercolumn4(a integer, b string) partitioned by (c integer); --修改表的文件格式 alter table altercolumn4 SET FILEFORMAT textfile; insert into altercolumn4 values (100, 'Daya', 500); alter table altercolumn4 partition (c=500) change column b empname string comment 'changed column name to empname' first; --修改分区表的存储位置（需要先在hdfs上创建目录，执行语句后，无法查到之前插入的那条数据） alter table altercolumn4 partition (c=500) set Location '/user/hive/warehouse/c500'; --修改列 b 改名为name，同时类型从integer转为string create table altercolumn1(a integer, b integer) stored as textfile; alter table altercolumn1 change column b name string; --修改altercolumn1的存储属性 ALTER TABLE altercolumn1 CLUSTERED BY(a, name) SORTED BY(name) INTO 25 BUCKETS; --查看altercolumn1的属性 describe formatted altercolumn1; Describe Formatted Table ---------------------------------------------------------------------------------------- # col_name data_type comment a integer name varchar # Detailed Table Information Database: default Owner: admintest LastAccessTime: 0 Location: hdfs://hacluster/user/hive/warehouse/altercolumn1 Table Type: MANAGED_TABLE # Table Parameters: STATS_GENERATED_VIA_STATS_TASK workaround for potential lack of HIVE-12730 numFiles 0 numRows 0 orc.compress.size 262144 orc.compression.codec GZIP orc.row.index.stride 10000 orc.stripe.size 67108864 presto_query_id 20210325_025238_00034_f63xj@default@HetuEngine presto_version rawDataSize 0 totalSize 0 transient_lastDdlTime 1616640758 # Storage Information SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe InputFormat: org.apache.hadoop.mapred.TextInputFormat OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat Compressed: No Num Buckets: 25 Bucket Columns: [a, name] Sort Columns: [SortingColumn{columnName=name, order=ASCENDING}] Storage Desc Params: serialization.format 1 (1 row) Query 20210325_090522_00091_f63xj@default@HetuEngine, FINISHED, 1 node Splits: 1 total, 1 done (100.00%) 0:00 [0 rows, 0B] [0 rows/s, 0B/s] ```