CAN Telematics at Scale - Device Fleet Management & Data Lakes

The goal of this article is to provide a holistic perspective of how to manage your CAN bus data logging at scale.

It is particularly relevant for users that deploy many CANedge devices (or aim to do so), but a lot of the subjects are useful as well for small scale deployments if your goal is to set up a streamlined workflow.

Throughout the article we will outline various solution examples, taking outset in our recommended deployment setups. These are generally based on AWS, but we also provide guidance on alternative deployments within Azure, Google Cloud and self-hosted setups.

The article applies to any CAN/LIN related telematics use case, including across higher layer protocols like J1939, OBD2, NMEA 2000, ISOBUS, UDS, CCP/XCP, CANopen and more.

In this article we cover a large number of topics at a high level. All topics are documented in further detail in the CANedge Intro and Docs. If you are unable to find specific documentation, contact us.

Once you have deployed your CANedge units, your server will receive a steady inflow of uploaded log files containing raw CAN/LIN data. In rare use cases this may be the 'final form' that your data needs to be in for consumption, but most often the data needs to be pre-processed.

The obvious reason is that most analysis focuses on DBC decoded parameters like speed, GPS position, temperatures etc. - rather than raw CAN/LIN frames. By DBC decoding the data, we make it more easily accessible for such analyses and many tools will only be able to work with the data in decoded form.

In principle this could be done on-the-fly: If a user wants to analyze DBC decoded data from period X to Y, he could perform both the data fetching, loading, DBC decoding and visualization in one step using tools like Python or MATLAB. However, for CAN/LIN telematics, the sheer volume of data involved will typically make this prohibitively slow.

As an example, a use case may involve a single device that uploads 30+ GB of raw CAN data per month including 500+ different signals packed in raw CAN frames. If you want to just determine the average value of one signal (e.g. speed) over the last 1 year, your script would need to download 360 GB of data and process it. As you can imagine, this would be a cumbersome way to work.

Here, pre-processing serves the purpose of performing the decoding of the data in a separate, automated manner and storing the results in a way that makes them very fast to query for later analysis.

We recommend to consider early on how the decoded data will be used - in particular related to the below challenges:

1. How to stay frontend agnostic? You need to plan which software/API tools will be used in consuming the processed data - but also incorporate interoperability as you cannot predict everything. In particular, be careful to avoid data duplication.
2. How to store your processed data? You should consider how to best store your processed data, balancing factors like the cost per GB per month vs. how 'hot' the storage has to be for ensuring sufficient data retrieval speed
3. How to manage your data life-cycle? Will you need to store data indefinitely, or is your data e.g. irrelevant once it is X weeks/months/years old? Should the retrieval speed be identical across the life-cycle?
4. How critical is the query speed? How fast should you be able to query the decoded data? And how is this affected by whether you need to visualize a couple of trips from a single device - vs. advanced historical multi-device statistical analyses?
5. How large a share of the data will be used? Will you need to pre-process 100% of your data in order to perform e.g. aggregated statistical analysis - or will you only need to access a small %-share on a case-by-case basis?
6. How to enable secure access to relevant users? Who will need to be able to view /process/edit the data - and how will this affect the data structure, privacy and security?

As previously discussed, AWS is our default recommended cloud solution for use with the CANedge2/CANedge3. This also means that we offer a number of plug & play solutions for deploying the above workflow within AWS.

With that said, you can set up very similar data pre-processing workflows in other clouds:

Azure: Within Azure, you can use Azure Functions as an alternative to Lambda functions. Further, you can store your DBC decoded Parquet data lake in an Azure container, similar to how you can store it in an AWS S3 bucket. From here, you can query the data via Azure Synapse Analytics serverless SQL pool endpoints with a similar pricing/concept as Amazon Athena (see the CANedge Intro for step-by-step deployment). You may also consider leveraging Microsoft Fabric going forward for this setup.

Google Cloud: Google Cloud Platform (GCP) offers an equivalent data pre-processing workflow. Data from the CANedge can be uploaded directly into Google Cloud Storage (GCS) buckets via the S3 interoperability. You can then deploy Cloud Functions that are triggered by the upload and serve the same role as Lambda functions. The results can be output in another Google Cloud bucket. Finally, you can use Google BigQuery to interface with your Parquet data lake. See also our intro on Grafana-BigQuery for details.

