Somebody on the team ran the batch SQL translator over 1,400 HiveQL scripts, got back 1,350 that converted without errors, and reported the migration as 96% done. It was maybe 30% done. The translated SQL was correct — it ran, it returned the same rows — and it was also, in a dozen places, a faithful reproduction of decisions that made sense on Hive and were actively wrong on BigQuery. A table partitioned by year/month/day directories became a table BigQuery couldn't partition that way at all. A query that scanned a whole table cost nothing on-prem, because the cluster was already paid for, and now had a price tag on every run.
That's the shape of this migration and the reason it's worth writing down. Hive-to-BigQuery is unusually well-tooled — Google gives away most of the mechanical work — and the tooling's competence is exactly what lets teams skip the part that actually matters. Translating the SQL is not the migration. Re-modeling for a warehouse with different physics is. This is the last leg of the Cloudera exit: Dataproc took the Spark jobs, Bigtable took the HBase tables, and the Hive warehouse is what's left.
What does the migration tooling actually do?
The BigQuery Migration Service is a free set of tools covering assessment, SQL translation, data transfer, and validation — and it's genuinely good at the mechanical parts. The batch SQL translator converts HiveQL to GoogleSQL at scale; the interactive translator sits in the BigQuery console for one-off conversions you want to test immediately. Point them at your scripts and most of them come back working.
What that buys you is real: nobody should hand-convert 1,400 scripts. What it doesn't buy you is a decision about whether the thing being translated should exist in that shape at all. The translator is a compiler, not an architect. It faithfully preserves your Hive design — including the parts of your Hive design that were workarounds for Hive.
There's also a bridge worth knowing about for the transition: the Hive-BigQuery connector, which went GA in 2023, is a Hive storage handler that lets Hive itself read and write BigQuery tables. That means you can move data into BigQuery first and keep existing Hive queries running against it while you migrate the query layer on your own schedule, rather than needing a big-bang cutover of storage and compute together. On a large estate, decoupling those two moves is often what makes the plan survivable.
Why doesn't Hive partitioning map onto BigQuery?
This is the big one, and it's the difference that quietly invalidates the most Hive design decisions. In Hive, a partition is a directory. You can partition by as many columns as you like, nested arbitrarily — /year=2024/month=11/day=01/region=emea/ — and dynamic partition inserts will happily create thousands of them. It's flexible, it's directory-shaped, and it produces the small-file swamps everyone who's run Hive at scale has waded through.
BigQuery doesn't do that. A BigQuery table has one partitioning column (by date/timestamp, ingestion time, or integer range), with a limit of a few thousand partitions per table, plus up to four clustering columns that sort data within each partition. That's the whole vocabulary. Multi-level nested partitioning has no direct translation, and this is where a mechanical port produces a bad warehouse.
| Hive | BigQuery | |
|---|---|---|
| Partitioning | Any number of columns, nested directories | Exactly one column (date/timestamp/ingestion/int range) |
| Partition count | Effectively unbounded (and that's the problem) | Capped in the low thousands per table |
| Secondary organization | Bucketing (CLUSTERED BY ... INTO n BUCKETS) | Clustering — up to 4 columns, order matters, auto-maintained |
| Physical layout | Yours to manage — files, dirs, compaction | Managed. You don't see files. |
| Pruning | Directory pruning from the metastore | Partition pruning + block-level clustering pruning |
The translation rule that works in practice: the highest-cardinality time dimension becomes the partition column, and the columns you used to nest underneath it become clustering columns, ordered by how often you filter on them. Your year/month/day/region Hive table becomes a table partitioned by day and clustered by region. You lose nothing that mattered, and you gain BigQuery pruning blocks within a partition instead of scanning it whole.
-- What a faithful translation of the Hive DDL wants to produce.
-- Multi-level partitions don't exist here; this is a design decision
-- being made by accident.
-- PARTITIONED BY (year INT, month INT, day INT, region STRING) -- no.
-- What you actually want: one time partition + clustering on the
-- dimensions you filter by, in filter-frequency order.
CREATE TABLE analytics.orders
PARTITION BY DATE(order_ts)
CLUSTER BY region, customer_id
AS SELECT * FROM staging.orders_raw;
-- Now this prunes to one partition AND skips blocks within it.
SELECT SUM(amount) FROM analytics.orders
WHERE DATE(order_ts) = '2024-11-01' -- partition prune: cheap
AND region = 'emea'; -- cluster prune: cheaperThe cost model changed underneath you, and nobody told the SQL. On the Cloudera cluster, a full-table scan was free — you'd already bought the machines, so a lazy query just took longer. On BigQuery's on-demand pricing you pay per byte scanned, which means the exact same query, translated perfectly, is now a recurring bill. A missing WHERE on the partition column doesn't fail, doesn't warn, and doesn't run slowly — it just charges you. This is why "the translator finished" is a dangerous milestone: it converts your queries and preserves every scan-everything habit your team learned when scanning was free. Partition pruning stopped being a performance tweak and became a cost control, and that reframing has to reach the analysts, not just the platform team.
What actually needs a rewrite?
Beyond the modeling, a short list of things the translator can't fix for you:
- Hive UDFs. Custom Java UDFs have no home in BigQuery. You rewrite them as SQL UDFs, JavaScript UDFs, or remote functions backed by Cloud Run. A jar full of a decade's accumulated business logic is a project, not a translation.
- Type-system gaps. Most Hive types map cleanly; the exceptions bite.
MAPandUNIONhave no direct equivalent — you land onSTRUCT,ARRAY<STRUCT>, orJSON, and the choice changes how every downstream query addresses that column. - SerDes and file-format quirks. A custom SerDe reading some 2014 fixed-width format is logic that has to move into the ingestion path.
- Dynamic partition insert patterns. Idioms built around Hive's dynamic partitioning often become
MERGEor partition-overwrite patterns with genuinely different semantics — worth re-deriving rather than translating. - Small-file compaction jobs. Delete them. BigQuery manages storage; the entire category of maintenance disappears, and it's satisfying to remove.
Do I have to move all the data at once?
No, and you shouldn't. BigLake external tables let you stage the HDFS files in Cloud Storage and query them from BigQuery in place, without loading them into BigQuery's managed storage. That gives you a staging state where the data is reachable from BigQuery before you've committed to a final schema — useful for the long tail of tables nobody's sure anyone still queries.
The pragmatic sequencing I'd use, and roughly the shape of the plan on the projects I've seen work:
flowchart LR
HDFS[("HDFS
Hive warehouse")] -->|"1 · copy files"| GCS[("Cloud Storage")]
GCS -->|"2 · BigLake external tables"| BQE["Queryable in place
no schema commitment yet"]
BQE -->|"3 · re-model: partition + cluster"| BQM[("BigQuery managed tables")]
HDFS -.->|"assess: what's actually used?"| ASSESS["Assessment
drop the dead tables"]
ASSESS -.-> BQM
HIVE["Existing Hive queries"] -->|"Hive-BigQuery connector
keeps running during transition"| BQM
BQM -->|"4 · translate + re-point"| BI["BI · dbt · downstream"]
Lift the files, expose them as BigLake external tables so nothing is blocked on schema decisions, then re-model the tables that earn it into managed BigQuery tables with proper partitioning and clustering. The connector keeps existing Hive queries alive so storage and compute don't have to cut over on the same day.
Step "assess" deserves more attention than it gets. Every Hive warehouse I've migrated contained a substantial fraction of tables that no one had queried in a year — outputs of pipelines whose consumers left, staging tables from a project that shipped in 2019. The migration is the one moment you have permission to delete them. Porting a dead table costs you the translation effort, the re-modeling decision, and then storage forever. Run the assessment, find the tables with no reads, and don't move them.
What does BigQuery give you that Hive couldn't?
Worth naming, because the migration is a lot of work and the payoff should be explicit. BigQuery separates storage from compute over Google's own filesystem and network, executes through Dremel's tree architecture, and has no cluster to size — the internals of which I went through in the BigQuery internals piece. Practically, that means no YARN queue contention between the ETL job and the analyst's ad-hoc query, no capacity planning meeting, no NameNode, no compaction cron, and concurrency that doesn't require someone to arbitrate. The Hive warehouse's operational surface — which, if you've run one, you know is most of the job — mostly evaporates.
What to carry away
Take the free tooling; it's good and hand-converting HiveQL is a waste of a quarter. Then refuse to believe the number it reports. A translated Hive warehouse is a Hive warehouse expressed in GoogleSQL, and BigQuery's physics are different in two ways that matter more than syntax: you get one partition column plus four clustering columns instead of arbitrary nested directories, so the multi-level partitioning at the heart of your Hive schema has to be re-derived rather than ported; and you pay per byte scanned, so the scan-everything habits that were free on a cluster you'd already bought now show up on an invoice every single month. Stage through BigLake so you're not blocked on schema decisions, use the assessment to delete the tables nobody reads instead of migrating them, budget honestly for the UDF jar nobody wants to open — and treat the last 20% as the actual migration, because it is.