文章目录一. ETL概念二. 数据抽取1逻辑抽取2物理抽取3变化数据捕获三. 数据转换四. 数据装载一. ETL概念ETL一词它是Extract、Transform、Load三个英文单词首字母的简写中文意为抽取、转换、装载。ETL是建立数据仓库最重要的处理过程也是最体现工作量的环节一般会占到整个数据仓库项目工作量的一半以上。ETL的工作● 抽取从操作型数据源获取数据。● 转换转换数据使之转变为适用于查询和分析的形式和结构。● 装载将转换后的数据导入到最终的目标数据仓库。ETL将异构统一为同构建立一个数据仓库就是要把来自于多个异构的源系统的数据集成在一起放置于一个集中的位置用于数据分析。ETL系统的工作就是要把异构的数据转换成同构的。如果没有ETL不可能对异构的数据进行程序化的分析。二. 数据抽取抽取操作从源系统获取数据给后续的数据仓库环境使用。抽取数据难度大源系统很可能非常复杂并且缺少相应的文档因此只是决定需要抽取哪些数据可能就已经非常困难了。如果已经明确了需要抽取的数据下一步就该考虑从源系统抽取数据的方法了。抽取原则一般情况下不可能因为需要提升数据抽取的性能而在源系统中添加额外的逻辑也不能增加这些源系统的工作负载。有时用户甚至都不允许增加任何“开箱即用”的外部应用系统这叫做对源系统具有侵入性。下面分别从逻辑和物理两方面介绍数据抽取方法。1逻辑抽取逻辑抽取有两种逻辑抽取类型全量抽取和增量抽取1全量抽取源系统的数据全部被抽取。因为这种抽取类型影响源系统上当前所有有效的数据所以不需要跟踪自上次成功抽取以来的数据变化。源系统只需要原样提供现有的数据而不需要附加的逻辑信息比如时间戳等。一个全表导出的数据文件或者一个查询源表所有数据的SQL语句都是全量抽取的例子。2只抽取某个事件发生的特定时间点之后的数据。必须能够标识出特定时间点之后所有的数据变化。这些发生变化的数据可以由:源系统自身来提供例如能够反映数据最后发生变化的时间戳列一个原始事务处理之外的只用于跟踪数据变化的变更日志表。但意味着需要在源系统上增加抽取逻辑。待优化的问题在许多数据仓库中抽取过程不含任何变化数据捕获技术。取而代之的是把源系统中的整个表抽取到数据仓库过渡区然后用这个表的数据和上次从源系统抽取得到的表数据作比对从而找出发生变化的数据。虽然这种方法不会对源系统造成很大的影响但显然需要考虑给数据仓库处理增加的负担尤其是当数据量很大的时候。使用cdc解决这个问题ing2物理抽取两种物理数据抽取机制直接从源系统联机抽取或者间接从一个脱机结构抽取数据。1联机抽取数据直接从源系统抽取直连源系统数据库访问它们的数据表或者连接到一个存储快照日志或变更记录表的中间层系统。注意这个中间层系统并不需要必须和源系统物理分离。binlog2脱机抽取数据不从源系统直接抽取而是从一个源系统以外的过渡区抽取过渡区可能已经存在例如数据库备份文件、关系数据库系统的重做日志、归档日志等或者抽取程序自己建立。该考虑以下的存储结构数据库备份文件。一般需要数据还原操作才能使用。备用数据库。如Oracle的DataGuard和MySQL的数据复制等技术。平面文件。数据定义成普通格式关于源对象的附加信息列名、数据类型等需要另外处理。导出文件。关系数据库大都自带数据导出功能如Oracle的exp/expdp程序和MySQL的mysql dump程序都可以用于生成导出数据文件。重做日志和归档日志。每种数据库系统都有自己的日志格式和解析工具。3变化数据捕获抽取处理需要重点考虑增量抽取也被称为变化数据捕获简称CDC。假设一个数据仓库系统在每天夜里的业务低峰时间从操作型源系统抽取数据那么增量抽取只需要过去24小时内发生变化的数据。变化数据捕获也是建立准实时数据仓库的关键技术。当你能够识别并获得最近发生变化的数据时抽取及其后面的转换、装载操作显然都会变得更高效因为要处理的数据量会小很多。遗憾的是很多源系统很难识别出最近变化的数据或者必须侵入源系统才能做到。变化数据捕获是数据抽取中典型的技术挑战。flink cdc。常用的变化数据捕获方法有时间戳、快照、触发器和日志四种:时间戳方法需要源系统有相应的数据列表示最后的数据变化。快照方法可以使用数据库系统自带的机制实现如Oracle的物化视图技术也可以自己实现相关逻辑但会比较复杂。触发器是关系数据库系统具有的特性源表上建立的触发器会在对该表执行insert、update、delete等语句时被触发触发器中的逻辑用于捕获数据的变化。日志可以使用应用日志或系统日志这种方式对源系统不具有侵入性但需要额外的日志解析工作。三. 数据转换数据转换一个最重要的功能是清洗数据目的是只有“合规”的数据才能进入目标数据仓库。例如一个系统的字符集在另一个系统中可能是无效的。另一方面由于某些业务和技术的需要也需要进行多种数据转换例如下面的情况只装载特定的数据列。例如某列为空的数据不装载。统一数据编码。例如性别字段有些系统使用的是1和0有些是‘M’和‘F’有些是‘男’和‘女’统一成‘M’和‘F’。自由值编码。例如将‘Male’改成‘M’。预计算。例如产品单价 * 购买数量 金额。基于某些规则重新排序以提高查询性能。合并多个数据源的数据并去重。预聚合。例如汇总销售数据。行列转置。将一列转为多列。例如某列存储的数据是以逗号作为分隔符的字符串将其分割成多列的单个值。合并重复列。预连接。例如查询多个关联表的数据。数据验证。针对验证的结果采取不同的处理通过验证的数据交给装载步骤验证失败的数据或直接丢弃或记录下来做进一步检查。四. 数据装载ETL的最后步骤是把转换后的数据装载进目标数据仓库。这步操作需要重点考虑两个问题数据装载的效率问题一旦装载过程中途失败了如何再次重复执行装载过程。即使经过了转换、过滤和清洗去掉了部分噪声数据但需要装载的数据量还是很大的。执行一次数据装载可能需要几个小时的时间同时需要占用大量的系统资源。先看装载效率要提高装载的效率加快装载速度可以从以下几方面入手。首先保证足够的系统资源。数据仓库存储的都是海量数据所以要配置高性能的服务器并且要独占资源不要与别的系统共用。在进行数据装载时输出源要禁用数据库约束唯一性、非空性检查约束等和索引当装载过程完全结束后再启用这些约束重建索引这种方法会很大的提高装载速度。在数据仓库环境中一般不使用数据库来保证数据的参考完整性即不使用数据库的外键约束它应该由ETL工具或程序来维护。接着看可重复装载实现可重复装载的关键是要记录下失败点并在装载程序中处理相关的逻辑。还有一种情况就是装载成功后数据又发生了改变这时需要重新再执行一遍装载过程已经正确装载的数据可以被覆盖但相同数据不能重复新增。简单的实现方式是先删除再插入或者用replace into、merge into等类似功能的操作。装载到数据仓库里的数据经过汇总、聚合等处理后交付给多维立方体或数据可视化、仪表盘等报表工具、BI工具做进一步的数据分析。参考《Hadoop构建数据仓库实践》 
长河君 · 2026/11/3 18:17:26 · 阅读 1.2k 【数据仓库基础（三）】抽取-转换-装载
文章目录一. ETL概念二. 数据抽取1逻辑抽取2物理抽取3变化数据捕获三. 数据转换四. 数据装载一. ETL概念ETL一词它是Extract、Transform、Load三个英文单词首字母的简写中文意为抽取、转换、装载。ETL是建立数据仓库最重要的处理过程也是最体现工作量的环节一般会占到整个数据仓库项目工作量的一半以上。ETL的工作● 抽取从操作型数据源获取数据。● 转换转换数据使之转变为适用于查询和分析的形式和结构。● 装载将转换后的数据导入到最终的目标数据仓库。ETL将异构统一为同构建立一个数据仓库就是要把来自于多个异构的源系统的数据集成在一起放置于一个集中的位置用于数据分析。ETL系统的工作就是要把异构的数据转换成同构的。如果没有ETL不可能对异构的数据进行程序化的分析。二. 数据抽取抽取操作从源系统获取数据给后续的数据仓库环境使用。抽取数据难度大源系统很可能非常复杂并且缺少相应的文档因此只是决定需要抽取哪些数据可能就已经非常困难了。如果已经明确了需要抽取的数据下一步就该考虑从源系统抽取数据的方法了。抽取原则一般情况下不可能因为需要提升数据抽取的性能而在源系统中添加额外的逻辑也不能增加这些源系统的工作负载。有时用户甚至都不允许增加任何“开箱即用”的外部应用系统这叫做对源系统具有侵入性。下面分别从逻辑和物理两方面介绍数据抽取方法。1逻辑抽取逻辑抽取有两种逻辑抽取类型全量抽取和增量抽取1全量抽取源系统的数据全部被抽取。因为这种抽取类型影响源系统上当前所有有效的数据所以不需要跟踪自上次成功抽取以来的数据变化。源系统只需要原样提供现有的数据而不需要附加的逻辑信息比如时间戳等。一个全表导出的数据文件或者一个查询源表所有数据的SQL语句都是全量抽取的例子。2只抽取某个事件发生的特定时间点之后的数据。必须能够标识出特定时间点之后所有的数据变化。这些发生变化的数据可以由:源系统自身来提供例如能够反映数据最后发生变化的时间戳列一个原始事务处理之外的只用于跟踪数据变化的变更日志表。但意味着需要在源系统上增加抽取逻辑。待优化的问题在许多数据仓库中抽取过程不含任何变化数据捕获技术。取而代之的是把源系统中的整个表抽取到数据仓库过渡区然后用这个表的数据和上次从源系统抽取得到的表数据作比对从而找出发生变化的数据。虽然这种方法不会对源系统造成很大的影响但显然需要考虑给数据仓库处理增加的负担尤其是当数据量很大的时候。使用cdc解决这个问题ing2物理抽取两种物理数据抽取机制直接从源系统联机抽取或者间接从一个脱机结构抽取数据。1联机抽取数据直接从源系统抽取直连源系统数据库访问它们的数据表或者连接到一个存储快照日志或变更记录表的中间层系统。注意这个中间层系统并不需要必须和源系统物理分离。binlog2脱机抽取数据不从源系统直接抽取而是从一个源系统以外的过渡区抽取过渡区可能已经存在例如数据库备份文件、关系数据库系统的重做日志、归档日志等或者抽取程序自己建立。该考虑以下的存储结构数据库备份文件。一般需要数据还原操作才能使用。备用数据库。如Oracle的DataGuard和MySQL的数据复制等技术。平面文件。数据定义成普通格式关于源对象的附加信息列名、数据类型等需要另外处理。导出文件。关系数据库大都自带数据导出功能如Oracle的exp/expdp程序和MySQL的mysql dump程序都可以用于生成导出数据文件。重做日志和归档日志。每种数据库系统都有自己的日志格式和解析工具。3变化数据捕获抽取处理需要重点考虑增量抽取也被称为变化数据捕获简称CDC。假设一个数据仓库系统在每天夜里的业务低峰时间从操作型源系统抽取数据那么增量抽取只需要过去24小时内发生变化的数据。变化数据捕获也是建立准实时数据仓库的关键技术。当你能够识别并获得最近发生变化的数据时抽取及其后面的转换、装载操作显然都会变得更高效因为要处理的数据量会小很多。遗憾的是很多源系统很难识别出最近变化的数据或者必须侵入源系统才能做到。变化数据捕获是数据抽取中典型的技术挑战。flink cdc。常用的变化数据捕获方法有时间戳、快照、触发器和日志四种:时间戳方法需要源系统有相应的数据列表示最后的数据变化。快照方法可以使用数据库系统自带的机制实现如Oracle的物化视图技术也可以自己实现相关逻辑但会比较复杂。触发器是关系数据库系统具有的特性源表上建立的触发器会在对该表执行insert、update、delete等语句时被触发触发器中的逻辑用于捕获数据的变化。日志可以使用应用日志或系统日志这种方式对源系统不具有侵入性但需要额外的日志解析工作。三. 数据转换数据转换一个最重要的功能是清洗数据目的是只有“合规”的数据才能进入目标数据仓库。例如一个系统的字符集在另一个系统中可能是无效的。另一方面由于某些业务和技术的需要也需要进行多种数据转换例如下面的情况只装载特定的数据列。例如某列为空的数据不装载。统一数据编码。例如性别字段有些系统使用的是1和0有些是‘M’和‘F’有些是‘男’和‘女’统一成‘M’和‘F’。自由值编码。例如将‘Male’改成‘M’。预计算。例如产品单价 * 购买数量 金额。基于某些规则重新排序以提高查询性能。合并多个数据源的数据并去重。预聚合。例如汇总销售数据。行列转置。将一列转为多列。例如某列存储的数据是以逗号作为分隔符的字符串将其分割成多列的单个值。合并重复列。预连接。例如查询多个关联表的数据。数据验证。针对验证的结果采取不同的处理通过验证的数据交给装载步骤验证失败的数据或直接丢弃或记录下来做进一步检查。四. 数据装载ETL的最后步骤是把转换后的数据装载进目标数据仓库。这步操作需要重点考虑两个问题数据装载的效率问题一旦装载过程中途失败了如何再次重复执行装载过程。即使经过了转换、过滤和清洗去掉了部分噪声数据但需要装载的数据量还是很大的。执行一次数据装载可能需要几个小时的时间同时需要占用大量的系统资源。先看装载效率要提高装载的效率加快装载速度可以从以下几方面入手。首先保证足够的系统资源。数据仓库存储的都是海量数据所以要配置高性能的服务器并且要独占资源不要与别的系统共用。在进行数据装载时输出源要禁用数据库约束唯一性、非空性检查约束等和索引当装载过程完全结束后再启用这些约束重建索引这种方法会很大的提高装载速度。在数据仓库环境中一般不使用数据库来保证数据的参考完整性即不使用数据库的外键约束它应该由ETL工具或程序来维护。接着看可重复装载实现可重复装载的关键是要记录下失败点并在装载程序中处理相关的逻辑。还有一种情况就是装载成功后数据又发生了改变这时需要重新再执行一遍装载过程已经正确装载的数据可以被覆盖但相同数据不能重复新增。简单的实现方式是先删除再插入或者用replace into、merge into等类似功能的操作。装载到数据仓库里的数据经过汇总、聚合等处理后交付给多维立方体或数据可视化、仪表盘等报表工具、BI工具做进一步的数据分析。参考《Hadoop构建数据仓库实践》