declare variable in sql (hive)

Question

I had a deep look on the internet, but I'm not able to find any suitable answer.

In hive, is it possible to declare a variable, lets say:

test = 1

And change the value of this variable inside a query?

select
   case
      when field > 1 then test = test+1
      else test = 1
   end as test
from my table

http://stackoverflow.com/questions/12464636/how-to-set-variables-in-hive-scripts — mohan111, May 19 '16 at 10:14
Two reasons why **no, it can't be done** -- (1) Hive **compiles** the query to create the appropriate execution plan, and (2) these parameters are managed on the client side (fat CLI or thin Beeline clients) and not on the server side. Think of a pre-processor doing text substitution before the final text is compiled. — Samson Scharfrichter, May 19 '16 at 15:52

score 15 · Accepted Answer · answered May 20 '16 at 09:23

15

It is possible. Please find the below code to create a variable in Hive.

hive> SET cust_id = 1234567890;

Once you create variable you can use it in your query like below.

hive> select * from cust_table where customer_id = '${hiveconf:cust_id}';

Hope this will help you. Now you can apply this to your scenario.

answered May 20 '16 at 09:23

Manindar

2

Thanks for the answer! This also works: set ids = 1,2,3; select * from users where id in (${hiveconf:ids}); – Alex Apr 13 '18 at 19:05
Also, it can be used as a column name instead of a value in a column, like this: `SET col_name = order_id; SELECT customer_id, ${hiveconf:col_name} from database.table_name;` This would return customer_id, order_id columns from the table assuming table has order_id as a column. Notice that while assigning the variable, the assignment value is not quoted. – Adiga Sep 22 '20 at 16:49

1 Answers1