Hive解析Json陣列超全講解

五分鐘學大資料發表於2021-05-12
HiveJSON陣列

在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;
  • 結果:
nameage
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;

結果:

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

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

websitename
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欄位的內容：

    1. 先將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函式分隔時也會把元素內的資料給分隔，這不是我們想要的結果。

    1. 上步已經把一個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_idjson_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解析出來。

下面我們就開始解析：

  1. 拆分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值：

nameid_list
zhangsan [1,2,3]
lisi [3,4,5]

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

  1. 對興趣id進行解析：
SELECT name, hobby_id 
FROM hobbies_table 
LATERAL VIEW explode(id_list) tmp_table AS hobby_id;

上述sql執行結果：

namehobby_id
zhangsan 1
zhangsan 2
zhangsan 3
lisi 3
lisi 4
lisi 5

將id_list拆分完之後統計在使用者中出現的次數就簡單了，按照hobby_id進行分組聚合即可：

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

結果：

hobby_idclient_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_idmonthSales
1 4900
1 2090
1 6987
2 4900
2 2090
2 6987
3 4900
3 2090
3 6987

如果表中還有其他欄位，我們可以根據其他欄位篩選出符合結果的資料。

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

 

相關文章