AWS Athena 에서 row to col (pivot) 구현하기

아래의 표와 같은 날짜가 동적으로 변경되는 List를 화면에 출력할 때 일반적인 RDB에서는 PIVOT 기능을 활용하여 쿼리를 아래 표와 같은 모양으로 실행하면 쉽게 구현할 수 있습니다.

그러나 AWS Athena에서는 아래 표와 같은 모양으로 쿼리를 실행할 수 없어서 JAVA에서 List를 만들어 주려고 합니다.

회사 코드	회사 명	2022-01-01	2022-01-02	2022-01-03	2022-01-04	2022-01-05	...
COMP001	우리회사	100	200	110	110	123	...
COMP002	니네회사	200	330	111	220	444	...

---

Athena Table 구조

Athena 테이블 구조는 다음과 같이 같단하게 구성하였습니다. 

CREATE EXTERNAL TABLE ex_row_to_col (
    comp_cd string COMMENT '회사 코드',
    comp_nm string COMMENT '회사 명',
    use_cnt int COMMENT '이용 회수'
)
PARTITIONED BY (
    reg_dt string COMMENT '등록 일자 yyyy-MM-dd'
)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
LOCATION 's3://athena/log/impressions/ex-row-to-col/'

---

Table 조회 쿼리

아래와 같이 Athena에서 지원하는 MULTIMAP_AGG 함수를 이용해서 comp_cd, comp_nm을 그룹으로 묶고 reg_dt 별 use_cnt 값을 keyValue 항목으로 출력하게 조회 쿼리를 작성하였습니다. 

SELECT comp_cd AS compCd
     , comp_nm AS compNm
     , MULTIMAP_AGG(reg_dt, use_cnt) AS keyValue
FROM   ex_row_to_col
WHERE  reg_dt >= '20220101'
AND    reg_dt <= '20220111'
GROUP BY comp_cd, comp_nm

이렇게 쿼리를 실행할 경우 다음과 같이 결과값이 출력됩니다.

compCd	compNm	keyValue
COMP001	우리회사	{2022-01-03=[49], 2022-01-04=[55], 2022-01-01=[26], 2022-01-02=[31], 2022-01-07=[45], 2022-01-08=[28], 2022-01-05=[45], 2022-01-06=[63], 2022-01-09=[41], 2022-01-10=[48], 2022-01-11=[94]}
COMP002	니네회사	{2022-01-03=[10], 2022-01-04=[11], 2022-01-01=[22], 2022-01-02=[99], 2022-01-07=[65], 2022-01-08=[77], 2022-01-05=[71], 2022-01-06=[23], 2022-01-09=[55], 2022-01-10=[66], 2022-01-11=[77]}

이렇게 출력된 쿼리 내용을 List<Map<String, String>> 형태로 받아서 다시 출력하는 LIST 형태로 변환 로직을 구현합니다.

List<Map<String, String>> 형태로 받아 오는 로직은 아래 링크를 참고하시면 됩니다.

AWS Athena 사용해서 게시판 만들기 with Java, jQuery

---

MULTI_MAPP 을 List map 으로 변환하기

위의 결과 값에서 보면 keyValue 값은 {2022-01-13=[49], 2022-01-04=[55]} 와 같은 형태로 출력됩니다.

이 값의 날짜를 key, 건수를 값으로 보고 각각의 날짜, 값을 컬럼으로 변경하려고 합니다.

/**
 * Athena 쿼리 조회 시 keyValue mapping 형태로 넘어온 데이터를 list 형태로 변환
 * @param str value
 * @return list map
 */
private List<Map.Entry<String, String>> convertStringToListMap(String str){
    String regex = "[\\[\\]{}\\s]"; // 특수문자 제거
    String[] pairs = str.replaceAll(regex, "").split(",");

    Map<String, String> convertMap = new HashMap<>();

    for (String pair : pairs) {
        String[] keyValue = pair.split("=");
        convertMap.put(keyValue[0], keyValue[1]);
    }

    // 정렬 후 list return
    return convertMap.entrySet().stream().sorted(Map.Entry.comparingByKey()).collect(Collectors.toList());
}

불필요한 특수문자([, ], {, }, 공백) 제거 후 콤마(,)를 기준으로 해서 keyValue 값을 split 해주고, 이퀄(=)을 기준으로 key, value 값을 구분해서 Map에 담은 후 key로 오름차순 정렬 후 List로 결과 값을 반환합니다.

이렇게 List<Map.Entry<String, String>> 형태로 반환된 값은 최종 list 의 값으로 만들기 위해 사용됩니다.

---

최종 목록 출력을 위한 데이터 세팅

