Data Access API Examples

This page discusses mostly the Warm Path endpoint of the Data Access API. The DA API endpoint retrieves both raw and aggregate timeSeries warm data. You can also get just the last value of a specified variable. Some of the discussed cases include best practices that you should follow while designing your queries. At the end of the article, you will also find more best practices applicable to any query you use.

Get Raw Telemetry​​​

Use the select object with the properties field (see below) to choose the properties that you want to see in response. Please note that you cannot use the properties field in the select object along with the aggregate function properties of min, max, avg, sum, first, last, or count; it is either/or. Either you have the properties field or you have one or many of the aggregate function properties.

Request

{
  "date": {
    "from": "<string>",
    "to": "<string>"
  },
  "filter": "<string>",
  "select": {
    "properties": "<string>"
  },
  "orderBy": {
    "property": "<string>",
    "order": "<string>"
  },
  "limit": "<int>"
}

Example

In this example, suppose the client has sent in a request object as follows:

{
  "date": {
    "from": "2018-08-01T00:00:00Z",
    "to": "2018-08-31T23:59:59Z"
  },
  "filter": "",
  "select": {
    "properties": "objectId,model"
  },
  "limit": "100"
}

The response for this request will be:

{
  "data": [
    {
      "objectId": "<string>",
      "model": "<string>"
    }
  ]
}

The client can also send in a request with custom properties as follows:

{
  "date": {
    "from": "2018-08-01T00:00:00Z",
    "to": "2018-08-31T23:59:59Z"
  },
  "filter": "",
  "select": {
    "properties": "objectId,model,mycustomproperty"
  },
  "limit": "100"
}

The response for this request will be:

{
  "data": [
    {
      "objectId": "<string>",
      "model": "<string>",
      "mycustomproperty": "<T>"
    }
  ]
}

​where T is whatever type that this property returns from the database.

Get Last Value

There are cases where you'd want to get just the last value of some variable. You can do that using select.last filtering.

Example

The following request will fetch the last value of "voltage" variable of some object.

{
    "filter": "objectId='22ab830b-6fc6-4bf5-9579-8ae38088074a' AND variable='voltage' AND model='abb.ability.device'",
    "date": {
        "from": "0001-01-01T00:00:00Z"
    },
    "select": {
        "last": "value, timestamp"
    },
    "groupBy": {
        "properties": "objectId, variable, model"
    }
}

If the object and the property exist (and have data), the response will be as follows:

{
  "data": [
    {
      "objectId": "22ab830b-6fc6-4bf5-9579-8ae38088074a",
      "variable": "voltage",
      "model": "abb.ability.device",
      "last": {
        "value": 34.291116909259515,
        "timestamp": "2020-12-10T23:59:50"
      }
    }
  ]
}

If there is no data, you wil get an empty array:

{
  "data": []
}

TIP

You can try this request on the API Playground.

Best Practices

Below you can find some recommendations that will improve the performance of your Last Value queries (when Redis is configured as the Last Value provider):

• Include the model in your filter - whenever your case allows it, include the model in your query to improve the performance of retrieving the data.

  For example, instead of just using objectId='a0707c42-e415-4e97-b777-30135dfde51e', use objectId='a0707c42-e415-4e97-b777-30135dfde51e' AND model='abb.ability.device'.

• Use STARTS_WITH/ENDS_WITH/HAS only when necessary in your filter - these operators are quite costly in terms of performance. If you do not have to use them, don't.

  For example, instead of using variable STARTS_WITH 'utilization.', use variable = 'utilization.memory' OR variable = 'utilization.cpu'.

• Avoid using to or from in the Last Known Value queries unless you have a particular use case for that.
  For example, when you set to to a specific date, and the last event change happens after that date, the query will return an empty response.

  Understanding the behavior of the time range filter

  When using the Redis Cache to retrieve last values for alarms, events, and variables, responses will be returned if their time value falls between the specified from and to time values. When you don't specify from or to value, all times are considered. However, there may be cases where the from time is used, for example, to see any last values from the last day. Another example would be to check whether certain alarms or events were last reported between a specific from and to time.

  Redis Cache keeps data only for 24 hours.

Get Aggregate Telemetry

Retrieves aggregate warm data for the constructed request. This request object for this endpoint has many variations and capabilities. Descriptions of the possibilities follow.

Request

{
  "date": {
    "from": "<string>",
    "to": "<string>"
  },
  "filter": "<string>",
  "select": {
    "count": "",
        "min": "<string>",
        "max": "<string>",
        "first": "<string>",
        "sum": "<string>",
        "avg": "<string>",
  },
  "groupBy": {
    "time": "<string>",
    "properties": "<string>"
  },
  "orderBy": {
    "property": "<string>",
    "order": "<string>"
  },
  "limit": "<int>"
}

