0

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?

Kshitij Kohli
  • 4,055
  • 4
  • 19
  • 27

3 Answers3

0

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
leftjoin
  • 36,950
  • 8
  • 57
  • 116
0

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
Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
  • Hi, I was able to get the result in the desired format but I had to set hive.strict.checks.cartesian.product to false. By default it was set as true. What is this check and am I putting anything to risk by setting it as true? – Kshitij Kohli Dec 12 '19 at 06:10
0

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; 
Strick
  • 1,512
  • 9
  • 15