让决策更智能
新一代智能数据分析平台

Smart ETL性能优化建议

观小远发表于:2021年12月07日 17:16:49更新于:2022年04月26日 14:12:42

        在观远数据产品的整体架构已经确定的情况下, ETL 的性能优化实际上就是减少计算的数据量和减少计算复杂度两个方面 。

1  减少计算的数据量

        可以从行列两个方面着手, 减少计算的数据量。

1 . 1 去掉无用的列

        dm 层为了后续建模的方便, 倾向于使宽表大而全, 但直接面向前端展示的报表可以删去不需要的列,减少数据的吞 吐量,加快运行时间。比方说可以使用选择列,并且操作 在整个ETL的过程中越靠前越好,建议直接放在输入数据集的后一步(不仅会减少计算复杂度,很多时候也会减少数据的读取量) 。

00161af24f6e679f4eeb45ec965731d

图 1  去掉无用的列

1 . 2  选择合适的源数据

        在设计 ads 层报表时通常一 张报表可供选择的数据数据源 有多个 ,应该在充分理解数据的基础上, 使用最小的数据集 。 比方说一 张要分析门店组织销售情况的 ads 层表格,源数据既可以从 dm_商品销售库存日汇总表里面来 ,也可以从 dm_销售 明细宽表中来,  也可以从 dm_销售 日汇总表里面来 。  dm_商品 销售库存日汇总表的数据量是 10 亿*62,  dm_销售明细宽表的 数据量是 2 亿*101, dm_销售 日汇总表的数据量是 600 万*23 。

        使用 dm_销售 日汇总表单单读取数据一项就比使用 dm_商品销售库存日汇总表节省 2 分钟以上的时间 。

1 .3  添加计算字段操作前置

        这是从行数的角度减少计算的数据量 , 能仅仅依靠维表计 算的字段, 尽量在维表关联之前计算; 可以依靠现有字段计 算的数据, 尽量在数据发生膨胀之前计算 。

1 .4  聚合操作前置

        同样出于减少行数的考虑, 应该在 etl 中尽早将数据聚合 到需要的粒度 。  避免在更细的粒度上进行不必要的大数据量 关联 、  筛选 、  添加计算字段等操作 。

 00161af25020d1cfe6e7501f9e44b4a

图 2  先聚合再关联

2  减少计算复杂度

2 . 1  避免冗余的联合主键

 00161af2514b4e1139259b0758e37cd

图 3  冗余的联合主键

        聚合操作的复杂度与主键数目正相关 。在忽略工具优化情 况下,对于 100 行的数据,当聚合主键为 1, 需要遍历数据集 一 次(100 次计算); 当聚合主键数为 2, 需要遍历数据集两次 (或者遍历一 次做两次比较,200 次计算)。可以看到,主键 数目的增加会使聚合操作的时间复杂度线性增加 。

        在图3中,一级区域名称 、二级区域名称等只依赖于组织id, 它们可以放进数值里面(聚合方式为最大值);卡类型 - 会员等级 、注册日期只依赖于会员卡号,它们也可以放进数值里面 。

 00161af252b5787c31e426416873800

图4  推荐的聚合操作主键设置

        同时,使用图4所示的聚合操作主键设置,还可以验证我们对于数据集主键的理解, 进一步缩小出错的空间 。

2 . 2  使用先拼接再聚合替代 1 对 1 的关联操作

        理论上,在关联字段没有设置索引的情况下,使用先拼接再聚合(union + group by)  会比直接 join 快很多(10 倍以上的提升) ; 但多数 情况下,  在 ETL 里面我们使用的关联字段都设置了索引, 所以速度几乎没有提升 。

        但在维表关联过早,大量维度数据需要直接从源表里面取而不是从维表里面取的时候,使用 union+group by 仍然会带来速度的提升。这主要是因为它避免全关联之后数量巨大的COALESCE操作(新建字段)。

00161af255d5abd152f7f75c25c7308

图5 大量的维度需要使用COALESCE操作计算得到

00161af256eeb1bd15909bf8ca8a431

图 6  使用 union+group by  避免 COALESCE 操作

        另外,在关联字段没有设置索引的情况下,仍然推荐使用 union + group by 来提升时间效率 。

2 .3  分叉前置

        强规则的同期数据计算等操作, 会需要将 ETL 分叉,由于观远的 smart ETL 并不会保留中间节点的值, 所以对于每一个分叉都需要从头开始计算 。

00161af258b8a628572e04c7ed6e5a8

图 7  分叉的 ETL

        对于图7所示的分叉情况,实际上分叉之前的节点会被重复计算 3 次 。 如果有比较复杂的节点在分叉之前,就会造成比较低的时间效率 。 所以建议在逻辑允许的情况下将分叉操作前置 。

3  全量计算变增量计算

       ETL的运行时间大致可以分成数据读写时间和计算时间。由于仍然要拼接成全量数据并写入数据集,所以基于 ETL 的增量更新不能优化数据的读写时间。所以基于 ETL 的增量更新仍然存在它的适用场景。

1. ETL 的计算复杂度较低,比方说只需要去匹配少量维表的 dm 层宽表,这时候基于 ETL 的增量更新对于时间效率的提升会相对较小 (过往的经验是在 50%左右)。

2. ETL 的计算复杂度较高,比方说欠品率的计算,由于可以大大缩小计算的商品和时间范围并且本身输出的数据集较小(写入耗时较少),时间效率的提升非常明显 。

具体做法参考 :《ETL如何实现增量更新》 。

4  其他

4 . 1  避免同一个 ETL输出多个数据集

        ETL并不保存中间节点的值,所以同一个 ETL输出多个数据集理论上并没有时间优化的效果,反而会干扰我们的逻辑 。建议另存为新的 ETL 再修改,然后输出 。

4 . 2  有效利用 ETL的连接线梳理逻辑

        数据的流向应当尽量清楚,应当可以从 ETL的外观一望而知。 尽量避免太随意的 ETL 布局 。

 00161af266276fcdc9bd60619ceb450

图8 太随意的布局

4 . 3 用强规则的操作确保节点的主键和自己预想的一致

        强烈建议对每一个节点的主键都有预想和验证。对于可能改变主键的操作,事先应该充分估计,事后应该验证。关联操作(用非主键关联,或者没有使用全部主键),拼接操作(拼接双方主键不一致),聚合操作,选择列(没有选择全部主键)以及部分添加计算列操作(explode)都有可能改变数据的主键,应当区分这种改变是不是自己想要的。

4 . 4 避免比较不能精确存储的数据格式或者使用他们作为主键(聚合操作的维度)

        double类型,timestamp类型在存储时都有可能与预想的存在误差,为了避免错误或者漏数据的情况发生,应该把他们转化为更合适的类型,或者使用其他字段作为替代。



 


    您需要登录后才可以回复