How to use Hive Query results(multiple) in a variable for other query
1
I have two tables one is schools and one is students.I want to find all the students of a particular school.
The schema of schools is: id, name, location
and of students is :id, name, schoolId.
I wrote the following script:
schoolId=$(hive -e "set hive.cli.print.header=false;select id from school;")
hive -hiveconf "schoolId"="$schoolId"
hive>select id,name from student where schoolId like '${hiveconf:schoolId}%'
I dont get any result as schoolId stores all the id together.For example there are 3 schools with id: 123, 256,346
schoolId variable stores as 123 256 346 and the result is null.
hive hiveql
asked Nov 13 '18 at 10:32
Vijaya SeetharamanVijaya Seetharaman
6419
add a comment |
1
I have two tables one is schools and one is students.I want to find all the students of a particular school.
The schema of schools is: id, name, location
and of students is :id, name, schoolId.
I wrote the following script:
schoolId=$(hive -e "set hive.cli.print.header=false;select id from school;")
hive -hiveconf "schoolId"="$schoolId"
hive>select id,name from student where schoolId like '${hiveconf:schoolId}%'
I dont get any result as schoolId stores all the id together.For example there are 3 schools with id: 123, 256,346
schoolId variable stores as 123 256 346 and the result is null.
hive hiveql
asked Nov 13 '18 at 10:32
Vijaya SeetharamanVijaya Seetharaman
6419
add a comment |
1
1
1
I have two tables one is schools and one is students.I want to find all the students of a particular school.
The schema of schools is: id, name, location
and of students is :id, name, schoolId.
I wrote the following script:
schoolId=$(hive -e "set hive.cli.print.header=false;select id from school;")
hive -hiveconf "schoolId"="$schoolId"
hive>select id,name from student where schoolId like '${hiveconf:schoolId}%'
I dont get any result as schoolId stores all the id together.For example there are 3 schools with id: 123, 256,346
schoolId variable stores as 123 256 346 and the result is null.
hive hiveql
asked Nov 13 '18 at 10:32
Vijaya SeetharamanVijaya Seetharaman
6419
I have two tables one is schools and one is students.I want to find all the students of a particular school.
The schema of schools is: id, name, location
and of students is :id, name, schoolId.
I wrote the following script:
schoolId=$(hive -e "set hive.cli.print.header=false;select id from school;")
hive -hiveconf "schoolId"="$schoolId"
hive>select id,name from student where schoolId like '${hiveconf:schoolId}%'
I dont get any result as schoolId stores all the id together.For example there are 3 schools with id: 123, 256,346
schoolId variable stores as 123 256 346 and the result is null.
hive hiveql
hive hiveql
asked Nov 13 '18 at 10:32
Vijaya SeetharamanVijaya Seetharaman
6419
asked Nov 13 '18 at 10:32
Vijaya SeetharamanVijaya Seetharaman
6419
edited Nov 13 '18 at 12:05
Vijaya Seetharaman
asked Nov 13 '18 at 10:32
Vijaya SeetharamanVijaya Seetharaman
6419
asked Nov 13 '18 at 10:32
Vijaya SeetharamanVijaya Seetharaman
6419
asked Nov 13 '18 at 10:32
Vijaya SeetharamanVijaya Seetharaman
6419
6419
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
0
Use collect_set() with concat_ws to get comma delimited string, IDs should be cast to string:
schoolId=$(hive -e "set hive.cli.print.header=false;select concat_ws('\',\'',collect_set(cast(id as string))) from school;");
hive -hiveconf "schoolId"="$schoolId"
Then use IN operator:
select id,name from student where schoolId in ('${hiveconf:schoolId}');
answered Nov 13 '18 at 11:17
leftjoinleftjoin
8,42922151
I got error like : cannot recognize input near ''33992,3d1e7,68a3e,1ed4b,8aaa7,c2eb2'' '<EOF>' '<EOF>' in expression specification
– Vijaya Seetharaman
Nov 13 '18 at 12:22
@VijayaSeetharaman Fixed. IDs are strings, need to single-quote them in concat_ws and also quote variable in the Hive query. Also no need to cast to string
– leftjoin
Nov 13 '18 at 12:40
Thank you. The above procedure worked. But when I do the select queryselect concat_ws('\',\'',collect_set(cast(id as string))) from school;individually in hive i get an error sayingFAILED: ParseException line 1:22 character '' not supported here. How do I get it right?
– Vijaya Seetharaman
Nov 14 '18 at 8:26
@VijayaSeetharaman In what tool are you running it? It is something with shielding,- maybe you need one more slash, or single slash to shield comma
– leftjoin
Nov 14 '18 at 8:34
set hiveconf:schoolId=select concat_ws('\',\'',collect_set(cast(id as string))) from school; select id,name from patient where schoolId in ('${hiveconf:schoolId}');I tried this command within hive but again its giving me the same parse exception.
– Vijaya Seetharaman
Nov 14 '18 at 8:36
|
show 7 more comments
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53279011%2fhow-to-use-hive-query-resultsmultiple-in-a-variable-for-other-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Use collect_set() with concat_ws to get comma delimited string, IDs should be cast to string:
schoolId=$(hive -e "set hive.cli.print.header=false;select concat_ws('\',\'',collect_set(cast(id as string))) from school;");
hive -hiveconf "schoolId"="$schoolId"
Then use IN operator:
select id,name from student where schoolId in ('${hiveconf:schoolId}');
answered Nov 13 '18 at 11:17
leftjoinleftjoin
8,42922151
I got error like : cannot recognize input near ''33992,3d1e7,68a3e,1ed4b,8aaa7,c2eb2'' '<EOF>' '<EOF>' in expression specification
– Vijaya Seetharaman
Nov 13 '18 at 12:22
@VijayaSeetharaman Fixed. IDs are strings, need to single-quote them in concat_ws and also quote variable in the Hive query. Also no need to cast to string
– leftjoin
Nov 13 '18 at 12:40
Thank you. The above procedure worked. But when I do the select queryselect concat_ws('\',\'',collect_set(cast(id as string))) from school;individually in hive i get an error sayingFAILED: ParseException line 1:22 character '' not supported here. How do I get it right?
– Vijaya Seetharaman
Nov 14 '18 at 8:26
@VijayaSeetharaman In what tool are you running it? It is something with shielding,- maybe you need one more slash, or single slash to shield comma
– leftjoin
Nov 14 '18 at 8:34
set hiveconf:schoolId=select concat_ws('\',\'',collect_set(cast(id as string))) from school; select id,name from patient where schoolId in ('${hiveconf:schoolId}');I tried this command within hive but again its giving me the same parse exception.
– Vijaya Seetharaman
Nov 14 '18 at 8:36
|
show 7 more comments
0
Use collect_set() with concat_ws to get comma delimited string, IDs should be cast to string:
schoolId=$(hive -e "set hive.cli.print.header=false;select concat_ws('\',\'',collect_set(cast(id as string))) from school;");
hive -hiveconf "schoolId"="$schoolId"
Then use IN operator:
select id,name from student where schoolId in ('${hiveconf:schoolId}');
answered Nov 13 '18 at 11:17
leftjoinleftjoin
8,42922151
I got error like : cannot recognize input near ''33992,3d1e7,68a3e,1ed4b,8aaa7,c2eb2'' '<EOF>' '<EOF>' in expression specification
– Vijaya Seetharaman
Nov 13 '18 at 12:22
@VijayaSeetharaman Fixed. IDs are strings, need to single-quote them in concat_ws and also quote variable in the Hive query. Also no need to cast to string
– leftjoin
Nov 13 '18 at 12:40
Thank you. The above procedure worked. But when I do the select queryselect concat_ws('\',\'',collect_set(cast(id as string))) from school;individually in hive i get an error sayingFAILED: ParseException line 1:22 character '' not supported here. How do I get it right?
– Vijaya Seetharaman
Nov 14 '18 at 8:26
@VijayaSeetharaman In what tool are you running it? It is something with shielding,- maybe you need one more slash, or single slash to shield comma
– leftjoin
Nov 14 '18 at 8:34
set hiveconf:schoolId=select concat_ws('\',\'',collect_set(cast(id as string))) from school; select id,name from patient where schoolId in ('${hiveconf:schoolId}');I tried this command within hive but again its giving me the same parse exception.
– Vijaya Seetharaman
Nov 14 '18 at 8:36
|
show 7 more comments
0
0
0
Use collect_set() with concat_ws to get comma delimited string, IDs should be cast to string:
schoolId=$(hive -e "set hive.cli.print.header=false;select concat_ws('\',\'',collect_set(cast(id as string))) from school;");
hive -hiveconf "schoolId"="$schoolId"
Then use IN operator:
select id,name from student where schoolId in ('${hiveconf:schoolId}');
answered Nov 13 '18 at 11:17
leftjoinleftjoin
8,42922151
Use collect_set() with concat_ws to get comma delimited string, IDs should be cast to string:
schoolId=$(hive -e "set hive.cli.print.header=false;select concat_ws('\',\'',collect_set(cast(id as string))) from school;");
hive -hiveconf "schoolId"="$schoolId"
Then use IN operator:
select id,name from student where schoolId in ('${hiveconf:schoolId}');
answered Nov 13 '18 at 11:17
leftjoinleftjoin
8,42922151
edited Nov 13 '18 at 12:39
answered Nov 13 '18 at 11:17
leftjoinleftjoin
8,42922151
answered Nov 13 '18 at 11:17
leftjoinleftjoin
8,42922151
answered Nov 13 '18 at 11:17
leftjoinleftjoin
8,42922151
8,42922151
I got error like : cannot recognize input near ''33992,3d1e7,68a3e,1ed4b,8aaa7,c2eb2'' '<EOF>' '<EOF>' in expression specification
– Vijaya Seetharaman
Nov 13 '18 at 12:22
@VijayaSeetharaman Fixed. IDs are strings, need to single-quote them in concat_ws and also quote variable in the Hive query. Also no need to cast to string
– leftjoin
Nov 13 '18 at 12:40
Thank you. The above procedure worked. But when I do the select queryselect concat_ws('\',\'',collect_set(cast(id as string))) from school;individually in hive i get an error sayingFAILED: ParseException line 1:22 character '' not supported here. How do I get it right?
– Vijaya Seetharaman
Nov 14 '18 at 8:26
@VijayaSeetharaman In what tool are you running it? It is something with shielding,- maybe you need one more slash, or single slash to shield comma
– leftjoin
Nov 14 '18 at 8:34
set hiveconf:schoolId=select concat_ws('\',\'',collect_set(cast(id as string))) from school; select id,name from patient where schoolId in ('${hiveconf:schoolId}');I tried this command within hive but again its giving me the same parse exception.
– Vijaya Seetharaman
Nov 14 '18 at 8:36
|
show 7 more comments
I got error like : cannot recognize input near ''33992,3d1e7,68a3e,1ed4b,8aaa7,c2eb2'' '<EOF>' '<EOF>' in expression specification
– Vijaya Seetharaman
Nov 13 '18 at 12:22
@VijayaSeetharaman Fixed. IDs are strings, need to single-quote them in concat_ws and also quote variable in the Hive query. Also no need to cast to string
– leftjoin
Nov 13 '18 at 12:40
Thank you. The above procedure worked. But when I do the select queryselect concat_ws('\',\'',collect_set(cast(id as string))) from school;individually in hive i get an error sayingFAILED: ParseException line 1:22 character '' not supported here. How do I get it right?
– Vijaya Seetharaman
Nov 14 '18 at 8:26
@VijayaSeetharaman In what tool are you running it? It is something with shielding,- maybe you need one more slash, or single slash to shield comma
– leftjoin
Nov 14 '18 at 8:34
set hiveconf:schoolId=select concat_ws('\',\'',collect_set(cast(id as string))) from school; select id,name from patient where schoolId in ('${hiveconf:schoolId}');I tried this command within hive but again its giving me the same parse exception.
– Vijaya Seetharaman
Nov 14 '18 at 8:36
I got error like : cannot recognize input near ''33992,3d1e7,68a3e,1ed4b,8aaa7,c2eb2'' '<EOF>' '<EOF>' in expression specification
– Vijaya Seetharaman
Nov 13 '18 at 12:22
I got error like : cannot recognize input near ''33992,3d1e7,68a3e,1ed4b,8aaa7,c2eb2'' '<EOF>' '<EOF>' in expression specification
– Vijaya Seetharaman
Nov 13 '18 at 12:22
@VijayaSeetharaman Fixed. IDs are strings, need to single-quote them in concat_ws and also quote variable in the Hive query. Also no need to cast to string
– leftjoin
Nov 13 '18 at 12:40
@VijayaSeetharaman Fixed. IDs are strings, need to single-quote them in concat_ws and also quote variable in the Hive query. Also no need to cast to string
– leftjoin
Nov 13 '18 at 12:40
Thank you. The above procedure worked. But when I do the select query
select concat_ws('\',\'',collect_set(cast(id as string))) from school; individually in hive i get an error saying FAILED: ParseException line 1:22 character '' not supported here. How do I get it right?– Vijaya Seetharaman
Nov 14 '18 at 8:26
Thank you. The above procedure worked. But when I do the select query
select concat_ws('\',\'',collect_set(cast(id as string))) from school; individually in hive i get an error saying FAILED: ParseException line 1:22 character '' not supported here. How do I get it right?– Vijaya Seetharaman
Nov 14 '18 at 8:26
@VijayaSeetharaman In what tool are you running it? It is something with shielding
, - maybe you need one more slash, or single slash to shield comma– leftjoin
Nov 14 '18 at 8:34
@VijayaSeetharaman In what tool are you running it? It is something with shielding
, - maybe you need one more slash, or single slash to shield comma– leftjoin
Nov 14 '18 at 8:34
set hiveconf:schoolId=select concat_ws('\',\'',collect_set(cast(id as string))) from school; select id,name from patient where schoolId in ('${hiveconf:schoolId}'); I tried this command within hive but again its giving me the same parse exception.– Vijaya Seetharaman
Nov 14 '18 at 8:36
set hiveconf:schoolId=select concat_ws('\',\'',collect_set(cast(id as string))) from school; select id,name from patient where schoolId in ('${hiveconf:schoolId}'); I tried this command within hive but again its giving me the same parse exception.– Vijaya Seetharaman
Nov 14 '18 at 8:36
|
show 7 more comments
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53279011%2fhow-to-use-hive-query-resultsmultiple-in-a-variable-for-other-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
