SQL 与 ETL | 数据类平台架构

sql 是数据操作的最佳工具（虽然不是唯一），ETL （extract-translate-loading）是数据开发的过程，是数据集市、数据仓库、数据中台数据处理程序的总称。sql 是 实现 ETL 过程的一种重要工具。

架构原型 #

传统的数据集市、数据仓库，以及近些年兴起的数据中台的架构，大多可抽象为这般模样。

认不认同本节内容中的观点并不重要，当项目案例积累到一定数量，对经验进行总结之后，就会发现原来可以这么简单，被人忽悠瘸了。人们学习知识的过程，往往是 “从简到繁，再由繁化简” 的过程。

数据仓库（data warehouse）

数据仓库是一种面向商务智能 (BI) 活动（尤其是分析）的数据管理系统，它仅适用于查询和分析，通常涉及大量的历史数据。在实际应用中，数据仓库中的数据一般来自应用日志文件和事务应用等广泛来源。¹

数据仓库可集中、整合多个信息源的大量数据，借助数据仓库的分析功能，企业可从数据中获得宝贵的业务洞察，改善决策。同时，随着时间推移，它还会建立一个对于数据科学家和业务分析人员极具价值的历史记录。得益于这些强大的功能，数据仓库可为企业提供一个单一信息源。

数据集市（data mart）

数据集市是一种简单的数据仓库，专注于单个主题或业务线。借助数据集市，团队可以更快地访问数据并获取洞察，而不必花时间在更复杂的数据仓库中搜索或从不同的源手动汇总数据。²

源层，指提供业务源数据的区域或层次。如果它是业务管理系统，那么它是日常业务营业活动过程中所生产出来的原始业务数据。如果源指数据仓库，那么它所提供的数据是已经经过标准化的、重新组织的业务数据，不是原始数据。

模型层，根据实际情况，它可能是数据仓库，也可能是具有特定应用目的的数据集市。为了更好的组织数据、以及保留上层所支持应用的结构灵活性、可扩展性，模型层内部往往会进行细分。比如定位为数据仓库时，数据模型会显示更底层化，更贴近业务系统中的原始形态，模型采用主题进行数据分类管理。在模型之上，会考虑当有与未来一段时间可能支持的应用，再提炼一个数据汇总子层次作为共享加工部分的预处理，来减少重复计算；历史数据查询区。

应用层，因为贴近实际的用户端，具体形式多样。可能是有数据消费需求的分析类应用，也可能是一个数据集市。不管是数据集市还是具体的应用，它所需要的数据均来自于模型层。

⚠️ 开卷：

1. 当数据集市处于数据平台的应用层时，数据集市还符合“架构原型”所描述的结构吗？试画看看。

洞察架构的内部 #

源层到模型层、模型层到应用层之间的数据传输，称为数据交换，数据从源到模型、再到应用层的过程，由一系列的数据交互与数据处理程序组成，这些程序称为作业，将作业按照先后执行顺序连接起来，就形成了作业流。这个作业流使用调度工具来统一执行。

对于数据开发的人员，数据处理作业，则是最小的工作交付单元。比如向一张数据表中装载数据、从多个表中取出数据后进行计算并将结果保存到某个位置、卸载某个数据表的数据为文本文件保存到本地某个位置。

实际上，具体的数据平台实施要比上面所提的“架构原型”复杂得多，有非常多的细节。在具体的实践活动中，会采用诸如数据架构、应用架构、部署架构来分别详细说明对应的数据组织、应用结构、拓扑结构。在实施数据平台时，往往是按照层次来组织资源的，建设模型层的团队或人员不一会参与应用层的建设。如一个企业建立数据平台的核心模型层，是为了以后支持上层建设多个数据集市或数据消费应用，此阶段的目标就是设计模型层，并从源层抽取数据，至于上层应用规划仅作为未来的参考。又如构建模型层时，需要熟悉业务的领域建模专家，他（或她）对业务的熟悉程度，影响模型层的质量。除了业务数据基础模型外，模型层还可能包含：

1. 设计预处理用途的汇总区
2. 为了查询历史数据的历史数据区
3. 为了方便数据处理在源与模型之间架设一个技术缓冲层
4. 因数据交换需要，对接数据源时，采用了日终批量、实时同步、外部数据抓取等不同的手段（因数据交换的手段不同，数据的落地方式可能不一样）

实际的数据架构，比本图具有更多的细节，特别包含了模型的顶层设计。

架构的差异，会导致应用架构相应发现变化，影响后期方案的落地。如上图（一个假设的数据平台）与“架构原型”的差别：

1. 数据交换明确了日终、实时、外部抓取
2. 模型层明确了技术缓冲层、传统的业务基础模型、实时的数据服务、历史数据管理
3. 在业务基础模型、实时同步数据之上，进行整合汇总

当然，这样的设计可能是应用层有对应的具体需要。

