hive中json字符串(get_json_object与json_tuple)及url解析（parse_url）

json字符串数据：

select xjson
from ods.ods_visit_new a1
where dt='2019-10-26' limit 3;
OK
{"type":"click","time":1572019190,"userinfoData":{"isNewUser":0},"requestData":{"oVersion":"9","screen":"360_772","reportVersion":"1.0.0","ip":"39.186.43.126","referer":"https://w.weipaitang.com/webApp/memberBean/signIn?r=member_center\u0026c=member_center","cVersion":"3.4.6","userAgent":"Mozilla%2F5.0%20(Linux%3B%20Android%209%3B%20vivo%20NEX%20S%20Build%2FPKQ1.181016.001%3B%20wv)%20AppleWebKit%2F537.36%20(KHTML%2C%20like%20Gecko)%20Version%2F4.0%20Chrome%2F66.0.3359.126%20MQQBrowser%2F6.2%20TBS%2F044904%20Mobile%20Safari%2F537.36%20NetType%2FNETWORK_WIFI%20Language%2Fzh_CN%20WptMessenger%2F3.4.6%20Channel%2Fyingyongbao%20DeviceId%2F869456032409553","webType":"android","deviceId":"869456032409553","appId":"wx2aa1f6beaa714b7c","href":"https://w.weipaitang.com/webApp/activity/PunchCheckin?r=member_center-huiyuan_daka\u0026c=member_center-huiyuan_daka\u0026isJoin=0","identity":"cd06ebacdb631a295886d95a9784a56c","sessionId":"20180921144046_109fvgusjn","os":"android","owner":"h5","platform":"app"},"usid":"2308073","data":{"id":77,"subType":"cardSignApply","clickData":{"configId":90}},"sc":"yingyongbao","uuri":"1510262036FxajpJ","clickData":{"configId":90},"page":{"pos":"","pageName":"https://w.weipaitang.com/webApp/activity/PunchCheckin","sc":"yingyongbao","r":"member_center-huiyuan_daka"},"user":{"isNewUser":0,"level":4,"uuri":"1510262036FxajpJ","usid":"2308073"},"uusid":"ztJccMIZ7MJ1RL18JVvXzzoCwoMKkR4B8lYgPk2Kn9E="}
{"time":1572019190,"userinfoData":{"isNewUser":0},"requestData":{"oVersion":"8.1.0","cVersion":"7.0.7.1521","identity":"c99f4d2501c34c1f374394f4a58ae59b","reportVersion":"1.0.0","deviceId":"","referer":"https://w.weipaitang.com/webApp/systemNotice?r=menu_my-menu_message\u0026c=menu_my-menu_message\u0026type=system\u0026systemUnread=0\u0026adminUnread=5\u0026needAjax=1","os":"android","owner":"h5","screen":"360_780","isBack":1,"ip":"111.37.247.184","userAgent":"Mozilla%2F5.0%20(Linux%3B%20Android%208.1.0%3B%20PBEM00%20Build%2FOPM1.171019.026%3B%20wv)%20AppleWebKit%2F537.36%20(KHTML%2C%20like%20Gecko)%20Version%2F4.0%20Chrome%2F66.0.3359.126%20MQQBrowser%2F6.2%20TBS%2F045005%20Mobile%20Safari%2F537.36%20MMWEBID%2F2998%20MicroMessenger%2F7.0.7.1521(0x2700073A)%20Process%2Ftools%20NetType%2FWIFI%20Language%2Fzh_CN","sessionId":"20190912183059_28x0wxwq9z","webType":"wechat","appId":"wx2aa1f6beaa714b7c","platform":"wechat","href":"https://w.weipaitang.com/webApp/activity/normApply/index?r=seller_promotion\u0026c=seller_promotion"},"usid":"16637574","user":{"usid":"16637574","isNewUser":0,"level":1,"uuri":"1710151113o8RlAw"},"uusid":"o5dEiiC+eiFatWH8M1K+strvY+3KkhF+a8ydMOhwt6s=","uuri":"1710151113o8RlAw","type":"visit","page":{"r":"seller_promotion","pos":"","pageName":"https://w.weipaitang.com/webApp/activity/normApply/index","sc":""},"data":{"subType":""}}
{"userinfoData":{"isNewUser":1},"requestData":{"isBack":1,"platform":"app","userAgent":"Mozilla%2F5.0%20(Linux%3B%20Android%209%3B%20VOG-AL00%20Build%2FHUAWEIVOG-AL00%3B%20wv)%20AppleWebKit%2F537.36%20(KHTML%2C%20like%20Gecko)%20Version%2F4.0%20Chrome%2F66.0.3359.126%20MQQBrowser%2F6.2%20TBS%2F045005%20Mobile%20Safari%2F537.36%20NetType%2FNETWORK_WIFI%20Language%2Fzh_CN%20WptMessenger%2F3.4.5%20Channel%2Fyingyongbao%20wptAid%2Fyingyongbao%20DeviceId%2F861982046932395%20identity%2F62bd31086630a416fd84e0fc6b55d73f","reportVersion":"1.0.0","cVersion":"3.4.5","sessionId":"20191015231123_55is8lf7c1","webType":"android","referer":"https://w.weipaitang.com/webApp/activity/saleLive/A20191024IWO5UXWMMP?r=menu_newHome-sjactivity_all_home_327\u0026c=menu_newHome-sjactivity_all_home_327\u0026pagebannerid=3541","os":"android","screen":"360_780","href":"https://w.weipaitang.com/webApp/activity/saleLive/A20191024IWO5UXWMMP?r=menu_newHome-sjactivity_all_home_327\u0026c=menu_newHome-sjactivity_all_home_327\u0026pagebannerid=3541","wptAid":"yingyongbao","identity":"62bd31086630a416fd84e0fc6b55d73f","owner":"h5","deviceId":"861982046932395","ip":"125.77.67.56","appId":"wx2aa1f6beaa714b7c","oVersion":"9"},"page":{"pageName":"https://w.weipaitang.com/webApp/activity/saleLive/:uri","sc":"yingyongbao","r":"menu_newHome-sjactivity_all_home_327","pos":""},"user":{"level":0,"uuri":"1910150947WyfKvT","usid":"47265958","isNewUser":1},"sc":"yingyongbao","uuri":"1910150947WyfKvT","type":"visit","time":1572019191,"data":{"subType":""},"uusid":"eAeEwT7+uxJKYbOm2wg6lzdG/0Duio/WcaLwDgmUUUE=","usid":"47265958"}
Time taken: 0.24 seconds, Fetched: 3 row(s)

