第七章 Hive数据类型的讲解

在hive中，数据类型分为基础数据类型和复杂数据类型两大类型

7.1 数据类型

分类	类型	描述	字面量示例
基本类型	BOOLEAN	true/false	TRUE
	TINYINT	1字节的有符号整数 -128~127	1Y
	SMALLINT	2个字节的有符号整数，-32768~32767	1S
	INT	4个字节的带符号整数	1
	BIGINT	8字节带符号整数	1L
	FLOAT	4字节单精度浮点数	1.0
	DOUBLE	8字节双精度浮点数	1.0
	DEICIMAL	任意精度的带符号小数	1.0
	STRING	字符串，变长	“a”,’b’
	VARCHAR	变长字符串,要设置长度	“a”,’b’
	CHAR	固定长度字符串	“a”,’b’
	BINARY	字节数组	无法表示
	TIMESTAMP	时间戳，纳秒精度	122327493795，另一种“yyyy-MM-dd HH:mm:ss”
	DATE	日期	‘2016-03-29’
复杂类型	ARRAY	有序的的同类型的集合	array(1,2)
	MAP	key-value,key必须为原始类型，value可以任意类型	map(‘a’,1,’b’,2)
	STRUCT	字段集合,类型可以不同	struct(‘1’,1,1.0), named_stract(‘col1’,’1’,’col2’,1,’clo3’,1.0)
	UNION	在有限取值范围内的一个值	create_union(1,’a’,63)

示例:

create table if not exists datatype1(
id1 tinyint,
id2 smallint,
id3 int,
id4 bigint,
slary float,
comm double,
isok boolean,
content binary,
dt timestamp
)
row format delimited 
fields terminated by '\t'
;233	12	342523	455345345	30000	60000	nihao	helloworld	2017-06-02
126	13	342526	455345346	80000	100000	true	helloworld1	2017-06-02 11:41:30-timestamp 如果是年月日时分秒的格式，必须是写全，才能映射成功。load data local inpath './data/datatype.txt' into table datatype1;

扩展） 数据类型转换

自动转换(隐式转换)

- 在做运算时，小范围类型都可以自动转为大范围类型做运算

7.2 复杂数据类型之array

7.2.1 定义格式如下：

create table tableName(
......
colName array<基本类型>
......
)说明：下标从0开始，越界不报错，以null代替

7.2.2 案例准备：

zhangsan	78,89,92,96
lisi	67,75,83,94
王五	23,12create table if not exists arr1(
name string,
scores array<String>
)
row format delimited 
fields terminated by '\t'
;drop table arr2;
create table if not exists arr2(
name string,
scores array<String>
)
row format delimited 
fields terminated by '\t'
collection items terminated by ','
;load data local inpath './data/arr1.txt' into table arr1;
load data local inpath './data/arr1.txt' into table arr2;

7.2.3 查询语句：

select * from arr1;
select name,scores[1] from arr2 where size(scores) > 3;--统计arr2中的每个人的总成绩
select scores[0]+scores[1]+nvl(scores[2],0)+nvl(scores[3],0) from arr2;

7.2.4 想要一种效果：也就是将数组类型的数据元素展开，换句话说，就是列转行

zhangsan	78,89,92,96
lisi	67,75,83,94
王五	23,12
将上述效果转成下面的效果，更方便统计每个人的总成绩。
zhangsan        78
zhangsan        89
zhangsan        92
zhangsan        96
lisi	67
lisi	75
lisi	83
lisi	94
王五	23
王五	12

7.3 展开函数的使用

7.3.1 简介

- explode：展开函数(UDTF函数中的一种),作用是：接受一个数据行，然后返回产生多个数据行  
- lateral view:虚拟表。会将UDTF函数生成的结果放到一个虚拟表中，然后这个虚拟表会和输入行进行join来达到数据聚合的目的

7.3.2 上案例：

- select explode(score) score from arr2;- select name,cj from arr2  lateral view explode(scores) mytable as cj;- 统计每个学生的总成绩：
select name,sum(cj) as totalscore from arr2 lateral view explode(score) mytable as cj 
group by name;

7.3.3 需求：向array字段中动态加载数据，不直接load加载，而是insert。

7.3.3.1 准备数据

create table arr_temp
as
select name,cj from arr2 lateral view explode(scores) score as cj;

7.3.3.2 借助collect_set函数，列转行函数，有去重效果。collect_list函数没有去重小郭

drop table arr3;
create table if not exists arr3(
name string,
scores array<string>
)
row format delimited 
fields terminated by ' '
collection items terminated by ','
;将数据写成array格式：
insert into arr3
select name,collect_set(cj) from arr_temp group by name;查询每个人的最后一科的成绩
select name,score[size(score)-1] lastsubject from arr3;

7.4. 复杂数据类型之map

7.4.1 定义格式如下：

create table tableName(
.......
colName map<T,T>
......
)

