SQL Server 2005 数据转换服务中的模糊查找和模糊分组

核心提示：本文描述了 SQL Server 2005 数据转换服务 (Data Transformation Services，DTS) 中的模糊查找转换和模糊分组转换，SQL Server 2005 数据转换服务中的模糊查找和模糊分组，模糊分组转换对于在目标数据库提高数据质量很有作用，模糊查找将“有问题&rdquo

本文描述了 SQL Server 2005 数据转换服务 (Data Transformation Services，DTS) 中的模糊查找转换和模糊分组转换。模糊分组转换对于在目标数据库提高数据质量很有作用。模糊查找将“有问题”的输入（由于拼写错误、截断、缺少或插入的标记、空字段、意外的缩略语和其他不规则问题造成的）与引用表中的无错记录相匹配。模糊分组检测输入行之间的相似性，并通过使用其字符串值确定哪些行是重复项。模糊查找和模糊分组是有用的基元，能够简化多种在数据仓库中经常遇到的数据清理和准备任务。 通过为您的域自定义模糊查找和模糊分组，您可以.

一、简介

由于拼写错误、截断、缺少或插入的标记、空字段、意外的缩略语和其他不规则问题，实际的数据是“有问题”的。 因此，在数据仓库项目中，很大一部分的时间和金钱都花费在了提取、转换和加载 (ETL) 阶段。 在 ETL 阶段，新数据被清理、标准化，并使其与现有数据一致。 在 Microsoft SQL Server?2005 中可用的模糊查找和模糊分组转换，有助于使 ETL 过程在遇到若干种在实际数据中观测到的常见错误时更易复原。 它们解决一般的匹配和分组问题，而无需特定于域的规则和脚本的专家集合。 通过为您的域自定义模糊查找和模糊分组，您可以利用数据转换服务 (Data Transformation Services，DTS) 设计器内的通用数据清理算法，并避免创建复杂的自定义规则和代码。

模糊查找使得您能够将输入记录与引用表中的无错的、标准化的记录匹配。 匹配过程对于在输入记录中存在的错误有复原功能。模糊查找返回最相近的匹配并指出匹配的质量。 例如，由于输入数据中的录入错误或其他错误，在一次新的销售交易中输入的客户信息（名称和地址）可能与包含所有当前客户的客户引用表中的任何记录都不完全匹配。即使不存在完全匹配，模糊查找也会从客户引用表返回最佳匹配记录，并提供度量值以表明匹配质量。

模糊分组使您能够标识一个表中的记录的组 — 在这个表中每个组都可能对应相同的实际实体。 分组对在实际数据中观测到的常见错误有复原功能，因为每组中的记录可能彼此不相同但彼此很相似。 例如，对于将一个客户引用表中描述每个实际客户的所有记录归类到一起，模糊分组是很有用的。

模糊查找和模糊分组为复杂的、常遇到的数据清理问题提供易用的解决方案。 尽管它们与现有的诸如 soundex、基于规则的系统、基于编辑距离的系统及全文搜索等现有方法有一些联系，但是模糊查找和模糊分组有一些优势：

•	模糊查找和模糊分组使用一个自定义的、考虑编辑距离（例如，“hits”与“bit”的距离为 2）、标记数、标记顺序以及相对频率的独立于域的距离函数。 结果，与全文搜索相比，模糊查找和模糊分组获得的辨别力要精细得多，因为它们捕获了更详细的数据结构。
•	由于它们完全是标记驱动的，模糊查找和模糊分组不像 soundex 那样有依赖于语言的组件。
•	因为它们不只使用编辑距离，模糊查找和模糊分组不容易被变换误导，而且与只使用编辑距离的方法相比，能够检测出更高级的模式。
•	模糊查找和模糊分组紧密集成在 DTS 中，这使它们对 SQL Server 2005 的 ETL 任务来说易于使用，而且无需或只需很少的自定义编程。

下面的部分提供了使用并了解模糊查找和模糊分组的分步指南，并且包括了这些转换的一些实现和性能方面的内容，这对用户来说很有用。 本文意在通过更详细地解释模糊查找和模糊分组的某些方面来补充在线书籍。 有关更多关于选项和配置参数方面的信息，请参阅在线书籍项。 这些项包括的信息有：列宽、层次结构、标记处理选项以及其他有用的参数，这些参数提供一些方法，以加入可用来提高某些方案的准确性的域知识。

