Hive——Hive的函数和自定义函数

最新推荐文章于 2024-08-05 18:12:45 发布

静惘

最新推荐文章于 2024-08-05 18:12:45 发布

阅读量66

点赞数

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

本文链接：https://blog.csdn.net/xdx_dili/article/details/132688613

版权

Hive 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

1、系统内置函数

查看系统自带的函数
```
show functions;
```
显示自带的函数的用法
```
desc function upper; 
```
详细显示自带的函数的用法
```
desc function extended upper;
```

2、常用内置函数

2.1、空字段赋值

函数说明

NVL： 给值为 NULL 的数据赋值，它的格式是 NVL( value，default_value)。它的功能是如果 value 为 NULL，则 NVL 函数返回 default_value 的值，否则返回 value 的值，如果两个参数都为 NULL ，则返回 NULL。
数据准备：采用员工表
查询：如果员工的 comm 为 NULL，则用-1 代替
```
select comm,nvl(comm, -1) from emp; 
```
查询：如果员工的 comm 为 NULL，则用领导id代替
```
select comm, nvl(comm,mgr) from emp;
```

2.2、CASE WHEN THEN ELSE END

数据准备
需求

创建本地 emp_sex.txt，导入数据

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 "\t";

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

按需求查询数据

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;

2.3、行转列

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

把星座和血型一样的人归类到一起。

创建本地 constellation.txt，导入数据

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 "\t";

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

按需求查询数据

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

2.4、列转行

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

将电影分类中的数组数据展开。

创建本地 movie.txt，导入数据

vi movie_info.txt
《疑犯追踪》  悬疑,动作,科幻,剧情
《Lie to me》 悬疑,警匪,动作,心理,剧情
《战狼 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

按需求查询数据

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

2.5、窗口函数

相关函数说明
- 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

需求
- 查询在2017 年 4 月份购买过的顾客及总人数
- 查询顾客的购买明细及月购买总额
- 上述的场景, 将每个顾客的 cost 按照日期进行累加
- 查询每个顾客上次的购买时间
- 查询前 20%时间的订单信息
创建本地 business.txt，导入数据
```
vi business.txt 
```

创建 Hive 表并导入数据

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

按需求查询数据

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

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

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

select name,orderdate,cost,sum(cost) 
over(partition by month(orderdate)) from business;

将每个顾客的 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 子句之后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量。

查看顾客上次的购买时间

select name,orderdate,cost,
lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) 
as time1, lag(orderdate,2) over (partition by name order by orderdate) as time2
from business;

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

select * from (
select name,orderdate,cost, ntile(5) over(order by orderdate) 
sorted from business) t
where sorted = 1;

2.6、Rank

函数说明
- RANK()： 排序相同时会重复，总数不会变；
- DENSE_RANK()： 排序相同时会重复，总数会减少；
- ROW_NUMBER()： 会根据顺序计算。
数据准备
需求

计算每门学科成绩排名。
创建本地 score.txt，导入数据
```
vi score.txt
```

创建 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;

按需求查询数据

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;

3、自定义函数

Hive 自带了一些函数，比如：max/min 等，但是数量有限，自己可以通过自定义 UDF 来方便的扩展。
当 Hive 提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。
根据用户自定义函数类别分为以下三种：
- UDF（User-Defined-Function）一进一出
- UDAF（User-Defined Aggregation Function）聚集函数，多进一出类似于：count/max/min
- UDTF（User-Defined Table-Generating Functions）一进多出如：lateral view explode()
官方文档地址

编程步骤：

继承 Hive 提供的类：

org.apache.hadoop.hive.ql.udf.generic.GenericUDF
org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;

实现类中的抽象方法
在 Hive 的命令行窗口创建函数添加 jar
```
add jar linux_jar_path
```

创建 function

create [temporary]function [dbname.]function_name ASclass_name;

在 Hive 的命令行窗口删除函数

drop [temporary] function [if exists] [dbname.]function_name;

静惘

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Hive——Hive的函数和自定义函数

Hive——Hive的函数和自定义函数
复制链接

扫一扫

专栏目录