概览

hive目前支持4种复合类型

array_type : ARRAY < data_type > map_type : MAP < primitive_type, data_type > struct_type : STRUCT < col_name : data_type [COMMENT col_comment], ...> union_type : UNIONTYPE < data_type, data_type, ... > -- (Note: Available in Hive 0.7.0 and later)

常用的为前3种, 这篇文章仅介绍这3种类型的用法

 

array类型

假设有个选课数据表，有2个字段——学号id, 所选课程列表

定义表

create table if not exists course_select( stu_id string comment 'the id of a student', select_courses array<string> comment 'selected courses of a student') row format delimited fields terminated by '\t' collection items terminated by ',';

注：书写hive sql时，不要使用tab键进行缩进，会产生错误，应使用空格方式缩进

准备数据

cat course_select_data.txt

08102111 小说,戏曲,军事理论 08102244 语文,古代汉语 08102308 小说,外贸,经济学 hive> load data local inpath '/var/lib/hadoop-hdfs/hive-app-data/select_courses.dat' > overwrite into table course_select; Loading data to table default.course_select Table default.course_select stats: [numFiles=1, totalSize=98] OK Time taken: 0.861 seconds

查看数据

hive> set hive.cli.print.header=true; # 显示表结构的配置参数 hive> select * from course_select; OK course_select.stu_id course_select.select_courses 08102111 ["小说","戏曲","军事理论"] 08102244 ["语文","古代汉语"] 08102308 ["小说","外贸","经济学"] # 查询array类型字段中的第一个元素值 hive> select stu_id, select_courses[0] from course_select; stu_id _c1 08102111 小说 08102244 语文 08102308 小说

 

struct 类型

设定有这样一个学生信息表,  学号id, 学生信息

定义表

create external table if not exists student_tb( stu_id int comment 'the id of a student', basic_info struct<name:string,age:int> comment 'the info of a student') row format delimited fields terminated by '\t' collection items terminated by ',';

准备数据

cat student_info.dat

08102101 张三,23 08102121 李二,25 08102141 马冬梅,20 08102108 XiaoXiu,24 hive> load data local inpath '/var/lib/hadoop-hdfs/hive-app-data/student_info.dat' > overwrite into table student_tb; Loading data to table default.student_tb Table default.student_tb stats: [numFiles=1, totalSize=101] OK Time taken: 0.436 seconds hive> select * from student_tb; OK student_tb.stu_id student_tb.basic_info 8102101 {"name":"张三","age":23} NULL NULL NULL NULL NULL NULL

出现问题了！共有4行数据，只有第一行数据被成功解析了

经过排查，发现第2，3，4行数据字段分隔符是空格，不是表创建时指定的 '\t'

删除原表，重新操作

# 删除原表 hive> drop table student_tb; OK Time taken: 0.169 seconds # 重新定义表 hive> create external table if not exists student_tb( > stu_id int comment 'the id of a student', > basic_info struct<name:string,age:int> comment 'the info of a student') > row format delimited > fields terminated by '\t' > collection items terminated by ','; OK Time taken: 0.082 seconds # 修正数据字段分隔符后,重新导入数据 hive> load data local inpath '/var/lib/hadoop-hdfs/hive-app-data/student_info.dat' > overwrite into table student_tb; Loading data to table default.student_tb Table default.student_tb stats: [numFiles=1, numRows=0, totalSize=80, rawDataSize=0] OK Time taken: 0.759 seconds # 再次查询，这回数据都解析成功了！ hive> select * from student_tb; OK student_tb.stu_id student_tb.basic_info 8102101 {"name":"张三","age":23} 8102121 {"name":"李二","age":25} 8102141 {"name":"马冬梅","age":20} 8102108 {"name":"XiaoXiu","age":24} Time taken: 0.083 seconds, Fetched: 4 row(s)

查询struct类型字段的子字段

hive> select stu_id, basic_info.age from student_tb; Total MapReduce CPU Time Spent: 2 seconds 430 msec OK stu_id age 8102101 23 8102121 25 8102141 20 8102108 24 Time taken: 16.571 seconds, Fetched: 4 row(s)

 

map 类型

假设有这样一个表, 它的字段有——学号id, 姓名, 家庭成员, 年龄

表定义

create external table if not exists stu_info_map( stu_id int comment 'the id of a student', name string, familly map<string,string>, age int) row format delimited fields terminated by ',' collection items terminated by '#' map keys terminated BY ':';

准备数据

cat stu_info_map.dat

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:mayongzhen#mother:angelababy,26 hive> load data local inpath '/var/lib/hadoop-hdfs/hive-app-data/stu_info_map.dat' > overwrite into table stu_info_map; Loading data to table default.stu_info_map Table default.stu_info_map stats: [numFiles=1, totalSize=222] OK Time taken: 0.409 seconds hive> select * from stu_info_map; OK stu_info_map.stu_id stu_info_map.name stu_info_map.familly stu_info_map.age 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":"mayongzhen","mother":"angelababy"} 26 Time taken: 0.071 seconds, Fetched: 4 row(s)

操作map类型字段

# 查询每条数据的map都包含的father信息 hive> select name, familly['father'] from stu_info_map; Total MapReduce CPU Time Spent: 2 seconds 950 msec OK name _c1 zhangsan xiaoming lisi mayun wangwu wangjianlin mayun mayongzhen Time taken: 18.269 seconds, Fetched: 4 row(s) # 查询每条数据的sister信息, 该信息有可能不存在 hive> select name, familly['sister'] from stu_info_map; Total MapReduce CPU Time Spent: 3 seconds 110 msec OK name _c1 zhangsan NULL lisi NULL wangwu jingtian mayun NULL Time taken: 17.363 seconds, Fetched: 4 row(s

 

复合字段分隔符语法讲解

 

  # 数据表顶级字段之间的分隔符 fields terminated by ','   # 复合字段内元素之间的分隔符, 比如array中, 相邻元素以 '#' 分割   collection items terminated by '#' # 复合字段内单个元素内部组成部分的分隔符，比如map中一个kv对的k与v的分隔符 ':'   map keys terminated BY ':';