How to update or drop a Hive Partition?

It is a common use case in your production jobs or Hive scripts to update or drop a Hive partition from your table. Let’s see how to update Hive partitions first and then see how to drop partitions and few variations of the same.

Update a Hive partition

Let’s say you had an issue with the way the data was loaded into a partition and now you have found a way to fix the data and fixed it. The corrected date is under hdfs://user/svc_account/fixed_date/2020/2. Here is the alter command to update the partition of the table sales.

hive> ALTER TABLE sales PARTITION(year = 2020, quarter = 2) SET LOCATION 'hdfs://user/svc_account/fixed_date/2020/2';

Drop a Hive partition

Let’s see a few variations of drop partition.

Drop a single partition

hive> ALTER TABLE sales DROP IF EXISTS PARTITION(year = 2020, quarter = 2);

Drop multiple partitions 

With the below alter script, we provide the exact partitions we would like to delete.

hive> ALTER TABLE sales drop if exists partition (year = 2020, quarter = 1), partition (year = 2020, quarter = 2);

Here is how we dynamically pick partitions to drop. Below script drops all partitions from sales table with year greater than 2019.

hive> ALTER TABLE sales drop partition (year > 2019);