Hive函数

最新推荐文章于 2024-09-26 22:15:20 发布

灰太狼！！

最新推荐文章于 2024-09-26 22:15:20 发布

阅读量1.2k

点赞数 24

分类专栏： Hive 文章标签： hive hadoop 数据仓库

本文链接：https://blog.csdn.net/weixin_52134189/article/details/137651199

版权

Hive 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

一、hive语句的执行顺序

1.from

2.join on 或 lateral view explode(扁平化) tbl as 扁平化的列名

3.where

4.group by

5.聚合函数如Sum() avg() count(1)等

6.having 在此开始可以使用select中的别名

7.select 若包含over（）开窗函数，此时select中的内容作为窗口函数的输入，窗口中所选的数据范围也是在group by，having之后，并不是针对where后的数据进行开窗，这点要注意。需要注意开窗函数的执行顺序及时间点。

8.distinct

9.order by

10.limit（建议：今后在大数据环境中，一张表的数据量肯定十分庞大的，养成加limit的习惯）

二、hql中的注意事项

1.where 条件里不支持不等式子查询，hive1.x版本是不支持的，hive3.x版本是支持的

2.hive中大小写不敏感（列名无所谓大小写）

3.explain可以查看SQL执行计划（查看更加详细的执行计划，explain后面加上extended）

hive函数官方文档地址：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

三、Hive内置函数

函数用法查看：

-- 1.查看系统自带函数
show functions;
-- 2.显示自带的函数的用法
desc function xxxx;
-- 3.详细显示自带的函数的用法
desc function extended upper;

内置函数分类：

关系操作符：包括 = 、 <> 、 <= 、>=等

算数操作符：包括 + 、 - 、 *、／等

逻辑操作符：包括AND 、 && 、 OR 、 || 等

复杂类型构造函数：包括map、struct、create_union等

复杂类型操作符：包括A[n]、Map[key]、S.x

数学操作符：包括ln(double a)、sqrt(double a)等

集合操作符：包括size(Array)、sort_array(Array)等

类型转换函数： binary(string|binary)、cast(expr as )

日期函数：包括from_unixtime(bigint unixtime[, string format])、unix_timestamp()等

条件函数：包括if(boolean testCondition, T valueTrue, T valueFalseOrNull)等

字符串函数：包括acat(string|binary A, string|binary B…)等

其他：xpath、get_json_objectscii(string str)、con

三种函数种类：

（1）UDF 进一出一

（2）UDAF 进多出一

（3）UDTF 进一出多

-- UDF 进一出一

-- UDAF 进多出一
-- collect_set()和collect_list() 都是对多列转成一行，区别就是list里面可重复而set里面是去重的
-- concat_ws(':',collect_set(列名)) ':' 表示你合并后用什么分隔，collect_set(列名)表示要合并表中的那一列数据
select 字段名,concat_ws(':',collect_set(列名)) as 别名 from 表名 group by id;

-- UDTF 进一出多
-- explode 可以将一组数组的数据变成一列表（扁平化）
select explode(split(列名,"数据的分隔符")) from 表名;
-- lateral view 表生成函数，可以将explode的数据生成一个列表
select id,name,列名 from 表1,lateral view explode(split(表1.列名,"数据的分隔符"))新列名 as 别列名;

1.条件函数

1.1 if

使用场景:数据清洗的过程中使用，有些构建表的过程也是需要的（转换数据信息）

语法：if(表达式,如果表达式成立的返回值,如果表达式不成立的返回值) （类似与java的三目运算符）

举例：

select if(3>1,'yes','no'); --yes

select score,if(score>120,'优秀',if(score>100,'良好',if(score>90,'及格','不及格'))) as pingfen from sc;

1.2 coalesce

从左往右依次匹配直到非空为止

select coalesce(null,'1','2'); --1
select coalesce('1',null,'2'); --1

1.3 case when

转换数据信息，与if类似

select score
,case when score>90 then '优秀'
when score>80 then '良好'
when score>=60 then '及格'
else '不及格'
end as pingfen
from sc;

2.日期函数

2.1 unix_timestamp()

查询当前时间（时间戳形式）

select unix_timestamp() -- 1712842504

2.2 from_unixtime

将时间戳转化为自定义格式

select from_unixtime(1712815703,'YYYY年MM月dd日 HH时mm分ss秒');

-- 2024年04月11日 06时08分23秒

// '2021年01月14日' -> '2021-01-14'
select from_unixtime(unix_timestamp('2022年06月06日','yyyy年MM月dd日'),'yyyy-MM-dd');

// "04哥哥2024弟弟11妹妹" -> "2021/04/16"
select from_unixtime(unix_timestamp("04哥哥2024弟弟11妹妹","MM哥哥yyyy弟弟dd妹妹"),"yyyy年MM月dd日");

3.字符串函数

3.1 concat

concat('123','456'); //123456
select concat('123','null'); //123null
concat('123','456',null); // NULL

3.2 concat_ws

select concat_ws('#','a','b','c'); //a#b#c
select concat_ws('#','a','b','c',NULL); //a#b#c 可以指定分隔符，并且会自动忽略NULL
select concat_ws("|",cast(id as string),name,cast(age as string),gender,clazz) from students limit 10;

