기본 강좌 35 - 유사 항목 조회 변환

유사항목조회.zip

유사 항목 조회

 

데이터를 처리하는 작업 중 데이터 정재 작업(Data Cleansing)을 해야 할 때가 종종 발생합니다. SQL 쿼리에서는 기본적으로 데이터가 완전히 일치하는 경우에만 매치됩니다. 물론 LIKE 연산자를 이용하여 유사 조회를 수행할 수 있습니다. 하지만, 이러한 유사 조회 작업은 제한적으로만 이용할 수 있으며 유사성 판단과 같은 작업을 수행하기에는 어려움이 있습니다..

 

예를 들어 다음과 같은 형태의 경우를 고려해 봅시다.

인터넷 사이트 초창기에는 사용자들로부터 직접 전체 주소 데이터를 입력 받는 경우가 종종 있었습니다. 지금은 대부분의 사이트가 우편번호를 선택하게 하여 주소의 앞부분은 우편번호 테이블에 있는 규격화된 형태로 입력이 되지만, 직접 입력하도록 한 경우에는 다양한 형태로 입력되었습니다.

 

Ÿ           서울특별시 강남구 삼성동

Ÿ           서울시 강남구 삼성동

Ÿ           서울 강남구 삼성동

Ÿ           서울 강남 삼성

 

사이트의 규모가 커지면서 이러한 정제되지 않은 데이터를 규격화된 데이터로 표준화 시키는 작업이 자주 발생되며, 이런 작업이 데이터 정재 작업의 예 입니다. 하지만 SQL 쿼리를 이용하여 데이터 정재 작업을 수행하는 것은 상당히 어렵고 고려해야 할 사항들이 많기 때문에 어려운 작업입니다. 이러한 작업을 수행하기 위해 텍스트 마이닝과 같은 고차원적인 데이터 처리 기법이 사용되기도 합니다.

 

SQL 2005 SSIS의 유사 항목 조회 변환을 이용하여 표준화 되지 않은 형태의 데이터를 쉽게 표준화 시킬 수 있습니다. 유사 항목 조회 변환을 수행할 때에는 원본 데이터에 대해 직접 처리하기 보다는 조회 변환 등을 이용하여 1차적으로 조회 작업을 수행한 후, 매핑 되지 않은 데이터에 대해 유사 항목 조회 변환을 수행하는 형태로 이용하는 것이 효율적입니다.

 

 

 

 

 

유사 항목 조회 변환은 조회 변환과 유사합니다. 조회 변환은 참조 테이블의 비교 항목과 정확히 일치하는 경우에만 출력하는데 비해 유사 항목 조회 변환은 정확히 일치하지는 않더라도 유사하다고 판단되는 수준일 경우 조회를 수행합니다.

 

참조 테이블 탭

 

 

참조 테이블 탭에서는 입력 데이터에 대해 조회 대상이 되는 참조 테이블을 지정하며, 유사 항목 조회 변환 수행 시 사용할 인덱스 테이블에 대한 설정을 지정합니다. 여기서 말하는 인덱스는 일반적으로 테이블에 설정하는 인덱스를 말하는 것이 아니며, 유사 비교를 수행할 때 이용하는 데이터 테이블을 말합니다.

 

새 인덱스 저장 옵션을 체크한 후, 새 인덱스 이름을 지정하면, 참조 테이블이 존재하는 DB에 지정한 이름의 인덱스 테이블이 생성됩니다.

저장된 인덱스 유지 관리 옵션을 체크하면 참조 테이블에 인덱스 테이블의 정보를 관리할 트리거가 생성됩니다. 이 트리거는 참조 테이블에 insert, update, delete 작업이 수행될 때 인덱스 테이블에 자동으로 반영하는 트리거입니다. 입력 데이터가 계속 변경되고 유사 항목 조회 작업을 반복적으로 수행해야 하는 상황일 때 이 옵션을 이용하여 인덱스 테이블을 효과적으로 관리할 수 있습니다. 대신, 데이터베이스에는

