2023学年完整公开课版数据的加载

数据的加载学习目标2知识目标●了解ETL数据加载机制●什么是ETL数据增量加载●数据加载的几种方式能力目标●了解大几种方式●总结与分析数据加载学习任务3了解ETL数据加载机制初步探索ETL数据增量加载机制初步探索数据加载的几种方式总结与分析数据加载机制总结与分析数据增量机制4目录ETL数据加载机制概述5ETL是数据抽取（Extract）、转换（Transform）、加载（Load）的简写，它的功能是从数据源抽取出所需的数据，经过数据清洗和转换，最终按照预先定义好的数据仓库模型，将数据加载到数据仓库中去，是构建数据仓库最重要的一步。在数据加载到数据库的过程中，分为全量加载（更新）和增量加载（更新）。全量加载：全表删除后再进行数据加载的方式。增量加载：目标表仅更新源表变化的数据。ETL数据增量加载机制6该方式通过分析数据库自身的日志来判断变化的数据。关系型数据库系统都会将所有的DML操作存储在日志文件中，以实现数据库的备份和还原功能。ETL增量抽取进程通过对数据库的日志进行分析，提取对相关源表在特定时间后发生的DML操作信息，就可以得知自上次抽取时刻以来该表的数据变化情况，从而指导增量抽取动作。系统日志分析方式7触发器增量抽取主要有2种方式：（1）直接进行数据加载直接进行数据加载方式是创建一个与源表结构类似的临时表，然后创建一个三种类型的触发器，分别对应insert,update,delete操作。每当源表有数据变动的时候，利用触发器将变化的数据填入此临时表表中。最后通过维护这个临时表，在进行ETL过程的时候，将目标表中相应的数据进行修改。ETL过程结束后，清空此临时表。ETL数据增量加载机制8（2）利用增量日志表进行增量加载利用增量日志表进行增量加载则是不直接抽取源表数据，仅仅是将操作内容写入一张增量日志表里（同时增量日志表中抽取过的数据要及时被标记或删除）。增量日志表一般不存储增量数据的所有字段信息，而只是存储源表名称、更新的关键字值和更新操作类型(insert、update或delete)，ETL增量抽取进程首先根据源表名称和更新的关键字值，从源表中提取对应的完整记录，再根据更新操作类型，对目标表进行相应的处理。ETL数据增量加载机制9实现原理是指增量抽取时，抽取进程通过比较系统时间或者源表上次抽取时的最大时间戳与抽取源表的时间戳字段的值来决定抽取哪些数据。这种方式需要在源表上增加一个时间戳字段，系统中更新修改表数据的时候，同时修改时间戳字段的值。采用时间戳进行增量更新时需要源表有相应的时间戳字段，所以对于没有时间戳的源表需要进行相应业务需要改造，增加必要的时间戳字段。时间戳字段的添加可以通过方式：系统自动添加系统时间。有的数据库(例如DB2)的时间戳支持自动更新，即表的其它字段的数据发生改变时，时间戳字段的值会被自动更新为记录改变的时刻。ETL数据增量加载机制时间戳方式10全表比对即在增量抽取时，ETL进程逐条比较源表和目标表的记录，将新增和修改的记录读取出来。优化之后的全部比对方式是采用MD5校验码，需要事先为要抽取的表建立一个结构类似的临时表，该临时表记录源表的主键值以及根据源表所有字段的数据计算出来的MD5校验码，每次进行数据抽取时，对源表和MD5临时表进行MD5校验码的比对，如有不同，进行UPDATE操作：如目标表没有存在该主键值，表示该记录还没有，则进行INSERT操作。然后，还需要对在源表中已不存在而目标表仍保留的主键值，执行DELETE操作。ETL数据增量加载机制全表比对方式11全表比对的DataStageJob示例-比对表生成：ETL数据增量加载机制全表比对方式12全表比对的DataStageJob示例-比对及后续处理图ETL数据增量加载机制全表比对方式13全表比对的DataStageJob示例-比对及后续处理图总结与分析各类增量抽取方式比较表14总结与分析系统日志分析系统日志分析触发器时间戳全表比对对目标表新增数据可可可可对目标表更新数据可可可可对目标表删除数据可可无法捕获可目标表数据量小小大适中目标表类型所有除视图以外均可所有所有源表数量11多多源表