Based on an analysis of typical automotive measurements data as found in ASAM MDF les, we derive requirements managing these data in a database system and create a mapping to a relational database structure. The performance of a parallel relational database solution is compared with a Python-based direct access and querying timeseries in Python and with big data frameworks such as Apache Spark in di erent scenarios. A hybrid approach using some object-relational features of PostgreSQL performs best in most cases.
In the automotive industry, due to the continuous growth of collected sensor data, traditional hardware and software setups come to their limits. Within a joint project with an industrial partner, we developed and evaluated database oriented solutions to o er scalable automotive analysis.
The basis for this project is the Measurement Data Format (MDF).
MDF is a binary le format that has been originally developed for automotive
measurement and analysis in the 1990's. It has been o cially standardized with
version 4.0 (MDF4) by the Association for Standardization of Automation and
Measuring Systems (ASAM) in 2009 and is even today the standard for storing
and reading automotive sensor data in industrial usage [
In order to overcome these data size limitations, we started a cooperation
project with an industrial partner and examined solutions that are based on
database support with Python front ends using a conventional vertical
architecture [
The three tasks of automotive analysis we evaluated can be categorized into two groups: { The rst, more data intensive group, is selecting time series ("channels") of a number of les (measurements). All channels of one measurement are interpolated with respect to one of the time axes, so all channels ultimately share one time axis, allowing for pointwise comparison or calculation. The interpolated data is either visualized, manipulated (for instance using arithmetic operations) or analyzed, e.g. nding time intervals where channel values are in certain ranges. { The second group can be refered to as meta-information queries: the goal is to nd measurements under constraints, like a concrete date of recording or some speci c time axis information.
Due to space limitations, we would like to refer to an extended technical report
[
In the following section, we give a brief overview of the course of the presented project. 2
We started our investigation focusing on relational database systems, as these seemed to be best suited for (1) the comparatively small amount of test data Exemplary Relation Exemplary Relation name time[ ]
A {0,1,2} B {0.5,1.5} values[ ] {0.2,0.4,0.6} {6.4,5.4} Simplified Internal Storage Scheme
row store with arrays ... A 0 1 2 0.2 0.4 0.6 ... (around 40 GB) that was available at the start of the project and (2) the evaluation of quite selective algorithms.
After evaluating rst drafts of database schemas, we found that providing a schema that enables systems to transparently apply compression techniques is a key factor for our automotive scenario. Therefore, we distinguished three di erent setups: { row stores, { column stores, { and row stores that allow the use of array data types (such as objectrelational database systems).
Since automotive measurement data are heavily compressible, especially in the context of run length encoding (RLE), column stores for rst normal form (1NF) relations or row stores with array data types (object-relational scheme) perform best (see Figures 1 and 2 for exemplary relations in the three di erent setups). With this di erentiation, we developed four di erent relational schemas in order to satisfy the requirement of either storing raw data (bit varying) and its conversion rules, or converted oating point data (double precision), while enabling sequential data storage and therefore transparent run-length encoding. As an example, the (ER) schema using arrays in a row store with oating point storage is depicted in Figure 2.
As requested by our industrial partner, we implemented the adjustment of MDF data and the subsequent import into database systems in Python. For this, we needed to evaluate di erent packages for reading MDF- les and database communication.
For the three automotive analysis tasks we have implemented several database
supported approaches. We took special focus on evaluating the pushdown of as
many operations as possible into database systems as we have discussed in our
recent work on SQL-based scienti c computing [
grid file [1,N] stores
[
v[]
timeseries
[
[1,N] measured by name device did
After using RDBMSs, we evaluated Apache Spark with di erent types of data storages including the use of CSV- and Parquet- les in HDFS and requesting data from (parallel) RDBMSs and NoSQL-systems using (array-based) schemas similar to the ones for row stores with array support.
As a starting point, we reviewed di erent libraries for storing and analyzing
timeseries in Apache Spark. Here, we have found that these have not been
sufcient for automotive data [
We have found several noteworthy results that we can only summarize in this
short paper. A more detailed description of all the results can be found in [
As a rst conclusion, importing complete datasets of measurements into relational database systems via Python is ine ective. As the heavily compressed sensor data is decompressed in order to adjust its structure to the internal representation of the respective database system, the amount of data that is communicated is needlessly high. Solutions to this problem are either transforming sensor data in its actual compressed form before the communication process or fully integrating the import (if possible) into the database system as a UDF.
Regarding relational database schemas, we have found that row stores supporting array datatypes performed best. Here, he main reasons are superior compression and faster selections as relations contain so much lesser tuples, that even data selections via B-tree index structures become signi cantly faster. On the other hand, row stores that have to work on strictly rst normal form relations are unusable for these kinds of operations as relation sizes in ate rapidly.
Evaluating the three aforementioned automotive methods has shown that the pushdown of interpolation (only applicable with UDFs) and arithmetic operations, as well as nding intervals under constraints are very e ective in this scenario. In comparison to Python implementations on SSD drives for local calculation or using network drives for cluster computing, all of the three methods could experience a signi cant speed up when supported by (parallel) relational database systems. Especially, inter-measurement queries for metainformation performed up to nearly 500 times faster, due to the very low communication cost and the selectivity of the problem.
The most promising relational database systems we have tested are PostgreSQL (local) and its parallel branch Postgres-XL (cluster setups). Besides superior performance, both systems run under BSD-like licenses and support a wide range of useful functionalities, like index structures, UDFs and array data types with transparent run length encoding. However, due to a relatively small amount of test data (40 GB) and fairly light-weight analysis, Postgres-XL could not bene t from its parallel computation capabilities, ultimately leading to a reduction of relative performance bene ts obtained by database support in comparison to the local setup. Similarly, the initial overhead of Apache Spark has shown to be to overwhelming for the data sizes we evaluated, making a comparison to the relational systems unfair. Despite this, the most promising data storage for Apache Spark has shown to be Parquet les in HDFS.
As this project has only been meant as an initial survey of possible database support for automotive applications, there are many ways to continue the research, like evaluating larger data sets or more complex methods like pattern recognition (e.g., peak detection). Nonetheless, this project has shown that scalable solutions for automotive inter-measurement analysis can be done e ciently using relational database systems.