5 ms·

I extensively used SQLite in a telemetry system for an electric race car. The car has an onboard computer, first a Raspberry Pi then a dual core Arm processor.

I extensively used SQLite in a telemetry system for an electric race car. The car has an onboard computer, first a Raspberry Pi then a dual core Arm processor. Onboard code logs ~4000 messages a second into three SQLite databases. After a drive session a script merges the three databases into a single SQLite session log. The session log is decoded on a different computer to ~400 columns of time series data again stored in SQLite. This data is then finally converted into a proprietary format for visual analysis. Prior to SQLite I tried CSV files and raw binary formats. None of them could match the flexibility, ease of use, and throughput of SQLite. My setup has been in use for several weeks now and has processed numerous rows of data. As the SQLite project outlines on its website: > SQLite does not compete with client/server databases. SQLite competes with fopen().

lloydatkinson 3y ago
Sounds cool! Any project details anywhere?
- xyx0826 3y ago
  Sadly my team is pretty light on public tech blogs, but there are plenty of projects like this one! Copying from my comment above: > The system is for a Formula SAE (FSAE) Electric style race car. FSAE is a collegiate competition, and I'm part of a US Pacific Northwest team. Our car uses a 550 V battery and is 4WD. Most of our electronics and firmware/software are in-house besides motors and inverters. I lead the telemetry project and work with 1-2 other members on the system. > The system consists of an on-vehicle computer (Raspberry Pi or NXP i.MX) and a ground station (Rockchip RK3588S). We use Python heavily and the real-time dashboard part is done with InfluxDB and Grafana. Both computers and any user devices are on a Wi-Fi network made with a long-range WISP access point. > Our team is unfortunately very light on public tech blogs and how I wished I can change that... There is plenty of information on FSAE electric cars, vehicle telemetry, and both on the internet and they were a source of inspiration as I designed my own solution.
nitwit005 3y ago
This article isn't talking about the edge as embedded systems. It means cloudflare workers and the like.
brtv 3y ago
Interesting! I'm currently working on a system that writes time series data to raw binary files, but we're considering switching to a different file format for the same reasons. Have you considered any other formats, such as hdf5?
- nunuvit 3y ago
  The one potential requirement I'll caution you on is resilience against write failures, in case you're collecting time-series data and can't afford to lose a "session" or spend time messing with recovery options. HDF5 is not made for that. Binary and SQLite are better in that respect. SQLite wins on usability against binary and HDF5. https://cyrille.rossant.net/moving-away-hdf5/ https://cyrille.rossant.net/moving-away-hdf5/
  - xyx0826 3y ago
    I'm not familiar with HDF5 but agree on resiliency. We are a collegiate racing team and our car's power rails aren't stable and redundant at all times, so power loss failure is something I've kept in mind from day one on my telemetry project. SQLite is generally equipped to handle power losses and write thread crashes: > An SQLite database is highly resistant to corruption. If an application crash, or an operating-system crash, or even a power failure occurs in the middle of a transaction... Quite often after a run the entire car is turned off and, on next power up, the databases are left as .db and .db-journal files. The code has no problem processing or even continuing on logging with DBs in this state.
    - nunuvit 3y ago
      HDF5 is more for storing and exchanging numerical simulation data. It doesn't have to be resilient to write failures because worst-case scenario you rerun the simulation or try again to copy the data into the file. SQLite writes are "atomic" transactions. After writing new data, it goes back to the index and registers that new data has been written using a single instruction. That's why interrupting it in the middle of a write doesn't result in partial data or a corrupted index.
