大数据---28. hive的数据类型和特殊数据处理

1.hive支持的类型

hive支持两种数据类型：原始数据类型、复杂数据类型
1.1原始数据类型
原始数据类型包括数据型，布尔型，字符串型，具体如下表：

2.各类型详解

2.1DATE类型
虽然我们上表写出了DATE类型，但是hive是不支持真正的日期类型的，而常用的日期格式转化操作则是通过从具有标准时间格式的字符串中提取，也就是从具有标准时间格式的字符串中提取进行操作；

2.2.整数类型
2.3 hive有四种带符号的整数类型，TINYINT，SMALLINT,INT,BIGINT,他们分别对应java里面的byte,short,int,long，字节长度分别为1字节，2字节，4字节，8字节，在使用整数字面量的时候，默认使用INT，如果需要使用其他，需要加对应的后缀名；

2.4. 小数、Boolean、二进制
2.4.1 hive中的FLOAT、DOUBLE类型对应java中的FLOAT、DOUBLE，32位，64位；
hive中的BOOLEAN类型对应java中的BOOLEAN类型
BINANY用于存储变长的二进制数据

2.5. 文本类型
2.5.1 hive有三种类型用于存储文本
STRING存储变长的文本，对长度没有限制，理论上可以存储大小为2GB，但是存储特别大 的对象时效率可能会收到影响
VARCHAR于STRING类似，但是最大长度为1-65355之间
CHAR则用固定长度来存储数据

2.6. 时间类型
TIMESTAMP类型则存储纳秒级别的时间戳，同时hive提供了一些内置函数用于再TIMESTAMP与Unix时间戳（秒）和字符串之间做转换，如：
date 类型： ‘2022-07-20’这个样子

cast（date as date)
cast(timestamp as date)
cast(string as date)
cast(date as string)

3. 类型转换

3.1 隐式转换
hive支持基本数据类型的转换

任意数据类型都可以转换为更宽的数据类型（通俗讲就是低转高，且不会导致精度丢失）或者文本类型
所有的文本类型都可以隐式的转换为另一种文本类型，也可以转换为DOUBLE或者DECIMAL，转换失败会抛出异常
BOOLEAN不能做任何的类型转换
时间戳和日期可以隐式的转换为文本类型
低字节的基本类型可以转换为高字节的数据类型，例如TINYINT,SMALLINT,INT可以转换为FLOAT，而所有的整数类型INT和FLOAT类型、STRING类型都可以转换为DOUBLE类型

3.2 显示转换（CAST函数）
高字节的类型也可以转换为低字节的数据类型
Hive中CAST（）函数用法

需要注意的地方：
　对于Hive的String类型相当于MySQL数据库的varchar类型，该类型是一个可变的字符串。不过它不能声明其中最多能存储多少个字符，但理论上它可以存储2GB的字符数。
　　　　在Java编程中，如果double类型都无法表示的精度，可以使用java中的BigDecimal类型，它能支持跟高精度的运算温馨提示:

4.复杂数据类型

4.1 ARRAY和MAP
数组的类型声明格式为ARRAY<data_type>,元素的访问可以通过0开始下标，例如array[1]访问第二个元素。
Map通过Map<key_type,data_type>，key只能是基本数据类型，值可以值任意数据类型，元素的访问使用[]，例如：map[“key1”]

4.2 STRUCT
STRUCT则封装一组带有名字的字段，他可以是任意的数据类型，元素的访问可以通过点号，表结构字段名.struct字段名

4.3 UNION
UNION则类似于C语言中的UNION结构，在给定的任何一个时间点，UNION类型可以保存指定数据类型中的任意一种。类型声明语法为UNIONTYPE<data_type,data_type,…>。每个UNION类型的值都通过一个整数来表示其类型，这个整数位声明时的索引，从0开始。例如：
CREATE TABLE union_test(foo UNIONTYPE<int,double,array,strucy<a:int,b:string>>);
foo的一些取值如下：
{0:1}
{1:2.0}
{2:[“three” , “four”]}
{3:[“a”:5,b:“five”]}
{0:9}

其中冒号左边的整数代表数据类型，必须在预先定义的范围类，通过0开始的下标表示。冒号右边是该类型的取值。
下面的这个CRATE语句用到了这4中复杂类型：
CREATE TABLE complex (
c1 ARRAY,
c2 MAP<STRING,INT>,
c3 STRUCT<a:STRING,b:INT,c:DOUBLE>,
c4 UNIONTYPE<STRING,INT>
);
通过下面的SELECT语句查询相应的数据：
SELECT c1[0] , c2[‘b’],c3.c , c4 FROM complex
结果类似：
1 2 1.0 {1:63}

集合 array数据的例子：

