Styjun

What is this symbol: semicircles facing eachother

How would one country purchase another?

Which household object drew this pattern?

How to compare two different formulations of a problem?

LeetCode: Pascal's Triangle C#

Avoiding racist tropes in fantasy

What professions would a medieval village with a population of 100 need?

Why don't electrons take the shorter path in coils

How is "sein" conjugated in this sub-sentence?

In what ways can a Non-paladin access Paladin spells?

Is there a known non-euclidean geometry where two concentric circles of different radii can intersect? (as in the novel "The Universe Between")

Fried gnocchi with spinach, bacon, cream sauce in a single pan

Why didn’t Doctor Strange stay in the original winning timeline?

What is the hex versus octal timeline?

Why does The Ancient One think differently about Doctor Strange in Endgame than the film Doctor Strange?

Script that helps people make better choices

Can I switch to third-person while not in 'town' in Destiny 2?

If the first law of thermodynamics ensures conservation of energy, why does it allow systems to lose energy?

Science fiction short story where aliens contact a drunk about Earth's impending destruction

On the feasibility of space battleships

If all stars rotate, why was there a theory developed, that requires non-rotating stars?

Is a butterfly one or two animals?

Do ability scores have any effect on casting the Wish spell?

Does an object count as "being moved" when placed in a Bag of Holding before its wielder moves, and then after moving they take the object out again?

I need to create a new column or alter the original from array to map where int values are in new table for respective string

SQL Server: How to Join to first rowWhat are the options for storing hierarchical data in a relational database?Hadoop/Hive query to split one column into several onesUpdate table new column with values from another table (MySQL)Need to add a constant value in a column while loading a hive tableWant to join table1 with table2 but want output of rows in the same sequence as in table2, but I am getting output as in table1SQL SELECT only rows having MAX value of a column from two different tablesDelete a record in Hive based on a table records in another databaseHow to map each distinct value of a column in one table with each distinct value of a column in another table in HiveHow to calculate the percentage of records when comparing hive tables?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

I have two tables.
1.table1:(string,array(string))

abc ["s","m"]
def ["m","a","l"]
xyz ["s","a"]

2.table2:(string,int)

m 12
s 26
l 57
a 45

Now i want a table like below:(string,map(string,int))

abc ["s":26,"m":12]
def ["m":12,"a":45,"l":57]
xyz ["s":26,"a":45]

1. I need HIVE query for doing this.
2. How to interate for a purticular row sum like

abc 38

sql hadoop hive hiveql

share|improve this question

edited Mar 27 at 6:36

Tim Biegeleisen

267k1313 gold badges116116 silver badges177177 bronze badges

asked Mar 27 at 6:35

maneeshmaneesh

11211 silver badge1313 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  I removed the MySQL tag, since it has nothing to do with your question.
  
  – Tim Biegeleisen
  Mar 27 at 6:36
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  representation is not map(string,int)) it's array<string: string>
  
  – Gaurang Shah
  Mar 27 at 16:00
  

  
  
  
  

add a comment | 

0

I have two tables.
1.table1:(string,array(string))

abc ["s","m"]
def ["m","a","l"]
xyz ["s","a"]

2.table2:(string,int)

m 12
s 26
l 57
a 45

Now i want a table like below:(string,map(string,int))

abc ["s":26,"m":12]
def ["m":12,"a":45,"l":57]
xyz ["s":26,"a":45]

1. I need HIVE query for doing this.
2. How to interate for a purticular row sum like

abc 38

sql hadoop hive hiveql

share|improve this question

edited Mar 27 at 6:36

Tim Biegeleisen

267k1313 gold badges116116 silver badges177177 bronze badges

asked Mar 27 at 6:35

maneeshmaneesh

11211 silver badge1313 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  I removed the MySQL tag, since it has nothing to do with your question.
  
  – Tim Biegeleisen
  Mar 27 at 6:36
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  representation is not map(string,int)) it's array<string: string>
  
  – Gaurang Shah
  Mar 27 at 16:00
  

  
  
  
  

add a comment | 

I have two tables.
1.table1:(string,array(string))

abc ["s","m"]
def ["m","a","l"]
xyz ["s","a"]

2.table2:(string,int)

m 12
s 26
l 57
a 45

Now i want a table like below:(string,map(string,int))

abc ["s":26,"m":12]
def ["m":12,"a":45,"l":57]
xyz ["s":26,"a":45]

1. I need HIVE query for doing this.
2. How to interate for a purticular row sum like

abc 38

sql hadoop hive hiveql

share|improve this question

edited Mar 27 at 6:36

Tim Biegeleisen

267k1313 gold badges116116 silver badges177177 bronze badges

asked Mar 27 at 6:35

maneeshmaneesh

11211 silver badge1313 bronze badges

abc ["s","m"]
def ["m","a","l"]
xyz ["s","a"]

