Pre-downsampling - Lindorm - Alibaba Cloud Documentation Center

Introduction

Pre-downsampling is performed to calculate data before it is written to databases. Pre-downsampling downsamples and separately stores the data to be written based on the specified pre-downsampling rule. When you query the written data, data that is downsampled at a pre-downsampling ratio that is closest to the downsampling ratio specified in the query conditions is queried. Then, the queried data is sent to and processed by the compute engine. This way, the amount of data that needs to be queried and calculated in real time is reduced and so that the query latency is decreased. To use pre-downsampling, the version of LindormTSDB must be 3.4.19 or later.

Compared with continuous queries, pre-downsampling has the following benefits:

The downsampled data can be queried immediately queried after it is written. You do not need to wait for the execution of continuous queries.
Pre-downsampling does not affect the write performance. Downsampled data is stored in real time when it is written.
The pre-downsampled data and original data is stored in the same table. When you perform pre-downsampling queries, pre-downsampled data that best matches the specified rule is queried. If the pre-downsampled data does not match the specified rule, the original data is queried.

Configure a pre-downsampling rule

A pre-downsampling rule applies only to data that is written after the rule is configured. If you configure a pre-downsampling rule for a database, the rule applies to all data in the database. You can configure different time-to-lives (TTLs) for the pre-downsampled data and original data. By default, the TTL of the pre-downsampled data is the same as that configured for the database in which the pre-downsampled data is stored.

Use SQL statements to configure a pre-downsampling rule

You can use the SQL statements in the following examples to configure a pre-downsampling rule. To configure a pre-downsampling rule that applies only to a table, you must use SQL statements to configure the rule.

Examples

Configure a pre-downsampling rule in which the pre-downsampling ratio is set to one hour and the sum and max operations are performed on the pre-downsampled data.
```
CREATE PREDOWNSAMPLE `1h` AGGREGATORS (`sum`, `max`) ON sensor;
```
Configure a pre-downsampling rule in which the pre-downsampling ratio is set to one hour, the sum and max operations are performed on the pre-downsampled data, and the TTL of the pre-downsampled data is set to 90 days.
```
CREATE PREDOWNSAMPLE `1h` AGGREGATORS (`sum`, `max`) TTL `90d` ON sensor;
```

For more information about the SQL syntax used to configure a pre-downsampling rule, see CREATE PREDOWNSAMPLE.

Use API operations to configure a pre-downsampling rule

The path and method of the API used to configure a pre-downsampling rule


Request path	Method	Description
/api/database	POST PUT	Configure a pre-downsampling rule for the specified database. If you initiate a POST request, the database is created. If the specified database already exists, you must use the PUT method to initiate a request to update the pre-downsampling rule of the database. Note A database named default is automatically created after you create a Lindorm instance. In this case, you must use the PUT method to initiate a request to update the pre-downsampling rule.

Request parameters


Parameter	Required	Description
name	Yes	The name of the database. Example: default.
downsampleRules	Yes	The list of pre-downsampling rules that you want to configure. The list of rules is specified in JSON format. 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. For more information, see Supported aggregate functions. Note The avg function is not supported in pre-downsampling. You can calculate the average value of pre-downsampled data by using the results of the count and sum functions. However, if data is repeatedly added and overwritten, the average value may be inaccurate.	sum, count, max, min, first, last, and median.
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 original data is separately pre-downsampled based on an interval of 10 minutes and an interval of one hour.

Sample requests

PUT /api/database

{
    "name": "default",
    "downsampleRules": {
        "rules": [
            {
                "aggregator": "sum",
                "intervals": [
                    "1m",
                    "1h"
                ]
            },
            {
                "aggregator": "max",
                "intervals": [
                    "1m",
                    "1h"
                ]
            }
        ]
    }
}

Response

If the request is successful, the HTTP status code 200 is returned.

Query pre-downsampling rules

You can use API operations or SQL statements to query pre-downsampling rules. If pre-downsampling rules are configured for a database and a table in the database at the same time, the rules of the table prevail when you query data. We recommend that you use SQL statements to query pre-downsampling rules.

Use SQL statements to query pre-downsampling rules

You can use the SQL statements in the following examples to query all pre-downsampling rules configured for a database or a table.

Examples

Query all pre-downsampling rules configured for a database.
```
SHOW PREDOWNSAMPLES
```
Query all pre-downsampling rules configured for a table named sensor.
```
SHOW PREDOWNSAMPLES ON sensor
```

For more information about the SQL syntax used to query pre-downsampling rules, see SHOW PREDOWNSAMPLES.

Use API operations to query pre-downsampling rules

Request path and method


Request path	Method	Description
/api/database	GET	Query the pre-downsampling rules configured for a database or a table.

Sample requests

[{
    "name": "default",
    "downsampleRules": {
        "rules": [
            {
                "aggregator": "sum",
                "intervals": [
                    "1m",
                    "1h"
                ]
            },
            {
                "aggregator": "max",
                "intervals": [
                    "1m",
                    "1h"
                ]
            }
        ]
    }
}]

Response

If the request is successful, the HTTP status code 200 is returned.

Delete pre-downsampling rules

You can use the SQL statements in the following examples to delete pre-downsampling rules.

Examples

DROP PREDOWNSAMPLE `1h` AGGREGATORS (`sum`, `max`) ON sensor;

Note After you delete a pre-downsampling rule, data that has been pre-downsampled based on the rule is not immediately deleted. However, the deleted pre-downsampling rule is not returned when you query pre-downsampling rules.

