Skip to content Skip to footer

上亿条数据,如何比对并发现两个表数据差异

目录

一、背景

二、分析流程

三、验数方法

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 © 2088 我的世界杯_瑞奇马丁世界杯主题曲 - msdc8.com All Rights Reserved.
友情链接