背景

大數據的 ETL(Extract-Transfer-Load) 過程的 Transfer 階段，需要對 json 串數據進行轉換“拍平”處理。

親測！超好用 Hive 內置的 json 解析函數一文中詳細介紹過 get_json_object 和 json_tuple 函數如何對 json 串進行有效解析，但美中不足的是這兩個函數都無法解析 json 數組，只能解析單個 json 串。

這里將會介紹 Hive 中常用于 json 數組的解析函數及詳細使用方法。

json數組解析：需求1

數據準備

例如：Hive中有一張 test_json 表，表中 json_data 字段的內容如下：

基于以上的 json_data 數據，現需要將以上 json 串數據解析為如下結構數據：

在進行解析之前，先來了解下面兩個函數的使用方法。

函數運用

1. explode函數

語法

explode(Array|Map)

說明

explode()函數接收一個 array 或者 map 類型的數據作為輸入，然后將 array 或 map 里面的元素按照每行的形式輸出。

即將 Hive 一列中復雜的 array 或者 map 結構拆分成多行顯示，也被稱為列轉行函數。

舉例

array測試sql語句：

select explode(array('user_id','name','age'));

執行結果：

map測試sql語句：

select explode(map('user_id',1,'name','rocky','age',18));

執行結果：

2. regexp_replace函數

語法

regexp_replace(str A, str B, str C)

說明

語法含義：將字符串 A 中的符合正則表達式 B 的部分替換為 C。

注意：當字符串 A 中有一些特殊字符時，在正則表達式 B 中要使用轉義字符。

舉例

sql語句：

select regexp_replace('hello world!', '\ |\!', '');

執行結果：

3. 具體函數運用

了解 explode 函數與 regexp_replace 函數的使用規則后，現在來完成上面數據準備中提出的解析需求。

第一步解析：json數組拆分成多行

sql語句：

SELECT explode(split(
     regexp_replace(
         regexp_replace(
            '[
                {"user_id":"1","name":"小琳","age":16},
                {"user_id":"2","name":"小劉","age":18},
                {"user_id":"3","name":"小明","age":20}
            ]', 
            '\[|\]' , ''), 將json數組兩邊的中括號去掉
            
              '\}\,\{' , '\}\;\{'), 將json數組元素之間的逗號換成分號
                
                 '\;') 以分號作為分隔符(split函數以分號作為分隔)
          );

執行結果：

第二步解析：json數組key轉列字段

sql語句：

select json_tuple(json, 'user_id', 'name', 'age') 
 from (select explode(split(
     regexp_replace(
      regexp_replace(
       '[
        {"user_id":"1","name":"小琳","age":16},
        {"user_id":"2","name":"小劉","age":18},
        {"user_id":"3","name":"小明","age":20}
       ]',
       '\[|\]' , ''), 
        '\}\,\{' , '\}\;\{'), 
          '\;') 
     )as json) tmp;

執行結果：

json數組解析：需求2

數據準備

例如：

Hive中有一張 data_json 表，表中 goods_id 和 str_data 字段的內容如下：

基于以上的 goods_id 和 str_data 數據，現需要將以上 json 串數據解析為如下結構數據：

在進行解析之前，先來了解下面兩個函數的使用方法。

函數運用

1. lateral view函數

說明

lateral view 用于和 split, explode 等 UDTF 一起使用，它能夠將一列數據拆成多行數據，在此基礎上可以對拆分后的數據進行聚合。

lateral view 首先為原始表的每行調用 UDTF，UDTF 會把一行拆分成一行或者多行，lateral view 在把結果組合，產生一個支持別名表的虛擬表。

舉例

例如：Hive 中有一張 page_ads 表，表數據結構如下：

page_name 代表頁面名稱，ads_id 代表投放廣告的所屬 id，多個 id之間使用逗號分隔。

需求：統計所有廣告 id 在所有頁面中出現的次數。

第一步解析：拆分廣告id

拆分sql語句：

SELECT page_name, ads_id 
    FROM page_ads LATERAL VIEW explode(ads_id) adTable AS adid;

拆分結果：

第二步解析：聚合統計

聚合統計sql語句：

SELECT adid, count(1) 
    FROM page_ads LATERAL VIEW explode(ads_id) adTable AS adid
GROUP BY adid;

統計結果：

2. 具體函數運用

解析 data_json 表的sql語句如下：

select goods_id,get_json_object(sale_json,'$.sold') as sold
from data_json 
  LATERAL VIEW explode(split(goods_id,','))goods as goods_id 
  LATERAL VIEW explode(split(
         regexp_replace(
           regexp_replace(json_str , '\[|\]',''),'\}\,\{','\}\;\{'),'\;')) sales as sale_json;

注意：

上述語句是 3*3 笛卡爾積的結果，所以此方式適用于數據量不是很大的情況。

執行結果如下：