Search code examples
hivehdfshive-metastorehiveddl

How to find the number of tables created in a database in hive?

I need to find out the number of tables created in each schemas and find out the size occupied by each schema.

Solution

  • This is possible to do using shell script

    1. Calculate the rows in command output hive -S -e "set hive.cli.print.header=false; use $schema; show tables;" | wc -l Where $schema is your schema name

    2. The size of schema is a little bit tricky. Each table in the schema can have it's own location in HDFS that is different from schema default location. You need to loop through schema tables (see previous command), describe formatted each table, parse table location, get location size and sum all table locations size in HDFS. To get table location size use this command: hdfs hadoop fs -du [table location].