CODEWITHSUNDEEP
sqlarrayshiveconcatenationhiveql
AKN
AKN

Reputation: 21

How to do string concatination in hive

I have 2 tables lets say table 1 as follows

enter image description here

and table 2 as follows

enter image description here

and the result i expected is

enter image description here

I tried concat_ws method in hive but the results are not as per expected and moreover when i use concat_ws method i can able to apply for only 1 field. Please let me know how to overcome or is there any solution available.

Upvotes: 0

Views: 136

Answers (1)

leftjoin
leftjoin

Reputation: 38335

Use collect_set to get an array of parts per (name, function), then use concat_ws to concatenate array using comma as delimiter:

select t.name1, t1.function, t2.parts, t2.body, t1.scope
from
(select name1, function,
        concat_ws(',',collect_set(scope)) as scope
   from table1
  group by name1, function
) t1
 inner join
(select name2, function,
        concat_ws(',',collect_set(parts)) as parts,
        concat_ws(',',collect_set(Body)) as Body
   from table2 t2 
  group by name2, function
)t2
 on t1.name1=t2.name2 and t1.function=t2.function

One more approach - without join, using UNION ALL+aggregation, this may be more performant:

select name, function,
       concat_ws(',',collect_set(parts)) as parts,
       concat_ws(',',collect_set(Body)) as Body,
       concat_ws(',',collect_set(scope)) as scope
from
(
select name1 as name, function, null as parts, null as Body, scope from table1
UNION ALL
select name2 as name, function, parts, Body, null as scope from table2
)s
group by name, function

Upvotes: 2

Related Questions