How to manage large datasets (approx 95GB)

Question

I was planning some data analysis on a dataset I'll be using for some projects. The dataset in question is ZINC20. Now, I don't need the whole thing so I was going to write some functions that would filter molecules based on certain characteristics. My question is how do I handle this massive amount of data in the first place? Even if I iterate through each "tranch" and add the filtered molecules to a csv or tsv, it will still probably be quite large.

Any tips on software or data types that would help? Thank you!

Answer 1

The solution here is to used distributed computing. Handwaving some details, we will simply take the large spread sheet, split the rows into roughly equal chunks and send them to a bunch of small distributed computers. Each computer runs the calculations sent by a master computer. Then the results are recollected and sent back to the master node.

A great implementation here is Pyspark in DataBricks. It is a pythonic way to use spark and tap into the power of distributed computing. Another method would be to use Google Cloud Platform. I find it substantially more difficult to use. But it has one huge advantage, free credits. Cloud computing costs money since you are using other people's computers. So with Google Cloud, you get $250 of free credits to tool around.

Answer 2

I think there are several approaches here, depending on your requirements on the downstream tasks. First, I want to note that 95GB is not that much data. It is simply a bit too much to store all data in memory at once and process it with something like Pandas all at once. But computationally, your local machine might suffice (again, depending on your use case). I will try and keep the approaches high-level, so that they generalize to other use cases/datasets.

1. Use a (local) Database

Most databases provide very efficient implementations of common operations such as filtering and aggregations, e.g., groupby or sum. Therefore, you could download the data, store it in a local database (a few starting points would be PostreSQL for relational/tabular data, InfluxDB for time series, MongoDB for semi-structured data) and continue processing from there.

You could then first use the functionalities of the DB and do everything else using your processing framework of choice. Most common frameworks such as Pandas work well with data from DBs. Maybe the analyses you want to conduct are small enough to fit into memory. Otherwise you can still split the workload, e.g., computing your analyses on splits of the data.

• Downside: Initial set-up, choice of fitting DB might be non-trivial

2. Use Stream/Batch Processing Engine

Such as Apache Spark. This enables distributed data processing and computations. If your workloads are very computationally expensive, this might be worth a try, but IMO it is only worth it if you use some cloud-based resources.

• Downside: Will most likely not be free, setup required, might be overkill

3. Chunk the Raw Data and Process it as Usual

By usual I mean using something like Pandas. Split the data into chunks that have roughly 1/3 to 1/2 of your memory size (might need to play around a bit). Remember that you must leave some space in memory for the calculations you want to perform and for the operating system.

Then perform all calculations regularly for each chunk. Save the results for each chunk as separate csv files to disk. After you finished all chunks, aggregate the results of each chunk (might be difficult depending on your specific use case).

• Downside: Chunk size required a bit of trial-and-error, aggregations in the end might be difficult