Hive之函数的使用

最新推荐文章于 2024-05-24 11:54:36 发布

piaow_

最新推荐文章于 2024-05-24 11:54:36 发布

阅读量888

点赞数 2

分类专栏：大数据笔记文章标签： hive 大数据 hadoop

本文链接：https://blog.csdn.net/weixin_52323239/article/details/127204408

版权

笔记同时被 2 个专栏收录

7 篇文章 0 订阅

订阅专栏

大数据

6 篇文章 0 订阅

订阅专栏

一、系统内置函数

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

常用内置函数

1.空字段赋值

1）函数说明
NVL：给值为 NULL 的数据赋值，它的格式是 NVL( value，default_value)。它的功能是如
果 value 为 NULL，则 NVL 函数返回default_value 的值，否则返回 value 的值，如果两个参数都为 NULL ，则返回 NULL。
2）数据准备：采用员工表
3）查询：如果员工的comm为NULL，则用-1代替；如果员工的comm为NULL，则用领导id代替

hive (default)> select comm,nvl(comm, -1) from emp;
hive (default)> select comm, nvl(comm,mgr) from emp;

2.CASE WHEN THEN ELSE END

1）数据准备
在这里插入图片描述
2）需求
求出不同部门男女各多少人。结果如下：

3）创建表格文件并导入数据集（load 方法从本地上传到指定表）
4）按需求查询数据

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;

3.行转列

1）相关函数说明
CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字
符串;
CONCAT_WS(separator, str1, str2,…)：它是一个特殊形式的 CONCAT()。第一个参数剩余参
数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将
为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接
的字符串之间;
注意: CONCAT_WS must be “string or array< string>”
COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重
汇总，产生 Array 类型字段。

举例演示：

3.1 CONCAT（string A/col, string B/col…）用法

在这里插入图片描述
几个列之间，必须写清彼此之间的连接符

3.2 CONCAT_WS(separator, str1, str2,…)用法

在这里插入图片描述
对于几个列想用相同的描述符进行连接，使用此函数较方便
注：参数可以是String类型，也可以是String数组

3.3 COLLECT_SET(col)用法

函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生 Array 类型字段（即返回类型），将数据进行合并为数组的函数。
在这里插入图片描述

COLLECT_LIST(col)函数返回不去重的数组

3.4 具体案例

现有name，constellation，bllod_type三列，需要将星座和血型一样的人归类到一起。
结果如下：
在这里插入图片描述

按需求查询数据
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

4.列转行

函数说明
EXPLODE(col)：将 hive 一列中复杂的 Array 或者 Map 结构拆分成多行。
LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：用于和 split, explode 等 UDTF 一起使用，它能够将一列数据拆成多行数据，在此
基础上可以对拆分后的数据进行聚合。

具体案例
现有如下数据：
在这里插入图片描述
需求：将电影分类中的数组数据展开。结果如下

按需求查询数据
SELECT
movie,
category_name
FROM
movie_info
lateral VIEW
explode(split(category,",")) movie_info_tmp AS category_name;

附上解释：explode使得split分隔后的内容进行炸裂，全部转为行。当炸开后的数据比原行数多的时候，需要用到LATERAL VIEW，将原行数与新行数进行合并

5.窗口函数

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 类型

数据集

数据准备：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

需求
（1）查询在 2017 年 4 月份购买过的顾客及总人数
（2）查询顾客的购买明细及每个顾客的月购买总额
（3）上述的场景, 将每个顾客的 cost 按照日期进行累加
（4）查询每个顾客上次的购买时间
（5）查询前 20%时间的订单信息

（1）查询在 2017 年 4 月份购买过的顾客及总人数
 注：count(*)在group by之后执行，统计的是分组之后有多少个name，
	select name,count(*) over () 
	from business
	where substring(orderdate,1,7) = '2017-04'
	group by name;

结果：
在这里插入图片描述

（2）查询顾客的购买明细及每个顾客的月购买总额（重点在每个人的月消费）
select name,orderdate,cost,sum(cost) over(partition by name,month(orderdate)) 
from business;
变式：查询顾客的购买明细及月购买总额（按月进行划分，重点在月）
select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) 
from business;

展示下变式结果：
在这里插入图片描述

（3） 将每个顾客的 cost 按照日期进行累加
select name,orderdate,cost,
sum(cost) over() as sample1,--所有行相加
sum(cost) over(partition by name) as sample2,--按 name 分组，组内数据相加
sum(cost) over(partition by name order by orderdate) as sample3,--按 name
分组，组内数据累加
sum(cost) over(partition by name order by orderdate rows between 
UNBOUNDED PRECEDING and current row ) as sample4 ,--和 sample3 一样,由起点到
当前行的聚合
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; 
注：rows 必须跟在 order by 子句之后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量

（4）查看顾客上次的购买时间，没有上次购买，则默认上次购买时间为这次购买时间
select name,orderdate,cost,
lag(orderdate,1,orderdate) over(partition by name order by orderdate ) 
as time1
from business;

（5）查询前 20%时间的订单信息
select * from (
 select name,orderdate,cost, ntile(5) over(order by orderdate) sorted
 from business
) t
where sorted = 1;
注：前20%不能用limit，因为无法控制20%的数据，需要用ntile（）over（）窗口函数，这样能准确得到前20%的数据

2.RANK() 排序相同时会重复，总数不会变
DENSE_RANK() 排序相同时会重复，总数会减少
ROW_NUMBER() 会根据顺序计算

案例需求：
计算每门学科成绩排名。

按需求查询数据
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) rmp
from score;

结果展示：
在这里插入图片描述

二、自定义函数

当 Hive 提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。

根据用户自定义函数类别分为以下三种：
（1）UDF（User-Defined-Function）
一进一出
（2）UDAF（User-Defined Aggregation Function）
聚集函数，多进一出
类似于：count/max/min
（3）UDTF（User-Defined Table-Generating Functions）
一进多出
如 lateral view explode()

实现方法：idea编译Java代码并实现相应API
（1）继承 Hive 提供的类
org.apache.hadoop.hive.ql.udf.generic.GenericUDF
org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
（2）实现类中的抽象方法