Hive 数据类型、操作符、聚合函数

一、数据类型

1. 基本类型

数据类型	大小	范围	示例
Tinyint	1byte	-128~127	100Y
Smallint	2byte	-32,768~32,767	100S
Int/Integer	4byte	-2,147,483,648~2,147,483,647	100
Bigint	8byte	-9,223,372,036,854,775,808~9,223,372,036,854,775,807	100L
Float	4byte	单精度浮点数	3.1415926
Double	8byte	双精度浮点数	3.1415926
Decimal	-	高精度浮点数	Decimal(9,8)
Boolean	-	布尔型，True/False	true
Binary	-	二进制类型	-

（1）数字类型

• 整数类型：
  -2,147,483,648~2,147,483,647之间的整数类型默认是Int型，除非指定了格式100Y、100S、100L、会自动转换为Tinyint、Smallint、Bigint
• 浮点数类型：

  浮点数默认会当作Double型；

  Hive中的Decimal基于Java中的BigDecimal，Decimal不指定精度时默认为Decimal(10,0)；

（2）字符串类型

• String（后面常用string）
  类型可以用单引号（'）或双引号（''）定义，这个类型是定义字符串的常用类型。
• Varchar
  Varchar类型由长度定义，范围为1~65355，如果存入的字符串长度超过了定义长度，超出部分会被截断。尾部的空格也会作为字符串的一部分，影响字符串的比较。
• Char

  char是固定长度的，最大长度255，且尾部的空格不影响字符串的比较。

  三种类型对尾部的区别，参考如下例子，每个字段都插入同样的字符且在尾部有不同的空格。

--建表
create table char_a(
c1 char(4),
c2 char(5),
str1 string,
str2 string,
var1 varchar(4),
var2 varchar(6));
--插入数据
insert into char_a values('ccc ','ccc  ','ccc ','ccc    ','ccc ','ccc   ');
--查询
select c1=c2,str1=str2,var1=var2 from char_a;
true false false

--示例
create table str_tab(a varchar(5), b string);
insert into str_tab values ('aaaaaaa', 'aaaaaaaa');
--hive是粗粒度的，虽然定义的a字符长度为5，insert了一个字符长度为7的字符串，依然可以插入，但是读的时候会进行类型的校验，因此在都的时候会把剩下两个截断，不显示。string类型无影响。

（3）日期与时间戳 

Timestamp

Timestamp表示UTC时间，可以是以秒为单位的整数；带精度的浮点数，最大精确到小数点后9位，纳秒级；java.sql.Timestamp格式的字符串YYYY-MM-DD hh:mm:ss.fffffffff

Date

Hive中的Date只支持YYYY-MM-DD格式的日期，其余写法都是错误的，如需带上时分秒，请使用timestamp。

--显示年月日时分秒
create table time_tab(t timestamp);
insert into time_tab values('2023-12-10 7:55:00');
--只显示年月日
create table date_tab(t date);
insert into date_tab values('2023-12-10 7:55:00');

日期相关函数：

获取当前timestamp：

current_timestamp()    --返回值：timestamp

获取当前日期：

current_date()    --返回值：date

格式化Timestamp/Date/String 为字符串：

date_format(date/timestamp/string ts, string fmt)    --返回值：字符串

--示例：
--当前日期格式化
select date_format(current_date(),'yyyy-MM-dd HH:mm:ss');
--当前时间格式化
select date_format(current_timestamp(),'yyyy|MM|dd HH-mm-ss');
--字符串格式化
select date_format('2023-12-10 9:39:59','yyyy-MM-dd');

Unix时间戳：

【当前时间或时间字符串】转【Unix时间戳】：

把一个日期类型的字段转换为Unix时间戳，应该是Bigint类型。从1970-01-01 00:00:00 UTC到指定时间的秒数，例如：1530752400。

--获取指定字符串的Unix时间戳
select unix_timestamp('2023-12-10 12:30:22');
--获取当前timestamp的Unix时间戳
select unix_timestamp(current_timestamp());

