기본 강좌 24 - UNION ALL, 정렬, 집계 변환

UNION ALL, 정렬, 집계 변환

 

SSIS에는 SQL에서 사용되는 연산과 유사한 작업들이 많이 존재합니다. 대표적인 예가 UNION ALL, 정렬, 집계, 조인 등입니다. SQL 쿼리는 테이블에 있는 데이터를 대상으로 SQL 엔진에서 이러한 작업들이 수행되지만, SSIS는 테이블뿐만 아니라, 다양한 형태의 원본 데이터에 대해서도 이러한 작업을 수행할 수 있습니다. 예를 들어, 여러 개의 텍스트 파일 및 엑셀 파일에서 읽은 데이터를 SQL 사용 없이 UNION ALL 변환 작업으로 하나의 파일로 합칠 수도 있으며, 특정 열로 정렬시킨 후 출력할 수도 있으며, COUNT나 SUM등과 같은 집계 연산 작업도 가능합니다. 이러한 작업들은 모두 파이프 라인이라는 SSIS 엔진의 메모리 영역에서 수행이 되기 때문에 SQL 서버의 임시 테이블에 데이터를 넣고 여러 작업들을 수행한 후 다시 읽어오는 부가적인 I/O 작업 없이 직접 처리가 가능합니다.

 

 

 

UNION ALL 변환

 

UNION ALL 변환은 여러 개의 입력 데이터를 하나로 결합하는 변환입니다. 다수의 입력에 대해 하나의 출력을 제공합니다.

 

 

 

위의 그림에서, UNION ALL 입력 1의 경우, 구매수량이라는 출력 열에 해당하는 데이터가 없는 경우, <무시>로 설정을 하면 해당 열은 NULL로 처리됩니다. 고객명이나 제품명과 같이 서로 다른 입력에 대해 동일한 출력으로 지정하도록 할 경우, 각 입력 데이터의 데이터 형식은 동일해야 합니다.

 

 

 

정렬

 

입력되는 데이터를 오름차순이나 내림차순으로 정렬한 후, 출력하는 변환입니다. SQL 쿼리에서 여러 열에 대해 정렬을 수행하고, 각 열 별로 정렬 형태를 지정하는 것과 마찬가지로, 정렬 변환에서도 여러 열에 대해서 정렬이 가능하며, 각 열 별로 정렬 옵션을 지정할 수 있습니다. 정렬 옵션은 오름차순이나 내림차순 설정뿐만 아니라, 대/소문자 무시, 문자 너비 무시 등과 같은 비교 플래그 설정을 포함합니다. 다른 변환 작업과 마찬가지로 출력 열의 이름을 변경할 수도 있습니다.

 

 

Ÿ           비교 플래그

ú           대/소문자 무시 –대소문자 구분 여부를 설정합니다. 이 옵션을 설정하게 되면 “ABC”와 “abc”를 동일하게 인식합니다.

ú           가나 형식 무시 – 두 가지 유형의 일본어 가나 문자인 히라가나와 가타가나에 대한 구분 여부를 설정합니다.

ú           비공백 문자 무시 – 공백 문자와 분음 기호의 구분 여부를 설정합니다. 이 옵션을 설정하게 되면 "å"와 "a"를 동일하게 인식합니다.

ú           문자 너비 무시 – 싱글바이트 문자와 더블바이트 문자의 구분 여부를 설정합니다. 이 옵션을 설정하게 되면 “A”와 “Ａ”를 동일하게 인식합니다.

ú           기호 무시 – 글자 문자와 공백 문자, 문장 부호, 통화 기호 및 수학 기호와 같은 기호에 대한 구분 여부를 설정합니다. 이 옵션을 설정하게 되면 “ ABC”나 “*ABC”, “ABC”를 모두 동일하게 인식합니다.

ú           문장 부호를 기호로 정렬 – 영문자 또는 숫자 앞에 하이픈과 따옴표(‘)를 제외한 모든 문장 부호에 대한 구분 여부를 설정합니다. 이 옵션을 설정하게 되면, “.ABC”가 “ABC” 앞에 정렬됩니다. 단, 데이터의 코드가 949(한글)인 경우에는 특별히 이 옵션을 설정하지 않아도 문장 부호를 기호로 정렬하게 됩니다.

 

 

 

집계

 

입력되는 데이터에 대해 SUM이나 AVERAGE, COUNT 등과 같은 집계 연산을 수행하여 결과를 출력하는 변환입니다. SQL 쿼리에서 GROUP BY 구문과 수행 방법이 비슷하며 집계를 수행할 열과 집계 연산을 지정합니다. 다른 변환 작업과는 달리 고급 설정 기능이 많이 포함되어 있습니다. 이는 집계 작업을 수행할 때 단순히 집계와 관련된 기능뿐만 아니라 배율이나 키 수 등과 같은 성능과 관련된 설정이 많이 포함되어 있기 때문입니다.

 

 

