Dynamic Data in ALTER TABLE (Hive)

Question

I'm getting an error with my query and I'm not entirely sure why:

ALTER TABLE revenue ADD PARTITION (ds=from_unixtime(unix_timestamp(), 'yyyy-MM-dd')) LOCATION CONCAT('s3://userenroll-analytics/prod/revenue/avro/', from_unixtime(unix_timestamp(), 'yyyy/MM/dd'))

Error:

Error while compiling statement: FAILED: ParseException line 1:38 cannot recognize input near 'from_unixtime' '(' 'unix_timestamp' in constant

Is there a way to use a dynamically generated value in a Hive ALTER TABLE query?

(P.S. - For those of you who say I should use dynamic partitions... I don't want to use the directory structure syntax mandated by Hive.)

Answer 1

when you use dynamic partitions for all partition fields you need to ensure that you are using nonstrict for your dynamic partition mode (hive.exec.dynamic.partition.mode)

use below command before your query.

SET hive.exec.dynamic.partition.mode=nonstrict;

Answer 2

Run following statement before alter table statment.

MSCK REPAIR TABLE revenue;

This statement will adds metadata about the partitions to the Hive catalogs.