1 Hive的内置函数
- 在Hive中给我们内置了很多函数官方地址
- 也可以在启动hive后输入命令查看函数:
SHOW FUNCTIONS;
DESCRIBE FUNCTION <function_name>;
DESCRIBE FUNCTION EXTENDED <function_name>;
- 查看所有的内置函数
hive> show functions;
- 查看函数的具体语法
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
hive> DESCRIBE FUNCTION EXTENDED case;
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_de
tails
2 Hive自定义函数
Hive中的内置函数,比较简单,用到的时候可以在官网进行查询,虽然有很多内置的函数,但是生产上肯定不够那么全面,所有,用户需要自定义函数来满足自身的求
- 自定义函数开发UDF(官方网址):
1. 使用maven创建一个java项目
2. 继承UDF类;
3. 重写evaluate方法。
(作者使用的IDEA进行开发的)
- 创建一个HelloUdf类继承UDF,并且重写evaluate方法
import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;
import java.io.UTFDataFormatException;
public class HelloUdf extends UDF {
public Text evaluate (final Text t) {
if (t == null) return null;
return new Text("Hello:" + t);
}
public static void main(String [] args){
System.out.println(new HelloUdf().evaluate(new Text("hero")));
}
}
打jar包
上传jar包
[hadoop@zhangyu ~]$ rz
rz waiting to receive.
Starting zmodem transfer. Press Ctrl+C to cancel.
100% 4 KB 4 KB/s 00:00:01 0 Errors
[hadoop@zhangyu lib]$ ll
total 8
-rw-r--r--. 1 hadoop hadoop 4232 Jan 15 2018 hive-1.0.jar
hive> add jar /home/hadoop/lib/hive-1.0.jar
> ;
Added [/home/hadoop/lib/hive-1.0.jar] to class path
Added resources: [/home/hadoop/lib/hive-1.0.jar]
创建自定义函数有两种格式:临时函数,永久函数(0.13版本后):
官方语法
create temporary function my_lower as 'com.example.hive.udf.Lower';
create function my_db.my_lower as 'com.example.hive.udf.Lower';
3 临时自定义函数(只对当前session有效)
hive> create temporary function say_hello as 'cn.zhangyu.HelloUdf';
OK
hive> select ename,say_hello(ename)from emp;
OK
ENAME Hello:ENAME
SMITH Hello:SMITH
ALLEN Hello:ALLEN
WARD Hello:WARD
JONES Hello:JONES
MARTIN Hello:MARTIN
BLAKE Hello:BLAKE
CLARK Hello:CLARK
SCOTT Hello:SCOTT
KING Hello:KING
TURNER Hello:TURNER
ADAMS Hello:ADAMS
JAMES Hello:JAMES
FORD Hello:FORD
MILLER Hello:MILLER
Time taken: 0.119 seconds, Fetched: 15 row(s)
- 查看jar包
hive> list jar;
/home/hadoop/lib/hive-1.0.jar
- 查看自定义函数
hive> show functons;
4 永久函数
- 把jar包上传到hdfs上
[hadoop@zhangyu lib]$ hdfs dfs -put /home/hadoop/lib/hive-1.0.jar /lib
[hadoop@zhangyu lib]$ hdfs dfs -ls /lib
-rw-r--r-- 1 hadoop supergroup 4232 2018-01-14 08:03 /lib/hive-1.0.jar
- 创建永久函数
hive> CREATE FUNCTION say_hello1
> AS 'cn.zhangyu.HelloUdf'
> USING JAR 'hdfs:///lib/hive-1.0.jar';
converting to local hdfs:///lib/hive-1.0.jar
Added [/tmp/7f999adc-e8a4-4642-af56-581f078cdc32_resources/hive-1.0.jar] to class path
Added resources: [hdfs:///lib/hive-1.0.jar]
OK
- 测试
hive> select ename,say_hello1(ename)from emp;
OK
ENAME Hello:ENAME
SMITH Hello:SMITH
ALLEN Hello:ALLEN
WARD Hello:WARD
JONES Hello:JONES
MARTIN Hello:MARTIN
BLAKE Hello:BLAKE
CLARK Hello:CLARK
SCOTT Hello:SCOTT
KING Hello:KING
TURNER Hello:TURNER
ADAMS Hello:ADAMS
JAMES Hello:JAMES
FORD Hello:FORD
MILLER Hello:MILLER
Time taken: 0.084 seconds, Fetched: 15 row(s)
注意:执行这条语句创建永久函数,show functiuons 会加上默认的数据库名在函数名前。(default.say_hello1)
5 在MySQL中查询创建的自定义函数
mysql> select * from funcs;
+---------+---------------------+-------------+-------+------------+-----------+------------+------------+
| FUNC_ID | CLASS_NAME | CREATE_TIME | DB_ID | FUNC_NAME | FUNC_TYPE | OWNER_NAME | OWNER_TYPE |
+---------+---------------------+-------------+-------+------------+-----------+------------+------------+
| 1 | cn.zhangyu.HelloUdf | 1515888313 | 1 | say_hello1 | 1 | NULL | USER |
+---------+---------------------+-------------+-------+------------+-----------+------------+------------+
1 row in set (0.00 sec)