hive中的lateral view 与 explode函数，及collect_set函数的使用

拼搏现实的明天。 2023-06-08 13:56 12阅读 0赞

**大纲：**  
    *1、概述  
    2、explode 使用例子  
    3、引入lateral view的原因  
    4、explode与lateral view使用示例1  
    5、explode与lateral view使用示例2  
    6、collect\_set()函数示例  
    7、substr()函数示例  
    8、concat\_ws()函数示例*

## *1、概述* ##

explode与lateral view在关系型数据库中本身是不该出现的，因为他的出现本身就是在操作不满足第一范式的数据（每个属性都不可再分），本身已经违背了数据库的设计原理（不论是业务系统还是数据仓库系统），不过大数据技术普及后，很多类似pv，uv的数据，在业务系统中是存贮在非关系型数据库中，用json存储的概率比较大，直接导入hive为基础的数仓系统中，就需要经过ETL过程解析这类数据，explode与lateral view在这种场景下大显身手。

## * 2、explode 使用例子* ##

explode作用是处理map结构的字段,使用案例如下（hive自带map、struct、array字段类型,但是需要先定义好泛型,所以在此案例不使用）:

### **2.1、建表语句：** ###

> drop table explode\_lateral\_view;
> 
> create table explode\_lateral\_view(  
> \`year\_month\` string,  
> \`week\_sales\` string  
> )  
> ROW FORMAT DELIMITED  
> FIELDS TERMINATED BY '|'  
> STORED AS textfile;

data.txt 内容如下：

> $ cat data.txt   
> 202001|w1\_100,w2\_125,w3\_150,w4\_198  
> 202002|w1\_210,w2\_233,w3\_268,w4\_192  
> 202003|w1\_305,w2\_350,w3\_372,w4\_395

### **2.2、导入数据并展示数据：** ###

导入数据

> hive -e " load data local inpath '/home/hdp-credit/workdir/yaoyingzhe/test\_shell/one.txt' into table explode\_lateral\_view;"  或者
> 
> hive> load data local inpath '/home/hdp-bx-ai/workdir/tmp/data.txt' into table explode\_lateral\_view;  
> Loading data to table hdp\_bx\_ai.explode\_lateral\_view  
> Table hdp\_bx\_ai.explode\_lateral\_view stats: \[numFiles=1, totalSize=69\]  
> OK  
> Time taken: 1.121 seconds

表内数据如下:

> hive> select \* from explode\_lateral\_view;  
> OK  
> 202001    w1\_100,w2\_125,w3\_150,w4\_198  
> 202002    w1\_210,w2\_233,w3\_268,w4\_192  
> 202003    w1\_305,w2\_350,w3\_372,w4\_395
> 
> Time taken: 0.325 seconds, Fetched: 3 row(s)  
> hive> desc explode\_lateral\_view;  
> OK  
> year\_month              string                                        
> week\_sales              string                                        
> Time taken: 0.363 seconds, Fetched: 2 row(s)

### 2.3、explode的使用： ###

我们只拆解array字段，语句和结果如下:

> hive> select year\_month,split(week\_sales,',') from explode\_lateral\_view;  
> OK  
> 202001    \["w1\_100","w2\_125","w3\_150","w4\_198"\]  
> 202002    \["w1\_210","w2\_233","w3\_268","w4\_192"\]  
> 202003    \["w1\_305","w2\_350","w3\_372","w4\_395"\]
> 
> Time taken: 0.215 seconds, Fetched: 3 row(s)hive> select year\_month,split(week\_sales,',')\[0\] from explode\_lateral\_view;  
> OK  
> 202001    w1\_100  
> 202002    w1\_210  
> 202003    w1\_305  
> Time taken: 0.187 seconds, Fetched: 3 row(s)  
> hive> select year\_month,split(week\_sales,',')\[1\] from explode\_lateral\_view;  
> OK  
> 202001    w2\_125  
> 202002    w2\_233  
> 202003    w2\_350  
> Time taken: 0.184 seconds, Fetched: 3 row(s)  
> hive> select year\_month,split(week\_sales,',')\[2\] from explode\_lateral\_view;  
> OK  
> 202001    w3\_150  
> 202002    w3\_268  
> 202003    w3\_372  
> Time taken: 0.185 seconds, Fetched: 3 row(s)  
> hive> select year\_month,split(week\_sales,',')\[3\] from explode\_lateral\_view;  
> OK  
> 202001    w4\_198  
> 202002    w4\_192  
> 202003    w4\_395  
> Time taken: 0.345 seconds, Fetched: 3 row(s)

## *3、引入lateral view的原因* ##

### **3.1、我们想用get\_json\_object来获取key为week\_sales的数据** ###

> **hive> select year\_month,split(week\_sales,',') from explode\_lateral\_view;  
> OK  
> 202001    \["100","125","150","198"\]  
> 202002    \["210","233","268","192"\]  
> 202003    \["305","350","372","395"\]**
> 
> **hive> select year\_month,explode(split(week\_sales,',')) as goods\_id from explode\_lateral\_view;**  
> FAILED: SemanticException \[Error 10081\]: UDTF's are not supported outside the SELECT clause, nor nested in expressions

然后挂了FAILED: SemanticException \[Error 10081\]: UDTF's are not supported outside the SELECT clause, nor nested in expressions UDTF explode不能写在别的函数内；

### ps，介绍下hive自定义函数的三种类型： ###

> UDF自定义函数
> 
> 　　1.首先创建JAVA类，继承UDF.class
> 
> 　　2.重写evaluate()方法；
> 
> 　　3.打jar包；
> 
> 　　4.加载自定义函数的jar包;
> 
> 　　　　hive>add jar /home/hyxy/XXX.jar ;
> 
> 　　　　hive>create temporary function \{function\_name\} as 'com.hyxy.hive.udf.xxx'
> 
> 　　5.自定义函数类型
> 
> 　　　　a.UDF:单行进-->单行出 split(',',comma\_split\_str),select field1
> 
> 　　　　b.UDAF：多行进-->单行出 collect\_set()
> 
> 　　　　c.UDTF：单行进-->多行出 explode函数

**使用UDTF的时候，只支持一个字段，这时候就需要LATERAL VIEW出场了**

## *4、explode与lateral view使用示例1* ##

LATERAL VIEW的使用：  
侧视图的意义是配合explode（或者其他的UDTF），一个语句生成把单行数据拆解成多行后的数据结果集。

> select explode(split(week\_sales,',')) as goods\_id from explode\_lateral\_view;  
> OK  
> w1\_100  
> w2\_125  
> w3\_150  
> w4\_198  
> w1\_210  
> w2\_233  
> w3\_268  
> w4\_192  
> w1\_305  
> w2\_350  
> w3\_372  
> w4\_395  
> Time taken: 0.191 seconds, Fetched: 12 row(s)
> 
> hive> select year\_month,w\_sale from explode\_lateral\_view **lateral view** explode(split(week\_sales,',')) tb\_all\_sale as w\_sale;  
> OK  
> 202001    w1\_100  
> 202001    w2\_125  
> 202001    w3\_150  
> 202001    w4\_198  
> 202002    w1\_210  
> 202002    w2\_233  
> 202002    w3\_268  
> 202002    w4\_192  
> 202003    w1\_305  
> 202003    w2\_350  
> 202003    w3\_372  
> 202003    w4\_395  
> Time taken: 0.306 seconds, Fetched: 12 row(s)
> 
> hive> select year\_month,split(w\_sale,'\_')\[0\] week\_no,split(w\_sale,'\_')\[1\] sale\_num from explode\_lateral\_view lateral view explode(split(week\_sales,',')) tb\_all\_sale as w\_sale;  
> OK  
> 202001    w1    100  
> 202001    w2    125  
> 202001    w3    150  
> 202001    w4    198  
> 202002    w1    210  
> 202002    w2    233  
> 202002    w3    268  
> 202002    w4    192  
> 202003    w1    305  
> 202003    w2    350  
> 202003    w3    372  
> 202003    w4    395  
> Time taken: 0.19 seconds, Fetched: 12 row(s)

**其中*lateral view explode(split(week\_sales,',')) tb\_all\_sale* 相当于一个虚拟表，与原表explode\_lateral\_view笛卡尔积关联。**

它也可以多重使用。

> hive> 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 **;  
> OK  
> 1    \[\{"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"\}\]    a:shandong  
> 2    \[\{"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"\}\]    b:beijing  
>   
> Time taken: 0.135 seconds, Fetched: 2 row(s)

**最终，我们可以通过下面的句子，把每月对应的四周销售数据的一行数据，完全转换成二维表的方式展现。**

> hive> select year\_month,split(w\_sale,'\_')\[0\] week\_no,split(w\_sale,'\_')\[1\] sale\_num from explode\_lateral\_view lateral view explode(split(week\_sales,',')) tb\_all\_sale as w\_sale;  
> OK  
> 202001    w1    100  
> 202001    w2    125  
> 202001    w3    150  
> 202001    w4    198  
> 202002    w1    210  
> 202002    w2    233  
> 202002    w3    268  
> 202002    w4    192  
> 202003    w1    305  
> 202003    w2    350  
> 202003    w3    372  
> 202003    w4    395  
> Time taken: 0.19 seconds, Fetched: 12 row(s)

## 5、尝试将列数据转换行 ##

> hive> select \* from explode\_lateral\_view;  
> OK  
> 202001    w1\_100,w2\_125,w3\_150,w4\_198  
> 202002    w1\_210,w2\_233,w3\_268,w4\_192  
> 202003    w1\_305,w2\_350,w3\_372,w4\_395  
> Time taken: 0.19 seconds, Fetched: 3 row(s)  
> hive> select year\_month,w\_sale from explode\_lateral\_view lateral view explode(split(week\_sales,',')) tb\_all\_sale as w\_sale;  
> OK  
> 202001    w1\_100  
> 202001    w2\_125  
> 202001    w3\_150  
> 202001    w4\_198  
> 202002    w1\_210  
> 202002    w2\_233  
> 202002    w3\_268  
> 202002    w4\_192  
> 202003    w1\_305  
> 202003    w2\_350  
> 202003    w3\_372  
> 202003    w4\_395  
> Time taken: 0.192 seconds, Fetched: 12 row(s)
> 
> hive>
> 
> **SELECT  year\_month  
>         ,collect\_set(w\_sale) as w\_sales\_set  
> from  
> (  
> select year\_month,w\_sale from explode\_lateral\_view lateral view explode(split(week\_sales,',')) tb\_all\_sale as w\_sale  
> ) tb\_final  
> group by year\_month**
> 
> *Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1  
> 2020-08-07 10:14:22,820 Stage-1 map = 0%,  reduce = 0%  
> 2020-08-07 10:14:39,553 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 3.45 sec  
> 2020-08-07 10:14:52,248 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 3.45 sec  
> MapReduce Total cumulative CPU time: 3 seconds 450 msec  
> Ended Job = job\_1595390479217\_783551  
> MapReduce Jobs Launched:   
> Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 5.27 sec   HDFS Read: 0 HDFS Write: 0 SUCCESS  
> Total MapReduce CPU Time Spent: 5 seconds 270 msec*  
>   
> **OK  
> 202001    \["w1\_100","w2\_125","w3\_150","w4\_198"\]  
> 202002    \["w1\_210","w2\_233","w3\_268","w4\_192"\]  
> 202003    \["w1\_305","w2\_350","w3\_372","w4\_395"\]  
> Time taken: 51.212 seconds, Fetched: 3 row(s)**
> 
>   
> hive>
> 
> **SELECT  year\_month  
>         ,concat\_ws('@',collect\_set(w\_sale)) as w\_sales\_str  
> from  
> (  
> select year\_month,w\_sale from explode\_lateral\_view lateral view explode(split(week\_sales,',')) tb\_all\_sale as w\_sale  
> ) tb\_final  
> group by year\_month**
> 
> Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1  
> 2020-08-07 10:15:56,555 Stage-1 map = 0%,  reduce = 0%  
> 2020-08-07 10:16:57,202 Stage-1 map = 0%,  reduce = 0%  
> 2020-08-07 10:17:05,465 Stage-1 map = 67%,  reduce = 0%, Cumulative CPU 4.42 sec  
> 2020-08-07 10:17:07,533 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 5.08 sec  
> 2020-08-07 10:17:19,963 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 7.35 sec  
> MapReduce Total cumulative CPU time: 7 seconds 350 msec  
> Ended Job = job\_1595390479217\_783605  
> MapReduce Jobs Launched:   
> Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 7.35 sec   HDFS Read: 0 HDFS Write: 0 SUCCESS  
> Total MapReduce CPU Time Spent: 7 seconds 350 msec
> 
>   
> **OK  
> 202001    w1\_100@w2\_125@w3\_150@w4\_198  
> 202002    w1\_210@w2\_233@w3\_268@w4\_192  
> 202003    w1\_305@w2\_350@w3\_372@w4\_395  
> Time taken: 107.979 seconds, Fetched: 3 row(s)**

###  6、collect\_set()函数示例 ###

说到explode()函数就不得不说一下collect\_set()函数。collect\_set(col)函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。例如，要统计每种no下的score，直接对no分组后对score进行collect\_set操作，如下：

> hive> desc yyz\_hive\_callback\_user\_info;  
> OK  
> user\_no                 string                  user\_no               
> click\_id                string                  click\_id              
> date\_str                string                  date\_str              
> rule\_code               string                  rule\_code             
> Time taken: 0.424 seconds, Fetched: 4 row(s)  
> hive> select collect\_set(rule\_code) from yz\_hive\_callback\_user\_info;  
> OK  
> \["R00004","E00001","E00002","C00012","C00002","C00014"\]  
> Time taken: 73.458 seconds, Fetched: 1 row(s)  
> hive> select date\_str,collect\_set(rule\_code) from yz\_hive\_callback\_user\_info group by date\_str ;  
> 2019-10-10    \["R00003","E00032","E00033","C00037"\]  
> 2019-10-11    \["E00024",,"C00005","E00026","C00033","E00022"\]  
> 2019-10-12    \["R00008",","C00018","C00031","E00015"\]

select no，collect\_set(score) from tablss group by no;  
这样，就实现了将列转行的功效，但是注意只限同列基本数据类型，函数只能接受一列参数。

### *7、substr()函数示例* ###

substr()是字符串截取函数，其语法为: substr(string A, int start, int len)，返回值为 string类型。说明：返回字符串A从start位置开始，长度为len的字符串。这里需要注意的是初始位置是从1开始。

> hive> select substr(goods\_id,1,3),goods\_id from explode\_lateral\_view;  
> OK  
> 1,2    1,2,3,4,5,6,7,8,9  
> Time taken: 0.219 seconds, Fetched: 1 row(s)  
> hive> select substr(goods\_id,1,4),goods\_id from explode\_lateral\_view;  
> OK  
> 1,2,    1,2,3,4,5,6,7,8,9  
> Time taken: 0.349 seconds, Fetched: 1 row(s)

### *8、concat\_ws()函数示例* ###

hive合并所有电话号码相同的问题内容，用冒号分割

> SELECT B.LDHM, concat\_ws(':',collect\_set(b.WTNR))  
> FROM (  
>         SELECT A.LDHM, A.DJRQ, A.WTNR  
>         FROM TEST1\_12366 A  
>         WHERE A.LDHM IS NOT NULL AND LENGTH(A.LDHM) > 5  
>         ORDER BY A.LDHM, A.DJRQ  
>      ) B  
> GROUP BY B.LDHM;

参考: https://blog.csdn.net/guodong2k/article/details/79459282  
          https://blog.csdn.net/gdkyxy2013/article/details/78683165