larger than memory queries?

I'm hoping to use Athena with Dask, performing queries which return 10-30 GB and then training some distributed ML algorithms. Any suggestions for concurrent/distributed io for such a task? I've been quite happy with the pandas cursor for smaller local use, following the examples in the pyAthena documentation, but I still have no idea what I am actually doing-- does the pandas cursor do concurrent io, or is it limited to one core?

I apologize in advance if this question belongs on some other forum-- let me know and I'll gladly move the conversation there. Thanks!

I think that it would be nice to create a DaskCursor similar to PandasCusor.
A cursor that returns Dask's Dataframe instead of Pandas' Dataframe.
https://docs.dask.org/en/latest/dataframe.html

Since I have not created any other forums, there is no problem with the issue of GitHub.

@mckeown12

@mckeown12 Isn't better to save the query to parquet in S3 (possibly nicely partitioned) and then read from S3 with dask?

@rpanai

@rpanai I was unaware that there was a way to save query results as parquet, or partition them. Do you have a reference/code example? If that is possible, I would be very interested!

@mckeown12

@mckeown12 Have at examples here

a typical example is

CREATE TABLE new_table
WITH (
 format='PARQUET',
 external_location='s3://my_bucket/',
 partitioned_by = ARRAY['key1']
) AS
SELECT ...

I might need to do something in this direction. Maybe we can add as feature to this project.

I work on large datasets with Athena. CTAS (Create Table AS) queries is the way to go as you can store the results in S3 with the right format.

@rogerganga

@rogerganga can you elaborate more on that? I am running on the same issue as @mckeown12 because I am using PandasCursor and the memory usage keeps growing as it reads the result set from S3 until it raises a MemoryError. So your solution is to create a table in Athena by saving the result as parquet in S3 and then reading it from there using Dask?

@rogerganga

@rogerganga can you elaborate more on that? I am running on the same issue as @mckeown12 because I am using PandasCursor and the memory usage keeps growing as it reads the result set from S3 until it raises a MemoryError. So your solution is to create a table in Athena by saving the result as parquet in S3 and then reading it from there using Dask?

Hey @Lucas-Cerqueira. You are right (I am unsure about Dask). So select Athena query will retrieve the data from S3 and store it in pandas cursor (your local machine) in order to view it later in a pandas dataframe. This is not a good idea for large datasets as your local machine might not have enough memory/storage to store all info.

On the other hand, running a CTAS query will run the query operation in cloud and store it in S3 which can be later viewed via Athena. No data is stored in your local machine.

You can check @rpanai code above or click this link to know more on how to create a CTAS query :)

There were dependencies conflicts in my environment that did not allow me to use Dask. Hence, as an additional note, it is possible to use a combination of CTAS, AVRO, boto:

Embed the SELECT query in a CTAS query using AVRO format.
Retrieve the temporary Athena table S3 location.
Download all the AVRO partitions in parallel (boto).
Read each partition sequentially (fastavro) and trigger the garbage collector.
Use pandas to concatenate the read partition to a CSV

This process is meant to optimise for memory but not storage, and the output goes directly to a CSV. It was not necessary to load the whole result set in memory to perform additional calculations.

I found this process interesting because, for a 25GB CSV, the resulting behaviour for the memory utilisation looked as follows:

Why AVRO?
Parquet does not accept empty columns, so having a result set with an empty column will trigger an exception executing CTAS. AVRO does not have such limitation.

Why not using the S3 stream with fastavro?
The connection could fail and recovering the state is another problem to solve.

References:
https://skeptric.com/export-athena/
https://stackoverflow.com/a/49214514/1705127
https://towardsdatascience.com/python-garbage-collection-article-4a530b0992e3

Related: #644 - Comprehensive benchmark including memory usage with chunk options will help address this use case.

