Time series

Limited access

This feature is only available to root and super_admin_domain profiles. Ask your administrator for proper user role profiling.

What a time series is

A time series turns the stream of values a device sends into a table of rows over time: one row per device per time period, with each column holding a value aggregated over that period.

Ask for the average temperature per hour of ten thousand devices for the last month. Over data points that is millions of raw values to fetch and aggregate yourself. Over a time series it is already computed — the engine aggregated each hour as the data arrived.

That is the trade: you declare up front what you want aggregated and how, and in exchange the query is cheap.

Time buckets

The aggregation period is called a time bucket, and two fields define it:

Field	Meaning
`origin`	The starting date of the time series
`timeBucket`	The length of each period in seconds, counted from `origin`

With an origin of 2022-01-01T00:00:00.000Z and a one hour bucket, the first period runs from 2022-01-01T00:00:00.001Z to 2022-01-01T01:00:00.000Z, the second from 2022-01-01T01:00:00.001Z to 2022-01-01T02:00:00.000Z, and so on:

2022-01-01T00:00:00.000Z -> 2022-01-01T01:00:00.000Z -> 2022-01-01T02:00:00.000Z ->...

Setting timeBucket to 0 seconds switches the engine into a different mode, storing every value instead of aggregating:

With only context columns defined, one record is saved per event received, and only when a column value actually changed — so you get a change log over time.
With aggregated columns defined, data is grouped by the at field of the incoming data points, and the aggregation function is applied when a new event arrives with the same at.

Which store do I want?

	Time series	Data points	Data sets
Holds	Values aggregated per period	Every raw value	Latest values, flat table
Rows	One per device per bucket	One per measurement	One per device
Aggregation	Computed on ingestion	You compute it	None
Best for	Trends and history at scale	Auditing exact readings	Exports and tabular views

The two halves of the API

Defining a time series is administration: you declare the columns, their aggregation functions, the bucket length and the retention. Done once, usually by an administrator.

Querying a time series is what applications do every day: POST a filter and read rows back, as JSON or CSV.

The aggregation functions available to columns come from the time series functions catalog, which also lets you register your own.

API specification

Defining a time series

Defining a time series is declaring, up front, what the engine should compute as data arrives. This is administration work: done once, changed rarely, and with consequences for data already stored — the last section of this page covers those.

Columns

A time series has four kinds of column, and only the first two are yours to name freely.

Aggregated columns (`columns`)

These hold a value aggregated over each time bucket. Each one names an aggregation function, and the engine re-applies it every time new data lands in an existing row. The available functions come from the time series functions catalog.

When timeBucket is 0, all received data is stored instead of aggregated, and only FIRST (keep the first value) or LAST (overwrite with the newest) make sense. Two consequences worth knowing:

The search endpoint returns all the historical data collected.
Several data streams can land in different columns of the same row when they share an at value.

When timeBucket is greater than 0, bucketColumn becomes required: it names the column the engine adds to search responses holding the end date of each bucket.

Context columns (`context`)

Context columns capture the value at the moment the bucket was created, and are never updated afterwards even if the aggregated columns keep changing. That is why they take no aggregation function. Use them for the things you want to know about the device at that point in time — its serial number, its firmware version, its subscription — so that a row is self-describing.

Identifier column (`identifierColumn`)

Required. It names the column that identifies the device, and always maps to provision.administration.identifier._current.value with filter=YES. The engine adds it to every row of every search result, using the name you chose.

Bucket columns (`bucketColumn`, `bucketInitColumn`)

Named by you, filled by the engine, holding the end and start instants of the bucket.

The `path` field

Every column and context needs a path, which the engine uses as a query to extract a data stream value and project it into the column. A path has two or three parts:

1. The data stream identifier — a data stream defined in an OpenGate data model.

Communication modules need an index

If the data stream id contains communicationModules[], the index is required: device.communicationModules[0].subscription.mobile.imsi

2. The data stream field — appended with a dot, one of:

_current.value · _current.date · _current.at · _current.feedId · _current.source · _current.sourceInfo

3. The value path — only when the data stream holds a JSON object or array, a JSONPath down to a primitive value.

What a column can be filtered by

