SSIS高級轉(zhuǎn)換任務(wù)—模糊查找

BI項目中經(jīng)常會有一些提取，轉(zhuǎn)換，數(shù)據(jù)處理（ELT）的工作，其中最主要的是處理過贓數(shù)據(jù)。假設(shè)在項目中我們向數(shù)據(jù)庫中注入了測試數(shù)據(jù)，但是通過一個外鍵從另外一個表中載入數(shù)據(jù)的時候沒有對應(yīng)的數(shù)據(jù)，那么這一行就是贓數(shù)據(jù)。這時候可以使用SQL中的Sound-Ex，full-text，相似度算法等方法查找。這種策略需要花費大量的時間和精力來設(shè)計算法，測試，維護，并且它們都是基于詞匯的，復(fù)用的可能性很小。也可能你會放棄自己處理并把它拋給一些有經(jīng)驗的高手專家來做這些工作，也可能你會在表中添加一些新的數(shù)據(jù)已達到外健關(guān)聯(lián)的目的，但是這種方法被稱作Lazy-add（懶惰的做法）。因為是手工添加數(shù)據(jù)難免會帶來拼寫錯誤，例如將職務(wù)名稱president錯誤地寫成平parsedent，將further錯誤地寫成future，將present錯誤地寫成parent，等等。

模糊查找和模糊分組提供一種新的方法來處理這種贓數(shù)據(jù)。這種轉(zhuǎn)換使得處理數(shù)據(jù)變得簡單，可兼容，可伸縮，可復(fù)用，它可以明顯地減少誤差。如果你的表中有贓數(shù)據(jù)，或者你開始處理數(shù)據(jù)，你會使用模糊分組來找出冗余數(shù)據(jù)。模糊分組會對表中的一列數(shù)據(jù)進行分析歸納出相似的并假設(shè)他們是某一個單詞的錯誤拼寫，進而計算出他們之間的相似度，利用這個相似度的數(shù)據(jù)可以 清洗表中數(shù)據(jù)。模糊分組將在下一個隨筆中講述。

如果你使用模糊查找來校正數(shù)據(jù)，建議先使用關(guān)鍵詞查找，這是因為模糊查找非常耗費資源。它會在數(shù)據(jù)表和參照表中建立索引。可以保存這些索引，但是這樣做會耗費耗費掉磁盤空間，并在運行的時候耗費寶貴的內(nèi)存資源。通過模糊查找得到一個關(guān)鍵詞列表是一個很好的策略。通過關(guān)鍵詞查找找出出現(xiàn)頻率較高的關(guān)鍵詞，模糊查找再使用內(nèi)連接來超出匹配項。如果還是有些數(shù)據(jù)不能匹配，將會把它標(biāo)記為unknow。

模糊查找要求輸入流中至少有一列是字符串，這點和關(guān)鍵詞提取有些不同，關(guān)鍵詞提取要求是NULL-terminated Unicode 字符串。模糊查找還需要連接到一個外鍵表作為參照。模糊查找的輸出列如下：

• 輸入數(shù)據(jù)：這些數(shù)據(jù)包含輸入流中的數(shù)據(jù)和需要從模糊查找中傳遞的數(shù)據(jù)
• 參照表數(shù)據(jù)：這些數(shù)據(jù)包含參照表中的數(shù)據(jù)
• 相似度：這一列數(shù)據(jù)是介于0和1之間的浮點數(shù)，用來描述相似程度，相似度是1表明匹配完全成功
• Confidence：這一列數(shù)據(jù)是介于0和1之間的浮點數(shù)，用來描述匹配的信任程度。Confidence是另外一種形式的相似度，他不是通過一對一的比較得來，而是通過一對多的比較得來。它可以獲得更加準(zhǔn)確的數(shù)據(jù)。

在模糊查找的編輯界面有3個標(biāo)簽

• 參照表：在這個標(biāo)簽內(nèi)設(shè)置一個連接到參照表的OLE DB Connection。比較之前模糊查找參照這個表中的數(shù)據(jù)建立一個索引。在這個標(biāo)簽可以選擇保存這個索引或者使用先前運行時保存的索引，還可以維護當(dāng)前索引，這樣會刪除以前的索引保存本次運行時得到的索引。這里要提醒的是如果處理的數(shù)據(jù)量很大，索引也會變得很大。
• 列：在這個標(biāo)簽內(nèi)設(shè)置輸入數(shù)據(jù)流中列和參照表中的一列的映射。用鼠標(biāo)拖拽的方法將他們連接起來。還可以在輸出數(shù)據(jù)流中添加一個外鍵列，只需要在Available Input Columns中選擇這個列舊可以了。如圖1是設(shè)置之后的界面
  
  圖1

• 高級：這個標(biāo)簽內(nèi)設(shè)置查找算法。Maximum number of matches to output per lookup設(shè)置每一行數(shù)據(jù)最多可以有多少個匹配。默認(rèn)的值是1，如果設(shè)置大于這個值，結(jié)果中將產(chǎn)生更多的行，但是如果輸入流數(shù)據(jù)中有很多類似的數(shù)據(jù)這種設(shè)置還是需要的。Similarity threshold用來設(shè)置相似度閥置。Token delimiters用來設(shè)置字符分割符，默認(rèn)的分割符是常見字符串分割符，如圖2
  
  圖2

