[SpringBoot] 대용량 엑셀 파일 생성 및 다운로드 삽질기

개요

작년에 이어 올해도 엑셀 파일 생성 및 다운로드 기능을 담당하게 되었습니다.

https://jaimemin.tistory.com/1889

[SpringBoot] 대용량 엑셀 다운로드를 위한 SXSSFWorkbook

한 가지 달라진 점은 기존 프로젝트와 달리 이번에는 OOM을 방지하기 위해 파일 업로드 및 생성을 별도 인스턴스에서 진행한다는 점입니다.

기존에는 모두 한 서버 내에서 진행했기 때문에 API에 대해 크게 신경을 쓰지 않아도 됐는데 이번에는 파일 전용 서버가 별도로 있었기 때문에 API 설계부터 많은 고민을 해야 했습니다.

제가 워낙 부족한 개발자이기 때문에 제가 한 방법이 정답은 아니겠지만 누군가에게는 도움이 될 것이라고 생각하고 과정과 코드를 기록에 남깁니다.

더 좋은 방법이 있다면 댓글로 알려주시면 감사하겠습니다!

 

1. 초기 접근법

초기에 생각했던 접근 방법은 아래와 같습니다.

• 화면에서 엑셀 생성 요청을 하면 컨트롤러에 엑셀 생성 요청
  • 조회 조건 RequestParam으로 전달
• 컨트롤러에서 엑셀 생성 인스턴스에 해당 요청을 전달
• 엑셀 생성 인스턴스에서 조회 조건 기반 엑셀 생성 후 생성된 엑셀 반환
• AbstractXlsxView를 통해 엑셀 다운로드

 

얼핏 보기에는 별 문제없을 것 같지만 해당 API 설계에는 몇 가지 치명적인 단점이 있습니다.

• API의 Body 내 Workbook이 이동하기 때문에 엑셀의 크기가 커질 경우 API 성능 과부하 발생 가능
• 요청한 엑셀 Row가 많아 엑셀 생성하는데 시간이 오래 걸릴 경우 gateway timeout이 발생하여 클라이언트 측에서 생성된 엑셀을 다운로드하지 못할 가능성 존재
  • gateway timeout이 발생하더라도 파일 인스턴스에서는 계속 엑셀을 생성하기 때문에 자원 낭비

 

2. 보완된 접근법

앞선 접근법에 존재하는 단점을 보완하기 위해 나름 짱구를 굴려 보완된 방법을 생각해봤습니다.

• 화면에서 엑셀 생성을 요청을 하여 컨트롤러에 엑셀 생성 요청
  • 조회 조건과 함께 고유 파일명을 RequestParam으로 전달
  • 요청하는 순간 고유 파일명을 SessionStorage에 저장하여 polling 방식으로 해당 파일명이 세션에 존재하는지 확인
• 컨트롤러에서 엑셀 생성 인스턴스에 요청 전달하고 화면에서 전달한 파라미터에 더해 엑셀 생성을 마친 뒤 호출할 콜백 URL도 함께 전달
• 엑셀 생성인스턴스에서 조회 조건 기반으로 엑셀 생성 후 NAS에 엑셀 저장
  • 해당 프로젝트에서는 NAS를 사용하기 때문에 NAS에 저장했지만 파일 인스턴스 내 저장해도 무방할 것 같음
• NAS에 엑셀 저장 후 파라미터로 받은 callback URL을 호출
  • NAS 내 파일 경로를 RequestParam으로 전달
• callback URL 호출받은 후 세션 내 파일명 저장
• 세션에 파일명이 존재할 경우 초기에 주기적으로 polling 했던 API에서 파일명이 존재한다고 응답을 할 것이고 이때 AbstractXlsxView를 통해 엑셀 다운로드

 

위 방법을 적용할 경우 기존의 문제점을 해결할 수 있습니다.

• API의 body 내 무거운 workbook이 왔다 갔다 하지 않으므로 성능 과부하를 피할 수 있음
• 엑셀이 생성된 후 callback URL을 호출하는 방식이므로 gateway timeout을 피할 수 있음

 

이 방법을 적용할 경우 view에서 주기적으로 엑셀이 생성되었는지 확인하기 위해 polling을 해야 한다는 문제점이 있지만 아직까지는 마이너 한 문제점이라고 판단하여 현재 엑셀 생성 및 다운로드하는 기능은 위 방법을 통해 진행하고 있습니다.

 

2.1 좀 더 보완된 접근법 (2022.05.16 기준)

보완된 접근법에서 추가적으로 보완할 방법을 생각하던 중 팀장님으로부터 Nginx reverse proxy를 적용하는 것을 추천받았습니다.

관련 내용은 아래 링크를 참고해주세요.

https://jaimemin.tistory.com/2121

[Nginx] Nginx Reverse Proxy 정리

 

우선, 기존 보완된 접근법의 문제점은 아래와 같습니다.

• 파일 인스턴스로부터 callback을 받은 WAS가 파일을 내려보낼 때 엑셀 파일을 File 객체를 통해 연 뒤 OutputStream으로 내려보내기 때문에 파일 인스턴스가 아닌 WAS에서 추가적으로 리소스를 사용합니다. 엑셀 크기가 클 경우 부하를 줄 수 있습니다.
• WAS끼리 RestTemplate으로 api를 호출하기 때문에 불필요한 지연이 발생합니다.

 

위 문제점을 해결하기 위해 Nginx Reverse Proxy를 적용하였고 파일 관련 api는 모두 파일 인스턴스에서 처리할 수 있도록 적용했습니다.