Every column and context carries a filter value that decides how queries may use it:

Value	Meaning
`NO`	Not filterable. The default
`YES`	Optional equality filter
`ALWAYS`	Required equality filter: every query must constrain this column
`RANGE`	Range filter, `>`, `<` and `BETWEEN`, as well as equality

RANGE is only allowed on numeric columns, integer and number. Columns of type date-time are always range-searchable whatever the value says, so you do not need RANGE for a bucket or a timestamp.

Retention

retention sets how long rows stay in the time series, in seconds. It cannot exceed the retention allowed by your organization’s policies.

The `sorts` section

Sorting is declared in the definition, not composed at query time. The sorts section holds a list of named sorts, each one an ordered list of columns with a direction, and a query then asks for a sort by its identifier.

"sorts": [
  {
    "identifier": "signalStrengthAsc",
    "description": "Sort by average signal strength ascending",
    "columns": [
      { "name": "Average Signal strength", "direction": "ASC" }
    ]
  },
  {
    "identifier": "bucket_id_desc",
    "columns": [
      { "name": "bucket_id", "direction": "DESC" }
    ]
  }
]

Field	Rules
`identifier`	Required, unique within the list. Letters, digits, spaces, `_` and `-`. Generated as a UUID if you omit it, which makes it awkward to use, so name it
`description`	Optional free text, for whoever reads the definition later
`columns`	Required, at least one. Each entry is a column `name` from the `columns` or `context` sections plus a `direction` of `ASC` or `DESC`

At least one sort is mandatory. Order matters inside columns: the list is the sort precedence.

The reverse of every sort comes for free

For each sort you declare, OpenGate also exposes its reverse, served by the same index through a reverse scan. Those appear in the definition marked derived: true, which is read-only: the platform sets it and the web console uses it. Never declare a derived sort yourself on create or update — flip the direction of an existing one and you are duplicating an index you already have.

Filtering and sorting limits

There is no fixed maximum number of filterable columns or declared sorts. Instead, each filterable column, each context and each declared sort consumes optimization units, and each time series has a budget of them. That budget is the real limit, and it is what keeps queries fast.

Where to look	What it tells you
The `searchOptimizationInfo` section of a time series	`usedSearchOptimizationUnits` and `freeSearchOptimizationUnits`
`POST .../optimizationPlan`	What a definition would consume, before you commit to it

Simulate with optimizationPlan while you are still designing. It is much cheaper than discovering the budget is spent after the fact — and since the reverse of each sort is free, declaring both directions wastes units for nothing.

Creating and updating

Creating a time series starts collecting data from devices into it. Updating one is where care is needed: changes can affect the data already stored or its structure, which triggers an adaptation process. Until that process finishes, dirty values can be present.

These fields can be modified:

Name · Description · IdentifierColumn · BucketColumn · BucketInitColumn · Retention · TimeBucket · Context · Columns · Sorts

Simulate the update before applying it

PUT accepts an onlyPlan query parameter. With onlyPlan=true nothing is modified: the response is a plan that summarizes the changes you asked for and explains their consequences, warning you where necessary. Default is false.

Use it on any non-trivial change. It is the difference between reading about dirty values and causing them.

Rules for columns and contexts:

Names are unique across all columns, contexts, the identifier and both bucket columns. You cannot rename something to a name already in use.
filter: ALWAYS is immutable. You cannot add or remove a column or context that has it, you cannot set it on an existing one, and you cannot change it away once it is set.
Paths cannot be edited. Remove the column and create it again, which gets you the same result.

Creation example

A daily time series (timeBucket: 86400) retained for 30 days, with one context column and one aggregated column summing the bytes a device sent:

curl --request POST \
     --header "X-ApiKey: <your-api-key>" \
     --header "Content-Type: application/json" \
     --data @timeserie.json \
     https://api.opengate.es/north/v80/timeseries/provision/organizations/{organizationName}

Content of timeserie.json:

