.. 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:

==================
Arrow JDBC Adapter
==================

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
======================

By default, the adapter will read up to 1024 rows in a batch. This
can be customized via ``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``.

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.

.. 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.math.RoundingMode;
   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));
                       }}
               )
               .setExplicitTypesByColumnName(
                       new HashMap<>() {{
                           put("BIGINT_FIELD5",
                                   new JdbcFieldInfo(Types.DECIMAL, 20, 7));
                       }}
               )
               .setBigDecimalRoundingMode(RoundingMode.UNNECESSARY)
               .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.0000000    some char text      [1,2,3]
   102    true    100000000030.0000000    some char text      [1,2]
   INT_FIELD1    BOOL_FIELD2    BIGINT_FIELD5    CHAR_FIELD16    LIST_FIELD19
   103    true    10000000003.0000000    some char text      [1]