I have tables with the same name in 2 different schemas. What I want to do is get a count comparison in the 2 tables in the format
TableName : Count1 : Count2
How can I achieve this via Hive query?
I have tables with the same name in 2 different schemas. What I want to do is get a count comparison in the 2 tables in the format
TableName : Count1 : Count2
How can I achieve this via Hive query?
Use UNION ALL:
select 'db1.table_name' table_name, count(col1) count1, count(col2) count2 from db1.table_name
UNION ALL
select 'db2.table_name' table_name, count(col1) count1, count(col2) count2 from db2.table_name
You can do a cross join
of the count queries.
select t1.count1,t2.count2
from (select count(*) as count1 from tbl1) t1
cross join (select count(*) as count2 from tbl2) t2
Try full outer join
select tt1.cn,tt2.cn from
(select count(1) as cn from db1.table) tt1
full outer join
(select count(1) as cn from db2.table ) tt2
on tt1.cn=tt2.cn;