Support Questions

Find answers, ask questions, and share your expertise
Celebrating as our community reaches 100,000 members! Thank you!

Hive: Set variable for Static Partitioning

New Contributor

Hello All,

How to set variable in hive and insert value as partition? I'm passing {counter} as parameter.


Consider counter as 5

set hiveConf:var= date_sub(current_date-{counter});

Select ${hiveConf:var} ; Gives proper output i.e. 2017-11-18


But gives error when the same is used as below:


INSERT INTO TABLE am_temp2 Partition (search_date=${hiveConf:var})
SELECT * FROM am_temp1;


Error: NoViableAltException(16@[233:1: constant : ( Number | dateLiteral | timestampLiteral | StringLiteral | stringLiteralSequence | BigintLiteral | SmallintLiteral | TinyintLiteral | DecimalLiteral | charSetStringLiteral | booleanValue );])

FAILED: ParseException line 1:189 cannot recognize input near '$' '{' 'hiveconf' in constant


Please let know of any solution for static partitioning



Of course you have a error. Because the insert statement tries to do this:

INSERT INTO TABLE am_temp2 Partition (search_date=2017-11-18)

insted of this:

INSERT INTO TABLE am_temp2 Partition (search_date="2017-11-18")

or this:

INSERT INTO TABLE am_temp2 Partition (search_date=20171118)

New Contributor

Sorry for late response:


INSERT INTO TABLE am_temp2 Partition (search_date="2017-11-18") this works 


But the goal is to generate date which is failing . I assume that you were asking to enclose like (search_date='${hiveConf:var}') 


the partition created with above solution becomes 




I worked on a work around where we generated the date using shell action and instead of counter we got the intended date and the used the approach which @Tomas79 suggested.


Thank you!!

Expert Contributor

May be it's because of a diferente version (I'm using HDP and Hadoop 3) but this doesn't work as described here.

In first place if you try to set a variable in the "hiveConf:" namespace you will get an error like this:

Error while processing statement: Cannot modify hiveConf:test at runtime

You have to use the "hivevar:" namespace for this like:

:2> set hivevar:test=date_sub(current_date, 5);

But more importante, Hive won't expand the variable value definition as shown here:

:2> set hivevar:test;
| hivevar:test=date_sub(current_date, 5) |

So the INSERT will not interpreted as you stated but instead as:

INSERT INTO TABLE am_temp2 PARTITION (search_date=date_sub(current_date, 5))

and this for some reason is not supported in Hive and gives a compilation error:

FAILED: ParseException line 1:50 cannot recognize input near 'date_sub' '('

It would be very useful to insert data into static partitioning using pre-calculated variable values like this, from functions or select queries, but I still haven't found how to do this in HiveQL.


As a reference, this seems to be (at least partially) related with this: