Hive内嵌集合函数：size,map_keys,map_values,array_contains,sort_array等详解

0.hive官方函数解释

hive官网函数大全地址：HIVE官网函数大全地址

Collection Functions

Return Type

Name(Signature)

Description

int

size(Map<K.V>)

Returns the number of elements in the map type.

int

size(Array)

Returns the number of elements in the array type.

boolean

array_contains(Array, value)

Returns TRUE if the array contains value.

array

map_values(Map<K.V>)

Returns an unordered array containing the values of the input map.

array

sort_array(Array)

Sorts the input array in ascending order according to the natural ordering of the array elements and returns it (as of version0.9.0).

array

map_keys(Map<K.V>)

Returns an unordered array containing the keys of the input map.

1.使用演示

1.1数据展示，手动生成map类型数据

 select 
str_to_map(concat(path_id,':',filter_name ))   ---是map类型的
from FDM_SOR.T_FIBA_MULTI_UBA_CFG_PATH_DETAIL_D 
group by path_id,filter_Name 

1.2函数测试

select 
size(m),  --求map的长度
map_keys(m), --将map中对应的所有keys.存储格式为array
map_values(m),  --将map中对应的所有values
sort_array(map_keys(m)),  --对map的keys进行排序
array_contains(map_keys(m),'162')  ---map的keys中是否包含162
from ( select 
str_to_map(concat(path_id,':',filter_name )) m   ---是map类型的
from FDM_SOR.T_FIBA_MULTI_UBA_CFG_PATH_DETAIL_D 
group by path_id,filter_Name ) a