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?
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]
concat_wshere?