关于hive中的array，map，struct

array<数据类型>

弹珠警察 白宝,黑宝,蓝宝,绿宝
龙珠超 孙悟空,贝吉塔,比鲁斯,布罗利

#建表语句
create table if not exists arr1(
province string,
city array<String>
)
row format delimited fields terminated by '\t'
collection items terminated by ',' #使用逗号分割数组元素
;
#加载数据
load data local inpath '/root/hivedata/arr.txt' into table arr1;
#结果查询
hive (test)> select * from arr2;
OK
弹珠警察        ["白宝","黑宝","蓝宝","绿宝"]
龙珠超  ["孙悟空","贝吉塔","比鲁斯","布罗利"]
Time taken: 0.094 seconds, Fetched: 2 row(s)

这样，一个字段应该被存进数组(集合)的值就被存进了数组(集合)。

#直接通过下表查询
hive (test)> select province,city[0] from arr2 ;
OK
弹珠警察        白宝
龙珠超  孙悟空
Time taken: 0.13 seconds, Fetched: 2 row(s)

也可以对其展开（行转列）

hive (test)> select province,person from arr2 lateral view explode(city) city as person;
OK
弹珠警察        白宝
弹珠警察        黑宝
弹珠警察        蓝宝
弹珠警察        绿宝
龙珠超  孙悟空
龙珠超  贝吉塔
龙珠超  比鲁斯
龙珠超  布罗利
Time taken: 0.127 seconds, Fetched: 8 row(s)

也可以将上一步转成列的数据再转成行

hive (test)> select province,collect_set(person) as city 
           > from (select province,person from arr2 lateral view explode(city) city as person) t1
           > group by province
           > ;
......
OK
弹珠警察        ["白宝","黑宝","蓝宝","绿宝"]
龙珠超  ["孙悟空","贝吉塔","比鲁斯","布罗利"]
Time taken: 1.839 seconds, Fetched: 2 row(s)

map<键类型，值类型>

拉面 面:200,牛肉:50,葱花:10
黄焖鸡 米:200,鸡肉:150,土豆:300

#建表
create table if not exists map1(
uname string,
itemIds map<String,int>
)
row format delimited fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':'
;
#加载数据
load data local inpath '/root/hivedata/map.txt' into table map1;
#查看
hive (test)> select * from map1;
OK
map1.uname      map1.itemids
拉面    {"面":200,"牛肉":50,"葱花":10}
黄焖鸡  {"米":200,"鸡肉":150,"土豆":300}
Time taken: 0.1 seconds, Fetched: 2 row(s)

查询数据（通过键获取值）

hive (test)> select uname,itemIds['面'] from map1;
OK
拉面    200
黄焖鸡  NULL
Time taken: 0.17 seconds, Fetched: 2 row(s)

展开（行转列）

hive (test)> select uname,name,num from map1 lateral view explode(itemIds) Ids as name,num; 
OK
拉面    面      200
拉面    牛肉    50
拉面    葱花    10
黄焖鸡  米      200
黄焖鸡  鸡肉    150
黄焖鸡  土豆    300
Time taken: 0.124 seconds, Fetched: 6 row(s)

拼接（concat）

hive (test)> select uname,concat(name,':',num) from (select uname,name,num from map1 lateral view explode(itemIds) Ids as name,num)t1;
OK
拉面    面:200
拉面    牛肉:50
拉面    葱花:10
黄焖鸡  米:200
黄焖鸡  鸡肉:150
黄焖鸡  土豆:300
Time taken: 0.143 seconds, Fetched: 6 row(s)

hive (test)> select uname,collect_set(concat(name,':',num)) from ( select uname,name,num from map1 lateral view explode(itemIds) Ids as name,num) t1 group by uname;
......
OK
拉面    ["面:200","牛肉:50","葱花:10"]
黄焖鸡  ["米:200","鸡肉:150","土豆:300"]
Time taken: 1.736 seconds, Fetched: 2 row(s)

struct<键:类型,键:类型…>

张三 河南省,洛阳市,月亮区,快乐大街11号
李四 河北省,石家庄市,太阳区,幸福大街21号

#建表
create table if not exists str(    
uname string,     
addr struct  < province:string,city:string,xian:string,dadao:string >) 
row format delimited fields terminated by '\t'    
collection items terminated by ',';
#加载数据
load data local inpath '/root/hivedata/struct.txt' into table str;
#查看
hive (test)> select * from str;
OK
str.uname       str.addr
张三    {"province":"河南省","city":"洛阳市","xian":"月亮区","dadao":"快乐大街11号"}
李四    {"province":"河北省","city":"石家庄市","xian":"太阳区","dadao":"幸福大街21号"}
Time taken: 0.086 seconds, Fetched: 2 row(s)

查询

hive (test)> select uname,addr.province,addr.city from str;
OK
uname   province        city
张三    河南省  洛阳市
李四    河北省  石家庄市
Time taken: 0.072 seconds, Fetched: 2 row(s)

总结：感觉array，map，struct是层层递进的，在前一层的基础上增加变化。