CODEWITHSUNDEEP
javahiveapache-pigbigdatahiveql
kay_pi'
kay_pi'

Reputation: 11

Hive Explode Function

myTable

name    id  number
Joe     20   72-11433.80-11427.14-6.66-R
kay     115  3-547.86-547.86-0.00-R
mary    85   12-0.49-0.49-0.00-R

select * from myTable

Joe   20    72-11433.80-11427.14-6.66-R
kay   115   3-547.86-547.86-0.00-R
mary  85    12-0.49-0.49-0.00-R

select split(number,'\-') from myTable

["72","11433.80","11427.14","6.66","R"]
["3","547.86","547.86","0.00","R"]
["12","0.49","0.49","0.00","R"]

SELECT explode(split(number,'\-')) FROM myTable

72
11433.80
11427.14
6.66
R

I want to know if there is a function that works like explode function but the desired output would be in columns NOT rows. such that: SELECT name, explode(split(number,'\-')) FROM myTable

Joe    72   11433.80    11427.14    6.66    R
kay    3    547.86      547.86      0.00    R
mary   12   0.49        0.49        0.00    R

increase the number of columns NOT rows

Upvotes: 0

Views: 556

Answers (1)

Joe K
Joe K

Reputation: 18424

I don't think such a function exists. But you can certainly just access the array elements directly, one at a time, like so:

select split(number,'\-')[0], split(number,'\-')[1], split(number,'\-')[2], split(number,'\-')[3], split(number,'\-')[4] from myTable

Upvotes: 1

Related Questions