ETL 프로세스를 활용한 데이터 웨어하우스 구축

데이터 웨어하우스 구축 과정에서 ETL(Extract, Transform, Load)은 핵심적인 역할을 수행합니다. ETL은 다양한 소스 시스템에서 데이터를 추출하고, 분석에 적합한 형태로 변환한 후, 최종적으로 데이터 웨어하우스에 적재하는 일련의 절차를 의미합니다.

ETL 프로세스 개요

ETL 프로세스는 크게 데이터 추출(Extract), 데이터 변환(Transform), 데이터 적재(Load) 세 단계로 구성됩니다.

ETL 프로젝트 설정

ETL 작업을 수행하기 위한 프로젝트를 생성하는 과정은 다음과 같습니다. 메타데이터를 데이터 웨어하우스로 옮기는 것이 주요 목표입니다.

프로젝트 생성: ETL 작업을 위한 새 프로젝트를 생성합니다.
소스 시스템 연결: 데이터 추출을 수행할 원본 시스템과의 연결을 설정합니다.
데이터 웨어하우스 연결: 데이터를 적재할 대상 데이터 웨어하우스와의 연결을 설정합니다.

SSIS 패키지 구성

SQL Server Integration Services(SSIS)를 사용하여 ETL 패키지를 설계합니다. 차원(Dimension) 테이블과 사실(Fact) 테이블에 대한 ETL 작업을 각각 별도의 패키지로 구성하는 것이 일반적입니다.

시퀀스 컨테이너 활용

여러 ETL 작업을 논리적으로 그룹화하고 실행 순서를 관리하기 위해 시퀀스 컨테이너를 사용합니다. 이를 통해 전체 패키지가 하나의 컨테이너처럼 동작하는 것을 방지하고, 각 작업 단위를 명확히 구분할 수 있습니다.

전체 로드(Full Load) vs. 증분 로드(Incremental Load)

전체 로드: 테이블의 모든 데이터를 한 번에 추출하여 적재하는 방식입니다. 데이터 중복을 방지하기 위해 적재 전에 기존 데이터를 삭제하는 단계를 포함할 수 있습니다.
증분 로드: 변경되거나 새로 생성된 데이터만 추출하여 적재하는 방식입니다. 매일 또는 주기적으로 데이터를 조금씩 업데이트하는 데 사용됩니다.

차원(Dimension) 테이블 ETL

차원 테이블은 데이터 웨어하우스에서 분석의 기준이 되는 정보를 담고 있습니다. 차원 테이블에 대한 ETL은 다음과 같은 단계로 진행될 수 있습니다.

SQL 작업 설정

SQL 작업을 통해 원본 시스템에서 데이터를 조회하고 필요한 경우 초기 변환을 수행합니다. 연결 정보와 SQL 쿼리를 설정합니다.

데이터 흐름 작업

데이터 흐름 작업을 사용하여 구체적인 데이터 변환 및 적재 로직을 구현합니다. 여기에는 OLE DB 원본, 데이터 변환, OLE DB 대상 등의 구성 요소가 사용됩니다.

OLE DB 원본: 원본 시스템에서 데이터를 읽어옵니다.
데이터 변환: 데이터 형식 변경, 값 수정, 새로운 컬럼 생성 등 필요한 변환 작업을 수행합니다. 예를 들어, 날짜 형식을 변환하거나 한글 데이터를 처리하는 과정이 포함될 수 있습니다.
OLE DB 대상: 변환된 데이터를 데이터 웨어하우스의 대상 테이블에 적재합니다.

예시 SQL (전체 로드):

SELECT
    [FrameNo],
    [SaleShop],
    CONVERT(INT, CONVERT(VARCHAR, [CreateDate], 112)) AS datekey,
    [SalePrice],
    [FactoryPrice],
    [SaleType]
FROM (
    SELECT
        [FrameNo],
        [SaleShop],
        [CreateDate],
        [SalePrice],
        [FactoryPrice],
        [SaleType]
    FROM [jtxy_source].[dbo].[tbl_EXE_SaleCar]
) AS SourceData
WHERE SourceData.datekey <= 20110814;

데이터 타입 불일치 문제가 발생할 경우, 컬럼의 데이터 타입을 수정하고 저장해야 합니다. 일반적으로 전체 로드를 먼저 수행한 후 증분 로드를 진행하는 것이 효율적입니다. 이는 분석 시점에 이미 방대한 양의 데이터가 축적되어 있을 가능성이 높기 때문입니다.

사실(Fact) 테이블 ETL

사실 테이블은 비즈니스 측정값을 기록하며, 여러 차원 테이블과 조인하여 분석에 활용됩니다. 사실 테이블 ETL은 차원 테이블 ETL과 유사한 구조를 가질 수 있습니다.

SQL 작업 및 데이터 흐름

사실 테이블 ETL 역시 SQL 작업을 통해 데이터를 조회하고, 데이터 흐름 작업을 통해 변환 및 적재를 수행합니다. 전체 로드와 증분 로드를 위한 분기 처리를 구현할 수 있습니다.

예시 SQL (전체 로드 - `tbl_EXE_TargetData`):

SELECT
    [TargetValue],
    [TargetRange],
    [TargetData],
    CONVERT(INT, CONVERT(VARCHAR, [SubmitTime], 112)) AS datekey,
    [TargetFor],
    [TargetShop]
FROM (
    SELECT
        [TargetValue],
        [TargetRange],
        [TargetData],
        [SubmitTime],
        [TargetFor],
        [TargetShop]
    FROM [jtxy_source].[dbo].[tbl_EXE_TargetData]
) AS SourceData
WHERE SourceData.datekey <= 20110809;

데이터 변환 단계에서 한글 데이터가 포함된 경우, 이를 처리하기 위한 별도의 변환 작업이 필요할 수 있습니다. 이후 데이터 웨어하우스의 대상 테이블에 데이터를 적재합니다.

증분 로드 구현

증분 로드를 위해 이전 실행 시점 이후의 데이터 변경분만 추출하여 적재하는 로직을 구현합니다. `ORDER BY` 절을 사용하여 `datekey`를 기준으로 정렬된 데이터를 처리하는 것이 중요합니다.

중복 제거를 위한 SQL (증분 로드):

SELECT DISTINCT
    CONVERT(INT, CONVERT(VARCHAR, [SubmitTime], 112)) AS datekey
FROM [jtxy_source].[dbo].[tbl_EXE_TargetData]
ORDER BY datekey;

반복적인 ETL 작업을 위해 시퀀스 컨테이너를 활용하며, 전체 로드와 증분 로드 로직을 설정합니다. 테스트 실행을 통해 각 로드의 정상 작동 여부를 확인합니다.

태그: ETL SSIS 데이터 웨어하우스 SQL Server 데이터 추출

8월 14일 13:55에 게시됨

괴물 클럽