hiatus 3y ago
> Onboard code logs ~4000 messages a second into three SQLite databases. After a drive session a script merges the three databases into a single SQLite session log. A bit unrelated, but curious as to why you wrote to three separate databases only later to merge them.
- magicalhippo 3y ago
  Could be three different processes interacting with different systems. Say one process listening to a CAN bus, another to local Ethernet[1] and a third to some I2C/SPI sensors. If they don't interact otherwise, which they probably wouldn't given they're just logging telemetry, it's more flexible to just have them as separate processes. The I2C/SPI might be a Python script, the others in C/C++ or Rust say, whatever is easier. [1]: https://www.keysight.com/blogs/tech/sim-des/2021/06/10/automotive-ethernet-the-future-of-in-vehicle-networking https://www.keysight.com/blogs/tech/sim-des/2021/06/10/autom...
  - kazinator 3y ago
    Another question is what is interesting in a speeding electric car in such a way that 4000 events per second must be captured about it.
    - magicalhippo 3y ago
      Good question. As a non-racer who enjoys watching racing, I'd imagine at least some accelerometers/gyros and load cells if they got them, and possibly Pitot tubes or similar for measuring aerodynamics when testing, to get a detailed understanding on the forces on the car and how it behaves. Motor currents probably up there too, to catch current spikes. Curious to see how far off I am :)
      - kazinator 3y ago
        > detailed understanding on the forces on the car and how it behaves But at 4000 samples per second? You'd think twenty would be plenty. NyQuist: 4000 samples per second means we are looking to get information about an up to 1800 Hz signal. A6 on the piano keyboard (excluding harmonics). Sorry, I mean Nyquist. I was confused by NyQuil, the night time flu medicine.
sgu999 3y ago
Other have asked, but I'd also be very interested in knowing more if you can share more details. I had to spent a non trivial amount of time convincing others to use SQLite instead of dumb files in similar situations, stories like yours are pure gold.
- Lex-2008 3y ago
  Haven't seen these links mentioned elsewhere in this thread, but in case you haven't seen them yet: * SQLite is 35% Faster Than The Filesystem [1] * Writing to files safely is hard [2], and SQLite goes to great details to ensure consistency in case of application, OS, or computer crash [3] [1]: https://sqlite.org/fasterthanfs.html https://sqlite.org/fasterthanfs.html [2]: https://danluu.com/deconstruct-files/ https://danluu.com/deconstruct-files/ [3]: https://sqlite.org/atomiccommit.html https://sqlite.org/atomiccommit.html
- rongenre 3y ago
  I've had a huge amount of success using sqlite to collect and analyze logs. The obvious approach would have been to dump enormous csv or json files, but doing in sqllite gave me (a) much better reliability, and (b) the ability to come up with post-hoc analysis approaches without changing the main process. I was really pleased to add it to my toolkit.
- xyx0826 3y ago
  There are a lot of SQLite praises in this thread, on the internet and on SQLite's website so here are a couple of reasons that drove me to choose it. My experience with traditional formats like CSV or raw binary is that they have a somewhat lower overhead to "get working" but are quite difficult to scale and adapt to changing needs, a phase most projects are bound to go through. SQLite's reputation and adoption makes me comfortable to assume its operational correctness so I can focus on data itself, not reader/writer logics for it. Even though a SQLite database's schema may change, I'm glad knowing that the database file can still be easily opened and read many years into the future, something that custom formats may not guarantee. On CSV: Syntax and library differences aside, SQLite almost works like a drop-in replacement for CSV since a single table database can be essentially treated as a glorified spreadsheet/CSV if wished. My post processing data pipeline is built with pandas so it is virtually the same for me to read a SQLite database vs. a CSV file.
