Hive Drop Database

The DROP DATABASE command in Hive is used to delete an entire database along with all its tables, views, and metadata. It’s important to use this command carefully because once we drop a database, we can lose all the associated tables and data (especially in managed tables).

Why Do We Use DROP DATABASE?

There are several situations where we may need to use the DROP DATABASE command:

  • When a project is completed and the database is no longer needed.
  • When we accidentally created a wrong or duplicate database.
  • To clean up and free space in your Hadoop system.
  • To delete test databases after development is done.
Syntax -

Basic DROP DATABASE -

DROP DATABASE database_name;

DROP DATABASE IF EXISTS - Drops database if only it exists

DROP DATABASE IF EXISTS database_name;

DROP DATABASE with CASCADE - If we want to forcefully delete a database even if it has tables, we can use the CASCADE keyword.

DROP DATABASE database_name CASCADE;

DROP DATABASE with RESTRICT - This is the default behavior. Hive will not allow us to drop a database if it contains any tables or views unless we use CASCADE.

DROP DATABASE marketing_db RESTRICT;
Examples -

Scenario1: Check if the database exists:

SHOW DATABASES;

Scenario2: Drop the database safely:

DROP DATABASE IF EXISTS temp_db CASCADE;