Hive常用函数

Hive常用函数

1、常用日期函数

unix_timestamp ():：返回当前或指定日期的时间戳

select unix_timestamp();	# 返回当前时间戳
select unix_timestamp('2021-05-10','yy-MM-dd');	# 返回指定日期的时间戳

from_unixtime()：将日期转为时间戳

select from_unixtime(1620604800);	# 默认有时分秒
#结果：2021-05-10 00:00:00
select from_unixtime(1620604800,'yyyy-MM-dd');	# 指定格式
# 结果：2021-05-10

current_date()：当前日期

hive (test)> select current_date();
OK
_c0
2021-05-09

current_timestamp()：当前日期加时间

hive (test)> select current_timestamp();
OK
_c0
2021-05-09 11:07:47.852

to_date()：抽取日期部分

# 日期格式必须为xxxx-xx-xx
hive (test)> select to_date('2021-05-10 12:12:12');
OK
_c0
2021-05-10

year()：获取年

hive (test)> select year('2021-05-10 12:12:12');
OK
_c0
2021

month()：获取月

hive (test)> select month('2021-05-10 12:12:12');
OK
_c0
5

day()：获取日

hive (test)> select day('2021-05-10 12:12:12');
OK
_c0
10

hour()：获取时

hive (test)> select hour('2021-05-10 12:12:12');
OK
_c0
12

minute()：获取分

hive (test)> select minute('2021-05-10 12:12:12');
OK
_c0
12

second()：获取秒

hive (test)> select second('2021-05-10 12:12:12');
OK
_c0
12

weekofyear()：当前时间是一年中的第几周

hive (test)> select weekofyear('2021-05-10 12:12:12');
OK
_c0
19

dayofmonth()：当前时间是一个月中的第几天

hive (test)> select dayofmonth('2021-05-10 12:12:12');
OK
_c0
10
hive (test)> select dayofmonth('2021-05-35 12:12:12');	# 自动识别到下个月的第几天
OK
_c0
4
Time taken: 0.599 seconds, Fetched: 1 row(s)
hive (test)> select dayofmonth('2021-06-35 12:12:12');
OK
_c0
5

months_between()：两个日期间的月份

# 前一个减后一个
hive (test)> select months_between('2021-06-35','2021-04-13');
OK
_c0
2.74193548

add_months()：日期加减月

hive (test)> select add_months('2020-09-29',5);
OK
_c0
2021-02-28
Time taken: 0.13 seconds, Fetched: 1 row(s)
hive (test)> select add_months('2020-09-29',-5);
OK
_c0
2020-04-29

datediff()：两个日期相差的天数

# 前一个减后一个
hive (test)> select datediff('2021-06-35','2021-04-13');
OK
_c0
83

date_add()：日期加天数

hive (test)> select date_add('2021-06-35',7);
OK
_c0
2021-07-12
Time taken: 0.131 seconds, Fetched: 1 row(s)
hive (test)> select date_add('2021-06-07',7);
OK
_c0
2021-06-14

date_sub()：日期减天数

hive (test)> select date_sub('2021-06-07',7);
OK
_c0
2021-05-31

last_day()：日期的当月的最后一天

hive (test)> select last_day('2021-06-09');
OK
_c0
2021-06-30

date_format()：格式化日期

hive (test)> select date_format('2021-06-09 14:25:46','yyyy-MM');
OK
_c0
2021-06
Time taken: 0.533 seconds, Fetched: 1 row(s)
hive (test)> select date_format('2021-06-09 14:25:46','yyyy MM');
OK
_c0
2021 06

2、常用取整函数

round()：四舍五入取整

hive (test)> select round(3.49);
OK
_c0
3
Time taken: 0.12 seconds, Fetched: 1 row(s)
hive (test)> select round(3.69);
OK
_c0
4

ceil()：向上取整

hive (test)> select ceil(3.69);
OK
_c0
4
Time taken: 0.132 seconds, Fetched: 1 row(s)
hive (test)> select ceil(3.01);
OK
_c0
4

floor()：向下取整

hive (test)> select floor(3.01);
OK
_c0
3
Time taken: 0.131 seconds, Fetched: 1 row(s)
hive (test)> select floor(3.9991);
OK
_c0
3

3、常用字符串操作函数

upper()：转大写

hive (test)> select upper('asDAwweaASDa');
OK
_c0
ASDAWWEAASDA

