ETL为什么经常变成ELT甚至LET？_物流中elt时间

ETL是将数据从来源端经过清洗（extract）、转换（transform）、加载（load）至目的端的过程。正常的 ETL 过程应当是 E、T、L 这三个步骤逐步进行，也就是先清洗转换之后再加载进目标端（通常是数据库），最后在数据库中的只是合理的结果数据。这个过程本来很合理，但实际过程中经常被执行成ELT甚至LET，即源端数据先装载进目标库再进行清洗和转换。

出现这种现象是因为源端数据可能来源多处，数据库、文件、web等等，不仅数据源多样数据质量也参差不齐，由于E和T这两个步骤会涉及大量数据计算，除了数据库以外，其他数据源并不具备多少的计算能力，想要完成这些计算就要先加载到数据库再进行，这就形成了LET。而且，即使源端是数据库也会面临多库的场景，跨库完成数据清洗和转换远没有先装载到目标库再处理方便，同样会造成ELT或LET。

那么ETL变成ELT/LET会带来哪些问题呢？

首先是时间成本增加。大量未经清洗和转换的原始（无用）数据装载进数据库会带来过长的时间消耗。而且数据库的计算资源有限，完成额外的E、T计算势必要消耗很长时间，进一步增加时间成本。ETL通常是有时间限制的，一般会在业务空闲的时间进行，比如前一天22点到第二天5点，如果在指定时间段没有完成就会影响第二天的业务，这就是常说的ETL时间窗口。ETL时间过长会导致时间窗口不足，影响正常业务。

此外，从数据库容量的角度来看，存储大量没有经过清洗转换的原始数据会占用过多数据库空间造成数据库容量过大，导致数据库面临扩容压力。现代应用经常使用的JSON或XML格式的多层数据入库还要在数据库中建立多个关联的表来存储，会进一步加剧数据库容量问题。任务越来越多、资源越来越少、时间窗口有限，这样就陷入了恶性循环。

那么，为什么要把数据加载数据库后才能做E和T这两个动作呢？如前所述，是因为数据库外的计算能力不足，先入库再计算是为了利用数据库的计算能力，如果能够提供库外计算能力，那么这个问题也就迎刃而解了，回归合理的ETL过程。

使用开源集算器SPL可以实现这个目标。

SPL是一款独立的开源数据计算引擎，提供了不依赖数据库的计算能力，可以对接多种数据源完成数据处理。基于SPL丰富的计算类库、敏捷语法和过程计算可以很方便地完成复杂数据计算任务，在数据库外完成数据清洗（E）和转换（T），将整理后数据加载（L）到目标库中实现真正的ETL。

库外计算实现真正ETL

多源支持与混合计算

SPL可以对接多种数据源，这样来源端数据源无论有无计算能力都可以通过SPL完成数据清洗和转换。

特别地，SPL还能实现多源混合计算，将多源数据统一清洗转换后加载到库，不需要再借助数据库的计算能力就能完成ETL工作。尤其是对JSON和XML等多层数据格式提供了很好支持，简单一个函数就能完成解析，非常方便。

举个简单的例子，将json与数据库混合计算后更新到数据库的过程：

强计算能力与过程控制

SPL提供了专业的结构化数据对象及其上的丰富运算。不仅分组汇总、循环分支、排序过滤、集合运算等基础计算可以进行，位置计算、排序排名、不规则分组也提供直接支持。

SPL还提供了专门用于大数据计算的游标支持，通过游标就可以处理超过内存容量的数据，计算实现与全内存方式几乎完全一样。比如通过游标读取文件并进行分组汇总：

=file(“persons.txt”).cursor@t(sex,age).groups(sex;avg(age))
1

与全内存读取计算:

=file(“persons.txt”).import@t(sex,age).groups(sex;avg(age))
1

