数据挖掘原理与应用.docx

1、数据挖掘原理与应用数据挖掘原理与应用SQL Server 2005 数据库第 12 章 SQL Server 集成服务数据挖掘在典型的数据挖掘项目中，最消耗资源的步骤是数据准备。创建和调整挖掘模型可能只 占全部项目工作量的 20。然而，在创建这些模型之前，数据的格式必须是正确的。数据准 备由多个步骤组成，包括数据收集、数据清理和数据转换。我们可以使用 SQL 脚本来准备数据，但是用来准备数据的更好的工具是 SQL Server集成服务(SSIS)，该工具以前称为数据转 换服务(Data Transformation Services, DTS)。SSIS提供了工作流的环境，可以使用该环境来 构

2、建数据转换包。可以从不同的数据源中提取数据，然后对该数据执行一系列操作。在 SSISToolbox 中以组件的形式预定义和提供了这些操作。在对数据进行转换后，可以直接在 SSIS环境中处理数据挖掘模型或者执行预测查询。在本章中，首先将介绍 SSIS然后将论述如何在 SSIS环境中执行数据挖掘的任务。在 本章中，将学习：SSIS的基本概念，包括控制流和数据流在SSIS中执行与数据挖掘相关的转换和任务，包括这些转换和任务的用法基于术语“提取转换”和术语“查找转换”的文本挖掘解决方案12.1SSIS 介绍SSIS首先是在1997年的SQL Server 7.0中引入的，当时它的名称为数据转换服务(D

3、TS)。 SSIS 属于 ETL 产品家族，ETL 代表提取(Extraction)、转换(Transformation)和加载(Loading)。现在，越来越多的企业都有数据仓库。 ETL 是将来自 OLTP 数据库的数据定期加载到数 据仓库中必不可少的工具。在 SQL Server的前两个版本 SQL Server 7.0和SQL Server 2000中，SSIS主要集中于提取和加载。通过使用 SSIS,可以从任何数据源中提取数据以及将数据加载到任何数据源中。在 SQL Server 2005中，对SSIS进行了重新设计和改进。SSIS 提供控制流和数据流。控制流也称为工作流或者任务流，

4、它更像工作流，在工作流中每个组 件都是一个任务。这些任务是按预定义的顺序执行的。在任务流中可能有分支。当前任务的 执行结果决定沿哪条分支前进。数据流是新的概念。数据流也称为流水线，主要解决数据转换的问题。数据流由一组预定义的转换操作组成。数据流的起点通常是数据源 （源表）；数据流的终点通常是数据的目的地（目标表）。可以将数据流的执行认为是一个流水线的过程，在 该过程中，每一行数据都是装配线中需要处理的零件，而每一个转换都是装配线中的处理单 丿元。图12-1显示了 SSIS设计 器。可以通过启动 Busin ess In tellige nee Developme nt Studio 然 后创建

5、一个Integration Services项目来调用SSIS设计器。在左边有一个工具箱窗口。工具箱 窗口包含预定义的控制流任务和数据流转换。中间的视图窗格包含 4个视图：控制流、数据流、事件处理程序和包资源管理器。控制流视图提供了一个设计环境，在这个设计环境中可 以使用工具箱中与控制流相关的项来构建控制流。数据流视图也提供了一个设计环境，在这 个设计环境中可以使用工具箱中与数据流相关的项来构建数据流。在事件处理程序视图中， 可以定义由特定的执行事件触发的后续响应操作。包资源管理器视图提供了流的树型视图。2询目丿二赫：f - 1屮當I fzll J| L/i ht i | Ut | 5 Q /

6、 爭詡 d II R图12-1 SSIS设计 器12.1.1理解 SSIS 包包（package）是 SSIS项目中基本的部署和执行单元。SSIS任务执行的所有工作都发生在包的上下文中。SSIS包是SSIS流的容器。可以通过以下方式来创建 SSIS包：在Integration Services项目文件夹中右击“ SSIS包”文件夹，然后选择“新建SSIS包”菜单项。SSIS项目 可以包含多个包。包只包含一个控制流，而该控制流可以包含一个或者多个数据流。除了控 制流和数据流，包还包含了 SSIS连接和包的变量。可以有不同作用域的变量，例如，作用域 为整个包的全局变量或者作用域为任务序列的局部变量

