Concat multiple rows with a delimiter in Hive

sql hive hiveql concat
Vaishak picture Vaishak · Mar 22, 2017 · Viewed 25k times · Source

I need to concat string values row wise with '~' as delimiter. I have the following data:

enter image description here

I need to concat 'Comment' column for each 'id' in the ascending order of 'row_id' with '~' as delimiter.

Expected output is as below:

enter image description here

GROUP_CONCAT is not an option since its not recognized in my Hive version. I can use collect_set or collect_list, but I won't be able to insert delimiter in between.

Is there any workaround?

Answer

David דודו Markovitz picture David דודו Markovitz · Mar 22, 2017

collect_list returns array, not string.
Array can be converted to delimited string using concat_ws.

This will work, with no specific order of comments. 

select      id
           ,concat_ws('~',collect_list(comment)) as comments

from        mytable 

group by    id
;

+----+-------------+
| id |  comments   |
+----+-------------+
|  1 | ABC~PRQ~XYZ |
|  2 | LMN~OPQ     |
+----+-------------+

Related questions

Hive insert query like SQL

I am new to hive, and want to know if there is anyway to insert data into hive table like we do in SQL. I want to insert my data into hive like INSERT INTO tablename VALUES (value1,value2..) I …

Read More
How to get/generate the create statement for an existing hive table?

Assuming you have "table" already in Hive, is there a quick way like other databases to be able to get the "CREATE" statement for that table?

Read More
Combine many tables in Hive using UNION ALL?

I'm trying to append one variable from several tables together (aka row-bind, concatenate) to make one longer table with a single column in Hive. I think this is possible using UNION ALL based on this question ( HiveQL UNION ALL ), but …

Read More