이제 사용자에게 보여줄 최종 목록에 필요한 형태를 만드려고 합니다.

필요 항목 아래와 같습니다.

1. 목록 header

2. 목록 body

3. 합계

조회 조건을 Map<String, String> params로 받고 쿼리 결과를 List<Map<String, String>> dataList로 받는다는 가정하에 소스를 작성합니다.

// key set을 오름차순으로 정렬
List<String> keySet = dataList.get(0).keySet().stream()
    .sorted(String::compareTo).collect(Collectors.toList());

dataList의 값을 컬럼 순서대로 정렬해서 key 값을 List<String> 형태로 keySet 변수에 할당합니다.

header 생성

Map<String, String> headerMap = new HashMap<>();// 헤더 값
List<String> dataKeyList = new ArrayList<>();	// 데이터 key

Map<String, String> params, List<Map<String, String>> dataList
int keyCnt = 0;
for(String key : keySet){
    if(!key.equals("keyValue")){
        // keyValue값을 제외한 나머지 key를 순서대로 세팅
        headerMap.put(String.valueOf(keyCnt), key);
        
        // 나중에 key에 해당하는 dataList에 있는 값을 세팅하기 위해 key 값 저장
        dataKeyList.add(key);
        keyCnt++;
    }
}

1차로 keyValue 값을 제외한 나머지 컬럼들을 헤더로 만들기 위해 headerMap 변수를 생성해서 순서대로 담고, key에 해당하는 값을 세팅하기 위해서 dataKeyList에 세팅합니다.

 

int headerSize = headerMap.size();
String sDate = params.get("sDate").replaceAll("-", "");
String eDate = params.get("eDate").replaceAll("-", "");
if(params.get("dateSec").equals("day")){ // 출력 방법 구분 (day : 일, month : 월)
    int diff = EgovDateUtil.getDaysDiff(sDate, eDate); // 시작일과 종료일의 일 차이
    for(int i=0; i<=diff; i++){
        String mapKey = EgovDateUtil.getTrargetDay(sDate, "yyyy-MM-dd", i);
        headerMap.put(String.valueOf(i + headerSize), mapKey);
        sumMap.put(String.valueOf(i + headerSize), "0");
    }
} else {
	// 시작일과 종료일의 월 차이
    int diff = EgovDateUtil.getMonthsDifference(sDate, eDate, "yyyyMMdd");
    for(int i=0; i<=diff; i++){
        String mapKey = EgovDateUtil.getTrargetMonth(sDate, "yyyy-MM", i);
        headerMap.put(String.valueOf(i + headerSize), mapKey);
        sumMap.put(String.valueOf(i + headerSize), "0");
    }
}

파라미터로 넘어온 시작일자와 종료일자의 차이를 구해서 그만큼의 날짜를 값으로 하는 header를 세팅합니다.

1차로 등록한 headerMap의 key값 다음으로 입력하기 위해서 header size를 가져와서 map의 key로 하나씩 증가시켜서 값을 저장합니다.

그리고 합계 출력에 사용할 Map도 생성 후 headerMap과 key를 똑같이해서 값은 0으로 저장해 줍니다.

이렇게 저장했을 경우 headerMap 값은 아래와 같습니다.

headerMap.put("1", "compId");
headerMap.put("2", "compNm");
headerMap.put("3", "2022-01-01");    // dateSec : day
headerMap.put("4", "2022-01-02");
headerMap.put("5", "2022-01-03");
headerMap.put("6", "2022-01-04");
...

...
headerMap.put("3", "2022-01");       // dateSec : month
...

Body 생성

List<Map<String, String>> bodyList = new ArrayList<>();
for(Map<String, String> dataMap : dataList){
    Map<String, String> bodyMap = new HashMap<>();

    // header map 과 key 똑같이 세팅
    for(String headerKey : headerMap.keySet()){
        bodyMap.put(headerKey, "0");
    }

    // key value 값 list 형태로 변경
    List<Map.Entry<String, String>> valueList = convertStringToListMap(dataMap.get("keyValue"));

    for(Map.Entry<String, String> valueMap : valueList){
        String key = valueMap.getKey();
        String value = valueMap.getValue();

        // header map의 value값과 value map의 key값이 같은 value map의 value값 세팅
        for(String headerKey : headerMap.keySet()){
            String headerValue = headerMap.get(headerKey);
            if(headerValue.equals(key)){
                bodyMap.put(headerKey, value);

                // 합계 계산
                int sumVal = Integer.parseInt(sumMap.get(headerKey)) + Integer.parseInt(value);
                sumMap.put(headerKey, String.valueOf(sumVal));
            }
        }
    }

    // key value 값을 제외한 나머지 값 세팅
    int dataKeyCnt = 0;
    for(String dataKey : dataKeyList){
        bodyMap.put(String.valueOf(dataKeyCnt), dataMap.get(dataKey));
        dataKeyCnt++;
    }

    bodyList.add(bodyMap);
}

