如何使用SQL校验Hadoop中交易金额列的小数分隔符合规性

核心校验逻辑 #

合规金额的格式可通过正则表达式^-?\\d+(\\.\\d+)?$覆盖，规则说明：

• 允许可选负号开头，兼容负金额
• 整数部分至少1位数字
• 小数部分可选，仅允许出现1个小数点，小数点后至少1位数字
• 禁止包含逗号、字母、多个小数点/负号等其他非法字符

1. 查询所有不符合规则的异常数据 #

假设表名为transaction_table，待校验金额列名为trans_amt，SQL代码如下：

SELECT 
  trans_amt AS 异常交易金额,
  -- 可选：标注异常类型方便排查
  CASE 
    WHEN trans_amt RLIKE ',' THEN '使用逗号作为分隔符'
    WHEN trans_amt RLIKE '[^0-9.-]' THEN '包含非法字符'
    WHEN trans_amt RLIKE '\\..*\\.' THEN '存在多个小数点'
    WHEN trans_amt RLIKE '-.*-' THEN '存在多个负号'
    ELSE '其他格式错误'
  END AS 异常类型
FROM transaction_table
WHERE trans_amt NOT RLIKE '^-?\\d+(\\.\\d+)?$';

2. 统计各校验结果的数据量 #

如需批量做数据质量统计，可使用如下SQL：

SELECT 
  CASE 
    WHEN trans_amt IS NULL THEN '金额为空'
    WHEN trans_amt RLIKE '^-?\\d+(\\.\\d+)?$' THEN '格式合规'
    WHEN trans_amt RLIKE ',' THEN '异常：使用逗号作为分隔符'
    WHEN trans_amt RLIKE '[^0-9.-]' THEN '异常：包含非法字符'
    WHEN trans_amt RLIKE '\\..*\\.' THEN '异常：存在多个小数点'
    WHEN trans_amt RLIKE '-.*-' THEN '异常：存在多个负号'
    ELSE '异常：其他格式错误'
  END AS 校验结果,
  COUNT(*) AS 数据量
FROM transaction_table
GROUP BY 校验结果;

适配说明 #

• 上述SQL默认适配Hive、Spark SQL，若使用Impala、Presto等引擎，将RLIKE替换为regexp_like(列名, 正则表达式)即可，正则规则通用
• 若业务不允许负金额，将正则中的-?删除即可
• 兼容整数金额、小数末尾补零、前导零等合规场景

内容的提问来源于stack exchange，提问作者MSZ