What's TSDB?


TSDB is MODE's proprietary time series database. TSDB handles high-frequency time series data which can be difficult for ordinary databases to handle. High-speed query function enables interactive manipulation of data. Within MODE, TSDB can be enabled as a Smart Module to your project.

A TSDB Smart Module is required and used in all Sensor Cloud projects to store and process data.

In this section, we will walk through the setup of TSDB in the MODE Developer Console as well as the ways you can send and retrieve data.

Additionally, You can also reference TSDB's API here.

Setting up TSDB Smart Module


ONE | In the Developer Console, navigate to the Smart Modules page and select +NEW. Click +ADD on the section for TSDB.

Screenshot - Console Smart Modules Add TSDB

TWO | Give the TSDB Smart Module an ID and Description. Click Save.

Screenshot - Console TSDB Settings

THREE | (Optional) You can specify which events the TSDB Smart Module should be subcribed to. It is important to send only time series events to the TSDB Smart Module. Events that are not in the time series data format (described below) will be rejected by the TSDB Smart Module, and will incur unnecessary workload on the system. You can set up event filtering by editing the events filtering preferences. Select the TSDB Smart Module you have created and select the gear icon to update the settings.

Under the settings, there is a Subscried Events section. By default, there is a "*" meaning this TSDB smart module is subscribed to all events. You can replace "*" with one or more event type IDs you will be using in your time series data events. Click Save.

Screenshot - Console Edit TSDB Settings

ALL DONE! | Your TSDB Smart Module is now set up and ready to receive data.

Collecting times series data


To send an event to a TSDB module, you will need a device set up and attached to a home. When your device wants to send a time series data event, use one of the event types that your TSDB module has subscribed to.

A. Sending a single data point

To send a single data point to the TSDB Smart Module, you will use the endpoint:

PUT /devices/:deviceId/event

Request Body Example

{
  "eventType": "sensor-data",
  "eventData": {
    "timeSeriesData": [
      {
        "timestamp": "2016-01-01T12:00:00.123Z",
        "seriesId": "sensor01",
        "value": 1
      }
    ]
  }
}

The event data must contain the key timeSeriesData with an array of time series data as its value. Each data point is a JSON dictionary with the following fields:

timestamp | string | Entry must be formatted as an RFC3339 date/time. You may specify the timestamp down to the millisecond. Note that if the data point has a timestamp that is not "current", it may not be immediately available for retrieval after injection.

seriesId | string | Specifies the time series ID of the data to be stored. The entry must have a string which only contains upper and lower case alphabets, numbers, underscores ("_"), hyphens ("-") and colons (":"). Series ID that contains any other characters may not be stored in TSDB.

value | number | Specifies the value to be stored. The value must be a valid JSON number (64-bit floating point number).

B. Sending multiple data points

You can send multiple time series data points in one event data to the same endpoint above the value of the timeSeriesData as an array of data points.

Request Body Example

{
  "eventType": "timeSeriesData",
  "eventData": {
    "timeSeriesData": [
      {
        "timestamp": "2017-02-01T12:00:00.123Z",
        "seriesId": "sensor01",
        "value": 1
      },
      {
        "timestamp": "2017-02-01T12:01:20.456Z",
        "seriesId": "sensor01",
        "value": 10
      }
    ]
  }
}

In this example, both data points have the same seriesId as sensor01 but you can set different seriesId in one event datum if you want.

If multiple data points with the same seriesId and timestamp are delivered to a TSDB Smart Module, the last data point delivered to TSDB is saved. TSDB guarantees idempotency.

Retrieving time series data


A. Query data between two timestamps

After the time series data is stored in TSDB, applications may want to query the stored data to show graphs or otherwise make use of the data. To query time series data with the name sensor01, for instance, make a request to the following endpoint:

GET /homes/:homeId/smartModules/:moduleId/timeSeries/:seriesId/data

You must specify the timespan with the "begin" and "end" query parameters. The begin and end query parameters represent the query timestamps between which you want to get the data.

GET /homes/613/smartModules/tsdb/timeSeries/sensor01/data?begin=2017-02-01T00:00:00Z&end=2017-02-03T00:00:00Z

The homeId must be the ID for the home to which the device belongs. The moduleId has to be the TSDB Smart Module ID which you specified when you made TSDB smart module.

Response Body Example

{
  "aggregation": "avg",
  "begin": "2017-02-01T00:00:00Z",
  "data": [["2017-02-01T12:00:00Z", 5.5]],
  "end": "2017-02-03T00:00:00Z",
  "resolution": "10min",
  "seriesId": "sensor01"
}

The result contains only one data point value (i.e. 5.5) at "2017-02-01T12:00:00Z". You may wonder why the returned result only contains one data point instead of two (as previously we sent an array of two data points with two distinct values). This is because TSDB automatically chooses the suitable bucket resolution according to the query time span, and aggregates the multiple points into one based on the bucket resolution.

In this case, TSDB chose a 10 minute resolution based on the query span between "2017-02-01T00:00:00Z" and "2017-02-03T00:00:00Z", and the mean value of the two points at "2017-02-01T12:00:00.123Z" and "2017-02-01T12:01:20.456Z" are merged into one point because two points are in the same 10 min bucket. The timestamp of the merged point becomes the beginning time of the bucket.

If we shorten the query span, the resolution goes up. Suppose we change the “end” parameter to “2017-02-01T13:00:00Z”.

GET https://api.tinkermode.com/homes/613/smartModules/tsdb/timeSeries/sensor01/data?begin=2017-02-01T00:00:00Z&end=2017-02-01T13:00:00Z

Response Body Example