tg_DataCleaningMaintenance_PendingDelete__20060908_000000_fabb4233-bde1-4515-8d09-a89f35023b4e

tg_DataCleaningMaintenance_PendingInsert__20060908_000000_87baf700-b004-440e-b169-0659e166380d

등과 같은 형태로 관리 테이블이 생성됩니다.

 

기존 인덱스 사용을 선택하면 이미 생성되어 있는 인덱스 테이블을 그대로 사용할 수 있습니다.

 

 

 

 

열 탭

열 탭에서는 입력 데이터와 조회 데이터 간의 연결을 설정합니다. 또한 각 열의 연결에 대해 상세한 매핑 설정을 할 수 있습니다.

 

 

 

 

 

매핑 유형을 Fuzzy 또는 Exact로 설정할 수 있습니다. Fuzzy는 유사 조회를 수행하는 것이며, Exact는 정확히 일치하는 조회를 수행하도록 하는 것입니다. 모든 비교 열이 Exact일 때에는 조회 변환과 동일하게 수행됩니다. 유사 항목 조회 변환에 이러한 옵션이 있는 것은 다음과 같은 이유입니다. 예를 들어, A, B, C라는 원본 데이터의 열에 대해 A’, B’, C’라는 참조 테이블의 열과 유사 항목 조회 변환을 수행한다고 할 때, A 열과 A’ 열은 반드시 일치를 해야 하며, B와 B’, C와 C’는 어느 정도의 유사성만 있으면 매핑이 되도록 설정할 수 있습니다. 이 때, A와 A’의 매핑 유형은 Exact로 지정하고 나머지는 Fuzzy로 지정하면 됩니다.

 

비교 플래그는 문자열 비교 변환 또는 집계 변환 등에서와 같이 문자열 비교 시 비교 방법에 대해 설정합니다.

최소 유사성은 해당 열에 대한 매핑 작업 수행 시 매핑을 수행할 최소의 유사성을 지정하는 것입니다. 이 값은 0에서 1 사이의 값입니다. 예를 들어 B와 B’열 간의 매핑에서 최소 유사성을 0.80으로 설정한다면, 유사성이 0.8이상인 경우에만 매핑이 이루어집니다.

유사성 출력 별칭은 해당 열의 유사성을 출력할 열의 이름을 설정합니다.

 

 

 

고급 탭

 

 

조회당 출력에서 일치하는 최대 항목 수는 입력 데이터에 대해 유사 조회 연산을 수행하여 출력할 수 있는 최대의 항목 수를 지정하는 것입니다. 이 값을 1로 설정하면, 유사한 항목들로 판단되는 데이터 중 1개만 출력을 합니다. 예를 들어 “서울 강동구”라는 데이터에 대해 “서울시 강동구”가 유사도가 가장 높은 조회 데이터입니다. 하지만 “서울시 강서구”도 유사하다고 판단이 됩니다. 만약 이 값이 1인 경우에는 가장 유사하다고 판단되는 “서울시 강동구”가 출력되지만, 2로 설정한 경우, 두 개의 유사 데이터 모두가 출력됩니다.

 

유사성 임계값은 변환 전체의 유사성에 대한 임계값을 설정합니다. 이는 열 탭에서의 각 열의 최소 유사성과 비슷한 기능이지만, 전체 열에 대한 평균을 이용한다는 차이가 있습니다. 예를 들어 B와 B’열의 유사성은 0.9이며, C와 C’ 열의 유사성은 0.5 인 경우, 전체 유사성은 0.7이 됩니다. 만약 유사성 임계값을 0.8로 설정했다면, 이 경우에는 유사 조회가 수행되지 않습니다.

 

토큰 구분 기호는 입력 데이터를 비교 가능한 개별 토큰으로 구분할 때 사용되는 구분자(Delimeter)를 지정하는 것입니다.

 

 

 

 

참고 – 유사성(Similarity)과 신뢰성(Confidence)

 