Request Constructions

The aggregate requests can be constructed to retrieve a grouped by the histogram of results, a histogram with no grouping, grouping without a histogram, or what's known as a fleet query which is no grouping and no histogram. Each construction with examples is given below.

Histogram with Grouping

Note: When using a last known state query with the Redis provider, grouping by a histogram (i.e. groupBy with a time request) is not supported.​

Request Example

{
  "date": {
    "from": "2018-01-01T09:00:00.000Z",
    "to": "2018-08-03T09:59:59.000Z"
  },
  "filter": "",
  "select": {
    "min": "value",
    "max": "value"
  },
  "groupBy": {
    "time": "1h",
    "properties": "objectId,variable"
  },
  "orderBy": {
    "property": "timestamp",
    "order": "asc"
  },
  "limit": 10000
}

Response Example

{
    "data": [
        {
            "objectId": "17c303d6-1667-4f1a-9ec3-0958d3f1b7b5",
            "variable": "MT00",
            "timestamp": "2018-01-10T11:00:00Z",
            "min": {
                "value": 0
            },
            "max": {
                "value": 0
            }
        },
        {
            "objectId": "18c303d6-1667-4f1a-9ec3-0958d3f1b7b5",
            "variable": "MT0",
            "timestamp": "2018-01-11T02:00:00Z",
            "min": {
                "value": 64
            },
            "max": {
                "value": 64
            }
        }
    ]
}

Histogram with Grouping and Multiple Properties in a Single Aggregate

Request Example

{
  "date": {
    "from": "2018-01-01T09:00:00.000Z",
    "to": "2018-10-31T09:59:59.000Z"
  },
  "filter": "",
  "select": {
    "last": "value,timestamp"
  },
  "groupBy": {
    "properties": "objectId,variable"
  }
}

Response Example

{
    "data": [
        {
            "objectId": "00334ce8-20e4-4947-bc0b-092dcb131912",
            "variable": "temperature.temp0",
            "last": {
                "value": 19,
                "timestamp": "2018-10-29T05:49:46.0922082Z"
            }
        },
        {
            "objectId": "00334ce8-20e4-4947-bc0b-092dcb131912",
            "variable": "temperature.temp1",
            "last": {
                "value": 20,
                "timestamp": "2018-10-29T05:54:46.0922082Z"
            }
        }
    ]
}

Histogram without Grouping

Request Example

{
  "date": {
    "from": "2018-01-01T09:00:00.000Z",
    "to": "2018-08-03T09:59:59.000Z"
  },
  "filter": "",
  "select": {
    "min": "value",
    "max": "value"
  },
  "groupBy": {
    "time": "1h"
  },
  "orderBy": {
    "property": "timestamp",
    "order": "asc"
  },
  "limit": 10000
}

Response Example

{
    "data": [
        {
            "timestamp": "2018-01-10T11:00:00Z",
            "min": {
                "value": 0
            },
            "max": {
                "value": 0
            }
        },
        {
            "timestamp": "2018-01-11T02:00:00Z",
            "min": {
                "value": 64
            },
            "max": {
                "value": 64
            }
        }
    ]
}

Grouping without Histogram

Request Example

{
  "date": {
    "from": "2018-01-01T09:00:00.000Z",
    "to": "2018-08-03T09:59:59.000Z"
  },
  "filter": "",
  "select": {
    "min": "value",
    "max": "value",
    "last": "value"
  },
  "groupBy": {
    "properties": "objectId,variable"
  },
  "orderBy": {
    "property": "timestamp",
    "order": "asc"
  },
  "limit": 10000
}

Response Example

{
    "data": [
        {
            "objectId": "17c303d6-1667-4f1a-9ec3-0958d3f1b7b5",
            "variable": "MT00.979166666666667",
            "min": {
                "value": 0.065403129230141341
            },
            "max": {
                "value": 0.065403129230141341
            },
            "last": {
                "value": 0.065403129230141341
            }
        },
        {
            "objectId": "18c303d6-1667-4f1a-9ec3-0958d3f1b7b5",
            "variable": "MT0",
            "min": {
                "value": 1
            },
            "max": {
                "value": 1
            },
            "last": {
                "value": 1
            }
        },
        {
            "objectId": "2a85ecee-97e5-4151-96b2-8b56ba78613d",
            "variable": "brokenRotorIndex",
            "min": {
                "value": 5
            },
            "max": {
                "value": 5
            },
            "last": {
                "value": 5
            }
        }
    ]
}

