hive数据库字段数据格式混合数值处理

hive数据库字段数据格式混合数值处理

最近在工作期间，用hive处理数据库中的数据时，遇到了人工补录的数据质量不规范的情况。数据库字段展示的是机构名称和机构代码，但是在机构名称中混合着机构代码和机构名，中英文都有，这种情况给数据处理带来了很大的麻烦，没办法直接进行多表关联，直接关联将会导致数据关联不上，查询数据丢失甚至是查询失败，经过多次尝试，终于找到了办法将数据甄别出来。

数据示例

如图可见，第二列数据应该为客户号码，但由于人工手动补录，导致数据质量无法得到保证，其中就混杂了客户姓名。这样就会给后续的数据处理人员带来很大程度上的困扰。

因此为了处理这种数据，想到了一个比较简单的方法，就是先将不符合数据格式的数据分离开，通过主键关联其他的表（例如针对本项目将会使用客户信息表进行关联处理），将原有的数据展示在最后一列中，合成一个临时表，再将处理好的两部分数据通过union all拼接在一起。
（union和union all 是两种数据拼接的函数，其差别就在于union会帮助我们自动去重，而union all则是将两部分数据完全拼接在一起）

处理过程

上图中是通过SELECT NVL(CUST_NO+0,0) FROM TABLENAME得到的结果图，当然也可以写作
SELECT COALESCE(CUST_NO+0,0) FROM TABLENAME，都能够得到相同的结果。
由于汉字无法与数字进行相加减，故而通过NVL函数或者COALESCE函数，即可将不符合规范的数据通过where筛选条件筛选出来，最后再关联表进行处理，union all拼接，就可以得到我们自己想要的数据了。

最后得到上图的结果，这样就可以得到一个新的结果表，对数据进行接下来的处理也就方便了。
（由于是私人电脑，没有安装hive编辑器，故使用私人电脑中的mysql进行演示，但是方法都是一样的,附上我的SQL）

SELECT A.*,A.CUST_NO
FROM(SELECT * FROM liang_mart.r_iou_lvl_tab WHERE DATA_DT='20220109' AND COALESCE(CUST_NO+0,-999)<>0)A
UNION ALL
SELECT A.*,B.CUST_NO
FROM(SELECT * FROM liang_mart.r_iou_lvl_tab WHERE DATA_DT='20220109' AND COALESCE(CUST_NO+0,-999)=0)A
LEFT JOIN (SELECT * FROM liang_mart.r_cli_lvl_tab WHERE DATA_DT='20220109')B
  ON A.CUST_NM=B.CUST_NM`

以上只是个人突然想到的，如果大家有更好的方法，请不吝赐教。