系列 | 漫谈数仓第三篇NO.3 『数据魔法』ETL

傷城~ 2023-05-31 07:12 61阅读 0赞

![640?wx\_fmt=gif][640_wx_fmt_gif]

点击上方蓝色字体，置顶/星标哦

目前10000+人已关注加入我们

![640?][640] ![640?][640 1] ![640?][640 2] ![640?][640 3] ![640?][640 4] ![640?][640 5] ![640?][640 6] ![640?][640 7]

![640?][640 8] ![640?][640 9] ![640?][640 10] ![640?][640 11] ![640?][640 12] ![640?][640 13] ![640?][640 14] ![640?][640 15]

**本文目录** **CONTENTS**

☞ ETL同步之道 \[ Sqoop、DataX、Kettle、Canal、StreamSets \]

☞ ETL之技术栈 \[ 重工具 vs 开发语言 \]

☞ ETL加载策略 \[ Merge、Delta、拉链 \]

ETL，是英文 Extract-Transform-Load 的缩写，用来描述将数据从来源端经过抽取(extract)、转换(transform)、加载(load)至目的端的过程。

ETL工具或类ETL的数据集成同步工具或语言，企业生产中工具也非常之多，主流的etl工具有Sqoop、DataX、Canal、flume、Logstash、kettle、DataStage、Informatica、Talend等，语言有强悍的SQL、Shell、Python、Java、Scala等。而数据源多为业务系统，埋点日志，离线文件，第三方数据等。

## **数据同步之道** ##

### **01. sqoop** ###

Sqoop，SQL-to-Hadoop 即 “SQL到Hadoop和Hadoop到SQL”。

是Apache开源的一款在Hadoop和关系数据库服务器之间传输数据的工具。主要用于在Hadoop与关系型数据库之间进行数据转移，可以将一个关系型数据库（MySQL ,Oracle等）中的数据导入到Hadoop的HDFS中，也可以将HDFS的数据导出到关系型数据库中。

sqoop命令的本质是转化为MapReduce程序。sqoop分为导入（import）和导出（export），策略分为table和query，模式分为增量和全量。

![640?wx\_fmt=png][640_wx_fmt_png]

命令简单示例：

![640?wx\_fmt=png][640_wx_fmt_png 1]

### **02. DataX** ###

DataX 是阿里巴巴集团内被广泛使用的离线数据同步工具/平台，实现包括 MySQL、Oracle、SqlServer、Postgre、HDFS、Hive、ADS、HBase、TableStore(OTS)、MaxCompute(ODPS)、DRDS 等各种异构数据源之间高效的数据同步功能。

github地址：https://github.com/alibaba/DataX

支持数据源：

![640?wx\_fmt=png][640_wx_fmt_png 2]

DataX本身作为离线数据同步框架，采用Framework + plugin架构构建。将数据源读取和写入抽象成为Reader+Writer插件，纳入到整个同步框架中。

目前已到datax3.0框架设计：

![640?wx\_fmt=png][640_wx_fmt_png 3]

datax使用示例，核心就是编写json配置文件job：

![640?wx\_fmt=png][640_wx_fmt_png 4]

### **03. kettle** ###

Kettle，中文名：水壶，是一款国外免费开源的、可视化的、功能强大的ETL工具，纯java编写，可以在Windows、Linux、Unix上运行，数据抽取高效稳定。

Kettle家族目前包括4个产品：Spoon、Pan、CHEF、Kitchen。

Kettle的最大特点：

*  免费开源：基于Java免费开源软件
 *  易配置：可跨平台，绿色无需安装
 *  不同数据库：ETL工具集，可管理不同数据库的数据
 *  两种脚本文件：transformation和job，transformation完成针对数据的基础转换，job则完成整个工作流的控制
 *  图形界面设计：托拉拽，无需写代码
 *  定时功能：在Job下的start模块，有一个定时功能，可以每日，每周等方式进行定时

![640?wx\_fmt=png][640_wx_fmt_png 5]

福利：需要Kettle资源教程视频的同学，公众号后台回复关键字：kettle，即可获取哦~

### **04. canal** ###

canal是阿里巴巴旗下的一款开源项目，纯Java开发。基于数据库增量日志解析，提供增量数据实时订阅和消费，目前主要支持了MySQL，也支持mariaDB。

很多大型的互联网项目生产环境中使用，包括阿里、美团等都有广泛的应用，是一个非常成熟的数据库同步方案，基础的使用只需要进行简单的配置即可。

github地址：https://github.com/alibaba/canal

当前的 canal 支持源端 MySQL 版本包括 5.1.x , 5.5.x , 5.6.x , 5.7.x , 8.0.x

![640?wx\_fmt=png][640_wx_fmt_png 6]

canal是通过模拟成为mysql 的slave的方式，监听mysql 的binlog日志来获取数据，binlog设置为row模式以后，不仅能获取到执行的每一个增删改的脚本，同时还能获取到修改前和修改后的数据，基于这个特性，canal就能高性能的获取到mysql数据数据的变更。

