Kettle中通过触发器方式实现数据增量更新

野性酷女 2022-05-27 00:13 374阅读 0赞

[Kettle中通过触发器方式实现数据 增量更新][Kettle_]

在使用Kettle进行数据同步的时候， 共有

1.使用时间戳进行数据增量更新

2.使用数据库日志进行数据增量更新

3.使用触发器+快照表 进行数据增量更新

今天要介绍的是第3中方法。

实验的思路是这样的，在进行数据同步的时候，

源数据表为A表， A表要对 目标表(target table) B 表和C表进行数据的同步更新。

即A表中的对应字段发生变化之后，

会通过触发器将对应变化的字段在A表中的主键值写入到一个临时表temp中(该表作为快照表使用)。

快照表中只有两个字段，一个是temp\_id,是快照表的主键，另一个是 A\_id,记录的是在A表中发生变化的字段对应的主键的值。

temp( temp\_id int primary key auto\_increment , A\_id int );

接下来，通过对快照表temp进行扫描，把在B表和C表中出现的与temp表中与A\_id相匹配的字段，

从B，C两个目标表中进行移除。

在接下来，让A表作为源 让B和C作为目标 对B，C做插入/更新操作;

这样就实现了A表对B，C两个表的更新，在后续的操作中可以使用SQL语句将 temp表， 以及触发器进行 drop操作，以免其浪费内存资源。

在创建 trigger的时候，只要针对A表的 删除、 更新操作进行创建trigger 即可。

下面来分析一下，对A表进行的不同操作的情况：

1.如果是向A表中插入一行数据： 没有对应的触发器，新插入A表的recorder 并没有被记录到temp 快照表中，

所以不会对B，C表中对应的字段进行移除操作。(不对 A 表的Insert 操作创建 trigger

可以避免 如果对 A表进行插入的 主键值 在 B， C表中 根本找不到，

这样的话无法 根据temp 中记录的 主键值 对B，C表中的字段进行移除 )

即便不建立触发器，后续操作中仍旧会因为有 插入/更新的这个 step，对B 表 和 C表进行插入操作的。

2.如果是向A表删除一行数据的话， 对应创建的触发器会将 A表中被删除的 主键 字段写入到 temp这个快照表中去，

接下来，会依照temp表中的字段对B 表和 C 表中的记录进行移除操作。

而在后续的插入更新操作中，A表中已经将该记录进行移除了，所以没有对应的记录对B，C两张表进行插入操作。

3. 如果对A表中的某一条记录 进行 Update 更新操作的话 ，对应Update进行创建的触发器会 把A表中被更新的记录

所对应的主键值 写入到快照表 temp中去，

接下来会根据temp表中的主键值，对表B 表 C 中的字段进行移除，

然后对B表 C表 依照 A表中的字段进行 插入更新操作。

插入更新操作是这样的，

如果是主键值相等的话， 目标表对应字段 与 源表对应字段 不相同，

就会对目标表字段进行依照 源表进行更新，

如果是某条记录的 主键值 在源表中出现，但是在目标表中没有出现的话，

则对目标表进行相应的插入操作。(依照源表)

========================================================================

接下来，简单介绍一下实验数据：

首先，在Mysql中创建3个表, A B C 表，

对应的字段都是一样的，对应的数据也都是一样的（为了简便......）。

![29154421-55130c4e19294e0e8cd7388e956f525e.png][]

接下来，在Kettle的Spoon中创建下面的流程：

**Main**

主流程中需要注意的是，在整个流程中，前段的prepare 和 后续 finish 只需要执行一次，

中间的syn是需要重复循环的，至少LZ是这样设计的，这样可以使得在循环的这段时期中，

若是A表中的相关字段发生变化的话，会实时地将变化同步到 B C两个表中。

![29155114-1261ca04e856449eac2fdca071af4259.png][]

**Main.prepare**

在创建触发器的时候需要注意一下，LZ在实验的时候，

发现如果触发器若想触发 多条操作的时候，

是要写

" DELIMITER + 分隔符号 "

begin

commands

end

分隔符号

但是，kettle目前的版本是不支持 delimiter的， 也就是写上之后会报错，

不写上，若是执行多条命令语句 后面是必须要加 分号 ";" 来结尾的，

不加分号，也会报错，

若加上之后，触发器会默认为 到分号 触发内容就会结束， 也是出错的，

LZ的这个步骤之所以没有出错是因为，LZ创建的 触发器只有一条触发语句，所以没有用到 delimiter。

如果您想写多条 触发语句的话，在delimiter这里要注意一下吧~

![29155336-5d833baba07a445f8a1535381952b19f.png][]

** Main.syn**

![29160448-e4f4b92eeb484e36abe3f6383134be77.png][]

**Main.syn.delete\_B\_C\_records**

![29161835-808d6d11eb71464096341283c64ef1f0.png][]

**Main.syn.A\_insert\_BandC**

![29162232-e72e355b82994c36b98c00344825d794.png][]

这里需要注意的是，在A\_insert\_into\_C这个步骤的时候，对应的目标表中要选择为c表。

而且，使用触发器+快照表 进行数据增量更新 主要是通过 ：先根据源表中变化(update delete)的记录 对应删除目标表中的字段，

然后再，将A表中变化的字段插入到目标表中； 所以整个过程不存在 使用源表 对 目标表进行更新 操作。所以"不执行任何更新" 要勾选 .

**Main.finish**

![29162831-77c9e0d0de4f4a96ae9d41300f646374.png][]

