All Products
Search
Document Center

Query data

Last Updated: Apr 09, 2021

Request path and method

Request path: /api/query

Request Method: POST.

Description: Queries the data

Request content (JSON format)

Name

Type

Required

Description

Default value

Example

start

Long

Yes

Start time in seconds or milliseconds. For details about how to judge the unit, see Timestamp unit judge below.

None

1499158925

end

Long

No

End time in seconds or milliseconds. For details about how to learn about the unit, see Timestamp unit judge below. The default value is the current time of the TSDB server.

Current time

1499162916

queries

Array

Yes

Subquery array

None

See the subquery description.

msResolution

boolean

No

Subquery array

false

The timestamps of returned data points are in seconds or milliseconds. You are recommended to set msResolution ture, otherwise, the data points in a second conduct downsampling according to the downsampling function requested. Data points without specifying downsampling operator returns the original values with the same timestamps.

hint

Map

No

Query Hint.

N/A

See Query Hint Description.

Timestamp unit judge

The timestamp unit is second or millisecond. TSDB judges the time unit by the numeric value according to the following rules:

  • When the timestamp range is [4284768,9999999999]: the unit is second, and the time range is [1970-02-20 00:59:28, 2286-11-21 01:46:39].

  • When the timestamp range is [10000000000,9999999999999]: the unit is millisecond, and the time range is [1970-04-27 01:46:40.000, 2286-11-21 01:46:39.999].

  • When the timestamp range is (-∞,4284768) or (9999999999999,+∞): the time range is [1970-04-27 01:46:40.000, 2286-11-21 01:46:39.999].

Note

This description is only applicable to the data write (/api/put) and data query (api/query) APIs.

Single timestamp data query

TSDB supports single timestamp data query by setting the start time same as the end time. For example, “start”: 1356998400,“end”: 1356998400.

Subquery (JSON format)

Name

Type

Required

Description

Default value

Example

aggregator

String

Yes

Aggregate function. For details, see Aggregation description below.

None

sum

metric

String

Yes

Metric name

None

sys.cpu0

rate

Boolean

No

Specifies whether to calculate the growth rate of a metric value; calculation:Vt-Vt-1/t1-t-1

false

true

delta

Boolean

No

Whether to calculate the difference between the specified index values; Calculation formula: Vt-Vt-1.

false

true

limit

Integer

No

data subpage, subquery returns maximum data points

0

1000

offset

Integer

No

data subpage, subquery returns data points offset

0

500

dpValue

String

No

filter returned data by conditions: “>”, “<”, “=”, “<=”, “>=”, “!=”.

None

>=1000

preDpValue

String

No

Filter when scanning original data points according to the provided conditions, support ">", "<", "=", "<=", ">=", "!=".

Note

It differs from the dpValue in that the former is value filtering of the results calculated after the query is completed; the latter is value filtering when the stored data points are scanned, so that the data points that do not meet the filtering conditions will not be added to the query calculation at all.

None

>=1000

downsample

String

No

Downsamples time series

None

60m-avg

tags

Map

No

The specified tag

None

-

filters

List

No

The filter

None

-

hint

Map

No

Query Hint.

None

See Query Hint Description

forecasting

String

No

Data prediction.

None

See query forecasting instructions

abnormaldetect

String

No

Data prediction.

None

See query normaldetect description

Note

  • A query can contain no more than 200 subqueries.

  • In the scenario where both tags and filters are specified, the post specified filter conditions take effect.

  • For details about “downsample”, “tags”, and “filters”, see the corresponding descriptions below.

Query example

Request: POST/api/query

{ "start": 1356998400, "end": 1356998460, "queries": [ { "aggregator": "sum", "metric": "sys.cpu.0" }, { "aggregator": "sum", "metric": "sys.cpu.1" } ] }

Data subpage query (Limit and Offset) description

Limit: The maximum number of returned subquery data points. The default value is 0, which indicates no limit on the number of returned data points.

Offset: The offset of returned subquery data points. The default value is 0, which indicates no offset of returned data points.

Notice

limit and offset cannot be negative numbers.

Examples

