ALTER-TABLE-PARTITION

ALTER TABLE PARTITION

This statement is used to modify a table with a partition.

This operation is synchronous, and the return of the command indicates the completion of the execution.

grammar:

ALTER TABLE [database.]table alter_clause;

The alter_clause of partition supports the following modification methods

grammar:

ADD PARTITION [IF NOT EXISTS] partition_name
partition_desc ["key"="value"]
[DISTRIBUTED BY HASH (k1[,k2 ...]) [BUCKETS num]]

Notice:

partition_desc supports the following two ways of writing
- VALUES LESS THAN [MAXVALUE|("value1", ...)]
- VALUES [("value1", ...), ("value1", ...))
The partition is left closed and right open. If the user only specifies the right boundary, the system will automatically determine the left boundary
If the bucketing method is not specified, the bucketing method and bucket number used for creating the table would be automatically used
If the bucketing method is specified, only the number of buckets can be modified, not the bucketing method or the bucketing column. If the bucketing method is specified but the number of buckets not be specified, the default value 10 will be used for bucket number instead of the number specified when the table is created. If the number of buckets modified, the bucketing method needs to be specified simultaneously.
The ["key"="value"] section can set some attributes of the partition, see CREATE TABLE
If the user does not explicitly create a partition when creating a table, adding a partition by ALTER is not supported
If the user uses list partition then they can add default partition to the table. The default partition will store all data not satisfying prior partition key's constraints.
- ALTER TABLE table_name ADD PARTITION partition_name

grammar:

DROP PARTITION [IF EXISTS] partition_name [FORCE]

Notice:

At least one partition must be reserved for tables using partitioning.
After executing DROP PARTITION for a period of time, the deleted partition can be recovered through the RECOVER statement. For details, see SQL Manual - Database Management - RECOVER Statement
If you execute DROP PARTITION FORCE, the system will not check whether there are unfinished transactions in the partition, the partition will be deleted directly and cannot be recovered, this operation is generally not recommended

grammar:

MODIFY PARTITION p1|(p1[, p2, ...]) SET ("key" = "value", ...)

illustrate:

Currently supports modifying the following properties of partitions:
- storage_medium -storage_cooldown_time
- replication_num
- in_memory
For single-partition tables, partition_name is the same as the table name.

Add partition, existing partition [MIN, 2013-01-01), add partition [2013-01-01, 2014-01-01), use default bucketing method

ALTER TABLE example_db.my_table
ADD PARTITION p1 VALUES LESS THAN ("2014-01-01");

ALTER TABLE example_db.my_table
ADD PARTITION p1 VALUES LESS THAN ("2015-01-01")
DISTRIBUTED BY HASH(k1) BUCKETS 20;

ALTER TABLE example_db.my_table
ADD PARTITION p1 VALUES LESS THAN ("2015-01-01")
("replication_num"="1");

ALTER TABLE example_db.my_table
MODIFY PARTITION p1 SET("replication_num"="1");

ALTER TABLE example_db.my_table
MODIFY PARTITION (p1, p2, p4) SET("replication_num"="1");

ALTER TABLE example_db.my_table
MODIFY PARTITION (*) SET("storage_medium"="HDD");

ALTER TABLE example_db.my_table
DROP PARTITION p1;

ALTER TABLE example_db.my_table
DROP PARTITION p1,
DROP PARTITION p2,
DROP PARTITION p3;

ALTER TABLE example_db.my_table
ADD PARTITION p1 VALUES [("2014-01-01"), ("2014-02-01"));

ALTER, TABLE, PARTITION, ALTER TABLE

ALTER-TABLE-PARTITION​