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.
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:
- Commonly used Hadoop Hive Commands
- Hadoop Hive Dynamic Partition and Examples
- Run Hive Script File Passing Parameter and Working Example
- Hadoop Hive Cumulative Sum, Average and Example
- Hadoop Hive Analytic Functions and Examples