I would like to read my single cell image feature data from SQLite. I need to join the data from 4 tables from the database and then read them before I start with data analysis.
I used SQLite Connector and Database Table Selector to select several tables, Then used Database Joiner to join them, all worked perfect. However, I cannt read the final table with Databse Connection Table Reader, Error Message is ´´too many columns in result set``. I have altogether 2199 columns.
I can read single table with 1597 columns, but with very long reading time, which means the columns are too many.
I also tried to use DB Reader and DB Joiner, failed already at DB Joiner, give error ´´too many columns in result set``.
I also tried to read single table and use normal Joiner to combine the columns together, It run till 99%, then stop there for several hours, I also cannt cancel it.
My suggestion would be to do the joining using the KNIME -Joiner- node which has been improved in efficiency and speed in the last KNIME versions. There is no real gain to do it with the -Database Joiner (legacy)- node since you are working with a SQLite database. Eventually the joining would be done in your computer with your own CPU and computer RAM memory whatever the choice, SQLite or normal Joiner.
Have you tried using it this way ? Alternatively I would replace the deprecated DB nodes you are using by the new ones too. They have been optimized since then as well.
@ShuLiu it seems there is a deliberate limit on the number of columns you can have in a SQLite table. You might be able to increas that but I would have to check how to do that in SQLite with KNIME (you might have to recompile SQLite …).
The default setting for SQLITE_MAX_COLUMN is 2000. You can change it at compile time to values as large as 32767. On the other hand, many experienced database designers will argue that a well-normalized database will never need more than 100 columns in a table.
You could try and use H2 local database instead where the limit seems to be higher:
The maximum number of columns in a table or expressions in a SELECT statement is 16384. The actual possible number can be smaller if their definitions are too long.
Nodes with “(legacy)” name are former database nodes. They have been replaced by more efficient new ones.
-Append- node is definitely faster although one needs to be careful since an appending operation is not the same as a joining operation. Does the appending of tables correspond to the expected results ?
