Configuring data aggregation

Configure data to aggregate automatically

Enable data aggregation

  1. Data aggregation is enabled by editing the config/services.json file. Set "enabled" to false as shown below:
{
		"serviceName": "aggregation",
		"serviceLibrary": "./aggregation/cttimestamp.dll",
		"enabled": false
	}
  1. Restart the server.

Requirements:

  • The table 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

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

Configure data aggregation

  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"
                        }
                    ]
                }
            ]
        }
    ]
}
  1. Edit the config/services.json file. Adjust the following section to change false to true:
{
	"serviceName": "aggregation",
	"serviceLibrary": "./aggregation/cttimestamp.dll",
	"enabled": true
}
  1. Restart the server.

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  

"sourceTableList"

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  

"aggregatedTableList"

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  

"aggregatedFieldList"

The "aggregatedFieldList" array contains a list of aggregated values to include in the aggregation table. Each value is one field in the table.

Table 4. "aggregatedFieldList" property summaries
Property Description Default Type Limits (inclusive)
"aggregatedFieldName" defines the aggregated field name to store in this aggregation table   string  
"sourceFieldName" contains the field name from the source table to aggregate   string  
"aggregateFunction"
 		 contains the aggregation function applied to the source data.Supported functions and storage type   string

"average"

"count"

"minimum"

"maximum"

"stddev"

"sum"
"aggregateFunction"

The "aggregateFunction" property specifies the aggregation function that is applied to the source data.

Supported aggregate storage types:

  • “average”
    A DOUBLE. The arithmetic mean of the values in the period.
  • “count”
    A BIGINT. The number of entries being aggregated.
  • “minimum”
    This is the same as the source field type. The minimum value of the period.
  • “maximum”
    This is the same as the source field type. The maximum value of the period.
  • “stddev”
    A DOUBLE. The standard deviation of the values during the period.
  • “sum”
    This is the same as the source field type. The total of the aggregated values in the period.
 
 
 
 

 

 
 
aggregation data

Was this article helpful?

Give feedback about this article

Related Articles