Detect time series data exceptions continuously - Lindorm

This topic describes how to continuously detect the exceptions of time series data in LindormTSDB.

Exception detection status

Overview

Exception detection status indicates the data distribution and statistics generated by using specific algorithms in a single detection query. This status determines the accuracy of the detection results generated by the time series data detection algorithm. The exception detection status affects the accuracy of the detection results in different scenarios:

By default, when you continuously detect exceptions from time series data, the exception detection status of each detection query is retained for a period of time to ensure the accuracy of the detection results. In this case, the accuracy of the algorithm improves as more detection queries are performed and the detection results approximate the accurate values.
In other scenarios such as random detection, you can configure the common parameter adhoc_state of the exception detection algorithm to specify that the exception detection status generated for a detection query affects only the query. You can also decide whether to clear the exception detection status for a detection query immediately after the detection query is complete.

Note

The warmupCount parameter of the detection algorithm specifies the number of data points after which exceptions are reported. If you specify this parameter when you set adhoc_state to true, make sure the number of data points involved in the detection query is equal to or larger than the value of warmupCount.
If the same algorithm and parameters are used in multiple detection queries, the queries share the same exception detection status.

Examples

Example 1: Use the ESD algorithm to detect exceptions in the temperature data within a specific time range in a time series table named sensor, and specify that the exception detection status affects only the current detection query.

Note When you execute the following statement to repeatedly detect exceptions in the same data, the results remain the same.

SELECT device_id, region, time,
anomaly_detect(temperature, 'esd', 'adhoc_state=true') AS detect_result
FROM sensor WHERE device_id IN ('F07A1260') AND time >= '2022-01-01 00:00:00' AND time < '2022-01-01 00:01:00' SAMPLE BY 0;

Returned results:

+-----------+----------+---------------------------+---------------+
| device_id |  region  |           time            | detect_result |
+-----------+----------+---------------------------+---------------+
| F07A1260  | north-cn | 2022-01-01T00:00:00+08:00 | false         |
| F07A1260  | north-cn | 2022-01-01T00:00:01+08:00 | false         |
| F07A1260  | north-cn | 2022-01-01T00:00:02+08:00 | true          |
+-----------+----------+---------------------------+---------------+

Example 2: Use the ESD algorithm to continually detect exceptions in the temperature data within a specific time range in a time series table named sensor.

Note

In this example, exceptions are detected every 10 minutes.
The same algorithm and parameters are used in the continuous detection queries. Therefore, the queries share the same exception detection status. The second detection query is performed based on the exception detection status returned from the first query and returns more accurate results.

Perform the first detection query.

SELECT device_id, region, time,
anomaly_detect(temperature, 'esd') AS detect_result
FROM sensor WHERE device_id IN ('F07A1260') AND time >= '2022-01-01 00:00:00' AND time < '2022-01-01 00:10:00' SAMPLE BY 0;

Returned results:

+-----------+----------+---------------------------+---------------+
| device_id |  region  |           time            | detect_result |
+-----------+----------+---------------------------+---------------+
| F07A1260  | north-cn | 2022-01-01T00:00:00+08:00 | false         |
| F07A1260  | north-cn | 2022-01-01T00:01:00+08:00 | false         |
| F07A1260  | north-cn | 2022-01-01T00:02:00+08:00 | true          |
|                          ...........                             |
| F07A1260  | north-cn | 2022-01-01T00:09:00+08:00 | false         |
+-----------+----------+---------------------------+---------------+

Perform the second detection query.

SELECT device_id, region, time,
anomaly_detect(temperature, 'esd') AS detect_result
FROM sensor WHERE device_id IN ('F07A1260') AND time >= '2022-01-01 00:10:00' AND time < '2022-01-01 00:20:00' SAMPLE BY 0;

Returned results:

+-----------+----------+---------------------------+---------------+
| device_id |  region  |           time            | detect_result |
+-----------+----------+---------------------------+---------------+
| F07A1260  | north-cn | 2022-01-01T00:10:00+08:00 | false         |
| F07A1260  | north-cn | 2022-01-01T00:11:00+08:00 | false         |
| F07A1260  | north-cn | 2022-01-01T00:12:00+08:00 | false         |
|                          ...........                             |
| F07A1260  | north-cn | 2022-01-01T00:19:00+08:00 | true          |
+-----------+----------+---------------------------+---------------+

Create a continuous exception detection query

If the same algorithm and parameters are used in multiple exception detection queries, the queries share the same exception detection status. Therefore, the exception detection status of a continuous query is continuously updated based on the detection results. Ideally, the exception detection status covers more time series data as more detection queries are performed, and the detection results approximate accurate values.

In general, you must develop codes or use third-party tools to perform continuous exception detection queries. However, LindormTSDB supports the continuous query feature by default. You can use this feature to perform detection queries on a regular basis and write the query results to a time series table.

Syntax

CREATE CONTINUOUS QUERY [database_name.] cq_name WITH (  INTERVAL='interval_string' ) AS anomaly_detect_statement

Parameters


Parameter	Description
database_name	The name of the database whose data you want to query. By default, the query is performed on the current database if you do not specify this parameter.
cq_name	The name of the continuous query that you want to perform.
interval_string	The time interval at which the queries are performed. For example, a value of `1h30s` indicates the interval is one hour and 30 seconds. The following units are supported: d: days. h: hours. m: minutes. s: seconds.
anomaly_detect_statement	The statement used to write data, which uses the ANOMALY_DETECT function to detect exceptions.

Examples

You can execute the following statements to create a continuous query. In this example, the ESD algorithm is used to detect exceptions from the data of the last minute in a table named tsdb.some_table every minute. The detection results are written to a table named tsdb.table_anomaly_points.

CREATE CONTINUOUS QUERY tsdb.cq_detector WITH (INTERVAL = '1m') AS
INSERT INTO tsdb.table_anomaly_points
SELECT tag1, tag2, time, anomaly_detect(field, 'esd') AS result FROM tsdb.some_table
WHERE tag1 = 'cn-shanghai' SAMPLE BY 0;