上亿条数据，如何比对并发现两个表数据差异 |
您所在的位置：网站首页 › 上亿条数据查询 › 上亿条数据，如何比对并发现两个表数据差异 |
|
目录 一、背景 二、分析流程 三、验数方法 3.1 数据量级比对 3.2 一致性比对 3.2.1 勾稽验证+md5方法 3.2.2 暴力比对法 3.3 差异数据发现 四、总结 本文是考拉验数(自动化验数)设计方案和实现中核心的验数SQL。 一、背景做数据，经常遇到数据验证，很烦很枯燥，即耗时又耗人，但又必须去做。如何去做数据验证，并标准化整个流程，让验数变得轻松。 二、分析流程…… 相同表结构数据验证：比如修改表逻辑 相似表结构数据验证：比如修改表字段。 新表数据校验：比如新开发了表，选择一个比对表参考等等 三、验数方法数据验证三步走： 数据量级比对：先比对两个表核心字段数据量级，如果量级不同，两个表数据肯定不一致。一致性比对：如果量级相同，比对一致性。差异数据发现：如果数据不一致，把不一致的数据打印出来。 3.1 数据量级比对 select left_table.pv-right_table.pv as pv_diff, left_table.user_id_uv - right_table.user_id_uv as user_id_uv_diff, left_table.order_id_uv - right_table.order_id_uv as order_id_uv_diff, left_table.city_id_uv - right_table.city_id_uv as city_id_uv_diff from ( select count(1) as pv, count(distinct user_id) as user_id_uv, count(distinct order_id) as order_id_uv, count(distinct city_id) as city_id_uv from mart_online.fact_user_order_day where dt=20190413 )left_table left outer join ( select count(1) as pv, count(distinct user_id) as user_id_uv, count(distinct order_id) as order_id_uv, count(distinct city_id) as city_id_uv from mart_test.fact_user_order_day where dt=20190413 )right_table on 1=1 左表pv减去右表pv值为：[0]，核心字段uv差为：[0] 即两个表数据条数相同 +-------+----------------+------------------+---------------+ |pv_diff|user_id_uv_diff |order_id_uv_diff |city_id_uv_diff| +-------+----------------+------------------+---------------+ | 0| 0| 0| 0| +-------+----------------+------------------+---------------+ 3.2 一致性比对 3.2.1 勾稽验证+md5方法勾稽是一个小姑娘起的名字，在这里就是看一下左表不为NULL的left_table_num，右表不为NULL的right_table_num，两个表都有的 left_right_equal_num，如果这三个数相等就说明数据是一致的。反之数据肯定不一致，同时可以计算出不一致的条数。 md5：就是计算一行数据的md5值，把它当成key去做比对。尤其是在百亿数据规模的情况下，这种方法也使用。 ************ 数据量一致性验证SQL ************* 注意：这里采用 full join select sum(case when left_table.record_key is not null or left_table.record_key !='' then 1 else 0 end) as left_table_num, sum(case when right_table.record_key is not null or right_table.record_key !='' then 1 else 0 end) as right_table_num, sum(case when left_table.record_key = right_table.record_key then 1 else 0 end) as left_right_equal_num from ( select md5( concat( if(user_id is null, '-', cast(user_id as string)), if(user_name is null, '-', cast(user_name as string)), if(order_id is null, '-', cast(order_id as string)), if(city_id is null, '-', cast(city_id as string)), if(city_name is null, '-', cast(city_name as string)), if(字段n…… is null, '-', cast(字段n…… as string)), if(dt is null, '-', cast(dt as string)) ) ) as record_key from mart_online.fact_user_order_day where dt=20190413 )left_table full outer join ( select md5( concat( if(user_id is null, '-', cast(user_id as string)), if(user_name is null, '-', cast(user_name as string)), if(order_id is null, '-', cast(order_id as string)), if(city_id is null, '-', cast(city_id as string)), if(city_name is null, '-', cast(city_name as string)), if(字段n…… is null, '-', cast(字段n…… as string)), if(dt is null, '-', cast(dt as string)) ) ) as record_key from mart_test.fact_user_order_day where dt=20190413 )right_table on left_table.record_key=right_table.record_key ************ 数据量一致性验证报表 ************* [left_table_num]左表中的数据条数，[right_table_num]右表中的条数，[left_right_equal_num]两个表中相等的数据条数。 左表中有[5660]条数据和右表不一致！ +--------------+---------------+--------------------+ |left_table_num|right_table_num|left_right_equal_num| +--------------+---------------+--------------------+ | 16358699| 16358699| 16353039| +--------------+---------------+--------------------+ 3.2.2 暴力比对法适合具有唯一ID的表，返回空说明验证准确。 select online.*, test.* from( select id, user_id, user_name, order_id, city_id, city_name from mart_online.fact_user_order_day where dt='20190413' )online left outer join ( select id, user_id, user_name, order_id, city_id, city_name from mart_test.fact_user_order_day where dt='20190413' ) test on test.id=online.id where test.user_id!=online.user_id or test.user_name!=online.user_name or test.order_id!=online.order_id or test.city_id!= online.city_id or test.city_name!= online.city_name 3.3 差异数据发现发现差异数据的方法很多，这里只讲一个通用的方法：逐条比对法（假定两个表有唯一的ID，如果没有唯一ID，其实md5不一样的数据就不一致），这种方法适合小规模数据，当然我们真是实现的时候是结合一致性验证的情况，直接就能找到差异的数据并打印出来。 select left_table.*, right_table.* from ( select * from mart_online.fact_user_order_day where dt=20190413 )left_table full outer join ( select * from mart_test.fact_user_order_day where dt=20190413 )right_table on left_table.id = right_table.id and left_table.dt = right_table.dt where COALESCE(left_table.user_id, 0) COALESCE(right_table.user_id, 0) or COALESCE(left_table.user_name, 0) COALESCE(right_table.user_name, 0) or COALESCE(left_table.order_id, 0) COALESCE(right_table.order_id, 0) or COALESCE(left_table.city_id, 0) COALESCE(right_table.city_id, 0) or COALESCE(left_table.city_name, 0) COALESCE(right_table.city_name, 0) or COALESCE(left_table.字段n……, 0) COALESCE(right_table.字段n……, 0) 不一致的条数：[5660]，case如下表所示： +-------+----------------+------------------+---------------+---------------+ |id |left_user_id |left_字段n…… |right_user_id |right_字段n…… | +-------+----------------+------------------+---------------+---------------+ | 0| 1| 哇哈哈| 1| 养乐多| +-------+----------------+------------------+---------------+---------------+ 四、总结如上验数SQL，可以通过代码封装，自动生成，就可以做成自动化数据验证的小工具了。真实情况比较复杂，要考虑字段的识别，where条件，两个表是否有唯一ID，没有唯一ID如何处理等等。 |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |