How can I use the LIKE operator on a map type in hiveql?

user_16236118 0
Viewed 128

I want to select two columns out of students:

id_test         int
number  map<string,string>

I tried followed command with the LIKE Operator:

SELECT id_test ,number FROM students WHERE id_test = 123456 AND number LIKE %MOBILE%;

And get this error:

FAILED: SemanticException [Error 10014]: Line 1:82 Wrong arguments ''%MOBILE%'': No 
matching method for class org.apache.hadoop.hive.ql.udf.UDFLike with 
(map<string,string>, string). Possible choices: _FUNC_(string, string)

Code for reproduction:

CREATE TABLE students(id_test INT, number MAP<STRING, STRING>) ROW FORMAT DELIMITED FIELDS TERMINATED by
'|' COLLECTION ITEMS TERMINATED BY ',' MAP KEYS TERMINATED BY ':';

INSERT INTO TABLE students SELECT 123434, map('MOBILE','918-555-1162') FROM existingtable LIMIT 1;
INSERT INTO TABLE students SELECT 245678, map('WORK','806-555-4722') FROM existingtable LIMIT 1;
hiveql hive sql-like dictionary sql
2 Answers

Explode map, then you can filter keys using LIKE. If you want to get single row per id_test, number even if there are many keys satisfying LIKE condition, use GROUP BY or DISTINCT.

Demo:

with students as (--one record has many MOBILE* keys in the map
SELECT 123434 id_test , map('MOBILE','918-555-1162', 'OFFICE', '123456', 'MOBILE2', '5678') number union all
SELECT 245678, map('WORK','806-555-4722')
)

select s.id_test, s.number 
  from students s
      lateral view explode(number) n as key,value
where n.key like '%MOBILE%'
group by s.id_test, s.number

Result:

123434 {"MOBILE":"918-555-1162","MOBILE2":"5678","OFFICE":"123456"}

If you know the key exactly 'MOBILE' then better to filter like this: where number['MOBILE'] is not null, without explode.

user_27003440 
select *
from   students
where  concat_ws(' ',map_keys(number)) like '%MOBILE%'

The following is posted here just that you could see how the expression used with LIKE looks like:

select concat_ws(' ',map_keys(number))
from   students

+-----------------------+
|          _c0          |
+-----------------------+
| MOBILE MOBILE2 OFFICE |
| WORK                  |
+-----------------------+
user_63364790
Related