LastRow Merge Engine

LastRow Merge Engine details

• Merge policy

  The LastRow merge policy is used by default. When the primary key of new data matches an existing record, the old record is overwritten. Only the latest record, based on processing time, is retained.

• Configuration

  If the 'table.merge-engine' parameter is not explicitly specified in the table properties when you create a table with a primary key, the system automatically enables the default LastRow Merge Engine.

• Supported operation types

  INSERT, UPDATE, and DELETE.

• Partial update support

  This engine supports partial column updates, allowing you to update only specified columns instead of the entire row.

Usage examples

These examples cover the following common scenarios:

• Real-time data synchronization: In data streaming scenarios, the LastRow Merge Engine ensures that records for a primary key always reflect the latest state.

• Incremental updates: This engine supports partial column updates, which is ideal for efficiently processing incremental data.

• Data cleanup: You can use delete operations to periodically clean up expired or invalid data and optimize storage space.

CREATE TABLE T (
 k INT,
 v1 DOUBLE,
 v2 STRING,
 PRIMARY KEY (k) NOT ENFORCED
);
-- Insert
INSERT INTO T(k, v1, v2) VALUES (1, 1.0, 't1');
INSERT INTO T(k, v1, v2) VALUES (1, 1.0, 't2');
SELECT * FROM T WHERE k = 1;
-- Outputs:
+----+-----+----+
| k | v1 | v2 |
+----+-----+----+
| 1 | 1.0 | t2 |
+----+-----+----+
-- Update
INSERT INTO T(k, v1, v2) VALUES (2, 2.0, 't2');
UPDATE T SET v1 = 4.0 WHERE k = 2;
SELECT * FROM T WHERE k = 2;
 -- Outputs:
+----+-----+----+
| k | v1 | v2 |
+----+-----+----+
| 2 | 4.0 | t2 |
+----+-----+----+
-- Partial update
INSERT INTO T(k, v1) VALUES (3, 3.0); -- Set v1 to 3.0
SELECT * FROM T WHERE k = 3;
-- Outputs:
+----+-----+------+
| k | v1 | v2 |
+----+-----+------+
| 3 | 3.0 | null |
+----+-----+------+
INSERT INTO T(k, v2) VALUES (3, 't3'); -- Set v2 to 't3'
SELECT * FROM T WHERE k = 3;
-- Outputs:
+----+-----+----+
| k | v1 | v2 |
+----+-----+----+
| 3 | 3.0 | t3 |
+----+-----+----+
-- Delete (Batch delete is not currently supported)
DELETE FROM T WHERE k = 2;
SELECT * FROM T;
-- Outputs:
+----+-----+----+
| k | v1 | v2 |
+----+-----+----+
| 1 | 1.0 | t2 |
+----+-----+----+
| 3 | 3.0 | t3 |
+----+-----+----+