Drop multiple partitions in Hive

Drop multiple partitions in Hive

Requirement

Suppose we are having a hive partition table. This table is partitioned by the year of joining. Our requirement is to drop multiple partitions in hive.

Components Involved

  • Hive
  • HDFS

Sample Data

Let’s say we are having given sample data:

Here, 1 record belongs to 1 partition as we will store data partitioned by the year of joining. In actual, there will be many records for each partition.

Solution

Step 1:  Create Table & Load data

If you already have a partitioned table, then skip this step else read this post for creating a table and loading data into it.

Step 2: Drop Multiple Partitions

If you see sample data, we are having 10 partitions of the year from 2005 to 2014.  Let’s check the partitions in the table:

In case, you want to add multiple partitions in the table, then mention all the partitions in the query like given below:

 
 
alter table employee add partition (year=2005) partition (year=2006) partition (year=2007) partition (year=2008) partition (year=2009) partition (year=2010) partition (year=2011) partition (year=2014);

 

Here, all the given partitions will get added to the table in a single query.

CASE I: Drop Specific Partitions

We will use this step’s command if we want to drop some specific partitions from the table. Here, we are going to drop partition 2008, 2009 and 2010 only.

 
 
ALTER TABLE db_bdpbase.Employee DROP IF EXISTS PARTITION (year=2008), PARTITION(year=2009), PARTITION(year=2010);

Here, I have mentioned all the specific partitions separated by a comma in the query. It will drop all mentioned partitions in a single query.

CASE II: Drop Range Partition

Here, We want to drop all partition above the value of 2010. That means we have to drop the partition from the value 2011 to 2014.

First, check all available partitions in the table

 
 
ALTER TABLE db_bdpbase.Employee DROP IF EXISTS PARTITION(year>2010);

It will drop all partitions from 2011 to 2014.

Wrapping Up

In this post, we have seen how we can add multiple partitions as well as drop multiple partitions from the hive table. We can drop multiple specific partitions as well as any range kind of partition.

0
0

Join in hive with example

Requirement You have two table named as A and B. and you want to perform all types of join in ...
Read More

Join in pyspark with example

Requirement You have two table named as A and B. and you want to perform all types of join in ...
Read More

Join in spark using scala with example

Requirement You have two table named as A and B. and you want to perform all types of join in ...
Read More

Java UDF to convert String to date in PIG

About Code Many times it happens like you have received data from many systems and each system operates on a ...
Read More
/ java udf, Pig, pig, pig udf, string to date, udf

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.