数据全量增量抽取
发布时间 2023-07-13 18:32:05作者: 萌哥-爱学习
在这里插入图片描述
在数据仓库中要实现增量抽取，关键是如何准确快速的捕获变化的数据。
增量抽取机制能够将业务系统中的变化数据按一定的频率准确地捕获到，同时不对业务系统造成太大的压力，也不影响现有业务。
相对全量抽取，增量抽取的设计更为复杂。
思维导图
在这里插入图片描述
增量抽取的特点与策略
1. 增量抽取的特点
只抽取发生变化的数据。
相对于全量抽取更为快捷，处理量更少。
采用增量抽取需要在与数据装载时的更新策略相对应。
2.增量抽取的策略
时间戳：扫描数据记录的更改时间戳，比较时间戳来确定被更新的数据。
增量文件：扫描应用程序在更改数据时所记录的数据变化增量文件，增量文件是指数据所发生的变化的文件。
日志文件：目的是实现恢复机制，其中记载了各种操作的影响。
修改应用程序代码：以产生时间戳、增量文件、日志等信息，或直接推送更新内容， 达到增量更新目标数据的目的。
快照比较：在每次抽取前首先对数据源快照，并将该快照与上次抽取时建立的快照相互比较，以确定对数据源所做的更改，并逐表、逐记录进行比较，抽取相应更改内容。
在数据抽取中，根据转移方式的不同，可以将数据转移分两个阶段，即初始化转移阶段和增量转移阶段。初始化转移阶段采用全量抽取的方式，增量转移阶段按照上述的增量抽取方式进行有选择的抽取。
基于触发器的增量抽取方式
当数据源存于数据库时，可在数据库管理系统中设置触发器来侦听数据源的增删改事件以监控数据的增量变化，并进一步采取措施将增量变化反映到目标数据中。
其具体方法如下。
使用配套工具直接捕获数据变化事件并实时刷新日标数据。
与时间戳法或增量文件法结合，在数据变化事件处理逻辑中，设置时间戳或产生增量记录。
在捕获到数据变化时，将增量数据追加到临时表中。
在这里插入图片描述
触发器方式是普遍采取的一种增量抽取机制。
该方式是根据抽取要求，在要被抽取的源表上建立插人、修改、删除三个触发器，每当源表中的数据发生变化，就被相应的触发器将变化的数据写人一个增量日志表，ETL 的增量抽取从增量日志表中而不是直接在源表中抽取数据， 同时增量日志表中抽取过的数据要及时做标记或者删除。
为了简单起见，增量日志表一般不存储增量数据的所有字段信息，而只是存储源表名称、更新的关键字值和更新操作类型（插人、修改或删除），ETL增量抽取进程首先根据源表名称和更新的关键字值，从源表中提取对应的完整记录，再根据更新操作类型，对日标表进行相应的处理。
基于时间戳的增量抽取方式
1.时间戳方式
时间戳方式是一种基于快照比较的变化数据捕获方式，在原表上增加一个时间戳字段，当系统中更新修改表数据的时，同时修改时间戳字段的值。
当进行数据抽取时，通过比较上次抽取时间与时间戳字段的值来决定抽取数据。
优缺点
时间戳方式的优点是性能优异，系统设计清晰，数据抽取相对简单，可以实现数据的递增加载。
时间戳方式的缺点是需要由业务系统来完成时间戳的维护，对业务系统需要加人额外的时间戳字段，特别是对不支持时间戳的自动更新的数据库，还要求业务系统进行额外的更新时间戳操作；另外，无法捕获对时间戳以前数据的删除和刷新操作，在数据准确性上受到了一定的限制。
2.基于时间戳的数据转移
时间戳方式抽取数据需要在源表上增加一个时间戳字段，当系统中更新修改表数据的时， 同时修改时问戳字段的值。
有的数据库（例如 SQL Server）的时间戳支持自动更新，即表的其他字段的数据发生改变时，时间戳字段的值也会被自动更新为记录改变的时刻。
这时进行 ETL 实施时只需要在源表加上时间戳字段就可以了。
对于不支特时间戳自动更新的数据库，要求业务系统在更新业务数据时，通过编程的方式手工更新时间戳字段。
使用时间戳方式可以正常捕获源表的插人和更新操作，但对于删除操作则无能为力，需要结合其他机制才能完成。
基于时间戳的数据转移如下图所示。
在这里插入图片描述
全表删除插入方式
全表删除插人方式是指每次抽取前先删除目标表数据，抽取时全新加载数据。
该方式实际上将增量抽取等同于全量抽取。
当数据量不大，全量抽取的时间代价小于执行增量抽取的算法和条件代价时，可以采用该方式。
全表删除插人方式的优点是加载规则简单，速度快，缺点是对于维表加外键不适应，当业务系统产生删除数据操作时，综合数据库将不会记录到所删除的历史数据，不可以实现数据的递增加载，同时对于目标表所建立的关联关系，需要重新进行创建。
全表比对抽取方式
全表比对抽取方式是指在增量抽取时，逐条比较源表和目标表的记录，将新增和修改的记录读取出来。
优化之后的全部比对方式是采用 MDS 校验码，需要事先为要抽取的表建立一个结构类似的 MDS 临时表，该临时表记录源表的主键值以及根据源表所有字段的数据计算出来的 MD5 校验码，每次进行数据抽取时，对源表和 MDS 临时表进行 MD5 校验码的比对，如果不同，则进行刷新操作。
如目标表没有存在该主键值，表示该记录还没有被抽取，则进行插人操作。然后，还需要对在源表中已不存在而目标表仍保留的主键值执行删除操作。下载文件之后，如果需要知道下载的这个文件与网站的原始文件是否相同，就需要给下载的文件进行 MDS 校验。
如果得到的 MDS 值和网站公布的相同，可确认下载的文件完整；如有不同，说明下载的文件不完整，其原因可能是在网络下载的过程中出现错误，或此文件已被别人修改。为防止他人更改该文件时放人病毒，不应使用不完整文件。
当用 E-mail 给好友发送文件时，可以将要发送文件的MD5值告诉对方，这样好友收到该文件以后即可对其进行校验，来确定文件是否安全。
在刚安装好系统后可以给系统文件做个MD5校验，过了一段时间后如果怀疑某些文件被人换掉，那么就可以给那些被怀疑的文件做个 MDS 校验，如果与从前得到的 MD5 校验码不相同，那么就可以肯定出现了问题。
典型的全表比对的方式是采用 MD5 校验码。
数据抽取事先为要抽取的表建立一个结构类似的 MDS 临时表，该临时表记录源表主键以及根据所有字段的数据计算出来的MDS校验码。
每次进行数据抽取时，对源表和 MD5 临时表进行 MD5 校验码的比对，从而决定源表中的数据是新增、修改还是删除，同时更新MDS校验码。
优缺点
MD5方式的优点是对源系统的倾人性较小(仅需要建立一个 MD5 临时表），缺点也是显而易见的，与触发器和时间戳方式中的主动通知不同， MD5 方式是被动地进行全表数据的比对，性能较差。当表中没有主键或唯一列且含有重复记录时，MD5 方式的谁确性较差。
日志表方式
对于建立了业务系统的生产数据库，可以在数据库中创建业务日志表，当特定需要监控的业务数据发生变化时，由相应的业务系统程序模块来更新维护日志表内容。增量抽取时，通过读日志表数据决定加载哪些数据及如何加载。日志表的维护需要由业务系统程序用代码来完成。
优缺点
在业务系统中添加系统日志表，当业务数据发生变化时，更新维护日志表内容，当加载时， 通过读日志表数据决定加载哪些数据及如何加载。
其优点是不需要修改业务系统表结构，源数据抽取清楚，速度较快，可以实现数据的递增加载；
其缺点是日志表维护需要由业务系统完成， 需要对业务系统业务操作程序作修改，记录日志信息。日志表维护较为麻烦，对原有系统有较大影响，且工作量较大，改动较大，有一定风险。
系统日志分析方式
系统日志分析方式通过分析数据库自身的日志来判断变化的数据。
关系型数据库系统都会将所有的 DML 操作存储在日志文件中，以实现数据库的备份和还原功能。
ETL 增量抽取进程通过对数据库的日志进行分析，提取对相关源表在特定时间后发生的 DML 操作信息，可以得知自上次抽取时刻以来该表的数据变化情况，从而指导增量抽取动作。
有些数据库系统提供了访向日志的专用的程序包，例如 Oracle 的 LogMiner，使数据库日志的分析工作更为简化。
各种数据抽取机制的比较与分析
在进行增量抽取操作时，存在多种可以选择的数据抽取机制。
从兼容性、完备性、性能和侵人性等方面对这些机制进行比较与分析，以合理选择数据抽取方式。
在这里插入图片描述
1.兼容性
数据抽取面对的源系统并不一定都是关系型数据库系统。某个 ETL 过程需要从若千年前的遗留系统中抽取数据的情形经常发生。这时所有基于关系型数据库产品的增量机制都无法工作，时间戳方式和全表比对方式可能有一定的利用价值。在这种情况下，只有放弃增量抽取的思路，转而采用全表删除插人方式。
2.完备性
在完备性方面，时间戳方式不能捕获删除操作，需要结合其他方式一起使用。
3.性能
增量抽取的性能因素表现在两方面：
一方面是抽取进程本身的性能，另一方面是对源系统性能的负面影响。
触发器方式、日志表方式以及系统日志分析方式由于不需要在抽取过程中执行比对步骤，所以增量抽取的性能较佳。
全表比对方式需要经过复杂的比对过程才能识别出更改的记录，抽取性能最差。
在对源系统的性能影响方面，触发器方式是直接在源系统业务表上建立触发器，同时写临时表，对于频繁操作的业务系统可能会有一定的性能损失，尤其是当业务表上执行批量操作时，行级触发器将会对性能产生严重的影响，同步CDC 方式内部采用触发器的方式实现，也同样存在性能影响的问题；
全表比对方式和日志表方式对数据源系统数据库的性能没有任何影响，只是它们需要业务系统进行额外的运算和数据库操作，会有少许的时间损耗，时间戳方式、系统日志分析方式以及基于系统日志分析的方式(异步CDC和闪同查询） 对数据库性能的影响也是非常小的。
4. 侵入性
对数据源系统的侵人性是指业务系统是否要为实现增量抽取机制做功能修改和额外操作， 在这一点上，时间戳方式值得特别关注。
该方式除了要修改数据源系统表结构外，对于不支持时间戳字段自动更新地关系型数据库产品，还必须要修改业务系统的功能，让它在源表执行每次操作时都要显式地更新表的时间戳字段，这在 ETL 实施过程中必须得到数据源系统高度的配合才能达到，并且在多数情况下这种要求在数据源系统看来比较过分，这也是时间戳方式无法得到广泛运用的主要原因。
另外，触发器方式需要在源表上建立触发器，这种在某些场合中也遭到拒绝。
还有一些需要建立临时表的方式，例如全表比对和日志表方式，可能因为开放给FTL 进程的数据库权限的限制而无法实施。
同样的情况也可能发生在基于系统日志分析的方式上，因为大多数的数据库产品只元许特定组的用户甚至只有DBA 才能执行日志分析。闪回查询在侵人性方面的影响是最小的。
总结
各种数据增量抽取机制性能比较如下表所示。
增量机制 兼容性 完备性 抽取性能 对源系统性能影响 对源系统侵入性 实现难度
触发器方式 关系数据库 高 优 大 一般 较容易
时间戳方式 关系型数据库，具有“字段” 结构的其他数据格式 低 较优 很小 大 较容易
全表删除插人方式 任何数据格式 高 极差 无 无 容易
全表比对方式 关系型数据库、文本格式 高 差 小 一般 一般
日志表方式 关系型数据库 高 优 小 较大 较容易
系统日志分析方式 关系型数据库 高 优 很小 较大 难
通过上表可以看出，没有哪一种机制具有绝对的优势，不同机制在各种因素下的表现大体上都是相对平衡的。
兼容性较差的机制，如闪回查询机制，由于充分利用了数据源系统DBMS 的特性，相对来说具有较好的整体优势；
最容易实现以及兼容性最佳的全表删除插人机制，则是以牺牲抽取性能为代价的；
系统日志分析方式对源业务系统的功能无须作任何改变，对源系统表也无须建立触发器，而抽取性能也不错，但有可能需要源系统开放 DBA 权限给ETL 抽取进程，并且自行分析日志系统难度较高，不同数据库系统的日志格式不一致，这就在一定程度上限制了它的使用范围。
所以，ETL 实施过程中究竞选择哪种增量抽取机制，需要根据实际的数据源系统环境进行决策，需要综合考虑源系统数据库的类型、抽取的数据量（决定对性能要求的苛刻程度）、对源业务系统和数据库的控制能力以及实现难度等各种因素，甚至结合各种不同的增量机制以针对环境不同的数据源系统进行 ETL 实施。
————————————————
版权声明：本文为CSDN博主「Shockang」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/Shockang/article/details/130989027