数据工程设计模式——ETL和ELT引言（Introduction）本章将探讨 ETL（Extract, Transfo

引言（Introduction）

本章将探讨 ETL（Extract, Transform, Load：抽取-转换-加载） 与 ELT（Extract, Load, Transform：抽取-加载-转换） 两种数据模式的运行方式，使读者熟悉它们各自能够解决的用例。我们还将基于开源与云技术，设计同时采用 ETL 与 ELT 的系统，并通过示例应用与代码片段进行演示。此外，本章也会介绍 ETL 与 ELT 模式在实际中的应用案例。

结构（Structure）

本章将涵盖以下主题：

ETL 与 ELT 模式的用例
采用 ETL 与 ELT 的系统设计
ETL 与 ELT 系统的技术选型
真实案例

目标（Objectives）

在本章结束时，读者将深入理解 ETL 与 ELT 数据工程模式；你还将能够基于这些模式设计并构建数据管道，并编写代码实现相应设计。你将了解用于构建 ETL/ELT 管道的技术栈。最后，你还将理解这些模式在银行业等真实场景中的应用。

ETL 与 ELT 模式的用例（Use cases for ETL and ELT patterns）

在批处理模式之后，ETL 可能是第二常见的数据工程模式。几乎所有传统的数据仓库系统都是基于该模式：从源事务系统抽取数据、进行转换、再加载入数据仓库。更现代的数据仓库也常采用 ELT：在初始摄取后，利用数据库自身的能力完成数据转换。尤其在实时系统中，因难以在实时路径上进行复杂转换，往往更依赖 ELT。

数据仓库中的 ETL（ETL in data warehousing）

自上世纪 80 年代末能处理海量数据的数据库出现以来，数据仓库便应运而生。数据仓库并非直接从应用写入，而是从上游事务数据库获取数据。为此，需要一个抽取流程，从事务系统抓取所需数据（既包括一次性回填，也包括持续增量）。这构成 ETL 中的 E（Extract） 。

数据从源系统抽出后，很少会原样加载到数据仓库中；通常需要多种丰富化与整形，这就是 T（Transform） 。例如，在入仓前，数据需要反规范化并建模为 星型（Star） 或 雪花（Snowflake）模式：星型以中心事实表连接多维表；雪花是在星型基础上将维表进一步规范化为子表。同时还需经过数据清洗，以便标准化用于报表（如地址规范化、隐私信息去除、数据质量提升等）。

完成转换后，将数据加载至数据仓库的过程即 L（Load） 。加载既包括用于历史回填（back population）的批量装载，也包括用于前向填充（forward population） 的新变化“细流式”加载。根据回填或前向的不同，所用加载技术也会不同。

注意：ETL 是更高层的模式，内部通常组合批处理或微批等基础模式。
例如，ETL 几乎总是采用批/微批：转换计算开销大，难以“边到边转”；将记录成批后再统一转换更高效。类似地，ETL 的加载也几乎总是以批/微批方式完成。

下图展示了使用 Apache Spark 在从事务数据库到数据仓库过程中执行转换的 ETL（图 7.1）：

图 7.1：基于 Apache Spark 的 ETL（ETL with Apache Spark）

点击流分析中的 ELT（ELT in clickstream analysis）

来自移动应用与网站的点击流分析对理解消费者行为至关重要：由此识别用户的搜索与购买模式，并将结果输入推荐引擎以制定合适的广告与促销方案。任何相对成功的应用或网站都会产生海量且实时的点击流数据，必须即时捕获。

原始点击流需要处理与存储才能发挥价值。例如，要计算某网页的流失率，需跨时间聚合大量用户的点击流。但若在实时链路上完成这些聚合会很困难，且在入库前先聚合会拖慢摄取速率。ELT 可以解决该问题：数据先入库，再在数据库内进行转换/聚合。现代 MPP 数据库引擎非常擅长在库内完成此类转换。

在工程上，可用 Apache Kafka 与 Kafka Connect 将应用产生的原始点击流抽取并加载至数据仓库。为保证摄取速率跟上数据生成速率，流数据以原样方式进入仓库，不在链路上做复杂转换；在 Kafka 中进行实时转换代价昂贵，尽管 SMT（Single Message Transforms） 支持轻量级的单条消息转换（如大小写转换、类型转换、四舍五入等），但并不适合聚合等复杂或高计算量的任务。

下图展示了 ELT 模式：数据先加载进仓库，随后在仓库内完成转换（图 7.2）：

图 7.2：ELT 模式（ELT pattern）

设计 ETL 与 ELT 系统（Designing ETL and ELT system）

