Skip to main content

Data aggregation

Configure data to aggregate automatically

Abstract

Automatic data aggregation simplifies the task of performing simple processing on timestamped data

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 (IoT), 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 with 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.

Requirements:
  • The table that is to be aggregated must have at least one CT_TIMESTAMP auto-timestamp field.

  • The auto-timestamp field must be the first segment of any index on that table.

Supported 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

Configure aggregation

The automatic data aggregation feature is configured using a plugin configuration file, cttimestamp.json.

  1. Navigate to and open the cttimestamp.json file in the config folder (server\aggregation\ctTimeStamp.dll).

  2. Observe the format of the cttimestamp.json file:

    {
        "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"
                            }
                        ]
                    }
                ]
            }
        ]
    } 

Example

{
    "sourceTableList": [
        {
            "database": "ctreeSQL",
            "sourceTableName": "sensor",
            "aggregatedTableList": [
                {
                    "aggregatedTableName": "aggregation1",
                    "timestampField": "ts",
                    "unitOfTime": "second",
                    "unitOfTimeValue": 20,
                    "aggregatedFieldList": [
                        {
                            "aggregatedFieldName": "TempAverage",
                            "sourceFieldName": "temperature",
                            "aggregateFunction": "average"
                        },
                        {
                            "aggregatedFieldName": "PressMax",
                            "sourceFieldName": "pressure",
                            "aggregateFunction": "maximum"
                        }
                    ]
                } 
            ]
        }
    ]
}

Property summary

Table 1. cttimestamp.json property summaries

Property

Description

Default

Type

Limits (inclusive)

"debug"

enables timestamp/aggregation debugging

true

Boolean

true
false

"sourceTableList"

contains an array of source tables

array



The "sourceTableList" array contains a list of source tables.

Property summary

Table 2. "sourceTableList" property summaries

Property

Description

Default

Type

Limits (inclusive)

"database"

contains the c-tree database name to contain the tables.name

string

"sourceTableName"

contains the source table to aggregate from

string

"aggregatedTableList"

contains a list of storage tables

array



Property summary
Table 3. "aggregatedTableList" properties summary

Property

Description

Default

Type

Limits (inclusive)

"aggregatedTableName"

defines the name of an aggregation table

string

"timestampFieldName"

defines the source table field name with auto-timestamp value to monitor

string

"unitOfTime"

defines the time unit of the aggregation frequency

Note

This parameter 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".

string

"unitOfTimeValue"

defines the time value of the aggregation frequency

Note

This parameter 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".

integer

"aggregatedTableInSQL"

indicates if the aggregation table is available

Boolean

true
false

"aggregatedTableOwner"

sets the SQL owner of the aggregation table

Inherited from the source table or "admin"

string

"aggregatedFieldList"

contains a list of aggregated values to include in the aggregation table where each value is one field in the table

array