2concat*是啥子?
指的是hive中的concat()函数、concat_ws()函数
为啥子写呢?
今天在核对数据的时候发现了一个问题,问题产生的原因是这样的。
核对数据需要参照表对吧?不管是父表还是兄弟姐妹表,总之是要有关联的,才有办法核对数据嘛。
怎么核数呢?
核对表与对照表的数据量、记录数情况
核对表中主键个数及是否存在重复(可能有人会说,既然是主键重复p呀,核算什么?大哥这里用的Hive没有主键的概念哦~)
对照表 && 结果表进行关联核数,根据结果判断是否有异常主键数据
按行核算对比数据(问题来了,怎么按行核算呢?我在这里用的是concat()函数 ,瞬间掉进了坑中… )
大概的SQL如下
select
t1.*,
t2.*,
md5(concat(t2.c1,t2.c2,t2.c3,t2.c4,t2.c5)) as t2_md5,
md5(concat(t1.c1,t1.c2,t1.c3,t1.c4,t1.c5)) as t1_md5
from xxx.xxx1 t1
full outer join xxx.xxx2 t2
on
md5(concat(t2.c1,t2.c2,t2.c3,t2.c4,t2.c5)) = md5(concat(t1.c1,t1.c2,t1.c3,t1.c4,t1.c5))
where t1.c1 is null or t2.c1 is null
;
执行完发现,咋还真对不上数了呢???出现了8条异常数据主键
分析原因:怎么产生的呢?发现关联主键为NULL,怎么产生的NULL呢,发现MD5(concat(t2.c1,t2.c2,t2.c3,t2.c4,t2.c5)) 这一串搞完是NULL,然后去掉MD5函数发现,拼接完就是NULL,去掉拼接函数,发现部分列不为NULL,有一个列为NULL。。。
好了找到了原因, 原来concat函数拼接的字段,一NULL到底呀~
concat函数
CONCAT() 函数用于将多个字符串连接成一个字符串。
- 语法及使用特点:CONCAT(str1,str2,…)
- 返回结果为连接参数产生的字符串。如有任何一个参数为NULL ,则返回值为 NULL。可以有一个或多个参数。
- 样例:
- SELECT concat(’@’,‘1’,‘2’,‘3’,‘4’) 返回结果为
@1234
SELECT concat(’@’,‘1’,‘2’,‘3’,null,‘4’) ; 返回结果为
NULL
在这里要说一句,concat字符串拼接函数如果一旦出现有字段为NULL则结果为NULL呦~
CONCAT_WS函数
如何指定参数之间的分隔符,使用函数CONCAT_WS()。
- 使用语法为:CONCAT_WS(separator,str1,str2,…)
CONCAT_WS() 代表 CONCAT With Separator ,是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串,也可以是其它参数。如果分隔符为 NULL,则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。但是CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL)。
如SELECT CONCAT_WS(’@’,‘1’,‘2’,‘3’,‘4’); 返回结果为
1@2@3@4
SELECT CONCAT_WS(’@’,‘1’,‘2’,‘3’,null,‘4’) ; 返回结果为
1@2@3@4
呕吼,这个竟然忽略了NULL字段,其他不影响拼接~ 你注意过吗???反正我之前没注意
使用过程中注意吧,兄弟~