自定义函数
------------------
1.创建类,继承UDF
import org.apache.hadoop.hive.ql.exec.Description;
import org.apache.hadoop.hive.ql.exec.UDF;
/**
* 自定义hive函数
*/
@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;
}
}
2.打成jar包。
cmd>cd {classes所在目录}
cmd>jar cvf HiveDemo.jar -C x/x/x/x/classes/ .
3.添加jar包到hive的类路径
//添加jar到类路径
$>cp /mnt/hgfs/downloads/bigdata/data/HiveDemo.jar /soft/hive/lib
3.重进入hive
$>....
4.创建临时函数
//
CREATE TEMPORARY FUNCTION myadd AS 'com.it18zhang.hivedemo.udf.AddUDF';
5.在查询中使用自定义函数
$hive>select myadd(1,2) ;
6.定义日期函数
1)定义类
public class ToCharUDF extends UDF {
/**
* 取出服务器的当前系统时间 2017/3/21 16:53:55
*/
public String evaluate() {
Date date = new Date();
SimpleDateFormat sdf = new SimpleDateFormat();
sdf.applyPattern("yyyy/MM/dd hh:mm:ss");
return sdf.format(date) ;
}
public String evaluate(Date date) {
SimpleDateFormat sdf = new SimpleDateFormat();
sdf.applyPattern("yyyy/MM/dd hh:mm:ss");
return sdf.format(date) ;
}
public String evaluate(Date date,String frt) {
SimpleDateFormat sdf = new SimpleDateFormat();
sdf.applyPattern(frt);
return sdf.format(date) ;
}
}
2)导出jar包,通过命令添加到hive的类路径(不需要重进hive)。
$hive>add jar /mnt/hgfs/downloads/bigdata/data/HiveDemo-1.0-SNAPSHOT.jar
3)注册函数
$hive>CREATE TEMPORARY FUNCTION to_char AS 'com.it18zhang.hivedemo.udf.ToCharUDF';
$hive>CREATE TEMPORARY FUNCTION to_date AS 'com.it18zhang.hivedemo.udf.ToDateUDF';
定义Nvl函数
------------------
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.ql.udf.generic.GenericUDFUtils;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
/**
* 自定义null值处理函数
*/
public class Nvl extends GenericUDF {
private GenericUDFUtils.ReturnObjectInspectorResolver returnOIResolver;
private ObjectInspector[] argumentOIs;
public ObjectInspector initialize(ObjectInspector[] arguments)
throws UDFArgumentException {
argumentOIs = arguments;
//检查参数个数
if (arguments.length != 2) {
throw new UDFArgumentLengthException(
"The operator 'NVL' accepts 2 arguments.");
}
returnOIResolver = new GenericUDFUtils.ReturnObjectInspectorResolver(true);
//检查参数类型
if (!(returnOIResolver.update(arguments[0]) && returnOIResolver
.update(arguments[1]))) {
throw new UDFArgumentTypeException(2,
"The 1st and 2nd args of function NLV should have the same type, "
+ "but they are different: \"" + arguments[0].getTypeName()
+ "\" and \"" + arguments[1].getTypeName() + "\"");
}
return returnOIResolver.get();
}
public Object evaluate(DeferredObject[] arguments) throws HiveException {
Object retVal = returnOIResolver.convertIfNecessary(arguments[0].get(), argumentOIs[0]);
if (retVal == null) {
retVal = returnOIResolver.convertIfNecessary(arguments[1].get(),
argumentOIs[1]);
}
return retVal;
}
public String getDisplayString(String[] children) {
StringBuilder sb = new StringBuilder();
sb.append("if ");
sb.append(children[0]);
sb.append(" is null ");
sb.append("returns");
sb.append(children[1]);
return sb.toString();
}
}
2)添加jar到类路径
...
3)注册函数
$hive>CREATE TEMPORARY FUNCTION nvl AS 'org.apache.hadoop.hive.ql.udf.generic.GenericUDFNvl';