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

来源:互联网 发布:fifo和lifo存货的算法 编辑:程序博客网 时间:2024/06/14 17:47

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


数据类型描述字面语法示例struct和C语言中的struce或者对象类型,都可以使用“点”符号访问元素内容。例如,如果某个列的数据类型是struce{first string,last string},那么第1个元素可以通过字段名.first来引用struct('John','Doe')mapMap是一组键-值对元组集合,使用数组表示法(例如['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>



0 0
原创粉丝点击