집계

Ÿ           연산

ú           Group By – 집계 작업을 수행하기 위한 그룹 열입니다. 예를 들어 고객명과 제품명으로 집계를 수행한다면, 고객명, 제품명이 그룹 열이 됩니다.

ú           Sum – 열의 값에 대한 합계를 계산합니다. 열의 값이 숫자형인 경우에만 설정할 수 있습니다.

ú           Average – 열의 값에 대한 평균을 계산합니다. 열의 값이 숫자형인 경우에만 설정할 수 있습니다.

ú           Count – 그룹 열에 대한 열의 항목 개수를 계산합니다.

ú           Distinct Count – 그룹 열에 대한 열 중 NULL을 제외한 항목의 고유한 개수를 계산합니다.

ú           Minimum – 열의 값 중 최소값을 반환합니다.

ú           Maximum – 열의 값 중 최대값을 반환합니다.

 

Ÿ           비교 플래그 – 정렬 변환의 비교 플래그 부분을 참고하시기 바랍니다.

 

Ÿ           고유 수 배율 – 연산이 Distinct Count 일 때에 설정할 수 있습니다. 고유한 키의 대략적인 수준을 지정해 주는 부분이며, 이러한 설정은 Distinct Count 연산 수행 시 고유 수의 정도에 따라 메모리를 미리 확보해 둠으로써 성능을 향상시킬 수 있는 설정입니다. 기본 값은 Unspecified 입니다.
       Unspecified – 고유 수 배율 속성을 사용하지 않습니다.
       Low – 약 500,000개 정도의 고유 수 정도인 경우
       Medium – 약 5,000,000개 정도의 고유 수 정도인 경우
       High – 약 25,000,000개 정도의 고유 수 정도인 경우

 

 

Ÿ           고유 키 수 – 고유 수 배율은 대략적인 고유 수 값을 지정하는 것입니다. 이에 비해 고유 키 수는 정확한 고유 수 값을 아는 경우 설정해주는 부분입니다. 예를 들어, Distinct Count를 수행해서 나올 수 있는 수치가 9,500,000 정도 된다라는 것을 미리 알고 있다면, 고유 키 수의 속성값에 이 추정치를 적어줌으로써 집계 연산의 작업에서 필요한 메모리를 미리 확보할 수 있도록 설정할 수 있습니다. 고유 수 배율과 고유 키 수를 동시에 지정한 경우, 고유 키 수의 속성을 적용하게 됩니다.

 

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

 

 

Ÿ           집계 이름 – 출력의 이름을 지정합니다.

Ÿ           Group By 열 – 집계 작업의 그룹 열입니다.

Ÿ           키 소수 자릿수 – ([참고] “키 배율”로 번역되어야 하는 것인데, 프로그램 상에서는 “키 소수 자릿수”로 번역되어 있습니다.) 고유 수 배율과 비슷한 옵션을 가지고 있으며, 키 열로 수행되는 Group By 연산 결과에 대한 추정치를 설정합니다. 예를 들어 Group By 연산에 의해 생성되는 결과 행 수가 대략 6,500,000개 정도가 된다면, 키 소수 자릿수 속성을 Medium으로 설정해 줌으로써 좀 더 최적화된 성능을 기대할 수 있습니다.

Ÿ           키 – 정확한 Group By 연산 결과의 수 또는 대략적인 수를 아는 경우, 입력합니다. 키 소수 자릿수 설정과 마찬가지로 이 값을 설정하는 경우, 집계 연산에 필요한 메모리를 추정하여 미리 확보해 둠으로써 성능을 향상시킬 수 있습니다. 키 소수 자릿수와 키 값이 모두 설정되어 있는 경우, 키 값의 설정을 적용하게 됩니다.

 

 

고급

 

고급 탭에서도 역시 키 배율, 키 수, 고유 수 배율, 고유 키 수를 지정할 수 있습니다. 하지만, 이러한 속성을 고급 탭에서 지정하게 되면 변환 작업 전체에 대해 설정을 지정하게 됩니다. 즉, 고급 탭의 설정은 집계 변환에 포함된 모든 집계 작업 전체에 대한 설정이고, 집계 탭의 고급 버튼을 눌러 나오는 화면에서 지정하는 것은 각 집계 작업에 대한 설정이며. 집계 탭 하단에서 지정하는 것은 출력에 포함된 각 열에 대해 지정하는 것입니다.

고급 탭에서는 자동 확장 비율이라는 속성이 있습니다. 이는 집계 작업을 위한 메모리 사용에 대한 고급 설정으로, 집계 작업 수행 중 추가로 메모리가 필요할 때 늘릴 비율을 지정하는 것입니다. 기본값은 25% 입니다.

 

 

 

 

 

따라하기 – [SSIS 실습3] UNION 및 집계 변환 만들기를 참고하시기 바랍니다