关于大数据:MaxCompute中如何处理异常字符

背景

在解决数据时，当业务数据同步至MaxCompute后，会产生一些含异样字符的脏数据，比方字段中蕴含了一个不可见字符，在DataWorks中显示不进去，但在BI界面又会显示成其余字符，影响整体观感。这种状况，通常咱们的解法是，将异样的字符洗掉，上面来介绍几种常见的解决异样字符的办法。

问题形容

定位

如下图，能够看到“异样name”和“失常name”的 length值 不同，多了个不可见字符，然而咱们并不能看进去啥。前期做数据处理或数据展现可能成为一个难以定位的问题。

SELECT 
    name as 异样name,
    LENGTH(name) as 异样name长度,
    '北京'  as 失常name,
    LENGTH('北京') as 失常name长度
from tbl1 
where name RLIKE '北京';

后果：

小技巧

• 咱们能够通过在线Unicode编码转换工具，将数值粘贴过来，获取到对应的Unicode码。同理也能够获取其余异样字符的Unicode码，以便后续解决。
• 输出异样 vs 失常的字符串，比照 Unicode 差别能够倒推不可见字符为“ \u200b” 。

解决方案

• 定位到问题后，回顾数据荡涤的惯例计划，想方法把消掉这种不可见字符

计划1：trim() – 替换

• MaxCompute的trim()函数反对通过设置参数的形式调特色字符
• TRIM相干文档：https://help.aliyun.com/document_detail/455667.html

成果如下：

• 利用 trim() 函数将数值中的异样不可见字符替换为失常空值字符（不可见字符可通过在线Unicode编码转换工具Unicode转中文复制一下）

SELECT 
    name as 异样name,
    LENGTH(name) as 异样name长度,
    trim(name,'')  as 失常name,
  LENGTH(trim(name,'')) as 失常name长度
from tbl1 
where name RLIKE '北京';

后果：

计划2：replace() – 替换

• 长处：点对点思路解决问题，方便快捷
• 毛病：实用待替换的字符只有一个状况，如果有多个，须要再套一层replace函数，不容易保护
• REPLACE相干文档：https://help.aliyun.com/document_detail/455611.html

成果如下：

• 利用 replace() 函数将数值中的异样不可见字符替换为失常空值字符（不可见字符可通过在线Unicode编码转换工具Unicode转中文复制一下）

SELECT 
 name as 异样name,
 LENGTH(name) as 异样name长度,
 replace(name,'','')  as 失常name,
 LENGTH(replace(name,'','')) as 失常name长度
from tbl1 
where name RLIKE '北京';

后果：

计划4：正则表达式 – 替换

• 应用函数一层一层替换会比拟麻烦，目前MaxCompute反对通过正则表达式匹配出异样的字符
• 只有筛选出了带有异样的一部分字符，再解决就不便很多了
• 例如上文中的不可见字符，Unicode码为“\u200b”，通过执行如下SQL

select id,name from tbl1 where name rlike '[\x{200b}]';

后果返回：

• 此时如果再用regexp_replace()函数能够替换掉这一部分含不可见字符的数值

-- regexp_replace()函数
SELECT 
 name as 异样name,
 LENGTH(name) as 异样name长度,
 regexp_replace(name, '[\x{200b}]', '',0)  as 失常name,
 LENGTH(regexp_replace(name, '[\x{200b}]', '',0)) as 失常name长度
FROM tbl1;

后果：

总结

针对于SQL中的不可见字符或者其余中文等异样字符，都能够应用计划3中的筛选形式筛进去，而后再做后续的替换或其余操作就能够了。

【MaxCompute 已公布收费试用打算，为数仓建设提速】新用户可 0 元支付 5000CU*小时计算资源与 100GB 存储，有效期 3 个月。  立刻支付>>

欢送各位开发者退出大数据计算 MaxCompute 社区。立刻退出