REST API Examples: TimeSeries Tables and Calendars

This topic provides a REST tutorial on creating, inserting, loading, and updating TimeSeries data and working with TimeSeries calendars.

The REST path /api/servers/{alias}/databases/{dbname}/timeseries provides APIs specific to TimeSeries data in HCL OneDB™. There are two sub-paths:
  • /api/servers/{alias}/databases/{dbname}/timeseries/tables for accessing TimeSeries tables
  • /api/servers/{alias}/databases/{dbname}/timeseries/calendars for accessing TimeSeries calendars

The REST API server supports both BSON TimeSeries row types and custom TimeSeries row types. A BSON TimeSeries row type includes a timestamp and a BSON field. The BSON field provides a flexible schema to hold any fields you need for your TimeSeries data, both now and in the future. BSON TimeSeries tables can be created automatically through the REST API. You can also create a custom TimeSeries row type through SQL that defines the particular fields and data types that make up your TimeSeries elements. Custom row types do not have the same flexibility as BSON row types, but can be customized to your exact data structure. Whichever route you choose for structuring your TimeSeries data, the REST API will detect your TimeSeries schema and automatically adapt your REST inserts and queries accordingly.

This topic provide a tutorial on creating TimeSeries tables, inserting, loading, and updating TimeSeries data, and working with TimeSeries calendars. For TimeSeries query examples, see REST API Examples: TimeSeries Queries.

Example 1: List TimeSeries tables in a database

To list all of the TimeSeries tables in a database, send an HTTP GET request to the timeseries/tables path for a particular database. Keep in mind that this API will only list the TimeSeries tables in the database; it does not include any relational tables or JSON collections that may also exist in the database.

Request
GET http://localhost:8080/api/servers/server1/databases/mydb/timeseries/tables
Response
[
    "ts_data",
    ...
]

Example 2: Create a BSON TimeSeries table

The REST API provides an easy to way to create a new BSON TimeSeries table.

TimeSeries tables created through the REST API will have the following schema:
create table <tabname> (
        id bigserial not null primary key,
        data timeseries(generic_bson_t),
        properties bson
);
TimeSeries row type will be a BSON row type that will have datetime field named timestamp and a BSON field named payload. 
create row type generic_bson_t (timestamp datetime year to fraction(5), payload bson);

By using BSON in the row type, you have the flexibility to put any mappable data type into the JSON (BSON) document for each TimeSeries element.

Besides the BSON TimeSeries data, each row in your TimeSeries table will have a unique bigserial id and an additional properties BSON column that can store any additional metadata about that particular row.

To create a new BSON TimeSeries table in REST, send an HTTP POST to the timeseries/tables path. The body of your request must include a JSON document that provides the name for the new table. You can optionally also specify a dbspace to create the table in. The HTTP response that you receive back will describe the TimeSeries table that was just created.

Request
POST http://localhost:8080/api/servers/server1/databases/mydb/timeseries/tables
Request Body
{
  "name": "meters"
}
Response
{
    "tabid": 149,
    "tabname": "meters",
    "owner": "onedbsa",
    "created": {
        "$date": "2021-03-12T06:00:00Z"
    },
    "type": "timeseries",
    "columns": [
        {
            "name": "id",
            "type": "bigserial",
            "size": 19,
            "nullable": false
        },
        {
            "name": "data",
            "type": "timeseries(generic_bson_t)",
            "size": 2048,
            "nullable": false,
            "typeSchema": [
                {
                    "name": "timestamp",
                    "type": "datetime year to fraction(5)"
                },
                {
                    "name": "payload",
                    "type": "bson"
                }
            ]
        },
        {
            "name": "properties",
            "type": "bson",
            "size": 4096,
            "nullable": true
        }
    ],
    "primaryKeyColumns": [
        "id"
    ]
}

Example 3: Get TimeSeries table information

If you need to get information about your TimeSeries table, including column names and the row type definition, run the following HTTP REST request. In this example, meters respresents the name of your TimeSerires table.

Request
GET http://localhost:8080/api/servers/server1/databases/mydb/tables/timeseries/meters
Response
{
    "tabid": 149,
    "tabname": "meters",
    "owner": "onedbsa",
    "created": {
        "$date": "2021-03-12T06:00:00Z"
    },
    "type": "timeseries",
    "columns": [
        {
            "name": "id",
            "type": "bigserial",
            "size": 19,
            "nullable": false
        },
        {
            "name": "data",
            "type": "timeseries(generic_bson_t)",
            "size": 2048,
            "nullable": false,
            "typeSchema": [
                {
                    "name": "timestamp",
                    "type": "datetime year to fraction(5)"
                },
                {
                    "name": "payload",
                    "type": "bson"
                }
            ]
        },
        {
            "name": "properties",
            "type": "bson",
            "size": 4096,
            "nullable": true
        }
    ],
    "primaryKeyColumns": [
        "id"
    ]
}

