'Java Stream a Large SQL Query into API CSV File

I am writing a Service that obtains data from large sql query in database (over 100,000 records) and streams into an API CSV File. Is there any java library function that does it, or any way to make the code below more efficient? Currently using Java 8 in Spring boot environment.

Code is below with sql repository method, and service for csv. Preferably trying to write to csv file, while data is being fetched from sql concurrently as query make take 2-3 min for user .

We are using Snowflake DB.

public class ProductService {

    private final ProductRepository productRepository;
    private final ExecutorService executorService;

    public ProductService(ProductRepository productRepository) {
        this.productRepository = productRepository;
        this.executorService = Executors.newFixedThreadPool(20);
    }

    public InputStream getproductExportFile(productExportFilters filters) throws IOException {
        PipedInputStream is = new PipedInputStream();
        PipedOutputStream os = new PipedOutputStream(is);
        executorService.execute(() -> {
            try {
                Stream<productExport> productStream = productRepository.getproductExportStream(filters);

                Field[] fields = Stream.of(productExport.class.getDeclaredFields())
                        .peek(f -> f.setAccessible(true))
                        .toArray(Field[]::new);
                String[] headers = Stream.of(fields)
                        .map(Field::getName).toArray(String[]::new);
                CSVFormat csvFormat = CSVFormat.DEFAULT.builder()
                        .setHeader(headers)
                        .build();

                OutputStreamWriter outputStreamWriter = new OutputStreamWriter(os);
                CSVPrinter csvPrinter = new CSVPrinter(outputStreamWriter, csvFormat);
                productStream.forEach(productExport -> writeproductExportToCsv(productExport, csvPrinter, fields));

                outputStreamWriter.close();
                csvPrinter.close();
            } catch (Exception e) {
                logger.warn("Unable to complete writing to csv stream.", e);
            } finally {
                try {
                    os.close();
                } catch (IOException ignored) { }
            }
        });
        return is;
    }

    private void writeProductExportToCsv(productExport productExport, CSVPrinter csvPrinter, Field[] fields) {
        Object[] values = Stream.of(fields).
                map(f -> {
                    try {
                        return f.get(productExport);
                    } catch (IllegalAccessException e) {
                        return null;
                    }
                })
                .toArray();
        try {
            csvPrinter.printRecord(values);
            csvPrinter.flush();
        } catch (IOException e) {
            logger.warn("Unable to write record to file.", e);
        }
    }

public Stream<PatientExport> getProductExportStream(ProductExportFilters filters) {
    MapSqlParameterSource parameterSource = new MapSqlParameterSource();
    parameterSource.addValue("customerId", filters.getCustomerId().toString());
    parameterSource.addValue("practiceId", filters.getPracticeId().toString());

    StringBuilder sqlQuery = new StringBuilder("SELECT * FROM  dbo.Product ");
    sqlQuery.append("\nWHERE CUSTOMERID = :customerId\n" +
                    "AND PRACTICEID = :practiceId\n"
    );
javaspring-bootsnowflake-cloud-data-platform


Solution 1:[1]

There is product which we bought some time ago for our company, we got even the source code back then. https://northconcepts.com/ We were also evaluating Apache Camel which had similar support but it didnt suite our goal. If you really need speed you should go to lowest level possible - pure JDBC and as simple as possible csv writer.

Nortconcepts library itself provides capability to read from jdbc and write to CSV on lower level. We found few tweaks which have sped up the transmission and processing. With single thread we are able to stream 100 000 records (with 400 columns) within 1-2 minutes.

Solution 2:[2]

Given that you didn't specify which database you use I can give you only generic answers.

In general code like this is network limited, as JDBC resultset is usually transferred in "only n rows" packages, and when you exhaust one, only then database triggers fetching of next packet. This property is often called fetch-size, and you should greatly increase it. By default settings, most of databases transfer 10-100 rows in one fetch. In spring you can use setFetchSize property. Some benchmarks here.

There are other similar low level stuff which you could do. For example, Oracle jdbc driver has "InsensitiveResultSetBufferSize" - how big in bytes is a buffer which holds result set. But dose things tend to be database specific.

Thus being said, the best way to really increase speed of your transfer is to actually launch multiple queries. Divide your data on some column value, and than launch multiple parallel queries. Essentially, if you can design data to support parallel queries working on easily distinguished subsets, bottleneck can be transferred to a network or CPU throughput.

For example one of your columns might be 'timestamp'. Instead having one query to fetch all rows, fetch multiple subset of rows with query like this:

SELECT * FROM  dbo.Product 
 WHERE CUSTOMERID = :customerId
   AND PRACTICEID = :practiceId
   AND :lowerLimit <= timestamp AND timestamp < :upperLimit

Launch this query in parallel with different timestamp ranges. Aggregate result of those subqueries in shared ConcurrentLinkedQueue and build CSV there.

With similar approach I regularly read 100000 rows/sec on 80 column table from Oracle DB. That is 40-60 MB/sec sustained transfer rate from a table which is not even locked.

Solution 3:[3]

Streaming allows you to transfer the data, little by little, without having to load it all into the server’s memory. You can do your operations by using the extractData() method in ResultSetExtractor. You can find javadoc about ResultSetExtractor here.

You can view an example using ResultSetExtractor here.

You can also easily create your JPA queries as ResultSet using JdbcTemplate. You can take a look at an example here. to use ResultSetExtractor.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Jozef Drop?o
Solution 2
Solution 3 fatih

Related Questions