找出两个异构数据库的差异（找出其中的缺失记录）

 

 

-- oracle
select
     substr(md5(key_id),1,1) as flag
    ,count(1)             as cnt
from (
    select 1  as key_id union all 
    select 2  as key_id union all 
    select 3  as key_id union all 
    select 4  as key_id union all 
    select 5  as key_id union all 
    select 6  as key_id union all 
    select 7  as key_id union all 
    select 8  as key_id union all 
    select 9  as key_id union all 
    select 10 as key_id
) t1
group by 
    substr(md5(key_id),1,1)
;
+------+-----+
| flag | cnt |
+------+-----+
| 1    |   1 |
| 4    |   1 |
| 8    |   1 |
| a    |   1 |
| c    |   3 |
| d    |   1 |
| e    |   2 |
+------+-----+

-- hive
select
     substr(md5(key_id),1,1) as flag
    ,count(1)             as cnt
from (
    select 1  as key_id union all 
    select 2  as key_id union all 
    select 3  as key_id union all 
    select 4  as key_id union all 
    select 5  as key_id union all 
    select 6  as key_id union all 
    select 7  as key_id union all 
    select 9  as key_id union all 
    select 10  as key_id
) t1
group by 
    substr(md5(key_id),1,1)
;
+------+-----+
| flag | cnt |
+------+-----+
| 1    |   1 |
| 4    |   1 |
| 8    |   1 |
| a    |   1 |
| c    |   2 |
| d    |   1 |
| e    |   2 |
+------+-----+

-- 经过比对，flag c有差异
-- oracle
select
     substr(md5(key_id),1,2) as flag
    ,count(1)             as cnt
from (
    select 1  as key_id union all 
    select 2  as key_id union all 
    select 3  as key_id union all 
    select 4  as key_id union all 
    select 5  as key_id union all 
    select 6  as key_id union all 
    select 7  as key_id union all 
    select 8  as key_id union all 
    select 9  as key_id union all 
    select 10 as key_id
) t1
where substr(md5(key_id),1,1) = 'c'
group by 
    substr(md5(key_id),1,2)
;
+------+-----+
| flag | cnt |
+------+-----+
| c4   |   1 |
| c8   |   1 |
| c9   |   1 |
+------+-----+

-- hive
select
     substr(md5(key_id),1,2) as flag
    ,count(1)             as cnt
from (
    select 1  as key_id union all 
    select 2  as key_id union all 
    select 3  as key_id union all 
    select 4  as key_id union all 
    select 5  as key_id union all 
    select 6  as key_id union all 
    select 7  as key_id union all 
    select 9  as key_id union all 
    select 10 as key_id
) t1
where substr(md5(key_id),1,1) = 'c'
group by 
    substr(md5(key_id),1,2)
;
+------+-----+
| flag | cnt |
+------+-----+
| c4   |   1 |
| c8   |   1 |
+------+-----+

-- 经过比对，flag c9有差异
-- oracle
select t1.*
from (
    select 1  as key_id union all 
    select 2  as key_id union all 
    select 3  as key_id union all 
    select 4  as key_id union all 
    select 5  as key_id union all 
    select 6  as key_id union all 
    select 7  as key_id union all 
    select 8  as key_id union all 
    select 9  as key_id union all 
    select 10 as key_id
) t1
where substr(md5(key_id),1,2) = 'c9'
;