Vectors, files and models

A column can hold more than numbers and text. Pondra has vector columns for “most like this” searches, files that live in the lake beside the tables, models you call from SQL, and JSON you read in place. Nothing here needs another service: the binary carries the functions, and your models stay wherever you run them.

Search by meaning

A vector is a FLOAT[] column. To search, sort by how close each vector is to the one you have. Start with a table of notes:

CREATE TABLE notes (id BIGINT, body VARCHAR, v FLOAT[]);
INSERT INTO notes (id, body) VALUES
  (1, 'green tea with honey'),
  (2, 'chocolate cake recipe'),
  (3, 'black tea and lemon');

The vectors come from a model: ai_embed calls one from SQL (below), or you compute them anywhere and write them like any other column. This page uses a tiny stand-in that counts four words, so the example runs without a model. A real embed has the same shape and a model in its body.

CREATE OR REPLACE FUNCTION embed(t VARCHAR) RETURNS FLOAT[] LANGUAGE python AS $$
    words = t.lower().split()
    return [float(words.count(w)) for w in ("tea", "cake", "coffee", "lemon")]
$$;
UPDATE notes SET v = embed(body);

Now “most like this” is an ORDER BY. cosine_similarity is 1 for the same direction and 0 for unrelated; cosine_distance, l2_distance and dot_product are there too, under DuckDB’s names as well (array_cosine_similarity, array_distance, …).

SQL

SELECT id, body, cosine_similarity(v, embed('tea please')) AS score
FROM notes ORDER BY score DESC LIMIT 2;

Python

import pondra
db = pondra.connect("http://localhost:8080")

query = db.sql("SELECT embed('tea please') AS q").item()      # a list of floats
db.sql("SELECT id, body, cosine_similarity(v, $q) AS score FROM notes ORDER BY score DESC LIMIT 2",
       q=query).rows()

id  body                  score
1   green tea with honey  1.0
3   black tea and lemon   0.7071067811865475

A vector from Python goes in as a parameter, and a table of vectors from pyarrow, numpy or pandas is written as any table is:

import pyarrow as pa

db.append("notes", pa.table({
    "id": [4],
    "body": ["iced tea"],
    "v": pa.array([[1.0, 0.0, 0.0, 0.0]], pa.list_(pa.float32())),
}))
db.sql("SELECT count(*) AS n FROM notes").item()

4

Vector columns publish to Delta and Iceberg as array and list, so other engines read them back (see other engines).

Caution

There is no vector index yet. A search is exact and compares every vector the WHERE leaves, so filter first (by date, by customer) when you can.

Files next to your tables

Put an image, a PDF, an audio clip or a model in the lake with PUT, and the bytes sit beside the tables. A table’s row keeps the path; a query reads the bytes only where it needs them.

printf 'hello' > hello.txt
curl -s -X PUT localhost:8080/files/docs/hello.txt --data-binary @hello.txt
curl -s -X PUT localhost:8080/files/docs/notes.txt --data-binary 'a second, longer file'
curl -s localhost:8080/files/docs/hello.txt

{"path":"files/docs/hello.txt","bytes":5}
{"path":"files/docs/notes.txt","bytes":21}
hello

files('prefix') lists what is there, and file_read(path) gives one file’s bytes as a BINARY value. A prefix or path may start with files/, the folder the lake keeps files in, or leave it out: files('docs/') and files('files/docs/') list the same files.

SELECT path, size FROM files('docs/') ORDER BY path;

path                  size
files/docs/hello.txt  5
files/docs/notes.txt  21

Keep a table of your files, and work with the bytes in SQL. byte_length, substr, sha256, md5, encode and decode take bytes, and CAST(bytes AS VARCHAR) reads them as text:

CREATE TABLE documents AS
SELECT path, size, encode(sha256(file_read(path)), 'hex') AS sha FROM files('files/docs/');

SELECT path, byte_length(file_read(path)) AS bytes, CAST(file_read(path) AS VARCHAR) AS text
FROM documents ORDER BY path;

path                  bytes  text
files/docs/hello.txt  5      hello
files/docs/notes.txt  21     a second, longer file

To keep bytes in a column of their own, declare it BYTEA (BINARY, VARBINARY and BLOB mean the same) and fill it with file_read, decode, or a bytes value from Python:

CREATE TABLE blobs (id BIGINT, raw BYTEA);
INSERT INTO blobs SELECT 1, file_read(path) FROM files('files/docs/') WHERE path LIKE '%hello%';
SELECT id, byte_length(raw) AS n FROM blobs;

id  n
1   5

A CSV, JSON or Parquet file among them reads as a table too, by its whole location (GET /objects says where the lake’s files are). Whoever may read the lake may read its files this way, as GET /files/… and file_read do; nothing else of the lake’s folder. The console opens one in a tab, as a table you can edit (CSV and JSON) and save back in place.

Caution

