Job Board
Consulting

SQL Scripting in Spark 4.1: Loops, Conditionals, and Error Handling

SQL Scripting reached GA and is enabled by default in Spark 4.1 (SPARK-54499), bringing variables, IF/CASE, WHILE/REPEAT/FOR loops, and DECLARE ... HANDLER error handling to pure SQL. Here's what it does, when it beats reaching for Scala, and how to run a script from a Spark Scala application.

For the broader release picture, see Spark 4.1 Release Highlights for Scala Developers.

What SQL Scripting Is

SQL Scripting is procedural SQL — the ANSI SQL/PSM control-flow layer that stored-procedure languages have had for decades — running natively inside Spark's SQL engine. It was introduced experimentally in Spark 4.0 behind a flag. In 4.1 it's GA and on by default (SPARK-54499), so there's no config to flip.

The unit of work is a compound statement: a BEGIN ... END block that can declare local variables, run statements, branch, loop, and catch errors. You submit the whole block as a single string:

spark.sql("""
  BEGIN
    DECLARE c INT DEFAULT 10;
    WHILE c > 0 DO
      INSERT INTO countdown VALUES (c);
      SET c = c - 1;
    END WHILE;
    SELECT * FROM countdown ORDER BY 1;
  END
""")

That's the whole feature in one breath: declarations, a loop, mutation, and a final query — no Scala driver loop, no round trips.

How Results Surface in Scala

This is the part that trips people up coming from spark.sql("SELECT ..."). A script can contain many statements, but the DataFrame you get back corresponds to the last statement that produces a result set — in the example above, the trailing SELECT. Everything before it (the DECLARE, the loop, the INSERTs) executes for its side effects.

// The returned DataFrame is the final SELECT's output
val summary = spark.sql("""
  BEGIN
    DECLARE total BIGINT;
    SET total = (SELECT COUNT(*) FROM events);
    SELECT total AS event_count, CURRENT_DATE() AS as_of;
  END
""")

summary.show()

A practical consequence: keep the query you actually care about last, and treat the script body as orchestration. If a script ends with an INSERT or DDL, spark.sql returns an empty DataFrame, exactly as those statements do on their own.

Because it's still just spark.sql, everything you already do with DataFrames applies — the script runs lazily-triggered like any action, respects the active SparkSession config, and participates in the same catalog. There is no separate API surface to learn on the Scala side.

Variables and Assignment

DECLARE introduces a local variable scoped to its compound statement. Spark 4.1 added multi-variable declarations (SPARK-52998), so related state doesn't need a line each:

spark.sql("""
  BEGIN
    DECLARE low_threshold, high_threshold INT;
    SET low_threshold = 100;
    SET high_threshold = 1000;

    -- SET can pull from a query
    DECLARE flagged BIGINT;
    SET flagged = (SELECT COUNT(*) FROM orders WHERE amount < low_threshold);

    SELECT flagged AS below_threshold;
  END
""")

Local script variables are distinct from session variables (DECLARE VARIABLE ... at the session level, set with SET VAR). Session variables outlive the script and are visible to later statements in the same Spark session; script-local variables vanish when the END is reached. Reach for session variables when a Scala job needs to stash a value across several spark.sql calls; reach for local variables for everything internal to one script.

Conditionals: IF and CASE

IF / ELSEIF / ELSE and a searched CASE cover branching. One of the 4.1 fixes worth knowing: NULL behavior in scripting conditions was corrected (SPARK-52345), so a condition that evaluates to NULL is treated as false rather than throwing or behaving inconsistently.

spark.sql("""
  BEGIN
    DECLARE backlog BIGINT;
    SET backlog = (SELECT COUNT(*) FROM jobs WHERE status = 'PENDING');

    IF backlog = 0 THEN
      INSERT INTO ops_log VALUES ('queue drained', CURRENT_TIMESTAMP());
    ELSEIF backlog < 1000 THEN
      INSERT INTO ops_log VALUES ('queue nominal', CURRENT_TIMESTAMP());
    ELSE
      INSERT INTO ops_log VALUES ('queue backed up', CURRENT_TIMESTAMP());
    END IF;
  END
""")

Loops: WHILE, REPEAT, LOOP, and FOR

Four loop forms, each mapping to a familiar shape.

WHILE tests before each iteration; REPEAT ... UNTIL tests after (so it always runs at least once):

spark.sql("""
  BEGIN
    DECLARE n INT DEFAULT 1;
    REPEAT
      INSERT INTO seq VALUES (n);
      SET n = n + 1;
    UNTIL n > 5
    END REPEAT;
  END
""")

LOOP is an unconditional loop you exit explicitly with LEAVE, and skip forward with ITERATE. Both target a label on the block, which is what lets you break out of nested loops cleanly:

spark.sql("""
  BEGIN
    DECLARE i INT DEFAULT 0;
    outer_loop: LOOP
      SET i = i + 1;
      IF i > 100 THEN
        LEAVE outer_loop;
      END IF;
      IF i % 2 = 0 THEN
        ITERATE outer_loop;   -- skip even numbers
      END IF;
      INSERT INTO odds VALUES (i);
    END LOOP outer_loop;
  END
""")