为了实现数据平台的最大价值，根据该假设所包含数据的特点，我们可以在应用层建设常见的业务汇总统计的报表系统、可以利用实时同步进入的数据，实现流行的实时数据展示（仪表盘、驾驶舱）及业务监控应用、还可以通过积累的历史数据，进行业务预测、营销推荐场景等等。

模型层与应用层之的数据交换根据实际需要进行具体化，如有高时效性数据消费需求的，那么就要求数据进入模型层处理完毕后，及时主动的向应用层推送；如有普通数据消费场景的，可通过一般的日终数据批量同步或者调用数据访问接口来获得数据。

在应用层出现重复性、有复用价值的，可以建设一些公共性质的应用，并以接口或者组件的方式向终端应用提供功能服务，此时会出现一个服务层。多出来的这个层次，它是应用层的内部细分，并未破坏“架构原型”。是否规划这个层次，完全视整体规划而定，与实现应用层所使用的技术、产品有非常大的关系。

一个假设平台的数据架构 #

一个假设平台的技术架构 #

架构上所支持的数据源、下游应用对数据消费有不同的时效性需求，相对于传统数据仓库或者数据集市或者单一的数据类应用来说，数据交换已经从早期的日终批量T+1同步演进到日终批量加载卸载、实时同步入库推送、数据服务接口请求等多种形式。

为满足业务系统数据同步、实时场景数据同步、外部数据抓取，数据交换从传统的日终批量同步，增加实时数据同步、爬虫程序爬取外部数据支持。当前实时数据同步的技术非常多样，数据库日志的CDC、业务结束前回调写入消息队列、实时查询业务系统的只读复制库等等。

当数据进入模型区后，根据区内各主题进行数据填充或者计算。传统数据仓库，最主要就是编写ETL程序完成整个模型的数据加载。本例所假设的，除了传统型数据平台外，还引入了实时的计算，也因此技术架构上需要考虑数据的实时获取、更新、聚合后，推送至下游有实时数据消费场景的应用。同时还要设计一种服务或方式，供下游应用可根据需求请求所消费的数据。

技术架构中的ETL，是数据处理程序的总称。它可以以多种程序类型出现。

总而言之，宏观上，技术架构是考虑如何使得整体规划得以满足；微观上，技术架构是实现数据流的基础保障。

一个假设平台的应用架构 #

这个应用架构很奇怪，没有任何具体的产品。为避广告之嫌，应用架构图没有体现功能产品。

实际项目中的应用规划，会带有详细的产品组合。实现某种单一功能产品的选择并不唯一，架构师会凭借自身过往或者他人的经验选择符合平台建设需求的（功能满足、预算可控）的功能产品，包括开源、商业的第三方产品，一般的，有广泛用户基础的、或者生态圈活跃的产品容易被选中。

从应用架构中，我们可以看到，建设这个假设的平台，需要用到以下这些类型的工具：

1. 适合于OLAP场景的数据库或者大数据存储：MPP数据库或列式关系型数据库，如基于postgres的 greenpulm与openguass、clickhouse，还有耳熟能详的 hadoop。
2. 实时：
   • 监听数据源的变动，抓取增量数据。当前这方面的方案，在互联网企业有广泛应用，方法与工具也比较多。如 kafka、flink等。
   • 计算引擎，在获得到低延时的数据后，一般需要经过及时的计算，并将结果送到下一步骤。这一点与传统日终T+1的数据处理非常不同。如 spark、storm等流式计算平台。
3. ETL工具：数据库所提供支持SQL语言的存储过程、商业的ETL工具。存储过程为数据库提供的SQL与过程语言组成。商业工具有老牌的 DataStage、Informatica，开源的 Kettel。
4. 在应用层，花样就比较多了。有BI引擎与制作数据展现的传统工具，也有面向互联网应用的用户推荐、关系分析、AI预测等。
5. 数据交换：负责系统之间的数据文件传输
6. 调度系统：负责整体ETL程序的执行，即ETL程序的依赖关系呈DAG有向无环图关系。

大数据应用发展非常快速，上面所列工具仅代表某个阶段比较突出的产品，不排除有后来居上者。

一个假设平台的部署架构 #

在这个部署架构中，假设使用了商业ETL工具来进行数据开发、BI工具来实现数据可视化，那么，上述的部署架构也提示了一个最小化数据应用系统数据产品组合。

架构小结 #

一个完整的架构，包含总体架构、数据架构、应用架构、技术架构、部署架构，它们指导数据平台解决方案的落地实施。 数据类平台或应用，除基础数据模型外，还包含数据交换、调度系统、数据服务等软件配套设施，若应用层包含数据展示场景，可能还会涉及BI类工具产品。

⚠️ 开卷：

1. 尝试将你当前的数据类应用，按照本文中架构的形式描述出来，特别是应用架构。
2. 你是否熟悉所在应用架构中的各类工具？

实践方法论 #

