Dear all,
I am looking for the best option in order to store big datasets with thousands of columns.
The dataset can contains from tens to hundred thousands lines and thousand of columns (some columns are string some numbers). Which is the best option to store and retrieve information from a dataset like this?
Actually I am using sqlite, tens of tables including maximum 200 columns. But I am not sure this is the best option. Within my application I have to query this dataset:
- retrieve a particular line (all or some columns);
- retrieve a particular column (all or some lines);
- order the dataset with respect to a particular column;
- delete/add line(s);
- delete/add column(s);
...

SQLIte is easy to use when I need to query the dataset but I am not sure that is the most suitable.

Any hint?
Andrea Mauri

one more thing. my data is more similar to a huge spreadsheet than a relational DB, anyway I am looking for the best option already available in lazarus/fpc to store and query the dataset

Il 26/03/2013 15:38, Andrea Mauri ha scritto:

I think this question is more related to databases than Lazarus/FPC.

If you think you'll be adding columns regularly, one solution that comes to my mind is this:

1) Create a "data_columns" table:

create table data_columns(
idcolumn integer,
column_name varchar(20),

  primary key(idcolumn));

2) Create the "data_rows" table:

create table data_rows(
idrow integer,
idcolumn integer,
value varchar(100),
primary key(idrow)
);
alter table data_rows add constraint fk_idcolum foreign key(idcolumn) references data_columns(idcolumn);

3) Then you can create the columns and add data to the "data_rows" database referencin to which column this "cell" is related.

4) To get the "spreadsheet", please read this:

http://stackoverflow.com/questions/3002499/postgresql-crosstab-query

Regards,

I tried something like this but I have to insert millions of entries when application is running and this solution will slow enormously the app.

If you know the columns in advance, nothing stops you from creating such large tables.

Andrea Mauri wrote

one more thing. my data is more similar to a huge spreadsheet than a relational DB

"store": Do you mean for working with it in "realtime" or for keeping it for the next time the program is started ?

When doing a with a 64 bit program it might be possible and appropriate just to use an array and have the OS do the dirty work of virtual memory management, using a huge swap partition.

Of course loading the data from a file when starting the program and storing it before ending might take some time. (BTW: this could be using compression.)

-Michael.
.

Il 27/03/2013 09:27, Michael Schnell ha scritto:

Ok I will explain better.

I have a GUI app.

The user loads samples (the rows), my app performs calculations on samples and for every sample give as output thousands of values (the columns). Samples could be from tens to hundreds of thousands. Columns can be tens to thousands.
Every column is an attribute of the sample defined by a unique name. After calculations the app/user should be able to search for one/more samples (or for one/more columns) getting all/some values for the sample/column. Briefly I need to be able to rapidly get some values from this huge data matrix.

I have two scenarios, both are possible, depending on calculations type:

1. all the columns (the number/type of attributes to be calculated on every sample) are defined by the user before performing calculations, so I know the total number of columns before starting the calculations. Actually I use a sqlite db that I define before calculations started. I create as many tables as I need where every table has 200 fields. The samples are stored in a table, every sample is a row. The attributes are stored in all the other tables (every sample has a row in every attributes tables).

2. The number of columns can vary from a sample to another. The number of columns/attributes for every sample is known only after calculation is performed on that sample. The total number of columns/attributes is known only after all samples are examined/calculated. During calculations I need to modify the number of columns. Using sqlite is quite annoying since I need to alter tables when a new column occurs also closing all the queries, modifying the insert queries and so on. During a calculation process thousands of columns can be created and need to be added.

The app can be splitted in 4 different tasks:

1. load of samples (then the user can look at samples, load new samples adding to existing ones..);
2. calculations (can take seconds, minutes, hours depending on the number of samples in the batch and on the number of attributes to be calculated), the user can leave the app working - no interaction, the user is just able to stop/pause the process;
2. calculated data analysis (interactive), the user can perform analysis on data (charts, data views, data manipulation [average, standard deviation...]...) (this should be as fast as possible, data retrieval from the db/datasheet fast);
3. save of calculated data to a txt file/spreadsheet... (not necessarily all calculated data will be saved by the user).

The user should also be able to delete/remove samples/columns or add samples/columns.

When doing a with a 64 bit program it might be possible and appropriate just to use an array and have the OS do the dirty work of virtual memory management, using a huge swap partition.

My app is actually compiled both on 32 and 64 bit. Maybe because I am old ;) (I started programming when RAM was to be used carefully, 1Mb of RAM was typical) but I am inhabited to not put all the data in memory, maybe I need to change my mind. Is it an option to not store on files but to take everything in memory and let app and os to perform the dirty work?

Andrea

I think that there's a risk that any solution that relies on having a large number of columns in a database could suddenly stop working if the data exceeds some server-specific limit. Granted that this limit has expanded over the years but in a pathological case where a very large input table (i.e. lots of rows) containing worst-case data was in effect rotated by 90 degrees (i.e. lots of columns) it could still be significant.

I think my choice would be to generate an intermediate table, if necessary with extra explicit indexes, that allowed the final query to extract and process only rows that matched certain criteria, even if they were then presented as columns.

