【Hive】集合函数

函数名称	返回值类型	描述
size(Map<K,V>)	int	返回map类型数据的长度
size(Array)	int	返回array类型数据的长度
map_keys(Map<K,V>)	array	返回map类型数据的键
map_values(Map<K,V>)	array	返回map类型数据的值
array_contains(Array, value)	boolean	判断value是否存在于array中
sort_array(Array)	array	升序排序array类型数据

1. 创建测试表并导入测试数据

#创建测试表complex
create table complex
(id int,
 profession ARRAY<string>,
 info map<string,string>,
 address struct<province:string, city:string, district:string>)
row format delimited fields terminated by ' '
collection items terminated by '#'
map keys terminated by ':';

#测试数据
1 teacher#driver name:john#age:28 shandong#qingdao#huangdaoqu
2 student#it#worker name:smith#age:12 shandong#dezhou#dechengqu

#导入测试数据
load data inpath '/complex.txt' overwrite into table complex;

#查看complex表数据
select * from complex;
+-------------+----------------------------+------------------------------+----------------------------------------------------+--+
| complex.id  |     complex.profession     |         complex.info         |                  complex.address                   |
+-------------+----------------------------+------------------------------+----------------------------------------------------+--+
| 1           | ["teacher","driver"]       | {"name":"john","age":"28"}   | {"province":"shandong","city":"qingdao","district":"huangdaoqu"} |
| 2           | ["student","it","worker"]  | {"name":"smith","age":"12"}  | {"province":"shandong","city":"dezhou","district":"dechengqu"} |
+-------------+----------------------------+------------------------------+----------------------------------------------------+--+

2. size(Map<K,V>) 和size(Array)

0: jdbc:hive2://node03:10000> select id,
. . . . . . . . . . . . . . > size(profession) as size_array,
. . . . . . . . . . . . . . > size(info) as size_map
. . . . . . . . . . . . . . > from complex;
+-----+-------------+-----------+--+
| id  | size_array  | size_map  |
+-----+-------------+-----------+--+
| 1   | 2           | 2         |
| 2   | 3           | 2         |
+-----+-------------+-----------+--+

3. map_keys(Map<K,V>) 和map_values(Map<K,V>)

0: jdbc:hive2://node03:10000> select id, map_keys(info) as map_keys,
. . . . . . . . . . . . . . > map_values(info) as map_values
. . . . . . . . . . . . . . > from complex;
+-----+-----------------+-----------------+--+
| id  |    map_keys     |   map_values    |
+-----+-----------------+-----------------+--+
| 1   | ["name","age"]  | ["john","28"]   |
| 2   | ["name","age"]  | ["smith","12"]  |
+-----+-----------------+-----------------+--+

4. array_contains(Array, value)

0: jdbc:hive2://node03:10000> select id, profession, array_contains(profession, 'teacher') as array_contains from complex;
+-----+----------------------------+-----------------+--+
| id  |         profession         | array_contains  |
+-----+----------------------------+-----------------+--+
| 1   | ["teacher","driver"]       | true            |
| 2   | ["student","it","worker"]  | false           |
+-----+----------------------------+-----------------+--+

5. sort_array(Array)

0: jdbc:hive2://node03:10000> select id, sort_array(profession) as sort_array from complex;
+-----+----------------------------+--+
| id  |         sort_array         |
+-----+----------------------------+--+
| 1   | ["driver","teacher"]       |
| 2   | ["it","student","worker"]  |
+-----+----------------------------+--+