Time Series Database:Query a multivariate data point

Use mquery to query a multivariate data point

Request path and method

Parameters in a data point

Timestamp units

Timestamps can be measured in seconds or milliseconds. TSDB uses the following rules to determine the unit of a timestamp based on the numeric value of the timestamp:

• If a timestamp is in the range of [4284768,9999999999], TSDB determines that the timestamp is measured in seconds. In this case, the corresponding date and time range is [1970-02-20 00:59:28, 2286-11-21 01:46:39].

• If a timestamp is in the range of [10000000000,9999999999999], TSDB determines that the timestamp is in milliseconds. In this case, the corresponding date and time range is [1970-04-27 01:46:40.000,2286-11-21 01:46:39.999].

• If a timestamp is in the range of (-∞,4284768) or (9999999999999,+∞), TSDB determines that the timestamp is invalid.

  Note

  This section describes the rules for determining the unit of a timestamp. These rules apply to the following API operations: /api/put, /api/mput, /api/query, and /api/mquery. /api/put and /api/mput are used to write data. /api/query and /api/mquery are used to query data.

Query data points at a single point in time

TSDB allows you to query data points at a single point in time. To query data points at a single point in time, set the start time and the end time to the same value.

For example, you can set the start and end parameters to 1356998400.

Parameters for subqueries in the JSON format

Parameters for field queries in the JSON format

Sample requests

Request body: POST/api/query

{
    "start" : 1346846400,
    "end" :   1346846411,
    "msResolution" : true,
    "queries" : [
        {
            "metric" : "wind",
            "fields" : [
                {
                    "field" : "speed",
                    "aggregator" : "sum",
                    "downsample" : "2s-last",
                      "alias" : "speed_sum"
                },
                {
                    "field" : "*",
                    "aggregator" : "sum",
                    "downsample" : "2s-count",
                    "where":"speed>10"
                }
            ]
        }
    ]
}

Use the limit and offset parameters

The limit parameter specifies the maximum number of data points to return in each timeline for a subquery. The default value of the limit parameter is 0. The default value 0 specifies that no limits are placed on the number of returned data points.

The offset parameter specifies the number of the data points that you want to skip in each timeline for a subquery. The default value of the offset parameter is 0. The default value 0 specifies that no data points are skipped.

Examples

If you want to obtain the data points whose rankings are 1001 to 1500, set the limit parameter to 500 and the offset parameter to 1000.

{
    "start" : 1346846400,
    "end"  :  1346846411,
    "msResolution" : true,
    "queries" : [
        {
            "metric" : "wind",
            "fields" : [
                {
                    "field" : "*",
                    "aggregator" : "sum",
                    "downsample" : "2s-count"
                }
            ],
            "filters" : [
                {
                    "filter" : "IOTE_8859_0005|IOTE_8859_0004",
                    "tagk" : "sensor",
                    "type" : "literal_or"
                }
            ],
            "limit" : 500,
            "offset" : 1000
        }
    ]
}

Parameter: dpValue

The dpValue parameter specifies the limit for data values. You can specify this parameter to filter data points that are to be returned. Valid values are >, <, =, <=, >=, and!=.

Examples

{
    "start" : 1346846400,
    "end" :   1346846411,
    "msResolution" : true,
    "queries" : [
        {
            "metric" : "wind",
            "fields" : [
                {
                    "field" : "level",
                    "aggregator" : "avg",
                    "downsample" : "2s-avg",
                    "dpValue" : ">=8.0"
                }
            ],
            "filters" : [
                {
                    "filter" : "IOTE_8859_0005|IOTE_8859_0004",
                    "tagk" : "sensor",
                    "type" : "literal_or"
                }
            ]
        }
    ]
}

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

Parameter: downsample

Use this parameter if you need to query data generated in a long time range and aggregate the data based on a specified time interval. A timeline is divided into multiple time ranges based on the specified time interval for downsampling. Each timestamp returned indicates the start of each time range. The following sample code provides an example on the format of the query:

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

The following section describes the parameters that are used in the query:

