這篇文章主要講解了“如何掌握SQL語法Explode和Lateral View”，文中的講解內(nèi)容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“如何掌握SQL語法Explode和Lateral View”吧！

創(chuàng)新互聯(lián)專注于昭蘇網(wǎng)站建設(shè)服務(wù)及定制，我們擁有豐富的企業(yè)做網(wǎng)站經(jīng)驗。 熱誠為您提供昭蘇營銷型網(wǎng)站建設(shè)，昭蘇網(wǎng)站制作、昭蘇網(wǎng)頁設(shè)計、昭蘇網(wǎng)站官網(wǎng)定制、小程序制作服務(wù)，打造昭蘇網(wǎng)絡(luò)公司原創(chuàng)品牌,更為您提供昭蘇網(wǎng)站排名全網(wǎng)營銷落地服務(wù)。

explode 和 lateral view

為什么把這兩個放一塊呢，因為這兩個經(jīng)常放在一起用啊

explode與lateral  view在關(guān)系型數(shù)據(jù)庫中本身是不該出現(xiàn)的，因為他的出現(xiàn)本身就是在操作不滿足第一范式的數(shù)據(jù)(每個屬性都不可再分)，本身已經(jīng)違背了數(shù)據(jù)庫的設(shè)計原理(不論是業(yè)務(wù)系統(tǒng)還是數(shù)據(jù)倉庫系統(tǒng))，不過大數(shù)據(jù)技術(shù)普及后，很多類似pv，uv的數(shù)據(jù)，在業(yè)務(wù)系統(tǒng)中是存貯在非關(guān)系型數(shù)據(jù)庫中，用json存儲的概率比較大，直接導入hive為基礎(chǔ)的數(shù)倉系統(tǒng)中，就需要經(jīng)過ETL過程解析這類數(shù)據(jù)，explode與lateral  view在這種場景下大顯身手。

explode用法

在介紹如何處理之前，我們先來了解下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() 接收一個 array 或 map 類型的數(shù)據(jù)作為輸入，然后將 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函數(shù)接收一個數(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提取出來，然后按行存放，一行一個,首先我們按照上一節(jié)我們學習的Json 處理的函數(shù)進行嘗試

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

但是我們注意到這里雖然提取出來了但是返回值是一個字符串啊,我為啥知道它是字符串，但是看起來像是一個數(shù)組啊，因為我用explode  函數(shù)試過了，那接下來怎么處理呢，這個時候就可以需要配合split 處理了，為了方便操作我直接用上么的結(jié)果進行操作

["SogouExplorer_embedupdate","SogouExplorer_embedupdate","SogouExplorer_embedupdate","2345Explorer_embedupdate","SogouExplorer_embedupdate"]

然我我們嘗試處理一下上面這個字符串，首先我們需要split 一下，但是在此之前我們需要將兩邊的中括號去掉，否則到時候我們的數(shù)據(jù)會包含這個兩個符號的

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替換掉中括號，然后再使用split函數(shù)拆分為數(shù)據(jù)，給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")        ,'[\\[\\]]' ,'')      ,'(},\\{)','}#\\{')     ,'#')  );

這里兩次調(diào)用了regexp_replace，第一次是為了去掉兩邊的中括號，第二次是為了將,jons 里面的逗號和分割json  的逗號進行區(qū)分，因為我們按照數(shù)組內(nèi)容之間的分隔符進行split ，所以這里可以看做是將數(shù)組字符串的分隔符有逗號換成了# 號，然后就按照# split 了

image-20201231122203730

接下來就可以調(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

這樣我們就將我們需要的字段解析出來了

lateral view

開始之前我們先說一下它的用法 LATERAL VIEW udtf(expression) tableAlias AS  columnAlias，你可以將lateral view翻譯為側(cè)視圖

我們有這樣的一份樣本數(shù)據(jù)(

劉德華    演員,導演,制片人 李小龍    演員,導演,制片人,幕后,武術(shù)指導 李連杰    演員,武術(shù)指導 劉亦菲    演員

這里我們希望轉(zhuǎn)換成下面這樣的格式

劉德華 演員 劉德華 導演 劉德華 制片人 李小龍 演員 李小龍 導演 李小龍 制片人 李小龍 幕后 李小龍 武術(shù)指導 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

從我們前面的學習，我們知道這里應(yīng)該用explode函數(shù)

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，所以你不能直接和其他字段一起使用，那應(yīng)該怎么做呢在

select    username,role from     ods.ods_actor_data LATERAL VIEW     explode(split(userrole,',')) tmpTable as role ;

如圖

image-20201231154758339

看起來到這里我們的實現(xiàn)就結(jié)束了

lateral view outer

為什么會多了一個 OUTER 關(guān)鍵字呢，其實你也可以猜到了outer join 有點像，就是為了避免explode 函數(shù)返回值是null  的時候，影響我們主表的返回，注意是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

其實一個SQL你可以多次使用lateral view也是可以的,就像下面這樣

SELECT * FROM exampleTable LATERAL VIEW explode(col1) myTable1 AS myCol1 LATERAL VIEW explode(myCol1) myTable2 AS myCol2;

lateral view 的實現(xiàn)原理是什么

首先我們知道explode()是一個UDTF 就是一個輸入進去，多個輸出出來，或者是進去一行，出來一列(多行)

image-20201231162007648

lateral view 關(guān)鍵字就是將每一行的特定字段交給explode  函數(shù)的表達式，然后將輸出結(jié)果和當前行做笛卡爾積,然后重復，直到循環(huán)完表里的全部數(shù)據(jù)，然后就變成下面裝了(圖中省略了傳給explode 字段的那一列)

image-20201231162254979

但其實到這里我就產(chǎn)生了一個疑問，為啥要這樣設(shè)計，直接將普通字段和UDTF 的函數(shù)的返回值一起查詢不好嗎，然后將原始字段和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  這個字段是包沒有直接是導演的，但是又包含導演的演員,導演,制片人,幕后,武術(shù)指導,其實這個時候你可以用下面的別名字段role

select     username,role from     ods.ods_actor_data         LATERAL VIEW     explode(split(userrole,',')) tmpTable as role where     role="導演" ;

如圖

image-20201231165856030

感謝各位的閱讀，以上就是“如何掌握SQL語法Explode和Lateral View”的內(nèi)容了，經(jīng)過本文的學習后，相信大家對如何掌握SQL語法Explode和Lateral View這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是創(chuàng)新互聯(lián)，小編將為大家推送更多相關(guān)知識點的文章，歡迎關(guān)注！

分享名稱：如何掌握SQL語法Explode和LateralView
瀏覽地址：http://www.2m8n56k.cn/article8/pjedip.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站維護、域名注冊、ChatGPT、微信小程序、網(wǎng)站導航、虛擬主機

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：[email protected]。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)