一句话回答
ETL 是 extract、transform、load 的缩写:把数据从源系统复制出来,清洗并重新整理,再把结果加载到数据仓库之类的目的地。ETL 管道是反复做这件事的一组自动化任务。
公式: 抽取行数 = 加载行数 + 拒绝行数 + 重复行数
什么是 ETL?
ETL 是 extract(抽取)、transform(转换)、load(加载)三个英文词的缩写:从源系统里把数据复制出来,清洗并重新整理,再把结果加载到目的地,目的地最常见的是数据仓库。转换发生在数据落地之前,在一个暂存区里完成,所以目的地收到的永远是已经符合其结构的数据。
ETL 是做数据集成的一种方式,它的转换这一步就是数据准备发生的地方。ETL 管道(pipeline)是自动化、有先后顺序的一组任务,反复做这件事。与它相对的 ELT 先把原始数据加载进去,再在目的地内部做转换。本页只讲这个术语的定义;两者的对比和一个算例在 ETL 与 ELT 的区别里。
三个步骤
| 步骤 | 做什么 | 典型规则 | 这里会出什么问题 |
|---|---|---|---|
| 抽取 | 把原始数据从源(数据库、CRM 和 ERP 系统、API、文件、网页)复制到暂存区 | 先拉全量,之后只拉上次运行以来变化的部分 | 源系统很慢或宕机;某一列被改名 |
| 转换 | 暂存区里的数据被清洗并整理成目标的格式 | 去重,丢弃或修正无效行,转换单位、币种和日期格式,对受监管的字段脱敏,关联和聚合 | 某条规则悄悄丢掉了行;时区搞混 |
| 加载 | 结果进入目标 | 先加载一份全量,再加载增量变化,偶尔做一次全量刷新 | 加载到一半失败;重跑时同样的行被加载两次 |
AWS 和 IBM 描述的是同样的三步。共同的核心是:数据在通过转换这一步之前,什么都不会进入目的地。
ETL 管道和数据管道
这两个词常常被当成一回事。Informatica 从三点把它们分开:
| ETL 管道 | 数据管道 | |
|---|---|---|
| 范围 | 数据管道的子集;数据加载完就结束 | 范围更大;加载之后还可以去触发别的系统 |
| 转换 | 一定有转换这一步 | 在系统之间搬数据,转换与否都可以 |
| 时效 | 通常按批运行 | 可以实时运行 |
一个把表从一个数据库原样复制到另一个数据库的任务,是数据管道,但不是 ETL 管道。
批处理和流式 ETL
传统 ETL 面向批处理:数据在固定的时间被收集、转换、加载,通常放在比较空闲的「批处理窗口」里。流式 ETL 在事件到达时连续转换,通常建在 Apache Kafka 这类流处理平台上,延迟很低。两者都是 ETL,区别在于目的地里的数据允许有多旧。
示例数据:一次批处理要跑 40 分钟。最坏的情况是,一个事件恰好发生在一次抽取刚结束之后,然后要等满一整个周期,再加上运行时间。
| 调度 | 新事件进入仓库时最坏的年龄 |
|---|---|
| 每天 02:00 | 24 小时 + 40 分钟 = 24 小时 40 分钟 |
| 每小时一次,每次跑 10 分钟 | 1 小时 + 10 分钟 = 1 小时 10 分钟 |
按数据要回答的问题来选调度。每周的财务报表不需要每小时跑一次;要把数据延迟控制在几分钟,就需要,或者需要流式的设计。
算例:核对一次加载
示例数据:一个每晚运行的任务,从商店数据库里抽取 10,000 行订单。
| 转换的结果 | 行数 |
|---|---|
| 拒绝:缺少订单号 | 150 |
| 丢弃:订单号重复 | 220 |
| 拒绝:日期无法解析 | 60 |
| 已加载 | 10,000 − 150 − 220 − 60 = 9,570 |
核对用一个简单的恒等式:抽取数 = 加载数 + 拒绝数 + 重复数,这里是 10,000 = 9,570 + 430。被拒绝和被丢弃的行占 430 ÷ 10,000 = 4.3%。如果明天的运行显示 15%,哪怕任务报告成功,源系统多半也已经变了。
现在把同一个文件重跑一遍。如果加载只是追加,表里就有 9,570 × 2 = 19,140 行。如果加载按订单号写入(upsert),表里仍然是 9,570 行。能放心重跑的管道,就是能标识每一行的管道,任务执行到一半失败时,这是最有用的一个特性。
ETL 管道为什么会出问题
| 原因 | 症状 | 检查方法 |
|---|---|---|
| 源系统改了结构(某一列改名或类型变了) | 某个字段出现空值,或转换这一步失败 | 每次抽取都对比列名和类型 |
| 数据迟到 | 昨天的合计今天变了 | 重新抽取一个滚动的时间窗口,比如最近几天,而不是只抽「上次运行以来」的 |
| 重跑时又追加了同样的行 | 计数和合计翻倍 | 按唯一键加载;对比行数 |
| 加载到一半失败 | 有的表有了新一天的数据,有的没有 | 先加载到新表再切换,或让加载可以从中断处重新开始 |
| 时区混用 | 每天的合计在午夜前后挪动 | 在转换这一步统一成一个时区,并在各处保持一致 |
用 ETL 还是 ELT?
最简单的规则看的是历史。如果以后可能要按新规则重算过去的结果,就保留原始数据,这是 ELT 一侧。如果数据必须先清洗、或先去掉个人字段,才允许进入目的地,就先转换,这是 ETL。多数真实的管道是两者混用。完整的论述,以及一个指标定义变更的例子,在 ETL 与 ELT 的区别里。
常见误区
- 把「任务成功了」当成「数据是对的」。 每次加载都要核对行数。
- 让转换规则悄悄丢行。 每个被拒绝的行都要计数,并记录原因。
- 做出一个不能重跑的管道。 每次加载都应该可以安全地重复。
- 把所有的数据搬运都叫 ETL。 没有转换的纯复制是数据管道,比较工具时这个区别很重要。
- 把批处理的调度当成实时。 由每日任务供数的看板,数据最多可能是 24 小时前的。
ETL 与 TapCub
TapCub 不是 ETL 工具。它不会从你的数据库里抽取数据,也不会替你运行转换任务。它位于你管道的两端:既是可以把数据加载进去的地方,也是可以把原始数据取出来的地方。假设你的订单历史在一个旧系统里,你想把它和现在的统计放在一起。
- 抽取和转换自己做。 把旧系统里的行导出成 UTF-8 的 CSV,先像上面的例子那样修正格式、去掉重复。
- 在导入器里加载。 打开 全端分析 › 数据管理 › 数据导入。它接受 CSV 事件、CSV 用户属性和 Nginx 或 Apache 日志,每个文件最多 100,000 行;更大的文件要拆开。向导会把你的列映射到 TapCub 字段,导入时还会做一点小转换:它能读 10 位或 13 位的时间戳以及 ISO 和常见的日期格式,把映射为属性的列里的数字和
true、false转成对应的类型;在用户属性文件里,邮箱和手机号会先做哈希,再绑定为身份。 - 核对结果。 任务列表对每个文件显示行数、已导入和失败,也就是算例里的那个恒等式。对日志来说,静态资源和爬虫这类被跳过的行也算失败,所以数字高不一定是出错。
- 不要把同一个文件加载两次。 导入器不会去重:同一个文件导入两次,就得到两份数据。
- 持续加载要从你的服务器发事件。
/api/v1/track接口每个请求最多 1,000 个事件,并按你给每个事件的insert_id去重,所以重发一批是安全的。导入用于一次性的历史数据,不是持续的通道。 - 要把原始数据取出来, 在 全端分析 › 数据管理 › 数据导出 里创建一个异步任务。它导出的是原始行(事件、会话、人),不是报表的截图,超过保留期的行已经被删掉了。
另见:什么是数据处理?步骤、类型和算例。
资料来源
- AWS,What is ETL?
- IBM,What is ETL (extract, transform, load)?
- Informatica,What is an ETL pipeline?
这个解释清楚吗?
我们会根据反馈修改和补充这篇内容。
提交没有成功,请稍后再试。
谢谢你的反馈,我们会据此改进这篇内容。你已经反馈过这篇了,谢谢。
