Hive Upgrade Path, in depth: planning 2.x to 3.x to 4.x, metastore schema, ACID, clients and rollback

Why Hive upgrades are hard

Four kinds of change arrive with a major Hive version, and each needs its own test. Metadata schema: the metastore's relational schema gains tables and columns, and the upgrade scripts must run against the backing database. Semantics: defaults, reserved words, type handling and table-type rules change, so the same SQL can parse differently or return different results. Execution: engines are deprecated and removed, so jobs pinned to an old engine stop running. Clients: every external system that talks to the metastore has its own Hive client version, and not every client can talk to every server.

The schema upgrade is the irreversible part. Once the metastore database has been upgraded, older Hive servers cannot use it, so rollback means restoring a database backup and losing any metadata changes made since. Everything in the process below is arranged to make that step boring.

What changes at each step

Apache Hive 4.0.0 was released on 29 March 2024 and 4.1.0 on 31 July 2025. Most production estates are on a vendor distribution, and vendors back-port fixes and change defaults, so read your distribution's upgrade notes as well as the Apache ones. The table separates Apache behaviour from distribution behaviour where they differ; the transactional-by-default rule is the important example, since it is a distribution policy built on Apache configuration options, not an Apache default.

Skipping versions is possible for the metastore schema, because the upgrade tool runs every intermediate script in order, but it is not wise for semantics. A 2.3 to 4.x jump stacks the ACID change, the engine removals and the timestamp rework into one cut-over, and when results differ you cannot tell which change caused it. Prefer one major version per change window unless you are rebuilding on a fresh platform anyway.

Choosing a strategy: in place or side by side

In-place upgrade stops Hive services, upgrades binaries on the same hosts, upgrades the metastore schema and restarts. It needs no extra hardware and keeps every path and URL. Its weakness is the window: all clients of the metastore must be compatible with the new server at the moment of cut-over, and rollback is a full restore.

Side-by-side migration builds a new Hive deployment with its own metastore database, seeded from a copy of the old one and upgraded there. Both point at the same storage, or the data is replicated. Workloads move in waves, and the old system stays available for comparison. Its weaknesses are cost and the need to keep metadata consistent while both are live: tables created on the old side after the copy must be replayed on the new side, and two metastores must never both write to the same ACID table, because each keeps its own transaction state.

A useful middle ground is a rehearsed in-place upgrade: copy the production metastore database into a scratch environment, upgrade it there, point a test HiveServer2 at it, and replay real queries against production data read-only. You get most of the safety of side by side without running two production systems.

The metastore schema upgrade

The metastore database records its schema version, and schematool ships SQL scripts for each supported database type (Derby, MySQL/MariaDB, PostgreSQL, Oracle, SQL Server) to move from one version to the next. Run it with the new release's binaries and configuration, against a database you have just backed up.

# 0. Stop every HMS and HiveServer2 instance; nothing may write during the upgrade.

# 1. Back up the metastore database (PostgreSQL shown).
pg_dump -Fc -h hmsdb -U hive metastore > metastore_$(date +%Y%m%d_%H%M).dump

# 2. With the NEW Hive binaries and hive-site.xml: what version does the DB claim?
schematool -dbType postgres -info

# 3. Print the scripts that would run, without executing them.
schematool -dbType postgres -upgradeSchemaFrom 3.1.0 -dryRun

# 4. Run the upgrade, then validate the result.
schematool -dbType postgres -upgradeSchemaFrom 3.1.0
schematool -dbType postgres -validate

# 5. Start one HMS, check its log for schema verification, then the rest.

Rehearse these exact commands on a copy of production first, timing them; scripts that add indexes or rewrite columns on large tables such as PARTITIONS and PARTITION_PARAMS can take far longer than on a test database. Keep hive.metastore.schema.verification enabled so a server refuses to start against a schema version it does not expect, rather than corrupting it. If you run the metastore as a highly available service, upgrade the database once and then roll every instance together; the metastore HA guide describes the topology.

Preparing ACID tables

The step from 2.x to 3.x changed the on-disk layout and versioning of ACID tables. The documented requirement is to run a major compaction on every ACID table and partition with outstanding deltas before upgrading, so that the data sits in base files the new version can read. Apache Hive ships a pre-upgrade tool in its upgrade-acid module that scans the metastore and generates the compaction statements; vendors package equivalents.

-- Find ACID tables still carrying deltas, then compact them before the upgrade window.
SHOW COMPACTIONS;

ALTER TABLE sales.orders PARTITION (order_date='2026-09-30') COMPACT 'major';
ALTER TABLE sales.customers COMPACT 'major';

-- Wait until every request is 'succeeded' (not 'initiated' or 'working')
SHOW COMPACTIONS;

Stop ingestion into those tables between compaction and upgrade, or new deltas in the old format reappear. Budget time: major compaction rewrites every row of every affected partition.

