How can I convert array to string in hive sql?

Question

I want to convert an array to string in hive. I want to collect_set array values to convert to string without [[""]].

select actor, collect_set(date) as grpdate from actor_table group by actor;

so that [["2016-07-01", "2016-07-02"]] would become 2016-07-01, 2016-07-02

leftjoin · Accepted Answer · 2020-02-15 08:41:33Z

54

Use concat_ws(string delimiter, array<string>) function to concatenate array:

select actor, concat_ws(',',collect_set(date)) as grpdate from actor_table group by actor;

If the date field is not string, then convert it to string:

concat_ws(',',collect_set(cast(date as string)))

Read also this answer about alternative ways if you already have an array (of int) and do not want to explode it to convert element type to string: How to concatenate the elements of int array to string in Hive

answered Aug 2, 2016 at 6:37

leftjoin

38.5k8 gold badges64 silver badges126 bronze badges

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

Comments

Tavy · Accepted Answer · 2021-10-12 14:05:35Z

1

Sometimes, you may need a JSON formatted list, so you can simply use:

SELECT CAST(COLLECT_SET(date) AS STRING) AS dates FROM actor_table

PS: I needed this but found only your question about array to string conversion.

answered Oct 12, 2021 at 14:05

Tavy

90111 silver badges16 bronze badges

In which version of Hive does it work? In 1.2 and 2,3,6 it says cast can only work with primitive types