A path that exists isn’t written again blind: a PUT without If-Match returns 409. To replace it, send the version a GET gave (its etag) as If-Match; if the file changed since, the PUT returns 412 (see Files). file_read of a path that isn’t there is NULL. file_read returns files up to 256 MB, eight at a time. String functions other than substr and byte_length need a CAST(… AS VARCHAR) first.

Models in SQL

ai_complete and ai_embed call any OpenAI-compatible endpoint: your own vLLM or Ollama, or a hosted one. Pondra carries no model, so the binary stays small and the GPU stays outside it. Start the node with the endpoint’s address:

PONDRA_AI_URL=http://localhost:11434/v1 pondra serve lake

SELECT id, ai_complete('Summarize in five words: ' || body) AS summary FROM tickets;

UPDATE tickets SET v = ai_embed(body) WHERE v IS NULL;

SELECT id, body FROM tickets
ORDER BY cosine_distance(v, ai_embed('printer problems')) LIMIT 5;

A second argument names the model: ai_complete(prompt, 'llama3.2'). Sorting by a distance, smallest first, leaves rows with no vector yet at the end; sorting by cosine_similarity … DESC puts them first, as DESC puts every NULL first. Each distinct text in a batch is asked once and embeddings go many to a request; a node keeps at most PONDRA_AI_CALLS requests in flight and tries one again after a 429, a 5xx or a lost connection. A row whose call still fails is NULL, so one bad row doesn’t lose the query. The reference lists the settings (PONDRA_AI_KEY and the default models).

Caution

With no endpoint listening, every row comes back NULL and the query still succeeds. If a model function is NULL for every row, check PONDRA_AI_URL first. A model call costs what the endpoint charges, for every row the query reaches, so put a WHERE and a LIMIT before it, and save an embedding with UPDATE rather than computing it in each search.

Your own model behind Arrow Flight

For a model that must stay loaded, or run on a GPU, register a process of your own as a function. Pondra sends it the rows it has as one Arrow batch (the arguments are columns arg0, arg1, …) and reads back one column of the same length. A crash or a slow batch in that process can’t take a node down, and the binary carries none of it.

This server is a function caption of bytes. In real life it is a program you run on its own; here it starts in a thread so the page is self-contained:

import threading
import pyarrow as pa
import pyarrow.flight as flight
import requests

class Caption(flight.FlightServerBase):
    def do_exchange(self, context, descriptor, reader, writer):
        args = reader.read_all()
        out = pa.array([None if b is None else f"a file of {len(b)} bytes" for b in args.column("arg0").to_pylist()])
        answer = pa.table({"out": out})
        writer.begin(answer.schema)
        writer.write_table(answer)
        writer.close()

server = Caption("grpc://127.0.0.1:0")
threading.Thread(target=server.serve, daemon=True).start()

reply = requests.post("http://localhost:8080/functions/caption",
                      json={"flight": f"http://127.0.0.1:{server.port}", "args": ["Binary"], "returns": "Utf8"})
reply.json()

{'function': 'caption'}

The function is in the lake’s catalog, so it works on every node, in any query, as soon as it is made (and stops working as soon as DELETE /functions/caption drops it). It can take another function’s answer, such as file_read:

SELECT path, caption(file_read(path)) AS caption FROM files('docs/') ORDER BY path;

path                  caption
files/docs/hello.txt  a file of 5 bytes
files/docs/notes.txt  a file of 21 bytes

GET /functions lists these functions and DELETE /functions/caption removes one. The server has to be reachable from every node. For code that doesn’t need its own process, Python functions run on the nodes and need nothing set up.

JSON in a column

Keep a JSON document in a JSON column (VARIANT is the same, and a VARCHAR column works too) and read it where it lies. Each function takes the document and a path: keys for objects, positions for arrays.

CREATE TABLE events (id BIGINT, payload JSON);
INSERT INTO events VALUES
  (1, '{"user": {"name": "ann", "tags": ["a", "b"]}, "amount": 4.5, "ok": true}'),
  (2, '{"user": {"name": "bo", "tags": []}, "amount": 2.0, "ok": false}');

SELECT id,
       json_get_str(payload, 'user', 'name') AS name,
       json_get_str(payload, 'user', 'tags', 0) AS first_tag,
       json_get_float(payload, 'amount') AS amount,
       json_length(payload, 'user', 'tags') AS tags
FROM events
WHERE json_contains(payload, 'user')
ORDER BY id;

id  name  first_tag  amount  tags
1   ann   a          4.5     2
2   bo    NULL       2.0     0

payload ->> 'amount' reads any value as text, and -> steps into a nested one. A path that isn’t there is NULL. The typed getters (json_get_str, json_get_int, json_get_float, json_get_bool) don’t convert, so a number read with json_get_str is NULL: use ->> and CAST when you don’t know the type. The functions reference has the full list. A Kafka topic’s values, and a Debezium feed, are read the same way.

Caution

A JSON or VARIANT column holds JSON text, as a VARCHAR column does. Documents are parsed when read, so a filter on a field reads and parses each document.