我们可以把数据仓库、数据集市称为“数据平台”。在国内，实施这类平台时，不会只是单纯的实施数据仓库、数据集市。比如建设数据仓库时，会附加一些业务功能应用，体现数据价值，以获取业务部门的支持。因此会添加一些表面上看似与数据仓库无关的内容，若非要纠结概念，避免不了掉进牛角钻陷阱。只要在架构上不受破坏，添加一些外围其它的非关键要素又有何所谓呢？

使用更模糊而非更专业的术语称呼，可以让业务部门更容易理解，IT建设部门知道自已要做什么、如何做，把好关即可。从总体上给业务部门讲清楚整个平台的宏观架构，覆盖了哪个业务范围、有哪些功能、支持什么样的场景、能达到什么结果，对促进业务发展能起到什么作用与价值等等。

有悟通常把实施过程规划成如下的路径：

规划阶段，对当前、未来一段时间的应用潜在空间进行适当的前瞻性展望，为整体设计顶层框架，选择符合自身实际需要的工具、规划数据集成、服务集成等。

调研数据平台或数据应用涉及的业务范围、业务系统，了解这些系统之间的接口情况、业务数据质量，形成一定详细程度的调研报告。为系统设计数据模型、存放数据的数据集成区，开发填充这些数据模型的ETL（从数据源将数据有序的同步过来）程序。

基于数据底座，开发具有特定应用场景与支撑运营、用户活动、产品销售、客户管理的一系列数据消费应用。这个过程大体可概括为需求分析、设计、功能开发、数据开发、集成，如果数据展示需求的，还涉及数据展现的开发。这个过程与一般的功能系统实践过程类似。若是数据类的应用，数据开发过程也类似于数据平台的ETL开发过程。

工程师视角 #

实践中，整体规划是由架构师所完成的。参与数据平台、数据类系统应用的建设，除了项目管理人员外，还需要按照使用工具、技能来组织团队资源。此类系统与一般的信息管理系统相比，涉及工具较多，一个团队不太可能人人熟悉掌握这些工具的使用。

• 建设信息管理系统的团队成员要求：后端开发（java、php、c#）、前端UI设计、前端开发（js + html + css )，数据库使用（SQL），应用容器中间件（weblogic、tomcat等）
• 建设数据型应用的团队成员要求：业务分析、模型设计、ETL开发（SQL或ETL工具）、数据展示设计、数据展示数据源模型设计、前端展示开发，相关的应用功能开发（与信息管理系统类似，也有前端后端之分）。

实践中，当数据平台复杂到一定程度时，业务分析、模型设计、ETL程序开发、数据展示页面开发、应用功能开发需要分别成立小组，这些小组对工具的使用、熟悉要求不尽相同。如果一个人能对以上这些方面都达到熟练程度（注意，还不是精通哦），他（或她）一定经过很多年的经验积累。

从数据开发工程师角度看数据平台 #

若非要使用一个词来概括数据开发工程他（或她）在数据平台中的工作，那么没有哪个词比 pipeline（流水线、管道）更合适。如同接水管一样，从业务源系统中的数据导向流入集中式的数据仓库，中间过程经历了层层过滤、混合（形态转换、格式转换、结构转换、聚合计算），直至最后目的应用中灌溉。

也因此，数据开发工程师比较注重ETL过程的数据加载、数据转换、数据卸载、数据计算等环节以及使用到的工具。

特别需要注意的是，数据工程师的主要工作内容是那各种ETL工具打交道，而不是编写各种各样的程序，如果你对编程非常有兴趣，那么一定要自己另外自学。见过不少数据工程师只会熟练编写SQL、使用ETL工具，对于 java、javascript、python非常陌生，更有甚者不会编写简单的 shell 脚本。

从BI工程师角度看数据平台 #

BI工程师负责将业务需求通过可视化的方式展示出来。涉及到业务理解、业务需求的理解、可视化设计、数据支持分析，有些BI工具要求在数据源的基础上进行业务数据建模后，再通过查询模型数据来渲染最终的页面。其实是否需要在BI工具中进行建模，并无准确答案。

如果BI工具连接是的数据集市，那大多数情况下，数据集市的模型设计已经包含了或者非常接近可视化的数据需求，甚至是直接使用，这时没有强烈的BI建模要求；若BI工具直连的业务系统，或者靠近业务系统一端，BI工具的数据建模，为源与可视化数据需求之间提供一个语义转化作用，可减少BI工具映射数据到可视化界面上的复杂度。

BI工程师的工作特点，SQL 是必备技能，通过洞察数据，来回答业务问题，在数据与可视化之间建立映射，着重讲好数据的故事。他（或她）们并没有ETL工程师思维中的那些管道线。

从下游应用建设角度看数据平台 #

对于以数据平台为源的下游数据消费应用，在建设时并不理会数据平台内部是怎么构成的。 建设者只关心彼此之间的边界与接口：

1. 源有什么数据可以使用
2. 源所提供的数据时效性
3. 源以什么方式提供数据
4. 以数据平台为源是否符合规划（不是可以连上管道承接到水就可以，有些系统建设有规范要求）

若你准备建设一个数据集市、或者数据消费应用，以上这些问题同样可以帮助到你。