Impala DROP DATABASE Statement – Process & Example

In our last Impala tutorial, we studied Impala Create Database. Today we see How Impala Drop a Database when we need to remove a database from Impala, we use Impala DROP DATABASE Statement.

However, there is much more to know about  Impala DROP DATABASE Statement. Also, we will cover its syntax, usage as well as the example to understand well.

So, let’s discuss How Impala DROP Database Statements.

Introduction – Impala DROP DATABASE 

In order to remove a database from Impala, we use Impala DROP DATABASE Statement. Moreover, it removes the metadata for the database from the Metastore. Also, deletes the corresponding *.db directory from HDFS.

• Basically, it removes a database from the system.
• However, it is recommended to remove all the tables from it, before deleting the database.

a. Syntax to Impala DROP DATABASE 

Here, is the syntax of the DROP DATABASE Statement, below;

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

b. Statement type

This statement is of DDL Type.

c. Usage of Impala DROP DATABASE Statement

Basically, to avoid losing any data, keep the database empty before it can be dropped.

Though, we can include the CASCADE clause to make Impala drop all tables and other objects in the database before dropping the database itself, in Impala 2.3 and higher.

So, there is one demand of the RESTRICT clause that the database is empty before being dropped. However, this clause is optional since the RESTRICT behavior is still the default.

Make sure to drop or move all the objects inside the database manually before dropping the database itself, while we do not use the CASCADE clause:

• In order to locate all tables and views in the database, use the SHOW TABLES statement. Hence, to remove them all,  issue DROP TABLE and DROP VIEW statements.
• Further, to locate all user-defined functions in the database, use the SHOW FUNCTIONS and SHOW AGGREGATE FUNCTIONS statements. Again to remove them all, issue DROP FUNCTION and DROP AGGREGATE FUNCTION statements.
• Also, while removing the database itself, use ALTER TABLE and ALTER VIEW, to keep tables or views contained by a database or to move the relevant objects to a different database before dropping the original database.

Although, make sure we cannot drop the current database. Current database means the database through which our session is connected to either by the USE statement or the -d option of impala-shell. Also, remember to issue a USE statement to switch to a different database first.

So, the reason behind it is the default database is always available, issuing USE default is a convenient way to leave the current database before dropping it.

d. Hive Considerations

When you drop a database in Impala, the database can no longer be used by Hive.

Amazon S3 Considerations

As we know, we can create or remove folders as needed in the Amazon S3 system Through following commands of Impala 2.6 and higher. Such as CREATE DATABASE, CREATE TABLE, DROP DATABASE CASCADE, DROP TABLE, and ALTER TABLE [ADD|DROP] PARTITION.

However, previous to Impala 2.6, we needed to create folders manually,  and point Impala database, tables, or partitions at them. Also, we require removing folders when no longer needed on our own.

Cancellation

It is not possible to cancel the Impala DROP database Statement. That implies it Cannot be canceled.

HDFS Permissions

However, it is essential that for the user ID that the ImpalaD daemon runs under, have to write permission for the parent HDFS directory under which the database is located.

Examples of Drop Database in Impala

For Example,

create database first_db;
use first_db;
create table t1 (x int);
create database second_db;
use second_db;

— Each database has its own namespace for tables.
— You can reuse the same table names in each database.

create table t1 (s string);
create database temp;

— You can either USE a database after creating it,
— or qualify all references to the table name with the name of the database.

— Here, tables T2 and T3 are both created in the TEMP database.

create table temp.t2 (x int, y int);
use database temp;
create table t3 (s string);

— You cannot drop a database while it is selected by the USE statement.
drop database temp;

ERROR: AnalysisException: Cannot drop current default database: temp

— The always-available database ‘default’ is a convenient one to USE

— before dropping a database you created.
use default;

— Before dropping a database, first, drop all the tables inside it,
— or in Impala 2.3 and higher use the CASCADE clause.
drop database temp;

ERROR: ImpalaRuntimeException: Error making ‘dropDatabase’ RPC to Hive Metastore:
CAUSED BY: InvalidOperationException: Database temp is not empty
show tables in temp;
+——+
| name |
+——+
| t3   |
+——+

— Impala 2.3 and higher:
drop database temp cascade;

— Earlier releases:
drop table temp.t3;
drop database temp;

So, this was all about Impala Drop Database Statement. Hope you like our explanation.

Conclusion – Impala Drop Database

So, we have seen the whole concept of Impala DROP DATABASE Statement. Still, if any doubt occurs, feel free to ask in the comment section.