Solvency 3y ago
I'd love to know more about this electric race car, the telemetry system, technology behind it, etc. Do you have a blog (or can you recommend a place) where you do deeper on this subject matter?
- xyx0826 3y ago
  The system is for a Formula SAE (FSAE) Electric style race car. FSAE is a collegiate competition, and I'm part of a US Pacific Northwest team. Our car uses a 550 V battery and is 4WD. Most of our electronics and firmware/software are in-house besides motors and inverters. I lead the telemetry project and work with 1-2 other members on the system. The system consists of an on-vehicle computer (Raspberry Pi or NXP i.MX) and a ground station (Rockchip RK3588S). We use Python heavily and the real-time dashboard part is done with InfluxDB and Grafana. Both computers and any user devices are on a Wi-Fi network made with a long-range WISP access point. Our team is unfortunately very light on public tech blogs and how I wished I can change that... There is plenty of information on FSAE electric cars, vehicle telemetry, and both on the internet and they were a source of inspiration as I designed my own solution.
  - Solvency 3y ago
    Awesome. If you dont mind me asking, what sort of dashboarding/visualization software do you use? Highchart homebrewn web apps? Some closed source auto software? What's that world look like? Always been curious about it from a front-end perspective.
    - xyx0826 3y ago
      Real time data is fed into an InfluxDB instance and shown on Grafana hosted on the ground station. This data is downsampled to cope with the volume of raw traffic. We also tried some solutions used by other teams like KX [0] but decided to go with a FOSS stack. A post processing pipeline converts original data to a however proprietary format used by AEMdata [1], a proprietary data analysis tool from our ECU vendor and used somewhat commonly in the racing scene. [0]: https://code.kx.com/dashboards/ https://code.kx.com/dashboards/ [1]: https://www.aemelectronics.com/products/software-aemnet/aemdata-logging-analysis-software https://www.aemelectronics.com/products/software-aemnet/aemd...
kazinator 3y ago
> None of them could match the flexibility, ease of use, and throughput of SQLite. It sounds unbelievable that you couldn't append some CSV or binary record to file via an open file descriptor more efficiently than inserting into SQLite. (It's clear that to have the data already in SQLite form saves post-race steps.)
- regularfry 3y ago
  It's an AND statement, not an OR statement. You can append to a CSV file efficiently, but while flexibility is context-dependent and ease of use is very subjective, we can assume the GP knows their own use case).
- xyx0826 3y ago
  The onboard code processes binary data: CAN bus messages and alike, along with their nonbinary metadata. The first version of the code used CSV because they are so trivial to read and write, but CSV as a text format required me to either hex or base64 the bytes which incurred a non-trivial waste of time and space. For a custom binary record I would need to write custom (de)serializers, which I wasn't willing to do given frequent changes in types of data I need to record during development. SQLite is the only solution for me so far that provides a structured data store for mixed data types, is power loss safe (important to an embedded system,) and is a high-quality and portable industry standard. Let along the SQL language itself, which has proved to be hugely useful for first-pass post processing given the amount of data that I ingest. Perhaps there are other technology that will work just as well or better: maybe Protocol Buffers and alike? Yet SQLite currently ticks all my boxes and I'm quite happy with it.

lloydatkinson 3y ago

Sounds cool! Any project details anywhere?

xyx0826 3y ago
Sadly my team is pretty light on public tech blogs, but there are plenty of projects like this one! Copying from my comment above: > The system is for a Formula SAE (FSAE) Electric style race car. FSAE is a collegiate competition, and I'm part of a US Pacific Northwest team. Our car uses a 550 V battery and is 4WD. Most of our electronics and firmware/software are in-house besides motors and inverters. I lead the telemetry project and work with 1-2 other members on the system. > The system consists of an on-vehicle computer (Raspberry Pi or NXP i.MX) and a ground station (Rockchip RK3588S). We use Python heavily and the real-time dashboard part is done with InfluxDB and Grafana. Both computers and any user devices are on a Wi-Fi network made with a long-range WISP access point. > Our team is unfortunately very light on public tech blogs and how I wished I can change that... There is plenty of information on FSAE electric cars, vehicle telemetry, and both on the internet and they were a source of inspiration as I designed my own solution.

nitwit005 3y ago

This article isn't talking about the edge as embedded systems. It means cloudflare workers and the like.

brtv 3y ago

Interesting! I'm currently working on a system that writes time series data to raw binary files, but we're considering switching to a different file format for the same reasons. Have you considered any other formats, such as hdf5?

