Delete partition with non-constant value in Hive

Question

I want to delete a partition in Hive with its value being in another table or being created by a function on-the-fly. For example:

ALTER TABLE
    table_1
DROP IF EXISTS
    PARTITION (dt = FROM_UNIXTIME(UNIX_TIMESTAMP(DATE_SUB(CURRENT_DATE, 63),'yyyy-MM-dd'), 'yyyyMMdd'))

Or something like this:

ALTER TABLE
    table_1
DROP IF EXISTS
    PARTITION (dt = SELECT date FROM table_2 LIMIT 1))

However, this returns the following error:

cannot recognize input near 'FROM_UNIXTIME' '(' 'UNIX_TIMESTAMP' in constant

If I replace the whole call to FROM_UNIXTIME() with a fixed number, it works fine. Is there a way to do this witouth hard-coding the value of the partition?

score 0 · Accepted Answer · answered Jun 14 '19 at 20:01

In Hive-cli doesn't support that, We need to use Shell script for that

Sample Script:

#!/bin/bash
my_value=$(hive -S -e "select FROM_UNIXTIME(UNIX_TIMESTAMP(DATE_SUB(CURRENT_DATE, 63),'yyyy-MM-dd'), 'yyyyMMdd')")
echo $my_value
hive -S -e "alter table table_1 drop partition (dt = $my_value)"

For more details refer to this and this links about hive variables.

Delete partition with non-constant value in Hive

1 Answers1