hive中的拼接函数contact,concat_ws,collect_set()及explode()，lateral view函数

╰半橙微兮° 2023-07-12 09:13 4阅读 0赞

hive工作中用到的一些拼接函数。

一， concat(string s1, string s2, string s3)  
这个函数能够把字符串类型的数据连接起来，连接的某个元素可以是列值。  
如 concat( aa, ‘:’, bb) 就相当于把aa列和bb列用冒号连接起来了，aa:bb。

二， cast  
用法：cast(value as type)  
功能：将某个列的值显示的转化为某个类型  
例子：cast(age as string ) 将int类型的数据转化为了String类型

三，Hive中collect相关的函数有collect\_list和collect\_set(列转行）。

它们都是将分组中的某列转为一个数组返回，不同的是collect\_list不去重而collect\_set去重。

collect\_set(字段):根据某个字段分组后，把分在一组的数据合并在一起，默认分隔符’,’

这样，就实现了将列转行的功效，但是注意只限同列基本数据类型，函数只能接受一列参数。  
      
    collect\_set(col)函数只接受基本数据类型，  
    它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

select no，collect\_set(score) from tablss group by no;  
1  
2  
3  
4  
5  
6  
突破group by限制  
还可以利用collect来突破group by的限制，Hive中在group by查询的时候要求出现在select后面的列都必须是出现在group by后面的，即select列必须是作为分组依据的列，但是有的时候我们想根据A进行分组然后随便取出每个分组中的一个B，代入到这个实验中就是按照用户进行分组，然后随便拿出一个他看过的视频名称即可：

select username, collect\_list(video\_name)\[0\] from t\_visit\_video group by username;  
1  
video\_name不是分组列，依然能够取出这列中的数据。

四，concat\_ws(seperator, string s1, string s2…)  
功能：制定分隔符将多个字符串连接起来，实现“列转行”（但常常结合group by与collect\_set使用）

使用函数CONCAT\_WS（）。使用语法为：CONCAT\_WS(separator,str1,str2,…)  
CONCAT\_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。但是CONCAT\_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。

有表结构a string , b string , c int  
数据为  
c d 1  
c d 2  
c d 3  
e f 4  
e f 5  
e f 6  
想要得到  
c d 1,2,3  
e f 4,5,6  
语句如下

select a, b, concat\_ws(',' , collect\_set(cast(c as string)))  
from table group by a,b;  
1  
2  
上述用的到的 collect\_set 函数，有两个作用，第一个是去重，去除group by后的重复元素，  
第二个是形成一个集合，将group by后属于同一组的第三列集合起来成为一个集合。与contact\_ws  
结合使用就是将这些元素以逗号分隔形成字符串

五，行转列的函数。

1，explode()的功能是将一行数据转换成列数据，可以用于array和map类型的数据。

用于array的语法如下：

select explode(arraycol) as newcol from tablename;  
1  
explode()：函数中的参数传入的是arrary数据类型的列名。  
newcol：是给转换成的列命名一个新的名字，用于代表转换之后的列名。  
tablename：原表名。

用于map的语法如下：

select explode(mapcol) as (keyname,valuename) from tablename;  
1  
explode()：函数中的参数传入的是map数据类型的列名。  
由于map是kay-value结构的，所以它在转换的时候会转换成两列，一列是kay转换而成的，一列是value转换而成的。  
keyname：表示key转换成的列名称，用于代表key转换之后的列名。  
valuename：表示value转换成的列名称，用于代表value转换之后的列名称。  
注意：这两个值需要在as之后用括号括起来然后以逗号分隔。

以上为explode()函数的用法，此函数存在局限性：

其一：不能关联原有的表中的其他字段。  
其二：不能与group by、cluster by、distribute by、sort by联用。  
其三：不能进行UDTF嵌套。  
其四：不允许选择其他表达式。

2、lateral view  
lateral view是Hive中提供给UDTF的结合，它可以解决UDTF不能添加额外的select列的问题。

lateral view其实就是用来和想类似explode这种UDTF函数联用的，lateral view会将UDTF生成的结果放到一个虚拟表中，然后这个虚拟表会和输入行进行join来达到连接UDTF外的select字段的目的。

格式一  
lateral view udtf(expression) tableAlias as columnAlias (,columnAlias)\*

lateral view在UDTF前使用，表示连接UDTF所分裂的字段。

UDTF(expression)：使用的UDTF函数，例如explode()。  
tableAlias：表示UDTF函数转换的虚拟表的名称。  
columnAlias：表示虚拟表的虚拟字段名称，如果分裂之后有一个列，则写一个即可；如果分裂之后有多个列，按照列的顺序在括号中声明所有虚拟列名，以逗号隔开。

格式二

from basetable (lateral view)\*  
在from子句中使用，一般和格式一搭配使用，这个格式只是说明了lateral view的使用位置。  
from子句后面也可以跟多个lateral view语句，使用空格间隔就可以了。  
格式三  
from basetable (lateral view outer)\*  
它比格式二只是多了一个outer，这个outer的作用是在UDTF转换列的时候将其中的空也给展示出来，UDTF默认是忽略输出空的，加上outer之后，会将空也输出，显示为NULL。这个功能是在Hive0.12是开始支持的。

注：  
explode()及lateral view（）函数详解请见  
https://blog.csdn.net/guodong2k/article/details/79459282  
https://www.liangzl.com/get-article-detail-121725.html  
https://blog.csdn.net/weixin\_36630761/article/details/78416341