Comparing two tables for equality in HIVE

Question

I have two tables, table1 and table2. Each with the same columns:

key, c1, c2, c3

I want to check to see if these tables are equal to eachother (they have the same rows). So far I have these two queries (<> = not equal in HIVE):

select count(*) from table1 t1 
left outer join table2 t2
on t1.key=t2.key
where t2.key is null or t1.c1<>t2.c1 or t1.c2<>t2.c2 or t1.c3<>t2.c3

And

select count(*) from table1 t1
left outer join table2 t2
on t1.key=t2.key and t1.c1=t2.c1 and t1.c2=t2.c2 and t1.c3=t2.c3
where t2.key is null

So my idea is that, if a zero count is returned, the tables are the same. However, I'm getting a zero count for the first query, and a non-zero count for the second query. How exactly do they differ? If there is a better way to check this certainly let me know.

Answer 1

The first one excludes rows where t1.c1, t1.c2, t1.c3, t2.c1, t2.c2, or t2.c3 is null. That means that you effectively doing an inner join.

The second one will find rows that exist in t1 but not in t2.

To also find rows that exist in t2 but not in t1 you can do a full outer join. The following SQL assumes that all columns are NOT NULL:

select count(*) from table1 t1
full outer join table2 t2
on t1.key=t2.key and t1.c1=t2.c1 and t1.c2=t2.c2 and t1.c3=t2.c3
where t1.key is null /* this condition matches rows that only exist in t2 */
   or t2.key is null /* this condition matches rows that only exist in t1 */

Answer 2

If you want to check for duplicates and the tables have exactly the same structure and the tables do not have duplicates within them, then you can do:

select t.key, t.c1, t.c2, t.c3, count(*) as cnt
from ((select t1.*, 1 as which from table1 t1) union all
      (select t2.*, 2 as which from table2 t2)
     ) t
group by t.key, t.c1, t.c2, t.c3
having cnt <> 2;

There are various ways that you can relax the conditions in the first paragraph, if necessary.

Note that this version also works when the columns have NULL values. These might be causing the problem with your data.

Answer 3

Well, the best way is calculate the hash sum of each table, and compare the sum of hash. So no matter how many column are they, no matter what data type are they, as long as the two table has the same schema, you can use following query to do the comparison:

select sum(hash(*)) from t1;
select sum(hash(*)) from t2;

And you just need to compare the return values.

Answer 4

I used EXCEPT statement and it worked.

select * from Original_table
EXCEPT
select * from Revised_table

Will show us all the rows of the Original table that are not in the Revised table.

If your table is partitioned you will have to provide a partition predicate. Fyi, partition values don't need to be provided if you use Presto and querying via SQL lab.

Answer 5

I would recommend you not using any JOINs to try to compare tables:

• it is quite an expensive operations when tables are big (which is often the case in Hive)
• it can give problems when some rows/"join keys" are repeated

(and it can also be unpractical when data are in different clusters/datacenters/clouds).

Instead, I think using a checksum approach and comparing the checksums of both tables is best.

I have developed a Python script that allows you to do easily such comparison, and see the differences in a webbrowser:

https://github.com/bolcom/hive_compared_bq

I hope that can help you!

Answer 6

First get count for both the tables C1 and C2. C1 and C2 should be equal. C1 and C2 can be obtained from the following query

select count(*) from table1

if C1 and C2 are not equal, then the tables are not identical.

2: Find distinct count for both the tables DC1 and DC2. DC1 and DC2 should be equal. Number of distinct records can be found using the following query:

select count(*) from (select distinct * from table1)

if DC1 and DC2 are not equal, the tables are not identical.

3: Now get the number of records obtained by performing a union on the 2 tables. Let it be U. Use the following query to get the number of records in a union of 2 tables:

SELECT count (*)
 FROM 
    (SELECT *
    FROM table1
    UNION
    SELECT *
    FROM table2)

You can say that the data in the 2 tables is identical if distinct count for the 2 tables is equal to the number of records obtained by performing union of the 2 tables. ie DC1 = U and DC2 = U

Answer 7

another variant

select c1-c2 "different row counts"
, c1-c3 "mismatched rows" 
from 
( select count(*) c1 from table1)
,( select count(*) c2 from table2 )
,(select count(*) c3 from table1 t1, table2 t2
    where t1.key= t2.key
    and T1.c1=T2.c1 )

Answer 8

Try with WITH Clause:

With cnt as(
   select count(*) cn1 from table1
   )
   select 'X' from dual,cnt where cnt.cn1 = (select count(*) from table2); 

Answer 9

One easy solution is to do inner join. Let's suppose we have two hive tables namely table1 and table2. Both the table has same column namely col1, col2 and col3. The number of rows should also be same. Then the command would be as follows

**

select count(*) from table1 
inner join table2 
on  table1.col1 = table2.col1 
and table1.col2 = table2.col2
and table1.col3 = table2.col3 ;

**

If the output value is same as number of rows in table1 and table2 , then all the columns has same value, If however the output count is lesser than there are some data which are different.

Answer 10

Use a MINUS operator:

SELECT count(*) FROM
  (SELECT t1.c1, t1.c2, t1.c3 from table1 t1
    MINUS
  SELECT t2.c1, t2.c2, t2.c3 from table2 t2)