This statement is used to modify the partition of an existing table.

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

grammar:

1. Add partition

grammar:

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

1. Delete the partition

grammar:

DROP PARTITION [IF EXISTS] partition_name [FORCE]

• 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

1. Modify the partition properties

   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.

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

1. Increase the partition and use the new number of buckets

ADD PARTITION p1 VALUES LESS THAN ("2015-01-01")

1. Increase the partition and use the new number of replicas

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

1. Modify the number of partition replicas

MODIFY PARTITION p1 SET("replication_num"="1");

1. Batch modify the specified partition

1. Batch modify all partitions

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

1. Delete partition

ALTER TABLE example_db.my_table
DROP PARTITION p1;

1. Add a partition specifying upper and lower bounds