背景

大數(shù)據(jù)的 ETL(Extract-Transfer-Load) 過(guò)程的 Transfer 階段，需要對(duì) json 串?dāng)?shù)據(jù)進(jìn)行轉(zhuǎn)換“拍平”處理。

親測(cè)!超好用 ??Hive 內(nèi)置的 json 解析函數(shù)?? 一文中詳細(xì)介紹過(guò) get_json_object 和 json_tuple 函數(shù)如何對(duì) json 串進(jìn)行有效解析，但美中不足的是這兩個(gè)函數(shù)都無(wú)法解析 json 數(shù)組，只能解析單個(gè) json 串。

這里將會(huì)介紹 Hive 中常用于 json 數(shù)組的解析函數(shù)及詳細(xì)使用方法。

json數(shù)組解析：需求1

數(shù)據(jù)準(zhǔn)備

例如：Hive中有一張 test_json 表，表中 json_data 字段的內(nèi)容如下：

基于以上的 json_data 數(shù)據(jù)，現(xiàn)需要將以上 json 串?dāng)?shù)據(jù)解析為如下結(jié)構(gòu)數(shù)據(jù)：

在進(jìn)行解析之前，先來(lái)了解下面兩個(gè)函數(shù)的使用方法。

函數(shù)運(yùn)用

1、explode函數(shù)

語(yǔ)法

explode(Array|Map)

說(shuō)明

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

即將 Hive 一列中復(fù)雜的 array 或者 map 結(jié)構(gòu)拆分成多行顯示，也被稱(chēng)為列轉(zhuǎn)行函數(shù)。

舉例

array測(cè)試sql語(yǔ)句：

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

執(zhí)行結(jié)果：

map測(cè)試sql語(yǔ)句：

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

執(zhí)行結(jié)果：

2、regexp_replace函數(shù)

語(yǔ)法

regexp_replace(str A, str B, str C)

說(shuō)明

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

注意：當(dāng)字符串 A 中有一些特殊字符時(shí)，在正則表達(dá)式 B 中要使用轉(zhuǎn)義字符。

舉例

sql語(yǔ)句：

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

執(zhí)行結(jié)果：

3、 具體函數(shù)運(yùn)用

了解 explode 函數(shù)與 regexp_replace 函數(shù)的使用規(guī)則后，現(xiàn)在來(lái)完成上面數(shù)據(jù)準(zhǔn)備中提出的解析需求。

第一步解析：json數(shù)組拆分成多行

sql語(yǔ)句：

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數(shù)組兩邊的中括號(hào)去掉
            
              '\\}\\,\\{' , '\\}\\;\\{'), 將json數(shù)組元素之間的逗號(hào)換成分號(hào)
                
                 '\\;') 以分號(hào)作為分隔符(split函數(shù)以分號(hào)作為分隔)
          ); 

執(zhí)行結(jié)果：

第二步解析：json數(shù)組key轉(zhuǎn)列字段

sql語(yǔ)句：

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;

執(zhí)行結(jié)果：

json數(shù)組解析：需求2

數(shù)據(jù)準(zhǔn)備

例如：

Hive中有一張 data_json 表，表中 goods_id 和 str_data 字段的內(nèi)容如下：

基于以上的 goods_id 和 str_data 數(shù)據(jù)，現(xiàn)需要將以上 json 串?dāng)?shù)據(jù)解析為如下結(jié)構(gòu)數(shù)據(jù)：

在進(jìn)行解析之前，先來(lái)了解下面兩個(gè)函數(shù)的使用方法。

函數(shù)運(yùn)用

1、 lateral view函數(shù)

說(shuō)明

lateral view 用于和 split, explode 等 UDTF 一起使用，它能夠?qū)⒁涣袛?shù)據(jù)拆成多行數(shù)據(jù)，在此基礎(chǔ)上可以對(duì)拆分后的數(shù)據(jù)進(jìn)行聚合。

lateral view 首先為原始表的每行調(diào)用 UDTF，UDTF 會(huì)把一行拆分成一行或者多行，lateral view 在把結(jié)果組合，產(chǎn)生一個(gè)支持別名表的虛擬表。

舉例

例如：Hive 中有一張 page_ads 表，表數(shù)據(jù)結(jié)構(gòu)如下：

page_name 代表頁(yè)面名稱(chēng)，ads_id 代表投放廣告的所屬 id，多個(gè) id之間使用逗號(hào)分隔。

需求：統(tǒng)計(jì)所有廣告 id 在所有頁(yè)面中出現(xiàn)的次數(shù)。

第一步解析：拆分廣告id

拆分sql語(yǔ)句：

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

拆分結(jié)果：

第二步解析：聚合統(tǒng)計(jì)

聚合統(tǒng)計(jì)sql語(yǔ)句：

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

統(tǒng)計(jì)結(jié)果：

2、 具體函數(shù)運(yùn)用

解析 data_json 表的sql語(yǔ)句如下：

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;

注意：

上述語(yǔ)句是 3*3 笛卡爾積的結(jié)果，所以此方式適用于數(shù)據(jù)量不是很大的情況。

執(zhí)行結(jié)果如下：