How should truncate and drop partition be implemented for Hive ACID tables?

[djsstarburst]

PR #5026 adds support for row-by-row delete for Hive ACID tables. How should table truncate and drop partition be implemented for ACID tables? Here are options:

• Truncate and drop partition work by deleting files, with no history maintained. This is how things work now. Also, both before and after PR #5026, "DELETE FROM my_table" on a non-partitioned table is processed as a "metadata delete" and does a by-hand truncation by deleting the data files.
• Truncate and drop partition work using row-by-row delete.

The argument for the first behavior is that it is familiar and fast. The argument for the second is that it preserves all the history. One possible outcome is that different customers prefer different behaviors, and we decide to "make it a mode" via a session property.

[findepi]

What does Hive 3 do?

[djsstarburst]

What does Hive 3 do?

5049 documents what Hive ACID does. For all DELETE FROM table WHERE ... requests, Hive ACID does row-by-row delete. For ALTER table DROP PARTITION or TRUNCATE table requests, Hive ACID deletes all the files in a non-transactional way.

It's a bit different for Presto (unless we "make it a mode" via a session property) because "metadata delete" causes partitions to be dropped, even though the DELETE request looks superficially like a row-by-row DELETE request.

[findepi]

non-transactional tables

Current, "metadata delete" is appropriate for non-transactional tables.

ORC ACID (full) transactional tables

• we should do row-level delete
• (optionally) unless ORC ACID / Transactional tables support a kind of time travel (which they do not seem to), we could still do "metadata delete" when WHERE condition matches whole partitions (is expressed on part keys only). For this, we would still need to do proper locking, so that the difference is not end-user visible.
  • I consider this optional, because if we do not do this, we still have a working DELETE implementation.

does Hive's ALTER TABLE .. COMPACT 'MAJOR' unregister partitions when no rows are left there?

insert-only transactional tables

How does Hive do DELETE? Is it allowed in Hive?

• likely we could do "metadata delete" as in ORC ACID case

[djsstarburst]

we should do row-level delete:

Yes, I agree: for Hive ACID, it seems to me that row-level delete is enough. One thing that convinces me we should not create a special case for "metadata delete" in Hive ACID is that the delete deltas will be tiny: 4 of 5 of the ACID columns will usually run-length-encode to a single value for each chunk deleted, and the 5th - - the rowId column - - should compress very well.

@electrum wonders if some customers will still need metadata delete for Hive ACID tables, and whether we should "make it a mode".

we could still do "metadata delete" when WHERE condition matches whole partitions (is expressed on part keys only). For this, we would still need to do proper locking, so that the difference is not end-user visible.

We could (and the PR now does, mistakenly in my opinion) translate Hive ACID whole partition deletes into metadata deletes rather than row-by-row deletes. However, the Hive ACID metastore treats partition dropping as a "non-transactional" operation. The lock you acquire is of type NO_TXN.

Since the only form of deletion supported by non-ACID Hive is partition dropping, it seems clear we must continue to support "metadata delete" for non-ACID Hive tables.

[electrum]

We discussed this further and it sounds like always doing normal ACID delete for transactional tables is the right behavior. We can add a drop_partition procedure later if needed.