先貼一下hive中get_json_object和json_tuple兩個函數的差別:
Hive中提供了兩種針對json資料格式解析的函數,即get_json_object(…)與json_tuple(…),理論不多說,直接上效果示意圖:
假設存在如下json資料對象,若使用hive環境可這麼設定:
set hivevar:msg={
“message”:”2015/12/08 09:14:4”,
“client”: “10.108.24.253”,
“server”: “passport.suning.com”,
“request”: “POST /ids/needVerifyCode HTTP/1.1”,
“server”: “passport.sing.co”,
“version”:”1”,
“timestamp”:”2015-12-08T01:14:43.273Z”,
“type”:”B2C”,”center”:”JSZC”,
“system”:”WAF”,”clientip”:”192.168.61.4”,
“host”:”wafprdweb03”,
“path”:”/usr/local/logs/waf.error.log”,
“redis”:”192.168.24.46”}
get_json_object函數
當使用get_json_object函數時,則可以這麼用:
select get_json_object(‘ hivevar:msg′,′ .server’) from test;
傳回:
passport.sing.com
其中,get_json_object函數第一個參數填寫json對象變量,第二個參數使用$表示json變量辨別,然後用 . 或 [] 讀取對象或數組;
json_tuple函數
當使用json_tuple對象時,可以顯著提高效率,一次擷取多個對象并且可以被組合使用,寫法如下:
select a.* from test lateral view json_tuple(‘${hivevar:msg}’,’server’,’host’) a as f1,f2;
傳回:
passport.sing.com wafprdweb03
其中,需要使用lateral view 視圖方法來寫,不需要加$标示符讀取對象,擷取的f1,f2對象可用于array()或map()等函數使用
以下是正文:
我有一個表tab1,表有兩個字段sub_id, sub_sku_info,表中有一條資料如下:
(注:第二個字段比較長,但這确實是一個字元串,請不要被換行欺騙):
sub_id : 101
sub_sku_info :
[{"skuId":"1017570","num":"2","price":5.8,"jd_price":23.9,"sale_price":5.8},{"skuId":"1329431","num":"1","price":38.38,"jd_price":59,"sale_price":36.84},{"skuId":"1381473","num":"1","price":8.5,"jd_price":39.8,"sale_price":8.5}]
讓我們逐漸來拆解這條資料:
1、将sub_sku_info中的“清單”拆成三行:
select sub_id, sku_info
from tab1
lateral view explode(split(substr(sub_sku_info, , length(sub_sku_info) - ), '},')) aa as sku_info;
結果:
2、補全每一行記錄,為第三步拆分做準備:
select t.sub_id, case when substr(t.sku_info, length(t.sku_info), ) = '}' then t.sku_info else concat(t.sku_info, '}') end as sku_info
from (
select sub_id, sku_info
from tab1
lateral view explode(split(substr(sub_sku_info, , length(sub_sku_info) - ), '},')) aa as sku_info) t;
結果:
3、将每一行的“字典”拆分成對應字段:
select m.sub_id, skuid, num, price, jd_price, sale_price
from (
select t.sub_id, case when substr(t.sku_info, length(t.sku_info), ) = '}' then t.sku_info else concat(t.sku_info, '}') end as sku_info
from (
select sub_id, sku_info
from tab1
lateral view explode(split(substr(sub_sku_info, , length(sub_sku_info) - ), '},')) aa as sku_info) t) m
lateral view json_tuple(m.sku_info, 'skuId', 'num', 'price', 'jd_price', 'sale_price') aa as skuid, num, price, jd_price, sale_price;
結果:
拆解完成。