Number of columns each table has in Hive

[#1]

Hello,

How do we fetch the number of columns each table has in each DB from HMS DB?

I have an urgent need to do a comparison for the number of columns each table has in each DB between 2 clusters. Doing that manually is practically impossible.

Kindly help

Thanks

snm1523

[#2 · Accepted solution]

@snm1523 From beeline

use sys;
select cd_id, count(cd_id) as column_count from columns_v2 group by cd_id order by cd_id asc;  -- this will return column_count for each table

Every individual table will have a unique cd_id. To map the table names with cd_id, try the following.

select t.tbl_name, s.cd_id from tbls t join sds s where t.sd_id=s.sd_id order by s.cd_id asc;

You could also merge the two queries to get the o/p together.

[#3]

Thank you again @smruti 😊