lower()：转小写

hive (test)> select lower('asDAwweaASDa');
OK
_c0
asdawweaasda

length()：长度

hive (test)> select length('asDAwweaASDa');
OK
_c0
12

tirm()：前后空格

hive (test)> select trim(' wqrw werrew ');
OK
_c0
wqrw werrew

lpad()：向左补齐，到指定长度

hive (test)> select lpad('iang',5,'j');
OK
_c0
jiang
Time taken: 0.571 seconds, Fetched: 1 row(s)
hive (test)> select lpad('iang',3,'j');
OK
_c0
ian

rpad()：向右补齐，到指定长度

hive (test)> select rpad('iang',5,'j');
OK
_c0
iangj
Time taken: 0.571 seconds, Fetched: 1 row(s)
hive (test)> select rpad('iang',3,'j');
OK
_c0
ian

regexp_replace()：使用正则表达式匹配目标字符串，匹配成功后替换

hive (test)> select regexp_replace('2021/06/09','/','-');
OK
_c0
2021-06-09
Time taken: 0.543 seconds, Fetched: 1 row(s)
hive (test)> select regexp_replace('2021/06/09','/',' ');
OK
_c0
2021 06 09

4、集合操作

test表数据

hive (test)> select * from test;
OK
test.name	test.friends	test.children	test.address
songsong	["bingbing","lili"]	{"xiao song":18,"xiaoxiao song":19}   {"street":"hui long guan","city":"beijing"}
Time taken: 0.309 seconds, Fetched: 1 row(s)

size()：集合中元素个数

hive (test)> select size(friends) from test;
OK
_c0
2

map_keys()：返回map中的key

hive (test)> select map_keys(children) from test;
OK
_c0
["xiao song","xiaoxiao song"]

mpa_values()：返回map中的value

hive (test)> select map_values(children) from test;
OK
_c0
[18,19]

array_contains()：判断array中是否包含某个元素

hive (test)> select array_contains(friends,'lili') from test;
OK
_c0
true
Time taken: 0.14 seconds, Fetched: 1 row(s)
hive (test)> select array_contains(friends,'lili3') from test;
OK
_c0
false

sort_array()：将array中的元素排序

# 数据
12,23,23,34     what,are,this
34,45,34,23,12  who,am,i,are
# 建表
hive (test)> create table sort_array_test(info1 array<int>,info2 array<string>)
           > ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
           > COLLECTION ITEMS TERMINATED BY ',';
# 查询数据
hive (test)> select * from sort_array_test;
OK
sort_array_test.info1	sort_array_test.info2
[12,23,23,34]	["what","are","this"]
[34,45,34,23,12]	["who","am","i","are"]
# 查询结果
hive (test)> select sort_array(info1) from sort_array_test;
OK
_c0
[12,23,23,34]
[12,23,34,34,45]

5、CASE WHEN THEN ELSE END

准备数据

[bigdata@hadoop102 datas]$ vi emp_sex.txt
悟空,A,男
大海,A,男
宋宋,B,男
凤姐,A,女
婷姐,B,女
婷婷,B,女

创建 hive 表并导入数据

create table emp_sex( name string,
dept_id string, sex string)
row format delimited fields terminated by ",";

load data local inpath '/opt/module/hive/data/emp_sex.txt' into table 

求出不同部门男女各多少人。结果如下：

dept_Id	男	女
A	2	1
B	1	2

按需求查询数据

select dept_id,
sum(case sex when '男' then 1 else 0 end) male_count,
sum(case sex when '女' then 1 else 0 end) female_count from emp_sex
group by dept_id;

6、行转列

CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字 符串;

CONCAT_WS(separator, str1, str2,…)：它是一个特殊形式的 CONCAT()。第一个参数剩余参 数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将 为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接 的字符串之间;

注意: CONCAT_WS must be "string or array

COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重 汇总，产生 Array 类型字段。

准备数据

[bigdata@hadoop102 datas]$ vim person_info.txt
孙悟空,白羊座,A
大海,射手座,A
宋宋,白羊座,B 
猪八戒,白羊座,A
凤姐,射手座,A
苍老师,白羊座,B

创建hive表并导入数据

create table person_info( name string, constellation string, blood_type string)
row format delimited fields terminated by ",";

