This tutorial explains how to use Import templates when using the BellaDati Rest Action in the sender. It covers the following topics:
To be able to prepare the import template, it is necessary to prepare a sample CSV file with the same structure the data which are being sent from the sender to BellaDati.
In this example, the data sent from the sensor have the following structure:
sensor01,2018/9/28 20:17,26.55,42 |
The data are sent via MQTT Publisher to the MQTT Broker running on the receiver. When processed in the BellaDati IoT Data Controller, the values will be transformed into JSON and additional information is added to the message. The same structure has to be used in the sample CSV file:
"L_ID","L_RECEIVER","L_JSON","L_TIMESTAMP" "99","Receiver12","{""sensorId"":""sensor01"",""values"":[""sensor01"",""2018/9/28 18:17"",""26.55"",""42""],""mqttTopic"":""belladati"",""mqttMessageId"":-1}","1536317563496" |
The file has the following structure:
You can download the CSV file from here.
This file has to imported to the destination data set. On the import settings page, it is necessary to map the values to correct columns:
Next, it is possible to add additional columns which will contain the parsed values from the JSON.
Apply following transformation script, to parse the JSON and read the sensorId value:
JSONElement e = parseJSON(value(3)) return e.getString('sensorId') |
To get the date, use this formula:
JSONElement e = parseJSON(value(3)) JSONArray a = e.getJSONArray('values') as JSONArray a.getString(1) |
To get the MQTT topic, use this formula.
JSONElement e = parseJSON(value(3)) return e.getString('mqttTopic') |
To learn how to work with JSON, please read the following article: http://support.belladati.com/doc/Importing+from+File
Once the import settings are finished, click on Continue and import the data.
After the import, make sure that the ETL names of attributes are correctly set. If they are not correct, change them to match the code of the attribute.
You can download the XML backup of the final data set from here. Please note that you need to import the data to create the import template.
Data Set ID - ID of the Data Set. The ID can be found in the URL
Import Template ID - ID of the Import template
ID attribute - Code of the attribute ID (e.g. L_ID)
Receiver Attribute - Code of the attribute Receiver (e.g. L_RECEIVER)
Content Attribute - Code of the attribute JSON (e.g. L_JSON)
Timestamp Attribute - Code of the attribute Timestamp (e.g. L_TIMESTAMP)