Self-hosted: You can of course also deploy a similar work flow on a self-hosted server. For example, you may have set up a MinIO S3 input bucket for receiving log files from the CANedge2/CANedge3. Here, you can deploy MinIO Object Lambda to automate your pre-processing and output the Parquet files to another MinIO S3 bucket. You can then e.g. use the open source ClickHouse interface as an alternative to Athena for querying the data. You can find details on deploying ClickHouse in the MF4 decoder Docs.

In other words, the deployment is not unique to AWS - but it requires a bit more work to set up in Azure or GCP. For a self-hosted deployment, you should expect significantly more effort in order to set up the equivalent workflow and it can be particularly complex to do so in a scalable way.

You may want to integrate the data into your own platform/system. Here, the data lake approach may still be applicable if your end destination platform integrates with Parquet data lakes (either directly or through an interface like Athena). Alternatively, you can directly inject data into your custom platform by modifying the Lambda function script. For example, you can adjust the script to skip the uploading of Parquet files to S3 - and instead load the data into dataframes and write it to your preferred platform destination endpoint using the relevant API. In the CANedge Intro you can find example scripts for modifying your Lambda function to write your decoded data to an external database via REST.

As discussed previously, you may not need to process all of the incoming data - but instead only process log files case-by-case. Here, we do not recommend deploying the Parquet data lake workflow, but instead we suggest using more basic desktop GUI tools. For example, you can use CANcloud to browse your way to the relevant data in your S3 bucket by leveraging CANcloud's ability to show the recording start time of uploaded log files directly. Once you've identified the relevant group of files, you can download them and load them in tools like asammdf, Vector CANalyzer or PEAK's PCAN-Explorer.

Alternatively, you can mimic the Parquet data lake workflow, but tweak it so that you only process relevant files. The simplest way to do this is to create a new 'ad hoc' S3 input bucket that is separate from your 'main' S3 input bucket (where your CANedge2/CANedge3 devices are uploading files). You then deploy the Parquet data lake integration as if the separate S3 input bucket was your main bucket. This way, you can manually download relevant log files from your main S3 input bucket and upload them to your ad hoc S3 input bucket. The Lambda function will then process the manually uploaded log files, outputting them into an S3 output bucket Parquet data lake. This way you can leverage all the data lake integration options - while only using it on relevant data.

It can be a bit unclear how to distinguish data pre-processing vs. data aggregation.

In our view, a key distinction is whether you are reducing the richness of the data or not:

When you perform data pre-processing, you are DBC decoding the data and writing the results to Parquet files. Notice here that no information is lost: You retain the full timestamp resolution and all messages/signals can be included in the Parquet data lake. In other words, you will be able to rely purely on the pre-processed data in your analysis, with no need to refer back to the original raw log files.

In contrast, when you perform data aggregation, you are deliberately resampling the data in order to create a summarized view of the results. The resulting aggregated data will rarely suffice in itself, meaning that it should be viewed as a supplement to the regular pre-processed data, not as a replacement.

Deploying data aggregation presents a number of challenges:

1. How to decide on relevant data/structure? You need to decide what CAN/LIN signals you wish to aggregate (e.g. RPM, speed, calculated signals, ...), in what way (avg, sum, min, max, ...) and to what level (devices, trips, days, ...)
2. How & when to aggregate the data? Consider what scripts/tools to use, where to deploy the processing, how frequently to run it (e.g. daily, weekly, ...), how to automate it - and how to manage this effectively.
3. How to maintain data integrity? When aggregating data across devices and time, you lose information. Your aggregation outputs should retain data integrity and enable further (valid) aggregations/comparisons across devices and time
4. How to add meta information? You may need to enrich your aggregated data with relevant meta information, either based on the CAN data or external databases. Consider how this can be incorporated in a systematic and automated way
5. How to store/access the output? The aggregated data should be stored in a way that enables seamless integration with other pre-processed data, as well as any dependant frontend tools
6. How to manage structural changes? Aggregation can be costly and redoing it should be minimized. Expect changes to be made in e.g. required parameters - and incorporate the necessary flexibility into your design of the aggregation outputs

The script we provide by default defines a trip based on a 'trip identifier' message (e.g. one containing Vehicle Speed or similar reasonably high-resolution message). The script then identifies 'trip windows' for a given device/day, with a window corresponding to a period of >1 min with <10 min between two observations. You can of course modify this as you see fit.

