Hive自定义函数UserDefineFunction
主要分为三大类：
UDF：一对一
UDTF：一对多
UDAF：多对一
注意：1、区分的条件只要看前后输入输出之后的行数的变化
2、UDF可以连续嵌套调用，类似于if语句
UDF：一进一出
定义UDF函数要注意下面几点:
- 继承
org.apache.hadoop.hive.ql.exec.UDF- 重写
evaluate()，这个方法不是由接口定义的,因为它可接受的参数的个数,数据类型都是不确定的。Hive会检查UDF,看能否找到和函数调用相匹配的evaluate()方法
- 创建maven项目，并加入依赖
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>1.2.1</version>
</dependency>
打包的时候可能会出现错误
Could not transfer artifact org.pentaho:pentaho-aggdesigner-algorithm:pom:5.1.5-jhyde
解决方案：
在pom文件中修改hive-exec的配置
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<exclusions>
<!--排除pentaho-aggdesigner-algorithm依赖，不将它引入-->
<exclusion>
<groupId>org.pentaho</groupId>
<artifactId>pentaho-aggdesigner-algorithm</artifactId>
</exclusion>
</exclusions>
</dependency>
-
编写代码，继承org.apache.hadoop.hive.ql.exec.UDF，实现evaluate方法，在evaluate方法中实现自己的逻辑
-
打成jar包并上传至Linux虚拟机
-
在hive shell中，使用
add jar 路径将jar包作为资源添加到hive环境中
add jar /usr/local/soft/hive_test/hive-1.0.jar;
- 使用jar包资源注册一个临时函数，fxxx1是你的函数名，'MyUDF'是主类名
create temporary function fxxx1 as 'MyUDF';
- 使用函数名处理数据
select fxx1(name) as fxx_name from students limit 10;
#施笑槐$
#吕金鹏$
#单乐蕊$
#葛德曜$
#宣谷芹$
#边昂雄$
#尚孤风$
#符半双$
#沈德昌$
#羿彦昌$
函数加载方式
命令加载
这种加载只对本session有效
# 1、将项目打包上传服务器：将打好的jar包传到linux系统中。(不要打依赖)
# 进入到hive客户端,执行下面命令
hive> add jar /usr/local/soft/bigdata17/data/xiaohu/hadoop-mapreduce-1.0-SNAPSHOT.jar
# 2、创建一个临时函数名,要跟上面hive在同一个session里面：
hive> create temporary function toUP as 'com.shujia.testHiveFun.udf.FirstUDF';
3、检查函数是否创建成功
show functions;
4. 测试功能
select toUp('abcdef');
5. 删除函数
drop temporary function if exists toUp;
创建永久函数
将jar上传HDFS：
hadoop fs -put hadoop-mapreduce-1.0-SNAPSHOT.jar /jar/
在hive命令行中创建永久函数：
create function myUp as 'com.testHiveFun.udf.FirstUDF' using jar 'hdfs:/jar/hadoop-1.0.jar';
create function bfy_fun as 'com.udfdemo.HiveTest' using jar 'hdfs:/hive_test/jar/hive-udf.jar';
UDTF：一进多出
UDTF是一对多的输入输出,实现UDTF需要完成下面步骤
M1001#xiaohu#S324231212,lkd#M1002#S2543412432,S21312312412#M1003#bfy
1001 xihu 324231212
1002 lkd 2543412432
1003 bfy 21312312412
继承org.apache.hadoop.hive.ql.udf.generic.GenericUDTF，
重写initlizer（）、process（）、close()。
执行流程如下:UDTF首先会调用initialize方法，此方法返回UDTF的返回行的信息（返回个数，类型）。
初始化完成后，会调用process方法,真正的处理过程在process函数中，在process中，每一次forward()调用产生一行；如果产生多列可以将多个列的值放在一个数组中，然后将该数组传入到forward()函数。
最后close()方法调用，对需要清理的方法进行清理。
"key1:value1,key2:value2,key3:value3"
key1 value1
key2 value2
key3 value3
- SQL
create temporary function my_udtf as 'com.testHiveFun.udtf.HiveUDTF';
select my_udtf("key1:value1,key2:value2,key3:value3");
字段：id,col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12 共13列
数据：
a,1,2,3,4,5,6,7,8,9,10,11,12
b,11,12,13,14,15,16,17,18,19,20,21,22
c,21,22,23,24,25,26,27,28,29,30,31,32
转成3列：id,hours,value
例如：
a,1,2,3,4,5,6,7,8,9,10,11,12
a,0时,1
a,2时,2
a,4时,3
a,6时,4
......
create table udtfData(
id string
,col1 string
,col2 string
,col3 string
,col4 string
,col5 string
,col6 string
,col7 string
,col8 string
,col9 string
,col10 string
,col11 string
,col12 string
)row format delimited fields terminated by ',';
添加jar资源:
add jar /usr/local/soft/HiveUDF2-1.0.jar;
注册udtf函数：
create temporary function my_udtf as 'MyUDTF';
SQL:
select id,hours,value from udtfData lateral view my_udtf(col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12) t as hours,value ;
UDAF：多进一出
Hive Shell
第一种：
hive -e "select * from test1.students limit 10"
第二种：
hive -f hql文件路径
将HQL写在一个文件里，再使用 -f 参数指定该文件
连续登陆问题
在电商、物流和银行可能经常会遇到这样的需求：统计用户连续交易的总额、连续登陆天数、连续登陆开始和结束时间、间隔天数等
数据：
注意：每个用户每天可能会有多条记录
id datestr amount
1,2019-02-08,6214.23
1,2019-02-08,6247.32
1,2019-02-09,85.63
1,2019-02-09,967.36
1,2019-02-10,85.69
1,2019-02-12,769.85
1,2019-02-13,943.86
1,2019-02-14,538.42
1,2019-02-15,369.76
1,2019-02-16,369.76
1,2019-02-18,795.15
1,2019-02-19,715.65
1,2019-02-21,537.71
2,2019-02-08,6214.23
2,2019-02-08,6247.32
2,2019-02-09,85.63
2,2019-02-09,967.36
2,2019-02-10,85.69
2,2019-02-12,769.85
2,2019-02-13,943.86
2,2019-02-14,943.18
2,2019-02-15,369.76
2,2019-02-18,795.15
2,2019-02-19,715.65
2,2019-02-21,537.71
3,2019-02-08,6214.23
3,2019-02-08,6247.32
3,2019-02-09,85.63
3,2019-02-09,967.36
3,2019-02-10,85.69
3,2019-02-12,769.85
3,2019-02-13,943.86
3,2019-02-14,276.81
3,2019-02-15,369.76
3,2019-02-16,369.76
3,2019-02-18,795.15
3,2019-02-19,715.65
3,2019-02-21,537.71
建表语句
create table deal_tb(
id string
,datestr string
,amount string
)row format delimited fields terminated by ',';
create table group_score(
a string
,b string
,c int
)row format delimited fields terminated by ' ';
select
a,
max(case when b="A" then c end) col_A,
max(case when b="B" then c end) col_B
from t1
group by a;
计算逻辑
-
先按用户和日期分组求和，使每个用户每天只有一条数据
-
根据用户ID分组按日期排序，将日期和分组序号相减得到连续登陆的开始日期，如果开始日期相同说明连续登陆
-
datediff(string end_date,string start_date); 等于0说明连续登录
-
统计用户连续交易的总额、连续登陆天数、连续登陆开始和结束时间、间隔天数
-
结果
1 2019-02-07 13600.23 3 2019-02-08 2019-02-10 NULL
1 2019-02-08 2991.650 5 2019-02-12 2019-02-16 1
1 2019-02-09 1510.8 2 2019-02-18 2019-02-19 1
1 2019-02-10 537.71 1 2019-02-21 2019-02-21 1
2 2019-02-07 13600.23 3 2019-02-08 2019-02-10 NULL
2 2019-02-08 3026.649 4 2019-02-12 2019-02-15 1
2 2019-02-10 1510.8 2 2019-02-18 2019-02-19 2
2 2019-02-11 537.71 1 2019-02-21 2019-02-21 1
3 2019-02-07 13600.23 3 2019-02-08 2019-02-10 NULL
3 2019-02-08 2730.04 5 2019-02-12 2019-02-16 1
3 2019-02-09 1510.8 2 2019-02-18 2019-02-19 1
3 2019-02-10 537.71 1 2019-02-21 2019-02-21 1
作业
-- 首先将所有用户的申请时间和通过时间另外记录在一列中
select id
,max(case when state='申请' then app_date end) apply_time
,max(case when state='通过' then app_date end) agree_time
from customer_application
group by id;
-- 计算出申请时间和通过时间差，同时计算出相差的天数
select id,apply_time,agree_time
,unix_timestamp(agree_time,"yyyy-MM-dd HH:mm:ss")-unix_timestmp(apply_time,'yyyy-MM-dd HH:mm:ss') as time_diff
,datediff(substr(agree_time,1,10),substr(apply_time,1,10)) as day_diff
from(
select id
,max(case when state='申请' then app_date end) apply_time
,max(case when state='通过' then app_date end) agree_time
from customer_application
group by id)t1)tt1;