DML — Data Manipulation

The SQL Gateway supports standard Data Manipulation Language operations, translated into Elasticsearch write APIs.

SQL Statement	Elasticsearch API
INSERT	index API (bulk)
INSERT ... AS SELECT	search + bulk index
UPDATE	update-by-query
DELETE	delete-by-query
COPY INTO	bulk from file

All DML operations return a DmlResult:

inserted: N, updated: N, deleted: N, rejected: N

---

INSERT INTO … VALUES

INSERT INTO table_name (col1, col2, ...)
VALUES (v1, v2, ...), (v3, v4, ...), ...;

INSERT operations use the table’s primary key to generate _id, pass documents through the table pipeline, and support STRUCT, ARRAY<STRUCT>, DEFAULT values, SCRIPT AS generated columns, and NOT NULL constraints.

Example with STRUCT

CREATE TABLE IF NOT EXISTS dql_users (
  id INT NOT NULL,
  name VARCHAR FIELDS(
    raw KEYWORD
  ) OPTIONS (fielddata = true),
  age INT SCRIPT AS (YEAR(CURRENT_DATE) - YEAR(birthdate)),
  birthdate DATE,
  profile STRUCT FIELDS(
    city VARCHAR OPTIONS (fielddata = true),
    followers INT
  )
);

INSERT INTO dql_users (id, name, birthdate, profile) VALUES
  (1, 'Alice', '1994-01-01', {city = "Paris", followers = 100}),
  (2, 'Bob',   '1984-05-10', {city = "Lyon",  followers = 50}),
  (3, 'Chloe', '1999-07-20', {city = "Paris", followers = 200}),
  (4, 'David', '1974-03-15', {city = "Marseille", followers = 10});

Notes

• Fields not provided in VALUES are omitted from the document
• Unknown fields are rejected if the index mapping is strict
• All values are validated against the Elasticsearch mapping

---

INSERT INTO … AS SELECT

INSERT INTO orders (order_id, customer_id, order_date, total)
AS SELECT
  id AS order_id,
  cust AS customer_id,
  date AS order_date,
  amount AS total
FROM staging_orders;

The SELECT is executed, results are inserted via the Bulk API, table pipelines are applied, and the primary key ensures upsert semantics.

ON CONFLICT

INSERT INTO orders (order_id, customer_id, total)
AS SELECT id, cust, amount FROM staging_orders
ON CONFLICT (order_id) DO UPDATE;

Validation rules:

• If the index has a primary key, the conflict target must match the PK exactly
• All INSERT columns must be present in the SELECT output
• Column matching is done by name, not position

---

UPDATE … SET … WHERE

UPDATE table_name
SET col1 = expr1, col2 = expr2, ...
WHERE condition;

Implemented using Elasticsearch update-by-query.

• Expressions are supported in SET clauses (e.g. SET x = x + 1)
• SET field = NULL removes the field from the document (Elasticsearch does not store SQL NULL)

Limitations

• Only top-level scalar fields or entire nested objects can be replaced
• Updating a specific element inside an ARRAY<STRUCT> is not supported

---

DELETE FROM … WHERE

DELETE FROM table_name
WHERE condition;

Implemented using Elasticsearch delete-by-query.

• WHERE is optional — if omitted, all documents are deleted (equivalent to TRUNCATE TABLE)
• Deleting nested fields or array elements is not supported; only whole documents

---

COPY INTO

Bulk load documents from external files into an Elasticsearch index using the Bulk API.

COPY INTO table_name
FROM 'path/to/file'
[FILE_FORMAT = 'JSON' | 'JSON_ARRAY' | 'PARQUET' | 'DELTA_LAKE']
[ON CONFLICT (pk_column) DO UPDATE];

COPY INTO performs:

1. Index name validation
2. Loading of the real Elasticsearch schema (mapping, primary key, partitioning)
3. Primary key extraction (_id generation, composite PK concatenation)
4. Partitioning extraction (suffix index name based on date)
5. Bulk ingestion via the Bulk API
6. Pipeline execution
7. Returns DmlResult with inserted and rejected counts

See the dedicated COPY INTO page for full details including remote file system support.

Quick Example

COPY INTO copy_into_test
FROM 's3://my-bucket/path/to/data.json'
FILE_FORMAT = 'JSON'
ON CONFLICT (uuid) DO UPDATE;

---

DML Lifecycle Example

-- Insert
INSERT INTO dml_chain (id, value) VALUES
  (1, 10), (2, 20), (3, 30);

-- Update
UPDATE dml_chain
SET value = 50
WHERE id IN (1, 3);

-- Delete
DELETE FROM dml_chain
WHERE value > 40;

-- Verify
SELECT * FROM dml_chain ORDER BY id ASC;

---

Version Compatibility

Feature	ES6	ES7	ES8	ES9
INSERT	Yes	Yes	Yes	Yes
INSERT AS SELECT	Yes	Yes	Yes	Yes
UPDATE	Yes	Yes	Yes	Yes
DELETE	Yes	Yes	Yes	Yes
COPY INTO	Yes	Yes	Yes	Yes
JSON	Yes	Yes	Yes	Yes
JSON_ARRAY	Yes	Yes	Yes	Yes
PARQUET	Yes	Yes	Yes	Yes
DELTA_LAKE	Yes	Yes	Yes	Yes