Hive常用函数与自定义函数
目录
一、系统内置函数
1)查看系统自带的函数
hive> show functions;
2)显示自带的函数的用法
hive> desc function upper;
3)详细显示自带的函数的用法
hive> desc function extended upper;
二、常用内置函数
2.1 空字段赋值
函数说明:
NVL:给值为NULL的数据赋值,它的格式是NVL( value,default_value)。它的功能是如果value为NULL,则NVL函数返回default_value的值,否则返回value的值,如果两个参数都为NULL ,则返回NULL。
2.2 CASE WHEN判断
函数说明:
用法:CASE 字段 WHEN 判断条件 THEN 进行的操作 ELSE 不满足条件进行的操作 END
对某一字段进行按条件操作,类似于java中的if…else…
2.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"
COLLECT_SET(col):函数只接受基本数据类型,它的主要作用是将某字段的值进行去重汇总,产生array类型字段。
COLLECT_LIST(col):函数只接受基本数据类型,它的主要作用是将某字段的值进行不去重汇总,产生array类型字段。
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 列转行
1)函数说明
Split(str, separator):将字符串按照后面的分隔符切割,转换成字符array。
EXPLODE(col):将hive一列中复杂的array或者map结构拆分成多行。
LATERAL VIEW
用法:LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释:lateral view用于和split, explode等UDTF一起使用,它能够将一行数据拆成多行数据,在此基础上可以对拆分后的数据进行聚合。
lateral view首先为原始表的每行调用UDTF,UTDF会把一行拆分成一或者多行,lateral view再把结果组合,产生一个支持别名表的虚拟表。
lateral view用于维持查询表和炸开后虚拟表的联系
#按需求查询数据
SELECT movie,category_name
FROM movie_info
lateral VIEW
explode(split(category,",")) movie_info_tmp AS category_name ;
2.5 窗口函数(开窗函数)
1)相关函数说明
OVER():指定分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变而变化。
ROWS BETWEEN 行开始位置 AND 行结束位置
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行数据
FIRST_VALUE (col,true/false):当前窗口下的第一个值,第二个参数为true,跳过空值
LAST_VALUE (col,true/false):当前窗口下的最后一个值,第二个参数为true,跳过空值
NTILE(n):把有序窗口的行分发到指定数据的组中,各个组有编号,编号从1开始,对于每一行,NTILE返回此行所属的组的编号。注意:n必须为int类型。
#按需求查询数据
#(1)查询在2017年4月份购买过的顾客及总人数
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)) name_month_cost
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,'1970-01-01') over(PARTITION by name order by orderdate) prev_time,
lead(orderdate,1,'1970-01-01') over(PARTITION by name order by orderdate) next_time
from business;
#(5)查询顾客每个月第一次的购买时间 和 每个月的最后一次购买时间
select
name,
orderdate,
cost,
FIRST_VALUE(orderdate) over(partition by name,month(orderdate) order by orderdate rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) first_time,
LAST_VALUE(orderdate) over(partition by name,month(orderdate) order by orderdate rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) last_time
from business;
#(6)查询前20%时间的订单信息
select * from (
select name,orderdate,cost, ntile(5) over(order by orderdate) sorted
from business
) t
where sorted = 1;
2.6 排序函数
函数说明
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 自带了一些函数,比如:max/min等,但是数量有限,自己可以通过自定义UDF来方便的扩展。
- 当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() - 官方文档地址
https://cwiki.apache.org/confluence/display/Hive/HivePlugins - 编程步骤:
(1)继承Hive提供的类
org.apache.hadoop.hive.ql.udf.generic.GenericUDF
org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
(2)实现类中的抽象方法
(3)在hive的命令行窗口创建函数
#添加jar
add jar linux_jar_path
#创建function
create [temporary] function [dbname.]function_name AS class_name;
(4)在hive的命令行窗口删除函数
drop [temporary] function [if exists] [dbname.]function_name;
四、自定义UDF函数
4.1 编写udf函数
0)需求:
自定义一个UDF实现计算给定字符串的长度,例如:
hive(default)> select my_len("abcd");
4
1)创建一个Maven工程Hive
2)导入依赖
<dependencies>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>3.1.2</version>
</dependency>
</dependencies>
3)创建一个类
package com.hadoop.hive.udf;
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.PrimitiveObjectInspectorFactory;
/**
* 自定义UDF函数,需要继承GenericUDF类
* 需求: 计算指定字符串的长度
*/
public class MyLength extends GenericUDF {
/**
* 初始化方法,里面要做三件事
* 1.约束函数传入参数的个数
* 2.约束函数传入参数的类型
* 3.约束函数返回值的类型
* @param arguments 函数传入参数的类型
* @return
* @throws UDFArgumentException
*/
@Override
public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException {
//1.约束函数传入参数的个数
if (arguments.length != 1) {
throw new UDFArgumentLengthException("Input Args Num Error,You can only input one arg...");
}
//2.约束函数传入参数的类型
if (!arguments[0].getCategory().equals(ObjectInspector.Category.PRIMITIVE)) {
throw new UDFArgumentTypeException(0,"Input Args Type Error,You can only input PRIMITIVE Type...");
}
//3.约束函数返回值的类型
return PrimitiveObjectInspectorFactory.javaIntObjectInspector;
}
/**
* 函数逻辑处理方法
* @param arguments 函数传入参数的值
* @return
* @throws HiveException
*/
@Override
public Object evaluate(DeferredObject[] arguments) throws HiveException {
//获取函数传入参数的值
Object o = arguments[0].get();
//将object转换成字符串
int length = o.toString().length();
//因为在上面的初始化方法里面已经对函数返回值类型做了约束,必须返回一个int类型
//所以我们要在这个地方直接返回length
return length;
}
/**
* 返回显示字符串方法,这个方法不用管,直接返回一个空字符串
* @param children
* @return
*/
@Override
public String getDisplayString(String[] children) {
return "";
}
}
4.2 创建临时函数
(1)打成jar包上传到服务器/opt/module/hive/datas/myudf.jar
(2)将jar包添加到hive的classpath,临时生效
hive (default)> add jar /opt/module/hive/datas/myudf.jar;
(3)创建临时函数与开发好的java class关联
hive (default)> create temporary function my_len as "com.hadoop.hive.udf.MyLength";
(4)即可在hql中使用自定义的临时函数
hive (default)> select ename,my_len(ename) ename_len from emp;
(5)删除临时函数
hive (default)> drop temporary function my_len;
注意:临时函数只跟会话有关系,跟库没有关系。只要创建临时函数的会话不断,在当前会话下,任意一个库都可以使用,其他会话全都不能使用。
4.3 创建永久函数
- 在HIVE家目录下面创建auxlib目录
[hadoop@hadoop102 hive]$ mkdir auxlib
- 将jar包上传到$HIVE_HOME/auxlib下,然后重启hive
- 创建永久函数
hive (default)> create function 自定义函数名 as "udf方法的全类名";
- 在hql中使用自定义的永久函数
hive (default)> select ename,my_len2(ename) ename_len from emp;
- 删除永久函数
hive (default)> drop function my_len2;
注意:永久函数跟会话没有关系,创建函数的会话断了以后,其他会话也可以使用。
永久函数创建的时候,在函数名之前需要自己加上库名,如果不指定库名的话,会默认把当前库的库名给加上。
永久函数使用的时候,需要在指定的库里面操作,或者在其他库里面使用的话加上 库名.函数名