Hive: How to check if values of one array are present in another?

Question

I have two arrays like this , which are being returned from a UDF I created:

array A - [P908,S57,A65] array B - [P908,S57]

I need to check if elements of array A are present in array B, or elements of array B are present in array A using hive queries.

I am stuck here. Could anyone suggest a way?

Can I also return some other data type from the UDF in place of array to make the comparison easier?

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

select      concat(',',concat_ws(',',A),',') regexp 
                concat(',(',concat_ws('|',B),'),')  as are_common_elements
           
from        mytable
;

Demo

create table mytable (id int,A array<string>,B array<string>);

insert into table mytable 

                select  1,array('P908','S57','A65'),array('P908','S57')
    union all   select  2,array('P908','S57','A65'),array('P9','S5777')
;      

select * from mytable;

+------------+----------------------+----------------+
| mytable.id |      mytable.a       |   mytable.b    |
+------------+----------------------+----------------+
|          1 | ["P908","S57","A65"] | ["P908","S57"] |
|          2 | ["P908","S57","A65"] | ["P9","S5777"] |
+------------+----------------------+----------------+

select      id
           ,concat(',',concat_ws(',',A),',')    as left_side_of_regexp
           ,concat(',(',concat_ws('|',B),'),')  as right_side_of_regexp
           
           ,concat(',',concat_ws(',',A),',') regexp 
                concat(',(',concat_ws('|',B),'),')  as are_common_elements
           
from        mytable
;

+----+---------------------+----------------------+---------------------+
| id | left_side_of_regexp | right_side_of_regexp | are_common_elements |
+----+---------------------+----------------------+---------------------+
|  1 | ,P908,S57,A65,      | ,(P908|S57),         | true                |
|  2 | ,P908,S57,A65,      | ,(P9|S5777),         | false               |
+----+---------------------+----------------------+---------------------+

score 0 · Answer 2 · edited Feb 19 '17 at 14:10

We can do this using the Lateral view. Lets we have 2 tables , Table1 and Table2 and column with array field as col1 and col2 respectively in the tables. Use something like below:-

select collect_set (array_contains (col1 , r.tab2) ) 
from table1 , 
(select  exp1 as tab2 
 from    (table2 t2 lateral view explode(col2) exploded_table as exp1 ) ) r

You can also use array_intersection or other array function.

Hive: How to check if values of one array are present in another?

2 Answers2

Demo