Make tables query accesible by SQL "+ according node

KNIME tables to be made “SQL aware” so that a SQL node could be made which one can query the table. this includes filtering, aggegration and any other “SELECT” related operation.

Hi @kienerj , I like that idea, and I was thinking about what the mechanics of it would be.

For a data table to work with the various DB nodes, in a familiar way they would probably have to have some form of a “KNIME Table Connector” node to provide a “database” connection that is then usable by the other nodes so that they would see it as a database. This connector would then have to supply the same driver calls that are currently used by other DB Connectors, so that from the DB nodes’ perspective they are simply interacting with a database.

So that got me wondering if this “architecture” could be emulated with components.

Having already built some components for joining KNIME tables using SQL, incorporating an H2 database under the hood, I thought it probably isn’t a huge step to make this more general and simply build a KNIME Table Connector.

This connector would also utilise an H2 database and hold the contents of the data table into the database, and then make it available to the DB nodes. There are always some limitations, such as the data types and column names being compatible with H2, and also I learned previously that it helps to be able to create indexes on H2 if you are going to join large tables so this has been included to an extent.

Take a look at my demo workflow here. It might help with ideas for eventually creating a standard node for achieving something similar.


Interesting or said otherwise I was thinking much smaller. like a single node “SQL Table Manipulator” and the input is just an SQL statement (I never use the DB nodes except connector and DB query reader).

But yes your prototype makes sense and is much more general than what I was thinking of.

1 Like

That’s @takbb alright :slight_smile:

1 Like