For more information about the SQL syntax used to delete pre-downsampling rules, see DROP PREDOWNSAMPLE.

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 ratio specified in a query does not exactly match that of the pre-downsampled data, the pre-downsampled data whose pre-downsampling ratio best matches the specified downsampling ratio is queried. We recommend that you set the time intervals based on which pre-downsampling is performed to 1m, 10m, 1h, and 1d to meet the requirements of most downsampling queries.

Note If the pre-downsampling ratio of the pre-downsampled data is the maximum value that can divide the downsampling ratio specified in the query, the pre-downsampling ratio best matches the downsampling ratio specified in the query. For example, if the original data is pre-downsampled based on 1m, 5m, 8m, and 15m and the downsampling ratio specified in a query is 10m, the pre-downsampling ratio that best matches the specified downsampling ratio is 5m.
In no pre-downsampling ratio matches the downsampling ratio specified in a query, the original data is queried.

Query pre-downsampled data

Use SQL statements to query pre-downsampled data

By default, when you query pre-downsampled data, the original data before pre-downsampling is queried. To query only pre-downsampled data, you must add the /*+ PREDOWNSAMPLE */ hint in the query statements.

SELECT /*+ PREDOWNSAMPLE */ sum(temperature) from sensor
WHERE time >= 1619074800000 AND time <= 1619085600000
SAMPLE BY 1h

Query pre-downsampled data by using methods that are compatible with TSDB

You can use query pre-downsampled data in Time Series Database (TSDB) by using the following three method: API-based multi-value queries, API-based single-value queries, and SDKs.

Query pre-downsampled data by using API-based multi-value queries

When you use API operations to query pre-downsampled data, you must configure the downsampleSource parameter to specify the data source. For more information, see Query a multivariate data point.

Request path and method


Request path	Method	Description
/api/mquery	GET	Query pre-downsampled data.

Request parameters


Parameter	Required	Description	Example
downsampleSource	No	The data source that is queried. Default value: raw. Valid values: raw: The original data is queried. downsample: The pre-downsampled data is queried.	downsample

Sample requests

POST /api/mquery

{
    "end": 1619085600000,
    "queries": [
        {
            "downsampleDataSource" : "downsample",
            "metric": "aggMetric",
            "fields" : [
                {
                    "field" : "score",
                    "aggregator" : "none",
                    "downsample" : "1h-sum"
                }
            ],
                      "tags":{
                    "sensor":"IOTE_8859_0005"
                  }
        }
    ],
    "start": 1619074800000
}

Query pre-downsampled data by using API-based single-value queries

The API operation used to query single-value data is compatible with OpenTSDB. You can also use the Grafana plug-in for OpenTSDB to configure the data source. In this case, you must set metrics in the metric@field##downsample format. In this format, set metric to the name of the table and field to the value that you want to query. The downsample suffix indicates that pre-downsampled data is queried.

Use SDKs to query pre-downsampled data

When you use a SDK to query pre-downsampled data, you must configure the downsampleDataSource parameter in MultiFieldSubQuery Builder.

Examples

long startTime = 1619074800000;
long endTime = 1619085600000;
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);

You can set the DownsampleDataSource parameter to one of the following values:

DOWNSAMPLE: Query the pre-downsampled data.
RAW: Query the original 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.

Best practices for pre-downsampling

Pre-downsampling for data sampled at an interval of seconds

For data that is sampled at an interval of seconds, we recommend that you configure pre-downsampling rules based on intervals of one minute, one hour, and one day to perform a downsampling query at a downsampling ratio of any minute, hour, or day.

Configure pre-downsampling rules in which the sum, count, min and max aggregate functions are specified based on intervals of one minute, one hour, and one day.

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;

Perform a downsampling query at a downsampling ratio of any minute by using the pre-dowmsampling rule configured based on an interval of one minute.
```
SELECT /*+ PREDOWNSAMPLE */ sum(temperature), max(temperature) from sensor
WHERE time >= 1619074800000 AND time <= 1619085600000
SAMPLE BY <N>m;
```
Perform a downsampling query at a downsampling ratio of any hour by using the pre-dowmsampling rule configured based on an interval of one hour.
```
SELECT /*+ PREDOWNSAMPLE */ sum(temperature), max(temperature) from sensor
WHERE time >= 1619074800000 AND time <= 1619085600000
SAMPLE BY <N>h;
```
Perform a downsampling query at a downsampling ratio of any day by using the pre-dowmsampling rule configured based on an interval of one day.
```
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 that is sampled at an interval of minutes, we recommend that you configure pre-downsampling rules based on intervals of one hour and one day to perform a downsampling query at a downsampling ratio of any hour or day.

Configure pre-downsampling rules in which the sum, count, min and max aggregate functions are specified based on intervals of one hour and one day.
```
CREATE PREDOWNSAMPLE `1h` AGGREGATORS (`sum`, `count`, `min`, `max`) ON sensor;
CREATE PREDOWNSAMPLE `1d` AGGREGATORS (`sum`, `count`, `min`, `max`) ON sensor;
```
Perform a downsampling query at a downsampling ratio of any hour by using the pre-dowmsampling rule configured based on an interval of one hour.
```
SELECT /*+ PREDOWNSAMPLE */ sum(temperature), max(temperature) from sensor
WHERE time >= 1619074800000 AND time <= 1619085600000
SAMPLE BY <N>h;
```
Perform a downsampling query at a downsampling ratio of any day by using the pre-dowmsampling rule configured based on an interval of one day.
```
SELECT /*+ PREDOWNSAMPLE */ sum(temperature), max(temperature) from sensor
WHERE time >= 1619074800000 AND time <= 1619600400000
SAMPLE BY <N>d;
```