Hive 3 ACID Improvements, in depth: split-update deltas, per-table write ids, unbucketed and insert-only tables, managed-by-default and the upgrade path

Where Hive 1 and 2 ACID left off

In the original design, every transactional table was ORC and bucketed with CLUSTERED BY. Each write created a delta_<min>_<max> directory, and each row carried a hidden identifier made of the original transaction id, the bucket and a row number. An update wrote a new version of the row with an operation code into a delta, and a reader reconstructed the current table by merging the base and all deltas, sorted by row identifier, so that later versions replaced earlier ones.

That merge was the problem. It forced a sort-merge per bucket at read time, it made vectorized execution hard because rows had to be compared one by one, and it prevented the ORC reader from using predicate pushdown and column statistics in deltas, because filtering a delta could drop the newer version of a row and resurrect the older one. Bucketing was required so that the merge had a natural unit of parallelism, which in turn forced users to choose bucket counts up front and to live with skew. Transaction ids were global across the warehouse, so every table's directory names advanced whenever any table was written.

Change 1: split-update and delete deltas

The most important change is the layout known as ACID v2 or split-update, introduced during the Hive 2 line and the standard format for Hive 3 transactional tables. An update is no longer stored as a modified row. It is split into a delete of the old row and an insert of the new one. Deletes go into a separate delete_delta_<min>_<max>_<stmt> directory containing only row identifiers; inserts go into an ordinary delta. Cloudera's documentation of Hive 3 internals puts it plainly: an update combines the deletion and insertion of new data.

Reading becomes an anti-join instead of a merge. The reader loads the row identifiers from the delete deltas that are visible to its snapshot, typically a small set, then streams every insert delta and the base, dropping rows whose identifier is in the delete set. Insert deltas no longer need to be merged against each other, so vectorized readers can process them batch by batch, predicate pushdown and ORC statistics work on them again, and splits can be generated without regard to bucket boundaries. The cost moves to workloads with very many deletes: if the delete set does not fit comfortably in memory, reads slow down until compaction removes it.

Change 2: per-table write ids

Hive 3 separates the transaction id, which is still global and allocated per transaction by the metastore, from the write id, which is allocated per table per transaction. When a transaction writes to a table, the metastore assigns the next write id for that table and records the mapping between the two. Directory names and the first field of every row identifier use the write id.

The practical effects are larger than they sound. Directory names for a quiet table stay small and sequential, so a table written once a day shows delta_0000041_0000041_0000 rather than a gap of millions. Snapshot computation becomes a per-table valid write id list derived from the global snapshot, which is cheaper to pass to readers that only touch a few tables. And replication and import or export of a table no longer depend on the source warehouse's global transaction numbering, which was one motivation for the change. The row identifier you can select as ROW__ID now has fields named writeid, bucketid and rowid.

Change 3: transactional tables without bucketing

Hive 3 no longer requires transactional tables to be bucketed. The bucket field in the row identifier is now an encoded value: it packs a codec version, the writer's bucket or task number and a statement id into one integer, which is why the bucket id you see in query output for the first writer of an unbucketed table is 536870912 rather than 0. Files in an unbucketed table are still named bucket_00000, bucket_00001 and so on, one per writer task, but the numbers no longer mean hash buckets.

You can create a partitioned ORC table, mark it transactional and let the engine decide parallelism. Bucketing is still allowed and still useful when you want bucket map joins or sampling, but it is no longer the price of being able to UPDATE.

Change 4: insert-only tables in any format

Hive 3 adds a second kind of transactional table, insert-only, also called micro-managed. It supports INSERT and INSERT OVERWRITE but not UPDATE, DELETE or MERGE, and in exchange it works with any storage format: text, Parquet, Avro and others. There are no row identifiers and no delete deltas; each insert writes a delta directory, and readers decide which directories are visible from the same write id snapshot.

