hive 中实现对json格式数据的解析

1. json对象

hive 中有json解析的工具get_json_object 和json_tupple,这里不多做解释,直接传入json类型的参数调用即可

比如以下数据，想要获取用户id，直接使用get_json_object(req_head,'$.terminal.uid')

{"curpage":"android_search.html","client_ip":"114.102.*.169","terminal":{"mode":"SM-N9009","manufacture":"samsung","macAddress":"","imei":"","imsi":"","uid":"e6dc1412aab8****"},"timestamp":1552320467}
{"curpage":"android_suggestion.html","client_ip":"223.104.*.73","terminal":{"mode":"F1-F2-F3-Y800-Y900-B5","manufacture":"alps","macAddress":"","imei":"","imsi":"","uid":"d8bd2a0cc0a****"},"timestamp":1552320494}
{"curpage":"android_search.html","client_ip":"113.194.*.31","terminal":{"mode":"OPPO A83","manufacture":"OPPO","macAddress":"","imei":"","imsi":"","uid":"6a9bdc0f3b*****"},"timestamp":1552320655}
{"curpage":"android_search.html","client_ip":"111.60.*.71","terminal":{"mode":"U25GT-C4YT","manufacture":"CUBE","macAddress":"","imei":"","imsi":"","uid":"59722af35aba****"},"timestamp":1552320678}
{"curpage":"android_search.html","client_ip":"123.171.*.89","terminal":{"mode":"U22","manufacture":"Allwinner","macAddress":"","imei":"","imsi":"","uid":"7d0607144c7****"},"timestamp":1552320921}

1.1 get_json_object :用来解析json字符串的一个字段

hive> select get_json_object(req_head,'$.terminal.uid') from tmp_yyb limit 5;
OK
e6dc1412aab*****
d8bd2a0cc0a*****
6a9bdc0f3bf*****
59722af35ab*****
7d0607144c7*****
Time taken: 0.122 seconds, Fetched: 5 row(s)

1.2 json_tuple: 用来解析json字符串中的多个字段

hive> select json_tuple(req_head,'curpage','client_ip') from tmp_yyb limit 5;
OK
android_search.html 114.102.*.169
android_suggestion.html 223.104.*.73
android_search.html 113.194.*.31
android_search.html 111.60.*.71
android_search.html 123.171.*.89

2. json数组

hive 对json数组没有默认的函数进行解析，这里要做的就是将json数组做一个转变

样例数据：

[
    "{\"recommendId\":\"w67EQf0IicA\",\"packageName\":\"com.tiange.grape\",\"source\":52423253,\"versionCode\":160,\"channelId\":\"\"}",
    "{\"recommendId\":\"w67EQf0IicB\",\"packageName\":\"com.tencent.abc\",\"source\":52453353,\"versionCode\":160,\"channelId\":\"\"}",
]

2.1 使用MapReduce转换为json_object的数组存入hive表，对于hive表来说就是array<string>类型

user_ip                 string                                      
ctime                   string                                      
event                   string                                      
product                 string                                      
req_head                string                                      
req_body                array<string>                               
cdate                   bigint

查询数组中第一个

hive> select get_json_object(req_body[0],'$.packageName') from tmp_yyb limit 5;
OK
com.tiange.grape
com.ss.android.article.news
com.huizheng.tcyyhz
com.baidu.searchbox
com.browser2345

查询数组中所有的，需要有一个列转行的操作，使用lateral view explode(req_body)将数组转换为多行

select cdate,
get_json_object(req_head,'$.terminal.uid') as uid,
get_json_object(rb,'$.packageName') as packageName
from tmp_yyb 
lateral view explode(req_body) x as rb
where  cdate ='20190315' and cdate = '20190315'

2.2 将json数组当做字符串进行一个拆分

样例数据：

{
    "errno": "0", 
    "count": "151", 
    "end_state": "1", 
    "data": [
        {
            "md5": "e0cf18080fe5bd78377c8d385a4d3932", 
            "pkg": "tv.danmaku.bili"
        }, 
        {
            "md5": "7d79c0194bf0effb737ec7147c685096", 
            "pkg": "oms.mmc.fortunetelling.measuringtools.naming"
        }
    ]
}

对应的hql语句

select t1.ctime,get_json_object(app,'$.pkg') as pkg
from
(select 
         ctime
        ,split(
            regexp_replace(
                regexp_extract(
                    get_json_object(rsp,"$.data") -- 获取data数组,格式[{json},{json}]
                    ,'^\\[(.+)\\]$'
                    ,1
                ) -- 删除字符串前后的[],格式{json},{json}
                ,'\\}\\,\\{'
                , '\\}\\|\\|\\{'
            ) -- 将josn字符串中的分隔符代换成||,格式{json}||{json}
            ,'\\|\\|'
        ) as str -- 按||分隔符切割成一个hive数组
from tmp_rsp) t1
lateral view explode(t1.str) xx as app
order by pkg,ctime asc
limit 100 -- 将hive数组转成行