The benchmark of the 4.1.0 release commit (#1157, benchmarks/history/2026-10-13-437276e1.md) measured results larger than a comfortable in-memory DataFrame.

Data: 10 million rows of the PyPI downloads dataset, about 2.9 GB as CSV.
Host: r7g.2xlarge.

With chunksize, PandasCursor and PolarsCursor read them in bounded memory, from CSV and from UNLOAD results.

Reader, 10 million flat rows	No chunks	10,000-row chunks
PandasCursor, CSV	5,132 MiB	363 MiB
PandasCursor, `unload=True`	5,376 MiB	604 MiB
PolarsCursor, CSV	6,854 MiB	874 MiB
PolarsCursor, `unload=True`	3,687 MiB	659 MiB
Wrangler CSV, for reference	7,812 MiB	278 MiB

Nested rows: chunked UNLOAD reads peaked at 588 MiB (PandasCursor) and 642 MiB (PolarsCursor), against up to 19,664 MiB without chunks.
On the parallel I/O question: CSV results are prefetched in the background, and ArrowCursor reads them in parallel range requests (Prefetch CSV results in S3FSCursor and chunked reads, read Arrow CSV in parallel #1144), small UNLOAD files are read in parallel ranges (Generalize the parallel read-ahead and read small UNLOAD files in parallel ranges #1153), and large ones one row group at a time with pre-buffering (Start chunked UNLOAD reads sooner and pre-buffer large Parquet files one row group at a time #1143). With unload=True, 10 million flat rows took about 19 seconds end to end with ArrowCursor, PolarsCursor, or PandasCursor.
For results that must be processed in a distributed framework, unload=True leaves Parquet files in S3, which Dask or Spark can read directly from the UNLOAD location.

RESULTS.md in #1157 now lists these recommendations.

The largest result measured so far is about 2.9 GB of CSV. The chunked peaks barely changed between 1 million and 10 million rows, but the 10–30 GB case of this issue has not been measured. #1162 proposes a run at 100 million rows, about 29 GB, on a host with less memory than the unchunked result. This issue stays open until that run.

The benchmark of PyAthena 4.2.0 (#1288, #1310, benchmarks/history/2026-10-19-9e99caa8.md) measured the case of this issue: a result larger than the host's memory.

Data: 100 million flat rows of the PyPI downloads dataset, about 29 GB as CSV.
Host: r7g.2xlarge, 64 GiB.
Settings: chunksize=100000, three trials.

Reader, 100 million flat rows	Peak RSS	Total time
PandasCursor, CSV	462 MiB	699.9 s
PandasCursor, `unload=True`	986 MiB	38.0 s
PandasCursor, `unload=True`, `dtype_backend="pyarrow"`	978 MiB	34.9 s
PolarsCursor, CSV (Polars 1.44.2 / 2.0.0)	1,001 / 1,016 MiB	373.6 / 364.2 s
PolarsCursor, `unload=True` (Polars 1.44.2 / 2.0.0)	1,051 / 1,045 MiB	35.7 / 35.9 s
S3FSCursor rows, arraysize 1000	327 MiB	3,386.5 s
Wrangler CSV, for reference	390 MiB	1,233.1 s
Wrangler UNLOAD, for reference	1,134 MiB	39.7 s

Chunked reads stayed at about the memory of 10 million rows. The same reads of 10 million rows peaked at 391 to 999 MiB.
One unchunked PandasCursor CSV read still completed, at 49,824 MiB, 98.6% of the harness's memory limit (0.8 of the host's memory), in 704.1 seconds. Without chunks, a slightly larger result would not fit on this host.
With unload=True, Athena writes the result as Parquet files: 8 files of up to 184 MiB for 100 million rows. PandasCursor and PolarsCursor read them in about 36 seconds with chunks. For a distributed framework, Dask or Spark can read the same files from the UNLOAD location.

benchmarks/RESULTS.md recommends chunksize with PandasCursor or PolarsCursor, from CSV or UNLOAD results, for results larger than memory. Closing this issue with #1310.

Navigation Menu

Repository navigation

larger than memory queries? #61

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

larger than memory queries? #61

Description

Activity

laughingman7743 commented on Jan 18, 2019

rpanai commented on Jun 24, 2019

mckeown12 commented on Jun 24, 2019

rpanai commented on Jun 24, 2019

rogergangawork commented on Apr 30, 2020

Lucas-Cerqueira commented on Sep 21, 2021

rogergangawork commented on Sep 22, 2021

ericson-cepeda commented on Oct 6, 2022

laughingman7743 commented on Jan 6, 2026

laughingman7743 commented on Oct 14, 2026

laughingman7743 commented on Oct 19, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions