Hive（四）自定义函数 | 窗口函数

用户自定义函数

按输入行数与输出行数的对应关系：

• UDF：普通函数，一进一出
• UDAF：聚合函数，多进一出
• UDTF：表生成函数，一进多出，如输入一行array类型，返回3行string类型

编程步骤

• 继承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 AS class_name;

• ）在hive的命令行窗口删除函数

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

举例：自定义UDF函数

• 需求：自定义一个UDF函数实现计算给定字符串的长度

hive(default)> select my_len("abcd");
4

• 创建一个Maven工程Hive
• 导入依赖

<dependencies>
  <dependency>
    <groupId>org.apache.hive</groupId>
    <artifactId>hive-exec</artifactId>
    <version>3.1.2</version>
  </dependency>
</dependencies>

• 创建一个类

package com.user.hive;
import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentLengthException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import 
org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectIn
spectorFactory;
/**
* 自定义 UDF 函数，需要继承 GenericUDF 类
* 需求: 计算指定字符串的长度
*/
public class MyStringLength extends GenericUDF {
 /**
 *
 * @param arguments 输入参数类型的鉴别器对象
 * @return 返回值类型的鉴别器对象
 * @throws UDFArgumentException
 */
  @Override
  public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException {
    // 判断输入参数的个数
    if(arguments.length !=1){
      throw new UDFArgumentLengthException("Input Args Length Error!!!");
   }
    // 判断输入参数的类型
    if(!arguments[0].getCategory().equals(ObjectInspector.Category.PRIMITIVE)){
      throw new UDFArgumentTypeException(0,"Input Args Type Error!!!");
   }
    //函数本身返回值为 int，需要返回 int 类型的鉴别器对象
    return PrimitiveObjectInspectorFactory.javaIntObjectInspector;
 }
 /**
 * 函数的逻辑处理
 * @param arguments 输入的参数
 * @return 返回值
 * @throws HiveException
 */
  @Override
  public Object evaluate(DeferredObject[] arguments) throws HiveException {
    if(arguments[0].get() == null){
      return 0;
   }
    return arguments[0].get().toString().length();
  }
  @Override
  public String getDisplayString(String[] children) {
    return "";
  }
}

• 打成jar包上传到服务器/opt/module/data/myudf.jar
• 将jar包添加到hive的classpath

hive (default)> add jar /opt/module/data/myudf.jar;

• 创建临时函数与开发好的java class关联

hive (default)> create temporary function my_len as "com.atguigu.hive.MyStringLength";

• 在HQL中使用自定义的函数

hive (default)> select ename,my_len(ename) ename_len from emp;

窗口函数

相关函数说明

• 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返回此行所属的组的编号

案例 business表

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月份购买过的顾客及总人数

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;