API Review: Apache Arrow Support in Bulk Copy

[gargsaumya]

Apache Arrow Support in Bulk Copy

Issue #570

We are working on native Apache Arrow support for bulk copy and would like to align on the public API design before implementation.
The primary question is whether Arrow ingestion should be exposed as:

• A dedicated bulkcopy_arrow() method, or
• An enhancement to the existing bulkcopy() API that accepts Arrow sources in addition to row iterators.

---

Motivation

Today, Cursor.bulkcopy() accepts an iterable of Python tuples or Row objects. Users who already hold data in an Arrow-based format (PyArrow, Polars, DuckDB, etc.) must first materialize that data into Python objects before bulk loading.

Allowing Arrow batches to be passed directly through the Arrow C Data Interface would eliminate that per-row materialization overhead. This is purely additive functionality.

---

Option A: Dedicated bulkcopy_arrow()

cur.bulkcopy_arrow(
    table_name,
    source,
    *,
    batch_size=0,
    timeout=30,
    column_mappings=None,
    keep_identity=False,
    ...
)
# source:
#   pyarrow.Table
#   pyarrow.RecordBatch
#   pyarrow.RecordBatchReader
#   __arrow_c_stream__ producers
#   __arrow_c_array__ producers
#   iterable[RecordBatch]

Pros

• Explicit and easy to discover.
• Clear separation between row-based and Arrow-based ingestion.
• Allows Arrow-specific validation and error messages.

Cons

• Introduces a second bulk-copy API to document, maintain, and evolve.
• Risks duplication if both methods ultimately support the same options and execution path.

---

Option B: Extend bulkcopy() to Accept Arrow Sources

Allow bulkcopy() to accept either a traditional row iterator or an Arrow source.

cur.bulkcopy(table_name, data, ...)
# data:
#   iterable[tuple | Row]
#   Arrow source

Pros

• Keeps a single bulk-copy entry point.
• Minimizes API surface area.
• Existing options (column_mappings, batch_size, keep_identity, etc.) remain unchanged.
• Treats Arrow as simply another supported input format rather than a separate operation.

Cons

• Implicit, type-dependent dispatch. One method runs two different paths (columnar Arrow vs. row tuples) based on input inspection - convenient, but less explicit.
• Raises questions around ambiguous inputs (awkward for lazy generators, see below).

---

Design Considerations

Column Mapping

column_mappings is already optional in bulkcopy() and defaults to ordinal mapping. The same behavior can naturally apply to Arrow sources, so column mapping alone does not justify a separate API.

Input Ambiguity

If we unify the API, we need to distinguish:

iterable[tuple]

from

iterable[RecordBatch]

Potential approaches include:

• Inspecting the first element.
• Supporting Arrow only through concrete Arrow types and Arrow C Data Interface protocols.
• Requiring an explicit opt-in flag. This feels like the main design tradeoff for a unified API.

Performance

Performance should not influence the API choice. Both approaches would use the same underlying implementation and differ only in the public surface area.

---

Open Questions

1. Do you prefer a dedicated bulkcopy_arrow() API or a unified bulkcopy() that also accepts Arrow sources?
2. If we unify the API, is implicit input detection acceptable, or would an explicit opt-in mechanism be preferable?
3. From a discoverability perspective, would you naturally look for bulkcopy_arrow(), or expect bulkcopy() to work directly with Arrow objects?

I'd appreciate feedback before implementation begins.

cc: @saurabh500 @dlevy-msft-sql @sumitmsft

[saurabh500]

Adding @amachanic @jschuba for any feedback.

[saurabh500]

Adding @oscarrobertson who had opened this issue #551