Joining Multiple tables using joiner nodes (Knime DB Labs)

[arunknimeuser]

Hi All,

I am developing a workflow in which I would like to push down the logic (SQL) to the Database. I am able to achieve that using Knime DB Labs nodes.

But is it possible to join more than 2 tables using a single joiner node. If I have to join 10 tables, I am ending up with almost 10 joiner nodes, where output of 1 joiner node is joined with next table and so on.

This not only makes my Workflow complex, but SQL generated at database is very inefficient as it forms in line queries.

Any help/suggestions in this topic are much appreciated. Thanks in advance.

Regards
Arun

[izaychik63]

In general the best performance is coming from stored procedures. So, you can create one with 10 tables join and call it from KNIME. Or you can do 10 tables join on SQL level using DB Query Reader (Labs) in one node.

[mlauber71]

Have you thought about using the SQL Executor node?

Database SQL Executor — NodePit

Here is a very simple example

multiple joins

I set up a demo how this could be done in KNIME although you could do that in consecutive steps or directly in a SQL database (as has been mentioned) but it is possible. Here I use SQLite which I like because it is a ‘real’ SQL database but you can just have it within one single file (you could switch that out for any SQL database). The Join itself is done in the SQL Executor node. Plus: you have it in one step. The downside could be it might get a little bit confusing with all the steps, but y…

[EvanB]

Nope, there isn’t an optional-in joiner in KNIME.

The way that KNIME generates SQL via nodes ins’t best delivered to DBAs for a separate production environment.

Best that you hand-write the SQL if you intend to pass it off to DBAs. If it is for your own purposes and you simply clean up the workflow, select all the joiners, then right-click the group and select
Collapse into Metanode.

[arunknimeuser]

Thanks EvanB. If I use multiple joiners , Workflow is generating dummy SQLs in between with condition 1 = 0 for all intermediate nodes. Is there a way that can be avoided? Of course writing the complete SQL to one is an option.

Thanks in advance for your reply.

[EvanB]

Not really sure of your use case for doing so. Unless you are doing some sort of looping of variables and inserting them into your SQL, you might as well write at least the basic SQL with your joins such as:

SELECT * FROM D.S.X AS X
LEFT JOIN D.S.Y AS Y ON Y.ID = X.Y_ID
LEFT JOIN D.S.Z AS Z ON Z.ID = X.Z_ID
WHERE X.CONDITION_1 = 0

From there, you can use DB row and column filters to get rid of what you don’t want.

Is this more what you are looking for??