How to search a string from a collection of string in hive column

user_6315118 0
Viewed 28

I have below two input table : table 1 and table 2

Input : table 1 id and currency are of type string

ID Currency
1 USD,JPY,EUR
2 NOK,MXN
3 AUD
4 AUD,HKD

input: table2 exception_currency are of type string

exception_currency
AUD
NOK
USD
HKD

expected output as below Exception is yes if the each currency column values from table 1 and exception_currency from table 2 are not matching. For example for id 1, the exception is "YES" because JPY and EUR are not available in the table 2.

ID Currency Exception
1 USD,JPY,EUR YES
2 NOK,MXN YES
3 AUD NO
4 AUD,HKD NO

I tried below code but not getting expected results.

select
id,
currency,
case when array_contains(split(t1.currency,','), t2.exception_currency) then 'NO' else 'YES' as exception
from table1 t1 left join table2 t2 on (t1.currency=t2.exception_currency);
hive hql string
1 Answers

First seperate the comma separated values into rows and then compare it with table2.

Select id, currency,
Max(excep) as exception 
from table1
Join (Select
Id, Case when exception_currency is null then 'yes' else 'no' end excep
From 
(SELECT ID, sep_curr
FROM table1 lateral view explode(split(currency,',')) currency AS sep_curr) rs
Left join table2 on exception_currency= sep_curr
) Rs on rs.id= table1.id
Group by id, currency

Here lateral view will generate rows from comma separated values.

Joining it with exception table to get non existent exception.

Then finally max will show yes if at least one value is exception.

user_64305230
Related