【Unix时间戳】转【时间字符串】：

from_unixtime(bigint unixtime[,string format])
--unixtime: 从1970-01-01 00:00:00 UTC到指定时间的秒数
--format: 目标转换格式
--返回值: string
--说明: 转化为Unix时间戳(从1970-01-01 00:00:00 UTC到指定时间的秒数)到当前时区的时间格式

示例:
--转换成yyyy-MM-dd HH:mm:ss
select from_unixtime(1530755469);
--转换成指定格式的字符串
select from_unixtime(1530755469, "yyyy-MM-dd");
--两个日期相差的天数
select datediff('2023-10-10','2023-12-10');

2. 复杂类型

Struct

Struct类似于java的类变量使用，Hive中定义的struct类型也可以使用点来访问。从文件加载数据时，文件里的数据分隔符要和建表指定的一致。

struct(val1,val2,val3,...)，只有字段值；name_struct(name1,val1,name2,val2,...)，带有字段名和字段值；一般用struct都是带有字段名和字段值

Array

相当于java的数组，通过arr[下标] 获取元素数据。Array表示一组相同数据类型的集合，下标从零开始，可以用下标访问，如：arr[0]。

Map

相当于java的Map，Map是一组键值对的组合，可以通过key访问value，键值之间同样要在创建表时指定分隔符。如：map_col['name']

Hive除了支持Struct、Array、Map这些原生集合类型，还支持集合的组合，不支持集合里再组合多个集合。

示例：创建带有复合结构的表

create table complex(
id int,
struct_col struct<name:string,country:string>,
array_col array<string>,
map_col map<string,string>,
union_col map<string,array<string>>
)
row format delimited fields terminated by ','
collection items terminated by '-'
map keys terminated by ':';
--其中：
--这个子句表明Hive使用字符','作为列分隔符
row format delimited fields terminated by ','
--这个子句表明Hive使用字符'-'作为集合元素间分隔符(一个字符各个item的分隔符)
collection items terminated by '-'
--这个子句表明Hive使用字符作为Map的键和值之间分隔符
map keys terminated by ':'

--查询导入数据
insert into table complex select 100, named_struct('name','yae','country','cn') as struct_col,array('99','21','33') as array_col,map('english','aaaa') as array_col,map('english','aaaa') as map_col,map('english',array('99','21','33')) as union_col;
hadoop fs -get /hive/warehouse/yae.db/complex/000000_0
cat 000000_0
100,yae-cn,99-21-33,english:aaaa,english:99^V^D21^V^D33
--只能在第一层字段中做约束，如果字段中嵌入了复杂结构，是约束不了的
--练习拿到字段中的数据
select struct.name from complex;
select array_col[0] from complex;
select map_col['english'] from complex;
select union_col['english'][2] from complex;

Hive读取json格式数据:

JSON是一个包含对象或数组的字符串。数据为 键 / 值 (name/value) 对；数据由逗号(,)分隔；大括号保存对象（Object）；方括号保存数组（Array）。

例如：

{"code":"100"}
# 对象由花括号括起来，逗号分隔的成员构成，成员是字符串键和上文所述的值由逗号分隔的键值对组成；
{"code":20,"type":"mysql"}
# 数组是由方括号括起来的一组值构成:
{"datesource":[
    {"code":"20","type":"mysql"},
    {"code":"20","type":"mysql"},
    {"code":"20","type":"mysql"}
]}

处理json串，Hive中提供了获取json数据的方法get_json_object

--获取json串中level这个key对应的值，$代表这个json串.key
select get_json_object('{"level":"2","time":"1650973942596","type":"0"}','$.level') as level;
2
--获取json串中所有的key对应的值/读取多个字段(通过一个函数获取多个值/调用多次get_json_object函数)
--time是关键字不能取为字段名
select 
get_json_object('{"level:"2","time":"1650973942596","type":"0"}','$.level') as level,
get_json_object('{"level:"2","time":"1650973942596","type":"0"}','$.time') as times,
get_json_object('{"level:"2","time":"1650973942596","type":"0"}','$.type') as type;

