原文地址:http://cache.baiducontent.com/c?m=9d78d513d9991aeb0ffa940f55578a3a0e55f0744ca1c7627fc3e23f84105f550026bdb47d645646c4c40f7a1cee1400bfb26b65377573f1c1df883c9be8cf787cd53034064ddb1e05d36ef48d116e9637902db8f246faeaae6fc6b3d1d9de5759ce59&p=8b2a975388981fee0cbd9b7e0b1d9d&newp=9b3dc54ad6c54af54eb3cf2d021496231610db2151d4db1e25c2cd13&user=baidu&fm=sc&query=hive+json%BD%E2%CE%F6&qid=&p1=8
1. UDTF介绍
UDTF(User-Defined Table-Generating Functions) 用来解决 输入一行输出多行(On-to-many maping) 的需求。
2. 编写自己需要的UDTF
继承org.apache.hadoop.hive.ql.udf.generic.GenericUDTF。
实现initialize, process, close三个方法
UDTF首先会调用initialize方法,此方法返回UDTF的返回行的信息(返回个数,类型)。初始化完成后,会调用process方法,对传入的参数进行处理,可以通过forword()方法把结果返回。最后close()方法调用,对需要清理的方法进行清理。
下面是我写的一个UDTF解析json格式,比较纠结的是这个字段很多时候不满足json的定义,有{}值,Null一些情况,做了很多判断,不知道页面展示怎么通过的
package com.taobao.voc.<span style="background-color: rgb(255, 255, 102);">hive</span>.udtf;
public class CopyOfUDTFJson2Rows extends GenericUDTF {
@Override
public StructObjectInspector initialize(ObjectInspector[] args) throws UDFArgumentException {
if (args.length != 1 && args.length != 2) {
throw new UDFArgumentLengthException("UDTFSplitValue takes only one or two argument");
}
if (args[0].getCategory() != ObjectInspector.Category.PRIMITIVE) {
throw new UDFArgumentException("UDTFSplitValue takes string as a parameter");
}
ArrayList<String> fieldNames = new ArrayList<String>();
ArrayList<ObjectInspector> fieldOIs = new ArrayList<ObjectInspector>();
fieldNames.add("col");
fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames, fieldOIs);
}
public void process(Object[] args) throws HiveException {
JSONObject <span style="background-color: rgb(160, 255, 255);">json</span>;
try {
<span style="background-color: rgb(160, 255, 255);">json</span> = new JSONObject(args[0].toString());
if (<span style="background-color: rgb(160, 255, 255);">json</span>.has("bzwd")) {
String bzwd = <span style="background-color: rgb(160, 255, 255);">json</span>.getString("bzwd");
bzwd = new JSONObject(bzwd).getString("data");
JSONObject asks = new JSONObject(bzwd);
String result = "";
for (int i = 0; i < asks.getJSONArray("child").length(); i++) {
result = result + getAskAnswer(asks.getJSONArray("child").getJSONObject(i), 1, (i + 1) + "");
}
String[] split = result.split("\n");
System.out.println(Thread.currentThread().getName() + " " + result);
for (int i = 0; i < split.length; i++) {
String[] temp = { split[i] };
forward(temp);
}
} else {
forward(new String[] { "非标准化问题", "非标准化问题", "非标准化问题", "非标准化问题" });
}
} catch (JSONException e) {
e.printStackTrace();
}
}
/**
* @param pNode
* <span style="background-color: rgb(160, 255, 255);">json</span>数组 level 问答层级 line 问和答属于第几条线路
* */
public String getAskAnswer(JSONObject pNode, int level, String sLine) throws JSONException {
String final_result = "";
if (!pNode.toString().isEmpty() && pNode.has("value") && pNode.has("index")) {
final_result = final_result + "p_id:" + pLine(sLine) + ";s_id:" + sLine + ";level:" + level + ";ask_id:"
+ pNode.get("index") + ";answer_id:" + pNode.get("value") + "\n";
}
// 子节点有子节点,并且子节点是有效的答案(即value字段有值)
if (pNode.has("child") && pNode.has("value")) {
System.out.println(pNode.get("value"));
for (int j = 0; j < pNode.getJSONArray("child").length(); j++) {
if (ifContinue(pNode)) {
final_result = final_result
+ getAskAnswer(pNode.getJSONArray("child").getJSONObject(j), level + 1, sLine(sLine, j));
}
}
}
return final_result;
}
//几个判断节点是否为空和获取p_id的函数省略
}
UDTF有两种使用方法,一种直接放到select后面,一种和lateral view一起使用。
输入格式为<span style="background-color: rgb(160, 255, 255);">JSON</span>
添加jar
add jar /home/taobao/dw_<span style="background-color: rgb(255, 255, 102);">hive</span>/hivelets/smoking/ext/tsa/hivesql/udf/<span style="background-color: rgb(160, 255, 255);">Json</span>2rows.jar;
CREATE TEMPORARY FUNCTION jrow AS 'com.taobao.voc.<span style="background-color: rgb(255, 255, 102);">hive</span>.udtf.UDTFJson2Rows';
1:直接select中使用:
select jrow(ext_attrs) as format_memo from s_tpp_case_universal;
2:和lateral view一起使用:
select id,format_memo,gmt_create,gmt_modified from s_tpp_case_universal lateral view jrow(ext_attrs) b as format_memo;
结果:
p_id:0;s_id:1;level:1;ask_id:dpxxwh1;answer_id:ppxgwt2
p_id:1;s_id:1.1;level:2;ask_id:ppxgwt1;answer_id:pptj2
p_id:1.1;s_id:1.1.1;level:3;ask_id:pptj1;answer_id:pptjtjh2
p_id:1.1.1;s_id:1.1.1.1;level:4;ask_id:pptjh1;answer_id:yyshjg2
p_id:1.1.1.1;s_id:1.1.1.1.1;level:5;ask_id:yyshjg1;answer_id:pptjbtg2
p_id:1.1.1.1.1;s_id:1.1.1.1.1.1;level:6;ask_id:shjgbtg1;answer_id:shcw2