For a given trip period, the script will add an entry to the output table for every signal aggregation you define via the JSON file we provide. For example, you can add the signal Speed from the message CAN2_GnssSpeed and specify the aggregation type as 'avg' to get the average returned.

The script by default provides multiple popular aggregations like min/max/avg/median/first/last as well as e.g. delta_sum, delta_sum_pos and delta_sum_neg, useful in aggregating parameters like e.g. consumed State of Charge over time. You can add further aggregations if needed.

You can of course add your own aggregation values, or even signal calculations, within the script. For example, you can add a custom aggregation type that checks if a signal exceeds a specific threshold for more than X seconds - and returns 0 or 1 depending on the result. However, if you need to add calculated signals across separate messages (i.e. with separate time rasters), it is recommended to do so separately from the data aggregation script.

The table structure will not necessarily benefit from the full efficiency of the Parquet file format (for technical reasons). On the other hand, this is compensated through the fact that the aggregated table will be extremely light weight.

For example, if you have 500 devices with an average of 10 trips per day and 20 signal aggregation entries across 1 year, the above structure would result in 365 separate Parquet files (due to the date partitioning), each containing 100,000 rows. An interface-based SQL query (e.g. from Athena) on this table would return the requested results in 2-4 seconds in our tests.

Trip aggregation is particularly powerful if your underlying Parquet data lake contains high-frequency data (for example if you have recorded your CAN data with no prescaling). Here, your underlying data lake can quickly become very dense, with a single device e.g. containing 100+ GB of data across a single year. In such situations, performing analytics on the non-aggregated data lake can become slow - in particular across devices.

When aggregating data, be conscious about how you interpret the data. For example, let's say you are interested in the average speed of your vehicles and you therefore extract the average speed across all devices at a trip level.

Next, you intuitively wish to get the 'average speed per device' for a given period and thus take the average speed across the relevant trip averages. However, this will result in the average vehicle speed per device per trip - not the overall average. Effectively, you are assigning equal weighting to each trip, whether a trip lasted 5 hours or 1 minute.

One way to address this is to leverage the fact that the trip summary table includes the 'duration', which you can use to perform weighted averages when aggregating across trips.

Another example is GPS positions: If you want to get an idea of the location of each trip, it makes sense to include aggregated Longitude/Latitude signals. But taking the average of these may not be a good idea as you essentially get 'meaningless' positions that do not reflect the actual trip route. A better solution can be to take the 'first' observation of each, thus letting you visualize the trip starting positions.

Some signals can only be aggregated in a meaningful way if you first calculate the delta values. For example, let's say you wish to understand how much State of Charge is consumed per trip, but you only have the absolute State of Charge signal available. In such a case you can use the 'delta_sum_neg' aggregation to calculate the change in State of Charge and ignore positive changes (e.g. related to charging events). The result will be a clean variable that you can also aggregate across trips.

The aggregation Python script is designed so that it can be deployed anywhere (incl. locally) and target either a locally stored data lake, or one stored on S3. As a result, you can also deploy it for setups outside AWS with minimal modification.

Locally, you can automate the script execution via e.g. a CRON job or task scheduler. In the Azure environment you can consider using e.g. Azure Data Factory, while in GCP you may consider e.g. Google Cloud Dataflow.

If you are primarily going to use e.g. Python/MATLAB to analyse your Parquet data lake, it may be less vital to perform the periodic data aggregation. This is because scripts allow you to circumvent a lot of the limitations outlined for the data lake structure.

For example, if you wish to extract the average speed across 50 devices for a given time period, you can do so easily in a loop - in fact that is exactly what our Python aggregation script does in the first place. It simply uses the S3 API and pyarrow to loop through relevant folders/files in the Parquet data lake, aggregating the results. Similarly, you could also do the same with Athena queries in Python using the Athena API. Here, you can then write a simple query for a single device/message - and simply adjust the parameters of that query as you loop through a list of devices/messages.

As such, the primary value of doing separate data aggregation in this scenario is the fact that it allows you to shift some of the data processing time required to a daily schedule, which lets you write simpler analysis code that will return results faster when the goal is to perform trip level analysis.

When designing your data visualization setup, we recommend to consider below:

