이번에는 Hive에서의 데이터 아키텍처 최적화 방법 중 '적은 데이터를 많게 만들어라'는 사상으로 HQL을 튜닝한 케이스를 살펴보도록 하겠습니다.
여기서 표현하고 있는 '적은 데이터'는 단순히 데이터의 건수가 적다는 것만을 뜻하는 건 아닙니다.
Equal 조인이 불가능하도록 데이터가 축약되어 있어 데이터의 분포도가 나쁜 경우를 의미합니다. 데이터가 축약되어 있기에 테이블의 건수가 상대적으로 적게 저장되어 있을 것입니다. 이런 경우에는 축약된 데이터를 상세화하여 하나의 로우로 표현된 데이터를 다수의 로우로 확장시켜줘야 합니다.
즉, 적은 데이터를 많게 만들어(상세화하여) 데이터의 분포도를 좋게 만들고 Equal 조인이 가능하도록 테이블 아키텍처를 변경해주는 작업이 필요합니다.
이번 튜닝 케이스에서 테이블 아키텍처를 변경하고자 하는 대상은 '국가별IP주소대역' 테이블입니다.
우리가 외부 네트워크에 접속하기 위해서는 우선 IP(이 포스트에서는 IPv4 기준으로 설명)를 할당받아야 합니다. 그런데 이 IP라는 것이 표현상의 한계(000.000.000.000 ~ 255.255.255.255)가 있기 때문에 IP 주소의 수도 한계가 있습니다. 따라서 국가별로 사용할 수 있는 IP대역을 정의하여 IP의 무분별한 사용을 제어하고 있습니다.
국가별로 사용할 수 있는 IP 대역이 지정되어 있으므로 우리는 IP주소만으로 접속자가 현재 어느 나라에서 접속을 하고 있는지 확인할 수 있습니다.
국가별 IP주소대역은 한국인터넷정보센터(KRNIC)에서 확인 가능하며, CSV 파일로 다운로드도 가능합니다.
* URL : https://krnic.or.kr/jsp/infoboard/stats/landCurrent.jsp
한국인터넷정보센터에서 다운로드한 국가별IP주소대역 CSV 파일의 구조는 아래와 같습니다.
국가별로 IP 주소 대역이 할당되므로 '시작IP'와 '끝IP'로 IP 대역의 범위를 표현해주고 있습니다.
'시작IP'와 '끝IP' 사이에 무수한 IP가 존재할 수 있는데 범위 값으로 데이터를 축약한 상태입니다.
'이 축약된 데이터를 어떻게 풀어나갈 것인가?'가 이 튜닝 케이스의 핵심이 되겠습니다.
우선 이번 튜닝의 결과는 아래와 같습니다.
위 결과는 제 PC에서 테스트용 데이터를 생성하여 측정한 결과입니다.
실제 운영 환경에서는 제가 디멘전 테이블 아키텍처 변경과 데이터 입력 작업만 수행하고, 실제 운영상의 쿼리 변경 작업은 운영 담당자가 진행하여 상세한 튜닝 결과는 확인하지 못했습니다. 운영 클러스터에서는 튜닝 전 약 2시간 정도 수행되는 쿼리가 튜닝 후에는 수행 시간이 약 7분 정도로 단축되었다고 합니다.
아마도 조인하고자 하는 Fact 테이블의 데이터 건수가 많을수록 성능 차이는 더욱더 크게 벌어질 것입니다.
참고로, 쿼리에 명시된 TEST_ODS.ACC_LOG 테이블(보안을 위해 스키마, 테이블, 컬럼 이름을 임의로 변경하여 사용)의 실 운영 데이터는 사양이 다른 다수의 인프라에서 수집된 로그입니다. 해당 DW 시스템에서 1일 수집되는 데이터는 약 500GB 이상이고요. 수집 대상 로그 데이터에 국가 정보 추가 요청을 원천 시스템에 요청할 수 있는 환경이 아니였습니다.
( 문의에 답변하고자 위 단락 내용 추가하였습니다. 2021/07/08 )
그럼 데이터 건수가 적은 '국가별IP주소대역(CNTRY_IP_RANGE)' 테이블의 아키텍처를 어떻게 변경하고 어떻게 데이터를 입력했는지 퀴즈를 풀듯이 하나하나씩 보여드리도록 하겠습니다.
| [ 튜닝 전 ] |
> 튜닝 전 쿼리
먼저 튜닝 전 쿼리를 살펴보도록 하겠습니다.
INSERT INTO TEST_DW.ACC_IP_CNTRY_MAPP PARTITION (ACC_ID, ACC_START_DT)
SELECT H.IP_ADDR
, G.CNTRY_CD
, G.CNTRY_NM
, H.C
, H.ACC_ID
, H.ACC_START_DT
FROM (
SELECT IP_ADDR
, COALESCE(SPLIT(IP_ADDR,'\\.')[0], '_') AS IP_OCTET1
, CAST( SPLIT(IP_ADDR,'\\.')[0] AS BIGINT) * 16777216 +
CAST( SPLIT(IP_ADDR,'\\.')[1] AS BIGINT) * 65536 +
CAST( SPLIT(IP_ADDR,'\\.')[2] AS BIGINT) * 256 +
CAST( SPLIT(IP_ADDR,'\\.')[3] AS BIGINT) AS IP_ADDR_NUMT
, COUNT(*) AS ACC_CNT
, MAX(ACC_ID) AS ACC_ID
, MAX(ACC_START_DT) AS ACC_START_DT
FROM TEST_ODS.ACC_LOG
WHERE ACC_ID = '${ACC_ID}'
AND ACC_START_DT = '${ACC_START_DT}'
GROUP BY IP_ADDR
) H
INNER JOIN
(
SELECT IP_OCTET1
, START_IP_NUMT
, END_IP_NUMT
, CNTRY_CD
, CNTRY_NM
FROM TEST_DW.CNTRY_IP_RANGE
) G
ON H.IP_OCTET1 = G.IP_OCTET1
WHERE H.IP_ADDR_NUMT BETWEEN G.START_IP_NUMT AND G.END_IP_NUMT;
쿼리문 하단에 보면 조인 컬럼으로 IP_OCTET1(파티션 키 컬럼)이 사용되었다는 것을 확인할 수 있습니다. WHERE 절에서는 BETWEEN 연산을 이용하여 IP 주소(IP_ADDR_NUMT)가 범위(START_IP_NUMT ~ END_IP_NUMT)에 포함되는 매핑 데이터만 필터링하고 있습니다.
위 텍스트 상으로는 성능 부하가 존재하는 쿼리인지 판단하는게 쉽지 않을 수 있습니다.
실제 테이블안 데이터의 형태와 분포도 등을 확인하면 좀 더 쉽게 성능상의 문제점을 파악할 수 있을 것입니다.
> IP 주소 구조
우선 IP 주소 구조를 간략하게 살펴보도록 하겠습니다.
IP 주소(정확하게는 IPv4)는 32자리의 이진수로 구성되어 있습니다. 이진수로 표현된 IP주소는 가독성이 떨어지므로 사람들이 쉽게 읽을 수 있도록 10진수로 변환하여 표기합니다. 그리고 더 가독성을 향상하기 위해 8비트마다 점( . , 옷뎃, OCTET)을 찍어 표현하도록 규정되어 있습니다.
일반적으로 우리가 데이터로 저장하고 관리하는 IP주소는 옷뎃으로 구분된 10진수로 된 문자열 형태(예: 10.168.80.15)입니다.
IP주소가 문자열 형태인 경우에는 가독성이 향상되지만 정렬이나 범위 검색 시에는 문제가 발생합니다.
예를 들어, 일부 문자열 형태의 IP 주소를 정렬한 결과는 아래와 같을 것입니다.
| 1.x.x.x 196.x.x.x 2.x.x.x 20.x.x.x 213.x.x.x 5.x.x.x 77.x.x.x 80.x.x.x 9.x.x.x 99.x.x.x |
위와 같은 이유로 문자열 IP주소로 정렬이나 범위 검색을 하는 경우에는 숫자로 변환하는 작업이 선행되어야 합니다.
문자열 IP 주소를 10진수 숫자로 변환하는 수식은 아래와 같습니다.
| 옷뎃1 * 16777216 + 옷뎃2 * 65536 + 옷뎃3 * 256 + 옷뎃4 |
위 수식을 SQL로 표현하면 아래와 같습니다.
SELECT CAST( SPLIT(IP_ADDR,'\\.')[0] AS BIGINT) * 16777216 +
CAST( SPLIT(IP_ADDR,'\\.')[1] AS BIGINT) * 65536 +
CAST( SPLIT(IP_ADDR,'\\.')[2] AS BIGINT) * 256 +
CAST( SPLIT(IP_ADDR,'\\.')[3] AS BIGINT) AS IP_ADDR_NUMT
FROM (SELECT '10.168.80.15' AS IP_ADDR) I;
물론 10진수로 표현된 IP 주소를 문자열 형태로도 변환 가능합니다.
아래는 문자열 IP주소로 변환하는 SQL문 예제입니다.
SELECT CONCAT( CAST(CAST(IP_ADDR_NUMT / 16777216 AS INT) AS STRING) , '.',
CAST(CAST(IP_ADDR_NUMT / 65536 % 256 AS INT) AS STRING) , '.',
CAST(CAST(IP_ADDR_NUMT / 256 % 256 AS INT) AS STRING) , '.',
CAST(CAST(IP_ADDR_NUMT % 256 AS INT) AS STRING)
) AS IP_ADDR
FROM (SELECT 178802703 AS IP_ADDR_NUMT) I;
실제 쿼리에서 IP 주소의 범위 검색 연산은 위와 같이 10진수로 표현된 숫자 IP 주소를 사용하였습니다.
하지만, 이 포스트에서는 장표나 부연 설명을 단순화 하기 위해 문자열 형태의 IP주소로 표기하였으니 참고 바랍니다.
> 데이터 처리 방식 1 (국가별IP주소대역이 비 파티션 테이블인 경우)
접속로그(ACC_LOG)의 IP 주소가 해당되는 국가 정보를 확인하기 위해서는 우선 국가별IP주소대역(CNTRY_IP_RANGE) 테이블과 조인을 해야 합니다.
그런데 국가별IP주소대역 테이블에는 국가코드(CNTRY_CD)별 시작IP(START_IP)와 끝IP(END_IP) 정보만 존재합니다.
국가별 IP주소대역이 시작과 끝이라는 범위로 표현되어 있기 때문에 Equal 조인이 불가능합니다. 어쩔 수 없이 크로스 조인(카테시안 곱) 후 BETWEEN 연산자를 사용해 접속로그 IP주소에 해당되는 국가코드를 찾도록 필터링해야 합니다.
위와 같이 '국가별IP주소대역' 테이블과 '접속로그' 테이블을 크로스 조인하게 되면 조인 결과 데이터 셋의 건수는 '국가별IP주소대역 데이터 건수 * 접속로그 테이블 건수'가 될 것입니다.
문제는 하나의 잡에 할당되는 맵과 리듀스의 건수는 조인하고자 하는 테이블 데이터의 크기를 기반으로 산정되어, 조인 결과 셋 크기에 비해 적은 수의 맵과 리듀스 태스크가 할당될 수 있습니다. 과도하게 큰 데이터를 적절하게 분산 처리하지 못하고 적은 수의 태스크에서 큰 데이터를 처리하게 되니 연산 속도가 떨어지게 됩니다. 심한 경우에는 자원 부족에 의해 태스크 실패가 발생할 수도 있습니다.
이러한 이유에서인지 하이브에서는 크로스 조인에 의한 부하를 방지하기 위해서 기본적으로 카테시안 곱 연산을 막아놨습니다. (일부 옵션을 변경 후 크로스 조인 가능) 그만큼 부하가 큰 연산인 관계로 쿼리 작성 시 지양해야 할 조인 방식입니다.
크로스 조인의 부하를 제거하기 위해서는 Equal 조인이 가능하도록 '국가별IP주소대역' 테이블 아키텍처를 변경해 Equal 조인이 가능한 컬럼을 추가해줘야 합니다.
> 데이터 처리 방식 2 (국가별IP주소대역 테이블을 파티셔닝)
위 튜닝 전 쿼리에서 매핑되는 데이터는 IP주소입니다.
각 IP주소를 Equal 조인하기 위해서는 '국가별IP주소대역' 테이블에 Equal 조인이 가능한 IP주소 기반의 추출 속성을 추가해줘야 합니다.
'국가별IP주소대역' 테이블에는 시작IP와 끝IP 컬럼이 존재합니다. 매핑시에도 두 컬럼을 이용한 범위 조건 연산을 하므로 신규 추출 컬럼은 시작IP와 끝IP 간의 공통 정보를 기반으로 생성되어야 합니다.
IP주소 구조에서 설명했듯이 IP 주소는 옷뎃(.)으로 구분됩니다. 시작IP와 끝IP에서 각 옷뎃별로 동일한 데이터가 공통적으로 존재한다면 공통된 옷뎃을 Equal 조인 컬럼으로 사용할 수 있을 것입니다.
한국인터넷정보센터에서 다운로드한 국가별IP주소대역 CSV 파일(2020년 7월 기준) 데이터를 기반으로 시작IP와 끝IP의 옷뎃별 일치율을 점검한 결과는 아래와 같습니다.
시작IP와 끝IP에서 100% 동일한 값을 가진 옷뎃은 옷뎃1 뿐입니다. 이 옷뎃1(IP_OCTET1)을 조인 키 컬럼으로 사용하여 Equal 조인 연산을 할 수 있습니다.
부수적으로 국가별IP주소대역 테이블은 대용량의 테이블과 조인되므로 건수는 적지만(190,350건) 옷뎃1별로 파티셔닝 하였습니다.
이제 파티션 된 국가별IP주소대역 테이블과 접속로그 테이블을 조인 시 데이터 처리 방식을 확인해보겠습니다. 참고로 IP_OCTET1 컬럼으로 파티션된 테이블이 튜닝 전 쿼리에 해당되는 테이블 형상입니다.
IP_OCTET1 추출 속성을 생성하고 파티셔닝을 적용하여 파티션 단위의 Equal 조인이 가능하게 되었습니다. 크로스 조인 결과 데이터 셋도 당연히 크기가 감소하게 됩니다.
하지만 형식상 Equal 조인 연산 일뿐, 매핑되는 데이터가 N:M이므로 사실상 파티션 별 크로스 조인입니다. 범위가 넓은 M:N 매핑이라 Equal 조인에 의한 부하 감소는 크지 않습니다. 실제 운영환경에서도 잡 실패가 발생할 만큼 완벽하게 부하를 제거했다고 볼 수는 없는 아키텍처입니다.
특히 파티션 키 컬럼인 IP_OCTET1 컬럼의 데이터 분포를 확인해보면 파티셔닝 전략이 성공적이었다고 볼 수는 없을 것입니다.
파티션 별 데이터 건수의 편차가 너무 큰 관계로 데이터가 몰려있는 파티션 데이터를 할당받은 태스크는 부하가 아주 클 것이라고 예상하실 수 있을 것입니다.
| [ 튜닝 후 ] |
> 국가별IP주소대역 테이블 아키텍처 전략
아래 그림의 예제처럼 기존의 '국가별IP주소대역' 테이블 내 데이터는 국가별 IP 주소 값이 시작IP와 끝IP로 축약되어 있습니다. 시작IP와 끝IP 사이에는 무수히 많은 IP가 존재할 수도 있는데도 말입니다.
축약되어 표현된 데이터로 '국가별IP주소대역' 테이블의 건수는 적지만 IP주소와 매핑 시 크로스 조인으로 인하여 대량의 조인 결과 데이터 셋이 생성되게 됩니다.
대량의 조인 결과 데이터 셋을 방지하는 방법은 '국가별IP주소대역' 테이블에서 축약되어 표현된 IP주소 정보를 풀어서 다수의 로우로 더 상세하게 표현하는 것입니다. 시작IP와 끝IP 사이에 존재하는 IP대역 정보를 세분화하여 각각의 로우로 저장을 하는 것이죠.
위 그림의 예제 '국가별IP주소대역' 테이블의 첫 번째 로우의 시작IP는 '103.106.140.0'이고 끝IP는 '103.106.143.255'입니다. 이 두 IP 사이에는 '103.106.140.x', '103.106.141.x', '103.106.142.x', '103.106.143.x'의 IP가 존재하게 됩니다.
이 IP 주소를 '옷뎃1.옷뎃2.옷뎃3' 단위로 세분화하여 각각의 로우로 저장할 수 있습니다.
세분화된 각 로우의 시작IP와 끝IP는 옷뎃1, 옷뎃2, 옷뎃3의 값이 동일하기 때문에 '옷뎃1.옷뎃2.옷뎃3' 형태의 IP_OCTET123라는 추출 속성을 만들 수 있습니다.
세분화 단위에서 옷뎃4는 왜 제외되었는지 궁금해 하실 수 있습니다. 국가별IP주소대역 데이터를 보면 거의 모든 데이터가 옷뎃3 단위로 구분되어 있으며, 옷뎃4 단위로 데이터가 구분되는 경우는 미비합니다.
옷뎃3까지의 데이터 만으로도 IP주소와 매핑 시 거의 모든 데이터가 1:1로 매핑될 수 있습니다.
옷뎃4 단위로 즉, 전체 IP 주소의 국가 정보를 저장하게 된다면 '국가별IP주소대역_확장' 테이블 데이터 건수의 256배의 데이터를 저장해야 하는데, 이는 쿼리 수행 시 액세스 대상 데이터의 크기를 증가시키는 원인이 될 수 있습니다.
위와 같은 이유로 '국가별IP주소대역_확장' 테이블은 옷뎃3 단위로 데이터를 세분화하였습니다.
위와 같은 방법으로 '국가별IP주소대역_확장' 테이블을 생성하면 IP_OCTET123 컬럼을 조인 키 컬럼으로 사용할 수 있게 됩니다.
IP_OCTET123 컬럼을 조인 키 컬럼으로 사용하게 되면 Equal 조인 시 두 테이블 데이터 간 매핑 정확도가 높아지게 됩니다. 이로 인하여 조인 결과 데이터 셋의 크기가 감소되고 태스크에서 처리해야 할 작업량이 줄어들어 성능 부하도 자연스럽게 감소됩니다.
또한, 파티션 별 데이터 건수는 아래와 같이 거의 고르게 분포되어 있습니다. 파티션 별 데이터 건수의 편차가 거의 없기에 특정 태스크에 작업이 몰리는 Skew가 발생할 가능성이 더 낮아지게 됩니다.
참고로, 파티션 키 컬럼은 IP_OCTET1으로 '국가별IP주소대역' 테이블과 동일합니다. 건수가 적은 테이블이고 테이블 당 파티션 수에 대한 제약이 존재하여 파티션 키는 동일하게 관리하였습니다.
> 튜닝 후 쿼리
튜닝 후 쿼리는 아래와 같습니다.
INSERT INTO TEST_DW.ACC_IP_CNTRY_MAPP PARTITION (ACC_ID, ACC_START_DT)
SELECT H.IP_ADDR
, G.CNTRY_CD
, G.CNTRY_NM
, H.ACC_ID
, H.ACC_START_DT
FROM (
SELECT IP_ADDR
, COALESCE(SPLIT(IP_ADDR,'\\.')[0], '_') AS IP_OCTET1
, CAST( SPLIT(IP_ADDR,'\\.')[0] AS BIGINT) * 16777216 +
CAST( SPLIT(IP_ADDR,'\\.')[1] AS BIGINT) * 65536 +
CAST( SPLIT(IP_ADDR,'\\.')[2] AS BIGINT) * 256 +
CAST( SPLIT(IP_ADDR,'\\.')[3] AS BIGINT) AS IP_ADDR_NUMT
, ${ACC_ID} AS ACC_ID
, ${ACC_START_DT} AS ACC_START_DT
, CONCAT( SPLIT(IP_ADDR,'\\.')[0], '.', SPLIT(IP_ADDR,'\\.')[1], '.', SPLIT(IP_ADDR,'\\.')[2] ) AS IP_OCTET123_DIV
FROM TEST_ODS.ACC_LOG
WHERE ACC_ID = ${ACC_ID}
AND ACC_START_DT = ${ACC_START_DT}
GROUP BY IP_ADDR
) H
INNER JOIN
(
SELECT IP_OCTET1
, IP_OCTET123_DIV
, PART_START_IP_NUMT
, PART_END_IP_NUMT
, CNTRY_CD
, CNTRY_NM
FROM TEST_DW.CNTRY_IP_RANGE_EXTENT
) G
ON H.IP_OCTET1 = G.IP_OCTET1
AND H.IP_OCTET123_DIV = G.IP_OCTET123_DIV
WHERE H.IP_ADDR_NUMT BETWEEN G.PART_START_IP_NUMT AND G.PART_END_IP_NUMT
;
접속IP주소(IP_ADDR) 별로 국가정보(CNTRY_CD와 CNTRY_NM)를 매핑하기 위해 아키텍처를 개선한 '국가별IP주소대역_확장'(CNTRY_IP_RANGE_EXTENT) 테이블을 조인합니다.
이때, 조인 키 컬럼으로는 파티션 키 컬럼인 IP_OCTET1과 '옷뎃1.옷뎃2.옷뎃3' 정보를 저장한 IP_OCTET123_DIV 컬럼을 사용하게 됩니다.
IP_OCTET123_DIV 컬럼을 조인 키 컬럼으로 사용하게 되어 거의 모든 데이터가 1:1로 매핑되고 조인 결과 데이터 셋의 크기가 감소되어 쿼리 성능이 향상되게 됩니다.
> 국가별IP주소대역_확장 테이블 데이터 만들기
이번 포스트의 핵심은 '국가별IP주소대역' 테이블을 이용해 '국가별IP주소대역_확장' 테이블 데이터를 만드는 것이라고 볼 수 있습니다.
'국가별IP주소대역_확장' 테이블 데이터를 만드는 쿼리문은 아래 Github에 업로드 해놨습니다.
* Github url : https://github.com/sparkdia/DB_Tuning/tree/master/IP_with_countries_mapping
참고로 '국가별IP주소대역_확장' 테이블 데이터를 생성하는 쿼리 작업이 크로스 조인으로 인해 부하가 있을 수 있습니다.
옷뎃별 확장되는 데이터 건수(OCTET2_GAP, OCTET3_GAP)나 파티션 키 컬럼(IP_OCTET1) 기준으로 Insert 작업을 분할하여 수행하는 Shell Script도 업로드해놨으니 참고 바랍니다.
긴 글 읽어주셔서 감사드리고요. 하이브에서의 튜닝 케이스였지만 테이블 아키텍처 변경 작업을 통한 성능 개선이므로 다른 RDBMS에서도 적용이 가능할 겁니다.
혹시 내용상의 오류나 궁금한 사항이 있으시면 친절한 댓글 부탁드립니다.
감사합니다.
'BigData > Hive' 카테고리의 다른 글
| Hive 2.3.6 설치 (CentOS 8) (2) | 2020.08.07 |
|---|---|
| HiveQL Tuning Case #1 - 많은 데이터는 적게 만들기 (0) | 2020.06.24 |
| ORC(Optimized Row Columnar) in 하이브(Hive) (0) | 2020.06.13 |
| 하이브(Hive) 파일 형식 (0) | 2020.06.01 |
| Hive 튜닝 기본 -실행계획에서 Partition pruning 확인하기 (0) | 2020.04.29 |