{
  "name": "basic_timeserie",
  "organizationId": "organizationName",
  "description": "time series description",
  "timeBucket": 86400,
  "retention": 2592000,
  "origin": "2021-01-01 00:00:00+00:00",
  "bucketColumn": "bucket_id",
  "identifierColumn": "Admin Identifier",
  "context": [
    {
      "path": "provision.device.serialNumber._current.value",
      "name": "Prov serial",
      "filter": "YES"
    }
  ],
  "columns": [
    {
      "path": "device.communicationModules[].subscription.traffic.sentBytes._current.value",
      "name": "Daily sent bytes",
      "filter": "NO",
      "aggregationFunction": "SUM"
    }
  ],
  "sorts": [
    {
      "identifier": "bucket_id_desc",
      "description": "Most recent bucket first",
      "columns": [
        { "name": "bucket_id", "direction": "DESC" }
      ]
    }
  ]
}

The reverse of that sort, oldest bucket first, is available without declaring it.

Changing the time bucket

Buckets have a fixed length, so changing timeBucket changes the length of new ones. As a precaution, buckets in the future are deleted when you do this. The situations below are the edge cases worth understanding before you change it on a live time series.

Buckets that started before the change and end after it

The engine closes them at the instant of the update, and if needed the next bucket starts at that same instant and runs until the following one would start according to the new definition.

Changing time bucket to a lower value Changing the time bucket to a lower value

Changing time bucket to a bigger value Changing the time bucket to a bigger value

Devices with no buckets yet

Adaptation only applies to devices that collected data before the change. A device whose first data arrives after the update simply gets a bucket following the new definition.

Changing time bucket before first device data collection Changing the time bucket before the first data collection of a device

Both at once

Combine the two and different devices end up with buckets that do not line up with each other. A device can also collect data belonging to a bucket in the past: if that bucket exists the engine uses it as is, otherwise it creates a new one following the new definition. Both are the price of changing the bucket length.

Two devices with different buckets after changing the time bucket

Two devices with different buckets in the past after changing the time bucket

From zero to a higher value

Going from timeBucket: 0 to a real length means each collection now creates a bucket of the new length. Zero-length buckets that the new bucket would overlap are absorbed rather than left behind, and their values feed the aggregation functions of each column.

Changing time bucket from zero to higher value Changing the time bucket from zero to a higher value

Deleting

DELETE /north/v80/timeseries/provision/organizations/{organizationName}/{identifier}

Removes the time series identified in the URL. Confirm the identifier before sending the request.

Querying a time series

Reading a time series is a POST with a JSON body, like every other OpenGate query:

POST /north/v80/timeseries/provision/organizations/{organizationName}/{identifier}/data

Copy this and change the identifiers:

curl --request POST \
     --header "X-ApiKey: <your-api-key>" \
     --header "Content-Type: application/json" \
     --data '{"filter": {"eq": {"Prov Identifier": "MyDevice1"}}, "sort": "EntityAscBucketDesc"}' \
     https://api.opengate.es/north/v80/timeseries/provision/organizations/{organizationName}/{identifier}/data

The response is a columns array naming the fields and a data array of rows in that order:

{
  "page": { "number": 26 },
  "columns": ["Bucket id", "Prov identifier", "Manufacturer", "ICC", "Daily sent bytes", "Daily received bytes", "Last presence", "Average Signal strength"],
  "data": [
    ["2021-04-06T12:00:00.000Z", "MyDevice1", "OpenGate", "icc1", 23500, 532, "IP", 75],
    ["2021-04-06T12:01:00.000Z", "MyDevice1", "OpenGate", "icc1", 3500, 14532, "IP", 65]
  ]
}

The request body

Clause	Accepts
`filter`	The standard operators, keyed by `bucketColumn`, `identifierColumn`, `columns.name` or `context.name`
`sort`	A string: the `identifier` of one of the sorts declared in the time series, not a list of fields
`select`	The same keys as `filter`
`limit`	`start` and `size`, as everywhere else

Two things differ from a plain Data Lake search, and both come from the time series being pre-computed: you can only filter on columns declared filterable, and you can only sort by sorts declared in the definition. See Defining a time series for how those are declared, and Query dialects for the full comparison.

Column order when you omit `select`

columns tells you the order, so read values off it rather than hardcoding positions. If you do depend on the order, it is:

The bucketColumn, holding the end date of the bucket
The identifierColumn, holding provision.administration.identifier._current.value
The context columns
The aggregated columns

