Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Split column in hive

Highlighted

Split column in hive

New Contributor

I am new to Hive and Hadoop framework. I am trying to write a hive query to split the column delimited by a pipe '|' character. Then I want to group up the 2 adjacent values and separate them into separate rows.


Example, I have a table

Id

mapper

1

a|0.1|b|0.2

2

c|0.2|d|0.3|e|0.6

3

f|0.6


I am able to split the column by using split(mapper, "\\|") which gives me the array

id

mapper

1

[a,0.1,b,0.2]

2

[c,0.2,d,0.3,e,0.6]

3

[f,0.6]


Now I tried to to use the lateral view to split the mapper array into separate rows, but it will separate all the values, where as I want to separate by group.


Expected:

---------

id

mapper

1

[a,0.1]

1

[b,0.2]

2

[c,0.2]

2

[d,0.3]

2

[e,0.6]

3

[f,0.6]


Actual

------

Id

mapper

1

a

1

0.1

1

b

1

0.2

etc .......


How can I achieve this?