7、。 在托盘中显示了连接和变量， 如 SSIS 设计器底部所示（参见图12-1）。连接可以连向关系数据库，也可以连向 Analysis Services数据 库。12.1.2 任务流SSIS包包含一个控制流，该控制流由一组任务组成。这些任务定义了包的工作，例如， 加载数据、执行 SQL 语句、处理脚本或者调用其他过程。在工具箱中列出了这些任务。可以 通过以下方式将任务加入包中：从工具箱中拖动该任务，然后将它释放到包的设计器中。在 任务流中，包通常包含多个任务。多个任务是按优先权约束的顺序来进行组织的。优先权约 束按以下顺序连接两个任务：执行第一个任务的结果决定是否运行第二个任务。可以在工作 流中

8、使用优先权约束来创建条件分支。可以将多个优先权约束进行组合，然后将其作为一个 约束来求值。1. 在 SSIS 中的标准任务表12-1列出了 SSIS任务流中的一些预构建的任务及其描述信息。除了在下表中所列出的任务之外，SSIS还提供了简单的API，可以使用这些API来开发 自己的任务。一旦注册了任务，则在工具箱中将会出现这些任务，然后可以在任务流中像所 有预构建的任务一样使用这些任务。表12-1 SSIS的任务执行包任务 执行进程任务 执行 SQL 任务 文件系统任务 FTP 任务描述将来自文本文件的大量数据加载到 SQL Server 表中 支持在不同类型的数据源之间进行数据的复制和转换。

9、一个数据流任务包含一条数据 流流水线 运行子包将程序或者批处理文件作为包的一部分来运行 在包的执行期间运行 SQL 语句，然后有选择地保存这些查询的结果 执行文件系统操作从远程服务器或者 Internet 的某个位置下载数据文件，并且将这些数据文件作为包的工作流的一部分消息队列任务在 SSIS 包之间使用消息队列来发送和接收消息脚本任务 使用脚本来执行在预构建的 SSIS 任务中不可用的函数。通过使用 Script 任务，可以 在 Microsoft Visual Studio for Applications(VSA) 环境中使用 Visual Basic.NET 来编写脚 本XML 任务

10、合并、过滤和转换 XML 文档中的数据2. 容器容器(container)是向包提供结构的SSIS对象。每个包都有一个容器，用来存储包的流。 包的容器可以包含其他类型的容器，例如序列容器、 Foreach循环容器和For循环容器，这些容器都可以在控制流工具箱中获得。可以在这些容器中定义子流。容器丰富了控制流的运行 时执行模型。例如，并不是所有的任务都是顺序执行的。在 Foreach循环容器内部的子流将会基于容器中定义的迭代器而多次执行。3. 调试对SSIS控制流进行调试是在SQL Server 2005中引入的新特性。当在设计器中选择了一 个任务时，只要按下F9就可以设置断点。通过调试，可以在

11、执行期间检查包的变量的值以及 任务的状态。断点可以基于事件来设置。例如，可以基于一个任务的以下事件来设置一个或 者多个断点：OnPreExecuteOnPostExecuteOnErrorOnWarningOnProgress当SSIS的运行时遇到了一个断点事件时，SSIS的运行时会在包的执行期间暂停。这与 在Visual Studio中调试C#代码类似。4.研究一个控制流示例图12-2显示了一个简单的控制流示例。它以 FTP任务开始，该FTP任务用于下载已压 缩的数据文件。一旦下载完这些文件，运行时就会到达 Foreach 循环容器，在该容器内部有 两个任务：执行进程任务 (该任务调用 un

12、zip 应用程序来解压缩数据文件 )和大容量插入任务 (该 任务将数据文件的数据插入到 SQL Server中)。当解压缩完数据文件并且将数据文件的数据加载到SQL Server中时，循环会停止。如果在循环期间发生了错误，则该控制流将执行发送邮 件任务：向DBA发送电子邮件。如果正确地执行了每个任务，则该控制流将执行 SQL任务:在新的数据集上创建一些视图。断点是在执行 SQL任务的On PreExecute事件上设置的，以便DBA在创建视图前可以对数据加以验证。图12-2控制流的示例12.1.3 数据流控制流是出现较早的概念，因为它从SSIS的第一个版本开始就已经存在了， 而数据流是 在SQ

13、L Server 2005中才引入的新概念。数据流是专门处理数据操作的工作流。 数据流也称为 流水线。可以将数据流认为是装配线，该装配线包含了顺序执行的多个操作。在数据流中的 每个节点都称为转换。数据流通常以源转换开始，以目标转换结束。在这两个转换之间，预 定义的数据流转换被依序应用到数据上。一些转换是同步的，例如，查找、条件性拆分和数 据转换。这些同步的转换可以并行执行。一旦已经将转换应用到数据行上，则下一个转换可 以开始处理该数据行，而无需等到上一级转换处理完整个数据集。一些转换是异步的，例如 聚合和排序。这些转换必须从前面的输出中获得所有的行，从而可以处理和产生输出，用于 后续转换。除了

