hive-udf-kafka批量数据导入kafka

背景:数据存在hive中,现在需要将数据导入kafka中,为了减少中间环节,使用自定义UDF将hive数据导入到kafka中

问题:UDF时对一行的处理,批量导入就会涉及多行的问题,怎么将多行数据放到一个udf中?
解决思路:用collect_list函数将多行转成集合,在udf中循环遍历,发送到kafka

1. package cn.kobold;
2.  
3. import org.apache.hadoop.hive.ql.exec.Description;
4. import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
5. import org.apache.hadoop.hive.ql.metadata.HiveException;
6. import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
7. import org.apache.hadoop.hive.serde2.objectinspector.*;
8. import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector.Category;
9. import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
10. import org.apache.hadoop.io.IntWritable;
11. import org.apache.kafka.clients.producer.KafkaProducer;
12. import org.apache.kafka.clients.producer.Producer;
13. import org.apache.kafka.clients.producer.ProducerRecord;
14. import org.json.JSONObject;
15.  
16. import java.util.HashMap;
17. import java.util.Map;
18. import java.util.Properties;
19.  
20. @Description(name = "hive2kafka", value = "_FUNC_(brokerhost_and_port,topic, array<map<string,string>>) - Return ret ")
21. public class Hive2KakfaUDF extends GenericUDF {
22.  
23.     private String hostAndPort;
24.     private String topics;
25.     private StandardListObjectInspector paramsListInspector;
26.     private StandardMapObjectInspector paramsElementInspector;
27.  
28.     public ObjectInspector initialize(ObjectInspector[] arg0) throws UDFArgumentException {
29.         if (arg0.length != 3) {
30.             throw new UDFArgumentException(" Expecting   two  arguments:<brokerhost:port> <topic>  array<map<string,string>> ");
31.         }
32.         // 第一个参数验证
33.         if (arg0[0].getCategory() == Category.PRIMITIVE
34.                 && ((PrimitiveObjectInspector) arg0[0]).getPrimitiveCategory() == PrimitiveObjectInspector.PrimitiveCategory.STRING) {
35.             if (!(arg0[0] instanceof ConstantObjectInspector)) {
36.                 throw new UDFArgumentException("broker host:port  must be constant");
37.             }
38.             ConstantObjectInspector brokerhost_and_port = (ConstantObjectInspector) arg0[0];
39.  
40.             hostAndPort = brokerhost_and_port.getWritableConstantValue().toString();
41.         }
42.  
43.         // 第二个参数验证
44.         if (arg0[1].getCategory() == Category.PRIMITIVE
45.                 && ((PrimitiveObjectInspector) arg0[1]).getPrimitiveCategory() == PrimitiveObjectInspector.PrimitiveCategory.STRING) {
46.             if (!(arg0[1] instanceof ConstantObjectInspector)) {
47.                 throw new UDFArgumentException("kafka topic must be constant");
48.             }
49.             ConstantObjectInspector topicCOI = (ConstantObjectInspector) arg0[1];
50.  
51.             topics = topicCOI.getWritableConstantValue().toString();
52.         }
53.  
54.  
55.         // 第三个参数验证
56.         if (arg0[2].getCategory() != Category.LIST) {
57.             throw new UDFArgumentException(" Expecting an array<map<string,string>> field as third argument ");
58.         }
59.         ListObjectInspector third = (ListObjectInspector) arg0[2];
60.         if (third.getListElementObjectInspector().getCategory() != Category.MAP) {
61.             throw new UDFArgumentException(" Expecting an array<map<string,string>> field as third argument ");
62.         }
63.         paramsListInspector = ObjectInspectorFactory.getStandardListObjectInspector(third.getListElementObjectInspector());
64.         paramsElementInspector = (StandardMapObjectInspector) third.getListElementObjectInspector();
65.         System.out.println(paramsElementInspector.getMapKeyObjectInspector().getCategory());
66.         System.out.println(paramsElementInspector.getMapValueObjectInspector().getCategory());
67.  
68.         return PrimitiveObjectInspectorFactory.writableIntObjectInspector;
69.  
70.     }
71.  
72.     public Object evaluate(DeferredObject[] arg0) throws HiveException {
73.         Properties props = new Properties();
74.         props.put("bootstrap.servers", hostAndPort);
75.         props.put("acks", "all");
76.         props.put("retries", 0);
77.         props.put("batch.size", 16384);
78.         props.put("linger.ms", 1);
79.         props.put("buffer.memory", 33554432);
80.         props.put("key.serializer", "org.apache.kafka.common.serialization.StringSerializer");
81.         props.put("value.serializer", "org.apache.kafka.common.serialization.StringSerializer");
82.  
83.         // 创建kafka生产者
84.         Producer<String, String> producer = new KafkaProducer<String, String>(props);
85.  
86.         for (int i = 0; i < paramsListInspector.getListLength(arg0[2].get()); i++) {
87.             Object row = paramsListInspector.getListElement(arg0[2].get(), i);
88.             Map<?, ?> map = paramsElementInspector.getMap(row);
89.             // Object obj = ObjectInspectorUtils.copyToStandardJavaObject(row,paramsElementInspector);
90.             // 转成标准的java map，否则里面的key value字段为hadoop writable对象
91.             Map<String, String> data = new HashMap<String,String>();
92.             for (Map.Entry<?, ?> entry : map.entrySet()) {
93.                 if (entry.getValue() != null && !"".equals(entry.getValue().toString())) {
94.                     data.put(entry.getKey().toString(), entry.getValue().toString());
95.                 }
96.             }
97.             JSONObject jsonObject = new JSONObject(data);
98.  
99.             //指定数据均匀写入3个分区中
100.             int part = i % 2;
101.             producer.send(new ProducerRecord<String, String>(topics, part,Integer.toString(i), jsonObject.toString()));
102.  
103.         }
104.  
105.         producer.close();
106.  
107.         return new IntWritable(1);
108.     }
109.  
110.     public String getDisplayString(String[] strings) {
111.         return "hive2kafka(brokerhost_and_port,topic, array<map<string,string>>)";
112.     }
113. }

测试SQL
第一个参数:broker所在位置
第二个参数:topic
第三个参数:将多行转成集合,每一行转成一个map

1. SELECT g,hive2kafka('bd01:9092','bobizli_test',collect_list(map('full_name',full_name,'simple_name',simple_name))) AS result
2. FROM
3. (
4. SELECT r1,pmod(ABS(hash(r1)),1000) AS g,full_name,simple_name
5. FROM(
6. SELECT row_number() over(PARTITION BY 1) AS r1,full_name,simple_name
7. FROM dws_bo_final_spider_contact
8. LIMIT 10000) tmp
9. ) tmp2
10. GROUP BY g;