二、模糊查找入门

模糊查找可以通过使用损坏的或不完整的字符串关键字查找大型表中的数据。 例如，如果您想要按名称和地址查找客户信息，您可以使用模糊查找来查找这些信息，即使您的输入与您的引用表中所存储的记录并不完全匹配。 用于模糊查找的最简单的包是由包含一个源、一个模糊查找转换和一个目标的单个 DTS 数据流任务组成（图 1）。

图 1. 最简单的模糊查找包

要构造最简单的模糊查找包：

1.	打开 DTS 设计器。
2.	创建一个新的 ETL 项目，添加一个新包，单击 Data Flow 选项卡，然后接受 add a data flow 项选项。
3.	在数据流图上，从 Toolbox 拖动 OLE DB 源和目标转换，然后通过使用一个模糊查找的实例连接它们。
4.	通过选择一个连接和包含有问题的数据的输入表，将 OLE DB 源指向您的新数据。 您的数据必须包含一些字符串列。
5.	双击 Fuzzy Lookup 打开自定义用户界面 (UI)。 从 Reference table name 下拉菜单选择您希望转换的连接和表，指向已经存储的引用数据。
6.	在 Columns 选项卡上，将您想要比较的项从 Available Input Columns（来自 OLE DB 源）拖动到 Available Lookup Columns（来自引用表）。 例如，您可能希望将输入中的 StreetAddress 与引用表中的 Address 相比较。
7.	为 Available Lookup Columns 中的所有项选择复选框，然后单击 OK。
8.	将 OLE DB 目标指向您可以为其编写新表的连接，然后单击 New。 接受默认创建语句，现在您已经准备好运行模糊查找了。
9.	要运行您刚刚创建的包，在“解决方案资源管理器”窗口中鼠标右击其名称，然后选择 Execute。

DTS 设计器运行此包，并提供关于管道的详细的可视反馈。 取决于引用数据的大小，您可能会注意到在容错索引 (Error-Tolerant Index，ETI) 创建时的延迟。 ETI 是模糊查找在运行时使用的主数据结构。

在 ETI 创建后，所有输入行都被处理然后结果被写入到目标。 通过显示由每个组件处理的行数，DTS 设计器为您提供关于管道进度的反馈。 您也可以通过鼠标右击模糊查找和 OLE DB 目标之间的连接器将一个 DataViewer 放置在管道上。 这允许您实时看到那些模糊查找与您的输入行匹配的行。 除了匹配元组，模糊查找还输出可信度和相似性百分比。 有关更多关于可信度和相似性百分比的信息，请参阅本文后面的解释结果。

三、最有效地使用模糊查找

运行模糊查找的主要步骤是创建 ETI、执行查找和检查输出。 下列部分提供了关于这些步骤的每一步的详细信息。

了解容错索引

模糊查找通过索引在引用数据和引用行 ID 中出现的标记创建 ETI。 如果您将 ETI 存储在了服务器上，您可以通过从中选择一些行来查看其内容。 每个行由一个索引标记和包含该标记的引用行 ID 序列组成。 在地址示例中，如果您的引用数据包含 13831 N.E. 8th St，ETI 将包含 13831、N、E、8th 和St. 的标记项。 以下是 ETI 如何随引用数据而增长： 在引用表中有越多的唯一标记和越多的行，ETI 中就会有越多的项和越长的列表。 有关更多关于 ETI 的大小如何随引用数据而增长的信息，请参阅本文后面的了解性能。 标记化过程是通过模糊查找自定义属性 delimiter string 控制的。 例如，如果您想要索引 N.E.，而不是 N 和 E，则请将句点从分隔符列表删除。 结果是 N.E. 作为一个单独的标记在 ETI 中显示，而且会在运行时作为一个单元被查找。 由于分隔符的全局应用，如 First.Avenue 也作为一个单独的标记显示。

由于 ETI 的构造成本因引用数据大小的增长而变得更加昂贵，模糊查找提供一个选项，可以将 ETI 存储在服务器上，日后可以重新使用。 这个选项使您能够避免在每次运行模糊查找时都重新创建一个 ETI。 如果您的 ETI 会花费太多的时间而不能每次运行都重建，考虑创建一次而在接下来的运行中对其进行重用。 要做到这一点，在 Reference Table 选项卡上选择 Store new index，然后指定一个表名称。

