데이터 필터링 구현

데이터 필터링 구현

한대성

MS SQL Server MVP

에이디컨설팅 책임 컨설턴트 | SQLLeader.com 운영자

Question

원본이 텍스트 파일이고, 각각의 카테고리 ID 가 있습니다. 여기서 제외해야될  ID  값이 있는데, 이게 한 100여개도 가능합니다. 간단한 필터링은 [조건부분할] 로 사용해서 처리가능하였는데, 정작 원하는 필터링은 일일이 입력할수 없어서 난감한 상태입니다. 아래와 같은 식인데..10번/20번/30번/40번.... 천번단위로 있고, 여기서 제외해야될 id 가 별도의 테이블등으로 존재한다고 할때, 어떠한 방식으로 처리해야 할지요? 단순히 테이블로 밀어넣은 이후에 삭제하는 방법이 꽤 비효율적으로 생각되어 문의드립니다. 예) 원본 텍스트 파일 category  content 10 AAA 10 AAA 20 BBB

 

다음과 같이 데이터 테이블을 준비합니다.

USE TEMPDB GO   CREATE TABLE DataTable (              seq int identity,              category int,              content varchar(100) ) GO   SET NOCOUNT ON   DECLARE @I AS INT SET @I = 1 WHILE (@I<=10000) BEGIN              INSERT DataTable(category, content)              SELECT CAST(rand()*1000 AS INT) * 10,                           REPLICATE(CHAR(CAST(rand()*26 AS INT)+65), rand()*100)                SET @I = @I + 1 END   SET NOCOUNT OFF GO

 

 

 

 

 

위의 데이터 중 필터링을 할 대상을 저장하는 FilterTable 테이블을 생성합니다. 본 예제에서는 구분하기 용이하게 seq 열이 11부터 90 사이에 있는 category 값을 필터 할 대상으로 생성하겠습니다.

 

CREATE TABLE FilterTable (              category int ) GO   INSERT FilterTable SELECT DISTINCT category FROM DataTable WHERE seq between 11 and 90 GO

 

 

 

 

패키지로 구현하기에 앞서 잠시 쿼리로 생각해 봅시다.

만약 위와 같은 테이블 들에 대해 필터링 할 경우에는 다음과 같은 쿼리를 생각할 수 있습니다.

 

성능을 고려한다면 다음과 같이 쿼리를 사용할 수도 있겠지요.

 

 

이와 같은 형식으로 패키지를 구현하고자 합니다. 데이터 원본은 텍스트 파일이든 OLE DB 원본이든 동일하기 때문에 편의상 OLE DB 원본으로 설명하겠습니다.

 

 

빈 패키지 파일을 만든 다음, 제어 흐름 영역에 데이터 흐름 작업을 추가하고, OLE DB 원본으로 위의 DataTable을 지정합니다.

 

 

조건부 분할을 이용해서 데이터를 필터 할 수 있지만, 필터 할 대상이 자주 변하거나 본 예제와 같이 별도의 테이블로 관리를 해야 한다면, 조건부 분할 대신 조회 변환을 이용하는 것이 유용할 수 있습니다.

만약 필터링 대상 테이블이 수 만 건 이상인 경우에는, SQL 2005 SSIS에서는 메모리 문제나 조회 대상 데이터 캐싱 문제로 인하여 성능 저하가 발생할 수도 있지만, 본 요구 사항과 같이 수 십에서 수백 건 정도라면 조회 변환을 이용하는데 큰 문제가 없습니다.

 

조회 변환을 추가한 후, OLE DB 원본과 연결합니다.

 

 

다음과 같이 조회 변환 편집기에서 조회 대상 테이블을 선택하거나 쿼리를 작성합니다. 가급적 테이블을 직접 지정하는 대신 쿼리를 사용하는 것을 권장합니다.

 

열 탭에서 아래와 같이 단순히 연결만 시킵니다.

 

 

그런 다음 화면 아래에 있는 오류 출력 구성(G)를 선택한 후, 오류 부분을 행 리디렉션으로 변경합니다.

 

 

이제, 멀티캐스트 또는 OLE DB 대상 등 아무 변환이나 대상을 추가한 후, 아래와 같이 조회 변환과 빨간 색 선으로 연결시킵니다.

 

각 경로에 데이터 뷰어를 추가한 후 처리되는 데이터를 확인해 봅시다.

 

오류 출력이기 때문에 본 데이터 외에 Error 및 ErrorColumn, ErrorCode-설명 열이 추가되지만, 특별히 사용할 필요는 없습니다.

 

 

조회가 되는 경우는 필터 되는 데이터이고, 조회가 안되어서 오류가 발생하는 데이터가 필터 되고 남은 데이터 입니다.

 

만약 FilterTable 테이블을 모두 지운 후에 패키지를 실행시키면 필터 되는 대상이 없기 때문에 모든 데이터가 출력되겠지요.