Hive+UDTF简单示例

       UDTF(User-Defined Table-Generating Functions)  用来解决输入一行输出多行(On-to-many maping)的需求。

         我们来看一个简单的例子。

         现在有一个名为studentScore.txt的文本,里面的内容如下：

                        A       90
                        A       80
                        A       70
                        A       50
                        B       60
                        B       90
                        B       95
                        B       80

         我们 要统计 A和B的总分。

          1）将studentScore.txt上传至hdfs.

                 bin/hadoop fs -put /home/wangzhun/tmp/studentScore.txt wz

          2)  在hive里面，创建studentScore表,并导入 数据 

                 create table studentScore(name string,score int) ROW FORMAT DELIMITED Fields TERMINATED BY '\t';

                 load data inpath '/user/root/wz/studentScore.txt' overwrite into table studentScore;               

          3）编写UDTF。

                

[java]  view plain copy

1. package com.wz.udf;  
2. import java.util.ArrayList;  
3.   
4. import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;  
5. import org.apache.hadoop.hive.ql.exec.UDFArgumentException;  
6. import org.apache.hadoop.hive.ql.exec.UDFArgumentLengthException;  
7. import org.apache.hadoop.hive.ql.metadata.HiveException;  
8. import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;  
9. import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;  
10. import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;  
11. import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;  
12.   
13. public class staticScore extends GenericUDTF{  
14.   Integer nTotalScore = Integer.valueOf(0);       //总分  
15.   Object forwardObj[] = new Object[1];  
16.   String strStudent;       //学生姓名  
17.   @Override  
18.   public void close() throws HiveException {    
19.         //输出最后一个学生的总分  
20.     forwardObj[0]=(strStudent+":"+String.valueOf(nTotalScore));  
21.     forward(forwardObj);  
22.     }  
23.   
24.     
25.   @Override  
26.   public StructObjectInspector initialize(ObjectInspector[] args)throws UDFArgumentException {  
27.           strStudent="";  
28.       ArrayList<String> fieldNames = new ArrayList<String>();  
29.           ArrayList<ObjectInspector> fieldOIs = new ArrayList<ObjectInspector>();  
30.           fieldNames.add("col1");  
31.           fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);  
32.           return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames,fieldOIs);  
33.        }  
34.   
35.   
36.   @Override  
37.   public void process(Object[] args) throws HiveException {  
38.     if(!strStudent.isEmpty() && !strStudent.equals(args[0].toString()))  
39.     {  
40.            //当学生名字变化时，输出该学生的总分  
41.        String[] newRes = new String[1];  
42.        newRes[0]=(strStudent+":"+String.valueOf(nTotalScore));  
43.        forward(newRes);  
44.        nTotalScore=0;  
45.     }     
46.     strStudent=args[0].toString();  
47.     nTotalScore+=Integer.parseInt(args[1].toString());  
48.        }  
49.   
50.    }  

          4）打包编译成jar包.

                 javac -classpath /home/wangzhun/hadoop/hadoop-0.20.2/hadoop-0.20.2-core.jar:/home/wangzhun/hive/hive-0.8.1/lib/hive-exec-0.8.1.jar staticScore.java

                 jar -cvf staticScore.jar com/wz/udf/staticScore.class

           5) 在hive里面添加jar包，创建临时函数，并执行得到结果。

                add jar /home/wangzhun/hive/hive-0.8.1/lib/staticScore.jar; 
                create temporary function statics as 'com.wz.udf.staticScore';
               

[plain]  view plain copy

1. hive> select statics(studentScore.name,studentScore.score) as col1 from studentSselect statics(studentScore.name,studentScore.score) mytable as col1 from studencreate temporary function statics as 'com.wz.udf.staticScore';         
2. OK  
3. Time taken: 0.213 seconds  
4. hive> select statics(studentScore.name,studentScore.score) as col1 from studentScore;  
5. Total MapReduce jobs = 1  
6. Launching Job 1 out of 1  
7. Number of reduce tasks is set to 0 since there's no reduce operator  
8. Starting Job = job_201311282251_0001, Tracking URL = http://localhost:50030/jobdetails.jsp?jobid=job_201311282251_0001  
9. Kill Command = /home/wangzhun/hadoop/hadoop-0.20.2/bin/../bin/hadoop job  -Dmapred.job.tracker=localhost:9001 -kill job_201311282251_0001  
10. Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0  
11. 2013-11-28 23:02:24,612 Stage-1 map = 0%,  reduce = 0%  
12. 2013-11-28 23:02:30,689 Stage-1 map = 100%,  reduce = 0%  
13. 2013-11-28 23:02:33,732 Stage-1 map = 100%,  reduce = 100%  
14. Ended Job = job_201311282251_0001  
15. MapReduce Jobs Launched:   
16. Job 0: Map: 1   HDFS Read: 40 HDFS Write: 12 SUCESS  
17. Total MapReduce CPU Time Spent: 0 msec  
18. OK  
19. A:290  
20. B:325  
21. Time taken: 34.356 seconds