If you want to return data points from 1001 to 1500, set limit for 500 and offset for 1000.

 { "start":1346046400, "end":1347056500, "queries":[ { "aggregator":"avg", "downsample":"2s-sum", "metric":"sys.cpu.0", "limit":"500", "offset":"1000", "tags":{ "host":"localhost", "appName":"hitsdb" } } ] }

dpValue description

Filter the final returned data points according to the value limit conditions set by users. Filter conditions of “>”, “<”, “=”, “<=”, “>=”, “!=” are available.

Examples

 { "start":1346046400, "end":1347056500, "queries":[ { "aggregator":"avg", "downsample":"2s-sum", "metric":"sys.cpu.0", "dpValue":">=500", "tags":{ "host":"localhost", "appName":"hitsdb" } } ] }

Delta description

When the user specifies the difference operator in the subquery, the value value of the key-value pair in the dps of the data returned by TSDB will be the calculated difference. It should be noted that if there are n key-value pairs in the returned dps when the difference is not specified, only n-1 key-value pairs will be included in the returned dps after calculating the difference (the first key-value pair will be rounded off because the difference cannot be calculated). The difference operator also applies to the value after Downsample.

In addition, when the user specifies the difference operator, he can further specify deltaOptions in the subquery to further control the behavior of the difference. The currently supported deltaOptions are as follows:

Name

Type

Required

Description

Default value

Example

counter

Boolean

No

When the mark bit is specified, it means that the indicator value used to calculate the difference is assumed to be regarded by the user as a monotonically increasing (or decreasing) cumulative value similar to a counter (but the server will not check).

false

true

counterMax

Integer

No

When counter is set to true, this value is used to specify the threshold of the difference. When the absolute value of the difference exceeds this threshold, it is considered an outlier; when the value is not specified, no threshold is set for the difference.

N/A

100

dropReset

Boolean

No

This tag bit needs to be used in conjunction with counterMax above. When the difference value of the exception is calculated by specifying counterMax, dropReset determines whether to directly discard the difference value of the exception. If the value is set to true, the outlier value is discarded. If the value is set to false (default), the outlier value is reset to zero.

false

true

Examples

 { "start":1346046400, "end":1347056500, "queries":[ { "aggregator":"none", "downsample":"5s-avg", "delta":true, "deltaOptions":{ "counter":true, "counterMax":100 } "metric":"sys.cpu.0", "dpValue":">=50", "tags":{ "host":"localhost", "appName":"hitsdb" } } ] }

Downsample description

When the query time scope is large, only data of certain precision are needed to be returned. The query format is as follows:

<interval><units>-<aggregator>[-fill policy]

Where:

  • interval: It is a numeric value, such as 5 and 60. “0all” indicates that the time dimension is aggregated to a point.

  • units: s indicates second, m indicates minute, d indicates day, n indicates month, y indicates year.

    Note

    supports calendar based downsampling. To use calendar boundaries, append a c to the interval time units. For example, 1 dc indicates a calendar day from 00:00 to 24:00.

  • aggregator: the specifications of downsampling operators are as follows.

    Operators

    Description

    avg

    Average value.

    count

    Number of data points.

    first

    get the first value

    last

    get the last value

    min

    The minimum value.

    max

    The maximum value.

    median

    Find the median.

    sum

    Summation.

    zimsum

    Summation.

    rfirst

    The function is the same as first But the time stamp of the result returned after downsampling is the timestamp of the original data; Instead of downsampling the aligned timestamps.

    rlast

    The function is the same as last But the time stamp of the result returned after downsampling is the timestamp of the original data; Instead of downsampling the aligned timestamps.

    rmin

    The function is the same as mimmin But the time stamp of the result returned after downsampling is the timestamp of the original data; Instead of downsampling the aligned timestamps.

    rmax

    The function is the same as mimmax But the time stamp of the result returned after downsampling is the timestamp of the original data; Instead of downsampling the aligned timestamps.

    Note

    When the aggregation operator of downsampling is specified as rfirst rlast Rmin or Rmax , fill policy cannot be specified in the down sampling expression.

    Fill policy

    Through downsample, all the time series are split up according to the specified precision and data in each downsampled range is calculated for one time. If a downsampled range has no value, the fill policy can be used to enter a specific value at this time point. For example, when the timestamp of a time series after downsample is t+0, t+20, t+30, if fill policy is not specified, the time series has only three values. If fill policy is specified to null, the time series has four values, and the value at the time point t+10 is null.

    The following table lists the relationship between the fill policy and specific filling value.

    Fill Policy

    Fill value

    none

    No value is filled by default.

    nan

    NaN

    null

    null

    zero

    0

    linear

    Linear fill value

    previous

    A previous value

    near

    An adjacent value

    after

    A subsequent value

    fixed

    designate a fixed value (please see the following example)

    Fixed Fill Policy

    Method: Fill the fixed value after “#”. Allows positive and negative values. The format is as follow:

    <interval><units>-<aggregator>-fixed#<number>

    Example :1h-sum-fixed#6,1h-avg-fixed#-8

    Downsampling example

    Example: 1m-avg,1h-sum-zero,1h-sum-near

    Notice

    Downsample is not a necessary clause when querying. You can even explicitly indicate that its value is null or empty ("") during the query, for example: {"downsample": null} or {"downsample": ""}, so that downsampling of data points will not be triggered.

