ALTER TABLE
Description
Modifies an existing table, including:
- Rename table, partition, index
- Atomic swap
- Add/delete partitions and modify partition attributes
- Schema change
- Create/delete rollup index
- Modify bitmap index
Syntax
ALTER TABLE [database.]table
alter_clause1[, alter_clause2, ...]
alter_clause
is classified into six operations: partition, rollup, schema change, rename, index, and swap.
- rename: renames a table, rollup index, or partition.
- comment: modifies the table comment (supported from v3.1 onwards).
- partition: modifies partition properties, drops a partition, or adds a partition.
- bucket: modifies the bucketing method and number of buckets.
- column: adds, drops, or reorders columns, or modifies column type.
- rollup index: creates or drops a rollup index.
- bitmap index: modifies index (only Bitmap index can be modified).
- swap: atomic exchange of two tables.
Limits and usage notes
- Operations on partition, column, and rollup index cannot be performed in one ALTER TABLE statement.
- Column names cannot be modified.
- Column comments cannot be modified.
- One table can have only one ongoing schema change operation at a time. You cannot run two schema change commands on a table at the same time.
- Operations on bucket, column and rollup index are asynchronous operations. A success message is return immediately after the task is submitted. You can run the SHOW ALTER TABLE command to check the progress, and run the CANCEL ALTER TABLE command to cancel the operation.
- Operations on rename, comment, partition, bitmap index and swap are synchronous operations, and a command return indicates that the execution is finished.
Rename
Rename supports modification of table name, rollup index, and partition name.
Rename a table
ALTER TABLE <tbl_name> RENAME <new_tbl_name>
Rename a rollup index
ALTER TABLE [<db_name>.]<tbl_name>
RENAME ROLLUP <old_rollup_name> <new_rollup_name>
Rename a partition
ALTER TABLE [<db_name>.]<tbl_name>
RENAME PARTITION <old_partition_name> <new_partition_name>
Modify partition
Add a partition
Syntax:
ALTER TABLE [database.]table
ADD PARTITION [IF NOT EXISTS] <partition_name>
partition_desc ["key"="value"]
[DISTRIBUTED BY HASH (k1[,k2 ...]) [BUCKETS num]];
Note:
-
Partition_desc supports the following two expressions:
VALUES LESS THAN [MAXVALUE|("value1", ...)]
VALUES ("value1", ...), ("value1", ...) -
partition is the left-closed-right-open interval. If the user only specifies the right boundary, the system will automatically determine the left boundary.
-
If the bucket mode is not specified, the bucket method used by the built-in table is automatically used.
-
If the bucket mode is specified, only the bucket number can be modified, and the bucket mode or bucket column cannot be modified.
-
User can set some properties of the partition in ["key"="value"]. See CREATE TABLE for details.
Drop a partition
Syntax:
-- Before 2.0
ALTER TABLE [database.]table
DROP PARTITION [IF EXISTS | FORCE] <partition_name>
-- 2.0 or later
ALTER TABLE [database.]table
DROP PARTITION [IF EXISTS] <partition_name> [FORCE]
Note:
- Keep at least one partition for partitioned tables.
- After executing DROP PARTITION for a while, the dropped partition can be recovered by the RECOVER statement. See the RECOVER statement for details.
- If DROP PARTITION FORCE is executed, the partition will be deleted directly and cannot be recovered without checking whether there are any unfinished activities on the partition. Thus, generally this operation is not recommended.
Add a temporary partition
Syntax:
ALTER TABLE [database.]table
ADD TEMPORARY PARTITION [IF NOT EXISTS] <partition_name>
partition_desc ["key"="value"]
[DISTRIBUTED BY HASH (k1[,k2 ...]) [BUCKETS num]]
Use a temporary partition to replace current partition
Syntax:
ALTER TABLE [database.]table
REPLACE PARTITION <partition_name>
partition_desc ["key"="value"]
WITH TEMPORARY PARTITION
partition_desc ["key"="value"]
[PROPERTIES ("key"="value", ...)]