This gives append-heavy tables atomic, isolated loads without forcing ORC. A failed or aborted insert leaves a delta that readers skip because its write id is not valid, rather than half-written files mixed into the table. INSERT OVERWRITE writes a new base directory, so readers that started before it keep reading the old one until they finish, which is a real improvement over the old overwrite that deleted files under running queries.

-- Full ACID: ORC only, supports INSERT, UPDATE, DELETE, MERGE. No CLUSTERED BY needed in Hive 3.
CREATE TABLE sales.orders (
  order_id    BIGINT,
  customer_id BIGINT,
  status      STRING,
  amount      DECIMAL(12,2),
  updated_at  TIMESTAMP
)
PARTITIONED BY (order_date DATE)
STORED AS ORC
TBLPROPERTIES ('transactional'='true');

-- Insert-only ACID: any storage format, INSERT and INSERT OVERWRITE only.
CREATE TABLE sales.clicks_raw (line STRING)
STORED AS TEXTFILE
TBLPROPERTIES ('transactional'='true', 'transactional_properties'='insert_only');

-- Data written by tools that bypass Hive's transaction manager belongs in an external table.
CREATE EXTERNAL TABLE landing.orders_csv (line STRING)
LOCATION '/landing/orders/';

Change 5: managed means transactional, and external means hands off

Apache Hive 3 includes settings that make new managed tables transactional, and Hortonworks HDP 3 and Cloudera CDP turn this on: a plain CREATE TABLE creates a full ACID table when the format is ORC and an insert-only table otherwise, in a managed warehouse directory owned by the hive user. Non-transactional managed tables are rejected under the strict managed tables setting. Data that other engines write directly goes into external tables, and Hive does not manage its files.

This is a policy change with operational consequences. Spark, MapReduce or a shell script that used to write files into a managed table's directory now corrupts it, because those files carry no write id and are invisible or misread. On HDP 3 and CDP, Spark reads and writes managed ACID tables through the Hive Warehouse Connector or through engines that implement the ACID reader. Treat the managed warehouse directory as private to Hive and route every other writer through external tables or a supported connector.

Change 6: faster reads for transactional tables

Several Hive 3 improvements only pay off because of the new layout. The vectorized ACID reader processes insert deltas and the base in column batches and applies the delete set as a filter. LLAP can cache data from transactional tables, keyed so that a cached base stays valid while new deltas are read alongside it. The cost-based optimizer can use statistics on transactional tables, and materialized views with automatic query rewriting require transactional source tables, because the rewrite must know whether a view is up to date with its sources' write ids and can be rebuilt incrementally from new inserts.

Once a table's state is a base plus a known set of deltas, caches, statistics and views can be checked against it. See LLAP for the cache and ORC for the file format underneath.

Worked example: one partition through its life

The session below creates two orders, updates one, deletes the other and compacts. The comments show the row identifiers and the directories each statement leaves behind. The exact zero padding and statement suffixes vary slightly between versions, but the shape is the same.

INSERT INTO sales.orders PARTITION (order_date='2026-10-01')
VALUES (1, 10, 'NEW', 20.00, '2026-10-01 09:00:00'),
       (2, 11, 'NEW', 35.50, '2026-10-01 09:05:00');

SELECT ROW__ID, order_id, status FROM sales.orders WHERE order_date='2026-10-01';
-- {"writeid":1,"bucketid":536870912,"rowid":0}   1   NEW
-- {"writeid":1,"bucketid":536870912,"rowid":1}   2   NEW

UPDATE sales.orders SET status='PAID' WHERE order_id=1 AND order_date='2026-10-01';
DELETE FROM sales.orders WHERE order_id=2 AND order_date='2026-10-01';

SELECT ROW__ID, order_id, status FROM sales.orders WHERE order_date='2026-10-01';
-- {"writeid":2,"bucketid":536870912,"rowid":0}   1   PAID