### **05. StreamSets** ###

Streamsets是一个大数据实时采集ETL工具，可以实现不写一行代码完成数据的采集和流转。通过拖拽式的可视化界面，实现数据管道(Pipelines)的设计和定时任务调度。

数据源支持MySQL、Oracle等结构化和半/非结构化，目标源支持HDFS、Hive、Hbase、Kudu、Solr、Elasticserach等。创建一个Pipelines管道需要配置数据源(Origins)、操作(Processors)、目的地(Destinations)三部分。

Streamsets的强大之处：

*  拖拽式可视化界面操作，No coding required 可实现不写一行代码
 *  强大整合力，100+ Ready-to-Use Origins and Destinations，支持100+数据源和目标源
 *  可视化内置调度监控，实时观测数据流和数据质量

![640?wx\_fmt=png][640_wx_fmt_png 7]

![640?wx\_fmt=png][640_wx_fmt_png 8]

## **二、ETL之技术栈** ##

### **2.1 工具** ###

重工具，kettle、DataStage、Informatica 三大工具依旧牢牢稳固传统数仓三大主力位置。kettle与时俱进，在大数据数仓，如一些互联网公司也有在使用kettle。

工具本文不再多做介绍。

### **2.2 语言** ###

开发语言，传统数仓一般SQL/Shell为主，互联网数仓又对Python、Java、Scala提出了新的要求。

不管是传统数仓，还是基于Hadoop生态的构建的(hive、spark、flink)数仓，SQL虽然戏码在下降，但依然是重头戏。强大的存储过程，更是屹立不倒，这么多年都在熠熠生辉。

善于发现的你，一定会发现，在大数据生态，不管哪种数据处理框架，总有一天都会孵化出强大SQL的支持。如Hive SQL，Spark SQL，Blink SQL 等。此时，你或许会得出一个结论：

**SQL是最好的语言！****（不接受反驳。****。）**

对于SQL，基本技能也是必备技能。

各种join、嵌套/标量子查询，强大的分析/窗口函数，变化无穷的正则表达式，层次查询，扩展分组，MODEL，递归with，多维分析，排列组合，行列互转，json解析，执行计划，四大类型(dql、dml、ddl、dcl)等，依然需要每个etl·er熟悉掌握。

**SQLjoin**，left/rignt/full join，每一个join都是暗藏韵理，on和where也不容小觑。

![640?wx\_fmt=png][640_wx_fmt_png 9]

**分析函数** 简捷高效，4类30+个分析/窗口函数最全总结，感兴趣的同学请移步： （点击链接即可）。

SQL开发规范和**执行计划**也需要每个erl·er在实际实践中不断加强、提炼、升级。

**SQL开发规范** 示例：

![640?wx\_fmt=png][640_wx_fmt_png 10]

如果你还在传统数仓领域，如果你还想将薪比薪，建议赶紧开始学**Java、scala**，拥抱大数据生态Hadoop/Spark/Flink，机会总是垂青有准备的人。

## **三、ETL加载策略** ##

数据集成加载策略，按类型可包括快照、流水、增量、全量、拉链等。

### **01. 增量** ###

有些表巨大，我们需要选择增量策略，新增delta数据需要和存量数据merge合并。欣赏并学习两种处理方式，直接上图，可斟酌体会：

（如果看图不能理解，来数仓ers群里，加入高手如云，我们一起探讨！加小助微：iom1128，备注：ETL）

**Merge方式（一）**

![640?wx\_fmt=png][640_wx_fmt_png 11]

*图片来源于《阿里巴巴大数据之路》*

**Merge方式（二）**

1）只有新增数据

![640?wx\_fmt=png][640_wx_fmt_png 12]

2）新增+删除

![640?wx\_fmt=png][640_wx_fmt_png 13]

*图片来源于《美团技术》*

### **02. 全量** ###

每天一个全量表，也可一个分区一个全量。

### **03. 拉链** ###

拉链表，记录数据生命周期，记录一条数据生命周期的开始和结束。

建议在设计拉链表的时候不仅要有开始时间和结束时间，最好再加一个生命状态字段，如chain\_status：有效 active、失效 expired、历史 history。

回想一下前面文章介绍的缓慢变化维，可类比SCD的TYPE2，有异曲同工之处

全量拉链，或许会存在性能问题，故建议根据实际业务场景中进行取舍，可只和最近一个时间周期(eg:1个月)的进行拉链处理。

## **四、结束语** ##

学思想，数据思维；

学Java，走大数据；

学实时，不怕被淘汰！

![640?][640 16]

![640?wx\_fmt=png][640_wx_fmt_png 14]

猜你

喜

欢

*  
 *  
 *  
 *  
 *

![640?wx\_fmt=png][640_wx_fmt_png 15]

福

利

时刻

01. 公众号后台回复「06」获取「数据仓库」「数据治理」等经典大数据和数据仓库电子书或视频赠送。

