You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@arrow.apache.org by GitBox <gi...@apache.org> on 2022/07/13 20:45:58 UTC
[GitHub] [arrow-cookbook] davisusanibar opened a new pull request, #229: [Java] Adding Arrow Java JDBC adapter examples
davisusanibar opened a new pull request, #229:
URL: https://github.com/apache/arrow-cookbook/pull/229
https://github.com/apache/arrow-cookbook/issues/228
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-cookbook] lidavidm commented on pull request #229: [Java] Adding Arrow Java JDBC adapter examples
Posted by GitBox <gi...@apache.org>.
lidavidm commented on PR #229:
URL: https://github.com/apache/arrow-cookbook/pull/229#issuecomment-1205379147
This can be updated for 9.0.0 now I think
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-cookbook] lidavidm commented on a diff in pull request #229: [Java] Adding Arrow Java JDBC adapter examples
Posted by GitBox <gi...@apache.org>.
lidavidm commented on code in PR #229:
URL: https://github.com/apache/arrow-cookbook/pull/229#discussion_r940148717
##########
java/ext/javadoctest.py:
##########
@@ -37,6 +37,9 @@ def compile(
"-q",
"dependency:build-classpath",
"-DincludeTypes=jar",
+ "-Dmaven.compiler.source="+os.environ.get('compiler_version', '8'),
+ "-Dmaven.compiler.target="+os.environ.get('compiler_version', '8'),
+ "-Darrow.version="+os.environ.get('arrow_version', '9.0.0'),
Review Comment:
```suggestion
f"-Dmaven.compiler.source={os.environ.get('compiler_version', '8')}",
f"-Dmaven.compiler.target={os.environ.get('compiler_version', '8')}",
f"-Darrow.version={os.environ.get('arrow_version', '9.0.0')}",
```
##########
java/source/jdbc.rst:
##########
@@ -0,0 +1,309 @@
+.. Licensed to the Apache Software Foundation (ASF) under one
+.. or more contributor license agreements. See the NOTICE file
+.. distributed with this work for additional information
+.. regarding copyright ownership. The ASF licenses this file
+.. to you under the Apache License, Version 2.0 (the
+.. "License"); you may not use this file except in compliance
+.. with the License. You may obtain a copy of the License at
+
+.. http://www.apache.org/licenses/LICENSE-2.0
+
+.. Unless required by applicable law or agreed to in writing,
+.. software distributed under the License is distributed on an
+.. "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+.. KIND, either express or implied. See the License for the
+.. specific language governing permissions and limitations
+.. under the License.
+
+.. _arrow-jdbc:
+
+====
+Jdbc
+====
Review Comment:
```suggestion
==================
Arrow JDBC Adapter
==================
```
##########
java/source/jdbc.rst:
##########
@@ -0,0 +1,309 @@
+.. Licensed to the Apache Software Foundation (ASF) under one
+.. or more contributor license agreements. See the NOTICE file
+.. distributed with this work for additional information
+.. regarding copyright ownership. The ASF licenses this file
+.. to you under the Apache License, Version 2.0 (the
+.. "License"); you may not use this file except in compliance
+.. with the License. You may obtain a copy of the License at
+
+.. http://www.apache.org/licenses/LICENSE-2.0
+
+.. Unless required by applicable law or agreed to in writing,
+.. software distributed under the License is distributed on an
+.. "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+.. KIND, either express or implied. See the License for the
+.. specific language governing permissions and limitations
+.. under the License.
+
+.. _arrow-jdbc:
+
+====
+Jdbc
+====
+
+The `Arrow Java JDBC module <https://arrow.apache.org/docs/java/jdbc.html>`_
+converts JDBC ResultSets into Arrow VectorSchemaRoots.
+
+.. contents::
+
+ResultSet to VectorSchemaRoot Conversion
+========================================
+
+The main class to help us to convert ResultSet to VectorSchemaRoot is
+`JdbcToArrow <https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrow.html>`_
+
+.. testcode::
+
+ import org.apache.arrow.adapter.jdbc.ArrowVectorIterator;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrow;
+ import org.apache.arrow.memory.BufferAllocator;
+ import org.apache.arrow.memory.RootAllocator;
+ import org.apache.arrow.vector.VectorSchemaRoot;
+ import org.apache.ibatis.jdbc.ScriptRunner;
+
+ import java.io.BufferedReader;
+ import java.io.FileReader;
+ import java.io.IOException;
+ import java.sql.Connection;
+ import java.sql.DriverManager;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+
+ try (BufferAllocator allocator = new RootAllocator();
+ Connection connection = DriverManager.getConnection(
+ "jdbc:h2:mem:h2-jdbc-adapter")) {
+ ScriptRunner runnerDDLDML = new ScriptRunner(connection);
+ runnerDDLDML.setLogWriter(null);
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/jdbc/h2-ddl.sql")));
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/jdbc/h2-dml.sql")));
+ try (ResultSet resultSet = connection.createStatement().executeQuery(
+ "SELECT int_field1, bool_field2, bigint_field5 FROM TABLE1");
+ ArrowVectorIterator iterator = JdbcToArrow.sqlToArrowVectorIterator(
+ resultSet, allocator)) {
+ while (iterator.hasNext()) {
+ try (VectorSchemaRoot root = iterator.next()) {
+ System.out.print(root.contentToTSVString());
+ }
+ }
+ }
+ } catch (SQLException | IOException e) {
+ e.printStackTrace();
+ }
+
+.. testoutput::
+
+ INT_FIELD1 BOOL_FIELD2 BIGINT_FIELD5
+ 101 true 1000000000300
+ 102 true 100000000030
+ 103 true 10000000003
+
+Configuring Array subtypes
+==========================
+
+JdbcToArrow accepts configuration through `JdbcToArrowConfig
+<https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrowConfig.html>`_.
+For example, the type of the elements of array columns can be specified by
+``setArraySubTypeByColumnNameMap``.
+
+.. testcode::
+
+ import org.apache.arrow.adapter.jdbc.ArrowVectorIterator;
+ import org.apache.arrow.adapter.jdbc.JdbcFieldInfo;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrow;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowConfig;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowConfigBuilder;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowUtils;
+ import org.apache.arrow.memory.BufferAllocator;
+ import org.apache.arrow.memory.RootAllocator;
+ import org.apache.arrow.vector.VectorSchemaRoot;
+ import org.apache.ibatis.jdbc.ScriptRunner;
+
+ import java.io.BufferedReader;
+ import java.io.FileReader;
+ import java.io.IOException;
+ import java.sql.Connection;
+ import java.sql.DriverManager;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+ import java.sql.Types;
+ import java.util.HashMap;
+
+ try (BufferAllocator allocator = new RootAllocator();
+ Connection connection = DriverManager.getConnection(
+ "jdbc:h2:mem:h2-jdbc-adapter")) {
+ ScriptRunner runnerDDLDML = new ScriptRunner(connection);
+ runnerDDLDML.setLogWriter(null);
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/jdbc/h2-ddl.sql")));
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/jdbc/h2-dml.sql")));
+ JdbcToArrowConfig config = new JdbcToArrowConfigBuilder(allocator,
+ JdbcToArrowUtils.getUtcCalendar())
+ .setArraySubTypeByColumnNameMap(
+ new HashMap<>() {{
+ put("LIST_FIELD19",
+ new JdbcFieldInfo(Types.INTEGER));
+ }}
+ )
+ .build();
+ try (ResultSet resultSet = connection.createStatement().executeQuery(
+ "SELECT int_field1, bool_field2, bigint_field5, char_field16, list_field19 FROM TABLE1");
+ ArrowVectorIterator iterator = JdbcToArrow.sqlToArrowVectorIterator(
+ resultSet, config)) {
+ while (iterator.hasNext()) {
+ try (VectorSchemaRoot root = iterator.next()) {
+ System.out.print(root.contentToTSVString());
+ }
+ }
+ }
+ } catch (SQLException | IOException e) {
+ e.printStackTrace();
+ }
+
+.. testoutput::
+
+ INT_FIELD1 BOOL_FIELD2 BIGINT_FIELD5 CHAR_FIELD16 LIST_FIELD19
+ 101 true 1000000000300 some char text [1,2,3]
+ 102 true 100000000030 some char text [1,2]
+ 103 true 10000000003 some char text [1]
+
+Configuring batch size
+======================
+
+The maximum rowCount to read each time is configured by default in 1024. This
+can be customized by setting values as needed by ``setTargetBatchSize``.
+
+.. testcode::
+
+ import org.apache.arrow.adapter.jdbc.ArrowVectorIterator;
+ import org.apache.arrow.adapter.jdbc.JdbcFieldInfo;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrow;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowConfig;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowConfigBuilder;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowUtils;
+ import org.apache.arrow.memory.BufferAllocator;
+ import org.apache.arrow.memory.RootAllocator;
+ import org.apache.arrow.vector.VectorSchemaRoot;
+ import org.apache.ibatis.jdbc.ScriptRunner;
+
+ import java.io.BufferedReader;
+ import java.io.FileReader;
+ import java.io.IOException;
+ import java.sql.Connection;
+ import java.sql.DriverManager;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+ import java.sql.Types;
+ import java.util.HashMap;
+
+ try (BufferAllocator allocator = new RootAllocator();
+ Connection connection = DriverManager.getConnection(
+ "jdbc:h2:mem:h2-jdbc-adapter")) {
+ ScriptRunner runnerDDLDML = new ScriptRunner(connection);
+ runnerDDLDML.setLogWriter(null);
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/jdbc/h2-ddl.sql")));
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/jdbc/h2-dml.sql")));
+ JdbcToArrowConfig config = new JdbcToArrowConfigBuilder(allocator,
+ JdbcToArrowUtils.getUtcCalendar())
+ .setTargetBatchSize(2)
+ .setArraySubTypeByColumnNameMap(
+ new HashMap<>() {{
+ put("LIST_FIELD19",
+ new JdbcFieldInfo(Types.INTEGER));
+ }}
+ )
+ .build();
+ try (ResultSet resultSet = connection.createStatement().executeQuery(
+ "SELECT int_field1, bool_field2, bigint_field5, char_field16, list_field19 FROM TABLE1");
+ ArrowVectorIterator iterator = JdbcToArrow.sqlToArrowVectorIterator(
+ resultSet, config)) {
+ while (iterator.hasNext()) {
+ try (VectorSchemaRoot root = iterator.next()) {
+ System.out.print(root.contentToTSVString());
+ }
+ }
+ }
+ } catch (SQLException | IOException e) {
+ e.printStackTrace();
+ }
+
+.. testoutput::
+
+ INT_FIELD1 BOOL_FIELD2 BIGINT_FIELD5 CHAR_FIELD16 LIST_FIELD19
+ 101 true 1000000000300 some char text [1,2,3]
+ 102 true 100000000030 some char text [1,2]
+ INT_FIELD1 BOOL_FIELD2 BIGINT_FIELD5 CHAR_FIELD16 LIST_FIELD19
+ 103 true 10000000003 some char text [1]
+
+Configuring numeric (decimal) precision and scale
+=================================================
+
+By default, the scale of any decimal values must exactly match the defined
+scale of the Arrow type of the column, or else an UnsupportedOperationException
+will be thrown with a message like ``BigDecimal scale must equal that in the Arrow
+vector``will be thrown during conversion.
+
+This can happen because Arrow infers the type from the ResultSet metadata, which
+is not accurate for all database drivers. The JDBC adapter lets you avoid this
+by either overriding the decimal scale, or by providing a RoundingMode via
+``setBigDecimalRoundingMode`` to convert values to the expected scale.
+
+In this example, we have a BigInt column. By default, the inferred scale
+is 0. We override the scale to 7 and then set a RoundingMode to convert
+values to the given scale.
Review Comment:
```suggestion
By default, the scale of any decimal values must exactly match the defined
scale of the Arrow type of the column, or else an UnsupportedOperationException
will be thrown with a message like ``BigDecimal scale must equal that in the Arrow
vector``.
This can happen because Arrow infers the type from the ResultSet metadata, which
is not accurate for all database drivers. The JDBC adapter lets you avoid this
by either overriding the decimal scale, or by providing a RoundingMode via
``setBigDecimalRoundingMode`` to convert values to the expected scale.
In this example, we have a BigInt column. By default, the inferred scale
is 0. We override the scale to 7 and then set a RoundingMode to convert
values to the given scale.
```
##########
java/source/demo/pom.xml:
##########
@@ -27,13 +25,11 @@
</extension>
</extensions>
</build>
-
<properties>
- <maven.compiler.source>8</maven.compiler.source>
- <maven.compiler.target>8</maven.compiler.target>
- <arrow.version>9.0.0</arrow.version>
+ <maven.compiler.source>injected_by_ci</maven.compiler.source>
Review Comment:
I would still much rather these have a default version so that it can be easily used locally.
##########
java/source/jdbc.rst:
##########
@@ -0,0 +1,309 @@
+.. Licensed to the Apache Software Foundation (ASF) under one
+.. or more contributor license agreements. See the NOTICE file
+.. distributed with this work for additional information
+.. regarding copyright ownership. The ASF licenses this file
+.. to you under the Apache License, Version 2.0 (the
+.. "License"); you may not use this file except in compliance
+.. with the License. You may obtain a copy of the License at
+
+.. http://www.apache.org/licenses/LICENSE-2.0
+
+.. Unless required by applicable law or agreed to in writing,
+.. software distributed under the License is distributed on an
+.. "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+.. KIND, either express or implied. See the License for the
+.. specific language governing permissions and limitations
+.. under the License.
+
+.. _arrow-jdbc:
+
+====
+Jdbc
+====
+
+The `Arrow Java JDBC module <https://arrow.apache.org/docs/java/jdbc.html>`_
+converts JDBC ResultSets into Arrow VectorSchemaRoots.
+
+.. contents::
+
+ResultSet to VectorSchemaRoot Conversion
+========================================
+
+The main class to help us to convert ResultSet to VectorSchemaRoot is
+`JdbcToArrow <https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrow.html>`_
+
+.. testcode::
+
+ import org.apache.arrow.adapter.jdbc.ArrowVectorIterator;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrow;
+ import org.apache.arrow.memory.BufferAllocator;
+ import org.apache.arrow.memory.RootAllocator;
+ import org.apache.arrow.vector.VectorSchemaRoot;
+ import org.apache.ibatis.jdbc.ScriptRunner;
+
+ import java.io.BufferedReader;
+ import java.io.FileReader;
+ import java.io.IOException;
+ import java.sql.Connection;
+ import java.sql.DriverManager;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+
+ try (BufferAllocator allocator = new RootAllocator();
+ Connection connection = DriverManager.getConnection(
+ "jdbc:h2:mem:h2-jdbc-adapter")) {
+ ScriptRunner runnerDDLDML = new ScriptRunner(connection);
+ runnerDDLDML.setLogWriter(null);
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/jdbc/h2-ddl.sql")));
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/jdbc/h2-dml.sql")));
+ try (ResultSet resultSet = connection.createStatement().executeQuery(
+ "SELECT int_field1, bool_field2, bigint_field5 FROM TABLE1");
+ ArrowVectorIterator iterator = JdbcToArrow.sqlToArrowVectorIterator(
+ resultSet, allocator)) {
+ while (iterator.hasNext()) {
+ try (VectorSchemaRoot root = iterator.next()) {
+ System.out.print(root.contentToTSVString());
+ }
+ }
+ }
+ } catch (SQLException | IOException e) {
+ e.printStackTrace();
+ }
+
+.. testoutput::
+
+ INT_FIELD1 BOOL_FIELD2 BIGINT_FIELD5
+ 101 true 1000000000300
+ 102 true 100000000030
+ 103 true 10000000003
+
+Configuring Array subtypes
+==========================
+
+JdbcToArrow accepts configuration through `JdbcToArrowConfig
+<https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrowConfig.html>`_.
+For example, the type of the elements of array columns can be specified by
+``setArraySubTypeByColumnNameMap``.
+
+.. testcode::
+
+ import org.apache.arrow.adapter.jdbc.ArrowVectorIterator;
+ import org.apache.arrow.adapter.jdbc.JdbcFieldInfo;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrow;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowConfig;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowConfigBuilder;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowUtils;
+ import org.apache.arrow.memory.BufferAllocator;
+ import org.apache.arrow.memory.RootAllocator;
+ import org.apache.arrow.vector.VectorSchemaRoot;
+ import org.apache.ibatis.jdbc.ScriptRunner;
+
+ import java.io.BufferedReader;
+ import java.io.FileReader;
+ import java.io.IOException;
+ import java.sql.Connection;
+ import java.sql.DriverManager;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+ import java.sql.Types;
+ import java.util.HashMap;
+
+ try (BufferAllocator allocator = new RootAllocator();
+ Connection connection = DriverManager.getConnection(
+ "jdbc:h2:mem:h2-jdbc-adapter")) {
+ ScriptRunner runnerDDLDML = new ScriptRunner(connection);
+ runnerDDLDML.setLogWriter(null);
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/jdbc/h2-ddl.sql")));
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/jdbc/h2-dml.sql")));
+ JdbcToArrowConfig config = new JdbcToArrowConfigBuilder(allocator,
+ JdbcToArrowUtils.getUtcCalendar())
+ .setArraySubTypeByColumnNameMap(
+ new HashMap<>() {{
+ put("LIST_FIELD19",
+ new JdbcFieldInfo(Types.INTEGER));
+ }}
+ )
+ .build();
+ try (ResultSet resultSet = connection.createStatement().executeQuery(
+ "SELECT int_field1, bool_field2, bigint_field5, char_field16, list_field19 FROM TABLE1");
+ ArrowVectorIterator iterator = JdbcToArrow.sqlToArrowVectorIterator(
+ resultSet, config)) {
+ while (iterator.hasNext()) {
+ try (VectorSchemaRoot root = iterator.next()) {
+ System.out.print(root.contentToTSVString());
+ }
+ }
+ }
+ } catch (SQLException | IOException e) {
+ e.printStackTrace();
+ }
+
+.. testoutput::
+
+ INT_FIELD1 BOOL_FIELD2 BIGINT_FIELD5 CHAR_FIELD16 LIST_FIELD19
+ 101 true 1000000000300 some char text [1,2,3]
+ 102 true 100000000030 some char text [1,2]
+ 103 true 10000000003 some char text [1]
+
+Configuring batch size
+======================
+
+The maximum rowCount to read each time is configured by default in 1024. This
+can be customized by setting values as needed by ``setTargetBatchSize``.
Review Comment:
```suggestion
By default, the adapter will read up to 1024 rows in a batch. This
can be customized via ``setTargetBatchSize``.
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-cookbook] lidavidm commented on a diff in pull request #229: [Java] Adding Arrow Java JDBC adapter examples
Posted by GitBox <gi...@apache.org>.
lidavidm commented on code in PR #229:
URL: https://github.com/apache/arrow-cookbook/pull/229#discussion_r921741133
##########
java/source/io.rst:
##########
@@ -444,6 +444,176 @@ Reading Parquet File
Please check :doc:`Dataset <./dataset>`
+Reading JDBC ResultSets
+***********************
+
+The `Arrow Java JDBC module <https://arrow.apache.org/docs/java/jdbc.html>`_
+help us to convert JDBC ResultSets objects into columnar arrow format objects
+through Arrow VectorSchemaRoots.
+
+ResultSet to VectorSchemaRoot Conversion
+----------------------------------------
+
+The main class to help us to convert ResultSet to VectorSchemaRoot is
+`JdbcToArrow <https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrow.html>`_
+
+.. testcode::
+
+ import org.apache.arrow.adapter.jdbc.ArrowVectorIterator;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrow;
+ import org.apache.arrow.memory.BufferAllocator;
+ import org.apache.arrow.memory.RootAllocator;
+ import org.apache.arrow.vector.VectorSchemaRoot;
+ import org.h2.jdbcx.JdbcConnectionPool;
+
+ import java.io.IOException;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+
+ try (BufferAllocator allocator = new RootAllocator()) {
+ JdbcConnectionPool pool = JdbcConnectionPool.create(
+ "jdbc:h2:zip:./thirdpartydeps/database/jdbc-cookbook.zip!/test",
+ "sa", "");
+ ResultSet resultSet = pool.getConnection().createStatement().executeQuery(
+ "SELECT int_field1, bool_field2, double_field7 FROM TABLE1");
+ try (ArrowVectorIterator iterator = JdbcToArrow.sqlToArrowVectorIterator(
+ resultSet, allocator)) {
+ while (iterator.hasNext()) {
+ VectorSchemaRoot root = iterator.next();
+ System.out.print(root.contentToTSVString());
+ }
+ }
+ } catch (SQLException throwables) {
+ throwables.printStackTrace();
+ } catch (IOException e) {
+ e.printStackTrace();
+ }
+
+.. testoutput::
+
+ INT_FIELD1 BOOL_FIELD2 DOUBLE_FIELD7
+ 101 true 5.6478356785345E10
+ 102 true 5.6478356785345E10
+ 103 true 5.6478356785345E10
+
+JdbcToArrow could be created also with a custom configurations needed with the
+support of `JdbcToArrowConfig <https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrowConfig.html>`_
+, it is useful at the moment to work with array columns.
+
+.. testcode::
+
+ import org.apache.arrow.adapter.jdbc.ArrowVectorIterator;
+ import org.apache.arrow.adapter.jdbc.JdbcFieldInfo;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrow;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowConfig;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowConfigBuilder;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowUtils;
+ import org.apache.arrow.memory.BufferAllocator;
+ import org.apache.arrow.memory.RootAllocator;
+ import org.apache.arrow.vector.VectorSchemaRoot;
+ import org.h2.jdbcx.JdbcConnectionPool;
+
+ import java.io.IOException;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+ import java.sql.Types;
+ import java.util.HashMap;
+
+ try (BufferAllocator allocator = new RootAllocator()) {
+ JdbcConnectionPool pool = JdbcConnectionPool.create(
+ "jdbc:h2:zip:./thirdpartydeps/database/jdbc-cookbook.zip!/test",
Review Comment:
Hmm, sticking to H2 is OK then. Didn't realize Derby lacked that
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-cookbook] davisusanibar commented on a diff in pull request #229: [Java] Adding Arrow Java JDBC adapter examples
Posted by GitBox <gi...@apache.org>.
davisusanibar commented on code in PR #229:
URL: https://github.com/apache/arrow-cookbook/pull/229#discussion_r923958304
##########
java/source/demo/pom.xml:
##########
@@ -21,9 +21,17 @@
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
- <arrow.version>8.0.0</arrow.version>
+ <arrow.version>9.0.0.dev191</arrow.version><!-- Prepare for 9.0.0 release -->
Review Comment:
Added configuration to define Stable/Dev version at CI script level.
But we need to talk about scope because current matrix is invoked 12 times:
```
python-version: [ '3.9' ]
java-version: [ '11', '17', '18' ]
compiler-version: ['11']
arrow-version: ['8.0.0', '9.0.0.dev405']
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-cookbook] davisusanibar commented on a diff in pull request #229: [Java] Adding Arrow Java JDBC adapter examples
Posted by GitBox <gi...@apache.org>.
davisusanibar commented on code in PR #229:
URL: https://github.com/apache/arrow-cookbook/pull/229#discussion_r923958773
##########
java/source/demo/pom.xml:
##########
@@ -21,9 +21,17 @@
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
- <arrow.version>8.0.0</arrow.version>
+ <arrow.version>9.0.0.dev191</arrow.version><!-- Prepare for 9.0.0 release -->
Review Comment:
If we consider OS it is invoked 24 times: 12 Linux / 12 MacOS
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-cookbook] davisusanibar commented on a diff in pull request #229: [Java] Adding Arrow Java JDBC adapter examples
Posted by GitBox <gi...@apache.org>.
davisusanibar commented on code in PR #229:
URL: https://github.com/apache/arrow-cookbook/pull/229#discussion_r939246469
##########
java/ext/javadoctest.py:
##########
@@ -37,6 +37,9 @@ def compile(
"-q",
"dependency:build-classpath",
"-DincludeTypes=jar",
+ "-Dmaven.compiler.source="+os.environ.get('compiler_version', '8'),
+ "-Dmaven.compiler.target="+os.environ.get('compiler_version', '8'),
+ "-Darrow.version="+os.environ.get('arrow_version', '9.0.0'),
Review Comment:
Yes, it was removed, this part is to pass the CI variables to the Java process and assign values if they are not set as for example local running
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-cookbook] davisusanibar commented on a diff in pull request #229: [Java] Adding Arrow Java JDBC adapter examples
Posted by GitBox <gi...@apache.org>.
davisusanibar commented on code in PR #229:
URL: https://github.com/apache/arrow-cookbook/pull/229#discussion_r921738140
##########
java/source/io.rst:
##########
@@ -444,6 +444,176 @@ Reading Parquet File
Please check :doc:`Dataset <./dataset>`
+Reading JDBC ResultSets
+***********************
+
+The `Arrow Java JDBC module <https://arrow.apache.org/docs/java/jdbc.html>`_
+help us to convert JDBC ResultSets objects into columnar arrow format objects
+through Arrow VectorSchemaRoots.
+
+ResultSet to VectorSchemaRoot Conversion
+----------------------------------------
+
+The main class to help us to convert ResultSet to VectorSchemaRoot is
+`JdbcToArrow <https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrow.html>`_
+
+.. testcode::
+
+ import org.apache.arrow.adapter.jdbc.ArrowVectorIterator;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrow;
+ import org.apache.arrow.memory.BufferAllocator;
+ import org.apache.arrow.memory.RootAllocator;
+ import org.apache.arrow.vector.VectorSchemaRoot;
+ import org.h2.jdbcx.JdbcConnectionPool;
+
+ import java.io.IOException;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+
+ try (BufferAllocator allocator = new RootAllocator()) {
+ JdbcConnectionPool pool = JdbcConnectionPool.create(
+ "jdbc:h2:zip:./thirdpartydeps/database/jdbc-cookbook.zip!/test",
+ "sa", "");
+ ResultSet resultSet = pool.getConnection().createStatement().executeQuery(
+ "SELECT int_field1, bool_field2, double_field7 FROM TABLE1");
+ try (ArrowVectorIterator iterator = JdbcToArrow.sqlToArrowVectorIterator(
+ resultSet, allocator)) {
+ while (iterator.hasNext()) {
+ VectorSchemaRoot root = iterator.next();
+ System.out.print(root.contentToTSVString());
+ }
+ }
+ } catch (SQLException throwables) {
+ throwables.printStackTrace();
+ } catch (IOException e) {
+ e.printStackTrace();
+ }
+
+.. testoutput::
+
+ INT_FIELD1 BOOL_FIELD2 DOUBLE_FIELD7
+ 101 true 5.6478356785345E10
+ 102 true 5.6478356785345E10
+ 103 true 5.6478356785345E10
+
+JdbcToArrow could be created also with a custom configurations needed with the
+support of `JdbcToArrowConfig <https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrowConfig.html>`_
+, it is useful at the moment to work with array columns.
+
+.. testcode::
+
+ import org.apache.arrow.adapter.jdbc.ArrowVectorIterator;
+ import org.apache.arrow.adapter.jdbc.JdbcFieldInfo;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrow;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowConfig;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowConfigBuilder;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowUtils;
+ import org.apache.arrow.memory.BufferAllocator;
+ import org.apache.arrow.memory.RootAllocator;
+ import org.apache.arrow.vector.VectorSchemaRoot;
+ import org.h2.jdbcx.JdbcConnectionPool;
+
+ import java.io.IOException;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+ import java.sql.Types;
+ import java.util.HashMap;
+
+ try (BufferAllocator allocator = new RootAllocator()) {
+ JdbcConnectionPool pool = JdbcConnectionPool.create(
+ "jdbc:h2:zip:./thirdpartydeps/database/jdbc-cookbook.zip!/test",
Review Comment:
Hi @lidavidm in the migration from H2 to Derby database I saw that `Array` support is enabled for H2 but not for Apache Derby ([ML](https://lists.apache.org/thread/xw37phkcrhvjccm94jnb8t9qz40f0cbc) / Jira [ticket](https://issues.apache.org/jira/browse/IOTDB-86))
How do we could proceed?
- One option will be to only showcase example for int/boolean/char data types
- Another option will be to maintain current H2 database
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-cookbook] lidavidm commented on a diff in pull request #229: [Java] Adding Arrow Java JDBC adapter examples
Posted by GitBox <gi...@apache.org>.
lidavidm commented on code in PR #229:
URL: https://github.com/apache/arrow-cookbook/pull/229#discussion_r920547587
##########
java/source/demo/pom.xml:
##########
@@ -21,9 +21,17 @@
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
- <arrow.version>8.0.0</arrow.version>
+ <arrow.version>9.0.0.dev191</arrow.version><!-- Prepare for 9.0.0 release -->
Review Comment:
I don't think we want to make the cookbook depend on nightly arrow…so we should hold off until after the release?
##########
java/source/io.rst:
##########
@@ -444,6 +444,176 @@ Reading Parquet File
Please check :doc:`Dataset <./dataset>`
+Reading JDBC ResultSets
+***********************
+
+The `Arrow Java JDBC module <https://arrow.apache.org/docs/java/jdbc.html>`_
+help us to convert JDBC ResultSets objects into columnar arrow format objects
+through Arrow VectorSchemaRoots.
+
+ResultSet to VectorSchemaRoot Conversion
+----------------------------------------
+
+The main class to help us to convert ResultSet to VectorSchemaRoot is
+`JdbcToArrow <https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrow.html>`_
+
+.. testcode::
+
+ import org.apache.arrow.adapter.jdbc.ArrowVectorIterator;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrow;
+ import org.apache.arrow.memory.BufferAllocator;
+ import org.apache.arrow.memory.RootAllocator;
+ import org.apache.arrow.vector.VectorSchemaRoot;
+ import org.h2.jdbcx.JdbcConnectionPool;
+
+ import java.io.IOException;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+
+ try (BufferAllocator allocator = new RootAllocator()) {
+ JdbcConnectionPool pool = JdbcConnectionPool.create(
+ "jdbc:h2:zip:./thirdpartydeps/database/jdbc-cookbook.zip!/test",
+ "sa", "");
+ ResultSet resultSet = pool.getConnection().createStatement().executeQuery(
Review Comment:
ResultSet needs to be closed, right?
##########
java/source/io.rst:
##########
@@ -444,6 +444,176 @@ Reading Parquet File
Please check :doc:`Dataset <./dataset>`
+Reading JDBC ResultSets
+***********************
+
+The `Arrow Java JDBC module <https://arrow.apache.org/docs/java/jdbc.html>`_
+help us to convert JDBC ResultSets objects into columnar arrow format objects
+through Arrow VectorSchemaRoots.
+
+ResultSet to VectorSchemaRoot Conversion
+----------------------------------------
+
+The main class to help us to convert ResultSet to VectorSchemaRoot is
+`JdbcToArrow <https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrow.html>`_
+
+.. testcode::
+
+ import org.apache.arrow.adapter.jdbc.ArrowVectorIterator;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrow;
+ import org.apache.arrow.memory.BufferAllocator;
+ import org.apache.arrow.memory.RootAllocator;
+ import org.apache.arrow.vector.VectorSchemaRoot;
+ import org.h2.jdbcx.JdbcConnectionPool;
+
+ import java.io.IOException;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+
+ try (BufferAllocator allocator = new RootAllocator()) {
+ JdbcConnectionPool pool = JdbcConnectionPool.create(
+ "jdbc:h2:zip:./thirdpartydeps/database/jdbc-cookbook.zip!/test",
+ "sa", "");
+ ResultSet resultSet = pool.getConnection().createStatement().executeQuery(
+ "SELECT int_field1, bool_field2, double_field7 FROM TABLE1");
+ try (ArrowVectorIterator iterator = JdbcToArrow.sqlToArrowVectorIterator(
+ resultSet, allocator)) {
+ while (iterator.hasNext()) {
+ VectorSchemaRoot root = iterator.next();
+ System.out.print(root.contentToTSVString());
+ }
+ }
+ } catch (SQLException throwables) {
+ throwables.printStackTrace();
+ } catch (IOException e) {
+ e.printStackTrace();
Review Comment:
```suggestion
} catch (SQLException | IOException e) {
e.printStackTrace();
```
and ditto below
##########
java/source/io.rst:
##########
@@ -444,6 +444,176 @@ Reading Parquet File
Please check :doc:`Dataset <./dataset>`
+Reading JDBC ResultSets
+***********************
+
+The `Arrow Java JDBC module <https://arrow.apache.org/docs/java/jdbc.html>`_
+help us to convert JDBC ResultSets objects into columnar arrow format objects
+through Arrow VectorSchemaRoots.
+
+ResultSet to VectorSchemaRoot Conversion
+----------------------------------------
+
+The main class to help us to convert ResultSet to VectorSchemaRoot is
+`JdbcToArrow <https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrow.html>`_
+
+.. testcode::
+
+ import org.apache.arrow.adapter.jdbc.ArrowVectorIterator;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrow;
+ import org.apache.arrow.memory.BufferAllocator;
+ import org.apache.arrow.memory.RootAllocator;
+ import org.apache.arrow.vector.VectorSchemaRoot;
+ import org.h2.jdbcx.JdbcConnectionPool;
+
+ import java.io.IOException;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+
+ try (BufferAllocator allocator = new RootAllocator()) {
+ JdbcConnectionPool pool = JdbcConnectionPool.create(
+ "jdbc:h2:zip:./thirdpartydeps/database/jdbc-cookbook.zip!/test",
+ "sa", "");
+ ResultSet resultSet = pool.getConnection().createStatement().executeQuery(
+ "SELECT int_field1, bool_field2, double_field7 FROM TABLE1");
+ try (ArrowVectorIterator iterator = JdbcToArrow.sqlToArrowVectorIterator(
+ resultSet, allocator)) {
+ while (iterator.hasNext()) {
+ VectorSchemaRoot root = iterator.next();
+ System.out.print(root.contentToTSVString());
+ }
+ }
+ } catch (SQLException throwables) {
+ throwables.printStackTrace();
+ } catch (IOException e) {
+ e.printStackTrace();
+ }
+
+.. testoutput::
+
+ INT_FIELD1 BOOL_FIELD2 DOUBLE_FIELD7
+ 101 true 5.6478356785345E10
+ 102 true 5.6478356785345E10
+ 103 true 5.6478356785345E10
+
+JdbcToArrow could be created also with a custom configurations needed with the
+support of `JdbcToArrowConfig <https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrowConfig.html>`_
+, it is useful at the moment to work with array columns.
Review Comment:
We talk about arrays and row counts here, what about decimals and other things Todd added?
##########
java/source/io.rst:
##########
@@ -444,6 +444,176 @@ Reading Parquet File
Please check :doc:`Dataset <./dataset>`
+Reading JDBC ResultSets
+***********************
+
+The `Arrow Java JDBC module <https://arrow.apache.org/docs/java/jdbc.html>`_
+help us to convert JDBC ResultSets objects into columnar arrow format objects
+through Arrow VectorSchemaRoots.
+
+ResultSet to VectorSchemaRoot Conversion
+----------------------------------------
+
+The main class to help us to convert ResultSet to VectorSchemaRoot is
+`JdbcToArrow <https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrow.html>`_
+
+.. testcode::
+
+ import org.apache.arrow.adapter.jdbc.ArrowVectorIterator;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrow;
+ import org.apache.arrow.memory.BufferAllocator;
+ import org.apache.arrow.memory.RootAllocator;
+ import org.apache.arrow.vector.VectorSchemaRoot;
+ import org.h2.jdbcx.JdbcConnectionPool;
+
+ import java.io.IOException;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+
+ try (BufferAllocator allocator = new RootAllocator()) {
+ JdbcConnectionPool pool = JdbcConnectionPool.create(
+ "jdbc:h2:zip:./thirdpartydeps/database/jdbc-cookbook.zip!/test",
+ "sa", "");
+ ResultSet resultSet = pool.getConnection().createStatement().executeQuery(
+ "SELECT int_field1, bool_field2, double_field7 FROM TABLE1");
+ try (ArrowVectorIterator iterator = JdbcToArrow.sqlToArrowVectorIterator(
+ resultSet, allocator)) {
+ while (iterator.hasNext()) {
+ VectorSchemaRoot root = iterator.next();
+ System.out.print(root.contentToTSVString());
+ }
+ }
+ } catch (SQLException throwables) {
+ throwables.printStackTrace();
+ } catch (IOException e) {
+ e.printStackTrace();
+ }
+
+.. testoutput::
+
+ INT_FIELD1 BOOL_FIELD2 DOUBLE_FIELD7
+ 101 true 5.6478356785345E10
+ 102 true 5.6478356785345E10
+ 103 true 5.6478356785345E10
+
+JdbcToArrow could be created also with a custom configurations needed with the
+support of `JdbcToArrowConfig <https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrowConfig.html>`_
+, it is useful at the moment to work with array columns.
+
+.. testcode::
+
+ import org.apache.arrow.adapter.jdbc.ArrowVectorIterator;
+ import org.apache.arrow.adapter.jdbc.JdbcFieldInfo;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrow;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowConfig;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowConfigBuilder;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowUtils;
+ import org.apache.arrow.memory.BufferAllocator;
+ import org.apache.arrow.memory.RootAllocator;
+ import org.apache.arrow.vector.VectorSchemaRoot;
+ import org.h2.jdbcx.JdbcConnectionPool;
+
+ import java.io.IOException;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+ import java.sql.Types;
+ import java.util.HashMap;
+
+ try (BufferAllocator allocator = new RootAllocator()) {
+ JdbcConnectionPool pool = JdbcConnectionPool.create(
+ "jdbc:h2:zip:./thirdpartydeps/database/jdbc-cookbook.zip!/test",
Review Comment:
is an in-memory database not possible? Apache Derby lets us do that (that's what I've been using for testing in ADBC, and it's what Flight SQL uses)
##########
java/source/io.rst:
##########
@@ -444,6 +444,176 @@ Reading Parquet File
Please check :doc:`Dataset <./dataset>`
+Reading JDBC ResultSets
+***********************
+
+The `Arrow Java JDBC module <https://arrow.apache.org/docs/java/jdbc.html>`_
+help us to convert JDBC ResultSets objects into columnar arrow format objects
+through Arrow VectorSchemaRoots.
+
+ResultSet to VectorSchemaRoot Conversion
+----------------------------------------
+
+The main class to help us to convert ResultSet to VectorSchemaRoot is
+`JdbcToArrow <https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrow.html>`_
+
+.. testcode::
+
+ import org.apache.arrow.adapter.jdbc.ArrowVectorIterator;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrow;
+ import org.apache.arrow.memory.BufferAllocator;
+ import org.apache.arrow.memory.RootAllocator;
+ import org.apache.arrow.vector.VectorSchemaRoot;
+ import org.h2.jdbcx.JdbcConnectionPool;
+
+ import java.io.IOException;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+
+ try (BufferAllocator allocator = new RootAllocator()) {
+ JdbcConnectionPool pool = JdbcConnectionPool.create(
+ "jdbc:h2:zip:./thirdpartydeps/database/jdbc-cookbook.zip!/test",
+ "sa", "");
+ ResultSet resultSet = pool.getConnection().createStatement().executeQuery(
+ "SELECT int_field1, bool_field2, double_field7 FROM TABLE1");
+ try (ArrowVectorIterator iterator = JdbcToArrow.sqlToArrowVectorIterator(
+ resultSet, allocator)) {
+ while (iterator.hasNext()) {
+ VectorSchemaRoot root = iterator.next();
+ System.out.print(root.contentToTSVString());
+ }
+ }
+ } catch (SQLException throwables) {
+ throwables.printStackTrace();
+ } catch (IOException e) {
+ e.printStackTrace();
+ }
+
+.. testoutput::
+
+ INT_FIELD1 BOOL_FIELD2 DOUBLE_FIELD7
+ 101 true 5.6478356785345E10
+ 102 true 5.6478356785345E10
+ 103 true 5.6478356785345E10
+
+JdbcToArrow could be created also with a custom configurations needed with the
+support of `JdbcToArrowConfig <https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrowConfig.html>`_
+, it is useful at the moment to work with array columns.
Review Comment:
```suggestion
JdbcToArrow accepts configuration through `JdbcToArrowConfig
<https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrowConfig.html>`_.
For example, the type of the elements of array columns can be specified.
```
##########
java/source/io.rst:
##########
@@ -444,6 +444,176 @@ Reading Parquet File
Please check :doc:`Dataset <./dataset>`
+Reading JDBC ResultSets
+***********************
+
+The `Arrow Java JDBC module <https://arrow.apache.org/docs/java/jdbc.html>`_
+help us to convert JDBC ResultSets objects into columnar arrow format objects
+through Arrow VectorSchemaRoots.
Review Comment:
```suggestion
The `Arrow Java JDBC module <https://arrow.apache.org/docs/java/jdbc.html>`_
converts JDBC ResultSets into Arrow VectorSchemaRoots.
```
##########
java/source/demo/pom.xml:
##########
@@ -21,9 +21,17 @@
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
- <arrow.version>8.0.0</arrow.version>
+ <arrow.version>9.0.0.dev191</arrow.version><!-- Prepare for 9.0.0 release -->
Review Comment:
either that or we do need to set up stable/nightly cookbooks, CC @amol-?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-cookbook] davisusanibar commented on a diff in pull request #229: [Java] Adding Arrow Java JDBC adapter examples
Posted by GitBox <gi...@apache.org>.
davisusanibar commented on code in PR #229:
URL: https://github.com/apache/arrow-cookbook/pull/229#discussion_r923973844
##########
java/source/demo/pom.xml:
##########
@@ -21,9 +21,17 @@
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
- <arrow.version>8.0.0</arrow.version>
+ <arrow.version>9.0.0.dev191</arrow.version><!-- Prepare for 9.0.0 release -->
Review Comment:
> IMO it's fine to only test Java 11 (especially if that's the language level we target) (or, compile with 11 and run on 17), and again: do we care about stable/nightly cookbooks? That is a separate discussion
Ok. PoC about how to pass variables matrix from .ci github to Java cookbook is possible, now we need to discuss: `do we care about stable/nightly cookbooks? `
This is the current setup:
```
strategy:
matrix:
python-version: [ '3.9' ]
java-version: [ '11', '17', '18' ]
compiler-version: ['11']
arrow-version: ['9.0.0.dev405']
```
This could be some capabilities that is needed to implement if we need to care about stable/dev:
- Configure skip/consider cookbook per Arrow Java version needed (for new dev features that is not supported on stable version)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-cookbook] davisusanibar commented on a diff in pull request #229: [Java] Adding Arrow Java JDBC adapter examples
Posted by GitBox <gi...@apache.org>.
davisusanibar commented on code in PR #229:
URL: https://github.com/apache/arrow-cookbook/pull/229#discussion_r939245439
##########
java/source/io.rst:
##########
@@ -461,6 +461,292 @@ Reading Parquet File
Please check :doc:`Dataset <./dataset>`
+Reading JDBC ResultSets
Review Comment:
Ok, lets move it to their own file
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-cookbook] davisusanibar commented on a diff in pull request #229: [Java] Adding Arrow Java JDBC adapter examples
Posted by GitBox <gi...@apache.org>.
davisusanibar commented on code in PR #229:
URL: https://github.com/apache/arrow-cookbook/pull/229#discussion_r920560781
##########
java/source/io.rst:
##########
@@ -444,6 +444,176 @@ Reading Parquet File
Please check :doc:`Dataset <./dataset>`
+Reading JDBC ResultSets
+***********************
+
+The `Arrow Java JDBC module <https://arrow.apache.org/docs/java/jdbc.html>`_
+help us to convert JDBC ResultSets objects into columnar arrow format objects
+through Arrow VectorSchemaRoots.
+
+ResultSet to VectorSchemaRoot Conversion
+----------------------------------------
+
+The main class to help us to convert ResultSet to VectorSchemaRoot is
+`JdbcToArrow <https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrow.html>`_
+
+.. testcode::
+
+ import org.apache.arrow.adapter.jdbc.ArrowVectorIterator;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrow;
+ import org.apache.arrow.memory.BufferAllocator;
+ import org.apache.arrow.memory.RootAllocator;
+ import org.apache.arrow.vector.VectorSchemaRoot;
+ import org.h2.jdbcx.JdbcConnectionPool;
+
+ import java.io.IOException;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+
+ try (BufferAllocator allocator = new RootAllocator()) {
+ JdbcConnectionPool pool = JdbcConnectionPool.create(
+ "jdbc:h2:zip:./thirdpartydeps/database/jdbc-cookbook.zip!/test",
+ "sa", "");
+ ResultSet resultSet = pool.getConnection().createStatement().executeQuery(
+ "SELECT int_field1, bool_field2, double_field7 FROM TABLE1");
+ try (ArrowVectorIterator iterator = JdbcToArrow.sqlToArrowVectorIterator(
+ resultSet, allocator)) {
+ while (iterator.hasNext()) {
+ VectorSchemaRoot root = iterator.next();
+ System.out.print(root.contentToTSVString());
+ }
+ }
+ } catch (SQLException throwables) {
+ throwables.printStackTrace();
+ } catch (IOException e) {
+ e.printStackTrace();
+ }
+
+.. testoutput::
+
+ INT_FIELD1 BOOL_FIELD2 DOUBLE_FIELD7
+ 101 true 5.6478356785345E10
+ 102 true 5.6478356785345E10
+ 103 true 5.6478356785345E10
+
+JdbcToArrow could be created also with a custom configurations needed with the
+support of `JdbcToArrowConfig <https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrowConfig.html>`_
+, it is useful at the moment to work with array columns.
+
+.. testcode::
+
+ import org.apache.arrow.adapter.jdbc.ArrowVectorIterator;
+ import org.apache.arrow.adapter.jdbc.JdbcFieldInfo;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrow;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowConfig;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowConfigBuilder;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowUtils;
+ import org.apache.arrow.memory.BufferAllocator;
+ import org.apache.arrow.memory.RootAllocator;
+ import org.apache.arrow.vector.VectorSchemaRoot;
+ import org.h2.jdbcx.JdbcConnectionPool;
+
+ import java.io.IOException;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+ import java.sql.Types;
+ import java.util.HashMap;
+
+ try (BufferAllocator allocator = new RootAllocator()) {
+ JdbcConnectionPool pool = JdbcConnectionPool.create(
+ "jdbc:h2:zip:./thirdpartydeps/database/jdbc-cookbook.zip!/test",
Review Comment:
Let me update to use Apache Derby also to standardize that in-memory database
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-cookbook] lidavidm commented on a diff in pull request #229: [Java] Adding Arrow Java JDBC adapter examples
Posted by GitBox <gi...@apache.org>.
lidavidm commented on code in PR #229:
URL: https://github.com/apache/arrow-cookbook/pull/229#discussion_r923958750
##########
java/source/demo/pom.xml:
##########
@@ -21,9 +21,17 @@
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
- <arrow.version>8.0.0</arrow.version>
+ <arrow.version>9.0.0.dev191</arrow.version><!-- Prepare for 9.0.0 release -->
Review Comment:
IMO it's fine to only test Java 11 (especially if that's the language level we target) (or, compile with 11 and run on 17), and again: do we care about stable/nightly cookbooks? That is a separate discussion
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-cookbook] lidavidm commented on a diff in pull request #229: [Java] Adding Arrow Java JDBC adapter examples
Posted by GitBox <gi...@apache.org>.
lidavidm commented on code in PR #229:
URL: https://github.com/apache/arrow-cookbook/pull/229#discussion_r942481250
##########
.github/workflows/test_java_osx_cookbook.yml:
##########
@@ -37,6 +37,8 @@ jobs:
matrix:
python-version: [ '3.9' ]
java-version: [ '11', '17', '18' ]
+ compiler-version: ['11']
+ arrow-version: ['9.0.0']
Review Comment:
At the very least, I'd rather we put these CI changes separately instead of combining them with a larger change
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-cookbook] davisusanibar commented on a diff in pull request #229: [Java] Adding Arrow Java JDBC adapter examples
Posted by GitBox <gi...@apache.org>.
davisusanibar commented on code in PR #229:
URL: https://github.com/apache/arrow-cookbook/pull/229#discussion_r942502815
##########
.github/workflows/test_java_osx_cookbook.yml:
##########
@@ -37,6 +37,8 @@ jobs:
matrix:
python-version: [ '3.9' ]
java-version: [ '11', '17', '18' ]
+ compiler-version: ['11']
+ arrow-version: ['9.0.0']
Review Comment:
Make sense, let me replace with the last CI configuration
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-cookbook] davisusanibar commented on a diff in pull request #229: [Java] Adding Arrow Java JDBC adapter examples
Posted by GitBox <gi...@apache.org>.
davisusanibar commented on code in PR #229:
URL: https://github.com/apache/arrow-cookbook/pull/229#discussion_r920558032
##########
java/source/io.rst:
##########
@@ -444,6 +444,176 @@ Reading Parquet File
Please check :doc:`Dataset <./dataset>`
+Reading JDBC ResultSets
+***********************
+
+The `Arrow Java JDBC module <https://arrow.apache.org/docs/java/jdbc.html>`_
+help us to convert JDBC ResultSets objects into columnar arrow format objects
+through Arrow VectorSchemaRoots.
+
+ResultSet to VectorSchemaRoot Conversion
+----------------------------------------
+
+The main class to help us to convert ResultSet to VectorSchemaRoot is
+`JdbcToArrow <https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrow.html>`_
+
+.. testcode::
+
+ import org.apache.arrow.adapter.jdbc.ArrowVectorIterator;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrow;
+ import org.apache.arrow.memory.BufferAllocator;
+ import org.apache.arrow.memory.RootAllocator;
+ import org.apache.arrow.vector.VectorSchemaRoot;
+ import org.h2.jdbcx.JdbcConnectionPool;
+
+ import java.io.IOException;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+
+ try (BufferAllocator allocator = new RootAllocator()) {
+ JdbcConnectionPool pool = JdbcConnectionPool.create(
+ "jdbc:h2:zip:./thirdpartydeps/database/jdbc-cookbook.zip!/test",
+ "sa", "");
+ ResultSet resultSet = pool.getConnection().createStatement().executeQuery(
+ "SELECT int_field1, bool_field2, double_field7 FROM TABLE1");
+ try (ArrowVectorIterator iterator = JdbcToArrow.sqlToArrowVectorIterator(
+ resultSet, allocator)) {
+ while (iterator.hasNext()) {
+ VectorSchemaRoot root = iterator.next();
+ System.out.print(root.contentToTSVString());
+ }
+ }
+ } catch (SQLException throwables) {
+ throwables.printStackTrace();
+ } catch (IOException e) {
+ e.printStackTrace();
+ }
+
+.. testoutput::
+
+ INT_FIELD1 BOOL_FIELD2 DOUBLE_FIELD7
+ 101 true 5.6478356785345E10
+ 102 true 5.6478356785345E10
+ 103 true 5.6478356785345E10
+
+JdbcToArrow could be created also with a custom configurations needed with the
+support of `JdbcToArrowConfig <https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrowConfig.html>`_
+, it is useful at the moment to work with array columns.
Review Comment:
> We talk about arrays and row counts here, what about decimals and other things Todd added?
I just read that, let me add that also
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-cookbook] davisusanibar commented on a diff in pull request #229: [Java] Adding Arrow Java JDBC adapter examples
Posted by GitBox <gi...@apache.org>.
davisusanibar commented on code in PR #229:
URL: https://github.com/apache/arrow-cookbook/pull/229#discussion_r942479337
##########
.github/workflows/test_java_osx_cookbook.yml:
##########
@@ -37,6 +37,8 @@ jobs:
matrix:
python-version: [ '3.9' ]
java-version: [ '11', '17', '18' ]
+ compiler-version: ['11']
+ arrow-version: ['9.0.0']
Review Comment:
The main reason is to handle that as a matrix, this support pass an arrays of arrow_versions needed on demand, I could suggest that new changes related to a version would be handled by this CI file without change main code.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-cookbook] davisusanibar commented on pull request #229: [Java] Adding Arrow Java JDBC adapter examples
Posted by GitBox <gi...@apache.org>.
davisusanibar commented on PR #229:
URL: https://github.com/apache/arrow-cookbook/pull/229#issuecomment-1205912773
> This can be updated for 9.0.0 now I think
Updated
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-cookbook] davisusanibar commented on a diff in pull request #229: [Java] Adding Arrow Java JDBC adapter examples
Posted by GitBox <gi...@apache.org>.
davisusanibar commented on code in PR #229:
URL: https://github.com/apache/arrow-cookbook/pull/229#discussion_r923957685
##########
java/source/io.rst:
##########
@@ -444,6 +444,176 @@ Reading Parquet File
Please check :doc:`Dataset <./dataset>`
+Reading JDBC ResultSets
+***********************
+
+The `Arrow Java JDBC module <https://arrow.apache.org/docs/java/jdbc.html>`_
+help us to convert JDBC ResultSets objects into columnar arrow format objects
+through Arrow VectorSchemaRoots.
+
+ResultSet to VectorSchemaRoot Conversion
+----------------------------------------
+
+The main class to help us to convert ResultSet to VectorSchemaRoot is
+`JdbcToArrow <https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrow.html>`_
+
+.. testcode::
+
+ import org.apache.arrow.adapter.jdbc.ArrowVectorIterator;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrow;
+ import org.apache.arrow.memory.BufferAllocator;
+ import org.apache.arrow.memory.RootAllocator;
+ import org.apache.arrow.vector.VectorSchemaRoot;
+ import org.h2.jdbcx.JdbcConnectionPool;
+
+ import java.io.IOException;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+
+ try (BufferAllocator allocator = new RootAllocator()) {
+ JdbcConnectionPool pool = JdbcConnectionPool.create(
+ "jdbc:h2:zip:./thirdpartydeps/database/jdbc-cookbook.zip!/test",
+ "sa", "");
+ ResultSet resultSet = pool.getConnection().createStatement().executeQuery(
Review Comment:
Added
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-cookbook] davisusanibar commented on a diff in pull request #229: [Java] Adding Arrow Java JDBC adapter examples
Posted by GitBox <gi...@apache.org>.
davisusanibar commented on code in PR #229:
URL: https://github.com/apache/arrow-cookbook/pull/229#discussion_r923958773
##########
java/source/demo/pom.xml:
##########
@@ -21,9 +21,17 @@
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
- <arrow.version>8.0.0</arrow.version>
+ <arrow.version>9.0.0.dev191</arrow.version><!-- Prepare for 9.0.0 release -->
Review Comment:
~~If we consider OS it is invoked 24 times: 12 Linux / 12 MacOS~~
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-cookbook] lidavidm merged pull request #229: [Java] Adding Arrow Java JDBC adapter examples
Posted by GitBox <gi...@apache.org>.
lidavidm merged PR #229:
URL: https://github.com/apache/arrow-cookbook/pull/229
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-cookbook] lidavidm commented on a diff in pull request #229: [Java] Adding Arrow Java JDBC adapter examples
Posted by GitBox <gi...@apache.org>.
lidavidm commented on code in PR #229:
URL: https://github.com/apache/arrow-cookbook/pull/229#discussion_r938739902
##########
java/source/io.rst:
##########
@@ -461,6 +461,292 @@ Reading Parquet File
Please check :doc:`Dataset <./dataset>`
+Reading JDBC ResultSets
+***********************
+
+The `Arrow Java JDBC module <https://arrow.apache.org/docs/java/jdbc.html>`_
+converts JDBC ResultSets into Arrow VectorSchemaRoots.
+
+ResultSet to VectorSchemaRoot Conversion
+----------------------------------------
+
+The main class to help us to convert ResultSet to VectorSchemaRoot is
+`JdbcToArrow <https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrow.html>`_
+
+.. testcode::
+
+ import org.apache.arrow.adapter.jdbc.ArrowVectorIterator;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrow;
+ import org.apache.arrow.memory.BufferAllocator;
+ import org.apache.arrow.memory.RootAllocator;
+ import org.apache.arrow.vector.VectorSchemaRoot;
+ import org.apache.ibatis.jdbc.ScriptRunner;
+
+ import java.io.BufferedReader;
+ import java.io.FileReader;
+ import java.io.IOException;
+ import java.sql.Connection;
+ import java.sql.DriverManager;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+
+ try (BufferAllocator allocator = new RootAllocator();
+ Connection connection = DriverManager.getConnection(
+ "jdbc:h2:mem:h2-jdbc-adapter")) {
+ ScriptRunner runnerDDLDML = new ScriptRunner(connection);
+ runnerDDLDML.setLogWriter(null);
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/database/h2-ddl.sql")));
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/database/h2-dml.sql")));
+ try (ResultSet resultSet = connection.createStatement().executeQuery(
+ "SELECT int_field1, bool_field2, bigint_field5 FROM TABLE1");
+ ArrowVectorIterator iterator = JdbcToArrow.sqlToArrowVectorIterator(
+ resultSet, allocator)) {
+ while (iterator.hasNext()) {
+ try (VectorSchemaRoot root = iterator.next()) {
+ System.out.print(root.contentToTSVString());
+ }
+ }
+ }
+
+ } catch (SQLException | IOException e) {
+ e.printStackTrace();
+ }
+
+.. testoutput::
+
+ INT_FIELD1 BOOL_FIELD2 BIGINT_FIELD5
+ 101 true 1000000000300
+ 102 true 100000000030
+ 103 true 10000000003
+
+ResultSet with Array to VectorSchemaRoot Conversion
Review Comment:
```suggestion
Configuring Array subtypes
```
##########
java/source/io.rst:
##########
@@ -461,6 +461,292 @@ Reading Parquet File
Please check :doc:`Dataset <./dataset>`
+Reading JDBC ResultSets
Review Comment:
Hmm, I feel like JDBC belongs as its own file, especially if we have a separate file for Avro?
##########
java/source/io.rst:
##########
@@ -461,6 +461,292 @@ Reading Parquet File
Please check :doc:`Dataset <./dataset>`
+Reading JDBC ResultSets
+***********************
+
+The `Arrow Java JDBC module <https://arrow.apache.org/docs/java/jdbc.html>`_
+converts JDBC ResultSets into Arrow VectorSchemaRoots.
+
+ResultSet to VectorSchemaRoot Conversion
+----------------------------------------
+
+The main class to help us to convert ResultSet to VectorSchemaRoot is
+`JdbcToArrow <https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrow.html>`_
+
+.. testcode::
+
+ import org.apache.arrow.adapter.jdbc.ArrowVectorIterator;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrow;
+ import org.apache.arrow.memory.BufferAllocator;
+ import org.apache.arrow.memory.RootAllocator;
+ import org.apache.arrow.vector.VectorSchemaRoot;
+ import org.apache.ibatis.jdbc.ScriptRunner;
+
+ import java.io.BufferedReader;
+ import java.io.FileReader;
+ import java.io.IOException;
+ import java.sql.Connection;
+ import java.sql.DriverManager;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+
+ try (BufferAllocator allocator = new RootAllocator();
+ Connection connection = DriverManager.getConnection(
+ "jdbc:h2:mem:h2-jdbc-adapter")) {
+ ScriptRunner runnerDDLDML = new ScriptRunner(connection);
+ runnerDDLDML.setLogWriter(null);
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/database/h2-ddl.sql")));
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/database/h2-dml.sql")));
+ try (ResultSet resultSet = connection.createStatement().executeQuery(
+ "SELECT int_field1, bool_field2, bigint_field5 FROM TABLE1");
+ ArrowVectorIterator iterator = JdbcToArrow.sqlToArrowVectorIterator(
+ resultSet, allocator)) {
+ while (iterator.hasNext()) {
+ try (VectorSchemaRoot root = iterator.next()) {
+ System.out.print(root.contentToTSVString());
+ }
+ }
+ }
+
+ } catch (SQLException | IOException e) {
+ e.printStackTrace();
+ }
+
+.. testoutput::
+
+ INT_FIELD1 BOOL_FIELD2 BIGINT_FIELD5
+ 101 true 1000000000300
+ 102 true 100000000030
+ 103 true 10000000003
+
+ResultSet with Array to VectorSchemaRoot Conversion
+---------------------------------------------------
+
+JdbcToArrow accepts configuration through `JdbcToArrowConfig
+<https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrowConfig.html>`_.
+For example, the type of the elements of array columns can be specified by
+``setArraySubTypeByColumnNameMap``.
+
+.. testcode::
+
+ import org.apache.arrow.adapter.jdbc.ArrowVectorIterator;
+ import org.apache.arrow.adapter.jdbc.JdbcFieldInfo;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrow;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowConfig;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowConfigBuilder;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowUtils;
+ import org.apache.arrow.memory.BufferAllocator;
+ import org.apache.arrow.memory.RootAllocator;
+ import org.apache.arrow.vector.VectorSchemaRoot;
+ import org.apache.ibatis.jdbc.ScriptRunner;
+
+ import java.io.BufferedReader;
+ import java.io.FileReader;
+ import java.io.IOException;
+ import java.sql.Connection;
+ import java.sql.DriverManager;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+ import java.sql.Types;
+ import java.util.HashMap;
+
+ try (BufferAllocator allocator = new RootAllocator();
+ Connection connection = DriverManager.getConnection(
+ "jdbc:h2:mem:h2-jdbc-adapter")) {
+ ScriptRunner runnerDDLDML = new ScriptRunner(connection);
+ runnerDDLDML.setLogWriter(null);
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/database/h2-ddl.sql")));
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/database/h2-dml.sql")));
+ JdbcToArrowConfig config = new JdbcToArrowConfigBuilder(allocator,
+ JdbcToArrowUtils.getUtcCalendar())
+ .setArraySubTypeByColumnNameMap(
+ new HashMap<>() {{
+ put("LIST_FIELD19",
+ new JdbcFieldInfo(Types.INTEGER));
+ }}
+ )
+ .build();
+ try (ResultSet resultSet = connection.createStatement().executeQuery(
+ "SELECT int_field1, bool_field2, bigint_field5, char_field16, list_field19 FROM TABLE1");
+ ArrowVectorIterator iterator = JdbcToArrow.sqlToArrowVectorIterator(
+ resultSet, config)) {
+ while (iterator.hasNext()) {
+ try (VectorSchemaRoot root = iterator.next()) {
+ System.out.print(root.contentToTSVString());
+ }
+ }
+ }
+ } catch (SQLException | IOException e) {
+ e.printStackTrace();
+ }
+
+.. testoutput::
+
+ INT_FIELD1 BOOL_FIELD2 BIGINT_FIELD5 CHAR_FIELD16 LIST_FIELD19
+ 101 true 1000000000300 some char text [1,2,3]
+ 102 true 100000000030 some char text [1,2]
+ 103 true 10000000003 some char text [1]
+
+ResultSet per Batches to VectorSchemaRoot Conversion
+----------------------------------------------------
+
+The maximum rowCount to read each time is configured by default in 1024. This
+can be customized by setting values as needed by ``setTargetBatchSize``.
+
+.. testcode::
+
+ import org.apache.arrow.adapter.jdbc.ArrowVectorIterator;
+ import org.apache.arrow.adapter.jdbc.JdbcFieldInfo;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrow;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowConfig;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowConfigBuilder;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowUtils;
+ import org.apache.arrow.memory.BufferAllocator;
+ import org.apache.arrow.memory.RootAllocator;
+ import org.apache.arrow.vector.VectorSchemaRoot;
+ import org.apache.ibatis.jdbc.ScriptRunner;
+
+ import java.io.BufferedReader;
+ import java.io.FileReader;
+ import java.io.IOException;
+ import java.sql.Connection;
+ import java.sql.DriverManager;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+ import java.sql.Types;
+ import java.util.HashMap;
+
+ try (BufferAllocator allocator = new RootAllocator();
+ Connection connection = DriverManager.getConnection(
+ "jdbc:h2:mem:h2-jdbc-adapter")) {
+ ScriptRunner runnerDDLDML = new ScriptRunner(connection);
+ runnerDDLDML.setLogWriter(null);
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/database/h2-ddl.sql")));
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/database/h2-dml.sql")));
+ JdbcToArrowConfig config = new JdbcToArrowConfigBuilder(allocator,
+ JdbcToArrowUtils.getUtcCalendar())
+ .setTargetBatchSize(2)
+ .setArraySubTypeByColumnNameMap(
+ new HashMap<>() {{
+ put("LIST_FIELD19",
+ new JdbcFieldInfo(Types.INTEGER));
+ }}
+ )
+ .build();
+ try (ResultSet resultSet = connection.createStatement().executeQuery(
+ "SELECT int_field1, bool_field2, bigint_field5, char_field16, list_field19 FROM TABLE1");
+ ArrowVectorIterator iterator = JdbcToArrow.sqlToArrowVectorIterator(
+ resultSet, config)) {
+ while (iterator.hasNext()) {
+ try (VectorSchemaRoot root = iterator.next()) {
+ System.out.print(root.contentToTSVString());
+ }
+ }
+ }
+ } catch (SQLException | IOException e) {
+ e.printStackTrace();
+ }
+
+.. testoutput::
+
+ INT_FIELD1 BOOL_FIELD2 BIGINT_FIELD5 CHAR_FIELD16 LIST_FIELD19
+ 101 true 1000000000300 some char text [1,2,3]
+ 102 true 100000000030 some char text [1,2]
+ INT_FIELD1 BOOL_FIELD2 BIGINT_FIELD5 CHAR_FIELD16 LIST_FIELD19
+ 103 true 10000000003 some char text [1]
+
+ResultSet with Precision/Scale to VectorSchemaRoot Conversion
+-------------------------------------------------------------
+
+There is a configuration about precision & scale for column data type needed
+(i.e. ``JdbcFieldInfo(Types.DECIMAL, /*precision*/ 20, /*scale*/ 7))``) but this
+configuration required exact matching of every row to the established scale
+for the column, and throws ``UnsupportedOperationException`` when there is a mismatch,
+aborting ResultSet processing,
+
+In this example we have BigInt data type configured on H2 Database, this is
+converted to (``/*scale*/0)`` by default, then we have a ``/*scale*/7`` configured on
+our code, this will be the error message for these differences: ``Caused by: java.lang.UnsupportedOperationException: BigDecimal scale must equal that in the Arrow vector: 0 != 7``
+if not applying ``setBigDecimalRoundingMode``
Review Comment:
```suggestion
In this example, we have a BigInt column. By default, the inferred scale
is 0. We override the scale to 7 and then set a RoundingMode to convert
values to the given scale.
```
##########
java/source/io.rst:
##########
@@ -461,6 +461,292 @@ Reading Parquet File
Please check :doc:`Dataset <./dataset>`
+Reading JDBC ResultSets
+***********************
+
+The `Arrow Java JDBC module <https://arrow.apache.org/docs/java/jdbc.html>`_
+converts JDBC ResultSets into Arrow VectorSchemaRoots.
+
+ResultSet to VectorSchemaRoot Conversion
+----------------------------------------
+
+The main class to help us to convert ResultSet to VectorSchemaRoot is
+`JdbcToArrow <https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrow.html>`_
+
+.. testcode::
+
+ import org.apache.arrow.adapter.jdbc.ArrowVectorIterator;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrow;
+ import org.apache.arrow.memory.BufferAllocator;
+ import org.apache.arrow.memory.RootAllocator;
+ import org.apache.arrow.vector.VectorSchemaRoot;
+ import org.apache.ibatis.jdbc.ScriptRunner;
+
+ import java.io.BufferedReader;
+ import java.io.FileReader;
+ import java.io.IOException;
+ import java.sql.Connection;
+ import java.sql.DriverManager;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+
+ try (BufferAllocator allocator = new RootAllocator();
+ Connection connection = DriverManager.getConnection(
+ "jdbc:h2:mem:h2-jdbc-adapter")) {
+ ScriptRunner runnerDDLDML = new ScriptRunner(connection);
+ runnerDDLDML.setLogWriter(null);
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/database/h2-ddl.sql")));
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/database/h2-dml.sql")));
+ try (ResultSet resultSet = connection.createStatement().executeQuery(
+ "SELECT int_field1, bool_field2, bigint_field5 FROM TABLE1");
+ ArrowVectorIterator iterator = JdbcToArrow.sqlToArrowVectorIterator(
+ resultSet, allocator)) {
+ while (iterator.hasNext()) {
+ try (VectorSchemaRoot root = iterator.next()) {
+ System.out.print(root.contentToTSVString());
+ }
+ }
+ }
+
+ } catch (SQLException | IOException e) {
+ e.printStackTrace();
+ }
+
+.. testoutput::
+
+ INT_FIELD1 BOOL_FIELD2 BIGINT_FIELD5
+ 101 true 1000000000300
+ 102 true 100000000030
+ 103 true 10000000003
+
+ResultSet with Array to VectorSchemaRoot Conversion
+---------------------------------------------------
+
+JdbcToArrow accepts configuration through `JdbcToArrowConfig
+<https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrowConfig.html>`_.
+For example, the type of the elements of array columns can be specified by
+``setArraySubTypeByColumnNameMap``.
+
+.. testcode::
+
+ import org.apache.arrow.adapter.jdbc.ArrowVectorIterator;
+ import org.apache.arrow.adapter.jdbc.JdbcFieldInfo;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrow;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowConfig;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowConfigBuilder;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowUtils;
+ import org.apache.arrow.memory.BufferAllocator;
+ import org.apache.arrow.memory.RootAllocator;
+ import org.apache.arrow.vector.VectorSchemaRoot;
+ import org.apache.ibatis.jdbc.ScriptRunner;
+
+ import java.io.BufferedReader;
+ import java.io.FileReader;
+ import java.io.IOException;
+ import java.sql.Connection;
+ import java.sql.DriverManager;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+ import java.sql.Types;
+ import java.util.HashMap;
+
+ try (BufferAllocator allocator = new RootAllocator();
+ Connection connection = DriverManager.getConnection(
+ "jdbc:h2:mem:h2-jdbc-adapter")) {
+ ScriptRunner runnerDDLDML = new ScriptRunner(connection);
+ runnerDDLDML.setLogWriter(null);
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/database/h2-ddl.sql")));
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/database/h2-dml.sql")));
+ JdbcToArrowConfig config = new JdbcToArrowConfigBuilder(allocator,
+ JdbcToArrowUtils.getUtcCalendar())
+ .setArraySubTypeByColumnNameMap(
+ new HashMap<>() {{
+ put("LIST_FIELD19",
+ new JdbcFieldInfo(Types.INTEGER));
+ }}
+ )
+ .build();
+ try (ResultSet resultSet = connection.createStatement().executeQuery(
+ "SELECT int_field1, bool_field2, bigint_field5, char_field16, list_field19 FROM TABLE1");
+ ArrowVectorIterator iterator = JdbcToArrow.sqlToArrowVectorIterator(
+ resultSet, config)) {
+ while (iterator.hasNext()) {
+ try (VectorSchemaRoot root = iterator.next()) {
+ System.out.print(root.contentToTSVString());
+ }
+ }
+ }
+ } catch (SQLException | IOException e) {
+ e.printStackTrace();
+ }
+
+.. testoutput::
+
+ INT_FIELD1 BOOL_FIELD2 BIGINT_FIELD5 CHAR_FIELD16 LIST_FIELD19
+ 101 true 1000000000300 some char text [1,2,3]
+ 102 true 100000000030 some char text [1,2]
+ 103 true 10000000003 some char text [1]
+
+ResultSet per Batches to VectorSchemaRoot Conversion
+----------------------------------------------------
+
+The maximum rowCount to read each time is configured by default in 1024. This
+can be customized by setting values as needed by ``setTargetBatchSize``.
+
+.. testcode::
+
+ import org.apache.arrow.adapter.jdbc.ArrowVectorIterator;
+ import org.apache.arrow.adapter.jdbc.JdbcFieldInfo;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrow;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowConfig;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowConfigBuilder;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowUtils;
+ import org.apache.arrow.memory.BufferAllocator;
+ import org.apache.arrow.memory.RootAllocator;
+ import org.apache.arrow.vector.VectorSchemaRoot;
+ import org.apache.ibatis.jdbc.ScriptRunner;
+
+ import java.io.BufferedReader;
+ import java.io.FileReader;
+ import java.io.IOException;
+ import java.sql.Connection;
+ import java.sql.DriverManager;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+ import java.sql.Types;
+ import java.util.HashMap;
+
+ try (BufferAllocator allocator = new RootAllocator();
+ Connection connection = DriverManager.getConnection(
+ "jdbc:h2:mem:h2-jdbc-adapter")) {
+ ScriptRunner runnerDDLDML = new ScriptRunner(connection);
+ runnerDDLDML.setLogWriter(null);
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/database/h2-ddl.sql")));
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/database/h2-dml.sql")));
+ JdbcToArrowConfig config = new JdbcToArrowConfigBuilder(allocator,
+ JdbcToArrowUtils.getUtcCalendar())
+ .setTargetBatchSize(2)
+ .setArraySubTypeByColumnNameMap(
+ new HashMap<>() {{
+ put("LIST_FIELD19",
+ new JdbcFieldInfo(Types.INTEGER));
+ }}
+ )
+ .build();
+ try (ResultSet resultSet = connection.createStatement().executeQuery(
+ "SELECT int_field1, bool_field2, bigint_field5, char_field16, list_field19 FROM TABLE1");
+ ArrowVectorIterator iterator = JdbcToArrow.sqlToArrowVectorIterator(
+ resultSet, config)) {
+ while (iterator.hasNext()) {
+ try (VectorSchemaRoot root = iterator.next()) {
+ System.out.print(root.contentToTSVString());
+ }
+ }
+ }
+ } catch (SQLException | IOException e) {
+ e.printStackTrace();
+ }
+
+.. testoutput::
+
+ INT_FIELD1 BOOL_FIELD2 BIGINT_FIELD5 CHAR_FIELD16 LIST_FIELD19
+ 101 true 1000000000300 some char text [1,2,3]
+ 102 true 100000000030 some char text [1,2]
+ INT_FIELD1 BOOL_FIELD2 BIGINT_FIELD5 CHAR_FIELD16 LIST_FIELD19
+ 103 true 10000000003 some char text [1]
+
+ResultSet with Precision/Scale to VectorSchemaRoot Conversion
+-------------------------------------------------------------
+
+There is a configuration about precision & scale for column data type needed
+(i.e. ``JdbcFieldInfo(Types.DECIMAL, /*precision*/ 20, /*scale*/ 7))``) but this
+configuration required exact matching of every row to the established scale
+for the column, and throws ``UnsupportedOperationException`` when there is a mismatch,
+aborting ResultSet processing,
Review Comment:
```suggestion
By default, the scale of any decimal values must exactly match the defined
scale of the Arrow type of the column, or else an UnsupportedOperationException
will be thrown with a message like ``BigDecimal scale must equal that in the Arrow
vector``will be thrown during conversion.
This can happen because Arrow infers the type from the ResultSet metadata, which
is not accurate for all database drivers. The JDBC adapter lets you avoid this
by either overriding the decimal scale, or by providing a RoundingMode via
``setBigDecimalRoundingMode`` to convert values to the expected scale.
```
Also, link to https://arrow.apache.org/docs/java/jdbc.html#type-mapping
##########
java/source/io.rst:
##########
@@ -461,6 +461,292 @@ Reading Parquet File
Please check :doc:`Dataset <./dataset>`
+Reading JDBC ResultSets
+***********************
+
+The `Arrow Java JDBC module <https://arrow.apache.org/docs/java/jdbc.html>`_
+converts JDBC ResultSets into Arrow VectorSchemaRoots.
+
+ResultSet to VectorSchemaRoot Conversion
+----------------------------------------
+
+The main class to help us to convert ResultSet to VectorSchemaRoot is
+`JdbcToArrow <https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrow.html>`_
+
+.. testcode::
+
+ import org.apache.arrow.adapter.jdbc.ArrowVectorIterator;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrow;
+ import org.apache.arrow.memory.BufferAllocator;
+ import org.apache.arrow.memory.RootAllocator;
+ import org.apache.arrow.vector.VectorSchemaRoot;
+ import org.apache.ibatis.jdbc.ScriptRunner;
+
+ import java.io.BufferedReader;
+ import java.io.FileReader;
+ import java.io.IOException;
+ import java.sql.Connection;
+ import java.sql.DriverManager;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+
+ try (BufferAllocator allocator = new RootAllocator();
+ Connection connection = DriverManager.getConnection(
+ "jdbc:h2:mem:h2-jdbc-adapter")) {
+ ScriptRunner runnerDDLDML = new ScriptRunner(connection);
+ runnerDDLDML.setLogWriter(null);
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/database/h2-ddl.sql")));
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/database/h2-dml.sql")));
+ try (ResultSet resultSet = connection.createStatement().executeQuery(
+ "SELECT int_field1, bool_field2, bigint_field5 FROM TABLE1");
+ ArrowVectorIterator iterator = JdbcToArrow.sqlToArrowVectorIterator(
+ resultSet, allocator)) {
+ while (iterator.hasNext()) {
+ try (VectorSchemaRoot root = iterator.next()) {
+ System.out.print(root.contentToTSVString());
+ }
+ }
+ }
+
+ } catch (SQLException | IOException e) {
+ e.printStackTrace();
+ }
+
+.. testoutput::
+
+ INT_FIELD1 BOOL_FIELD2 BIGINT_FIELD5
+ 101 true 1000000000300
+ 102 true 100000000030
+ 103 true 10000000003
+
+ResultSet with Array to VectorSchemaRoot Conversion
+---------------------------------------------------
+
+JdbcToArrow accepts configuration through `JdbcToArrowConfig
+<https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrowConfig.html>`_.
+For example, the type of the elements of array columns can be specified by
+``setArraySubTypeByColumnNameMap``.
+
+.. testcode::
+
+ import org.apache.arrow.adapter.jdbc.ArrowVectorIterator;
+ import org.apache.arrow.adapter.jdbc.JdbcFieldInfo;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrow;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowConfig;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowConfigBuilder;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowUtils;
+ import org.apache.arrow.memory.BufferAllocator;
+ import org.apache.arrow.memory.RootAllocator;
+ import org.apache.arrow.vector.VectorSchemaRoot;
+ import org.apache.ibatis.jdbc.ScriptRunner;
+
+ import java.io.BufferedReader;
+ import java.io.FileReader;
+ import java.io.IOException;
+ import java.sql.Connection;
+ import java.sql.DriverManager;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+ import java.sql.Types;
+ import java.util.HashMap;
+
+ try (BufferAllocator allocator = new RootAllocator();
+ Connection connection = DriverManager.getConnection(
+ "jdbc:h2:mem:h2-jdbc-adapter")) {
+ ScriptRunner runnerDDLDML = new ScriptRunner(connection);
+ runnerDDLDML.setLogWriter(null);
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/database/h2-ddl.sql")));
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/database/h2-dml.sql")));
+ JdbcToArrowConfig config = new JdbcToArrowConfigBuilder(allocator,
+ JdbcToArrowUtils.getUtcCalendar())
+ .setArraySubTypeByColumnNameMap(
+ new HashMap<>() {{
+ put("LIST_FIELD19",
+ new JdbcFieldInfo(Types.INTEGER));
+ }}
+ )
+ .build();
+ try (ResultSet resultSet = connection.createStatement().executeQuery(
+ "SELECT int_field1, bool_field2, bigint_field5, char_field16, list_field19 FROM TABLE1");
+ ArrowVectorIterator iterator = JdbcToArrow.sqlToArrowVectorIterator(
+ resultSet, config)) {
+ while (iterator.hasNext()) {
+ try (VectorSchemaRoot root = iterator.next()) {
+ System.out.print(root.contentToTSVString());
+ }
+ }
+ }
+ } catch (SQLException | IOException e) {
+ e.printStackTrace();
+ }
+
+.. testoutput::
+
+ INT_FIELD1 BOOL_FIELD2 BIGINT_FIELD5 CHAR_FIELD16 LIST_FIELD19
+ 101 true 1000000000300 some char text [1,2,3]
+ 102 true 100000000030 some char text [1,2]
+ 103 true 10000000003 some char text [1]
+
+ResultSet per Batches to VectorSchemaRoot Conversion
Review Comment:
```suggestion
Configuring batch size
```
##########
java/source/io.rst:
##########
@@ -461,6 +461,292 @@ Reading Parquet File
Please check :doc:`Dataset <./dataset>`
+Reading JDBC ResultSets
+***********************
+
+The `Arrow Java JDBC module <https://arrow.apache.org/docs/java/jdbc.html>`_
+converts JDBC ResultSets into Arrow VectorSchemaRoots.
+
+ResultSet to VectorSchemaRoot Conversion
+----------------------------------------
+
+The main class to help us to convert ResultSet to VectorSchemaRoot is
+`JdbcToArrow <https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrow.html>`_
+
+.. testcode::
+
+ import org.apache.arrow.adapter.jdbc.ArrowVectorIterator;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrow;
+ import org.apache.arrow.memory.BufferAllocator;
+ import org.apache.arrow.memory.RootAllocator;
+ import org.apache.arrow.vector.VectorSchemaRoot;
+ import org.apache.ibatis.jdbc.ScriptRunner;
+
+ import java.io.BufferedReader;
+ import java.io.FileReader;
+ import java.io.IOException;
+ import java.sql.Connection;
+ import java.sql.DriverManager;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+
+ try (BufferAllocator allocator = new RootAllocator();
+ Connection connection = DriverManager.getConnection(
+ "jdbc:h2:mem:h2-jdbc-adapter")) {
+ ScriptRunner runnerDDLDML = new ScriptRunner(connection);
+ runnerDDLDML.setLogWriter(null);
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/database/h2-ddl.sql")));
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/database/h2-dml.sql")));
+ try (ResultSet resultSet = connection.createStatement().executeQuery(
+ "SELECT int_field1, bool_field2, bigint_field5 FROM TABLE1");
+ ArrowVectorIterator iterator = JdbcToArrow.sqlToArrowVectorIterator(
+ resultSet, allocator)) {
+ while (iterator.hasNext()) {
+ try (VectorSchemaRoot root = iterator.next()) {
+ System.out.print(root.contentToTSVString());
+ }
+ }
+ }
+
Review Comment:
```suggestion
```
##########
java/source/io.rst:
##########
@@ -461,6 +461,292 @@ Reading Parquet File
Please check :doc:`Dataset <./dataset>`
+Reading JDBC ResultSets
Review Comment:
Also, I think the header levels are mixed up again (so a separate file will make that clearer)
##########
java/source/io.rst:
##########
@@ -461,6 +461,292 @@ Reading Parquet File
Please check :doc:`Dataset <./dataset>`
+Reading JDBC ResultSets
+***********************
+
+The `Arrow Java JDBC module <https://arrow.apache.org/docs/java/jdbc.html>`_
+converts JDBC ResultSets into Arrow VectorSchemaRoots.
+
+ResultSet to VectorSchemaRoot Conversion
+----------------------------------------
+
+The main class to help us to convert ResultSet to VectorSchemaRoot is
+`JdbcToArrow <https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrow.html>`_
+
+.. testcode::
+
+ import org.apache.arrow.adapter.jdbc.ArrowVectorIterator;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrow;
+ import org.apache.arrow.memory.BufferAllocator;
+ import org.apache.arrow.memory.RootAllocator;
+ import org.apache.arrow.vector.VectorSchemaRoot;
+ import org.apache.ibatis.jdbc.ScriptRunner;
+
+ import java.io.BufferedReader;
+ import java.io.FileReader;
+ import java.io.IOException;
+ import java.sql.Connection;
+ import java.sql.DriverManager;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+
+ try (BufferAllocator allocator = new RootAllocator();
+ Connection connection = DriverManager.getConnection(
+ "jdbc:h2:mem:h2-jdbc-adapter")) {
+ ScriptRunner runnerDDLDML = new ScriptRunner(connection);
+ runnerDDLDML.setLogWriter(null);
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/database/h2-ddl.sql")));
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/database/h2-dml.sql")));
+ try (ResultSet resultSet = connection.createStatement().executeQuery(
+ "SELECT int_field1, bool_field2, bigint_field5 FROM TABLE1");
+ ArrowVectorIterator iterator = JdbcToArrow.sqlToArrowVectorIterator(
+ resultSet, allocator)) {
+ while (iterator.hasNext()) {
+ try (VectorSchemaRoot root = iterator.next()) {
+ System.out.print(root.contentToTSVString());
+ }
+ }
+ }
+
+ } catch (SQLException | IOException e) {
+ e.printStackTrace();
+ }
+
+.. testoutput::
+
+ INT_FIELD1 BOOL_FIELD2 BIGINT_FIELD5
+ 101 true 1000000000300
+ 102 true 100000000030
+ 103 true 10000000003
+
+ResultSet with Array to VectorSchemaRoot Conversion
+---------------------------------------------------
+
+JdbcToArrow accepts configuration through `JdbcToArrowConfig
+<https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrowConfig.html>`_.
+For example, the type of the elements of array columns can be specified by
+``setArraySubTypeByColumnNameMap``.
+
+.. testcode::
+
+ import org.apache.arrow.adapter.jdbc.ArrowVectorIterator;
+ import org.apache.arrow.adapter.jdbc.JdbcFieldInfo;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrow;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowConfig;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowConfigBuilder;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowUtils;
+ import org.apache.arrow.memory.BufferAllocator;
+ import org.apache.arrow.memory.RootAllocator;
+ import org.apache.arrow.vector.VectorSchemaRoot;
+ import org.apache.ibatis.jdbc.ScriptRunner;
+
+ import java.io.BufferedReader;
+ import java.io.FileReader;
+ import java.io.IOException;
+ import java.sql.Connection;
+ import java.sql.DriverManager;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+ import java.sql.Types;
+ import java.util.HashMap;
+
+ try (BufferAllocator allocator = new RootAllocator();
+ Connection connection = DriverManager.getConnection(
+ "jdbc:h2:mem:h2-jdbc-adapter")) {
+ ScriptRunner runnerDDLDML = new ScriptRunner(connection);
+ runnerDDLDML.setLogWriter(null);
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/database/h2-ddl.sql")));
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/database/h2-dml.sql")));
+ JdbcToArrowConfig config = new JdbcToArrowConfigBuilder(allocator,
+ JdbcToArrowUtils.getUtcCalendar())
+ .setArraySubTypeByColumnNameMap(
+ new HashMap<>() {{
+ put("LIST_FIELD19",
+ new JdbcFieldInfo(Types.INTEGER));
+ }}
+ )
+ .build();
+ try (ResultSet resultSet = connection.createStatement().executeQuery(
+ "SELECT int_field1, bool_field2, bigint_field5, char_field16, list_field19 FROM TABLE1");
+ ArrowVectorIterator iterator = JdbcToArrow.sqlToArrowVectorIterator(
+ resultSet, config)) {
+ while (iterator.hasNext()) {
+ try (VectorSchemaRoot root = iterator.next()) {
+ System.out.print(root.contentToTSVString());
+ }
+ }
+ }
+ } catch (SQLException | IOException e) {
+ e.printStackTrace();
+ }
+
+.. testoutput::
+
+ INT_FIELD1 BOOL_FIELD2 BIGINT_FIELD5 CHAR_FIELD16 LIST_FIELD19
+ 101 true 1000000000300 some char text [1,2,3]
+ 102 true 100000000030 some char text [1,2]
+ 103 true 10000000003 some char text [1]
+
+ResultSet per Batches to VectorSchemaRoot Conversion
+----------------------------------------------------
+
+The maximum rowCount to read each time is configured by default in 1024. This
+can be customized by setting values as needed by ``setTargetBatchSize``.
+
+.. testcode::
+
+ import org.apache.arrow.adapter.jdbc.ArrowVectorIterator;
+ import org.apache.arrow.adapter.jdbc.JdbcFieldInfo;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrow;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowConfig;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowConfigBuilder;
+ import org.apache.arrow.adapter.jdbc.JdbcToArrowUtils;
+ import org.apache.arrow.memory.BufferAllocator;
+ import org.apache.arrow.memory.RootAllocator;
+ import org.apache.arrow.vector.VectorSchemaRoot;
+ import org.apache.ibatis.jdbc.ScriptRunner;
+
+ import java.io.BufferedReader;
+ import java.io.FileReader;
+ import java.io.IOException;
+ import java.sql.Connection;
+ import java.sql.DriverManager;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+ import java.sql.Types;
+ import java.util.HashMap;
+
+ try (BufferAllocator allocator = new RootAllocator();
+ Connection connection = DriverManager.getConnection(
+ "jdbc:h2:mem:h2-jdbc-adapter")) {
+ ScriptRunner runnerDDLDML = new ScriptRunner(connection);
+ runnerDDLDML.setLogWriter(null);
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/database/h2-ddl.sql")));
+ runnerDDLDML.runScript(new BufferedReader(
+ new FileReader("./thirdpartydeps/database/h2-dml.sql")));
+ JdbcToArrowConfig config = new JdbcToArrowConfigBuilder(allocator,
+ JdbcToArrowUtils.getUtcCalendar())
+ .setTargetBatchSize(2)
+ .setArraySubTypeByColumnNameMap(
+ new HashMap<>() {{
+ put("LIST_FIELD19",
+ new JdbcFieldInfo(Types.INTEGER));
+ }}
+ )
+ .build();
+ try (ResultSet resultSet = connection.createStatement().executeQuery(
+ "SELECT int_field1, bool_field2, bigint_field5, char_field16, list_field19 FROM TABLE1");
+ ArrowVectorIterator iterator = JdbcToArrow.sqlToArrowVectorIterator(
+ resultSet, config)) {
+ while (iterator.hasNext()) {
+ try (VectorSchemaRoot root = iterator.next()) {
+ System.out.print(root.contentToTSVString());
+ }
+ }
+ }
+ } catch (SQLException | IOException e) {
+ e.printStackTrace();
+ }
+
+.. testoutput::
+
+ INT_FIELD1 BOOL_FIELD2 BIGINT_FIELD5 CHAR_FIELD16 LIST_FIELD19
+ 101 true 1000000000300 some char text [1,2,3]
+ 102 true 100000000030 some char text [1,2]
+ INT_FIELD1 BOOL_FIELD2 BIGINT_FIELD5 CHAR_FIELD16 LIST_FIELD19
+ 103 true 10000000003 some char text [1]
+
+ResultSet with Precision/Scale to VectorSchemaRoot Conversion
Review Comment:
```suggestion
Configuring numeric (decimal) precision and scale
```
##########
java/ext/javadoctest.py:
##########
@@ -37,6 +37,9 @@ def compile(
"-q",
"dependency:build-classpath",
"-DincludeTypes=jar",
+ "-Dmaven.compiler.source="+os.environ.get('compiler_version', '8'),
+ "-Dmaven.compiler.target="+os.environ.get('compiler_version', '8'),
+ "-Darrow.version="+os.environ.get('arrow_version', '9.0.0'),
Review Comment:
I think we can remove all the changes to the build/CI config right?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-cookbook] lidavidm commented on a diff in pull request #229: [Java] Adding Arrow Java JDBC adapter examples
Posted by GitBox <gi...@apache.org>.
lidavidm commented on code in PR #229:
URL: https://github.com/apache/arrow-cookbook/pull/229#discussion_r942450212
##########
.github/workflows/test_java_osx_cookbook.yml:
##########
@@ -37,6 +37,8 @@ jobs:
matrix:
python-version: [ '3.9' ]
java-version: [ '11', '17', '18' ]
+ compiler-version: ['11']
+ arrow-version: ['9.0.0']
Review Comment:
I still don't understand why we're parameterizing this in CI? It just makes it harder to update Arrow.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-cookbook] lidavidm commented on a diff in pull request #229: [Java] Adding Arrow Java JDBC adapter examples
Posted by GitBox <gi...@apache.org>.
lidavidm commented on code in PR #229:
URL: https://github.com/apache/arrow-cookbook/pull/229#discussion_r942480564
##########
.github/workflows/test_java_osx_cookbook.yml:
##########
@@ -37,6 +37,8 @@ jobs:
matrix:
python-version: [ '3.9' ]
java-version: [ '11', '17', '18' ]
+ compiler-version: ['11']
+ arrow-version: ['9.0.0']
Review Comment:
I don't think we should do this until we decide how we want to approach multiple Cookbook versions.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org