Hive中的map类型及其操作

–创建表，并定义map类型

create table employee(id string, perf map)     
ROW FORMAT DELIMITED                                        
FIELDS TERMINATED BY '	'                              
COLLECTION ITEMS TERMINATED BY ','    --map形式key:value,key:value
MAP KEYS TERMINATED BY ':';  --map形式key:value,key:value
1
2
3
4
5

Map中的方法：

1. size(Map)函数：可得map的长度。返回值类型：int

2. map_keys(Map)函数：可得map中所有的key; 返回值类型: array

3. map_values(Map)函数：可得map中所有的value; 返回值类型: array

4. 判断map中是否包含某个key值：array_contains(map_keys(k_v),‘A’)或者k_V[“A”] is not null

5. 在k-v对中，若value有多个值的情况，如 {‘k1’:‘01,02,03’} ，如果要用 ‘k1’ 中 '02’作为过滤条件，则语句如下：（这里用到split来处理）select * from t where split(t.params['k1'],',')[1] ;

6. 如果过滤条件为：k2的值必须为’45’开头，则语句如下：
   （这里用到substr方法来处理，这里注明一下，1和2分别表示起始位置和长度）

   select * from t where substr(t.params[‘k2’],1,2) = ‘45’;

案例：
–字符串转map类型
–1.判断key是否在map中存在

select mm.*, array_contains(map_keys(k_v),'A') from (
select t.w,str_to_map(CONCAT_WS('&',COLLECT_SET(concat(M,":",mc))),"&",":" ) k_V  --1.拼接字串为 key:value&key:value；2.将拼接的字串转成map类型
FROM (
SELECT 1 W,'A' M,"中" mc
UNION ALL
SELECT 1 W,'B' M,"国" mc
UNION ALL
SELECT 2 W,'C' M,"好" mc
) T  group by t.w
) mm 
;
1
2
3
4
5
6
7
8
9
10
11

结果：

–2.判断key是否在map中存在

select mm.* from (
select t.w,str_to_map(CONCAT_WS('&',COLLECT_SET(concat(M,":",mc))),"&",":") k_V
FROM (
SELECT 1 W,'A' M,"中" mc
UNION ALL
SELECT 1 W,'B' M,"国" mc
UNION ALL
SELECT 2 W,'C' M,"好" mc
) T  group by t.w 
) mm where k_V["A"] is not null
;
1
2
3
4
5
6
7
8
9
10
11

结果：

–3.map的单行转多行

select mm.*, y.n_m,y.n_mc from (
select t.w,str_to_map(CONCAT_WS('&',COLLECT_SET(concat(M,":",mc))),"&",":") k_V
FROM (
SELECT 1 W,'A' M,"中" mc
UNION ALL
SELECT 1 W,'B' M,"国" mc
UNION ALL
SELECT 2 W,'C' M,"好" mc
) T  group by t.w
) mm 
lateral view explode(mm.k_V) y as n_m,n_mc
;
1
2
3
4
5
6
7
8
9
10
11
12

结果：