Spark SQL Formatter

Quoting rules that Spark shares with Hive

Spark SQL quotes names with backticks: web.sessions, or a path table like parquet.s3://acme-lake/orders/. Everything in backticks is one identifier, slashes and all.

String literals work differently from ANSI SQL:

• Single and double quotes both create strings. "checkout" is text, not a column name. Code ported from PostgreSQL or Trino that double-quotes identifiers needs those quotes changed to backticks.
• Backslash is the escape character, so an apostrophe is written 'it\'s'. A doubled '' is not an escape: 'it''s' is two adjacent literals, and the formatted output shows them apart as 'it' 's'.
• Raw strings (r'^(\w+)-\d+$') keep backslashes literally, which is the easiest way to write regexes for regexp_extract and rlike. Hex literals like X'1F' are recognised too.

Comments are -- and /* */.

Hive-style clauses and Delta Lake DDL

The dialect knows the clauses that Hive-era Spark code is full of. LATERAL VIEW EXPLODE(...) gets its own line between FROM and WHERE. CLUSTER BY, DISTRIBUTE BY and SORT BY are laid out as clauses, the same way as GROUP BY. INSERT OVERWRITE TABLE ... PARTITION (dt = '2026-03-01') keeps the partition spec next to the table name, and the SELECT feeding it starts a new block.

For CREATE TABLE ... USING delta PARTITIONED BY (...) TBLPROPERTIES (...), the column list is indented one column per line and the table options follow the closing bracket. Delta Lake time travel (VERSION AS OF 12, TIMESTAMP AS OF '2026-03-01'), CACHE TABLE and OPTIMIZE ... ZORDER BY all format without errors.

Lambdas in higher-order functions (aggregate(items, 0, (acc, x) -> acc + x.qty)) are left on one line with the arrow intact.

Known gaps in Spark support

A few patterns from notebooks and jobs do not format cleanly, so check for them:

• Parameter markers. Both ? and named :region markers are rejected as invalid Spark SQL syntax. Put a literal in while formatting.
• Variable substitution glued to a name. ${db}.orders and '${run_date}' are fine, but ${env}_orders becomes ${env} _orders, which changes the table name. Wrap the whole name in backticks or substitute the variable first.
• TRANSFORM and FILTER as functions. These collide with the Hive TRANSFORM clause and the FILTER keyword, so transform(items, x -> ...) is pushed to the left margin like a clause. The SQL is unchanged, just oddly indented. Other higher-order functions, such as aggregate, keep normal indentation.
• Multi-column LATERAL VIEW aliases (t AS pos, item) are broken after the comma.
• Delta MERGE with UPDATE SET * stays mostly on one long line.
• Databricks colon paths for JSON strings (payload:customer.id) are not part of this grammar; use get_json_object or from_json.

Useful settings for notebook SQL

Notebook cells are read in narrow panes, so a smaller line width plus Indent style: Standard usually reads best. Keyword case UPPER is the common Databricks convention; table options like delta in USING delta keep their original case.

When you embed SQL in PySpark (spark.sql("...")), minify it first or keep it formatted in a triple-quoted Python string. Minify removes comments and whitespace but never edits text inside quotes or backticks. Read the SQL style guide for naming and layout conventions that suit lakehouse code.

Examples

select s.session_id, s.user_id, e.event_name, e.event_ts from `web`.`sessions` s lateral view explode(s.events) x as e where s.dt = '2026-03-01' and e.event_name in ("add_to_cart", "checkout") -- cart funnel

SELECT
  s.session_id,
  s.user_id,
  e.event_name,
  e.event_ts
FROM
  `web`.`sessions` s
LATERAL VIEW EXPLODE (s.events) x AS e
WHERE
  s.dt = '2026-03-01'
  AND e.event_name IN ("add_to_cart", "checkout") -- cart funnel

create table if not exists sales.orders (order_id bigint, customer_id bigint, amount decimal(12,2), order_date date) using delta partitioned by (order_date) tblproperties ('delta.autoOptimize.optimizeWrite' = 'true');
insert overwrite table sales.daily_revenue partition (dt = '2026-03-01') select customer_id, sum(amount) as revenue from sales.orders where order_date = '2026-03-01' group by customer_id

CREATE TABLE IF NOT EXISTS sales.orders (
  order_id BIGINT,
  customer_id BIGINT,
  amount DECIMAL(12, 2),
  order_date DATE
) USING delta PARTITIONED BY (order_date) TBLPROPERTIES ('delta.autoOptimize.optimizeWrite' = 'true');

INSERT OVERWRITE TABLE
  sales.daily_revenue PARTITION (dt = '2026-03-01')
SELECT
  customer_id,
  sum(amount) AS revenue
FROM
  sales.orders
WHERE
  order_date = '2026-03-01'
GROUP BY
  customer_id

with s as (select session_id, min(event_ts) as started, max(event_ts) as ended, count(*) as events from web.events where dt = '2026-03-01' group by session_id) select session_id, unix_timestamp(ended) - unix_timestamp(started) as duration_s, events, dense_rank() over (order by events desc) as rnk from s distribute by session_id sort by rnk

WITH
  s AS (
    SELECT
      session_id
    , min(event_ts) AS started
    , max(event_ts) AS ended
    , count(*) AS events
    FROM
      web.events
    WHERE
      dt = '2026-03-01'
    GROUP BY
      session_id
  )
SELECT
  session_id
, unix_timestamp(ended) - unix_timestamp(started) AS duration_s
, events
, dense_rank() OVER (
    ORDER BY
      events DESC
  ) AS rnk
FROM
  s
DISTRIBUTE BY
  session_id
SORT BY
  rnk

select order_id, from_json(payload, 'struct<sku:string,qty:int>') as item, regexp_extract(order_ref, r'^(\w+)-\d+$', 1) as channel from parquet.`s3://acme-lake/raw/orders/dt=2026-03-01/` where get_json_object(payload, '$.status') = "paid"

SELECT
  order_id,
  from_json(payload, 'struct<sku:string,qty:int>') AS item,
  regexp_extract(order_ref, r'^(\w+)-\d+$', 1) AS channel
FROM
  parquet.`s3://acme-lake/raw/orders/dt=2026-03-01/`
WHERE
  get_json_object(payload, '$.status') = "paid"

Common errors and how to fix them

Related tools

• SQL formatter
• sql formatter/trino
• sql formatter/bigquery
• sql formatter/mysql
• sql validator
• sql minifier
• sql style guide
• sql unterminated quoted string