Hive query using lookup table

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

edited Nov 14 '18 at 5:45

Gaurang Shah

3,30121335

asked Nov 13 '18 at 20:00

Chopstick

what is stopping you from joining this two tables?

– Gaurang Shah
Nov 14 '18 at 5:45

add a comment |

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

edited Nov 14 '18 at 5:45

Gaurang Shah

3,30121335

asked Nov 13 '18 at 20:00

Chopstick

what is stopping you from joining this two tables?

– Gaurang Shah
Nov 14 '18 at 5:45

add a comment |

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

edited Nov 14 '18 at 5:45

Gaurang Shah

3,30121335

asked Nov 13 '18 at 20:00

Chopstick

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,30121335

asked Nov 13 '18 at 20:00

Chopstick

edited Nov 14 '18 at 5:45

Gaurang Shah

3,30121335

asked Nov 13 '18 at 20:00

Chopstick

edited Nov 14 '18 at 5:45

Gaurang Shah

3,30121335

edited Nov 14 '18 at 5:45

Gaurang Shah

3,30121335

edited Nov 14 '18 at 5:45

Gaurang Shah

3,30121335

asked Nov 13 '18 at 20:00

Chopstick

asked Nov 13 '18 at 20:00

Chopstick

asked Nov 13 '18 at 20:00

Chopstick

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

add a comment |

1 Answer
1

active

oldest

votes

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)

edited Nov 14 '18 at 17:01

answered Nov 14 '18 at 8:30

leftjoin

8,96022151

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

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

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

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)

edited Nov 14 '18 at 17:01

answered Nov 14 '18 at 8:30

leftjoin

8,96022151

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 |

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)

edited Nov 14 '18 at 17:01

answered Nov 14 '18 at 8:30

leftjoin

8,96022151

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 |

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)

edited Nov 14 '18 at 17:01

answered Nov 14 '18 at 8:30

leftjoin

8,96022151

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)

edited Nov 14 '18 at 17:01

answered Nov 14 '18 at 8:30

leftjoin

8,96022151

edited Nov 14 '18 at 17:01

answered Nov 14 '18 at 8:30

leftjoin

8,96022151

answered Nov 14 '18 at 8:30

leftjoin

8,96022151

answered Nov 14 '18 at 8:30

leftjoin

8,96022151

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

@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

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Pfthb