Impala - Truncate a Table

The Truncate Table Statement of Impala is used to remove all the records from an existing table.

You can also use DROP TABLE command to delete a complete table, but it would remove the complete table structure from the database and you would need to re-create this table once again if you wish you store some data.

Syntax

Following is the syntax of the truncate table statement.

truncate table_name;

Example

Suppose, we have a table named customers in Impala, and if you verify its contents, you are getting the following result. This means that the customers table contains 6 records.

[quickstart.cloudera:21000] > select * from customers; 

Query: select * from customers 
+----+----------+-----+-----------+--------+--------+ 
| id | name     | age | address   | salary | e_mail | 
+----+----------+-----+-----------+--------+--------+
| 1  | Ramesh   | 32  | Ahmedabad | 20000  | NULL   | 
| 2  | Khilan   | 25  | Delhi     | 15000  | NULL   | 
| 3  | kaushik  | 23  | Kota      | 30000  | NULL   |
| 4  | Chaitap | 25  | Mumbai    | 35000  | NULL   | 
| 5  | Hardik   | 27  | Bhopal    | 40000  | NULL   | 
| 6  | Komal    | 22  | MP        | 32000  | NULL   | 
+----+----------+-----+-----------+--------+--------+

Following is an example of truncating a table in Impala using truncate statement. Here we are removing all the records of the table named customers.

[quickstart.cloudera:21000] > truncate customers;

On executing the above statement, Impala deletes all the records of the specified table, displaying the following message.

Query: truncate customers 

Fetched 0 row(s) in 0.37s

Verification

If you verify the contents of the customers table, after the delete operation, using select statement, you will get an empty row as shown below.

[quickstart.cloudera:21000] > select * from customers;
Query: select * from customers 

Fetched 0 row(s) in 0.12s

Truncating a Table using Hue Browser

Open Impala Query editor and type the truncate Statement in it. And cpck on the execute button as shown in the following screenshot.

After executing the query/statement, all the records from the table are deleted.