Hive复杂数据类型Array、Map、Struct

原文转载地址:https://blog.csdn.net/qq_33398607/article/details/100099194

Array

1. 创建表，字段类型array

2. 指定数组分隔符 collection items terminated by ‘:’

3. where 查询需要使用函数

   数组包含某个值：array_contains(数组名,‘value’)

   数组长度：size(数组名)

# 创建表，字段类型array
# 指定数组分隔符 collection items terminated by ':'

create table movie(movie_name string,actors array,show_date date)
row format delimited fields terminated by ',' 
collection items terminated by ':';

+-------------+----------------+----------+
|  col_name   |   data_type    | comment  |
+-------------+----------------+----------+
| movie_name  | string         |          |
| actors      | array  |          |
| show_date   | date           |          |
+-------------+----------------+----------+
#导入数据
+-------------------+----------------------------+------------------+
| movie.movie_name  |        movie.actors        | movie.show_date  |
+-------------------+----------------------------+------------------+
| 战狼2               | ["吴京","吴刚","龙母"]           | 2017-08-16       |
| 三生三世十里桃花          | ["赵丽颖","刘亦菲","普罗米修","小李"]  | 2018-02-04       |
+-------------------+----------------------------+------------------+


# where 查询需要使用函数 array_contains(数组名,'value')
select movie_name from movie where array_contains(actors,'吴刚');
+-------------+
| movie_name  |
+-------------+
| 战狼2         |
+-------------+

# size(数组名)
select movie_name,size(actors) from movie;
+-------------+------+
| movie_name  | _c1  |
+-------------+------+
| 战狼2         | 3    |
| 三生三世十里桃花    | 4    |
+-------------+------+

Map

data

1,zhangsan,father:xiaoming#mother:xiaohuang#brother:xiaoxu,28

2,lisi,father:mayun#mother:huangyi#brother:guanyu,22

3,wangwu,father:wangjianlin#mother:ruhua#sister:jingtian,29

4,mayun,father:mayongzheng#mother:angelababy:,26

 create

create table tmap(id int,name string,family_members map<string,string>,age int)

row format delimited fields terminated by ','

collection items terminated by '#'

map keys terminated by ':'; 

search 

select id,name,family_members['father'] as father,age from tmap;
+-----+-----------+--------------+------+
| id  |   name    |    father    | age  |
+-----+-----------+--------------+------+
| 1   | zhangsan  | xiaoming     | 28   |
| 2   | lisi      | mayun        | 22   |
| 3   | wangwu    | wangjianlin  | 29   |
| 4   | mayun     | mayongzheng  | 26   |
+-----+-----------+--------------+------+
select id,name,family_members['father'] as father,family_members['sister'] as sister,age from tmap;
+-----+-----------+--------------+-----------+------+
| id  |   name    |    father    |  sister   | age  |
+-----+-----------+--------------+-----------+------+
| 1   | zhangsan  | xiaoming     | NULL      | 28   |
| 2   | lisi      | mayun        | NULL      | 22   |
| 3   | wangwu    | wangjianlin  | jingtian  | 29   |
| 4   | mayun     | mayongzheng  | NULL      | 26   |
+-----+-----------+--------------+-----------+------+

keys

select id,name,map_keys(family_members),age from tmap;
+-----+-----------+--------------------------------+------+
| id  |   name    |              _c2               | age  |
+-----+-----------+--------------------------------+------+
| 1   | zhangsan  | ["father","mother","brother"]  | 28   |
| 2   | lisi      | ["father","mother","brother"]  | 22   |
| 3   | wangwu    | ["father","mother","sister"]   | 29   |
| 4   | mayun     | ["father","mother"]            | 26   |
+-----+-----------+--------------------------------+------+

values

select id,name,map_values(family_members),age from tmap;
+-----+-----------+-------------------------------------+------+
| id  |   name    |                 _c2                 | age  |
+-----+-----------+-------------------------------------+------+
| 1   | zhangsan  | ["xiaoming","xiaohuang","xiaoxu"]   | 28   |
| 2   | lisi      | ["mayun","huangyi","guanyu"]        | 22   |
| 3   | wangwu    | ["wangjianlin","ruhua","jingtian"]  | 29   |
| 4   | mayun     | ["mayongzheng","angelababy:"]       | 26   |
+-----+-----------+-------------------------------------+------+

 size

select id,name,size(family_members),age from tmap;
+-----+-----------+------+------+
| id  |   name    | _c2  | age  |
+-----+-----------+------+------+
| 1   | zhangsan  | 3    | 28   |
| 2   | lisi      | 3    | 22   |
| 3   | wangwu    | 3    | 29   |
| 4   | mayun     | 2    | 26   |
+-----+-----------+------+------+

contains

先使用map_keys转为key数组，然后使用array_contains判断key数组中是否包含需要查询的值。

select id,name,family_members['brother'] as brother,age from tmap where array_contains(map_keys(family_members),'brother');
+-----+-----------+----------+------+
| id  |   name    | brother  | age  |
+-----+-----------+----------+------+
| 1   | zhangsan  | xiaoxu   | 28   |
| 2   | lisi      | guanyu   | 22   |
+-----+-----------+----------+------+

Struct

C语言的结构体

data

id,name,age:sex:address

1,zhangsan,18:male:深圳
2,lisi,28:female:北京
3,wangwu,38:male:广州
4,赵六,26:female:上海
5,钱琪,35:male:杭州
6,王八,48:female:南京

create

create table tstruct(id int,name string,info struct<age:int,sex:string,addr:string>)
row format delimited fields terminated by ','
collection items terminated by ':';
+-----------+-----------------------------------------+----------+
| col_name  |                data_type                | comment  |
+-----------+-----------------------------------------+----------+
| id        | int                                     |          |
| name      | string                                  |          |
| info      | struct<age:int,sex:string,addr:string>  |          |
+-----------+-----------------------------------------+----------+

search

直接用 struct.property 获取数据

select id,name,info.age,info.addr from tstruct;
+-----+-----------+------+-------+
| id  |   name    | age  | addr  |
+-----+-----------+------+-------+
| 1   | zhangsan  | 18   | 深圳    |
| 2   | lisi      | 28   | 北京    |
| 3   | wangwu    | 38   | 广州    |
| 4   | 赵六        | 26   | 上海    |
| 5   | 钱琪        | 35   | 杭州    |
| 6   | 王八        | 48   | 南京    |
+-----+-----------+------+-------+