Convert String to array in Hive

Question

I have a integer array represented with String. For example, "[1,2,2,3]"

And the field type in Hive table is the array integer, I was wondering if there is any Hive build-in UDF that can cast the above string into the array integer.

Thanks

Answer 1

tl;dr I do not know of a Hive UDF that will do this for you, and casting on your own can be gross.

---

No, there isn't a UDF. As for building your own solution:

Casting to array[string] would be easy enough - just drop the square brackets using regexp_replace and split the resulting string on ,.

The problem is converting an array[string] to array[int] for arrays of arbitrary size. You can individually cast the array elements one by one:

hive> select id, my_array from array_table limit 3;
OK
10023307    ["0.20296966","0.17753501","-0.03543373"]
100308007   ["0.16155224","0.1945944","0.09167781"]
100384207   ["0.025892768","0.023214806","-0.003712816"]

hive> select array(cast(my_array[0] as double), cast(my_array[1] as double), cast(my_array[2] as double)) from array_table limit 3;
OK
[0.20296966,0.17753501,-0.03543373]
[0.16155224,0.1945944,0.09167781]
[0.025892768,0.023214806,-0.003712816]

but this approach only works because I know I have arrays of length 3.