Write Rows

Add a new batch of frames to frames, correct the labels of some frames, merge a batch of upserts, or remove the frames you no longer want, while other people keep reading the Table. Each write uploads new files and creates a new Table version, and one commit publishes it, so readers see the old version or the new one, never a partial write.

Before you startCopy Anchor Link

• A Table frames in the Project demo. See Create a Table.
• A client that can write, from the Python, Rust or TypeScript SDK.
• Arrow data with the complete Table schema, in Table field order: a pyarrow Table, RecordBatch or RecordBatchReader in Python, RecordBatch values in Rust, or an Arrow JS table in TypeScript. An append supplies every column.

Append rowsCopy Anchor Link

Pin the branch, open the Table from that pinned head, append, and publish the new version in a commit on the same head:

main = client.project("demo").branch("main")
appended = main.table("frames").append(frames)
commit = main.commit()
commit.update_table(appended)
head = commit.publish()
print(f"appended version {appended.version_id} in commit {head.commit_id}")

An append writes new files and does not touch existing rows. The new rows keep their input order. append returns the new version without publishing it; update_table stages it, and publish makes it visible. The Table must come from the commit's own head; staging a Table from another head fails.

To append several batches as separate writes, append to the result of the previous append and stage only the last version. The commit publishes that version, and the versions in between are never published.

Replace all rowsCopy Anchor Link

Use overwrite to replace the complete contents of a Table. The Table keeps its name and Resource ID:

main = client.project("demo").branch("main")
replaced = main.table("frames").overwrite(frames)
commit = main.commit()
commit.update_table(replaced)
commit.publish()

Readers that pinned an earlier commit keep the earlier rows.

Delete rowsCopy Anchor Link

Remove the rows that match a filter, such as every frame from the left camera, without rewriting the rows you keep:

main = client.project("demo").branch("main")
deleted = main.table("frames").delete_rows(eq(column("camera"), literal("left")))
print(f"deleting {deleted.rows} rows")
commit = main.commit()
commit.update_table(deleted.table)
commit.publish()

The filter is an expression. Pass no filter to delete every row. delete_rows records which rows are gone instead of rewriting files, so the write stays small however large the Table is, and retained rows keep their row IDs. It returns the next version and the number of rows it deleted. When no row matches, it writes nothing and returns the Table you called it on, so staging it publishes no change.

The filter must be one that a scan can enforce exactly; a filter it cannot enforce fails before anything is written. A row for which the filter is null is not deleted.

Update chosen rowsCopy Anchor Link

Change the values of the rows a filter matches, such as relabeling every frame of the left camera, without writing the other columns again. patch_rows takes the filter and one expression for each column it sets:

main = client.project("demo").branch("main")
updated = main.table("frames").patch_rows(
    eq(column("camera"), literal("left")),
    {"camera": literal("left-wide")},
)
print(f"updated {updated.rows} rows")
commit = main.commit()
commit.update_table(updated.table)
commit.publish()

Each expression names an existing column and must evaluate to that column's stored type, because nothing is cast for you. An expression can read the row's other columns, so a new value can depend on the old one. Pass no filter to set the columns in every row. The method returns the next version and the number of rows it updated; when no row matches, it writes nothing and returns the Table you called it on. Updated rows keep their row IDs.

patch_rows writes a small patch beside the data, with the changed rows' new values for the assigned columns, so the write costs as much as the changed values. Reads combine the patch with the data until maintenance folds it in. What each change writes explains the files.

A null in a patch sets the column to null, so a patch can clear a value of a nullable column. A patch cannot change a column that the Table is partitioned or sorted by; delete those rows and append them with their new values instead.

Merge rows by keyCopy Anchor Link

Apply a batch of upserts, such as frames that a pipeline reprocessed, by matching them to the Table's rows on a key column. By default a merge updates the rows whose key the batch has and appends the batch's other rows:

main = client.project("demo").branch("main")
merged = main.table("frames").merge_rows(frames, on=["frame_id"])
print(f"inserted {merged.inserted}, updated {merged.updated}, deleted {merged.deleted}")
commit = main.commit()
commit.update_table(merged.table)
commit.publish()