Example 4: Insert a base row into a TimeSeries table

There are two types of inserts for TimeSeries – the insert of the row in the base TimeSeries table and then the insert of new elements into the TimeSeries itself. Both types of inserts supported by the REST API. This example covers the insert of the row into the base TimeSeries table while Example 5 below shows insert of TimeSeries elements.

After having newly created your TimeSeries table, you will need to populate the table with the base rows. In the case of a BSON TimeSeries table created by the REST API, these are the rows that include your unique id and any additional metadata properties you want to store along with the row.

To insert the base row, you use the relational tables path for insert because you are inserting into the base table. This contrasts with the next example where you will use the timeseries/tables path to insert new elements into the actual TimeSeries data structure.

The following REST request inserts a base row into the TimeSeries table, but does not initialize the TimeSeries data structure. If the TimeSeries data structure is not initialized, the REST API will do it automatically whenever the first TimeSeries element is inserted.

Request
POST http://localhost:8080/api/servers/server1/databases/mydb/tables/meters/insert
Request Body
{
  "id": 101,
  "properties": { 
        "type": "VRO",
        "deployed": { "$date": "2021-03-12T10:33:00Z" }
   }
}
Response
{
    "n": 1,
    "responseTime": 35
}

Alternatively, you can also include an $options document for the TimeSeries column when you do a base row insert. If you do so, the REST API will initialize the TimeSeries object as part of the insert using the options provided. The $options document can contain the fields shown in the following table.
Table 1. TimeSeries $options document
Property Description
type regular or irregular

If not specified, the REST API will default to creating an irregular TimeSeries. For more information about TimeSeries types, see Regular time series and Irregular time series.

calendar A calendar name or pattern document defining the TimeSeries calendar.

If specified as a string, the calendar field must be the name of an existing calendar. If the calendar field is not specified, the default value is the pre-defined ts_1min calendar, which allows for TimeSeries elements to be inserted for every minute.

If specified as a document, the calendar field will be interpreted as a pattern document. A pattern document must have an time unit field (second, minute, hour, day, week, month, or year). Then it can either have an integer frequency or a list of interval documents. When specified as interval documents, each document must have integer duration and a type (on or off).

For example, a 15 second calendar can be either specified with a frequency 
{"frequency": 15, "unit": "second" }
or with a list of interval documents
{
  "intervals": [
        { "duration": 1, "type": "off" },
        { "duration": 14, "type": "on" }
  ],
  "unit": "second"
}
container A container name.

If not specified, a container will be automatically created by the database server in the autopool. See Manage container pools.
origin A timestamp representing the origin of the TimeSeries.

If not specified, the REST API will use 12:00am on January 1st of the current year as the origin.
threshold The maximum number of time series elements that are stored in-row. Extra elements are stored in containers.

The default threshold is 0. A value of 0 means that all time series elements are stored in containers.
nElements The number of elements initially allocated for the TimeSeries. If the number of elements exceeds this value, the time series is expanded through reallocation. The default is 0.

The following REST request inserts another row in the base TimeSeries table and initializes a regular TimeSeries with the ts_15min calendar. Notice the addition of the data field in the insert document. data is the name of the TimeSeries column to be initialized and its value is the $options document that holds our initialization options.

Request
POST http://localhost:8080/api/servers/server1/databases/mydb/tables/meters/insert
Request Body
{
  "id": 102,
  "data": {
      "$options": {
            "type": "regular",
            "calendar": "ts_15min",
            "origin": { "$date": "2021-03-01T16:00:00Z" }
    }
  },
  "properties": { 
        "type": "DES",
        "deployed": { "$date": "2021-03-01T16:11:32Z" }
   }
}
Response
{
    "n": 1,
    "responseTime": 81
}

In addition to the $options document, the REST API also allows you to include the insert of your first TimeSeries element as part of the base row insert. To include the first TimeSeries element, you must include all of the fields of the TimeSeries row type in the value of the TimeSeries column. Since our example is based on the BSON TimeSeries, you would include a timestamp and payload that defines the initial value of the TimeSeries. You can combine this with the $options document as shown in the REST request below. Or you can omit the $options document to get all of the default values.

