CODEWITHSUNDEEP
arrayshivehiveql
kikee1222
kikee1222

Reputation: 2006

Querying a set in Hive if it contains

I have the below, which gives me a set in hive

collect_set(quotaname)

Sample output:

["Quota1", "Quota2"]

I have tried to use contains or LIKE operator, but I can't figure out how to say If the last item in the set = X then...

Can anyone advise me?

Upvotes: 1

Views: 673

Answers (1)

leftjoin
leftjoin

Reputation: 38290

You can use array_contains:

hive> select array_contains(array("Quota1", "Quota2"),'Quota2');
OK
true
Time taken: 0.144 seconds, Fetched: 1 row(s)
hive> select array_contains(array("Quota1", "Quota2"),'Quota3');
OK
false
Time taken: 0.096 seconds, Fetched: 1 row(s)

For accessing last item in a set, use set[size(set)-1]:

with mydata as (
 select array('Quota1', 'Quota2') as myset
)
select case when myset[size(myset)-1] = 'Quota2' then 'Contains!' else 'No' end from mydata ;

Result:

OK
Contains!
Time taken: 3.375 seconds, Fetched: 1 row(s)

You can use LIKE operator: myset[size(myset)-1] LIKE '%Quota%', etc, etc

Upvotes: 1

Related Questions