How to get all the elements in an array but not in another array in HIVE?

Question

For example, I have two columns of arrays now:

id     col1     col2
A      [1, 3]   [1, 2, 3]
B      [2]      [1, 2, 3]

what I want is all the elements in col2 but not in col1:

id     output
A      [2]
B      [1, 3]

How can I achieve this?

Using Pandas this is one line job

Ajay A
– Ajay A

2020-09-30 06:59:27 +00:00
Commented Sep 30, 2020 at 6:59 — Ajay A
– Ajay A, Commented Sep 30, 2020 at 6:59

leftjoin · Accepted Answer · 2020-09-30 07:00:41Z

2

Explode col2 array, use array_contains to check each element is in another array, collect array again for elements not in col1 array

select t.id, 
       collect_set(case when array_contains(t.col1, e.elem) then NULL else e.elem end) as result  
  from my_table t 
       lateral view explode(t.col2) e as elem
 group by t.id

answered Sep 30, 2020 at 7:00

leftjoin

38.5k8 gold badges64 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to get all the elements in an array but not in another array in HIVE?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related