Database for sensor time series w/ metric summaries?

user_3429954 0
Viewed 177

I have 4 sensors, hourly event counts and periodic metrics. Sensors record 5 hours/day, @ 512 sample rate ~ 280mb/day (once a week its ~500mb). Event counts are 4 different floats representing event occurrences during that hour. Metrics are various evaluations that occur periodically (2-5x/day). Data is received once/day so inserts are rare.

Operations/Transformations

  1. Signal Processing
    • artifact removal
    • spectral decomposition
    • correlations
    • statistics (means, stds, kurtosis, skew, distributions, etc)
  2. Event/Metrics
    • progression over time
    • statistics across various time scales
    • clustering/grouping
  3. Event/Metric Correlations w/ Signals
    • what does the signal look like during an event? pre/post an event? across similar events?
    • Are there patterns or trends when we align the time series around events or metrics?
    • How do these relationships evolve over time?
  4. General Anomaly Detection

The majority of the signal processing is fixed but the raw data will need to be kept along with summaries, i.e., store raw data, store artifact time frames (along with what type of artifact occurred) and spectra (5 different windows so 5 additional time series but this will be at most 4 samples/sec and up to a sample every minute). The events and metrics can likely be stored as meta data or even in another db as it's much smaller.

Data is currently sitting in HDF5 store w/ a bunch of python wrappers to ease queries. The queries are getting slower every day to the point where after a month of data collection running windowed transformations across all time (30s window across entire db, very common operation) takes 10minutes. After a year of collection this will be 2 hours. 2 years of data 4 hours etc.

I have access to AWS, I have some experience with databases (sql, Cassandra, mongodb) but am willing to learn w/e tool/db fits my use case best. I'm good with python, shell scripting and have some c++/cuda xp. Most of the operations I have mentioned are highly parallelizable so I was thinking spark w/ hdfs/cassandra, redshift etc. I'm not really sure.

apache-spark time-series distributed-computing database
1 Answers

Spark

Apache Spark achieves high performance for both batch and streaming data, using a state-of-the-art DAG scheduler, a query optimizer, and a physical execution engine.

Spark SQL lets you query structured data inside Spark programs, using either SQL or a familiar DataFrame API. Usable in Java, Scala, Python and R.

Spark SQL supports the HiveQL syntax as well as Hive SerDes and UDFs, allowing you to access existing Hive warehouses.

HDFS

HDFS is a major part of the Hadoop framework it takes care of all the data in the Hadoop Cluster. It works on Master/Slave Architecture and stores the data using replication.

Hive

Hive is a data warehouse software that allows users to quickly and easily write SQL-like queries to extract data from Hadoop.

While Hadoop is very scalable reliable and great for extracting data, its learning curve is too steep to make it cost-efficient and time-effective. Another great alternative to it is Apache Hive on top of MapReduce.

Or you could combine them all as one ecosystem of your services.

user_41157520
Related