Anurag’s Read

How Uber Migrated ETL from Hive to Spark SQL.

A rare concrete migration story with shadow runs, query translation, data validation and many Hive versus Spark differences. Strong migration lessons.
Score
8.7
Verdict
Must read
Blog and date
Uber,
Original length and level
14 min, advanced
Topics
migrations, spark, data quality, etl
Read the original

Context

Hive on Spark had about 18,000 workflows and 5 million queries a month across 440 teams. Open-source Hive on Spark was discontinued from Hive 3. Spark 3 features such as AQE and dynamic partition pruning showed gains up to 4x.

Architecture

The Automated Migration Service watches production Hive workflows and starts a Spark SQL shadow run. The Query Translation Service converts the Hive query and rewrites table, database and location names to protect production data. After both runs finish, the Data Validation Service compares data and performance.

When enough runs of each query pass, the workflow becomes READY_TO_MIGRATE. Later runs use dynamic translation, and finally the source code is updated to submit Spark SQL.

Mechanisms

M1

Translation pipeline

The query becomes an AST, then an engine-neutral SqlNode, then transformers rename or replace nodes. The target side produces an engine-specific node and unparses it with the Spark dialect. Multi-query payloads keep context from earlier queries.

M2

Validation chain

Row count runs first and stops on mismatch. Row checksum comes next and can allow a mismatch threshold. Sampling uses a hash of a primary key below a threshold so both sides compare the same rows.

M3

Validation noise

Float sums differ slightly, so a tolerance and rounding apply. JSON strings get sorted keys before the checksum. Non-deterministic functions such as RAND are excluded by hand.

M4

Small-file fix

Hive merges output files at the end, so Spark made many small files. Uber added a rebalance node before the write and changed AQE rules to aim at file size. HiveHash support makes each Hive bucket file match one Spark partition.

Design decisions

No.ChoseInstead ofWhy
D1Build a custom translatorLinkedIn CoralCoral supports only SELECT, misses some syntax and depends on Calcite RelNode.
D2Automate with shadow runsAsk each team to migrateShadow runs avoided effort from 440 teams.

Key numbers

  • 18,000 workflows.
  • 5 million queries per month.
  • 200K Hive queries per day, 50K unique.
  • 440 teams.
  • About 50% less runtime and resource use.

Failures and limits

  • Spark lacked some Hive behavior, such as boolean casts, header skipping and varchar.
  • Manual column exclusion and tolerance are not foolproof.
  • Checksum checks cost compute and storage.
  • UUID file names caused read failures during overwrite, fixed with partition snapshot isolation.

Lessons you can use

  • Shadow-run both engines and compare data before cutover.
  • Use consistent key-hash sampling for comparisons.
  • Expect behavior differences beyond syntax.

Glossary

Shadow runRunning new code on real inputs without exposing its output.
AQEAdaptive query execution, which tunes a Spark plan at run time.
CoralLinkedIn's SQL translation library.
ETLExtract, transform, load jobs for data pipelines.