• interval: specifies a numeric value, such as 5 or 60. The value 0all specifies that the data points in the time range are aggregated into a single value.

• units: the unit. s represents seconds. m represents minute. h represents hour. d represents day. n represents month. y represents year.

  Note

  • By default, the modulo and truncation operation is performed to align timestamps. Timestamps are aligned by using the following formula: Aligned timestamp = Data timestamp - (Data timestamp % Time interval).

  • You can downsample data based on a calendar time interval. To use the calendar time interval, add c to the end of the value of the units parameter. For example, 1dc specifies the 24-hour period from 00:00 of the current day to 00:00 of the next day.

• aggregator: the aggregation settings. The following table describes the operators that are used for downsampling.

Fill policy

You can specify the fill policy parameter to determine how to fill missing values with pre-defined values. During downsampling, all timelines are split based on a specified time interval, and data points in each time range are aggregated. If no value exists during a time range in the downsampling result, you can specify a fill policy to fill the missing value with a pre-defined value. An example is used to explain fill policies. In this example, the timestamps of a timeline after downsampling are t+0, t+20, and t+30. If you do not specify a fill policy, only three values are reported. If you set the fill policy to null, four values are reported. The missing value at the point in time t+10 is filled with null. The following table describes the fill policies and the values that are to be filled.

Fixed Fill Policy

