Pre-downsampling
Pre-downsampling precomputes data at a specified time granularity as the data is written and stores the results separately. During a downsampling query, LindormTSDB automatically queries matching pre-downsampled data to reduce query latency. This topic introduces the concept of pre-downsampling, how to create and manage pre-downsampling rules, how to query pre-downsampled data, and best practices.
Prerequisites
The LindormTSDB version of the Lindorm instance is 3.4.19 or later.
For more information about how to view and upgrade the LindormTSDB version of the Lindorm instance, see Release notes of LindormTSDB and Upgrade the minor engine version of a Lindorm instance.
Overview
Pre-downsampling
Pre-downsampling is a precomputation method. When data is written, the system downsamples it according to the configured rules and stores the downsampled data separately. During a query, the system automatically selects the pre-downsampled data whose time granularity is closest to the granularity specified in the query, then sends that data to the execution engine for further processing. This reduces the amount of data that must be processed in real time and lowers query latency.
Compared with continuous queries, pre-downsampling has the following benefits:
Pre-downsampled data is available as soon as it is written, without the execution delay associated with continuous queries.
Pre-downsampling provides high performance without affecting write performance because the downsampled data is stored in real time during writes.
Pre-downsampled data and raw data are stored in the same table. During a pre-downsampling query, the system automatically selects the data that best matches the configured rule. If no rule matches, the query falls back to the raw data.
Pre-downsampling rules
Pre-downsampling rules specify the time granularity and aggregation method. For example, if one row of raw data is generated every minute, a rule can pre-downsample the data to hourly averages. Rules can be configured at the database or table level. Both types support pre-downsampling but differ in some aspects, as shown in the following figure.
Create a pre-downsampling rule
Pre-downsampling rules do not apply to data that was written before the rules were created.
Create a pre-downsampling rule for a table by using SQL statements
Examples
Set the pre-downsampling interval to one hour and precompute the sum and max aggregates. You can test the following statement:
CREATE PREDOWNSAMPLE `1h` AGGREGATORS (`sum`, `max`) ON sensor;Set the pre-downsampling interval to one hour, precompute the sum and max aggregates, and set the TTL of the pre-downsampled data to 90 days. You can test the following statement:
CREATE PREDOWNSAMPLE `1h` AGGREGATORS (`sum`, `max`) TTL `90d` ON sensor;
Syntax reference
Create a pre-downsampling rule for a database by using API operations
Request path and method
Request path | Request method | Description |
/api/database |
| Configure a pre-downsampling rule for the specified database. If the specified database (such as the default database) already exists, use the PUT method to update the pre-downsampling rules for the database. To configure pre-downsampling rules for a database when you create the database, use the POST method. |
Request parameters
Parameter | Required | Description |
name | Yes | The database name. Example: default. |
downsampleRules | Yes | The list of pre-downsampling rules that you want to configure. Each time you update the pre-downsampling rules of the database, all rules in the list are updated. |
Parameters for a single pre-downsampling rule
Parameter | Required | Description | Example |
aggregator | Yes | The aggregate function used in pre-downsampling. Valid values: sum, count, max, min, first, last, and median. For more information about aggregate functions, see CREATE PREDOWNSAMPLE. Important
| "aggregator": "sum" |
intervals | Yes | The list of time intervals based on which pre-downsampling is performed. The following units are supported: s (second), m (minute), h (hour), and d (day). | For example, if the parameter is set to ["10m", "1h"] , the raw data is separately pre-downsampled based on an interval of 10 minutes and an interval of one hour. |
Sample request
PUT /api/database
{
"name": "default",
"downsampleRules": {
"rules": [
{
"aggregator": "sum",
"intervals": [
"1m",
"1h"
]
},
{
"aggregator": "max",
"intervals": [
"1m",
"1h"
]
}
]
}
}Response description
If the request is successful, the HTTP status code 200 is returned.
Query pre-downsampling rules
Use API operations to query database-level rules and SQL statements to query table-level rules.
Query pre-downsampling rules configured for a table by using SQL statements
Example
Query all pre-downsampling rules configured for all tables in a database.
SHOW PREDOWNSAMPLES;Query all pre-downsampling rules configured for a table named sensor.
SHOW PREDOWNSAMPLES ON sensor;
Syntax reference
Query pre-downsampling rules configured for a database by using API operations
Request path and method
Request path | Request method | Description |
/api/database | GET | Query the pre-downsampling rules configured for a database. |
Sample response
[{
"name": "default",
"downsampleRules": {
"rules": [
{
"aggregator": "sum",
"intervals": [
"1m",
"1h"
]
},
{
"aggregator": "max",
"intervals": [
"1m",
"1h"
]
}
]
}
}]Response description
If the request is successful, the HTTP status code 204 is returned.
Delete a pre-downsampling rule
Use API operations to delete database-level rules and SQL statements to delete table-level rules.
After you delete a pre-downsampling rule, data that has been pre-downsampled based on the rule is not immediately deleted. However, the data is not returned when you query pre-downsampled data. Pre-downsampled data is automatically deleted after it expires.
Delete pre-downsampling rules configured for a table by using SQL statements
Example
DROP PREDOWNSAMPLE `1h` AGGREGATORS (`sum`, `max`) ON sensor;Syntax reference
Delete pre-downsampling rules configured for a database by using API operations
The request path, request method, and request parameters that you need to configure to delete pre-downsampling rules for a database are the same as those when you create a pre-downsampling rule for a database. However, you need to set the downsampleRules field to empty. The HTTP status code that is returned in the response is also the same as that when you create a pre-downsampling rule for a database. For more information, see Create a pre-downsampling rule for a database by using API operations.
Sample request
PUT /api/database
{
"name": "default",
"downsampleRules": {
}
}Query pre-downsampled data
Matching rules for pre-downsampled data
If pre-downsampling rules are configured for a database and a table in the database at the same time, the rules configured for the table prevail in pre-downsampling queries.
If the downsampling interval specified in a query does not exactly match an available pre-downsampling interval, the system automatically selects the closest matching interval. We recommend configuring intervals of 1m, 10m, 1h, and 1d to cover most downsampling query scenarios.
NoteThe best-matching interval is the largest available pre-downsampling interval that evenly divides the interval specified in the query. For example, if pre-downsampled data is available at 1m, 5m, 8m, and 15m and a query specifies 10m, the 5m interval is selected.
If no pre-downsampling interval matches the query, the query uses the raw data.
Query methods
You can use SQL statements or methods that are compatible with Time Series Database (TSDB) to query pre-downsampled data. We recommend that you use SQL statements to query pre-downsampled data. If your application is built based on OpenTSDB or TSDB, you can use methods that are compatible with TSDB to query pre-downsampled data.
Use SQL statements to query pre-downsampled data
By default, downsampling queries read the raw data. To query pre-downsampled data, add the /*+ PREDOWNSAMPLE */ hint to the query statement.
SELECT /*+ PREDOWNSAMPLE */ sum(temperature) from sensor
WHERE time >= 1619074800000 AND time <= 1619085600000
SAMPLE BY 1h;Use TSDB-compatible methods to query pre-downsampled data
You can use API-based multi-value queries, API-based single-value queries, and SDKs to query pre-downsampled data.
Use SDKs to query pre-downsampled data
To query pre-downsampled data with the SDK, set the DownsampleDataSource parameter in the MultiFieldSubQuery builder.
Example
long startTime = 1619074800000L;
long endTime = 1619085600000L;
final String metric = "wind";
final String field = "speed";
MultiFieldSubQueryDetails fieldSubQueryDetails = MultiFieldSubQueryDetails
.field(field)
.aggregator(Aggregator.SUM)
.downsample("1h-sum")
.build();
MultiFieldSubQuery subQuery = MultiFieldSubQuery
.metric(metric)
.fieldsInfo(fieldSubQueryDetails)
// Query pre-downsampled data.
.downsampleDataSource(DownsampleDataSource.DOWNSAMPLE)
.build();
MultiFieldQuery query = MultiFieldQuery.
start(startTime).
end(endTime).
sub(subQuery).
build();
List<MultiFieldQueryResult> result = tsdb.multiFieldQuery(query); Parameters
You can set the DownsampleDataSource parameter to one of the following values:
DOWNSAMPLE: Query the pre-downsampled data.
RAW: Query the raw data. The default value of this parameter is RAW.
For more information about how to use SDK multi-value data model to query pre-downsampled data, see Use the SDK multi-value data model to read data.
Query pre-downsampled data by using API-based multi-value queries
When you use API-based multi-value queries to query pre-downsampled data, you must add the downsampleSource parameter and set it to downsample. For more information, see Query multi-value data.
Request path and method
Request path | Request method | Description |
/api/mquery | GET | Query pre-downsampled data. |
Request parameters
Parameter | Required | Description | Example |
downsampleSource | No | The data source that is queried. Valid value:
| downsample |
Sample request
POST /api/mquery
{
"start": 1346846400000,
"end": 1346846402000,
"queries": [
{
"downsampleDataSource" : "downsample",
"metric": "wind",
"fields" : [
{
"field" : "speed",
"aggregator" : "none",
"downsample" : "1h-sum"
}
],
"tags":{
"sensor":"IOTE_8859_0002"
}
}
]
}Best practices for pre-downsampling
Pre-downsampling for data sampled at an interval of seconds
For data sampled every second, we recommend configuring pre-downsampling rules at 1m, 1h, and 1d intervals. These rules support downsampling queries at minute, hour, and day granularities.
Sample pre-downsampling rules
Configure pre-downsampling rules for the four commonly used aggregate functions—sum, count, min, and max—at intervals of 1m, 1h, and 1d.
CREATE PREDOWNSAMPLE `1m` AGGREGATORS (`sum`, `count`, `min`, `max`) ON sensor;
CREATE PREDOWNSAMPLE `1h` AGGREGATORS (`sum`, `count`, `min`, `max`) ON sensor;
CREATE PREDOWNSAMPLE `1d` AGGREGATORS (`sum`, `count`, `min`, `max`) ON sensor;Sample pre-downsampling queries
Use the rule configured with a 1-minute interval for downsampling queries at any minute-level interval.
SELECT /*+ PREDOWNSAMPLE */ sum(temperature), max(temperature) FROM sensor WHERE time >= 1619074800000 AND time <= 1619085600000 SAMPLE BY <N>m;Use the rule configured with a 1-hour interval for downsampling queries at any hour-level interval.
SELECT /*+ PREDOWNSAMPLE */ sum(temperature), max(temperature) FROM sensor WHERE time >= 1619074800000 AND time <= 1619085600000 SAMPLE BY <N>h;Use the rule configured with a 1-day interval for downsampling queries at any day-level interval.
SELECT /*+ PREDOWNSAMPLE */ sum(temperature), max(temperature) FROM sensor WHERE time >= 1619074800000 AND time <= 1619600400000 SAMPLE BY <N>d;
Pre-downsampling for data sampled at an interval of minutes
For data sampled every minute, we recommend configuring pre-downsampling rules at 1h and 1d intervals to support downsampling queries at any hour- or day-level interval.
Sample pre-downsampling rules
Configure pre-downsampling rules for the four commonly used aggregate functions—sum, count, min, and max—at intervals of 1h and 1d.
CREATE PREDOWNSAMPLE `1h` AGGREGATORS (`sum`, `count`, `min`, `max`) ON sensor;
CREATE PREDOWNSAMPLE `1d` AGGREGATORS (`sum`, `count`, `min`, `max`) ON sensor;Sample pre-downsampling queries
Use the rule configured with a 1-hour interval for downsampling queries at any hour-level interval.
SELECT /*+ PREDOWNSAMPLE */ sum(temperature), max(temperature) FROM sensor WHERE time >= 1619074800000 AND time <= 1619085600000 SAMPLE BY <N>h;Use the rule configured with a 1-day interval for downsampling queries at any day-level interval.
SELECT /*+ PREDOWNSAMPLE */ sum(temperature), max(temperature) FROM sensor WHERE time >= 1619074800000 AND time <= 1619600400000 SAMPLE BY <N>d;