注 ETI 可能会变得相当巨大，所以规划服务器空间可能是必要的。 在最坏的情况下，ETI 可能会是引用表的索引行中的数据大小的两倍。

如果您想要存储 ETI 但是引用数据不时地更改，您还可以启用 Maintain stored index。 这个功能在您的 ETI 上安装一个触发器，它检测对基础引用数据的修改。 只要这样的修改发生，此触发器将相应的更改传递到 ETI，从而使其保持为最新。 如果您不安装表维护，对您的引用表所做的更改将在没有警告的情况下使任何关联的 ETI 无效。

注 表维护功能在 Beta 2 版中不可用。

在运行时发生了什么

在运行时，模糊查找使用 ETI 查找其输入的最佳匹配。 在确定最佳匹配时，最重要的参数是 MinSimilarity 阀值。 您可以通过使用模糊查找UI 来设置这个自定义属性。 引用元组只有在其与输入足够相似时才会被返回。 因此，如果您设置了一个很高的相似性要求，模糊查找考虑的候选也会较少，而且结果可能是不返回任何匹配。 如果您将 MinSimilarity 设置得低，模糊查找将考虑更多的候选，而更有可能找到一个匹配，但搜索可能会用去更长的时间。

匹配条件包括：

•	为匹配给出的引用元组而需要对输入元组做的标记或字符插入、删除、替换以及重新排序的数量。 例如，输入 122 First Lane 很可能被认为比输入 22 N.E. 1st Ln & Leary Way 更接近引用 122 First Ln。
•	来自引用表的标记频率。 非常频繁的标记通常被认为几乎不会提供对匹配有用的信息。 相对稀少的标记被认为是它们在其中出现的行的特性。

设置正确的阀值取决于您的应用程序和数据的性质。 如果您要求一个在您的输入和引用之间的相近的匹配，您应该考虑为 MinSimilarity 设置一个大值，如 0.95。 如果您在进行一个研究性的项目，您可能会对检查弱匹配与相近匹配一样感兴趣，那么您应该将 MinSimilarity 设置为一个较低的值，如 0.1。 并没有可以用于确定这个范围的固定规则，所以建议您对数据设置进行试验。 查看几次运行的输出可以供设置最优值考虑。 例如，您执行第一次运行使用的阀值为 0.1。 您观测到一个特定的输入与一个相似性为 0.2 的特定的输出匹配。 如果对于您的应用程序来说此元组过于不相似（详细信息请参阅解释结果），第二次运行您可以将 MinSimilarity 设置为 0.3，从而排除与其过于不相似的匹配。 在一个小的测试集上重复此过程并反复数次测试设置，这会帮助您确定什么设置对于您的应用程序是合适的。

如果您想查看每个输入的多个匹配，您可以将 Maximum matches to output per lookup 属性设置为一个大于 1 的值 n。那么模糊查找会返回顶部的 n 个匹配。 不过，为每个输入记录查找匹配所需的时间也增加了。 选择一个高 n 值不一定总是返回 n 个匹配，因为即使当 MinSimilarity 设置为 0 时，模糊查找也可能会认为某些行过于不相似而不返回。 有关更多关于对性能的影响的信息，请参阅本文后面的了解性能。

模糊查找还为每个匹配引用记录返回一个置信量度。 如果所有匹配都同样相近，它们每一个的置信度约等于 1/n。 置信度与相似性的不同之处在于，它不仅是输入行和其所限定的引用行的一个函数，它取决于返回的整个结果集，可能还取决于下一个最佳可用引用行。

解释结果

如何解释结果取决于您的应用程序目标。 如果您想显示存在一些相似的匹配，您应该设置一个高相似性阀值要求并筛选高置信度的匹配。 当引用表有一个输入元组的相近匹配时，相似性为高。 如果在所有引用元组中有一个记录很相近地匹配输入元组，那么置信度也为高。 因此，您可以使用相似性和置信度值来确定您想要如何进一步处理一个模糊查找的结果。

