Data Synchronization Best Practices
Learn how to synchronize telematics data from Samsara into a relational database using the API.
Overview
This guide will explain how to synchronize telematics data from Samsara into a relational database using the API. This guide describes an integration that:
- Backfills the database with all historical telematics data
- Synchronizes any new telematics data into the database
Data Schema
In the physical world, each type of telematics data generates its own event series. For example: GPS pings, odometer readings, and changes in engine state all constitute their own series of events. This is how Samsara collects, stores, and serves telematics data. For each vehicle, each type of telematics data is stored as its own series of events.
TODO: potential illustration
If you want to process telematics event streams independently, then no extra work needs to be done. You can query the Telematics APIs directly.
If you want to stitch each of these event streams together into a single table in a database, then this guide is for you.
This guide will describe how to stitch telematics data together to construct a database that looks like this:
Vehicle ID | Timestamp | GPS Ping | Odometer Reading | Engine State | Engine Hours | Fuel Level | ...other data types... |
---|---|---|---|---|---|---|---|
281474977075805 | 2021-08-23T03:16:09Z | (37.77489889, -122.39419785) | 248378614 | Off | 8483.15 | 34% | ... |
212014918085958 | 2021-08-25T19:07:53Z | (32.77063555, -96.81309711) | 371551170 | On | 92513.20 | 20% | ... |
... | ... | ... | ... | ... | ... | ... | ... |
Note: you can select which types of telematics data you want to include in the table - ranging from just a few to all of them.
💡Guaranteeing no missing events
Because we are stitching disparate event series into a single row in a table, there is potential that events may occur in between rows. If you need to guarantee that no events are missed, then you should query and store the event series independently. (See
types
in the Telematics Guide).
APIs
Samsara provides a different set of APIs for each asset type:
Vehicles
https://api.samsara.com/fleet/vehicles/stats
https://api.samsara.com/fleet/vehicles/stats/feed
https://api.samsara.com/fleet/vehicles/stats/history
Equipment
https://api.samsara.com/fleet/equipment/stats
https://api.samsara.com/fleet/equipment/stats/feed
https://api.samsara.com/fleet/equipment/stats/history
Trailers
https://api.samsara.com/fleet/trailers/stats
https://api.samsara.com/fleet/trailers/stats/feed
https://api.samsara.com/fleet/trailers/stats/history
Each of these sets of APIs uses the same conventions for querying data. The rest of this guide will simply refer to the /stats
APIs in general, and you can add in the prefix for the asset type(s) relevant to your use case. The examples will use telematics from the /vehicles/stats
APIs.
Integration Outline
Because we want to backfill historical data and synchronize new data, we will split the integration into two processes:
- Begin a
/stats/feed
of data. Store the time that the feed was initiated asfeedStartTime
. This feed will be the ongoing process that synchronizes any new telematics data to the database. - Get the
/stats/history
. Set thestartTime
parameter to be the beginning of Samsara's history:2015-01-01T00:00:00Z
. Set theendTime
parameter to thefeedStartTime
. This will allow you to backfill any data that was generated since the beginning of Samsara's history up until when you started the feed.
These two processes can be run in parallel. You can immediately begin the /stats/history
job as soon as you know when you started the feed (feedStartTime
). The /stats/feed
job will continue to ingest new data while the /stats/history
job processes any historical data that's been generated. (The /stats/history
job should be a one-time backfill, but the /stats/feed
job will be an ongoing process. We'll discuss handling application failures in a later section.)
Both these processes will use the same query patten described below to stitch telematics events together into a single row in the database.
Query Pattern
Both the /stats/history
and /stats/feed
jobs will use the same query pattern to stitch events together into a single database row.
To combine telematics events together, we will use the concept of decorations.
The /stats/history
and /stats/feed
endpoints have two query parameters for requesting telematics data:
types
decorations
types
indicates the main event series you are most interested in.
decorations
indicates which data you want to decorate the main event series with.
For example, the query:
GET https://api.samsara.com/fleet/vehicles/stats/feed?types=gps&decorations=obdOdometerMeters,engineStates
will return a feed of GPS pings, where each GPS ping is decorated by the last known odometer reading and engine state at that time:
{
"data": [
{
"id": "212014918174311",
"name": "217322",
"gps": [
{
"decorations": {
"engineStates": {
"value": "On"
},
"obdOdometerMeters": {
"value": 360526145
}
},
"time": "2021-08-24T00:00:04.005Z",
"latitude": 29.71555136,
"longitude": -95.334823
},
{
"decorations": {
"engineStates": {
"value": "On"
},
"obdOdometerMeters": {
"value": 360526170
}
},
"time": "2021-08-24T00:00:10.008Z",
"latitude": 29.7148708,
"longitude": -95.33464721
},
...
]
},
...
],
"pagination": {....}
}
This allows us to start building a couple of rows of our database:
Vehicle ID | Timestamp | GPS Ping | Odometer Reading | Engine State | Engine Hours | Fuel Level | ...other data types... |
---|---|---|---|---|---|---|---|
212014918174311 | 2021-08-24T00:00:04.005Z | (29.71555136, -95.334823) | 360526145 | On | |||
212014918174311 | 2021-08-24T00:00:10.008Z | (29.7148708, -95.33464721) | 360526170 | On |
At this point, the Engine Hours, Fuel Level, and other data types cells are still empty.
This is because decorations
is limited to two types.
In order to fill in the rest of the row, we need to make another query for Engine Hours and Fuel Level. We'll set types
to gps
to get the same event series as before, but we'll set decorations
to obdEngineSeconds,fuelPercents
to get the new event decorations:
GET https://api.samsara.com/fleet/vehicles/stats/feed?types=gps&decorations=obdEngineSeconds,fuelPercents
The query will return the same of GPS pings we queried earlier, but instead, each GPS ping is decorated by the last known engine runtime reading (in seconds) and fuel percentage level at that time:
{
"data": [
{
"id": "212014918174311",
"name": "217322",
"gps": [
{
"decorations": {
"fuelPercents": {
"value": 34
},
"obdEngineSeconds": {
"value": 30539340
}
},
"time": "2021-08-24T00:00:04.005Z",
"latitude": 29.71555136,
"longitude": -95.334823
},
{
"decorations": {
"fuelPercents": {
"value": 34
},
"obdEngineSeconds": {
"value": 30539346
}
},
"time": "2021-08-24T00:00:10.008Z",
"latitude": 29.7148708,
"longitude": -95.33464721
},
...
]
},
...
],
"pagination": {....}
}
This allows us to fill in the rest of the stats in our database:
Vehicle ID | Timestamp | GPS Ping | Odometer Reading | Engine State | Engine Hours | Fuel Level | ...other data types... |
---|---|---|---|---|---|---|---|
212014918174311 | 2021-08-24T00:00:04.005Z | (29.71555136, -95.334823) | 360526145 | On | 8483.15 | 34 | |
212014918174311 | 2021-08-24T00:00:10.008Z | (29.7148708, -95.33464721) | 360526170 | On | 8483.15 | 34 |
We can update the rows using (Vehicle ID, Timestamp)
as the composite key for each row. Since we are querying types=gps
each time, then the GPS event series for each vehicle will have the same set of timestamps. We can match these timestamps across different decorations
queries to combine X amount of decorations together (even though the API limits decorations
to two types per query).
Summary
Here's the online for how to stitch multiple telematics events into a single row in the database:
- Select one telematic event to decorate all other events off of. This could be the event that you are most interested in, or you could base this on the frequency of that event. For example, GPS pings every 5 seconds (most frequent event).
- Make a series of
/stats/feed
or/stat/history
queries, each withtypes
set to the main telematic event, and each with 2 differentdecorations
. You can select as many decorations as you want, split into pairs of twos:/feed?types=gps&decorations=ambientAirTemperatureMilliC,auxInput1
/feed?types=gps&decorations=auxInput2,auxInput3
/feed?types=gps&decorations=auxInput4,auxInput5
- …other sets of decorations...
/feed?types=gps&decorations=obdEngineSeconds,obdOdometerMeters
- Insert or update rows of the database with the events from the queries using
(id,gps[i].time)
as the composite key.
Updated over 2 years ago