Test whether Kettle supports cascade or not during incremental update (based on mysql_MySQL

Test whether Kettle supports cascade during incremental update-mysql-based bitsCN.com

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:

create database test7_26;use test7_26;create table class( classId  int not null, className  varchar(50) not null, primary key(classId) );

create table stu
(
id  int not null,
name varchar(50) not null,
primary key (id),
classId int not null,
key  classId (classId),
constraint id_to_class foreign key(classId)
references  class(classId)
);

Insert into class values (2310, 'China'), (2308, 'Physical '), (2511, 'eng'), (2191, 'pe'), (2383, 'chemical'), (2276, 'Music '),

(1961, 'Photoshop/target = _ blank class = infotextkey> photoshop '), (2792, 'flash'), (1632, 'computer'), (3216, 'History ');

Insert into stu values (100, 'inuyasha', 2310), (102, 'koyun ', 2308), (209, 'kuagome', 2511), (90, 'natsume', 2191), (98, 'kikiyou', 2383), (133, 'tegomass', 2276 );

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:

create test7_26_new;use test7_26_new;create table class( classId  int not null, className  varchar(50) not null, primary key(classId) ); insert class values(12402，'new class'),(2191,'PE'),(2383,'chemical'),(2276,'economic_changed'),
(1961,'photoshop/ target=_blank class=infotextkey>photoshop_changed'),(2792,'flash'),(1632,'computer'),
(3216,'history'),(480604, 'operating system_new');

// (12402, 'New class'), (480604, 'operating system_new ') => corresponding to new
// (1961, 'Photoshop/target = _ blank class = infotextkey> photoshop_changed '), (2276, 'Economy _ changed') => corresponds to changed
// (2310, 'China'), (2308, 'Physical '), (2511, 'eng'); => deleted
// The rest correspond to identical

The corresponding records of new, changed, and deleted do not change the stu table in the old data source after the old data source is synchronized,

This is not the focus of this experiment.

This experiment focuses on testing the related fields in the class table of the deleted old data source,

Related to this (100, 'inuyasha', 2310), (102, 'koyun ', 2308), (209, 'kagome', 2511 ),

How will the classId field of the three records in the stu table of the three old data sources be changed,

Whether related cascade automatic update and change operations will be performed.

Based on the previous article, first create a new conversion in Kettle:

Overall process:

Next, create two database connections,

Corresponding to test7_26; test7_26_new;

(Figure)

Drag and drop two table inputs,

Corresponding to test7_26.class and test7_26_new.class respectively

Test7_26_new.class:

(I wrote it with a pencil as a drawing tool, which is worse than writing it with my feet. hahahaha,

Cg corresponds to the changed fields after the record is merged)

Next we will sort the two memories

(You can only sort the classId,

Because sorting is to improve the efficiency of merging records,

The subsequent merging records are only for the classId

Compare the old and new data sources,

Therefore, you can only compare the classId values)

And then merge the records and view the results;

The results are consistent with the assumptions.

Then add the data synchronization step.

The following describes the corresponding field settings for the step:

If errors are not considered, you can add a log processing step after the synchronization data,

You can forcibly update the class of the data table of the old data source,

Because cascade is connected to the class and stu, you can insert and update the corresponding fields in the class,

The corresponding deleted field does not execute the corresponding delete record operation from the class of the old data source.

Therefore, the corresponding field in stu is not changed:

After the overall process is designed and executed, use the mysql console to display the database test7_26. in the old data source,

Update of fields corresponding to the data table stu:

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 ~

BitsCN.com