Hive必會SQL語法Explode 和 Lateral View
本文轉載自微信公眾號「Java大數據與數據倉庫」，作者劉不二。轉載本文請聯系Java大數據與數據倉庫公眾號。
explode 和 lateral view
為什么把這兩個放一塊呢，因為這兩個經常放在一起用啊
explode與lateral view在關系型數據庫中本身是不該出現的，因為他的出現本身就是在操作不滿足第一范式的數據(每個屬性都不可再分)，本身已經違背了數據庫的設計原理(不論是業務系統還是數據倉庫系統)，不過大數據技術普及后，很多類似pv，uv的數據，在業務系統中是存貯在非關系型數據庫中，用json存儲的概率比較大，直接導入hive為基礎的數倉系統中，就需要經過ETL過程解析這類數據，explode與lateral view在這種場景下大顯身手。
explode用法
在介紹如何處理之前，我們先來了解下Hive內置的 explode 函數，官方的解釋是：explode() takes in an array (or a map) as an input and outputs the elements of the array (map) as separate rows. UDTFs can be used in the SELECT expression list and as a part of LATERAL VIEW. 意思就是 explode() 接收一個 array 或 map 類型的數據作為輸入，然后將 array 或 map 里面的元素按照每行的形式輸出。其可以配合 LATERAL VIEW 一起使用。光看文字描述很不直觀，咱們來看看幾個例子吧。
- hive (default)> select explode(array('A','B','C'));
- OK
- A
- B
- C
- Time taken: 4.188 seconds, Fetched: 3 row(s)
- hive (default)> select explode(map('a', 1, 'b', 2, 'c', 3));
- OK
- key value
- a 1
- b 2
- c 3
explode函數接收一個數組或者map類型的數據，通常需要用split函數生成數組。
explode 配合解析Json 數組
這里有數據：
- {"info":[
- {"AppName":"SogouExplorer_embedupdate","pepper":"-1"},
- {"AppName":"SogouExplorer_embedupdate","pepper":"-1"},
- {"AppName":"SogouExplorer_embedupdate","pepper":"-1"},
- {"AppName":"2345Explorer_embedupdate","plugin":"-1"},
- {"AppName":"SogouExplorer_embedupdate","pepper":"-1"}
- ]}
現在需要將AppName和pepper提取出來，然后按行存放，一行一個,首先我們按照上一節我們學習的Json 處理的函數進行嘗試
- select
- get_json_object(
- '{"info":[
- {"AppName":"SogouExplorer_embedupdate","pepper":"-1"},
- {"AppName":"SogouExplorer_embedupdate","pepper":"-1"},
- {"AppName":"SogouExplorer_embedupdate","pepper":"-1"},
- {"AppName":"2345Explorer_embedupdate","plugin":"-1"},
- {"AppName":"SogouExplorer_embedupdate","pepper":"-1"}
- ]}',
- "$.info[*].AppName"
- );
如圖
image-20201231111231311
但是我們注意到這里雖然提取出來了但是返回值是一個字符串啊,我為啥知道它是字符串，但是看起來像是一個數組啊，因為我用explode 函數試過了，那接下來怎么處理呢，這個時候就可以需要配合split 處理了，為了方便操作我直接用上么的結果進行操作
- ["SogouExplorer_embedupdate","SogouExplorer_embedupdate","SogouExplorer_embedupdate","2345Explorer_embedupdate","SogouExplorer_embedupdate"]
然我我們嘗試處理一下上面這個字符串，首先我們需要split 一下，但是在此之前我們需要將兩邊的中括號去掉，否則到時候我們的數據會包含這個兩個符號的
- select regexp_replace('["SogouExplorer_embedupdate","SogouExplorer_embedupdate","SogouExplorer_embedupdate","2345Explorer_embedupdate","SogouExplorer_embedupdate"]',"[\\[\\]]",'')
然后我們就可以split和explode 的了
- select explode(split(regexp_replace('["SogouExplorer_embedupdate","SogouExplorer_embedupdate","SogouExplorer_embedupdate","2345Explorer_embedupdate","SogouExplorer_embedupdate"]',"[\\[\\]]",''),','));
image-20201231112616809
這里解析json數組，我們本質上還是使用regexp_replace替換掉中括號，然后再使用split函數拆分為數據，給explode去分裂成多行。上面的這種寫法有問題嗎，功能是可以完成，但是這里只是提出來了AppName 這個字段，還有一個字段沒有提取出來呢，要是想把它提取出來，上面的步驟你還得再來一遍才可以，接下來我們嘗試引入json_tuple來簡化一下我們的操作,我們先將其explode 成多行簡單json 字符串，然后再使用json_tuple 進行處理
- select
- explode(
- split(
- regexp_replace(
- regexp_replace(
- get_json_object(
- '{"info":[
- {"AppName":"SogouExplorer_embedupdate","pepper":"-1"},
- {"AppName":"SogouExplorer_embedupdate","pepper":"-1"},
- {"AppName":"SogouExplorer_embedupdate","pepper":"-1"},
- {"AppName":"2345Explorer_embedupdate","plugin":"-1"},
- {"AppName":"SogouExplorer_embedupdate","pepper":"-1"}
- ]}',"$.info")
- ,'[\\[\\]]' ,'')
- ,'(},\\{)','}#\\{')
- ,'#')
- );
這里兩次調用了regexp_replace，第一次是為了去掉兩邊的中括號，第二次是為了將,jons 里面的逗號和分割json 的逗號進行區分，因為我們按照數組內容之間的分隔符進行split ，所以這里可以看做是將數組字符串的分隔符有逗號換成了# 號，然后就按照# split 了
image-20201231122203730
接下來就可以調用json_tuple 函數了
- select
- json_tuple(data,'AppName','pepper')
- from(
- select
- explode(
- split(
- regexp_replace(
- regexp_replace(
- get_json_object(
- '{"info":[
- {"AppName":"SogouExplorer_embedupdate","pepper":"-1"},
- {"AppName":"SogouExplorer_embedupdate","pepper":"-1"},
- {"AppName":"SogouExplorer_embedupdate","pepper":"-1"},
- {"AppName":"2345Explorer_embedupdate","plugin":"-1"},
- {"AppName":"SogouExplorer_embedupdate","pepper":"-1"}
- ]}',"$.info")
- ,'[\\[\\]]' ,'')
- ,'(},\\{)','}#\\{')
- ,'#')
- ) as data
- ) json_table;
如圖
image-20201231122505355
這樣我們就將我們需要的字段解析出來了
lateral view
開始之前我們先說一下它的用法 LATERAL VIEW udtf(expression) tableAlias AS columnAlias，你可以將lateral view翻譯為側視圖
我們有這樣的一份樣本數據(
- 劉德華 演員,導演,制片人
- 李小龍 演員,導演,制片人,幕后,武術指導
- 李連杰 演員,武術指導
- 劉亦菲 演員
這里我們希望轉換成下面這樣的格式
- 劉德華 演員
- 劉德華 導演
- 劉德華 制片人
- 李小龍 演員
- 李小龍 導演
- 李小龍 制片人
- 李小龍 幕后
- 李小龍 武術指導
- create table ods.ods_actor_data(
- username string,
- userrole string
- )
- ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
- load data local inpath "/Users/liuwenqiang/workspace/hive/lateral.data" overwrite into table ods.ods_actor_data;
如圖
image-20201231133130769
從我們前面的學習，我們知道這里應該用explode函數
- select explode(split(userrole,',')) from ods.ods_actor_data;
image-20201231134156444
理論上我們這下只要把username 也選出來就可以了
- select username,explode(split(userrole,',')) from ods.ods_actor_data;
Error: Error while compiling statement: FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions (state=42000,code=10081)
因為explode 是一個UDTF，所以你不能直接和其他字段一起使用，那應該怎么做呢在
- select
- username,role
- from
- ods.ods_actor_data
- LATERAL VIEW
- explode(split(userrole,',')) tmpTable as role
- ;
如圖
image-20201231154758339
看起來到這里我們的實現就結束了
lateral view outer
為什么會多了一個 OUTER 關鍵字呢，其實你也可以猜到了outer join 有點像，就是為了避免explode 函數返回值是null 的時候，影響我們主表的返回，注意是null 而不是空字符串
- select
- username,role
- from
- ods.ods_actor_data
- LATERAL VIEW
- explode(array()) tmpTable as role
- ;
如圖
image-20201231160414501
加上outer 關鍵字之后
- select
- username,role
- from
- ods.ods_actor_data
- LATERAL VIEW outer
- explode(array()) tmpTable as role
- ;
如圖
image-20201231160459117
其實一個SQL你可以多次使用lateral view也是可以的,就像下面這樣
- SELECT * FROM exampleTable
- LATERAL VIEW explode(col1) myTable1 AS myCol1
- LATERAL VIEW explode(myCol1) myTable2 AS myCol2;
lateral view 的實現原理是什么
首先我們知道explode()是一個UDTF 就是一個輸入進去，多個輸出出來，或者是進去一行，出來一列(多行)
image-20201231162007648
lateral view 關鍵字就是將每一行的特定字段交給explode 函數的表達式，然后將輸出結果和當前行做笛卡爾積,然后重復，直到循環完表里的全部數據，然后就變成下面裝了(圖中省略了傳給explode 字段的那一列)
image-20201231162254979
但其實到這里我就產生了一個疑問，為啥要這樣設計，直接將普通字段和UDTF 的函數的返回值一起查詢不好嗎，然后將原始字段和UDTF 的返回值做笛卡爾積就行了啊，為啥還要lateral view 呢，哈哈。
lateral view 中where 的使用
你可能會說where 不就那么用嗎，還有啥不一樣的，還真有，例如我上面的信息只要劉德華的，那你肯定會寫出下面的SQL
- select
- username,role
- from
- ods.ods_actor_data
- LATERAL VIEW
- explode(split(userrole,',')) tmpTable as role
- where
- username='劉德華'
- ;
要是我只要導演的呢，但是我們知道userrole 這個字段是包沒有直接是導演的，但是又包含導演的演員,導演,制片人,幕后,武術指導,其實這個時候你可以用下面的別名字段role
- select
- username,role
- from
- ods.ods_actor_data
- LATERAL VIEW
- explode(split(userrole,',')) tmpTable as role
- where
- role="導演"
- ;
如圖
image-20201231165856030
總結
一個SQL 里lateral view 你可以多次使用，就會多次做笛卡爾積;
UDTF 要配合lateral view 一起使用才可以;
其實回過頭來看的話，我們上面的處理過程就是將一行轉化為多行，典型的行轉列的實現，是SQL 面試的高頻考點;
