I have an Arduino project that sends lots of measurement data to a PC which at the moment just gets saved into into a huge log file. Ok if you're only intested in todays data but not so good if you want to compare todays measurements with those from yesterday, last week, last month or last year.
What's a sensible way of saving the data so I can do this kind of data analysis?
Relational databases aren't designed for time series data and from what I can tell, proper 'time series' databases are complex enterprise scale behemoths.
I just need something that I can get working in a few hours, not spend days wrestling with config files and cryptic error messages.
It allows you to send formatted data to excel which means you can pick and choose and graph what you please.
Excel would simply time stamp the data as it arrives...
No need to add new data to the stream.
From there a database is an easy step with pass through.
It looks like an Excel sheet can only hold just over a million rows. At a sampling rate of 1Hz it'll be full after 12 days. To store a full years worth of data the data points will have to be at least 30 seconds apart.
Once in excel you can MACRO to save / close and open a new sheet.
Or simply MACRO to pass data to a database.
IIRC somebody also got it to work with open office too.
You haven't said much about the contents of any stream apart from there is lots of it.
Are they short variable ? eg temperature and humidity only occupy small amounts of space from a single sensor whereas a 6 axis gyro would give the 6 readings and any interpolations consuming more space.
AN idea of your data stream itself (sample of a completed packet) would be useful maybe too.
ballscrewbob:
AN idea of your data stream itself (sample of a completed packet) would be useful maybe too.
Currently there are about 40 fields, either temperature/voltage/current/flow rates to 2 decimal places, counters with 6 digits, 8 character status fields, or variable length text fields for debug messages. About 10 or so change their value every couple of seconds, the rest change every few seconds, minutes or hours.
On the day it's being collected it's good to capture everything at 1 second resolution for debugging purposes.
Once the data is more than a day old then I'd be happy to discard the numerours duplicate values for the slowly changing fields.
One second intervals with all that much information may be pushing a lowly Arduino a little bit.
What board are you currently using ?
A good reason to offload as you are trying to do.
Given the possible overheads of a serial connection and delays you might want to re-assess your project a little.
Or settle for a slightly lower sample rate.
If your project is reasonably stable certainly consider cutting back on debug.
Your data may also be too fast for most of the IOT / CLOUD units too as many have sample time restrictions.
A WiFi or Ethernet stream may be a better option then you could post it almost directly to a database as they can be set to listen to IP's or simple web addresses.
Check out the networking section and I am sure there are some good helpful posts in there for the type of project you have.