Sail
0.7.2

Appearance

Examples ​

INFO

In the code below, spark refers to a Spark client session connected to the Sail server. You can refer to the Getting Started guide for how it works.

Basic Usage ​

The Python example writes a DataFrame to a Delta table, appends the same rows, and reads the result. The SQL example creates a table at a path before inserting and querying rows.

python
path = "file:///tmp/sail/users"
df = spark.createDataFrame(
    [(1, "Alice"), (2, "Bob")],
    schema="id INT, name STRING",
)

# This creates a new table or overwrites an existing one.
df.write.format("delta").mode("overwrite").save(path)
# This appends data to an existing table.
df.write.format("delta").mode("append").save(path)

df = spark.read.format("delta").load(path)
df.show()
sql
CREATE TABLE users (id INT, name STRING)
USING delta
LOCATION 'file:///tmp/sail/users';

INSERT INTO users VALUES (1, 'Alice'), (2, 'Bob');

SELECT * FROM users;

Data Partitioning ​

Partitioning a Delta table places rows in directories according to column values. When a query filters on those columns, Sail can skip directories that cannot contain matching rows. Here, both APIs partition metrics by year and then read only rows from 2025.

python
path = "file:///tmp/sail/metrics"
df = spark.createDataFrame(
    [(2024, 1.0), (2025, 2.0)],
    schema="year INT, value FLOAT",
)

df.write.format("delta").mode("overwrite").partitionBy("year").save(path)

df = spark.read.format("delta").load(path).filter("year > 2024")
df.show()
sql
CREATE TABLE metrics (year INT, value FLOAT)
USING delta
LOCATION 'file:///tmp/sail/metrics'
PARTITIONED BY (year);

INSERT INTO metrics VALUES (2024, 1.0), (2025, 2.0);

SELECT * FROM metrics WHERE year > 2024;

Schema Evolution ​

Delta Lake checks incoming data against the table schema. To add fields during an append or overwrite, set mergeSchema to true. For the users table from the basic Python example, the following append adds an age column:

python
path = "file:///tmp/sail/users"
df = spark.createDataFrame([(3, "Carol", 30)], "id INT, name STRING, age INT")
df.write.format("delta").mode("append").option("mergeSchema", "true").save(path)

When replacing all the data, overwriteSchema can also replace the table schema:

python
df = spark.createDataFrame([(1, "Alice")], "id BIGINT, name STRING")
df.write.format("delta").mode("overwrite").option("overwriteSchema", "true").save(path)

To apply supported type widening to an existing column, enable delta.enableTypeWidening first. For example, the SQL users table can widen id from INT to BIGINT:

sql
ALTER TABLE users SET TBLPROPERTIES ('delta.enableTypeWidening' = 'true');
ALTER TABLE users ALTER COLUMN id TYPE BIGINT;

DML Operations ​

DELETE, UPDATE, and MERGE INTO change rows in an existing Delta table. The following statements use the users table from the SQL example:

sql
UPDATE users SET name = 'Robert' WHERE id = 2;

DELETE FROM users WHERE id = 1;

MERGE INTO users AS target
USING (SELECT * FROM VALUES (2, 'Bob'), (3, 'Carol') AS s(id, name)) AS source
ON target.id = source.id
WHEN MATCHED THEN UPDATE SET name = source.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (source.id, source.name);

To use deletion vectors for later changes, enable them on the table:

sql
ALTER TABLE users SET TBLPROPERTIES ('delta.enableDeletionVectors' = 'true');
UPDATE users SET name = 'Caroline' WHERE id = 3;

With deletion vectors enabled, an update can append replacement rows and mark the old rows as deleted without copying unaffected rows from the same data file. See DML operations for the supported modes.

Defaults and Identity Columns ​

A Delta table can fill in omitted values through column defaults or generated identity columns. This table assigns an identity value to id and uses new as the default status:

sql
CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY,
  status STRING DEFAULT 'new',
  quantity INT
)
USING delta
LOCATION 'file:///tmp/sail/orders'
TBLPROPERTIES ('delta.feature.allowColumnDefaults' = 'supported');

INSERT INTO orders (quantity) VALUES (10), (20);
INSERT INTO orders (status, quantity) VALUES (DEFAULT, 30);

SELECT * FROM orders ORDER BY id;

An identity column declared GENERATED ALWAYS rejects explicit values. Use GENERATED BY DEFAULT AS IDENTITY if inserts also need to supply an id.

Time Travel ​

Time travel reads an earlier version of a Delta table by version number or timestamp. A timestamp selects the latest version committed at or before that time.

python
df = spark.read.format("delta").option("versionAsOf", "0").load(path)
df = spark.read.format("delta").option("timestampAsOf", "2025-01-02T03:04:05.678").load(path)

In SQL, use VERSION AS OF or TIMESTAMP AS OF:

sql
SELECT * FROM users VERSION AS OF 0;
SELECT * FROM users TIMESTAMP AS OF '2025-01-02T03:04:05.678';

Choose a version or timestamp from the retained table history.

Column Mapping ​

Column mapping lets a Delta table track columns by name or ID independently of their Parquet field names. Choose name or id when creating the table. These examples use separate locations:

python
df.write.format("delta").option("delta.columnMapping.mode", "name").save(
    "file:///tmp/sail/mapped_by_name"
)
df.write.format("delta").option("delta.columnMapping.mode", "id").save(
    "file:///tmp/sail/mapped_by_id"
)

Read an existing table with column mapping in the same way as any other Delta table.

Catalog-Managed Tables ​

Read a table with the Delta catalogManaged feature by name through a configured Unity Catalog:

python
df = spark.table("my_catalog.my_schema.my_table")

Replace the identifier with the catalog, schema, and table name. Sail uses commits ratified by the catalog to reconstruct the table snapshot. See catalog integration for supported operations.