背景

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

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

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

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

舉例：

user表字段如下：

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

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

從 user 表到 user_detail_info 表的轉換，就是“行轉列”的過程。

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

下文會解答你的疑惑。

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

語法：

get_json_object(json_string, '$.column')

說明：

解析 json 的字符串 json_string, 返回 path 指定的內容。如果輸入的 json 字符串無效，結果返回 NULL。

這個函數(shù)每次只能返回一個數(shù)據(jù)項。

舉例：

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

查詢sql語句：

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

解析結果：

如果需要同時解析 age, preference 這兩個字段。

sql語句如下：

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

執(zhí)行結果如下：

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

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

語法：

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

說明：

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

舉例：

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

(1). 準備 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;

解析結果：

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