I have released PyAthena 4.1.0, with improvements to CSV result reads and memory use when processing large UNLOAD results in chunks. The release notes contain the complete list of changes and migration notes.

CSV result reads

By default, PandasCursor now downloads the next block of a CSV result from S3 while pandas parses the current one, including when reading in chunks. S3FSCursor also prefetches CSV data, and ArrowCursor downloads CSV data through parallel range requests.

The release benchmark measured the following times for 10 million flat rows, without chunking:

These are medians from three trials per case on r7g.2xlarge hosts. Total time includes Athena execution; client result time starts when the client observes query completion. The two runs used the same instance type and row counts, on different hosts and days.

Prefetching uses additional memory for buffered data. For the same 10 million flat rows with chunksize=10_000, PandasCursor's CSV peak memory rose from 256 MiB to 363 MiB. The S3 read options describe how to select a different block size or cache type.

Memory use for UNLOAD results

CSV result chunking was already supported by both PandasCursor and PolarsCursor before this release. PolarsCursor also supported chunked UNLOAD reads; 4.1.0 improves how it reads those Parquet files to reduce memory use.

With pyarrow installed and no custom Polars read options, it now decodes the files one at a time, prefetches small files, and buffers larger files one row group at a time. Memory use depends on the chunk size, row groups, and buffers.

The release benchmarks measured the following peak memory for PolarsCursor with UNLOAD, 10 million rows, and chunksize=10_000:

These values are sampled process RSS, including the benchmark's imports. For the flat case, client result time increased from 1.2 seconds to 2.2 seconds.

For PandasCursor, this release adds chunked reading of the Parquet files produced by UNLOAD. In the 4.1.0 benchmark, 10 million flat rows peaked at 604 MiB with 10,000-row chunks, compared with 5,376 MiB without chunking. For this case, client result time was 5.1 seconds with chunks, compared with 1.8 seconds without.

For pandas, use unload=True, chunksize, and iter_chunks() to process one DataFrame at a time:

from pyathena import connect
from pyathena.pandas.cursor import PandasCursor

with connect(
    s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
    region_name="us-west-2",
) as connection:
    with connection.cursor(PandasCursor, unload=True, chunksize=10_000) as cursor:
        cursor.execute("SELECT * FROM your_table")
        for chunk in cursor.iter_chunks():
            print(len(chunk))

This example requires the Pandas and Arrow extras. Process each chunk before moving on; collecting the chunks into a single DataFrame holds the whole result in memory. Additional pandas Parquet options can select the whole-result reader; the chunking guide lists the supported columns and dtype_backend settings.

JSON columns in pandas

For CSV results with json columns, PandasCursor now converts the JSON values after pandas parses the CSV. It no longer forces low_memory=False, allowing pandas' C engine to use its default internal chunking.

This changes how pandas options interact with JSON text. With parse_dates naming a json column, pandas attempts date parsing before JSON decoding, so a JSON string such as "2020-01-01" is no longer parsed as a date; cast the value in SQL instead. With engine="python" and NA options such as keep_default_na=True, a JSON string such as "NA" stays a string, while SQL NULL remains None.

Asyncio cleanup and SQLAlchemy names

The native asyncio cursors for rows, pandas, Arrow, Polars, and S3FS now close existing result sets in a worker thread when starting another query, running executemany(), or exiting an async with block. Closing a result stream can wait for an outstanding read, so moving that work off the event loop lets other coroutines continue.

The SQLAlchemy adapter classes in pyathena.aio.sqlalchemy.base have been renamed:

There are no aliases for the old names, so code importing them must be updated. Engines created with awsathena+aio* URLs are unaffected by the rename.

Upgrading

Install this release with:

python -m pip install --upgrade 'PyAthena==4.1.0'

Keep the extras used by the application, for example 'PyAthena[Pandas,Arrow]==4.1.0' for the UNLOAD example above. Python and dependency requirements are unchanged from 4.0.0. For an upgrade from 3.x, read the 4.0.0 release post as well. The full changelog includes the remaining fixes and benchmark tooling changes.