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:

 
 
  1. 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.

 
 
  1. 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

 
 
  1. 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.

Load CSV file into hive AVRO table

Requirement You have comma separated(CSV) file and you want to create Avro table in hive on top of it, then ...
Read More

Load CSV file into hive PARQUET table

Requirement You have comma separated(CSV) file and you want to create Parquet table in hive on top of it, then ...
Read More

Hive Most Asked Interview Questions With Answers – Part II

What is bucketing and what is the use of it? Answer: Bucket is an optimisation technique which is used to ...
Read More
/ hive, hive interview, interview-qa

Spark Interview Questions Part-1

Suppose you have a spark dataframe which contains millions of records. You need to perform multiple actions on it. How ...
Read More

Leave a Reply