Asking for a sort

You do not compose an ordering in the request. You name one that already exists:

{ "filter": {}, "sort": "bucket_id_desc" }

Valid values are the identifier of any sort in the time series definition, plus the automatically exposed reverse of each one. So a definition declaring bucket_id_desc gives you both directions without declaring the second.

Read the definition to see what is available — GET the time series, or use expand=sorts, and the sorts list comes back with the derived ones included. There is no fixed limit on how many sorts a definition can hold; the constraint is the optimization unit budget, described in Defining a time series.

Pagination and CSV

The response format changes what limit means, which catches people out:

Body	JSON	CSV
`{"filter": {}, "limit": {"size": 500, "start": 1}}`	500 rows from row 1	500 rows from row 1
`{"filter": {}}`	Configured default page	Everything
`{"filter": {}, "limit": {}}`	Configured default page	Error — an incomplete `limit` is rejected

CSV retrieval also turns sorting off, deliberately, so that large exports stay fast. If you need ordered output, sort downstream or read JSON.

Complete retrieval is expensive

Omitting limit in CSV mode downloads the whole time series. Page it unless you truly want everything.

The CSV formatting itself — quoting character, escape character, end-of-line sequence and how nulls are represented — is set through HTTP header options, and you are responsible for the result being well-formed. The exact header names are not currently published, so ask your platform contact for them.

Aggregated read: one row per device

Besides reading buckets, you can collapse every bucket of a device into a single row:

POST /north/v80/timeseries/provision/organizations/{organizationName}/{identifier}/dataset

Here select.columns describes the output variables, each with the source column, an alias for the output name, and the aggregation function to apply across buckets:

{
  "filter": {
    "gt": { "bucket_id": "device_200" }
  },
  "limit": { "start": 1, "size": 50 },
  "select": {
    "columns": [
      { "column": "temperature", "alias": "first", "aggregation": "FIRST" },
      { "column": "temperature", "alias": "last",  "aggregation": "LAST" },
      { "column": "temperature", "alias": "avg",   "aggregation": "AVG" },
      { "column": "temperature", "alias": "max",   "aggregation": "MAX" },
      { "column": "temperature", "alias": "min",   "aggregation": "MIN" },
      { "column": "cpu",         "alias": "p_avg", "aggregation": "AVG" },
      { "column": "cpu",         "alias": "p_count", "aggregation": "COUNT" }
    ]
  }
}

filter and limit behave as above, and CSV output is available too. Two rules are specific to this endpoint:

The output is always sorted ascending by identifierColumn.
The identifierColumn is always included, added as the first column if you did not ask for it.

Parquet export

For bulk analytical work, a time series can be exported to a Parquet file:

POST /north/v80/timeseries/provision/organizations/{organizationName}/{identifier}/export
GET  /north/v80/timeseries/provision/organizations/{organizationName}/{identifier}/export

POST starts the export, GET reports the state of the current one. The output order is decided internally and cannot be changed.

Time Series Functions

Limited access

This feature is only available to root and super_admin_domain profiles. Ask your administrator for proper user role profiling.

Introduction

The objective of custom time series functions is to augment the capabilities of the default functions (see Common Platform Functions) through the addition of bespoke functions written in JavaScript. These functions will be managed through the creation of a catalogue tailored to the specific requirements of each organisation.

Common Platform Functions

A common catalogue will be established for all organisations, comprising default platform time series functions. The following functions have been defined:

FIRST: Please note that the engine will store only the first received value per time bucket. Consequently, the collection engine will ignore the following values obtained in the same time bucket.
LAST: Please note that the engine will store only the last received value per time bucket, overwriting the previous ones.
AVG: The engine will calculate the arithmetic mean of all values received within the specified time interval. This feature is only available for numeric values.
MAX: The engine will save the maximum value of all received values within the configured time frame. Please note that this feature is only available for numeric values.
MIN: The engine will save the lowest value of all received values within the configured time frame. This feature is only available for numeric values.
SUM: The engine will accumulate the total of all received values within the specified time interval. This feature is only available for numeric values.
COUNT: The engine will record the total number of values received in each time bucket for subsequent analysis.
MEDIAN: The engine will calculate the median of all received values within the configured time bucket. Please note that this feature is only available for numeric values.
GEO_AVG: The engine will calculate the geometric average of all received values within the configured time bucket. Please note that this feature is only available for numeric values.
VARIANCE: The engine will calculate the variance of all received values within the configured time bucket. Please note that this feature is only available for numeric values.
STD_DEVIATION: The engine will calculate the standard deviation of all received values within the configured time bucket. Please note that this feature is only available for numeric values.

