★ FEATURED ARTICLE 简介这份《ETL设计详解数据抽取、清洗与转换》文档面向BI项目中的数据仓库建设者、ETL开发初学者以及需要梳理实施流程的技术人员围绕数据抽取、清洗转换与加载三个环节帮助读者建立从业务系统到ODS、DW的完整设计思路。内容结合数据源调研问题展开涵盖同构与异构数据源抽取、文件类型数据导入、增量更新判断以及不完整数据、错误数据、重复数据的清洗策略并说明不一致数据转换、粒度转换和商务规则计算。文档还讨论ETL工具、SQL及两者结合的实现方式补充日志分类与警告发送机制便于在项目中定位运行问题、完善数据质量校验。资源包共1个docx文件约20KB轻量便于查阅已有1538人学习。适合作为ETL方案设计、面试复盘或数据仓库实施前的参考笔记。1. ETL 到底在干什么把散落的账本汇成一本总账一家公司里销售用一套表格记账仓库用另一套系统记录出入库财务那边还压着一摞手工登记的流水。到了月底老板想看「这个月到底赚了多少」三份数据摆在一起客户名字写法不一样金额单位不一样还有几条明显录错的日期。想让机器自动回答这个问题就得有人把这三摊数据搬到一处洗干净、统一口径再码成能直接算的格式。这套动作就是 ETL全称 Extract、Transform、Load中文叫抽取、转换、加载。它不是某个软件的名字而是一类工作的统称。理解了 ETL你基本就理解了所有报表、看板、经营分析背后的第一道工序。在 BI 项目里ETL 通常要吃掉整个项目三分之一的时间其中清洗和转换又占掉这三分之一里的三分之二。说白了真正难的不是把数据搬过来而是搬过来之后怎么让它「说得通」。2. 数据抽取为什么第一步最难也最容易埋雷抽取听起来最没技术含量——把数据从 A 搬到 B 而已。可真做过项目的人都知道这一步是最需要前期调研的。搬到哪、从哪搬、怎么搬、搬多少这四个问题只要有一个没答清楚后面清洗和加载全是返工。数据抽取的设计不是从写代码开始的是从问问题开始的。2.1 抽取之前先摸清家底四个问题决定方案动手之前调研阶段至少要回答四个问题。第一数据来自几个业务系统一个和五个是完全不同的工作量。第二各个业务系统的数据库服务器上跑的是什么 DBMS第三有没有手工数据手工数据量有多大第四有没有非结构化的数据这四个问题看着像例行公事实际每一个都会直接改变后面的技术选型。先看第一个问题的分量。只有一个业务系统抽取就是一条线五个业务系统意味着五种表结构、五套字段命名习惯、五种更新节奏还要处理它们之间的口径冲突。某公司的销售系统和仓储系统都能查到「出库数量」但一个按发货时间算一个按签收时间算抽过来直接相加就是错的。这类问题在抽取阶段不发现等到报表对不上账再回头查成本翻好几倍。第二个问题决定了通道怎么走。同一种数据库之间通常自带数据库链接功能在数据仓库服务器和业务系统之间建立直接链接写一条查询语句就能访问简单直接。如果源系统和目标系统不是同一种数据库一般可以走通用的数据库连接接口在两边建链接。要是连链接都建不起来还有两条退路一条是用工具把源数据导出成文本文件或表格文件再导入到 ODS另一条是通过程序接口来取数。第三个问题最容易被忽略。手工数据往往藏在某个人的表格里量不大但格式随意、字段缺失、更新没有规律。它的处理方式通常是培训业务人员先把这些数据导入到指定的数据库再从那个库统一抽取。听起来绕了一圈但这一圈换来的是格式可控。第四个问题关乎工具箱里要不要多备几件东西。文本、表格这类文件型数据源可以借助带平面数据源组件的集成工具导入 ODS也可以让业务方先导入中间库再抽。非结构化数据的处理逻辑完全不同需要单独规划。提示调研阶段把「源系统清单」写成一张纸逐条确认 DBMS 类型、数据量级、更新频率、有没有时间戳字段。这张纸后面会反复用到别嫌它土。2.2 同源、异源与文件三条通道怎么选把上面这些问题答完抽取通道基本就浮出水面了。按源系统和目标仓库的关系可以分成三类结构相同的、结构不同的、以及根本不是数据库的文件。三类各有各的走法混着用也很正常。同源的情况最省心。数据库自带链接功能在仓库服务器和业务系统之间建立链接直接写查询语句访问源表。这种做法的好处是实时性由源系统控制不用维护额外的中间文件缺点是查询压力会打到业务库上抽取时间要避开业务高峰。异源的情况要复杂一层。两边数据库类型不同一般通过通用的数据连接接口建立链接比如两种常见商业数据库之间的互连。这条通道能走通就用它走不通再退到文件导出或程序接口。文件导出这条路的本质是「以空间换兼容」——把数据落成文本或表格文件再导入 ODS中间的格式转换由工具负责。程序接口更灵活但要开发适合源系统本身对外提供标准接口的场景。文件型数据源是第三类。文本文件和表格文件没有数据库结构抽取的第一步其实是「让它变成能查的东西」。常见做法是先导入到一个指定的中间库再从中间库抽取进 ODS也可以借助集成工具里的平面数据源组件直接读文件。这两条路的差别在于谁来承担格式校验前者由数据库的导入工具做后者由 ETL 工具做。通道类型典型场景优点注意点数据库直接链接源库与仓库同类型无需中间文件链路短查询压力会打到业务库通用连接接口源库与仓库不同类型兼容性好配置简单需要驱动和权限配合文件导出再导入无法建链接、量不大绕开网络与权限限制多一道落盘格式要校验程序接口源系统对外提供标准接口灵活可按需取字段需要开发与维护工具平面数据源组件文本、表格等文件免去手工导入步骤依赖工具能力与文件规范2.3 增量抽取时间戳这把尺子怎么量数据量一大全量抽取就行不通了。每次把几千万行搬一遍不仅慢还会把仓库压得喘不过气。这时候必须考虑增量抽取——只取「上次之后发生变化」的那部分数据。最常用也最朴素的办法是时间戳。业务系统一般会记录业务发生的时间把它当作增量标志。每次抽取之前先去 ODS 里查一下已经落库记录的最大时间然后带着这个时间去业务系统取所有大于该时间的记录。逻辑上就是「接着上次的尾巴往后接」。-- 第一步查出 ODS 中该表已抽取到的最大业务时间 SELECT MAX(biz_time) AS last_time FROM ods.order_detail; -- 第二步带着这个时间边界去源库取增量 -- :last_time 是上一步查出来的值由调度程序传入 SELECT order_id, customer_id, amount, biz_time FROM source.order_detail WHERE biz_time :last_time AND biz_time SYSDATE; -- 上界防止抽到未来时间的脏数据这段逻辑的关键参数有两个。last_time是水位线它决定了这一次从哪里开始。上界条件看起来多余实际很有用源系统里偶尔会出现时间写成未来日期的脏记录不加限制的话这些记录会被反复抽取把水位线顶到一个不合理的位置后面的增量就全乱了。这就是那种不写不知道、踩过一次就忘不掉的坑。时间戳方案有个前提业务系统得有时间戳字段。现实是有的系统没有有的只有部分表有。没有时间戳的情况下可以退而求其次用自增主键、更新序号或者干脆按分区做全量覆盖。还有一类变化时间戳捕捉不到——物理删除。源系统把一条记录删了增量抽取是看不见的仓库里那条旧记录会一直留着。这个问题的解法要么靠源系统提供删除标记要么定期做一次全量比对具体选哪种取决于业务对数据一致性的要求。增量抽取真正的难点不在 SQL在于「水位线放在哪」。放在 ODS 表里、放在独立的控制表里、还是放在调度系统的变量里决定了故障恢复时能不能准确续跑。放在业务表里最省事但一旦这张表被重跑覆盖水位线也跟着丢了那就只能从头再来。稳妥的做法是单独维护一张抽取控制表记录每张源表上次成功抽取的时间和状态。3. 数据清洗与转换最耗时间的那 2/3 到底在做什么ETL 三部分里抽取和加载本质上是搬运转换才是判断。搬运可以靠工具提速判断只能靠人把业务规则一条条想清楚。这也是为什么清洗转换阶段的工作量能占到整个 ETL 的三分之二——它不是技术问题是「什么算对、什么算错」的定义问题。数据仓库一般分成 ODS 和 DW 两层清洗和转换也大致按这个层次分工从业务系统到 ODS 做清洗把脏数据和不完整数据挡在外面从 ODS 到 DW 做转换完成业务规则的计算和聚合。这个分工不是死规定但它符合一个直觉——先把原料挑干净再下锅炒。3.1 脏数据的三副面孔不完整、错误、重复不符合要求的数据翻来覆去就是三类。认清这三类清洗方案基本就有了骨架。第一类是不完整的数据。特征是「该有的信息缺了」比如供应商名称缺失、分公司名称缺失、客户的区域信息缺失或者业务系统里主表和明细表匹配不上。这类数据的问题不在格式在内容机器没法自动补只能把缺失的记录按缺失内容分类导出交给业务部门在规定时间内补全补全之后才写入数据仓库。第二类是错误的数据。根源通常是业务系统不够健全输入的时候没做校验就直接写进了数据库。典型表现包括数值被输成全角数字字符、字符串后面多了一个回车、日期格式不正确、日期超出合理范围。这一类里还要再分两小类。全角字符、首尾不可见字符这种只能写 SQL 把记录挑出来让业务方在源系统改完再抽。日期格式错误或者日期越界则更严重它们会直接导致 ETL 运行失败必须提前用 SQL 捞出来交给业务主管部门限期修正。第三类是重复的数据维表里尤其常见。客户表里同一个客户被录了两遍一遍带区域信息一遍不带。处理方式是把重复记录的所有字段都导出来让业务方确认哪条为准、哪些可以合并。这里不能图省事按主键去重因为重复记录往往各有各的完整之处贸然留一条会丢掉另一条上的有效信息。-- 找出字符串末尾带回车或换行的记录 -- CHR(13) 是回车CHR(10) 是换行不同数据库函数名略有差异 SELECT customer_id, customer_name FROM ods.customer WHERE customer_name LIKE % || CHR(13) || % OR customer_name LIKE % || CHR(10) || %; -- 找出日期越界的记录业务合理区间之外的都算异常 SELECT order_id, order_date FROM ods.order_detail WHERE order_date DATE 2000-01-01 OR order_date SYSDATE 1;这两段查询的思路很直接把「人眼很难发现、但会导致后续计算出错」的模式翻译成数据库能执行的字符串和日期条件。参数部分需要按实际业务调整比如日期下界不一定是 2000 年要看这家企业的系统是哪一年上线的上界加一天是为了容忍时区或批处理带来的微小偏差。判断标准最好和业务方一起定不要自己拍脑袋。3.2 清洗不是删掉了事过滤、回传、确认的闭环很多初学者的直觉是脏数据嘛过滤掉就完了。真做过一轮就知道过滤只是动作闭环才是关键。过滤掉的数据去哪了、谁知道了、什么时候修、修完怎么重新进来这四个问题不解决脏数据会一直在源系统里躺着每次抽取都要重新过滤一遍。标准做法是把过滤结果交给业务主管部门确认。确认的结果无非两种确实该过滤或者由业务单位修正之后再抽取。对于过滤掉的数据可以写入表格文件也可以写入一张专门的过滤数据表。在 ETL 开发初期比较有效的手段是每天向业务单位发送过滤数据的清单促使他们尽快修正错误同时这些清单本身也是将来验证数据质量的依据。这里有一条血泪经验每一条过滤规则都要认真验证并且要用户确认。规则写宽了脏数据漏进仓库后面报表出错很难溯源规则写严了把有用数据误杀业务方发现数字对不上回头查是一场灾难。清洗规则本质上是和数据提供方的一份约定约定就得双方签字。注意清洗是一个反复的过程不可能几天内完成。一开始就要做好「发现问题、补充规则、再验证」的循环准备别指望一次性把规则写全。3.3 转换的三件事口径统一、粒度聚合、指标计算清洗解决的是「数据对不对」转换解决的是「数据能不能用」。转换主要干三件事。第一件是不一致数据的转换本质是整合。不同业务系统对同一个实体有不同的编码方式同一个供应商在结算系统里编号是某一串字符在客户关系系统里是另一串。抽取过来之后必须统一成一个编码否则做供应商维度分析时会被拆成两个主体。这类映射有的能靠规则自动完成有的必须维护一张对照表由业务方定期维护。第二件是数据粒度的转换。业务系统存的是明细每一笔订单、每一次点击都留着数据仓库是拿来分析的不需要那么细。所以要把明细按仓库需要的粒度做聚合比如按天、按客户、按区域汇总。粒度的选择要看分析场景既要能支撑日常报表又不能聚合得太狠导致下钻不下去。第三件是业务规则的计算。不同企业有不同的业务规则和指标定义有些指标不是简单的加减乘除能算出来的。比如毛利要考虑退货、折扣、分摊运费某个转化率要排除内部测试账号。这些指标应当在 ETL 里算好并存进数据仓库供分析直接使用。放在 ETL 里算的好处是口径统一所有报表用的是同一份计算结果代价是规则一变就要改 ETL所以指标定义最好文档化改的时候有据可查。三件事里最容易出问题的是第三件。因为前两件是技术活第三件是业务活而业务规则往往散落在各个人的脑子里。把规则问清楚、写成文档、让业务方确认这一步花的时间通常比写代码多得多。4. 加载、日志与告警结果怎么进仓库翻车了靠什么复盘数据洗干净、转换完就该进仓库了。加载这一步技术难度不高但它和日志、告警绑在一起构成了整个 ETL 的安全网。跑批跑成功没成功、哪一步慢、错了多少行全靠这套东西记录。没有它出问题时你面对的就是一个不透明的系统只能靠猜。4.1 从 ODS 到 DW加载为什么发生在清洗之后加载的一般做法是数据清洗完成之后直接写入数据仓库。这个顺序不是随意定的它背后是一条清晰的数据流——业务系统到 ODS 做清洗ODS 到 DW 做转换和加载。清洗在前保证进入仓库的数据是合格的加载在后保证仓库里不留中间态。批量写入时要注意两件事。一是失败回滚一批数据写到一半出错要么整体回滚重来要么有断点续传的机制不能留下半批数据在表里。二是重复写入的幂等性同一天的重跑不能把数据翻倍。常见的做法是先删后插、按分区覆盖或者用唯一键做冲突处理。加载频率也要和业务节奏对齐。日批适合大多数经营分析场景小时批适合对时效敏感的监控类需求。频率越高对源系统的压力和调度的复杂度越大不是所有场景都值得往上加。4.2 工具、SQL 与混合三条实现路线的取舍ETL 的实现方式常见有三种各有各的适用面。第一种是借助 ETL 工具。商业工具通常提供图形化的作业编排界面拖拽组件就能把抽取、转换、加载串起来。它的优势是快——快速建立 ETL 工程屏蔽掉复杂的编码任务提高开发速度降低技术门槛。代价是灵活性欠缺遇到工具不支持的复杂逻辑绕起来很别扭。第二种是纯 SQL 实现。所有逻辑都写成 SQL 脚本由调度系统按顺序执行。优点是灵活能贴着数据库的能力做优化运行效率往往更高缺点是编码复杂对开发人员的技术要求高脚本多了以后维护成本直线上升。第三种是工具和 SQL 结合。整体流程用工具编排复杂的转换逻辑下沉到 SQL 里执行。这样既保留了工具在调度、监控、依赖管理上的便利又保留了 SQL 在复杂计算上的灵活性开发速度和运行效率都能兼顾。路线上手难度灵活性维护成本适合的团队纯工具低一般中等开发人员少、流程标准纯 SQL高高高有数据库功底、逻辑复杂工具加 SQL中等较高中等大多数实际项目选哪条路线取决于团队的技术结构和业务的复杂程度没有绝对优劣。工具不是银弹SQL 也不是。真正影响成败的是流程设计得清不清楚而不是用了哪个组件。4.3 三类日志加一路告警黑匣子怎么留日志的目的只有一个随时能知道 ETL 跑得怎么样出错出在哪。日志一般分三类。第一类是执行过程日志记录每一步的执行情况——每次运行每一步骤的起始时间、影响了多少行数据形式上是流水账。它的价值在于定位性能瓶颈一眼就能看出哪一步特别慢。第二类是错误日志某个模块出错时写入记录出错时间、出错模块、出错信息。这是排查问题的第一手材料字段要设计得足够细最好带上出错的SQL或者关键参数。第三类是总体日志只记录 ETL 的开始时间、结束时间和是否成功。它面向的是「今天跑没跑完」这个最粗的问题适合做成看板。三类日志各有分工缺一类就会在某个场景下抓瞎。只有总体日志出了问题不知道错在哪只有执行过程日志翻起来太累。光有日志还不够还得出错时主动通知。常见做法是给系统管理员发邮件附上出错信息方便第一时间排查。告警的关键是别滥用——每次小波动都发邮件管理员很快就会把它当成垃圾信息忽略掉。分级告警比较务实致命错误立即发可重试的失败合并成一条日报。提示日志表要定期清理或归档。跑了一年的过程日志能把磁盘吃满这件事在项目上线半年后才会暴露属于典型的「早该想到」。5. 易混概念与常见误区五组最容易搅在一起的说法ETL 这套东西概念不多但几个词长得像、意思近初学者经常混着用。混着用的后果不是考试扣分是设计的时候选错方案。5.1 易混点一ETL 和 ELT 是一回事吗为什么混三个字母完全一样只是顺序换了位置看起来像同一件事的两种叫法。怎么区分关键看转换发生在哪一侧。ETL 是先抽取、在独立的处理层完成转换、再加载进目标库ELT 是先抽取、直接加载进目标库、在目标库内部完成转换。前者适合目标库算力有限、或者转换逻辑需要独立管理的场景后者依赖目标库本身的计算能力数据量大、转换逻辑相对简单时更省事。判断标准很简单转换这步的代码跑在仓库外面还是里面。5.2 易混点二ODS 和 DW 是不是重复建设为什么混两层都存数据看起来像把同一份东西存了两遍浪费空间。怎么区分定位完全不同。ODS 贴近源系统保留细节、保留原始形态作用是缓冲和清洗的落点数据留存时间通常较短。DW 面向分析按主题组织、经过转换和聚合服务的是报表和指标。把 ODS 当成 DW 用会拖慢分析查询把 DW 当成 ODS 用会丢掉追溯明细的能力。两层各有各的职责不是重复。5.3 易混点三增量抽取等于「只抽新增的那几条」为什么混「增量」这个词天然让人联想到「新增」好像只处理新插入的记录就够了。怎么区分增量抽取处理的是「变化」包括新增、修改理想情况下还包括删除。按时间戳抽取能覆盖新增和修改但删掉的记录是看不见的。如果业务对删除敏感就要额外设计删除标记或者定期全量比对。只盯着新增很容易在数据对账时发现总数对不上而且是那种查半天查不出原因的玄学问题。5.4 易混点四清洗就是把不合格的数据删掉为什么混「清洗」听起来像打扫卫生把脏的扫出去就完事了。怎么区分过滤只是手段之一。完整的数据清洗流程包括识别、分类、回传、确认、修正、重新抽取这一串动作。很多不合格的数据不是删掉就完了而是要回到源系统修正再重新流进来。删掉只是把问题藏起来源头的错误还在下次抽取照样出现。5.5 易混点五上了工具就不用写 SQL为什么混商业工具的卖点之一就是图形化、低代码很容易让人以为所有事情都能拖拽完成。怎么区分工具擅长的是流程编排、依赖管理、调度监控这些确实是它比自己写脚本强的地方。但复杂的业务规则计算、性能调优、特殊的数据探查仍然要落到 SQL 上。实际项目里最常见的形态是混合方案——工具管流程SQL 管逻辑。指望完全不写 SQL最后往往是在工具里用更别扭的方式把 SQL 重写一遍。6. 什么时候该上 ETL什么时候别硬上不是所有数据需求都值得搭一套 ETL。判断的标准不复杂看三件事源头的数量、口径的统一程度、以及这个问题会不会反复被问。源系统只有一个数据结构稳定查询需求也就是「把昨天的订单拉出来看看」那直接连库查就行中间加一层 ETL 纯属给自己找活。反过来数据散在好几个系统里每个系统对同一个指标的理解还不一样每次做报表都要人工对数那这套流程迟早要建早建比晚建省事。口径统一程度是第二个判断点。如果各部门对「活跃客户」「有效订单」的定义还没谈拢这时候上 ETL 是把分歧固化进代码改一次要命一次。比较务实的顺序是先把指标定义谈清楚再动手写转换逻辑。技术上跑得通不等于业务上说得清这个顺序反了后面全是返工。第三个判断点是复用性。同一个口径的报表一个月做一次做完就扔手工拉数完全够用。如果是每天都要看、好几个部门都在看那就值得把流程固化下来。场景建议理由单源、临时看数直接查询加一层只增加维护成本多源、口径冲突先定口径再建否则规则反复改高频、多部门复用建 ETL固化流程省人力指标定义未达成共识暂缓代码改不起对时效要求到秒级慎用批量 ETL批处理天生有延迟举个具体的例子。某公司的销售报表最初是手工做的每周从两个系统各导一份表格人工对客户编码再拼到一起。做了一年做表的人离职接手的人对着一堆表格完全看不懂对应关系。后来重建流程时第一步就是把客户编码映射关系落成一张对照表第二步才是写抽取和转换。整个项目里写代码的时间不到一半其余全花在梳理规则和确认口径上。这个过程很枯燥但它决定了后面三年这张报表能不能稳定跑下去。也有反过来的教训。一个需求当时看着很急源数据就一个表我图快直接写了个定时脚本跑转换没做日志、没做告警、也没留过滤记录。三个月后源系统改了一个字段类型脚本静默失败报表空白了一周才有人发现。后来排查的时候没有任何记录可查只能从头复现。从那以后我每接一个数据项目都先把「源系统清单、指标口径、谁能修数据」三张纸写出来再加上日志和告警这两件小事才允许自己动手写第一行转换逻辑。这套流程看着笨但它能让你在半年后接到「这个数字怎么和上个月对不上」的电话时十分钟内给出答案而不是打开一堆脚本从头猜。希望帮到你。本文还有配套的精品资源点击获取
阅读完成 · 觉得有帮助？
