Mysql or Postgres with Knime

[nxfxcom]

Hello,
We are planing to set up a massive local database on a knime machine for raw storage. Lots of large inserts, writes, raw JSON (I know it’s not ideal), and more. Multiple writes at the same time, advanced joins etc. Based on my research, it seems Postgres is better for this. How about Knime nodes interactions? Is there a benefit (better code, more functions, etc.) to either?

Thank you

[mlauber71]

@nxfxcom KNIME does work well together with all sorts of (SQL) databases. You can either use the dedicated nodes or just use your SQL code. The main benefit will be that the users will be able to combine the power and functions of KNIME with that of your database.

KNIME, Databases and SQL

That being said. First the performance will very much depend on the setup and the power of the database and the server it is running on and (possibly) the network connection (if there is any).

You mentioned JSON structures in your database. Can you elaborate on that. @armingrudd said once that if you want to do more than store the data (like extracting information from a JSON substructure) other databases might be better suited then MySQL - PostGres is already on you roadmap it seems:

Storing JSON in Database

Hi @nxfxcom, Based on my experience, if you are dealing with large JSON files, it would be wise to consider MongoDB or DynamoDB instead of saving the whole thing into table cells. So, in case you need to access part of the JSON, you don’t need to call everything back and parse it locally, instead you just query for what you require. To work with MongoDB in KNIME, you could use the KNIME MongoDB Integration extension and for DynamoDB, we have KNIME Amazon DynamoDB Nodes. Alternatively, PostgreS…

Luckily KNIME can also work with them - maybe you can elaborate further.