impala deletes the database

Impala's DROP DATABASE statement is used to remove the database from Impala. I t is recommended that you delete all tables from the database before deleting it.

Grammar

The following is the syntax of the DROP DATABASE statement.

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

Here, IF EXISTS is an optional clause. I f we use this clause when we have a database with a given name, it will be deleted. I f there is no existing database with a given name, no action is taken.

Cases

The following is an example of a DROP DATABASE statement. S uppose you have a database in Impala sample_database name.

Also, if you use the SHOW DATABASES statement to validate the database list, you will observe the names 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

You can now use the DROP DATABASE statement to delete this database, as shown below.

 < DROP DATABASE IF EXISTS sample_database;

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

Query: drop DATABASE IF EXISTS sample_database;

Verify

You can use the SHOW DATABASES statement to verify that a given database has been deleted. H ere, you can observe that a database sample_database the database is removed from the database list.

[quickstart.cloudera:21000] > SHOW DATABASES;

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

Cascading

In general, to delete a database, you need to manually delete all of its tables. I f cascading is used, Impala deletes the table in the specified database before deleting it.

Cases

Suppose you have a database in Impala namedample that contains two tables, object and test. If you try to delete this database directly, you will receive 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: InvalidOperationException: Database sample is not empty. One or more 
tables exist.

Cascading allows you to delete this database directly (without having to manually delete its contents), as shown below.

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

Note - You cannot delete the Current Database in Impala. T herefore, before you delete a database, you need to make sure that the current context is set to a database other than the one you want to delete.

Use The Hue browser to delete the database

Open the Impala query editor and type the DELETE DATABASE statement in it, and then click the execute button, as shown below. S uppose you have three databases, namely, my_db, my_database and sample_database, and the default database. H ere we delete the database my_database the database.

After executing the query, gently move the cursor to the top of the drop-down menu. Y ou will then find a refresh symbol, as shown in the following screenshot. I f you click refresh symbol, the list of databases is refreshed and the most recent changes are applied to it.

Verify

Click the drop-down menu under the title DATABASE on the left side of the editor. T here, you can see a list of databases in the system. H ere you can observe the newly created database my_db, as shown below.

If you look closely, you can only see one database, the list of my_db the default database.