CODEWITHSUNDEEP
hivehiveql
Bala
Bala

Reputation: 685

Using variables in HiveQL

In my use case, I need to something like this below

set x=<query1> // This query 1 returns a value 75
set y=x
print y // only for testing

select * from table1 where id=y

set var1=<query2>
set var2=<query3>
set var3=<query4>
set count = var1+var2+var3

Is it possible to do like this in Hive? I am struggling to even run a simple query and put the results in a variable. Any clue will be really helpful.

To start with, I am trying to do something like below

set param = SELECT P.K_ID FROM T_PARAMETER AS P, T_TOOL_PARAMETER AS M WHERE P.K_PARAMETER_GENERIC = 6 AND P.K_ID = M.K_PARAMETER AND M.k_airborn = 17;


INSERT INTO T_TEMP SELECT T.F_RECORDEDVALUE, T.F_VALIDITY FROM T_RECORD AS T WHERE T.K_PARAMETER=${hiveconf:param};

The above query fails. I am not sure whether the syntax is correct or not. The error I get is 

cannot recognize input near 'SELECT' 'P' '.' in expression specification

If I put the double quotes in where clause WHERE T.K_PARAMETER="${hiveconf:parameter}";, then, No error comes but the T_TEMP is empty.

If I hardcode the value set parameter = 36 and use in where clause WHERE T.K_PARAMETER=${hiveconf:parameter};, then, it works cleanly. The issue comes when only i try to put the results of a query into a variable

I prefer to use Hue as I need to write many queries like this.

Upvotes: 0

Views: 376

Answers (1)

Kishore
Kishore

Reputation: 5881

on terminal

var1=$(hive -e "select count(*) from table1;")
var2=$(hive -e "select count(*) from table2;")
var1=$(hive -e "select count(*) from table3;")
count=$(( $var1 + $var1 + $var3))
echo $count

on hive console

INSERT INTO T_TEMP SELECT T.F_RECORDEDVALUE, T.F_VALIDITY FROM T_RECORD AS T WHERE T.K_PARAMETER In (SELECT P.K_ID FROM T_PARAMETER AS P, T_TOOL_PARAMETER AS M WHERE P.K_PARAMETER_GENERIC = 6 AND P.K_ID = M.K_PARAMETER AND M.k_airborn = 17);

Upvotes: 1

Related Questions