[[396420]]

本文轉(zhuǎn)載自微信公眾號(hào)「五分鐘學(xué)大數(shù)據(jù)」，作者園陌。轉(zhuǎn)載本文請(qǐng)聯(lián)系五分鐘學(xué)大數(shù)據(jù)公眾號(hào)。

在Hive中會(huì)有很多數(shù)據(jù)是用Json格式來存儲(chǔ)的，如開發(fā)人員對(duì)APP上的頁面進(jìn)行埋點(diǎn)時(shí)，會(huì)將多個(gè)字段存放在一個(gè)json數(shù)組中，因此數(shù)據(jù)平臺(tái)調(diào)用數(shù)據(jù)時(shí)，要對(duì)埋點(diǎn)數(shù)據(jù)進(jìn)行解析。接下來就聊聊Hive中是如何解析json數(shù)據(jù)的。

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字段，也解析age字段，則可以這樣寫：

select  
get_json_object('{"name":"zhangsan","age":18}','$.name'), 
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é)果:

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

如果在使用json_tuple函數(shù)時(shí)加上$.就會(huì)解析失?。?/p>

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

結(jié)果:

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

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

Hive解析json數(shù)組

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

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

我們想把這個(gè)字段解析出來，形成如下的結(jié)構(gòu)：

要解析這個(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 字段中的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值：

我們要統(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é)果：

2. 按照hobby_id進(jìn)行分組聚合即可：

SELECT hobby_id ,count(1) client_num 
FROM hobbies_table  
LATERAL VIEW explode(id_list) tmp_table AS hobby_id 
group by hobby_id; 

結(jié)果：

介紹完 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é)果如下：

如果表中還有其他字段，我們可以根據(jù)其他字段篩選出符合結(jié)果的數(shù)據(jù)。

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