Add Columns

Give every frame in frames a value it did not have when it was written: a flag computed from its other columns, or a sharpness score from your own model. The new column joins the Table's schema in its next version, and its values go into new files beside each fragment's existing ones, so the images and the other columns are not rewritten. Readers see the column like any other, and jobs that pinned an earlier version keep reading the Table without it.

Before you startCopy Anchor Link

• A Table frames in the Project demo. See Create a Table.
• A client that can write, from the Python SDK or the Rust SDK.

Compute a column from an expressionCopy Anchor Link

When the value follows from the row's own columns, let Spiral compute it. add_columns takes one expression for each new column, here a flag for frames from the front camera:

main = client.project("demo").branch("main")
added = main.table("frames").add_columns(
    {"is_front": eq(column("camera"), literal("front"))},
)
commit = main.commit()
commit.update_table(added)
commit.publish()

Each new column is nullable and has the expression's type. The SDK reads each fragment with the expressions as its projection and writes the values beside it, several fragments at once; the rows themselves never reach your process. The method returns the next version, which a commit publishes.

Write values your code computesCopy Anchor Link

When a model or another library in your application computes the values, write them yourself with a column writer. It covers one fragment at a time: read the inputs of a fragment with a scan, compute one value for each row, and pass the values with the fragment's ID. attach_columns takes a fragment's values as one stream of batches and writes them as they arrive, into new files attached to the fragment; the fragment's existing files are not read or rewritten. This sample adds a sharpness score computed by a score function of yours:

main = client.project("demo").branch("main")
table = main.table("frames")
fields = pa.schema([pa.field("sharpness", pa.float32())])
write = table.column_writer(fields)

scan = table.scan(["image"])
fragments: dict[str, list[WorkItem]] = {}
for index, split in enumerate(scan.splits):
    if split.fragment_id is None:
        raise ValueError("this ScanDAL does not name split fragments")
    fragments.setdefault(split.fragment_id, []).append(WorkItem(index, split.start, split.end))
for fragment_id, items in fragments.items():
    batches = [
        pa.record_batch([score(batch.column("image"))], schema=fields) for batch, _, _ in scan.read_work(items)
    ]
    write.attach_columns(fragment_id, pa.Table.from_batches(batches, schema=fields))
added = write.finish()

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

column_writer takes the new fields, which must be nullable. Each split of a scan reads one fragment and names it in fragment_id, so grouping the splits by that ID gives each fragment's rows. Reading a fragment's splits in order returns its rows in stored order, which is the order attach_columns expects: one value of every new field for each row the fragment keeps, so that the new files line up with the fragment's rows by position. The SDK checks the count and writes NULL for the rows the version deleted.

finish returns the next version with the new columns and every value written. Fragments the writer did not cover read NULL.

Write values for chosen rowsCopy Anchor Link

To give values to some rows only, such as frames a reviewer scored by hand, pass the rows to patch_rows with a ROW_ID_COLUMN column that names each row, and some of the new fields:

main = client.project("demo").branch("main")
fields = pa.schema([pa.field("sharpness", pa.float32())])
write = main.table("frames").column_writer(fields)
write.patch_rows(scored)
added = write.finish()
commit = main.commit()
commit.update_table(added)
commit.publish()

The values are recorded beside the fragments that hold the rows, as a patch is. Read the row IDs with a scan, as Read changes shows. One writer can take both whole fragments and row patches, for different fragments.

Rows without values read nullCopy Anchor Link

Rows that a write gave no value read NULL, so a backfill can span several versions: publish the column with the fragments you have computed, and fill the others later. A column writer only adds columns, so once a version with the column is published, write later values through the Table's writer, by row ID, as for any other column.

Rows appended after the column was added also read NULL until something writes their values.

Verify the resultCopy Anchor Link

Read the new column from the version that the commit published. A scan of frames with columns set to frame_id and sharpness shows the values and the rows still at NULL. See Read rows.

Keep the values out of the TableCopy Anchor Link

A new column belongs to the Table: it is in its schema, every reader of the Table sees it, and only people who can write the Table can write it. When the values are derived from each row by an expression, and some readers of the Table do not need them, define an Enrichment instead. An Enrichment keeps its values in its own versions and leaves the Table's schema and storage unchanged; readers attach it to a scan when they want it.

The values of a column in the Table are not checked against the column's inputs: when camera changes, is_front keeps its old value until you write a new one. An Enrichment records which source columns its values depend on, and reports values as stale once those columns change.