CODEWITHSUNDEEP
sqlstringhadoophive
qbzenker
qbzenker

Reputation: 4652

Hive Query: Trying to string match using WHERE, LIKE on map<string, string>

I am new to Hive and am trying to do a search similar to the following:

SELECT * FROM table1 WHERE col1 LIKE "%abcd%";

However, when I do I get the following error:

Error while compiling statement: FAILED: SemanticException [Error 10014]: Line 1:30 Wrong arguments '"%abcd%"': No matching method for class org.apache.hadoop.hive.ql.udf.UDFLike with (map, string). Possible choices: FUNC(string, string)

It looks like col1 has the wrong type, namely a data type of map. Is there a simple way to search this column for the '%abcd%' pattern? Thanks!

Upvotes: 3

Views: 10859

Answers (2)

zero
zero

Reputation: 2104

You are getting this error because you are running like query on a map<string,string> type column. In hive, if column type is map then you can directly search for the value against a key, like this

select * from mytable where mycol['mykey'] = 'myvalue'

You can also perform like on that as well 

select * from mytable where mycol['mykey'] like '%myvalue%'

Not sure if key exists then 

select * from mytable where mycol['mykey'] is not null limit 1

Ref this post for more here

Upvotes: 2

David דודו Markovitz
David דודו Markovitz

Reputation: 44991

This is most likely what you want

select  *
from    mytable 
where   concat_ws(',',map_values(mymap)) like '%abcd%'

Upvotes: 4

Related Questions