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, 2019 in Big Data Hadoop by Kishan
3,381 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, 2019 by Tina

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.

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
• 11,380 points
899 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,480 points
476 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, 2019 in Big Data Hadoop by Vijay Dixon
• 190 points
5,921 views
+1 vote
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
• 11,380 points
7,390 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
• 11,380 points
1,202 views
+2 votes
11 answers

hadoop fs -put command?

Hi, You can create one directory in HDFS ...READ MORE

answered Mar 16, 2018 in Big Data Hadoop by nitinrawat895
• 11,380 points
54,960 views
–1 vote
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,390 points
2,789 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, 2019 in Big Data Hadoop by Gitika
• 65,910 points
619 views
0 votes
1 answer

Can you please help with Hive Query to get FirstName, MiddleName, LastName and Suffix from FullName in Hive. Thank you

Hey, You can get first name, middle name, ...READ MORE

answered May 15, 2019 in Big Data Hadoop by Gitika
• 65,910 points

edited May 15, 2019 by Gitika 979 views