Fleet Query

Request Example

{
  "date": {
    "from": "2018-01-01T09:00:00.000Z",
    "to": "2018-08-03T09:59:59.000Z"
  },
  "filter": "variable='skinTemperature'",
  "select": {
    "min": "value",
    "max": "value",
    "last": "value"
  },
  "orderBy": {
    "property": "timestamp",
    "order": "asc"
  },
  "limit": 10000
}

Response Example

{
    "data": [
        {
            "min": {
                "value": 0.065403129230141341
            },
            "max": {
                "value": 0.065403129230141341
            },
            "last": {
                "value": 0.065403129230141341
            }
        }
    ]
}

The successful response object will contain either all properties specified in the 'select' clause or all properties specified in the 'group by' clause, plus each function in the 'select' statement will contain the calculated value in the same named property.

Standard Response Object

{
  "data": [
    {
      "propertyrequested": "<T>"
    }
  ]
}

Specified Response Object

In this example, suppose the client has sent in a request object as follows:

{
  "date": {
    "from": "2018-01-01T09:00:00.000Z",
    "to": "2018-08-03T09:59:59.000Z"
  },
  "filter": "",
  "select": {
    "min": "value",
    "max": "value"
  },
  "groupBy": {
    "time": "1h",
    "properties": "objectId,variable"
  },
  "orderBy": {
    "property": "timestamp",
    "order": "asc"
  },
  "limit": 10000
}

The response for this request will be:

{
  "data": [
    {
      "timestamp": "2018-07-10T11:00:00Z",
      "objectId": "17c303d6-1667-4f1a-9ec3-0958d3f1b7b5",
      "variable": "MT00.0416666666666667",
      "min": {
          "value": 0.13052619222005157
      },
      "max": {
          "value": 0.13052619222005157
      }
    },
    {
      "timestamp": "2018-07-11T04:00:00Z",
      "objectId": "18c303d6-1667-4f1a-9ec3-0958d3f1b7b5",
      "variable": "MT0",
      "min": {
          "value": 15
      },
      "max": {
          "value": 15
      }
    }
  ]
}

In this example, suppose the client has sent in a request object as follows:

{
  "date": {
    "from": "2018-01-01T09:00:00.000Z",
    "to": "2018-08-03T09:59:59.000Z"
  },
  "filter": "",
  "select": {
    "max": "value,timestamp",
    "last": "value,timestamp"
  },
  "groupBy": {
    "properties": "objectId,variable"
  },
  "orderBy": {
    "property": "timestamp",
    "order": "asc"
  },
  "limit": 10000
}

The response for this request will be:

{
  "data": [
    {
      "objectId": "18c303d6-1667-4f1a-9ec3-0958d3f1b7b5",
      "variable": "MT0",
      "max": {
        "value": 95,
        "timestamp": "2018-07-11T04:01:35.6034Z"
      },
      "last": {
        "value": 95,
        "timestamp": "2018-07-11T04:01:35.6034Z"
      }
    },
    {
      "objectId": "2a85ecee-97e5-4151-96b2-8b56ba78613d",
      "variable": "brokenRotorIndex",
      "max": {
        "value": 5,
        "timestamp": "2018-05-01T15:12:09.473Z"
      },
      "last": {
        "value": 5,
        "timestamp": "2018-05-01T15:12:09.473Z"
      }
    }
  ]
}

Best Practices

Below you can find a few recommendations on how to make your Warm Path queries efficient. Following these might improve the response times significantly.

• Try to spread your queries in time - instead of sending multiple requests concurrently, try to send them with some delay in between. The Ability Platform uses Azure Time Series Insights component for its Warm Path offering. This Azure component is limited to serving maximum 10 concurrent requests. Sending more requests concurrently to the Data Access API will result in bigger latencies in response.
• When fetching files from the Data Access API (Object Storage or Global Storage), note that the response time is highly dependent on the number of files being stored. If the number of files is very high, you should expect the latencies to grow.
• If a single request contains query for both Warm Path and Last Value, the response will be returned only after the slowest of the requested queries is done.
• When making requests with multiple aggregates, the last value cannot be included with other aggregates when groupBy includes time.
• Avoid fleet queries with aggregations if possible. In the case of aggregation queries, do not use large time intervals with small-time breaks, which can cause reaching TSI v1 cardinality limit (which is equal to 150 000).

Query​ Expression Support

The default database implementation for warm data is Time Series Insights. It utilizes its own query language which the API translates from the filter property. The filter currently supports every operator of the ABB Ability™ Query Expression Language format.