nunuvit 3y ago
The one potential requirement I'll caution you on is resilience against write failures, in case you're collecting time-series data and can't afford to lose a "session" or spend time messing with recovery options. HDF5 is not made for that. Binary and SQLite are better in that respect. SQLite wins on usability against binary and HDF5. https://cyrille.rossant.net/moving-away-hdf5/ https://cyrille.rossant.net/moving-away-hdf5/
- xyx0826 3y ago
  I'm not familiar with HDF5 but agree on resiliency. We are a collegiate racing team and our car's power rails aren't stable and redundant at all times, so power loss failure is something I've kept in mind from day one on my telemetry project. SQLite is generally equipped to handle power losses and write thread crashes: > An SQLite database is highly resistant to corruption. If an application crash, or an operating-system crash, or even a power failure occurs in the middle of a transaction... Quite often after a run the entire car is turned off and, on next power up, the databases are left as .db and .db-journal files. The code has no problem processing or even continuing on logging with DBs in this state.
  - nunuvit 3y ago
    HDF5 is more for storing and exchanging numerical simulation data. It doesn't have to be resilient to write failures because worst-case scenario you rerun the simulation or try again to copy the data into the file. SQLite writes are "atomic" transactions. After writing new data, it goes back to the index and registers that new data has been written using a single instruction. That's why interrupting it in the middle of a write doesn't result in partial data or a corrupted index.

hiatus 3y ago

> Onboard code logs ~4000 messages a second into three SQLite databases. After a drive session a script merges the three databases into a single SQLite session log. A bit unrelated, but curious as to why you wrote to three separate databases only later to merge them.

magicalhippo 3y ago
Could be three different processes interacting with different systems. Say one process listening to a CAN bus, another to local Ethernet[1] and a third to some I2C/SPI sensors. If they don't interact otherwise, which they probably wouldn't given they're just logging telemetry, it's more flexible to just have them as separate processes. The I2C/SPI might be a Python script, the others in C/C++ or Rust say, whatever is easier. [1]: https://www.keysight.com/blogs/tech/sim-des/2021/06/10/automotive-ethernet-the-future-of-in-vehicle-networking https://www.keysight.com/blogs/tech/sim-des/2021/06/10/autom...
- kazinator 3y ago
  Another question is what is interesting in a speeding electric car in such a way that 4000 events per second must be captured about it.
  - magicalhippo 3y ago
    Good question. As a non-racer who enjoys watching racing, I'd imagine at least some accelerometers/gyros and load cells if they got them, and possibly Pitot tubes or similar for measuring aerodynamics when testing, to get a detailed understanding on the forces on the car and how it behaves. Motor currents probably up there too, to catch current spikes. Curious to see how far off I am :)
    - kazinator 3y ago
      > detailed understanding on the forces on the car and how it behaves But at 4000 samples per second? You'd think twenty would be plenty. NyQuist: 4000 samples per second means we are looking to get information about an up to 1800 Hz signal. A6 on the piano keyboard (excluding harmonics). Sorry, I mean Nyquist. I was confused by NyQuil, the night time flu medicine.

sgu999 3y ago

Other have asked, but I'd also be very interested in knowing more if you can share more details. I had to spent a non trivial amount of time convincing others to use SQLite instead of dumb files in similar situations, stories like yours are pure gold.

Lex-2008 3y ago
Haven't seen these links mentioned elsewhere in this thread, but in case you haven't seen them yet: * SQLite is 35% Faster Than The Filesystem [1] * Writing to files safely is hard [2], and SQLite goes to great details to ensure consistency in case of application, OS, or computer crash [3] [1]: https://sqlite.org/fasterthanfs.html https://sqlite.org/fasterthanfs.html [2]: https://danluu.com/deconstruct-files/ https://danluu.com/deconstruct-files/ [3]: https://sqlite.org/atomiccommit.html https://sqlite.org/atomiccommit.html
rongenre 3y ago
I've had a huge amount of success using sqlite to collect and analyze logs. The obvious approach would have been to dump enormous csv or json files, but doing in sqllite gave me (a) much better reliability, and (b) the ability to come up with post-hoc analysis approaches without changing the main process. I was really pleased to add it to my toolkit.
xyx0826 3y ago
There are a lot of SQLite praises in this thread, on the internet and on SQLite's website so here are a couple of reasons that drove me to choose it. My experience with traditional formats like CSV or raw binary is that they have a somewhat lower overhead to "get working" but are quite difficult to scale and adapt to changing needs, a phase most projects are bound to go through. SQLite's reputation and adoption makes me comfortable to assume its operational correctness so I can focus on data itself, not reader/writer logics for it. Even though a SQLite database's schema may change, I'm glad knowing that the database file can still be easily opened and read many years into the future, something that custom formats may not guarantee. On CSV: Syntax and library differences aside, SQLite almost works like a drop-in replacement for CSV since a single table database can be essentially treated as a glorified spreadsheet/CSV if wished. My post processing data pipeline is built with pandas so it is virtually the same for me to read a SQLite database vs. a CSV file.