收尾工作，主要回收资源，调用Sql语句，执行删除 快照表、触发器工作。

接下来我们向A表中，

1.insert 一条记录，

2.delete一条 同时存在于 A B C表中的记录，

3.update 一条A表中的记录。

4.然后对 A表中的 同一条 记录 先 update 再 delete 它 看结果如何

5.(元数据) A表中还有上一次实验被存入的，但是B C两张表中所没有的 记录：

原A：

![29164429-fad06429066c4118b88861e3ec7ab58a.png][]

原B：

![29164510-50c7de8829734aef904eaeb53a7918f5.png][]

原C：

![29164540-88da48a6f0f146cc9d07c153777bc902.png][]

after prepare:

![29164953-1e25636ff2b24f17a945d58c27500f88.png][]

![29165054-3dfaea939e2d49bd9cecc78994ad60bc.png][]

modify A ：

![29171100-d3f5a4739f5948a1a844421869bf47ef.png][]

执行上述的 1 2 3 4 操作后：

A：

![29164429-fad06429066c4118b88861e3ec7ab58a.png][]                   ![29171143-80855c22a5874373ae9e752e3ac82366.png][]

snapshot table temp :

![29171234-1c75b193fcd345d4986267083d2c2279.png][]

对A表的 id=8 进行一次update 一次 delete，

这个temp表需要对A\_id进行去除重复记录操作，这也是为什么要在syn.delete\_B\_C\_records中

去除重复记录的原因。

为了将整个流程分解，我们来调用Main.syn.delete\_B\_C\_record 这个transformation:

之后显示的

![29171941-f4dc63ccd942434681b1d013c8de9f66.png][]

![29171952-dcd2f2547d7544269f0f06135070ed92.png][]

接下来，使用A表对B，C两表进行 插入/更新步骤 即 Main.syn.A\_insert\_BandC

B：![29172153-c515dd5d9d634a46b15986cea1b669b1.png][]    C：![29172213-e3e0aa9def3a4fbc9c1670d8f36010db.png][]

这样的话，不仅实现了更新，删除，同步操作，而且同样把A表中的原来的字段同步到B C两张表中去了。

如果，不希望将A表中原有数据同步到B，C表中的话，可以通过字段选择来实现。

当然在具体业务中的情况要比实验内容复杂得多，这里仅提供简单的思路，

如果有不恰当之处，敬请指正。

\------------能改变现状的，只有战斗的觉悟------------

[Kettle_]: http://www.cnblogs.com/inuyasha1027/p/kettle_synchronized_trigger.html
[29154421-55130c4e19294e0e8cd7388e956f525e.png]: /images/20220527/1d771442e7224ae28273848dfa954bc4.png
[29155114-1261ca04e856449eac2fdca071af4259.png]: /images/20220527/0ef2dc39a3114be48bf0b56c17283576.png
[29155336-5d833baba07a445f8a1535381952b19f.png]: /images/20220527/94769080d4d64d229590a9b0a27d7eef.png
[29160448-e4f4b92eeb484e36abe3f6383134be77.png]: /images/20220527/cac3556d08d94e56bf2b5607bbd340a3.png
[29161835-808d6d11eb71464096341283c64ef1f0.png]: /images/20220527/22cb45e4809d4af4bba727a780bd7778.png
[29162232-e72e355b82994c36b98c00344825d794.png]: /images/20220527/dd9e082871684bf684808c7c8d17116b.png
[29162831-77c9e0d0de4f4a96ae9d41300f646374.png]: /images/20220527/06e0c0e771e14045bf498dc317840bea.png
[29164429-fad06429066c4118b88861e3ec7ab58a.png]: /images/20220527/a1188752452d47f486964a7650e8a50e.png
[29164510-50c7de8829734aef904eaeb53a7918f5.png]: /images/20220527/f0a4a4d45de34ea587ce1d9c89757a40.png
[29164540-88da48a6f0f146cc9d07c153777bc902.png]: /images/20220527/a9f8737f15234b3cbc97ef213cba819b.png
[29164953-1e25636ff2b24f17a945d58c27500f88.png]: /images/20220527/7d874a4687e54d46b4b21fe5e7b532cd.png
[29165054-3dfaea939e2d49bd9cecc78994ad60bc.png]: /images/20220527/c7b57b2e539a43019d4ab0d916c7722f.png
[29171100-d3f5a4739f5948a1a844421869bf47ef.png]: /images/20220527/47036b1ac10a48deacc39198ca1437ce.png
[29171143-80855c22a5874373ae9e752e3ac82366.png]: /images/20220527/eea0e95fda09477ca104030fe14ce41f.png
[29171234-1c75b193fcd345d4986267083d2c2279.png]: /images/20220527/b0eb85618bd84e8998eff1d3cdd17110.png
[29171941-f4dc63ccd942434681b1d013c8de9f66.png]: /images/20220527/56623361a69b417ebe5c04af6ede6a2a.png
[29171952-dcd2f2547d7544269f0f06135070ed92.png]: /images/20220527/ce4e0b0cfbc04c6d89fce7bcf7b8281a.png
[29172153-c515dd5d9d634a46b15986cea1b669b1.png]: https://images0.cnblogs.com/blog/493782/201310/29172153-c515dd5d9d634a46b15986cea1b669b1.png
[29172213-e3e0aa9def3a4fbc9c1670d8f36010db.png]: /images/20220527/3e5cac13158a47309da74ccf4a9dd7d9.png