DELETE

The DELETE statement deletes records from a table.

Our Bulk HFile Delete feature can be used to optimize deletion of large amounts of data.

Syntax

{
  DELETE FROM correlation-Name]
    [WHERE clause]
}

table-Name

The name of the table from which you want to delete records.

correlation-Name

The optional alias (alternate name) for the table.

WHERE clause

The clause that specifies which record(s) to select for deletion.

Usage

The DELETE statement removes all rows identified by the table name and WHERE clause.

Examples

splice> DELETE FROM Players WHERE Year(Birthdate) > 1990;
8 rows inserted/updated/deleted

Using our Bulk HFile Delete Feature

Our Bulk Delete feature leverages HFile bulk deletion to significantly speed things up when you are deleting a lot of data; it does so by generating HFiles for the deletion and then bypasses the Splice Machine write pipeline and HBase write path when deleting the data.

You simply add a splice-properties hint that specifies where to generate the HFiles. If you’re specifying an S3 bucket on AWS, please review our Configuring an S3 Bucket for Splice Machine Access tutorial before proceeding.

splice> DELETE FROM my_table --splice-properties bulkDeleteDirectory='/bulkFilesPath'
;

We recommend performing a major compaction on your database after deleting a large amount of data; you should also be aware of our new SYSCS_UTIL.SET_PURGE_DELETED_ROWS system procedure, which you can call before a compaction to specify that you want the data physically (not just logically) deleted during compaction.

Statement Dependency System

A searched delete statement depends on the table being updated, all of its conglomerates (units of storage such as heaps or indexes), and any other table named in the WHERE clause. A  DROP INDEX statement for the target table of a prepared searched delete statement invalidates the prepared searched delete statement.

A CREATE INDEX or DROP INDEX statement for the target table of a prepared positioned delete invalidates the prepared positioned delete statement.

See Also