Impala - Drop a Table

The Impala drop table statement is used to delete an existing table in Impala. This statement also deletes the underlying HDFS files for internal tables

NOTE − You have to be careful while using this command because once a table is deleted, then all the information available in the table would also be lost forever.

Syntax

Following is the syntax of the DROP TABLE Statement. Here, IF EXISTS is an optional clause. If we use this clause, a table with the given name is deleted, only if it exists. Otherwise, no operation will be carried out.

DROP table database_name.table_name;

If you try to delete a table that doesn’t exist without the IF EXISTS clause, an error will be generated. Optionally you can specify database_name along with table_name.

Example

Let us first verify the list of tables in the database my_db as shown below.

[quickstart.cloudera:21000] > show tables;

Query: show tables 
+------------+ 
| name       | 
+------------+ 
| customers  | 
| employee   | 
| student    | 
+------------+ 
Fetched 3 row(s) in 0.11s

From the above result, you can observe that the database my_db contains 3 tables

Following is an example of the drop table statement. In this example, we are deleting the table named student from the database my_db.

[quickstart.cloudera:21000] > drop table if exists my_db.student;

On executing the above query, a table with the specified name will be deleted, displaying the following output.

Query: drop table if exists student

Verification

The show Tables query gives a list of the tables in the current database in Impala. Therefore, you can verify whether a table is deleted, using the Show Tables statement.

First of all, you need to switch the context to the database in which the required table exists, as shown below.

[quickstart.cloudera:21000] > use my_db; 
Query: use my_db

Then, if you get the list of tables using the show tables query, you can observe the table named student is not in the list.

[quickstart.cloudera:21000] > show tables; 

Query: show tables 
+-----------+ 
| name      | 
+-----------+ 
| customers | 
| employee  | 
| student   | 
+-----------+ 
Fetched 3 row(s) in 0.11s

Creating a Database using Hue Browser

Open Impala Query editor and type the drop Table Statement in it. And click on the execute button as shown in the following screenshot.

After executing the query, gently move the cursor to the top of the dropdown menu and you will find a refresh symbol. If you click on the refresh symbol, the list of databases will be refreshed and the recent changes done are applied to it.

Verification

Click on the drop down under the heading DATABASE on the left-hand side of the editor. There you can see a list of databases; select the database my_db as shown below.

On selecting the database my_db, you can see a list of tables in it as shown below. Here you cannot find the deleted table student in the list as shown below.