fv-streams-sql 0.4.0

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 — one runtime, two front doors. No DataFusion planner, no fork; expressions compile through the value dialect as the steps already do.
Documentation

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 = fv_streams_sql::compile(
    "INSERT INTO out SELECT auction, price * 0.908 AS eur FROM bids WHERE type = 'bid'",
)?;

Design

  • No DataFusion LogicalPlan / physical layer, no fork. We lower the sqlparser AST 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. (See design/…-phase5-… §10.)
  • Expressions reuse the value dialect. SQL scalar expressions unparse to the value-dialect strings the expression steps already carry, compiled through fv-value-datafusion at 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]]:

name = "enrich"
[brokers]
default = "localhost:9092"
[datasets.bids]
topic = "bids"
[datasets.out]
topic = "out"
sql = "INSERT INTO out SELECT auction, price * 0.908 AS eur FROM bids WHERE type = 'bid'"