Note

Please note that these functions will be available for querying with the API defined below. However, please be aware that it will not be possible to modify or delete them.

Custom Catalog

Each organisation will have access to a custom time series functions catalogue, which will enable them to manage their functions effectively. These functions can be used to define time series columns, which can then be referenced in the aggregationFunction field. The following example illustrates this process:

{
  "name": "basic_timeserie",
  "organizationId": "organizationName",
  "description": "time series description",
  "timeBucket": 86400,
  "retention": 2592000,
  "origin": "2021-01-01T00:00:00.000Z",
  "bucketColumn": "bucket_id",
  "identifierColumn": "Admin Identifier",
  "context": [
    ...
  ],
  "columns": [
      {
          "path":"some.path.A.value._current.value",
          "name":"columnaA",
          ...
          ...
          "aggregationFunction":"AVG", //platform aggregation function
      },
      
      
      {
          "path":"some.path.B.value._current.value",
          "name":"columnaB",
          ...
          ...
          "aggregationFunction":"myCustomTimeserieFunction" //custom function
      }
  ]
}

Warning

The defined API enables the modification of the script for the custom aggregation function. It should be noted that such modifications may result in changes to the aggregated data. Consequently, there is a possibility of inconsistencies between the new aggregated data and the previous values.

Note

The defined API permits the deletion of a custom aggregation function, provided that it is not utilised in any time series.

Custom Time series Function script

Considerations when developing Custom Aggregation Function:

The following are the code’s implicit input parameters:

receivedValues: an array of new values to be used for the final value calculation.
currentValue: the column’s current value.
extra: JSON object containing the current extra variables for the column, used for the value calculation. The code should utilise implicit input values to calculate the final value and subsequently construct the result object. It will be possible to use helper functions defined in the JS API. The code must return a JSON with three specific properties:
executionResult: If the execution was completed successfully, the OK value must be returned. If not, an error description must be provided.
value: The calculated value

extra: A JSON with the updated extra variables

```json
{
  "executionResult": "OK",
  "value": 5, 
  "extra":{
    "sum": 10,
    "count": 2
  }
}
```

Note

Any datetime values included in the ‘receivedValues’ input parameter will be formatted as an ISO string.

Note

For further details on how to implement custom aggregation functions, please refer to the JavaScript API documentation, specifically the section on aggregation functions.

Functions values types

To confirm that a time series function can be assigned to a time series column, the valueTypes field will be used. This field is an array of strings that accepts the following values:

integer
number
string
boolean
date-time

These types are the same as those specified for time series column type fields. When assigning a time series function to a specific column, the system will verify that the time series function in the array matches the type specified for that column.

Note

Please note that this field is not mandatory. If it is not defined by default, it will be set to an empty array. In the event that the time series function has empty valueTypes, no validation will be carried out when assigning to a column.

Note

Please note that it will not be possible to update the time series function and remove one of the ‘valueTypes’ if the function is being used by some column whose type is the removed value. However, if all valueTypes are removed, this should not cause any issues.

Comprehensive API actions

Existing Timeseries Functions List

Getting custom timeserie functions full catalog

In both instances, only the metadata will be retrieved (no script) from both the organisations’ custom timeseries functions and the platform timeseries functions.

Usage examples

Get the full time series functions catalog (organization custom functions plus platform functions, metadata only):

curl --request GET \
     --header "X-ApiKey: <your-api-key>" \
     https://api.opengate.es/north/v80/timeseries/provision/organizations/{organizationName}/catalog

Trimmed JSON response:

