Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

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

avatar
Expert Contributor

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

avatar
Super Collaborator
hide-solution

This problem has been solved!

Want to get a detailed solution you have to login/registered on the community

Register/Login
5 REPLIES 5

avatar
Super Collaborator
hide-solution

This problem has been solved!

Want to get a detailed solution you have to login/registered on the community

Register/Login

avatar
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!

avatar
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');

avatar
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')

avatar
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 |

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