Query with SMQL

Applies to: development sources. SMQL is not included in published APT 0.1.0-2. Use matching development tools and a server that supports database-aware scans.

Before you begin

Use a running sharded cluster, a reachable management seed, and matching smkv-ctl tools. SMQL discovers the owners through the management port and reads rows through their data ports; the client must reach both. A standalone node without cluster management discovery is not supported by this query command. Use an existing table with data written through an SDK; see tables and columns. No CREATE TABLE statement is needed.

If authentication is enabled, configure the client security profile as described in TLS and authentication. Your identity needs permission to read the selected database and discover the cluster.

Select from one table

SMQL is a small, read-only SQL-like language. It queries structured tables, with no joins or changes to stored records. Raw key/value records are not SQL tables.

smkv-ctl --seed 127.0.0.1:7381 query \
  'SELECT name, age FROM users WHERE age >= 18 AND active = true LIMIT 25'

Use --database customers for an unqualified table, or qualify the table in SQL:

SELECT name, age FROM customers.users WHERE age >= 18 LIMIT 25;

A qualified database overrides --database. Names preserve case, while keywords are case-insensitive. Use double quotes for names with punctuation:

SELECT "display-name" FROM "tenant-one".users WHERE email IS NOT NULL;

In SMKV, IS NOT NULL means the column exists; there is no stored null type.

Supported conditions

Use = for equality and <, <=, >, >= for numeric comparisons. Combine conditions with AND. Literals support strings ('O''Brien'), int64 integers, finite floats (1.5), booleans (true), and bytes (X'00ff'). Comparisons match exact types: integer 1, float 1.0, and string '1' are distinct.

SELECT * returns all columns; otherwise name the columns to return. Missing projected columns are omitted. Filters are evaluated before projection.

Joins, OR, sorting, aggregates, grouping, subqueries, offsets, SQL writes, and multiple statements are not supported. Unsupported syntax returns an error.

Read subsequent pages

Each invocation returns JSON containing rows and an opaque cursor. Pass that cursor to the same query using --cursor. An empty page can still have a cursor; continue until cursor is null. Changing the query requires starting again.

LIMIT is the total row budget across all pages, not the page size. It defaults to 100 and accepts 1–1000. Each request returns at most 100 rows, and may return fewer because work and response sizes are bounded. Requests never automatically traverse the whole cluster. limit_remaining reports the unused row budget.

This shell example uses jq to save the first page and explicitly fetch the next:

query='SELECT name, age FROM users WHERE age >= 18 LIMIT 25'
smkv-ctl --seed 127.0.0.1:7381 query "$query" > page.json
jq '.rows' page.json
cursor=$(jq -r '.cursor // empty' page.json)
if [ -n "$cursor" ]; then
  smkv-ctl --seed 127.0.0.1:7381 query "$query" --cursor "$cursor" > next-page.json
fi

Repeat with the cursor from each new page. Keep the query and selected database unchanged. Do not edit cursor contents or interpret a nonzero limit_remaining as proof of more matching rows: cursor is the completion signal.

A completed page might look like this (illustrative values):

{
  "rows": [
    {
      "key": "757365722d31",
      "columns": {
        "name": {"type": "string", "value": "Ada"},
        "age": {"type": "int", "value": "37"}
      }
    }
  ],
  "cursor": null,
  "examined": 8,
  "partition": 3,
  "limit_remaining": 24
}

Record keys and byte values are hexadecimal. Integers are decimal strings inside typed values to preserve all 64 bits. examined counts work on this page, not the number of matching records in the database.

Results have no guaranteed order and are not a snapshot. Concurrent writes can change the results. Ownership or storage changes can invalidate a cursor; restart the query when instructed. These are table scans, not secondary-index lookups.

Use the web console

Open Tables, select SMQL as the query mode, enter a SELECT statement, and choose Run query. Use Next page to continue or Cancel to stop the current request. Editing the query clears its results and continuation cursor.

SMQL uses the same database permissions, deadlines, and low-priority scan scheduling as the table browser. Point reads and writes retain priority. The web connection token and configured cluster identity checks still apply.

Statements are limited to 4096 bytes, 64 projected columns, and 16 conditions. Very large literals or query shapes are rejected. There is no SQL SDK method yet; existing structured scan APIs remain available to SDK clients.

Troubleshoot a query

See the SMQL language reference for the full syntax, limits, and web API request format.