[
  {
    "id": "01234567890abcdeffffffff",
    "name": "customAvg",
    "description": "Custom implementation for avg function.",
    "valueType": ["integer", "number"],
    "catalog": "ORGANIZATION"
  },
  {
    "id": "AVG",
    "name": "AVG",
    "description": "The engine will calculate the arithmetic average of all received values in the configured time bucket. Only available in numeric values.",
    "valueType": ["integer", "number"],
    "catalog": "PLATFORM"
  }
]

Create a new custom function (multipart request with a metadata JSON part and a script plain text part):

curl --request POST \
     --header "X-ApiKey: <your-api-key>" \
     --form 'metadata={"name": "customAvg", "description": "Custom implementation for avg function."};type=application/json' \
     --form 'script=@custom_avg.js;type=text/plain' \
     https://api.opengate.es/north/v80/timeseries/provision/organizations/{organizationName}/catalog

API specification

JavaScript API

Timeseries functions JS API guide

This guide describes how to write Custom Timeseries Functions and the contents of the JS API.

The API contains both the implementation of some predefined timeseries functions and some useful functions that can be used when writing Custom Aggregation Functions.

Writing Custom Aggregation Function

Input parameters

All functions will have three implicit input parameters that must be used for value calculation.

receivedValues: Array of Json of collected values. Each value will have two fields:
- value: collected value. value type depends on Column’s datastream type (please note that any datetime value will be formatted as an ISO string).
- at: datetime of collected value. This value will be defined in ISO string.
currentValue: Columns current aggregated value. The type depends on aggregation function behavior.
extra: Json with useful data for aggregated value updating. In some cases, when aggregated value must be updated, some previous auxiliary data must be used to calculate new values. The fields and their format will be defined taking into account the requirements of the function. For example, if an average data must be updated, previously received number of elements and their sum are necessary to calculate correctly new average value.

receivedValues example:

"receivedValues":[
    {
        "value": 3,
        "at": "2023-03-01T00:30:00.000Z"
    },{
        "value": 5,
        "at": "2023-03-01T01:30:00.000Z"
    },{
        "value": 6,
        "at": "2023-03-01T02:30:00.000Z"
    },{
        "value": 8,
        "at": "2023-03-01T02:30:00.000Z"
    }
];

currentValue example:

"currentValue": 4;

extra example:

"extra": {
  "sum": 8,
  "count": 2  
};

Timeserie function result

Defined function must return a result with specific format. It will be a json with two parameters:

value: New calculated value.
extra: A JSON with auxiliary data updated. The content of this JSON will be the same of the extra input parameter.

Result example:

return {
    "executionResult": "OK",
    "value": 5,
    "extra": {
      "sum": 30,
      "count": 5  
    }
};

There is an auxiliary function that takes value and extra fields as parameters and returns the json with correct format. For further description of this function check documentation.

Result example using auxiliary function:

return result.ok(5, {"sum":30}, {"count":6});

If some timeserie function execution throws an exception it will be internally caught. In this case result object will be like this:

return {
    "executionResult": "Some javascript execution error message"
}

This behavior can be forced using result.error function with an Error object or an string message. For example:

return result.error(new Error("Custom error message"));
// or
return result.error("Custom error message");

Function implementation example

Taking described Input parameters and the Result to be returned into account, AVG Aggregation Function implementation example is shown here:

var newCount = receivedValues.length;
var newSum = 0;
for(var recVal in receivedValues){
    newSum = newSum + receivedValues[recVal].value;
}
if (extra) {
    if(data.exists(extra.count)) newCount = newCount + extra.count;
    if(data.exists(extra.sum)) newSum = newSum + extra.sum;
}
var newValue = newSum / newCount;
return result.ok(newValue, {"sum":newSum}, {"count":newCount});

JS API

data.exists(value)

Aux method to check if some value has value. If value is undefined or null it will return false.

Kind: global function
Returns: Boolean - Json with result.

Param	Type	Description
value	any	value to be checked.

Example of use:

if(data.exists(receivedValues[recVal].value)){
    newSum = newSum + receivedValues[recVal].value;
}

data.undefined2null(value)

Aux method to replace all undefined fields with null values. Internally used to avoid issues when processing execution result.

Kind: global function
Returns: Any - Json with result.

Param	Type	Description
value	any	value to be checked and transformed.

Example of use:

var newValue = data.undefined2null(undefined);

console.log(newValue) // null