Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Need to drop a hive partition based on date range (through sub query in Alter statement)

Solved Go to solution
Highlighted

Need to drop a hive partition based on date range (through sub query in Alter statement)

Rising Star

While i was trying to drop a partition based on date range, i unable to achieve it and below is what i am trying to do

alter table X drop partition(partdate <= (select max(partdate) as from Y));

I am getting error, while i am executing above query -- cannot recognize input near '(' 'select' 'max' in constant

Looks like the alter statement accepts only the constant value rather then a sub-query.

Basicaly - Alter table drop partition ( partdate <= date -- and this date need to fetch from another table).

Any help is appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Need to drop a hive partition based on date range (through sub query in Alter statement)

Super Collaborator

@Reddy

The commands below work:

Hive does not accept subquery in that DDL clause, but this works:

ALTER TABLE myTable DROP PARTITION (date < 'date1') , PARTITION (date >'date2');

It needs literals for 'date1' and 'date2'. If you need these to be dynamic then you can use ' --hivevar date1=xxxxx ' for it.

More on that here

5 REPLIES 5

Re: Need to drop a hive partition based on date range (through sub query in Alter statement)

Super Collaborator

@Reddy

The commands below work:

Hive does not accept subquery in that DDL clause, but this works:

ALTER TABLE myTable DROP PARTITION (date < 'date1') , PARTITION (date >'date2');

It needs literals for 'date1' and 'date2'. If you need these to be dynamic then you can use ' --hivevar date1=xxxxx ' for it.

More on that here

Re: Need to drop a hive partition based on date range (through sub query in Alter statement)

New Contributor

It is not working. It deleted all my partitions. Looks like it first delete partitions below date1 and then it deleted partitions above date 2. Which resulted in total data loss!

Re: Need to drop a hive partition based on date range (through sub query in Alter statement)

New Contributor

It should be like,

ALTER TABLE myTable DROP PARTITION (date >'date1'), PARTITION (date <'date2');

,

It should be like

ALTER TABLE myTable DROP PARTITION (date >'date1'), PARTITION (date<'date2');

Re: Need to drop a hive partition based on date range (through sub query in Alter statement)

New Contributor

Don't use this method. It will delete all your partitions. No matter you use

PARTITION (date >'date1'), PARTITION (date <'date2')

OR

PARTITION (date <'date1'), PARTITION (date >'date2')

Re: Need to drop a hive partition based on date range (through sub query in Alter statement)

New Contributor

To drop partitions with a Range filter, use below syntax. (CDH 5.7, Hive 1.1.0).

ALTER TABLE tableName DROP PARTITION (date >='20190410', date <='20190415');


Example:

Before:

+-----------------------+--+

| partition |

+-----------------------+--+

| date=20190401 |

| date=20190402 |

| date=20190403 |

| date=20190404 |

| date=20190405 |

| date=20190406 |

| date=20190410 |

| date=20190411 |

+-----------------------+--+


ALTER TABLE tableName DROP PARTITION (date >='20190410', date <='20190415');

AFTER:

+-----------------------+--+

| partition |

+-----------------------+--+

| date=20190401 |

| date=20190402 |

| date=20190403 |

| date=20190404 |

| date=20190405 |

| date=20190406 |

+-----------------------+--+