mysql hive 内置函数_Hive内嵌集合函数：size,map_keys,map_values,array_contains,sort_array等详解...

0.hive官方函数解释

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

Collection Functions

Return Type

Name(Signature)

Description

int

size(Map)

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)

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 version 0.9.0).

array

map_keys(Map)

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