1、UDF的定义
UDF(User-Defined Functions)即是用户定义的hive函数。hive自带的函数并不能完全满足业务需求,这时就需要我们自定义函数了
UDF的分类
UDF:one to one,进来一个出去一个,row mapping。是row级别操作,如:upper、substr函数
UDAF:many to one,进来多个出去一个,row mapping。是row级别操作,如sum/min。
UDTF:one to many ,进来一个出去多个。如alteral view与explode
这三类中,我们只对UDF类的函数进行改写
2、UDF函数的编写
(1)pom文件配置
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5iYhZjMzQ2NhRmYhFTYmNjZ4ATZmljZ2UWNhJGNwMDO08CX0JXZ252bj91Ztl2Lc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
4.0.0
com.wsk.bigdata
g6-hadoop
1.0
g6-hadoop
1.7
1.7
UTF-8
2.6.0-cdh5.7.0
1.1.0-cdh5.7.0
nexus-aliyun
http://maven.aliyun.com/nexus/content/groups/public
cloudera
https://repository.cloudera.com/artifactory/cloudera-repos
org.apache.hadoop
hadoop-client
${hadoop.version}
junit
junit
4.11
test
org.apache.hive
hive-exec
${hive.version}
org.apache.maven.plugins
maven-compiler-plugin
2.4
1.7
1.7
UTF-8
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5iYhZjMzQ2NhRmYhFTYmNjZ4ATZmljZ2UWNhJGNwMDO08CX0JXZ252bj91Ztl2Lc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
(2)UDF函数编写
(3)打jar包
(4)上传jar包
[[email protected] lib]$ rz
[[email protected] lib]$ ll g6-hadoop-1.0.jar
-rw-r--r--. 1 hadoop hadoop 11447 Apr 19 2019 g6-hadoop-1.0.jar
注意:如果jar包是上传到$HIVE_HOME/lib/目录以下,就不需要执行add命令了
添加jar包到hive
语法:add jar +jar包所在的目录/jar包名字
hive> add jar /home/hadoop/data/hive/g6-hadoop-1.0.jar;
(5)在hive中创建UDF函数
创建临时函数 -----只对当前黑窗口有效
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5iYhZjMzQ2NhRmYhFTYmNjZ4ATZmljZ2UWNhJGNwMDO08CX0JXZ252bj91Ztl2Lc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
CREATE TEMPORARY FUNCTION function_name AS class_name;
function_name函数名
*******class_name 类路径,包名+类名********* 这里就是你写的UDF函数的第一行的package后边的东西然后在加个点加个类的名字
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5iYhZjMzQ2NhRmYhFTYmNjZ4ATZmljZ2UWNhJGNwMDO08CX0JXZ252bj91Ztl2Lc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
实例:
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5iYhZjMzQ2NhRmYhFTYmNjZ4ATZmljZ2UWNhJGNwMDO08CX0JXZ252bj91Ztl2Lc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
hive>CREATE TEMPORARY FUNCTION HelloUDF AS 'org.apache.hadoop.hive.ql.udf.HelloUDF';
OK Time taken: 0.485 seconds
hive>
hive> show functions; 【查看可以看到HelloUDF】
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5iYhZjMzQ2NhRmYhFTYmNjZ4ATZmljZ2UWNhJGNwMDO08CX0JXZ252bj91Ztl2Lc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
测试
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5iYhZjMzQ2NhRmYhFTYmNjZ4ATZmljZ2UWNhJGNwMDO08CX0JXZ252bj91Ztl2Lc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
hive>select HelloUDF('17');
OK
Hello:17
#检查mysql中的元数据,因为是临时函数,故元数据中并没有相关的信息
mysql> select * from funcs;
Empty set (0.11 sec)
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5iYhZjMzQ2NhRmYhFTYmNjZ4ATZmljZ2UWNhJGNwMDO08CX0JXZ252bj91Ztl2Lc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
删除临时函数 :
语法:DROP TEMPORARY FUNCTION [IF EXISTS] function_name;
测试
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5iYhZjMzQ2NhRmYhFTYmNjZ4ATZmljZ2UWNhJGNwMDO08CX0JXZ252bj91Ztl2Lc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
hive> DROP TEMPORARY FUNCTION IF EXISTS HelloUDF;
OK
Time taken: 0.003 seconds
hive> select HelloUDF('17');
FAILED: SemanticException [Error 10011]: Line 1:7 Invalid function 'HelloUDF'
##其实不删除也无所谓,重新开一个窗口即可
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5iYhZjMzQ2NhRmYhFTYmNjZ4ATZmljZ2UWNhJGNwMDO08CX0JXZ252bj91Ztl2Lc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
创建永久函数
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5iYhZjMzQ2NhRmYhFTYmNjZ4ATZmljZ2UWNhJGNwMDO08CX0JXZ252bj91Ztl2Lc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
CREATE TEMPORARY FUNCTION function_name AS class_name USING JAR path;
function_name函数名
class_name 类路径,
包名+类名
path jar包hdfs路径
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5iYhZjMzQ2NhRmYhFTYmNjZ4ATZmljZ2UWNhJGNwMDO08CX0JXZ252bj91Ztl2Lc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
将jar上传到指定目录
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5iYhZjMzQ2NhRmYhFTYmNjZ4ATZmljZ2UWNhJGNwMDO08CX0JXZ252bj91Ztl2Lc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
[[email protected] hive-1.1.0-cdh5.7.0]$ hadoop fs -mkdir /lib
[[email protected] hive-1.1.0-cdh5.7.0]$ hadoop fs -put /home/hadoop/data/hive/hive_UDF.jar /lib/
[[email protected] ~]$ hadoop fs -mkdir /lib
[[email protected] ~]$ hadoop fs -ls /lib
[[email protected] ~]$ hadoop fs -put ~/lib/g6-hadoop-1.0.jar /lib/ 把本地的jar上传到HDFS的/lib/目录下
[[email protected] ~]$ hadoop fs -ls /lib
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5iYhZjMzQ2NhRmYhFTYmNjZ4ATZmljZ2UWNhJGNwMDO08CX0JXZ252bj91Ztl2Lc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
创建永久生效的UDF函数
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5iYhZjMzQ2NhRmYhFTYmNjZ4ATZmljZ2UWNhJGNwMDO08CX0JXZ252bj91Ztl2Lc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
CREATE FUNCTION HelloUDF AS 'org.apache.hadoop.hive.ql.udf.HelloUDF'
USING JAR 'hdfs://hadoop001:9000/lib/g6-hadoop-1.0.jar';
#测试 hive> select HelloUDF("17") ;
OK hello:17
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5iYhZjMzQ2NhRmYhFTYmNjZ4ATZmljZ2UWNhJGNwMDO08CX0JXZ252bj91Ztl2Lc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
检查mysql中的元数据,测试函数的信息已经注册到了元数据中
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5iYhZjMzQ2NhRmYhFTYmNjZ4ATZmljZ2UWNhJGNwMDO08CX0JXZ252bj91Ztl2Lc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
mysql> select * from funcs;
+---------+------------------------------+-------------+-------+------------+-----------+------------+------------+
| FUNC_ID | CLASS_NAME | CREATE_TIME | DB_ID | FUNC_NAME | FUNC_TYPE | OWNER_NAME | OWNER_TYPE |
+---------+------------------------------+-------------+-------+------------+-----------+------------+------------+
| 1 |org.apache.hadoop.hive.ql.udf.HelloUDF | 1555263915 | 6 | HelloUDF | 1 | NULL | USER |
+---------+------------------------------+-------------+-------+------------+-----------+------------+------------+
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5iYhZjMzQ2NhRmYhFTYmNjZ4ATZmljZ2UWNhJGNwMDO08CX0JXZ252bj91Ztl2Lc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
创建的永久函数可以在任何一个窗口使用,重新启动函数也照样可以使用