Metrics Engine

In-memory SQL compliant metrics engine for advance analytics calculations, features or alert based triggers

---

This guide explains how to use the Metrics Engine by defining SQL expressions that store metrics and the runtime frequency using a policy.

Joule provides a SQL compliant metrics engine. The engine computes SQL query based metrics using a runtime policy using the events stored within auto generated event tables.

Key features

• ANSI SQL compliant metrics calculation definition

• Apply calculated metrics within processors

• Pre-computed metrics import

• Runtime policy

• Metric management

Powered by the DuckDB In-memory column database

Example

Below computes a single family of metrics BidMovingAverage at set time intervals and saved to an in-memory standardQuoteAnalyticsStream.BidMovingAverage SQL table. The emit definition performs a query lookup against eh table and returns the avg_bid_max for each matching symbol.

processing unit:
  metrics engine:
    runtime policy:
      frequency: 1
      startup delay: 2
      time unit: MINUTES

    foreach metric compute:
      metrics:
        - name: BidMovingAverage
          metric key: symbol
          table definition: standardQuoteAnalyticsStream.BidMovingAverage (symbol VARCHAR, avg_bid_min FLOAT, avg_bid_avg FLOAT,avg_bid_max FLOAT)
          query:
            SELECT symbol,
            MIN(bid) AS 'avg_bid_min',
            AVG(bid) AS 'avg_bid_avg',
            MAX(bid) AS 'avg_bid_max'
            FROM standardQuoteAnalyticsStream.quote
            WHERE
            ingestTime >= date_trunc('minutes',now() - INTERVAL 2 MINUTES) AND ingestTime <= date_trunc('minutes',now())
            GROUP BY symbol
            ORDER BY 1;
          truncate on start: true
          compaction policy:
            frequency: 8
            time unit: HOURS

emit:
  select: "symbol, BidMovingAverage.avg_bid_max;WHERE symbol=${symbol} 'avg_bid_max'"

group by:
  - symbol