Run DB Queries on multiple DBs

[masgo]

I want to do the same query on different databases and concatenate the results. The DBs have an identical structure, just different data. The queries are complex and might change later, therefore I don’t want to duplicate the nodes.

Here is a toy example of what I mean. This is how I would do it for two DBs. The parts in the yellow boxes are identical. The example gets worse if I want to add a third DB.

[mlauber71]

@masgo these things to consider:

• are the databases of the same type (ORACLE, Postgres, …) or are they different databases
• to just reproduce a code you can use a meta node or component so you would only have to edit (and store) the code once and you can re-use it in the other items. You might have to use Flow Variables to insert the table names
• if you have different database types they might have slightly different SQL dialects. You can address that by using the ‘brown’ DB nodes of KNIME which should take care of that
• you can combine the approaches (meta node/component) with a loop or switch. You will have to store the items that change (DB access, table name and so on) in some sort of meta table
• if you plan for a loop it is easier to include new DB connections (If there could be much more)
• do you want all the SQL code in one node or do you want to split the table selection and code (brown DB nodes) to easier maintain the setting

Maybe you start by setting up an example using SQLite and H2 as stand-Ins for your different databases and start setting up a working example that would represent your challenge

[masgo]

@mlauber71 thx for your thoughts. The switch thread was probably the solution for me. I am doing it like shown below and it seems to work great.

The DBs are actually identcal (same software, same version, same CREATE statements) but they are on different servers with different credentials and different DB URLs.

The only minor drawback of using CASE Switch nodes combined with a loop is that the queries run sequentially and not in parallel. But this is just a performance thing and since the whole thing is done in a few minutes, I don’t mind it.