fv-streams-sql
The SQL front door for FusionVault Streams.
A streaming SQL statement parses to a small typed logical plan and lowers to the same
topology the TOML frontend produces (its steps are the engine's own JSON vocabulary). One runtime,
two front doors: SQL inherits checkpointing, the exchange, exactness and --join because it emits
the identical steps a hand-written pipeline would.
let topo = compile?;
Design
- No DataFusion
LogicalPlan/ physical layer, no fork. We lower thesqlparserAST to our own IR, which models streaming (DataFusion's logical plan does not); the errors are streaming-aware and ours, and the IR is the seam a future optimiser plugs into. (Seedesign/…-phase5-…§10.) - Expressions reuse the value dialect. SQL scalar expressions unparse to the value-dialect
strings the
expressionsteps already carry, compiled throughfv-value-datafusionat build time exactly as the TOML path does — one expression layer, so the two front doors cannot drift.
The supported SQL surface
Exactly what lowers to a spec operator — everything else is rejected at plan time with an error
that names the supported shape.
| SQL | lowers to |
|---|---|
INSERT INTO <sink> SELECT … FROM <source> [WHERE …] |
a stateless stage (filter / applyExpression / select) |
SELECT *, <expr> AS name … |
keep every column + the computed one (no trailing select) |
GROUP BY TUMBLE(ts, INTERVAL …), <keys> + count/sum/avg/min/max |
windowedAggregate (tumbling), keyed |
GROUP BY HOP(ts, INTERVAL <slide>, INTERVAL <size>), … |
windowedAggregate (sliding) |
GROUP BY SESSION(ts, INTERVAL <gap>), … |
sessionAggregate |
a trailing INTERVAL on any window (TUMBLE(ts, INTERVAL <size>, INTERVAL <lateness>), HOP(…, INTERVAL <lateness>), SESSION(…, INTERVAL <lateness>)) |
the window's allowedLatenessMs (how long a closed window still accepts late events; omitted → the engine default) |
WITH <name> AS (<subquery>), … <outer> |
multi-stage: each CTE (or a subquery feeding a join/aggregate) materialises as its own stage under <name>, referenced downstream — the SQL form of the TOML [[transforms]] DAG |
count(DISTINCT col) / count(DISTINCT CASE … END) |
countDistinct (a CASE/expression argument becomes a pre-aggregate applyExpression column) |
a computed SELECT column over windowStart/group/aggregates |
a post-aggregate applyExpression (e.g. substring(epochToIso(windowStart), 0, 10) AS day) |
… GROUP BY …, TUMBLE(…) EMIT CHANGES EVERY <n> ROWS|MS |
the updating aggregate (early firing) — the emit block |
FROM a JOIN b ON a.k = b.k AND ts BETWEEN … |
streamJoin (interval) |
FROM stream JOIN <bounded> ON a.k = b.k |
the lookup join (right side = the bounded table) |
FROM (SELECT …, ROW_NUMBER() OVER (PARTITION BY g ORDER BY c [DESC], [tie]) AS rn FROM s [WHERE …]) WHERE rn <= n |
topN (a second ORDER BY column is the tie-break) |
Scalar expressions render into the value dialect: =→==, <>→!=, AND→&&, OR→||, SQL
|| concat→+, CASE→?:, IS [NOT] NULL→== null/!= null, SUBSTRING(x, a, b)→
substring(x, a, b); function names keep their written (camelCase) case, since the dialect's are
case-sensitive.
Rejected at plan time (each with a message naming the supported shape): a bare SELECT with no
sink; a GROUP BY with neither a window function nor EMIT CHANGES; EMIT CHANGES on a stateless /
session shape or without a cadence; a comma join (FROM a, b); a JOIN whose key columns don't share a
name; SELECT *, <bare column>; HAVING; UNION/INTERSECT/EXCEPT; WITH RECURSIVE (a streaming
CTE has no fixpoint); a non-INSERT statement. (ORDER BY over an unbounded stream, correlated
subqueries and DDL are likewise not part of the subset.)
Multi-stage worked examples (each e2e-exact against its TOML twin's oracle): q13 — WITH bids AS (SELECT *, auction % N AS key … WHERE type='bid') SELECT * FROM bids JOIN side ON bids.key = side.key
(a computed key, then the lookup join); q5 — a sliding windowed count CTE then a per-window
ROW_NUMBER() <= 1; q4 — four CTEs (two filtered streams, their interval join, a per-auction
max) then an avg per category, five stages with two nested windowed aggregates.
In a pipeline
A pipeline config declares its datasets as usual and puts the query in a sql field instead of
[[transforms]]:
= "enrich"
[]
= "localhost:9092"
[]
= "bids"
[]
= "out"
= "INSERT INTO out SELECT auction, price * 0.908 AS eur FROM bids WHERE type = 'bid'"