Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Hive: Set variable for Static Partitioning

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

Thanks

2 REPLIES 2

Re: Hive: Set variable for Static Partitioning

Master Collaborator
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)
Highlighted

Re: Hive: Set variable for Static Partitioning

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 

/../search_date=date_sub(current_date-5)/ 

 

 

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!!