DELETE Statement in Apache Impala

In OLTP databases, DELETE is one of the most commonly used DML commands. When it comes to OLAP databases, however, DELETE is used less frequently. Apache Hive is mostly used as an OLAP database. Despite the fact that Apache Impala is a massively parallel processing SQL query engine for data stored in an Apache Hadoop computer cluster, it still uses Apache Hive’s OLAP databases.

It deletes an arbitrary number of rows in a Kudu table. This statement only works for Impala tables that use the Kudu storage engine. And, the DELETE command is available in Impala 2.8 or higher only.

Syntaxes/Examples:

— The below command helps in deleting all the rows of a table.
DELETE FROM tbEmployee;

— Delete only the rows that match the condition.
DELETE FROM tbEmployee WHERE salary < 3000 AND DeptNo = 10;
DELETE FROM tbEmployee WHERE salary = 3000 AND DeptID IN (10,11);

— Delete only the rows based on the subquery.
DELETE FROM tbEmployee WHERE DeptID IN (SELECT DeptID FROM Dept WHERE DName = ‘Sales’);

Hope you find this article helpful.