示例：array类型的应用
假如有如下数据需要用hive的表去映射：
名字为sting 单个一个； 演员 有很多个就使用array来进行； 时间为日期： date

流浪地球,吴京:吴孟达:李光洁,2019-02-06
三生三世十里桃花,杨幂:迪丽热巴:高伟光,2017-08-20
都挺好,姚晨:倪大伟:郭京飞,2019-03-01

如果主演信息用一个数组来映射比较方便

建立表：
create table t_movie(name string,actors array,times string)
row format delimited fields terminated by ‘,’
collection items terminated by ‘:’;

导入数据：

load data local inpath ‘/root/movie.dat’ into table t_movie;

select *from t_movie;

select name, actors[0] times from t_movie;
select name, actors[1] times from t_movie;

select name,size(actors) from t_movie; 统计里边的演员个数；

select name,actors times from t_movie where times<‘2019-01-01’;

集合map的例子

数据在存储的时候，一个键对应多个值；不如；家庭成员： 张三；李四；王五；赵柳等等；maps: MAP<primitive_type, data_type>
假如有以下数据： map集合
1,zhangsan,father:xiaoming#mother:xiaohuang#brother:xiaoli,28
2,lisi,father:mayun#mother:huangyi#brother:mahuateng,22
3,wangwu,father:wangjianlin#mother:ruhua#sister:jingtian,29
4,mayun,father:mayongzhen#mother:angelababy,26

可以用一个map类型来对上述数据中的家庭成员进行描述
创建表：
create table t_movie(name string,actors array,times string)
row format delimited fields terminated by ‘,’
collection items terminated by ‘:’;

数据导入：
load data local inpath ‘/root/family.txt’ into table t_person;

数据查询：查询所有数据； select *from t_person;

查询map具体的key值中的数据：
select id,name,family_members[‘father’] as father from t_person;

select id,name,family_members[‘mother’] as father from t_person;

取map字段的所有key
select id,name,map_keys(family_members) as relation from t_person;

取map字段的所有value
select id,name,map_values(family_members) as relation from t_person;

select id,name ,map_values(family_members)[0] from t_person;
select id,name ,map_values(family_members)[1] from t_person;
select id,name ,map_values(family_members)[2] from t_person;

综合：查询有brother的用户信息（他的方法也可以使用sql的具体sql语句；）
select id,name,father
from
(select id,name,family_members[‘brother’] as father from t_person) tmp
where father is not null;

struct类型

structs: STRUCT<col_name : data_type, …>
类似和 c语言中的结构体；
假如我们有一个数据中的内容基本相同；就可以使用结构体来存储数据；
user_st structage:int,sex:string,address:string
以下数据就是 id；name；age； address；
1,zhangsan,18:male:beijing
2,lisi,28:female:shanghai
3,wangwu,38:male:henan
4,zhaoliu,30:female:shanxi
其中的用户信息包含：年龄：整数，性别：字符串，地址：字符串
设想用一个字段来描述整个用户信息，可以采用struct

建表：
create table t_person_struct(id int,name string,info structage:int,sex:string,addr:string)
row format delimited fields terminated by ‘,’
collection items terminated by ‘:’;
导入数据：
load data local inpath ‘/root/struct.txt’ into table t_person_struct;
select *from t_person_struct;

select id,name,info.age,info.addr from t_person_struct;

注意
提交hive的时候时间需要同步
yum install -y ntpdate
ntpdate pool.ntp.org

提示：在做小数据量查询测试时，可以让hive将mrjob提交给本地运行器运行，可以在hive会话中设置如下参数：
hive> set hive.exec.mode.local.auto=true;

集合的例子：
假设某表有如下两行，我们用JSON格式来表示其数据结构(注释内容为Hive中的访问格式)。

{
“name”: “曹操”,
“friends”: [“荀彧” , “典韦”] , //列表Array,
“children”: { //键值Map,
“曹昂”: 30,
“曹丕”: 25,
　　　　　“曹彰”:22,
　　　　　“曹冲”:18

}
"address": {  //结构Struct,
    "street": "丞相府" ,
    "city": "许昌" 
}

},
{
“name”: “刘备”,
“friends”: [“诸葛亮” , “庞统”] , //列表Array,
“children”: { //键值Map,
“刘禅”: 25,
“刘永”: 20,
　　　　　“刘理”:15
}
“address”: { //结构Struct,
“street”: “武当山”,
“city”: “成都”
}
},
…

1.创建文件： vim sanguo.txt

可以使用文档的方式写入数据；
提示:
　　MAP，STRUCT和ARRAY里的元素间关系都可以用同一个字符表示，这里用"_"。
　　2.基于上述数据结构，我们在Hive里创建对应的表，并导入数据。

