Test whether Kettle supports cascade during incremental update-not supported (based on mysql)

Since LZ is like sorting out how to use Kettle to incrementally update a table,

You can also update other tables in the database.

The first method is to use a trigger, that is, to add an SQL script step between the corresponding hop and then perform the update operations on other data tables in step.

Another method is the timestamp. You can add a time point and then determine whether the data has changed by checking whether the time point has changed.

In fact, there is another way to analyze logs. This is a bit difficult for me for the time being, and the database is very targeted. So let's filter it out automatically first ~

 

However,

If you do not set step in kettle, when updating a table

The corresponding fields in the table will be automatically updated based on the changes in the field, deleted and automatically changed to null, and so on.

Let's talk about the result first: In kettle, if the two tables only rely on the foreign key constraint,

The foreign key of the primary table is the primary key of the appendix (that is, the value of the corresponding field in the primary table is a subset of the value set of the corresponding field in the Appendix ),

If data is updated in the attached table,

The fields in the master table will not be updated, but an error will be reported.

 

Therefore, you can only write the trigger by yourself to synchronize the updates of multiple connected tables.

 

The following figure shows the experiment table created by LZ and related data:

First, create a database in the mysql database as the old data source with updates:

    ()   
 (
id    name ()   classId    id_to_class  );

 

 

 

Because there are constraints between the two tables, you should first create the appendix class.

When inserting data, you should first insert data to the table.

Create a database as the new data source,

To facilitate the creation of only one class table,

(Because creating an stu table in the new data source is useless in this experiment, it is not created)

Change related data in the class table.

Use kettle to synchronize two class tables in two data sources,

Check whether the stu field in the old data source changes accordingly.

 

The statement for creating a new data source is as follows:

   ()    class (，,),(,),(,),
(,),(,),(,),
(,),(, );

, 'New class'), (, 'operating system_new ') => corresponds to new
// (, 'Photoshop _ changed '), (, 'economic _ changed') => corresponding to changed
// (2310, 'China'), (2308, 'Physical '), (2511, 'eng'); => deleted
// The rest correspond to identical

 

 

 

 

 

 

 

 

 

 

If there are no foreign key constraints, the corresponding three records marked with red hooks should be retrieved from the old data source.

Table: removed from class.

This is inconsistent with the concept.

However, the merged records correspond to three new records:

New class

Except system new is synchronized successfully.

This is consistent with the concept.

 

Next, you can use snapshots and triggers to synchronize data ~

Although this experiment is really simple,

However, it is still difficult to operate, and there are many methods and ideas that are not mainstream.

I hope I will be more proficient in this technology in the future.

There are still many things to learn. Come on ~