How to add partition to an existing table in Hive?
Contents
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, which reduces the time taken by query to produce the result.
Syntax
|
1 2 3 |
ALTER TABLE <Table_Name> ADD [IF NOT EXISTS] PARTITION <partition_spec> LOCATION 'hdfs_path_of_directory' |
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.
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
|
1 2 |
Alter Table Transaction Add Partition (Day=date '2019-11-20') Partition(Day=date '2019-11-21'); |
Also we can specify the required location in the add partition statement to create the partition file.
|
1 2 |
ALTER TABLE Transaction ADD PARTITION (Day=date '2019-11-22') LOCATION '/apps/bank/cust_transactions/00'; |
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
|
1 2 |
Alter Table Transaction Add IF NOT EXISTS Partition (Day=date '2019-11-22') Partition(Day=date '2019-11-23'); |
ALTER Table Drop Partition in Hive
We can delete the partitioned files in Hive using the Alter table Drop partition statement.
|
1 2 |
ALTER TABLE <table_name> DROP IF EXISTS PARTITION (<Partitioned_column> = '<value>'); |
Lets drop the few date partitions from the Transaction table.
|
1 2 |
ALTER TABLE Transaction DROP IF EXISTS PARTITION (Day= '2019-11-22'); |
Recommended Articles
