Search code examples
sqldatabasehivedatabase-partitioning

Delete partition with non-constant value in Hive

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?

Solution

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