Hive 复杂数据类型有三种分别是Array Map Struct1、Array的使用create table tableName( ...... colName array基本类型 ...... )说明下标从0开始越界不报错以null代替准备数据zhangsan 78,89,92,96 lisi 67,75,83,94 王五 23,12这里得保证字段之间是制表符然后数组之间是逗号根据数据格式新建对应的表create table db03.arr1( name string, scores arrayint ) row format delimited fields terminated by \t collection items terminated by ,;加载数据load data local inpath /home/hivedata/arr1.txt into table db03.arr1;select * from db03.arr1;需求1查询每一个学生的第一个成绩select name,scores[0] from arr1;需求2查询拥有三科成绩的学生的第二科成绩select name,scores[1] from arr1 where size(scores) 3;需求3查询所有学生的总成绩select name,scores[0]scores[1]nvl(scores[2],0)nvl(scores[3],0) from arr1;需求4查询每个人的最后一科的成绩select name,scores[size(scores)-1] from arr1;2、展开函数的使用 explode为什么学这个因为我们想把数据变为如下格式zhangsan 78 zhangsan 89 zhangsan 92 zhangsan 96 lisi 67 lisi 75 lisi 83 lisi 94 王五 23 王五 12explode 专门用于炸集合。select explode(scores) from arr1; col 78 89 92 96 67 75 83 94 23 12这里我们想统计合计结果这样写报错了select name,explode(scores) from arr1FAILED: SemanticException [Error 10081]: UDTFs are not supported outside the SELECT clause, nor nested in expressions我们需要使用下面得方法进行计算合计-- lateral view:虚拟表。会将UDTF函数生成的结果放到一个虚拟表中然后这个虚拟表会和输入行进行join来达到数据聚合的目的。具体使用select name,cj from arr1 lateral view explode(scores) mytable as cj;解释一下lateral view explode(scores) 形成一张虚拟的表表名需要自己起里面的列有几列就起几个别名其他的就跟正常的虚拟表一样了。然后我们在计算每个人的总分select name,sum(cj) from arr1 lateral view explode(scores) mytable as cj group by name; 等同于如下写法 select name,sum(score) from (select name,score from arr1 lateral view explode(scores) myscore as score ) t group by name;3、Map的使用语法格式create table tableName( ....... colName mapT,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 db03.map1( name string, scores mapstring,int ) row format delimited fields terminated by \t collection items terminated by , map keys terminated by :;加载数据load data local inpath /home/hivedata/map1.txt into table db03.map1;查看数据select * from db03.map1;需求1:查询数学大于35分的学生的英语和自然成绩select name,scores[english],scores[nature] from map1 where scores[math] 35;需求2 :查看每个人的前两科的成绩总和select name,scores[chinese]scores[math] from map1;需求3将数据展示如下-- 展开效果zhangsan chinese 90zhangsan math 87zhangsan english 63zhangsan nature 76select name,subject,cj from map1 lateral view explode(scores) mytable as subject,cj ;需求4统计每个人的总成绩select name,sum(cj) from map1 lateral view explode(scores) mytable as subject,cj group by name; 假如根据总成绩降序排序 select name,sum(score) sumScore from map1 lateral view explode(scores) myscore as subject,score group by name order by sumScore desc; 子查询写法 select name,sum(score) from ( select name,subject,score from map1 lateral view explode(scores) mytable as subject,score ) a group by name;需求5行转列-- 将下面的数据格式 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_temp as select name,subject,cj from map1 lateral view explode(scores) mytable as subject,cj ;先将学科和成绩形成一个kv对其实就是字符串拼接--使用concat函数,进行字符串拼接 select name,concat(subject,:,cj) from map_temp;-- collect_set() 将分组的数据变成一个set集合。里面的元素是不可重复的。 select name,collect_set(concat(subject,:,cj)) from map_temp group by name;-- concat_ws(分隔符,集合) : 将集合中的所有元素通过分隔符变为字符串。 select name,concat_ws(,,collect_set(concat(subject,:,cj))) from map_temp group by name;--将字符串变为map集合 使用一个函数 str_to_map select name,str_to_map(concat_ws(,,collect_set(concat(subject,:,cj)))) from map_temp group by name;4、Struct结构体create table tableName( ........ colName structsubName1:Type,subName2:Type,........ ........ ) 有点类似于java类 调用的时候直接. colName.subName数据准备struct1.txtzhangsan 90,87,63,76 lisi 60,30,78,0 wangwu 89,25,81,9创建表create table if not exists struct1( name string, score structchinese:int,math:int,english:int,natrue:int ) row format delimited fields terminated by \t collection items terminated by ,;加载数据load data local inpath /home/hivedata/struct1.txt into table struct1;查看数据有点像map:查询数学大于35分的学生的英语和语文成绩select name, score.english,score.chinese from struct1 where score.math 35;