[[396420]]

本文轉載自微信公眾號「五分鐘學大數據」，作者園陌。轉載本文請聯系五分鐘學大數據公眾號。

在Hive中會有很多數據是用Json格式來存儲的，如開發人員對APP上的頁面進行埋點時，會將多個字段存放在一個json數組中，因此數據平臺調用數據時，要對埋點數據進行解析。接下來就聊聊Hive中是如何解析json數據的。

Hive自帶的json解析函數

1. get_json_object

• 語法：get_json_object(json_string, '$.key')
• 說明：解析json的字符串json_string,返回path指定的內容。如果輸入的json字符串無效，那么返回NULL。這個函數每次只能返回一個數據項。
• 示例：

select  
get_json_object('{"name":"zhangsan","age":18}','$.name');  

結果：

name
zhangsan

如果既要解析name字段，也解析age字段，則可以這樣寫：

select  
get_json_object('{"name":"zhangsan","age":18}','$.name'), 
get_json_object('{"name":"zhangsan","age":18}','$.age'); 

但是如果要解析的字段有很多，再這樣寫就太麻煩了，所以就有了 json_tuple 這個函數。

2. json_tuple

• 語法：json_tuple(json_string, k1, k2 ...)
• 說明：解析json的字符串json_string,可指定多個json數據中的key，返回對應的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; 

• 結果:

name	age
zhangsan	18

注意：上面的json_tuple函數中沒有$.

如果在使用json_tuple函數時加上$.就會解析失敗：

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

結果:

name	age
NULL	NULL

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

總結：json_tuple相當于get_json_object的優勢就是一次可以解析多個json字段。但是如果我們有個json數組，這兩個函數都無法處理。

Hive解析json數組

一、嵌套子查詢解析json數組

如果有一個hive表，表中 json_str 字段的內容如下：

json_str
[{"website":"baidu.com","name":"百度"},{"website":"google.com","name":"谷歌"}]

我們想把這個字段解析出來，形成如下的結構：

website	name
baidu.com	百度
google.com	谷歌

要解析這個json數組，僅用上面介紹的兩個函數就解析不出來了，還需用到如下介紹的幾個函數：

explode函數

• 語法：explode(Array OR Map)
• 說明：explode()函數接收一個array或者map類型的數據作為輸入，然后將array或map里面的元素按照每行的形式輸出，即將hive一列中復雜的array或者map結構拆分成多行顯示，也被稱為列轉行函數。
• 示例：

-- 解析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函數

• 語法: regexp_replace(string A, string B, string C)
• 說明：將字符串A中的符合java正則表達式B的部分替換為C。注意，在有些情況下要使用轉義字符，類似oracle中的regexp_replace函數。
• 示例：

hive> select regexp_replace('foobar', 'oo|ar', '');  
OK 
fb 

上述示例將字符串中的 oo 或 ar 替換為''。

有了上述幾個函數，接下來我們來解析json_str字段的內容：

先將json數組中的元素解析出來，轉化為每行顯示：

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":"谷歌"} 

對上述sql進行簡要說明：

SELECT explode(split( 
    regexp_replace( 
        regexp_replace( 
            '[ 
                {"website":"baidu.com","name":"百度"}, 
                {"website":"google.com","name":"谷歌"} 
            ]',  
            '\\[|\\]' , ''), 將json數組兩邊的中括號去掉 
             
              '\\}\\,\\{' , '\\}\\;\\{'), 將json數組元素之間的逗號換成分號 
                 
                 '\\;') 以分號作為分隔符(split函數以分號作為分隔) 
          );   

為什么要將json數組元素之間的逗號換成分號?

因為元素內的分隔也是逗號，如果不將元素之間的逗號換掉的話，后面用split函數分隔時也會把元素內的數據給分隔，這不是我們想要的結果。

上步已經把一個json數組轉化為多個json字符串了，接下來結合son_tuple函數來解析json里面的字段：

select  
json_tuple(explode(split( 
regexp_replace(regexp_replace('[{"website":"baidu.com","name":"百度"},{"website":"google.com","name":"谷歌"}]', '\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{'),'\\;'))  
) , 'website', 'name') ; 

執行上述語句，結果報錯了：

FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions 

意思是UDTF函數不能寫在別的函數內，也就是這里的explode函數不能寫在json_tuple里面。

既然explode函數不能寫在別的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; 

執行上述語句，沒有報錯，執行結果如下：

www.baidu.com   百度 
google.com      谷歌 

二 使用 lateral view 解析json數組

hive表中 goods_id 和 json_str 字段的內容如下：

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數組轉化成多個json字符串：

select  
explode(split(goods_id,',')) as good_id, 
explode(split(regexp_replace(regexp_replace(json_str , '\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{'),'\\;'))  
as sale_info  
from tableName; 

執行上述語句，結果報錯：

FAILED: SemanticException 3:0 Only a single expression in the SELECT clause is supported with UDTF's. Error encountered near token 'sale_info' 

意思是用UDTF的時候，SELECT 只支持一個字段。而上述語句select中有兩個字段，所以報錯了。

那怎么辦呢，要解決這個問題，還得再介紹一個hive語法：

lateral view

lateral view用于和split、explode等UDTF一起使用的，能將一行數據拆分成多行數據，在此基礎上可以對拆分的數據進行聚合，lateral view首先為原始表的每行調用UDTF，UDTF會把一行拆分成一行或者多行，lateral view在把結果組合，產生一個支持別名表的虛擬表。

• 示例：

假設我們有一張用戶興趣愛好表 hobbies_table，它有兩列數據，第一列是name，第二列是用戶興趣愛好的id_list，是一個數組，存儲興趣愛好的id值：

name	id_list
zhangsan	[1,2,3]
lisi	[3,4,5]

我們要統計所有興趣id在所有用戶中出現的次數：

對興趣id進行解析：

SELECT name, hobby_id  
FROM hobbies_table  
LATERAL VIEW explode(id_list) tmp_table AS hobby_id; 

上述sql執行結果：

name	hobby_id
zhangsan	1
zhangsan	2
zhangsan	3
lisi	3
lisi	4
lisi	5

2. 按照hobby_id進行分組聚合即可：

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

結果：

hobby_id	client_num
1	1
2	1
3	2
4	1
5	1

介紹完 lateral view 之后，我們再來解決上面遇到的用UDTF的時候，SELECT 只支持一個字段的問題：

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; 

注意：上述語句是三個表笛卡爾積的結果，所以此方式適用于數據量不是很大的情況。

上述語句執行結果如下：

goods_id	monthSales
1	4900
1	2090
1	6987
2	4900
2	2090
2	6987
3	4900
3	2090
3	6987

如果表中還有其他字段，我們可以根據其他字段篩選出符合結果的數據。

 

總結：lateral view通常和UDTF一起出現，為了解決UDTF不允許在select存在多個字段的問題。