7.4.2 案例准备：

zhangsan	chinese:90,math:87,english:63,nature:76
lisi	chinese:60,math:30,english:78,nature:0
wangwu	chinese:89,math:25create table if not exists map1(
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 './data/map1.txt' into table map1;

7.4.3 查询语句：

#查询数学大于35分的学生的英语和自然成绩：
select 
m.name,
m.score['english'] ,
m.score['nature']
from map1 m
where m.score['math'] > 35
;#查看每个人的前两科的成绩总和
select 
m.name,
m.score['chinese']+m.score['math']
from map1 m;

7.4.4 展开查询

- 展开效果
zhangsan	chinese		90
zhangsan	math	87
zhangsan	english 	63
zhangsan	nature		76- explode展开数据：
select explode(score) as (m_object,m_score) from map1;- 使用lateral view explode 结合查询：
select name,m_object,m_score from map1 lateral view explode(score) score as 
m_object,m_score;- 统计每个人的总成绩
select name,sum(m_score)
from map1 lateral view explode(score) score as 
m_object,m_score
group by name;

7.4.5 将数据动态写入map字段中

将下面的数据格式
zhangsan        chinese 90
zhangsan        math    87
zhangsan        english 63
zhangsan        nature  76
lisi    chinese 60
lisi    math    30
lisi    english 78
lisi    nature  0
wangwu  chinese 89
wangwu  math    25
wangwu  english 81
wangwu  nature  9
转成：
zhangsan chinese:90,math:87,english:63,nature:76
lisi chinese:60,math:30,english:78,nature:0
wangwu chinese:89,math:25,english:81,nature:9

7.4.5.1 准备数据

create table map_temp
as
select name,m_subject,m_score from map1 lateral view explode(score) t1 as m_subject,m_score;

7.4.5.2 开始写：

第一步：将科目和成绩组合在一起，concat
select name,concat(m_subject,':',m_score) as score from map_temp;第二步: 将所有属于同一个人的数据组合在一起
select name,collect_set(concat(m_subject,":",m_score)) 
from map_temp
group by name
;第三步:将数组变成一个字符串concat_ws
select name,concat_ws(",",collect_set(concat(m_subject,":",m_score)))
from map_temp
group by name
;第四步:将字符串转成map 使用函数str_to_map(text, delimiter1, delimiter2)
text：是字符串
delimiter1：多个键值对之间的分隔符
delimiter2：key和value之间的分隔符select
name,
str_to_map(concat_ws(",",collect_set(concat(m_subject,":",m_score))),',',':')
from map_temp
group by name
;第五步：存储准备的表中
create table map2 as
select
name,
str_to_map(concat_ws(",",collect_set(concat(m_subject,":",m_score))),',',':') score
from map_temp
group by name
;

7.5. 复杂数据类型 struct

7.5.1 简介

struct类型，类似于java编程语言中对象实例的模板，即类的结构体。如地址类型的结构体:

public class Address{String provinces;String city;String street;.......
}

使用struct类型来定义一个字段的类型，语法格式为:

create table tableName(
........
colName struct<subName1:Type,subName2:Type,........>
........
)

调用语法：

colName.subName

7.5.2 案例演示：

1）数据准备

zhangsan	90,87,63,76
lisi	60,30,78,0
wangwu	89,25,81,9create table if not exists struct1(
name string,
score struct<chinese:int,math:int,english:int,natrue:int>
)
row format delimited 
fields terminated by '\t'
collection items terminated by ',';导入数据：
load data local inpath './data/arr1.txt' into table struct1;

**2）需求：**查询数学大于35分的学生的英语和语文成绩：

select name,
score.english,
score.chinese
from str2
where score.math > 35
;

7.5 综合案例演示

1）数据准备

-- 主管信息表如下：
manager(uid uname belong tax addr)-- 数据如下：
1	xdd	ll,lw,lg,lc,lz	wx:600,gongjijin:1200,shebao:450	北京,西城,中南海
2	lkq	lw,lg,lc,lz,lq	wx:1000,gongjijin:600,shebao:320	河北,石家庄,中山路
3	zs	lw,lg,lc	    wx:2000,gongjijin:300,shebao:10	    江西,南昌,八一大道-- 建表语句：
create table manager_t(
uid int,
uname string,
belong array<string>,
tax map<string,int>,
addr struct<province:string,city:string,street:string>
)
row format delimited
fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':'
;-- 加载数据
load data local inpath './data/manager_t.txt' into table manager_t;

**2）查询需求：**下属个数大于4个，公积金小于1200，省份在河北的数据

select * from manager_t where size(belong)>4 and tax['gongjijin']<1200 and addr.province='河北';

**扩展说明）**嵌套数据类型

举例说明：map<string,array<map<string,struct>>>
嵌套类型：所有元素分隔符都要自己去定义默认分隔符：^A	对应输入方式:ctrl+V ctrl+A