Walleye docs
Your first query
Three ways to ask, and they are not interchangeable: a filter, a nearest neighbour search, and SQL.
Filter and count
table.count_rows() # 4
table.count_rows("city = 'seattle'") # 2
table.search().where("id > 1").select(["id", "city"]).limit(10).to_list()
await table.countRows();
await table.query().where("city = 'seattle'").limit(10).toArray();
where takes a SQL expression over the table’s columns. select takes column
names. limit and offset page through the result.
Nearest neighbours
table.search([0.0, 0.9]).limit(2).to_list() # nearest first
table.search([0.0, 0.9]).where("city = 'portland'").to_list()
Each row comes back with a _distance. The search uses the index’s own
metric, and asking for a different one is an error rather than a silent
rescoring — set the metric once with create_index and queries follow it.
A filter alongside a vector query narrows the same search rather than filtering afterwards.
SQL
SQL is a Walleye route rather than a LanceDB one: the client has no call for it, so you post to it.
curl -s localhost:8080/v1/query \
-H "authorization: Bearer $WALLEYE_TOKEN" -H "content-type: application/json" \
-d '{"sql": "SELECT city, count(*) AS n FROM clicks GROUP BY city ORDER BY n DESC"}'
[{"city": "seattle", "n": 2}, {"city": "portland", "n": 1}, {"city": "boise", "n": 1}]
It is read-only DataFusion SQL with a sixty second deadline, a 64 KiB limit on
the statement and an 8 MiB limit on the JSON it returns. There is no DDL and no
DML: tables are created and written through the LanceDB calls above, and a
SELECT sees the rows in memory as well as the rows on disk. On one node it
reads every table on the node; on three it also gathers the tables this node
does not own from their owners.
Sorting is the common reason to reach for it. order_by is not part of the
LanceDB search surface here, and asking for it returns
order_by is not supported; use SQL via /v1/query.
On three nodes a statement spanning tables with different owners still works, with one limit worth knowing about before you write it.
What is not there
Saying this here saves you finding it out from an error:
- no scalar indexes.
Index.btree(),Index.bitmap()andIndex.labelList()are all a 400 —index type BTREE is not supported; vector columns use IVF_HNSW_SQ. OnlyIVF*andHNSW*are accepted, and vector columns are indexed for you anyway. Filtering a scalar column still works; it is a scan - no full-text search and no full-text indexes, each with its own 400
- no
update,deleteormerge_insert. These have no route at all, so they are a 404 with an empty body rather than a reason — write a row with the same key instead, and the newest one wins. So areadd_columns,restore, the version and tag calls, and per-index stats - no ordering — on a search or on a plain scan — and no column expressions in
select: both are a 400 pointing at/v1/query - namespaces are accepted and ignored: every namespace id lists the one root
Refusals is the full list with the message each one returns. What you own keeps the same list for the managed service as for a node you run. Nothing on it is a managed feature held back.