建设网络平台 英语,windows优化大师收费吗,网站开发哪个好,专门做酒店的招聘网站2023年8月16日~18日#xff0c;第14届中国数据库技术大会#xff08;DTCC 2023#xff09;于北京隆重召开#xff0c;拓数派受邀参与本次大会#xff0c;PieCloudDB 技术专家邱培峰在大会做了《云原生虚拟数仓 PieCloudDB ETL 方案设计与实现》的主题演讲#xff0c;详…2023年8月16日~18日第14届中国数据库技术大会DTCC 2023于北京隆重召开拓数派受邀参与本次大会PieCloudDB 技术专家邱培峰在大会做了《云原生虚拟数仓 PieCloudDB ETL 方案设计与实现》的主题演讲详细介绍了 PieCloudDB 的 ETL 方案总体设计与实现分析了 ETL 工具 pdbconduct 及相关数据库内核扩展。 图为拓数派 PieCloudDB 技术专家邱培峰标题 对于数据库用户而言ETL 的重要性不言而喻。 ETL Extract, Transform, Load 即数据的抽取、转换和加载简单理解为数据库的数据导入过程。ETL 的本质是不同系统数据组织形式之间的数据移动。ETL 的过程有助于数据库用户实现数据的高效管理和优化。它确保数据库中的数据不仅仅是存储在其中还经过了精心的处理以满足用户的需求。
1 云原生环境下的 ETL
随着云原生时代的到来经济实惠且可轻松扩展的对象存储解决方案成为满足用户对高弹性、高性价比需求的首选。传统 ETLExtract, Transform, Load是一种将数据从源系统抽取、清洗、转换最后加载到目标系统中进行分析的过程。传统 ETL 的特点是吞吐量大批量加载性能非常好缺点是对源端和目标系统影响较大通常是在非业务高峰进行因而会有较大的数据延迟通常为 T1。
CDCChange Data Capture是指实时或者准实时捕获数据库或文件系统中发生变化的数据并将其同步到其他数据系统中同时确保数据的一致性和准确性。CDC 通常通过解析源端日志的方式实现对源系统影响较小且有较低的时延。但对目标系统尤其是分析性数据库相比于批量模式会带来较大的数据更新开销。即使如此CDC 方式在数据同步方面应用越来越广泛同样的传统的 ETL 模式在很多场景仍有不可替代的优势。
无论 ETL 还是 CDC 都是把数据复制作为目标的因此不可避免的会造成一定程度的数据冗余也存在造成数据不一致的风险而基于湖仓技术的一写多读zero-ETL 等技术可以完全消除数据复制造成潜在冗余和不一致风险。统一 ETL、CDC 和湖仓技术正是 PieCloudDB Database 的 ETL 方案的目标之一。
PieCloudDB 存算分离的架构使得不同系统可以直接共享同一份底层数据避免了繁琐的数据抽取、转换和加载过程。目前PieCloudDB 支持直接读取对象存储上的 Parquet 等格式的文件实现了数据共享和访问方面提供了便捷性。
某些实际场景下会产生 ETL 需求例如同一份底层原始数据使用不同系统查询时或为不同类型的查询特化的系统会有不同的存储方式等。因此在进行 ETL 的方案设计时需要考虑以下几个要素
多种数据源需要考虑不同系统和数据源如生产 IoT 数据的多样性确保能够从不同来源事务型数据库HDFSKafka 等抽取数据应对不同系统的数据接入需求。多种数据格式数据可能以多种格式存在如 CSV、JSON、Parquet、二进制等。确保 ETL 流程具备处理不同格式数据的能力能够解析、转换和统一这些数据以适应目标系统的要求。通用的数据处理/转换使数据能够被规范地清洗、加工和转换以满足不同系统的需要。这将提高数据质量并减少冗余的转换逻辑。唯一性和事务性保证确保在数据加载过程中维护数据的唯一性和事务性。避免重复数据的导入同时在 ETL 过程中实现事务控制确保数据的完整性。断点续传在 ETL 过程中通过记录和恢复处理状态避免数据丢失或重复处理。错误处理能够捕获、记录和处理在 ETL 过程中出现的错误包括数据格式错误、连接问题等保证数据的完整性和可靠性。
这些要素的设计将帮助确保数据在从抽取到加载的整个过程中得到适当处理为数据驱动的决策和分析提供坚实的基础。
2 PieCloudDB ETL 方案总体设计与实现
2.1 PieCloudDB ETL 方案的总体设计
充分考虑到云原生时代的ETL需求PieCloudDB 的 ETL 方案总体设计主要包括三个方面
任务调度总控 pdbconduct在 ETL 流程中任务的调度和协调由 pdbconduct 负责。pdbconduct 充当着总控角色管理任务的排程、执行顺序和依赖关系。通过 pdbconduct 不同的 ETL 任务可以被智能地调度确保整个数据流程的有效运行。数据源提取插件/客户端工具数据源提取阶段涉及从业务系统的原始数据库中获取数据。这需要开发插件和工具以确保从业务系统中高效导出数据。这些插件和工具能够与不同业务系统进行连接从中抽取数据然后将其转换成适合 ETL 流程的格式。计算节点 Foreign Table 和 Formatter 解耦在计算节点上运行Foreign Table 是 ETL 过程的核心。这一步骤将从业务系统中提取的数据传输到 PieCloudDB 中并在计算节点上维护不同的数据格式。Foreign Table 允许将数据映射到数据库表中为数据的转换和处理创造了环境。
通过这三个方面的设计PieCloudDB 的 ETL 方案能够实现任务的有效调度、从业务系统提取数据以及在计算节点上处理数据的目标。整个流程确保了数据从业务系统到 PieCloudDB 的顺畅传输并为数据的转换和处理提供了必要的基础。这使得数据在被集成、转换和加载的过程中保持了准确性和一致性为后续分析和应用提供了高质量的数据资源。
2.2 PieCloudDB ETL 执行流程
当在 PieCloudDB 上开启 ETL 任务时具体流程如下图所示 源系统连接和数据提取首先与源系统建立连接执行 SQL 查询或其他高频操作以提取所需数据。这一步骤有助于从源系统获取需要进行 ETL 的数据。数据传输到中间系统提取的数据可以直接传输到中间系统其中中间系统可以是源系统的本地磁盘、PieCloudDB 的磁盘或者其他中间存储位置。这一步骤有助于临时存储数据以便后续处理。中间系统处理中间系统可能是云存储或服务器例如 Kafka具体选择根据业务场景的需要进行配置。在中间系统中为后续的 Foreign Table 准备数据。Foreign Table 连接在准备好的数据上通过 Foreign Table 的连接机制将数据映射到 PieCloudDB 中。这一步骤使得数据可以在 PieCloudDB 的环境下被进一步处理和分析。数据加载及验证可以进行数据的转换和处理 同时确保云存储上的文件是否符合预期进行必要的验证和检查以确保数据的完整性和正确性。
根据业务需求任务调度总控 pdbconduct 会在适当的时间按需触发 ETL 任务从源系统中提取所有需要进行处理的数据。这一步确保所需数据可用于后续的处理。
一旦数据导出完成pdbconduct 将相应的 SQL 语句发送到 PieCloudDB 的控制节点。这些 SQL 语句可能包括数据转换、加载或其他操作以准备数据进入 PieCloudDB 的环境。
在 PieCloudDB 控制节点执行 SQL 语句后pdbconduct 收集执行结果记录任务的进度以及任何可能的错误信息。这可以帮助监测任务的状态并在出现问题时迅速采取适当的措施。如果在执行过程中出现错误pdbconduct 将记录所有错误信息并根据需要采取相应的补救措施。
2.3 INSERT/MERGE 模式
PieCloudDB 的 ETL 支持 INSERT 和 MERGE 两种常见的数据处理模式。用户可以根据业务需求、数据更新频率、和数据变化情况选择 INSERT 模式或 MERGE 模式。
2.3.1 INSERT 模式
INSERT 模式是将源系统中的数据直接插入到 PieCloudDB 中的一种模式。在这种模式下从源系统中提取的数据会被逐行或逐批插入到 PieCloudDB 中的对应表中。INSERT 模式适用于对数据进行批量导入或者当数据变化较小且新增记录为主要操作时。INSERT 模式的优势在于简单直接支持单纯的导入场景特别擅长与现有数据没有逻辑关联的时序数据流 步骤 1获取原始数据
首先针对特定的数据源需要开发适配器或插件以便 PieCloudDB 能够连接到该数据源。可能需要开发 PostgreSQL 扩展来支持数据源的通信和数据格式解析。
接着控制节点将读取数据源信息包括连接参数、认证信息、数据抽取规则等决定是否将任务进行拆分来提高并发性和效率接着生成任务信息查询语句、任务依赖关系等。最后计算节点根据任务信息读取数据源并将原始数据和元信息返回给控制节点。
通过这些步骤INSERT 模式下的 ETL 流程将数据从数据源中获取并通过 Foreign Table 的方式插入到 PieCloudDB 中。
CREATE FOREIGN TABLE foreign_table(meta text, raw bytea);
SELECT meta, raw FROM foreign_table;
步骤 2数据的准备和解析
经过步骤 1从 Foreign Table 中获取的原始数据需要经过解析和转换以适应内部行格式。而这个转换过程通常是通过 Formatter 完成的。
PieCloudDB Formatter 会先对 Foreign Table 中获得的原始数据进行解析根据数据的格式如 CSVJSONXML 等将原始数据分解成可操作的数据单元字段、行、列等。
接着PieCloudDB Formatter 会将解析后的数据进行转换以适应 PieCloudDB 的内部的行格式生成需要的各列。
CREATE FUNCTION formatter(input bytea) RETURNS user_type …;
SELECT meta, raw FROM foreign_table
LATERAL JOIN formatter(raw);
步骤 3数据的转换
在步骤 3 中会对步骤 2 中解析出的列执行数据转换操作以确保数据的准确性和一致性使数据能够顺利插入 PieCloudDB 表中为后续的分析和应用提供可靠的数据基础。
SELECT r.a, r.br.c, func(r.d) … FROM (SELECT meta, raw FROM foreign_table
LATERAL JOIN formatter(raw) AS r) sub;
步骤 4插入目标表
经过前面三个步骤数据已经完成了准备和转换此时将在步骤 4 中完成插入目标表。
INSERT INTO table
SELECT r.a, r.br.c, func(r.d) … FROM (SELECT meta, raw FROM foreign_table
LATERAL JOIN formatter(raw) AS r) sub;
步骤 5插入历史表支持断点续传
最后为了支持断点续传会将数据插入历史表以保存数据的变更历史新增、更新和删除操作从而实现对断点续传的支持。
INSERT INTO history
SELECT meta FROM foreign_table;
2.3.2 MERGE 模式
PieCloudDB 的 ETL MERGE/UPSERT 模式支持 CDCChange Data Capture场景。这种模式可处理具有操作类型、逻辑主键和顺序键的数据以实现数据的插入、更新和删除操作。
在MERGE模式下数据需要包含操作字段OP即 INSERT/UPDATE/DELETE、逻辑主键和顺序键。当逻辑主键不存在时模式会执行 INSERT 操作当逻辑主键已存在时会执行更新或删除操作。顺序键用于确定操作的顺序在处理多个操作时根据顺序键确定操作的执行顺序以防止操作间的冲突。MERGE 模式允许处理重复数据但不可以有事务逻辑错误。 步骤 1数据解析和导入临时表
首先从外部数据源获取的原始数据经过解析以获取包含操作字段OP、逻辑主键LPK和顺序键OK等的数据。接着将解析后的数据导入到与目标表类型相同的临时表中。这个临时表用于存储待合并和更新的数据。
SELECT r.a, r.br.c, func(r.d) … FROM (SELECT meta, raw FROM foreign_table
LATERAL JOIN formatter(raw) AS r) parsed;
步骤 2临时表内部去重
在临时表内部对于具有相同逻辑主键LPK的行根据顺序键OK选择保留 OK 最大的那行确保只保留顺序键最大的唯一记录。
INSERT INTO temp_table
SELECT all_columns FROM ( SELECT *, row_number() OVER PARTITION BY lek
ORDER BY ok DESC FROM parsed
) AS no_dup WHERE no_dup.row_number 1;
步骤 3目标表删除 PK 匹配行
在目标表中根据逻辑主键LPK进行匹配删除与临时表中的数据具有相同逻辑主键的记录。这确保了数据的更新操作。
DELETE FROM table USING temp_table
WHERE table.pk temp_table.pk;
步骤 4插入目标表完成 merge
将经过去重和操作处理后的数据插入到目标表中完成数据的合并和更新。插入操作可能涉及 INSERT、UPDATE 或 DELETE 操作根据数据的操作字段OP决定。
INSERT INTO table SELECT all_columns
FROM temp_table;
在完成 MERGE 后同 INSERT 模式一样会记录历史信息这里就不再赘述。 最后,让我们通过一段视频讲解及 demo 更加具象地了解一下这个过程。
