Compacting Hive ACID ORC Tables

A Hive ACID (transactional) ORC table creates a new delta_* directory under the partition for every INSERT, UPDATE or DELETE. On frequently loaded tables a single partition can accumulate hundreds of deltas — that is where small files come from in ACID tables.

For non-ACID tables we rewrote partitions with PySpark through a staging area, but ACID tables must not be merged with Spark directly. This post covers compacting ACID tables with Hive's own features and controlling the resulting file count. The source document is ACID_COMPACTION.md in the DataDynamics/hive-orc-table-compaction repository.

Identifying an ACID table

Table properties

SHOW TBLPROPERTIES dw.sales('transactional');             -- true means ACID
SHOW TBLPROPERTIES dw.sales('transactional_properties');

On HDP 3.x / CDP, managed tables are created as ACID by default, so a table can be ACID even if nobody asked for it.

HDFS layout

If the partition path contains directories like these, it is an ACID table:

/warehouse/tablespace/managed/hive/dw.db/sales/dt=2026-10-01/
├── base_0000123/
│   ├── bucket_00000
│   ├── bucket_00001
│   └── ...
├── delta_0000124_0000124_0000/
│   └── bucket_00000
├── delta_0000125_0000125_0000/
│   └── bucket_00000
└── delete_delta_0000126_0000126_0000/
    └── bucket_00000

Each repeated INSERT adds one more delta_*:

Why not merge with Spark

• Without the Hive Warehouse Connector (HWC), Spark cannot merge base / delta / delete_delta on read. Row counts and data may be wrong, or the read may fail outright.
• An INSERT OVERWRITE from Spark can leave Hive's transaction metadata (write IDs) out of sync with the actual files.
• The PySpark program from the non-ACID post aborts when transactional=true for exactly this reason.

So ACID tables are compacted with Hive's own features.

Minor vs. major compaction

For small-file cleanup, use major compaction.

Running a major compaction

Run in beeline:

-- 1. Confirm the table is ACID
SHOW TBLPROPERTIES dw.sales('transactional');
 
-- 2. Request a major compaction per partition
ALTER TABLE dw.sales
PARTITION (dt='2026-10-01')
COMPACT 'major';
 
-- 3. Check progress
SHOW COMPACTIONS;

ALTER TABLE ... COMPACT only enqueues the request and returns immediately. Append AND WAIT to block until it finishes — handy in batch scripts before moving on to validation.

ALTER TABLE dw.sales
PARTITION (dt='2026-10-01')
COMPACT 'major' AND WAIT;

SHOW COMPACTIONS moves through these states:

The cleaner deletes old base / delta directories only after every query reading them has finished, so they may linger in HDFS for a while after compaction.

If a compaction is stuck in initiated, check these Hive Metastore settings:

Controlling file count and size

This is the most commonly misunderstood part of ACID compaction: COMPACT 'major' has no option for the resulting file count or size. After a major compaction, the number of files in base_N is determined as follows:

ORC settings such as orc.stripe.size only affect stripe size inside a file, not the file count or size. To control the file count directly, use one of two methods:

Method 1: Rebalance compaction (Hive 4.x)

Since Hive 4.0, non-bucketed full ACID tables can be compacted into a specified number of buckets (files):

ALTER TABLE dw.sales
PARTITION (dt='2026-10-01')
COMPACT 'rebalance'
CLUSTERED INTO 8 BUCKETS;

• Query-based compaction must be enabled.
• Not available for bucketed or insert-only tables.
• Hive 3.x / HDP 3.x do not have this syntax; on CDP, support depends on the release.
• Check your Hive version with SELECT version(); and confirm the syntax in your distribution's documentation first.

Method 2: Hive INSERT OVERWRITE (recommended, any version)

Rewrite the same partition from Hive (beeline). The output file count equals the reducer count, so you get exact control.

Compute the target file count from the partition size:

Target Files = ceil(Partition Size / Target File Size)
 
e.g. 4 GB / 512 MB = 4 × 1024 / 512 = 8

hdfs dfs -du -s -h /warehouse/tablespace/managed/hive/dw.db/sales/dt=2026-10-01

Fixed file count

SET hive.tez.auto.reducer.parallelism=false;
SET mapreduce.job.reduces=8;
 
INSERT OVERWRITE TABLE dw.sales
PARTITION (dt='2026-10-01')
SELECT
    id,
    customer_id,
    amount
FROM dw.sales
WHERE dt='2026-10-01'
DISTRIBUTE BY pmod(hash(id), 8);

Do not use DISTRIBUTE BY rand(). When a task is retried, the same row can land on a different reducer and be duplicated or lost. Use a deterministic, evenly distributed column (e.g. the PK) as the key.

Size-based

SET hive.exec.reducers.bytes.per.reducer=536870912;  -- 512 MB

This estimates the reducer count from input size, so output file sizes are approximate. Use the fixed-count method when you need an exact number.

Caveats

• ACID tables support snapshot isolation, so you can overwrite a partition while reading from it — unlike non-ACID tables, which needed a staging area. A new base_N is produced and the cleaner removes the old deltas.
• The partition holds an exclusive lock while this runs, so only target fully loaded partitions (typically D-1 or older).
• The SELECT list must contain every non-partition column in definition order.

Row count validation

Compare counts before and after the compaction or INSERT OVERWRITE. With hive.compute.query.using.stats=true, COUNT(*) may be answered from Metastore statistics instead of the data, so turn it off for validation:

SET hive.compute.query.using.stats=false;
 
-- Before
SELECT COUNT(*) FROM dw.sales WHERE dt='2026-10-01';
 
-- Run compaction / INSERT OVERWRITE
 
-- After
SELECT COUNT(*) FROM dw.sales WHERE dt='2026-10-01';

Check the resulting file count in HDFS:

hdfs dfs -ls -R /warehouse/tablespace/managed/hive/dw.db/sales/dt=2026-10-01

Automatic compaction thresholds

The Hive Metastore initiator requests compactions automatically based on these settings. They decide when a compaction starts, not the resulting file size.

To disable automatic compaction for a single table — useful when you manage its file count yourself via INSERT OVERWRITE:

ALTER TABLE dw.sales SET TBLPROPERTIES ('no_auto_compaction'='true');

Preventing small files

Changing how you load data is more effective than compacting after the fact:

• Batch small INSERTs together and load in as few statements as possible.
• Avoid repeated INSERT INTO ... VALUES; each run creates another delta.
• Run major compaction regularly on partitions that have finished loading.

Operational flow

Recommended settings

Summary

For ACID tables, COMPACT 'major' is the default; reach for Hive INSERT OVERWRITE or rebalance compaction only when you also need to pin the file count. Whichever you use, run it only on fully loaded partitions and validate with before/after COUNT(*) with stats-based answers disabled.