CODEWITHSUNDEEP
hadoophive
JaemyeongEo
JaemyeongEo

Reputation: 403

Hive Parameter in hive query

I have two code.

One is named testing.hql

select dt, '${hiveconf:var}' from temp_table;

other is named testing.sh

temp= date --date='yesterday' +%y%m%d
hive -f testing.hql -hiveconf var=$temp

so basically im trying to pass date value to the query so i can filter the data i process in the query based on the current date.

im running this by this command. easy.

./testing.sh

This doesn't work,, can someone quickly check and see which part am i making a mistake?

So basically what I want to do is 

select jobid from temp_table where dt >= '${hiveconf:var}';

so that the jobid i get are only the ones that are done from yesterday, since the shell script sets paramter as the date for yesterday.

Thanks!

Currently this outputs the empty space after the dt value.

Upvotes: 3

Views: 9173

Answers (4)

Andrey Dmitriev
Andrey Dmitriev

Reputation: 568

You can use BeeTamer for that. It allows to store result (or part of it) in a variable, and use this variable later in your code.

Beetamer is macro extension to Hive or Impala that allows to extend functionality of the Apache Hive and Cloudera Impala engines.

select avg(a) from abc;
%capture MY_AVERAGE;
select * from abc2 where avg_var=#MY_AVERAGE#;

In here you save average value from you query into macro variable MY_AVERAGE and then reusing it in the second query.

Upvotes: 0

Suchith U Shetty
Suchith U Shetty

Reputation: 1

temp variable should be declared as below,

temp=$(date --date='yesterday' +%y%m%d)

Upvotes: 0

user 923227
user 923227

Reputation: 2715

There is a space after temp= removing that possibly should solve the issue

temp=<blank>date ...

Upvotes: 0

JaemyeongEo
JaemyeongEo

Reputation: 403

Figured it out.

2 simple bugs.

1) When doing shell scripts, executing lines have to be wrapped with `. so i did 

    temp=`date --date='yesterday' +%y%m%d`
    hive -f testing.hql -hiveconf var=$temp

and it works like a charm

2) in the query, the parameter must be in double quotes. 

    select jobid from temp_table where dt >= "${hiveconf:var}";

Hope this question can help others who had this issue.

Upvotes: 4

Related Questions