Snowflake PoC 목표
- AWS Redshift 속도 이슈 해결을 위한 벤치마크 체크(Slow-Query 이슈)
- AWS Redshift 개발 환경 구축 이슈 대응
- AWS Redshift 운영 비용 벤치마크 체크
고객현황
- AWS Redshift를 서울 리전에서 사용 중
- 고객 자체 시각화 서비스 (유니콘, 밀키웨이 등) 에서의 Slow Query 이슈
- 특정 시간대 동시 사용자로 인한 병목현상 존재
- 개발 환경 구축에 대한 비용 절감하고자 하는 니즈
PoC 진행
- 고객 : PoC 대상 데이터를 S3에 적재 및 테스트 쿼리 선정
- 펜타시스템테크놀러지 : 데이터 정합성 및 기존 SQL/프로시저와의 차이 확인, 테스트 수행 및 결과 공유
- Snowflake : Redshift to Snowflake 방안 안내, 성능 및 기능테스트에 대한 PoC 검증항목 수행
PoC 대상 테이블
DDL 작업
Redshift(ASIS)에서 Snowflake(TOBE)로 데이터를 이관하기 위한 첫번째 작업으로 PoC 대상 테이블의 DDL이 필요하다. Snowflake에서의 DDL은 Redshift에서의 DDL과 약간의 차이가 있는데 아래 예시를 보며 살펴보자.
-- Redshift DDL
CREATE TABLE IF NOT EXISTS ABC.T1(
VAL1 VARCHAR(30) NOT NULL ENCODE LZO
, VAL2 VARCHAR(8) NOT NULL ENCODE RAW
, VAL3 VARCHAR(8) NOT NULL ENCODE LZO
, PRIMARY KEY (VAL1)
)
DISTSTYLE AUTO
DISTKEY (VAL1)
SORTKEY (
VAL2
, VAL3
)
;-- Snowflake DDL
CREATE TABLE IF NOT EXISTS ABC.T1 (
VAL1 VARCHAR(30) NOT NULL
, VAL2 VARCHAR(8) NOT NULL
, VAL3 VARCHAR(8) NOT NULL
);Snowflake는 호환성이 좋은 데이터 타입 지원으로 인해 효율적인 DDL 이식성을 보여준다. 또한 기존 DDL 대비 ENCODING 타입을 기재하지 않아도 되는 편의성도 갖추고 있고 Storage Layer에서 데이터를 파티셔닝하고 인덱싱하여 빠르게 검색할 수 있도록 하는 특징 때문에 별도로 Primary Key를 정의하지 않아도 데이터에 대한 빠른 검색이 가능하다. 따라서 Redshift와 달리 1) 파티션 정의 2) 인덱스 사용 및 관리 3) 정렬키 사용이 불필요하며 모든 최적화를 Snowflake에서 자동으로 처리한다.
Data Load
AWS S3에 적재되어 있는 데이터를 Snowflake로 Load 하기 위해서는 COPY INTO 구문을 사용한다. 이 과정에 대한 자세한 설명은
를 참조하기 바란다.
파일 포맷 생성
CREATE OR REPLACE FILE FORMAT CSV_FORMAT
TYPE = CSV
FIELD_OPTIONALLY_ENCLOSED_BY = '"'
FIELD_DELIMITER = ','
SKIP_HEADER=1
ESCAPE="\"
ESCAPE_UNENCLOSED_FIELD=NONE
NULL_IF = ('\\N', 'NULL', '');COPY INTO
COPY INTO SNOWFLAKE_POC.POC_SCHEMA.T1
FROM @EXTERNAL_STAGE/TEST/T1 FILE_FORMAT = (FORMAT_NAME = CSV_FORMAT);SIZE OF WAREHOUSE
Snowflake는 Storage Layer와 Compute Layer가 분리되어 있어서 웨어하우스마다 워크로드를 분담하여 리소스 경쟁 없이 빠르게 처리할 수 있는 특징이 있다. 이러한 웨어하우스의 사이즈는 티셔츠 사이징 방식으로 늘어나며 기본 사이즈인 X-Small부터 6X-Large까지 존재한다. 아래의 표를 보면 thread의 수가 사이즈마다 기재되어 있는데 이 thread의 수가 한번에 처리할 수 있는 파일의 수라고 보면 된다. 적재해야 할 파일의 수를 확인하고 적절한 웨어하우스의 사이즈를 지정하면 되는데 기본 사이즈인 X-Small부터 차례대로 늘려가면서 적재하는 것을 추천한다.
AWS S3에서 적재해야 할 파일(CSV)의 수에 따라 웨어하우스의 사이즈를 조절해가며 Snowflake로 Load한다. 실제로 적재해야 할 데이터는 CSV 형식으로 S3에 저장되어 있었고 데이터의 크기에 따라 웨어하우스의 사이즈를 조절해가면서 적재를 진행하였다. 아래의 표를 보면 웨어하우스의 사이즈가 커짐에 따라 적재 속도가 빨라지는 것을 볼 수 있다. 아래의 표는 실제로 PoC를 진행하며 S3에서 Snowflake로 데이터를 적재할 때 웨어하우스 크기에 따른 데이터 적재 속도 차이를 보여준다.
| 파일명 | 파일 수 | ROW 수 | X-SMALL | SMALL | MEDIUM | LARGE | X-LARGE | 2X-LARGE |
| A | 4 | 47,263 | 3.3s | - | - | - | - | - |
| B | 68 | 93,802,031 | 2m 13s | 1m 15s | 45s | 30s | ||
| C | 583 | - | - | - | - | 3m 19s | 1m 44s | 1m 3s |
웨어하우스를 생성하는 구문은 아래와 같다.
CREATE OR REPLACE WAREHOUSE WH1 WITH WAREHOUSE_SIZE = 'MEDIUM'; -- Default Size = 'X-Small'마이그레이션
1. ASIS에서 사용 된 함수(listagg, row_number) 등이 대상 웨어하우스 솔루션과 Snowflake에서 그대로 적용 및 사용 가능한지 체크
마이그레이션 작업을 할 때 ASIS에서 사용되었던 쿼리 문법 및 윈도우 함수 등이 그대로 적용 가능한지 체크해야 한다. DB마다 쿼리 문법이 조금씩 상이한 것처럼 같은 기능이더라도 해당 함수를 지원하지 않을 시 대체할 수 있는 방법을 찾아야 한다. 예를 들면 Snowflake에서는 스칼라 서브쿼리에 단일 조건이 아닌 조인 조건이 오면 지원하지 않기 때문에 이를 밑에서 해결하는 등의 쿼리 수정이 필요하고 LISTAGG 함수에 `DISTINCT` 키워드가 있을 경우 `ORDER BY` 절에 사용하는 컬럼과 동일해야 하는 등의 제약 조건이 있다. 좀 더 자세하게 살펴보자.
스칼라 서브 쿼리
SELECT DEPARTMENT_NAME,
(SELECT MAX(SALARY)
FROM EMPLOYEES
WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID) AS MAX_SALARY
FROM DEPARTMENTS;위의 쿼리는 각 부서의 최고 급여를 찾는 쿼리지만 SELECT 절에 단일 조건을 가져와야 한다는 Snowflake의 제한 사항에 위배 된다. 따라서 해당 쿼리는 아래와 같이 JOIN과 GROUP BY를 사용하여 각 부서별 최고 급여를 계산할 수 있도록 수정해야 한다.
SELECT DEPARTMENTS.DEPARTMENT_NAME, MAX(EMPLOYEES.SALARY) AS MAX_SALARY
FROM EMPLOYEES
JOIN DEPARTMENTS
ON EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
GROUP BY DEPARTMENTS.DEPARTMENT_NAME;
LISTAGG()
SELECT COL1
, LISTAGG(DISTINCT COL2, ',') WITHIN GROUP (ORDER BY COL3 DESC) AS COL4
FROM SNOWFLAKE_POC.POC_SCHEMA.T1
WHERE COL5 IN ('A','KR')
AND COL6 IS NOT NULL
GROUP BY COL1;위의 쿼리는 `DISTINCT` 키워드를 사용하여 중복 제거한 `COL2` 컬럼을 사용하지만 `ORDER BY` 절에서 `COL2` 컬럼이 아닌 `COL3` 컬럼을 사용하기 때문에 Snowflake의 제한 사항에 위배 된다. 따라서 해당 쿼리는 `WITH` 키워드를 사용하여 공통 테이블 식(CTE)를 정의하고 이를 활용하여 아래와 같이 수정하였다.
WITH CTE AS (
SELECT COL1
, COL2
, ROW_NUMBER() OVER (PARTITION BY COL1, COL2 ORDER BY COL3 DESC) AS RN
FROM SNOWFLAKE_POC.POC_SCHEMA.T1
WHERE COL5 IN ('A', 'KR')
AND COL6 IS NOT NULL
)
SELECT COL1
, LISTAGG(COL2, ',') WITHIN GROUP (ORDER BY RN) AS COL4
FROM CTE
WHERE RN = 1
GROUP BY COL1;
프로시저 차이
이 뿐만 아니라 프로시저의 경우 쿼리 실행 중에 메시지를 출력하거나 디버깅 용으로 사용하는 `RAISE NOTICE`, `RAISE INFO`, `DBMS_OUT` 같은 명령어를 사용할 수 없기 때문에 로그용 테이블을 생성한 뒤 해당 테이블에 로그를 쌓는 방향으로 진행하였다. 아래는 LOGS_RAISE 테이블을 생성해서 프로시저를 호출할 때 해당 테이블에 로그를 쌓는 예시이다.
-- 로그 테이블 생성
CREATE TABLE SNOWFLAKE_POC.DBMS_OUTPUT.LOGS_RAISE (
exec_time DATETIME
, msg VARCHAR
);
-- 프로시저의 실행 로그를 쌓는 커스텀 프로시저 LOGS_RAISE 생성
CREATE OR REPLACE PROCEDURE SNOWFLAKE_POC.DBMS_OUTPUT.LOGS_RAISE(msg_content VARCHAR)
RETURNS STRING
LANGUAGE JAVASCRIPT -- 이 밖에도 Java, Python, Scala 등의 다양한 언어로 작성할 수 있다.
AS
$$
var today = new Date();
var date = today.getFullYear()+'-'+(today.getMonth()+1)+'-'+today.getDate();
var time = today.getHours() + ":" + today.getMinutes() + ":" + today.getSeconds();
var dateTime = date+' '+time;
var msg = MSG_CONTENT
var log_stmt = snowflake.createStatement( {
sqlText: "INSERT INTO SNOWFLAKE_POC.DBMS_OUTPUT.LOGS_RAISE(exec_time, msg) VALUES(?,?)",
binds:[dateTime, msg]
} );
log_stmt.execute();
return msg
$$
;ASIS 프로시저에서 `RAISE INFO`를 활용하여 사용자에게 메시지를 보내거나 디버깅 정보를 출력했다면 TOBE에서는 위에서 만든 커스텀 프로시저를 활용해 로그를 쌓고 이후 사용자가 확인하는 방안으로 수정하였다.
-- ASIS
RAISE INFO 'SCM.MYPROCEDURE(%) START',yymm;
-- TOBE
CALL SNOWFLAKE_POC.DBMS_OUTPUT.LOGS_RAISE('SCM.MYPROCEDURE('||:yymm||') START');이처럼 마이그레이션 작업을 진행할 때 대상 솔루션 및 DB에서 호환이 되는지 확인하고 대상 솔루션에서 지원하는 문법 및 키워드에 맞게 커스텀이 필요하다. 또한 Snowflake는 각 워크로드마다 웨어하우스를 할당하여 리소스 경쟁 없이 처리할 수 있지만 초 단위로 과금이 되기 때문에 웨어하우스를 사용하지 않을 시 이를 `Suspend`하여 더욱 효율적이고 경제적으로 활용해야 할 필요가 있다. 이와 함께 해당 웨어하우스의 쿼리 문법과 가장 유사한 DB 엔진을 채택하여 과금부담 없이 테스트 하는 방법도 있으니 여러가지 방법으로 마이그레이션을 진행하는 것을 추천한다.
2. 정합성 확인 작업
정합성을 확인하기에 앞서 가장 중요한 것은 동일한 수집 주기의 데이터이다. 좀 더 자세히 말하자면 PoC 대상 테이블의 동일한 수집 주기가 보장되어야 이후 정확한 정합성 확인이 가능하다는 것이다. 쿼리 Conversion 작업 이후 1차적으로 정합성 테스트를 진행할 때 레거시 데이터(다수 테이블)의 수집 주기가 달라서 이에 대한 정합성이 지켜지지 않았다. 단기간에 수행해야 하는 PoC에서 고객이 운영하는 시스템의 모든 데이터 수집 주기를 파악한다는 것은 쉽지 않은 일이기 때문에 초반 Kick-off 또는 사전 회의에서 해당 사항을 정확하게 논의하고 명확한 데이터 수집 시점을 고정하는 것이 가장 중요하다. 그럼 정합성을 확인하는 작업을 살펴보자.
원본 테이블과 복제 테이블의 비교
원본 테이블과 동일한 테이블을 생성하여 프로시저를 실행한 후 원본 테이블과 복제 테이블의 전체 건수를 비교한다.
SELECT 'TBL_ORG' AS TBL, COUNT(*) FROM TBL_ORG UNION ALL
SELECT 'TBL_REP' AS TBL, COUNT(*) FROM TBL_REP;
HAVING 절 활용한 KEY 검증
Primary Key인 `COL1`, `COL2` 컬럼의 값이 중복되는 경우 중복된 값들의 레코드 수를 계산하여 반환하는 쿼리이다. 쿼리 실행 후 어떠한 레코드도 반환되지 않아야한다.
-- 원본 테이블 확인
SELECT COL1, COL2, COUNT(*)
FROM TBL_ORG
GROUP BY COL1, COL2
HAVING COUNT(*) > 1
;
-- 복제 테이블 확인
SELECT COL1, COL2, COUNT(*)
FROM TBL_ORG
GROUP BY COL1, COL2
HAVING COUNT(*) > 1
;
모든 컬럼의 VALUE 값 확인
SELECT
A.COL1 , B.COL1
A.COL2 , B.COL2
A.COL3 , B.COL3
A.COL4 , B.COL4
A.COL5 , B.COL5
FROM TBL_ORG A
INNER
JOIN TBL_REP B
ON A.COL1 = B.COL1
AND A.COL2 = B.COL2
WHERE A.COL1 != B.COL1
OR A.COL2 != B.COL2
OR A.COL3 != B.COL3
OR A.COL4 != B.COL4
OR A.COL5 != B.COL5
;위의 쿼리를 통해 원본 테이블과 복제 테이블을 조인하여 컬럼의 값을 모두 비교해 다른 VALUE 값이 있는 경우를 찾을 수 있다. 위 과정에서 VALUE가 틀어진 값이 반환된다면 대상 컬럼의 데이터를 어디에서 가져오는지 등의 확인이 필요하다. 실제 운영에서 사용되는 프로시저는 다수의 테이블이 조인되어 있고 각 테이블마다 데이터 수집 주기가 다를 수 있기 때문에 SQL/프로시저에서 사용되는 모든 테이블의 동일한 시점 데이터를 전부 재수집 하는 등 데이터가 틀어질 수 있는 모든 가능성을 열어두고 체크해야 한다.
이 밖에도 마이그레이션 과정 중 무작위한 대소문자 변경과 같은 1차원적인 작업도 데이터 정합성을 깨트릴 수 있는 요소 중 하나이며 각 DB 엔진마다 BOOLEAN 타입의 RETURN 값(TRUE, FALSE ↔ 0, 1)이 다를 수 있기 때문에 ASIS와 TOBE의 조건을 잘 확인하여 마이그레이션을 진행해야 한다.
'Snowflake' 카테고리의 다른 글
| [Snowflake] 3. Compute Layer (0) | 2023.06.09 |
|---|---|
| [Snowflake] 2. Storage Layer (1) | 2023.06.09 |
| [Snowflake] 1. Snowflake Overview and Architecture (0) | 2023.06.09 |
| Snowflake 프로젝트 사전 준비 - 2 (0) | 2023.05.23 |
| Snowflake 프로젝트 사전 준비 -1 (0) | 2023.05.23 |
