how to drop hive partitions with dynamic values

Question

I'm looking for a way to drop partitions in relation to the current day.

alter table table_name drop partition(rep_date < from_unixtime(unix_timestamp(),'yyyy-MM-dd'));

This returns an error:

cannot recognise input near from(unix...

I can do this without literally putting in '2017-06-14'.Can I cast this into a literal type? When ever I try to put 'cast' in it doesn't like it?

Answer 1

Demo

hive

create table mytable (i int) partitioned by (dt date)
;

alter table mytable add
    partition (dt=date '2017-06-11')
    partition (dt=date '2017-06-12')
    partition (dt=date '2017-06-13')
    partition (dt=date '2017-06-14')
    partition (dt=date '2017-06-15')
    partition (dt=date '2017-06-16')
    partition (dt=date '2017-06-17')
;

show partitions mytable
;

---

+---------------+
|   partition   |
+---------------+
| dt=2017-06-11 |
| dt=2017-06-12 |
| dt=2017-06-13 |
| dt=2017-06-14 |
| dt=2017-06-15 |
| dt=2017-06-16 |
| dt=2017-06-17 |
+---------------+

bash

hive --hivevar dt="$(date +'%Y-%m-%d')" -e 'alter table mytable drop partition (dt < date "${hivevar:dt}")'

...
Dropped the partition dt=2017-06-11
Dropped the partition dt=2017-06-12
Dropped the partition dt=2017-06-13
OK
Time taken: 1.621 seconds
...
bash-4.1$ 

hive

show partitions mytable
;

---

+---------------+
|   partition   |
+---------------+
| dt=2017-06-14 |
| dt=2017-06-15 |
| dt=2017-06-16 |
| dt=2017-06-17 |
+---------------+