Blog / How-to guides

Load a CSV, sum a column exactly: Semurg as a columnar database

1 November 2026 · Semurg · 4 min read · Markdown

Last checked on r11.142.20261030.e037138 (30 Oct 2026): every command below ran on a fresh node of that release. The current release is r11.143.20261102.b5a9758 (2 Nov 2026), and the commands have not been re-checked on it yet (how)

columnaranalyticsdatabasehow-to

A column sum answers one question over and over: what is the total of this number across every row? Semurg answers it from a table whose whole-number columns are laid down as column runs, so the answer reads only that column. This post lands a CSV file on your node, loads it as a table, and sums a column.

A CSV file is landed in the node's customs area, loaded as a table whose whole-number columns become column runs, and a column is summed exactly with the bytes the answer read reported beside it.A CSV fileLand it on your node:admit, write, adoptLoad it as a tableSum a column: an exacttotal, with the bytesread

Land the file and load it

The commands on this page ran on the release published on 30 October 2026.[1]

Landing a file needs your node's owner token, which on a node you installed yourself is the token it printed. The file is described by its exact size and its SHA-256, and arrives in three calls: admit, write, adopt. The load is one more call, and it answers with a job that finishes in moments for a small file. The block below does all of it for a four-row file and then sums the cents column:

bash
export SEMURG_API_TOKEN=<your token>
H="Authorization: Bearer $SEMURG_API_TOKEN"
API=http://localhost:4000
printf 'id,units,cents,region\n1,3,1250,north\n2,5,400,south\n3,7,99900,north\n4,1,5,east\n' > orders.csv
BYTES=$(wc -c < orders.csv)
SHA=$(sha256sum orders.csv | cut -d' ' -f1)
ADMIT=$(curl -sS $API/v1/cluster/customs/land -H "$H" -H "Content-Type: application/json" \
  -d "{\"action\":\"admit\",\"args\":{\"name\":\"orders.csv\",\"bytes\":$BYTES,\"local_index\":0}}")
ID=$(echo "$ADMIT" | grep -o '"id":"[^"]*"' | head -1 | cut -d'"' -f4)
FILE=$(echo "$ADMIT" | grep -o '"path":"[^"]*"' | head -1 | cut -d'"' -f4)
curl -sS $API/v1/cluster/customs/land -H "$H" -H "Content-Type: application/json" \
  -d "{\"action\":\"write\",\"args\":{\"id\":\"$ID\",\"offset\":0,\"content_base64\":\"$(base64 -w0 orders.csv)\"}}"
echo
curl -sS $API/v1/cluster/customs/land -H "$H" -H "Content-Type: application/json" \
  -d "{\"action\":\"adopt\",\"args\":{\"id\":\"$ID\",\"bytes\":$BYTES,\"sha256\":\"$SHA\"}}"
echo
curl -sS $API/v1/query -H "$H" -H "Content-Type: application/json" \
  -d "{\"op\":\"connector_ingest\",\"family\":\"csv\",\"options\":{\"db\":\"semurg\",\"max_concurrency\":1,\"tables\":[{\"name\":\"orders\",\"path\":\"$FILE\",\"pk\":[\"id\"],\"types\":{\"id\":\"bigint\",\"units\":\"bigint\",\"cents\":\"bigint\",\"region\":\"text\"}}]}}"
echo
for i in $(seq 1 30); do
  SUM=$(curl -sS $API/v1/query -H "$H" -H "Content-Type: application/json" \
    -d '{"op":"aggregate","column":{"db":"semurg","table":"orders","name":"cents"},"cmp":"ge","threshold":0}')
  case "$SUM" in *'"ok":true'*) break;; esac
  sleep 1
done
echo "$SUM"

Expected: the file lands, the load starts, and the sum of cents is 101555 over 4 rows, read from column cells

The sum arrives as a decimal string, so it is exact for any size of total, and the answer carries what it read: here one 64-byte line for four cells.

Sum above a threshold

aggregate sums only the cells that pass a comparison, and reports how many did. The table stays on your node, so this call stands on its own: the sum of units where units is greater than 3.

bash
export SEMURG_API_TOKEN=<your token>
curl -sS http://localhost:4000/v1/query \
  -H "Authorization: Bearer $SEMURG_API_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"op": "aggregate", "column": {"db": "semurg", "table": "orders", "name": "units"},
       "cmp": "gt", "threshold": 3}'

Expected: two cells pass, 5 and 7, and their sum is 12

Read rows back

A row of the loaded table reads back by its key, with the same per-key answer as any table: the row, or null for an absent key.

bash
export SEMURG_API_TOKEN=<your token>
curl -sS http://localhost:4000/v1/query \
  -H "Authorization: Bearer $SEMURG_API_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"op": "read_rows", "db": "semurg", "table": "orders", "pks": [1, 4, 9]}'

Expected: rows 1 and 4 come back whole, and key 9 is absent

How it behaves

  • Landing a file needs the node's owner token and the file's exact size and SHA-256; the load names the file by the path the admit answer gives.
  • The whole-number columns that are neither the key nor a foreign key are stored as column runs; the key and text columns keep the row form.
  • An aggregate answers with an exact total as a decimal string, how many cells passed, and the bytes it read.
  • A job that would need more memory than the node has free is refused by name; "max_concurrency": 1 asks for the smallest.
  • An aggregate on a column that is not a whole number is refused with 422, and on a column the table does not have with 404, each naming the column.

Next

This post was written with AI assistance. Every result in it is checked against a published release or record before the post goes out.

How these commands were checked

Our test farm ran each of the 3 command blocks marked runnable, exactly as written, with the answer checked against the expectation printed under it, against a fresh node of the published release r11.142.20261030.e037138. A post is published only when every one of them passed there.

Evidence

  1. Release r11.142.20261030.e037138, published on one.semurg.io (release:r11.142.20261030.e037138; the record as JSON)

Related