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

[#1]

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.

[#2 · Accepted solution]

@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

[#3]

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!

[#4]

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

[#5]

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

[#6]

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 |

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