20

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?

  • 2
    Do they want you to do it blindfolded too? That's my problem right now... – Captain Hypertext May 3 '16 at 19:35
  • 1
    @Neow, How did it go? Any advice you can offer now? – Edwin Evans May 17 '17 at 22:24
  • 3
    @EdwinEvans basically I stayed with my first idea, but especially due to constraints I had. My script creates md5 hashes based on key data for all items. Then I compare against previous hashes. If the hashes are different, then it loads all data for the item and updates everything. Not sure if this is the best way, but it runs at night and performances are decent. – Neow May 22 '17 at 7:21
7

This is pretty much what I have been doing or a living the past few years, and my gut instinct is that the time to read 500,000 items from the source database and sync in the destination will not take as much time as one might think and the time taken to read the "key" fields, compute the MD5 hash, and cross check with your table to avoid syncing items that haven't changed won't end up saving too much time and may even run longer. I'd simply read all and update all. If that results in a runtime that is too long, then I'd compress the runtime by making the ETL muti-threaded, with each thread only operating on a segment of the table but working in parallel.

It would be important to ensure that your destination database has a primary key index or unique index. Otherwise, each of your updates/inserts could lock the entire table. This would be bad if you are taking the multithreaded approach, but important even if you are remaining single-threaded because your job could lock the destination DB table and interfere with the application that rides on top of that DB.

You say the source DB "may be DB2". When you say "may" it implies that DB is still being designed/planned? DB2 9 or above does have built-in tracking of last update time, and the ability to query and get back only the items that have changed since a point in time. Perhaps this is why the DB was designed to not have a column indicating the last updated time, eg:

SELECT * FROM T1 WHERE ROW CHANGE TIMESTAMP FOR TAB t1 > current timestamp - 1 hours;

The timestamp cutoff for the above query would be the last timestamp your sync ran.

If this is the case, that should solve your problem. But, your solution would end up being tied very tightly to DB2 and in the future they may like to move to another DB platform and expect your sync job to not need to be re-visited. So it would be important to make sure all the right people know that your product will be dependant on remaining on DB2, or if they plan to migrate that migration would include restructuring the DB to have a "last changed timestamp" column, and make whatever changes necessary at the app level to populate that field.

5

The data sync would be much better and faster, if it can be done on the basis of some kind of delta identifier or flag. Basically, you should update the target db data rows only when it is out of sync with the source db.

In SQL server db, you can take the help of the Checksum fn also to build the delta based identifier.

You should develop a SQL based job to get invoked at a certain time of the day or night to get this sql logic fired. It is better to run it as a nightly SQL job, when the db usage is very low. If the delta of the source and the target db records does not match, then pull those records only. But the downside would be to calculate the checksum of the source data rows every time and then compare it with the target data.

If you have a column like "LastModifiedDate" in the source db tables, then you can skip the checksum approach. This way, your evaluation will be executed on the date based column and takes less time in comparison to the checksum approach.

  • Thanks but I'm not sure your solution could work - see my edits in the "issues" part. – Neow Jul 1 '15 at 9:29
  • Since there are no updated time fields in the source database, then we are left to pull the qualified data rows based on the checksum or the hash. – Karan Jul 1 '15 at 9:45
  • Since your source is db2. How do you intent to pull the data from it ? via some webservice or API.. – Karan Jul 1 '15 at 9:48
  • A dsn has been set up using an odbc driver. I can connect and do queries using pyodbc for Python. – Neow Jul 1 '15 at 9:59
  • Alright this is good, since you can perform the queries using the tool called PyODBC into the remote DB. You can do one more thing. You can pull the product data straight in the same format as it is into the new "Staging table" in your target DB without any checks or validations. This way you will get the live data in a single shot in your target db under the stage tables. Then later in the second step, you can perform the checksum operations and update the target transactional table data. This would prevent the hash or the checksum evaluation with the source db data at the real time. – Karan Jul 1 '15 at 10:49
1

Using a hash is a good idea. Since security is not the goal in this case, choose a hash function that is fast (md5 is fine).

Unless you plan to split the hash calculation across multiple threads/processes, you don't really need to store the current hash value in the database. If your process is a single script, you will just have the current hash in memory, and will write it to the database as the old hash after you have updated the data in the new database.

-1

you should have create a windows service which will run at some speficif times whenever you want and it will find the changes in your source database and insert that changes in your destination database.

  • -1 (didn't really downvote, but;) for windows only suggestion. let's not rely on any specific architecture when developing software it just means that only a few people can use your stuff. the only constant is change and so it's better not to rely on any specific platform to the extent that makes things easy to maintain for yourself and for users – pythonian29033 May 23 '17 at 9:49
  • @manish kumar the part "it will find the changes in your source database" is the hardest one! – Narvalex Aug 25 '18 at 1:44

protected by gnat May 13 '17 at 11:27

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.