Select count(*) from multiple tables in Hive

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?

3 answers

  • answered 2019-12-09 15:15 leftjoin

    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
    

  • answered 2019-12-09 15:15 Vamsi Prabhala

    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
    

  • answered 2019-12-10 10:14 Strick

    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;