Sail examples

Appearance

How Sail behaves

Why did a model run twice? Where do engine settings go? Will the same SQL return the same result on Spark? These 38 notes help you make those decisions before they become surprises. Open a note to read its Python check and run it yourself.

Check targets: pysail==0.7.3 with pyspark-client==4.1.3

Each note comes from facts/ and has an executable check. Details attributed to external documentation are not covered by that check. These notes describe the pinned versions; newer releases may behave differently.

A Python UDF is a user-defined function that runs inside a query. The Python UDF section covers how Sail runs these functions and what that means for your code.

Run and connect

Start Sail and connect a PySpark session

Connect to Sail with a normal PySpark Spark Connect session. In a script, start Sail in-process with pysail.spark.SparkConnectServer(), call start(), read the port from server.listening_address, and connect with SparkSession.builder.remote(f"sc://localhost:{port}"). As a long-running service, run sail spark server (default 127.0.0.1:50051, per the Sail docs). SELECT version() returns the Sail version; spark.version returns the PySpark client's version.

Related examples (2)

Collected timestamps use the client’s time zone

collect() returns TIMESTAMP values as Python datetimes in the client process's local time zone, whatever spark.sql.session.timeZone is; TIMESTAMP_NTZ values come back unchanged. Store wall-clock values as TIMESTAMP_NTZ (for example to_timestamp_ntz), or compare instants, so tests do not depend on the machine they run on.

Related examples (2)

Configure

Python UDFs

Python UDFs share an interpreter in local mode

In local mode (the default), Python UDFs run inside the Sail server process, in one Python interpreter shared by all tasks, while Apache Spark runs them in separate Python worker processes. Prefer built-in functions, and for CPU-heavy Python work prefer libraries that release the GIL (NumPy, PyArrow compute, ONNX Runtime, tokenizers) so tasks can overlap.

Related examples (1)

Protect libraries that cannot run in concurrent threads

In local mode, Sail runs the partitions of a Python UDF at the same time as threads of the server process (Apache Spark uses separate Python worker processes). A library that must not be called from two threads at once (PDFium through pypdfium2, per its docs) therefore needs one lock for the whole process. Keep that lock in sys.modules, which every task and every later query in the process share. A global of your script will not do: its functions are pickled by value, so each task gets its own copy of the globals.

Related examples (3)

Load a model once per partition

To load a model or client once per partition, put the setup before the loop in mapInArrow, mapInPandas or an iterator pandas_udf. In an iterator arrow_udf, code before the loop runs once per batch instead.

Related examples (6)

Match Arrow output types to the declared schema

Build mapInArrow output batches with an Arrow schema that matches the declared schema exactly (int32 for int, int64 for long), for example pa.RecordBatch.from_pydict(rows, schema=...). Sail accepts an int64 column declared as int, but Apache Spark fails on it, so code that relies on the leniency is not portable.

Related examples (6)

Create Python UDFs after starting the session

Create UDFs after the Spark session exists. With PySpark 4 clients, @udf, @pandas_udf and @arrow_udf applied at import time, before any session, fail with NO_ACTIVE_OR_DEFAULT_SESSION (or SESSION_OR_CONTEXT_NOT_EXISTS). Keep plain functions at module level and wrap them, for example F.udf(fn, "double"), once the session is up.

Related examples (2)

Data sources

Read and update Delta Lake tables directly

Sail reads and writes Delta Lake with no extra package: format("delta") writes (partitionBy, append, replaceWhere), CREATE TABLE ... USING delta, UPDATE, DELETE, MERGE INTO, time travel (versionAsOf, VERSION AS OF), ALTER TABLE ... ADD COLUMNS, DESCRIBE HISTORY, OPTIMIZE and VACUUM. A mergeSchema append of computed (nullable) values also adds a column to a table created with CREATE TABLE t (...) USING delta. RESTORE is not supported; read the old version with versionAsOf instead. Apache Spark needs delta-spark with its session extension and catalog (per the Delta Lake docs).

