impala deletes the table

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

Note - Care must be taken when using this command, because when you delete a table, all information available in the table is also lost forever.

Grammar

The following is the syntax of the DROP TABLE statement. H ere, IF EXISTS is an optional clause. I f we use this clause, the table with the given name is deleted as long as it exists. O therwise, nothing is done.

DROP table database_name.table_name;

If you try to delete a table that does not have an IF EXISTS clause, an error is generated. O ptionally, you can specify database_name and table_name.

Cases

Let's first verify that my_db list of tables in the database, as shown below.

[quickstart.cloudera:21000] > show tables;

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

As can be seen from the results above, my_db database contains three tables

The following is an example of a drop table statement. I n this example, we remove a my_db named student from the database database.

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

When you perform the query above, the table with the specified name is deleted and the following output is displayed.

Query: drop table if exists student

Verify

The show Tables query provides a list of tables in the current database in Impala. T herefore, you can use the Show Tables statement to verify that the table has been deleted.

First, you need to switch the context to the database where the desired table is located, as shown below.

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

Then, if you use the show tables query to get a list of tables, you can observe that 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

Create a database using The Hue browser

Open the Impala query editor and type a drop table statement in it. T hen click the execute button, as shown in the screenshot below.

After executing the query, gently move the cursor to the top of the drop-down menu and you'll notice a refresh symbol. I f you click refresh symbol, the database list is refreshed and 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; S elect the database my_db, as shown below.

When you select a my_db, you can see a list of tables in it, as shown below. H ere, you can't find deleted table students in the list, as shown below.