Continuous Query

Continuous queries are queries that TDengine automatically executes on a regular basis, and are computed in a sliding window manner, which is a simplified, time-driven streaming computation. For tables in the library or supertables, TDengine provides periodic, automatic, continuous queries that can be pushed by TDengine or written back to TDengine. Each query executed is a time window that slides forward over time. When defining continuous query, you need to specify the time window size (interval) and each forward sliding times (parameter sliding).

TDengine’s continuous queries are time-driven and can be defined directly using TAOS SQL with no additional operations. Continuous query can easily and quickly generate results according to time window, so as to carry out Down sampling on the original collected data. After a user defines a continuous query using TAOS SQL, TDengine automatically pulls the query at the end of the last complete time period and pushes the calculated results to the user or writes them back to TDengine.

The continuous query provided by TDengine differs from the time window computation in normal flow computation in the following ways:

  • Unlike the real-time feedback results of stream computation, continuous queries are computed only after the time window is closed. For example, if the time period is 1 day, the result of the current day is generated only after 23:59:59.
  • If a history is written to a time interval that has been calculated, a continuous query does not recalculate or push the results back to the user. For schemas written back to TDengine, existing calculations are also not updated.
  • With the continuous query push result mode, the server does not cache the state of the client computation, nor does it provide the exact-once semantic guarantee. If the user’s application crashes, the successive queries that are pulled up again will recalculate only the most recent full time window from the time they were pulled up again. If you use write back mode, TDengine ensures that data is written back efficiently and continuously.

Using continuous queries

Using TAOS SQL to define the process of continuous query, you need to call API TAOS_Stream to start continuous query on the application side. For example, to count the number of records in the FOO_TABLE table every minute, slide forward time is 30 seconds, SQL statement as follows:

SELECT COUNT(*) 
FROM FOO_TABLE 
INTERVAL(1M) SLIDING(30S)
Copy the code

The query time window is 1 minute, and the forward sliding time is 30 seconds. Sliding can also be used to specify the forward sliding time. In this case, the system will automatically slide a query time window forward before starting the next calculation, that is, the length of the time window is equal to the forward sliding time.

SELECT COUNT(*) 
FROM FOO_TABLE 
INTERVAL(1M)
Copy the code

To write the calculation results of continuous queries back to the database, use the following SQL statements

CREATE TABLE QUERY_RES 
  AS 
  SELECT COUNT(*) 
  FROM FOO_TABLE 
  INTERVAL(1M) SLIDING(30S)
Copy the code

The system automatically creates the table QUERY_RES and writes the results of successive queries to the table. It should be noted that the forward sliding time cannot be greater than the time window. If the time specified by the user exceeds the time window range, the system automatically sets it to the time window range. In the PRECEDING SQL statement, if you set the forward sliding time to more than 1 minute, the system forcibly sets the forward sliding time to 1 minute.

In addition, TDengine allows users to specify an end time for continuous queries. By default, if no end time is entered, continuous queries are permanently run. If you specify an end time, continuous queries stop running after the system time reaches the specified time. As shown in SQL, the continuous query will run for one hour, after which the continuous query automatically stops.

CREATE TABLE QUERY_RES 
  AS 
  SELECT COUNT(*) 
  FROM FOO_TABLE 
  WHERE TS > NOW AND TS <= NOW + 1H 
  INTERVAL(1M) SLIDING(30S) 
Copy the code

In addition, it is important to note that the minimum value of the query time window is 10 milliseconds, and there is no upper limit of the time window range.

Managing Continuous Query

You can run the show Streams command in the console to view all the continuous queries running in the system, and kill the corresponding continuous queries by running the kill Stream command. In write back mode, if the user can directly delete the written table, the continuous query is automatically stopped and closed. Later versions will provide more fine-grained and convenient continuous query management commands.