最近接到需求:客户端上报json数据,入数据库后需要对json中的key分组统计时长和次数,但json中的key是不确定的,经常会增删。
明细层模型设计:将key和value分别设计为1列,这样能不需要关心json中的key和value怎么变,都就根据客户端上报的数据放入数据数据库
由此引发思索:
- 在json不确定的key和value情况下,怎么把key和value取出,并进行行转列放入表中?
- hive自带解析json的的函数get_json_object和json_tuple都必须要指定key才能将json解析出,并且一个key一列,value作为key列中的值
最开始的想法是通过自定义函数实现该功能,但自定义函数有时并不是很稳定
Java实现解析json中key和value
package com.fuyun.java;
import org.codehaus.jettison.json.JSONException;
import org.codehaus.jettison.json.JSONObject;
import java.util.Iterator;
/**
* @Author: 浮云
* @Date: 2020/1/8 18:09
*/
public class ParseJsonTest {
public static void main(String[] args) {
String jsonData = "{\"name\":\"Bob\",\"age\":\"18\"}";
try {
JSONObject jsonObject = new JSONObject(jsonData);
//通过迭代器获取这段json当中所有的key值
Iterator keys = jsonObject.keys();
//然后通过一个循环取出所有的key值
while (keys.hasNext()){
String key = String.valueOf(keys.next());
//最后就可以通过刚刚得到的key值去解析后面的json了
String value = jsonObject.optString(key);
System.out.println("key:" + key + " value:" + value);
}
} catch (JSONException je) {
je.printStackTrace();
}
}
}
以下用hive自带函数实现该功能
表中的数据大概为这样:
operation_name network_type detail_load_time load_time
电信 WIFI NULL 231
电信 NONETWORK {"MonkeySdkTime":173,"shellTime":100,"shellTime2":36,"ProbeSDKTime":88,"playerSDKTime":674,"pushSDKTime":34} 398
- 将json中所有key和value解析出来转换为数组
select operation_name
,network_type
,arr1_detail_load_time
,load_time
from temp.temp_page_detail_load_time
--去掉两表的{}再按,分割成数组,再行转列
lateral view explode(split(regexp_replace(detail_load_time, '\\{|\\}', ''), ',')) arr1 as arr1_detail_load_time
limit 2;
result:
移动 WIFI "shellTime":167 3211
移动 WIFI "shellTime2":30 3211
这样存在一个小小的bug:会过滤detail_load_time为null的数据,优化代码:
select operation_name
,network_type
,arr1_detail_load_time
from temp.temp_page_detail_load_time
--先将null值做转换,再去掉两表的{}再按,分割成数组,再行转列
lateral view explode(split(regexp_replace(nvl(detail_load_time, '-998'), '\\{|\\}', ''), ',')) arr1 as arr1_detail_load_time
limit 2;
result:
移动 WIFI "ProbeSDKTime":198 3221
移动 WIFI "playerSDKTime":36 3221
- 将上面准换好的数组再进行分割,分别解析出key和value置为两列
select operation_name
,network_type
--先将"去掉,再按:分割成数组
,split(regexp_replace(arr1.arr1_detail_load_time, '"', ''), ':')[0] detail_load_time_type
,cast(nvl(split(regexp_replace(arr1.arr1_detail_load_time, '"', ''), ':')[1], 0) as bigint) detail_load_time
from temp.temp_page_detail_load_time
--去掉两表的{}再按,分割成数组,再行转列
lateral view explode(split(regexp_replace(nvl(detail_load_time, '-998'), '\\{|\\}', ''), ',')) arr1 as arr1_detail_load_time
limit 2;
reslut:
电信 WIFI -998 0
移动 WIFI playerSDKTime 167
这样就大功告成!