Search code examples
mysqlpentaho-spoonpentaho-data-integrationpdispoon

Delete records from MySQL source database table ,after copying to MySQL target database using Pentaho spoon

I would like to do the below operation using Pentaho spoon/PDI.

  1. Copy data from the Mysql source database to the Mysql target database. 2)After that I would like to truncate/Delete Data from Source Database

Can Someone help with this?

Solution

  • Just run two transformations and one job:

    1. First transformation: Table input step to read table data from source database and Table output step to write said data to table in target database
    2. Second transformation: Truncate table in Source database. I don't remember if there's a specific step for this or if you could use a job step instead of a transformation running a SQL script to truncate the table. Just check what you have available in Pentaho, I don't have it opened at the moment to verify.
    3. Job: After successfully running First transformation run the second transformation or the step.

    Now, you can complicate matters from this depending on your situation:

    • Do you need to replicate a whole database instead of just one table? You'll need to add steps to work with Metadata Injection, so you run the first a second transformations for all the tables in the source database. You can read the dictionary tables in the source database to inject the information about table names and columns to the transformations so you don't create one pair of transformations for each table.
    • Do you need to create the tables in the target database before populating them? You'll need to add additional transformations to read the metadata of the table to create and build the script to create said table in the target database before populating it.

    And so on