멀티 집계 변환 수행하기

20070207_멀티집계변환.dtsx

멀티 집계 변환 수행

   

한대성

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

 

 

오늘은 조금 가벼운 기능을 소개합니다. 새로운 기능은 아니며, 집계 변환 강좌에 소개된 내용을 예제로 만든 것입니다.

 

집계 탭에서 상단의 고급(A) 버튼을 클릭하면 출력 경로에 대한 설정을 할 수 있는 부분이 나타납니다. 집계 연산은 하나의 입력에 대해 여러 개의 여러 개의 집계를 만들 수 있으며, 각각의 집계 작업이 출력이 됩니다. 예를 들어, 고객명, 제품명, 조회수, 구매수량, 구매금액이 입력 데이터에 대해, 고객명과 제품명을 기준으로 값들의 합계를 구하는 연산 작업(집계 출력 1)과, 고객명에 대한 고유한 제품명 수(Distinct Count)를 계산하는 집계 작업(집계 출력 2)을 수행해야 한다면, 별도의 집계 변환을 추가하지 않고도, 하나의 집계 변환에서 두 가지 형태의 집계 작업을 수행할 수 있습니다.

 

SSIS의 특징 중 하나는 데이터를 자체 데이터 처리 엔진을 이용하여 메모리에서 처리한다는 점입니다. 이러한 방식으로 인해 SQL 쿼리에서와는 조금 다른 형태로 이용할 수 있습니다.

 

집계 변환, 즉 GROUP BY 연산을 수행하는 변환은 SSIS에 포함된 여러 변환들 중에서 비용이 높은 변환입니다. 비용이 높다는 것은 데이터를 처리할 때 메모리를 많이 사용한다는 것입니다. 추후에 자세히 다루겠지만, SSIS의 변환은 크게 세 가지 형태로 나뉩니다.

 

동기 변환(Synchronize Transform), 비동기 변환(Asynchronize Transform), 반동기 변환(Semi-Synchronize Transform)입니다. (한글 변역은 제 마음대로 한 것입니다.^^)

동기 변환에는 데이터 변환, 파생 열, 감사, 조건부 분할 등이며, 비동기 변환은 집계, 정렬, 행 샘플링 등입니다. 반동기 변환에는 비율 샘플링이 있습니다.

이런 구분은 입력 데이터에 대한 처리 방식에 따라 나뉩니다. 데이터 변환이나 파생 열 변환과 같이 입력되는 데이터를 바로 처리한 후 출력하는 방식이 동기 변환입니다. 정렬이나 집계와 같이 모든 데이터가 다 로딩된 후에야 처리를 하고 출력하는 방식이 비동기 변환이며, 입력 데이터 중 일부가 입력된 후에 수행되는 방식이 반동기 변환입니다.

 

성능상으로는 동기 변환 유형의 작업이 다른 유형에 비해 성능도 우수하며 서버에 큰 부하도 발생시키지 않습니다. 하지만, 정렬이나 집계 변환과 같은 비동기 변환 작업을 수행해야 할 경우가 있습니다.

 

예제로 사용할 데이터는 SQL 2000의 pubs DB에 있는 sales 테이블입니다.

 

 

이와 같은 형태의 데이터에 대해, stor_id 별, ord_num 별, payterms 별, title_id별 qty 합을 구해야 할 경우, 다음과 같이 총 4개의 쿼리를 이용해야 합니다.

 

SELECT stor_id, sum(qty) as sumqty FROM saels GROUP BY stor_id GO SELECT ord_num, sum(qty) as sumqty FROM saels GROUP BY ord_num GO SELECT payterms, sum(qty) as sumqty FROM saels GROUP BY payterms GO SELECT title_id, sum(qty) as sumqty FROM saels GROUP BY title_id GO

 

하지만, SSIS 패키지를 이용하여 이를 구현한다면 다음과 같이 한 번만 데이터를 입력한 후 메모리 내에서 각 유형별로 집계를 수행하게 됩니다.

 

 

메모리에 데이터를 모두 로딩해야 한다는 점은 있지만, 일단 로딩된 데이터에 대해 여러 형태의 집계를 수행할 수 있다는 점에서는 상당한 장점이 될 수 있습니다.

 

여러 형태의 집계를 한 번에 수행한다면 메모리 부하가 클 수 있습니다.

물론 하나의 집계 연산일 때 보다는 더 필요할 수 있습니다. 하지만, 우선 쿼리를 이용하는 방식과 비교했을 때 원본 데이터를 한 번만 읽어도 되기 때문에 Disk Read 부하를 줄일 수 있습니다. 물론 SQL Table 형태의 데이터가 아니더라도 집계를 수행할 수 있다라는 장점도 있겠지요.

 

그럼 위와 같이 한 번에 4개의 집계를 수행하는 것과 한 번에 하나의 집계 형태로 4번 수행하는 것이 좋을까요? 특별한 경우를 제외하고는 위와 같은 경우가 더 좋습니다.

집계나 정렬 변환에서 데이터를 처리할 때에는 두 가지 형태의 메모리 버퍼를 이용합니다 하나는 플랫 버퍼(Flat Buffer)이며, 입력되는 데이터를 저장하는 역할을 합니다. 또 다른 하나는 프라이빗 버퍼(Private Buffer)이며, 집계 또는 정렬 연산을 수행할 때 이용되는 임시 메모리 영역입니다. 위와 같이 작성한다면 최소한 플랫 버퍼로 데이터를 로딩하는 작업은 한 번만 수행하면 되기 때문에 여러 개로 분리해서 수행할 때보다 우수하다고 할 수 있습니다.

 

이와 같이 설정하는 방법은 다음과 같습니다.

 

 

기본적인 집계 변환 편집기는 위와 같은 형태입니다. 여기서 위에 있는 고급(A) 버튼을 클릭하면 여러 집계를 설정할 수 있는 부분이 나타납니다.

 

 

두 번째 행을 선택한 후, 집계 이름을 입력하면 아래의 사용 가능한 입력 열이 다시 초기화되어 열을 설정하도록 변합니다.

 

 

집계 연산에서 사용할 열을 선택하고 아래 부분에서 연산 방법(Sum or Avg 등)을 지정하면 두 번째의 집계가 설정된 것입니다. 이와 같은 방식으로 필요한 만큼의 집계 작업을 설정합니다.

 

자, 이제 집계 변환 작업을 다른 변환 또는 데이터 대상으로 연결을 하려고 하면 다음과 같이 집계를 선택할 창이 나타납니다. 해당 변환 또는 대상에 맞는 집계를 선택해 주면 됩니다.

 

 

이외에도 집계 연산에서 사용할 메모리의 크기를 지정해주는 옵션이나 집계 데이터의 추정치를 입력하여 메모리를 사전에 확보하도록 하는 고급 설정 기능도 있습니다.

 

물론 SQL의 GROUP BY보다 항상 우수한 것은 아닙니다.

대표적으로, 문자열 데이터에 대해서는 MAX, MIN 연산이 불가능하다는 것입니다.

 

 

아쉽지만  다른 우수한 기능들로 인해 만족스럽지요.^^

 

그러나~!, 처리 데이터가 SQL Table이며, 한 번만 GROUP BY를 수행해도 될 경우에는 집계 연산보다는 가급적 SQL Engine에서 처리하도록 합시다. 정렬도 마찬가지입니다.

(이와 관련해서는 추후에 다룰 예정입니다.)