Impala DROP TABLE Statement – A Complete Tutorial

In our last Impala tutorial, we saw how the Impala Create Table Statement. Here, we are going to discuss the Impala Drop Table statement. It is used to delete an existing table in Impala.

Also, we will cover its syntax, usage as well as an example of Impala Drop table statement to understand it well.

So, let’s start How Impala Drop Table Statement.

How to use Impala DROP TABLE Statement?

Basically, Impala DROP TABLE Statement removes an Impala table. Although there is an exception, it does removes the underlying  HDFS data files for internal tables, but not for external tables. In other words, to delete an existing table in Impala we use Impala Drop Table Statement.

However, it is very important to note that, while using this command we have to be very careful. Since all the information available in the table would also be lost forever once a table is deleted.

a. Syntax of Impala Drop Table Statements

So, the syntax for using Impala DROP TABLE Statement is-

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

b. Statement type

Impala DROP TABLE Statement is of DDL Type.

c. Usage

As we discussed earlier, Impala removes the associated HDFS directory and data files for the table. But sometimes, it is possible, data files doesnot deleted if you issue a DROP TABLE. There can be following reasons behind it. Such as:

• Basically, Impala leaves all files and directories untouched when the table was created with the EXTERNAL clause. Since Impala is only used to query the data files from their original locations. So, try using external tables when the data is under the control of other  Hadoop components.
• Also, if there is no HDFS location available to hold the HDFS trash can for the Impala user, Impala might leave the data files behind unintentionally.

However,  before dropping a table ensure that you are in the correct database. Even by using a fully qualified name db_name.table_name or by issuing a USE statement first.
Also, first issue DROP TABLE statements to remove all the tables in that database, if you intend to issue a DROP DATABASE statement.

d. Examples of Impala Drop Table Statements

Let’s see an example of Impala drop table statement,

create database temporary;
use temporary;
create table unimportant (x int);
create table trivial (s string);
-- Drop a table in the current database.
drop table unimportant;
-- Switch to a different database.
use default;
-- To drop a table in a different database...
drop table trivial;
ERROR: AnalysisException: Table does not exist: default.trivial
-- ...use a fully qualified name.
drop table temporary.trivial;

e. Cancellation

It is not possible to cancel it. That implies it Cannot be canceled.

IF EXISTS Clause in Impala

It is an optional clause. This clause makes the statement succeed even if the table exists or not. There are two possible conditions,  Either the table does exist or not exist. So, if it does exist, it is dropped; or if it does not exist the statement has no effect.

To be more specific, in standardized setup scripts that remove existing schema objects and create new ones, this is very useful.

Hence, the script can run successfully the first time you run it, by using some combination of IF EXISTS for the DROP statements and IF NOT EXISTS clauses for the CREATE statements.

PURGE Clause in Impala

In CDH 5.5 / Impala 2.3 and higher, there is an optional PURGE keyword, available. Hence, instead of going through the HDFS trashcan mechanism, it causes Impala to remove the associated HDFS data files immediately.

So, if it is crucial to remove the data as quickly as possible to free up space use this keyword when dropping a table. Also, if there is a problem with the trash can, like the trashcan not being configured or being in a different HDFS encryption zone than the data files, we can use it.

Amazon S3 Considerations

However, it is not possible to write new data to a table stored in the Amazon S3 filesystem through Impala, Since the DROP TABLE statement can remove data files from S3 if the associated S3 table is an internal table.

HDFS Permissions in Impala

It is must that the Impala user ID has to write permission for all the files and directories that make up the table that the Impalad daemon runs under, Especially, for an internal table.

Whereas, dropping the table only involves changes to metadata in the Metastore database, for an external table. As we know, when external tables are dropped Impala does not remove any HDFS files or directories.

Hence, for the associated HDFS files or directories, no need of a particular permissions.
So, this was all about Impala Drop Table Statement. Hope you like our explanation.

Conclusion – Impala Drop Table Statement

As a result, we have seen the whole concept of Impala DROP TABLE Statement. Still, if any doubt occurs in how to use Impala DROP TABLE Statement, feel free to ask in the comment section.