Hive自帶的json解析函數(shù)

1. get_json_object
   語法：get_json_object(json_string, '$.key')

說明：解析json的字符串json_string,返回path指定的內(nèi)容灰瞻。如果輸入的json字符串無效辕棚，那么返回NULL锻狗。這個(gè)函數(shù)每次只能返回一個(gè)數(shù)據(jù)項(xiàng)售睹。

示例：

select
get_json_object('{"name":"zhangsan","age":18}','$.name');
結(jié)果：

name
zhangsan
如果既要解析name字段蜻牢，也解析age字段稍坯，則可以這樣寫：

select
get_json_object('{"name":"zhangsan","age":18}','.age');
但是如果要解析的字段有很多似芝，再這樣寫就太麻煩了嵌赠，所以就有了 json_tuple 這個(gè)函數(shù)。

2. json_tuple
   語法： json_tuple(json_string, k1, k2 ...)

說明：解析json的字符串json_string,可指定多個(gè)json數(shù)據(jù)中的key忽妒，返回對(duì)應(yīng)的value玩裙。如果輸入的json字符串無效兼贸，那么返回NULL。

示例：

select
b.name
,b.age
from tableName a lateral view
json_tuple('{"name":"zhangsan","age":18}','name','age') b as name,age;
結(jié)果:

name age
zhangsan 18
注意：上面的json_tuple函數(shù)中沒有$.

如果在使用json_tuple函數(shù)時(shí)加上$.就會(huì)解析失敵越Α：

select
b.name
,b.age
from tableName a lateral view
json_tuple('{"name":"zhangsan","age":18}','.age') b as name,age;
結(jié)果:

name age
NULL NULL
字段全是NULL溶诞，所以json_tuple函數(shù)不需要加$.了，否則會(huì)解析不到决侈。

總結(jié)：json_tuple相當(dāng)于get_json_object的優(yōu)勢(shì)就是一次可以解析多個(gè)json字段很澄。但是如果我們有個(gè)json數(shù)組，這兩個(gè)函數(shù)都無法處理颜及。

Hive解析json數(shù)組
一甩苛、嵌套子查詢解析json數(shù)組

如果有一個(gè)hive表，表中 json_str 字段的內(nèi)容如下：

json_str
[{"website":"baidu.com","name":"百度"},{"website":"google.com","name":"谷歌"}]
我們想把這個(gè)字段解析出來俏站，形成如下的結(jié)構(gòu)：

website name
baidu.com 百度
google.com 谷歌
要解析這個(gè)json數(shù)組讯蒲，僅用上面介紹的兩個(gè)函數(shù)就解析不出來了，還需用到如下介紹的幾個(gè)函數(shù)：

explode函數(shù)
語法： explode(Array OR Map)

說明：explode()函數(shù)接收一個(gè)array或者map類型的數(shù)據(jù)作為輸入肄扎，然后將array或map里面的元素按照每行的形式輸出墨林，即將hive一列中復(fù)雜的array或者map結(jié)構(gòu)拆分成多行顯示，也被稱為列轉(zhuǎn)行函數(shù)犯祠。

示例：

-- 解析array
hive> select explode(array('A','B','C'));
OK
A
B
C
-- 解析map
hive> select explode(map('A',10,'B',20,'C',30));
OK
A 10
B 20
C 30
regexp_replace函數(shù)
語法: regexp_replace(string A, string B, string C)

說明：將字符串A中的符合java正則表達(dá)式B的部分替換為C旭等。注意，在有些情況下要使用轉(zhuǎn)義字符衡载，類似oracle中的regexp_replace函數(shù)搔耕。

示例：

hive> select regexp_replace('foobar', 'oo|ar', '');
OK
fb
上述示例將字符串中的 oo 或 ar 替換為''。

有了上述幾個(gè)函數(shù)痰娱，接下來我們來解析json_str字段的內(nèi)容：

先將json數(shù)組中的元素解析出來弃榨，轉(zhuǎn)化為每行顯示：

hive> SELECT explode(split(regexp_replace(regexp_replace('[{"website":"baidu.com","name":"百度"},{"website":"google.com","name":"谷歌"}]', '\[|\]',''),'\}\,\{','\}\;\{'),'\;'));
OK
{"website":"baidu.com","name":"百度"}
{"website":"google.com","name":"谷歌"}
對(duì)上述sql進(jìn)行簡要說明：

SELECT explode(split(regexp_replace(regexp_replace('[{"website":"baidu.com","name":"百度"},{"website":"google.com","name":"谷歌"}]', '\[|\]' , ''), 將json數(shù)組兩邊的中括號(hào)去掉'\}\,\{' , '\}\;\{'), 將json數(shù)組元素之間的逗號(hào)換成分號(hào)'\;') 以分號(hào)作為分隔符(split函數(shù)以分號(hào)作為分隔));
為什么要將json數(shù)組元素之間的逗號(hào)換成分號(hào)？
因?yàn)樵貎?nèi)的分隔也是逗號(hào)梨睁，如果不將元素之間的逗號(hào)換掉的話鲸睛，后面用split函數(shù)分隔時(shí)也會(huì)把元素內(nèi)的數(shù)據(jù)給分隔，這不是我們想要的結(jié)果坡贺。

上步已經(jīng)把一個(gè)json數(shù)組轉(zhuǎn)化為多個(gè)json字符串了官辈，接下來結(jié)合son_tuple函數(shù)來解析json里面的字段：

