Hive学习笔记 --- Array, Map, Struct类型使用

来源：互联网发布：http aq.qq.com js 编辑：程序博客网时间：2024/06/05 16:02

Hive提供了多种复合数据类型：

Array, Struct, Map

Struct使用:

hive> create table student_test(id INT, info struct<name:STRING, age:INT>)    > ROW FORMAT DELIMITED FIELDS TERMINATED BY ','                           > COLLECTION ITEMS TERMINATED BY ':';

'FIELDS TERMINATED BY' ：字段与字段之间的分隔符
''COLLECTION ITEMS TERMINATED BY' ：一个字段各个item的分隔符

导入数据：

$ cat test5.txt 1,zhou:302,yan:303,chen:204,li:80hive> LOAD DATA LOCAL INPATH '/home/work/data/test5.txt' INTO TABLE student_test;Copying data from file:/home/work/data/test5.txtCopying file: file:/home/work/data/test5.txtLoading data to table default.student_testOKTime taken: 0.35 seconds

查询：

hive> select info.age from student_test;Total MapReduce jobs = 1......Total MapReduce CPU Time Spent: 490 msecOK30302080Time taken: 21.677 seconds

Array使用
建表：

hive> create table class_test(name string, student_id_list array<INT>)    > ROW FORMAT DELIMITED                                                > FIELDS TERMINATED BY ','                                            > COLLECTION ITEMS TERMINATED BY ':';                             OKTime taken: 0.099 seconds

导入数据：

$ cat test6.txt 034,1:2:3:4035,5:6036,7:8:9:10hive>  LOAD DATA LOCAL INPATH '/home/work/data/test6.txt' INTO TABLE class_test ;Copying data from file:/home/work/data/test6.txtCopying file: file:/home/work/data/test6.txtLoading data to table default.class_testOKTime taken: 0.198 seconds

查询：

hive> select student_id_list[3] from class_test;Total MapReduce jobs = 1......Total MapReduce CPU Time Spent: 480 msecOK4NULL10Time taken: 21.574 seconds

Map使用
建表：

hive> create table employee(id string, perf map<string, int>)         > ROW FORMAT DELIMITED                                            > FIELDS TERMINATED BY '\t'                                  > COLLECTION ITEMS TERMINATED BY ','                         > MAP KEYS TERMINATED BY ':';                                  OKTime taken: 0.144 seconds

‘MAP KEYS TERMINATED BY’ ：key value分隔符

导入数据：

$ cat test7.txt 1       job:80,team:60,person:702       job:60,team:803       job:90,team:70,person:100hive>  LOAD DATA LOCAL INPATH '/home/work/data/test7.txt' INTO TABLE employee;

查询：

hive> select perf['person'] from employee;Total MapReduce jobs = 1......Total MapReduce CPU Time Spent: 460 msecOK70NULL100Time taken: 20.902 secondshive> select perf['person'] from employee where perf['person'] is not null;   Total MapReduce jobs = 1.......Total MapReduce CPU Time Spent: 610 msecOK70100Time taken: 21.989 secondshive> <span style="font-family:Arial, Helvetica, sans-serif;"><span style="white-space: normal;"></span></span>

0 0