On 10/16/19, Mitar <[email protected]> wrote:
> Hi!
>
> We are considering using SQLite as a ML dataset archival format for
> datasets in OpenML (https://www.openml.org/). When investigating it,
> we noticed that it has a very low limit on number of columns. Quite
> some of datasets we are dealing with have 100k or so columns. Are
> there any fundamental reasons why this limit is so low (even if we
> extend it during compiling, it can be at most 32k columns), while
> others are comfortably large? Any plans to extend this limit in the
> future?
Are you trying to store a big matrix with approx 100k columns? A
better way to do that in a relational database (*any* relational
database, not just SQLite) is to store one entry per matrix elements:
CREATE TABLE matrix(row_number INT, column_number INT, value ANY);
Only three columns are required in such a relation, regardless of the
number of columns in the matrix.
If performance and storage efficiency are a high priority and if the
number of rows and columns are limited to 2 billion each, then you can
combine the row and column number into a single integer key:
cell_number := row_number*2147483648 + column_number;
Then make your table just this:
CREATE TABLE matrix(cell_number INTEGER PRIMARY KEY, value ANY);
--
D. Richard Hipp
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
