DML refers to “Data Manipulation Language”, a subset of SQL statements that modify the data stored in tables. Because Impala focuses on query performance and leverages the append-only nature of HDFS storage, currently Impala only supports a small set of DML statements.
Impala supports the following DML statements:
DELETE: Works for Kudu tables only
LOAD DATA: Does not apply for HBase or Kudu tables
UPDATE: Works for Kudu tables only
UPSERT: Works for Kudu tables only
INSERT in Impala is primarily optimized for inserting large volumes of data
in a single statement, to make effective use of the multi-megabyte HDFS blocks. This is the
way in Impala to create new data files. If you intend to insert one or a few rows at a time,
such as using the
INSERT ... VALUES syntax, that technique is much more
efficient for Impala tables stored in HBase.
LOAD DATA moves existing data files into the directory for an Impala table, making them
immediately available for Impala queries. This is one way in Impala to work with data files produced by other
Hadoop components. (
CREATE EXTERNAL TABLE is the other alternative; with external tables,
you can query existing data files, while the files remain in their original location.)
In Impala 2.8 and higher, Impala does support the
UPSERT statements for Kudu tables. For HDFS
or S3 tables, to simulate the effects of an
DELETE statement in other database systems, typically you use
CREATE TABLE AS SELECT to copy data from one
table to another, filtering out or changing the appropriate rows during the copy operation.
You can also achieve a result similar to
UPDATE by using Impala tables stored in HBase.
When you insert a row into an HBase table, and the table
already contains a row with the same value for the key column, the older row is hidden, effectively the same
as a single-row
Impala can perform DML operations for tables or partitions stored in the Amazon S3 filesystem.