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