How can I convert array to string in hive sql?

arrays string hive concatenation hiveql
Bethlee picture Bethlee · Aug 2, 2016 · Viewed 52.9k times · Source

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

Answer

leftjoin picture leftjoin · Aug 2, 2016

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

Related questions

How to add new elements to an array?

I have the following code: String[] where; where.append(ContactsContract.Contacts.HAS_PHONE_NUMBER + "=1"); where.append(ContactsContract.Contacts.IN_VISIBLE_GROUP + "=1"); Those two appends are not compiling. How would that work correctly?

Read More
How to convert UTF-8 byte[] to string?

I have a byte[] array that is loaded from a file that I happen to known contains UTF-8. In some debugging code, I need to convert it to a string. Is there a one liner that will do this? Under …

Read More
How to convert Java String into byte[]?

Is there any way to convert Java String to a byte[] (not the boxed Byte[])? In trying this: System.out.println(response.split("\r\n\r\n")[1]); System.out.println("******"); System.out.println(response.split("\r\n\r\n")[1].getBytes().…

Read More