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

最新推荐文章于 2024-08-07 03:43:31 发布

孤数不证

最新推荐文章于 2024-08-07 03:43:31 发布

阅读量2.5w

点赞数 3

本文链接：https://blog.csdn.net/weixin_43668299/article/details/94967204

版权

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;

突破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;

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;

上述用的到的 collect_set 函数，有两个作用，第一个是去重，去除group by后的重复元素，
第二个是形成一个集合，将group by后属于同一组的第三列集合起来成为一个集合。与contact_ws
结合使用就是将这些元素以逗号分隔形成字符串

五，行转列的函数。

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

用于array的语法如下：

select explode(arraycol) as newcol from tablename;

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

用于map的语法如下：

select explode(mapcol) as (keyname,valuename) from tablename;

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