데이터가 별로 많지 않으면 클라이언트에서 엑셀파일을 만들어 다운받는것도 고려할만 하나
데이터가 수십만건 이상일 경우 메모리부족, 타임아웃 등의 문제가 생기기 때문에 백엔드에서 처리하는 방법을 고려해야 한다.
하지만 백엔드에서 처리를 한다고 해도 응답시간 지연으로 인한 타임아웃, 백엔드 자체의 메모리 부족이 발생할 수 있는데 이 때 SpringBoot3에서 선택할 수 있는 해결방법을 공유해보고자 한다.
고려사항
방대한 양의 DB 데이터 조회로 인한 메모리 부족
DB데이터로 생성하는 엑셀파일의 메모리 부족
엑셀파일 생성까지 http응답을 기다리다 타임아웃 방지
해결방법
엑셀파일 생성 : 버퍼 사이즈 마다 저장소에 temp파일을 생성하는 라이브러리 선택
DB 데이터 조회 : ResultHandler를 이용해 fetchSize마다 데이터 처리
http 응답시간 지연 : 비동기 파일생성 및 후속조치
Service
ExcelService.java
public <E> void generateExcel(Consumer<ResultHandler<E>> queryExecutor,
Class<E> clazz) throws IOException {
}ResultHandler를 재정의 하기 위한 필드와 반환 객체를 담을 필드를 매개변수로 만들어준다.
엑셀파일 생성 : SXSSFWorkbook 라이브러리
public <E> void generateExcel(Consumer<ResultHandler<E>> queryExecutor,
Class<E> clazz) throws IOException {
Workbook workbook = new SXSSFWorkbook(1024);
ByteArrayOutputStream baos = new ByteArrayOutputStream();
try {
workbook.write(baos);
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
MultipartFile excelMutlpartFile = new CustomMultipartFile(baos.toByteArray(),
"excelName.xlsx",
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
uploadService.upload(excelMultipartFile);
}SXSSFWorkbook을 사용하면 지정된 row만큼 데이터가 버퍼에 쌓이면 temp폴더에 임시 파일이 생성되어 저장하게 된다. 따라서 엑셀파일을 생성하다가 메모리가 초과되는 현상을 방지할 수 있다.
이 때 버퍼의 크기는 new SXSSFWorkbook(1024); 에서 객체를 생성할 때 설정 가능하며, -1을 넣어주면 통째로 생성한다.
참고로 byte[]형태로 된 파일 데이터를 MultipartFile로 생성해 저장하기 위해 CustomMultipartFile에서 MutipartFile을 상속해서 사용했다.
CustomMultipartFile.java
public class CustomMultipartFile implements MultipartFile {
private final byte[] fileContent;
private final String fileName;
private final String contentType;
public CustomMultipartFile(byte[] fileContent, String fileName, String contentType) {
this.fileContent = fileContent;
this.fileName = fileName;
this.contentType = contentType;
}
@Override
public String getName() {
return fileName;
}
@Override
public String getOriginalFilename() {
return fileName;
}
@Override
public String getContentType() {
return contentType;
}
@Override
public boolean isEmpty() {
return fileContent.length == 0;
}
@Override
public long getSize() {
return fileContent.length;
}
@Override
public byte[] getBytes() throws IOException {
return fileContent;
}
@Override
public InputStream getInputStream() throws IOException {
return new ByteArrayInputStream(fileContent);
}
@Override
public void transferTo(File dest) throws IOException {
try (FileOutputStream fos = new FileOutputStream(dest)) {
fos.write(fileContent);
}
}
}ResultHandler
SampleMapper.java
@Options(fetchSize = 1024)
void findExcelData(ResultHandler<SampleExcelDto> handler);Mapper의 매개변수로 ResultHandler 객체를 선언해준다
이때 Options에 fetchSize를 적용해 개별 fetchSize 적용이 가능하다.
ExcelService.java
public <E> void generateExcel(Consumer<ResultHandler<E>> queryExecutor,
Class<E> clazz,
List<String> headerList) throws IOException {
...
AtomicReference<Sheet> sheet = new AtomicReference<>(null);
AtomicInteger currentSheet = new AtomicInteger(0);
AtomicInteger rowIndex = new AtomicInteger(0);
ResultHandler<E> handler = context -> {
E data = context.getResultObject();
// 새 sheet 생성
if(rowIndex.get()/1048575 + 1 != currentSheet.get()) {
currentSheet.incrementAndGet();
sheet.set(workbook.createSheet("Sheet " + currentSheet.get()));
// header 데이터 세팅
Row headerRow = sheet.get().createRow(0);
for(int j = 0; j < headerList.size(); j++) {
headerRow.createCell(j).setCellValue(headerList.get(j));
}
}
// row 생성
Row row = sheet.get().createRow((rowIndex.getAndIncrement() % 1048575) + 1);
Field[] fields = clazz.getDeclaredFields();
// row에 데이터 삽입
for (int cellIndex = 0; cellIndex < fields.length; cellIndex++) {
fields[cellIndex].setAccessible(true);
try {
Object value = fields[cellIndex].get(data);
Cell cell = row.createCell(cellIndex);
if (value instanceof Number) {
cell.setCellValue(((Number) value).doubleValue());
} else {
cell.setCellValue(value != null ? value.toString() : "");
}
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
};
// 쿼리 실행
queryExecutor.accept(handler::handleResult);
...
}ResultHandler로 엑셀 의 row를 생성해주도록 재정의 해주고 쿼리를 실행시켜준다.
이 때 () -> {}에서 사용 가능한 변수를 만들어주기 위해 Atomic 객체를 이용해준다.
excel 파일에서 한 sheet에 들어가는 최대 row 수는 1,048,576개 이며, header를 고려하면 1,048,575개의 row를 적재할 수 있다.
이를 고려해서 다음 sheet를 생성해서 데이터를 넘기는 로직을 고려해야 한다.
위 코드에서는 headerList를 따로 매개변수로 받아 헤더데이터에 집어넣었는데,
반환 Class의 fileName을 집어넣는 방법도 고려해볼만 하다.
ex)
List<String> headerList = Arrays.stream(clazz.getDeclaredFields())
.map(Field::getName)
.collect(Collectors.toList());비동기 처리
ExcelService .java
@Async
public <E> CompletableFuture<Void> generateExcel(Consumer<ResultHandler<E>> queryExecutor,
Class<E> clazz,
List<String> headerList) throws IOException {
return CompletableFuture.runAsync(() -> {
...
});
}반환형을 CompletableFuture<Void>로 바꿔준다
종합
@Async
public <E> CompletableFuture<Void> generateExcel(Consumer<ResultHandler<E>> queryExecutor,
Class<E> clazz,
List<String> headerList) throws IOException {
return CompletableFuture.runAsync(() -> {
try {
// Workbook 객체 생성(buffer 개수 : 1024)
Workbook workbook = new SXSSFWorkbook(1024);
AtomicReference<Sheet> sheet = new AtomicReference<>(null);
AtomicInteger currentSheet = new AtomicInteger(0);
AtomicInteger rowIndex = new AtomicInteger(0);
// handler 정의
ResultHandler<E> handler = context -> {
E data = context.getResultObject();
// 새 sheet 생성
if(rowIndex.get()/1048575 + 1 != currentSheet.get()) {
currentSheet.incrementAndGet();
sheet.set(workbook.createSheet("Sheet " + currentSheet.get()));
// header 데이터 세팅
Row headerRow = sheet.get().createRow(0);
for(int j = 0; j < headerList.size(); j++) {
headerRow.createCell(j).setCellValue(headerList.get(j));
}
}
// row 생성
Row row = sheet.get().createRow((rowIndex.getAndIncrement() % 1048575) + 1);
Field[] fields = clazz.getDeclaredFields();
// row에 데이터 삽입
for (int cellIndex = 0; cellIndex < fields.length; cellIndex++) {
fields[cellIndex].setAccessible(true);
try {
Object value = fields[cellIndex].get(data);
Cell cell = row.createCell(cellIndex);
if (value instanceof Number) {
cell.setCellValue(((Number) value).doubleValue());
} else {
cell.setCellValue(value != null ? value.toString() : "");
}
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
};
// 쿼리 실행
queryExecutor.accept(handler::handleResult);
// workbook 객체를 byteArray로 변환
ByteArrayOutputStream baos = new ByteArrayOutputStream();
try {
workbook.write(baos);
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
// byteArray를 mutipartFile로 변환(선택)
MultipartFile excelMutlpartFile = new CustomMultipartFile(baos.toByteArray(),
"excelName.xlsx",
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
// 객체 업로드 서비스 실행
fileIOService.upload(excelMultipartFile);
} catch (IOException e) {
e.printStackTrace();
}
});
}사용
excelDownloadService.generateExcel(
handler -> sampleMapper.findExcelData(handler),
SampleExcelDto.class
);위에서 생성했던 fineExcelData 메서드와 반환형을 매개변수로 전달하고 파일 업로드를 진행해주면 된다.
다운로드
@GetMapping(value = "/downloadStream")
public ResponseEntity<StreamingResponseBody> downloadFile(@RequestParam String downloadFileName) throws IOException {
InputStream inputStream = fileIOService.downloadStream(downloadFileName);
StreamingResponseBody responseBody = outputStream -> {
byte[] buffer = new byte[8192]; // 8KB 버퍼
int bytesRead;
while ((bytesRead = inputStream.read(buffer)) != -1) {
outputStream.write(buffer, 0, bytesRead);
outputStream.flush(); // 즉시 클라이언트로 전송
}
};
return ResponseEntity.ok()
.contentType(MediaType.APPLICATION_OCTET_STREAM)
.header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=\"" + downloadFileName + "\"")
.body(responseBody);
}대용량 파일의 경우 다운로드 중 타임아웃이 발생할 수 있기 때문에 stream 방식으로 전송하는 것을 고려해준다.
추가 고려사항
서버의 메모리를 사용하고 싶지 않았기 때문에 엑셀 파일 생성후 지정된 저장소에 비동기로 저장하고,
저장된 파일정보의 key를 담아 클라이언트에 반환한다.
이 때 저장이 완료되었는 지 상태를 체크할 수 있는 로직을 만들어야 하는데 웹소켓을 사용할 수도 있으나
서버 부하를 최소화 하고자 주기적으로 서버에 통신해서 플래그를 받는 방법을 사용했다.
플래그를 통해 파일 업로드가 완료된 것을 확인하면 그 때 InputStream을 통해 다운로드 하는 방법을 사용하면 된다.
결론
데이터 양이 많아지면서 메모리, 시간 등 고려해야될 것이 많아져 설계적 요소가 들어가게 됐다.
현재는 업로드가 완료되었는지를 단순히 true, false로만 체크하고 있어
두가지 상태값만 클라이언트에서 보여지게 되지만,
웹소켓, Redis, Kafka등을 접목시키면 상태관리를 더욱 상세하게 할 수 있어 더 나은 UI를 제공할 수 수 있으나
이것 또한 자원이니.. 가용할 수 있는 자원을 고려하여 추가 설계를 할 수 있으면 될듯 하다.
비고
너무 많은 양의 DB통신은 더미데이터 생성도 귀찮고 서버에 무리를 줄 수 있기에
DB통신을 생략하고 엑셀파일을 생성하기 위해 만들었던 로직이다.
private void getDummyExcel(Workbook workbook, AtomicReference<Sheet> sheet, AtomicInteger currentSheet) {
List<String> headerListSample = Arrays.asList("header1", "header2", "header3", "header4", "header5", "header6", "header7", "header8");
for(int i = 0; i < 10000000; i++) {
if((i / MAX_ROW_COUNT) + 1 != currentSheet.get()) {
currentSheet.incrementAndGet();
sheet.set(workbook.createSheet("Sheet" + currentSheet.get()));
setHeaderRow(headerListSample, sheet);
}
Row row = sheet.get().createRow((i % MAX_ROW_COUNT) + 1);
row.createCell(0).setCellValue("test");
row.createCell(1).setCellValue("i");
row.createCell(2).setCellValue("tewqerqwerasdrst" + i);
row.createCell(3).setCellValue("tesadrqwerasdfqtergasdfst" + i);
row.createCell(4).setCellValue("teqwetasdgzxfhdwtqwerasdrst" + i);
row.createCell(5).setCellValue("teasdfasdfasdst" + i);
row.createCell(6).setCellValue("tefasdfasdfasdfasdfasdfst" + i + "asdfasdfasdfasdf " + i);
row.createCell(7).setCellValue("teasdfasdfasdfasdfasdfasdfasdfst" + i + " " + i + " asdfasdfasdfase" + i);
}
}
