Совсем недавно передо мной стояла задача написать сервис, который будет заниматься всего одной, но очень емкой задачей — сбором большого объема данных из базы, агрегированием и заполнением всего этого в Excel по определенному шаблону.
В процессе поиска лучшего решения было протестировано несколько подходов и решены проблемы, связанные с памятью и производительностью.
В этой статье я хочу поделиться с вами основными моментами и этапами реализации данной задачи.
1. Постановка задачи
В связи с тем, что я не могу раскрыть подробности технических характеристик, сущностей, алгоритмов сбора данных и т.п.пришлось придумать что-то подобное: Итак, представим, что у нас есть онлайн-чат с высокой активностью, и заказчик хочет загрузить в Excel все сообщения, обогащенные данными пользователя, за определенную дату.
За день может накапливаться более 1 миллиона сообщений.
У нас есть 3 таблицы:
- Пользователь.
Хранит имя пользователя и его определенный рейтинг (независимо от того, откуда он взят и как рассчитывается)
- Сообщение.
Хранит данные сообщения — имя пользователя, дату и время, текст сообщения.
- Задача.
Задача формирования отчета, созданная заказчиком.
Хранит идентификатор, статус задачи (выполнена или нет) и два параметра: дату начала сообщения и дату окончания сообщения.
В Excel Заказчик хочет видеть 4 столбца: 1) message_date. 2) имя.
3) рейтинг.
4) текст. Ограничение на количество строк — 1 миллион.
Эти данные необходимо заполнить в Excel, а дальше заказчик будет работать с этими данными в Excel самостоятельно.
2. Проблема ясна, приступим к поиску решения
Поскольку все в компании стараются придерживаться единого стиля в разработке приложений, мне пришлось начать с самого распространенного подхода, который используется во всех остальных микросервисах — Spring + Hibernate для запуска приложения и работы с базой данных.В качестве базы данных используется Oracle, хотя использование любой другой СУБД будет более-менее похоже.
Для запуска приложения нам нужна зависимость весна-загрузка-стартер-данные-JPA , который объединяет сразу Spring Data, Hibernate и JPA, всё это нам понадобится для удобства работы с базой данных и нашими сущностями.
Для тестирования добавим весна-загрузка-стартер-тест<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> <version>2.4.5</version> </dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>А еще нам нужен сам драйвер для подключения к базе данных
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc10</artifactId>
<version>19.10.0.0</version>
</dependency>Далее нам нужно добавить некоторые настройки конфигурации.
У нас будет один метод, который будет заходить в таблицу TASK, искать задачу в статусе «CREATED» и, если такая задача существует, то начинать формировать отчет с параметрами.
Предполагается, что генерация отчета может занять продолжительное время, поэтому наш метод будет запускаться по расписанию в два потока асинхронными процессами.
Мы также укажем наш репозиторий для Spring Data для поиска соответствующих сущностей.
Класс конфигурации будет выглядеть так: package com.report.generator.demo.config;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.scheduling.TaskScheduler;
import org.springframework.scheduling.annotation.EnableAsync;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.concurrent.ThreadPoolTaskScheduler;
@Configuration
@EnableScheduling
@EnableAsync
@EnableJpaRepositories(basePackages = "com.report.generator.demo.repository")
@PropertySource({"classpath:application.properties"})
@ConditionalOnProperty(
value = "app.scheduling.enable", havingValue = "true", matchIfMissing = true
)
public class DemoConfig {
private static final int CORE_POOL_SIZE = 2;
@Bean(name = "taskScheduler")
public TaskScheduler getTaskScheduler() {
ThreadPoolTaskScheduler scheduler = new ThreadPoolTaskScheduler();
scheduler.setPoolSize(CORE_POOL_SIZE);
scheduler.initialize();
return scheduler;
}
}
Класс формирования отчета содержит @Запланированное метод, который ищет Задачу раз в минуту и, если найден, начинает формировать отчет с параметрами из этой задачи.
@Async("taskScheduler")
@Scheduled(fixedDelay = 60000)
public void scheduledTask() {
log.info("scheduledTask is started");
Task task = getTask();
if (Objects.isNull(task)) {
log.info("task not found");
return;
}
log.info("task found");
generate(task);
}
В стартовом классе приложения нет ничего примечательного, весь код можно посмотреть по адресу GitHub .
3. Выборка данных из базы данных
Т.к.
компания везде использует Hibernate и решила его использовать.
Добавлена сущность MessageData с необходимым набором полей (id, имя, рейтинг, messageDate, тест).
Первой попыткой выбрать нужные данные была попытка сбросить всё в List используя простой метод: List<Message> findAllByMessageDateBetween(Instant dateFrom, Instant dateTo);
А затем в цикле создайте объекты MessageData и дополните их недостающими данными.
Было очевидно, что такой подход в корне неверен и выгрузка сразу миллиона записей в Список была как минимум медленной.
Но ради экспериментов и замеров скорости работы мне захотелось проверить и потом сравнить с другими вариантами.
Но в результате этот набор записей выгружался около 30 минут, после чего был получен Ошибка OutOfMemoryError и на этом эксперимент закончился.
Даже если бы пользователь задал в параметрах узкую область действия и мы смогли бы выбрать все в один Список, то мы столкнулись бы со следующей проблемой — чтобы заполнить все нужные столбцы нам нужно было бы собрать идентификаторы пользователей, вернуться назад. в базу данных, получить их имена и рейтинги и заполнить полными данными.
Сложность такого алгоритма значительно возросла.
Было понятно, что отбор нужно делать по частям и все возможные действия с данными перенести на сторону базы данных.
Чтобы не выбирать все сразу и не блокировать велосипеды, было решено использовать Прокручиваемые результаты .
Это позволяет нам получить ссылку на курсор и перебирать результаты с определенным шагом.
Далее нам пришлось переписать запрос так, чтобы он сразу возвращал все необходимые данные после всех джойнов, объединений, группировок и т.д. Следующий вопрос — где хранить сам текст запроса.
Это была непростая ситуация, потому что.
на самом деле количество таблиц, участвовавших в запросе, было около десяти, количество объединений и всевозможных группировок было огромным, в результате чего текст запроса в итоге оказался 200 + строки после проверки разными коллегами и одобрения самим руководителем.
Хранить такой запрос в java-коде мне не хотелось, плюс некоторые константы в условиях были жестко запрограммированы и показывать их в общем репозитории было бы неправильно.
Для решения всех этих вопросов мне на помощь пришла идея использовать вид .
Туда идеально помещается весь текст запроса, плюс на выходе мы получаем готовую сущность, с которой hibernate может работать как с обычной сущностью.
Поначалу всё выглядело нормально, запрос на выбор 1 миллиона таких строк был выполнен за разумные 10 минут. или так.
Немного больше, чем хотелось бы, но заказчик остался доволен.
Однако в ходе тестирования был обнаружен серьезный недостаток такого подхода — когда мы выбираем 1 миллион записей, выполнение запроса занимает 10 минут, но когда мы хотим более короткий отчет и указываем в параметрах более узкие границы дат — наш запрос также занимает 10 минут. минут, но в результате мы можем получить хоть 1 запись, хоть миллион.
Дело в том, что внутри запроса вид Вы не можете передавать параметры, мы можем только выполнить статический запрос и применить параметры к результату.
Поэтому, сколько бы строк ни было в результате, в первую очередь будет выбрано все, что найдено в базе данных, и только потом будут применены параметры.
Клиенту было все равно; он также был удовлетворен тем, что отчет в одну строку будет создан почти за то же время, что и отчет в 1 миллион строк.
Однако это излишне нагружало базу данных и от этого варианта было решено отказаться.
Оставался только один вариант, который нас устраивал — функция, хранящаяся в базе данных.
Ему можно передавать параметры, он может возвращать ссылку на курсор, а его результат можно удобно сопоставить с нашей сущностью.
Таким образом, была описана функция, которая принимала на вход несколько параметров и возвращала sys_refcursor , весь скрипт в реальности занял около 300 строк, но в упрощенном варианте здесь он выглядит так: create function message_ref(
date_from timestamp,
date_to timestamp
) return sys_refcursor as
ret_cursor sys_refcursor;
begin
open ret_cursor for
select m.id,
u.name,
u.rating,
m.message_date,
m.text
from message m
left join users u on m.user_id = u.id
where m.message_date between date_from and date_to;
return ret_cursor;
end message_ref;
Как теперь его использовать? Отлично подходит для этого @NamedNativeQuery .
Запрос на вызов функции выглядит так: "{ ? = call message_ref(?, ?) }", отзывной = true дает понять, что запрос является вызовом функции, режим кэширования = CacheModeType.IGNORE для указания не использовать кэш, так как скорость работы для нас не так критична, как потребляемая память, и в итоге класс результата = MessageData.class для сопоставления результата с нашей сущностью.
Класс MessageData выглядит следующим образом: package com.report.generator.demo.repository.entity;
import lombok.Data;
import org.hibernate.annotations.CacheModeType;
import org.hibernate.annotations.NamedNativeQuery;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import java.io.Serializable;
import java.time.Instant;
import static com.report.generator.demo.repository.entity.MessageData.MESSAGE_REF_QUERY_NAME;
@Data
@Entity
@NamedNativeQuery(
name = MESSAGE_REF_QUERY_NAME,
query = "{ ? = call message_ref(?, ?) }",
callable = true,
cacheMode = CacheModeType.IGNORE,
resultClass = MessageData.class
)
public class MessageData implements Serializable {
public static final String MESSAGE_REF_QUERY_NAME = "MessageData.callMessageRef";
private static final long serialVersionUID = -6780765638993961105L;
@Id
private long id;
@Column
private String name;
@Column
private int rating;
@Column(name = "MESSAGE_DATE")
private Instant messageDate;
@Column
private String text;
}
Чтобы не использовать кеш, было решено выполнить запрос в Сеанс без гражданства .
Однако есть важная особенность: если вы попытаетесь вызвать NameQuery, спящий режим выкинет при попытке установить CacheMode. УнподдерживаемоеОператионИсключение .
Чтобы этого избежать нужно установить две подсказки: query.setHint(JPA_SHARED_CACHE_STORE_MODE, null);
query.setHint(JPA_SHARED_CACHE_RETRIEVE_MODE, null);
В результате наш метод генерации выглядит так: @Transactional
void generate(Task task) {
log.info("generating report is started");
try (
StatelessSession statelessSession = sessionFactory.openStatelessSession()
) {
ReportExcelStreamWriter writer = new ReportExcelStreamWriter();
Query<MessageData> query = statelessSession.createNamedQuery(MESSAGE_REF_QUERY_NAME, MessageData.class);
query.setParameter(1, task.getDateFrom());
query.setParameter(2, task.getDateTo());
query.setHint(JPA_SHARED_CACHE_STORE_MODE, null);
query.setHint(JPA_SHARED_CACHE_RETRIEVE_MODE, null);
ScrollableResults results = query.scroll(ScrollMode.FORWARD_ONLY);
int index = 0;
while (results.next()) {
index++;
writer.createRow(index, (MessageData) results.get(0));
if (index % 100000 == 0) {
log.info("progress {} rows", index);
}
}
writer.writeWorkbook();
task.setStatus(DONE.toString());
log.info("task {} complete", task);
} catch (Exception e) {
task.setStatus(FAIL.toString());
e.printStackTrace();
log.error("an error occurred with message {}.
While executing the task {}", e.getMessage(), task);
} finally {
taskRepository.save(task);
}
}
4. Запись данных в Excel
На этом этапе был решен вопрос с получением данных из базы данных и возник следующий вопрос — как теперь все это написать в Excel, чтобы это было быстро и не требовало памяти.Первая попытка была самой очевидной — использование библиотеки org.apache.poi .
Здесь все просто: подключаем зависимость <dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.0.0</version>
</dependency>
Мы создаем XSSFWorkbook Дальше XSSFЛист , из этого уже идет ряд и все такое.
Ничего особенного, пример кода ниже: package com.report.generator.demo.service;
import com.report.generator.demo.repository.entity.MessageData;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
import java.time.Instant;
public class ReportExcelWriter {
private final XSSFWorkbook wb;
private final XSSFSheet sheet;
public ReportExcelWriter() {
this.wb = new XSSFWorkbook();
this.sheet = wb.createSheet();
createTitle();
}
public void createRow(int index, MessageData data) {
XSSFRow row = sheet.createRow(index);
setCellValue(row.createCell(0), data.getMessageDate());
setCellValue(row.createCell(1), data.getName());
setCellValue(row.createCell(2), data.getRating());
setCellValue(row.createCell(3), data.getText());
}
public void writeWorkbook() throws IOException {
FileOutputStream fileOut = new FileOutputStream(Instant.now().
getEpochSecond() + ".
xlsx");
wb.write(fileOut);
fileOut.close();
}
private void createTitle() {
XSSFRow rowTitle = sheet.createRow(0);
setCellValue(rowTitle.createCell(0), "Date");
setCellValue(rowTitle.createCell(1), "Name");
setCellValue(rowTitle.createCell(2), "Rating");
setCellValue(rowTitle.createCell(3), "Text");
}
private void setCellValue(XSSFCell cell, String value) {
cell.setCellValue(value);
}
private void setCellValue(XSSFCell cell, long value) {
cell.setCellValue(value);
}
private void setCellValue(XSSFCell cell, Instant value) {
cell.setCellValue(value.toString());
}
}
Но такой подход оказался не очень оптимальным.
Чтобы выбрать 1 миллион строк из базы данных и записать их в Excel, потребовалось примерно 3 минуты.
И в конечном итоге привело к Ошибка OutOfMemoryError .
Вот пример: 
И когда я запустил его на терминале с 2 ГБ выделенной оперативной памяти, он вылетел с Ошибка OutOfMemoryError примерно 30% прогресс.
Загружать весь миллион строк в память Excel было такой же плохой идеей, как выгружать весь запрос в список.
Очевидно, здесь нужно было использовать какой-то поток, но Google на тот момент не дал мне хорошего примера.
Была попытка написать свой собственный I/O Stream для работы с Excel, но мысль о том, что я пишу велосипед, не давала мне покоя.
В итоге я начал изучать библиотеку org.apache.poi присмотрелся и оказалось, что там уже была посылка потоковая передача .
Этот пакет уже содержит весь необходимый набор классов для работы с большими объемами данных в Excel. Оставалось только заменить все ключевые классы на аналогичные из пакета.
потоковая передача и это все: package com.report.generator.demo.service;
import com.report.generator.demo.repository.entity.MessageData;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
import java.time.Instant;
public class ReportExcelStreamWriter {
private final SXSSFWorkbook wb;
private final SXSSFSheet sheet;
public ReportExcelStreamWriter() {
this.wb = new SXSSFWorkbook();
this.sheet = wb.createSheet();
createTitle();
}
public void createRow(int index, MessageData data) {
SXSSFRow row = sheet.createRow(index);
setCellValue(row.createCell(0), data.getMessageDate());
setCellValue(row.createCell(1), data.getName());
setCellValue(row.createCell(2), data.getRating());
setCellValue(row.createCell(3), data.getText());
}
public void writeWorkbook() throws IOException {
FileOutputStream fileOut = new FileOutputStream(Instant.now().
getEpochSecond() + ".
xlsx");
wb.write(fileOut);
fileOut.close();
}
private void createTitle() {
SXSSFRow rowTitle = sheet.createRow(0);
setCellValue(rowTitle.createCell(0), "Date");
setCellValue(rowTitle.createCell(1), "Name");
setCellValue(rowTitle.createCell(2), "Rating");
setCellValue(rowTitle.createCell(3), "Text");
}
private void setCellValue(SXSSFCell cell, String value) {
cell.setCellValue(value);
}
private void setCellValue(SXSSFCell cell, long value) {
cell.setCellValue(value);
}
private void setCellValue(SXSSFCell cell, Instant value) {
cell.setCellValue(value.toString());
}
}
Теперь сравним скорость обработки данных этой библиотекой: 
Вся обработка заняла полминуты и, что самое главное, никакой Ошибка OutOfMemoryError .
5. Резюме
В результате удалось добиться максимальной производительности, используя хранимую функцию StatelessSession, ScrollableResults и используя библиотеку org.apache.poi из пакета.Если очень захотеть, то можно еще больше улучшить производительность, если писать все на чистом jdbc, возможно есть другие варианты как, что и где можно улучшить.
Буду рад услышать комментарии более опытных специалистов в этом вопросе.
В данном примере ограничение в 1 миллион строк не учитывается, так как это простая формальность и не очень важна для примера.
Для наполнения базы тестовыми данными был добавлен тестовый класс DemoApplicationTests. Весь код можно просмотреть в репозитории по адресу GitHub .
Теги: #java #sql #Excel
-
Цицерон, Марк Туллий
19 Oct, 24 -
Веб-Авторизация: Что Это Может Быть?
19 Oct, 24 -
Ajax.net Против Jquery.ajax
19 Oct, 24
