CODEWITHSUNDEEP
hivehiveql
Pavan Chakravarthy
Pavan Chakravarthy

Reputation: 593

explode function in hive

I have the following sample data and I am trying to explode it in hive.. I used split but I know I am missing something..

["[[-80.742426,35.23248],[-80.740424,35.23184],[-80.739583,35.231562],[-80.735935,35.23041],[-80.728624,35.228069],[-80.727753,35.227836],[-80.727294,35.227741],[-80.726762,35.227647],[-80.726321,35.227594],[-80.725687,35.227544],[-80.725134,35.227535],[-80.721502,35.227615],[-80.691298,35.216202],[-80.688009,35.215396],[-80.686516,35.215016],[-80.598433,35.234307]]"]

I used the below query

select explode(split(col, ',')) from sample2;
and the result is this

        ["[[-80.742426
        35.23248]
        [-80.740424
        35.23184]
        [-80.739583
        35.231562]
        [-80.735935
        35.23041]
        [-80.728624
        35.228069]
        [-80.727753
        35.227836]
        [-80.71143
        35.227831]
        [-80.711007
        35.227795]
        [-80.710638
        35.227741]
        [-80.673884
        35.21014]
        [-80.672358
        35.209481]
        [-80.672036
        35.209356]
        [-80.671686
        35.209234]
        [-80.67124
        35.209099]
        [-80.670815
        35.209006]
        [-80.670267
        35.208906]
        [-80.669612
        35.208833]
        [-80.668924
        35.208806]
        [-80.598433
        35.234307]]"]

I need it in below format

    [-80.742426,35.23248]
    [-80.740424,35.23184]
    [-80.739583,35.231562]
    [-80.735935,35.23041]
    [-80.728624,35.228069]
    [-80.727753,35.227836]
    [-80.727294,35.227741]
    [-80.726762,35.227647]
    [-80.726321,35.227594]
    [-80.725687,35.227544]
    [-80.725134,35.227535]
    [-80.721502,35.227615]
    [-80.691298,35.216202]
    [-80.688009,35.215396]
    [-80.686516,35.215016]
    [-80.684281,35.214466]
    [-80.68396,35.214395]
    [-80.683375,35.214231]
    [-80.682908,35.214079]
    [-80.682444,35.213905]
    [-80.682045,35.213733]
    [-80.68062,35.213112]
    [-80.678078,35.211983]
    [-80.676836,35.211447]
    [-80.598433,35.234307]

Any help over here..?

Upvotes: 7

Views: 22104

Answers (1)

Farooque
Farooque

Reputation: 3766

You have your data set as arrays of array and you want to explode your data at first level only, so use LATERAL VIEW explode(colname) to explode at the first level.

Below is the SELECT query with explode():

SELECT col1 FROM sample2 LATERAL VIEW EXPLODE(col) explodeVal AS col1;

output generated from your input data set as below:

[-80.742426,35.23248]  
[-80.740424,35.23184]  
[-80.739583,35.231562]  
[-80.735935,35.23041]  
[-80.728624,35.228069]  
[-80.727753,35.227836]  
[-80.727294,35.227741]  
[-80.726762,35.227647]  
[-80.726321,35.227594]  
[-80.725687,35.227544]  
[-80.725134,35.227535]  
[-80.721502,35.227615]  
[-80.691298,35.216202]  
[-80.688009,35.215396]  
[-80.686516,35.215016]  
[-80.684281,35.214466]  
[-80.68396,35.214395]  
[-80.683375,35.214231]  
[-80.682908,35.214079]  
[-80.682444,35.213905]  
[-80.682045,35.213733]  
[-80.68062,35.213112]  
[-80.678078,35.211983]  
[-80.676836,35.211447]  
[-80.598433,35.234307]

Upvotes: 8

Related Questions