Apache Hive group_concat Alternative and Example

  • Post author:
  • Post last modified:February 28, 2018
  • Post category:BigData
  • Reading time:2 mins read

Group concat is a single string representing the argument value concatenated together for each row of the result set. The resulting string is a comma separated values. Many relational databases supports group-concat functionality as a standard built in function. Unfortunately, Hive does not have group_concat function. In this article, we will check Apache Hive group_concat alternative functions and working examples.

Hive group_concat Alternative

Apache Hive group_concat alternative Functions

As mentioned earlier, Apache Hive does not support group_concat function. You have to use other built in functions available in Hive to perform group_concat. Apache Hive has important array function, collect_set. You can use this built in function along with concat_ws function as Hive group_concat alternative.

We shall see these usage of these functions with an examples.

Hive collect_set and concat_ws function Syntax

Below is the syntax of collect_set and concat_ws built in functions:

collect_set(col);
concat_ws(string SEP, array<string>);

Apache Hive group_concat alternative Examples

You can use collect_set and concat_ws functions to perform group_concat in Apache Hive. Below is the examples on usage of these two functions as an alternative to RDBMS group_concat function:

https://gist.github.com/4b54c5b6744449df42aa7d4544967225

Also Read: