CODEWITHSUNDEEP
hivehiveql
M. Uk
M. Uk

Reputation: 53

check if all elements in hive array contain a string pattern

I have two columns in a hive table that look something like this:

code    codeset
AB      AB123,MU124
LM      LM123,LM234

I need to verify that all elements in codeset column contain the value in code column so in the above example the first row would be false and the second row would be true.

Is there a simple way to do this that I am missing? I already read about array_contains but that returns true if just one element matches, I need all elements to contain what's in the code column.

Thanks in advance.

Upvotes: 2

Views: 1164

Answers (2)

Raptor0009
Raptor0009

Reputation: 268

select a.pattern,b.listOfInputs from ( select * from (select a.pattern, case when inputCount = sumPatternMatchResult then true else false end finalResult from (select pattern , sum(patternMatchResult) as sumPatternMatchResult from (select pattern,case when locate(pattern,input) !=0 then 1 else 0 end patternMatchResult from (select pattern,explode(split(listOfInputs,',')) as input from tbl)a ) b group by pattern) a join (select pattern , count(input) inputCount from (select pattern,explode(split(listOfInputs,',')) as input from tbl)a group by pattern) b on a.pattern=b.pattern )c where finalResult=true)a join (select * from tbl) b on a.pattern=b.pattern

This works too.

column mapping details for your table:

code -> pattern

codeset -> listOfInputs

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

split the string, explode and use lateral view to unpivot the data. Then check using locate if the split codeset contains each code (which is done with group by and having).

select code,codeset
from tbl
lateral view explode(split(codeset,',')) t as split_codeset
group by code,codeset
having sum(cast(locate(code,split_codeset)>0 as int))=count(*)

Upvotes: 2

Related Questions