However a lot depends on (a) how often the program is started, (b) the acceptable latency at startup and (c) whether persistent storage is necessary so that e.g. additional clients can inspect the data without having to regenerate it themselves. Iff the answers are "seldom", "lots" and "no" then using an internal array or some form of sparse matrix might be better than using a database.

Andrea Mauri wrote

but I am inhabited to not put all the data in memory, maybe I need to change my mind. Is it an option to not store on files but to take everything in memory and let app and os to perform the dirty work?

That is the main reason why 64 Bit has been introduced: decently handling a huge amount of linearly addressed memory (real and virtual).

-Michael

Although I'm worried about the extent to which OS vendors etc. might assume that "nobody really needs 64 bit addresses" and partition off large chunks for their own use (ditto with 128-bit IP6 addresses) http://en.wikipedia.org/wiki/File:China_imperialism_cartoon.jpg

If using a relational database, I would also propose that solution. Messing with columns each time is just not what they're designed for.

Additionally, if you ever need to scale it up or store data in a database server: different dbs have different limits on max number of columns, performance may suffer when you have a huge amount of columns etc.

Andrea, looking at your description I probably have a suggestion, although we're are more and more drifting from Pascal :)

Once I wanted to extract CIA Factbook data and when noticed there are about 170 columns decided to try Triplestore (Object-Propery-Value) similar to rdf. Basically the data was contained in a table of Sqlite having very few columns. Changing to your case this can be

CREATE TABLE [Data] (
... ,
[Row] INTEGER, [Column] INTEGER, [Value] TEXT
...
)

Additional tables were used for naming countries and properties (rows and columns in this case) and indexes was used for quick querying. Although in my case 170 was much less than your tens of thousands, this approach can work. Your I noticed that queries for such table were similar two general queries, only extra join was usually added. Moving from sql columns helped a lot in thinking abstractly about the data and queries became more flexible.

Max
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20130327/e2d95916/attachment.html>

Il 27/03/2013 16:11, Max Vlasov ha scritto:

As I answered to Leonardo I tried something like this but I have to insert millions of entries when application is running and this solution will slow enormously the app.

Andrea Mauri wrote

My app is actually compiled both on 32 and 64 bit.

As this is a really typical application that requests for 64 Bit I would drop the 32 Bit route here.

-Michael

On 03/27/2013 12:48 PM, Mark Morgan Lloyd wrote:
Even if half the address room (i.e the upper Bit) is stolen by whatever organization. 63 Bits is by far enough for any thinkable purpose.

-Michael

Michael Schnell wrote

On 03/27/2013 12:48 PM, Mark Morgan Lloyd wrote: Even if half the address room (i.e the upper Bit) is stolen by whatever organization. 63 Bits is by far enough for any thinkable purpose.

And then somebody steals another bit, and somebody else a couple more... and before long the application program is limited to 40 and people start scratching around for an extension.

Scientific data formats maybe best for this task. Something like HDF or NetCDF or the like.

I have wrapped the header of HDF5 in version 1.6.4 (or was it .5?) a long while ago with the help of MvC and that is lingering on my harddisk somewhere in an unknown state - but has been working quite good in the cases tested. That might help somehow.

I'd have to clean up a bit for sending you a copy, but be aware that curently HDF5 is coming in to flavors: the "old" style that is sort of abandoned but still usable had version number 1.6.9 last time I had looked after it. The new "product line" is starting with version 1.8.x and I have no idea what changes would be necessary. Those two flavors are not installable in parallel (at least on FreeBSD I'm using).

There are some problems in declaring hfd's "compound types" in source code coming from the original source using C-macro-voodoo that is not very handily portable, but you should not be forced to use that data types, simple "array"-type is yours, I think.

Look there to get an impression:

http://www.hdfgroup.org/HDF5/

If you're intrested and have a little time to wait for results from my side, I'll send you a copy.

Have fun,
Marc

Alternatively: http://ibexpert.net/ibe/index.php?n=Doc.Array

Also supported by PostgreSQL, although I've not had a chance to investigate how compatible the implementations are- I've got no immediate intention of attempting to use them.

As a general comment, sometimes populating a table can be done far faster with a server-provided program rather than going through SQL. Also Postgres doctrine has it that if populating a big table it might pay to disable all indexes until the bulk operation is completed.

Andrea Mauri wrote

I am looking for the best option in order to store big datasets with thousands of columns. The dataset can contains from tens to hundred thousands lines and thousand of columns (some columns are string some numbers).

Have you looked at '/Synopse Big Table/'?

http://blog.synopse.info/post/2011/01/22/Synopse-Big-Table-1.12a

It is Delphi code --I have no idea whether it compiles with FPC or how much effort is required to do so.

But, from the sound of what it does, it seems it might be something you might be interested in --plus it is in Pascal.

You might also want to read this:

http://stackoverflow.com/questions/1799634/how-should-i-implement-a-huge-but-simple-indexed-stringlist-in-delphi -------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20130403/2eb36f7b/attachment.html>

Il 03/04/2013 01:00, ListMember ha scritto:

I will have a look, thanks!