盡管模糊查找通過一些簡單的設(shè)置來實現(xiàn)復(fù)雜的邏輯，結(jié)果頁并不是完美的。需要花費一些時間來觀察不同設(shè)置產(chǎn)生的不同結(jié)果。

下面來做一個練習(xí)。這個練習(xí)用來展示模糊查找的功能，使用一個含有職位名稱的參照表，然后輸入一個個人信息表，當(dāng)然個人信息中包含這個人的職位，但是并不是所有的都能和參照表匹配，模糊查詢將會找出匹配，然后在在目標(biāo)表中查看匹配結(jié)果。

1. 使用下面的內(nèi)容創(chuàng)建一個文本文件并命名：c:\import\empdata.txt，這個表包含的內(nèi)容是個人信息。我們可以看到這個表中的數(shù)據(jù)參差不齊，這些是ETL過程中常見的情況。
   

   EMPIDTITLE                  LNAME
   00001EXECUTIVE VICE PRESIDENWASHINGTON
   00002EXEC VICE PRES         PIZUR
   00003EXECUTIVE VP           BROWN
   00005EXEC VP                MILLER
   00006EXECUTIVE VICE PRASIDENSWAMI
   00007FIELDS OPERATION MGR   SKY
   
   00008FLDS OPS MGR           JEAN
   00009FIELDS OPS MGR         GANDI
   00010FIELDS OPERATIONS MANAGHINSON
   00011BUSINESS OFFICE MANAGERBROWN
   00012BUS OFFICE MANAGER     GREEN
   00013BUS OFF MANAGER        GATES
   00014BUS OFF MGR            HALE
   00015BUS OFFICE MNGR        SMITH
   00016BUS OFFICE MGR         AI
   00017X-RAY TECHNOLOGIST     CHIN
   00018XRAY TECHNOLOGIST      ABULA
   00019XRAY TECH              HOGAN
   00020X-RAY TECH             ROBERSON

   
   　　
2. 在數(shù)據(jù)庫AdventureWorks中使用下面的語句創(chuàng)建一個參照表
   

   CREATE TABLE [Occupation](
     [OccupationID] [smallint] IDENTITY(1,1) NOT NULL,
     [OccupationLabel] [varchar] (50) NOT NULL
    CONSTRAINT [PK_Occupation_OccupationID] PRIMARY KEY CLUSTERED
   (
     [OccupationID] ASC
   ) ON [PRIMARY]
   ) ON [PRIMARY]
   GO
   INSERT INTO [Occupation] Select 'EXEC VICE PRES'
   INSERT INTO [Occupation] Select 'FIELDS OPS MGR'
   INSERT INTO [Occupation] Select 'BUS OFFICE MGR'
   INSERT INTO [Occupation] Select 'X-RAY TECH'

   
   　　
3. 創(chuàng)建一個package命名為FuzzyLookupExample，拖放一個Data Flow task雙擊進入Control Flow設(shè)計界面。
   　　
4. 創(chuàng)建一個Flat File Connection命名為Employee Data，將數(shù)據(jù)源設(shè)置為c:\import\empdata.txt，F(xiàn)ormat屬性設(shè)置為Ragged Right，選中Column names in the first data row。如圖3。點擊Columns標(biāo)簽，在Source data columns文本框內(nèi)點擊選擇第5行和第28行，如圖4，點擊Advanced標(biāo)簽設(shè)置TITLE列的數(shù)據(jù)長度是50，如圖5
   
   圖3
   
   圖4
   
   圖5
   　　
5. 添加一個Flat File Data Source，使用Employee Data連接。添加一個OLE DB Destination選擇AdventureWorks數(shù)據(jù)庫
   　　
6. 添加一個Fuzzy Lookup將Flat  File DataSource和它連接起來，再將它和OLE DB Destination連接起來
   　　
7. 雙擊打開Fuzzy Lookup的編輯界面，在Reference標(biāo)簽界面內(nèi)，設(shè)為AdventureWorks數(shù)據(jù)庫中的Occupation表如圖6，在Columns標(biāo)簽內(nèi)將輸入表中的TITLE列和參照表中的OccupationLabel標(biāo)簽連接起來，如圖7，Advanced標(biāo)簽界面中設(shè)置相似門檻為0.5，如圖8
   
   圖6
   
   圖7
   
   圖8
   　　
8. 雙擊打開OLE DB Destination的編輯界面，雙擊New查看動態(tài)產(chǎn)生的表，修改表名為FuzzyResults，點擊Mappings標(biāo)簽，默認(rèn)所有設(shè)置
   　　
9. 在Fuzzy Lookup和OLE DB Destination之間加一個類型為grid的Data View
   　　

 運行這個package，Data View中的數(shù)據(jù)如圖9，最終表中的數(shù)據(jù)也是這樣子的。我們可以看到在設(shè)置相似門檻為0.5的情況下所有輸入數(shù)據(jù)都被匹配，表中也將OccupationID附帶進來。如果使用INNER JOIN來選擇的話只能找到4行完全匹配的數(shù)據(jù)，就是在表中similarity值是1的4行數(shù)據(jù)

 圖9