【数仓日常踩坑】记录一次特殊符号引发数据异常的排查思路

业务背景：新业务，需求方想要拿到app注册用户的通讯录里同样也注册了此app的好友id，需要用手机号做关联；最终要交付两个字段(注册id，通讯录好友id)，要求全量表

• 上游传过来两条流的数据。一条是(app注册用户的user_id,注册的手机号)，redis数据，每天同步全量数据到数仓；另外一条是(user_id，通讯录里的联系人手机号)，mysql的binlog数据，第一次同步全量数据，后续同步增量数据。

由于是新业务，产品方自己也没见过原始数据的明细是什么样子，数据接入到数仓之后才发现两条流里的手机号，redis同步过来的手机号是明文，mysql的binlog数据同步过来的数据是AES加密之后的，例如：36pE7uow2jJcag60vLkdJw==

用手机号没办法直接做关联，查了一下AES加密算法是对称加密，用同一个密钥对某个手机号加密出来的结果是固定的；

所以问业务方要了密钥，自己尝试写UDF函数，考虑到安全问题，直接对没有加密的手机号进行加密。

redis传来的明文手机号原始数据格式如下：

需要对key做切分，取到手机号，再通过udf函数进行加密

遇到的问题

• 使用自定义AES加密的UDF函数对手机号加密时，会出现很多行空值（不是单个字段空值，整行都为空），sql如下

SELECT
    json_str AS `user_id`
    ,encrypt(split(`key`,':')[2]) AS `phone_number`
FROM database.table
WHERE partition_date='${hivevar:partition_date}'
AND json_str IS NOT NULL AND json_str != ''
1
2
3
4
5
6

查询出来的结果如下：

一条正常数据，一条空数据

排查思路：

1）考虑是否是公司数据开发平台查询结果显示有问题，去掉udf函数单独查询数据，结果正常，没有空行

SELECT
    json_str AS `user_id`
    ,split(`key`,':')[2] AS `phone_number`
FROM database.table
WHERE partition_date='${hivevar:partition_date}'
AND json_str IS NOT NULL AND json_str != ''
1
2
3
4
5
6

2）用udf时，查询用limit = 1作为限制条件，看一条数据输出的是否是两条数据（一条正常，一条空值），结果不出所料，出来了两条数据，暂时定位到是udf函数导致的问题。

3）怀疑上一层的表传来的数据是否有脏数据，查了一下还真有，每天会有一条如下的数据，做split操作时肯定会出问题

4）过滤掉脏数据再从新查询

SELECT
    json_str AS `user_id`
    ,encrypt(split(`key`,':')[2]) AS `phone_number`
FROM database.table
WHERE partition_date='${hivevar:partition_date}'
AND json_str IS NOT NULL AND json_str != ''
AND size(split(`key`, ':')) = 4
1
2
3
4
5
6
7

发现还是存在空行的数据

5）现在确定是udf函数导致的空行数据，怀疑是不是UDF函数里new出来的自定义类AesEncrypt导致的，尝试将其改为单例模式

public class UDFAesEnCrypt extends UDF {
		AesEncrypt aesEncrypt = new AesEncrypt();
    public String evaluate(String phone_number) throws Exception {
        String encodeStr = aesEncrypt.encrypt(phone_number, AesKey.CONTACT);
      	return encodeStr;
    }
}
-----------------------------------------------------------------------------
修改后
public class UDFAesEnCrypt extends UDF {
    public String evaluate(String phone_number) throws Exception {
        AesEncrypt aesEncrypt = AesEncrypt.getInstance();
        String encodeStr = aesEncrypt.encrypt(phone_number, AesKey.CONTACT);
        return encodeStr;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

修改UDF之后从新尝试，依然还有空行的数据，绞尽脑汁排查了好久，检查各种脏数据的情况，都解决不了

6）最后考虑到，udf函数只是对手机号这一个字段进行了加密，但是为什么解析出来的结果中会存在所有字段都为空的情况呢？突然想到是否是换行符导致的，所以拿着上游传来的已经加密的手机号拷贝出来看了一下，结果后面真有换行符，查看了多条数据，存在\t \n \r等多种特殊字符的情况，太坑了，排查了一天的问题

"sSEMUxOxpfuJbpPqu47MKA==\t"
1

7）定位到问题之后，重新修改了我的udf加密函数，对加密后的字符串做了处理，替换掉特殊符号，在sql里从新使用udf加密函数，问题终于解决了，没有出现空行的情况。

8）后面接着做需求，(user_id,注册手机号)、(user_id,通讯录手机号)，现在手机号都是加密过的。

现在需要将两条流里的手机号做JOIN，关联到的数据，输出(user_id, 通讯录好友id)，代码写完运行之后查看结果，发现没有一条关联上的数据，回想起加密时会有特殊符号，我只处理了自己的UDF函数，但是业务方有一条流里传来的是已经加密的手机号，里面肯定也会有特殊符号，于是在sql里对两边的加密数据又做了替换特殊字符的操作，最后结果终于正常了。

跟业务方反馈了一下问题，最终排查到的原因是：

根据RFC822规定，BASE64Encoder编码每76个字符，会加一个回车换行
AES加密方法里Base64类导致的特殊字符出现
public String encrypt(String plainText, AesKey key) throws Exception {
  if (null == plainText || "".endsWith(plainText)) {
    return "";
  }
  Cipher cipher = getCipher(key, CipherMode.ENCRYPT);
  byte[] encrypted = cipher.doFinal(plainText.getBytes());
  return Base64.encodeBase64String(encrypted);
}
1
2
3
4
5
6
7
8
9
10