Transpose array in Hive

Question

I have the table

create table regs(
id string,
regs string)

Example of data

id   regs
1    23:7:97.27%, 77:1:0.56%, 09:1:0.48%
2    01:3:1.26%, 15:1:0.09%
3    26:1:0.17%

How i can get this results?

id   regs
1 23:7:97.27%
1 77:1:0.56%
1 09:1:0.48%
2 01:3:1.26% 
2 15:1:0.09%
3 26:1:0.17%

user_2700344 · Answer

Split regs using this pattern ', *' - means comma + any number of spaces, and explode:

select r.id, e.reg
from regs r
     lateral view explode(split(r.regs, ', *')) e as reg