Problem
The pyarrow backend in sql_database always fetches SQLAlchemy rows and rebuilds Arrow tables via row_tuples_to_arrow(). With mssql-python, this creates an unnecessary conversion chain:
ODBC buffers -> mssql-python Row -> SQLAlchemy Row -> Python tuples -> PyArrow buffers
mssql-python exposes cursor.arrow_reader(batch_size) which yields native RecordBatch objects via the Arrow C Data Interface, bypassing the intermediate layers entirely.
Proposed solution
Register a new mssql_arrow backend that:
- Executes the query through SQLAlchemy (compilation, parameters, credentials unchanged)
- Accesses the raw DBAPI cursor from the
CursorResult
- Iterates
cursor.arrow_reader(batch_size=chunk_size) for native Arrow batches
- Falls back to the standard
pyarrow path when arrow_reader is unavailable
An opt-in backend keeps compatibility risk isolated and fits the TableLoader registry from #2736.
Use case
Large SQL Server tables with varbinary(max) columns create substantial memory pressure during extraction. The native path removes the SQLAlchemy/Python tuple layer and one complete Arrow buffer construction.
Related
Problem
The
pyarrowbackend insql_databasealways fetches SQLAlchemy rows and rebuilds Arrow tables viarow_tuples_to_arrow(). Withmssql-python, this creates an unnecessary conversion chain:mssql-pythonexposescursor.arrow_reader(batch_size)which yields nativeRecordBatchobjects via the Arrow C Data Interface, bypassing the intermediate layers entirely.Proposed solution
Register a new
mssql_arrowbackend that:CursorResultcursor.arrow_reader(batch_size=chunk_size)for native Arrow batchespyarrowpath whenarrow_readeris unavailableAn opt-in backend keeps compatibility risk isolated and fits the
TableLoaderregistry from #2736.Use case
Large SQL Server tables with
varbinary(max)columns create substantial memory pressure during extraction. The native path removes the SQLAlchemy/Python tuple layer and one complete Arrow buffer construction.Related
sql_database#2736 (custom TableLoader backends)