In the next example REST request, we are inserting a new base row for with id 103. We are inserting an initial TimeSeries element with timestamp 2021-03-01T18:00:00 and payload { "value": 114.3 }. We are including an options document to get a regular TimeSeries with a 15 minute calendar, but you will also notice that the calendar is specified as a pattern document this time instead of as a name. The various calendar formats are also described in Example 9: Create a TimeSeries calendar.

Request
POST http://localhost:8080/api/servers/server1/databases/mydb/tables/meters/insert
Request Body
{
  "id": 103,
  "data": {
      "timestamp": { "$date": "2021-03-01T18:00:00Z" },
      "payload": { "value": 114.3 },  
      "$options": {
            "type": "regular",
            "calendar": { "frequency": 15, "unit": "minute" },
            "origin": { "$date": "2021-03-01T16:00:00Z" }
    }
  },
  "properties": { 
        "type": "VRO",
        "deployed": { "$date": "2021-03-01T17:25:09Z" }
   }
}
Response
{
    "n": 1,
    "responseTime": 37
}

Example 5: Insert a new TimeSeries element

To insert an element into a TimeSeries, POST to the timeseries/tables/<tabname>/insert path for the TimeSeries table. If the TimeSeries table has not be initialized as shown with the $options document examples in Example 4: Insert a base row into a TimeSeries table, the REST API will initialize the TimeSeries automatically as an irregular time series with a 1 minute calendar frequency and an origin of midnight on January 1st of the current year.

When you POST to the timeseries element insert API, you need to provide a document that has key-value pairs that match to the columns that uniquely identify the TimeSeries from the base table (for example, the id column) and then you must have a key-value pair that represents the TimeSeries column name and the value of the element to insert. The REST examples in this topic continue to show the use of a BSON row type with timestamp and payload fields, but you if use a custom row type instead, you will have to name the keys in your JSON insert document based on your row type field names.

This example shows how to insert a TimeSeries element with payload { "value": 115.1 } at timestamp 2021-03-01T19:00:00 into the meter with id 103. Keep in mind that since this is a BSON timeseries, you can include as many key-value pairs in the payload document as you wish.

Request
POST http://localhost:8080/api/servers/server1/databases/mydb/timeseries/tables/meters/insert
Request Body
{
      "id": 103,
      "data": {
          "timestamp": { "$date": "2021-03-01T18:00:00Z" },
          "payload": { "value": 114.3 }
      }
}
Response
{
    "n": 1,
    "responseTime": 29
}

Example 6: Load multiple TimeSeries elements

To load (insert) multiple TimeSeries elements in a single request, use the REST load API. The load API uses the JDBC TimeSeries Loader to load TimeSeries data in an efficient way.

To load TimeSeries data, POST a JSON document with a data array of TimeSeries elements. Each TimeSeries element must include the identifying columns from the base table (for example, the id column), the timestamp field, and all other fields required for the TimeSeries row type. You can load data from multiple different TimeSeries (multiple different identifying columns) in the same load request.

Request
POST http://localhost:8080/api/servers/server1/databases/mydb/timeseries/tables/meters/load
Request Body
{
  "data": [
    { "id": 103, "timestamp": { "$date": "2021-03-01T18:15:00Z" }, "payload": { "value": 114.6 } },
    { "id": 103, "timestamp": { "$date": "2021-03-01T18:30:00Z" }, "payload": { "value": 114.1 } },
    { "id": 103, "timestamp": { "$date": "2021-03-01T18:45:00Z" }, "payload": { "value": 114.2 } },
    { "id": 103, "timestamp": { "$date": "2021-03-01T19:00:00Z" }, "payload": { "value": 114.4 } },
    { "id": 103, "timestamp": { "$date": "2021-03-01T19:15:00Z" }, "payload": { "value": 113.9 } },
    { "id": 103, "timestamp": { "$date": "2021-03-01T19:30:00Z" }, "payload": { "value": 114.0 } },
    { "id": 103, "timestamp": { "$date": "2021-03-01T19:45:00Z" }, "payload": { "value": 113.2 } },
    { "id": 103, "timestamp": { "$date": "2021-03-01T20:00:00Z" }, "payload": { "value": 114.3 } },
    { "id": 102, "timestamp": { "$date": "2021-03-01T18:15:00Z" }, "payload": { "value": 123.1 } },
    { "id": 102, "timestamp": { "$date": "2021-03-01T18:30:00Z" }, "payload": { "value": 122.9 } },
    { "id": 102, "timestamp": { "$date": "2021-03-01T18:45:00Z" }, "payload": { "value": 122.5 } }
  ]
}
Response
{
    "n": 11,
    "responseTime": 37
}