本节我们将设计并构建一个结合 Lambda 架构与 ETL/ELT 模式的系统：使用 ETL 支持历史数据回填（back population） ，用 ELT 支持前向增量（forward population） 。该系统通过融合历史与实时数据来完成分析。演示中以 MySQL 为源系统、ClickHouse 为目标系统；在 ETL 流程中，Apache Spark 用于转换阶段。

下面是“回填”与“前向增量”的高层对比：

表 7.1：数据回填与前向增量对比（Comparison of back and forward population of data）

数据加载用例（Data loading use case）	回填（Back population）	前向增量（Forward population）
摄取类型（Ingestion type）	批处理（Batch）	批处理与实时（Batch and real-time）
数据特性（Data characteristic）	历史数据（Past data）	当前与未来数据（Current and future data）
常见用例（Common use case）	迁移或数据修复（Migration or data correction）	新数据摄取（New data ingestion）

使用 ELT 的前向增量（Forward population using ELT）

速度层的前向增量通过 Kafka 源连接器从 MySQL 实时抽取数据（读取 CDC 变更日志）构建。前向增量即把新生成的数据引入系统。生成的数据被推送到 Kafka 的 topic，再由 ClickHouse 的 sink 连接器消费。为保证 Kafka 端实时摄取，数据原样推送，不做转换。

ClickHouse sink 连接器将 Kafka 消息转换为 INSERT/UPDATE/DELETE 操作，写入 ClickHouse 的 speed 表。此阶段同样不做数据转换，以确保消费速度能跟上数据产生速度。

下图展示了从 MySQL 到 ClickHouse 的基于 Kafka、不做转换的实时装载（体现 ELT 模式）（图 7.3）：

图 7.3：前向数据增量（Forward data population）

前向增量搭建步骤（MySQL → Kafka → ClickHouse）：

下载并安装 Kafka 集群：

wget https://dlcdn.apache.org/kafka/3.9.0/kafka_2.12-3.9.0.tgz
tar -xvf kafka_2.12-3.9.0.tgz
mv kafka_2.12-3.9.0 /opt/kafka
cd /opt/kafka

启动 Zookeeper 与 Kafka broker：

/opt/kafka/bin/zookeeper-server-start.sh /opt/kafka/config/zookeeper.properties
/opt/kafka/bin/kafka-server-start.sh /opt/kafka/config/server.properties

创建 Kafka topic：

/opt/kafka/bin/kafka-topics.sh \
  --create --topic <topic> \
  --bootstrap-server <broker-node-list> \
  --replication-factor <num-replicas> \
  --partitions <partition-count>

下载并配置 MySQL Kafka 连接器：

wget https://repo1.maven.org/maven2/io/debezium/debezium-connector-mysql/3.0.6.Final/debezium-connector-mysql-3.0.6.Final.jar
mkdir -p /opt/kafka/plugins/mysql
tar -xvzf debezium-connector-mysql-3.0.6.Final.jar -C /opt/kafka/plugins/mysql

启动 Kafka Connect：

/opt/kafka/bin/connect-standalone.sh connector.properties

创建 MySQL 源连接器配置文件：

{ "name": "debezium-mysql-connector", "config": { "connector.class": "io.debezium.connector.mysql.MySqlConnector", "tasks.max": "1", "database.hostname": "host", "database.port": "port", "database.user": "debezium", "database.password": "password", "database.server.id": "1", "database.server.name": "mysql_server", "database.include.list": "database-name", "table.include.list": "table-name", "database.history.kafka.bootstrap.servers": "ip:port", "database.history.kafka.topic": "topic-name", "include.schema.changes": "true", "snapshot.mode": "initial" } }

部署 MySQL 源连接器：

curl -X POST -H "Content-Type: application/json" \
  --data @<mysql-config-file> \
  http://ip:port/connectors

创建 ClickHouse Kafka sink 连接器配置文件：

{ "name": "clickhouse-sink-connector", "config": { "connector.class": "com.altinity.clickhouse.sink.connector.ClickHouseSinkConnector", "tasks.max": "1", "topics": "<kafka_topic_name>", "clickhouse.url": "http://host:port", "clickhouse.database": "database-name", "clickhouse.username": "username", "clickhouse.password": "password", "clickhouse.table.name": "clickhouse-table", "key.converter": "org.apache.kafka.connect.storage.StringConverter", "value.converter": "org.apache.kafka.connect.json.JsonConverter", "value.converter.schemas.enable": "false", "batch.size": "1000", "insert.mode": "insert" } }

部署 ClickHouse sink 连接器：

curl -X POST -H "Content-Type: application/json" \
  --data @<clickhouse-config-file> \
  http://ip:port/connectors

使用 ETL 的历史回填（Backward population using ETL）