이를 통해 파일 관련 리소스는 파일 인스턴스에서만 잡아먹고 WAS끼리의 불필요한 네트워크 통신을 방지할 수 있었습니다.

 

3. 코드 및 설명

코드는 앞서 설명한 순서대로 소개해보겠습니다.

 

3.1 View 내 jQuery

 

 

* 고유 파일명을 부여하기 위해 UUID를 파일명에 포함했습니다.

* 편의를 위해 조회 조건을 넘기는 것은 생략했습니다.

 

 

3.2 Controller + ExcelView

 

 

 

* ExcelController는 엑셀 생성 요청을 받는 API, 세션에 파일명을 저장하는 콜백 API

* ExcelApiController는 view에서 polling 방식으로 파일명이 세션 내 존재하는지 확인하는 API

* ExcelView는 AbstractXlsView를 상속받은 View

 

ExcelView 부연 설명

ExcelView 클래스의 경우 부연설명이 필요합니다.

저는 초기에 주석으로 표시된 두 줄처럼 FileInputStream과 XSSFWorkbook을 통해 엑셀을 workbook으로 저장하고 뷰에 엑셀을 내려주려고 했습니다.

하지만 이럴 경우 엑셀 파일의 용량이 조금만 커지더라도(10MB만 되더라도) 아래와 같이 heap size 메모리 오류가 발생합니다.

java.lang.OutOfMemoryError: Java heap space

 

저장된 엑셀은 분명 10MB 밖에 안되기 때문에 메모리 부족이 발생할 것 같지 않았는데 OKKY 게시글 답변을 보고 해당 에러가 발생한 이유를 알 수 있었습니다.

엑셀파일 용량 작아보이죠.

근데 그건 파일 형식 자체에 압축기능이 들어가서 그런 것이고, 압축 풀면, 고작 5만건 정도에 수백메가 정도 합니다.

https://okky.kr/article/972184

OKKY | JVM heap 메모리 사용량 많을 때 해결 법 질문드립니다.

 

위 답변을 보면 알 수 있다시피 엑셀 파일이 조금만 커지더라도 workbook을 바로 읽을 수 없으므로 저는 monitorjbl님이 개발해주신 오픈소스를 통해 스트리밍 방식으로 엑셀을 cell 단위로 읽고 SXSSFWorkbook을 재구성하는 방식으로 위 문제를 해결했습니다.

https://github.com/monitorjbl/excel-streaming-reader

GitHub - monitorjbl/excel-streaming-reader: An easy-to-use implementation of a streaming Excel reader using Apache POI

 

엑셀 생성 인스턴스에서도 SXSSFWorkbook을 생성하고 ExcelView 내에서도 SXSSFWorkbook을 생성하기 때문에 다소 비효율적인 방법이지만 현재로서는 이 방법이 최선이라고 생각했기 때문에 이렇게 구현했습니다.

 

3.3 엑셀 생성 인스턴스

 

 

* 2022.05.16 부족한 코드 업데이트했습니다.

 

우선, 엑셀 생성하는 코드는 아래 링크를 참고해주세요.

[SpringBoot] 대용량 엑셀 다운로드를 위한 SXSSFWorkbook

* 어차피 ExcelView에서 SXSSFWorkbook을 재구성하므로 기존에 map으로 넘겼던 align과 width는 안 넘겨도 될 것 같습니다.

 

엑셀 생성 인스턴스 부연 설명

20번째 줄을 보면 임시 파일명으로 우선 엑셀을 저장한 뒤 46번째 줄 finally 절에서 파라미터로 넘겨받은 파일명으로 변경하는 것을 확인할 수 있습니다.

이렇게 한 이유는 대용량 엑셀을 생성할 때 데이터를 다 입력한 상태에서 파일이 생성되는 것이 아니라 파일을 생성한 뒤 데이터들이 입력되기 때문입니다.

위 방식대로 진행하지 않고 바로 파라미터로 넘겨받은 파일명으로 엑셀 파일을 생성할 경우 polling 방식으로 요청할 때 파일이 다 생성되지 않았는데도 파일이 존재한다고 판단하여 다운로드를 시도하고 그렇게 할 경우 아래와 같은 에러가 발생합니다.

Exception: Unexpected end of ZLIB input stream

 

따라서, 저는 임시 파일명으로 파일을 생성한 뒤 데이터가 전부 입력되면 원래 파일명으로 바꾸는 방식으로 구현을 했습니다.

 

3.4 SessionListener

엑셀 파일이 NAS나 인스턴스에 쌓일 경우 메모리를 상당히 잡아먹을 수 있기 때문에 일정 시간이 지나면 삭제해줘야 합니다.

따라서, 엑셀이 생성되고 30분이 지나면 파일을 제거하는 방식을 선택했고 코드는 아래와 같습니다.

 

 

 

* SessionListener는 Bean으로 등록해줘야 동작합니다.

 

4. 후기

우선, 긴 글 읽어주셔서 감사합니다.

API를 직접 설계하고 구현까지 완료해서 대단한 일을 한 것처럼 느껴졌는데 막상 글로 작성하고 보니 태클받을 요소가 많이 보이는 것 같습니다.

앞서 개요에서도 말했다시피 지적, 조언은 언제나 환영입니다!

고수님들 많은 도움 부탁드립니다!

이제 저는 wav 파일 업로드 기능을 개발하러 20000...

 

[업데이트] 22.09.15

response stream에 바로 XLSX를 작성할 수 있는 fastexcel 라이브러리를 도입하기 위해 정리한 내용 공유 드립니다.

https://jaimemin.tistory.com/2191

[SpringBoot + Fastexcel] 대용량 엑셀 생성 및 다운로드