Automatic Data Aggregation

Using Automatic Data Aggregation

Automatic Data Aggregation simplifies the task of performing simple processing on timestamped data. For example, a continuous series of readings from a temperature sensor can be stored in a table. The Automatic System Time function is used to add data timestamps if necessary. Automatic Data Aggregation is used to calculate average temperature readings based on this data.

Some applications, especially those in the Internet of Things, can generate a huge amount of data due to the nature of sensors, many of which produce a constant stream of readings. This may result in more data than can be stored locally on a small device. It may be impractical to send all of the raw data to the cloud for storage or processing due to bandwidth considerations. Developers may be interested in storing data that has been aggregated over a given amount of time. For example, a temperature sensor may produce readings many times in a minute, however the moving window average of those readings may be more meaningful than individual data points and requires far less storage.

Automatic Data Aggregation provides developers and DBAs an easy solution for aggregating and storing data.

Automatic Data Aggregation monitors Automatic System Time fields, aggregates data record values within a given amount of time (user specified sample window), and stores defined aggregated values in an aggregation table.

How to Use Automatic Data Aggregation

This plug-in feature is enabled by adding the following server configuration in ctsrvr.cfg:

PLUGIN cttimestamp;<cttimestamp dynamic library>

where:

<cttimestamp dynamic library> is the path and filename of the plug-in, e.g., server\aggregation\ctTimeStamp.dll.

This plug-in is loaded at server startup and begins aggregating tables listed in the cttimestamp.json configuration file described below.

Requirements:

The table to aggregate must have at least one auto-timestamp field of type CT_TIMESTAMP.
The auto-timestamp field must be the first segment of any index on that table.

Supported Field Types - The fields to aggregate must have one of the following field types:

Signed integer types: CT_TINYINT, CT_SMALLINT, CT_INTEGER, CT_BIGINT
Unsigned integer types: CT_UTINYINT, CT_USMALLINT, CT_UINTEGER, CT_UBIGINT
Float types: CT_SFLOAT, CT_EFLOAT, CT_DFLOAT

In This Chapter

Configuring Aggregation

The Automatic Data Aggregation feature is configured using a plug-in configuration file, cttimestamp.json. This file is located in the config folder. (A shared library is provided in the aggregation directory, e.g., server\aggregation\ctTimeStamp.dll.) The format of this JSON file is as follows:

{

"debug": false,

"sourceTableList": [

{

"database": "ctreeSQL",

"sourceTableName": "sensor",

"aggregatedTableList": [

{

"aggregatedTableName": "aggregation1",

"timestampFieldName": "ts",

"unitOfTime": "second",

"unitOfTimeValue": 20,

"aggregatedTableInSQL": true,

"aggregatedTableOwner": "admin",

"aggregatedFieldList": [

{

"aggregatedFieldName": "TempAverage",

"sourceFieldName": "temperature",

"aggregateFunction": "average"

}

]

}

]

}

]

}

cttimestamp.json attributes

debug: <boolean> Enable timestamp/aggregation debugging. true or false.
sourceTableList: <array> Array of source tables.
database: <string> c-tree database name to contain the tables.name
sourceTableName: <string> Source table to aggregate from.
aggregatedTableList: <array> Array of aggregation storage tables.
- aggregatedTableName: <string> Name of aggregation table
- timestampFieldName: <string> Source table field name with auto-timestamp value to monitor.
The following pair of parameters sets the width of the aggregation sample window, in units of time. For example, setting "unitOfTime: "minute", and "unitOfTimeValue": "3", creates a 3-minute aggregation window
wherein the aggregation function is applied to each field in aggregatedFieldList.
- unitOfTime: <string> Time unit of the aggregation frequency.
  Supported values: “second”, “minute”, “hour”, “day”, “month” and “year”.
- unitOfTimeValue: <numeric> Time value of the aggregation frequency.
- aggregatedTableInSQL: <boolean> Indicate if aggregation table is available via SQL
  Values: "true", "false".
- aggregatedTableOwner: <string> Set SQL owner of aggregation table. By default, this is inherited from the source table or "admin".
- aggregatedFieldList: <array> Array of aggregated values to include in aggregation table. Each value is one field in the table.
  - aggregatedFieldName: <string> Aggregated field name to store in this aggregation table
  - sourceFieldName: <string> Field name from source table to aggregate.
  - aggregateFunction: <string> Aggregation function applied to the source data.
    Supported functions and storage type:
"average" - The arithmetic mean of the values in the period. (DOUBLE)

"count" - The number of entries being aggregated. (BIGINT)

"minimum" - The minimum value of the period. (Same as source field type)

"maximum" - The maximum value of the period. (Same as source field type)

"stddev" - The standard deviation of the values during the period. (DOUBLE)

"sum" - Total of the aggregated values in the period. (Same as source field type)

Example

{