Aggregate description

After downsampling, multiple time series are generated and their timestamps are aligned. Aggregation is the action of combining these multiple time series into one by aggregating the values at each timestamp. During aggregation, each time series must have a value at each timestamp. If a time series has no value at a timestamp, interpolation is performed. The details about interpolation are as follows.

Interpolation

If a time series has no value at a timestamp and the fill policy is not used in this case to fill a value, and meanwhile, one of other time series to be aggregated has a value at the timestamp, then a value is interpolated to this time series at the timestamp.

For example, when the downsampling and aggregation conditions are {“downsample”: “10s-avg”, “aggregator”: “sum”}, and there are two time series that need to use “sum” for aggregation, after downsampling by “10s-avg”, the timestamps of the two time series that have values are:

line 1: t+0, t+10, t+20, t+30 line 2: t+0, t+20, t+30

The line 2 lacks the value at the time point “t+10”. Therefore, before aggregation, a value is interpolated for line 2 at “t+10”. The interpolation method is determined by the aggregation operator. For details, see the following operator table.

Operators

Description

Interpolation method

avg

Average

Linear interpolation (slope fitting)

count

Number of data points

0 is interpolated

mimmin

Minimum value

Insert maximum value

mimmax

Maximum value

Insert minimum value

min

Minimum value

Linear interpolation

max

Maximum value

Linear interpolation

none

No operation

0 is interpolated.

sum

Summation

Linear interpolation

zimsum

Summation

0 is interpolated.

Filters description

You can specify a filter in any of the following ways:

  • Specify a filter when specifying the TagKey:

    • tagk = *: Perform “groupBy” for the Tagv of the Tagk to aggregate the values under the same Tagv.

    • tagk = tagv1|tagv2: Aggregate the values under Tagv 1 and Tagv 2 of the TagKey respectively.

  • Specify a filter in JSON format:

Query Hint description

Scenario

This feature is mainly to improve the query speed. Assuming that the timeline hit by one tags A is significantly larger than that hit by other tags B, it needs to be discarded to avoid the result set equal to tagsB after a large number of timelines of tags A are captured and intersected by tagsB small-scale timelines.

Formats

  • The current version only supports tagk-level query index restrictions (tagk under hint is a fixed writing method).

  • where 0 indicates that the index corresponding to the tagk is not used, whereas 1 indicates that the index corresponding to the tagk is used.

Versions

The hint feature is supported from v2.6.1.

Query example

Subquery level

{ "start": 1346846400, "end": 1346846400, "queries": [ { "aggregator": "none", "metric": "sys.cpu.nice", "tags": { "dc": "lga", "host": "web01" }, "hint": { "tagk": { "dc": 1 } } } ] }

Overall query level

{ "start": 1346846400, "end": 1346846400, "queries": [ { "aggregator": "none", "metric": "sys.cpu.nice", "tags": { "dc": "lga", "host": "web01" } } ], "hint": { "tagk": { "dc": 1 } } }

Error

0 and 1 cannot be specified at the same time

{ "start": 1346846400, "end": 1346846400, "queries": [ { "aggregator": "none", "metric": "sys.cpu.nice", "tags": { "dc": "lga", "host": "web01" } } ], "hint": { "tagk": { "dc": 1, "host": 0 } } }

