Advanced

Temporal ML Compute

ChronusQL supports tested online temporal ML queries with multi-stream alignment, feature extraction, label definitions, online classifier training, and prediction compute fields.

Guide

ChronusQL supports tested online temporal ML queries with multi-stream alignment, feature extraction, label definitions, online classifier training, and prediction compute fields.

The main tested ML query shape is:

sql
SELECT c.deviceId,
       c.ts,
       predicted_label,
       confidence,
       impact_confidence,
       normal_confidence
INTO output
FROM cpu AS c TIMESTAMP BY c.ts
ALIGN JOIN memory AS m TIMESTAMP BY m.ts
  ON c.deviceId = m.deviceId
  WITHIN Duration(second, 5)
ALIGN JOIN network AS n TIMESTAMP BY n.ts
  ON c.deviceId = n.deviceId
  WITHIN Duration(second, 5)
ANCHOR c

PARTITION BY c.deviceId
WINDOW BY SlidingWindow(Duration(minute, 30))

FEATURES
  cpu_value = c.cpu,
  mem_value = m.mem,
  network_value = n.bytes,
  cpu_delta = diff(c.cpu),
  mem_avg = avg(m.mem),
  cpu_z = zscore(c.cpu),
  latency_mean = mean(c.latency)

LABELS
  impact = future(c.latency > 500, Duration(minute, 2)),
  normal = c.cpu < 60 AND m.mem < 70

MODEL incident = TRAIN ONLINE_CLASSIFIER()
LABELS impact, normal
WITH (
  algorithm = 'online_logistic',
  learning_rate = 0.05,
  l2 = 0.001,
  min_examples = 20
)

COMPUTE
  predicted_label = CLASSIFY(incident),
  confidence = PROBABILITY(incident),
  impact_confidence = PROBABILITY(incident, impact),
  normal_confidence = PROBABILITY(incident, normal)

ML Query Flow

mermaid
flowchart LR
    A["Anchor stream cpu AS c"] --> D["Aligned row"]
    B["memory AS m"] --> D
    C["network AS n"] --> D
    D --> E["PARTITION BY c.deviceId"]
    E --> F["SlidingWindow"]
    F --> G["FEATURES"]
    G --> H["LABELS"]
    H --> I["TRAIN ONLINE_CLASSIFIER"]
    I --> J["COMPUTE predictions"]
    J --> K["SELECT output fields"]

ML FROM and ALIGN JOIN

The ML `FROM` clause starts with an anchor input:

sql
FROM cpu AS c TIMESTAMP BY c.ts

Additional streams are aligned by key and time tolerance:

sql
ALIGN JOIN memory AS m TIMESTAMP BY m.ts
  ON c.deviceId = m.deviceId
  WITHIN Duration(second, 5)

The tests validate:

  • The anchor input is `cpu` with alias `c`.
  • Aligned inputs are `memory` and `network`.
  • Output timestamps follow the anchor stream timestamps.
  • Missing optional aligned stream records do not prevent anchor rows from producing output.

ML Windows

ML windows support:

sql
WINDOW BY SlidingWindow(Duration(minute, 30))

The parser also recognizes `TumblingWindow`, `SlidingWindow`, and `HoppingWindow` for ML windows.

ML queries require `WINDOW BY`. The tests reject an ML query with the window clause removed.

ML Features

The tests cover feature definitions from direct fields and feature functions:

sql
FEATURES
  cpu_value = c.cpu,
  mem_value = m.mem,
  network_value = n.bytes,
  cpu_delta = diff(c.cpu),
  mem_avg = avg(m.mem),
  cpu_z = zscore(c.cpu),
  latency_mean = mean(c.latency)

The tested ML examples use:

  • `diff(field)`
  • `avg(field)`
  • `mean(field)`
  • `zscore(field)`

The ML parser also recognizes `count(field)` and `stddev(field)` feature functions.

Feature names must be unique. The tests reject duplicate feature names.

ML Labels

Labels are named boolean expressions:

sql
LABELS
  impact = future(c.latency > 500, Duration(minute, 2)),
  normal = c.cpu < 60 AND m.mem < 70

`future(condition, Duration(...))` creates a future-looking label. The real-time classifier tests use:

sql
LABELS
  impact = future(c.latency > 500, Duration(second, 10)),
  normal = c.cpu < 70 AND c.mem < 78

ML Model

The tested model syntax is:

sql
MODEL incident = TRAIN ONLINE_CLASSIFIER()
LABELS impact, normal
WITH (
  algorithm = 'online_logistic',
  learning_rate = 0.05,
  l2 = 0.001,
  min_examples = 20
)

Supported model constraints from tests and parser:

  • Only `ONLINE_CLASSIFIER()` is supported.
  • `ONLINE_CLASSIFIER()` takes no feature arguments; it uses the `FEATURES` block.
  • Model labels must reference labels defined in the `LABELS` block.
  • If parameters are omitted, defaults are supplied by the parser.

ML Compute

Prediction fields are created in `COMPUTE`:

sql
COMPUTE
  predicted_label = CLASSIFY(incident),
  confidence = PROBABILITY(incident),
  impact_confidence = PROBABILITY(incident, impact),
  normal_confidence = PROBABILITY(incident, normal)

The tests validate that output contains numeric confidence fields and that repeated real-time runs learn from an initial incident and raise impact confidence before a second incident crosses the latency threshold.

PreviousWindow Statistics With WINDOW BY ... COMPUTENextError Handling and Supported Constraints