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

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
– David דודו Markovitz

2017-04-10 14:58:05 +00:00
Commented Apr 10, 2017 at 14:58 — David דודו Markovitz
– David דודו Markovitz, Commented Apr 10, 2017 at 14:58

David דודו Markovitz · Accepted Answer · 2017-04-10 15:18:41Z

4

This is most likely what you want

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

answered Apr 10, 2017 at 15:18

David דודו Markovitz

45.2k7 gold badges75 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

qbzenker Over a year ago

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.

Reihan_amn Over a year ago

I cannot get how ',',map_values(mymap) is related to col1. could you please explain?

David דודו Markovitz Over a year ago

mymap is col1. I wanted to clarify that we are dealing with a map here

zero · Accepted Answer · 2019-12-25 15:18:10Z

2

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

edited Dec 25, 2019 at 15:18

answered Dec 25, 2019 at 15:12

zero

2,1142 gold badges17 silver badges25 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Ref this post for more here

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Ref this post for more here

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related