Import from local CSV file

[cyrixsimon]

Description

Currently, the driver provides an ImportBuilder and ExportBuilder API to stream data between Exasol and external sources or sinks.
However, the ExportBuilder can only use an Exasol query or table as its source.
Therefore, it cannot be used to import data from a local CSV file located on the client machine.

Problem

When attempting to import local CSV files using the ImportBuilder, the driver internally opens multiple HTTP endpoints (writers) that Exasol connects to.

Each writer must receive data, otherwise the import session can hang or fail with an error such as:

Exasol error 42636: ETL-5105: Following error occured while reading data from external connection [
https://27.1.0.16:43780/import_00005.csv failed after 0 bytes. [Failed to connect to 27.1.0.16 port 43780 after 0 ms: Couldn't connect to server]

This behavior makes it impractical to import small or local CSV files, especially when the number of rows is smaller than the number of writers created by the import process.

Importing local CSV files into Exasol is a very common use case, especially for handling ETL tasks, testing data pipelines, or loading sample data during development.
It would be highly valuable if the library could be extended to support this scenario natively, providing a more reliable way to bring local data into Exasol directly.

[bobozaur]

Hey, thanks for raising your concerns!

This is unfortunately a lengthier topic.

When attempting to import local CSV files using the ImportBuilder, the driver internally opens multiple HTTP endpoints (writers) that Exasol connects to.

That's the interface Exasol provides. What happens is that the driver connects to all the database nodes and serves an HTTP endpoint through that TCP connection. The database then opens some internal proxies to provide local addresses to use in the query generation.

Each writer must receive data, otherwise the import session can hang or fail with an error such as:

Exasol error 42636: ETL-5105: Following error occured while reading data from external connection [
https://27.1.0.16:43780/import_00005.csv failed after 0 bytes. [Failed to connect to 27.1.0.16 port 43780 after 0 ms: Couldn't connect to server]

I believe this limitation is documented. The overall idea is that the HTTP servers the driver creates run concurrently and when one of them closes without sending data, Exasol will retry the HTTP request. But by then, the internal proxy is already closed so the request fails and the error you see gets thrown. Unfortunately there does not seem to be a way to tell Exasol to keep the proxy alive for multiple requests.

This behavior makes it impractical to import small or local CSV files, especially when the number of rows is smaller than the number of writers created by the import process.

Understandable, but I'd also argue that if the number of rows from a file is smaller than the number of writers, perhaps the overhead of the whole import process is not worth it. A simple INSERT would be much faster.

Importing local CSV files into Exasol is a very common use case, especially for handling ETL tasks, testing data pipelines, or loading sample data during development. It would be highly valuable if the library could be extended to support this scenario natively, providing a more reliable way to bring local data into Exasol directly.

Also understandable. However, the goal was to make the interface as generic as possible, which is why the AsyncRead and AsyncWrite workers are provided, enabling piping data from/to any data source. Providing common patterns like reading from / writing to a file runs into the unfortunately divided and not yet generic async ecosystem in Rust.

Primarly, an async File interface would be required here and while runtimes like tokio provide them this would mean locking into that dependency. Moreover, the readers/writers can currently be handled in any way the user wants. They can all run concurrently on the same thread or can be sent to other threads for parallel processing. While there could be ways to address this they would still be limiting and I'd rather encourage users to handle this however it works best for their usecase.

I was also surprised about the brittleness of the IMPORT operations and raised exasol/websocket-api#33 a while ago. I strongly suggest reading through the discussion there if you're interested in finding out more, but overall Exasol does not plan to change this.

Leaving that aside though, I recently got an idea of how to make imports more resilient but it will require testing. Essentially, Exasol seems to really only complain when the first HTTP request is empty. But if at least one writer sends some data (or rather, the first writer) then Exasol seemed happy that the others would not. If this really is the case, then decoupling the underlying HTTP servers from the writers could make this more resilient so that data gets piped to HTTP responses in the order of HTTP requests that Exasol makes because these seem to happen sequentially.

I've had this idea for a while but I'll only get to it once I wrap up #35, which hopefully won't be long from now. The ground work is done but I've unfortunately been struggling with my professional life so I wasn't able to give it the attention it deserves. I was also waiting on a new sqlx release which happened recently so I plan on getting my PR done in the upcoming weeks. After that I can test my idea with decoupling the servers and workers and see if that makes imports better and only then create a new release.