为解决get_json_object一次解析不了整个json文件的问题，就有了json_tuple函数，一条便能处理一条json数据，基础语法为：

json_tuple(json_string, k1, k2 ... )

解析json字符串json_string，可以指定多个json数据中的key，返回对应的value，如果输入的json字符串无效，那么返回Null。

select json_tuple('{"level":"2","time":"123456486351","type":"0"}','level','time','type');

Hive解析JSON数组：

[{"website":"baidu.com","name":"百度"},{"website":"google.com","name":"谷歌"}]
需求：解析JSON数组中website和name的数据
1. 第一步将[]去掉，需要用到regexp_replace函数

将字符串A中的符合Java正则表达式B的部分替换为C
regexp_replace(string A, string B, string C)

Hive中没有可以解析json数组的函数，就需要一步一步拆解，拿到数组中的json串，再对json串进行单独处理
\\代表转义，匹配[或者]替换成空字符串
select regexp_replace('[{"website":"baidu.com","name":"百度"},
{"website":"google.com","name":"谷歌"}]','\\[|\\]','');

返回的也是一个字符串
{"website":"baidu.com","name":"百度"},{"website":"google.com","name":"谷歌"}
拿到json串，不能按照,分隔，会切分成数组。需要将中间的,替换成能切分的符号比如|

