CODEWITHSUNDEEP
hivehiveqlhive-udf
Kuwali
Kuwali

Reputation: 253

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

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?

Upvotes: 2

Views: 3030

Answers (2)

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

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               |
+----+---------------------+----------------------+---------------------+

Upvotes: 1

Ashish Singh
Ashish Singh

Reputation: 533

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.

Upvotes: 0

Related Questions