
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如何处理等等。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/盐析白兔/article/detail/656493