Copy data from mysql to postgresql

[mgiammarco]

Hi,
I have a mysql database in normal form with around 20 tables, with numerical id key.
I need to copy data to a postgresql database, uuid based, that is similar but not identical to first one (it is a new version of the same application).
I am a newbye of knime, I would like to understand if knime help me saving time or it is better to write some code.
So I ask:

• has knime some nodes to help migrating from id to uuid?
• has knime some nodes to map columns for source table to destination table?
• is knime a good tool to save time for this job?

Thanks,
Mario

[mlauber71]

@mgiammarco concerning the transfer you might want to think about streaming like in this example.

Transferring data between databases – tobias.koetter

Then depending on the database it might support UUIDs so you could create them in the DB, maybe after storing the table after transfer in a temporary table.

Errors writing KNIME string to PostgreSQL uuid field

A solution could be something like this. You upload the file with the UUID into a emporary table and then insert it into the target table [image] The CAST() command would convert it into a uuid [image]

Postgres seems to support uuids: The Basics Of PostgreSQL UUID Data Type

Another option would be to create uuid in KNIME. You would have to see how to then insert them into the target DB

GUID Generator – sjporter

[mlauber71]

@mgiammarco here is an example. You would have to change the DB nodes. Your initial database is in the H2 DB with some data in: “data_insert_types”.

Based on this table an empty table “data_insert_types_transfer” is being created in the Postgres DB. Then this empty table gets adapted so that it will automatically produce an additional column called “my_uuid_column” with this command:

ALTER TABLE "public"."data_insert_types_transfer" ADD COLUMN my_uuid_column UUID DEFAULT (uuid_generate_v4());

This column now automatically will have uuids (you will not have to fill it yourself). Please note you might have to activate/create this function once in your Postgres DB:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Then you can just write the data from H2 thru KNIME into the Postgres DB.

In the component Streaming will be used in order to speed up the process (you can experiment with the chunk size and also do additional manipulations):

The data will run thru the (local) KNIME in small chunks and so speed up the whole process:

Since the databases will be dynamically created you could work this example into a loop just substituting the table names.

PostgreSQL - create data table with uuid - transfer data from H2 database to...

kn_forum_67024_db_postgres_transfer_data_uuid.knwf (85.1 KB)

[mgiammarco]

Thanks for detailed replies I am positively suprised!
I have just another question: main table has several one to many relationships. How can I recreate them on destination db? Is there a component that can fill a lookup table while streaming data from main table?
Thanks,
Mario

[mlauber71]

@mgiammarco I would assume you would have to enter some constraints when creating the databases. You might have to test it.

---

PostgreSQL Constraints

5.4. Constraints

PostgreSQL Foreign Key

PostgreSQL Foreign Key