hive的数据类型
1、基本类型
create table student (id int, name string, sex boolean, zipcode string);HQL语句会被翻译成MR程序的。 这些类型底层其实也会自动的被包装成可序列化的类型
八大基本类型 + String
int string float, double boolean,
date timestamp
复杂类型:
Array : 该集合中的所有元素的类型和意义都一致 (指定元素的类型)
array(key,value1,key2,value2,....)
Map : 键值对的keyvalue类型,泛型
Struct : address struct<int, string, string ,float, boolean>
union : 解决以上三种复杂类型的嵌套使用问题
2、复杂类型
例子程序:
CREATE TABLE student_c(
name STRING, favors ARRAY<STRING>, scores MAP<STRING, FLOAT>,
address STRUCT<province:STRING, city:STRING, detail:STRING, zip:INT>
) ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ';'
MAP KEYS TERMINATED BY ':' ;
2.1 array的使用
准备的数据: 在 array.txt
huangbo beijing,shanghai,tianjin,hangzhou
xuzheng changchu,chengdu,wuhan
wangbaoqiang dalian,shenyang,jilin
创建表
create table table_array (name string, city array<string>)
row format delimited fields terminated by "\t"
collection items terminated by ",";
数据导入表中
load data local inpath "/home/hadoop/array.txt" into table table_array;select * from table_array;
select name, city[1] ,city[2],city[3],city[4] from table_array;
array 使用总结:建表 建表时候用< > , 查询时候用[ ]
2.2 map 的使用
数据:map.txt
huangbo yuwen:80,shuxue:89,yingyu:95
xuzheng yuwen:70,shuxue:65,yingyu:81
wangbaoqiang yuwen:75,shuxue:100,yingyu:75
create table table_map (name string, score map<string, int>)
row format delimited fields terminated by "\t"
collection items terminated by ","
map keys terminated by ":";
load data local inpath "/home/hadoop/map.txt" into table table_map;
select * from table_map;
select name,score["shuxue"],score["yingyu"],score["yuwen"] from table_map;
map 使用总结:建表 建表时候用< > , 查询时候用[ ]
2.3 struct 的使用
数据 struct.txt
1 english,80,true
2 math,89,false
3 chinese,95,true
create table table_struct (id int, info struct<course:string, score:int, sex:boolean>)
row format delimited fields terminated by "\t"
collection items terminated by ",";
load data local inpath "/home/hadoop/struct.txt" into table table_struct;
select * from table_struct;
select id, info.course, info.sex from table_struct;
struct 使用 建表使用 <> 查询时候使用 .访问对应的字段