Hive复杂数据类型 | 每天，遇到更好的你

1.Array数据类型的使用

1.1.创建数据库表，以array作为数据类型

1
2
3

0: jdbc:hive2://192.168.230.10:10000> create table person(name string,worklocations array<string>)
row format delimited fields terminated by '\t'
collection items terminated by ',';

1.2.在/opt/modules/hive-1.2.1目录下创建person文件，并将文件内容导入hive的person表中

1
2
3

[root@node1 hive-1.2.1]# vi person.txt
zhangsanbeijing,shanghai,tianjin,hangzhou
lisichangchu,chengdu,wuhan

1 2	0: jdbc:hive2://192.168.230.10:10000> load data local inpath '/opt/modules/hive-1.2.1/person.txt' OVERWRITE INTO TABLE person;

1.3.查询person表

1.4.查询person表中array数据类型字段的指定列

0: jdbc:hive2://192.168.230.10:10000> select name,worklocations[0] from person;
0: jdbc:hive2://192.168.230.10:10000> select name,worklocations[3] from person;
0: jdbc:hive2://192.168.230.10:10000> select name,worklocations[2] from person;
0: jdbc:hive2://192.168.230.10:10000> select name,worklocations[1] from person;

1.5.查询array数据类型字段的长度

1	0: jdbc:hive2://192.168.230.10:10000> select name,size(worklocations) from person;

1.6.查询包含array数据类型字段指定列的一行数据

1	0: jdbc:hive2://192.168.230.10:10000> select * from person where array_contains(worklocations,"beijing");

1.7.查看表结构

1	0: jdbc:hive2://192.168.230.10:10000> desc person;

2.Map数据类型的使用

2.1.创建表的同时使用Map数据类型

hive> create table testmap(name string,score map<String,int>)
    > row format delimited
    > fields terminated by '\t'
    > collection items terminated by ','
    > map keys terminated by ':';

2.2.在/opt/modules/hive-1.2.1/demo/中编辑testmap.txt文件

[root@node1 demo]# vi testmap.txt

keke'数学':80,'语文':89,'英语':95
matrix'语文':60,'数学':80,'英语':99

2.3.将testmap.txt文件中的数据导入hive中的testmap表中

1	hive> load data local inpath '/opt/modules/hive-1.2.1/demo/testmap.txt' into table testmap;

2.4.查询testmap表中全部数据

1	hive> select * from testmap;

2.5.查询testmap表中数据

1	hive> select score["'语文'"],score["'英语'"] from testmap;

1	hive> select h.score["'语文'"],h.score["'英语'"] from testmap h;

1	hive> select h.score["'语文'"] from testmap h;

1	hive> select h.score["'英语'"] from testmap h;

3.struct的使用：

3.1.创建表的同时使用struct数据类型

hive> create table teststruct(id int,course struct<course:string,score:int>)
    > row format delimited
    > fields terminated by '\t'
    > collection items terminated by ':';

3.2.创建teststruct.txt文件，并将文件数据导入到hive的teststruct表中

1	[root@node1 demo]# vi teststruct.txt

1	hive> load data local inpath '/opt/modules/hive-1.2.1/demo/teststruct.txt' into table teststruct;

3.3.查询teststruct表中所有数据

1	hive> select * from teststruct;