load data local inpath "/opt/module/hive/data/person_info.txt" into table person_info

需求

把星座和血型一样的人归类到一起。结果如下：

# 将星座和血型拼接在一起
select
    concat(constellation,',',blood_type) con_blood,
    name
from person_info;

# 聚合相同星座血型人的姓名	
select
    con_blood,
    collect_set(name) name_arr
from
(select
    concat(constellation,',',blood_type) con_blood,
    name
from person_info)t1
group by con_blood;
# 结果：
射手座,A	["大海","凤姐"]
白羊座,A	["孙悟空","猪八戒"]
白羊座,B	["苍老师"]
白羊座,B 	["宋宋"]
############################################################

select
    con_blood,
    concat_ws('|',name_arr)
from
(select
    con_blood,
    collect_set(name) name_arr
from
(select
    concat(constellation,',',blood_type) con_blood,
    name
from person_info)t1
group by con_blood)t2;
# 结果：
射手座,A	大海|凤姐
白羊座,A	孙悟空|猪八戒
白羊座,B	苍老师
白羊座,B 	宋宋

# 合并
select
    con_blood,
    concat_ws('|',collect_set(name))
from
(select
    concat(constellation,',',blood_type) con_blood,
    name
from person_info)t1
group by con_blood;
# 结果：
射手座,A	大海|凤姐
白羊座,A	孙悟空|猪八戒
白羊座,B	苍老师
白羊座,B 	宋宋

按需求查询数据

SELECT
t1.c_b,
CONCAT_WS("|",collect_set(t1.name))
FROM (
SELECT
NAME,
CONCAT_WS(',',constellation,blood_type) c_b FROM person_info
)t1
GROUP BY t1.c_b;
# 结果：
射手座,A	大海|凤姐
白羊座,A	孙悟空|猪八戒
白羊座,B	苍老师
白羊座,B 	宋宋

列转行

EXPLODE(col)：将 hive 一列中复杂的 Array 或者 Map 结构拆分成多行

LATERAL VIEW

用法：LATERAL VIEW udtf(expression)(炸裂字段) tableA(侧写表名) AS columnAlias(炸裂出的字段)

解释：用于和 split, explode 等 UDTF 一起使用，它能够将一列数据拆成多行数据，在此 基础上可以对拆分后的数据进行聚合。

准备数据:

movie_info.movie	movie_info.category
《疑犯追踪》	悬疑,动作,科幻,剧情
《Lie to me》	悬疑,警匪,动作,心理,剧情
《战狼 2》	战争,动作,灾难

需求：

将电影分类中的数组数据展开。结果如下：

《疑犯追踪》	悬疑
《疑犯追踪》	动作
《疑犯追踪》	科幻
《疑犯追踪》	剧情
《Lie to me》	悬疑
《Lie to me》	警匪
《Lie to me》	动作
《Lie to me》	心理
《Lie to me》	剧情
《战狼 2》	战争
《战狼 2》	动作
《战狼 2》	灾难

创建hive表并导入数据

create table movie_info( movie string, category string)
row format delimited fields terminated by "\t";

load data local inpath "/opt/module/data/movie.txt" into table

按需求查询数据

# 对category字段进行按“，”切割转化为一列
select explode(split(category,',')) from movie_info;

# 结果SQL
SELECT
movie, category_name
FROM movie_info
lateral VIEW explode(split(category,",")) movie_info_tmp AS category_name;

窗口函数（开窗函数）

1、相关函数说明

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化

CURRENT ROW：当前行

n PRECEDING：往前 n 行数据 n FOLLOWING：往后 n 行数据 UNBOUNDED：起点，

UNBOUNDED PRECEDING 表示从前面的起点，

UNBOUNDED FOLLOWING 表示到后面的终点 LAG(col,n,default_val)：往前第 n 行数据 LEAD(col,n, default_val)：往后第 n 行数据

NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从 1 开始，对 于每一行，NTILE 返回此行所属的组的编号。注意：n 必须为 int 类型。

2、数据准备：name，orderdate，cost

jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94

3、创建hive表和导入数据

create table business( name string,orderdata string,cost int)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
load data local inpath "/opt/module/data/business.txt" into table business;

4、需求

（1）查询在 2017 年 4 月份购买过的顾客及总人数

select name,count(*) over() 
from business 
where substring(orderdata,0,7)='2017-04'
group by name;

