Hive学习(三)-集合数据类型

Hive中的列支持使用struct,map,array三种集合数据类型。

数据类型	描述	字面语法示例
struct	和C语言中的struce或者对象类型，都可以使用“点”符号访问元素内容。例如，如果某个列的数据类型是struce{first string,last string}，那么第1个元素可以通过字段名.first来引用	struct('John','Doe')
map	Map是一组键-值对元组集合，使用数组表示法（例如['Key']）可以访问元素。例如，如果某个列的数据类型是Map，其中键->值对是'first'->'John'和'last'->'Doe'，那么可以通过字段名['last']获取最后一个元素	map('first','JOIN','last','Doe')
array	数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素，每个数组元素都有一个编号，编号从零开始。例如，数组值为['John','Doe']，那么第2个元素可以通过数组名[1]进行引用	Array('John','Doe')

下面创建一个包含3种集合数据类型的表：

hive> create table employees
    (
    Number string,
    Deductions map<string,float>,
    Subordinates array<string>,
    Address struct<street:string,city:string,pro:string>
     )
    row format delimited
    fields terminated by ','
     collection items terminated by '_'
     map keys terminated by ':';

将下面的数据上传到employees表中：

1001,salary:20020_insurance:205_accumulation:170,John_Aaron_Abbott_Abel,1 Micheigan Ave_Chicago_IL
1002,maternity:120_accumulation:170,Beau_Beck,2 Broush_Chicago_IL
1003,salary:11020_insurance:275_accumulation:210,Benedict_Benjamin_Bill,Yosef 12A_Mulans_Texas
1004,salary:7420_insurance:505_accumulation:580,Bowen,Hanan_Andal_Texas

该数据保存在文件filemacsn.txt当中。文件位于用户主目录。

hive>load data local inpath 'filemacsn.txt' into table employees;

Loading data to table default.employees
OK
Time taken: 0.733 seconds
hive>

1.查询完整数据：

hive> select * from employees;
OK
1001 {"salary":20020.0,"insurance":205.0,"accumulation":170.0} ["John","Aaron","Abbott","Abel"] {"street":"1 Micheigan Ave","city":"Chicago","pro":"IL"}
1002 {"maternity":120.0,"accumulation":170.0} ["Beau","Beck"] {"street":"2 Broush","city":"Chicago","pro":"IL"}
1003 {"salary":11020.0,"insurance":275.0,"accumulation":210.0} ["Benedict","Benjamin","Bill"] {"street":"Yosef 12A","city":"Mulans","pro":"Texas"}
1004 {"salary":7420.0,"insurance":505.0,"accumulation":580.0} ["Bowen"] {"street":"Hanan","city":"Andal","pro":"Texas"}
Time taken: 1.117 seconds, Fetched: 4 row(s)
hive>
2.查询所有street:

hive> select address.street from employees;
OK
1 Micheigan Ave
2 Broush
Yosef 12A
Hanan
Time taken: 2.64 seconds, Fetched: 4 row(s)
hive>

3.查询编号为1001的salary:

hive> select deductions['salary'] from employees where number='1001';
OK
20020.0
Time taken: 1.962 seconds, Fetched: 1 row(s)
hive>