hive数据类型

hive的复杂数据类型

create table student1(id int,name string,scores array<float>);插入数据格式{1, 'chb', [89, 90, 100]}create table student2(id int,name string,scores map<string, float>);插入数据格式:{1, 'chb', <'english', 89>}create table student3(id int,scores array<map<string, float>>);插入数据格式:{1, 'chb', [<'english', 78>, <'math', 80>}create table student4(id int,info struct<name:string, age:int, sex:string>);插入数据格式:{1, {'chb', 23, '男'}}

三种复杂类型的创建，及数据导入，查询

##结构体 struct create table t1 (id int,info struct<name:string, age:int>)row format delimited fields terminated by ','collection items terminated by ':';###测试数据[hive@test ~]$ cat struct 1,dd:202,cc:233,ee:78[hive@test ~]$ ###加载数据load data load input "/home/hive/struct" into table t1;###struct的查询  使用字段名.元素名  即info.name hive> select id , info.name, info.age from t1;OK1       dd      202       cc      233       ee      78Time taken: 0.453 seconds, Fetched: 3 row(s)hive> ###array create table t2(name string,list array<int>)row format delimited fields  terminated by ","   -- 字段之间的分割符collection items terminated by ":";              -- 集合元素之间的分割符##测试数据[hive@test ~]$ cat array zhangsan,12:13:14li,23:24:25[hive@test ~]$ ##加载数据load data load input "/home/hive/array" into table t2;###array的查询，使用字段名[元素索引] 即list[0]hive> select name, list[0] from t2;OKzhangsan        12li      23Time taken: 0.46 seconds, Fetched: 2 row(s)hive> ###mapcreate table t3(id int,scores map<String, int>)row format delimited fields  terminated by "|"  -- 字段之间的分割符collection items terminated by ","              -- 结合元素之间的分割符map keys terminated by ":";                     -- map中keyvalue的分割符###测试数据[hive@idc007129 ~]$ cat map 1|aa:12,bb:23,cc:552|aa:90,bb:89,cc:23[hive@idc007129 ~]$ ###加载数据load data local inpath "/home/hive/map" into table t3;###map的查询， 有些区别使用  字段名.['元素名'], 注意“'”, scores['aa']hive> select id, scores['aa'] from t3;OK1       122       90Time taken: 0.453 seconds, Fetched: 2 row(s)hive> select * from t3;OK1       {"aa":12,"bb":23,"cc":55}2       {"aa":90,"bb":89,"cc":23}Time taken: 0.457 seconds, Fetched: 2 row(s)hive>