一、数据类型
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;