Lindorm:Pre-downsampling

Create a pre-downsampling rule for a table by using SQL statements

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;

Syntax reference

CREATE PREDOWNSAMPLE

Create a pre-downsampling rule for a database by using API operations

Request path and method

Request parameters

Parameters for a single pre-downsampling rule

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 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

SHOW PREDOWNSAMPLES

Query pre-downsampling rules configured for a database by using API operations

Request path and method

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 pre-downsampling rules configured for a table by using SQL statements

Example

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

Syntax reference

DROP PREDOWNSAMPLE

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": {
    }
}

Use SQL statements to query pre-downsampled data

By default, when you query pre-downsampled data, the raw 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;

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

When you use a SDK to query pre-downsampled data, you must configure 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 a multivariate data point.

Request path and method

Request parameters

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"
                  }
        }
    ]
}