ALTER TABLE
Syntax
name, new_name, column_nam, new_column_name and table_name_* are user-defined parameters.
- The following statement is used to rename a table.
ALTER TABLE name RENAME TO new_name
When a Hive data source is connected, external partitioned tables do not support this operation.
- The following statement is used to change the column name of a table and add comments (optional) and properties (optional) to the column.
ALTER TABLE name ADD COLUMN column_name data_type [ COMMENT comment ] [ WITH ( property_name = expression [, ...] ) ]
- The following statement is used to delete the column_name column from the table.
ALTER TABLE name DROP COLUMN column_name
The DROP COLUMN operation cannot be performed on a table that contains data.
- The following statement is used to rename the column_name column to new_column_name.
ALTER TABLE name RENAME COLUMN column_name TO new_column_name
This syntax is not supported when an external Hive data source is used.
- The following statement is used to add partitions to a partitioned table.
ALTER TABLE name ADD [IF NOT EXISTS] PARTITION partition_spec [LOCATION 'location'][ PARTITION partition_spec [LOCATION 'location'], ...];
- The following statement is used to delete a partition from a partitioned table. This operation deletes data and metadata from the partition. If the directory for storing partition is specified when ADD PARTITION is run, the folder where the partition is located and date will not be deleted after DROP PARTITION is run, regardless of whether the table is an internal table or external table. If the directory for storing partition is specified when ADD PARTITION is run, the directory will be deleted from HDFS and data is moved to the .Trash/Current folder.
ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec[, PARTITION partition_spec, ...];
When an external Hive data source is used, if the partition key is a fixed-length string, for example, char(5), and the string length of the corresponding data is fewer than five characters, the drop partition operation fails.
- The following statement is used to rename a partition.
ALTER TABLE table_name PARTITION(partition_key = partition_value1) rename to partition(partition_key = partition_value2)
- The following statement is used to migrate the partition of table_name_1 to table_name_2.
ALTER TABLE table_name_2 EXCHANGE PARTITION (partition_spec) WITH TABLE table_name_1;
- The following statement is used to migrate multiple partitions of table_name_1 to table_name_2.
ALTER TABLE table_name_2 EXCHANGE PARTITION (partition_spec, partition_spec2, ...) WITH TABLE table_name_1;
- The following statements are used to add or modify table properties.
ALTER TABLE table_name SET TBLPROPERTIES (property_name = property_value, property_name = property_value, ... );
If the table properties to be set do not exist, new properties are added. If the table properties exist, the original properties will be modified, and the property name and property value must be strings enclosed in single quotation marks.
- The following statement is used to modify column properties.
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]
- For an existing table, modify the column name, data type, comment, location ([FIRST|AFTER column_name] is used to specify the location of the column after modification), or any combination of the preceding items. If a partition clause is included in the syntax, the metadata of the corresponding partition also changes. In CASCADE mode, the syntax will take effect on the metadata of the table and table partition. In the default RESTRICT mode, the modification to a column takes effect only on the metadata of the table.
- The column modification statement can modify only the metadata of a table or partition, but cannot modify the data itself. Ensure that the actual data layout of the table or partition complies with the metadata definition.
- The partition column or bucket column of a table and the column of an Optimized Row Columnar (ORC) transaction table cannot be modified.
- The following statement is used to change the storage location of the partition.
ALTER TABLE table_name [PARTITION partition_spec] SET LOCATION location;
- You can run the ALTER TABLE [PARTITION] SET statement to set the table or partition location.
- After the SET LOCATION statement is run, table or partition data may not be displayed.
- When a table or partition directory is created, SET LOCATION uses the specified directory instead of the default directory created on Hive during the creation of the table or partition.
- This statement does not affect the original data in the table or partition, or modify the original table or partition directory. New data is saved to the new directory.
- The following statement is used to change the format of the data file of a table or partition.
ALTER TABLE table_name [PARTITION partition_spec] SET FILEFORMAT file_format;
- This operation changes the metadata of a table or partition and the type of the inventory data file. The operation cannot be performed at the SQL layer and can only be performed externally.
- The following file formats are supported: AVRO, PARQUET, ORC, RCFILE, TEXTFILE, and SEQUENCEFILE.
- The following statement is used to modify the physical storage properties of a table.
ALTER TABLE table_name CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name, ...)] INTO num_buckets BUCKETS;
Remarks
- EXCHANGE PARTITION:
- The single or multiple partitions to be migrated must exist and belong to the source table before migration, and the partitions are not included in the target table.
- The tables involved in this operation must be in the same schema and have the same partition key.
- If the table contains indexes, the operation fails.
- If either the source table or the target table is a transaction table, the EXCHANGE PARTITION operation is not allowed.
- The operation result for the target table is that multiple partitions are successfully migrated at the same time or fail to be migrated. For the source table, all migrated partitions are released after the operation is successful.
- The ALTER TABLE statement for column modification does not support ORC tables.
- The ALTER TABLE table_name ADD | DROP col_name statement is available only for non-partitioned tables in ORC or PARQUET format.
Example
- To change the table name from users to people:
ALTER TABLE users RENAME TO people;
- To add the zip column to the users table:
ALTER TABLE users ADD COLUMN zip varchar;
- To delete the zip column from the users table:
ALTER TABLE users DROP COLUMN zip;
- To change the column name id in the users table to user_id:
ALTER TABLE users RENAME COLUMN id TO user_id;
- To add the creator property to the users table. The value is user1.
You can run the DESCRIBE EXTENDED/FORMATTED TABLE statement to view the result.
ALTER TABLE users set tblproperties('creator' = 'user1'); --View the new property. DESCRIBE FORMATTED users; Describe Formatted Table ------------------------------------------------------------------------------ # col_name data_type comment id integer name varchar # Detailed Table Information Database: default Owner: admintest LastAccessTime: 0 Location: hdfs://hacluster/user/hive/warehouse/users Table Type: MANAGED_TABLE # Table Parameters: STATS_GENERATED_VIA_STATS_TASK workaround for potential lack of HIVE-12730 creator user1 numFiles 0 numRows 0 orc.compress.size 262144 orc.compression.codec GZIP orc.row.index.stride 10000 orc.stripe.size 67108864 presto_query_id 20210308_023136_00031_jiwsq@default@HetuEngine presto_version rawDataSize 0 totalSize 0 transient_lastDdlTime 1615170696 # Storage Information SerDe Library: org.apache.hadoop.hive.ql.io.orc.OrcSerde InputFormat: org.apache.hadoop.hive.ql.io.orc.OrcInputFormat OutputFormat: org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat Compressed: No Num Buckets: -1 Bucket Columns: [] Sort Columns: [] serialization.format: 1 (1 row) Query 20210308_032152_00046_jiwsq@default@HetuEngine, FINISHED, 1 node Splits: 1 total, 1 done (100.00%) 0:00 [0 rows, 0B] [0 rows/s, 0B/s] - To modify a partition:
--Create two partitioned tables. 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; --Add partitions. 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) ; --View the partition. 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) --Delete a partition. 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); --View the partition. show partitions hetu_int_table5; dt | country | year | bonus -------------------------|---------|------|--------- 2008-08-09 10:20:30.000 | IN | 2001 | 100.500 (1 row) --Example of migrating a partition CREATE TABLE hetu_exchange_partition1 (a string, b string) PARTITIONED BY (ds string); CREATE TABLE hetu_exchange_partition2 (a string, b string) PARTITIONED BY (ds string); ALTER TABLE hetu_exchange_partition1 ADD PARTITION (ds='1'); --View the partition. show partitions hetu_exchange_partition1; ds ---- 1 (1 row) show partitions hetu_exchange_partition2; ds ---- (0 rows) --Migrate the partition from table 1 to table 2. ALTER TABLE hetu_exchange_partition2 EXCHANGE PARTITION (ds='1') WITH TABLE hetu_exchange_partition1; --View the partition again. The partition is successfully migrated. show partitions hetu_exchange_partition1; ds ---- (0 row) show partitions hetu_exchange_partition2; ds ---- 1 (1 rows) --Rename a partition. 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) --Modify a partitioned table. create table altercolumn4(a integer, b string) partitioned by (c integer); --Modify the file format of the table. 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; --Change the storage location of the partitioned table. (You need to create a directory in HDFS. After the statement is run, the inserted data cannot be queried.) alter table altercolumn4 partition (c=500) set Location '/user/hive/warehouse/c500'; -- Change the name of column b to name and change the type from integer to string. (Before modifying the column properties, add hive.orc.use-column-names=true to the properties of the Hive data source. Otherwise, an error is reported.) create table altercolumn1(a integer, b integer); alter table altercolumn1 change column b name string; --Modify the storage property of altercolumn1. ALTER TABLE altercolumn1 CLUSTERED BY(a, name) SORTED BY(name) INTO 25 BUCKETS; --View the properties of 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]
Precautions
When modifying the column properties of a table in the ORC storage format, add hive.orc.use-column-names=true to the properties of the Hive data source. Otherwise, an error will be reported when you modify the column properties.
- For co-deployed Hive:
Log in to FusionInsight Manager, choose Services > HetuEngine > Configurations > All Configurations, search for hive.properties in the search box, and add the following custom configuration item:
- For Hive that is independently deployed:
Log in to FusionInsight Manager, choose Services > HetuEngine > Dashboard, click the link next to HSConsole WebUI to go to the compute instance page, and choose Data Source > Hive data source name > Edit. On the Custom Configuration page, click Add to add the custom configuration item.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.