Related examples (4)

Process media through a table of file paths

To process loose files (images, PDFs, audio, documents), build a DataFrame of file paths and read each file inside mapInArrow or a pandas UDF, returning only what later stages need (text, features, embeddings). This gives exactly one row per file and keeps large binaries out of the data passed between stages. Paths must be readable from where the UDF runs (local files on one machine, object storage on a cluster).

Related examples (10)

Read and update Iceberg tables directly

Sail reads and writes Apache Iceberg tables with no extra package: format("iceberg") writes to a path (including partitionBy, append and overwrite), CREATE TABLE ... USING iceberg LOCATION, UPDATE, DELETE, MERGE INTO, and time travel to a snapshot with option("snapshot-id", id) or VERSION AS OF id. The metadata tables t.snapshots and t.history list the snapshot ids. In a table created with PARTITIONED BY, Sail lists the partition columns last; select columns by name.

Related examples (2)

Declare schemas when types or column order matter

Inferred schemas differ from Apache Spark's in three ways: CSV inferSchema reads whole numbers as BIGINT (Spark: INT); JSON keeps columns in file order (Spark sorts them by name); and partition columns of a partitioned directory come back as strings (Spark infers numbers and dates). Pass an explicit schema (spark.read.schema(...)) when types or column order matter.

Related examples (3)

Query files without loading a database

Query files in place with FROM parquet.`path`, csv.`path` or json.`path`, where the path is a file, a folder, or a glob such as data/2024-*.parquet that reads every matching file as one table. A CSV file queried this way is read without a header: its first line is a data row and the columns are _c0, _c1, and so on. For a header and declared types, create a view: CREATE TEMPORARY VIEW v (a INT, b STRING) USING csv OPTIONS (path '...', header 'true').

Related examples (2)

What to use instead

Rewrite unsupported Spark SQL clauses

A few Spark SQL clauses need another form on Sail: filter on row_number() in a subquery instead of QUALIFY; use df.groupBy(...).pivot(col, values) instead of the SQL PIVOT clause; number sessions with lag and a running sum instead of session_window. CACHE TABLE and UNCACHE TABLE are not supported; save the result to Parquet and read it back instead.

Related examples (1)

Behaves differently from Spark

Take care when overwriting Parquet partitions

Sail does not support dynamic partition overwrite for Parquet: with partitionOverwriteMode set to dynamic (in the session or as a writer option), df.write.mode("overwrite").partitionBy(c) still replaces every partition of the path, and INSERT OVERWRITE replaces the whole table, where Apache Spark replaces only the partitions the data contains. Delta Lake tables follow the setting on both engines. INSERT OVERWRITE t PARTITION (c = value) is not supported. To replace one Parquet partition, overwrite its own directory (path/c=value, without the partition column), or write Delta Lake.

Related examples (2)

Use dayofweek for consistent weekday numbers

Use dayofweek(d) (Sunday 1 to Saturday 7) for the day of the week: it agrees on Sail and Apache Spark. The other forms differ on Sail, for dates and timestamps alike: EXTRACT(DOW FROM d) and date_part('dow', d) count from Sunday = 0 (Spark: 1), weekday(d) returns -1 for Sunday (Spark: 6; Monday to Saturday agree, 0 to 5), and EXTRACT(DAYOFWEEK FROM d) and EXTRACT(DOW_ISO FROM d) are not supported.

Related examples (2)

Make regular-expression character classes explicit

In regular expressions (regexp_replace, regexp_extract, rlike, split), Sail's \s, \d and \w match Unicode characters (an ideographic space, an Arabic-Indic digit, é), while Apache Spark's match ASCII only, so the same pattern can return different results on non-ASCII text. For the same results on both, write the class you mean: [ \t\n\r], [0-9] and [A-Za-z0-9_] for ASCII, or [\p{Z}\t\n\r], \p{Nd} and [\p{L}\p{Nd}_] for Unicode. The (?U) flag is not a portable switch: Spark reads it as Unicode classes, Sail as lazy quantifiers.