1、get_json_object
函数的作用：用来解析json字符串的一个字段。

select get_json_object(xjson,'$.user'),
       get_json_object(xjson,'$.user.uuri'),
       get_json_object(xjson,'$.usid')
  from ods.ods_visit_new a1
 where dt='2019-10-26' limit 9;

使用说明：
get_json_object(hive表字段名,'$.[一级Key].[二级key]..[N级key]')

2、json_tuple
函数的作用：用来解析json字符串中的多个字段

select
a2.* 
from ods.ods_visit_new a1
lateral view json_tuple(a1.xjson, 'user', 'requestData') a2 as f1, f2
where dt='2019-10-26'
limit 9;

使用说明：
lateral view json_tuple(hive表字段名, '一级Key1', '一级Key2') a2 as [列别名1], [列别名2]
where条件要放在lateral语句后
目前，尚不知道如何用这种方法取二级key。

3、parse_url函数使用

parse_url('http://facebook.com/path/p1.php?query=1', 'HOST')-->返回'facebook.com' ， 
parse_url('http://facebook.com/path/p1.php?query=1', 'PATH')-->返回'/path/p1.php' ， 
parse_url('http://facebook.com/path/p1.php?query=1', 'QUERY')-->返回'query=1'， 

或，可以指定key来返回特定参数，key的格式是QUERY:， 
例如：QUERY:k1 

parse_url('http://facebook.com/path/p1.php?query=1#Ref', 'REF')-->返回'Ref' 
parse_url('http://facebook.com/path/p1.php?query=1#Ref', 'PROTOCOL')-->返回'http'