How to Update/Drop a Hive Partition?

After adding a partition to an external table in Hive, how can I update/drop it?

5 Answers

You can update a Hive partition by, for example:

ALTER TABLE logs PARTITION(year = 2012, month = 12, day = 18) SET LOCATION 'hdfs://user/darcy/logs/2012/12/18'; 

This command does not move the old data, nor does it delete the old data. It simply sets the partition to the new location.

To drop a partition, you can do

ALTER TABLE logs DROP IF EXISTS PARTITION(year = 2012, month = 12, day = 18); 

Hope it helps!

in addition, you can drop multiple partitions from one statement (Dropping multiple partitions in Impala/Hive).

Extract from above link:

hive> alter table t drop if exists partition (p=1),partition (p=2),partition(p=3); Dropped the partition p=1 Dropped the partition p=2 Dropped the partition p=3 OK 

EDIT 1:

Also, you can drop bulk using a condition sign (>,<,<>), for example:

Alter table t drop partition (PART_COL>1); 

Alter table table_name drop partition (partition_name); 

You can either copy files into the folder where external partition is located or use

INSERT OVERWRITE TABLE tablename1 PARTITION (partcol1=val1, partcol2=val2...)...

statement.

You may also need to make database containing table active

use [dbname] 

otherwise you may get error (even if you specify database i.e. dbname.table )

FAILED Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Unable to alter partition. Unable to alter partitions because table or database does not exist.