Related examples (1)

Approximate results and random samples can differ

Approximate and random results can differ from Apache Spark's: percentile_approx, approx_count_distinct, the percentiles from df.summary(), and the rows chosen by sample(fraction, seed) and randomSplit(weights, seed). With the same seed, sample and randomSplit pick the same rows again on Sail, but not the rows Spark picks. When results must match, use percentile, median, count(DISTINCT ...), and a deterministic filter on a hash of a key (hash and xxhash64 return the same values on both).

Related examples (1)

Choose an explicit type when truncating timestamps

date_trunc(unit, ts) on a TIMESTAMP_NTZ column (the type Parquet timestamps without a time zone are read as) returns TIMESTAMP_NTZ on Sail but TIMESTAMP on Apache Spark. So the column type in files written from it differs, and collected values can differ when the client's local time zone is not the session time zone. Cast the result (CAST(date_trunc('MONTH', ts) AS TIMESTAMP_NTZ)) or truncate a date (trunc(date(ts), 'MM')), which give the same type and value on both.

Related examples (2)

Decimal division can change precision and rounding

Dividing DECIMAL values gives a different result type on Sail than on Apache Spark (sometimes fewer, sometimes more decimal places), and Sail truncates where Spark rounds. 7 / 60.0 is 0.1166 on Sail and 0.116667 on Spark; CAST(2 AS DECIMAL(10,0)) / CAST(3 AS DECIMAL(10,0)) is 0.6666 and 0.66666666667. A literal with a decimal point, such as 60.0, is a DECIMAL. For the same results on both, divide DOUBLE values (x / 60.0D, CAST(x AS DOUBLE) / y) or integers (x / 60, which returns a DOUBLE on both), and round explicitly when you need a fixed number of digits.

Related examples (1)

Caching does not save the result

cache() and persist() are accepted but do not keep results: every action recomputes the DataFrame, including any UDF or model call in it. To reuse an expensive result (for example model outputs), write it to Parquet or a table and read it back.

Related examples (8)

AI and vector functions

Store embeddings as arrays of floats

For embeddings, store vectors as ARRAY<FLOAT> and use vector_cosine_similarity, vector_inner_product and vector_l2_distance (Spark 4.2 has them too). Both engines reject ARRAY<DOUBLE>; Sail also rejects Arrow fixed-size lists (as written by NumPy or PyArrow), which Spark accepts. CAST(col AS ARRAY<FLOAT>) first.

Related examples (6)

Call Jev models from SQL

Sail adds SQL functions that call TypeSafe's Jev models: jev_noul, jev_choice, jev_score, jev_system_one and jev_models (Apache Spark has none of them). They need a non-empty TYPESAFE_API_KEY in the Sail server's environment, and on a cluster in every worker that runs Jev calls (per the Sail Jev docs); with an empty key a call fails with an error that names the variable. The calls send row values to the TypeSafe API (per the Sail Jev docs).

Related examples (1)

Deduplicate inputs before calling Jev

A Jev function asks one question per row it evaluates. Adjacent rows with the same text share one request, but each row is still its own question, and the same text in rows that are not adjacent is sent again. Apply Jev to SELECT DISTINCT inputs, save the answers, and join them back to ask once per distinct value. The endpoint comes from TYPESAFE_BASE_URL (per the Sail Jev docs).

Related examples (1)

Try a check yourself

After following the test setup, run a note’s check from the repository root (sail-examples/). For example: 

uv run pytest tests/test_facts.py -k cache-does-not-keep-results

The checks workflow tests the pinned versions and runs a weekly check against the latest stable Sail package. A failed check is a reason to investigate the behavior and update the note when appropriate.

Using a coding assistant? The same notes supply agent-context.md and llms.txt.