Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: How to do daily data synchronization?

  1. #1
    Join Date
    Nov 2007
    Posts
    4

    Default How to do daily data synchronization?

    Hi,
    I am new to ETL world. I plan to use ETL in a new project to do database synchronization. The src database is a commercial CRM application's backend database. And the target database is our owned development database to do development based on the CRM information stored in the CRM database.

    In the production, we need to get the data from CRM daily to make our cached data fresh. I am not sure whether Kettle have a best practise to do that kind of daily synchronization.

    What I can image are the following two ways?
    1. First time do a fully sync. Then in the successive runs, just use a lookup node to find whether the primary key is there in the target table. If the node cannot find a duplicate, then insert that row.
    2. Or use a flag / date field to identify the sync status. And in the table input node use where clause to select the newly updated one.

    The second way is not suitable for our case. Because we cannot control the CRM database. So we left with the first way.

    Does Kettle provide a much elegant way to do that?

    Best Regards,
    Yang Sun

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    "golden hammer syndrome" ... there are better ways than Kettle to synchronize databases. If you would be on Oracle use DataGuard e.g. (comes free with the enterprise edition), on DB2 use DPROPR, ...

    Regards,
    Sven

  3. #3
    Join Date
    Nov 2007
    Posts
    4

    Default

    Hi, Sven:

    Thanks for your reply. Are you saying the tool provided by the database vendor is better to handle that kind of sync and transformation tasks? I am guess the tool may use some internal meta data to get the job done.

    And I am a little confused. Could you pls tell me what is the advantages to use Kettle except for its free license?

    Regards,
    Yang Sun

  4. #4
    Join Date
    May 2006
    Posts
    4,882

    Default

    Kettle is an ETL tool, not a database synchronization tool. Extract/Transform/Load for datawarehousing, not to keep your local cache of the source system in sync. Transformation yes, sync no

    You can probably use a hammer for screws, but a hammer is better suited for nails.

    You can use Kettle to make a database copy with some effort but why bother if it's not the right tool and better solutions exist for it. Oracle/DataGuard, DB2/DPROPR, ...
    Even open source there are database synchronization tools available like e.g. http://sourceforge.net/projects/symmetricds/

    What I would expect for a database synchronization tool e.g. is that you would select the tables to be synchronized and the tool would find out which columns the tables have and moving those automatically. If DDL changes would happen on both databases I would expect it to cope with this as well. Kettle e.g. does not cope DDL changes automatically, because it's not meant to be a synchronization tool.

    The advantages of the vendor own solutions compared to open source solutions (if the vendor is not using open source, ...) are that the vendor solutions can work more transparantly, they have "inside information".

    Regards,
    Sven
    Last edited by sboden; 11-24-2007 at 12:59 PM.

  5. #5
    Join Date
    Nov 2007
    Posts
    4

    Default

    Your comments really helps me solve my confusion.

    Currently, the problem I face needs ETL (some calculation from the src table before inserting to target table) and synchronization feature(daily updated the target database when updates to the src database) as well. The src and target databases are all based on MS SQL Server. I have a quick view of the opensource product you suggest. It seems that it only provides the sync capability and no ETL.

    Do we have a license-free solution to have both the ETL and synchronization capability? Could you give me some hints if possible?

    Thanks,
    Yang Sun

  6. #6
    Join Date
    May 2006
    Posts
    4,882

    Default

    It's either sychronization or ETL... I've not seen both mixed (also not in commercial tools).

    For SQL Server I like e.g. http://www.red-gate.com/products/SQL..._databases.htm
    but it's not open source and there at least 10 more of these kind of synchronization tools out there.

    Regards,
    Sven

  7. #7
    Join Date
    Nov 2007
    Posts
    4

    Thumbs up

    I got your points. You really help me a lot in understanding ETL and Kettle.

    Thank you for your detailed explaination so far.

    Best Regards,
    Yang Sun

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.