Concatenated multiple lines with a delimiter in the hive

Question

I need to specify the string values ​​of a string with the character '~' as a separator. I have the following data:

I need to execute the "Comment" column for each "id" in ascending order of "row_id" with "~" as a separator.

The expected result is as follows:

GROUP_CONCAT is not an option since it is not recognized in my version of Hive. I can use collect_set or collect_list, but I will not insert a separator between them.

Is there any work?

Answer 1

collect_list , .
concat_ws.

---

.

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

from        mytable 

group by    id
;

---

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