Reenactment-Style Updates

[okennedy]

Data access in Mimir is presently read-only. LOAD and CREATE TABLE AS both allow the creation of new data, but the classical SQL DDL commands (UPDATE, DELETE, and INSERT) are not supported. This is by design.

1. Most data sources (CSV files, Hive, URLs) are either unfriendly to point updates or outright don't support writes.
2. Modifying data directly goes against a core Mimir philosophy: Source data stays intact.
3. Modifying data directly loses provenance.

Nevertheless, DDL is useful. Key goals:

• We need to preserve the provenance of changes, including original values.
• Edits should be identifiable by date
• Ideally we'd be able to revert to an earlier version.

An ideal approach is reenactment, as discussed by @lordpretzel in #321 Simply put, instead of modifying tables in-place, re-enactment treats each DDL operation as a query that translates the prior table into the new one. A few examples:

UPDATE foo SET A = 1 WHERE B = 2;
SELECT CASE B WHEN 2 THEN 1 ELSE A AS A, B, C, ... FROM foo;

DELETE FROM foo WHERE B = 2;
SELECT * FROM foo WHERE B <> 2; 

INSERT INTO foo SELECT * FROM bar;
INSERT INTO foo(A, B, C) values (1, 2, 3);
SELECT * FROM foo 
  UNION ALL 
SELECT * FROM bar
  UNION ALL
SELECT 1 AS A, 2 AS B, 3 AS C;

In effect, the goal would be to allow DDL statements, but instead of applying the updates in-situ, we'd define a new view (or similar) with the update applied that would override the source table/view.