批处理层的回填通过按日从 MySQL 表抽取数据，在 Apache Spark 作业中进行转换/整备（curate） ，并写入 ClickHouse 的 batch 表来实现。回填即将历史数据装入系统。Spark 分别通过 JDBC 连接器读取 MySQL、写入 ClickHouse；可用 cron 调度每日的批作业。

下图展示了将数据从 MySQL 搬运到 ClickHouse 的批处理层作业（图 7.4）：

图 7.4：批处理层作业（MySQL → ClickHouse）

系统包含一张名为 sales_table 的 MySQL 表（存放当月销售数据），其中 transaction_date 为交易日期。Spark 程序以天为批读取、处理后，加载到 ClickHouse 目标表用于分析。

该程序需要 MySQL 与 ClickHouse 的 JDBC 驱动（从各自下载页获取）；还需安装 Spark。在本 ETL 流程中，我们用 JDBC 从 MySQL 抽取数据，在 Spark 中做一个简单舍入/取整类转换示例（文中示例使用绝对值），最后用 JDBC 写回 ClickHouse。实际的数据仓库常支持批量装载以应对大体量 ETL，但为简化示例，这里使用 JDBC。

brew install apache-spark

在创建 MySQL 表之前，需要安装并启动 MySQL 服务（详见官方文档）。同样，创建 ClickHouse 数据库与表之前，需要在本地或云端完成 ClickHouse 的安装/开通。

步骤如下：

创建 MySQL 数据库：

CREATE DATABASE sales_db;

连接至 sales_db 并创建月度交易数据表：

USE sales_db;
CREATE TABLE sales_table (
    transaction_id INT AUTO_INCREMENT PRIMARY KEY,
    transaction_amount DECIMAL(10, 2) NOT NULL,
    product_id INT NOT NULL,
    location_id INT NOT NULL,
    transaction_date DATE NOT NULL
);

向 MySQL 载入示例数据：

LOAD DATA INFILE '/tmp/sales_table_january_2024.csv' INTO TABLE sales_table
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(transaction_id, transaction_amount, product_id, location_id, transaction_date);

在 ClickHouse 上创建数据库：

CREATE DATABASE sales_db;

连接到 sales_db 并创建用于分析的 ClickHouse 表：

USE sales_db;
CREATE TABLE sales_table (
    transaction_id UInt32,
    transaction_amount Decimal(10, 2),
    product_id UInt32,
    location_id UInt32,
    transaction_ts Timestamp
)
ENGINE = MergeTree()
ORDER BY transaction_id;

按日运行、在加载前做简单转换（将金额取绝对值）的 Spark 作业示例：

from pyspark.sql import SparkSession

# Create a SparkSession
spark = SparkSession.builder \
    .appName("MySQL to Clickhouse") \
    .getOrCreate()

# Setup the MySQL JDBC connection properties
mysql_url = "jdbc:mysql://localhost:3306/sales_db"
mysql_properties = {
    "user": "root",
    "password": "MyPassword",
    "driver": "com.mysql.cj.jdbc.Driver"
}

# Query to batch query the day's data in MySQL
query = "(SELECT transaction_id, abs(transaction_amount), product_id, location_id, transaction_date FROM sales_table where DAYOFMONTH(transaction_date)=DAYOFMONTH(CURDATE()) limit 5) AS tmp"

# Read from MySQL daily data table into DataFrame
mysql_df = spark.read.jdbc(url=mysql_url, table=query, properties=mysql_properties)

# Setup ClickHouse JDBC connection properties
clickhouse_url = "jdbc:clickhouse://url:port/default"
clickhouse_properties = {
    "user": "default",
    "password": "MyPassword",
    "ssl": "true",
    "driver": "com.clickhouse.jdbc.ClickHouseDriver"
}

# Write daily data from MySQL to Clickhouse in a batch
mysql_df.write \
    .mode("append") \
    .jdbc(url=clickhouse_url, table="sales_table", properties=clickhouse_properties)

# Stop the SparkSession
spark.stop()

使用 cron 调度 Spark 作业：

crontab -e
0 22 * * * spark-submit --jars "/tmp/jdbc_dir/mysql-connector-j-9.0.0.jar,/tmp/jdbc_dir/clickhouse-jdbc-0.7.0.jar" spark_program.py

ETL 与 ELT 系统的技术（Technologies for ETL and ELT systems）

ETL 与 ELT 系统所用技术与批处理、微批以及实时系统相近，因为它们在底层正是利用这些数据模式。

