Fetching results does not work with Athena managed query result storage

[pnorman]

Athena has a new option to manage the storage for your queries. This doesn't appear to work with pyathena

When I am using a workgroup with this set, I always get an empty result set for any query
e.g.

@app.command()
def test():
    with pyathena.connect(region_name="eu-north-1",
                          cursor_class=pyathena.pandas.cursor.PandasCursor,
                          work_group="primary") as conn:
        with conn.cursor() as cursor:
            cursor.execute("SELECT 1")
            print(cursor.fetchall())

gives a result of

AWS_PROFILE=osm-service-logs uv run ofastlylog test
[]

When I switch to Customer managed storage I get

AWS_PROFILE=osm-service-logs uv run ofastlylog test
[(np.int64(1),)]

[laughingman7743]

Thanks for reporting this! We've implemented support for managed query result storage in #665.

What was happening

When a workgroup has managed query result storage enabled, GetQueryExecution does not return ResultConfiguration.OutputLocation. File-based cursors (PandasCursor, ArrowCursor, PolarsCursor, S3FSCursor) relied on this to read results from S3 files, so they returned empty results.

The fix

When output_location is None but the query succeeded, PyAthena now falls back to the GetQueryResults API to fetch data. S3 file reading remains the primary path when output_location is available.

Usage note

If you have AWS_ATHENA_S3_STAGING_DIR set as an environment variable, you need to explicitly disable it when connecting to a managed workgroup. Otherwise the API will reject the request because ResultConfiguration and ManagedQueryResultsConfiguration cannot be set together.

import pyathena

# Pass s3_staging_dir="" to skip the env var fallback
conn = pyathena.connect(work_group="primary", s3_staging_dir="")

If you don't have the env var set and only pass work_group, it will work without any extra configuration.

[laughingman7743]

One additional note: the API fallback (GetQueryResults) fetches data at 1000 rows per request, which is significantly slower than reading S3 files directly. For small to moderate result sets this is fine, but for large datasets the performance difference can be substantial.

This is an inherent limitation of managed query result storage — the S3 output files are not accessible to users. If query performance is critical for large result sets, consider using customer-managed storage (the traditional S3 output location).