-- hdfs dfs -ls .../sales.db/orders/order_date=2026-10-01/
-- delta_0000001_0000001_0000/
-- delete_delta_0000002_0000002_0000/      <- old ROW__ID of order 1
-- delta_0000002_0000002_0000/             <- new version of order 1
-- delete_delta_0000003_0000003_0000/      <- ROW__ID of order 2

ALTER TABLE sales.orders PARTITION (order_date='2026-10-01') COMPACT 'major';
SHOW COMPACTIONS;
-- after the cleaner runs: base_0000003/ holding one row, older directories removed

Notice three things. The update gave order 1 a new row identifier with write id 2: the row physically moved, which is why tools that cache row identifiers across writes are wrong. The delete wrote only an identifier, not the row. And after compaction the partition is a single base directory; the cleaner removes the old directories only once no running reader can still need them, so a long query can delay cleaning.

Upgrading from Hive 2: compaction first

Hive 3 cannot read the delta format of transactional tables written by earlier versions. The Apache Hive transactions documentation is explicit: every transactional table created before Hive 3 needs a major compaction on every partition that has had UPDATE, DELETE or MERGE since its last major compaction, before the upgrade. Major compaction rewrites everything into a base, which the new reader understands.

In practice: stop writes to transactional tables, run major compactions and wait until SHOW COMPACTIONS shows them all succeeded and cleaned, then upgrade. Distributions ship a pre-upgrade tool that finds the tables and partitions needing compaction and generates the statements; run it and keep its output as a record. Non-transactional managed tables are converted during migration according to the distribution's rules, and existing files in a converted table, called original files, are read with synthetic row identifiers until the first major compaction rewrites them into a base.

Operating Hive 3 ACID tables

Compaction is no longer optional housekeeping; it is part of the read path. Minor compaction merges deltas into one delta and delete deltas into one delete delta; major compaction rewrites a base with deletes applied. The initiator decides when based on thresholds, workers do the rewrite and the cleaner removes obsolete directories once readers have moved on. The compaction article covers tuning in detail; the checks below catch most incidents.

-- Health checks to run on a schedule
SHOW COMPACTIONS;                 -- state: initiated, working, ready for cleaning, failed, succeeded
SHOW TRANSACTIONS;                -- open and aborted transactions; an old open txn pins the cleaner
SHOW LOCKS EXTENDED;

-- Count deltas per partition from the filesystem (run as the hive user)
-- hdfs dfs -count -v /warehouse/tablespace/managed/hive/sales.db/orders/*

-- Core settings (hive-site.xml); the defaults vary by distribution, so read yours
hive.support.concurrency=true
hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager
hive.compactor.initiator.on=true          -- on exactly one or a few metastore instances
hive.compactor.worker.threads=4           -- greater than 0 on the hosts that run compaction
hive.compactor.delta.num.threshold=10     -- deltas before a minor compaction is queued
hive.compactor.delta.pct.threshold=0.1    -- delta size relative to base before a major one

Hive 3 ACID tables compete with open table formats. Iceberg tables in Hive keep metadata in files rather than the metastore, are readable by many engines without a connector, and support schema and partition evolution. Hive ACID keeps an edge where LLAP caching and view rewriting matter. Choose per table.

What to do next

1. Run SELECT ROW__ID on a transactional table and confirm you see writeid, bucketid and rowid; if you see transactionid you are not on the Hive 3 format.
2. List managed tables and check every writer: anything not going through Hive or a supported connector must move to an external table.
3. For new append-only non-ORC tables, use insert-only transactional tables instead of plain managed tables.
4. Drop CLUSTERED BY from new transactional table designs unless you need bucket joins or sampling.
5. Schedule SHOW COMPACTIONS and SHOW TRANSACTIONS checks and alert on failed compactions and transactions open longer than an hour.
6. Before any upgrade from Hive 2, run the pre-upgrade tool, complete major compactions and keep the output.
7. Measure read time on your largest UPDATE-heavy partitions before and after a major compaction to set compaction thresholds from data.