Apache Impala DROP Statement

The DROP command aids in the deletion of database objects. This is a popular command in any RDBMS, and it should be used with caution in production servers because the items that were removed can only be recovered if there is a backup.

In this blog, you’ll see the usage of DROP to delete the databases, tables, functions, roles, views and Stats.

DATABASE:
This command deletes a database from the system. The physical operations entail deleting the *.db directory from HDFS and removing the database’s metadata from the metastore.

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

Example:
DROP DATABASE TestDB;

By default, the mode is RESTRICT which blocks the deletion of the database if it holds tables. Hence use ‘Cascade’ to drop the database even if it has tables.

To drop the database if exists and even if it contains tables
DROP DATABASE IF EXISTS TestDB CASCADE;

To avoid dropping the database which has tables.
DROP DATABASE IF EXISTS TestDB RESTRICT;

TABLE:
Syntax:
DROP TABLE [IF EXISTS] [db_name.]table_name [PURGE]

Examples:
DROP TABLE tbStudent;
The above command will delete the table from the existing database.

VIEW:
Syntax:
DROP VIEW [IF EXISTS] [db_name.]view_name

Example:
DROP VIEW vwtbStudentInformation;

Function:
Syntax:
DROP [AGGREGATE] FUNCTION [IF EXISTS] [db_name.]function_name(type[, type…])

Example:
DROP FUNCTION fnCalcNetSalary;

ROLE:
The DROP ROLE statement removes a role from the metastore database. Once dropped, the role is revoked for all users to whom it was previously assigned, and all privileges granted to that role are revoked. Queries that are already executing are not affected. Impala verifies the role information approximately every 60 seconds, so the effects of DROP ROLE might not take effect for new Impala queries for a brief period.

Syntax:
DROP ROLE RoleName;

Example:
DROP ROLE DataReader;

STATS:
Removes the specified statistics from a table or partition. The statistics were originally created by the COMPUTE STATS or COMPUTE INCREMENTAL STATS statement.

Syntax:
DROP STATS [database_name.]table_name
DROP INCREMENTAL STATS [database_name.]table_name PARTITION (partition_spec) partition_spec ::= partition_col=constant_value