m 12
s 26
l 57
a 45

abc ["s":26,"m":12]
def ["m":12,"a":45,"l":57]
xyz ["s":26,"a":45]

abc 38

share|improve this question

edited Mar 27 at 6:36

Tim Biegeleisen

267k1313 gold badges116116 silver badges177177 bronze badges

asked Mar 27 at 6:35

maneeshmaneesh

11211 silver badge1313 bronze badges

share|improve this question

edited Mar 27 at 6:36

Tim Biegeleisen

267k1313 gold badges116116 silver badges177177 bronze badges

asked Mar 27 at 6:35

maneeshmaneesh

11211 silver badge1313 bronze badges

edited Mar 27 at 6:36

Tim Biegeleisen

267k1313 gold badges116116 silver badges177177 bronze badges

edited Mar 27 at 6:36

Tim Biegeleisen

267k1313 gold badges116116 silver badges177177 bronze badges

asked Mar 27 at 6:35

maneeshmaneesh

11211 silver badge1313 bronze badges

asked Mar 27 at 6:35

maneeshmaneesh

11211 silver badge1313 bronze badges

1 Answer
1

active

oldest

votes

0

This will do the trick

select t1.cat, collect_list(concat_ws(":", t1.subs, cast(t2.cnt as string))) from 
(select cat, subs from temp.table1
lateral view explode(sub) s as subs) t1
join 
 temp.table2 t2
on
 t1.subs = t2.sub
group by t1.cat

And if you want to do a sum based on the above table.

select cat, sum(split(sub, ":")[1]) from temp.test3
lateral view explode(`_c1`) c as sub
group by cat

share|improve this answer

edited Mar 27 at 16:26

answered Mar 27 at 16:15

Gaurang ShahGaurang Shah

4,28422 gold badges2222 silver badges4545 bronze badges

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

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55371174%2fi-need-to-create-a-new-column-or-alter-the-original-from-arraystring-to-mapst%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

This will do the trick

select t1.cat, collect_list(concat_ws(":", t1.subs, cast(t2.cnt as string))) from 
(select cat, subs from temp.table1
lateral view explode(sub) s as subs) t1
join 
 temp.table2 t2
on
 t1.subs = t2.sub
group by t1.cat

And if you want to do a sum based on the above table.

select cat, sum(split(sub, ":")[1]) from temp.test3
lateral view explode(`_c1`) c as sub
group by cat

share|improve this answer

edited Mar 27 at 16:26

answered Mar 27 at 16:15

Gaurang ShahGaurang Shah

4,28422 gold badges2222 silver badges4545 bronze badges

add a comment | 

0

This will do the trick

select t1.cat, collect_list(concat_ws(":", t1.subs, cast(t2.cnt as string))) from 
(select cat, subs from temp.table1
lateral view explode(sub) s as subs) t1
join 
 temp.table2 t2
on
 t1.subs = t2.sub
group by t1.cat

And if you want to do a sum based on the above table.

select cat, sum(split(sub, ":")[1]) from temp.test3
lateral view explode(`_c1`) c as sub
group by cat

share|improve this answer

edited Mar 27 at 16:26

answered Mar 27 at 16:15

Gaurang ShahGaurang Shah

4,28422 gold badges2222 silver badges4545 bronze badges

add a comment | 

This will do the trick

select t1.cat, collect_list(concat_ws(":", t1.subs, cast(t2.cnt as string))) from 
(select cat, subs from temp.table1
lateral view explode(sub) s as subs) t1
join 
 temp.table2 t2
on
 t1.subs = t2.sub
group by t1.cat

And if you want to do a sum based on the above table.

select cat, sum(split(sub, ":")[1]) from temp.test3
lateral view explode(`_c1`) c as sub
group by cat

share|improve this answer

edited Mar 27 at 16:26

answered Mar 27 at 16:15

Gaurang ShahGaurang Shah

4,28422 gold badges2222 silver badges4545 bronze badges

select t1.cat, collect_list(concat_ws(":", t1.subs, cast(t2.cnt as string))) from 
(select cat, subs from temp.table1
lateral view explode(sub) s as subs) t1
join 
 temp.table2 t2
on
 t1.subs = t2.sub
group by t1.cat

select cat, sum(split(sub, ":")[1]) from temp.test3
lateral view explode(`_c1`) c as sub
group by cat

share|improve this answer

edited Mar 27 at 16:26

answered Mar 27 at 16:15

Gaurang ShahGaurang Shah

4,28422 gold badges2222 silver badges4545 bronze badges

answered Mar 27 at 16:15

Gaurang ShahGaurang Shah

4,28422 gold badges2222 silver badges4545 bronze badges

answered Mar 27 at 16:15

Gaurang ShahGaurang Shah

4,28422 gold badges2222 silver badges4545 bronze badges