Tables have no primary key, so you name the key columns in on. Every patch, deletion and appended row of a merge goes into one version, which one commit publishes. The result reports how many rows the merge inserted, updated and deleted. A matched row whose values are already the same is not written again.

Four options choose what happens to each kind of row:

In Rust these are the fields of MergeActions, whose Default is the upsert. This merge only corrects the camera of frames that the Table already has, and ignores source rows for other frames:

main = client.project("demo").branch("main")
merged = main.table("frames").merge_rows(
    corrections,
    on=["frame_id"],
    columns=["camera"],
    not_matched="ignore",
)
commit = main.commit()
commit.update_table(merged.table)
commit.publish()

An inserted row must carry every column of the Table. An update needs only the keys and the changed columns, each with its stored type. Two source rows with the same key are an error, and a null key matches nothing. One source row may match several Table rows, and then it updates all of them.

The SDK holds the source rows in memory while it reads the Table's keys, so a merge suits batches of upserts. For a source too large to hold, use MERGE INTO through the DataFusion integration, which joins in DataFusion and writes the same kind of version.

Delete rows by row IDCopy Anchor Link

Some rows are chosen by your application rather than by a filter: a reviewer rejects frames in a labeling tool, or a model flags them. Read their row IDs with a scan, as Read changes shows, and delete them through a writer:

main = client.project("demo").branch("main")
schema = pa.schema([pa.field(ROW_ID_COLUMN, pa.uint64(), nullable=False)])
ids = pa.table({ROW_ID_COLUMN: pa.array(rejected, pa.uint64())}, schema=schema)

write = main.table("frames").writer()
write.delete_rows(ids)
deleted = write.finish()

commit = main.commit()
commit.update_table(deleted)
commit.publish()

The input to delete_rows on a writer has one column, the ROW_ID_COLUMN (s:row_id), of non-null uint64 row IDs. Every row ID must be visible in the version the writer started from. A row ID is stable across maintenance, so IDs read from that version stay correct even after other versions rewrite its files.

Combine several changes in one versionCopy Anchor Link

writer() collects several kinds of change and writes them as one version: it patches and deletes existing rows, named by row ID, and appends new rows. Use it when a change has several parts that belong together, such as a review that relabels some frames, rejects others and adds the frames it recaptured:

main = client.project("demo").branch("main")
write = main.table("frames").writer()
write.patch_rows(relabeled)
write.delete_rows(rejected)
write.append(new_frames)
edited = write.finish()

commit = main.commit()
commit.update_table(edited)
commit.publish()

The rows for patch_rows hold a ROW_ID_COLUMN column that names each target row and some of the Table's columns, with their stored types, whose values they replace. delete_rows takes only the row ID column, and append takes rows with every column.

finish checks the parts against each other before it writes the version. It refuses a row that is both changed and deleted and a cell changed twice. A row ID appears at most once in each part.

Upload large blobs with their rowsCopy Anchor Link

A drive's video can be many gigabytes, far more than one Arrow batch or one request carries. Upload it first, as a blob of the same write, then append the row that references it. This sample adds a recording to a Table drives whose rows hold a drive_id and a video:

main = client.project("demo").branch("main")
table = main.table("drives")
write = table.writer()
video = write.upload_blob("video/mp4", recording)
print(f"uploaded {video.size} bytes, BLAKE3 {video.digest}")

row = pa.Table.from_arrays(
    [pa.array(["drive-0042"]), blob_array([video])],
    schema=table.scan().schema,
)
write.append(row)
appended = write.finish()

commit = main.commit()
commit.update_table(appended)
commit.publish()

writer starts a write of the Table's next version, and upload_blob sends the recording into it in parts of 64 MiB, several at once. Each part is retried on its own, so a lost request costs one part, not the whole upload. The returned blob names where the payload is stored in the Table and carries its size and BLAKE3 digest, which Spiral computes from the parts as they arrive without reading the payload again. blob_array in Python and Blob::array in Rust make the video column from it. finish registers the version and returns it, as append does, and update_table stages it. To upload blobs into a new Table, start the write with project.table_writer(schema) instead, and stage the finished Table with commit.create_table(name, table).

