SQL

Everything that reads records — Space Window subscriptions, tools, notifications, Space::query in the Rust package, spacestation query, API keys — speaks ClickHouse SQL through one door.

The mirage

You write FROM orders. Logical tables share one physical ClickHouse table. The backend parses the query, checks each table reference and scopes it to the authenticated account UUID and table id. A ClickHouse row policy enforces the account boundary again. API keys and the notification engine stay within the same owning account.

Queries run read-only (readonly=1, allow_ddl=0) with a 10 s limit, at most 100 000 rows / 16 MB per result, and no access to system tables or logs.

Columns

Every table has the same columns:

Casting JSON paths

record and metadata are ClickHouse JSON columns, so a path such as record.amount has a dynamic type. Cast it to use it:

SELECT record.id::String AS id, record.amount::Float64 AS amount
FROM orders
WHERE record.amount::Float64 > 5
ORDER BY event_ts_ms DESC
LIMIT 50

• Cast every path you compare, sum or sort on: ::String, ::Float64, ::Int64, ::Bool, ::Array(String), ::DateTime64(3) and so on.
• 64-bit numbers. Integers inside record arrive as JSON numbers and lose precision above 2^53. Send large ids as strings, and read them as ::String.
• Key order inside record is not preserved.
• Missing paths are NULL; use ifNull(record.x::Float64, 0) when that matters.

64-bit integers come back as strings

ClickHouse quotes every UInt64 and Int64 in JSON output, so nothing is lost in transit above 2^53 — and so every value of those types is a string in what the query API, the CLI, the package and a notification's rows hand you. That is the three columns, and it is also every expression of that type, which is where it surprises:

So {"n": "42"} is not a bug, and rows[0].n > 5 in JavaScript compares a string. Either cast in SQL to a type that is not 64-bit —

SELECT toInt32(count()) AS n, toFloat64(sum(record.qty::Int64)) AS qty FROM orders

— or Number() it where you read it. One exception: the space window runtime turns exactly cursor, event_ts_ms and registered_ts_ms into numbers before your processor sees a row, because it needs them itself; nothing else is touched. toFloat64 is safe for anything that fits in 2^53 — every count and every millisecond timestamp for the next 280 000 years.

Arrays

A JSON path is Dynamic and ARRAY JOIN wants an Array, so cast it in a derived table and ARRAY JOIN that:

SELECT i.sku::String AS sku, count() AS n
FROM (SELECT record.items::Array(JSON) AS items FROM orders) ARRAY JOIN items AS i
GROUP BY sku ORDER BY n DESC LIMIT 10

::Array(JSON) for an array of objects (reach into each one with i.sku::String), ::Array(String) and friends for arrays of scalars. The cast cannot be written where the ARRAY JOIN is — :: there does not parse and CAST(…) there reads as a table function and is refused — and ARRAY JOIN record.items without it is a ClickHouse type mismatch (Code: 53). arrayJoin(record.items::Array(String)) in the SELECT list works too, for scalars.

Notifications: every row is a notification

A notification's SQL must return dedup_key (non-empty string ≤ 256 bytes), text (string) and metadata (an object — map(...) renders as one). The select list is checked when the notification is saved — a missing or extra column is 400 invalid_sql_shape naming it — and the types are checked on every row at run time (a bad row is a dev error, and nothing is sent):

SELECT record.id::String AS dedup_key,
       concat('Order ', record.id::String, ' for ', record.amount::String) AS text,
       map('amount', record.amount::String, 'customer', record.customer::String) AS metadata
FROM orders
WHERE record.amount::Float64 > 100

Bounds

In a delta subscription and in a table-triggered notification the SQL only sees rows with cursor in (last cursor, watermark] for each trigger table; a snapshot subscription, a schedule-triggered notification and a plain query see everything up to the watermark at query start. Watermarks are returned with every result so the client can carry on from them.

You can ask for a range yourself: POST /query takes restrict: {table: {from?, to?}} and Space::query(sql, restrict) takes the same map. from is exclusive, to inclusive, and a missing to becomes that table's watermark at query start — which is what comes back in watermarks.

What is refused

• Anything that is not exactly one SELECT (with WITH, UNION, sub-selects and joins allowed).
• SETTINGS, FORMAT, INTO OUTFILE — at any nesting level.
• Table functions: url(), remote(), file(), s3(), mysql(), …
• Qualified names (db.table), system.*, and any table outside your account.
• Identifiers inside IN (…) that are not columns of the query (x IN (system.one)).

A refused query is an error with a code — a dev error in a window, dev_errors for a notification, Error::Api { code, .. } from the package, a message in the CLI.

Tables outside the authenticated account are unavailable.