大數據有道之行與列轉換

一、Hive sql

Hive是基於Hadoop的數據倉庫工具，為分散式大數據存儲提供了高效的讀、寫、管理等機制。

Hive sql簡稱HiveQL或HQL是Hive數據分析引擎，提供了標準SQL功能，支持：

UDF（user-defined functions，自定義函數）；

UDAF（User-Defined Aggregation Funcation，自定義聚合函數）；

UDTF(user-defined table-generating functions，自定義表生成函數)。

極大地豐富了數據開發的功能和樂趣 。

二、行列轉換

Hive支持textfile等行式和ORC等列式存儲，對應Hive sql也可以實現結構化數據的行列轉換。

下面大數據有道將通過以下函數，介紹Hive sql中行與列之間轉換方式。

1.CONCAT_WS()

2.COLLECT_SET() / COLECT_LIST()

3.EXPLODE() / LATERAL VIEW EXPLODE()

1.數據與表結

a.江南皮革廠訂單數據結構如下（重點關注訂單、用戶、訂單狀態和日期欄位）：

b.創建Hive表：

c.數據載入（select所得數據見a）：

2.列拼接

a.列拼接函數：CONCAT_WS(separator, str1, str2,...)，特殊形式的CONCAT()，其中separator指定了拼接符號（必須有，可為空""），該內置函數以separator為拼接符，拼接每一行數據中str1、str2等欄位內容，作為新欄位。

b.根據廠長指示，統計當天用戶+訂單信息數據（簡單的欄位間拼接）。

c.計算結果

d.其他

CONCAR_WS()函數中支持substring()、length()、round()等嵌套函數操作。

3.行拼接（全量）

a.行拼接函數：COLLECT_LIST(col)，該內置函數默認將每一行中的col欄位列拼接到一個數組中，作為新的欄位，當然可以通過CONCAR_WS()拼接同行中多個欄位為一個col。

b.根據廠長指示，現在整理每天用戶+訂單信息數據（將每一行拼接後的欄位收集到對應天中，即CONCAT_WS(":",user,order_id)作為col）。

c.計算結果

d.其他

COLLECT_LIST(col)一般結合group by使用，但注意該函數中收集到的數據不能去重。

4.行拼接（去重）

a.行拼接去重函數：COLLECT_SET(col)，該內置函數工作原理同COLLECT_LIST(col)，區別是能夠對collect到的數組做去重。

b.根據廠長指示，多列拼接為一列的全量數據進行去重。

c.計算結果

d.其他

相較於COLLECT_LIST()，COLLECT_SET()能夠整理出去重後的所有數據，但是兩個函數收集的數據集均沒有特定的排序。

5.行拼接+排序

a.排序函數：SORT_ARRAY(array[])，對COLLECT_SET(col)得到的數組進行排序，默認以ascii進行升序排序。

b.根據廠長指示，需要統計各訂單處理狀態（參考modify_time降序排序通過列轉行整理訂單狀態並添加排序索引）。

c.計算結果

6.行拆分（EXPLODE）

a.行拆分函數：EXPLODE(array[]/map[])，將某一行欄位（array類型）中 n個數據轉為n行或將array(map[k,v])轉換為n行×（k（新欄位）,v（新欄位）），簡單來講，就是一行複雜的array或map映射為多行。

b.根據廠長指示，進行Hive sql進階訓練，需要在4中聚合數據基礎上拆分每條訂單作為一行記錄。

c.計算結果

d.其他

需要注意，EXPLODE()獲取到的數據不能直接進行length、substring、split等嵌套函數操作，EXPLODE作為UDTF不支持嵌套調用，只能使用select調用。

對應異常：AnalysisException: Generators are not supported when it"s nested in expressions, but got: length(explode(sales_detail_sum))

7.行拆列（LATERAL VIEW EXPLODE）

a.行拆分函數LATERAL VIEW EXPLODE(array[]/map[])，改進了行拆分函數不能嵌套查詢的問題，執行過程中相當於單獨進行了兩次抽取然後union到表中。

b.根據廠長指示，繼續在4中聚合數據基礎上拆分每條訂單作為一行記錄，記錄中拼接的列也要展開。

c.計算結果

d.其他

LATERAL VIEW EXPLODE()抽取的結果sales_detail能夠進行嵌套查詢；str_to_map(str,"sep1","sep2")函數根據分隔符sep1拆分出數組，再根據分隔符sep2將數據中拆分為map[k,v]。

三、總結

1.CONCAT_WS()函數可實現多列拼接為一列功能；

2.COLLECT_SET()和COLLECT_LIST()函數可實現多行拼接為一行功能，同時結合CONCAT_WS()函數能夠實現多行多列拼接為一行一列，一般多與GROUP BY結合使用；

3.EXPLODE()可實現一行拆分為多行功能；LATERAL VIEW EXPLODE()解決了UDTF不能嵌套查詢問題，與STR_TO_MAP()結合使用可實現一行拆分為多行多列功能。

大數據有道BigData In Thinking!

大數據有道，為您提供通俗易懂的技能分享，讓大數據更容易！

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！