question Re: Need to drop a hive partition based on date range (through sub query in Alter statement) in Archives of Support Questions (Read Only)

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

PentaReddy — Fri, 27 Jan 2017 08:07:19 GMT

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.

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

jknulst — Fri, 27 Jan 2017 17:42:00 GMT

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

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

goguladileepkum — Thu, 06 Jul 2017 16:48:00 GMT

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)

rkarthik2468 — Mon, 23 Oct 2017 19:05:35 GMT

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)

lonelybooky — Thu, 13 Dec 2018 12:51:39 GMT

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

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

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

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

shivamohanmp — Sat, 27 Apr 2019 22:55:15 GMT

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 |

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