Skip to content
Tutorials Center
My learning

Topics

Lesson 29 of 36 ยท UDFs and Transactions

MERGE, UPDATE, DELETE and Compaction

Write row-level changes in Hive with UPDATE, DELETE and MERGE INTO, then keep ACID tables fast with minor and major compaction, SHOW COMPACTIONS and the cleaner.

With transactional tables in place, you can change data row by row. This lesson shows the statements, then the housekeeping that keeps such tables fast: compaction.

These Hive statements need a real transactional table, so they are read-only here. The editors use SQLite, which has UPDATE, DELETE and an INSERT ... ON CONFLICT upsert but no MERGE; the stand-ins say where that matters.

INSERT forms

SQL
INSERT INTO customers VALUES (1, 'Ana', '[email protected]'), (2, 'Ben', '[email protected]');
INSERT INTO customers SELECT id, name, email FROM staging_customers;
INSERT OVERWRITE TABLE dim_country SELECT * FROM staging_country;   -- replaces contents

-- Multi-insert: scan the source once, write several targets
FROM staging_orders s
INSERT INTO TABLE orders_eu SELECT s.* WHERE s.region = 'EU'
INSERT INTO TABLE orders_us SELECT s.* WHERE s.region = 'US';

INSERT INTO appends a new delta; INSERT OVERWRITE replaces the contents (on an ACID table it is a transactional operation too).

UPDATE and DELETE

SQL
UPDATE customers SET email = lower(email) WHERE email <> lower(email);

DELETE FROM customers WHERE id = 2;

Rules:

  • The table must be a full ACID table (ORC, transactional = true).
  • You cannot update partition or bucketing columns (the value decides the file location). Delete and insert instead.
  • Each statement writes new delta files, so thousands of single-row statements create thousands of tiny files. Batch changes into set-based statements.
SQL UPDATE and DELETE (SQLite stand-in)

Press Run (or Ctrl + Enter) to see the result here.

MERGE INTO

MERGE applies a batch of changes (inserts, updates, deletes) from a source to a target in one atomic statement. It is the standard way to synchronize a Hive table with a change feed from an operational database.

SQL
MERGE INTO customers AS t
USING staging_customers AS s
ON t.id = s.id
WHEN MATCHED AND s.op = 'D' THEN DELETE
WHEN MATCHED THEN UPDATE SET name = s.name, email = s.email
WHEN NOT MATCHED AND s.op <> 'D' THEN INSERT VALUES (s.id, s.name, s.email);

Rules and cautions:

  • WHEN MATCHED clauses are tried in order; the first whose extra condition is true wins. At most two WHEN MATCHED clauses are allowed (one UPDATE, one DELETE), and each source row may match at most one target row. If several source rows match the same target row, Hive raises a cardinality violation error, so de-duplicate the source first (for example by latest timestamp with ROW_NUMBER()).
  • The ON clause should use the table's key. Joining on a non-key column is slow and often wrong.
  • The statement is one transaction, so a failure leaves the target unchanged.

SQLite lacks MERGE, but an upsert plus a delete achieves the same result for this change feed:

SQL Upsert and delete as a MERGE stand-in (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

Why compaction exists

Every INSERT, UPDATE and DELETE adds a delta directory. After many statements a reader must open hundreds of small files and merge them, which makes queries slower and puts pressure on the NameNode. Compaction is a background job that rewrites many delta files into fewer ones.

Type What it does Cost
Minor Merges all deltas (and delete deltas) into one delta and one delete delta, leaving base alone Cheap
Major Rewrites base plus all deltas into a new base, physically applying deletes Expensive, reclaims space

After a compaction finishes, the cleaner deletes the old directories once no running query can still need them. Until the cleaner runs, you temporarily use more disk.

flowchart LR
    A["base_5 + delta_6 + delta_7 + delete_delta_8 ..."] -->|"minor"| B["base_5 + delta_6_8 + delete_delta_6_8"]
    A -->|"major"| C["base_8 (single new base)"]

Minor compaction reduces the number of files; major compaction produces a clean base.

Automatic compaction

The initiator thread (in the metastore) checks tables and starts compactions when thresholds are met:

SQL
-- metastore settings
-- hive.compactor.initiator.on = true
-- hive.compactor.worker.threads = 2
-- hive.compactor.delta.num.threshold = 10      deltas before minor compaction
-- hive.compactor.delta.pct.threshold = 0.1     delta size / base size before major compaction

Manual compaction and monitoring

SQL
ALTER TABLE customers COMPACT 'minor';
ALTER TABLE customers PARTITION (sale_date = '2026-01-15') COMPACT 'major' AND WAIT;

SHOW COMPACTIONS;

SHOW COMPACTIONS lists each request with its state: initiated, working, ready for cleaning, succeeded, failed or attempted. A table that repeatedly fails to compact is a sign of stuck transactions, missing worker threads or permission problems on the data directory. You can also set per-table properties, for example 'NO_AUTO_COMPACTION'='true' to disable it, or 'compactorthreshold.hive.compactor.delta.num.threshold'='20' to override a threshold.

Simulating the effect of compaction

JS How many files must a reader open?

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Good practice

  • Run batch MERGE/INSERT ... SELECT statements, not row-at-a-time scripts.
  • Let the compactor run; monitor SHOW COMPACTIONS and alert on failed.
  • Avoid long-lived open transactions and queries: they block the cleaner.
  • After a very large update, trigger a manual major compaction during a quiet period.
  • For purely append-only data, consider an insert-only table or external tables with partitions instead; they have no delete files to reconcile.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What happens if two source rows match the same target row in a MERGE?

    Each source row must match at most one target row, so de-duplicate the source first.
  2. 2. Which compaction rewrites the base and applies deletes physically?

    Major compaction builds a new base from the old base plus all deltas.
  3. 3. Which command shows pending and finished compactions?

    SHOW COMPACTIONS lists each request and its state.
  4. 4. Why are thousands of single-row INSERT statements a bad idea on an ACID table?

    Batch the data so each statement writes a larger delta.

Summary

  • UPDATE, DELETE and MERGE INTO work on full ACID (ORC) tables; partition and bucket columns cannot be updated.
  • MERGE applies inserts, updates and deletes atomically; each source row may match only one target row.
  • Every change adds delta files; compaction (minor and major) merges them and the cleaner removes old files.
  • Monitor with SHOW COMPACTIONS; batch your writes and avoid long-running transactions.

Next: Execution Engines: Tez and LLAP