유사 항목 조회 – 데이터 클렌징 하기

유사 항목 조회 – 데이터 클렌징 하기

 

안녕하세요 강성욱 입니다.(www.sqltag.org, www.sqler.com, http://blog.naver.com/jevida)

이번 시간에는 [유사 항목 조회]에 대해서 알아 보도록 하겠습니다.

 

[유사 항목 조회] SQL Server 2012의 DQS 서비스의 일부 기능 처럼 표준화 되지 않은 데이터에 대해 기준 데이터와 가장 유사한 값을 조회하고 조회된 데이터와의 유사도, 신뢰도를 판단해 주는 작업을 합니다.

 

[유사 항목 조회 변환]은 [용어 추출]과 비슷합니다. [용어 추출]은 [참조 테이블]의 비교 항목과 일치하는 경우에만 출력하지만 [유사 항목 조회] 변환은 정확히 일치 하지 않더라도 유사하다고 판단될 경우 데이터를 출력 합니다.

[용어 추출] 관련 링크

http://blog.naver.com/jevida/140159227758

 

[BIDS]를 실행하여 [Integration Services 프로젝트]를 생성 합니다.

 

[제어 흐름] 탭에서 [데이터 흐름 태스크]를 드래그 앤 드롭으로 추가 합니다.

[유사 항목 조회]이름을 지정하여 태스크를 생성 합니다.

 

[데이터 흐름]탭에서 그림과 같이 [원본 데이터(OLE DB 원본)]와 [대상 데이터(OLE DB 대상)]을 끌어다 놓습니다. 그 사이 [유사 항목 조회]작업을 추가하여 데이터 흐름선을 연결 합니다.

 

[원본 데이터]에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

 

그림과 같이 편집 창이 나타나면 [OLE DB 연결 관리자]에서 원본 DB의 연결 관리자를 선택하고 해당 테이블을 선택하여 [미리보기]를 이용하여 정상적으로 데이터가 읽어오는지 확인 합니다.

(본 실습에서는 Adventureworks의 Person.Address 테이블 사용)

 

[유사 항목 조회]작업에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

 

아래 그림과 같이 [유사 항목 조회 변환 편집기]가 실행 됩니다.

[참조 테이블]에 데이터 원본을 지정하고 [참조 테이블]을 지정 합니다.

 

[유사 조회 변환]에서 사용할 참조 테이블을 생성합니다. SSMS를 실행하여 아래 쿼리를 이용하여참조 테이블을 생성합니다.

create table MasterData ( Address_Data nvarchar(500)) GO   INSERT MasterData select 'May Way' union all select 'Westwood ' union all select 'University' union all select 'Clair Ave East' union all select 'College' union all select 'Colony Mall'

 

 

다시 편집기 창으로 돌아와서 [참조 테이블]탭에서 앞서 만들었던 참조 테이블 [MasterData]를 선택 합니다.

 

• 참조 테이블 – 조회 대상이 되는 메타 테이블.
• 새 인덱스 저장 – 참조 테이블이 존재하는 데이터베이스에 지정한 이름의 인덱스가 생성됨.
• 저장된 인덱스 유지 관리 – 참조 테이블에 인덱스의 정보를 관리할 트리거가 생성됨. 입력 데이터가 자주 변경되며 유사 항목 조회 작업을 반복해서 수행할 경우 이 옵션을 이용하여 인덱스 테이블을 효과적으로 관리 가능.
• 기존 인덱스 사용 – 이미 생성되어 있는 인덱스 사용

 

[열] 탭에서는 입력 데이터와 조회 데이터간의 매핑을 설정 합니다.

빈 화면에서 마우스 오른쪽을 클릭하여 [매핑 편집]을 실행 합니다.

 

아래 그림과 같이 [관계 만들기] 창이 나타 납니다.

[입력 열]과 [조회 열(참조 테이블 열)]을 선택합니다.

[매핑 유형]에는 두 가지가 유형이 있습니다.

• Fuzzy –유사 조회를 수행 하는 것.
• Exact – 정확히 일치하는 조회를 수행하도록 하는 것. 모든 비교열이 Exact일 때에는 조회 변환과 동일하게 수행 됩니다.

[비교 플래그] 에서는 문자표 변환 또는 집계 변환 등에서와 같이 문자열 비교 작업에 대한 속성을 설정 합니다.

• 문자표 변환 참조 링크

http://blog.naver.com/jevida/140158797722

• 집계 변환 참조 링크

http://blog.naver.com/jevida/140158945633

[최소 유사성]에는 해당 열에 대한 매핑 작업 수행 시 매핑을 수행할 최소의 유사성을 지정 합니다. 예를들어 유사성이 0.80으로 설정한다면 유사성이 0.80 이상인 경우에만 매핑이 이루어 집니다.

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

 

[고급] 탭에서는 일치 하는 최대 항목 수 임계값 등을 상세히 설정 할 수 있다.

• 조회당 출력에서 일치하는 최대 항목 수 – 입력 데이터에 대해 유사 조회 연산을 수행 한 후 출력할 수 있는 최대 항목 수를 지정 합니다. 이 값을 1로 설정할 경우 유사한 항목으로 판단되는 데이터들 중에서 가장 유사성이 높은 항목 1개만 출력 됩니다.
• 유사성 임계값 – 변환 전체의 유사성에 대한 임계값을 설정 합니다. [열 탭]에서의 [최소 유사성]과 비슷한 기능이지만 [전체 열의 평균값]을 이용하여 판단 하는 점이 다릅니다.
• 토큰 구분 기호 – 입력 데이터를 비교 가능한 개별 토큰으로 구분 할 때 사용되는 구분 기호를 지정하는 부분 입니다.

 

[대상 데이터] 작업에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

 

[OLE DB 연결 관리자]에서 대상 DB의 연결 관리자를 선택 합니다. 그리고 [새로 만들기] 버튼을 클릭하여 대상 테이블을 새로 만들어 봅니다. (이미 대상 테이블이 존재하는 경우 [테이블 또는 뷰 이름]에서 대상 테이블을 선택 합니다.)

[새로 만들기]를 선택하면 [참조 열]에 대한 컬럼과 [유사성], [임계값], [유사 데이터] 컬럼이 생성됨을 확인 할 수 있습니다.

 

[매핑] 탭으로 이동하여 대상이 적절히 매핑 되었는지 확인 합니다.

 

편집이 완료 되었으면 패키지를 실행 합니다.

늘 그러듯!

녹색 : 정상 실행

빨간색 : 오류 발생

 

패키지 작업이 완료 되었으면 SSMS를 실행하여 데이터를 확인 합니다.

 

데이터를 확인해 보면 유사성, 임계값 등을 확인 할 수 있습니다.

 

[유사 항목 조회] 작업은 우편 주소나 정제되지 않은 데이터 집계 또는 정제시 매우 유용하게 사용될 듯 합니다.

SQL Server 2012에는 DQS기능이 새롭게 추가 되었습니다.