除了丰富的计算类库，SPL还支持过程计算，可以按自然思维思维分步编写代码，适合完成原本在数据库中使用存储过程实现的ETL复杂计算。而SPL计算在数据库外，不会对数据库造成负担，同时兼具灵活性和高性能，实现“库外存储过程”的效果，是传统存储过程的很好替代。库外计算还可以为数据库充分减负。以往要借助数据库完成的ET计算现在都在库外完成，既不需要额外消耗数据库的计算资源，也无需存储未经清洗的大量原始数据，空间占用也少，数据库的资源和容量问题都能得到很好解决。

同时，SPL的语法体系比SQL和Java等也更为敏捷，涉及E和T计算尤其是复杂计算，算法实现更简洁代码更短，开发效率更高。比如在实现某保险公司车险保单ETL业务时，使用SPL不到500格（网格式编码）代码就实现了原本2000行存储过程的计算，工作量减少了1/3以上。（案例详情：开源 SPL 优化保险公司跑批优从 2 小时到 17 分钟）

从技术栈的角度来看，基于SPL还可以获得一致的语法风格，面对多样性数据源ETL时可以获得通用一致的计算能力。不仅技术路线统一，开发维护也很方便，程序员无需掌握不同数据源数据的处理方法，学习成本也更低。特别的，统一的技术路线具备更强的移植性，ETL数据源变化只需要更改取数代码即可，主要的计算逻辑无需更改，具备很强的移植性。

高性能保障时间窗口

对于源数据读取，SPL能很方便地进行并行处理，充分发挥多CPU的优势加速数据读取和计算速度。比如并行取数：

类似的，读取大文件时也可以并行：

=file(“orders.txt”).cursor@tm(area,amount;4)
1

使用 @m 选项即可创建多路并行游标，SPL 会自动处理并行以及将结果再汇总。SPL还有很多计算函数也提供并行选项，如过滤A.select()、排序A.sort()等增加@m选项后都可以自动完成并行计算。

在ELT任务中还经常出现数据落地的情况，无论是中间数据还是最后的计算结果，这都涉及数据存储。SPL提供了两种二进制存储形式，不仅存储了数据类型不必再次解析效率更高，而且还采用了适合的压缩机制可以有效平衡CUP和硬盘时间，同时提供了行式和列式存储方式适应更多场景，采用独有的倍增分段技术还可以实现单文件可追加分块方案更方便并行计算。这些高性能存储机制为计算性能提供了基础保障，要知道高性能计算依靠的就是存储和算法。

SPL提供了众多高性能算法，仍是上述案例（开源 SPL 优化保险公司跑批优从 2 小时到 17 分钟）中，使用SPL不仅把代码量减少到1/3，还将计算时间从2小时缩短到17分钟。其中主要使用了SPL特有的遍历复用技术，可以在对大数据的一次遍历过程中实现多种运算，有效地减少外存访问量。而关系数据库中用SQL无法实现这样的运算，有多种运算就需要遍历多次。在本例中就涉及对一个大表进行三次关联和汇总的运算，使用SQL要将大表遍历三次，而使用SPL只需要遍历一次，所以获得了巨大的性能提升。

在ETL业务中还经常出现巨大主子表关联的情况，比如订单和订单明细，这些关联是通过主键（或部分主键）的一对多关联，如果事先按照主键排序，那么关联计算可以使用有序归并算法，相对常规HASH JOIN算法，复杂度可以从O(N*M)降到O(M+N)，性能将大幅提升。但数据库基于无序集合理论，SQL也很难利用数据有序来提高性能。在上面案例中也涉及这种主子关联运算，使用SPL的有序归并算法大幅提升了关联性能。

将具备强计算能力的SPL作为ETL过程中的ET引擎，将数据计算从源端和目标端独立出来不与任何一端耦合在一起，这样可以获得更强的灵活性和更高的移植性，同时不对源和目标造成过大压力，享受库外计算的便利，实现了真正的ETL过程。同时基于SPL的高性能存储、高性能算法与并行计算又充分保障了ETL效率，这样就可以在有限的时间窗口内完成更多ETL任务。

SPL资料

• SPL官网
• SPL下载
• SPL源代码

欢迎对SPL有兴趣的加小助手（VX号：SPL-helper），进SPL技术交流群