SQL Server 2005 데이터 변환 서비스의 퍼지 조회 및 퍼지 그룹화
Surajit Chaudhuri, Kris Ganjam, Venky Ganti, Vivek Narasayya, and Theodore Vassilakis
Microsoft Corporation
적용 대상
Microsoft SQL Server 2005
요약 : 본 문서에서는 SQL Server 2005 DTS(데이터 변환 서비스)의 퍼지 조회 변환 및 퍼지 그룹화 변환에 대해 설명합니다. 퍼지 그룹화 변환은 대상 데이터베이스에서의 데이터 품질을 향상시키는 데 유용합니다. 퍼지 조회는 맞춤법 오류, 잘림, 토큰 누락 또는 삽입, null 필드, 예상치 못한 약어 및 기타 불규칙으로 인한 "더티(dirty)" 상태의 입력 내용을 참조 테이블에 있는 올바른 레코드에 맞춥니다. 퍼지 그룹화는 입력된 행 사이의 유사점을 검색하고 해당 문자열 값을 사용하여 어떤 행이 중복되었는지 확인합니다. 퍼지 그룹화 및 퍼지 조회는 데이터 웨어하우징에서 흔히 수행하는 다양한 데이터 정리 및 준비 작업을 단순화할 수 있는 유용한 기본 기능입니다. 이러한 기능을 도메인에 맞게 사용자 지정하면 복잡한 사용자 지정 코드 없이도 DTS 디자이너 내에서 일반 검색 및 클러스터링 알고리즘을 활용할 수 있습니다.
목차
소개
퍼지 조회 시작
퍼지 조회 최대한 활용하기
퍼지 그룹화 시작
퍼지 그룹화 최대한 활용하기
성능 이해
결론
추가 정보
참고 본 문서는 SQL Server 2005의 베타 2 릴리스를 참조합니다. 최종 릴리스 이전에 내용이 변경될 수도 있습니다.
소개
실제 데이터는 맞춤법 오류, 잘림, 토큰 누락 또는 삽입, null 필드, 예상치 못한 약어 및 기타 불규칙으로 인해 "더티(dirty)" 상태입니다. 따라서 대부분의 시간과 비용이 데이터 웨어하우징 프로젝트에서 ETL(추출, 변환 및 로드) 단계를 수행하는 데 소요됩니다. ETL을 수행하는 동안 새 데이터는 정리 및 표준화되고 기존 데이터에 맞게 조정됩니다. Microsoft SQL Server 2005에서 사용 가능한 퍼지 조회 및 퍼지 그룹화 변환은 실제 데이터에서 흔히 발견되는 여러 가지 오류를 ETL 프로세스에서 탄력적으로 해결할 수 있도록 합니다. 이러한 변환은 전문적인 도메인 관련 규칙 및 스크립트 모음 없이도 일반 일치 및 그룹화 문제를 해결합니다. 도메인에 맞게 퍼지 조회 및 퍼지 그룹화를 사용자 지정하면 DTS(데이터 변환 서비스) 디자이너 내에서 일반 데이터 정리 알고리즘을 활용할 수 있으며 복잡한 사용자 지정 규칙 및 코드를 빌드하지 않아도 됩니다.
퍼지 조회를 사용하면 입력 레코드를 참조 테이블의 정리되고 표준화된 레코드에 맞출 수 있습니다. 일치 프로세스는 입력 레코드에 있는 오류를 탄력적으로 해결합니다. 퍼지 조회는 가장 근접하게 일치하는 내용을 반환하고 일치 정도를 표시합니다. 예를 들어 입력 데이터의 오자나 기타 오류로 인해 새 판매 거래 동안 입력된 고객 정보(이름 및 주소)가 현재의 모든 고객이 포함되어 있는 Customers 참조 테이블의 어떤 레코드와도 정확히 일치하지 않을 수 있습니다. 이 경우 퍼지 조회는 정확히 일치하는 내용이 없더라도 Customers 참조 테이블에서 가장 일치하는 레코드를 반환하고 일치 정도를 표시합니다.
퍼지 그룹화를 사용하면 각 그룹이 잠재적으로 실제 엔터티에 해당하는 테이블에서 레코드 그룹을 식별할 수 있습니다. 각 그룹에 있는 레코드가 서로 정확하게 일치하지는 않지만 매우 유사하므로 그룹화는 실제 데이터에서 흔히 발생하는 오류를 탄력적으로 해결합니다. 예를 들어 퍼지 그룹화는 Customers 참조 테이블에서 실제로 한 명의 고객을 나타내는 모든 레코드를 그룹화하는 데 유용합니다.
퍼지 조회 및 퍼지 그룹화는 흔히 발생하는 복잡한 데이터 정리 문제에 대해 간편한 솔루션을 제공합니다. soundex, 규칙 기반 시스템, 편집 거리 기반 시스템(Edit-distance-based systems), 전체 텍스트 검색 등의 기존 방법과 비슷한 면도 있지만 퍼지 조회 및 퍼지 그룹화는 다음과 같은 여러 가지 장점을 갖고 있습니다.
퍼지 조회 및 퍼지 그룹화는 편집 거리(예: "hits"와 "bit"의 편집 거리는 2), 토큰 수, 토큰 순서 및 상대적인 빈도를 고려하는 도메인 독립적인 사용자 지정 Distance 함수를 사용합니다. 따라서 퍼지 조회 및 퍼지 그룹화는 좀 더 자세한 데이터 구조를 파악하기 때문에 전체 텍스트 검색보다 훨씬 세부적인 판별이 가능합니다.
퍼지 조회 및 퍼지 그룹화는 전적으로 토큰에 기반하는 기능이므로 soundex와 같은 언어 종속적인 구성 요소가 없습니다.
퍼지 조회 및 퍼지 그룹화는 단순히 편집 거리만 사용하는 것이 아니므로 위치 치환으로 인한 판독 오류가 적으며 편집 거리만 사용하는 방식보다 높은 수준의 패턴을 검색할 수 있습니다.
퍼지 조회 및 퍼지 그룹화는 DTS에 밀접하게 통합되어 있으므로 SQL Server 2005에서 거의 아무런 사용자 지정 프로그래밍 없이 ETL 작업에 쉽게 사용할 수 있습니다.
다음 절에서는 퍼지 조회 및 퍼지 그룹화를 사용하고 이해할 수 있도록 하는 단계별 안내를 제공하며 이와 같이 사용자에게 유용한 변환의 몇 가지 구현 및 성능 측면을 다룹니다. 본 문서는 퍼지 조회 및 퍼지 그룹화의 특정 측면을 자세히 설명하여 온라인 설명서를 보충하기 위한 것입니다. 옵션 및 구성 매개 변수에 대한 자세한 내용은 온라인 설명서 항목을 참조하십시오. 이 항목에는 열 가중치, 계층, 토큰 처리 옵션 및 기타 유용한 매개 변수에 대한 정보가 들어 있습니다. 이러한 정보를 통해 얻은 도메인 지식을 활용하여 특정 시나리오에서의 정확성을 높일 수 있습니다.
퍼지 조회 시작
퍼지 조회는 손상되거나 불완전한 문자열 키를 사용하여 큰 테이블에서 데이터를 찾을 수 있습니다. 예를 들어 이름 및 주소로 고객 정보를 조회하려는 경우 입력 내용이 참조 테이블에 저장된 내용과 정확히 일치하지 않더라도 퍼지 조회를 사용하여 정보를 찾을 수 있습니다. 퍼지 조회에서 사용할 수 있는 가장 간단한 패키지는 원본, 퍼지 조회 변환 및 대상이 포함되어 있는 단일 DTS 데이터 흐름 작업으로 구성되어 있습니다(그림 1 참조).
.gif "fzdtssql0501_big.gif")
그림 1. 가장 간단한 퍼지 조회 패키지
가장 간단한 퍼지 조회 패키지를 만드는 방법
DTS 디자이너를 엽니다.
새 ETL 프로젝트를 만들어 새 패키지를 추가하고 Data Flow(데이터 흐름) 탭을 클릭한 다음 add a data flow(데이터 흐름 추가) 항목 옵션을 선택합니다.
데이터 흐름 표면에서 OLE DB 원본 및 대상 변환을 도구 상자로부터 끌어온 다음 퍼지 조회 인스턴스를 사용하여 연결합니다.
연결을 선택하고 더티 데이터가 들어 있는 입력 테이블을 선택하여 새 데이터에 있는 OLE DB 원본을 가리킵니다. 데이터에는 문자열 열이 일부 들어 있어야 합니다.
Fuzzy Lookup(퍼지 조회)을 두 번 클릭하여 사용자 지정 UI(사용자 인터페이스)를 엽니다. Reference table name(참조 테이블 이름) 드롭다운 메뉴에서 이미 웨어하우징된 참조 데이터로 변환하려는 연결 및 테이블을 선택하여 가리킵니다.
열 탭에서 비교할 항목을 Available Input Columns(사용 가능한 입력 열)(OLE DB 원본)에서 Available Lookup Columns(사용 가능한 조회 열)(참조 테이블)로 끕니다. 예를 들어 입력 내용에 있는 StreetAddress를 참조 테이블에 있는 Address와 비교할 수 있습니다.
Available Lookup Columns(사용 가능한 조회 열)에 있는 모든 항목의 확인란을 선택한 다음 확인을 클릭합니다.
새 테이블에 쓸 수 있는 연결에 OLE DB 대상을 가리키고 New(새로 만들기)를 클릭합니다. 기본 작성 문을 받아들이면 이제 퍼지 조회를 실행할 준비가 된 것입니다.
방금 만든 패키지를 실행하려면 솔루션 탐색기 창에서 해당 이름을 마우스 오른쪽 단추로 클릭한 다음 Execute(실행)를 선택합니다.
DTS 디자이너는 패키지를 실행하고 파이프라인에 대해 자세한 시각적 피드백을 제공합니다. 참조 데이터의 크기에 따라 ETI(오류 허용 인덱스)가 빌드되는 동안 잠시 지연될 수도 있습니다. ETI는 퍼지 조회가 런타임에 사용하는 주 데이터 구조입니다.
ETI가 빌드되면 모든 입력 행이 처리되고 결과가 대상에 작성됩니다. DTS 디자이너는 각 구성 요소에서 처리한 행 수를 표시하여 파이프라인의 진행 상황에 대한 피드백을 제공합니다. 퍼지 조회 및 OLE DB 대상 사이의 커넥터를 마우스 오른쪽 단추로 클릭하여 파이프라인에 DataViewer를 넣을 수도 있습니다. 그러면 퍼지 조회가 입력 행에 일치시킨 행을 실시간으로 확인할 수 있습니다. 일치하는 튜플 외에도 퍼지 조회는 신뢰도 및 유사도 점수를 출력합니다. 신뢰도 및 유사도 점수에 대한 자세한 내용은 이 문서의 뒷부분에 있는 결과 해석을 참조하십시오.
퍼지 조회 최대한 활용하기
퍼지 조회를 실행하는 주요 단계에는 ETI 빌드, 조회 수행, 출력 검사 등이 있습니다. 다음 절에서는 이러한 각 단계에 대해 자세히 설명합니다.
오류 허용 인덱스 이해
퍼지 조회는 참조 데이터 및 참조 행 ID에서 발생하는 토큰을 인덱싱하여 ETI를 빌드합니다. ETI를 서버에 저장하면 해당 ETI에서 일부 행을 선택하여 그 내용을 볼 수 있습니다. 각 행은 인덱싱된 토큰과 해당 토큰을 포함하는 일련의 참조 행 ID로 구성되어 있습니다. 주소 예제에서 참조 데이터가 13831 N.E. 8th St를 포함하는 경우 ETI에 포함되는 토큰 항목은 13831, N, E, 8th 및 **St.**입 니다. ETI는 참조 데이터에 따라 증가합니다.즉, 참조 테이블에 고유한 토큰과 행이 많을수록 ETI의 항목이 많아지고 목록이 길어집니다. 참조 데이터에 따라 ETI의 크기가 증가하는 방법에 대한 자세한 내용은 이 문서의 뒷부분에 있는 성능 이해를 참조하십시오. 토큰화 프로세스는 구분 기호 문자열이라는 퍼지 조회 사용자 지정 속성으로 제어됩니다. 예를 들어 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를 최신 상태로 유지합니다. 테이블 유지 관리를 설치하지 않으면 참조 테이블에서 변경된 내용으로 인해 연결된 모든 ETI가 경고 없이 무효화됩니다.
참고 베타 2 릴리스에서는 테이블 유지 관리 기능을 사용할 수 없습니다.
런타임에 수행되는 작업
런타임에서, 퍼지 조회는 ETI를 사용하여 입력 내용과 가장 일치하는 내용을 찾습니다. 가장 일치하는 내용을 찾을 때 무엇보다 중요한 매개 변수는 MinSimilarity 임계값입니다. 퍼지 조회 UI를 사용하여 이 사용자 지정 속성을 설정할 수 있습니다. 입력 내용과 충분히 비슷할 경우에만 참조 튜플이 반환됩니다. 따라서 유사도 요구 사항을 높게 설정하면 퍼지 조회가 좀 더 적은 후보 개체 중에서 검색하므로 아무런 일치 내용이 반환되지 않을 수도 있습니다. MinSimilarity를 낮게 설정하면 퍼지 조회가 더 많은 후보 개체 중에서 검색하므로 일치 내용은 반환되지만 시간이 오래 걸릴 수 있습니다.
일치 조건에는 다음이 포함됩니다.
입력 튜플을 주어진 참조 튜플에 맞추는 데 필요한 토큰 또는 문자 삽입, 삭제, 대체 및 재정렬 수가 포함됩니다. 예를 들어 22 N.E. 1st Ln & Leary Way라는 입력 항목보다는 122 First Lane이라는 입력 항목이 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개의 일치 항목을 반환합니다. 그러나 각 입력 레코드의 일치 항목을 조회하는 데 필요한 시간이 늘어날 수도 있습니다. 퍼지 조회는 MinSimilarity가 0으로 설정되어 있는 경우라도 특정 행이 너무 다르다고 판단하면 반환하지 않을 수 있으므로 n 값을 높게 설정한다고 해서 항상 n개의 일치 항목이 반환되는 것은 아닙니다. 성능에 미치는 영향에 대한 자세한 내용은 이 문서의 뒷부분에 있는 성능 이해를 참조하십시오.
퍼지 조회는 또한 일치하는 각 참조 레코드에 대한 신뢰도를 반환합니다. 모든 일치 항목의 유사도가 동일한 경우에는 각각의 신뢰도 점수가 대략 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와 같이 높은 수로 올리고자 할 수 있습니다. 고급 편집기를 열려면 디자이너에서 퍼지 조회 구성 요소를 클릭한 다음 속성 창에서 Show Advanced Editor(고급 편집기 표시)를 클릭합니다. 또는 Conditional Split(조건부 분할) 변환을 사용하여 State에서 정확히 일치하는 내용을 찾는 조회를 먼저 수행한 다음 정확히 일치하는 내용이 없으면 퍼지 조회를 수행할 수 있습니다.
퍼지 그룹화 시작
퍼지 그룹화를 사용하여 문자열 특성이 있는 행 컬렉션에서 "퍼지(fuzzy)" 또는 유사 중복 내용을 검색할 수 있습니다. 예를 들어 퍼지 그룹화를 사용하여 다른 부서의 고객 테이블을 통합할 수 있습니다. 퍼지 그룹화에서 사용할 수 있는 가장 간단한 패키지는 원본, 퍼지 그룹화 변환 및 대상이 포함되어 있는 단일 DTS 데이터 흐름 작업으로 구성되어 있습니다(그림 2 참조).
.gif "ms345128.fzdtssql0502_big(ko-kr,SQL.90).gif")
그림 2. 가장 간단한 퍼지 그룹화 패키지
가장 간단한 퍼지 그룹화 패키지를 만드는 방법
DTS 디자이너를 엽니다.
OLE DB 원본 및 대상 변환을 데이터 흐름으로 끌어온 다음 퍼지 그룹화 인스턴스를 사용하여 연결합니다.
연결을 선택하고 테이블 이름을 선택하여 잠재적 중복 내용이 있는 데이터를 포함하는 테이블에 있는 OLE DB 원본을 가리킵니다.
참고 이 테이블에 문자열 열이 일부 들어 있어야 퍼지 그룹화가 분석할 수 있습니다.
Fuzzy Grouping(퍼지 그룹화)를 두 번 클릭하여 사용자 지정 UI를 열고 Available Input Columns(사용 가능한 입력 열)(OLE DB 원본)에 있는 모든 항목의 확인란을 선택한 다음 확인을 클릭합니다.
새 테이블에 쓸 수 있는 연결에 OLE DB 대상을 가리키고 New(새로 만들기)를 클릭합니다. 기본 작성 문을 받아들이면 이제 퍼지 그룹화를 실행할 준비가 된 것입니다.
방금 만든 패키지를 실행하려면 솔루션 탐색기 창에서 해당 이름을 마우스 오른쪽 단추로 클릭한 다음 Execute(실행)를 선택합니다.
입력 데이터의 크기에 따라 퍼지 그룹화가 데이터를 버퍼링하는 동안 잠시 지연될 수 있습니다. 행이 파이프라인 아래로 흘러 내려가기 전에 퍼지 그룹화는 퍼지 조회를 호출하여 입력 데이터의 ETI를 빌드하고 임시 연결에 저장합니다. 따라서 큰 개체가 해당 연결에 배치될 수 있습니다. 입력 내용에 대한 ETI가 빌드되면 모든 입력 행이 처리되고 결과가 대상에 작성됩니다. 그룹화 수행 방법에 대한 자세한 내용은 이 문서의 뒷부분에 있는 퍼지 그룹화 최대한 활용하기를 참조하십시오.
기본적으로 퍼지 그룹화는 _key_out 및 _key_in을 호출한 일부 열을 추가로 출력합니다. 행이 파이프라인을 통해 흐르면 퍼지 그룹화는 각 행에 ID를 할당합니다. 이 ID가 _key_in입니다. 퍼지 그룹화는 특정 행 컬렉션을 그룹화할 때 대표가 되는 행을 결정합니다. 그런 다음 그룹에 있는 행의 모든 _key_out 열을 대표의 _key_in 값에 할당합니다. 따라서 대표 행만 출력에 작성하려는 경우 _key_in이 _key_out과 일치하는 행만 선택하는 Conditional split(조건부 분할) 변환을 통해 퍼지 그룹화 출력을 필터링하면 됩니다.
퍼지 그룹화 최대한 활용하기
퍼지 그룹화에는 조정할 외부 매개 변수가 퍼지 조회보다 좀 더 많습니다. 그러나 해당 매개 변수의 몇 가지 내부 매개 변수를 이해하면 성능을 최적화하는 데 도움이 될 수 있습니다. 퍼지 그룹화는 퍼지 조회를 사용하여 그룹화를 수행합니다. 예를 들어 퍼지 그룹화는 토큰화 문자열을 퍼지 조회에 그대로 전달합니다. 런타임에 퍼지 그룹화는 퍼지 조회를 사용하여 입력 데이터에 대한 임시 ETI를 빌드함으로써 어떤 입력 행이 서로 유사한지 결정합니다. 퍼지 그룹화는 각 입력 행을 조사하고 MinSimilarity 임계값을 빠르게 설정하면서 데이터에 대해 다양한 퍼지 조회 쿼리를 수행합니다. 반환되는 결과 수에 따라 그룹이 생성됩니다.
퍼지 조회와 마찬가지로 퍼지 그룹화를 사용하려면 MinSimilarity 임계값을 설정해야 합니다. 각각의 유사도가 UI에 설정된 임계값을 초과할 경우에만 튜플이 함께 그룹화됩니다. 따라서 임계값을 낮게 설정하고 퍼지 그룹화를 실행하면 전혀 다른 튜플도 함께 그룹화되므로 유사도를 높여야 합니다. 퍼지 그룹화는 각 튜플의 유사도를 그룹의 대표 튜플에 보고합니다. 대표 튜플의 선택에 영향을 줄 수 있는 방법은 없습니다. 그룹을 분할하려면 필요한 유사도를 보고된 숫자보다 높게 설정하십시오. 그룹화되는 튜플이 너무 적은 경우 유사도 값에 대한 이진 검색을 수행하면 특정 튜플을 그룹화하기 위해 유사도 임계값을 얼마나 낮게 설정해야 하는지 알아낼 수 있습니다. 예를 들어 0.9로 설정하면 그룹이 너무 적고 0.7로 설정하면 너무 많은 경우 0.8로 설정해 보십시오.
참고 퍼지 그룹화가 두 개의 튜플을 강제로 그룹화하도록 할 수 있는 방법은 없습니다. 유사도를 0으로 설정해도 모든 튜플이 반드시 한 그룹에 포함되어 반환되는 것은 아닙니다. 그룹 품질은 데이터의 특정 의미에 크게 영향을 받으므로 응용 프로그램에 알맞은 설정을 찾아내려면 계속 적용해 보는 수밖에 없습니다.
또는 퍼지 조회를 사용하여 입력 데이터의 ETI를 빌드한 다음, 반환할 일치 항목 수에는 높은 값을 사용하고 필요한 유사도에 높은 임계값을 사용하여 해당 행에 대한 조회를 수행할 수 있습니다. 그러나 퍼지 그룹화에는 튜플을 중복 집합으로 적절히 그룹화하고 이 중 하나를 표준 대표로 제안하는 추가 기능이 통합되어 있습니다.
성능 이해
퍼지 조회 및 퍼지 그룹화는 단순한 인터페이스를 갖고 있지만 복잡한 프로세스이므로 성능을 이해하려면 몇 가지 분석이 필요합니다. 다음 절에서는 공통적인 성능 문제점을 설명하고 샘플 측정을 제공합니다. 다음에 나오는 바와 같이, 퍼지 조회 및 퍼지 그룹화 성능을 결정하는 기본 요소는 데이터 크기입니다. 퍼지 조회 및 퍼지 그룹화에서 데이터 크기란 입력 행, 토큰 및 바이트 수를 의미합니다. 퍼지 조회에는 참조 데이터 크기와 관련하여 추가로 고려해야 할 사항이 있습니다. 성능을 결정하는 두 번째 요소는 사용 가능한 컴퓨팅 리소스입니다. 여기에는 메모리, 데이터베이스 서버 공간, 네트워크 대역폭 및 이러한 리소스의 설정 방법 등이 포함됩니다.
설정 고려 사항
퍼지 조회 및 퍼지 그룹화에서 최적의 성능을 얻으려면 메모리 사용 및 아키텍처와 관련된 몇 가지 지침을 따르는 것이 좋습니다.
컴퓨터에서 메모리 요구 사항을 최소화하는 방법
전체 DTS 디자이너를 사용하는 대신 메모리를 적게 차지하는 DTExec.exe를 사용하여 작성 중인 패키지를 실행합니다.
파이프라인에서 메모리를 차지하는 불필요한 열을 삭제합니다.
varchar 열은 DTS 파이프라인에 의해 고정 폭 char 열로 변환되므로 되도록 짧게 입력합니다.
열에 있는 가장 긴 항목과 동일한 길이의 고정 길이 char 필드로 varchar를 제한하는 변환을 통해 모든 데이터를 전달합니다.
DTS 클라이언트와 서버를 동일한 컴퓨터에서 실행하기로 한 경우 잠재적인 네트워크 문제를 피할 수는 있지만 메모리가 충돌할 수 있습니다. sp_configure를 사용하고 set max server memory(최대 서버 메모리 설정) 설정을 256으로 설정하면 두 프로세스 간의 메모리 충돌을 완화할 수 있습니다. DTS 디자이너에서 고급 편집기를 사용하여 퍼지 조회 사용자 지정 속성인 MaxMemoryUsage를 설정할 수도 있습니다.
다음 절에서 보고된 경험적인 추세선은 모두 다음 설정을 사용하여 얻은 것입니다.
Intel Pentium III 1 GHz 프로세서, 512 MB RAM, Microsoft Windows Server 2003 Enterprise Edition을 갖춘 서버.
DTS 클라이언트와 동일한 컴퓨터에서 실행되는 SQL Server.
참조 테이블이 일반 입력 테이블보다 훨씬 큰 반복적 퍼지 조회 작업의 경우에는 인덱스를 미리 컴퓨팅하는 것을 고려해야 합니다. 그러한 경우에는 인덱스를 다시 빌드하는 작업이 실제 조회 실행 시간의 대부분을 차지하므로 추가 테이블을 관리하는 것이 좋습니다.
대규모 퍼지 조회 입력 작업의 경우 중앙 서버에서 여러 개의 DTS 클라이언트를 함께 사용하는 것을 고려해 볼 수도 있습니다. 이 설정에서는 중앙 서버가 참조 테이블과 미리 컴퓨팅된 ETI를 호스트합니다. 각 DTS 클라이언트는 중앙 참조 테이블 및 ETI를 사용하여 대규모 입력 테이블 작업의 일부분에서 퍼지 조회를 수행합니다. 퍼지 조회가 완료되면 개별 DTS 클라이언트에서 만든 모든 출력 테이블을 다시 병합합니다.
참고 이 아키텍처는 퍼지 그룹화에 적용되지 않습니다.
측정
퍼지 조회 및 퍼지 그룹화 성능에 가장 큰 영향을 미치는 것은 사용되는 데이터의 크기입니다. 퍼지 조회의 경우 참조 및 입력 테이블의 크기가 이에 해당하며,퍼지 그룹화의 경우에는 입력 테이블 크기가 이에 해당합니다. 입력 크기는 다음 두 가지 방법으로 영향을 미칩니다.
행 및 열 수가 성능에 가장 큰 영향을 미칩니다. 데이터가 많을수록 퍼지 조회 및 퍼지 그룹화에 더 많은 리소스가 필요합니다. 다음 절에 나와 있는 그림은 다양한 시나리오에 대한 특정 데이터를 보여 줍니다.
퍼지 일치가 수행되는 문자열 열당 평균 토큰 수도 성능에 영향을 미칩니다. 퍼지 변환은 문서 검색을 위한 것이 아닙니다. 필드가 긴 경우(20 토큰 초과) SQL Server 전체 텍스트 인덱싱 기능을 사용하는 것이 더욱 효율적입니다.
다음 절에서는 퍼지 조회 및 퍼지 그룹화 성능에 대한 몇 가지 자세한 그래프를 소개합니다. 이 그래프는 변환 추세를 보여 주기 위한 것이며 절대적인 수치를 제공하기 위한 것은 아닙니다. 일부 그래프는 기본 측정에서 증가 또는 감소하는 부분만 나타냅니다. 그러한 그래프를 사용하면 입력 크기가 증가할 때 실행 시간이 배로 늘어날 수 있다는 것을 대략적으로 유추할 수 있습니다.
퍼지 조회 성능
퍼지 조회 성능을 결정하는 두 가지 주요 변수는 참조 데이터 및 입력 데이터의 크기입니다. 이러한 변수는 퍼지 조회의 두 단계, 즉 ETI를 빌드하는 작업과 실제로 조회를 수행하는 작업에 해당합니다. 일반적으로 이러한 작업은 관련 입력 내용의 크기와 정비례합니다. 입력 내용의 크기에 따라 실제 조회 시간은 ETI 빌드 시간보다 적거나 많이 걸릴 수 있습니다.
그림 3에서는 ETI를 빌드하는 데 필요한 시간과 참조 테이블 크기를 비교하여 보여 줍니다. 앞에서 설명했듯이 ETI를 빌드하는 작업은 한 번만 수행하고 결과를 저장할 수 있으므로 매번 비용을 지불할 필요가 없습니다. 인덱싱된 참조 열 크기에 비해 최종 ETI의 크기는 최대 두 배에 이릅니다. 큰 ETI를 빌드하는 동안 일부 DTS 파이프라인은 중지된 것처럼 보일 수 있습니다. 진행 상황은 서버에서 테이블의 크기가 늘어나는 것을 통해 알 수 있습니다.
.gif "ms345128.fzdtssql0503_big(ko-kr,SQL.90).gif")
그림 3. ETI 작성 시간과 참조 테이블 크기 비교
ETI 크기도 실행 시간에 영향을 미칩니다. ETI에 데이터가 많이 들어 있을수록 더 많은 검색 공간을 나타내므로 사용하는 데 더 많은 비용이 듭니다. 그림 4에서는 고정 입력 크기에 대한 퍼지 조회의 런타임 성능과 참조 테이블 크기를 비교하여 보여 줍니다. 그림 4의 곡선은 토큰이 여러 행에 배포되어 있는지에 따라서도 달라집니다. 이는 조회 시간에 큰 영향을 미칩니다. 메모리가 많을수록 큰 참조 테이블과 ETI의 효과가 감소할 수 있습니다. 런타임에서 퍼지 조회는 메모리에 있는 ETI 부분을 MaxMemoryUsage 사용자 지정 속성에 지정된 제한량만큼 캐시합니다. 이 한도를 설정하려면 고급 편집기를 사용해야 합니다.
.gif "ms345128.fzdtssql0504_big(ko-kr,SQL.90).gif")
그림 4. 퍼지 조회 런타임과 참조 테이블 크기 비교
데이터 크기 다음으로 성능에 가장 큰 영향을 미치는 것은 반환할 일치 항목 수, 필요한 유사도 임계값, 일치시킬 열 수 등의 나머지 변환 매개 변수입니다.
퍼지 조회에서 반환해야 하는 일치 항목이 많을수록 검색 속도가 느려집니다. 이는 충분한 일치 항목을 발견할 때까지 후보 튜플 목록에서 검색 작업이 계속 수행되기 때문입니다.
필요한 유사도 인수가 클수록 퍼지 조회 검색 속도는 빨라집니다. 유사도를 높게 설정하고 검색하면 퍼지 조회가 튜플을 더 과감하게 버릴 수 있기 때문입니다.
퍼지 그룹화 성능
퍼지 그룹화에는 퍼지 조회보다 영향을 미치는 변수가 적습니다. 퍼지 그룹화 성능에 영향을 미치는 주요 변수는 입력 데이터 크기입니다. 그림 5에서는 입력 크기가 커질수록 비용이 정비례하는 것을 보여 줍니다. 그래프의 앞부분에서 반비례하는 것은 ETI의 빌드 시간 때문입니다. 일치 임계값이 커지면 그룹화할 튜플이 발견되지 않으므로 결과적으로 실행 시간이 줄어듭니다. 이는 그림 6을 보면 알 수 있습니다. 또한 그림 6에서는 실행 시간에서 데이터 배포가 큰 영향을 미치며 일부 반비례하는 결과를 가져올 수도 있음을 보여 줍니다. 대규모 퍼지 그룹화 작업을 실행하면 임시 연결로 지정한 서버에 큰 임시 개체가 발생합니다. 퍼지 그룹화 사전 처리 단계 동안 DTS 파이프라인이 중지된 것처럼 보일 수 있습니다. 그러한 경우에는 서버에서 임시 개체 크기를 통해 진행 상황을 알 수 있습니다.
.gif "ms345128.fzdtssql0505_big(ko-kr,SQL.90).gif")
그림 5. 퍼지 그룹화 시간과 MinSimilarity=0.8 의 입력 크기 비교
.gif "ms345128.fzdtssql0506_big(ko-kr,SQL.90).gif")
그림 6. 퍼지 그룹 시간과 20K 행의 MinSimilarity 임계값 비교
결론
퍼지 조회 및 퍼지 그룹화 변환은 도메인 독립적인 기본 기능으로서 데이터를 정리하고 준비하는 데 유용합니다. 이들은 데이터에 세밀한 조회 및 그룹화 작업을 수행할 때 사용할 수 있는 토큰 기반의 거리 개념을 사용합니다. 거리 함수와 구성 요소의 전반적인 아키텍처에 대한 자세한 내용은 아래에 나와 있는 리서치 문서를 참조하십시오. 퍼지 조회 및 퍼지 그룹화는 DTS 구성 요소로 구현되어 있기 때문에 대규모 ETS 프로세스에서 그대로 사용할 수 있으며 사용자 지정 프로그래밍 및 관련 비용 없이도 로깅, 이벤트, 오류 추적 등의 DTS 인프라를 활용할 수 있습니다. 변환을 컴퓨팅하는 데 일부 리소스가 필요하기는 하지만 퍼지 조회 및 퍼지 그룹화는 이전에 상당한 수동 작업 및 도구의 사용자 지정 없이는 어렵거나 불가능했던 작업을 자동화합니다.
추가 정보
리서치 문서
Eliminating Fuzzy Duplicates in Data Warehouses.
.gif "ms345128.tous(ko-kr,SQL.90).gif")
제 28회 VLDB 2002(Very Large Databases) 국제 회의 회보, 홍콩. Rohit Ananthakrishna, Surajit Chaudhuri, Venkatesh Ganti.Robust and Efficient Fuzzy Match for Online Data Cleaning.
ACM SIGMOD 2003, 샌디에고. Surajit Chaudhuri, Kris Ganjam, Venkatesh Ganti, Rajeev Motwani.
SQL Server 온라인 설명서
퍼지 조회 및 퍼지 그룹화 섹션
DTS 섹션