Workload

Hadoop legacy SQL queries to BigQuery

Translate Hive/Impala/Spark SQL—partition-driven filters, analytic functions, and Hadoop-era function idioms—into BigQuery Standard SQL with validation gates that prevent drift and scan-cost surprises.

Book assessment Explore workloads

Quick answer

Translate Hive/Impala/Spark SQL—partition-driven filters, analytic functions, and Hadoop-era function idioms—into BigQuery Standard SQL with validation gates that prevent drift and scan-cost surprises.

Back to pair page

Context

Why this breaks

Hadoop-era SQL estates are shaped by Hive metastore conventions, partition columns as a performance requirement, and dialect-specific functions. BigQuery will compile many translated queries—but drift and cost spikes happen when partition pruning, implicit casts, and time semantics aren’t made explicit. Common symptoms after cutover:

  • Scan costs spike because partition predicates aren’t translated into BigQuery pruning-friendly filters - KPI drift from NULL/type coercion differences in CASE/COALESCE and join keys - Window logic behaves differently when ordering is incomplete or ties exist - Regex and string function behavior changes due to dialect differences - Timestamp/date intent shifts (DATE vs TIMESTAMP, timezone boundaries) SQL migration must preserve both meaning and pruning posture so BigQuery stays predictable.

Approach

How conversion works

  • Inventory & prioritize the SQL corpus (BI extracts, views, scheduled reports, ETL SQL) across Hive/Impala/Spark. Rank by business impact and risk patterns (partition filters, time, windows, casts). - Normalize dialect noise: identifiers/quoting, CTE shapes, and common Hadoop-era function idioms. - Rewrite with rule-anchored mappings: dialect-specific functions → BigQuery equivalents, explicit cast strategy, and deterministic ordering for windowed filters. - Partition/pruning rewrites: translate year/month/day/dt predicates into direct BigQuery partition filters and eliminate patterns that defeat pruning. - Validate with gates: compile/run checks, catalog/type alignment, golden-query parity, and edge-cohort diffs. - Performance-safe refactors: recommend partitioning/clustering alignment and pruning-friendly query shapes for top workloads.

Coverage

Supported constructs

Representative Hadoop-era SQL constructs we commonly convert to BigQuery Standard SQL (exact coverage depends on your estate).

SourceTargetNotes
Partition predicates (dt/year/month columns)BigQuery partition filters (DATE/TIMESTAMP partitioning)Rewrite to preserve pruning and predictable scan costs.
Window functions (ROW_NUMBER/RANK/OVER)BigQuery window functionsDeterministic ordering and tie-breakers enforced where needed.
Epoch/time conversion helpersTIMESTAMP_SECONDS/MILLIS and explicit timezone handlingDATE vs TIMESTAMP intent normalized explicitly.
NULL/type coercion idiomsExplicit casts + null-safe comparisonsPrevents join drift and filter selectivity changes.
String/regex functionsBigQuery string/regex equivalentsRegex differences validated with golden cohorts.
LIMIT/TOP-N patternsLIMIT with explicit ORDER BYOrdering made explicit for deterministic top-N outputs.

Compare

How workload changes

TopicHadoop SQL (Hive/Impala/Spark)BigQueryNotes
Performance contractPartition predicates are mandatory to avoid HDFS scansBytes scanned is the cost driver; pruning must be explicitQuery translation must preserve pruning-friendly filters.
Type behaviorImplicit casts often toleratedExplicit casts recommended for stable outputsValidation focuses on CASE/COALESCE and join keys.
Time semanticsEpoch/timezone assumptions often implicitExplicit DATE vs TIMESTAMP + timezone conversionsBoundary-day reporting must be tested explicitly.

Examples

Examples

Representative Hadoop SQL → BigQuery rewrites for partition filters, epoch conversion, and windowed dedupe. Adjust identifiers and types to your schema.

