Dropping a range of partitions in HIVE

Question

I have a Hive (ver 0.11.0) table partitioned by column date, of type string. I want to know if there exists a way in Hive by which I can drop partitions for a range of dates (say from 'date1' to 'date2'). I have tried the following (SQL type) queries, but they don't seem to be syntactically correct:

ALTER TABLE myTable DROP IF EXISTS PARTITION
(date>='date1' and date<='date2');

ALTER TABLE myTable DROP IF EXISTS PARTITION
(date>='date1' && date<='date2');

ALTER TABLE myTable DROP IF EXISTS PARTITION
(date between 'date1' and 'date2');

score 21 · Accepted Answer · answered May 18 '18 at 09:58

21

I tried this syntax it worked.

ALTER TABLE mytable DROP PARTITION (dates>'2018-04-14',dates<'2018-04-16');

Command output:

    Dropped the partition dates=2018-04-15/country_id=107
    Dropped the partition dates=2018-04-15/country_id=110
    Dropped the partition dates=2018-04-15/country_id=112
    Dropped the partition dates=2018-04-15/country_id=14
    Dropped the partition dates=2018-04-15/country_id=157
    Dropped the partition dates=2018-04-15/country_id=159
    Dropped the partition dates=2018-04-15/country_id=177
    Dropped the partition dates=2018-04-15/country_id=208
    Dropped the partition dates=2018-04-15/country_id=22
    Dropped the partition dates=2018-04-15/country_id=233
    Dropped the partition dates=2018-04-15/country_id=234
    Dropped the partition dates=2018-04-15/country_id=76
    Dropped the partition dates=2018-04-15/country_id=83
    OK
    Time taken: 0.706 seconds

I am using, Hive 1.2.1000.2.5.5.0-157

answered May 18 '18 at 09:58

Kharthigeyan

555
4
14

I *think* this will drop everything on the table? Larger than 201-04-14 OR smaller than 2018-04-16... – Hein du Plessis Dec 12 '18 at 09:16
1

@HeinduPlessis This is AND not OR. Example: dates>'2018-04-14' and dates<'2018-04-16' – Kharthigeyan Dec 16 '18 at 20:06
1

@HeinduPlessis, no, it will not drop other partitions, it ONLY drops those in between – lhdgriver Jan 08 '21 at 07:42
@HeinduPlessis can you confirm that this answer is more elegant and up-to-date than my post on Aug.2017? (see above). If so, I will be happy to delete my misleading and outdated solution. Many thanks and best regards. – vreyespue Apr 27 '21 at 09:53
1

@vreyespue I think there's use for both - your script is much more dynamic, but for the OP's (and my) requirement, this answer is easier to apply. – Hein du Plessis Apr 28 '21 at 11:15
2

@Kharthigeyan indeed I never realised it's an AND logic. – Hein du Plessis Apr 28 '21 at 11:16

score 2 · Answer 2 · answered Jan 24 '18 at 12:15

2

Solution: alter table myTable drop partition (unix_timestamp('date1','yyyy-MM-dd')>unix_timestamp(myDate,‌'yyyy-MM-dd'),unix_t‌imestamp('date2','yy‌yy-MM-dd')<unix_time‌stamp(myDate,'yyyy-M‌M-dd'));

answered Jan 24 '18 at 12:15

HakkiBuyukcengiz

417
4
18

What is here date1, date2 and myDate. Say you want to delete between 2018-11-01 and 2019-02-12? – Paul Velthuis Feb 11 '19 at 18:05
Alter table mytable drop partition (myDate > '2018-11-01' , myDate < '2019-02-12') – HakkiBuyukcengiz Feb 12 '19 at 19:59

Dropping a range of partitions in HIVE

2 Answers2

Linked