Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Concat multiple rows with a delimiter in Hive

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?

like image 621
Vaishak Avatar asked Mar 22 '17 10:03

Vaishak


People also ask

How do I concatenate two fields in hive?

Use concat_ws function to concatenate values with ^ as a delimiter. If columns are not string, wrap them with cast as string using shell, this will allow concat_ws work with strings and not-string columns.

How do you concatenate in Hadoop?

You can use || operator to concatenate two or more strings. The result of the operation is always a string. If an operand is a number, it is implicitly converted to string before concatenation. If an operand is NULL, it is treated as an empty string '' in the concatenation.

Can we use where clause in hive?

The Hive Query Language (HiveQL) is a query language for Hive to process and analyze structured data in a Metastore. This chapter explains how to use the SELECT statement with WHERE clause. SELECT statement is used to retrieve the data from a table. WHERE clause works similar to a condition.

What is Concat_ws?

The CONCAT_WS() function adds two or more strings together with a separator.


1 Answers

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     |
+----+-------------+
like image 136
David דודו Markovitz Avatar answered Oct 21 '22 21:10

David דודו Markovitz