CODEWITHSUNDEEP
mysqlhive
User12345
User12345

Reputation: 5480

How to find empty tables in hive database

I have a hive database records with 50 tables;

I want to check if any tables are empty.

The database name is employee.

I don't want to do this manually i.e Do a select * query on each table individually.

Can anyone explain

Upvotes: 0

Views: 1375

Answers (1)

franklinsijo
franklinsijo

Reputation: 18300

Hive does not keep track of the number of records present in a table. Only during the query execution, the files belonging to the particular table is read and processed. So there is no other way to know the number of records present in each table without querying each table individually.

Alternatively, You can run a disk usage command on the database directory in HDFS

hdfs dfs -du -s -h <hive.warehouse.dir>/employee/*

the table folders with 0B are obviously empty.

This is possible because Hive stores the table files in the HDFS LOCATION given at the time of table creation or at the path mentioned for hive.warehouse.dir property in hive-site.xml. Default is /user/hive/warehouse.

If the tables are a managed tables, for the database employee all the tables' records will be stored under <hive.warehouse.dir>/employee/.

Upvotes: 2

Related Questions