Workload

UDFs & procedural utilities for Hive → BigQuery

Re-home reusable logic—from Hive UDFs and SerDe-era parsing helpers to macro-style ETL utilities—into BigQuery routines with explicit contracts and a replayable harness so behavior stays stable under reruns and backfills.

Book assessment Explore workloads

Quick answer

Re-home reusable logic—from Hive UDFs and SerDe-era parsing helpers to macro-style ETL utilities—into BigQuery routines with explicit contracts and a replayable harness so behavior stays stable under reruns and backfills.

Back to pair page

Context

Why this breaks

Hive environments rarely have “stored procedures,” but they do have procedural behavior: Java/Scala/Python UDFs, SerDe-era parsing utilities, and script-driven macros that generate SQL, move partitions, and update control tables. These assets embed business rules, typing assumptions, and side effects. When migrated naïvely, SQL may compile in BigQuery but outputs drift because UDF semantics, regex behavior, NULL handling, and time conversion differ—and restartability rules disappear. Common symptoms after migration:

  • UDF outputs drift due to type coercion and NULL handling differences - Regex/string behavior changes (dialect and escaping differences) - Epoch/time conversion helpers drift on boundary days (timezone intent missing) - Script-driven dynamic SQL behaves differently under templating and quoting - Side effects (audit/control tables) aren’t modeled, so reruns/backfills double-apply or skip A successful migration converts these scattered utilities into explicit BigQuery routines with a behavior contract and a replayable test harness.

Approach

How conversion works

  • Inventory & classify UDFs and utilities: Hive UDF jars, custom functions referenced in SQL, SerDe parsing logic, and macro-like scripts (Oozie/shell) that generate SQL. - Extract the behavior contract: inputs/outputs, typing, NULL rules, regex expectations, time semantics, side effects, and failure behavior. - Choose BigQuery target form per asset:
  • SQL UDF for pure expressions - JavaScript UDF for complex/regex-heavy logic - Stored procedure (SQL scripting) for multi-statement control flow and dynamic SQL - Set-based refactor where script loops exist - Translate and normalize: explicit casts, null-safe comparisons, timezone intent, and deterministic ordering where logic depends on ranking/dedup. - Validate with a harness: golden inputs/outputs, branch coverage, failure-mode tests, and side-effect assertions—then integrate into representative pipelines.

Coverage

Supported constructs

Representative Hive-era procedural constructs we commonly migrate to BigQuery routines (exact coverage depends on your estate).

SourceTargetNotes
Hive UDFs (Java/Scala/Python)BigQuery SQL UDFs / JavaScript UDFsChoose target form based on complexity and semantics; validate edge cases.
Regex-heavy string transformsBigQuery REGEXP_* functions or JS UDFsRegex dialect differences validated with golden cohorts.
SerDe parsing helpersTyped extraction tables + UDFs where neededExtract once, cast once, reuse everywhere.
Dynamic SQL via macrosEXECUTE IMMEDIATE with parameter bindingNormalize identifier rules; reduce drift and injection risk.
Control tables for restartabilityApplied-window tracking + idempotency markersReruns/backfills become safe and auditable.
Epoch/time conversion helpersTIMESTAMP_SECONDS/MILLIS + explicit timezone handlingPrevents boundary-day drift in reporting.

Compare

How workload changes

TopicHiveBigQueryNotes
Where logic livesUDF JARs + script-driven SQL utilities in orchestrationCentralized routines (UDFs/procedures) with explicit contractsMigration consolidates and stabilizes reusable logic.
Typing and coercionHive implicit casts often toleratedExplicit casts recommended for stable outputsValidation focuses on mixed-type branches and join keys.
Regex/time semanticsDialect-specific regex and epoch conversionsBigQuery REGEXP + explicit timestamp intentEdge cohorts are mandatory for tricky strings and boundary days.
Operational behaviorReruns/retries encoded in scripts and coordinatorsIdempotency and side effects must be explicitHarness proves behavior under reruns/backfills.

Examples

Examples

Illustrative patterns for moving Hive-era UDF and macro utilities into BigQuery routines. Adjust datasets and types to match your environment.

01_sql_udf_bigquery.sql
-- BigQuery SQL UDF example
CREATE OR REPLACE FUNCTION `proj.util.safe_div`(n NUMERIC, d NUMERIC) AS (
  IF(d IS NULL OR d = 0, NULL, n / d)
);
02_js_udf_bigquery.sql
-- BigQuery JavaScript UDF example (regex/complex logic)
CREATE OR REPLACE FUNCTION `proj.util.normalize_code`(x STRING)
RETURNS STRING
LANGUAGE js AS """
  if (x === null) return null;
  return x.trim().toUpperCase();
""";
03_proc_dynamic_sql_bigquery.sql
-- BigQuery stored procedure with dynamic SQL + parameter binding
CREATE OR REPLACE PROCEDURE `proj.util.refresh_window`(start_d DATE, end_d DATE)
BEGIN
  DECLARE sql STRING;
  SET sql = '''
    DELETE FROM `proj.mart.fact_orders`
    WHERE event_date BETWEEN @s AND @e;

    INSERT INTO `proj.mart.fact_orders`
    SELECT * FROM `proj.stg.fact_orders`
    WHERE event_date BETWEEN @s AND @e;
  ''';

  EXECUTE IMMEDIATE sql USING start_d AS s, end_d AS e;
