ALTER TABLE

Updated on 2024-12-13 GMT+08:00

View PDF

Syntax

NOTE:

name, new_name, column_name, 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

Change the column name of the table and add comments (optional) and properties (optional) to the column. For details about supported column properties, see Description.
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
NOTICE:
- Partition or bucket columns cannot be deleted.
- DROP COLUMN does not support tables stored in RCTEXT, RCBINARY, or RCFILE format. Connector accesses columns in different file formats in different modes. The query may fail after the DROP COLUMN operation is performed. For example:
  - For a non-partitioned table stored in ORC format, if the query fails after the DROP COLUMN operation is performed, run the following command to set the Session property:
    set session hive.orc_use_column_names=true;
  - For a non-partitioned table stored in Parquet format, if the query fails after the DROP COLUMN operation is performed, run the following command to set the Session property:
    set session hive.parquet_use_column_names=true;
  - For partitioned or transaction tables in ORC or Parquet format, session properties cannot be configured to ensure query success after the DROP COLUMN operation is performed.

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

NOTICE:

Partition or bucket columns cannot be renamed.

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 data 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, ...];

NOTICE:

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, …] );
NOTE:
TBLPROPERTIES allows you to add or modify table attributes supported by a connector in key-value pair mode (attribute names and attributes must be strings enclosed in single or double quotation marks). The following uses the Hive connector as an example:
- TBLPROPERTIES ("transactional"="true"). The value can be true or false.
- TBLPROPERTIES ("auto.purge"="true"). The value can be true or false.

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]
NOTICE:
- 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 Optimized Row Columnar (ORC) tables cannot be modified.
The following statement is used to change the storage location of the table or partition.
ALTER TABLE table_name [PARTITION partition_spec] SET LOCATION location;
NOTE:
- 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;
NOTE:
- 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.
- Tables involved in this operation must have the same column definition and 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 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 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');
 
--View the partition.
 show partitions hetu_exchange_partition1;
 ds 
----
 1  
(1 row)
 
show partitions part_test.hetu_exchange_partition2;
 ds 
----
(0 rows)
 
--Migrate the partition from table 1 to table 2.
ALTER TABLE part_test.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 part_test.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 the data type from integer to string.
create table altercolumn1(a integer, b integer) stored as textfile;
 
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]

Parent topic: HetuEngine DDL SQL Syntax

Previous topic: ALTER MATERIALIZED VIEW

Next topic: ALTER VIEW