Help Center> Data Lake Insight> SQL Syntax Reference> SQL Syntax of Batch Jobs> Syntax for Partitioning a Table> Adding a Partition (Only OBS Tables Supported)
View PDF

Adding a Partition (Only OBS Tables Supported)

Function

This statement is used to add partitions.

Syntax

1
2
3
4
5
 
ALTER TABLE table_name ADD [IF NOT EXISTS]
  PARTITION partition_specs1
  [LOCATION 'obs_path1']
  PARTITION partition_specs2
  [LOCATION 'obs_path2'];

Keyword

None

Precautions

  • When you add a partition to a table, the table must exist, but the partition to be added cannot exist. Otherwise, an error occurs. You can use IF NOT EXISTS to avoid errors in the existing partitions.
  • If partition tables are partitioned based on multiple fields, you need to specify all partition fields in any sequence when adding partitions.
  • By default, the partition_specs parameter contains (). For example: PARTITION (dt='2009-09-09',city='Shanghai')
  • If you need to specify an OBS path when adding a partition, the OBS path must exist. Otherwise, an error occurs.
  • To add multiple partitions, you need to use spaces to separate each set of LOCATION 'obs_path' in the PARTITION partition_specs. The following is an example:

    PARTITION partition_specs LOCATION 'obs_path' PARTITION partition_specs LOCATION 'obs_path'

  • If the path specified in the new partition contains subdirectories (or nested subdirectories), all file types and content in the subdirectories are considered partition records. Ensure that all file types and file content in the partition directory are the same as those in the table. Otherwise, an error is reported.

Example

  • To add the partition dt='2009-09-09',city='Shanghai' to the student table, run the following statement: 
    1
2
3
    		 
    ALTER TABLE student ADD
  PARTITION (dt='2009-09-09',city='Shanghai')
  LOCATION 'obs://bucketName/fileName/students/dt=2009-09-09/city=Shanghai';
  • To add a partition (DT = '2009-09-09',city = 'Shanghai') and a partition (dt='2008-08-08',city='Hangzhou') to the student table, run the following statement: 
    1
2
3
    		 
    ALTER TABLE student ADD
  PARTITION(dt='2009-09-09',city='Shanghai')
  PARTITION(dt='2008-08-08',city='Hangzhou');