ETL优化建议
ETL 的性能优化主要就是减少计算的数据量和减少计算复杂度两个方面 。
一. 减少计算的数据量
1. 选择合适的源数据
通常一张报表可供选择的数据数据源有多个，应该在充分理解数据的基础上，使用最小的数据集 。
比方说一张要分析门店组织销售情况的 ads 层表格，源数据既可以从 "dm_商品销售库存日汇总表"（数据量10 亿*62）里面来 ,也可以从 "dm_销售 明细宽表"（数据量2 亿*101）中来，也可以从 "dm_销售 日汇总表"（数据量600 万*23）里面来。使用 "dm_销售 日汇总表"单单读取数据一项就比使用 "dm_商品销售库存日汇总表"节省 2 分钟以上的时间 。
2. 去掉无用的行和列
直接面向前端展示的报表可以删去不需要的行和列，减少数据的吞吐量，加快运行时间。可以使用选择列、筛选数据行，并且操作在整个ETL的过程中越靠前越好，建议直接放在输入数据集后第一二步（不仅会减少计算复杂度，很多时候也会减少数据的读取量） 。
3. 聚合操作前置
应该在ETL中尽早将数据聚合到需要的粒度，避免在更细的粒度上进行不必要的大数据量关联、筛选、添加计算字段等操作。特别是在关联节点前添加分组聚合，可以避免关联产生数据膨胀。
4. 添加计算字段操作前置
仅仅依靠维表计算的字段，尽量在维表关联之前计算；可以依靠现有字段计算的数据，尽量在数据发生膨胀之前计算。
二. 减少计算复杂度
1. 避免冗余的联合主键
聚合操作的复杂度与主键数目正相关 。在忽略工具优化情况下，对于100行的数据，当聚合主键为1，需要遍历数据集一次(100次计算)； 当聚合主键数为2，需要遍历数据集两次（或者遍历一次做两次比较，200次计算）。可以看到，主键数量的增加会使聚合操作的时间复杂度线性增加 。
在下图1中，一级区域名称、二级区域名称等组织架构相关字段只依赖于“组织id”，卡类型 - 会员等级、注册日期只依赖于“会员卡号”，更建议把它们放进数值栏（聚合方式选最大值或无处理）。推荐做法请参考下图2，高效同时还可以验证我们对于数据集主键的理解，进一步缩小出错的空间 。
2. 分叉前置
使用同一个数据集计算同期数据、按照不同维度聚合等操作, 会需要将 ETL 分叉，由于观远的 smart ETL 并不会保留中间节点的值， 所以对于每一个分叉都需要从头开始计算 。
以下图为例，实际上分叉之前的节点会被重复计算 3 次 。如果有比较复杂的节点在分叉之前，就会造成比较低的时间效率 。所以建议在逻辑允许的情况下将分叉操作前置 。
3. 避免同一个ETL输出多个数据集
ETL并不保存中间节点的值，所以同一个 ETL输出多个数据集理论上并没有时间优化的效果，反而容易干扰我们的逻辑 。建议另存为新的ETL再修改，或者拆分为多个ETL，然后分别输出 。
以下图为例，ETL分叉靠后，第2个输出数据集相当于是基于第1个输出数据集制作的，那么比起同一个ETL里输出2个数据集，更合理高效的方式是：该ETL仅输出1个数据集“活跃度宽表”，再新建一个ETL，用“活跃度宽表”作为输入数据集制作得到第2个数据集。
三. 全量计算变增量计算
ETL的运行时间大致可以分成数据读写时间和计算时间。由于仍然要拼接成全量数据并写入数据集，所以基于 ETL 的增量更新不能优化数据的读写时间。但是基于 ETL 的增量更新仍然存在它的适用场景:
1）ETL 的计算复杂度较低，比方说只需要去匹配少量维表的部分字段，这时候基于 ETL 的增量更新对于时间效率的提升会相对较小 （过往的经验是在 50%左右）。
2.）ETL 的计算复杂度较高，比方说欠品率的计算，由于增量计算可以大大缩小计算的商品和时间范围并且本身输出的数据集较小（写入耗时较少），时间效率的提升非常明显 。
具体做法参考 :《ETL如何实现增量更新》 。
四. 提效和减轻维护成本
1. 有效利用 ETL的连接线梳理逻辑
数据的流向应当尽量清楚，应当可以从 ETL的外观一望而知。 尽量避免太随意的 ETL 布局 （如下图），可以借助ETL里的“自动布局”和“添加注释”来帮助梳理逻辑。
2. 用强规则的操作确保节点的主键符合预期
强烈建议对每一个节点的主键（聚合操作的维度）都有预想和验证。对于可能改变主键的操作，事先应该充分估计，事后应该验证。关联数据（用非主键关联，或者没有使用全部主键），行拼接（拼接双方主键不一致），分组聚合，选择列（没有选择全部主键）以及部分添加计算列操作（explode函数使用）都有可能改变数据的主键，应当区分这种改变是不是自己想要的。
可以借助“数据探查”来查看数据行数、唯一值个数、null值个数，以此判断主键是否发生变化，数据量变化是否符合预期；也可以使用“分组聚合”、“SQL”、“筛选数据行”等节点来校验数据。
3. 使用能精确存储的数据格式作为主键
double、timestamp类型在存储时都有可能与预想的存在误差，为了避免错误或者漏数据的情况发生（关联、行拼接时易产生脏数据或匹配失败），应该把他们转化为更合适的类型（例如string、int、date），或者使用其他字段作为替代。同时，关联、行拼接时，更要保证多张表的对应主键格式类型完全一致。
4. 规范使用函数
1) 尽量不要嵌套多层函数
如果SQL函数多层嵌套使用，特别是窗口函数，计算复杂度升高，数据量大的情况下可能导致该节点运行时服务器资源不够用。所以建议把复杂的计算拆分为多个节点来进行，同时也便于校验数据准确性。
例如：sum(case when [Area]='A' then [sales] else 0 end) over (partition by date_format([FILDATE],'yyyy-MM'))
建议：ETL先新建2个字段 [年月]: date_format([FILDATE],'yyyy-MM')，[销售额]：case when [Area]='A' then [sales] else 0 end；然后，sum([销售额]) over (partition by [年月])
2) 窗口函数合理设置partition
Partition设置不合理，比如数据表行数较多时使用partition by 1，partition后跟过多字段，容易导致数据倾斜，CPU占用过高，最终出现计算失败，甚至server挂掉BI不可用。建议尽量使用「分组聚合+自关联」方式来代替。
3) 谨慎使用collect_set、collect_list
去重计数的开窗用法 size(collect_set( [A] ) over(partition by [B] )) 。collect_set会生成大量的中间数据，数据量大的话可能会导致磁盘和性能问题，类似函数的还有collect_list。
建议：如果目的是去重计数，在ETL里改为分组聚合+自关联方式，具体参考 去重计数函数实现开窗；如果用于字符串拼接，例如 concat_ws(collect_set([A]))、collect_list(struct([A],[B]))等，则需要控制每个分组的行数（上限50万行），尽量减少数据量。
4) 改变字段类型要使用函数转换
不规范用法1：新建字段时，下拉框里选择和原始字段不同的字段类型；
不规范用法2：使用的函数和指定的字段类型不一致。
这些用法只能修改BI里显示出来的字段类型，并不能真正转换数据格式，后续参与计算的时候大概率会出错。建议使用适合的数据库函数转换格式，且保证函数和目标类型是一致的。
注意：例如 substr() 此类字符串函数对date/timestamp类型处理比较慢，concat是拼接文本，返回的也是string格式，不建议用来处理日期时间字段。
5. 多条件判断+筛选尽量做成一次筛选
例如：新建多个计算字段判断是否某字段内包含a、b、c等内容，然后筛选不包含这些内容的数据。
建议改为：
6. 尽量用系统自带的算子代替SQL算子
ETL里的SQL算子需要对数据表进行全表扫描，重新判断字段类型和计算，相比起其他节点是比较耗资源的。可能导致用户不习惯的现象有： 如果前面节点存在脏数据，SQL节点可能无法正确判断字段类型，导致字段类型的显示发生变化；前面节点修改后，SQL节点必须重新提交保存才能预览到正确数据；预览加载时间长，容易超时；后续维护和修改SQL语句逻辑较困难，无法及时校验数据准确性；使用SQL语句对数据进行排序不生效（观远BI不支持数据集排序）。
建议：常用的SQL逻辑都可以用系统自带算子代替。拆分为多个节点更利于梳理逻辑，校验数据、后续维护都会比较容易。仅建议系统自带算子无法实现的场景再用SQL算子，且尽量简化语句，例如两个数据集模糊匹配；同时也需要控制ETL内SQL节点的个数。
SELECT input2.*,input1.`客户ID` from input2 left join input1 on array_contains(split(input2.`name_list`,','),input1.`客户简称`)
7. 合理利用ETL参数配置
ETL开发人员较多，难以保证所有人都按照以上优化措施执行，管理员可以在管理员设置--运维管理--参数配置对ETL进行全局限制。例如关联数据、SQL输入、行拼接这3种节点需要消耗更多的资源，可以进行重点限制。
