Arrow and Storage¶
SQLSpec bridges relational and analytical workloads with first-class Apache Arrow query results (select_to_arrow), bulk ingestion from Arrow tables, object storage, and Python records (load_from_arrow, load_from_storage, load_from_records), streaming RecordBatch pipelines (read_arrow_batches / write_arrow_batches), pluggable object storage (StorageRegistry over local paths, obstore, and fsspec), and schema-aware JSONL table fixtures.
Required Packages¶
Arrow operations require pyarrow. Some adapters, including DuckDB, Snowflake,
ADBC, and Arrow ODBC, include it in their extras. For other adapters, install
pyarrow alongside the driver extra.
| Feature | Packages |
|---|---|
| Polars conversion | sqlspec[polars] installs polars and pyarrow |
| Pandas conversion | sqlspec[pandas] installs pandas and pyarrow |
| Object storage via obstore | sqlspec[obstore] |
| Filesystems via fsspec | sqlspec[fsspec], plus the filesystem's protocol package when required |
Local file storage needs no storage-backend extra. Arrow file formats still
require pyarrow. Add only the backend and conversion packages you use.
flowchart LR
DB[("Database Session")] <-->|"select_to_arrow / load_from_arrow"| Arrow["Arrow Table / RecordBatch Stream"]
Arrow <-->|"Sync / AsyncStoragePipeline"| Store["StorageRegistry (local / obstore / fsspec)"]
Arrow -->|"Zero-Copy"| DF["Polars / Pandas / PyArrow"]
Querying Directly to Apache Arrow, Polars, and Pandas¶
Call session.select_to_arrow(...) (or session.fetch_to_arrow(...); await on async drivers) to return an ArrowResult wrapping a pyarrow.Table or streaming RecordBatchReader:
from sqlspec import SQLSpec
from sqlspec.adapters.sqlite import SqliteConfig
spec = SQLSpec()
config = spec.add_config(
SqliteConfig(connection_config={"database": ":memory:"})
)
with spec.provide_session(config) as session:
session.execute("CREATE TABLE metrics (host TEXT, cpu_pct REAL)")
session.execute_many(
"INSERT INTO metrics VALUES (?, ?)", [("web-1", 41.5), ("web-2", 68.0)]
)
arrow_result = session.select_to_arrow(
"SELECT host, cpu_pct FROM metrics ORDER BY host"
)
arrow_table = arrow_result.data
Adapters with native Arrow support include DuckDB, ADBC, Arrow ODBC, BigQuery's Storage Read API, and Oracle's DataFrame APIs. These paths avoid intermediate Python row dictionaries. Other DB-API adapters convert rows when native_only=False (the default).
Convert an ArrowResult to DataFrame libraries in one call:
arrow_result.data: Underlyingpyarrow.Table(whenreturn_format="table") orRecordBatchReader(return_format="reader").arrow_result.to_polars(): Converts to apolars.DataFrame(requires thepolarsextra).arrow_result.to_pandas(): Converts to apandas.DataFrame(requires thepandasextra).
Empty Results and Explicit Schemas¶
Row-based drivers keep column names when a query has no rows. Without adapter type hints, each empty column has the Arrow null type. Set arrow_schema= to choose each column's type. This example requires PyArrow:
import pyarrow as pa
from sqlspec import SQLSpec
from sqlspec.adapters.sqlite import SqliteConfig
spec = SQLSpec()
config = spec.add_config(SqliteConfig(connection_config={"database": ":memory:"}))
schema = pa.schema([("host", pa.string()), ("cpu_pct", pa.float64())])
try:
with spec.provide_session(config) as session:
session.execute("CREATE TABLE metrics (host TEXT, cpu_pct REAL)")
query = "SELECT host, cpu_pct FROM metrics WHERE 1 = 0"
empty_result = session.select_to_arrow(query, arrow_schema=schema)
empty_table = empty_result.data
finally:
config.close_pool()
The result has zero rows and the schema host: string, cpu_pct: double. Empty table, batch, and reader results retain this schema. Empty batches results are an empty list, so they cannot carry a schema.
Bulk Loading from Arrow, Storage, and Python Records¶
Every sync and async driver exposes three symmetric bulk-ingestion entry points that route through the adapter's fastest native bulk path (PGCOPY on PostgreSQL, BulkCopy on SQL Server, Arrow C-stream registration on DuckDB/ADBC, DBMS_CLOUD on Oracle, and staged upserts across dialects) and return a StorageBridgeJob:
session.load_from_arrow(table, arrow_data, ...): Ingests apyarrow.Table,RecordBatch, orRecordBatchReaderintotable.session.load_from_storage(table, source, ...): Streams Parquet, Arrow IPC, CSV, or JSONL shards (including glob patterns like"s3://bucket/events/*.parquet") intotable.session.load_from_records(table, records, ...): Normalizes a sequence of Python dictionaries or tuples into an Arrow table (inferring or applyingarrow_schema/ target table metadata) and delegates toload_from_arrow:
import pyarrow as pa
from sqlspec import SQLSpec
from sqlspec.adapters.sqlite import SqliteConfig
spec = SQLSpec()
config = spec.add_config(
SqliteConfig(connection_config={"database": ":memory:"})
)
with spec.provide_session(config) as session:
session.execute(
"CREATE TABLE metrics (host TEXT PRIMARY KEY, cpu_pct REAL)"
)
job = session.load_from_records(
"metrics",
[{"host": "web-1", "cpu_pct": 41.5}, {"host": "web-2", "cpu_pct": 68.0}],
conflict_keys=["host"],
)
arrow_batch = pa.table({"host": ["web-1"], "cpu_pct": [52.0]})
upsert_job = session.load_from_arrow(
"metrics", arrow_batch, conflict_keys=["host"]
)
Unified Object Storage and StorageRegistry¶
storage_registry resolves local paths and cloud URIs (s3://, gs://, az://, file://) behind a consistent ObjectStoreProtocol backed by local, obstore (obstore extra), or fsspec (fsspec extra). In SyncStoragePipeline and AsyncStoragePipeline, use write_rows(..., format_hint="jsonl") for row dictionaries ("json" or "jsonl"), write_arrow(..., format_hint="parquet") for columnar Arrow tables ("parquet", "arrow-ipc", or "csv"), and unpack table, telemetry = pipeline.read_arrow(...):
from pathlib import Path
import tempfile
import pyarrow as pa
from sqlspec.storage import SyncStoragePipeline, storage_registry
with tempfile.TemporaryDirectory() as tmp_dir:
archive_dir = Path(tmp_dir)
storage_registry.register_alias(
"exports", archive_dir.as_uri(), backend="local"
)
pipeline = SyncStoragePipeline(registry=storage_registry)
rows = [
{"host": "web-1", "cpu_pct": 41.5},
{"host": "web-2", "cpu_pct": 68.0},
]
pipeline.write_rows(
rows, "alias://exports/metrics.jsonl", format_hint="jsonl"
)
arrow_table = pa.Table.from_pylist(rows)
pipeline.write_arrow(
arrow_table,
"alias://exports/metrics.parquet",
format_hint="parquet",
)
table, telemetry = pipeline.read_arrow(
"alias://exports/metrics.parquet", file_format="parquet"
)
You can also write an ArrowResult directly to any storage destination via arrow_result.write_to_storage_sync("alias://exports/metrics.parquet", write_options={"row_group_size": 10_000}, overwrite=True) (or await arrow_result.write_to_storage_async(...)). file:// URIs and bare local paths resolve to LocalStore (backend="local") by default unless backend="obstore" or backend="fsspec" is explicitly requested. Across all storage backends (LocalStore, ObStoreBackend, and FSSpecBackend), write_bytes_sync, write_text_sync, write_bytes_async, and write_text_async accept overwrite=False for atomic create-only writes, raising FileExistsInStorageError if the target object already exists.
Local backends (LocalStore, FSSpecBackend on the file protocol, and ObStoreBackend on file://) never delete their root directory, and delete_sync(path, recursive=True) (or delete_async) is required to remove a directory; deleting a single file needs no flag. Storage logs and errors omit URI credentials, query strings, and fragments.
Streaming RecordBatch Coercion and Schema Validation¶
For datasets larger than available RAM, SyncStoragePipeline and AsyncStoragePipeline expose read_arrow_batches(...), write_arrow_batches(...), and write_arrow_batches_async(...), and sqlspec.utils.arrow_helpers provides coerce_arrow_batches(...):
- Bounded-Memory Streaming:
read_arrow_batchesyieldspyarrow.RecordBatchchunks lazily from Parquet, Arrow IPC, or JSONL sources without materializing a fullpyarrow.Table. - Single-Pass Target Schema Coercion: When
target_schema(or target tableColumnMetadata) is supplied toload_from_arroworload_from_storage, SQLSpec validates and coerces incoming batches (DECIMAL,TIMESTAMP,UUID, binary, andFIXED_SIZE_LISTvector columns) oneRecordBatchat a time before streaming to the database driver.
Streaming Arrow Exports and Conditional Overwrite¶
write_arrow, write_arrow_batches, write_arrow_batches_async, and ArrowResult.write_to_storage_sync / write_to_storage_async stream "parquet", "arrow-ipc", and "csv" payloads directly to backends that satisfy supports_direct_stream_write(backend) and backend.supports_direct_arrow_write(file_format, overwrite=overwrite) (LocalStore, ObStoreBackend, and FSSpecBackend). Note that "csv" exports accept flat scalar columns only and cannot serialize list, struct, map, or vector columns.
import asyncio
from pathlib import Path
import tempfile
import pyarrow as pa
from sqlspec import SQLSpec
from sqlspec.adapters.sqlite import SqliteConfig
from sqlspec.exceptions import FileExistsInStorageError
from sqlspec.storage import (
AsyncStoragePipeline,
SyncStoragePipeline,
storage_registry,
)
spec = SQLSpec()
config = spec.add_config(SqliteConfig(connection_config={"database": ":memory:"}))
with (
tempfile.TemporaryDirectory() as tmp_dir,
spec.provide_session(config) as session,
):
export_dir = Path(tmp_dir)
storage_registry.register_alias(
"stream_exports", export_dir.as_uri(), backend="local"
)
session.execute(
"CREATE TABLE events (id INTEGER PRIMARY KEY, host TEXT, latency_ms REAL)"
)
session.execute_many(
"INSERT INTO events (id, host, latency_ms) VALUES (?, ?, ?)",
[(1, "web-1", 12.4), (2, "web-2", 18.9), (3, "web-3", 9.1)],
)
arrow_result = session.select_to_arrow(
"SELECT host, latency_ms FROM events ORDER BY id"
)
arrow_result.write_to_storage_sync(
"alias://stream_exports/from_result.parquet",
format_hint="parquet",
compression="zstd",
write_options={"row_group_size": 2},
overwrite=True,
)
sync_pipeline = SyncStoragePipeline(registry=storage_registry)
source_table = pa.table({"host": ["web-1", "web-2"], "cpu_pct": [41.5, 68.0]})
batches = source_table.to_batches(max_chunksize=1)
sync_pipeline.write_arrow_batches(
"alias://stream_exports/batches.parquet",
batches,
file_format="parquet",
compression="zstd",
write_options={"row_group_size": 1},
overwrite=False,
)
try:
sync_pipeline.write_arrow(
pa.table({"host": ["web-3"], "cpu_pct": [50.0]}),
"alias://stream_exports/batches.parquet",
format_hint="parquet",
write_options={"row_group_size": 1},
overwrite=False,
)
except FileExistsInStorageError:
pass
async def write_async() -> None:
async_pipeline = AsyncStoragePipeline(registry=storage_registry)
async_table = pa.table({"host": ["web-1"], "cpu_pct": [41.5]})
async_batches = async_table.to_batches()
await async_pipeline.write_arrow_batches_async(
"alias://stream_exports/async_batches.parquet",
async_batches,
file_format="parquet",
compression="zstd",
write_options={"row_group_size": 1},
overwrite=True,
)
asyncio.run(write_async())
During direct multipart and file streaming, peak in-flight memory stays bounded to roughly four staging chunks plus the largest single Arrow write. If an upstream batch iterator raises an exception or an async task receives asyncio.CancelledError, SQLSpec poisons the active writer stream so PyArrow cannot flush a trailing Parquet footer and aborts the incomplete upload or staged file without modifying any pre-existing destination object.
When overwrite=False, each built-in backend enforces atomic create-only semantics and raises FileExistsInStorageError when the target already exists:
LocalStore: Streams into a sibling temporary file and publishes via an exclusive hard link (os.link).ObStoreBackend: Streams into a hidden.sqlspec-stage-*object and publishes with an atomic conditional copy (copy_if_not_exists). S3 stores requirecopy_if_not_existsto be configured instorage_options(such as"header:If-None-Match:*"on S3-compatible stores that support conditional copy); whencopy_if_not_existsis unavailable on S3,ObStoreBackendfalls back to a single-requestmode="create"put. If a process terminates abruptly mid-upload, hidden.sqlspec-stage-*objects can remain in the bucket and can be expired automatically with a bucket lifecycle rule on the.sqlspec-stage-prefix.FSSpecBackend: Streams create-only writes on S3, GCS, Azure, and local filesystems, and uses a single exclusive write (open(..., "xb")) on other filesystems.
Streaming Database Exports and Type Settlement¶
session.select_to_storage(...) streams query results in batches across every adapter with a client-side export path, including Db2 and mssql-python. Pass return_format="table" when you explicitly want select_to_storage to materialize a full pyarrow.Table before writing.
On row-streaming adapters, SQLSpec inspects the first batch to pin the Arrow schema:
- Settled in One Batch: Flat scalar columns (strings, floats, booleans, dates, timestamps, binary values) and floating-point vector lists settle on the first batch and stream directly with bounded memory.
- Full-Result Read-Ahead for Unsettled Types: Encountering a
NULL,Decimal, struct, or map value at any nesting depth, or an integer inside a list, causes SQLSpec to read the full result into columnar memory first so the column's Arrow type is settled accurately. SQLite integer columns and Oracle row-stream integer columns also trigger read-ahead because later rows can hold floating-point values. - Avoiding Read-Ahead: Drivers that report exact decimal precision and scale (
PyMySQL,aiomysql,asyncmy, andpsycopg) settle decimal columns on the first batch, and safe declared column types settle all-NULLcolumns without read-ahead. Passing an explicitarrow_schema=...always avoids read-ahead for every column. - Columns Unbounded by Default: Without
arrow_schema, struct and JSON columns, integer array columns, all-NULLcolumns with no safe declared type, decimals without driver precision (including every decimal onmysql-connector,asyncpg,psqlpy, andpymssql), and SQLite and Oracle row-stream integer columns remain read-ahead columns. - Observability and Mid-Stream Type Changes: Whenever read-ahead runs, SQLSpec logs
storage.export.read_aheadand records the column names injob.telemetry["extra"]["read_ahead_columns"]. If a pinned column changes type family in a later batch, PyArrow raises an Arrow type error, the in-flight stream aborts, and nothing is published at the destination.
Custom Multipart Backends¶
Custom storage backends opt into zero-buffering Arrow exports by implementing DirectStreamWriteProtocol (supports_direct_arrow_write, open_write_stream_sync, and open_write_stream_async). Verify protocol conformance at runtime with supports_direct_stream_write and stream Arrow payloads with encode_arrow_to_sink or encode_arrow_to_async_sink:
from collections.abc import AsyncIterator, Iterator
from contextlib import asynccontextmanager, contextmanager
from pathlib import Path
from typing import Any
import pyarrow as pa
from sqlspec.protocols import AsyncWritableBytesSink, WritableBytesSink
from sqlspec.storage import StorageFormat, encode_arrow_to_sink
from sqlspec.utils.type_guards import supports_direct_stream_write
class InMemoryMultipartStore:
"""Minimal custom store demonstrating DirectStreamWriteProtocol."""
def __init__(self) -> None:
self.objects: dict[str, bytes] = {}
def supports_direct_arrow_write(
self, file_format: StorageFormat, *, overwrite: bool = True
) -> bool:
return overwrite and file_format in {"parquet", "arrow-ipc", "csv"}
@contextmanager
def open_write_stream_sync(
self, path: str | Path, *, overwrite: bool = True, **kwargs: Any
) -> Iterator[WritableBytesSink]:
_ = (overwrite, kwargs)
parts: list[bytes] = []
class _PartSink:
def write(self, data: bytes, /) -> int:
parts.append(bytes(data))
return len(data)
yield _PartSink()
self.objects[str(path)] = b"".join(parts)
@asynccontextmanager
async def open_write_stream_async(
self, path: str | Path, *, overwrite: bool = True, **kwargs: Any
) -> AsyncIterator[AsyncWritableBytesSink]:
_ = (overwrite, kwargs)
parts: list[bytes] = []
class _AsyncPartSink:
async def write(self, data: bytes, /) -> int:
parts.append(bytes(data))
return len(data)
yield _AsyncPartSink()
self.objects[str(path)] = b"".join(parts)
store = InMemoryMultipartStore()
assert supports_direct_stream_write(store)
with store.open_write_stream_sync("metrics.parquet") as sink:
bytes_written, rows_written = encode_arrow_to_sink(
pa.table({"host": ["web-1"], "cpu_pct": [41.5]}),
sink,
format_hint="parquet",
)
Schema-Aware Table Fixtures¶
sqlspec.utils.fixtures exports and loads per-table .json, .json.gz, .jsonl, and .jsonl.gz files while introspecting live column types (UUID, ISO timestamps, Decimal, base64 bytes, JSON/JSONB, identity columns, and sequence resyncing) via driver.data_dictionary:
from pathlib import Path
import tempfile
from sqlspec import SQLSpec
from sqlspec.adapters.sqlite import SqliteConfig
from sqlspec.utils.fixtures import (
export_table_fixtures_sync,
load_table_fixtures_sync,
)
spec = SQLSpec()
config = spec.add_config(
SqliteConfig(connection_config={"database": ":memory:"})
)
with (
tempfile.TemporaryDirectory() as tmp_dir,
spec.provide_session(config) as session,
):
fixtures_dir = Path(tmp_dir)
session.execute(
"CREATE TABLE roles (id INTEGER PRIMARY KEY, slug TEXT UNIQUE)"
)
session.execute("INSERT INTO roles (slug) VALUES ('admin'), ('reader')")
export_table_fixtures_sync(
session, fixtures_dir, ["roles"], jsonl=True, compress=False
)
counts = load_table_fixtures_sync(
session,
fixtures_dir,
tables=["roles"],
conflict_keys={"roles": "slug"},
exclude_update_columns={"roles": ["id"]},
)
Recap¶
- Use
select_to_arrow,select_to_storage, andload_from_arrow/load_from_records/load_from_storagefor columnar queries, streaming storage exports, and native bulk loads. - Use
pipeline.write_rows(..., format_hint="jsonl")for JSON/JSONL row payloads,pipeline.write_arrow(...)andpipeline.write_arrow_batches(...)(withwrite_options=...andoverwrite=Falsewhen create-only protection is needed) for streaming Parquet/Arrow IPC/CSV exports, and unpacktable, telemetry = pipeline.read_arrow(...). - Register named storage buckets on
storage_registry(s3://,gs://,az://, orbackend="local") or implementDirectStreamWriteProtocolon custom backends so application code addresses portablealias://...destinations.