The following error message will be returned:

{ "error": { "code": 400, "message": "The value of hint should only be 0 or 1, and there should not be both 0 and 1", "details": "TSQuery(start_time=1346846400, end_time=1346846400, subQueries[TSSubQuery(metric=sys.cpu.nice, filters=[filter_name=literal_or, tagk=dc, literals=[lga], group_by=true, filter_name=literal_or, tagk=host, literals=[web01], group_by=true], tsuids=[], agg=none, downsample=null, ds_interval=0, rate=false, rate_options=null, delta=false, delta_options=null, top=0, granularity=null, granularityDownsample=null, explicit_tags=explicit_tags, index=0, realTimeSeconds=-1, useData=auto, limit=0, offset=0, dpValue=null, preDpValue=null, startTime=1346846400000, endTime=1346846400000, Query_ID=null)] padding=false, no_annotations=false, with_global_annotations=false, show_tsuids=false, ms_resolution=false, options=[])" } } 

Values other than 0 and 1 cannot be specified

{ "start": 1346846400, "end": 1346846400, "queries": [ { "aggregator": "none", "metric": "sys.cpu.nice", "tags": { "dc": "lga", "host": "web01" } } ], "hint": { "tagk": { "dc": 100 } } }

The following error message will be returned:

{ "error": { "code": 400, "message": "The value of hint can only be 0 or 1, and it is detected that '100' is passed in", "details": "TSQuery(start_time=1346846400, end_time=1346846400, subQueries[TSSubQuery(metric=sys.cpu.nice, filters=[filter_name=literal_or, tagk=dc, literals=[lga], group_by=true, filter_name=literal_or, tagk=host, literals=[web01], group_by=true], tsuids=[], agg=none, downsample=null, ds_interval=0, rate=false, rate_options=null, delta=false, delta_options=null, top=0, granularity=null, granularityDownsample=null, explicit_tags=explicit_tags, index=0, realTimeSeconds=-1, useData=auto, limit=0, offset=0, dpValue=null, preDpValue=null, startTime=1346846400000, endTime=1346846400000, Query_ID=null)] padding=false, no_annotations=false, with_global_annotations=false, show_tsuids=false, ms_resolution=false, options=[])" } }

Time series forecasting description

Based on the existing data of the time series as a training set, the AI algorithm is used to discover the data trends and periods, so as to predict the data points of the time series in the future. The query format is as follows:

<AlgorithmName>-<ForecastPointCount>[-<ForecastPolicy>] 

Where:

  • AlgorithmName: the algorithm name. currently, the arima and holtwinters algorithms are supported.

  • ForecastPointCount: Predicts the number of future data points. Data of the Integer type. For example, the 2nd generation table predicts the next 2 points.

  • ForecastPolicy: the prediction policy. Different algorithms represent different meanings.

    • When AlgorithmName is arima, the ForecastPolicy format is as follows:

      Note
      • Delta represents the difference order, which defaults to 1. By increasing the difference, data fluctuations can be adjusted to stabilize the data.

      • Seasonality represents the season cycle, and the default is 1. When the data fluctuates according to the periodic rule, you can adjust the prediction period by setting seasonality. For example, if the data fluctuates once every 10 data points, the seasonality is set to 10.

    • When AlgorithmName is holtwinters, the ForecastPolicy format is as follows:

      Note

      Seasonality represents the season cycle, and the default is 1. When the data fluctuates according to the periodic rule, you can adjust the prediction period by setting seasonality. For example, if the data fluctuates once every 10 data points, the seasonality is set to 10.

Prediction sample

Example: arima-1,arima-48-1-48,holtwinters-1-1. Assume that the data is

[ { "metric": "sys.cpu.nice", "tags": { "dc": "lga", "host": "web00" }, "aggregateTags": [], "dps": { "1346837400": 1, "1346837401": 2, "1346837402": 3, "1346837403": 4, "1346837404": 5, "1346837405": 6, "1346837406": 7, "1346837407": 8, "1346837408": 9, "1346837409": 10, "1346837410": 11, "1346837411": 12 } } ]

Query process information

