Loading time series data with the MQTT protocol

You can load JSON documents into time series through the MQTT wire listener. The MQTT wire listener publishes data directly to the time series base table by internally running time series loader routines.

Before you begin

You cannot create a time series through the MQTT wire listener. Create a JSON time series with the REST API, the MongoDB API, or SQL statements.

For instructions on creating a JSON time series with SQL statements, see Example for JSON data: Create and load a time series with JSON documents.

Procedure

To load JSON data through the MQTT wire listener:

Set the following parameters in the wire listener configuration file:
- Set the wire listener type to MQTT: listener.type=mqtt
- Optional. Set the number of connections between the wire listener and each time series table: Set timeseries.loader.connections to the number of connections that you want.
Restart the wire listener.
From the MQTT clients, load the data into the time series table by publishing data as JSON documents.
- For BSON timeseries tables, i.e. tables where the timeseries row type contains only a timestamp plus one BSON column, the message argument of the PUBLISH packet must contain the following fields within the JSON documents:
  - One or more fields that identifies the primary key of the time series table. The field names must be the same as the primary key column names in the time series table.
  - A field that identifies the time stamp. The field name must be the same as the time stamp column in the TimeSeries row type.
  - One or more fields to insert into the BSON column in the TimeSeries row type. All fields that are not identified as a primary key column or the time stamp field are inserted into the BSON column.
  For example"
```
{ "pkey": value, "tstamp": value, "field1": value, "field2": value, ...}
```
  where "pkey" is the name of the primary key column, "tstamp" is the name of the timestamp column, and "field1", "field2", etc. are whatever fields you want in the BSON column of the timeseries.
  Note: For BSON timeseries tables, you do not use the BSON column name from the row type. The timeseries loader will extract the primary key field(s) and timestamp fields; all other fields will be inserted into the BSON column of the row type.
- For non-BSON timeseries tables, the message argument of the PUBLISH packet must contain the following fields within the JSON documents:
  - One or more fields that identify the primary key of the time series table. The field names must be the same as the primary key column names in the time series table.
  - A field that identifies the time stamp. The field name must be the same as the time stamp column in the TimeSeries row type.
  - One or more fields that match the names of the other columns in the TimeSeries row type.
  For example"
```
{ "pkey": value, "tstamp": value, "rowtypeField1": value, "rowTypeField2": value, ...}
```
  where the fields in addition to primary key and timestamp match the column name in the timeseries row type.
  If the timeseries row type has an integer column (named "intData") and a BSON column (named "bsonData"), the data will take the following format:
```
{ "pkey": value, "tstamp": value, "intData": 10, "bsonData":
  { "value1": 1.234, "label": "any fields can go here within the bsonData document..."}}
```

Example 1

The following example creates a BSON TimeSeries row type, a time series table, a time series container, and a time series instance:

CREATE ROW TYPE ts_data_j2(
      tstamp       datetime year to fraction(5),
      tsdata       BSON);

CREATE TABLE IF NOT EXISTS tstable_j2(
   id  VARCHAR(50) NOT NULL PRIMARY KEY,
   ts  timeseries(ts_data_j2)
) LOCK MODE ROW;

EXECUTE PROCEDURE
   TSContainerCreate('container_j', 'dbspace1', 'ts_data_j2', 512, 512);

INSERT INTO tstable_j2 VALUES(1, 'origin(2014-01-01 00:00:00.00000), 
                             calendar(ts_15min), container(container_j),
                             regular, threshold(0), []');

For this example, the message argument has and id field for the primary key, a tstamp field for the time stamp, and two fields for the BSON column:

{"id": "value", "tstamp": "time_stamp", "reading": number, "sensor_type": "string"}

The following sample Java code connects a client to the MQTT wire listener, loads a sensor reading, and disconnects from the client:

String broker = "tcp://localhost:1883";
String topicName = "mydb/tstable_j2";
String clientId = "mqttclient1";
MemoryPersistence persistence = new MemoryPersistence();

MqttClient sampleClient = new MqttClient(broker, clientId, persistence);
MqttConnectOptions connOpts = new MqttConnectOptions();
connOtps.setCleanSession(true);
sampleClient.connect(connOpts);

String content = "[{ \"id\": \"sensor1234\" , \"tstamp\": "2016-01-01 00:00:00" , 
                     \"reading\": 87.5,  \"sensor_type\": "TEMP"}];
MqttMessage message = new MqttMessage(conent.getBytes());
message.setQos(2);
sampleClient.publish(topicName, message);

sampleClient.disconnect();