select
json_tuple(explode(split(
regexp_replace(regexp_replace('[{"website":"baidu.com","name":"百度"},{"website":"google.com","name":"谷歌"}]', '\[|\]',''),'\}\,\{','\}\;\{'),'\;'))
, 'website', 'name') ;
執(zhí)行上述語句，結(jié)果報(bào)錯(cuò)了：
FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions

意思是UDTF函數(shù)不能寫在別的函數(shù)內(nèi)遍坟，也就是這里的explode函數(shù)不能寫在json_tuple里面拳亿。

既然explode函數(shù)不能寫在別的json_tuple里面，那我們可以用子查詢方式政鼠，如下所示：

select json_tuple(json, 'website', 'name')
from (
select explode(split(regexp_replace(regexp_replace('[{"website":"baidu.com","name":"百度"},{"website":"google.com","name":"谷歌"}]', '\[|\]',''),'\}\,\{','\}\;\{'),'\;'))
as json) t;
執(zhí)行上述語句风瘦，沒有報(bào)錯(cuò)，執(zhí)行結(jié)果如下：

www.baidu.com 百度
google.com 谷歌
二 使用 lateral view 解析json數(shù)組

hive表中 goods_id 和 json_str 字段的內(nèi)容如下：

goods_id json_str
1,2,3 [{"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"},{"source":"jd","monthSales":2090,"userCount":78981,"score":"9.8"},{"source":"jdmart","monthSales":6987,"userCount":1600,"score":"9.0"}]
目的：把 goods_id 字段和 json_str 字段中的monthSales解析出來公般。

下面我們就開始解析：

拆分goods_id字段及將json數(shù)組轉(zhuǎn)化成多個(gè)json字符串：

select
explode(split(goods_id,',')) as good_id,
explode(split(regexp_replace(regexp_replace(json_str , '\[|\]',''),'\}\,\{','\}\;\{'),'\;'))
as sale_info
from tableName;
執(zhí)行上述語句万搔，結(jié)果報(bào)錯(cuò)：
FAILED: SemanticException 3:0 Only a single expression in the SELECT clause is supported with UDTF's. Error encountered near token 'sale_info'

意思是用UDTF的時(shí)候，SELECT 只支持一個(gè)字段官帘。而上述語句select中有兩個(gè)字段瞬雹，所以報(bào)錯(cuò)了。

那怎么辦呢刽虹，要解決這個(gè)問題酗捌，還得再介紹一個(gè)hive語法：

lateral view
lateral view用于和split、explode等UDTF一起使用的涌哲，能將一行數(shù)據(jù)拆分成多行數(shù)據(jù)胖缤，在此基礎(chǔ)上可以對(duì)拆分的數(shù)據(jù)進(jìn)行聚合，lateral view首先為原始表的每行調(diào)用UDTF阀圾，UDTF會(huì)把一行拆分成一行或者多行哪廓，lateral view在把結(jié)果組合，產(chǎn)生一個(gè)支持別名表的虛擬表初烘。

示例：

假設(shè)我們有一張用戶興趣愛好表 hobbies_table涡真，它有兩列數(shù)據(jù)，第一列是name肾筐，第二列是用戶興趣愛好的id_list哆料，是一個(gè)數(shù)組，存儲(chǔ)興趣愛好的id值：

name id_list
zhangsan [1,2,3]
lisi [3,4,5]
我們要統(tǒng)計(jì)所有興趣id在所有用戶中出現(xiàn)的次數(shù)：

對(duì)興趣id進(jìn)行解析：

SELECT name, hobby_id
FROM hobbies_table
LATERAL VIEW explode(id_list) tmp_table AS hobby_id;
上述sql執(zhí)行結(jié)果：

name hobby_id
zhangsan 1
zhangsan 2
zhangsan 3
lisi 3
lisi 4
lisi 5
將id_list拆分完之后統(tǒng)計(jì)在用戶中出現(xiàn)的次數(shù)就簡單了吗铐，按照hobby_id進(jìn)行分組聚合即可：

SELECT hobby_id ,count(name) client_num
FROM hobbies_table
LATERAL VIEW explode(id_list) tmp_table AS hobby_id
group by hobby_id;
結(jié)果：

hobby_id client_num
1 1
2 1
3 2
4 1
5 1
介紹完 lateral view 之后东亦，我們?cè)賮斫鉀Q上面遇到的用UDTF的時(shí)候，SELECT 只支持一個(gè)字段的問題：

select good_id,get_json_object(sale_json,'$.monthSales') as monthSales
from tableName
LATERAL VIEW explode(split(goods_id,','))goods as good_id
LATERAL VIEW explode(split(regexp_replace(regexp_replace(json_str , '\[|\]',''),'\}\,\{','\}\;\{'),'\;')) sales as sale_json;
注意：上述語句是三個(gè)表笛卡爾積的結(jié)果唬渗，所以此方式適用于數(shù)據(jù)量不是很大的情況讥此。

上述語句執(zhí)行結(jié)果如下：

goods_id monthSales
1 4900
1 2090
1 6987
2 4900
2 2090
2 6987
3 4900
3 2090
3 6987
如果表中還有其他字段，我們可以根據(jù)其他字段篩選出符合結(jié)果的數(shù)據(jù)谣妻。

總結(jié)：lateral view通常和UDTF一起出現(xiàn)萄喳，為了解決UDTF不允許在select存在多個(gè)字段的問題。