유사 항목 조회 변환 및 유사 항목 그룹화 변환에서는 유사성과 신뢰성이라는 두 가지의 수치가 사용됩니다.

유사성은 입력 데이터와 참조 데이터 사이의 문자적 유사성에 대한 측정치 입니다. 예를 들어 “AAA AA”라는 참조 데이터에 대해 “AAA AB”가 “AAA BB” 보다 유사성이 높습니다. 입력 데이터와 참조 데이터가 완전히 동일한 경우에는 유사성은 1이며, 완전 불일치인 경우에는 0이 됩니다.

신뢰성은 출력되는 데이터가 참조 테이블의 다른 일치 데이터들과 비교했을 때 가장 유사한 일치 항목이 될 수 있는지의 가능성을 나타내는 수치입니다. 입력 데이터에 대해 선택한 참조 데이터가 다른 유사한 항목 없이 가장 확실한 대상이 된다고 판단되는 경우 1이며, 다른 비슷한 형태의 유사 데이터가 존재하는 경우에는 0에 가깝습니다.

 

“Chapter 4”라는 입력 데이터에 대해 “Chapter 1”, “Chapter 2”, “Chapter 3”이라는 조회 데이터가 있을 때, 세 개의 데이터 모두가 유사 항목의 후보가 되며, 전체 8자리 중 7자리가 동일하기 때문에 이 입력 값에 대한 각 참조 데이터의 유사성은 높습니다. 하지만, 세 개의 참조 데이터 중 어떤 것이 가장 일치할 지를 알 수 없기 때문에 각 참조 데이터에 대한 신뢰성은 낮습니다. 만약 입력 데이터가 “3”인 경우, 유사 항목의 후보가 될 수 있는 것은 “Chapter 3”만 가능합니다. 이 경우, 8자리 중 한 자리만 동일하기 때문에 유사성은 매우 낮지만, 유사 항목은 하나만 존재하기 때문에, 신뢰성은 높습니다.

(실제로 “Chapter 3”과 “3”으로는 유사성이 매우 낮기 때문에 유사 조회가 이루어지지 않습니다. 설명을 위해 예를 든 것입니다.)

 

 

 

따라하기

다음과 같은 형태의 주소 데이터에 대해 유사 항목 조회 변환을 수행하는 예제를 구현해 보겠습니다.

 

 

 

1.       쿼리 분석기를 이용하여 다음과 같은 참조 테이블을 생성합니다. 본 예제에서는 tempdb에 SIDO 라는 이름으로 테이블을 만들겠습니다.

USE tempdb GO   IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SIDO]') AND type in (N'U')) DROP TABLE [dbo].[SIDO] GO   CREATE TABLE SIDO ( Seq INT IDENTITY, Address VARCHAR(17) COLLATE Korean_Wansung_CI_AS NULL ) GO   INSERT SIDO(Address) VALUES ('서울시 강동구') INSERT SIDO(Address) VALUES ('서울시 강서구') INSERT SIDO(Address) VALUES ('서울시 송파구') INSERT SIDO(Address) VALUES ('서울시 강남구') INSERT SIDO(Address) VALUES ('부산시 해운대구') INSERT SIDO(Address) VALUES ('부산시 사하구') INSERT SIDO(Address) VALUES ('부산시 서구') GO

 

2.       빈 패키지 파일을 하나 추가한 후, 데이터 흐름 작업을 추가합니다.

 

3.       아래 부분에 있는 연결 관리자에서 tempdb를 지정하는 OLE DB 연결을 생성합니다. 이 연결은 유사 조회 변환에서 참조 테이블을 읽어오는 데 이용될 것입니다.

 

4.       연결 관리자에서 플랫 파일 연결을 생성합니다. 플랫 파일 연결의 원본 파일은 본 예제의 입력 데이터인 유사조회입력데이터.txt이며, 첫 번째 데이터의 행의 열 이름(A) 옵션을 선택하고 고급 탭에서 유형 제안(S)를 눌러 데이터의 유형을 자동으로 설정하도록 합니다.

