SQLite - make table with (unfortunately) many columns

[dpak]

I have a 2D DBL array listing the relative abundance of >50k chemicals in >5000 samples.  Not big, but not tiny.  And, I am loking into the possibility of saving this into a SQLite file (along with other info about the library - but this is the most valuable table)  I know I could use JSON as a method of making multiple tables with smaller numbers of columns, but given that some users of this data are likely to want the table to be human readable it would be useful to have it stored as a 50k fields x 5000 records table.

If I use the SQLite for labview transaction code for Insert many rows, I would need to drag down the Bind parameters property node to 50k elements - clearly not practical to wire!

But, does anyone know of a way to make a table such as this using SQLite for Labview? Given that I'm starting with a 2D DBL array (and two 1D string arrays holding the sample names and the chemical formulae).

We will be searching the table, as a library, but also doing PCA and other tasks on this table - so it will need to be available whenever the data is visualized and to compare against new data.

Thanks in advance,

David

[drjdpowell · Accepted solution]

Note that you can use Bind in a For Loop, to bind an Array of Columns.

However, I'm not sure SQLite can do 50,000 columns. 

Though, I'm having a hard time believing one can measure the abundance of 50 thousand different chemicals in a single sample.  Are you sure each sample has meaningful numbers for more than a few chemicals?

Another way to do it is have a table of ChemicalID, SampleID, Value.  Then it is easy to find, say, all Samples with certain chemicals above a certain value.  If each sample has only a few chemicals, then this would be much smaller and faster.

[dpak]

Hi,

Thanks for your answer!

They are not my results 🙂  But, with crude oil or natural organic matter analysis by FTICR-MS or 2D-GC-MS, you can easily get to these numbers now.

I tried putting the Bind in a For Loop, to bind an Array of Columns, but it was really slow!  So slow that I abandoned that approach.  But, maybe I was wrong?  Do you mean that instead of having a Bind Parameters property node with n lines, you have it with a single line, but in a for-next loop running n times?

[drjdpowell]

Yes, one bind, in a loop (with a shift register, of course).

[drjdpowell]

Another technology you should consider, as it is designed for large arrays, is HDF5.

[dpak]

Sorry - kids' bedtime got in the way.

I had worked the attached out while waiting for stroppy 4 year old to give in to sleep!  Maybe it will be useful for someone else? I must have been doing something very daft before.

A quick halving search, using that vi, reveals that 2k columns is the max permitted in SQLite.  I wonder how many tables is the max per file - I'll find that out tomorrow.

Yes, I'm using hdf5 elsewhere but that format is a bit hard for some of the users of this app to deal with. (They want to be able to acess the data via R, as well, and my experience with hdf5 is that you need more technical understanding to work with those files.)

Thanks for your help with the looping bind statement - that will be very useful elsewhere too.

[drjdpowell]

The 2k limit on columns can be increased, but there is a hard limit of 32k which can't be gotten around. https://www.sqlite.org/limits.html