报表自动化: 神奇的粘合剂 ETL 工具

ETL

Extract, transform, load https://en.wikipedia.org/wiki/Extract,_transform,_load

抽取,转换,加载 https://baike.baidu.com/item/ETL/1251949?fr=aladdin

说的通俗点,就是帮我们从源表 select 数据 –> 经过一系列计算 / join 等操作 –> update / insert 到目标表 这一套流程的工具,除此以外工具还有定时任务、任务执行顺序约束等一系列功能,通过 ETL 让我们表与表之间、库与库之间实现了可控的信息流转。

还有叫做 ELT 的工具,调换了顺序,针对了不同的场景

Informatica 与 Azure Data Factory

此文撰写于 2020.4,对比内容为个人有限的使用经历,并不权威,不保证公正,若有错误或侵权内容请告知,会尽快删改。

两者都可以进行 ETL 功能,相对来说前者具有全面的功能,后者在中国区里功能较弱(其他区有 Data Flow 功能集可以做更多地边界操作),让我们从功能角度来看看差异。

https://docs.microsoft.com/zh-cn/azure/data-factory/introduction

https://www.informatica.com/

一个执行任务

Informatica 里面使用叫做 Mapping 的模块存储一个执行任务,里面会进行从多个源表读数据、各种运算处理、存储到多个目标表等一系列的相关联操作

除此以外 Informatica 还有Synchronization Task 可以快速的做简单地 表对表拷贝,中间不进行复杂逻辑,最多就是字段名映射关系控制

ADF 通过一个 Pipeline 来配置一个执行任务,通过 Pipeline 里的 Copy Data 功能块来进行源与目标的拷贝配置

Copy Data 是一个完整的块,只能进行一个到一个表的拷贝,源与目标配置在了一起,难以做到读取多个表经过各种操作后写入到多个表的操作行为(Data Flow 功能集可以做到分离)

而 Mapping 是有 Source 与 Target 两个功能块,将源和目标分离,更灵活的进行配置

控制执行时间

job 不只是一系列操作的聚合,还需要掌控时机。真的要让数据自由的流转,那必须要让其可以被自动的触发,所有 ETL 都具有类似于定时器的功能。

Informatica 使用 Mapping Task 功能对上述提到的 Mapping 进行执行周期/频次等配置,而 ADF 通过 trigger 对 Pipeline 进行配置,相对来说两者功能近似。但除此以外 Informatica 还有 PowerCenter Task 以及对于简单拷贝 Sync Task 可以直接配置好执行时间周期。

一个执行队列

一系列的任务,我们不能去人工的都安排好具体的执行时间,因为我们也无法确定每次的执行总时间,如果遇到强依赖的右前后顺序的任务就需要进行执行队列控制了。

Informatica 有 Taskflow 系列功能可以对各种任务进行执行顺序的安排。

对于 ADF 比较特殊,他还是通过 Pipeline 进行管控,Pipeline 里面有很多功能除了能够 Copy Data,还可以 Excute Pipeline 模块可以去指定的执行其他 pipeline,通过此功能可构成了 Pipeline 之间的树形关系,进而实现执行顺序的控制

执行存储过程

对于过于复杂的逻辑,如果使用 各种 join 等 ETL 功能,可能会导致可视化界面极其混乱且不易懂,也许又到了翻出存储过程的时候了

Informatica 的 Mapping 必须最少有一个 Source 一个 Target,这就很难操作,需要 Source 一个 select 1 等方式构建假信息,并对于 Target 写出一个假信息来满足这个必要条件,然后再 Source 里可以通过 pre sql 或者 post sql 等方式实现存储过程的执行,但无法获取执行结果

ADF 有专门的 Stored Procedure 模块可传参也可获取返回结果

其他细节差异

错误处理:默认 Informatica 写入过程是分批 / 逐条的,当有错误后会跳过这条进行后续的,并记录下来;ADF 默认是出错后直接停止报错,可以配置错误记录位置并跳过(ADF 更像是一个开放的云平台,给的资源并不多,这里就要自己有资源存储位置了)。