5.       데이터 흐름 영역에서 플랫 파일 원본을 추가한 후, 4에서 추가한 플랫 파일 연결로 지정합니다.

 

6.       왼쪽 도구 상자에서 조회 변환을 하나 추가한 후, 플랫 파일 원본의 녹색 선과 연결합니다. 입력 데이터 중, 100% 일치하는 데이터를 먼저 추출하는 작업을 수행하도록 하는 것입니다.

 

7.       조회 변환 편집기에서 참조 테이블을 [dbo].[SIDO]로 설정하고, 열 탭에서 사용 가능한 입력 열의 주소 열과 사용 가능한 조회 열의 Address 열을 연결시킨 후, Address 열에 체크합니다.

8.       조회 변환 편집기의 아래 부분에 있는 오류 출력 구성(G)를 눌러 오류 출력 구성 창을 띄운 후, 오류 부분을 구성 요소 실패에서 행 리디렉션 으로 변경합니다. 조회 변환에서 조회가 이루어지지 않은 입력 데이터는 오류로 처리가 되어 별도의 경로로 출력 시키도록 하는 작업입니다. 확인을 눌러 오류 출력 구성 창을 닫은 후, 다시 확인을 눌러 조회 변환 편집기를 닫습니다.

9.       도구 상자에서 유사 항목 조회 변환을 하나 추가한 후, 조회 변환의 붉은 색 선과 연결 시킵니다. 연결 시킬 때, 자동으로 오류 출력 구성 창이 나타나는데, 이미 8에서 설정했기 때문에 확인을 누르시면 됩니다.

 

10.   유사 항목 조회 변환을 더블 클릭하여 나타나는 유사 항목 조회 변환 편집기의 참조 테이블 탭에서 조회할 테이블을 지정합니다. 조회할 테이블은 7에서와 같이 [dbo].[SIDO] 테이블입니다. 또한 열 탭에서도 동일하게 주소 열과 Address 열을 연결시킵니다.

11.   고급 탭에서 지정된 속성에 대해서는 별도의 변경 없이 기본값으로 적용하겠습니다. 예제가 완료된 후, 조회당 출력에서 일치하는 최대 항목 수(X) 값이나 유사성 임계값(H)등을 변경하여 테스트 해 보시기 바랍니다.

 

12.   도구 상자에서 UNION ALL 변환을 추가한 후, 조회 변환의 녹색 선과, 유사 항목 조회의 녹색 선을 연결합니다.

13.   데이터 흐름 영역의 빈 곳에서 마우스 오른쪽 클릭을 하여 나타나는 메뉴 중 변수(S)를 선택하여 변수 창을 띄운 후, tempResult라는 이름의 Object 형 변수를 하나 추가합니다. 이는 임시 데이터 대상으로 쓰일 레코드 집합 대상에서 이용할 임시 변수입니다.

14.   도구 상자에서 레코드 집합 대상을 추가한 후, UNION ALL 변환의 녹색 선과 연결합니다. 레코드 집합 대상을 더블 클릭하여 레코드 집합 대상 편집기를 띄운 후, 구성 요소 속성 탭에서 VariableName 부분에 tempResult를 입력합니다. 입력 열 탭에서는 세 개의 열을 모두 선택합니다.

15.   데이터가 처리되는 과정을 확인하기 위해 중간 경로에 데이터 뷰어를 추가합니다. 조회 변환과 UNION ALL 변환 간의 녹색 경로를 더블 클릭한 후 나타나는 데이터 흐름 경로 편집기의 데이터 뷰어 탭에서 추가(A)를 누른 후 표 형태의 뷰어를 추가합니다. 이와 동일한 방법으로 유사 항목 조회 변환과 UNION ALL 변환 사이의 경로 및 UNION ALL 변환과 레코드 집합 대상 간에도 데이터 뷰어를 추가합니다.

16.   패키지를 수행시켜 유사 항목 조회가 수행되는 결과를 확인합니다.