Hive: Assign subquery to variable

0 votes

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

Jul 30 in Big Data Hadoop by Kishan
26 views

1 answer to this question.

0 votes

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};
answered Jul 30 by Tina

Related Questions In Big Data Hadoop

0 votes
1 answer

How Impala is fast compared to Hive in terms of query response?

Impala provides faster response as it uses MPP(massively ...READ MORE

answered Mar 21, 2018 in Big Data Hadoop by nitinrawat895
• 10,690 points
323 views
0 votes
1 answer

Cannot connect to Hive from MicroStrategy BI tool

The problem is with your hive authentication. ...READ MORE

answered May 8, 2018 in Big Data Hadoop by Shubham
• 13,300 points
194 views
0 votes
3 answers

How to connect Spark to a remote Hive server?

JDBC is not required here. Create a hive ...READ MORE

answered Mar 8 in Big Data Hadoop by Vijay Dixon
• 180 points
1,405 views
0 votes
1 answer

Hadoop Mapreduce word count Program

Firstly you need to understand the concept ...READ MORE

answered Mar 16, 2018 in Data Analytics by nitinrawat895
• 10,690 points
3,025 views
0 votes
1 answer

hadoop.mapred vs hadoop.mapreduce?

org.apache.hadoop.mapred is the Old API  org.apache.hadoop.mapreduce is the ...READ MORE

answered Mar 16, 2018 in Data Analytics by nitinrawat895
• 10,690 points
337 views
0 votes
10 answers

hadoop fs -put command?

put syntax: put <localSrc> <dest> copy syntax: copyFr ...READ MORE

answered Dec 7, 2018 in Big Data Hadoop by Aditya
14,942 views
0 votes
1 answer

Hadoop dfs -ls command?

In your case there is no difference ...READ MORE

answered Mar 16, 2018 in Big Data Hadoop by kurt_cobain
• 9,260 points
1,109 views
0 votes
1 answer

What is the command to know the details of your data created in a table in Hive?

Hey, Yes, there is a way to check ...READ MORE

answered May 14 in Big Data Hadoop by Gitika
• 25,340 points
137 views
0 votes
1 answer