Solvency 3y ago

I'd love to know more about this electric race car, the telemetry system, technology behind it, etc. Do you have a blog (or can you recommend a place) where you do deeper on this subject matter?

xyx0826 3y ago
The system is for a Formula SAE (FSAE) Electric style race car. FSAE is a collegiate competition, and I'm part of a US Pacific Northwest team. Our car uses a 550 V battery and is 4WD. Most of our electronics and firmware/software are in-house besides motors and inverters. I lead the telemetry project and work with 1-2 other members on the system. The system consists of an on-vehicle computer (Raspberry Pi or NXP i.MX) and a ground station (Rockchip RK3588S). We use Python heavily and the real-time dashboard part is done with InfluxDB and Grafana. Both computers and any user devices are on a Wi-Fi network made with a long-range WISP access point. Our team is unfortunately very light on public tech blogs and how I wished I can change that... There is plenty of information on FSAE electric cars, vehicle telemetry, and both on the internet and they were a source of inspiration as I designed my own solution.
- Solvency 3y ago
  Awesome. If you dont mind me asking, what sort of dashboarding/visualization software do you use? Highchart homebrewn web apps? Some closed source auto software? What's that world look like? Always been curious about it from a front-end perspective.
  - xyx0826 3y ago
    Real time data is fed into an InfluxDB instance and shown on Grafana hosted on the ground station. This data is downsampled to cope with the volume of raw traffic. We also tried some solutions used by other teams like KX [0] but decided to go with a FOSS stack. A post processing pipeline converts original data to a however proprietary format used by AEMdata [1], a proprietary data analysis tool from our ECU vendor and used somewhat commonly in the racing scene. [0]: https://code.kx.com/dashboards/ https://code.kx.com/dashboards/ [1]: https://www.aemelectronics.com/products/software-aemnet/aemdata-logging-analysis-software https://www.aemelectronics.com/products/software-aemnet/aemd...

kazinator 3y ago

> None of them could match the flexibility, ease of use, and throughput of SQLite. It sounds unbelievable that you couldn't append some CSV or binary record to file via an open file descriptor more efficiently than inserting into SQLite. (It's clear that to have the data already in SQLite form saves post-race steps.)

regularfry 3y ago
It's an AND statement, not an OR statement. You can append to a CSV file efficiently, but while flexibility is context-dependent and ease of use is very subjective, we can assume the GP knows their own use case).
xyx0826 3y ago
The onboard code processes binary data: CAN bus messages and alike, along with their nonbinary metadata. The first version of the code used CSV because they are so trivial to read and write, but CSV as a text format required me to either hex or base64 the bytes which incurred a non-trivial waste of time and space. For a custom binary record I would need to write custom (de)serializers, which I wasn't willing to do given frequent changes in types of data I need to record during development. SQLite is the only solution for me so far that provides a structured data store for mixed data types, is power loss safe (important to an embedded system,) and is a high-quality and portable industry standard. Let along the SQL language itself, which has proved to be hugely useful for first-pass post processing given the amount of data that I ingest. Perhaps there are other technology that will work just as well or better: maybe Protocol Buffers and alike? Yet SQLite currently ticks all my boxes and I'm quite happy with it.