{
  "aggregation": "avg",
  "begin": "2017-02-01T00:00:00Z",
  "data": [
    ["2017-02-01T12:00:00Z", 1],
    ["2017-02-01T12:01:00Z", 10]
  ],
  "end": "2017-02-01T13:00:00Z",
  "resolution": "1min",
  "seriesId": "sensor01"
}

As you can see, the “resolution” becomes 1min, and the result shows two data points. The following table describes how the resolution is determined.

  • TimespanResolution
  • under 5 minutesn/a
  • 5 to 15 minutes5 seconds
  • 15 minutes to 1 hour15 seconds
  • 1 to 12 hours1 minute
  • 12 hours to 5 days10 minutes
  • 5 to 28 days1 hour
  • 28 days to 1 year1 day
  • 1 to 5 years1 week
  • over 5 years1 month

The default aggregation method is to average values when bucketing happens. You can also use other aggregation functions by specifying an aggregation query parameter with max, min, count, or sum.

An Important Note Regarding Data Availability

Data points added to a time series are available to be retrieved via the query API if the data points have timestamps that are close to the "current time" (within the last hour). However, if the data points have timestamps in the past, they may not show up on the query results immediately after injection. For most time resolutions, such data will become available about 10 minutes after injection. If the query results based on daily, weekly or monthly aggregation, the newly added data will be accounted for 4 hours after injection.

B. Query raw time series data points

This is an alternative way to fetch time series data. Instead of fetching data points by time ranges, you can fetch raw data points before or after a particular point in time.

The REST API endpoint is the same as that for query-by-time-range:

GET /homes/:homeId/smartModules/:moduleId/timeSeries/:seriesId/data

Instead of using the begin and end URL parameters to specify a time range, use the following parameters:

ts | string | Timestamp from which data points will be fetched in RFC3339 format.

limit | string | Maximum number of raw data points to be fetched. If it is a positive number, data points since the timestamp are returned. If it is a negative number, data points before the timestamp are returned. The value must be between -500 and 500 (inclusively).

Response Body Example

{
  "seriesId": "sensor01",
  "ts": "2017-02-01T00:00:00Z",
  "limit": 10,
  "data": [
    ["2017-02-01T00:12:20Z", 1],
    ["2017-02-01T01:11:00Z", 10]
  ]
}

The request will succeed and response code 200 will be returned when there are fewer data points than the specified limit (ex. if you specify limit as 100 and there are 20 data points).

NOTE: this API can be used to fetch the "last data point", if ts is the current time and limit is -1.

C. Query boundaries of time series

Sometimes we want to find the time boundaries of a time series, i.e. when the series started and when it ended. To do that, use the following endpoint:

GET get /homes/:homeId/smartModules/:moduleId/timeSeries/:seriesId/timeRange

The result will be a JSON that looks like the following:

Response Body Example

{
  "seriesId": "sensor01",
  "begin": "2017-03-03T12:00:00Z",
  "end": "2018-04-13T06:23:22Z"
}

Exporting time series data


You may export the raw data of one or more time series as CSV files. TSDB will pack and compress the CSV files into a single ZIP file for download.

To export time series data, follow these steps.

A. Initiate an export

Make a POST request to the following API endpoint:

POST /homes/:homeId/smartModules/:moduleId/export

The body of this request is a JSON object with the fields begin, end and sensorIds. The begin and end fields define the time range from which data will be exported, and must be strings in RFC3339 format. The value for the field seriesIds must be an array of strings that specify the series IDs of the time series to be exported.

Request Body Example

{
  "begin": "2017-02-01T00:00:00Z",
  "end": "2017-02-01T13:00:00Z",
  "seriesIds": ["sensor01", "sensor02"]
}

B. Confirm export status

The return object of the above call will look similar to the following example:

Response Body Example

{
  "dataUrl": "https://s3-us-west-2.amazonaws.com/scdata.tinkermode.com/export/_HOME_ID_/XXXX.zip…",
  "statusUrl": "https://s3-us-west-2.amazonaws.com/scdata.tinkermode.com/export/_HOME_ID_/XXXX.json…"
}

dataUrl is the URL where the final exported zipped file can be accessed. The URL is for one-time use only and expires in one hour. Your HTTP call to the data URL may fail right after you receive the response from the export POST call. Because gathering the data may take a while, you need to wait until the data is ready before accessing dataUrl. To know when it is ready, you need to poll statusUrl (GET repeatedly).

statusUrl is the URL string to tell if the data export is successful. While an export is still in progress, it returns a 404 ("Not found") HTTP status. If the data is ready to be downloaded, a GET request to statusUrl returns the following JSON object:

{ "status": "SUCCESS" }

If an error occurs, it returns:

{ "status": "ERROR" }

C. Download export file

After confirming the export was successful, your app can now download the exported data by making a GET request to dataUrl.

Deleting time series data


Time series data belonging to home is deleted when the home is deleted. Similary, if a TSDB smart module is deleted from a project, or when the entire project is deleted, all associated time series data will be deleted.

You may also choose to manually delete individual time series. You may send a DELETE request to the following endpoint. There are no parameters required.

DELETE /homes/:homeId/smartModules/:moduleId/timeSeries/:seriesId

Please note that time series deletion takes effect immediately and is irreversible.

Troubleshooting TSDB


A helpful tool in managing TSDB is the system logs. You can find the logs in the Developer Console by selecting the TSDB Smart Module.

This tool can be helpful when troubleshooting issues with data injection. In the example below, you can see the error message "Time Series DB [tsdb] did not store data of event [timeSeriesData] (no time series data found in event data)", which indicates an incorrectly formatted event data field.

Screenshot - Console TSDB Logs