How to get the index of element in an array and return the next element in Hive?

user_12746574 0
Viewed 465

I have two tables.

table1:

id    |   array1
1     |     ['a', 'b', 'c']
2     |     ['b', 'a', 'c']
3     |     ['c', 'b', 'a']

table2:

id    |    value2
1     |     'b'
3     |     'a'

I wish to get the following table:

id    |    value3
1     |     'c'
2     |     'b'
3     |     'c'

Explanation: what I want is that if the id in table1 does't exist in table2, then return the first element of array1. if the id in table1 exists in table2, then return the next element of value2 in array1 (in this case if value2 is the last element in array1, return the first element of array1)

How can I achieve this goal?

hiveql hive arrays sql
1 Answers

Explode array using posexplode, join with table2, calculate position for joined rows, aggregate, extract array elements.

Demo:

with table1 as(
select stack(3,
1, array('a', 'b', 'c'),
2, array('b', 'a', 'c'),
3, array('c', 'b', 'a')
) as (id,array1)
),

table2 as(
select stack(2,
1,'b',
3,'a'
) as (id,value2)
)

select s.id, nvl(s.array1[pos], s.array1[0]) value3
from
(
select s.id, s.array1, min(case when t2.id is not null then s.pos+1 end) pos
from
(
select t.id, t.array1, a.pos, a.value1
  from table1 t
       lateral view posexplode(t.array1) a as pos, value1
)s left join table2 t2 on s.id=t2.id and s.value1=t2.value2  
group by s.id, s.array1
)s
order by id

Result:

id  value3
1   c
2   b
3   c
user_27003440
Related