Gain real-time visibility into fleet operations using only SQL.
Ultra-low latency data architecture is critical to providing driver safety to large fleets and to maintaining competitive advantage in operational efficiency. In this post, we’ll discuss how leading logistics player Duckbill is leveraging Timeplus to monitor their entire truck fleet’s status in real-time to detect speeding and fatigued drivers, and to conduct geofencing related checks. All from a streaming SQL query.
Duckbill’s Challenge: Managing Latency, Scalability & Flexibility
Founded in 2017 and headquartered in Shanghai, Duckbill is China’s leading and fastest growing solution-based container trucking fleet, with a network covering all of China’s main harbor ports.
Duckbill leverages big data and AI to dynamically manage 2,100+ trucks and 100+ professional inspectors. Their core offering Duckbill Tech is a state-of-the-art digital platform which offers clients a fully digital experience, where users can do online inquiries, receive instant quotations, book online, run inspection services, and track & trace shipments in real-time, 24/7.
Duckbill had developed a Java-based program for its monitoring functionality. They encountered many challenges:
A large number of trucks need to be monitored, and the fleet is expanding rapidly. The Java-based solution wasn’t scaling well and often crashed.
The operational rules governing fleet management often change; but updating the Java code is cumbersome and time-consuming.
Maintaining and operating such monitoring systems takes a lot of effort. Duckbill had an operations team running the monitoring system, where each team member is responsible for monitoring a portion of the fleet. Team members need to manually validate triggers and manually notify the driver to slow down. System response times were slow, and the solution was costly to scale.
Duckbill wanted a high performance, flexible and scalable streaming data processing solution to replace their existing system. The team evaluated multiple vendors/solutions, including Timeplus and Apache Flink. They ultimately choose Timeplus because:
Timeplus’s streaming SQL is more flexible than writing Java code.
Timeplus Cloud has no additional DevOps team cost. Just click and run. The Duckbill team estimated that running Apache Flink would require a dedicated operational team to run its Flink cluster.
Timeplus provides comprehensive alert/sink and SDK integration. Timeplus can be easily integrated with Duckbill’s existing monitoring stacks, making implementation much faster.
Solution Overview
Timeplus is deployed as the key platform to store and analyze real-time data, power real-time dashboards, and send smart alerts. All monitoring and alerting logic is written in SQL.
In this case, the trucks are sending all the data to an IoT gateway, and Duckbill is storing the data in their own message queue. An agent is responsible for sending all the data to Timeplus Cloud in real-time using Timeplus’s REST API. The application UI is implemented using JS/React which calls Timeplus’s REST API to query all the data. The UI is deployed on Timeplus Cloud.
Above screenshot shows the main UI of the fleet management sample application. It contains:
A list of all the alerts
A map view where the user can monitor all the truck’s position in real-time
A history line chart which shows the history speed of a specific truck
Check the solution introduction video here:
Data Collection
As mentioned, the data is collected using an agent program that sends all the data to Timeplus Cloud by calling Timeplus’s Ingest API.
Here is a sample to ingest data using cURL.
curl -X POST \
'https://beta.timeplus.cloud/{workspace_id}/api/v1beta1/streams/{stream_name}/ingest' \
--header 'Accept: */*' \
--header 'Content-Type: application/json' \
--header 'x-api-key: {api-key}' \
--data-raw '{
"columns":["col1","col2"],
"data": [ "{\"col1\":65,\"col2\":\"test\"}" ]
}'Or you can do the same thing using golang:
package main
import (
"fmt"
"strings"
"net/http"
"io/ioutil"
)
func main() {
url := "https://beta.timeplus.cloud/{workspace_id}/api/v1beta1/streams/{stream_name}/ingest"
payload := strings.NewReader(`{
"columns":["col1","col2"],
"data": [ "{\"col1\":65,\"col2\":\"test\"}" ]
}`)
req, _ := http.NewRequest("POST", url, payload)
req.Header.Add("Accept", "*/*")
req.Header.Add("x-api-key", "{api-key}")
req.Header.Add("Content-Type", "application/json")
res, _ := http.DefaultClient.Do(req)
defer res.Body.Close()
body, _ := ioutil.ReadAll(res.Body)
fmt.Println(res)
fmt.Println(string(body))
}In this case, all the raw data is ingested into a stream called truck, this stream contains following fields:
Now, with these basic data in the stream, Duckbill can use Timeplus’ streaming SQL to monitor different cases.
Truck Monitoring
The basic fleet monitoring requirement is to monitor all the trucks and display the trucks in real-time on the map.
This can be done using one simple streaming query:
select * from truckIf you input this query in Timeplus’s Cloud console UI, it will show all the truck data in real-time. We sometimes call it a streaming tail query, like if you are using linux `tail` command to view the content of a file (usually a log) in real-time.
The solution UI will call this SQL query to show all the trucks on the map, the streaming query will always sending the new data for each truck to the client, but the monitor UI only need to show the latest truck by its position, so the UI is actually store all the trucks’ latest data seen from the SQL in a hashmap using lpn as the key of the map, so the trucks are updated on the mapping using their latest position data.
Alerts
There are several cases in fleet management where the user wants to know whether trucks are speeding or drivers have been driving a long time without rest. All these cases can be monitored using a streaming SQL. Once such a condition has been detected by the SQL, alerts will be sent to the user so the operators can take proper actions to notify the drivers to slow down or take some rest.
Here are some of those cases.
Speeding
Effective real-time monitoring that helps detect speeding protects drivers and other vehicles on the road and prevents possible fining.
A simple query could just monitor if any point of data contains speeding. Say:
SELECT lpn, time, lat, lon, spd
FROM truck
WHERE spd > 100While in a lot of cases, it is only taken as speeding if the speed keeps over the threshold for a certain amount of time. So to be more precise, we can use a query with a hop window:
SELECT lpn, min(spd)
FROM hop(truck, 10s, 1m)
GROUP BY lpn, window_end
HAVING min(spd) >100In the above streaming query, a hop window with size of 1 minute and step 10 seconds is used to check if a truck’s minimal speed is faster than 100, which means the truck’s speed keeps being higher than the threshold for over 1 minute, and an alert will be reported then.
Fatigued Drivers
Driving while fatigued is a type of impairment that results in reduced mental and/or physical performance. It’s a far-reaching safety risk for logistics companies when thinking of fleet safety.
There are different definitions for what is a fatigue status. In our case, it is defined as the driver has been driving for 4 hours, with resting time less than 15 minutes.
It is important to define what is a rest, when the truck reports a speed 0, should it be taken as a rest? Consider a SQL like this:
SELECT lpn, time, spd FROM truck
WHERE spd = 0 There are several limitations if you use above query to check if the drive is having a rest:
First, in lots of cases, a speed 0 does not mean the driver is taking a rest, for example, the driver is waiting for a traffic light and he/she is still in a driving state without taking a rest.
Second, this query only gives you a data point, and to calculate how long the driver is taking a rest, you need a time period with begin and end.
A session window is used in this case:
WITH duration AS
(
SELECT lpn, window_start as ws, window_end as we, date_diff(minute,ws,we) as du
FROM session(truck,1h,4h,spd>0,spd=0,lpn)
GROUP BY lpn, ws, we
)
SELECT lpn, window_start, window_end, sum(du)
FROM hop(duration,we,1m,4h)
WHERE du>=3
GROUP BY lpn, window_start,window_end
HAVING sum(du)> 4*60-15
In the Common Table Expression (CTE), a session window with start condition as speed > 0 and end condition as speed =0 which identifies a driving session, in this session, the driver is considered driving the truck and the driving time (du) can be calculated by the difference between the session start time and the session end time. Note, the session window has a keyBy field, in this case, it is the trucks lpn, which means this query will compute all the sessions per truck.
A hop window-based query is executed on the above CTE, where the window size is 4 hours with a sliding step 1 minute. With the hop window, there are two conditions, first, du > 3 which means the driver takes at least two rests, the other condition is that the total driving time is more than 3 hours 45 minutes, which means the rest time is less than 15 minutes.
This query looks a little bit complex, but it is very efficient since it is used to monitor the fatigued driver of all the trucks at the same time with just one query.
Geofencing
The last case shows how Timeplus can do geolocation-based analysis. A geofence is a virtual perimeter for a real-world geographic area.
With Timeplus’s streaming query, it is easy to detect a specific truck running across a geo-fence boundary:
Here is the query that detects if a truck is running from outside of a specific region into that region:
WITH cte AS (
SELECT a.area as area , lpn, lat, lon, time,
lag(lat) OVER (PARTITION BY lpn) AS last_lat,
lag(lon) OVER (PARTITION BY lpn) AS last_lon
FROM truck, table(areas) AS a
WHERE a.name = 'REGION_0'
)
SELECT *
FROM cte
WHERE
last_lat*last_lon!=0
AND
point_in_polygon((lon, lat), area)
AND
NOT point_in_polygon((last_lon, last_lat), area)The cte query is a table/stream join, where the table areas store the geo positions of specific regions.
The region is composed of an array of longitude/latitude pairs:
When the truck stream joins with the areas table in the CTE, it is actually just putting the area of that specific region for each row.
To detect if a truck is running across a border, we need check if the current location is in the region and the previous location is out of the region, so the lag function is used to get the previous lat/lon of the trucks. Because we want to analyze all the trucks at the same time, an OVER/PARTITION BY clause is used here, so all the trucks are being monitored at the same time.
Based on that CTE, we just need to add the condition as we mentioned, the truck’s current position should be in the region, and the previous position is out of the region, the function point_in_polygon is used to check whether the truck’s position is in the region.
Note condition last_lat*last_lon!=0 is used to make sure the truck’s lat/lon is available. This is because for the very first data point in the stream, there is no lag data, so the lat/lon are empty.
Summary
At Timeplus, we’ve worked hard to make powerful analytics super easy to deploy and maintain. Duckbill found that using Timeplus Cloud empowered their developers to build a ultra-low latency monitoring system that is easy and cost efficient to scale, while being flexible to adapt to changing business needs.
Or, in Duckbill’s CTO Minfeng Xie words, “We are thrilled to partner with Timeplus and gain real-time visibility to our 2000+ trucks, with much lower latency, smarter alerts and shorter Time-to-Value compared to our previous solutions.”
Looking to build your own real-time analytics capabilities? Learn more about us at www.timeplus.com.