Introduction
This article is broken up into two parts, one for each approach for pulling CSV data into Cumulocity IoT. The first approach is technical and hands-on, while the second approach is more automated and easy to use but less flexible.
The first part of this article is meant to guide users on how to convert CSV sensor data into Cumulocity IoT measurements by using Cumulocity IoT’s OpenAPI specification. The steps described here should be usable across a wide range of CSV formats, as long as you understand how your sensor data correlates to Cumulocity IoT’s domain model .
The second part of this article takes you through a much easier but less flexible approach using a tool from our open-source community called Application Builder , which includes a CSV simulator capability in the UI that can ingest very specific measurement data.
Pre-requisite
First approach
Cumulocity IoT tenant (free trial works), a graphical API tool like Postman, and some scripting knowledge
Second approach
Cumulocity IoT tenant (free trial works) with Application Builder deployed on the tenant as well as the csv-simulator-ms microservice
Detailed installation instructions for Application Builder and the CSV microservice can be found here
Approach 1 – CSV Simulator from Scratch
CSV Format
Let’s get started with an example CSV that includes 4 devices spread across 2 groups, each reporting a combination of pressure, temperature, and vibration from their associated motor sensors. We’re also sending a unit for each measurement as well as a timestamp of when the measurement took place, which helps to create an accurate simulation of events that occurred in the past.
| GroupName | DeviceName | MeasurementName | MeasurementSeries | MeasurementValue | Unit | Timestamp |
|---|---|---|---|---|---|---|
| Group1 | Device1 | MotorSensors | Pressure | 123.45 | Pa | 2023-07-12T06:00:00.000Z |
| Group1 | Device2 | MotorSensors | Temperature | 12 | C | 2023-07-12T07:00:00.000Z |
| Group1 | Device2 | MotorSensors | Temperature | 13 | C | 2023-07-12T07:00:00.000Z |
| Group2 | Device3 | MotorSensors | Pressure | 124.35 | Pa | 2023-07-12T06:32:12.000Z |
| Group2 | Device3 | MotorSensors | Pressure | 13 | Pa | 2023-07-12T06:32:34.000Z |
| Group2 | Device4 | MotorSensors | Vibration | 2 | Hz | 2023-07-12T06:12:23.000Z |
In true CSV form, it would look like this.
GroupName,DeviceName,MeasurementName,MeasurementSeries,MeasurementValue,Unit,Timestamp
Group1,Device1,MotorSensors,Pressure,123.45,Pa,2023-07-12T06:00:00.000Z
Group1,Device2,MotorSensors,Temperature,12,C,2023-07-12T07:00:00.000Z
Group1,Device2,MotorSensors,Temperature,13,C,2023-07-12T07:00:00.000Z
Group2,Device3,MotorSensors,Pressure,124.35,Pa,2023-07-12T06:32:12.000Z
Group2,Device3,MotorSensors,Pressure,13,Pa,2023-07-12T06:32:34.000Z
Group2,Device4,MotorSensors,Vibration,2,Hz,2023-07-12T06:12:23.000Z
CSV Simulation Process
The simulation process is a great way to get familiar with Cumulocity IoT’s API for inventory and measurements. I recommend trying this out in Postman using either our provided Postman collection or by importing our OpenAPI specification into Postman.
The steps below will list each API URL, what information you need to provide, and what you should keep track of along the way. In your code, you should be iterating through each row, and for each row, each of the following steps should be executed, while keeping track of which groups and devices you have already created and what their IDs are.
Step 1 – Create a group if it doesn’t exist
We’ll start by checking to see if the group is already created.
GET {{CumulocityURL}}/inventory/managedObjects?query=$filter=(name eq '{{GroupName}}')
If the group does exist, save the ID from the return payload and skip to Step 2.
If the group does not exist, use the following API call to create it.
POST {{CumulocityURL}}/inventory/managedObjects
{
"name": "{{GroupName}}",
"type": "c8y_DeviceGroup",
"c8y_IsDeviceGroup": {}
}
Response for either API call should include an id which is used in future calls and referred to as {{GroupID}}
Example response can be found here: https://cumulocity.com/api/core/#operation/getManagedObjectResource
Step 2 – Create a device if it doesn’t exist
Now let’s check to see if the device is already created.
GET {{CumulocityURL}}/inventory/managedObjects?query=$filter=(name eq '{{DeviceName}}')
If the device does exist, save the ID from the return payload and skip to Step 4 .
If the device does not exist, use the following API call to create it.
POST {{CumulocityURL}}/inventory/managedObjects
{
"name": "{{DeviceName}}",
"type": "c8y_SimulationDevice",
"c8y_IsDevice": {}
}
Response for either API call should include an id which is used in future calls and referred to as {{DeviceID}}
Example response can be found here: https://cumulocity.com/api/core/#operation/getManagedObjectResource
Step 3 – Place device in group
Now the device is created, but it doesn’t belong to any group. Let’s place it in the correct group with the following API.
POST {{CumulocityURL}}/inventory/managedObjects/{{GroupID}}/childAssets
{
"references": [
{
"managedObject": {
"id": "{{DeviceID}}"
}
}
]
}
Example response can be found here: https://cumulocity.com/api/core/#operation/postManagedObjectChildAssetsResource
Step 4 – Create measurement on device
Now your group is created, device is create, and device is put in the correct group. It’s finally time to send the measurement using the rest of the CSV information in the current row.
POST {{CumulocityURL}}/measurement/measurements
{
"time": "{{TimeStamp}}",
"source": {
"id": "{{DeviceID}}"
},
"{{MeasurementName}}": {
"{{MeasurementSeries}}": {
"value": {{MeasurementValue}},
"unit": "{{Unit}}"
}
},
"type": "{{MeasurementName}}"
}
Next Steps
Continue iterating through each row of the CSV and repeat these steps for each row. If you already know you’ve created that group, created the device, and put the device in the right group, you can skip straight to Step 4 and send the measurement right away.
Try this out in Postman and then build it into an automated script using any programming language, so that you can re-run it whenever needed.
Approach 2 – Application Builder Simulator
CSV Format
Let’s get started with an example CSV that simulates 1 device, which can either be a brand new device or an existing one. From this CSV, we’ll be reporting a combination of pressure, temperature, and vibration from their associated motor sensors. We’re also sending a unit for each measurement as well as a timestamp of when the measurement took place.
| MeasurementFragment | Measurement1Series | Measurement2Series | Measurement3Series | Measurement1Value | Measurement2Value | Measurement3Value | Measurement1Unit | Measurement2Unit | Measurement3Unit | Timestamp |
|---|---|---|---|---|---|---|---|---|---|---|
| MotorSensors | Temperature | Pressure | Vibration | 1 | 1 | 1 | C | Pa | Hz | 2023-07-12T06:00:00.000Z |
| MotorSensors | Temperature | Pressure | Vibration | 2 | 2 | 2 | C | Pa | Hz | 2023-07-12T06:00:01.000Z |
| MotorSensors | Temperature | Pressure | Vibration | 3 | 3 | 3 | C | Pa | Hz | 2023-07-12T06:00:02.000Z |
| MotorSensors | Temperature | Pressure | Vibration | 4 | 4 | 4 | C | Pa | Hz | 2023-07-12T06:00:03.000Z |
| MotorSensors | Temperature | Pressure | Vibration | 5 | 5 | 5 | C | Pa | Hz | 2023-07-12T06:00:04.000Z |
| MotorSensors | Temperature | Pressure | Vibration | 6 | 6 | 6 | C | Pa | Hz | 2023-07-12T06:00:05.000Z |
| MotorSensors | Temperature | Pressure | Vibration | 7 | 7 | 7 | C | Pa | Hz | 2023-07-12T06:00:06.000Z |
| MotorSensors | Temperature | Pressure | Vibration | 8 | 8 | 8 | C | Pa | Hz | 2023-07-12T06:00:07.000Z |
| MotorSensors | Temperature | Pressure | Vibration | 9 | 9 | 9 | C | Pa | Hz | 2023-07-12T06:00:08.000Z |
| MotorSensors | Temperature | Pressure | Vibration | 10 | 10 | 10 | C | Pa | Hz | 2023-07-12T06:00:09.000Z |
In true CSV form, it would look like this.
MeasurementFragment,Measurement1Series,Measurement2Series,Measurement3Series,Measurement1Value,Measurement2Value,Measurement3Value,Measurement1Unit,Measurement2Unit,Measurement3Unit,Timestamp
MotorSensors,Temperature,Pressure,Vibration,1,1,1,C,Pa,Hz,2023-07-12T06:00:00.000Z
MotorSensors,Temperature,Pressure,Vibration,2,2,2,C,Pa,Hz,2023-07-12T06:00:01.000Z
MotorSensors,Temperature,Pressure,Vibration,3,3,3,C,Pa,Hz,2023-07-12T06:00:02.000Z
MotorSensors,Temperature,Pressure,Vibration,4,4,4,C,Pa,Hz,2023-07-12T06:00:03.000Z
MotorSensors,Temperature,Pressure,Vibration,5,5,5,C,Pa,Hz,2023-07-12T06:00:04.000Z
MotorSensors,Temperature,Pressure,Vibration,6,6,6,C,Pa,Hz,2023-07-12T06:00:05.000Z
MotorSensors,Temperature,Pressure,Vibration,7,7,7,C,Pa,Hz,2023-07-12T06:00:06.000Z
MotorSensors,Temperature,Pressure,Vibration,8,8,8,C,Pa,Hz,2023-07-12T06:00:07.000Z
MotorSensors,Temperature,Pressure,Vibration,9,9,9,C,Pa,Hz,2023-07-12T06:00:08.000Z
MotorSensors,Temperature,Pressure,Vibration,10,10,10,C,Pa,Hz,2023-07-12T06:00:09.000Z
When modifying this CSV for your use case, it’s important to note that each measurement series column needs a corresponding measurement value column and a corresponding measurement unit column
CSV Simulation Process
The simulation process is a great way to get familiar with Application Builder’s simulation capabilities. There are many other functions beyond just CSV simulation, including simulating random values, sine waves, square waves, DTDL, and more.
To get started, open Application Builder and create a new application. I named mine Device Simulators, since it is where I consolidate all of the simulators I use on my tenant.
Open your application, navigate to Configuration → Simulator, and click on Add Simulator.
Here we’ll choose the File (CSV/JSON) option, select New Device, and then select our formatted CSV. Fill out the fields as shown in this screenshot.
Form Descriptions
- Name - The name of your new device
- Column Header - Gives you the option to either use a column header or have it pre-generated with default values (I recommend including a column header)
- Category - Type of data being sent, in this case we’re just focusing on measurements (i.e., numeric data)
- Simulation Type - Sequential will go through the rows in order, Random will randomize the row order, and +Steps will skip a certain amount of rows every iteration
- Interval Type - Original indicates that you want to use a timestamp column, Fixed will use the current time
- Run in a Loop - Loop back to the beginning of the file and simulate the values again
- Loop Delay - Amount of time before the next loop is run
- Select Type - The type of measurement that each measurement series belongs to
- Select Fragment - The fragment which represents all of your measurement series (I recommend making this the same as your measurement type)
- Interval - Delay between each row of data being simulated
- Series - CSV column with the different measurements/sensors that have associated values and units
- Value - CSV column with the numeric value of the measurement series
- Unit - CSV column with the unit of the measurement series
- Select Timestamp - CSV column with the timestamp of the measurement series (only if Interval Type = Original)
Once you fill out the form and click Save, activate the simulator by clicking on the slider.
If everything is set up correctly, you’ll see measurements start to come into your new device!
Next Steps
Play around with different CSV simulation setups, try using events instead of measurement, add many new sensor types, or different Application Builder simulators altogether. There are many other ways to pull values into these simulators.