CODEWITHSUNDEEP
hadoophivehiveql
amd
amd

Reputation: 31

How do I store the result of a query into a variable in HiveQL and then use it in another select statement?

How do I store the result of a query into a variable in HiveQL and then use it in another select statement?

For example, whenever I store a normal variable and use it in a select statement it works just fine. SET a=1; SELECT CASE WHEN b > ${hiveconf:a} THEN NULL ELSE 1 from my_table

But when I try and put a query into the variable, its seems to store the query instead of running it and storing the result. This then results in an error. SET a=SELECT MAX(num) FROM my_other_table; SELECT CASE WHEN b > ${hiveconf:a} THEN NULL ELSE 1 from my_table

The error being: cannot recognize input near 'select' 'max' '(' in select clause

Does anyone know a work around to this? I am using Hive 0.13

Upvotes: 1

Views: 2222

Answers (1)

amow
amow

Reputation: 2223

You cant do that only by hive.

If your hive query is controlled by outer script like shell or python.You can perform the first query, get the output and then put it in the next sql.

Or you can change your sql to use join.Your example code can be changed to 

select case when b > t.a then NULL else 1 from my_table 
join (select max(num) a from my_other_table) t

Upvotes: 2

Related Questions