背景

在大數(shù)據(jù) ETL(Extract-Transfer-Load) 過程中，經(jīng)常需要從不同的數(shù)據(jù)源來提取數(shù)據(jù)進(jìn)行加工處理，比較常見的是從 Mysql 數(shù)據(jù)庫來提取數(shù)據(jù)，而 Mysql 數(shù)據(jù)庫中數(shù)據(jù)存儲(chǔ)的比較常見方式是使用 json 串進(jìn)行存儲(chǔ)。

通過大數(shù)據(jù)加工處理出來的數(shù)據(jù)是需要具有可直觀分析的特點(diǎn)，可從數(shù)據(jù)分析中挖掘出商業(yè)價(jià)值的。

因此在數(shù)據(jù)預(yù)處理層需要將 json 串進(jìn)行“拍平”處理，所謂“拍平”是指將 json 中的 key 轉(zhuǎn)換為表的列字段，其 key 對(duì)應(yīng)的 value 值則為列字段對(duì)應(yīng)的值。

“拍平”的處理行業(yè)內(nèi)也可稱為“行轉(zhuǎn)列”處理，我舉個(gè)例子你就能明白什么是行轉(zhuǎn)列了。

舉例：

user表字段如下：

現(xiàn)需要將 user 表中字段 detail_info 中的 json 串值，以每個(gè) key 作為 user_detail_info 表的字段來進(jìn)行存儲(chǔ)。

實(shí)現(xiàn)的 user_detail_info 表字段如下：

從 user 表到 user_detail_info 表的轉(zhuǎn)換，就是“行轉(zhuǎn)列”的過程。

你是否會(huì)好奇，在 Hive 中這個(gè)過程是如何實(shí)現(xiàn)的呢?

下文會(huì)解答你的疑惑。

Hive內(nèi)置的json解析函數(shù)：get_json_object

語法：

get_json_object(json_string, '$.column')

說明：

解析 json 的字符串 json_string, 返回 path 指定的內(nèi)容。如果輸入的 json 字符串無效，結(jié)果返回 NULL。

這個(gè)函數(shù)每次只能返回一個(gè)數(shù)據(jù)項(xiàng)。

舉例：

test_data = '{"name": "zhangsan",
              "age": 18, 
              "preference": "music"}'

查詢sql語句：

select get_json_object(test_data,'$.preference');

解析結(jié)果：

如果需要同時(shí)解析 age, preference 這兩個(gè)字段。

sql語句如下：

select get_json_object(test_data,'$.age'),get_json_object(test_data,'$.preference');

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

如果需要同時(shí)解析的字段很多，很顯然使用這種方式寫就比較麻煩了，這時(shí)候 json_tuple 這個(gè)函數(shù)是個(gè)更好的選擇。

Hive內(nèi)置的json解析函數(shù)：json_tuple

語法：

json_tuple(json_string, column1, column2, column3 ...)

說明：

解析 json 的字符串 json_string,可同時(shí)指定多個(gè) json 數(shù)據(jù)中的 column，返回對(duì)應(yīng)的 value。如果輸入的 json 字符串無效，結(jié)果返回 NULL。

舉例：

例如：test_table1 表的 data 字段存儲(chǔ)的是以下 json 串信息，現(xiàn)在想要獲取這個(gè) json 串的每個(gè) key 并將其對(duì)應(yīng)的 value 值查詢出來。

(1). 準(zhǔn)備 test_table1 表 data 字段的 json 數(shù)據(jù)

data = '{
   "name": "rocky",
   "age": 20,
   "prefer": "dance",
   "height": 1.8,
   "nation": "China"
}'

(2). sql查詢語句

select t1.name,
        t1.age,
        t1.prefer,
        t1.height,
        t1.nation
 from (select data
             from test_table1
          ) t0
          lateral view json_tuple(t0.data,
                  'name',
                  'age',
                  'prefer',
                  'height',
                  'nation'
              ) t1 as name,age,prefer,height,nation;

解析結(jié)果：

• get_json_object函數(shù) & json_tuple函數(shù)。
• get_json_object 函數(shù)的使用語法中，使用到$.加上 json 的 key。
• json_tuple 函數(shù)的使用語法中，不能使用$.加上 json 的 key，如果使用則會(huì)導(dǎo)致解析失敗。
• json_tuple 函數(shù)與 get_json_object 函數(shù)對(duì)比，可以發(fā)現(xiàn) json_tuple 函數(shù)的優(yōu)點(diǎn)是一次可以解析多個(gè) json 字段。
• 但是如果被要求解析的 json 是一個(gè) json 數(shù)組，那么這兩個(gè)函數(shù)都無法完成解析。