Hive Assign subquery to variable

Question

Hi,

I have a query where I have a settings table(tbl_Settings) with active one record all the time. There is only 1 column "NoofRecords" containing value the noofrecords to be displayed in the application window.

I have a use case where I need to display the 500 employees of a company in the window which is customized in the Settings table in column NoofRecords Todisplay. 

set hivevar:NoofRecords=500;

select * from tbl_Employees order by empId limit $NoofRecords;

In the above variable, I set the variable value as 500  statically. 

Now my case is I want to have a variable as 

set hivevar:NoofRecords=(select NoofRecords from tbl_settings); 

But the above once is throwing an error. Could you correct my code

Answer 1

Refer to this example:

Step1: Check table test1 description:

hive> desc Table test1;
OK
columnA date
Time taken: 0.452 seconds, Fetched: 1 row(s)

Step2: Table test2 description:

hive> desc Table test2;
OK
columnB date
Time taken: 0.516 seconds, Fetched: 1 row(s)

Step3: Assigning subquery to variable:

hive> set hivevar:var1=(select columnB from Table test2 where columnB=’2016-09-19′);

Step4: Running main query with subquery:

hive> select * from Table test1 where columnA IN ${hivevar:var1};

Comments

This solution wont work .

set hivevar:var1=(select columnB from Table test2 where columnB=’2016-09-19′);Will set the query itself not the value 

---

Hi@rasmi,

You are right. But this is the actual requirement as someone asked above. We are not executing the command. We are assigning the command to a variable. So that we can use the variable in our main command as a subcommand.