Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

How to get total number of hive tables in a database.

How to get total number of hive tables in a database.

New Contributor

Is it possible to get total number of hive tables? If its yes, please suggest me how to do.

3 REPLIES 3
Highlighted

Re: How to get total number of hive tables in a database.

Expert Contributor
@rajendra

use the following to compute the total # of tables you have in Hive.


hive -e "show databases" >db3.out;cat db3.out | while read line; do hive -e "use $line;show tables" >> tables3.out;done; cat tables3.out | wc -l

Re: How to get total number of hive tables in a database.

New Contributor

Approach is good but it also captures unwanted characters like "+------------+" and etc. and after manual cleaning of unwanted symbols from db3.out it also counts the unwanted lines from tables3.out.

Re: How to get total number of hive tables in a database.

@rajendra

you can use the below sql statement in the metastore database.

mysql -u root -e "use hive;SELECT NAME, TBL_NAME FROM DBS as a, TBLS as b where a.DB_ID=b.DB_ID;"> tables.txt

tables.txt will have the list of all tables.

Don't have an account?
Coming from Hortonworks? Activate your account here