How do I create pre-downsampling rules-Lindorm(Lindorm)-阿里云帮助中心

Manage pre-downsampling rules in LindormTSDB.

Applicable engines and versions

Applies to LindormTSDB only. All versions are supported.

CREATE PREDOWNSAMPLE

Creates pre-downsampling rules that control how LindormTSDB aggregates time series data at a reduced resolution for storage and query efficiency.

Syntax

create_predownsample_statement ::= CREATE PREDOWNSAMPLE time_interval
                                    AGGREGATORS ('function_identifier' [, 'function_identifier'] ...)
                                    [ TTL time_interval ] ON table_identifier
time_interval                  ::= interval units

Parameters

Parameter	Description
`interval`	The interval at which time series data is aggregated. Must be a positive integer.
`units`	The unit of the interval. Valid values: `s` (seconds), `m` (minutes), `h` (hours), `d` (days).
`AGGREGATORS`	The aggregation functions to apply during pre-downsampling. Valid values: `count`, `first`, `last`, `min`, `max`, `sum`.
`function_identifier`	The name of the aggregation function. A single statement can specify multiple functions with the same interval. Alternatively, define functions with the same interval across multiple statements.
`TTL`	The time to live (TTL) of the pre-downsampled data.
`table_identifier`	The name of the table for which pre-downsampling rules are configured.

The avg function is not supported. To calculate an average over pre-downsampled data, divide the sum result by the count result. If data is repeatedly added and overwritten, the computed average may be inaccurate.

If multiple pre-downsampling rules specify different TTL values, the largest TTL applies to all rules.

Query pre-downsampled data

By default, querying a table returns the original data, not the pre-downsampled data. To query only pre-downsampled data, add the /*+ PREDOWNSAMPLE */ hint to your query statement.

Examples

Example 1: Create a pre-downsampling rule with a 90-day TTL

The following statement aggregates data in the sensor table hourly using sum and max, and retains the pre-downsampled data for 90 days.

CREATE PREDOWNSAMPLE `1h` AGGREGATORS (`sum`, `max`) TTL `90d` ON sensor;

Example 2: Query pre-downsampled data

The following statement queries hourly pre-downsampled data from the sensor table within a specified time range.

SELECT /*+ PREDOWNSAMPLE */ SUM(temperature) FROM sensor
WHERE time >= 1619076780000 AND time <= 1619076800000
SAMPLE BY 1h;

SHOW PREDOWNSAMPLES

Queries the pre-downsampling rules configured for one or all tables in LindormTSDB.

Syntax

SHOW PREDOWNSAMPLES [ ON table_identifier ]

Parameters

Parameter	Required	Description
`table_identifier`	No	The name of the table whose pre-downsampling rules to query. If omitted, rules for all tables are returned.

Examples

Query pre-downsampling rules for all tables

SHOW PREDOWNSAMPLES;

Output:

+--------+----------+------------+-----+----------------------+
| table  | interval | aggregator | ttl | update_time          |
+--------+----------+------------+-----+----------------------+
| sensor | 1h       | sum, max   | 90d | 2021-10-10T08:00:00Z |
| sensor | 5m       | sum, max   | 30d | 2021-10-10T08:00:00Z |
| test   | 1m       | sum, max   | 30d | 2021-10-10T08:00:00Z |
| test   | 2h       | sum, max   | 30d | 2021-10-10T08:00:00Z |
+--------+----------+------------+-----+----------------------+

Query pre-downsampling rules for a specific table

SHOW PREDOWNSAMPLES ON sensor;

Output:

+--------+----------+------------+-----+----------------------+
| table  | interval | aggregator | ttl | update_time          |
+--------+----------+------------+-----+----------------------+
| sensor | 1h       | sum, max   | 90d | 2021-10-10T08:00:00Z |
| sensor | 5m       | sum, max   | 30d | 2021-10-10T08:00:00Z |
+--------+----------+------------+-----+----------------------+

Each row in the result represents one pre-downsampling rule. The result columns are:

Column	Description
`table`	The name of the table.
`interval`	The downsampling interval (for example, `1h`, `5m`).
`aggregator`	The aggregation functions applied during downsampling (for example, `sum`, `max`).
`ttl`	The Time to Live (TTL) of the downsampled data (for example, `90d`).
`update_time`	The time when the rule was last updated, in UTC.

DROP PREDOWNSAMPLE

Deletes one or more pre-downsampling rules from a time series table.

Syntax

DROP PREDOWNSAMPLE time_interval
    AGGREGATORS (function_identifier [, function_identifier ...])
    ON table_identifier

time_interval ::= interval units

Parameters

Parameter	Description
`interval`	The aggregation interval. Must be a positive integer.
`units`	The time unit for the interval. Valid values: `s` (seconds), `m` (minutes), `h` (hours), `d` (days).
`AGGREGATORS`	The aggregation functions to remove. Valid values: `count` (returns the total number of data points), `first` (returns the first value), `last` (returns the last value), `min` (returns the minimum value), `max` (returns the maximum value), `sum` (returns the sum of the values).
`function_identifier`	The name of an aggregation function. Specify one or more functions with the same interval in a single statement, or split them across multiple statements.
`table_identifier`	The name of the time series table.

avg is not supported in pre-downsampling. To compute an average, derive it from the sum and count results. If data is repeatedly written and overwritten, the derived average may be inaccurate.

Examples

Delete the sum and max pre-downsampling rules with a 1-day interval from the sensor table:

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