02. 如要获取《大牛带你从0到1建设数据仓库》实战高清PPT或数仓实战视频，请关注公众号后添加小助手微信\[ ID**：**iom1128昵称：紫霞仙子\]，备注：PPT。

03. 高手如云，拉您入群「数据」，「Python」、「资料分享」，公众号后台回复：加群。技术大佬们在等你，各种资源定期分享~

Q: 关于数据仓库，你还想了解什么？

欢迎留言区与大家分享

觉得不错，请把这篇文章分享给你的朋友哦

投稿请联系小助手：iom1128『紫霞仙子』

更多精彩，请在后台点击“紫霞秘籍”查看

![640?wx\_fmt=jpeg][640_wx_fmt_jpeg]

*！**关注不迷路~ 各种福利、资源定期分享**！*

[640_wx_fmt_gif]: /images/20230531/bc31386b16b842abb2420f2d088b1a34.png
[640]: https://img-blog.csdnimg.cn/img_convert/aabff94c382f27edd93b379420f06f56.gif
[640 1]: https://img-blog.csdnimg.cn/img_convert/ca5141b8d3c3f90edd2e6c4855801949.gif
[640 2]: https://img-blog.csdnimg.cn/img_convert/baf59f19798b75871e5554e800a04d4b.gif
[640 3]: https://img-blog.csdnimg.cn/img_convert/62e220dfa8abaf3cf660576379cb689a.gif
[640 4]: https://img-blog.csdnimg.cn/img_convert/e71379ef4813dcb4e171e8f2dab065c4.gif
[640 5]: https://img-blog.csdnimg.cn/img_convert/dc6a4775b834fe0ad9389642b2986c65.gif
[640 6]: https://img-blog.csdnimg.cn/img_convert/8f9b6713338f1574164b47dde9c032fc.gif
[640 7]: /images/20230531/262202230c414825833f07fb4eacd46e.png
[640 8]: https://img-blog.csdnimg.cn/img_convert/f72c31f597f75be3531daaaa670a993d.gif
[640 9]: https://img-blog.csdnimg.cn/img_convert/2392a16208f4b9580d8ee5be9cc4a108.gif
[640 10]: https://img-blog.csdnimg.cn/img_convert/e09e441b97d9e6c7f51101b47bd1330a.gif
[640 11]: https://img-blog.csdnimg.cn/img_convert/8c76345e6c2226efb44dbf31d539c814.gif
[640 12]: https://img-blog.csdnimg.cn/img_convert/447aa2a2c45729807a8e1ad56dbf61ce.gif
[640 13]: https://img-blog.csdnimg.cn/img_convert/fa0d47850a35ae4deda1598e19b1b5dc.gif
[640 14]: https://img-blog.csdnimg.cn/img_convert/dcac5df90a0dbde753908a1286f8c253.gif
[640 15]: /images/20230531/57085183f54d489d99ddf4c6e3ded4ca.png
[640_wx_fmt_png]: /images/20230531/20d7c5fc13af4cdf88b1fd1be08ad433.png
[640_wx_fmt_png 1]: /images/20230531/b35d729c58c946ccab84c13e9bf0316a.png
[640_wx_fmt_png 2]: /images/20230531/fb0d3505caaa43f8bbb90dcc2c7639c0.png
[640_wx_fmt_png 3]: /images/20230531/931fd0c2c6244aeb994d4938fc1979b6.png
[640_wx_fmt_png 4]: /images/20230531/88fdc3f6fcef4763ba15039fce6c0812.png
[640_wx_fmt_png 5]: /images/20230531/182a1fa653ce4ade98f38140a3d24493.png
[640_wx_fmt_png 6]: /images/20230531/40e9957337de4acf9a0fe18aadadb2f8.png
[640_wx_fmt_png 7]: /images/20230531/afa95b80923042d299b76dc318a48cde.png
[640_wx_fmt_png 8]: /images/20230531/afaedd351a2342c186a9deee31d0f0c3.png
[640_wx_fmt_png 9]: /images/20230531/ffdc310e24d249f19d9fcc7140f20936.png
[640_wx_fmt_png 10]: /images/20230531/ba3d028c479b4de48b3feb4d6f246d63.png
[640_wx_fmt_png 11]: /images/20230531/f3adab56c379499182235aa27baac294.png
[640_wx_fmt_png 12]: /images/20230531/41c9edf76cc04b0195dfeab7ff4cfd42.png
[640_wx_fmt_png 13]: /images/20230531/17391153bcf341d4b709a906e9132b8e.png
[640 16]: /images/20230531/a1334c6067bb43fa8b049ab0f92c0b4f.png
[640_wx_fmt_png 14]: /images/20230531/7f892856ba90438d9dda5ea9be91d12f.png
[640_wx_fmt_png 15]: /images/20230531/18c0935b8ec145229eb16be15fa1e7b1.png
[640_wx_fmt_jpeg]: /images/20230531/2da35bca4bf3492c93d4b6a0bd0463fc.png