/*
以下這段文字出自?https://blog.csdn.net/guodong2k/article/details/79459282
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)），不過(guò)大數(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)過(guò)ETL過(guò)程解析這類數(shù)據(jù)，explode與lateral view在這種場(chǎng)景下大顯身手扇单。
*/
1. explode
explode是用于處理array商模、map、struct類型的UDTF蜘澜，能夠?qū)⒁恍杏涗洶匆蟛鸱譃槎嘈惺┝鳎_(dá)到行轉(zhuǎn)列的效果。
用法是select explode(expression(col)) from table_name; 要求explode的參數(shù)必須是map或者array類型鄙信。
為了演示用法與效果瞪醋，建立一張hive外部表laternal_explode_test
CREATE EXTERNAL TABLE laternal_explode_test
(
name STRING,
skill ARRAY<STRING>,
friend MAP<STRING,STRING>,
appearance struct<face:STRING,body:STRING>,
sigil STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE
LOCATION '***';
通過(guò)文本向表中灌入數(shù)據(jù)，內(nèi)容如下
AegonTargaryen|sword,dragon,wife|before:Velaryon|beautiful,strong|blood and fire
TyrionLannister|wisedom,name,bravery|always:Jamie|ugly,short|hear me roar
TonyStark|money,technology,genuis|now:SteveRogers|handsome,powerful|winter is coming
導(dǎo)入數(shù)據(jù)后装诡，查詢?cè)摫硪埽l(fā)現(xiàn)可以顯示全部三條記錄
select * from laternal_explode_test;
| laternal_explode_test.name | laternal_explode_test.skill | laternal_explode_test.friend | laternal_explode_test.appearance | laternal_explode_test.sigil |
| AegonTargaryen? ? ? ? ? ? ? | ["sword","dragon","wife"]? ? ? ? | {"before":"Velaryon"}? ? ? ? | {"face":"beautiful","body":"strong"}? | blood and fire? ? ? ? ? ? ? |
| TyrionLannister? ? ? ? ? ? | ["wisedom","name","bravery"]? ? | {"always":"Jamie"}? ? ? ? ? ? | {"face":"ugly","body":"short"}? ? ? ? | hear me roar? ? ? ? ? ? ? ? |
| TonyStark? ? ? ? ? ? ? ? ? | ["money","technology","genuis"]? | {"now":"SteveRogers"}? ? ? ? | {"face":"handsome","body":"powerful"}? | winter is coming? ? ? ? ? ? |
使用explode拆分部分字段
select explode(skill) as skills from laternal_explode_test;
| skills |
| sword? ? ? |
| dragon? ? ? |
| wife? ? ? ? |
| wisedom? ? |
| name? ? ? ? |
| bravery? ? |
| money? ? ? |
| technology? |
| genuis? ? ?
select explode(friend) from laternal_explode_test;
| key | value |
| before? | Velaryon? ? |
| always? | Jamie? ? ? ? |
| now? ? | SteveRogers? |
select explode(split(sigil,' ')) from laternal_explode_test;
| blood |
| and? ? |
| fire? ? |
| hear? ? |
| me? ? ? |
| roar? ? |
| winter? |
| is? ? ? |
| coming? |
可以看到explode確實(shí)有爆炸效果，能夠準(zhǔn)確快速拆分map和array鸦采。
但是繼續(xù)使用卻會(huì)發(fā)現(xiàn)explode有明顯的缺陷：
1.不支持同時(shí)select其他字段宾巍。如select name,explode(split(sigil,' ')) from laternal_explode_test;是不允許的，會(huì)報(bào)錯(cuò)渔伯。
2.不支持嵌套顶霞。select explode(explode(friend)) from laternal_explode_test; 也是不行的。
3.不能與group by锣吼、sort by 和cluster by一起使用选浑。
2. lateral view
lateral view專用于與split、explode吐限、stack等UDTF函數(shù)結(jié)合使用鲜侥，主要解決在select使用UDTF做查詢的過(guò)程中查詢不能包含其它字段以及多個(gè)UDTF的問(wèn)題。典型用法是：
table a LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)
對(duì)于上面的select?name,explode(split(sigil,' ')) from laternal_explode_test;如果我們一定要同時(shí)select其他字段诸典，可以改寫為
select name,cc
from laternal_explode_test t1
lateral view explode(split(sigil,' ')) t2 as cc;
上面語(yǔ)句的執(zhí)行過(guò)程可以簡(jiǎn)述為描函，首先通過(guò)explode函數(shù)將sigil列拆分成多行崎苗，再將拆分后的sigil視為一個(gè)虛擬表，即t2舀寓，這個(gè)表只有一列胆数，別名叫cc。最后將多行cc與其對(duì)應(yīng)的name組合起來(lái)互墓，select出結(jié)果必尼。故其結(jié)果是
| name | cc |
+------------------+---------+
| AegonTargaryen? | blood? |
| AegonTargaryen? | and? ? |
| AegonTargaryen? | fire? ? |
| TyrionLannister? | hear? ? |
| TyrionLannister? | me? ? ? |
| TyrionLannister? | roar? ? |
| TonyStark? ? ? ? | winter? |
| TonyStark? ? ? ? | is? ? ? |
| TonyStark? ? ? ? | coming? |
lateral view支持連續(xù)使用，如以下用法
select name,cc,bb
from laternal_explode_test t1
lateral view explode(split(sigil,' ')) t2 as cc
lateral view explode(skill) t3 as bb;
執(zhí)行過(guò)程是從左到右篡撵，t1先和t2組合成一個(gè)虛擬表判莉，然后整個(gè)虛擬表和t3再組成一個(gè)新的虛擬表。結(jié)果如下
| name | cc | bb |
+------------------+---------+-------------+
| AegonTargaryen? | blood? | sword? ? ? |
| AegonTargaryen? | blood? | dragon? ? ? |
| AegonTargaryen? | blood? | wife? ? ? ? |
| AegonTargaryen? | and? ? | sword? ? ? |
| AegonTargaryen? | and? ? | dragon? ? ? |
| AegonTargaryen? | and? ? | wife? ? ? ? |
| AegonTargaryen? | fire? ? | sword? ? ? |
| AegonTargaryen? | fire? ? | dragon? ? ? |
| AegonTargaryen? | fire? ? | wife? ? ? ? |
| TyrionLannister? | hear? ? | wisedom? ? |
| TyrionLannister? | hear? ? | name? ? ? ? |
| TyrionLannister? | hear? ? | bravery? ? |
| TyrionLannister? | me? ? ? | wisedom? ? |
| TyrionLannister? | me? ? ? | name? ? ? ? |
| TyrionLannister? | me? ? ? | bravery? ? |
| TyrionLannister? | roar? ? | wisedom? ? |
| TyrionLannister? | roar? ? | name? ? ? ? |
| TyrionLannister? | roar? ? | bravery? ? |
| TonyStark? ? ? ? | winter? | money? ? ? |
| TonyStark? ? ? ? | winter? | technology? |
| TonyStark? ? ? ? | winter? | genuis? ? ? |
| TonyStark? ? ? ? | is? ? ? | money? ? ? |
| TonyStark? ? ? ? | is? ? ? | technology? |
| TonyStark? ? ? ? | is? ? ? | genuis? ? ? |
| TonyStark? ? ? ? | coming? | money? ? ? |
| TonyStark? ? ? ? | coming? | technology? |
| TonyStark? ? ? ? | coming? | genuis? ? ? |
Hive中的函數(shù)和用法真是的豐富多彩育谬，每次摸索到了一個(gè)新用法券盅，就仿佛是在廣闊的寶山中尋得了一件新寶物。
