DELETE Statement (Impala 2.8 or higher only)

Deletes an arbitrary number of rows from a Kudu table. This statement only works for Impala tables that use the Kudu storage engine.

Syntax:

  2. DELETE [FROM] [database_name.]table_name [ WHERE where_conditions ]
  4. DELETE table_ref FROM [joined_table_refs] [ WHERE where_conditions ]

The first form evaluates rows from one table against an optional WHERE clause, and deletes all the rows that match the WHERE conditions, or all rows if WHERE is omitted.

The second form evaluates one or more join clauses, and deletes all matching rows from one of the tables. The join clauses can include non-Kudu tables, but the table from which the rows are deleted must be a Kudu table. The FROM keyword is required in this case, to separate the name of the table whose rows are being deleted from the table names of the join clauses.

Usage notes:

The conditions in the WHERE clause are the same ones allowed for the SELECT statement. See SELECT Statement for details.

The conditions in the WHERE clause can refer to any combination of primary key columns or other columns. Referring to primary key columns in the WHERE clause is more efficient than referring to non-primary key columns.

If the WHERE clause is omitted, all rows are removed from the table.

Because Kudu currently does not enforce strong consistency during concurrent DML operations, be aware that the results after this statement finishes might be different than you intuitively expect:

  • If some rows cannot be deleted because their some primary key columns are not found, due to their being deleted by a concurrent DELETE operation, the statement succeeds but returns a warning.

  • A DELETE statement might also overlap with INSERT, UPDATE, or UPSERT statements running concurrently on the same table. After the statement finishes, there might be more or fewer rows than expected in the table because it is undefined whether the DELETE applies to rows that are inserted or updated while the DELETE is in progress.

The number of affected rows is reported in an impala-shell message and in the query profile.

Statement type: DML

Important: After adding or replacing data in a table used in performance-critical queries, issue a COMPUTE STATS statement to make sure all statistics are up-to-date. Consider updating statistics for a table after any INSERT, LOAD DATA, or CREATE TABLE AS SELECT statement in Impala, or after loading data through Hive and doing a REFRESH table_name in Impala. This technique is especially important for tables that are very large, used in join queries, or both.

Examples:

The following examples show how to delete rows from a specified table, either all rows or rows that match a WHERE clause:

  2. -- Deletes all rows. The FROM keyword is optional.
  3. DELETE FROM kudu_table;
  4. DELETE kudu_table;
  6. -- Deletes 0, 1, or more rows.
  7. -- (If c1 is a single-column primary key, the statement could only
  8. -- delete 0 or 1 rows.)
  9. DELETE FROM kudu_table WHERE c1 = 100;
  11. -- Deletes all rows that match all the WHERE conditions.
  12. DELETE FROM kudu_table WHERE
  13.   (c1 > c2 OR c3 IN ('hello','world')) AND c4 IS NOT NULL;
  14. DELETE FROM t1 WHERE
  15.   (c1 IN (1,2,3) AND c2 > c3) OR c4 IS NOT NULL;
  16. DELETE FROM time_series WHERE
  17.   year = 2016 AND month IN (11,12) AND day > 15;
  19. -- WHERE condition with a subquery.
  20. DELETE FROM t1 WHERE
  21.   c5 IN (SELECT DISTINCT other_col FROM other_table);
  23. -- Does not delete any rows, because the WHERE condition is always false.
  24. DELETE FROM kudu_table WHERE 1 = 0;

The following examples show how to delete rows that are part of the result set from a join:

  2. -- Remove _all_ rows from t1 that have a matching X value in t2.
  3. DELETE t1 FROM t1 JOIN t2 ON t1.x = t2.x;
  5. -- Remove _some_ rows from t1 that have a matching X value in t2.
  6. DELETE t1 FROM t1 JOIN t2 ON t1.x = t2.x
  7.   WHERE t1.y = FALSE and t2.z > 100;
  9. -- Delete from a Kudu table based on a join with a non-Kudu table.
  10. DELETE t1 FROM kudu_table t1 JOIN non_kudu_table t2 ON t1.x = t2.x;
  12. -- The tables can be joined in any order as long as the Kudu table
  13. -- is specified as the deletion target.
  14. DELETE t2 FROM non_kudu_table t1 JOIN kudu_table t2 ON t1.x = t2.x;

Related information:

Using Impala to Query Kudu Tables, INSERT Statement, UPDATE Statement (Impala 2.8 or higher only), UPSERT Statement (Impala 2.8 or higher only)

Parent topic: Impala SQL Statements