您可能还想要了解为什么没有获取某些行。 主要的原因是 ETI 和模糊查找检索策略。 当模糊查找索引一个标记（如 committee）时，它也索引子标记元素 comm.、ommi、mmit、mitt、itte、ttee。 这个方案有助于提高检索和从输入错误恢复的速度。 例如，如果 committee 出现在引用数据中，而输入的是 comittee（只有一个 m），模糊查找可能能够通过查找子标记 mitt 来找到正确的引用行，即使没有检索到完整输入标记。 这就是模糊查找索引被称为“容错”的原因之一。

不过，这个检索过程并不是完美的。 例如，如果引用标记 Pattel 在输入中被误拼为 Patel（只有一个 t），没有一个输入标记片段（Pate 和 atel）会匹配索引引用片段（Patt、atte 和 ttel）。 结果是，模糊查找必须依赖存在于元组中的其他标记来执行正确的检索。 如果在行中不存在其他标记，模糊查找将不能恢复正确的引用行。 但是，对于包含多于一个标记的行来说，模糊查找通常能够基于输入中的标记和片段获取几个候选行。

列宽

在一些情况下，您可能需要更严密地检查数据。 例如，模糊查找可能会认为 CA 和 WA 相互近似，即使在地址表的状态列中这两个字符串在语义上相差甚远。 因为模糊查找是独立于域的，您必须将一些知识编码到您的 DTS 管道中。 在此例中，您可能希望查找一个 State 的精确匹配或将此列的 MatchContribution 改为一个更大的数，比如 5，这可通过使用高级编辑器（要打开高级编辑器，单击设计器中的模糊查找组件，然后单击 Properties 窗格中的 ShowAdvanced Editor）完成。 另一种方法是，您可以使用 Conditional Split 转换首先查找 State 的精确匹配，然后如果不存在精确匹配，再执行模糊查找。

四、模糊分组入门

您可以使用模糊分组来检测有字符串属性的行的集合中的“模糊”或近似的副本。 例如，您可以使用模糊分组合并来自不同部门的客户表。 模糊分组使用的最简单的包由一个包含一个源、一个模糊分组转换和一个目标的单个 DTS 数据流任务组成（图 2）。

图 2. 最简单的模糊分组包

要构建最简单的模糊分组包：

1.	打开 DTS 设计器。
2.	把 OLE DB 源和目标转换拖动到一个数据流上，并通过使用模糊分组的一个实例连接它们。
3.	通过选择一个连接和表名称，将 OLE DB 源指向包含有可能重复的数据的表。 注 这个表必须包含一些可供模糊分组进行分析的字符串列。
4.	双击模糊分组来打开自定义 UI，选择 Available Input Columns（来自 OLE DB 源）中所有项的复选框，然后单击 OK。
5.	将 OLE DB 目标指向您可以为其编写新表的连接，然后单击 New。 接受默认创建语句，现在您已经准备好运行模糊分组了。
6.	要运行您刚刚创建的包，在“解决方案资源管理器”窗口中鼠标右击其名称，然后选择 Execute。

取决于您输入数据的大小，您可能会在模糊分组缓冲数据时遇到延迟。 在行进一步沿管道流动之前，模糊分组调用模糊查找来为输入数据创建一个 ETI，并将 ETI 存储在临时连接上。 结果是，可调大小的对象可能会被放置在该连接上。 输入上的 ETI 创建后，所有的输入行都被处理然后结果被写入到目标。 有关分组是如何执行的更多信息，请参阅本文后面的最有效地使用模糊分组。

在默认情况下，模糊分组输出一些名为 _key_out 和 _key_in 的附加列。 当行流经管道时，模糊分组为其每一个分配一个 ID，即 _key_in。 当模糊分组将某一行集合分组时，它确定哪个行应该作为代表。 它然后将组中的行的所有 _key_out 列分配为该代表的 _key_in 值。 结果是，如果您希望只将代表行写入到您的输出，您可以通过一个只选择 _key_in 等于 _key_out 的行的条件拆分转换来筛选模糊分组输出。

五、最有效地使用模糊分组

与模糊查找相比，模糊分组需要调整的外部参数较少，不过，了解它的一些内部机制会帮助您获得最佳性能。模糊分组在后台使用模糊查找来执行分组。 例如，模糊分组将其标记化的字符串原封不动地传递给模糊查找。 在运行时，模糊分组依据输入数据使用模糊查找创建一个临时 ETI，并用其确定哪些输入行是彼此相近的。模糊分组检查每个输入行，并对数据执行多种模糊查找查询，同时自适应地设置 MinSimilarity 阀值。 依据得回的结果数，由它生成组。

