共計(jì) 5976 個(gè)字符，預(yù)計(jì)需要花費(fèi) 15 分鐘才能閱讀完成。
這篇文章主要講解了“如何掌握 SQL 語(yǔ)法 Explode 和 Lateral View”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著丸趣 TV 小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“如何掌握 SQL 語(yǔ)法 Explode 和 Lateral View”吧！
explode 和 lateral view
為什么把這兩個(gè)放一塊呢，因?yàn)檫@兩個(gè)經(jīng)常放在一起用啊
explode 與 lateral view 在關(guān)系型數(shù)據(jù)庫(kù)中本身是不該出現(xiàn)的，因?yàn)樗某霈F(xiàn)本身就是在操作不滿足第一范式的數(shù)據(jù) (每個(gè)屬性都不可再分)，本身已經(jīng)違背了數(shù)據(jù)庫(kù)的設(shè)計(jì)原理 (不論是業(yè)務(wù)系統(tǒng)還是數(shù)據(jù)倉(cāng)庫(kù)系統(tǒng))，不過大數(shù)據(jù)技術(shù)普及后，很多類似 pv，uv 的數(shù)據(jù)，在業(yè)務(wù)系統(tǒng)中是存貯在非關(guān)系型數(shù)據(jù)庫(kù)中，用 json 存儲(chǔ)的概率比較大，直接導(dǎo)入 hive 為基礎(chǔ)的數(shù)倉(cāng)系統(tǒng)中，就需要經(jīng)過 ETL 過程解析這類數(shù)據(jù)，explode 與 lateral view 在這種場(chǎng)景下大顯身手。
explode 用法
在介紹如何處理之前，我們先來(lái)了解下 Hive 內(nèi)置的 explode 函數(shù)，官方的解釋是：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() 接收一個(gè) array 或 map 類型的數(shù)據(jù)作為輸入，然后將 array 或 map 里面的元素按照每行的形式輸出。其可以配合 LATERAL VIEW 一起使用。光看文字描述很不直觀，咱們來(lái)看看幾個(gè)例子吧。
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 3explode 函數(shù)接收一個(gè)數(shù)組或者 map 類型的數(shù)據(jù)，通常需要用 split 函數(shù)生成數(shù)組。
explode 配合解析 Json 數(shù)組
這里有數(shù)據(jù)：
{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} ]}現(xiàn)在需要將 AppName 和 pepper 提取出來(lái)，然后按行存放，一行一個(gè), 首先我們按照上一節(jié)我們學(xué)習(xí)的 Json 處理的函數(shù)進(jìn)行嘗試
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
但是我們注意到這里雖然提取出來(lái)了但是返回值是一個(gè)字符串啊, 我為啥知道它是字符串，但是看起來(lái)像是一個(gè)數(shù)組啊，因?yàn)槲矣?explode 函數(shù)試過了，那接下來(lái)怎么處理呢，這個(gè)時(shí)候就可以需要配合 split 處理了，為了方便操作我直接用上么的結(jié)果進(jìn)行操作
[SogouExplorer_embedupdate , SogouExplorer_embedupdate , SogouExplorer_embedupdate , 2345Explorer_embedupdate , SogouExplorer_embedupdate]然我我們嘗試處理一下上面這個(gè)字符串，首先我們需要 split 一下，但是在此之前我們需要將兩邊的中括號(hào)去掉，否則到時(shí)候我們的數(shù)據(jù)會(huì)包含這個(gè)兩個(gè)符號(hào)的
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 數(shù)組，我們本質(zhì)上還是使用 regexp_replace 替換掉中括號(hào)，然后再使用 split 函數(shù)拆分為數(shù)據(jù)，給 explode 去分裂成多行。上面的這種寫法有問題嗎，功能是可以完成，但是這里只是提出來(lái)了 AppName 這個(gè)字段，還有一個(gè)字段沒有提取出來(lái)呢，要是想把它提取出來(lái)，上面的步驟你還得再來(lái)一遍才可以，接下來(lái)我們嘗試引入 json_tuple 來(lái)簡(jiǎn)化一下我們的操作, 我們先將其 explode 成多行簡(jiǎn)單 json 字符串，然后再使用 json_tuple 進(jìn)行處理
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 ) , [\\[\\]] , ) , (},\\{) , }#\\{) , #) );
這里兩次調(diào)用了 regexp_replace，第一次是為了去掉兩邊的中括號(hào)，第二次是為了將,jons 里面的逗號(hào)和分割 json 的逗號(hào)進(jìn)行區(qū)分，因?yàn)槲覀儼凑諗?shù)組內(nèi)容之間的分隔符進(jìn)行 split，所以這里可以看做是將數(shù)組字符串的分隔符有逗號(hào)換成了# 號(hào)，然后就按照# split 了
image-20201231122203730
接下來(lái)就可以調(diào)用 json_tuple 函數(shù)了
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
這樣我們就將我們需要的字段解析出來(lái)了
lateral view
開始之前我們先說(shuō)一下它的用法 LATERAL VIEW udtf(expression) tableAlias AS columnAlias，你可以將 lateral view 翻譯為側(cè)視圖
我們有這樣的一份樣本數(shù)據(jù) (
劉德華 演員, 導(dǎo)演, 制片人 李小龍 演員, 導(dǎo)演, 制片人, 幕后, 武術(shù)指導(dǎo) 李連杰 演員, 武術(shù)指導(dǎo) 劉亦菲 演員 這里我們希望轉(zhuǎn)換成下面這樣的格式
劉德華 演員 劉德華 導(dǎo)演 劉德華 制片人 李小龍 演員 李小龍 導(dǎo)演 李小龍 制片人 李小龍 幕后 李小龍 武術(shù)指導(dǎo) 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
從我們前面的學(xué)習(xí)，我們知道這里應(yīng)該用 explode 函數(shù)
select explode(split(userrole, ,)) from ods.ods_actor_data;image-20201231134156444
理論上我們這下只要把 username 也選出來(lái)就可以了
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)
因?yàn)?explode 是一個(gè) UDTF，所以你不能直接和其他字段一起使用，那應(yīng)該怎么做呢在
select username,role from ods.ods_actor_data LATERAL VIEW explode(split(userrole, ,)) tmpTable as role ;如圖
image-20201231154758339
看起來(lái)到這里我們的實(shí)現(xiàn)就結(jié)束了
lateral view outer
為什么會(huì)多了一個(gè) OUTER 關(guān)鍵字呢，其實(shí)你也可以猜到了 outer join 有點(diǎn)像，就是為了避免 explode 函數(shù)返回值是 null 的時(shí)候，影響我們主表的返回，注意是 null 而不是空字符串
select username,role from ods.ods_actor_data LATERAL VIEW explode(array()) tmpTable as role ;如圖
image-20201231160414501
加上 outer 關(guān)鍵字之后
select username,role from ods.ods_actor_data LATERAL VIEW outer explode(array()) tmpTable as role ;如圖
image-20201231160459117
其實(shí)一個(gè) SQL 你可以多次使用 lateral view 也是可以的, 就像下面這樣
SELECT * FROM exampleTable LATERAL VIEW explode(col1) myTable1 AS myCol1 LATERAL VIEW explode(myCol1) myTable2 AS myCol2;lateral view 的實(shí)現(xiàn)原理是什么
首先我們知道 explode() 是一個(gè) UDTF 就是一個(gè)輸入進(jìn)去，多個(gè)輸出出來(lái)，或者是進(jìn)去一行，出來(lái)一列 ( 多行)
image-20201231162007648
lateral view 關(guān)鍵字就是將每一行的特定字段交給 explode 函數(shù)的表達(dá)式，然后將輸出結(jié)果和當(dāng)前行做笛卡爾積, 然后重復(fù)，直到循環(huán)完表里的全部數(shù)據(jù)，然后就變成下面裝了 (圖中省略了傳給 explode 字段的那一列)
image-20201231162254979
但其實(shí)到這里我就產(chǎn)生了一個(gè)疑問，為啥要這樣設(shè)計(jì)，直接將普通字段和 UDTF 的函數(shù)的返回值一起查詢不好嗎，然后將原始字段和 UDTF 的返回值做笛卡爾積就行了啊，為啥還要 lateral view 呢，哈哈。
lateral view 中 where 的使用
你可能會(huì)說(shuō) where 不就那么用嗎，還有啥不一樣的，還真有，例如我上面的信息只要?jiǎng)⒌氯A的，那你肯定會(huì)寫出下面的 SQL
select username,role from ods.ods_actor_data LATERAL VIEW explode(split(userrole, ,)) tmpTable as role where username= 劉德華 ;要是我只要導(dǎo)演的呢，但是我們知道 userrole 這個(gè)字段是包沒有直接是導(dǎo)演的，但是又包含導(dǎo)演的演員, 導(dǎo)演, 制片人, 幕后, 武術(shù)指導(dǎo), 其實(shí)這個(gè)時(shí)候你可以用下面的別名字段 role
select username,role from ods.ods_actor_data LATERAL VIEW explode(split(userrole, ,)) tmpTable as role where role= 導(dǎo)演 ;如圖
image-20201231165856030
感謝各位的閱讀，以上就是“如何掌握 SQL 語(yǔ)法 Explode 和 Lateral View”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對(duì)如何掌握 SQL 語(yǔ)法 Explode 和 Lateral View 這一問題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是丸趣 TV，丸趣 TV 小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！
