事件地址解析总结

单单纯纯想在开头加个图

这段时间在负责事件地址解析工作，有些枯燥。和QSY小姐姐一起做的，就没有那么枯燥啦，哈哈哈。
简单总结一下。

什么是事件地址解析

当交易行为发生时，交易系统需要记录行为的来源，如IP、MAC、IMEI、UUID、手机号、电话号、CPU、磁盘序列号、终端产品类型、终端版本号。源系统通常会将这些信息保存为一个字段，当这个字段入数仓时，需要将起解析分拆。
样例：

MIP:127.0.0.1;MPN:13912344321;MAC:EF12AB23CD56;

为什么做事件地址解析

这个工作主要是为了配合反洗钱的要求。那么问题来了，什么是反洗钱呢？
参见另一文章反洗钱与可疑交易的相关知识

解决问题思路

前辈思路：
依赖源系统的标识符的顺序，例如源系统的顺序是IP、MAC、UUID，那我们就将第一个取出作为IP,以此类推。这种方式的弊端是，如果源系统更换版本，顺序换了，我们解析出的标识就乱了。并且不同的源系统顺序不同，情况分好多种，后期维护的成本巨大。
优化的思路：
依赖标识符和分隔符截取。此处以分隔符为':'和';'举例，实际还有'='和'|'搭配的情况。

CASE  
        WHEN TRANSLATE_CHK(COALESCE(TRIM(field),'') USING Latin_to_Unicode) <> 0
        THEN ''
        WHEN field <> '' AND INSTR(field ,'MAC:',1,1) > 0
        THEN SUBSTR(field ||';', INSTR(field ||';', ':', INSTR(field ||';','MAC:',1,1), 1) +1,  
        INSTR(field ||';', ';', INSTR(field ||';','MAC:',1,1), 1) 
        -INSTR(field ||';', ':', INSTR(field ||';','MAC:',1,1), 1) - 1) 
        ELSE ''
    END AS MAC

遇到的问题与踩到的坑

1. 要先判断字段是否发生了乱码

TRANSLATE_CHK(field USING Latin_to_Unicode)<>0 

判断不乱码应该是首个条件，否则会报错：

The string contains an untranslatable character.

该种方式不能囊括所有的乱码问题，
可以尝试反向，或者：CAST(original_field AS VARCHAR(255))

2. 在字段后手动加上分隔符
   原因：避免field结尾无分隔符的情况。

MIP:127.0.0.1;MPN:13912344321;MAC:EF12AB23CD56

3. TD的ETLAdmin.jar中，insert语句中字段顺序和后面select语句中字段顺序要高度一致，否则会出现串行的问题。

4. 部分源系统IMEI存储的是UUID。
   解决方案：

1. IMEI=按照标识符截取的'IMEI'
2. SET UUID = IMEI where IMEI符合UUID正则规则
3. SET IMEI = ''where IMEI符合UUID正则规则

5. 最让人头大的是，有些终端信息无标识符。
   解决方案：按照正则取值。正则规则参见：相关正则
   语法为：

UPDATE table
SET IP = REGEXP_SUBSTR(filed,  '正则规则'  ,1,1,'i') 
WHERE REGEXP_SIMILAR(filed, '.*正则规则.*') = 1;