END;
04_harness_example.sql
-- Minimal harness pattern: compare routine outputs to golden expectations
CREATE TEMP TABLE golden AS
SELECT 10 AS n, 2 AS d, 5 AS expected UNION ALL
SELECT 10, 0, NULL;

SELECT
  n, d,
  `proj.util.safe_div`(n, d) AS got,
  expected,
  IF(`proj.util.safe_div`(n, d) IS NOT DISTINCT FROM expected, 'PASS', 'FAIL') AS verdict
FROM golden;

Workload Assessment

Migrate Hive UDFs with a replayable harness

We inventory your Hive UDFs and macro utilities, migrate a representative subset into BigQuery routines, and deliver a harness that proves parity—including side effects and rerun behavior.

Book assessment

Avoid

Common pitfalls

  • Hidden dependencies: UDFs rely on external JARs/configs or implicit Hive settings not captured in migration.
  • Mixed-type branches: CASE/IF returns mixed types; BigQuery needs explicit casts to preserve intent.
  • NULL semantics drift: comparisons and string functions behave differently unless made explicit.
  • Regex dialect differences: pattern syntax and escaping change outputs for edge inputs.
  • Dynamic SQL via templating: string substitution behaves differently; identifier quoting breaks.
  • Side effects ignored: control-table/audit updates not recreated; reruns/backfills become unsafe.
  • Row-by-row script logic: loops and per-partition scripts should become set-based SQL or bounded windows.

Proof

Validation approach

  • Compile + interface checks: each routine deploys; signatures match the contract (args/return types).
  • Golden tests: curated input sets validate outputs, including NULL-heavy and boundary cases.
  • Regex/time edge cohorts: validate tricky strings, escaping, and boundary-day timestamp conversions.
  • Branch + failure-mode coverage: expected failures are tested (invalid inputs, missing rows).
  • Side-effect verification: assert expected writes to control/log/audit tables and idempotency under reruns/backfills.
  • Integration replay: run routines inside representative pipelines and compare downstream KPIs/aggregates.

Execution

Migration steps

A sequence that keeps behavior explicit, testable, and safe to cut over.

  1. 01

    Inventory UDFs and macro utilities

    Collect Hive UDF jars and custom functions referenced in SQL, plus macro-like scripts that generate SQL or manage partitions/control tables. Build the call graph to pipelines and reports.

  2. 02

    Define the behavior contract

    For each asset, specify inputs/outputs, typing, NULL rules, regex/time expectations, expected failures, and side effects. Decide target form (SQL UDF, JS UDF, procedure, or refactor).

  3. 03

    Convert with safety patterns

    Make casts explicit, normalize timezone intent, implement null-safe comparisons, and migrate dynamic SQL using EXECUTE IMMEDIATE with bindings and explicit identifier rules.

  4. 04

    Build a replayable harness

    Create golden input sets, regex/time edge cohorts, and failure-mode tests. Validate outputs and side effects deterministically so parity isn’t debated at cutover.

  5. 05

    Integrate and cut over behind gates

    Run routines in representative pipelines, compare downstream KPIs, validate reruns/backfills, and cut over with rollback-ready criteria.

FAQ

Frequently asked questions

Hive doesn’t have stored procedures—what are we migrating? +

Mostly UDFs (often Java/Scala/Python) and script-driven procedural behavior embedded in orchestration (partition management, macro SQL generation, control tables). We migrate that into BigQuery UDFs/procedures with explicit contracts and a validation harness.

Do we have to rewrite all UDFs in JavaScript? +

No. Many can become BigQuery SQL UDFs. We use JS UDFs only when logic is complex or regex/object handling requires it. The target form is chosen per asset to reduce risk and cost.

How do you handle regex and time edge cases? +

We treat them as contract tests: build golden cohorts for tricky patterns and boundary days, then validate outputs explicitly. JS UDFs are used when that’s the safest parity path.

How do you prove parity for UDFs and utilities? +

We build a replayable harness with golden inputs/outputs, branch and failure-mode coverage, and side-effect assertions. Integration replay validates downstream KPIs before cutover.

Migration Acceleration

Cut over routines with proof-backed sign-off

Get a conversion plan, review markers for ambiguous intent, and validation artifacts so UDF and utility cutover is gated by evidence and rollback-ready criteria.

Book assessment
Book assessment