Hive 复杂数据类型之map_hive map

定义格式如下：

create table tableName(
.......
colName map<T,T>
......
)
1
2
3
4
5

案例准备：

测试数据如下

zhangsan	chinese:90,math:87,english:63,nature:76
lisi	chinese:60,math:30,english:78,nature:0
wangwu	chinese:89,math:25
1
2
3

create table if not exists map1(
    name string,
    score map<string,int>
)
row format delimited
fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':';
load data local inpath '/data/map1.txt' into table map1;
select *
from map1;
1
2
3
4
5
6
7
8
9
10
11

结果如下,可以看出,map整体用{}包裹

+--------+-------------------------------------------------+
|name    |score                                            |
+--------+-------------------------------------------------+
|zhangsan|{"chinese":90,"math":87,"english":63,"nature":76}|
|lisi    |{"chinese":60,"math":30,"english":78,"nature":0} |
|wangwu  |{"chinese":89,"math":25}                         |
+--------+-------------------------------------------------+
1
2
3
4
5
6
7

查询语句：

--查询数学大于35分的学生的英语和自然成绩：
select m.name,m.score['english'] english, m.score['nature'] nature
from map1 m
where m.score['math']>35;

+--------+-------+------+
|name    |english|nature|
+--------+-------+------+
|zhangsan|63     |76    |
+--------+-------+------+


--查看每个人的前两科的成绩总和
select  m.name,m.score['chinese']+m.score['math'] from map1 m;
+--------+---+
|name    |c1 |
+--------+---+
|zhangsan|177|
|lisi    |90 |
|wangwu  |114|
+--------+---+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

展开查询

- 展开效果
zhangsan	chinese		90
zhangsan	math	87
zhangsan	english 	63
zhangsan	nature		76
1
2
3
4
5

map类型的也可以用explode展开

select explode(score) as (m_subject,m_score) from map1;
+---------+-------+
|m_subject|m_score|
+---------+-------+
|chinese  |90     |
|math     |87     |
|english  |63     |
|nature   |76     |
|chinese  |60     |
|math     |30     |
|english  |78     |
|nature   |0      |
|chinese  |89     |
|math     |25     |
+---------+-------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

-- 使用lateral view explode 结合查询：
select name, m_subject, m_score
from map1 lateral view explode(score) subview as
         m_subject, m_score;
+--------+---------+-------+
|name    |m_subject|m_score|
+--------+---------+-------+
|zhangsan|chinese  |90     |
|zhangsan|math     |87     |
|zhangsan|english  |63     |
|zhangsan|nature   |76     |
|lisi    |chinese  |60     |
|lisi    |math     |30     |
|lisi    |english  |78     |
|lisi    |nature   |0      |
|wangwu  |chinese  |89     |
|wangwu  |math     |25     |
+--------+---------+-------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

- -统计每个人的总成绩
select name, sum(m_score)
from map1 lateral view explode(score) subview as
         m_subject, m_score
group by name;
+--------+---+
|name    |_c1|
+--------+---+
|lisi    |168|
|wangwu  |114|
|zhangsan|316|
+--------+---+

1
2
3
4
5
6
7
8
9
10
11
12
13

将数据动态写入map字段中

就是把上面的流程反过来

将下面的数据格式
zhangsan        chinese 90
zhangsan        math    87
zhangsan        english 63
zhangsan        nature  76
lisi    chinese 60
lisi    math    30
lisi    english 78
lisi    nature  0
wangwu  chinese 89
wangwu  math    25
wangwu  english 81
wangwu  nature  9
转成：
zhangsan chinese:90,math:87,english:63,nature:76
lisi chinese:60,math:30,english:78,nature:0
wangwu chinese:89,math:25,english:81,nature:9
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

准备数据

create table map_tmp as
select name, m_subject, m_score
from map1 lateral view explode(score) subview as
         m_subject, m_score;
1
2
3
4

开始写：

--第一步：将科目和成绩组合在一起，concat
select name,concat(m_subject,":",m_score) from map_tmp;
+--------+----------+
|name    |c1        |
+--------+----------+
|zhangsan|chinese:90|
|zhangsan|math:87   |
|zhangsan|english:63|
|zhangsan|nature:76 |
|lisi    |chinese:60|
|lisi    |math:30   |
|lisi    |english:78|
|lisi    |nature:0  |
|wangwu  |chinese:89|
|wangwu  |math:25   |
+--------+----------+


--第二步: 将所有属于同一个人的数据组合在一起
select name,collect_set(concat(m_subject,":",m_score)) from map_tmp
group by name;
+--------+-------------------------------------------------+
|name    |c1                                               |
+--------+-------------------------------------------------+
|lisi    |["chinese:60","math:30","english:78","nature:0"] |
|wangwu  |["chinese:89","math:25"]                         |
|zhangsan|["chinese:90","math:87","english:63","nature:76"]|
+--------+-------------------------------------------------+


--第三步:将数组变成一个字符串concat_ws
select name,concat_ws(",",collect_set(concat(m_subject,":",m_score))) from map_tmp
group by name;

+--------+---------------------------------------+
|name    |c1                                     |
+--------+---------------------------------------+
|lisi    |chinese:60,math:30,english:78,nature:0 |
|wangwu  |chinese:89,math:25                     |
|zhangsan|chinese:90,math:87,english:63,nature:76|
+--------+---------------------------------------+


--第四步:将字符串转成map 使用函数str_to_map(text, delimiter1, delimiter2)
--text：是字符串
--delimiter1：多个键值对之间的分隔符
--delimiter2：key和value之间的分隔符

select name,
       str_to_map(concat_ws(",",collect_set(concat(m_subject,":",m_score))),",",":")
       from map_tmp
group by name;
+--------+---------------------------------------------------------+
|name    |c1                                                       |
+--------+---------------------------------------------------------+
|lisi    |{"chinese":"60","math":"30","english":"78","nature":"0"} |
|wangwu  |{"chinese":"89","math":"25"}                             |
|zhangsan|{"chinese":"90","math":"87","english":"63","nature":"76"}|
+--------+---------------------------------------------------------+

--第五步：存储准备的表中
create table map2 as
select name,
       str_to_map(concat_ws(",",collect_set(concat(m_subject,":",m_score))),",",":")
       from map_tmp
group by name;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

总结

• hive的map类型创建时需要指定分隔符

 collection items terminated by ','
map keys terminated by ':';
1
2

• map类型可以通过[]来取值