hive自定义函数

生产中自定义函数用于公司特有的公式

UDF  操作单个数据行，产生单个数据行；

1.
[hadoop@h91 hhh]$ vi TimeFormat.java

import java.sql.Date;
import java.text.SimpleDateFormat;
import org.apache.hadoop.hive.ql.exec.UDF;
public class TimeFormat extends UDF {
 public String evaluate(String num){
  Date d=new Date(Long.decode(num));
  SimpleDateFormat sdf =new SimpleDateFormat("yyyy-MM-dd HH:MM:SS");
  return sdf.format(d) ;
 }
}

2.打包
[hadoop@h91 hhh]$ /usr/jdk1.7.0_25/bin/javac -classpath /home/hadoop/hadoop-0.20.2-cdh3u5/hadoop-core-0.20.2-cdh3u5.jar:/home/hadoop/hive-0.9.0-bin/lib/hive-exec-0.9.0.jar TimeFormat.java

[hadoop@h91 hhh]$ vi main.mf
Manifest-Version: 1.0

[hadoop@h91 hhh]$ /usr/jdk1.7.0_25/bin/jar cvfm TF.jar main.mf TimeFormat.class

3.
hive> add jar /home/hadoop/hhh/TF.jar;
hive> CREATE TEMPORARY FUNCTION TFF AS 'TimeFormat';

create table ss(id bigint)
     row format delimited
     fields terminated by '\t'
     stored as textfile;

hive> load data local inpath '/home/hadoop/c.txt' into table ss;

[hadoop@h851 ~]$ date +%s （显示当前时间 转换成秒）
（时间戳为 把时间转换成秒 *1000 变为 毫秒点位）

[hadoop@h851 ~]$ vi c.txt
1417792627000

TFF把时间戳 转换为 ("yyyy-MM-dd HH:MM:SS")
hive> select TFF(time) from ha;

-----------------------------------------------
[hadoop@h91 hhh]$ vi hello.java
import org.apache.hadoop.hive.ql.exec.UDF;
public class hello extends UDF {
    public String evaluate(String str) {

        try {

            return "HelloWorld " + str;

        } catch (Exception e) {

            return null;

        }
    }
}

2.打包
[hadoop@h91 hhh]$ /usr/jdk1.7.0_25/bin/javac -classpath /home/hadoop/hadoop-0.20.2-cdh3u5/hadoop-core-0.20.2-cdh3u5.jar:/home/hadoop/hive-0.9.0-bin/lib/hive-exec-0.9.0.jar hello.java

[hadoop@h91 hhh]$ vi main.mf
Manifest-Version: 1.0

[hadoop@h91 hhh]$ /usr/jdk1.7.0_25/bin/jar cvfm H.jar main.mf hello.class

3.
hive> add jar /home/hadoop/hhh/H.jar;
hive> CREATE TEMPORARY FUNCTION HH AS 'hello';

hive> select HH(name) from ha;

---------------------------------
大写转换为小写

import org.apache.hadoop.hive.ql.exec.UDF; 
import org.apache.hadoop.io.Text; 
 
public class ToLower extends UDF { 
    public Text evaluate(final Text s) { 
        if (s == null) { return null; } 
        return new Text(s.toString().toLowerCase()); 
    } 
} 

[hadoop@h101 ~]$ /usr/jdk1.7.0_25/bin/javac ToLower.java
[hadoop@h101 ~]$ /usr/jdk1.7.0_25/bin/jar cvf tt.jar ToLower.class

hive> add jar /home/hadoop/tt.jar;
hive> CREATE TEMPORARY FUNCTION tt AS 'ToLower';
hive> select tt(name) from st;

------------------------------------------
[hadoop@h101 ~]$ vi Add.java
import org.apache.hadoop.hive.ql.exec.UDF;
public final class Add extends UDF {
public Integer evaluate(Integer a, Integer b) {
               if (null == a || null == b) {
                               return null;
               } return a + b;
}
}

[hadoop@h101 ~]$ /usr/jdk1.7.0_25/bin/javac Add.java
[hadoop@h101 ~]$ /usr/jdk1.7.0_25/bin/jar cvf ad.jar Add.class

hive> add jar /home/hadoop/ad.jar;
hive> CREATE TEMPORARY FUNCTION ad AS 'Add';
hive> select ad(id,age) from st;