How to compute the intersections and unions of two arrays in Hive?

Question

For example, the intersection

select intersect(array("A","B"), array("B","C"))

should return

["B"]

and the union

 select union(array("A","B"), array("B","C"))

should return

["A","B","C"]

What's the best way to make this in Hive? I have checked the hive documentation, but cannot find any relevant information to do this.

Did you try `select array("A","B") UNION ALL select array("B","C"))`? — OneCricketeer, Mar 22 '16 at 12:34
Possible duplicate of [Array intersect Hive](http://stackoverflow.com/questions/21578477/array-intersect-hive) — OneCricketeer, Mar 22 '16 at 12:36

score 5 · Answer 1 · answered Mar 22 '16 at 12:28

5

Your problem solution is here. Go to the githubLink, there is lot of udfs are created by klout. Download, crate the JAR and add the JAR in the hive. Example

 CREATE TEMPORARY FUNCTION combine AS 'brickhouse.udf.collect.CombineUDF';
 CREATE TEMPORARY FUNCTION combine_unique AS 'brickhouse.udf.collect.CombineUniqueUDAF';

select combine_unique(combine(array('a','b','c'), array('b','c','d'))) from reqtable;

OK
["d","b","c","a"]

answered Mar 22 '16 at 12:28

Kishore

5,761
5
28
53

3

The correct function found in the link above should be [brickhouse.udf.collect.ArrayIntersectUDF](https://github.com/klout/brickhouse/blob/master/src/main/java/brickhouse/udf/collect/ArrayIntersectUDF.java) which can be used as `intersect_array(array1, array2, ...)` and [brickhouse.udf.collect.ArrayUnionUDF](https://github.com/klout/brickhouse/blob/master/src/main/java/brickhouse/udf/collect/ArrayUnionUDF.java) as `array_union(array1, array2, ...)` – Christoph Körner Nov 03 '16 at 09:16

score 0 · Answer 2 · edited Mar 09 '23 at 23:39

0

array_intersect(array1,array2,...)

and

array_union(array1, array2, ...)

edited Mar 09 '23 at 23:39

Ahmed Sbai

10,695
9
19
38

answered Mar 07 '23 at 03:28

Jinming Zhang

49
2

How to compute the intersections and unions of two arrays in Hive?

2 Answers2

Linked