（2）查询顾客的购买明细及月购买总额

# 查询顾客的购买明细及顾客购买总额
select name,orderdata,cost,sum(cost) over(partition by name) from business;
# 查询顾客的购买明细及顾客月购买总额
select name,orderdata,cost,sum(cost) over(partition by name,month(orderdata))
from business;
# 查询顾客的购买明细及月购买总额
select name,orderdata,cost,sum(cost) over(partition by month(orderdata)) from business;

（3）上述的场景, 将每个顾客的 cost 按照日期进行累加

select name,orderdata,cost,sum(cost) over(partition by name order by orderdata) 
from business;

select name,orderdate,cost,
--所有行相加
sum(cost) over() as sample1,
--按 name 分组，组内数据相加 sum(cost) 
sum(cost) over(partition by name) as sample2,
--按 name 分组，组内数据累加
sum(cost) over(partition by name order by orderdate) as sample3,
--和 sample3 一样,由起点到 当前行的聚合
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as sample4 ,
--当前行和前面一行做聚合
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5,
--当前行和前边一行及后面一行
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,
--当前行及后面所有行
sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 
from business;

（4）查询每个顾客上次的购买时间

# 查询每个顾客上次的购买时间
select name,orderdata,lag(orderdata,1) over(partition by name order by orderdata ) from business;
# 查询每个顾客下次的购买时间，lead(orderdata,1,orderdata(可选项，这里指自己))
select name,orderdata,lead(orderdata,1,orderdata) over(partition by name order by orderdata ) from business;

（5）查询前 20%时间的订单信息

select name,orderdata,cost from (select name,orderdata,cost,ntile(5) over(order by orderdata) groupid from business)t1 where groupid = 1;

Rank函数

Rank() 排序相同时会重复，总数不会变

Dense_rank() 排序相同时会重复，总数会减少

Row_number() 会根据顺序计算

准备数据：

name	subject	score
孙悟空	语文	87
孙悟空	数学	95
孙悟空	英语	68
大海	语文	94
大海	数学	56
大海	英语	84
宋宋	语文	64
宋宋	数学	86
宋宋	英语	84
婷婷	语文	65
婷婷	数学	85
婷婷	英语	78

创建hive表和导入数据

create table score( name string, subject string, score int)
row format delimited fields terminated by "\t";
load data local inpath '/opt/module/data/score.txt' into table score;

1、按照分数全局排列（计算成绩排名）

# rank()总数不变
select name,subject,score,rank() over(order by score) from score; 
# dense_rank()总数改变(有排名相同时)
select name,subject,score,dense_rank() over(order by score) from score;
# row_number() 总数不变(按照行号排列)
select name,subject,score,row_number() over(order by score) from score;

结果（rank）：

name	subject	score	rank_window_0
大海	数学	56	1
宋宋	语文	64	2
婷婷	语文	65	3
孙悟空	英语	68	4
婷婷	英语	78	5
宋宋	英语	84	6
大海	英语	84	6
婷婷	数学	85	8
宋宋	数学	86	9
孙悟空	语文	87	10
大海	语文	94	11
孙悟空	数学	95	12
Time taken: 1.987 seconds, Fetched: 12 row(s) # 结果数不变

2、按学科对成绩进行排列（计算每门学科成绩排名）

select name,subject,score,rank() over(partition by subject order by score) from score;

结果：

name	subject	score	rank_window_0
大海	数学	56	1
婷婷	数学	85	2
宋宋	数学	86	3
孙悟空	数学	95	4
孙悟空	英语	68	1
婷婷	英语	78	2
宋宋	英语	84	3
大海	英语	84	3
宋宋	语文	64	1
婷婷	语文	65	2
孙悟空	语文	87	3
大海	语文	94	4
Time taken: 1.693 seconds, Fetched: 12 row(s)

3、获取每学科成绩的前三名信息

select name,subject,score,rk 
from
(select *,rank() over(partition by subject order by score desc) rk from score)t1
where rk<=3;

结果：

name	subject	score	rk
孙悟空	数学	95	1
宋宋	数学	86	2
婷婷	数学	85	3
大海	英语	84	1
宋宋	英语	84	1
婷婷	英语	78	3
大海	语文	94	1
孙悟空	语文	87	2
婷婷	语文	65	3
Time taken: 1.494 seconds, Fetched: 9 row(s)