SQL (beta)

SQL over a namespace (beta)

Beta: the dialect may grow. One SELECT ... FROM docs over the documents' metadata (columns: id and every top-level meta key, typed from the values: int (booleans 1/0), float or text; missing is NULL; SHOW COLUMNS lists them), with contains(text, '...') and grep(text, 'regex') through the text index. WHERE, GROUP BY, HAVING, ORDER BY, LIMIT/OFFSET, COUNT, COUNT(DISTINCT), SUM, AVG, MIN, MAX, CASE WHEN, COALESCE, LIKE, IN, IS NULL, DATE_TRUNC('minute'|'hour'|'day', x) (x an integer of seconds or a time column), EXTRACT(MINUTE FROM x), REGEXP_REPLACE, LENGTH (bytes); arithmetic + and -. Not yet: joins, subqueries, UNION, window functions, * / %. Answers stop at 10,000 rows (truncated). Guide and patterns: https://skimmerdb.com/docs#analytics. A time limit per plan (10 s free, 30 s pro) and SQL queries in flight per tenant (2 free, 8 pro; 429 past it). Errors in the SQL are 422 with the reason; 413 when it would read too much. Counts as one query (metered; see Usage).

Parameters

namestring · pathrequiredThe namespace's name.

Body application/json

sqlstringrequiredOne SELECT ... FROM docs (or SHOW COLUMNS).

Answer

namespacestring

columnsstring[]

typesstring[]int, float or text, one a column.

rowsstring | number | null[][]

chunksintegerColumn blocks in the namespace.

scannedintegerColumn blocks read.

partialbooleanReserved for response compatibility; incomplete SQL text reads fail with 503.

truncatedbooleanMore than 10,000 rows: these are the first.

took_msnumber

temperaturestringHow warm the namespace was: warm (open), local (opened from the server's disk) or cold (opened from object storage).

store_getsintegerObject-storage reads this query made (0 when warm).

store_bytesintegerBytes those reads fetched.

cpu_msnumberCPU the query used (metered, not billed).

timed_outbooleanThe query ran past the plan's time limit (query_seconds): the answer is partial.

Status codes

200OK

400Malformed: not the JSON expected.

401No key, or an unknown or revoked one.

402The free plan's included usage this period is used up (queries, query CPU, object-storage reads, bytes returned, writes or storage; the message says which), or its namespace count or daily namespace creations are reached (code namespace_limit): upgrade to pro.

403The key's scope or namespaces don't allow it, or the tenant is suspended.

404No such namespace, document, key or tenant (or one this key may not see: the same answer).

413Too big: a request's documents (64 MB), an import (2 GB), or what a SQL query would read.

422Invalid: the message says why (code invalid with fields: which input fields; query_timeout: past the plan's time limit; scan_refused: send scan: true).

429Over the tenant's or the key's rate, concurrency or cap, or writes arriving faster than the namespace commits them (code write_busy): retry after Retry-After seconds.

503Object storage failed: retry (nothing was written, unless the code is write_unknown: then it may have been; retrying a write is safe).

curl -s https://api.skimmerdb.com/v1/namespaces/support/sql \
  -H "Authorization: Bearer $KEY" \
  -d '{"sql":"SELECT status, COUNT(*) AS n, AVG(priority) AS p FROM docs WHERE contains(text, 'refund') GROUP BY status ORDER BY n DESC"}'

from skimmerdb import SkimmerDB
from skimmerdb._api.api.sql_beta import query_sql
from skimmerdb._api.models import SqlRequest

db = SkimmerDB()  # SKIMMERDB_KEY
answer = query_sql.sync("support", client=db.api, body=SqlRequest.from_dict({"sql":"SELECT status, COUNT(*) AS n, AVG(priority) AS p FROM docs WHERE contains(text, 'refund') GROUP BY status ORDER BY n DESC"}))

import { SkimmerDB } from "skimmerdb";
import { querySql } from "skimmerdb/api";

const db = new SkimmerDB(); // SKIMMERDB_KEY
const { data } = await querySql({ client: db.client, path: { name: "support" }, body: {"sql":"SELECT status, COUNT(*) AS n, AVG(priority) AS p FROM docs WHERE contains(text, 'refund') GROUP BY status ORDER BY n DESC"} });

{
  "namespace": "support",
  "columns": [
    "status",
    "n",
    "p"
  ],
  "types": [
    "text",
    "int",
    "float"
  ],
  "rows": [
    [
      "open",
      1,
      3
    ]
  ],
  "chunks": 1,
  "scanned": 1,
  "partial": false,
  "truncated": false,
  "took_ms": 0.9,
  "temperature": "warm",
  "store_gets": 0,
  "store_bytes": 0,
  "cpu_ms": 0.4,
  "timed_out": false
}