I have to implement data synchronization between two big databases which have completely different structures. Basically, I need to grap some data about products in different tables in the first database and re-arrange them for other tables in the second database.
Creating my products on the first time is not very complicated. But I'm looking for a way to update some specific data - not all data - about each product.
Obviously, there are a few issues that make this tricky.
- I'm not allowed to do anything on the source database apart select queries.
- On the target database, I can do usual queries (select, update, insert, create) but I can't modify the existing structure/tables.
- Target and source db have completely different structures, tables are not the same at all, therefore data really have to be rearranged - comparing tables won't work.
- Target database uses a MySQL server - source may be DB2.
- There are no "updated time" fields anywhere.
So the whole process needs to be done in a single Python (ideally) script.
I think about creating a hash for each product, based on the fields to update in the target database: md5( code + description + supplier + around 10 other fields). A new hash based on the same data will be created on a daily basis from the source database. I will store all hashes in a single table ( item code, current_hash, old_hash ) for performances purpose. Then compare and update the product if the new hash is different from the old one.
There are around 500 000 products so I'm a bit worried about performances.
Is it the good way to go?