nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Clustering 4 variables, 139M rows of data, 30GB

Phil_Kay

Hi Everyone,

Slightly difficult request this. A prospective customer has large data and wants to analyse all of it.

Of course, we have told them that sampling would make much more sense. But they still want to analyse all of it.

We have also told them that they will need lots of RAM, which will mean server installation. Still they want to know if it will be possible to analyse all of their data.

What suggestions do you have for how we can respond and provide them assurance that JMP will work for them? Or, if clustering on this volume of data would be a non-starter, that would also be good to know.

The details about their data that they have provided are below. I didn't want to ask for more details at this stage without deciding on the best approach for responding to their request.

We want to run the cluster for 10 months’ worth of data for total volume as below:
Number of rows : 138734720 (138.7 Million rows)
Number of columns: 4
Data volume: 30 GB

(Copying @MarcoSalfi as I will be out of office after today.)

Thanks!

Phil

Accepted answers

russ_wolfinger

Wanted to mention data this size is fairly common in genomics and I don't consider the customer request to be that extreme, nor do I think a server is required. For the new performance work in 17 we are mostly targeting wide tables (transpose of what you describe), but certain tasks like clustering may still be desirable in tall form.

I just tried creating a 138734720 x 4 table with random normal data on a Windows PC with 128 GB RAM and ran the latest build of 17:

- K-Means: K=100, 30 iterations takes 20 minutes and uses 44 GB RAM
- Hierarchical: Hybrid Ward, pegs CPUs at 100% most of the time, 16 Cycles take 3.5 hours, 80 GB RAM
- PCA: 15 minutes

One trick is to use K-Means is to save out representatives for each cluster and then do Hierarchical on them.

All comments

MikeD_Anderson

So - TL;DR - try to table the question off until JMP 17. This sounds a lot like something that they were working on as part of addressing the needs of Genomics data.

With something that big in JMP 16 - you're going to need to do it on a big cruncher. And it's going to take a while to do clustering if it works at all. It looks like neither K means or Hierarchical are Multi-threaded (https://www.jmp.com/support/notes/49/280.html) so they're not going to get any benefit from a high CPU count.

Some things they could try could be:

Make sure all the data is numerical and that it is compressed (i.e. run Cols > Utilities > Compress Selected Columns on everything).
Categorical data should be encoded using Labels to Code
Try recasting the data. If there's a time domain (or something time-like), can you break it up in some logical fashion so that FDE could be used to simplify the data set and then cluster on the FPC's? This would be useful for sensor traces, etc.

Best,

Chris_Kirchberg

On a more practical level, what are they trying to do with clustering 100's millions of rows with only 4 variables( or is it 3 and the 4 column is a sample ID column). Are they looking for patterns in the rows so they can group them? Maybe there are more variables that are hidden in the rows that should be split into columns (like time/date). Clustering that many rows does not seem like it would produce any value or meaningful information (let alone how to visualize it). IF patterns are what they are after, then they probably need to reduce the rows into additional columns (factors) that represent the know groupings.

As @MikeD_Anderson hinted, if this is a time based analysis, FDE would be better to look for patterns over time instead of clustering. Time/date variable(s) would need to be teased out.

russ_wolfinger

One trick is to use K-Means is to save out representatives for each cluster and then do Hierarchical on them.