Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Dropping a range of partitions in HIVE

Tags:

hql

hive

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');
like image 243
Rajeev Atmakuri Avatar asked Oct 16 '15 13:10

Rajeev Atmakuri


Video Answer


1 Answers

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

like image 88
Kharthigeyan Avatar answered Sep 27 '22 16:09

Kharthigeyan