ETL 系统通常基于批处理或微批模式构建。原因在于：实时路径上除了非常简单的转换外，几乎无法完成更复杂的转换。既然 ETL 采用批/微批模式，就必须能处理成批汇聚的大量数据。历史上，Hadoop MapReduce 是常见的批处理方案：作业可可靠消费并处理海量数据，且以 HDFS 作为临时与持久存储。随着现代系统的发展，工程师意识到 MapReduce 的资源利用效率不高，逐渐转向 Apache Spark 执行批处理作业。Spark 具备从失败点恢复的能力，是更高性能、更可靠的批处理执行框架。存储方面，对象存储正逐渐取代 HDFS，成为批处理的默认存储系统。

此外，ETL 作业需要根据批频进行调度与编排。Apache Airflow 由于能适配多种系统、并以 DAG（有向无环图） 建模工作流而成为强力选择；ETL 作业之间常存在依赖关系，DAG 是很好的表达方式。对于不需要复杂编排的简单场景，Linux 的 Cron 依旧是便捷的批作业调度工具。

市面上有多款商用现成（COTS） ETL 产品（如 IBM DataStage、Informatica），也有新入局者（如 Fivetran、Hevo Data、Airbyte）。当需要不仅从数据库，还要从 ERP/CRM 等业务软件中抽取数据时，这些工具凭借丰富的连接器生态更受欢迎。它们通常集成 ETL 所需的大部分能力，无需再分开采购“抽取、编排、加载”等组件。

表 7.2：Fivetran、Airbyte 与 Informatica 的高层对比

维度（Dimension）	Informatica	Fivetran	Airbyte
成本（Cost）	高（High）	中（Medium）	中到低（Medium to Low）
开源（Open-source）	否（No）	否（No）	是（Yes）
部署模型（Deployment model）	SaaS、云托管、本地部署	SaaS	SaaS、云托管、本地部署
用例（Use case）	ETL、ELT、治理、主数据管理	ETL	ETL 与数据集成

ELT 模式多采用实时 + 批处理的混合：抽取与加载（EL）通常走实时模式，以数据生成速率摄取到库；而转换（T）在入库后按批处理完成。由于实时 EL 需要低延迟消息投递，Apache Kafka、Apache Flink、Spark Streaming 等技术十分流行。其中 Kafka 因为对各类源与汇系统提供广泛的连接器而最为常用，并提供至少一次投递语义，大幅简化实时系统开发。受 SMT 可做转换能力所限（仅适合轻量单消息转换），复杂转换需延后到入库后进行。

真实世界示例（Real-world examples）

下面给出一个来自银行业的实例。

银行业（Banking）

银行常用 ETL 构建报表系统。银行拥有多条业务线（信用、外汇、公司金融、零售银行、保险等），各自的数据存放在对应的数据集市中；企业数据仓库（EDW）汇聚这些集市的数据，生成如 Customer 360（客户全景视图）等能力。为实现这一点，需要将各业务线的数据转换并与其他数据源整合。

难点在于：不同业务线数据集市的模型与格式差异很大——有的为关系模型，有的为文档模型，或以 CSV/Parquet 等平面文件存在。将它们统一进一个数据仓库具有挑战，这正是 ETL 发挥作用之处：从多源抽取数据，利用转换框架标准化为统一的数据模型与格式，然后加载入仓。

下图展示一种架构：从多数据源抽取、合并、转换，并加载到数据仓库。各业务线数据集市、对象存储与平面文件等由 Apache Spark 完成转换，随后写入数据仓库（图 7.5）。

图 7.5：银行业中的 ETL（ETL in banking）

当然，银行也会在适用场景中采用 ELT。例如反欺诈：为实现实时欺诈检测，交易发生时数据必须立刻可用于打分；在加载前做转换会引入不可接受的延迟，可能拖慢交易，甚至放任欺诈发生。因此数据需要实时入库以供打分。

随后，为了在实时交易数据上进行特征工程，数据仍需再做转换。特征工程往往较复杂，难以在实时系统的 SMT 中完成；同时还需要来自其他数据源的查找表/特征表，这类与外部数据的结合也不适合在单条消息转换中完成。

下图展示了这种路径：使用实时模式完成 ELT 的 EL（抽取+加载），随后对数据进行转换以开展特征工程（图 7.6）。

图 7.6：用于 ELT 的实时模式（Real-time pattern for ELT）

结语（Conclusion）

本章介绍了基于 ETL 与 ELT 数据模式的常见用例，并设计了在 Lambda 系统中以 ETL 支撑批处理层、以 ELT 支撑速度层的方案。我们看到这些模式如何借助批处理与实时技术栈来实现；同时给出了银行业的行业案例：通过 ETL 与 ELT 既支持 Customer 360 的全景视图，也支撑实时反欺诈。

下一章将在进入数据存储与服务模式之前，回顾数据的基础知识。