Query data

Request path and method

Request path: /api/query

Request Method: POST.

Description: Queries the data

Request content (JSON format)

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

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)

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.

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:

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.

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

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

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