UDF：User Definition Function
一、function
#查看自带的函数
hive (db_hive)> show functions;
#查看一个函数的详细用法
hive (db_hive)> desc function extended split;
OK
tab_name
split(str, regex) - Splits str around occurances that match regex
Example:
> SELECT split('oneAtwoBthreeC', '[ABC]') FROM src LIMIT 1;
["one", "two", "three"]
Time taken: 0.005 seconds, Fetched: 4 row(s)二、UDF
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
https://cwiki.apache.org/confluence/display/Hive/HivePlugins #自定义UDF
Hive自带了一些函数，比如：max/min等，但是数量有限，自己可以通过自定义UDF来方便的扩展。
UDF：用户自定义函数，允许用户扩展HiveQL功能；
##
UDF（User-Defined-Function）
一进一出
UDAF（User-Defined Aggregation Funcation）
聚集函数，多进一出；类似于：count/max/min
UDTF（User-Defined Table-Generating Functions）
一进多出；如lateral view explore（）
编程步骤：
1、继承org.apache.hadoop.hive.ql.UDF
2、需要实现evaluate函数；evaluate函数支持重载；
注意事项：
1、UDF必须要有返回类型，可以返回null，但是返回类型不能为void；
2、UDF中常用Text/LongWritable等类型，不推荐使用java类型；创建一个UDF-方式一：
1、Creating Custom UDFs
### LowerUDF.java###
package com.beifeng.senior.hive.udf;
import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;
/**
* 1. Implement one or more methods named
* "evaluate" which will be called by Hive.
*
* 2."evaluate" should never be a void method. However it can return "null" if
* needed.
* @author root
*
*/
public class LowerUDF extends UDF{
public Text evaluate(Text str) {
//validate
if(null == str.toString()) {
return null;
}
//lower
return new Text (str.toString().toLowerCase());
}
public static void main(String[] args) {
System.out.println(new LowerUDF().evaluate(new Text("HIVE")));
}
}
#然后打成jar包
[root@hadoop-senior datas]# pwd
/opt/datas
[root@hadoop-senior datas]# ls hiveudf.jar
hiveudf.jar2、usage
#添加
hive (db_hive)> add jar /opt/datas/hiveudf.jar;
Added /opt/datas/hiveudf.jar to class path
Added resource: /opt/datas/hiveudf.jar
#注册，my_lower是要注册的函数名，com.beifeng.senior.hive.udf.LowerUDF是类名
hive (db_hive)> create temporary function my_lower as "com.beifeng.senior.hive.udf.LowerUDF";
OK
Time taken: 0.012 seconds
#查看
hive (db_hive)> show functions;
...
my_lower
...
#测试使用
hive (db_hive)> select ename, my_lower(ename) lowername from emp limit 5;
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1554717689707_0031, Tracking URL = http://hadoop-senior.ibeifeng.com:8088/proxy/application_1554717689707_0031/
Kill Command = /opt/modules/hadoop-2.5.0/bin/hadoop job -kill job_1554717689707_0031
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2019-04-24 15:32:42,268 Stage-1 map = 0%, reduce = 0%
2019-04-24 15:32:47,387 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.28 sec
MapReduce Total cumulative CPU time: 1 seconds 280 msec
Ended Job = job_1554717689707_0031
MapReduce Jobs Launched:
Job 0: Map: 1 Cumulative CPU: 1.28 sec HDFS Read: 894 HDFS Write: 60 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 280 msec
OK
ename lowername
SMITH smith
ALLEN allen
WARD ward
JONES jones
MARTIN martin
Time taken: 10.548 seconds, Fetched: 5 row(s)创建一个UDF-方式二：
此方法jar包要位于hdfs上；
CREATE FUNCTION myfunc AS 'myclass' USING JAR 'hdfs:///path/to/jar';
1、
##上传jar包到hdfs
hive (db_hive)> dfs -mkdir -p /user/root/hive/jars/;
hive (db_hive)> dfs -put /opt/datas/hiveudf.jar /user/root/hive/jars/;
hive (db_hive)> dfs -ls -R /user/root/hive/jars;
-rw-r--r-- 1 root supergroup 910 2019-04-24 15:40 /user/root/hive/jars/hiveudf.jar
#创建function
hive (db_hive)> create function self_lower as 'com.beifeng.senior.hive.udf.LowerUDF' using jar 'hdfs://hadoop-senior.ibeifeng.com:8020/user/root/hive/jars/hiveudf.jar';
converting to local hdfs://hadoop-senior.ibeifeng.com:8020/user/root/hive/jars/hiveudf.jar
Added /tmp/5356b66f-bf56-4de6-abf8-30be8029fa8b_resources/hiveudf.jar to class path
Added resource: /tmp/5356b66f-bf56-4de6-abf8-30be8029fa8b_resources/hiveudf.jar
OK
Time taken: 0.025 seconds
#使用
hive (db_hive)> select ename, self_lower(ename) lowername from emp limit 5;
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1554717689707_0032, Tracking URL = http://hadoop-senior.ibeifeng.com:8088/proxy/application_1554717689707_0032/
Kill Command = /opt/modules/hadoop-2.5.0/bin/hadoop job -kill job_1554717689707_0032
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2019-04-24 15:53:28,378 Stage-1 map = 0%, reduce = 0%
2019-04-24 15:53:33,504 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.35 sec
MapReduce Total cumulative CPU time: 1 seconds 350 msec
Ended Job = job_1554717689707_0032
MapReduce Jobs Launched:
Job 0: Map: 1 Cumulative CPU: 1.35 sec HDFS Read: 894 HDFS Write: 60 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 350 msec
OK
ename lowername
SMITH smith
ALLEN allen
WARD ward
JONES jones
MARTIN martin
Time taken: 10.549 seconds, Fetched: 5 row(s)