Redshift DW에서 PG DM을 만드는 여정

매드업이 Redshift 데이터 웨어하우스에 직접 쿼리를 붙였다가 Hash Join 부하로 세션이 끊기는 문제를 만나고, PostgreSQL 데이터 마트를 중간에 두는 쪽으로 옮긴 기록이다. DB Link 와 Stored Procedure 로 Redshift 데이터를 끌어오되, 컬럼이 수십 개라 프로시저를 파이썬으로 자동 생성하는 제너레이터까지 만들었다.

핵심 포인트

출발점은 S3 와 Athena 조합이었다. Athena 가 비용 대비 느리고 그 쿼리를 도는 람다도 내내 떠 있어야 해 비효율적이었다.
Redshift 로 DW 를 만든 뒤 여러 테이블을 조인해 직접 쿼리했더니, Hash Join 으로 CPU 사용률이 치솟아 Redshift 가 스스로를 지키려 세션을 끊었다.
Redshift 는 분산키와 정렬키 지정이 쉽지 않고 조인 연결고리에 인덱스가 없어 대부분의 상황에서 Hash Join 이 선택된다.
임시방편 대신 데이터 마트를 중간에 두는 장기 해법을 택했다. 마트에서는 복합 인덱스를 이용한 조인과 필요 시 추가 인덱스 생성이 가능하다.
이관 방법은 서버가 Redshift 에서 받아 DB 로 넣는 대신, DB Link 와 Stored Procedure 로 DB 끼리 직접 빨아들이는 방식이다. 수백만 행을 1분 정도에 가져왔다.

상세 정리

Redshift 는 PostgreSQL 을 포크해 AWS 가 튜닝한 대용량 데이터베이스라 개발 환경에서 잘 돌던 쿼리가 운영에서도 괜찮을 거라 기대했지만 그렇지 않았다.
Hash Join 은 Merge Join 시절 디스크 I/O 부담을 줄이려 나온 기법이다. 두 테이블 중 작은 쪽을 메모리에 올리고 큰 쪽을 읽으며 해시 값으로 짝을 맞춘다. CPU 여유가 있으면 대개 유리하다.
EXPLAIN 으로 확인하니 여러 테이블을 서빙하려고 Hash Join 이 반복되며 비용이 크게 쌓이고 있었다.
마트에 담고 싶었던 요건이 여럿이다. 월별 파티셔닝, 자사 광고주 데이터만 적재해 스캔 범위 축소, Redshift 적재 주기에 맞춘 동기화, 필요 시 DW 에서 즉시 가져오기, REST API 로 외부 송출, 매체 즉시 수집 콜백 수신이다.
마트로 옮길 데이터는 일주일에 10~11GB 수준이고 앞으로 더 늘어날 예정이다.
서버를 경유하는 안은 하루 1GB 를 가져올 때 쿼리 실행보다 fetching 이 더 오래 걸릴 수 있고 메모리에 올려두는 부담도 컸다.
프로시저는 오류 지점과 원인을 파악하기 어렵다는 단점이 있어, 가능한 한 짧게 여러 개로 나누기로 하고 동기화 대상 테이블을 8개 섹션으로 분류했다.
DB Link 쿼리는 상대 테이블의 메타데이터가 없는 상태로 조회하는 것이라 컬럼마다 데이터 타입을 일일이 지정해야 한다.
컬럼이 수십 개라 손으로 쓸 수 없어 프로시저를 자동 생성하는 파이썬 코드를 만들었다. 담당자가 없어도 유지보수되는 시스템을 만들어야 한다는 이유를 든다.
마트 스키마는 Redshift 와 동일하게 만들되, 매체가 주는 대로 저장된 컬럼명의 마침표를 전부 밑줄로 바꿨다.
제너레이터는 자사 DB 메타데이터를 읽어 컬럼별 데이터 타입을 파악하고 프로시저 문자열을 조립한다. end 처럼 SQL 예약어인 컬럼명은 따옴표로 감싸는 처리까지 넣었다.
프로시저는 광고계정 목록을 문자열로 만들어 조회 범위를 좁힌다. 인자를 주지 않으면 전체 계정을, 주면 지정한 계정만 대상으로 한다.
수집 시작일과 종료일도 변수로 받아 기본 수집 일수와 함께 조절한다.
구글만 별도 파일을 만들어야 했다. DW 가 매체 데이터를 그대로 가져오는 바람에 테이블명과 컬럼명에 마침표가 규칙 없이 섞여 있어 ETL 처리가 달라졌기 때문이다.
대응은 마트 컬럼의 밑줄을 다시 마침표로 되돌려 Redshift 쪽 이름과 맞추는 방식이다.
필요한 컬럼만 추리면 나중에 다시 필요해졌을 때 테이블 구조와 프로시저를 고치기 어려울 것 같아 일단 전부 수집하기로 했다.