Example 7: Update TimeSeries element

To update an element in a TimeSeries, POST a JSON document to the update API representing the new element.

The format of the update document is the same as a TimeSeries element insert. The JSON document in the request body must include the key-value pairs that match to the columns that uniquely identify the TimeSeries from the base table (for example, the id column) and a key-value pair for the TimeSeries column name and the updated TimeSeries element. The value provided as the updated TimeSeries element will replace the existing value at the specified timestamp.

Request
POST http://localhost:8080/api/servers/server1/databases/mydb/timeseries/tables/meters/update
Request Body
{
      "id": 103,
      "data": {
          "timestamp": { "$date": "2021-03-01T18:00:00Z" },
          "payload": { "value": 114.5 }
      }
}
Response
{
    "n": 1,
    "responseTime": 42
}

Example 8: List TimeSeries calendars in a database

TimeSeries calendars are used to define the timestamp interval when creating a regular TimeSeries. They are also used in certain TimeSeries pipeline queries when aggregating TimeSeries data across certain time intervals. The REST API provides a way of listing the calendars that exist in a database.

To list all of the TimeSeries calendars in a database, send an HTTP GET request to the timeseries/calendars path for a particular database.

Request
GET http://localhost:8080/api/servers/server1/databases/mydb/timeseries/calendars
Response
[
    "ts_15min",
    "ts_1day",
    "ts_1hour",
    "ts_1min",
    "ts_1month",
    "ts_1week",
    "ts_1year",
    "ts_30min"
]

Example 9: Create a TimeSeries calendar

The REST API provides an easy to way to create a new TimeSeries calendar.

POST a document to the timeseries/calendars path that represents the new calendar to create. A calendar must include a name, a start date, and a pattern.

A calendar pattern is specified as document and must have an time unit (second, minute, hour, day, week, month, or year). Then it can either have an integer frequency or a list of interval documents. When specified as interval documents, each document must have integer duration and a type (on or off).

For example, a 15 second calendar can be either specified with a integer frequency

{ "frequency": 15, "unit": "second" }
or with a list of interval documents 
{ 
    "intervals": [ 
        { "duration": 1, "type": "off" }, 
        { "duration": 14, "type": "on" }
    ],
    "unit": "second"
}

For more information on calendar patterns, see CalendarPattern data type.

The HTTP response after creating a new calendar is a JSON description of the newly created calendar. Note that the response will always describe the pattern using the list of interval document format because that is how it is stored on the database server. If you create the calendar using the shorter frequency format, it will be automatically translated for you to the list of interval documents.

Request
POST http://localhost:8080/api/servers/server1/databases/mydb/timeseries/calendars
Request Body
{
    "name": "ts_15sec",
    "start": { "$date": "2021-01-01T00:00:00Z" },
    "pattern": { "frequency": 15, "unit": "second" }
}
Response
{
    "name": "ts_15sec",
    "startDate": {
        "$date": "2021-01-01T00:00:00Z"
    },
    "patternStartDate": {
        "$date": "2021-01-01T00:00:00Z"
    },
    "pattern": {
        "intervals": [
            {
                "duration": 1,
                "type": "on"
            },
            {
                "duration": 14,
                "type": "off"
            }
        ],
        "unit": "second"
    }
}

Example 10: Get TimeSeries calendar information

If you need to get information about a TimeSeries calendar, including the calendar pattern, run an HTTP GET request against the timeseries/calendars/calendar_name API.

Request
GET http://localhost:8080/api/servers/server1/databases/mydb/timeseries/calendars/ts_15sec
Response
{
    "name": "ts_15sec",
    "startDate": {
        "$date": "2021-01-01T00:00:00Z"
    },
    "patternStartDate": {
        "$date": "2021-01-01T00:00:00Z"
    },
    "pattern": {
        "intervals": [
            {
                "duration": 1,
                "type": "on"
            },
            {
                "duration": 14,
                "type": "off"
            }
        ],
        "unit": "second"
    }
}

Example 11: Drop a TimeSeries calendar

To drop a TimeSeries calendar from the database, run an HTTP DELETE request against the TimeSeries calendar url. For example:
Request
DELETE http://localhost:8080/api/servers/server1/databases/mydb/timeseries/calendar/ts_15sec
Response
HTTP Status OK (200)