create table sanguo(
name string,
friends array,
children map<string, int>,
address struct<street:string, city:string>
)
row format delimited
fields terminated by ‘,’
collection items terminated by ‘_’
map keys terminated by ‘:’
lines terminated by ‘\n’;

行格式定义(row format delimited)参数含义如下所示:
　　fields terminated by ‘,’
　　　　指定列分隔符。
　　collection items terminated by ‘_’
　　　　指定MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
　　map keys terminated by ‘:’
　　　　指定MAP中的key与value的分隔符
　　lines terminated by ‘\n’;
　　　　指定行分隔符，若不指定，则默认为"\n"。

查看表的结构：

.3.导入文本数据到测试表（中文乱码不管先）

4.加载外部数据；就是把外部的文件加载到hive中
load data local inpath ‘/root/sanguo.txt’ into table sanguo;

这样就是吧数据导入到hive中然后查询出来的结果；但是需要；
注意：
如果这个地方是集群的操作的时候；不行启动集群；不然就导入的时候不可以导入，出错。
Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.MoveTask；

5. .访问三种集合列里的数据，以下分别是ARRAY，MAP，STRUCT的访问方式：
   1.select *from sanguo;
   
   2.select friends[1],children[‘刘禅’],address.city from sanguo where name=“刘备”;
   查询：刘备的第一个朋友；孩子是刘禅；以及他城市；
   
   3.select friends[1],children[‘曹丕’],address.city from sanguo where name=“曹操”;
   

五.类型转换

1.类型转换概述
Hive的原子数据类型(其实就是基础数据类型)是可以进行隐式转换的。

类似于Java的类型转换，例如某表达式使用INT类型，TINYINT会自动转换为INT类型。

但是Hive不会进行反向转化，例如，某表达式使用TINYINT类型，INT不会自动转换为TINYINT类型，它会返回错误，除非使用CAST操作进行强制类型转换。

综上所述，隐式类型转换我们称为自动类型转换，它可以从小的范围转换到大的范围。强制类型转换则是从大的类型范围转换为小的类型范围(可能存在数据丢失的风险)。

2.隐式类型转换规则
隐式类型转换规则如下所示：
(1)任何整数类型都可以隐式地转换为一个范围更广的类型，如TINYINT可以转换成INT，INT可以转换成BIGINT。
(2)所有整数类型、FLOAT和STRING类型都可以隐式地转换成DOUBLE。
(3)TINYINT、SMALLINT、INT都可以转换为FLOAT。
(4)BOOLEAN类型不可以转换为任何其它的类型。

3.强制类型转换(可以使用CAST操作显示进行数据类型转换)

SELECT “100” + 25; // 这里会自动发生类型转换，将字符串类型转换成DOUBLE类型。
SELECT CAST(“100” AS INT) + 25; // 这里我们将字符串类型转换为INT类型。
SELECT CAST(“10AF” AS INT); // 当类型转换失败时会返回NULL。

查询语句

6.1基本查询示例

select * from t_access;
select count(*) from t_access;
select max(age) from t_access;
select min(age) from t_access;
select avg(age) from t_access;

6.2. 条件查询 date

select * from t_access where access_time<‘2017-08-06 15:30:20’
select * from t_access where access_time<‘2017-08-06 16:30:20’ and ip>‘192.168.33.3’;

6.3. 关联查询（左连接；右连接；全连接；子连接）

准备工作： 有两个表；表中有相同的内容点； 然后就可以进行相关的查询；

假如有a.txt文件
a,10
b,15
c,20
d,25
假如有b.txt文件
a,xx
b,yy
d,zz
e,ww

创建表：
create table a(name string,age int)
row format delimited fields terminated by ‘,’;

create table b(name string,infos string)
row format delimited fields terminated by ‘,’;

导入数据：1.可以通过文件的方式：2.使用insert进行插入；


b表的化可以使用导入的方式来进行：
load data local inpath ‘/root/b.txt’ into table b;

两张表之间的查询；
1.两个表的乘积：
select a.name,a.age,b.name,b.infos from a,b;

2. 右连接查询：
   select *from a right join b on a.name=b.name;
   
   3.左连接
   select *from a left join b on a.name=b.name;
   
   4.交集公共数据；两张表中公共的内容
   select *from a join b on a.name=b.name;
   select *from a inner join b on a.name=b.name;
   
3. full join 全连接；两张表相互；
4. select *from a full join b on a.name=b.name;
   

6.4 分组查询的 group by

它的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对若干个小区域进行数据处理。其实就是去掉小组中的重复数据；进行统计；
可以按照这个的address进行分组：去掉重复的；并进行统计；


这样写的化就会出错：因为分组需要 在函数之前的每个都要写进去；也就是from前面的内容都需要些到group by中；
select name,address, count() from t_partition;
select name from t_partition group by name;
select address from t_partition group by address;


select name,address, count() from t_partition group by name,address;