官方地址
启动hive后通过命令查看函数
SHOW FUNCTIONS;
DESCRIBE FUNCTION <function_name>;
DESCRIBE FUNCTION EXTENDED <function_name>;
1
2
3
hive> show functions;
...
Time taken: 1.933 seconds, Fetched: 220 row(s)
hive> DESCRIBE FUNCTION case;
OK
CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END - When a = b, returns c; when a = d, return e; else return f
Time taken: 0.009 seconds, Fetched: 1 row(s)
hive> DESCRIBE FUNCTION EXTENDED case;
OK
CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END - When a = b, returns c; when a = d, return e; else return f
Example:
SELECT
CASE deptno
WHEN 1 THEN Engineering
WHEN 2 THEN Finance
ELSE admin
END,
CASE zone
WHEN 7 THEN Americas
ELSE Asia-Pac
END
FROM emp_details
Time taken: 0.005 seconds, Fetched: 13 row(s)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
自定义函数
@Description(name = "myadd",
value = "myadd(int a , int b) ==> return a + b ",
extended = "Example:\n"
+ " myadd(1,1) ==> 2 \n"
+ " myadd(1,2,3) ==> 6;")
public class AddUDF extends UDF {
public int evaluate(int a ,int b) {
return a + b ;
}
public int evaluate(int a ,int b , int c) {
return a + b + c;
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
将程序打成jar包放到目标机器上去
将jar包上传到hdfs上
[root@node00 ~]# hdfs dfs -put /root/test/substring_index_UDF.jar /user/hive/udf
[root@node00 ~]# hdfs dfs -ls /user/hive/udf
Found 1 items
-rw-r--r-- 3 yarn hive 24859 2018-12-21 20:48 /user/hive/udf/substring_index_UDF.jar
1
2
3
4
创建永久函数
hive> CREATE FUNCTION substring_index AS 'com.hnb.data.hive.SubStringIndexUDF' using jar 'hdfs://nameservice1/user/hive/udf/substring_index_UDF.jar';
1
注意：执行这条语句创建永久函数，show functiuons 会加上默认的数据库名在函数名前。（default.say_hello1）
在MySQL中查询创建的自定义函数
mysql> use hive;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
加油，感谢分享