2. 再次使用regexp_replace函数
select regexp_replace(regexp_replace('[{"website":"baidu.com","name":"百度"},
{"website":"google.com","name":"谷歌"}]','\\[|\\]',''), '\\}\\,\\{', '\\}\\|\\{')

返回
{"website":"baidu.com","name":"百度"}|{"website":"google.com","name":"谷歌"}

3. 按照|进行切分后会转换成数组
select split(regexp_replace(regexp_replace('[{"website":"baidu.com","name":"百度"},
{"website":"google.com","name":"谷歌"}]','\\[|\\]',''), '\\}\\,\\{', '\\}\\|\\{'), '\\|');
["{\"website\":\"baidu.com\",\"name\":\"百度\"}","{\"website\":\"google.com\",\"name\":\"谷歌\"}"]

4. 想拿到数组中的每一个元素，调用get_json_object函数进行处理
需要把数组中的每一个元素当成一行一行数据进行打印，即explode函数：
explode(array or map)
explode()函数接收一个array或map类型的数据作为输入，然后将array或map里面的元素按照每行的形式输出，即将Hive一列中复杂的array或map结构拆分成多行显示，也被称为列转行函数。
select json_tuple(json,'website','name') from (select explode(split(regexp_replace(regexp_replace('[{"website":"baidu.com","name":"百度"},
{"website":"google.com","name":"谷歌"}]','\\[|\\]',''), '\\}\\,\\{', '\\}\\|\\{'), '\\|')) as json) t;

二、操作符

1. 关系操作符

以下操作符比较操作数(operands)从而产生true/false值

运算符	操作数	描述
A=B	所有基本类型	如果表达A等于表达B，结果True，否则False。
A!=B	所有基本类型	如果A不等于表达式B表达返回True，否则False。
A<B	所有基本类型	True，如果表达式小于表达式B，否则False。
A<=B	所有基本类型	True，如果表达式小于或等于表达式B，否则False。
A>B	所有基本类型	True，如果表达式A大于表达式B，否则False。
A>=B	所有基本类型	True，如果表达式A大于或等于表达式B，否则False
A is null	所有类型	True，如果表达式的计算结果为null，否则False
A is not null	所有类型	False，如果表达式A的计算结果为Null，否则True。
A like B	字符串	True，如果字符串模式A匹配到B，否则False。关系型数据库中的like功能。
A rlike B	字符串	B是否在A里面，在是True，否则是False(B可以是Java正则表达式)
A regexp B	字符串	等同于rlike

是否包含aa
select * from complex where map_col['english'] like '%aa%';
\s表示空白字符，是否有空白字符，\S表示非空白字符
select * from complex where map_col['english'] rlike '\\s';
select * from complex where map_col['english'] rlike '\\S';

2. 算数运算符

Hive中可用的算术运算符： 

运算符	操作	描述
A + B	所有数字类型	A加B的结果
A - B	所有数字类型	A减B的结果
A * B	所有数字类型	A乘以B的结果
A / B	所有数字类型	A除以B的结果
A % B	所有数字类型	A除以B产生的余数
A & B	所有数字类型	A和B的按位与结果
A | B	所有数字类型	A和B的按位或结果
A ^ B	所有数字类型	A和B的按位异或结果
~A	所有数字类型	A按位非的结果

select 5/2;
2.5
select 9%3;
0
select 12%5;
2
select ~7;
-8
select ~8;
-9

3. 逻辑运算符

运算符是逻辑表达式，所有这些返回True或False

运算符	操作	描述
A and B	boolean	True，如果A和B都是True，返回True，否则False
A or B	boolean	True，如果A或B或两者都是True，返回True，否则False
not A	boolean	True，如果A是False，返回True，否则False

select 3>5 and 5<8;
false
select 3<5 and 5<8;
true
select 3>5 or 5<8;
true
select not 3>5;
true

三、聚合函数

将多行聚合成一行

返回类型	函数	描述
Bigint	count(*), count(expr)	count(*) -返回检索行的总数
Double	sum(col), sum(distinct col)	返回该组或该组中的列的不同值的分组和所有元素的总和
Double	avg(col), avg(distinct col)	返回上述组或该组中的列的不同值的元素的平均值
Double	min(col)	返回该组中的列的最小值
Double	max(col)	返回该组中的列的最大值

count(1) count(*) count(字段)

count(1) count(*) 是包含null值，count(字段)不包含null值，count(1)稍微比count(*)快一点

准备数据文件data.txt
创建表
create table `user_install_status_limit`(
    `aid` string,
    `pkgname` string,
    `uptime` bigint,
    `type` int,
    `country` string,
    `gpcategory` string
) row format delimited fields terminated by '\t';
将导出的100条data数据放到表的HDFS目录
hadoop fs -put data.txt /hive/warehouse/yae.db/user_install_status_limit
--其中
aid: 每个用户的标识
pkgname: 用户安装某个手机应用的包名
uptime: 更新时间
type: 系统预装应用还是用户自己装的应用
country: 用户所在国家
gpcategory: 应用的类型，游戏类、社交类、....
--统计不同的用户   统计用户id以8d304开头的记录数
select count(distinct aid), sum(if(aid like '8d304%',1,0)) from user_install_status_limit;
--统计按照国家分组后的最大值
select max(a.n) from 
(select country,count(1) n from user_install_status_limit group by country) a;

--统计按照国家分组后的最大值和国家码
--select * from 数据集1 t1 join 数据集2 t2 on t1.xx=t2.xx;
--select * from 数据集2 t2 where t2.num in 数据集 t;
--select * from 数据集 group by country order by num desc limit 1;
--1. 通过join的方式
select t2.* from
(select max(num) as maxnum from
(select country,count(*) as num from user_install_status_limit group by country) t) t1
join
(select country,count(*) as num from user_install_status_limit group by country) t2
on t1.maxnum=t2.num;
--2. 通过子查询的方式
select t2.* from
(select country,count(*) as num from user_install_status_limit group by country) t2
where t2.num in (select max(num) as maxnum from
(select country,count(*) as num from user_install_status_limit group by country) t);
--3. 通过order by 排序limit 1，hive2.x版本order by后count(*)需要起别名，高版本不需要
select country,count(*) as num from user_install_status_limit group by country order by num desc limit 1;