{ "start":1346837400, "end": 1346847400, "queries":[ { "aggregator":"none", "metric":"sys.cpu.nice", "forecasting" : "arima-1" } ] }

The predicted result is

[ { "metric": "sys.cpu.nice", "tags": { "dc": "lga", "host": "web00" }, "aggregateTags": [], "dps": { "1346837400": 1, "1346837401": 2, "1346837402": 3, "1346837403": 4, "1346837404": 5, "1346837405": 6, "1346837406": 7, "1346837407": 8, "1346837408": 9, "1346837409": 10, "1346837410": 11, "1346837411": 12, "1346837412": 13 } } ]

time series prediction (normaldetect) description

Based on the existing data of the time series as a training set, the AI algorithm is used to discover the data trends and periods, so as to predict the data points of the time series in the future. The query format is as follows:

<AlgorithmName>[-<Sigma>-<NP>-<NS>-<NT>-<NL>]

Currently, only one algorithm is supported for anomaly detection: stl method. For those who do not know much about parameter tuning, it is recommended to select the default parameter. If users are familiar with STL algorithm, better prediction results can be obtained by additional parameter adjustment. There are 6 complete parameters for anomaly detection, which are AlgorithmName-Sigma-NP-NS-NT-NL as stl-5-5-7-0-0 below. The parameters are separated by-. Each parameter represents the following meaning:

  • Sigma: Abnormal point detection. If the absolute value of the difference between the data point and the mean is 3 times the standard deviation, it is considered an abnormal point. Generally 3.0.

  • NP: the number of points contained in each cycle, judged by the cycle.

  • NS: the seasonal smoothing parameter.

  • NT: the trend smoothing parameter.

  • NL: low-pass filtering smoothing parameters

Prediction sample

Example 1: Return the original CIDR block.

"abnormaldetect”:“stl”,

Example 2: Convert the format of a CIDR block to a format that specifies the prefix length of the CIDR block.

"abnormaldetect”:“stl-5-5-7-0-0”,
  • Example

    { "start":1346836400, "end":1346946400, "queries":[ { "aggregator": "none", "metric": "sys.cpu.nice", "abnormaldetect": "stl-5-5-7-0-0", "filters": [ { "type": "literal_or", "tagk": "dc", "filter": "lga", "groupBy": false }, { "type": "literal_or", "tagk": "host", "filter": "web00", "groupBy": false } ] } ] }

    Output sample

    The exception detection output of TSDB is a linked list with a fixed layout format as follows:

    [srcValue, upperValue, lowerValue, predictValue, isAbnormal]

    • srcValue: the value of the original data

    • upperValue: the upper bound of data values

    • lowerValue: the lower bound of the data value

    • predictValue: the predicted value after stl calculation

    • IsAbnormal: marks whether the original value is abnormal, 0 means normal, and the 1st generation table is abnormal.

      [ { "metric": "sys.cpu.nice", "tags": { "dc": "lga", "host": "web00" }, "aggregateTags": [], "dps": { "1346837400": [ 1, 1.0000000000000049, 0.9999999999999973, 1.0000000000000013, 0 ], "1346837401": [ 2, 2.0000000000000036, 1.9999999999999958, 1.9999999999999998, 0 ], "1346837402": [ 3, 3.0000000000000036, 2.9999999999999956, 3, 0 ], "1346837403": [ 4, 4.0000000000000036, 3.9999999999999956, 4, 1 ], "1346837404": [ 5, 5.0000000000000036, 4.9999999999999964, 5, 0 ], "1346837405": [ 6, 6.000000000000002, 5.999999999999995, 5.999999999999998, 0 ], "1346837406": [ 7, 7.0000000000000036, 6.9999999999999964, 7, 1 ], "1346837407": [ 8, 8.000000000000004, 7.9999999999999964, 8, 0 ], "1346837408": [ 9, 9.000000000000004, 8.999999999999996, 9, 0 ], "1346837409": [ 10, 10.000000000000004, 9.999999999999996, 10, 0 ], "1346837410": [ 11, 11.000000000000005, 10.999999999999998, 11.000000000000002, 0 ], "1346837411": [ 12, 12.000000000000004, 11.999999999999996, 12, 0 ] } } ]