[Java] JDBC Adapter for Apache Arrow

[asfimport]

At a high level the JDBC Adapter will allow upstream apps to query RDBMS data over JDBC and get the JDBC objects converted to Arrow objects/structures. The upstream utility can then work with Arrow objects/structures with usual performance benefits. The utility will be very much similar to C++ implementation of "Convert a vector of row-wise data into an Arrow table" as described here - https://arrow.apache.org/docs/cpp/md_tutorials_row_wise_conversion.html

The utility will read data from RDBMS and covert the data into Arrow objects/structures. So from that perspective this will Read data from RDBMS, If the utility can push Arrow objects to RDBMS is something need to be discussed and will be out of scope for this utility for now.

Reporter: Atul Dambalkar
Assignee: Atul Dambalkar

PRs and other links:

• GitHub Pull Request #1759

_{Note: This issue was originally created as ARROW-1780. Please see the migration documentation for further details.}

[asfimport]

Atul Dambalkar:
Comments from Uwe Korn on Slack channel - 

My main plan was to make JDBC drivers accessible very fast from Python / Pandas programs. Currently, you either have the option for most DBs to either use ODBC/python-native drivers that are quite often awful or use JDBC ones but have a high cost of serialization between the JVM and the Python objects. By using Arrow, we should be able to use the good JDBC drivers from Python without the normal serialization overhead.

We’re looking at SQL-engines that work on distributed filesystem in general at the moment (Apache Drill and Presto are two best candidates at the moment) and the common pattern is that they have good JDBC drivers but the other connectors are not so well maintained or really slow. Currently, Presto is the one of biggest interest for me.

For me it seems that having a JDBC<->Arrow adapter already yields a significant performance impact in comparison to the current situation. And it will also give the speedup independent of the underlying DB.

 

[asfimport]

Atul Dambalkar:
I have put together some very basic interface for the JDBC Adapter - so far by forking Arrow (https://github.com/atuldambalkar/arrow/tree/master/java/adapter/jdbc). I had a brief discussion with Uwe earlier on this on Slack, so wanted to get some more views on this and also not to redo or overstep. At this time, I have one API in the adapter which can return Arrow Vector objects after executing SQL query on the given JDBC connection object - VectorSchemaRoot sqlToArrow(Connection connection, String query).

One more possible interface could be to fetch a certain number of records from all the tables from the SQL database and build Arrow objects for that. The API can of-course be implemented lazily and only when the data for a particular table is requested.

[asfimport]

Atul Dambalkar:
Comment from Jacques Nadeau on Slack channel -

Nice start @atul_dambalkar. It would be good to add the ability to set the amount to return per call as opposed to trying to deplete the whole dataset in one call.

 

[asfimport]

Atul Dambalkar:
Based on above comments, I will update the API with necessary parameters. It will be more or less like pagination.

[asfimport]

Atul Dambalkar:
Added the necessary components - #1759

Code is under - https://github.com/apache/arrow/tree/master/java/adapter/jdbc