After 3.x, policy matters as much as format. If your distribution creates managed tables as transactional, a CREATE TABLE that used to produce a plain ORC table now produces a full-ACID table, and engines without full-ACID read support, notably Spark's built-in Hive reader, can no longer read it. Decide per table: tables written only by Hive can be managed and ACID; tables shared with other engines should usually be EXTERNAL, or migrated to an open table format. Hive 4's Iceberg integration makes Iceberg the natural target for shared tables, because Spark, Trino and Impala read it natively.

Engines and clients

Execution engine. Hive 4.0 removed Hive on Spark and deprecated Hive on MapReduce, leaving Tez as the supported engine, optionally with LLAP daemons. Search every script, workflow definition and connection string for hive.execution.engine before the upgrade, not after. Tez has its own version line, so install the one your Hive release was built and tested against. Hive on Tez execution and LLAP cover what to tune afterwards.

Hive CLI. The old hive shell is deprecated in 4.0; use Beeline over JDBC to HiveServer2. Scripts that relied on the CLI bypassing HiveServer2 authorisation will start obeying it, which is a security improvement and a source of permission errors on day one.

Metastore clients. Spark talks to the metastore with a configurable client: spark.sql.hive.metastore.version and spark.sql.hive.metastore.jars choose which client library to load, and each Spark release supports a fixed list of versions. Check that list for your Spark release before upgrading the metastore server. Do the same for Impala, Trino, Flink, ingestion tools and BI connectors. A newer metastore usually serves older Thrift clients, but new features and table types may be invisible or unreadable to them.

Worked example: a query-replay harness

The most valuable rehearsal artefact is a replay of real production queries against the upgraded test system, comparing results with the old one. Collect a few days of queries from HiveServer2 logs or your query history, keep the read-only ones, and run the harness below against both servers. It compares row counts and an order-independent checksum, and records failures by category.

import hashlib, json
from pyhive import hive

OLD = dict(host="hs2-old.example", port=10000, username="replay")
NEW = dict(host="hs2-new.example", port=10000, username="replay")

def run(conn_args, sql):
    cur = hive.connect(**conn_args).cursor()
    try:
        cur.execute(sql)
        rows = cur.fetchall()
    except Exception as exc:
        return {"error": type(exc).__name__, "msg": str(exc)[:300]}
    digest = 0
    for r in rows:                          # XOR of row hashes: independent of order
        h = hashlib.sha256(json.dumps(r, default=str).encode()).digest()
        digest ^= int.from_bytes(h[:8], "big")
    return {"rows": len(rows), "digest": digest}

def replay(queries):
    report = {"same": 0, "diff": [], "new_errors": []}
    for q in queries:
        a, b = run(OLD, q), run(NEW, q)
        if "error" in b and "error" not in a:
            report["new_errors"].append((q[:120], b["msg"]))
        elif a != b:
            report["diff"].append((q[:120], a, b))
        else:
            report["same"] += 1
    return report

Triage the output into three buckets. New errors are mostly parse failures from new reserved words, removed functions or configuration names, and engine settings. Different results cluster around timestamps and time zones, implicit casts and decimal precision; each needs a decision on whether the new answer is correct. Performance is the third bucket: record runtimes too, since plan changes from a newer Calcite-based optimizer can make a few queries much slower. XOR checksums ignore duplicate rows that cancel out in pairs, so for critical reports also compare sorted results directly.

In a typical estate the first replay surfaces dozens of failures, most of them a handful of root causes. Fix them in the queries or with explicit session settings, rerun, and repeat until the diff list contains only changes you have consciously accepted.

Failure modes and rollback

• Schema upgrade fails midway. Some scripts are not transactional on every database. Restore the backup and fix the cause; never hand-edit a half-upgraded schema in production.
• Uncompacted deltas. Queries on some ACID partitions fail or return wrong data after 2.x to 3.x. Prevent with the pre-upgrade scan, and re-check immediately before the window.
• Silent result changes. Timestamp and cast differences raise no error. Only replay comparison catches them before users do.
• Broken neighbours. Spark jobs fail reading tables that became ACID, or a BI connector cannot list tables. Inventory clients first and test each against the rehearsal metastore.
• Long compaction backlog. After cut-over, a burst of writes floods the compactor. Watch the compaction queue and the count of open transactions, and size compactor workers before go-live.
• Rollback that was never tested. Rollback means old binaries plus the database backup plus discarding metadata changes since. Rehearse it once with the rest.

What to do next

1. Inventory: Hive version, distribution, metastore database, ACID tables, engines in use and every metastore client with its version.
2. Read the Apache and vendor notes for each major step you will cross; prefer one major version per window.
3. Copy the production metastore database to a scratch environment and rehearse schematool with timings.
4. Run the ACID pre-upgrade scan and major compactions; freeze ingestion until cut-over.
5. Replace Hive on Spark, MapReduce and Hive CLI usage before the upgrade.
6. Replay a week of production queries against old and new systems; resolve every diff deliberately.
7. Decide per table between managed ACID, external and Iceberg, based on which engines read it.
8. Write and rehearse the rollback, then schedule the window.