Hive query using lookup table
1
I have 2 tables,
- Customer table with JSON Serde whih contains just one column
- other is a look-up table to locate customer level (1,2,3,4)
Customer Table
{
"Name": "John Doe",
"Info" : {
"Address": "111 Main Street",
"ID": 2222
}
}
Look-up table
has 2 columns, customer ID and level. For Example
ID Level
1111 1
1123 4
2234 1
How do I write a Hive query to identify all customers that are level 1 from my customer table?
Thanks
hadoop hive
edited Nov 14 '18 at 5:45
Gaurang Shah
3,07611334
asked Nov 13 '18 at 20:00
ChopstickChopstick
12
add a comment |
1
I have 2 tables,
- Customer table with JSON Serde whih contains just one column
- other is a look-up table to locate customer level (1,2,3,4)
Customer Table
{
"Name": "John Doe",
"Info" : {
"Address": "111 Main Street",
"ID": 2222
}
}
Look-up table
has 2 columns, customer ID and level. For Example
ID Level
1111 1
1123 4
2234 1
How do I write a Hive query to identify all customers that are level 1 from my customer table?
Thanks
hadoop hive
edited Nov 14 '18 at 5:45
Gaurang Shah
3,07611334
asked Nov 13 '18 at 20:00
ChopstickChopstick
12
what is stopping you from joining this two tables?
– Gaurang Shah
Nov 14 '18 at 5:45
add a comment |
1
1
1
I have 2 tables,
- Customer table with JSON Serde whih contains just one column
- other is a look-up table to locate customer level (1,2,3,4)
Customer Table
{
"Name": "John Doe",
"Info" : {
"Address": "111 Main Street",
"ID": 2222
}
}
Look-up table
has 2 columns, customer ID and level. For Example
ID Level
1111 1
1123 4
2234 1
How do I write a Hive query to identify all customers that are level 1 from my customer table?
Thanks
hadoop hive
edited Nov 14 '18 at 5:45
Gaurang Shah
3,07611334
asked Nov 13 '18 at 20:00
ChopstickChopstick
12
I have 2 tables,
- Customer table with JSON Serde whih contains just one column
- other is a look-up table to locate customer level (1,2,3,4)
Customer Table
{
"Name": "John Doe",
"Info" : {
"Address": "111 Main Street",
"ID": 2222
}
}
Look-up table
has 2 columns, customer ID and level. For Example
ID Level
1111 1
1123 4
2234 1
How do I write a Hive query to identify all customers that are level 1 from my customer table?
Thanks
hadoop hive
hadoop hive
edited Nov 14 '18 at 5:45
Gaurang Shah
3,07611334
asked Nov 13 '18 at 20:00
ChopstickChopstick
12
edited Nov 14 '18 at 5:45
Gaurang Shah
3,07611334
asked Nov 13 '18 at 20:00
ChopstickChopstick
12
edited Nov 14 '18 at 5:45
Gaurang Shah
3,07611334
edited Nov 14 '18 at 5:45
Gaurang Shah
3,07611334
edited Nov 14 '18 at 5:45
Gaurang Shah
3,07611334
3,07611334
asked Nov 13 '18 at 20:00
ChopstickChopstick
12
asked Nov 13 '18 at 20:00
ChopstickChopstick
12
asked Nov 13 '18 at 20:00
ChopstickChopstick
12
12
what is stopping you from joining this two tables?
– Gaurang Shah
Nov 14 '18 at 5:45
add a comment |
what is stopping you from joining this two tables?
– Gaurang Shah
Nov 14 '18 at 5:45
what is stopping you from joining this two tables?
– Gaurang Shah
Nov 14 '18 at 5:45
what is stopping you from joining this two tables?
– Gaurang Shah
Nov 14 '18 at 5:45
add a comment |
1 Answer
1
active
oldest
votes
0
Extract ID from JSON using get_json_object() and join with lookup table, add filtering.
Demo:
select s.cust_name, s.Address, s.ID, lkp.level
from
(select
get_json_object(json_col,'$.Info.ID') as ID,
get_json_object(json_col,'$.Name') as cust_name,
get_json_object(json_col,'$.Info.Address') as Address
from
( --replace this subquery with your table
select '{"Name": "John Doe","Info" : {"Address": "111 Main Street","ID": 2222}}' as json_col)s
) s
left join
( --replace this subquery with your table
select stack(4,
1111, 1,
1123, 4,
2234, 1,
2222, 3
) as (ID, Level)
)lkp on s.ID=lkp.ID
where lkp.Level !=1 --filter out level 1
or lkp.level is null --this is to allow records without corresponding level in lkp
;
Result:
OK
cust_name address id level
John Doe 111 Main Street 2222 3
Time taken: 31.469 seconds, Fetched: 1 row(s)
answered Nov 14 '18 at 8:30
leftjoinleftjoin
8,41922051
Thank you for the solution. How do I update your solution to remove all customer with customer level =1 from Customer Table?
– Chopstick
Nov 14 '18 at 15:12
@Chopstick Added filter in the query
– leftjoin
Nov 14 '18 at 17:02
add a comment |
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%2f53288615%2fhive-query-using-lookup-table%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
Extract ID from JSON using get_json_object() and join with lookup table, add filtering.
Demo:
select s.cust_name, s.Address, s.ID, lkp.level
from
(select
get_json_object(json_col,'$.Info.ID') as ID,
get_json_object(json_col,'$.Name') as cust_name,
get_json_object(json_col,'$.Info.Address') as Address
from
( --replace this subquery with your table
select '{"Name": "John Doe","Info" : {"Address": "111 Main Street","ID": 2222}}' as json_col)s
) s
left join
( --replace this subquery with your table
select stack(4,
1111, 1,
1123, 4,
2234, 1,
2222, 3
) as (ID, Level)
)lkp on s.ID=lkp.ID
where lkp.Level !=1 --filter out level 1
or lkp.level is null --this is to allow records without corresponding level in lkp
;
Result:
OK
cust_name address id level
John Doe 111 Main Street 2222 3
Time taken: 31.469 seconds, Fetched: 1 row(s)
answered Nov 14 '18 at 8:30
leftjoinleftjoin
8,41922051
Thank you for the solution. How do I update your solution to remove all customer with customer level =1 from Customer Table?
– Chopstick
Nov 14 '18 at 15:12
@Chopstick Added filter in the query
– leftjoin
Nov 14 '18 at 17:02
add a comment |
0
Extract ID from JSON using get_json_object() and join with lookup table, add filtering.
Demo:
select s.cust_name, s.Address, s.ID, lkp.level
from
(select
get_json_object(json_col,'$.Info.ID') as ID,
get_json_object(json_col,'$.Name') as cust_name,
get_json_object(json_col,'$.Info.Address') as Address
from
( --replace this subquery with your table
select '{"Name": "John Doe","Info" : {"Address": "111 Main Street","ID": 2222}}' as json_col)s
) s
left join
( --replace this subquery with your table
select stack(4,
1111, 1,
1123, 4,
2234, 1,
2222, 3
) as (ID, Level)
)lkp on s.ID=lkp.ID
where lkp.Level !=1 --filter out level 1
or lkp.level is null --this is to allow records without corresponding level in lkp
;
Result:
OK
cust_name address id level
John Doe 111 Main Street 2222 3
Time taken: 31.469 seconds, Fetched: 1 row(s)
answered Nov 14 '18 at 8:30
leftjoinleftjoin
8,41922051
Thank you for the solution. How do I update your solution to remove all customer with customer level =1 from Customer Table?
– Chopstick
Nov 14 '18 at 15:12
@Chopstick Added filter in the query
– leftjoin
Nov 14 '18 at 17:02
add a comment |
0
0
0
Extract ID from JSON using get_json_object() and join with lookup table, add filtering.
Demo:
select s.cust_name, s.Address, s.ID, lkp.level
from
(select
get_json_object(json_col,'$.Info.ID') as ID,
get_json_object(json_col,'$.Name') as cust_name,
get_json_object(json_col,'$.Info.Address') as Address
from
( --replace this subquery with your table
select '{"Name": "John Doe","Info" : {"Address": "111 Main Street","ID": 2222}}' as json_col)s
) s
left join
( --replace this subquery with your table
select stack(4,
1111, 1,
1123, 4,
2234, 1,
2222, 3
) as (ID, Level)
)lkp on s.ID=lkp.ID
where lkp.Level !=1 --filter out level 1
or lkp.level is null --this is to allow records without corresponding level in lkp
;
Result:
OK
cust_name address id level
John Doe 111 Main Street 2222 3
Time taken: 31.469 seconds, Fetched: 1 row(s)
answered Nov 14 '18 at 8:30
leftjoinleftjoin
8,41922051
Extract ID from JSON using get_json_object() and join with lookup table, add filtering.
Demo:
select s.cust_name, s.Address, s.ID, lkp.level
from
(select
get_json_object(json_col,'$.Info.ID') as ID,
get_json_object(json_col,'$.Name') as cust_name,
get_json_object(json_col,'$.Info.Address') as Address
from
( --replace this subquery with your table
select '{"Name": "John Doe","Info" : {"Address": "111 Main Street","ID": 2222}}' as json_col)s
) s
left join
( --replace this subquery with your table
select stack(4,
1111, 1,
1123, 4,
2234, 1,
2222, 3
) as (ID, Level)
)lkp on s.ID=lkp.ID
where lkp.Level !=1 --filter out level 1
or lkp.level is null --this is to allow records without corresponding level in lkp
;
Result:
OK
cust_name address id level
John Doe 111 Main Street 2222 3
Time taken: 31.469 seconds, Fetched: 1 row(s)
answered Nov 14 '18 at 8:30
leftjoinleftjoin
8,41922051
edited Nov 14 '18 at 17:01
answered Nov 14 '18 at 8:30
leftjoinleftjoin
8,41922051
answered Nov 14 '18 at 8:30
leftjoinleftjoin
8,41922051
answered Nov 14 '18 at 8:30
leftjoinleftjoin
8,41922051
8,41922051
Thank you for the solution. How do I update your solution to remove all customer with customer level =1 from Customer Table?
– Chopstick
Nov 14 '18 at 15:12
@Chopstick Added filter in the query
– leftjoin
Nov 14 '18 at 17:02
add a comment |
Thank you for the solution. How do I update your solution to remove all customer with customer level =1 from Customer Table?
– Chopstick
Nov 14 '18 at 15:12
@Chopstick Added filter in the query
– leftjoin
Nov 14 '18 at 17:02
Thank you for the solution. How do I update your solution to remove all customer with customer level =1 from Customer Table?
– Chopstick
Nov 14 '18 at 15:12
Thank you for the solution. How do I update your solution to remove all customer with customer level =1 from Customer Table?
– Chopstick
Nov 14 '18 at 15:12
@Chopstick Added filter in the query
– leftjoin
Nov 14 '18 at 17:02
@Chopstick Added filter in the query
– leftjoin
Nov 14 '18 at 17:02
add a comment |
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%2f53288615%2fhive-query-using-lookup-table%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
what is stopping you from joining this two tables?
– Gaurang Shah
Nov 14 '18 at 5:45