使用explode函數(shù)將hive表中的Map和Array字段數(shù)據(jù)進(jìn)行拆分
lateral view用于和split、explode等UDTF一起使用的，能將一行數(shù)據(jù)拆分成多行數(shù)據(jù)，在此基礎(chǔ)上可以對(duì)拆分的數(shù)據(jù)進(jìn)行聚合，lateral view首先為原始表的每行調(diào)用UDTF，UDTF會(huì)把一行拆分成一行或者多行，lateral view在把結(jié)果組合，產(chǎn)生一個(gè)支持別名表的虛擬表。
其中explode還可以用于將hive一列中復(fù)雜的array或者map結(jié)構(gòu)拆分成多行
需求：現(xiàn)在有數(shù)據(jù)格式如下
zhangsan child1,child2,child3,child4 k1:v1,k2:v2
lisi child5,child6,child7,child8 k3:v3,k4:v4
字段之間使用\t分割，需求將所有的child進(jìn)行拆開成為一列
+----------+--+
| mychild |
+----------+--+
| child1 |
| child2 |
| child3 |
| child4 |
| child5 |
| child6 |
| child7 |
| child8 |
+----------+--+
將map的key和value也進(jìn)行拆開，成為如下結(jié)果
+-----------+-------------+--+
| mymapkey | mymapvalue |
+-----------+-------------+--+
| k1 | v1 |
| k2 | v2 |
| k3 | v3 |
| k4 | v4 |
+-----------+-------------+--+
- 創(chuàng)建hive數(shù)據(jù)庫(kù)
創(chuàng)建hive數(shù)據(jù)庫(kù)
hive (default)> create database hive_explode;
hive (default)> use hive_explode;
- 創(chuàng)建hive表，然后使用explode拆分map和array
hive (hive_explode)> create table t3(
name string,
children array<string>,
address Map<string,string>
) row format delimited fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':'
stored as textFile;
- 加載數(shù)據(jù)
node03執(zhí)行以下命令創(chuàng)建表數(shù)據(jù)文件
mkdir -p /export/servers/hivedatas/
cd /export/servers/hivedatas/
vim maparray
內(nèi)容如下:
zhangsan child1,child2,child3,child4 k1:v1,k2:v2
lisi child5,child6,child7,child8 k3:v3,k4:v4
hive表當(dāng)中加載數(shù)據(jù)
hive (hive_explode)> load data local inpath '/export/servers/hivedatas/maparray' into table t3;
- 使用explode將hive當(dāng)中數(shù)據(jù)拆開
將array當(dāng)中的數(shù)據(jù)拆分開
hive (hive_explode)> SELECT explode(children) AS myChild FROM t3;
將map當(dāng)中的數(shù)據(jù)拆分開
hive (hive_explode)> SELECT explode(address) AS (myMapKey, myMapValue) FROM t3;
使用explode拆分json字符串
需求: 需求：現(xiàn)在有一些數(shù)據(jù)格式如下：
a:shandong,b:beijing,c:hebei|
1,2,3,4,5,6,7,8,9|
[{"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"},
{"source":"jd","monthSales":2090,"userCount":78981,"score":"9.8"},
{"source":"jdmart","monthSales":6987,"userCount":1600,"score":"9.0"}]
其中字段與字段之間的分隔符是 |
我們要解析得到所有的monthSales對(duì)應(yīng)的值為以下這一列（行轉(zhuǎn)列）
4900
2090
6987
- 創(chuàng)建hive表
hive (hive_explode)> create table explode_lateral_view
> (`area` string,
> `goods_id` string,
> `sale_info` string)
> ROW FORMAT DELIMITED
> FIELDS TERMINATED BY '|'
> STORED AS textfile;
- 準(zhǔn)備數(shù)據(jù)并加載數(shù)據(jù)
準(zhǔn)備數(shù)據(jù)如下
cd /export/servers/hivedatas
vim explode_json
a:shandong,b:beijing,c:hebei|
1,2,3,4,5,6,7,8,9|
[{"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"},
{"source":"jd","monthSales":2090,"userCount":78981,"score":"9.8"},
{"source":"jdmart","monthSales":6987,"userCount":1600,"score":"9.0"}]
加載數(shù)據(jù)到hive表當(dāng)中去
hive (hive_explode)> load data local inpath '/export/servers/hivedatas/explode_json'
> overwrite into table explode_lateral_view;
- 使用explode拆分Array
hive (hive_explode)> select explode(split(goods_id,',')) as goods_id from explode_lateral_view;
- 使用explode拆解Map
hive (hive_explode)> select explode(split(area,',')) as area from explode_lateral_view;
- 拆解json字段
hive (hive_explode)> select explode(split(regexp_replace(regexp_replace(sale_info,
> '\\[\\{',''),'}]',''),'},\\{')) as sale_info from explode_lateral_view;
然后我們想用get_json_object來獲取key為monthSales的數(shù)據(jù)：
hive (hive_explode)> select get_json_object(explode(split(regexp_replace(regexp_replace(sale_info,
> '\\[\\{',''),'}]',''),'},\\{')),'$.monthSales') as sale_info from explode_lateral_view;
然后掛了FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions
UDTF explode不能寫在別的函數(shù)內(nèi)
如果你這么寫，想查兩個(gè)字段，select explode(split(area,',')) as area,good_id from explode_lateral_view;
會(huì)報(bào)錯(cuò)FAILED: SemanticException 1:40 Only a single expression in the SELECT clause is supported with UDTF's.
Error encountered near token 'good_id'
使用UDTF的時(shí)候，只支持一個(gè)字段，這時(shí)候就需要LATERAL VIEW出場(chǎng)了
配合LATERAL VIEW使用
配合lateral view查詢多個(gè)字段
select
goods_id2,sale_info
from
explode_lateral_view LATERAL VIEW explode(split(goods_id,',')) goods as goods_id2;
其中LATERAL VIEW explode(split(goods_id,','))goods相當(dāng)于一個(gè)虛擬表，與原表explode_lateral_view笛卡爾積關(guān)聯(lián)
也可以多重使用
hive (hive_explode)> select goods_id2,sale_info,area2
from explode_lateral_view
LATERAL VIEW explode(split(goods_id,','))goods as goods_id2
LATERAL VIEW explode(split(area,','))area as area2;也是三個(gè)表笛卡爾積的結(jié)果
最終，我們可以通過下面的句子，把這個(gè)json格式的一行數(shù)據(jù)，完全轉(zhuǎn)換成二維表的方式展現(xiàn)
select
get_json_object(concat('{',sale_info_1,'}'),'$.source') as source,
get_json_object(concat('{',sale_info_1,'}'),'$.monthSales') as monthSales,
get_json_object(concat('{',sale_info_1,'}'),'$.userCount') as monthSales,
get_json_object(concat('{',sale_info_1,'}'),'$.score') as monthSales
from
explode_lateral_view LATERAL VIEW explode(split(regexp_replace(regexp_replace(sale_info,
'\\[\\{',''),'}]',''),'},\\{')) sale_info as sale_info_1;
總結(jié)：
Lateral View通常和UDTF一起出現(xiàn)，為了解決UDTF不允許在select字段的問題。Multiple Lateral View可以實(shí)現(xiàn)類似笛卡爾乘積。Outer關(guān)鍵字可以把不輸出的UDTF的空結(jié)果，輸出成NULL，防止丟失數(shù)據(jù)。
行轉(zhuǎn)列
相關(guān)參數(shù)說明:
CONCAT(string A/col, string B/col…)：返回輸入字符串連接后的結(jié)果，支持任意個(gè)輸入字符串;
CONCAT_WS(separator, str1, str2,...)：它是一個(gè)特殊形式的 CONCAT()。第一個(gè)參數(shù)剩余參數(shù)間的分隔符。分隔符可以是與剩余參數(shù)一樣的字符串。如果分隔符是 NULL，返回值也將為 NULL。這個(gè)函數(shù)會(huì)跳過分隔符參數(shù)后的任何 NULL 和空字符串。分隔符將被加到被連接的字符串之間;
COLLECT_SET(col)：函數(shù)只接受基本數(shù)據(jù)類型，它的主要作用是將某字段的值進(jìn)行去重匯總，產(chǎn)生array類型字段。
數(shù)據(jù)準(zhǔn)備:
| name | constellation | blood_type |
|---|---|---|
| 孫悟空 | 白羊座 | A |
| 老王 | 射手座 | A |
| 宋宋 | 白羊座 | B |
| 豬八戒 | 白羊座 | A |
| 鳳姐 | 射手座 | A |
需求: 把星座和血型一樣的人歸類到一起。結(jié)果如下：
射手座,A 老王|鳳姐
白羊座,A 孫悟空|豬八戒
白羊座,B 宋宋
實(shí)現(xiàn)步驟:
- 創(chuàng)建本地constellation.txt，導(dǎo)入數(shù)據(jù)
node03服務(wù)器執(zhí)行以下命令創(chuàng)建文件，注意數(shù)據(jù)使用\t進(jìn)行分割
cd /export/servers/hivedatas
vim constellation.txt
數(shù)據(jù)如下:
孫悟空 白羊座 A
老王 射手座 A
宋宋 白羊座 B
豬八戒 白羊座 A
鳳姐 射手座 A
- 創(chuàng)建hive表并導(dǎo)入數(shù)據(jù)
創(chuàng)建hive表并加載數(shù)據(jù)
hive (hive_explode)> create table person_info(
name string,
constellation string,
blood_type string)
row format delimited fields terminated by "\t";
加載數(shù)據(jù)
hive (hive_explode)> load data local inpath '/export/servers/hivedatas/constellation.txt' into table person_info
- 按需求查詢數(shù)據(jù)
hive (hive_explode)> select
t1.base,
concat_ws('|', collect_set(t1.name)) name
from
(select
name,
concat(constellation, "," , blood_type) base
from
person_info) t1
group by
t1.base;
列轉(zhuǎn)行
所需函數(shù):
EXPLODE(col)：將hive一列中復(fù)雜的array或者map結(jié)構(gòu)拆分成多行。
LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解釋：用于和split, explode等UDTF一起使用，它能夠?qū)⒁涣袛?shù)據(jù)拆成多行數(shù)據(jù)，在此基礎(chǔ)上可以對(duì)拆分后的數(shù)據(jù)進(jìn)行聚合。
數(shù)據(jù)準(zhǔn)備:
cd /export/servers/hivedatas
vim movie.txt
文件內(nèi)容如下: 數(shù)據(jù)字段之間使用\t進(jìn)行分割
《疑犯追蹤》 懸疑,動(dòng)作,科幻,劇情
《Lie to me》 懸疑,警匪,動(dòng)作,心理,劇情
《戰(zhàn)狼2》 戰(zhàn)爭(zhēng),動(dòng)作,災(zāi)難
需求: 將電影分類中的數(shù)組數(shù)據(jù)展開。結(jié)果如下：
《疑犯追蹤》 懸疑
《疑犯追蹤》 動(dòng)作
《疑犯追蹤》 科幻
《疑犯追蹤》 劇情
《Lie to me》 懸疑
《Lie to me》 警匪
《Lie to me》 動(dòng)作
《Lie to me》 心理
《Lie to me》 劇情
《戰(zhàn)狼2》 戰(zhàn)爭(zhēng)
《戰(zhàn)狼2》 動(dòng)作
《戰(zhàn)狼2》 災(zāi)難
實(shí)現(xiàn)步驟:
- 創(chuàng)建hive表
create table movie_info(
movie string,
category array<string>)
row format delimited fields terminated by "\t"
collection items terminated by ",";
- 加載數(shù)據(jù)
load data local inpath "/export/servers/hivedatas/movie.txt" into table movie_info;
- 按需求查詢數(shù)據(jù)
select
movie,
category_name
from
movie_info lateral view explode(category) table_tmp as category_name;
reflect函數(shù)
reflect函數(shù)可以支持在sql中調(diào)用java中的自帶函數(shù)，秒殺一切udf函數(shù)。
需求1: 使用java.lang.Math當(dāng)中的Max求兩列中最大值
實(shí)現(xiàn)步驟:
- 創(chuàng)建hive表
create table test_udf(col1 int,col2 int) row format delimited fields terminated by ',';
- 準(zhǔn)備數(shù)據(jù)并加載數(shù)據(jù)
cd /export/servers/hivedatas
vim test_udf
文件內(nèi)容如下:
1,2
4,3
6,4
7,5
5,6
- 加載數(shù)據(jù)
hive (hive_explode)> load data local inpath '/export/servers/hivedatas/test_udf' overwrite into table test_udf;
- 使用java.lang.Math當(dāng)中的Max求兩列當(dāng)中的最大值
hive (hive_explode)> select reflect("java.lang.Math","max",col1,col2) from test_udf;
需求2: 文件中不同的記錄來執(zhí)行不同的java的內(nèi)置函數(shù)
實(shí)現(xiàn)步驟:
- 創(chuàng)建hive表
hive (hive_explode)> create table test_udf2(class_name string,method_name string,col1 int , col2 int) row format delimited fields terminated by ',';
- 準(zhǔn)備數(shù)據(jù)
cd /export/servers/hivedatas
vim test_udf2
文件內(nèi)容如下:
java.lang.Math,min,1,2
java.lang.Math,max,2,3
- 加載數(shù)據(jù)
hive (hive_explode)> load data local inpath '/export/servers/hivedatas/test_udf2' overwrite into table test_udf2;
- 執(zhí)行查詢
hive (hive_explode)> select reflect(class_name,method_name,col1,col2) from test_udf2;
需求3: 判斷是否為數(shù)字
實(shí)現(xiàn)方式:
使用apache commons中的函數(shù)，commons下的jar已經(jīng)包含在hadoop的classpath中，所以可以直接使用。
select reflect("org.apache.commons.lang.math.NumberUtils","isNumber","123")
Hive 窗口函數(shù)
窗口函數(shù)最重要的關(guān)鍵字是 partition by 和 order by
具體語(yǔ)法如下：XXX over (partition by xxx order by xxx)
特別注意：over()里面的 partition by 和 order by 都不是必選的，over()里面可以只有partition by，也可以只有order by，也可以兩個(gè)都沒有，大家需根據(jù)需求靈活運(yùn)用。
窗口函數(shù)我劃分了幾個(gè)大類，我們一類一類的講解。
1. SUM、AVG、MIN、MAX
講解這幾個(gè)窗口函數(shù)前，先創(chuàng)建一個(gè)表，以實(shí)際例子講解大家更容易理解。
首先創(chuàng)建用戶訪問頁(yè)面表：user_pv
create table user_pv(
cookieid string, -- 用戶登錄的cookie，即用戶標(biāo)識(shí)
createtime string, -- 日期
pv int -- 頁(yè)面訪問量
);
給上面這個(gè)表加上如下數(shù)據(jù)：
cookie1,2021-05-10,1
cookie1,2021-05-11,5
cookie1,2021-05-12,7
cookie1,2021-05-13,3
cookie1,2021-05-14,2
cookie1,2021-05-15,4
cookie1,2021-05-16,4
- SUM()使用
執(zhí)行如下查詢語(yǔ)句：
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime) as pv1
from user_pv;