01_partition_filter_hadoop.sql
-- Hadoop-era pattern: year/month/day partitions
SELECT COUNT(*)
FROM events
WHERE year = 2025 AND month = 1 AND day BETWEEN 1 AND 7;
01_partition_filter_bigquery.sql
-- BigQuery: prefer direct partition filter
SELECT COUNT(*)
FROM `proj.ds.events`
WHERE event_date BETWEEN DATE '2025-01-01' AND DATE '2025-01-07';
02_epoch_to_ts_hadoop.sql
-- Hive/Impala: epoch seconds to timestamp
SELECT from_unixtime(ts_sec) AS ts
FROM t;
02_epoch_to_ts_bigquery.sql
-- BigQuery: TIMESTAMP_SECONDS
SELECT TIMESTAMP_SECONDS(ts_sec) AS ts
FROM `proj.ds.t`;
03_windowed_dedupe_bigquery.sql
-- BigQuery: deterministic dedupe with tie-breaker
SELECT *
FROM `proj.ds.events`
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY id
  ORDER BY event_ts DESC, SAFE_CAST(src_offset AS INT64) DESC, ingested_at DESC
) = 1;

Workload Assessment

Translate Hadoop SQL with pruning and parity gates

We inventory your SQL estate, convert a representative slice, and deliver parity evidence on golden queries—plus a pruning/cost risk register so BigQuery spend stays predictable.

Book assessment

Avoid

Common pitfalls

  • Partition predicate loss: queries that relied on Hadoop partition columns now scan entire BigQuery tables.
  • Defeating pruning: wrapping partition columns in functions or casting in filters prevents partition elimination.
  • Implicit cast drift: Hive/Impala coercion differs; BigQuery needs explicit casts for stable outputs.
  • NULL semantics in joins: equality behavior differs; joins can drop or duplicate rows.
  • Window ordering ambiguity: ROW_NUMBER/RANK without stable tie-breakers causes nondeterministic drift.
  • Regex dialect differences: escaping and matching behavior changes edge outputs.
  • Timezone assumptions: epoch conversions and boundary-day reporting drift if not standardized.

Proof

Validation approach

  • Compilation gates: converted queries compile under BigQuery Standard SQL.
  • Catalog/type checks: referenced objects exist; implicit casts surfaced and made explicit.
  • Golden-query parity: critical dashboards and reports match outputs or agreed tolerances.
  • KPI aggregates: compare aggregates by key dimensions and partitions (date, region, product, cohorts).
  • Edge-cohort diffs: validate ties, null-heavy segments, boundary dates, and timezone transitions.
  • Pruning/performance baseline: capture bytes scanned and runtime for top queries; set regression thresholds.

Execution

Migration steps

A sequence that keeps correctness measurable and keeps scan costs predictable.

  1. 01

    Collect and prioritize the query estate

    Export BI SQL, view definitions, scheduled report queries, and ETL SQL across Hive/Impala/Spark. Rank by business impact, frequency, and risk patterns.

  2. 02

    Define pruning and semantic contracts

    Agree on partition/filter contracts, casting rules, null-safe join expectations, and timezone intent. Identify golden queries for sign-off.

  3. 03

    Convert with rule-anchored mappings

    Apply deterministic rewrites for common Hadoop-era idioms and flag ambiguous intent with review markers (implicit casts, ordering ambiguity, regex semantics).

  4. 04

    Validate with golden queries and edge cohorts

    Compile and run in BigQuery, compare KPI aggregates, and test edge cohorts (ties, null-heavy segments, boundary dates).

  5. 05

    Tune top queries for BigQuery

    Ensure partition filters are pruning-friendly, align partitioning/clustering to access paths, and recommend pre-aggregations/materializations for the most expensive BI queries.

FAQ

Frequently asked questions

What’s the biggest cost risk when migrating Hadoop SQL to BigQuery? +

Losing partition-pruning behavior. If partition predicates don’t translate into BigQuery partition filters (or filters defeat pruning), bytes scanned can explode. We treat pruning as part of correctness and validate it.

Do Hive/Impala/Spark functions map cleanly to BigQuery? +

Many do, but edge cases around types, NULLs, regex, and time conversion can drift. We use rule-anchored mappings and validate high-risk constructs with golden queries and edge cohorts.

How do you validate that outputs match? +

We run compile/type gates, then validate golden queries, KPI aggregates by key dimensions, and targeted diffs on edge cohorts (ties, null-heavy segments, boundary dates).

Will performance be the same after translation? +

Not automatically. Hadoop-era query shapes need pruning-friendly filters and BigQuery-native layout alignment (partitioning/clustering). We baseline and tune the top queries as part of the workload.

Migration Acceleration

Ship BigQuery queries with proof-backed sign-off

Get a conversion plan, review markers, and validation artifacts so query cutover is gated by evidence and rollback-ready criteria—without scan-cost surprises.

Book assessment
Book assessment