Hive : How to convert string to array of arrays

Question

I have a hive column value stored as string

[[1,2],[3,4,8],[5,6,7,9]]

I need to find out the length of each inner array. How should i go about it ?

Basically i need a query that sums up the sizes of each inner array. Had this column been stored as an array of arrays, i would do something like this

select sum(size(innerArray)) from myTab lateral view explode (mycol) arr as innerArray;

but now when i try the above, i get

FAILED: UDFArgumentException explode() takes an array or a map as a parameter

leftjoin · Accepted Answer · 2021-01-22 10:26:17Z

1

Because your initial array is not real array, it is string, you need to parse and explode it:

with mytable as(
select '[[1,2],[3,4,8],[5,6,7,9]]' as mycol
)

select mycol as original_string,
       innerArray_str, 
       --split inner array and get size
       size(split(innerArray_str,',')) inner_array_size
from mytable
    --explode upper array
    --replace `],` (allow spaces before comma) with `,,,` and remove all `[` and `]`, split using ,,, as a delimiter 
     lateral view outer explode(split(regexp_replace(regexp_replace(mycol,'\\] *,',',,,'),'\\[|\\]',''),',,,') )e as innerArray_str

Result:

original_string             innerarray_str  inner_array_size
[[1,2],[3,4,8],[5,6,7,9]]   1,2             2
[[1,2],[3,4,8],[5,6,7,9]]   3,4,8           3
[[1,2],[3,4,8],[5,6,7,9]]   5,6,7,9         4

Now you can add sum() and group by.

edited Jan 22, 2021 at 10:26

answered Jan 22, 2021 at 10:19

leftjoin

38.5k8 gold badges64 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Hive : How to convert string to array of arrays

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related