目录
1、 数据ETL
ETL,Extraction- Transformation-Loading的缩写,即数据抽取(Extract)、转换(Transform)、装载(Load)的过程,它是构建数 据仓库的重要环节。ETL是将业务系统的数据经过抽取、清洗转换之后加载到数据仓库的过程,目的是将企业中的分散、零乱、标准不统一的数据整合到一起,为企业的决策提供分析依据。ETL是BI项目重要的一个环节。通过ETL,我们可以基于源系统中的数据来生成数据仓库。ETL为我们搭建了OLTP系统和 OLAP系统之间的桥梁,是数据从源系统流入数据仓库的通道。通常情况下,在BI项目中ETL会花掉整个项目的1/3的时间,ETL设计的好坏直接关接到 BI项目的成败。
创建ETL系统的时候,头脑里应并存的两条主线:规划&设计主线和数据流主线
规划&设计主线:需求和实现===〉架构===〉系统实施===〉测试和发布
数据流主线:抽取===〉清洗====〉规格化====〉提交
手工编码还是使用ETL工具ETL
实现方式一般有以下3种:一种是借助ETL工具实现,一种是SQL方式实现,另外一种是ETL工具和SQL相结合。前两种方法各有各的优缺点,借助工具可以快速的建立起ETL工程,屏蔽了复杂的编码任务,提高了速度,降低了难度。SQL的方法优点是灵活,提高ETL运行效率,但是编码复杂,对技术要求比较高。第三种是综合了前面二种的优点,会极大地提高ETL的开发速度和效率。
批处理式数据流还是流式数据流
ETL系统的标准架构是从数据源中周期性的以批处理的方式进行抽去数据,流经整个系统,最后以批处理的方式对最终用户表进行批量更新。大部分数据仓库主要基于此种架构方式。如果数据仓库的加载的实时性变得很急迫,批处理的方式就会被打破,替代的方法就是流式数据流,记录级数据从原系统不停的流向最终用户数据库和屏幕。
批处理到流处理方式的转变会改变所有的一切,尽管必须也有抽取,清晰,转换和提交步骤,但是这些步骤必须经过修改,以便适应实时性记录处理需求,尤其是对于快速流方式而言,很多关于数据到达甚至参照完整性方面的常规假设都必须进行修改。
水平任务依赖还是垂直任务依赖
水平方式组织任务流是指每个最终的数据库加载相互独立运行,因此,如果有订货和配送两项任务,这两项数据库加载任务会相互独立运行,这通常意味着抽取,清洗,转换和提交的步骤在两个工作流之间是非同步的。
垂直方式任务流会对多个离散的作业进行同步,这样最终的数据库加载会同步进行。尤其是多个系统使用共同的维表的时候,比如客户或者供应商等,之前的步骤一定要同步,这样,如果之前的步骤没有完全执行完,后面的步骤,比如转换或者提交就不会往下进行。
恢复和重启
从 一开始建立ETL系统的时候,就应当考虑如何保证系统从非正常结束状态下的恢复和重 启能力。比如有一个ETL作业是从全部的产品种类中抽取某个品牌产品的销售业绩,这样的任务不允许执行两次。在设计每一个ETL作业时都需要这样来考虑问 题,因为每个作业迟早都会出现非正常终止或者错误地执行多次的情况。无论如何,必须想办法防止发生这种情况。
元数据
来自于关系型数据库表和数据模型设计工具的元数据比较容易获取,但这些元数据可能只占系统全部元数据的25%。还有25%的元数据会在数据清洗过程中产生。 对于ETL小组而言,最大的元数据难题是在哪里以及以何种方式存储流程信息。ETL工具的一个重要的优势在于它们能够自动维护流程元数据。如果是使用手写 编写ETL系统,用户则必须构建流程元数据的中央资料库。
除此之外,自动调度、异常处理、质量控制和安全也是ETL架构设计中不可忽视的环节。
抽取
这一部分需要在调研阶段做大量的工作,首先要搞清楚数据是从几个业务系统中来,各个业务系统的数据库服务器运行什么DBMS,是否存在手工数据,手工数据量有多大,是否存在非结构化的数据等等,当收集完这些信息之后才可以进行数据抽取的设计。源系统的原始数据在进行大的转换之前通常直接写入到磁盘。来自于结构化源系统的数据(比如IMS数据库,或者XML数据集)在这一步中经常写入到文本文件或者关系型数据库表中。这使得最初的抽取尽可能简单和快速。
对于源数据的不同来源,有以下不同的实现方法
1、对于与存放DW的数据库系统相同的数据源处理方法
这一类数据源在设计上比较容易。一般情况下,DBMS(SQLServer、Oracle)都会提供数据库链接功能,在DW数据库服务器和原业务系统之间建立直接的链接关系就可以写Select语句直接访问。
2、对于与DW数据库系统不同的数据源的处理方法
对于这一类数据源,一般情况下也可以通过ODBC的方式建立数据库链接——如 SQLServer和Oracle之间。如果不能建立数据库链接,可以有两种方式完成,一种是通过工具将源数据导出成.txt或者是.xls文件,然后再 将这些源系统文件导入到ODS(Operating Data Source)中。另外一种方法是通过程序接口来完成。
3、对于文件类型数据源(.txt,.xls),可以利用数据库工具将这些数据导入到指定的数据库,然后从指定的数据库中抽取。或者还可以借助工具实现,如SQLServer2005的SSIS服务的平面数据源和平面目标等组件导入ODS中去。
4、增量更新的问题
对于数据量大的系统,必须考虑增量抽取。典型的做法是通过在ODS上建立时间戳以作为抽取记录,那么下次抽取时只要对比时间戳的抽取记录即可判断出增量数据。
数据清洗
数据清洗的任务是过滤那些不符合要求的数据,将过滤的结果交给业务主管部门,确认是否过滤掉还是由业务单位修正之后再进行抽取。不符合要求的数据主要是有不完整的数据、错误的数据、重复的数据三大类。
大多数情况下,源系统可接受的数据质量程度依据数据仓库要求的质量而不同。数据质量的处理可能包括几个独立的步骤,包括有效值检测(如是否是已有的邮政编 码?是否在有效值范围内?)、一致性检测(如邮政编码与城市代码是否一致?)、删除重复记录(如是否有同一个客户出现两次而相关的属性略有不同?)、检测 是否有复杂的业务规则和过程需要增强(如白金客户是否有相关的信用状态?)等等。数据的清洗转换可能需要人为的干预和判断。数据清洗步骤的结果往往半永久 保存,因为需要的转换往往难度非常大,并且是不可逆的。另外,清洗过的数据是否需要返回到源系统以提高数据质量,从而减少抽取时可能发生的问题呢?这是个 很有趣的问题。即使清洗过的数据不能物理返回到源系统,也应当具备数据异常报告机制以提高源系统的质量。这些数据的问题在最终的商务智能应用中也是非常重要的。
数据转换
数据转换的任务主要进行不一致的数据转换、数据粒度的转换,以及一些商务规则的计算。
1、不一致数据转换:这个过程是一个整合的过程,将不同业务系统的相同类型的数据统一,比如同一个供应商在结算系统的编码是XX0001,而在CRM中编码是YY0001,这样在抽取过来之后统一转换成一个编码。
2、数据粒度的转换:业务系统一般存储非常明细的数据,而数据仓库中数据是用来分析的,不需要非常明细的数据。一般情况下,会将业务系统数据按照数据仓库粒度进行聚合。
3、商务规则的计算:不同的企业有不同的业务规则、不同的数据指标,这些指标有的时候不是简单的加加减减就能完成,这个时候需要在ETL中将这些数据指标计算好了之后存储在数据仓库中,以供分析使用。
数据装载
数据装载也叫提交。后台任务的终点就是准备好数据以方便查询。这一步骤中至关重要的是将数据物理地组织成简单、对称的框架模型,我们称之为维度模型,或者星型模型。这种框架大大地降低了查询时间,简化了开发过程。许多查询工具都需要维度框架,也是构建OLAP立方体的必要的基础。
2、 什么是Azure数据工厂
Azure 数据工厂是为这些复杂的混合提取-转换-加载 (ETL)、提取-加载-转换 (ELT) 和数据集成项目而构建的托管云服务。它是基于云的 ETL 和数据集成服务,可让你创建数据驱动型工作流用于大规模协调数据移动和转换数据。 可以使用 Azure 数据工厂创建和计划数据驱动型工作流(称为管道),以便从不同的数据存储引入数据。 可以构建复杂的 ETL 流程,用于通过数据流或使用 Azure HDInsight Hadoop、Azure Databricks 和 Azure SQL 数据库等计算服务直观转换数据。此外,还可以将转换的数据发布到数据存储(例如 Azure SQL 数据仓库),供商业智能 (BI) 应用程序使用。 最终,通过 Azure 数据工厂,可将原始数据组织成有意义的数据存储和数据湖,以实现更好的业务决策。
工作原理
数据工厂包含一系列为数据工程师提供完整端到端平台的互连系统。
连接和收集
企业拥有各种类型的数据(位于云、结构化、非结构化和半结构化的本地分散源中),都以不同的时间间隔和速度到达。
构建信息生产系统时,第一步是连接到所有必需的数据和处理源(例如软件即服务 (SaaS) 服务、数据库、文件共享、FTP Web 服务)。 下一步是根据需要将数据移至中央位置进行后续处理。
没有数据工厂,企业就必须生成自定义数据移动组件或编写自定义服务,以便集成这些数据源并进行处理。 集成和维护此类系统既昂贵又困难。 另外,这些系统通常还缺乏企业级监视、警报和控制,而这些功能是完全托管的服务能够提供的。
而有了数据工厂,便可以在数据管道中使用复制活动,将数据从本地和云的源数据存储移到云的集中数据存储进行进一步的分析。 例如,可以先将数据收集在 Azure Data Lake Storage 中,以后再使用 Azure Data Lake Analytics 计算服务对数据进行转换。 也可将数据收集在 Azure Blob 存储中,在以后再使用 Azure HDInsight Hadoop 群集对其进行转换。
转换和扩充
将数据保存到云中的集中式数据存储后,使用 ADF 映射数据流来处理或转换收集的数据。 数据工程师可以使用数据流来构建和维护在 Spark 中执行的数据转换图,而无需了解 Spark 群集或 Spark 编程。
如果你偏向于手动编写转换代码,ADF 支持使用外部活动在 HDInsight Hadoop、Spark、Data Lake Analytics 和机器学习等计算服务中执行转换。
CI/CD 和发布
数据工厂完全支持使用 Azure DevOps 和 GitHub 实现数据管道的 CI/CD。 这样,你就可以增量开发和交付 ETL 流程,然后发布成品。 原始数据被优化为业务就绪型可使用的窗体后,请将数据载入 Azure 数据仓库、Azure SQL 数据库、Azure CosmosDB 或业务用户可从其商业智能工具中指向的任何分析引擎。
监视
成功地构建和部署数据集成管道后(提供优化数据的业务值),请监视计划的活动和管道,以了解成功率和失败率。 Azure 数据工厂通过 Azure 门户上的 Azure Monitor、API、PowerShell、Azure Monitor 日志和运行状况面板,对管道监视提供内置支持。
3、 Azure数据工厂概述
一个 Azure 订阅可以包含一个或多个 Azure 数据工厂实例(或数据工厂)。 Azure 数据工厂由四个关键组件组成。 这些组件组合起来提供一个平台,供你在上面编写数据驱动型工作流(其中包含用来移动和转换数据的步骤)。
管道
数据工厂可以包含一个或多个管道。 管道是执行任务单元的活动的逻辑分组。 管道中的活动可以共同执行一项任务。 例如,一个管道可能包含一组活动,这些活动从 Azure Blob 引入数据,然后在 HDInsight 群集上运行 Hive 查询,以便对数据分区。
这样做的好处是,可以通过管道以集的形式管理活动,不必对每个活动单独进行管理。 管道中的活动可以链接在一起来按顺序执行,也可以独立并行执行。
映射数据流
创建和管理可用于转换任意大小的数据的数据转换逻辑图。 可以构建可重用的数据转换例程库,并通过 ADF 管道以横向扩展方式执行这些流程。 数据工厂将在可按需扩展和缩减的 Spark 群集上执行逻辑。 你根据不需要管理或维护群集。
活动
活动表示管道中的处理步骤。 例如,可以使用复制活动将数据从一个数据存储复制到另一个数据存储。 同样,可以使用在 Azure HDInsight 群集上运行 Hive 查询的 Hive 活动来转换或分析数据。 数据工厂支持三种类型的活动:数据移动活动、数据转换活动和控制活动。
数据集
数据集代表数据存储中的数据结构,这些结构直接指向需要在活动中使用的数据,或者将其作为输入或输出引用。
链接服务
链接服务十分类似于连接字符串,用于定义数据工厂连接到外部资源时所需的连接信息。 不妨这样考虑:链接服务定义到数据源的连接,而数据集则代表数据的结构。 例如,Azure 存储链接服务指定连接到 Azure 存储帐户所需的连接字符串。 另外,Azure Blob 数据集指定 Blob 容器以及包含数据的文件夹。
数据工厂中的链接服务有两个用途:
代表 数据存储。此类存储包括但不限于本地 SQL Server 数据库、Oracle 数据库、文件共享或 Azure Blob 存储帐户。
代表可托管活动执行的计算资源。 例如,HDInsightHive 活动在 HDInsight Hadoop 群集上运行。
数据工厂可以包含一个或多个数据管道。 “管道”是共同执行一项任务的活动的逻辑分组。 例如,管道可能包含一组活动,这些活动用于引入和清理日志数据,然后启动映射数据流来分析日志数据。 使用管道可以将活动作为一个集来管理,而不是分别管理每个活动。 您可以独立部署和计划管道,而不是单独部署和计划。
管道中的活动定义对数据执行的操作。 例如,可使用复制活动将数据从本地 SQL Server 复制到 Azure Blob 存储。 然后,使用数据流活动或 Databricks 笔记本活动处理数据,并将其从 blob 存储转换到 Azure Synapse 分析池,该池位于构建商业智能报表解决方案之上。
数据工厂包含三组活动:数据移动活动、数据转换活动和控制活动。 每个活动可获取零个或多个输入数据集,并生成一个或多个输出数据集。
输入数据集表示管道中活动的输入,输出数据集表示活动的输出。数据集可识别不同数据存储(如表、文件、文件夹和文档)中的数据。创建数据集后,可将其与管道中的活动一起使用。例如,数据集可以是复制活动或HDInsightHive活动的输入/输出数据集。
数据移动活动
数据工厂中的复制活动可以将数据从源数据存储复制到接收器数据存储。来自任何源的数据都可以写入到任何接收器。
数据移动活动中支持多种数据源,除了Azure本事支持的数据存储及数据库服务以外,还支持像Amazon Redshift、GoogleBigQuery、S3等云厂的数仓服务,另外也支持传统的数据库例如DB2、Oracle、MySQL、Hive、Hbase、MongoDB等;甚至是一些应用例如Saleforce、office365、SAP、Dynamics等。支持的接收器主要是Azure上的存储及数据库服务,另外有少部分第三方数据及应用。作为Extract和Load工具来说ADF可支持的数据服务类型相当丰富。
数据转换活动
你可以使用这些活动将原始数据转换和处理为大规模预测和见解。 在计算环境(如 Azure Databricks 或 Azure HDInsight)中执行转换活动。
主要分为两种方式,一是数据流转换,使用ADF自带的数据流处理,生成的数据流将作为使用扩展 Spark 群集的 Azure 数据工厂管道中的活动执行。二是外部转换,使用外部计算资源对数据集进行处理,支持HDInsight、Azure 机器学习 Web 服务、SQL Server存储过程、Databricks等。
控制流活动
控制所有管道和活动的执行,定义活动执行时的参数,监控活动的执行过程,定义不同触发计划等。作为作业调度的管理方式。
链接的服务类似于连接字符串,它定义数据工厂连接到外部资源时所需的连接信息。 不妨这样考虑:数据集代表链接的数据存储中的数据结构,而链接服务则定义到数据源的连接。 例如,Azure 存储链接服务可将存储帐户链接到数据工厂。 Azure Blob 数据集表示 blob 容器以及包含要处理的输入 blob 的 Azure 存储帐户的文件夹。
下面是一个示例方案。 要将数据从 Blob 存储复制到 SQL 数据库,请创建两个链接服务,即 Azure 存储和 Azure SQL 数据库。 然后创建两个数据集:Azure Blob 数据集(即 Azure 存储链接服务)和 Azure SQL 表数据集(即 Azure SQL 数据库链接服务)。 Azure 存储和 Azure SQL 数据库链接服务分别包含数据工厂在运行时用于连接到 Azure 存储和 Azure SQL 数据库的连接字符串。 Azure Blob 数据集指定 blob 容器和 blob 文件夹,该文件夹包含 Blob 存储中的输入 blob。 Azure SQL 表数据集指定要向其复制数据的 SQL 数据库中的 SQL 表。
数据集这一名称的意义已经变为看待数据的一种方式,就是以输入和输出的形式指向或引用活动中要使用的数据。 数据集可识别不同数据存储(如表、文件、文件夹和文档)中的数据。 例如,Azure Blob 数据集可在 Blob 存储中指定供活动读取数据的 Blob 容器和文件夹。在数据集中定义数据集的名称,数据集类型指定数据工厂支持的类型之一(例如:AzureBlob、AzureSqlTable),数据集的架构也就是元数据,每种类型(例如 Azure Blob、Azure SQL 表)的类型属性各不相同。
可以简单理解为ADF中的一种计算资源,使用Spark集群对数据进行转换活动的计算。
映射数据流是在 Azure 数据工厂中以可视方式设计的数据转换。 数据流允许数据工程师开发图形数据转换逻辑,而无需编写代码。 生成的数据流将作为使用扩展 Spark 群集的 Azure 数据工厂管道中的活动执行。 数据流活动可以通过现有的数据工厂计划、控制、流和监视功能来操作化。
映射数据流提供完全直观的体验,无需编码。 数据流将在自己的执行群集上运行,以便进行扩展的数据处理。 Azure 数据工厂处理所有代码转换、路径优化和数据流作业的执行。
组织需要进行数据准备和整理,以便准确分析每天持续增长的复杂数据。 需要进行数据准备,以便组织可以使用各种业务流程中的数据并缩短价值。
通过 Azure 数据工厂中的整理数据流,可以在云规模上以迭代方式进行无代码的数据准备。 整理数据流与Power Query Online集成,使 Power Query M 函数可供数据工厂用户使用。
整理数据流将 Power Query Online 混合编辑器生成的 M 转换为用于云缩放执行的 spark 代码。整理数据流对数据工程师或 “公民数据集成商” 特别有用。
4、 适用场景
创建现代化数据仓库
数据仓库帮助公司存储大量的历史数据,分析这些数据可以帮助企业更好的理解他们的客户、收入或者其他与业务有关的情况。大多数现在的数据仓库都是建设在企业本地,使用SQL server等技术手段实现。然而现在有更多的企业由于云的存储成本低廉和无限扩展的计算能力将数据仓库迁移上云。但是在建造的过程当中需要通过自动化的手段将数据集成到云环境中。ADF正是为这样的场景设计的。以下是通过ADF pipeline传输和处理数据的示意图。
在这个场景中,数据是从本地的Oracle数据库和Saleforce当中抽取的(步骤1)。数据并不是直接进入数据仓库,而是先进入低价的存储空间例如Blog Storage或者Azure Data Lake。和结构化的数据仓库不同的是数据湖会存储所有raw data,也就是未经过加工的源数据。如果数据是结构化的,则会存储结构化的表格。企业会有大量的数据,无论是结构化的还是非结构化,但并不是所有的数据都有分析的价值,因此先把数据放在廉价的数据湖存储中,再对数据进行梳理后,把有价值的数据放入数据仓库中进行存储是更节省成本的做法。在将数据存储数仓前,我们需要对数据进行去重、转换、筛选等,可以通过HDInsight的Spark集群来并行处理这个过程(步骤2)。在一些情况下会直接将转换过数据直接放入数据仓库中,但也会对转换过的数据做再次处理,比如使用机器学习的算法为数据增加维度,如果处理的数据是用户数据,可以利用机器学习算法对用户进行分类,然后将分类的结果作为标签添加在用户数据中(步骤3)。处理完的数据就可以放入关系型或者非关系型的数据仓库中(步骤4),并对接数据分析工具,例如Power BI,Tableau或者其他工具(步骤5)。
通常数据处理会将数据分为几个层级反复进行处理或分析,这些处理的过程都可以通过ADF进行定义和规划,并调用相关的计算资源进行处理。
为SaaS应用提供数据分析
对于SaaS服务提供商来说数据分析也是尤为重要的。例如社交软件中的推荐算法,为了实现这样的目的需要定时计算大量的数据,然后在应用上使用这些结果。甚至是分析用户的分类情况也需要大量的后端计算能力。使用ADF实现这样的功能如下图所示:
这个场景看似和上面的类似。在这个场景里数据分析结果并不是作为数据仓库,而是作为业务数据实际应用在SaaS服务中。和之前场景不同的是数据不是给到BI分析工具进行数据分析,而是给到SaaS应用服务于通过浏览器或设备访问的用户(步骤5)。