Nothing is visible until the commit publishes. If an upload fails, the SDK aborts it, so its parts are not kept. A blob that no committed row references is never part of any version, and readers cannot find it. One blob holds at most 10,000 parts, 625 GiB at the default part size.

Pack many small blobsCopy Anchor Link

Camera frames and thumbnails are small, and one object per payload costs a request per payload to read. A blob pack stores many payloads together, as a write packs the inline bytes of the rows it appends. This sample adds images to a Table frames whose rows hold a frame_id and an image:

main = client.project("demo").branch("main")
write = main.table("frames").writer()
blobs = write.pack_blobs([(image, "image/jpeg") for image in images])

schema = pa.schema(
    [
        pa.field("frame_id", pa.uint64(), nullable=False),
        pa.field("image", blobs.type, nullable=False, metadata={"ARROW:extension:name": "spql.Blob"}),
    ]
)
frame_ids = pa.array(range(len(images)), pa.uint64())
write.append(pa.Table.from_arrays([frame_ids, blobs], schema=schema))
next_version = write.finish()

commit = main.commit()
commit.update_table(next_version)
commit.publish()

blob_pack starts a pack in the write, and push adds each payload with its media type. finish stores them and returns their blobs in push order, as an image column of Blob::field's type. Payloads share packs of up to 64 MiB; one of 4 MiB or more is an object of its own, and one over 32 MiB is uploaded on its own in parts. Equal payloads share one stored range. The pack holds its payloads in memory until finish, so push at most a few hundred megabytes before finishing it and starting another. As with an upload, nothing is visible until a commit publishes rows that carry the blobs.

Verify the resultCopy Anchor Link

Read the Table from the branch's new head. The Table reports the version you published. See Read rows.

Handle a moved branchCopy Anchor Link

If another writer moved the branch after you pinned it, nothing is published: Python raises BranchMoved, and Rust's publish returns PublishError::BranchMoved, which the ? operator converts into Error::BranchMoved. Pin the branch again and repeat the write, including the upload, from the new head. The SDK does not rebase a write onto the new head for you.

Recover an unknown outcomeCopy Anchor Link

A moved branch is a definite answer. A lost response is not: a network failure during publish can hide whether the publication happened. The SDK then reports the commit frozen, so it can only send the same request: Python raises PublishOutcomeUnknown with the frozen commit in its commit attribute, and Rust returns PublishError::Unknown. In Rust, match it before ? converts it into Error::OutcomeUnknown, which keeps only the operation ID. Do not upload again and do not build a new commit. Recover the frozen one:

appended = main.table("frames").append(frames)
commit = main.commit()
commit.update_table(appended)
try:
    return commit.publish()
except PublishOutcomeUnknown as unknown:
    return recover(unknown.commit)

def recover(commit: FrozenCommit) -> BranchHead:
    """Publish a frozen commit unless a lost acknowledgement already published it."""
    head = commit.recover()
    return head if head is not None else commit.publish()

recover returns the new branch head if the commit was published, or None when Spiral has no record of the publication. After that, publishing again is safe because the frozen commit sends the same request, which Spiral publishes at most once.

To recover after a process restart, freeze the commit and save the value of its save() before you publish. After the restart, pass the saved value to project.resume_commit. See Survive a process restart.

A failure can also hide whether the new Table version was registered, after the files were uploaded. The Rust SDK then returns Error::RegistrationUnknown with a receipt. Your input stream is already consumed, so pass the receipt to recover_write on the Table you wrote from, or to Client::register_prepared for a new Table, instead of uploading again. Both return the version to stage. The Python SDK cannot recover such a write yet; write it again from the pinned head.

Retries describes the same rules for HTTP clients.

LimitsCopy Anchor Link

• A commit stages one version of each Table: chain writes to one Table and stage the last, or put several changes into one writer.
• A merge holds its source rows in memory. Merge larger sources with MERGE INTO in DataFusion.
• A patch cannot change a column the Table is partitioned or sorted by. Delete those rows and append them with their new values instead.
• A writer, and every part it holds, belongs to the process that started it. Many workers cannot write parts of one version; have each write its own version from the last one, in turn.