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

Question

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!

Do you know what a map is? – David דודו Markovitz Apr 10 '17 at 14:58 — David דודו Markovitz, Apr 10 '17 at 14:58

score 3 · Accepted Answer · answered Apr 10 '17 at 15:18

3

This is most likely what you want

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

answered Apr 10 '17 at 15:18

David דודו Markovitz

42,900
6
64
88

I did but it says since i have a rep < 15, the +1 wont show up: "those with less than 15 reputation are recorded, but do not change the publicly displayed post score." Sorry. When I get a rep > 15, I'll +1 your answer again. – qbzenker Apr 10 '17 at 16:07
1

I cannot get how ',',map_values(mymap) is related to col1. could you please explain? – Reihan_amn Feb 06 '18 at 05:26
mymap is col1. I wanted to clarify that we are dealing with a map here – David דודו Markovitz Feb 07 '18 at 18:42

zero · Answer 2 · 2019-12-25T15:18:10.240

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

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

2 Answers2

Ref this post for more here