如何在Hive中使用SQL实现decimal(5,2)类型字段的合规性校验

首先明确decimal(5,2)的合法格式要求：总有效位数为5位，其中小数部分固定占2位，整数部分最多占3位，例如123.45为合法值，12.2、1234.56、12.345均为非法值。

原有SQL的问题 #

• 正则写法不符合Hive语法：Hive正则匹配不需要添加/开头结尾和gm修饰符，该写法仅适用JavaScript等语言，Hive无法识别
• 正则逻辑错误：[\d]{5}.[\d]{2}要求整数部分5位+小数2位，总有效位已达7位，完全不符合decimal(5,2)的规则，且小数点未转义，会匹配任意字符
• 函数使用错误：REGEXP_REPLACE是字符串替换函数，你的需求是筛选非法记录，应该用RLIKE/REGEXP运算符做匹配过滤

正确校验SQL #

核心正则规则（适配Hive语法） #

匹配合法decimal(5,2)格式的正则为：^[-+]?\\d{1,3}\\.\\d{2}$，各部分含义：

• ^ 匹配字符串开头
• [-+]? 匹配可选正负号，若业务不允许负数值可删除该段
• \\d{1,3} 匹配1-3位整数，符合decimal(5,2)整数部分最多3位的要求
• \\. 转义后匹配小数点
• \\d{2} 匹配固定2位小数
• $ 匹配字符串结尾

完整查询&存储异常数据的SQL #

你可以直接将筛选出的非法数据存入异常表用于后续分析，示例如下：

-- 可根据业务需求调整异常表结构和字段
INSERT INTO apl_loan_tape_ci.tbl_check_error_result
SELECT
  KEY_FIELD,
  'CRE_INCM_CRRNCY字段不符合decimal(5,2)格式规范' AS error_type,
  CRE_INCM_CRRNCY AS error_value,
  current_timestamp() AS check_time
FROM apl_loan_tape_ci.tbl_garant_lt
WHERE
  -- 过滤NULL值异常
  CRE_INCM_CRRNCY IS NULL
  -- 过滤格式不符合要求的异常
  OR CRE_INCM_CRRNCY NOT RLIKE '^[-+]?\\d{1,3}\\.\\d{2}$';

扩展适配场景 #

• 若不允许数值有前导零（例如排除012.34这类值），可将正则调整为：^[-+]?(0|[1-9]\\d{0,2})\\.\\d{2}$
• 若需要额外校验数值范围（确保在-999.99~999.99之间），可在WHERE条件中追加：OR CAST(CRE_INCM_CRRNCY AS DECIMAL(5,2)) NOT BETWEEN -999.99 AND 999.99

内容的提问来源于stack exchange，提问作者Jose Maria Acevedo Reinoso