Hive Aggregate function for merging arrays

Question

I need to merge arrays in a GROUP BY in HiveSQL. The table schema is something like this:

key int,
value ARRAY<int>

Now here is the SQL I would like to run:

SELECT key, array_merge(value)
FROM table_above
GROUP BY key

If this array_merge function only keeps unique values, that will be even better but not must.

Cheers, K

Answer 1

there is no UDAF to perform that kind of operation. The following query should result in the same without much overhead (keep running one map and one reduce operation) removing duplicates

select key, collect_set(explodedvalue) from (
  select key, explodedvalue from table_above lateral view explode(value) e as explodedvalue
) t group by key;