Error while dropping the database in HIVE

InvalidOperationException(message:Database test_db2 is not empty. One or more tables exist.)

If you receive the error listed above, it means that the database you are attempting to drop is not empty. In order to drop the database, the table(s) must be dropped first.

This feature is very good, as most RDBMSs do not have such functionality. If a user has accidentally dropped a database, it’s very hard to get it back. If no timely backup is in place, it’s a nightmare.

What if there are hundreds of tables in the database? It is truly a time consuming operation to drop the tables one by one and then drop the database.

Hive offers RESTRICT and CASCADE modes while dropping the database. Let’s see the drop statement syntax.

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

It is in RESTRICT mode by default. This means that it would not allow the database to be removed if there are any tables.

hive> DROP DATABASE TestDB RESTRICT;
This is equal to the DROP DATABASE TestDB, meaning that if there are no tables in it, the database has to be deleted.

hive> DROP DATABASE IF EXISTS TestDB;
This will return an error if the database contains any tables. This will not return an error if such a database does not exist.

hive> DROP DATABASE IF EXISTS userdb CASCADE;
It drops the database if it is already exists. It will also drop the database even if there are any tables.

Hope you find this article helpful.