正如使用模糊查找，模糊分组要求您设置 MinSimilarity 阀值。 只有元组彼此的相似性高于在 UI 中设置的阀值时，它们才会被分组，记住这一点是很重要的。 因此，如果您用一个低阀值运行模糊分组并发现很不近似的元组被分组到一起，那么就提高相似性设置。模糊分组将每个元组的相似性报告给该组的代表元组。 代表元组的选择是无法影响的。 如果您想要拆分一个组，将所需的相似性设置为高于报告数。 如果被分组的元组过少，您可以对相似性值执行一个二进制搜索，以确定为对某些元组进行分组，将相似性阀值设置为多低合适。 例如，如果设置为 0.9 导致组太少，而 0.7 导致组太多，那么试一下 0.8。

注 是无法强制模糊分组对两个元组分组的。 即使将相似性设置为零也不一定会返回带有所有元组的一个单个组。 因为组的质量极大地取决于您的数据的特定语义，所以试验是找出您的应用程序的正确设置的唯一方法。

您还可以尝试使用模糊查找为您的输入数据创建一个 ETI，并通过使用一个大值以返回该数量的匹配和高阀值以达到所需的相似性，来查找特定的行。 不过，模糊分组合并了以下两项内容：将元组适当地归类为多个重复项的集合的附加智能及使代表规范化的建议。

六、了解性能

尽管其界面简单，但模糊查找和模糊分组是复杂的过程，了解其性能需要一些分析。 下列各节解释了常见性能问题并提供了一些措施的示例。 正如您将要看到的，模糊查找和模糊分组性能的主要决定因素是数据大小。 对于模糊查找和模糊分组来说，这是指输入行、标记和字节的数量。 对于模糊查找，还有关于引用数据大小的附加注意事项。 次要的决定因素是可用的计算资源，包括内存、数据库服务器上的空间、网络带宽，以及设置这些资源的方法。

设置注意事项

要从模糊查找和模糊分组获得最佳性能，遵循关于内存使用和体系结构的一些原则是很有用处的。

要将在您计算机上的内存要求最小化：

•	使用更轻量的 DTExec.exe 而非完整的 DTS 设计器来在生产中执行包。
•	去除您管道中未使用的列，因为其需要内存。
•	避免在输入中使用长 varchar 列，因为它们由 DTS 管道转换为固定宽度的 char 列。
•	通过限制 varchar 字段的转换，将所有数据传递到长度与列中最长的项相等的固定长度 char 字段。

如果您选择在同一台计算机上运行 DTS 客户端和服务器，您将会避免潜在的网络问题，但是可能遇到内存争用。 您可以使用 sp_configure 并将 set max server memory 设置设为 256 以减轻两个进程之间的内存争用。 您还可以使用 DTS 设计器中的高级编辑器设置 MaxMemoryUsage Fuzzy Lookup 自定义属性。

在以下各节中报告的所有实验趋势线都是使用下列设置获得的：

•	一台有 Intel Pentium III 1 千兆赫 (GHz) 处理器的服务器、 512 兆字节 (MB) 的 RAM，以及 Microsoft Windows Server 2003 企业版。
•	与 DTS 客户端运行在同一台机器上的 SQL Server。

对于反复出现的其引用表比通常的输入表大很多的模糊查找任务，您应该考虑预计算索引。 在这些情况下，重新创建索引会在实际查找所花费的运行时间中占支配地位，而这使得管理附加表非常值得。

对于大的模糊查找输入任务，您可能想要考虑将多个 DTS 客户端与一台中央服务器一起使用。 在这个设置中，此中央服务器承载引用表和预计算的 ETI。 通过使用中央引用表和 ETI，每个 DTS 客户端对该大输入表任务的一部分执行模糊查找。 当模糊查找完成后，您重新合并由各个 DTS 客户端生成的所有输出表。

注 这个体系结构不适用于模糊分组。

措施

对模糊查找和模糊分组性能的最大影响来自于所使用数据的大小。 在模糊查找中，这是指引用和输入表的大小。 在模糊分组中，这是指输入表的大小。 输入的大小与两个方面有关系：

