Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

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

avatar
Contributor

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

4 REPLIES 4

avatar
Master Collaborator
@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

avatar
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.

avatar
@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.

avatar
Contributor

If you are using the Postgres DB then use the below query to get table information :

 

1. log in to DB server and execute the below query :

 

./psql -d hive -c "SELECT \"NAME\", \"TBL_NAME\" FROM \"DBS\" as a, \"TBLS\" as b where a.\"DB_ID\"=b.\"DB_ID\";" > /tmp/tables1.txt

 > awk '{print $1" " $3}' tables1.txt >> tables.txt
 > cat /tmp/tables1.txt | wc -l