How to add partition to an existing table in Hive?

ALTER TABLE ADD PARTITION in Hive

Alter table statement is used to change the table structure or properties of an existing table in Hive. In addition, we can use the Alter table add partition command to add the new partitions for a table. Using partitions, we can query the portion of the data.

For example, A table is created with date as partition column in Hive. When we query from this table for the particular date, It will search the records only in the specified date partitioned file.It will increase the query processing time.

Syntax

Here the IF NOT EXITS and Location are the optional values. We can create the partition by giving the table name and partition specification alone in the add partition statement.

Example for Alter table Add Partition

Lets create the Transaction table with partitioned column as Date and then add the partitions using the Alter table add partition statement.

Create partitioned table in Hive
Create partitioned table in Hive
Adding the new partition in the existing Hive table
Adding the new partition in the existing Hive table

The new partition for the date ‘2019-11-19’ has added in the table Transaction. Similarly we can add the multiple partitions for the different dates as below

Also we can specify the required location in the add partition statement to create the partition file.

If we are not sure whether the partitions exists for the particular date or not, We can use IF NOT EXISTS condition in the Add partition query. It validates the conditions as follows

  • If the Partition exists for the given date => Ignore the add partition command
  • If the Partition doesn’t exist for the given date => Create the partition for it

ALTER Table Drop Partition in Hive

We can delete the partitioned files in Hive using the Alter table Drop partition statement.

Lets drop the few date partitions from the Transaction table.