edit-icon download-icon

Query data

Last Updated: Sep 05, 2018

Request path and method

Path Method Description
/api/query POST Queries the data

Request content

Request content in 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.

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。

Supports millisecond accuracy

TSDB timestamp supports millisecond accuracy. When the timestamps of the data written in are in milliseconds, reading data needs passing parameter msResolution:true.

Subquery in 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
limit Integer Yes data subpage, subquery returns maximum data points 0 1000
offset Integer Yes data subpage, subquery returns data points offset 0 500
dpValue String No filter returned data by conditions: “>”, “<”, “=”, “<=”, “>=”, “!=”. None >=1000
downsample String No Downsamples time series None 60m-avg
tags Map No The specified tag None -
filters List None The filter None -

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

Query example

Request: POST/api/query

Body:

  1. {
  2. "start": 1356998400,
  3. "end": 1356998460,
  4. "queries": [
  5. {
  6. "aggregator": "sum",
  7. "metric": "sys.cpu.0"
  8. },
  9. {
  10. "aggregator": "sum",
  11. "metric": "sys.cpu.1"
  12. }
  13. ]
  14. }

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.

Note: Negative limit and offset are not allowed.

Example

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

  1. {
  2. "start":1346046400,
  3. "end":1347056500,
  4. "queries":[
  5. {
  6. "aggregator":"avg",
  7. "downsample":"2s-sum",
  8. "index":0,
  9. "metric":"sys.cpu.0",
  10. "limit":"500",
  11. "offset":"1000",
  12. "tags":{
  13. "host":"localhost",
  14. "appName":"hitsdb"
  15. }
  16. }
  17. ]
  18. }

dpValue description

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

Example:

  1. {
  2. "start":1346046400,
  3. "end":1347056500,
  4. "queries":[
  5. {
  6. "aggregator":"avg",
  7. "downsample":"2s-sum",
  8. "metric":"sys.cpu.0",
  9. "dpValue":">=500",
  10. "tags":{
  11. "host":"localhost",
  12. "appName":"hitsdb"
  13. }
  14. }
  15. ]
  16. }

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:

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

Wherein:

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

Operator Description Interpolation method
avg Average value Linear interpolation (slope fitting)
count Number of data points 0 is interpolated
first get the first value -
last get the last value -
mimmin Minimum value The maximum value is interpolated.
mimmax Maximum value The minimum value is interpolated.
min Minimum value Linear interpolation
max Maximum value Linear interpolation
sum Summation Linear interpolation
zimsum Summation 0 is interpolated.

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 Filling value
none No value is filled by default.
nan NaN
null null
zero 0
linear linear interpolation
previous the previous value
near the nearest value
after the after 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:

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

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

Downsampling example

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

Note: Downsampling is not a must when querying. You can specify the value as null or (“”), for example, {“downsample”: null} or {“downsample”: “”}, so that data point dowmsampling is not triggered。

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

Operator Description Interpolation method
avg Average value Linear interpolation (slope fitting)
count Number of data points 0 is interpolated
mimmin Minimum value The maximum value is interpolated.
mimmax Maximum value The minimum value is interpolated.
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:
Name Type Required Description Default value Example
type String Yes filter. For details, see the description below. None literal_or
tagk String Yes Specify the TagKey name Name host
filter String Yes filter expression None web01丨web02
groupBy Boolean No Specify whether to perform “groupBy” for TagValue false false

Filter type description

Name Example Description
literal_or web01丨 web02 Perform aggregation for multiple TagValues respectively. It is case sensitive.
wildcard *mysite.com Perform aggregation for TagValues that contain the specified wildcard respectively. It is case sensitive.

Query example

Example of queries without filter

Body:

  1. {
  2. "start": 1356998400,
  3. "end": 1356998460,
  4. "queries": [
  5. {
  6. "aggregator": "sum",
  7. "metric": "sys.cpu.0",
  8. "rate": "true",
  9. "tags": {
  10. "host": "*",
  11. "dc": "lga"
  12. }
  13. }
  14. ]
  15. }

Example of queries with filter

Body:

  1. {
  2. "start": 1356998400,
  3. "end": 1356998460,
  4. "queries": [
  5. {
  6. "aggregator": "sum",
  7. "metric": "sys.cpu.0",
  8. "rate": "true",
  9. "filters": [
  10. {
  11. "type": "wildcard",
  12. "tagk": "host",
  13. "filter": "*",
  14. "groupBy": true
  15. },
  16. {
  17. "type": "literal_or",
  18. "tagk": "dc",
  19. "filter": "lga|lga1|lga2",
  20. "groupBy": false
  21. }
  22. ]
  23. }
  24. ]
  25. }

Query result description

For a successful query, the HTTP response code is “200” and the response is returned in JSON format, as shown in the following table:

Dame Description
metric Metric name
tags The tag of which TagValues are not aggregated
aggregateTags The tag of which TagValues are aggregated
dps Data point pair

Response example:

  1. [
  2. {
  3. "metric": "tsd.hbase.puts",
  4. "tags": {"appName": "hitsdb"},
  5. "aggregateTags": [
  6. "host"
  7. ],
  8. "dps": {
  9. "1365966001": 25595461080,
  10. "1365966061": 25595542522,
  11. "1365966062": 25595543979,
  12. "1365973801": 25717417859
  13. }
  14. }
  15. ]
Thank you! We've received your feedback.