14、预构建的转换清单，SSIS还提供了一些API，可以使用这些API来构建自己 的数据流转换。例如，可以构建Numeric Processing转换来将数学计算和转换应用到流水线数 据上。数据流总是包含于任务流中。有一个特殊的任务，其名称为数据流任务，该任务是用 于容纳数据流的容器。在构建数据流之前，必须将数据流任务加入到设计器中。1.转换任务是任务流的基本组件，而转换是数据流的基本组件。转换是预定义的数据操作。可以将转换视为装配线中的机器，用于对输入数据进行操作。表 12-2显示了在SSIS数据流环境中流行的转换列表。表12-2 SSIS的转换转换描述聚合”转换“执行聚集，例如 average、

15、sum和count“字符映射表”转换将字符串函数应用到字符数据上“条件性拆分”转换基于指定的标准来将数据行路由到不同的输出“复制列”转换将输入列的副本添加到转换的输出中“数据转换”转换将列的数据类型转换为不冋的数据类型“派生列”转换使用表达式来生成从现有列中派生的新列“处理维度”转换处理 Analysis Services 的维“模糊分组”转换通过以下方式来执行数据清理的任务：首先识别可能重复的数据行，然后选择 规范的数据行来用于对数据进行标准化“模糊查找”转换在引用表中使用模糊匹配来查找值“查找”转换在引用表中使用精确匹配来查找值“合并”转换合并两个已分类的数据集“合并联接”转换使用FULL

16、、LEFT或者INNER join来连接两个已分类的数据集“多播”转换将数据集分布到多个输出中“分区处理”转换处理 Analysis Services 的划分“透视”转换创建规范表的较低规范化版本“排序”转换对流水线的数据进行分类“ Un io n All ”转换创建多个数据集的并集逆透视”转换“创建不规范表的较规范版本2.查看器查看器是为了调试数据流而设计的。可以在执行期间使用查看器来查看流水线中的流。 可以将查看器附加在转换之间的连线上。要增加查看器，可以在连线上右击，然后选择“数 据查看器”菜单项。默认的查看器是网格图。也可以增加直方图，散点图和圆柱图作为图形 查看器。如果这些预定义的查

17、看器还不能满足您的需求，则您甚至可以构建自己的查看器。3.研究一个数据流示例图12-3显示了一个数据流的示例。该数据流以 OLE DBSource转换开始，该转换加载包含零售销售事务的事实表。第二个转换是“查找” ，该转换从客户维表中查找客户姓名中的名和姓。这两列被加入到流水线的数据中。下一个转换是“派生列”，该转换基于姓名中的名和姓来创建新的 Full Name列。然后，数据到达“聚合” 转换，该转换累加每个客户的总销售额。基于销售额，“条件性拆分”转换将客户拆分为两个 目标表：一个表对应重要的客户，而另一个表对应普通的客户。图12-3数据流的示例12.2 在SSIS环境中进行数据挖掘SSI

18、S提供了流的环境，在该环境中可以通过一组内置的任务和转换来进行数据提取、加 载和转换。正如您已经学习的，在数据挖掘项目中最消耗资源的工作是数据清理和转换。 SSIS自然可以作为数据挖掘项目很好的补充。可以使用这个强大的工具来从不同的源中加载数据，将 这些数据连接在一起，规范化列的值，移除无用的记录，替换缺失值，以及将数据分成训练 数据集和测试数据集等。SSIS不仅仅作为数据挖掘的ETL工具，SSIS实际上还在控制流和数据流环境中提供了 一些内置的数据挖掘组件。特别地，在表12-3中列出了一些专门用于数据挖掘的任务和转换。表12-3用于数据挖掘的任务和转换数据挖掘的任务或者转换描 述数据挖掘查询

19、任务基于数据挖掘模型来运行预测查询Analysis Services 处理任务处理需要分析的对象，例如立方体、维、划分和数据挖掘模型Analysis Services 执行 DDL 任务运行数据定义语言 (Data Definition Language，DDL)代码，这些代码可 以创建、修改、删除和处理需要分析的对象数据挖掘查询转换执行数据挖掘预测查询数据挖掘模型定型转换使用流水线数据作为输入来处理数据挖掘模型字词提取转换处理文本列来提取关键术语 (单个词或者较短的名词短语 )。提取的术语可以用来作为字词查找转换的字典字词查找转换从输入的文本列中基于字典来搜索和提取关键术语。字词查找的结果

