Hive 完美解析 Json 數組的函數
背景
大數據的 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 笛卡爾積的結果，所以此方式適用于數據量不是很大的情況。
執行結果如下：
