Search code examples
sqldictionaryhivehiveqlsql-like

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


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;

Solution

  • 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.