•	行和列的数量对性能有最大的影响。 您有的数据越多，模糊查找和模糊分组所需要的资源就越多。 以下各节中的数字展示了不同方案下的特定数据。
•	每个模糊匹配在其上执行的字符串列的平均标记数也对性能有影响。 模糊转换并不意味着文档检索。 对于较长的字段（多于 20 个标记），使用 SQL Server 全文索引功能可能会更有效率。

以下各节展示了一些详细的模糊查找和模糊分组性能图表。 这些图表旨在建议转换的趋势，而不是给出绝对数字。 一些图表表达对一个基准度量值增加或减少的比例。 在这些情况下，该图表能够被用于粗略推断多大的输入大小增长会导致运行时间的加倍。

模糊查找性能

决定模糊查找性能的两个主要变量是引用数据的大小和输入数据的大小。 这些变量与模糊查找的两个阶段对应： 创建 ETI 和实际执行查找。 总的来说，这些任务在其各自的输入大小上是线性变化的。 取决于您的输入的大小，实际的查找时间可能少于或多于 ETI 创建时间。

图 3 说明了与引用表大小相对的创建 ETI 所需的时间。 如前面所解释的，创建 ETI 可以执行一次并将结果保存，从而避免每次都因为创建造成开销。 由此得到的 ETI 的大小最高是索引引用列的大小的两倍。 注意一些 DTS 管道计数器在创建大型 ETI 的时候可能看来是冻结的。 您可以通过观测服务器上表的增长来跟踪进展。

图 3. 与引用表大小相对的 ETI 创建时间

ETI 的大小也影响运行时间。 ETI 包含的数据越多，它的使用就越昂贵，因为这表示需要更大的搜索空间。 图 4 说明了与固定输入大小的引用表大小相对的模糊查找的运行时间性能。 图 4 中的曲线也取决于对在查找时间中扮演重要角色的不同行的标记分布。 有更多内存的话能够减轻大的引用表和 ETI 的影响。 在运行时，模糊查找将部分 ETI 缓存在内存里，其值最高等于 MaxMemoryUsage 自定义属性中指定的约束大小。 必须使用高级编辑器来设置这个限制。

图 4. 与引用表的大小相对的模糊查找运行时间

排在数据大小对性能的影响之后，对性能的最大影响来自于剩下的转换参数，如要返回的匹配数、所需的相似性阀值，以及对其进行匹配的列的数量。

要求模糊查找返回的匹配越多，搜索就越慢。 这是因为会连续搜索到候选元组的列表中，直至找到足够的匹配。

要求的相似性因子越大，模糊查找搜索就越快。 这是因为在要求高相似性的搜索中，模糊查找更积极地放弃元组。

模糊分组性能

模糊分组比模糊查找依赖的变量更少。 影响模糊分组性能的主要变量是输入数据大小。 图 5 展示了当输入数据变大后，成本变化呈线性。 图表前面部分的非线性是由于 ETI 的创建时间。 随着匹配阀值的增大，因为找不到相似的元组来创建分组，执行时间最终会减小。 这显示在图 6 中。图 6 展示出数据的分布扮演了重要角色，而且可能导致运行时间的一些非线性变化。 要注意的是，运行大的模糊分组任务，会导致您指定作为临时连接的服务器上的临时对象也很大。 在模糊分组预处理步骤中，DTS 管道可能看来是冻结的。 如果发生这种情况，您可以通过观测服务器上临时对象的大小来跟踪进展。

图 5. 与 MinSimilarity=0.8 的输入大小相对的模糊分组时间

图 6. 与 20K 行的 MinSimilarity 阀值相对的模糊分组时间

小结

模糊查找和模糊分组转换是独立于域的基元，可以有助于数据清理和准备。 它们采用基于标记的距离概念 — 您可用于对您的数据执行细致的查找和分组操作。 关于距离函数和该组件的整体体系结构的进一步的详细信息，可以在下面引用的研究论文中找到。 因为模糊查找和模糊分组是作为 DTS 组件实现的，您可以在较大的 ETI 过程中透明地使用它们，并利用 DTS 基础结构，如日志记录、事件和错误跟踪，无须使用自定义编程，从而避免了相关费用。 尽管需要一些资源来计算转换，但模糊查找和模糊分组将曾几何时、不用大量手动干预和自定义工具就难于完成或无法完成的操作自动化了。