hive sql transform big int array to string array

Question

I am trying to convert an array of big ints into string array in hive SQL

I've tried using

concat_ws

but that gave me an error

Argument 2 of function CONCAT_WS must be "string or array<string>", but "array<bigint>" was found.

I tried using

transform(mycolumn) using '/bin/cat' as mycolumn_as_string

It gives an error of

 cannot recognize input near 'transform' '(' 'mycolumn' in selection target

How can I convert this bigint array into an array string?

I tried using it before but you cant use concat_ws with a bigint it gives an error — jumpman8947, Feb 26 '18 at 14:44
please show us 1) table schema and 2) the hive query that is getting an error. — jose_bacoy, Feb 26 '18 at 16:13

score 0 · Answer 1 · answered Feb 26 '18 at 17:09

Assuming that your table looks like this:

tb_name mycolumn
------- --------
tblname [111,222,333]
tblname [1234,123456,3423]

Then below query will not work because concat_ws only accepts string or array of string:

select tb_name, concat_ws('-', mycolumn) from mytable;
FAILED: Argument 2 of function CONCAT_WS must be "string or array<string>", but "array<int>" was found.

Based on this SO: How to concatenate the elemets of int array to string in Hive

Incorrect:

select concat_ws('-', transform(mycolumn) using '/bin/cat') as mycolumnstr from mytable;
FAILED: ParseException line 1:22 cannot recognize input near 'transform' '(' 'mycolumn' in select expression

Correct:

with tbl as (select transform(mycolumn, tb_name) using '/bin/cat' as (mycolumnstr, tb_name) from mytable) 
select concat_ws('-', tb_name, mycolumnstr) from tbl;
Result:
tblname-[111,222,333]
tblname-[1234,123456,3423]

hive sql transform big int array to string array

1 Answers1