3.3 substring

select substring("abcdefg",1); // abcdefg HQL中涉及到位置的时候是从1开始计数

3.4 split

select split("abcde,fgh",","); // ["abcde","fgh"]
select split("a,b,c,d,e,f",",")[2]; // c 数组的下标依旧是从0开始

select explode(split("abcde,fgh",",")); // abcde
// fgh

3.5 get_json_object

解析json格式的数据

select get_json_object('{"name":"zhangsan","age":18,"score":[{"course_name":"math","score":100},{"course_name":"english","score":60}]}',"$.score[0].score"); // 60

{
   "name": "zhangsan",
   "age": 18,
   "score": [{
       "course_name": "math",
       "score": 100
   }, {
       "course_name": "english",
       "score": 60
   }]
}

// 60

四、 Hive窗口函数

1.概述：

（1）普通的聚合函数每组(Group by)只返回一个值，而开窗函数则可为窗口中的每行都返回一个值。

（2）简单理解，就是对查询的结果多出一列，这一列可以是聚合值，也可以是排序值。

（3）开窗函数一般就是说的是over()函数，其窗口是由一个 OVER 子句定义的多行记录

（4）开窗函数一般分为两类,聚合开窗函数和排序开窗函数。

2.语法格式：

-- 聚合格式
select sum(字段名) over([partition by 字段名] [ order by 字段名]) as 别名,
max(字段名) over() as 别名
from 表名;

-- 排序窗口格式
select rank() over([partition by 字段名] [ order by 字段名]) as 别名 from 表名;

注意：

over()函数中的分区、排序、指定窗口范围可组合使用也可以不指定，根据不同的需求结合使用
over()函数中如果不指定分区，窗口大小是针对查询产生的所有数据，如果指定了分区，窗口大小是针对每个分区的数据

3. 聚合开窗函数

sum(求和)

min(最小)

max(最大)

avg(平均值)

count(计数)

lag(获取当前行上一行的数据）

案例：

数据：

-- 创建表
create table t_fraction(
name string,
subject string,
score int)
row format delimited fields terminated by ","
lines terminated by '\n';

-- 测试数据 fraction.txt
孙悟空,语文,10
孙悟空,数学,73
孙悟空,英语,15
猪八戒,语文,10
猪八戒,数学,73
猪八戒,英语,11
沙悟净,语文,22
沙悟净,数学,70
沙悟净,英语,31
唐玄奘,语文,21
唐玄奘,数学,81
唐玄奘,英语,23

-- 上传数据
load data local inpath 'linux路径/fraction.txt' into table t_fraction;

数据查询：

select name,subject,score,
sum(score) over() as sum1,
sum(score) over(partition by subject) as sum2,
sum(score) over(partition by subject order by score) as sum3,

-- 由起点到当前行的窗口聚合，和sum3一样
sum(score) over(partition by subject order by score rows between unbounded preceding and current row) as sum4,

-- 当前行和前面一行的窗口聚合
sum(score) over(partition by subject order by score rows between 1 preceding and current row) as sum5,

-- 当前行的前面一行到后面一行的窗口聚合前一行+当前行+后一行
sum(score) over(partition by subject order by score rows between 1 preceding and 1 following) as sum6,

-- 当前行与后一行之和
sum(score) over(partition by subject order by score rows between current row and 1 following) as sum6,

-- 当前和后面所有的行
sum(score) over(partition by subject order by score rows between current row and unbounded following) as sum7
from t_fraction;

小总结：

rows：行
unbounded preceding：起点
unbounded following：终点
n preceding：前 n 行
n following：后 n 行
current row：当前行

查询结果表：

注意：rows必须跟在Order by 子句之后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量

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

CURRENT ROW：当前行

n PRECEDING：往前n行数据

n FOLLOWING：往后n行数据

UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点

LAG(col,n,default_val)：往前第n行数据，col是列名，n是往上的行数，当第n行为null的时候取default_val

LEAD(col,n, default_val)：往后第n行数据，col是列名，n是往下的行数，当第n行为null的时候取default_val

NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。

cume_dist（），计算某个窗口或分区中某个值的累积分布。假定升序排序，则使用以下公式确定累积分布：

小于等于当前值x的行数 / 窗口或partition分区内的总行数。其中，x 等于 order by 子句中指定的列的当前行中的值。

4.排序开窗函数

RANK() 排序相同时序号会重复，总数不会变
DENSE_RANK() 排序相同时序号会重复，总数会减少
ROW_NUMBER() 排序相同时序号不会重复
PERCENT_RANK() 计算给定行的百分比排名。可以用来计算超过了百分之多少的人(当前行的rank值-1)/(分组内的总行数-1)

举例：

select name,subject,score,
rank() over(partition by subject order by score desc) rp,
dense_rank() over(partition by subject order by score desc) drp,
row_number() over(partition by subject order by score desc) rnp,
percent_rank() over(partition by subject order by score) as percent_rank
from t_fraction;

查询结果：