1. What visualization tool to use? Ensure that your visualization tools are compatible with your processed data and enable you to deploy the required views of your data
2. How interactive should visualizations be? Static visualizations are often problematic as you often need to create new custom views. However, excess interactivity may result in complexity and unintended data access
3. How frequently should visualizations be updated? You can set up the CANedge to split data in 10 second intervals to achieve near real-time updates - but at a significant storage/processing cost vs. e.g. a 30 min frequency
4. How to handle visualizations across devices/trips? Even if you have performed data aggregation across devices and trips, it can be complex to visualize such data in a meaningful way
5. How to balance costs vs. performance? Determine how fast your visualizations need to be vs. the impact this will have on the cost of e.g. storing the underlying data
6. How to manage users and security? Your visualization tool and data structure will typically need to enable different users with customizable access rights in order to make data sharing practical, secure and performant

Azure: If you are working in an Azure environment, you can still leverage the data lake structure and infrastructure as discussed previously. However, instead of Athena you may prefer to use e.g. Synapse/Fabric, which offers similar functionality. Grafana does not come with a data source plugin for Synapse/Fabric, but you can instead visualize the data in PowerBI, which offers an alternative way to create dashboard visualizations. We provide a step-by-step guide for setting up PowerBI-Synapse in the CANedge Intro.

Google Cloud: If you are uploading data from your CANedge devices to Google Cloud, you may opt for using Google BigQuery instead of Amazon Athena to query data from your Parquet data lake. Here, Grafana offers a BigQuery plugin that enables direct integration - see our intro to Grafana-BigQuery for details.

Self-hosted: In a self-hosted context, you can replace Athena with ClickHouse and then use Grafana's ClickHouse data source. We describe this setup in the CANedge Intro.

As a supplement or alternative to Grafana, you can also consider using Excel to visualize your data and/or create reports. In our Excel + CAN intro we outline how you can directly query data from your data lake in Excel using ODBC drivers. This is great if you e.g. wish to create classic Excel reports that dynamically show data from your data lake - without creating heavy worksheets.

Below are relevant considerations in scripted analysis of large volumes of CAN data:

1. What tools to use? The scripting tool(s) should both enable easy integration with your data - as well as offer the functionality you need for your analysis. Make sure to research e.g. relevant packages up-front
2. How to deploy your scripts? Consider local vs. cloud, scaling of your compute resources and costs. Try to perform processing 'near' the data i.e. in the backend
3. How to handle memory? You may easily exceed memory limits when e.g. analyzing TBs of data - consider how this affects your script design and setup
4. How to consume results? Outputs and results need to be stored and accessible in a way that is suitable for your intended consumption, e.g. via reports or visualization
5. What skills are required? Determine early on if your team has the skills required to perform the necessary analyses

MATLAB natively supports loading the CANedge MF4 files if you have the Vehicle Network Toolbox installed. This is useful for file-by-file analysis. However, you cannot e.g. load raw MF4 files into data stores or tall arrays, making this method less suited for large scale analysis.

MATLAB does offer 'data lake style' support for MF4 files if they are DBC decoded. This works similarly to the support for Parquet data lakes and even offers out-of-memory functionality like tall arrays.

However, we consider this less ideal than the Parquet data lakes for multiple reasons:

1. The Vehicle Network Toolbox is not part of the base version of MATLAB
2. Parquet data lakes are more interoperable than MF4 data lakes
3. Preparing the DBC decoded data can be done via MATLAB, but there are limitations (e.g. no multi-frame support)

You can also use our Python API to directly load the CANedge MF4 files and analyse the raw data or DBC decode it. By doing so, you avoid the pre-processing step and you can directly process the raw files from your S3 input bucket.

However, in practice this also means that any scripts you develop will need to incorporate a significant element related to the initial pre-processing of the data (e.g. fetching files from S3 and DBC decoding them). This step then has to run prior to any subsequent analysis code.

In addition, the actual analysis code will be more convoluted as you will have to incorporate more complex file-by-file handling and memory considerations. You cannot rely on e.g. Athena SQL queries for handling out-of-memory result extraction.

A more subtle downside is that you often want to selectively analyse a small subset of messages from your CAN bus. With the Parquet data lake, you can do so efficiently because the data lake splits the DBC decoded data by CAN message, storing these in separate folders. In contrast, if you work directly with the raw MF4 files, you will need to fetch the entire file and iterate through all of it in order to extract the subset of messages relevant to your analysis. This is of course computationally expensive and time consuming.

The above downsides become particularly visible if you do repeat analyses across the same data. Here you end up performing the data pre-processing multiple times, which is costly both in terms of money and time.