Impala - Drop a Database

The DROP DATABASE Statement of Impala is used to remove a database from Impala. Before deleting the database, it is recommended to remove all the tables from it.

Syntax

Following is the syntax of DROP DATABASE Statement.

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

Here, IF EXISTS is an optional clause. If we use this clause when a database with the given name exists, then it will be deleted. And if there is no existing database with the given name, then no operation is performed.

Example

Following is an example of DROP DATABASE statement. Assume you have a database in Impala with the name sample_database.

And, if you verify the pst of databases using the SHOW DATABASES statement, you will observe the name in it.

[quickstart.cloudera:21000] > SHOW DATABASES;

Query: show DATABASES
+-----------------------+ 
| name                  | 
+-----------------------+ 
| _impala_builtins      | 
| default               | 
| my_db                 | 
| sample_database       | 
+-----------------------+ 
Fetched 4 row(s) in 0.11s

Now, you can delete this database using the DROP DATABASE Statement as shown below.

 < DROP DATABASE IF EXISTS sample_database;

This will delete the specified database and gives you the following output.

Query: drop DATABASE IF EXISTS sample_database;

Verification

You can verify whether the given database is deleted, using the SHOW DATABASES statement. Here you can observe that the database named sample_database is removed from the pst of databases.

[quickstart.cloudera:21000] > SHOW DATABASES;

Query: show DATABASES 
+----------------------+ 
| name                 | 
+----------------------+ 
| _impala_builtins     | 
| default              | 
| my_db                | 
+----------------------+ 
Fetched 3 row(s) in 0.10s 
[quickstart.cloudera:21000] >

Cascade

In general, to delete a database, you need to remove all the tables in it manually. If you use cascade, Impala removes the tables within the specified database before deleting it.

Example

Suppose there is a database in Impala named sample, and it contains two tables, namely, student and test. If you try to remove this database directly, you will get an error as shown below.

[quickstart.cloudera:21000] > DROP database sample;
Query: drop database sample 
ERROR: 
ImpalaRuntimeException: Error making  dropDatabase  RPC to Hive Metastore: 
CAUSED BY: InvapdOperationException: Database sample is not empty. One or more 
tables exist.

Using cascade, you can delete this database directly (without deleting its contents manually) as shown below.

[quickstart.cloudera:21000] > DROP database sample cascade; 
Query: drop database sample cascade

Note − You cannot delete the “current database” in Impala. Therefore, before deleting a database, you need to make sure that the current context is set to the database other than the one which you are going to delete.

Deleting a Database using Hue Browser

Open Impala Query editor and type the DELETE DATABASE statement in it and cpck on the execute button as shown below. Suppose there are three databases, namely, my_db, my_database, and sample_database along with the default database. Here we are deleting the database named my_database.

After executing the query, gently move the cursor to the top of the dropdown menu. Then, you will find a refresh symbol as shown in the screenshot given below. If you cpck on the refresh symbol, the pst of databases will be refreshed and the recent changes done will be appped to it.

Verification

Cpck on the drop down under the heading DATABASE on the left-hand side of the editor. There, you can see a pst of databases in the system. Here you can observe the newly created database my_db as shown below.

If you observe carefully, you can see only one database, i.e., my_db in the pst along with the default database.