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数组转成行
