All Products
Search
Document Center

ALTER TABLE

Last Updated: May 13, 2019

Change partitions of a table

Add partitions

Syntax

  1. ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec [LOCATION 'location'][, PARTITION partition_spec [LOCATION 'location'], ...];
  2. partition_spec:
  3. : (partition_column = partition_col_value, partition_column = partition_col_value, ...)

Note: If you specify IF NOT EXISTS when adding a partition and the partition that you want to add already exists, the new directory specified by the LOCATION parameter overwrites the directory to which the original partition points.

Example

  1. alter table test_opencsv_part add partition(dt = 'bar') location 'oss://oss-cn-beijing-for-openanalytics-test/datasets/test/test_opencsv_part/dt=foo/';

Delete partitions

Syntax

  1. ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec[, PARTITION partition_spec, ...] ;

Note:

  • In the preceding command, you can specify the partitions that you want to delete only by setting Partition column name=Partition column value. The partitions cannot be specified by an expression, such as partitionCol > 100.

  • If a deleted partition complies with the naming rule Partition column name=Partition column value, the MSCK command will automatically add this partition.

Example

  1. ALTER TABLE order_part DROP
  2. PARTITION (dt='2008-08-08', status='ready');