20、可以用作文本挖掘的训练数据12.2.1 数据挖掘任务在本节中，我们将更详细地介绍一些与数据挖掘相关的任务。1.数据挖掘查询任务数据挖掘查询任务用于执行数据挖掘的查询，主要是执行 SSIS控制流中的预测查询。图12-4显示了对话框的“挖掘模型”选项卡，该对话框用于编辑数据挖掘查询任务的属 性。在该选项卡中，首先将连接指定到活跃的Analysis Services数据库。然后必须指定查询所 基于的挖掘结构和挖掘模型。图12-5显示了数据挖掘查询任务的“查询”选项卡。在该选项卡中，必须输入数据挖掘 查询语句。可以直接在文本框中编写查询语句，也可以单击“生成新查询”按钮来调用预测 查询生成器。预测查询

21、生成器提供了图形化的环境， 在该环境中可以构建DMX查询。在第3章中已经学习了如何使用该工具。yr* -ui.rir.4i.11 ).111 Um 申申 Trt*TH Iifciiv 1-h.-l,3 rt nw*i v*t图12-5数据挖掘查询任务构建查询DMX查询可以包含一些参数。这些参数可以映射到 SSIS变量。例如，您可能希望编写一个查询来返回一些客户，这些客户拥有黄金会员卡的概率超过给定的值。该给定的值可以 通过SSIS变量Probability来设置。“结果集”选项卡提供了一个选项，该选项可用于将查询返回的结果映射到一个 SSIS变量，比如一个集合变量图12-6显示了数据挖掘查询任

22、务的“输出”选项卡。在这个选项卡中，可以指定连接的 目标数据库，在该目标数据库中将保存查询结果。它可以是与源数据相同的数据库，也可以 是不同的数据库。您还需要对结果表进行命名。图12-6数据挖掘查询任务 指定输出注意:DMX查询可以产生嵌套的结果，例如，如果在Select子句中使用PredictHistogram函数, 则数据挖掘查询任务将自动平坦化嵌套行集，然后在一个表中插入查询结果。2.Analysis Service处理任务Analysis Services处理任务用于处理 Analysis Services数据库中的主要对象。这些对象可以是维、立方体、挖掘结构和挖掘模型。图12-7显示

23、了该任务的编辑器。可以使用“添加”按钮来选择对象，也可以在“处理选项”列中指定处理的选项和设置图 12-7 An alysis Services 处理任务在预测查询生成器中执行DMX查询除了 DMX预测查询，还可以在预测查询生成器中执行其他类型的 DMX查询。例如，可以输入以下的内容查询：Select * from myAssociatio nM odel.C on te nt Where Node_Type = 7也可以调用存储过程：Call mystoreproc实际上，也可以在数据挖掘查询任务中指定 DMX的Creation语句和Insert Into语句。可 以使用该任务来创建和处理挖

24、掘模型。3.Analysis Service执行 DDL 任务DDL（数据定义语言）可以采用XML格式来描述。在Analysis Services 2005中，所有的对 象（挖掘模型、挖掘列、维、立方体和角色等等）都是使用DDL来定义的（在第14章中将学习 DDL的基本概念）。除了对象的定义，DDL还包含了一组命令。这些命令使创建、更新、处理和删除对象变 得容易。当使用图形化的工具（例如挖掘模型编辑器）来创建挖掘模型时，这些工具会产生发送给服务器的DDL脚本如果不使用数据挖掘编辑器来创建/更新这些对象，则可以复制或者编写这些 DDL脚本, 然后通过执行DDL任务来执行它们。图12-8显示了执行

25、DDL任务的编辑器。首先，必须指定与Analysis Services的连接。然 后单击SourceDirect属性，该属性调用用来显示 DDL命令的文本框。在图12-8中的DDL用 于在Adventure Works DW数据库中创建挖掘结构 DM Customers。图 12-8 Analysis Services执行 DDL 任务4. 一个使用数据挖掘的控制流示例图12-9显示了使用数据挖掘查询任务的控制流示例。 控制流首先通过FTP任务下载新客 户的数据集，然后使用大容量插入任务将数据加载到 SQL Server数据库中。如果在数据加载期间发生了错误，则它会向DBA发送电子邮件。如果数

26、据加载成功，则它会执行数据挖掘 查询任务，该任务用于查找那些对特殊的促销产品可能感兴趣的客户。预测的结果会保存在 表中。在预测之后，它会给可能感兴趣的客户发送电子邮件。图12-9使用数据挖掘查询的控制流1222 数据挖掘转换在本节中，我们将更详细地论述与数据挖掘相关的转换。1.数据挖掘模型定型转换数据挖掘模型定型转换（简称DM定型转换）用于在数据流环境中处理挖掘结构及其相关 的挖掘模型。An alysis Services处理任务也可以处理挖掘模型，但是必须在任务中指定输入表。在执行期间，AS服务器从存储了训练数据的源表中提取数据。然而，在数据流环境中， DM定型转换在执行期间将数据从流水线推

