Spark compatibility

Spark SQL and PySpark run on Sail. This page lists where Sail matches Apache Spark, where it differs, and what isn't supported yet.

We check Sail against Apache Spark 4.0 (with Delta Lake and Apache Iceberg) by running thousands of the same statements and DataFrame calls on both and comparing the results. Newer functions are checked against Spark 4.2. Many of the differences those checks found are now fixed. The ones you're most likely to meet are listed below, along with the features Sail doesn't have yet.

What behaves like Spark

Writing tables

• Dynamic partition overwrite. With spark.sql.sources.partitionOverwriteMode=dynamic, INSERT OVERWRITE replaces only the partitions you write, on Iceberg and Delta tables.
• INSERT column lists and static partition values (PARTITION (region = 'west')).
• Row counts for INSERT, MERGE, UPDATE and DELETE come back in Spark's shape.
• Default table format. CREATE TABLE without USING takes the catalog's format: Iceberg in an Iceberg catalog, Delta in a Delta one. A different explicit format is refused.
• CREATE OR REPLACE TABLE with or without AS SELECT, CREATE TABLE LIKE, COMMENT ON TABLE.
• DROP TABLE … PURGE and DROP SCHEMA … CASCADE.
• Iceberg MERGE WITH SCHEMA EVOLUTION.

Numbers, dates and time zones

• ANSI arithmetic. Integer overflow raises an error as Spark's ANSI mode does, instead of wrapping silently.
• Rounding and decimals. round rounds half up; decimal precision, avg rounding, type widening and double-to-string conversion match Spark.
• Dates. Day-of-week functions, to_timestamp on a number, and RANGE window frames over dates.
• Time zones. SET TIME ZONE and spark.sql.session.timeZone work as in Spark on Iceberg and Delta tables.

Strings, regex and functions

• Java regular expressions, with Spark's semantics for rlike, regexp_extract, regexp_replace and the rest.
• split, substring, initcap and the binary string functions.
• Collations UTF8_BINARY and UTF8_LCASE in comparisons, IN, LIKE, joins, ORDER BY and windows (not yet in GROUP BY or set operations on UTF8_LCASE).
• approx_count_distinct gives the same estimates as Spark, and percentile_approx accepts arrays.
• session_window, input_file_name() and the _metadata column, and some functions added in Spark 4.1 and 4.2 (see the Sail SQL reference).

Reading files

• CSV reads as Spark's reader does, including PERMISSIVE, DROPMALFORMED and FAILFAST modes, _corrupt_record, and encoding / charset.
• JSON parse modes, multiLine, inferTimestamp and prefersDecimal.
• Reader options Sail doesn't support fail with a clear error instead of being ignored.
• Folder listing follows Spark's rules: basePath, globs, recursiveFileLookup, and __HIVE_DEFAULT_PARTITION__ read as NULL. Folders with conflicting layouts fail instead of dropping rows.
• Parquet INT96 timestamps.

SQL statements

• PIVOT, GROUP BY ALL, ORDER BY ALL and lateral column aliases.
• DISTRIBUTE BY, CLUSTER BY, SORT BY, TABLESAMPLE with ROWS, LIKE ANY / LIKE ALL.
• SET, RESET and ${var} substitution; session variables and EXECUTE IMMEDIATE; temporary SQL functions.
• DESCRIBE in its forms, including DESCRIBE QUERY and single columns.
• Persistent views in lake catalogs: CREATE VIEW, CREATE OR REPLACE VIEW, ALTER VIEW … AS and DROP VIEW. Iceberg views written in Trino SQL are translated when you read them, if they mean the same thing in Spark SQL; otherwise Sail tells you why it can't run them.
• CACHE TABLE, UNCACHE TABLE and REFRESH answer as Spark does.

DataFrames

• describe(), summary(), transpose(), groupingSets(), approxQuantile(), crosstab().
• cache() and persist() report storage levels as Spark does.
• Result order. Results come back in partition order, as Spark returns them: UNION ALL branch by branch, and show(), take(), first() and LIMIT from the first partition on.

Differences from Spark

These are deliberate, or known and not yet changed:

Some Spark features are turned off on purpose, for security on a shared lakehouse: reading or writing storage by path (except your project's /Files), LOCATION on tables, engine-managed table properties and settings, Python UDFs on shared clusters, writes to tables with hidden columns, and the socket and console sources. How Sail works explains each one.

Not supported yet

• Readers and formats: ORC and XML files, the Avro functions (from_avro, to_avro).
• SQL: pipe syntax, ALTER DATABASE and COMMENT ON DATABASE, ICU collations, st_srid.
• Tables: Delta MERGE into identity columns, Iceberg REPLACE WHERE beyond whole partitions.
• Access control: row filters and column masks. A table protected by one is refused.
• DataFrame API: _joinAsOf and observe().
• Outside Spark SQL: Spark ML (MLlib), Structured Streaming, the RDD API, Scala, addArtifacts. See Python and PySpark on Sail.

We haven't yet compared these with Spark: Delta deletion vectors, change data feed, CHECK constraints and generated columns. If you rely on them, test on Sail first.

Each cluster keeps the Sail build it was created with, so a cluster created a while ago may not have every fix on this page yet. Before you rely on one, such as dynamic partition overwrite, try it on a small table first. If a result on your cluster doesn't match Spark and isn't listed here, tell us through support.

Where to go next