Arrow JDBC Adapter#

The Arrow JDBC Adapter assists with working with JDBC and Arrow data. Currently, it supports reading JDBC ResultSets into Arrow VectorSchemaRoots.

ResultSet to VectorSchemaRoot Conversion#

This can be accessed via the JdbcToArrow class. The resulting ArrowVectorIterator will convert a ResultSet to Arrow data in batches of rows.

try (ArrowVectorIterator it = JdbcToArrow.sqlToArrowVectorIterator(resultSet, allocator)) {
  while (it.hasNext()) {
    VectorSchemaRoot root = it.next();
    // Consume the root…
  }
}

The batch size and type mapping can both be customized:

JdbcToArrowConfig config = new JdbcToArrowConfigBuilder(allocator, /*calendar=*/null)
    .setReuseVectorSchemaRoot(reuseVectorSchemaRoot)
    .setJdbcToArrowTypeConverter((jdbcFieldInfo -> {
      switch (jdbcFieldInfo.getJdbcType()) {
        case Types.BIGINT:
          // Assume actual value range is SMALLINT
          return new ArrowType.Int(16, true);
        default:
          return null;
      }
    }))
    .build();
try (ArrowVectorIterator iter = JdbcToArrow.sqlToArrowVectorIterator(rs, config)) {
  while (iter.hasNext()) {
    VectorSchemaRoot root = iter.next();
    // Consume the root…
  }
}

The JDBC type can be explicitly specified, which is useful since JDBC drivers can give spurious type information. For example, the Postgres driver has been observed to use Decimal types with scale and precision 0; these cases can be handled by specifying the type explicitly before reading. Also, some JDBC drivers may return BigDecimal values with inconsistent scale. A RoundingMode can be set to handle these cases:

Map<Integer, JdbcFieldInfo> mapping = new HashMap<>();
mapping.put(1, new JdbcFieldInfo(Types.DECIMAL, 20, 7));
JdbcToArrowConfig config = new JdbcToArrowConfigBuilder(allocator, /*calendar=*/null)
    .setBigDecimalRoundingMode(RoundingMode.UNNECESSARY)
    .setExplicitTypesByColumnIndex(mapping)
    .build();
try (ArrowVectorIterator iter = JdbcToArrow.sqlToArrowVectorIterator(rs, config)) {
  while (iter.hasNext()) {
    VectorSchemaRoot root = iter.next();
    // Consume the root…
  }
}

The mapping from JDBC type to Arrow type can be overridden via the JdbcToArrowConfig, but it is not possible to customize the conversion from JDBC value to Arrow value itself, nor is it possible to define a conversion for an unsupported type.

Type Mapping#

The JDBC to Arrow type mapping can be obtained at runtime from JdbcToArrowUtils.getArrowTypeFromJdbcType.

JDBC Type	Arrow Type	Notes
ARRAY	List	(1)
BIGINT	Int64
BINARY	Binary
BIT	Bool
BLOB	Binary
BOOLEAN	Bool
CHAR	Utf8
CLOB	Utf8
DATE	Date32
DECIMAL	Decimal128	(2)
DOUBLE	Double
FLOAT	Float32
INTEGER	Int32
LONGVARBINARY	Binary
LONGNVARCHAR	Utf8
LONGVARCHAR	Utf8
NCHAR	Utf8
NULL	Null
NUMERIC	Decimal128
NVARCHAR	Utf8
REAL	Float32
SMALLINT	Int16
STRUCT	Struct	(3)
TIME	Time32[ms]
TIMESTAMP	Timestamp[ms]	(4)
TINYINT	Int8
VARBINARY	Binary
VARCHAR	Utf8

(1) The list value type must be explicitly configured and cannot be inferred. Use setArraySubTypeByColumnIndexMap or setArraySubTypeByColumnNameMap.
(2) By default, the scale of decimal values must match the scale in the type exactly; precision is allowed to be any value greater or equal to the type precision. If there is a mismatch, by default, an exception will be thrown. This can be configured by setting a different RoundingMode with setBigDecimalRoundingMode.
(3) Not fully supported: while the type conversion is defined, the value conversion is not. See ARROW-17006.
(4) If a Calendar is provided, then the timestamp will have the timezone of the calendar, else it will be a timestamp without timezone.

VectorSchemaRoot to PreparedStatement Parameter Conversion#

The adapter can bind rows of Arrow data from a VectorSchemaRoot to parameters of a JDBC PreparedStatement. This can be accessed via the JdbcParameterBinder class. Each call to next() will bind parameters from the next row of data, and then the application can execute the statement, call addBatch(), etc. as desired. Null values will lead to a setNull call with an appropriate JDBC type code (listed below).

final JdbcParameterBinder binder =
    JdbcParameterBinder.builder(statement, root).bindAll().build();
while (binder.next()) {
    statement.executeUpdate();
}
// Use a VectorLoader to update the root
binder.reset();
while (binder.next()) {
    statement.executeUpdate();
}

The mapping of vectors to parameters, the JDBC type code used by the converters, and the type conversions themselves can all be customized:

final JdbcParameterBinder binder =
    JdbcParameterBinder.builder(statement, root)
        .bind(/*parameterIndex*/2, /*columnIndex*/0)
        .bind(/*parameterIndex*/1, customColumnBinderInstance)
        .build();

Type Mapping#

The Arrow to JDBC type mapping can be obtained at runtime via a method on ColumnBinder.

Arrow Type	JDBC Type	Notes
Binary	VARBINARY (setBytes)
Bool	BOOLEAN (setBoolean)
Date32	DATE (setDate)
Date64	DATE (setDate)
Decimal128	DECIMAL (setBigDecimal)
Decimal256	DECIMAL (setBigDecimal)
FixedSizeBinary	BINARY (setBytes)
Float32	REAL (setFloat)
Int8	TINYINT (setByte)
Int16	SMALLINT (setShort)
Int32	INTEGER (setInt)
Int64	BIGINT (setLong)
LargeBinary	LONGVARBINARY (setBytes)
LargeUtf8	LONGVARCHAR (setString)	(1)
Time[s]	TIME (setTime)
Time[ms]	TIME (setTime)
Time[us]	TIME (setTime)
Time[ns]	TIME (setTime)
Timestamp[s]	TIMESTAMP (setTimestamp)	(2)
Timestamp[ms]	TIMESTAMP (setTimestamp)	(2)
Timestamp[us]	TIMESTAMP (setTimestamp)	(2)
Timestamp[ns]	TIMESTAMP (setTimestamp)	(2)
Utf8	VARCHAR (setString)

(1) Strings longer than Integer.MAX_VALUE bytes (the maximum length of a Java byte[]) will cause a runtime exception.
(2) If the timestamp has a timezone, the JDBC type defaults to TIMESTAMP_WITH_TIMEZONE. If the timestamp has no timezone, technically there is not a correct conversion from Arrow value to JDBC value, because a JDBC Timestamp is in UTC, and we have no timezone information. In this case, the default binder will call setTimestamp(int, Timestamp), which will lead to the driver using the “default timezone” (that of the Java VM).