To fill a missing value with a fixed value, you can add the fixed value to the end of the number sign (#). You can specify the fixed value as a positive or negative number. The following sample code provides an example on the valid format:

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

Examples: 1h-sum-fixed#6 and 1h-avg-fixed#-8

Downsampling examples

Three downsampling examples are 1m-avg, 1h-sum-zero, and 1h-sum-near.

Parameter: aggregator

After data is downsampled, values along multiple timelines are obtained and timestamps of these timelines are aligned. You can perform aggregation to merge these timelines into one by aggregating the values at each aligned timestamp. Aggregation is not performed if only one timeline exists. During aggregation, each timeline must have a value at each aligned timestamp. If no value can be found at an aligned timestamp, interpolation is performed. For more information, see the following "Interpolation" section.

Interpolation

If a timeline has no value at a timestamp, a value is interpolated to the timeline at the timestamp. This occurs only if you do not specify a fill policy and one of the other timelines to be aggregated has a value at this timestamp.

An example is used to explain interpolation. In this example, you want to merge two timelines by using the sum operator. The downsampling and aggregation settings are {"downsample": "10s-avg", "aggregator": "10s-avg", "aggregator": "sum"}. After the data is downsampled based on 10s-avg, values can be found on the following timestamps along the two timelines:

The timestamps of timeline 1 on which values can be found are t+0, t+10, t+20, and t+30. The timestamps of timeline 2 on which values can be found are t+0, t+20, and t+30.

Along timeline 2, the value at the t+10 timestamp is missing. Before the data is aggregated, a value is interpolated for timeline 2 at this timestamp. The interpolation method varies based on aggregation operators. The following table lists the operators and interpolation methods.

Parameter: filters

You can use the following methods to configure the filters parameter:

• Use tagk to specify filters.

  • tagk = *: You can group the tag values of a tag key to aggregate the same tag values.

  • tagk = tagv1|tagv2: You can aggregate the tagv1 values of the tag key and aggregate the tagv2 values of the tag key.

• Specify filters in the JSON format. The following table describes the parameters.

Filter types

Sample requests

Sample requests with filters

Request body:

{
    "start" : 1346846400,
    "end" :   1346846411,
    "msResolution" : true,
    "queries" : [
        {
            "metric" : "wind",
            "fields" : [
                {
                    "field" : "speed",
                    "aggregator" : "none",
                    "alias" : "column_speed"
                },
                {
                      "field" : "*",
                    "aggregator" : "none",
                    "alias" : "column_"
                }
            ],
            "filters" : [
                {
                    "filter" : "IOTE_8859_0005|IOTE_8859_0004",
                    "tagk" : "sensor",
                    "type" : "literal_or"
                }
            ]
        }
    ]
}

Query result

If a query is successful, the HTTP status code is 200 and the response is returned in the JSON format. The following table describes the response parameters.

Sample responses:

• The following sample code shows the query result when the aggregator parameter is set to none.

[
   {
      "metric":"wind",
      "columns":[
         "timestamp",
         "column_speed",
         "column_description",
         "column_direction",
         "column_level",
         "column_speed"
      ],
      "tags":{
         "city":"hangzhou",
         "country":"china",
         "province":"zhejiang",
         "sensor":"IOTE_8859_0005"
      },
      "aggregatedTags":[],
      "values":[
         [ 1346846406000, null, "Fresh breeze", "East", 0.5, null ],
         [ 1346846407000, null, "Fresh breeze", "South", 1.5, null ]
   },
   {
      "metric":"wind",
      "columns":[
         "timestamp",
         "column_speed",
         "column_description",
         "column_direction",
         "column_level",
         "column_speed"
      ],
      "tags":{
         "city":"hangzhou",
         "country":"china",
         "province":"zhejiang",
         "sensor":"IOTE_8859_0004"
      },
      "aggregatedTags":[],
      "values":[
         [ 1346846400000, 40.4, "Fresh breeze", "East", 0.4, 40.4 ],
         [ 1346846401000, 41.4, "Fresh breeze", "South", 1.4, 41.4 ],
         [ 1346846402000, 42.4, "Fresh breeze", "West", 2.4, 42.4 ],
         [ 1346846403000, 43.4, "Fresh breeze", "North", 3.4,43.4 ]
   }
]

• The following sample code shows the query result when the aggregator parameter is set to avg. The result indicates the average wind speed and the average wind level based on all the sensors in the Hangzhou city.

[
  {
    "metric": "wind",
    "columns": [
      "timestamp",
      "avg_level",
      "avg_speed"
    ],
    "tags": {
      "city": "hangzhou"
    },
    "aggregatedTags": [
      "country",
      "province",
      "sensor"
    ],
    "values": [
      [1346846400000, 0.25, 40.25],
      [1346846401000, 1.25, 41.25],
      [1346846402000, 2.5, 42.5],
      [1346846411000, 5.5, null]
    ]
  }
]

Parameter: hint

Scenarios

In most cases, a query hint is used to reduce the response time of queries. For example, Tags A and Tags B are specified and the timelines hit by Tags B are included by the timelines hit by Tags A. In this case, data is not read from the timelines hit by Tag A. The intersection between the set of timelines hit by Tag A and the set of timelines hit by Tag B is equal to the set of timelines hit by Tag B.

Format description

• The current TSDB version allows you to use only the tagk parameter in a hint to limit query indexes.

• In the tag key-value pairs specified by the tagk parameter, the tag values of the tag keys must be the same. Valid values: 0 and 1. If the tag values are 0, the indexes corresponding to the tag keys are not used. If the tag values are 1, the indexes corresponding to the tag keys are used.

Version description

The query hint feature is supported by TSDB V2.6.1 and later.

Sample requests

Hint that applies to a subquery

{
  "queries": [
    {
      "metric": "demo.mf",
      "tags": {
        "sensor": "IOTE_8859_0001",
        "city": "hangzhou",
        "province": "zhejiang",
        "country": "china"
      },
      "fields": [
        "speed"
      ],
      "hint": {
        "tagk": {
          "dc": 1
        }
      }
    }
  ]
}

Hint that applies to the entire query

{
  "queries": [
    {
      "metric": "demo.mf",
      "tags": {
        "sensor": "IOTE_8859_0001",
        "city": "hangzhou",
        "province": "zhejiang",
        "country": "china"
      },
      "fields": [
        "speed"
      ]
    }
  ],
  "hint": {
    "tagk": {
      "dc": 1
    }
  }
}

Exceptions

An error is returned when the tag values in the key-value pairs specified by the tagk parameter contain both 0 and 1.

{
  "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 is 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=[])"
    }
}

An error is returned when a tag value in the key-value pairs specified by the tagk parameter is not 0 or 1.

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

The following error message is 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=[])"
    }
}