body map을 하나 생성해서 header와 같은 순서로 기본 값을 "0"으로 세팅합니다.

그리고 위에서 생성한 method convertStringToListMap을 활용해서 valueList에 keyValue 값을 할당한 다음 for문으로 각각의 값을 bodyMap에 입력하는데, hedaderMap의 key와 같은 valueList의 key의 value 값을 bodyMap에 입력합니다.

만약 keyValue에 없는 날짜는 값이 계속 "0" 입니다.

그리고 headerMap의 key와 같게해서 합계 값을 구해 sumMap에 세팅합니다.

 

마지막으로 key value 의 값을 제외한 나머지 key에 해당하는 값들도 bodyMap에 입력하고 최종 bodyList에 add 합니다.

 

이렇게 하면 최종 return 값은 아래와 같습니다.

Map<String, Object> returnMap = new HashMap<>();
returnMap.put("header", headerMap);		// list header 값 세팅
returnMap.put("body", bodyList);		// list body 값 세팅
returnMap.put("sum", sumMap);			// list sum 값 세팅

header 는 list table의 thead에 들어갈 내용들이고, body는 tbody, sum은 list 마지막 합계에 들어갈 내용들이 저장되어 있습니다.

화면에 출력하기

let arrHeader = Object.values(data.header); // 목록 헤더를 list로 변경
let arrBody = data.body;    // 목록 body
let arrSum = Object.values(data.sum);      // 목록 합계를 list로 변경

let tableContent = '<table class="table table-hover" id="listTable" data-size="">';
tableContent += '    <colgroup>';

let headerLen = arrHeader.length;
arrHeader.forEach(function(){
    // 컬럼 사이즈를 전체 100% 나누기 전체 컬럼 개수로 처리
    tableContent += '        <col style="width:'+(Math.round(100 / headerLen))+'%">';
});

tableContent += '    </colgroup>';
tableContent += '    <thead>';
tableContent += '        <tr>';

arrHeader.forEach(function(header){
    tableContent += '            <th>'+header.replace(/[#$]/gi, "")+'</th>';
});

tableContent += '        </tr>';
tableContent += '    </thead>';
tableContent += '    <tbody>';

arrBody.forEach(function(row){
    tableContent += '        <tr>';

    let arrCol = Object.values(row);
    arrCol.forEach(function(col){
        if(!isNaN(Number(col))){
            tableContent += '        <td>'+ commaChk(col) +'</td>';
        } else {
            tableContent += '        <td>'+ col +'</td>';
        }
    });
    tableContent += '        </tr>';
});

// 합계
let sumLen = arrSum.length;
if(arrSum.length > 0){
    let sumHeaderSize = headerLen - sumLen;
    tableContent += '        <tr>';
    tableContent += '            <td colspan="'+sumHeaderSize+'">합계</td>';
    arrSum.forEach(function(sum){
        tableContent += '        <td>'+ commaChk(sum) +'</td>';
    });
    tableContent += '        </tr>';
}

tableContent += '    </tbody>';
tableContent += '</table>';

let $dataList = $('#dataList');
$dataList.empty();
$dataList.append(tableContent);

ajax를 이용해서 데이터를 조회하고 조회한 데이터를 이용해서 화면을 그리는 소스 입니다.

map으로 넘어온 header 값을 array로 변환하고 header 개수만큼 for문으로 그려주고 width는 100% 나누기 컬럼 개수로 처리합니다.

다음 body list를 for문으로 돌면서 column을 array로 변환 후 다시 for문으로 각 항목에 맞는 column을 그려줍니다. 

이때 문자일 경우에는 그냥 출력하고 숫자일 경우에는 천단위 콤마를 찍어 주는 함수를 호출합니다.

그리고 마지막으로 합계 값을 같은 방식으로 그려주고 표시할 곳에 tableContent를 append하면 최종 완료 입니다. 

---

찾아보면 다른 방식으로 처리할 수 있을 수 있는데 찾는 것 보다는 직접 구현해보고 싶어서 직접 작성하였고, 기록으로 남기고 싶어서 정리해서 블로그에 작성하였습니다. 

중요한 부분만 추려서 작성하고 나머지 일반적인 부분은 생략하였습니다. 궁금한 사항이 있으면 댓글 달아주시면 답변 드릴 수 있는 부분은 드리도록 하겠습니다. 

 

추후 더 나은 방향으로 개선되면 그것 또한 정리해서 블로그에 작성할 예정입니다.