Skip to content

SQL Temporal Store

A temporal key-value store held in the database, which unlike the other two can be corrected in place.

A SQL Temporal Store holds values keyed by a map name, a key and an effective time, in Stroom's database. A pipeline loads it, and translations and queries look values up from it by the time of the event being processed.

That description also fits reference data and Plan B, and the three really are alternatives for the same job. What separates this one is that its contents can be edited in place.

Why that matters

Reference data and Plan B are both derived: reference data is rebuilt from the streams that loaded it, and a Plan B shard is merged from what was written to it. Correcting either means fixing the source and reloading.

A SQL Temporal Store holds the data itself. Entries can be added, changed and removed directly, and the change is the record rather than a step towards one. That suits facts that are maintained by people rather than produced by a feed: a list of sites, who owns an asset, what a room is called.

It is also why floor maps use one for their facts. See Floor mapping.

Choosing between the three

UseWhen
Reference dataThe facts arrive as a feed and the volume is modest. Loads lazily, so you pay only for what is looked up
Plan BVery high lookup rates, or large state written by many nodes. Node-local and built for speed
SQL Temporal StoreThe facts are maintained rather than fed, and being able to correct a single entry matters more than throughput

The last row is the deciding one. If nobody will ever hand-correct an entry, one of the other two is usually a better fit.

Reference data LOADED BY A reference feed LIVES IN An off-heap store, on each node CORRECTED BY Fixing the feed and reloading Loads lazily, so you pay only for what is used. Plan B LOADED BY A pipeline, per node LIVES IN Local shards, merged and read as snapshots CORRECTED BY Rewriting the source and re-merging Built for very high lookup rates. SQL Temporal Store LOADED BY A pipeline, or by hand LIVES IN The database, shared by the whole cluster CORRECTED BY Editing the entry in place For facts people maintain, not feeds.
The first two are derived and have to be rebuilt from their source. The third holds the data itself, which is why an entry can be corrected on its own.

Because it lives in the database, it does not scale the way Plan B does. It is sized for the sort of data a person could plausibly maintain, not for millions of entries changing constantly.

How data gets in and out

In, from a pipeline: an XSLTFilter produces the same reference-data:2 format a reference loader produces, and SqlStoreFilter writes it to the store.

In, by hand: through the REST API, or through the floor map editor for floor-map facts. The store's own screens show and query its contents, and can reset it, but do not edit single entries.

Out: stroom:lookup() from XSLT, exactly as for reference data and Plan B, resolving against the event's effective time. The store's Data tab also queries it directly, and has a Create Dashboard button that turns the query you are looking at into a dashboard. See Building a dashboard.

The temporal part

Entries are keyed by effective time, so a lookup returns what was true when the event happened rather than what is true now. That is the same rule as reference data, and the same trap: correcting a value today does not change how events from last week are enriched.

Effective times explains the rule and how to test it. It applies here unchanged.

All documentation