前言:每次使用UDTF函數(shù)的時(shí)候都要百度米苹，會(huì)用但一直一知半解糕伐，今天對(duì)UDTF函數(shù)做一個(gè)總結(jié)。
目錄：
一蘸嘶、UDTF函數(shù) explode() 講解
二良瞧、百度explode()，總會(huì)出現(xiàn)lateral view亏较，它們各自的作用是什么莺褒？
三、explode雪情、posexplode與lateral view 3套案例練習(xí)
1遵岩、找出相同數(shù)字的號(hào)碼超過5位的手機(jī)號(hào)
2、求一下每個(gè)學(xué)生成績最好的學(xué)科及分?jǐn)?shù)巡通、最差的學(xué)科及分?jǐn)?shù)尘执、平均分?jǐn)?shù)
3、生成start_time('2020-11-01')到end_time("2020-11-30")之間的所有日期
4宴凉、計(jì)算酒店每天房間的入住數(shù)--重點(diǎn)
hive中的函數(shù)分為3類,UDF函數(shù)誊锭、UDAF函數(shù)、UDTF函數(shù)
- UDF：一進(jìn)一出
- UDAF:聚集函數(shù)弥锄，多進(jìn)一出丧靡，類似于：count/max/min
- UDTF:一進(jìn)多出,如explore()蟆沫、posexplode()，UDTF函數(shù)的時(shí)候只允許一個(gè)字段
百度explode()時(shí)温治，經(jīng)常會(huì)出現(xiàn)lateral view + explode相關(guān)的文章饭庞，很少單獨(dú)寫explode()。分別了解ecplode() 與lateral view的各自作用很重要熬荆，不然過程都不知道實(shí)現(xiàn)的舟山，換個(gè)UDTF函數(shù)就不會(huì)使用了。
而大部分文章都是explode()與lateral view一起講解,當(dāng)我們都不會(huì)使用UDTF函數(shù)時(shí)卤恳，不理解該類函數(shù)時(shí)累盗，不知道僅UDTF函數(shù)如何使用、我們也不會(huì)真正的使用突琳。所以先看第一部分吧
一若债、UDTF函數(shù) explode() 講解
UDTF函數(shù)作用都是輸入一行數(shù)據(jù)，將該行數(shù)據(jù)拆分本今、并返回多行數(shù)據(jù)拆座。不同的UDTF函數(shù)只是拆分的原理不同、作用的數(shù)據(jù)格式不同而已冠息。
這里詳細(xì)講解explode()用法，學(xué)會(huì)這一個(gè)其他的UDTF函數(shù)也會(huì)使用孕索。
explode()將一行數(shù)據(jù)轉(zhuǎn)換成列數(shù)據(jù)逛艰，可以用于array和map類型的數(shù)據(jù)
1）explode()用于array的語法如下：
select explode(arraycol) as newcol from tablename;
#arraycol：arrary數(shù)據(jù)類型字段。
#tablename：表名
2）explode()用于map的語法如下：
select explode(mapcol) as (keyname,valuename) from tablename;
#tablename：表名
#mapcol：map類型的字段
#keyname：表示key轉(zhuǎn)換成的列名稱搞旭，用于代表key轉(zhuǎn)換之后的列名散怖。
#valuename：表示value轉(zhuǎn)換成的列名稱，用于代表value轉(zhuǎn)換之后的列名稱肄渗。
explode()用于map類型的數(shù)據(jù)時(shí)镇眷，由于map是kay-value結(jié)構(gòu)的，所以它在轉(zhuǎn)換的時(shí)候會(huì)轉(zhuǎn)換成兩列翎嫡，一列是kay轉(zhuǎn)換而成的欠动，一列是value轉(zhuǎn)換而成的。
3）以上為explode()函數(shù)的用法惑申，此函數(shù)存在局限性：
- 其一：不能關(guān)聯(lián)原有的表中的其他字段具伍。
- 其二：不能與group by、cluster by圈驼、distribute by人芽、sort by聯(lián)用。
- 其三：不能進(jìn)行UDTF嵌套绩脆。
- 其四：不允許選擇其他表達(dá)式萤厅。
二橄抹、百度explode()，總會(huì)出現(xiàn)lateral view惕味，它們各自的作用是什么害碾？
第一部分對(duì)explode()函數(shù)做了簡單的講解，知道它作用的數(shù)據(jù)格式為array和map ,也知道了如何單獨(dú)使用explode赦拘，可能腦袋還是有點(diǎn)懵慌随，下面將結(jié)合案例一起學(xué)習(xí)。
UDTF函數(shù)(如：explode)只能只允許對(duì)拆分字段進(jìn)行訪問躺同，即select時(shí)只能出現(xiàn)explode作用的字段阁猜，不能在選擇表中其它的字段，否則會(huì)報(bào)錯(cuò)蹋艺。
但是實(shí)際中需求中經(jīng)常要拆某個(gè)字段,然后一起與別的字段一起取剃袍。這時(shí)就要使用lateral view。
lateral view為側(cè)視圖,其實(shí)就是用來和像類似explode這種UDTF函數(shù)聯(lián)用的捎谨，lateral view會(huì)將UDTF生成的結(jié)果放到一個(gè)虛擬表中民效，然后這個(gè)虛擬表會(huì)和輸入行進(jìn)行join來達(dá)到連接UDTF外的select字段的目的。
不加lateral view的UDTF函數(shù)只能提取單個(gè)字段拆分,并不能塞回原來數(shù)據(jù)表中涛救。加上lateral view就可以將拆分的單個(gè)字段數(shù)據(jù)與原始表數(shù)據(jù)關(guān)聯(lián)上畏邢。在使用lateral view的時(shí)候需要指定視圖別名和生成的新列別名。
1检吆、udtf + lateral view 格式一
lateral view udtf(expression) tableAlias as columnAlias (,columnAlias)*
- lateral view在UDTF前使用舒萎，表示連接UDTF所分裂的字段。
- UDTF(expression)：使用的UDTF函數(shù)蹭沛，例如explode()臂寝。
- tableAlias：表示UDTF函數(shù)轉(zhuǎn)換的虛擬表的名稱。
- columnAlias：表示虛擬表的虛擬字段名稱摊灭，如果分裂之后有一個(gè)列咆贬，則寫一個(gè)即可；如果分裂之后有多個(gè)列帚呼，按照列的順序在括號(hào)中聲明所有虛擬列名掏缎，以逗號(hào)隔開。
2萝挤、udtf + lateral view 格式二
from basetable (lateral view)*
- 在from子句中使用御毅，一般和格式一搭配使用，這個(gè)格式只是說明了lateral view的使用位置怜珍。
- from子句后面也可以跟多個(gè)lateral view語句端蛆，使用空格間隔就可以了
eg:
SELECT myCol1, myCol2 FROM baseTable
LATERAL VIEW explode(col1) myTable1 AS myCol1
LATERAL VIEW explode(col2) myTable2 AS myCol2;
#col1為表baseTable字段中的map或者array類型
#col2為表baseTable字段中的map或者array類型
3、udtf + lateral view 格式三
from basetable (lateral view outer)*
它比格式二只是多了一個(gè)outer酥泛，這個(gè)outer的作用是在UDTF轉(zhuǎn)換列的時(shí)候?qū)⑵渲械?strong>空也給展示出來今豆，UDTF默認(rèn)是忽略輸出空的嫌拣，加上outer之后，會(huì)將空也輸出呆躲，顯示為NULL异逐。這個(gè)功能是在Hive0.12是開始支持的
eg:
select name,key,value from student_score lateral view outer explode(score) scntable as key,value;
-------------可接助下面邏輯理解-------------
# 查看表數(shù)據(jù)
hive> select * from udtf_test;
OK
jim5 ["james5","datacloase"]
jim4 ["james4","datacloase"]
jim3 ["james3","datacloase"]
jim2 ["james2","datacloase"]
Time taken: 0.084 seconds, Fetched: 4 row(s)
# 1）hive只允許對(duì)其拆分字段進(jìn)行訪問
hive> select explode(subordinates) from udtf_test;
OK
james5
datacloase
james4
datacloase
james3
datacloase
james2
datacloase
Time taken: 0.075 seconds, Fetched: 8 row(s)
#2）同時(shí)select 查詢 explode作用字段及其它字段時(shí)，報(bào)錯(cuò)
hive> select explode(subordinates),name from udtf_test;
FAILED: SemanticException 1:29 Only a single expression in the SELECT clause is supported with UDTF's. Error encountered near token 'name'
#3）借助lateral view插掂，同時(shí)查詢explode作用字段及其它字段
hive> select name,subordinate from udtf_test
> lateral view explode(subordinates)sub as subordinate;
OK
jim5 james5
jim5 datacloase
jim4 james4
jim4 datacloase
jim3 james3
jim3 datacloase
jim2 james2
jim2 datacloase
Time taken: 0.06 seconds, Fetched: 8 row(s)
三灰瞻、explode、posexplode與lateral view 2套案例練習(xí)
拓展：
explode與lateral view在關(guān)系型數(shù)據(jù)庫中本身是不該出現(xiàn)的辅甥，因?yàn)樗某霈F(xiàn)本身就是在操作不滿足第一范式的數(shù)據(jù)（每個(gè)屬性都不可再分）酝润，本身已經(jīng)違背了數(shù)據(jù)庫的設(shè)計(jì)原理（不論是業(yè)務(wù)系統(tǒng)還是數(shù)據(jù)倉庫系統(tǒng)）
不過大數(shù)據(jù)技術(shù)普及后，很多類似pv璃弄，uv的數(shù)據(jù)要销，在業(yè)務(wù)系統(tǒng)中是存貯在非關(guān)系型數(shù)據(jù)庫中，用json存儲(chǔ)的概率比較大夏块，直接導(dǎo)入hive基礎(chǔ)的數(shù)倉系統(tǒng)中疏咐，就需要經(jīng)過ETL過程解析這類數(shù)據(jù)，explode與lateral view在這種場景下大顯身手脐供。
1浑塞、找出相同數(shù)字的號(hào)碼超過5位的手機(jī)號(hào)
1) 使用數(shù)據(jù)
jimmhe 18191512076
xiaosong 18392988059
jingxianghua 18118818818
donghualing 17191919999
2) 創(chuàng)建表
CREATE TABLE udtf_test1(
name string,
phonenumber string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
3) 加載數(shù)據(jù)
load data local inpath '/home/atguigu/data/test_20211215.txt' into table udtf_test1;
4) 查看加載表數(shù)據(jù)
hive> select * from udtf_test1;
OK
jimmhe 18191512076
xiaosong 18392988059
jingxianghua 18118818818
donghualing 17191919999
Time taken: 0.076 seconds, Fetched: 4 row(s)
5) 解題分析思路
split將電話號(hào)碼，拆分成數(shù)組患民，在用explode炸裂
select name,phonenumber
from(
select
name
,phonenumber
,phone_num
from udtf_test1
lateral view explode(split(phonenumber,'')) view_number as phone_num)aa
group by name,phonenumber,phone_num
having count(1)>=5
2缩举、求一下每個(gè)學(xué)生成績最好的學(xué)科及分?jǐn)?shù)、最差的學(xué)科及分?jǐn)?shù)匹颤、平均分?jǐn)?shù)
有一張hive表，分別是學(xué)生姓名name(string)托猩，學(xué)生成績score(map<string,string>),成績列中key是學(xué)科名稱印蓖，value是對(duì)應(yīng)學(xué)科分?jǐn)?shù)，請(qǐng)用一個(gè)hql求一下每個(gè)學(xué)生成績最好的學(xué)科及分?jǐn)?shù)京腥、最差的學(xué)科及分?jǐn)?shù)
1）表數(shù)據(jù)如下：
zhangsan|Chinese:80,Math:60,English:90
lisi|Chinese:90,Math:80,English:70
wangwu|Chinese:88,Math:90,English:96
maliu|Chinese:99,Math:65,English:60
2）創(chuàng)建表：
create table stu_score_test(name string,score map<String,string>)
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':';
3）導(dǎo)入數(shù)據(jù)：
load data local inpath '/home/atguigu/data/test_20211216' into table stu_score_test;
4）查看導(dǎo)入后表數(shù)據(jù)
hive> select * from stu_score_test;
OK
zhangsan {"Chinese":"80","Math":"60","English":"90"}
lisi {"Chinese":"90","Math":"80","English":"70"}
wangwu {"Chinese":"88","Math":"90","English":"96"}
maliu {"Chinese":"99","Math":"65","English":"60"}
Time taken: 0.164 seconds, Fetched: 4 row(s)
5）解題思路
explode拆分map數(shù)據(jù)類型
select
name,course,csorce
from(
select
name
,course
,csorce
,rank()over(partition by name order by csorce) last_rn
,rank()over(partition by name order by csorce desc) best_rn
from stu_score_test
lateral view explode(score) score_view as course,csorce
)aa
where last_rn=1 or best_rn=1
3赦肃、生成start_time('2020-11-01')到end_time("2020-11-30")之間的所有日期
select pos, date_add('2020-11-01', pos) as dynamic_date ,'2020-11-01' as start_time, '2020-11-30' end_time
from fdc_dc.temp_hotal_live a
# lateral view posexplode(split(space(datediff('2020-11-30', '2020-11-01')),' ')) pe as i, x
# 我用上面的就不能實(shí)現(xiàn)結(jié)果 ，其它人夠能實(shí)現(xiàn)公浪，腦袋疼
LATERAL VIEW posexplode( split ( REPEAT('A,',datediff( '2021-12-17', '2021-11-17' )) , ',' ) ) t AS pos,val
WHERE user_id='8'
- 第一列為生成的索引值他宛。其中的 REPEAT函數(shù)為復(fù)制幾個(gè)字符串，方便以
欠气，切分生成對(duì)應(yīng)的索引個(gè)數(shù)厅各。
4、計(jì)算酒店每天有多少個(gè)房間的入住---重點(diǎn)
1）需求如下
2）原始數(shù)據(jù)
7,2004,2021-03-05,2021-03-07
23,2010,2021-03-05,2021-03-06
7,1003,2021-03-07,2021-03-08
8,2014,2021-03-07,2021-03-08
14,3001,2021-03-07,2021-03-10
18,3002,2021-03-08,2021-03-10
23,3020,2021-03-08,2021-03-09
25,2006,2021-03-09,2021-03-12
3) 建表
create table temp_hotal_live(
user_id varchar(50),
room_code varchar(50),
Check_date varchar(50),
leave_date varchar(50)
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
;
4) 分析思路
用posplode炸裂预柒，補(bǔ)充完整時(shí)間
SELECT
start_dd,end_dd,count(1)
from
(SELECT
user_id, --用戶id
check_date, --入店時(shí)間
leave_date, --離店時(shí)間
date_add( check_date, pos ) start_dd,
date_add( check_date, pos+1 ) end_dd
FROM
temp_hotal_live
lateral VIEW
posexplode ( split ( REPEAT('A,',datediff( leave_date, check_date )) , ',' ) ) t AS pos, val
--posexplode ( split ( space(datediff( leave_date, check_date )) , ' ' ) ) t AS pos, val
--用space的時(shí)候一直報(bào)錯(cuò)队塘，我也不知道為什么袁梗，反正就是沒返回的結(jié)果，還好找到了另外的一種思路repeat
)
group BY start_dd,end_dd
- datediff,計(jì)算住了多少天憔古，兩個(gè)時(shí)間之間的差值
- REPEAT(),把字符串復(fù)制多少次遮怜，把'A,'本題是把A,復(fù)制
- split，把字符串按分隔符分割為數(shù)組
- posexplode ：炸裂鸿市，并排序
百度時(shí)間锯梁，發(fā)現(xiàn)更多是用空格sapce占位,不用repeat,但是我在使用sapce時(shí)一直實(shí)現(xiàn)不了功能，比如數(shù)據(jù)全為空焰情，不能排序陌凳。
可以看下面文章增加了解：
hive高階函數(shù)（1）repeat、posexplode
Hive高階函數(shù)posexplode（可以用于生成動(dòng)態(tài)日期序列）
總結(jié)：雖然文章大部分借鑒了其他博主的文章烙样，但自己寫完之后冯遂，思路更加清晰，比看十幾篇?jiǎng)e的博主文章掌握更好谒获。并且網(wǎng)上文章的質(zhì)量好的很少蛤肌。
參考文章1：Hive應(yīng)用：explode和lateral view，寫的最好的文章批狱。
參考文章2：Hive學(xué)習(xí)之Lateral View裸准，文章總結(jié)了多個(gè)lateral view 使用，及l(fā)ateral view out 使用赔硫。
參考文章3：Hive表生成函數(shù)explode講解
