Say, you have a Table like below -
A |
[K1, K2, K3] |
[V1,V2,V3] |
B |
[K2] |
[V2] |
C |
[K1,K2,K3,K4,K5] |
[V1,V2,V3,V4,V5] |
|
K1 |
K2 |
K3 |
K4 |
K5 |
A |
V1 |
V2 |
V3 |
|
|
B |
|
V2 |
|
|
|
C |
V1 |
V2 |
V3 |
V4 |
V5 |
It Can be done with SQL -
select id, arrMap['K1'] K1, arrMap['K2'] K2, arrMap['K3'] K3, arrMap['K4'] K4, arrMap['K5'] K5, from (select id, str_to_map(concat_ws(',', (collect_list(kv)))) arrMap from (select id, n.pos, concat_ws(':', n.attribute, value[n.pos]) kv from INPUT_TABLE lateral view posexplode(attributes) n as pos, attribute )T group by id) T1;
Explanation:
1) Inner query explodes multiple columns and does a 1-to-1 mapping instead of 1-to-many mapping
select id, n.pos, concat_ws(':', n.attribute, value[n.pos]) kv from INPUT_TABLE lateral view posexplode(attributes) n as pos, attribute
A |
K1:V1 |
A |
K2:V2 |
A |
K3:V3 |
B |
K2:V2 |
C |
K1:V1 |
C |
K2:V2 |
C |
K3:V3 |
C |
K4:V4 |
C |
K5:V5 |
2) Then we convert multiple rows with same key into one row and value as a Map for all key value.
select id, str_to_map(concat_ws(',', (collect_list(kv)))) arrMap from (select id, n.pos, concat_ws(':', n.attribute, value[n.pos]) kv from INPUT_TABLE lateral view posexplode(attributes) n as pos, attribute )T group by id
A |
{K1:V1, K2:V2, K3:V3} |
B |
{K2:V2} |
C |
{K1:V1, K2:V2, K3:V3,K4:V4,K5:V5} |
3) Finally, we select Keys from table
select id, arrMap['K1'] K1, arrMap['K2'] K2, arrMap['K3'] K3, arrMap['K4'] K4, arrMap['K5'] K5, from (select id, str_to_map(concat_ws(',', (collect_list(kv)))) arrMap from (select id, n.pos, concat_ws(':', n.attribute, value[n.pos]) kv from INPUT_TABLE lateral view posexplode(attributes) n as pos, attribute )T group by id) T1;
|
K1 |
K2 |
K3 |
K4 |
K5 |
A |
V1 |
V2 |
V3 |
|
|
B |
|
V2 |
|
|
|
C |
V1 |
V2 |
V3 |
V4 |
V5 |
Comments
Post a Comment