hive解析不确定key的json，将key和value分别放入两列

逃离我推掉我的手 2021-09-23 15:46 841阅读 0赞

最近接到需求：客户端上报json数据，入数据库后需要对json中的key分组统计时长和次数，但json中的key是不确定的，经常会增删。

明细层模型设计：将key和value分别设计为1列，这样能不需要关心json中的key和value怎么变，都就根据客户端上报的数据放入数据数据库  
由此引发思索：

1.  在json不确定的key和value情况下，怎么把key和value取出，并进行行转列放入表中？
2.  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

1.  将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

1.  将上面准换好的数组再进行分割，分别解析出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

这样就大功告成！