27、到 Analysis Services AS服务器以推的模式获得训练数据。 在将流水线数据发送给AS服务器之前，DM定型转换以XML行集的格式对该数据进行封装。图12-10显示了 DM定型转换的属性对话框的“连接”选项卡。在该选项卡中，可以指 定到AS Server数据库的连接，然后选择您希望使用流水线数据来处理的挖掘结构。也可以使用“新建”按钮来创建新的挖掘结构。单击该按钮后会启动数据挖掘向导。在 该向导中，对模型创建过程的处理方式与在数据挖掘编辑器环境中的处理方式相同。然而， 还存在一些不同的地方。首先，列是来自流水线，而不是来自关系表。某些特性 （例如，自动检测内容类型和相关建议）不可用

28、。这是因为这些特性需要对输入数据进行扫描和采样，而在 流水线环境中这些操作是不可行的。图12-11显示了 DM定型转换的“列”选项卡，在该选项卡上可以指定流水线的列和挖掘结构的列之间的映射。在默认情况下，该工具会基于列名自动进行映射。提示：虽然DM定型转换支持模型创建，但是对模型进行编辑（比如改变模型的参数）却相当困难。一种解决方案是在相同的解决方案中拥有两个 BI项目：SSIS项目和AS项目。在这种情况下，可以在AS项目中使用挖掘模型编辑器，从而获得对于挖掘模型的完整编辑功能。2.数据挖掘查询转换顾名思义，数据挖掘查询转换（简称DM查询转换）是用于执行数据挖掘查询的转换。在 第2章中，已学习

29、了不同类型的 DMX查询，包括预测、内容、模型创建和模型训练。在这 些查询中，预测查询是最常见的。要执行预测查询，需要训练过的挖掘模型和输入数据集。在流水线环境中，输入数据集 是从流水线推来的。DM查询转换将来自流水线的输入数据封装为 XML行集，然后通过XML/A协议将它们发送给AS服务器。AS服务器执行查询，然后将XML结果返回给该转换。 然后，该转换解析该结果，再将解析后的结果推给流水线，以便下一个转换进行处理。当输 入数据比较大时，该过程会以块的方式来处理：每次将一组行发送给 AS服务器来进行预测。图12-12显示了该转换的“挖掘模型”选项卡，在该选项卡上将连接指定到活跃的 AS服务器

30、的数据库，还必须选择挖掘结构和它的模型之一。图12-12 DM查询转换一一“挖掘模型”选项卡在该转换的“查询”选项卡上可以输入DMX查询。可以手动编写查询，也可以单击“生 成新查询”按钮来启动预测查询生成器。预测的输入表是流水线的输入，表示为InputRowset。DMX查询结果可以包含嵌套的结果，例如，在 Select语句中使用PredictionHistogram函数。在这种情况下，将会自动平坦化嵌套行集在SQL Server 2005中，SSIS数据流本身不支持嵌套行集。这一点会给数据挖掘的训练 和预测增加技术上的挑战，因为它可能需要嵌套的输入，然后产生嵌套的输出。为了解决这 个问题，D

31、M查询转换在有嵌套行集时会自动平坦化结果。然而，对于嵌套的输入，问题更 大。当模型或者查询需要嵌套的输入时，推荐使用 DM定型任务和DM查询任务。3.数据流示例图12-13显示了用于模型训练的数据流示例。它以 OLE DB数据源转换开始，该转换选 择FoodMart Customer表。下一个转换是派生列转换，该转换创建了新的列 Age，该列是从 Birthdate列中派生的。一旦将新的列加入到流水线中，就会将数据发送给 DM定型转换，该 转换并行地处理挖掘结构及其相关的挖掘模型。图12-13用于模型训练的数据流示例图12-14显示了使用DM查询转换的数据流。流水线以 OLE DB源转换开始，该转换加 载NewCustomer表。下一个转换是派生列，该转换添加从 Birthdate列中派生的Age列。第三个转换是DM查询，该转换使用已训练的决策树模型来为每个新的客户预测最有可能拥有 的会员卡。预测的结果会发送给条件性拆分转换，该转换基于会员卡的类型将数据分成 3个流水线分支。左边的分支只包含金卡会员，中间的分支只包含银卡会