SSIS高级数据转换：文本分析、数据清洗与模糊匹配

### SSIS高级数据转换：文本分析、数据清洗与模糊匹配 #### 1. 数据透视转换的关键属性 在数据透视转换（Pivot Transformation）中，每个输出列都需要明确源列数据的来源。因此，每个输出列的`SourceColumn`属性应设置为相应输入列的`LineageID`。数据流中的每列都有一个`LineageID`属性，该属性在缓冲区中定义了此列，且`LineageID`可能与`ID`属性不同。 对于数据透视键值（PivotKeyValue），透视列中的每个值都将成为输出中的一列。因此，需要像步骤8那样，在输出列部分手动添加与这些值对应的输出列。仅包含传递值或作为透视键的输出列，只需设置`SourceColumn`属性；而透视列还需要设置`PivotKeyValue`属性，该属性的值应与该输出列对应的透视列的值相等。例如，在显示值为2005的输出列中，`PivotKeyValue`应为2005。透视列的`SourceColumn`属性应设置为`PivotUsage`为3的输入列的`LineageID`，该列包含应出现在输出透视数据行中的值。 需注意，在早期版本的SSIS（2005、2008和2008R2）中，数据透视转换只有高级编辑器。若要使用这些早期版本，需采用上述方法。 #### 2. 文本分析：术语查找与术语提取转换 在数据分析中，有许多转换可用于分析数值数据，甚至可以使用T - SQL中的聚合函数从数值数据中获取分析信息。但当需要分析用户评论这类无分类的自由文本时，情况就不同了。文本分析是数据分析中的一个完整主题，旨在从文本数据中获取分析信息，这些文本数据可以是完全自由格式的。幸运的是，SSIS提供了两个用于文本分析的有用且高级的转换：术语提取（Term Extraction）和术语查找（Term Lookup）。 ##### 2.1 准备工作 - **创建产品表**：在`PacktPub_SSISbook`数据库中创建`Product`表，使用以下脚本： ```sql CREATE TABLE [dbo].[Product]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](128) NULL, CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED ( [ID] ASC )) ON [PRIMARY] GO USE [PacktPub_SSISbook] GO INSERT INTO [dbo].[Product] ([Name]) VALUES ('PX12') INSERT INTO [dbo].[Product] ([Name]) VALUES ('PX800') INSERT INTO [dbo].[Product] ([Name]) VALUES ('PX609') ``` - **获取评论文件**：从在线资源下载或复制`comments.txt`文件，并将其复制到`C:\SSIS\Ch05_Data Flow Task - Part 3 - Advanced Transformations\R02_Text Analysis\Files`。该文件内容如下： ```plaintext ID,TextData 1,I used PX609 for 5 years that was really good. 2,the PX800 delivered very soon but it was a mistaken delivery so I asked for a re - send 3,this company has a brilliant support service but everytime I should spend a lot of time in the long queue, that was my experience with PX609 4,I recommended PX12 to couple of my friends and they are happy with its good performance 5,I received my PX12 and PX800 with delayed delivery 6,PX609 was a fair product for me 7,there were no upgrades from PX800 that made me crazy 8,my PX12 had a problem at first turn off but after that I used it for 10 months and it works great ``` ##### 2.2 操作步骤 以下是使用术语查找和术语提取转换进行文本分析的操作步骤： 1. 创建一个SSIS项目，命名为`R02_Text Analysis`。 2. 在解决方案资源管理器的连接管理器文件夹中，添加一个OLE DB连接管理器到`PacktPub_SSISbook`数据库。 3. 添加一个平面文件连接管理器，连接到`comments.txt`文件（路径为`C:\SSIS\Ch05_Data Flow Task - Part 3 - Advanced Transformations\R02_Text Analysis\Files\comments.txt`）。将格式设置为分隔符，勾选“第一行包含列名”复选框，并在高级选项卡中将`TextData`列的数据类型更改为Unicode字符串`[DT_WSTR]`。若要进行术语提取或术语查找，使用`DT_WSTR`或`DT_NTEXT`是必需的，因为这些组件仅适用于这些数据类型。 4. 将`package.dtsx`重命名为`P1_Term Lookup`，并在包中添加一个数据流任务，然后转到数据流选项卡。 5. 添加一个平面文件源，并将其连接到步骤3中创建的平面文件连接管理器。 6. 在平面文件源之后拖放一个术语查找转换。 7. 在术语查找转换编辑器中，将OLE DB连接管理器设置为`PacktPub_SSISbook`数据库，并将参考表名称设置为`[dbo].[Product]`。 8. 转到术语查找选项卡，将`TextData`从可用输入列拖放到可用参考列，并勾选可用输入列中的`TextData`和`ID`复选框。 9. 关闭术语查找转换编辑器，拖放一个联合全部转换。右键单击术语查找和联合全部之间的数据路径，然后单击“启用数据查看器”（此处使用联合全部仅用于在数据查看器中查看数据）。 10. 运行包，并在数据查看器中检查数据。结果将显示`TextData`中出现的每个术语及其在每条记录中的频率。对于提及多个产品的评论，术语查找结果中将有多个记录。 11. 向项目中添加另一个包，命名为`P2_Term Extraction`，添加一个数据流任务并转到数据流选项卡。 12. 添加一个平面文件源，连接到步骤3中创建的平面文件连接管理器。 13. 从工具箱中拖放一个术语提取转换到平面文件源之后的数据流中。 14. 在术语提取转换编辑器窗口的术语提取选项卡中，选择可用输入列中的`TextData`，并保持输出列名称不变。 15. 转到高级选项卡，在术语类型部分选择“名词和名词短语”，将得分类型设置为“频率”，在参数部分将频率阈值设置为1，最大术语长度设置为18。 16. 关闭术语提取转换编辑器，拖放一个联合全部转换。右键单击术语提取和联合全部之间的数据路径，然后单击“启用数据查看器”。 17. 运行包，并在数据查看器中检查数据。将看到按名词和名词短语分隔的术语在不同记录中显示，以及它们在整个输入`TextData`列中的频率。 18. 再次打开术语提取转换编辑器，将术语类型更改为“名词短语”，得分类型更改为“TFIDF”，然后再次运行包，将看到不同的结果。 ##### 2.3 工作原理 - **术语查找**：术语查找会提取输入文本记录中的所有术语，并在参考表中查找它们。如果找到匹配项，则返回该术语及其在源文本列中的频率。如果在高级选项卡中勾选相应选项，此转换可以检测区分大小写的数据。 - **术语提取**：术语提取会将每个术语扩展到输出的单独行中，并显示其得分。术语可以通过以下三种方式检测：名词、名词短语、名词和名词短语。频率得分根据术语在所有数据行中的出现频率对每个术语进行评分。TFIDF是术语频率 - 逆文档频率的缩写，其算法公式为：术语T的TFIDF =（T的频率）* log（（输入中的行数）/（包含T的行数））。可以通过设置频率阈值参数来过滤频率，默认值为2，即出现次数少于2的术语默认将在结果中被忽略。还可以设置最大术语长度以避免出现过长的名词短语。术语提取组件还能够检测大小写敏感的单词。术语提取组件结果提供的得分和术语可用于其他转换以进行