The FOR loop is the one that feels most like Spark: it iterates over the rows of a query, exposing each row's columns as loop-local variables. This is genuinely useful for small driver-side control tables — a list of partitions to process, a set of tenants to iterate — without pulling the rows back to Scala with collect():

spark.sql("""
  BEGIN
    FOR row AS (SELECT tenant_id, region FROM active_tenants) DO
      INSERT INTO tenant_audit
      SELECT row.tenant_id, row.region, COUNT(*)
      FROM events
      WHERE tenant_id = row.tenant_id;
    END FOR;
  END
""")

A word of caution: FOR runs its body once per row serially. That's fine for a control table of dozens of rows, but it is not a substitute for a set-based join or a groupBy. If you can express the work as one distributed query, do that — SQL scripting is for orchestration, not for row-by-row processing of your actual data.

Error Handling: DECLARE … HANDLER

This is where 4.1 made the biggest step. A condition handler intercepts an exception and decides whether to EXIT the enclosing block or CONTINUE after the failing statement. CONTINUE HANDLER is new in 4.1 (SPARK-53621) — before it, your only option was to unwind.

spark.sql("""
  BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
      INSERT INTO error_log VALUES ('load failed', CURRENT_TIMESTAMP());
    END;

    INSERT INTO staging SELECT * FROM raw_feed;
    INSERT INTO fact SELECT * FROM staging;   -- if this throws, the handler runs, then the block exits
  END
""")

Handlers can match on a specific SQLSTATE, a named condition, or the catch-all categories SQLEXCEPTION and NOT FOUND. When more than one handler could apply, the most specific wins — a named condition beats a SQLSTATE match, which beats the generic SQLEXCEPTION. You can also declare your own conditions and raise them with SIGNAL / RESIGNAL, and inspect the current error with GET DIAGNOSTICS — the same vocabulary anyone migrating from a traditional stored-procedure environment will recognize.

The CONTINUE variant is what makes resilient batch loops possible: process every item, log the failures, keep going.

spark.sql("""
  BEGIN
    DECLARE failed INT DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
      SET failed = failed + 1;

    FOR t AS (SELECT table_name FROM tables_to_refresh) DO
      -- a failure here bumps the counter and moves to the next table
      INSERT INTO refresh_result
      SELECT t.table_name, COUNT(*) FROM identifier(t.table_name);
    END FOR;

    SELECT failed AS failed_tables;
  END
""")

When to Use This Instead of Scala

You already have a full programming language on the driver, so the honest question is when SQL scripting earns a place next to it. A few situations where it's the better tool:

Quick ad-hoc procedural logic in a SQL-first context. If you're already in a notebook cell or a .sql file and need "run this, check the count, then conditionally do that," a short script keeps the whole thing in one language instead of interleaving Scala and SQL strings.

Migrating stored-procedure-heavy systems. Teams moving off Teradata, Oracle PL/SQL, SQL Server T-SQL, or Snowflake scripting have thousands of lines of procedural SQL. SQL Scripting gives a target that preserves the shape of that code — DECLARE, WHILE, handlers — so the port is a translation rather than a rearchitecture. You can move it onto Spark first and refactor toward set-based DataFrames later.

Logic that belongs next to the data, driven by non-Scala users. Analysts and SQL-first engineers can own and edit a script without touching the Scala build. The Scala application just invokes it.

And where Scala still wins, decisively:

  • Anything that needs real software engineering — unit tests, dependency injection, reuse across jobs, typed domain models. Scala's tooling is in a different league.
  • Complex orchestration — retries with backoff, external service calls, branching workflows. That's application code, not a SQL script.
  • Performance-sensitive transformation — the set-based DataFrame/SQL engine is what you're paying for. Don't loop where you could join.

The rule of thumb: SQL Scripting is for orchestrating SQL steps, not for replacing the distributed query engine underneath them. A script that's mostly INSERT/MERGE steps with a little branching is a great fit; a script with a FOR loop doing arithmetic over millions of rows is a mistake.

A Note on Stored Procedures

Spark 4.1 also landed the Stored Procedures API for catalogs (SPARK-44167), which lets catalog implementations expose callable procedures. That's a related but distinct feature — SQL Scripting is the procedural language; stored procedures are a packaging and invocation mechanism a catalog can offer. If you're evaluating a migration off a stored-procedure platform, both are worth watching as the ecosystem fills in.

Should You Use It?

If you're on Spark 4.1, SQL Scripting is already there and on by default — nothing to enable. For a Scala team, the pragmatic stance is: keep your data transformations in DataFrames and typed Scala, and treat SQL Scripting as a clean way to express SQL-side orchestration and to land stored-procedure migrations without rewriting them first. The 4.1 additions — CONTINUE HANDLER, multi-variable DECLARE, and the NULL-condition fix — are exactly the pieces that make it usable for real batch logic rather than a demo.

If you're still on Spark 3.x, this is one more entry on the growing list of reasons to plan the 4.x upgrade. See our Spark 3 to 4 migration guide for the path, and SQL Pipe Syntax in Spark 4.0 for the other big readability upgrade on Spark's SQL surface.

For the full statement reference, see the Spark SQL Scripting documentation and the official Spark 4.1.0 release notes.

Article Details
More Spark Scala News and Updates

Created: 2026-07-20

Last Updated: 2026-07-20 10:29:07 PM