目录
1.需求
自定义函数,通过用户生日,计算每个人对应的星座
2.创建数据
[root@hadoop l0415]# vim littlebigdata.txt
edward capriolo,edward@media6degrees.com,2-12-1981,209.191.139.200,M,10
bob,bob@test.net,10-10-2004,10.10.10.1,M,50
sara connor,sara@key.net,4-5-1974,64.64.5.1,F,2
3.创建表并导入数据
0: jdbc:hive2://192.168.17.151:10000> create table if not exists littlebigdata
. . . . . . . . . . . . . . . . . . > (name string,
. . . . . . . . . . . . . . . . . . > emaile string,
. . . . . . . . . . . . . . . . . . > bday string,
. . . . . . . . . . . . . . . . . . > ip string,
. . . . . . . . . . . . . . . . . . > gender string,
. . . . . . . . . . . . . . . . . . > anum int)
. . . . . . . . . . . . . . . . . . > row format delimited fields
. . . . . . . . . . . . . . . . . . > terminated by ',';
No rows affected (0.583 seconds)
0: jdbc:hive2://192.168.17.151:10000> load data local inpath '/usr/word/l0415/littlebigdata.txt'
. . . . . . . . . . . . . . . . . . > into table littlebigdata;
No rows affected (0.915 seconds)
函数的输入将是一个日期,函数输出将是表示该用户星座的字符串
4.创建一个类
(在这里我用的IDEA)
package ch1;
import org.apache.hadoop.hive.ql.exec.UDF;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
//自定义函数
public class UDFZodiacSign extends UDF {
private SimpleDateFormat df;
public UDFZodiacSign(){
df = new SimpleDateFormat("MM-dd-yyyy");
}
public String evaluate (Date bday){
return this.evaluate(bday.getMonth()+1,bday.getDate());
}
public String evaluate (String bday){
Date date = null;
try {
date = df.parse(bday);
} catch (ParseException e) {
return null;
}
return this.evaluate(date.getMonth()+1,date.getDay());
}
private String evaluate(Integer month, Integer day) {
if (month==1){
if (day < 20) {
return "Capricorn";
}else{
return "Aquarius";
}
}
if (month==2){
if (day <19) {
return "Aquarius";
}else {
return "Pisces";
}
}
return null;
}
}
5.打包jar并导入hive中
6.将jar包添加到hive的classpath
0: jdbc:hive2://192.168.17.151:10000> add jar /usr/word/l0415/con2.jar;
No rows affected (0.009 seconds)
7.创建临时函数与开发好的java class关联
0: jdbc:hive2://192.168.17.151:10000> create temporary function zodiac
. . . . . . . . . . . . . . . . . . > as 'ch1.UDFZodiacSign';
8.即可在hql中使用自定义的函数strip
0: jdbc:hive2://192.168.17.151:10000> select name, bday, zodiac(bday)
. . . . . . . . . . . . . . . . . . > from littlebigdata;
+------------------+-------------+-----------+--+
| name | bday | c2 |
+------------------+-------------+-----------+--+
| edward capriolo | 2-12-1981 | Aquarius |
| bob | 10-10-2004 | NULL |
| sara connor | 4-5-1974 | NULL |
| lisi | 5-29-2005 | NULL |
| wuwu | 6-19-2000 | NULL |
+------------------+-------------+-----------+--+
5 rows selected (0.169 seconds)