定义格式如下：
create table tableName(
.......
colName map<T,T>
......
)
案例准备：
测试数据如下
zhangsan chinese:90,math:87,english:63,nature:76
lisi chinese:60,math:30,english:78,nature:0
wangwu chinese:89,math:25
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/' into table map1;
select *
from map1;
结果如下,可以看出,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} |
+--------+-------------------------------------------------+
查询语句：
--查询数学大于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|
+--------+---+
展开查询
- 展开效果
zhangsan chinese 90
zhangsan math 87
zhangsan english 63
zhangsan nature 76
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 |
+---------+-------+
-- 使用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 |
+--------+---------+-------+
- -统计每个人的总成绩
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|
+--------+---+
将数据动态写入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
准备数据
create table map_tmp as
select name, m_subject, m_score
from map1 lateral view explode(score) subview as
m_subject, m_score;
开始写：
--第一步：将科目和成绩组合在一起，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;
总结
-
hive的map类型创建时需要指定分隔符
collection items terminated by ','
map keys terminated by ':';
-
map类型可以通过[]来取值