运行环境变量:Informatica 可以通过变量的方式获取上次运行时间等信息,ADF 需要自己存好、需要的时候取来用自己配置好这一套记录方式,还是上面说的给的资源并不多

自定义能力:Informatica 几乎对于每一个行为都给了前后可自定义执行脚本的功能,比如 source 在获取数据前后有 pre sql 和 post sql,同时可以在读取同时进行 sort 和 filter 等操作,ADF 对于自定义脚本执行等功能相对较少,但可以通过各种功能块魔改出来

upsert 功能:ADF 并不原生支持,需要自己通过存储过程等方式执行,会多一次数据拷贝,尤其是对于其也不原生完整支持跳过错误并记录等功能,需要自建很多存储过程。并且由于调用的是存储过程将难以参看 upsert 进度。

datasets:ADF 独有的一个概念,将 connect 与某个表绑定到一起,实现了复用,但是对于 copydata 的 target(sink) 要求必须使用 datasets,这样在拷贝操作数量极其庞大时下会产生海量的 datasets,而且复用就意味着一个改了其他的连带的被改变,增加了这一层不知道有什么特殊目的

……

敏捷团队如何做基于数仓的报表

最初开始时,不能把数仓的设计当多一个简单地 epic,认为只是在一个业务功能的开发,否则后患无穷。

开始时需要整个团队了解认知要做的是什么,在业务明确后进行一次整体设计,制定好开发方式以及严格的详细的 code review 过程,我们还需要尽早的考虑多个超出报表最终产物的内容,比如:

  • 数据来源稳定性
  • 第一次上线大量数据如何处理,根据什么分库分表?还是根据时间段划分?
  • 如何测试
  • 报表结构合理性
  • 更新频率
  • 共用数据 / 字段以及如果在频率不同时确定依赖关系
  • ……

不要直接就 IPM、开卡、逐个报表开发,否则上面的几个问题后续会不断地对进行遍及所有报表全面改动,进一步衍生出遍及所有报表的全面测试,以及遍地 bug 四处救火的收尾时期……

数据仓库只是开始

回看前面多篇文章,我们可以发现数仓很大的问题:

  • 单体架构,随着规模的庞大数仓如何支撑复杂运算与大量数据
  • 层次紧耦合,虽然有 ETL 的逻辑与数据解耦但仍然是难以拆解的层次耦合,超级复杂的层次关系
  • 数据流复杂,经过多层的梳理,数据经历了业务库、OBS、事实与多层维度、多层指标……以及中间的各种 ETL 逻辑,如何梳理出最终一个数的来源?
  • 领域知识传输困难,数仓的复杂性往往会独立构建团队,这就与业务团队分离了,业务知识传递过程难免遗漏,而且业务逻辑的变化如何对应的更改呢?
  • 增量困难,数仓的设计可以看到更加的偏重于设计,如果有超出设计的需求出现,我们如何快速的响应?构建新的 obs 表,重建维度模型或者是在一堆模型里找线索?如何新增一个产品的数据呢?
  • 低演进能力,几乎无法演进的设计是由于上面各项导致的牵一发而动全身,谁敢随便改?单纯从技术角度来看看一个 table 会被什么使用: view、procedure、etl……

后面该如何?Data Lake~Micro Data Warehouse~Data Mesh~

我们真正希望的是挖掘出数据的潜在价值,让资源尽可能的充分利用。支撑业务系统是数据的第一价值,然后呢?

感谢阅读,欢迎关注微信公众号:coologic

报表相关的系列文章请参考:


| 版权声明: 本站文章采用 CC 4.0 BY-SA 协议 进行许可,转载请附上原文出处链接和本声明。
| 本文链接: Cologic Blog - 报表自动化: 神奇的粘合剂 ETL 工具 - https://www.coologic.cn/2020/04/1781/

留下评论