PostgreSQL DOMAIN을 이용한 효과적인 ELT 작업 가이드
PostgreSQL의 DOMAIN을 활용한 ELT 작업 수행하기
안녕하세요! 오늘은 PostgreSQL에서 DOMAIN을 사용하여 ETL(추출, 변환, 적재) 프로세스의 변주인 ELT(추출, 적재, 변환)를 어떻게 수행할 수 있는지에 대해 이야기해보겠습니다.
ETL vs. ELT
먼저 ETL과 ELT의 정의를 명확히 하고 넘어가겠습니다.
-
ETL(Extract, Transform, Load): 데이터를 목표 시스템에 로드하기 전에 변환합니다. 즉, 다양한 소스에서 원본 데이터를 추출하고, 이를 변환한 후 목표 데이터베이스에 로드하는 과정입니다.
-
ELT(Extract, Load, Transform): 데이터를 로드한 후 변환합니다. 원본 데이터를 추출하여 목표 데이터베이스에 적재하고, 이후 데이터 시스템 내에서 변환하는 방식입니다.
데이터베이스 시스템 간의 데이터 저장 방식에 차이가 있어, 데이터 마이그레이션은 때때로 상당한 도전을 요구합니다. 이러한 경우, 애플리케이션을 리팩토링하는 번거로움을 최소화하고, 마이그레이션 과정에서 데이터 자체에 대해 동적인 ELT 작업을 수행할 수 있습니다.
문제 해결: DATE 데이터 타입
두 개의 RDBMS 간 CSV 파일에서 DATE 데이터 타입을 마이그레이션할 때 자주 발생하는 문제입니다. 다음의 세 가지 방법을 제안합니다:
- 빈 문자열을 NULL로 변환: ELT 변환을 수행하고, 테이블에 로드하는 동안 트리거를 사용하여 "DATE" 타입의 열에 로드합니다.
- 커스텀 PostgreSQL 명령어 제작: 빈 문자열을 NULL로 동적으로 변환하여 표준 "DATE" 타입의 열에 데이터를 삽입합니다.
- 커스텀 데이터 타입 생성: 빈 문자열을 직접 로드할 수 있는 커스텀 DATE 열을 사용합니다.
첫 번째 및 두 번째 방법은 데이터를 표준 PostgreSQL DATE 열에 직접 삽입하지만, 빈 문자열이 향후 트랜잭션에서 다시 데이터베이스에 삽입되지 않을 것이라는 가정을 전제로 합니다. 세 번째 방법은 가장 간단하고 빠른 방법으로, 데이터 마이그레이션 후 데이터 관리에 대한 추가적인 고려가 필요할 수 있습니다.
DOMAIN 사용 방법
1단계: 함수 생성
먼저, CAST에 사용될 함수를 생성합니다. 다음의 예는 PostgreSQL에서 함수를 만드는 방법입니다:
CREATE OR REPLACE FUNCTION f_ex(in x varchar)
RETURNS date AS $$
BEGIN
RAISE NOTICE 'Entering function f_ex, the argument is "%"', x;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
2단계: CAST 생성
다음으로, 위에서 만든 함수를 사용해 CAST를 생성합니다:
CREATE CAST (varchar AS date) WITH FUNCTION f_ex(varchar) AS ASSIGNMENT;
3단계: DOMAIN 생성
마지막으로, DOMAIN을 생성하여 삽입된 값이 특정 데이터 타입으로 저장되도록 강제합니다.
CREATE DOMAIN uk_date1 AS date;
CREATE DOMAIN uk_date2 AS varchar;
검증 및 사용 사례
위에서 생성한 CAST와 DOMAIN을 사용하여 테이블을 생성하고 데이터를 삽입해 보겠습니다.
1단계: 테이블 생성
예시 테이블을 생성합니다:
CREATE TABLE example_table (
id serial PRIMARY KEY,
date_field1 uk_date1,
date_field2 uk_date2,
date_default date
);
2단계: 레코드 삽입
여기에서는 다양한 날짜 포맷과 빈 문자열을 적용하여 레코드를 삽입합니다:
INSERT INTO example_table (date_field1, date_field2, date_default) VALUES ('24/10/2024', '24/10/2024', '24/10/2024');
INSERT INTO example_table (date_field1, date_field2, date_default) VALUES ('24.10.2024', '24.10.2024', '24.10.2024');
INSERT INTO example_table (date_field1, date_field2, date_default) VALUES (now()::date, now()::date, now()::date);
INSERT INTO example_table (date_field1, date_field2, date_default) VALUES (NULL, NULL, NULL);
3단계: 데이터 쿼리
테이블의 모든 데이터를 조회하여 확인합니다:
SELECT * FROM example_table;
주의사항
함수 f_ex는 어떤 문자 문자열도 삽입할 수 있도록 허용하므로, PostgreSQL DATE 타입 구현에 따라 변환되지 않을 수 있습니다. 이는 함수 호출에 적절한 조건을 추가함으로써 완화할 수 있습니다.
결론
빈 문자열을 포함하는 데이터를 CAST와 DOMAIN을 사용해 삽입하는 과정은 비교적 간단하지만, 데이터가 저장된 이후에 어떻게 조작할 것인지에 대한 고려도 필수적입니다. 최종적으로 데이터베이스에 데이터가 어떻게 적재되는지, 그리고 그 데이터를 어떻게 사용할 것인지의 측면을 항상 고려해야 합니다.
더 나아가 PostgreSQL 성능을 최적화하는 데 도움이 되고자, 저희의 PostgreSQL 성능 조정 eBook도 함께 확인해 보세요!
여러분의 데이터베이스 관리에 도움이 되길 바랍니다!
