Usage

Basic usage

from pyathena import connect

cursor = connect(s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
                 region_name="us-west-2").cursor()
cursor.execute("SELECT * FROM one_row")
print(cursor.description)
print(cursor.fetchall())

Closing a cursor, for example at the end of a with block, also closes its result set. After that, execute(), executemany(), the fetch methods, and the other methods of the cursor raise ProgrammingError, as DB API 2.0 requires. The query_id property keeps the ID of the last query. A closed result set, such as one that an AsyncCursor future returned, also raises ProgrammingError from its fetch methods and from as_pandas(), as_arrow(), as_polars(), and iter_chunks(). An iterator that iter_chunks() returned before the close raises ProgrammingError from its next read.

Closing an AsyncCursor, an AsyncDictCursor, or the AsyncPandasCursor, AsyncArrowCursor, AsyncPolarsCursor, or AsyncS3FSCursor stops the futures of execute(), description(), and poll() that still wait for their query. They raise ProgrammingError at their next status check, also when that check finds the query finished, without reading the result, and the query itself keeps running. The futures of cancel() and query_execution() still run. A future of execute() that is already reading its result resolves with a closed result set, and one that is just finishing can resolve with either an open or a closed result set. A result set whose future resolved before the close belongs to the caller, and closing the cursor leaves it open; close it yourself.

Keyword arguments in PyAthena 4.0

Starting with PyAthena 4.0, connection settings and cursor constructor settings must be passed as keyword arguments. This applies to connect(), aio_connect(), connection classes, and all synchronous, thread-based asynchronous, and native asyncio cursor classes. Passing these settings positionally raises TypeError.

Replace positional connection settings such as connect("s3://YOUR_S3_BUCKET/path/to/", "us-west-2") with:

connection = connect(
    s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
    region_name="us-west-2",
)

For execute(), the SQL operation and query parameters can still be passed positionally. All subsequent settings require keywords:

cursor = connection.cursor()
cursor.execute("SELECT %(value)s", {"value": 1}, work_group="YOUR_WORK_GROUP")

The cursor class can still be selected positionally with connection.cursor(DictCursor).

Unknown keyword arguments raise TypeError in cursor constructors and in execute() instead of being ignored. The connection’s cursor_kwargs are passed to every cursor that it creates, so the requested cursor class must accept them. A cursor subclass that receives settings of its own must not pass them on to the base class constructor. The pandas and Polars cursors pass the execute() keyword arguments that they do not use themselves to the reader, such as pandas.read_csv(). The reader rejects an unknown name when it reads the query results, which PyAthena raises as OperationalError; the name is not checked when there are no results to read.

Worker settings in PyAthena 4.0

The pandas and Polars cursors take, as s3_max_workers, the size of the request pool of the S3 filesystem that reads the result files; a file reads at most that many ranges at once. It can be set in the cursor constructor and overridden for one query in execute(). The default is None, which takes the max_pool_connections of the connection’s S3 client: 10, unless set with s3_config. Polars uses it for CSV results read without chunksize, and for UNLOAD results read in chunks through PyArrow without read options; its other readers do not use this setting.

The thread-pool cursors, such as AsyncCursor, AsyncPandasCursor, and AsyncPolarsCursor, keep max_workers for the size of the thread pool that waits for queries and collects their results.

from pyathena.pandas.async_cursor import AsyncPandasCursor

cursor = connection.cursor(AsyncPandasCursor, max_workers=4, s3_max_workers=2)
query_id, future = cursor.execute("SELECT 1", s3_max_workers=3)

Before PyAthena 4.0, max_workers set the S3 read workers of PandasCursor, PolarsCursor, AioPandasCursor, and AioPolarsCursor, and of execute() in the pandas and Polars cursors. Replace it with s3_max_workers in those places. Passing max_workers to those four constructors raises TypeError. Passing it to execute() raises TypeError after the query has run; with AsyncPandasCursor and AsyncPolarsCursor, the returned future raises it. AsyncPolarsCursor used its max_workers for both the thread pool and the S3 read workers; pass both arguments to keep the former values.

Environment fallbacks in PyAthena 4.0

For connection settings, s3_staging_dir and work_group use the same fallback rules:

Argument

Resolved setting

Omitted or None

Read the corresponding environment variable.

""

Use None and ignore the corresponding environment variable.

Nonempty string

Use the supplied value.

The environment variables are AWS_ATHENA_S3_STAGING_DIR and AWS_ATHENA_WORK_GROUP. These rules apply to connect(), aio_connect(), Connection, and AioConnection. At least one resolved setting must be nonempty; otherwise connection creation raises ProgrammingError.

This is a breaking change for work_group="", which previously read AWS_ATHENA_WORK_GROUP. Omit work_group or pass None to retain that fallback. The behavior of s3_staging_dir="" is unchanged, including its use with managed query result storage.

Managed query result storage

When using a workgroup with managed query result storage enabled, you don’t need to specify an S3 staging directory.

from pyathena import connect

cursor = connect(work_group="YOUR_MANAGED_WORK_GROUP",
                 region_name="us-west-2").cursor()
cursor.execute("SELECT * FROM one_row")
print(cursor.fetchall())

If the AWS_ATHENA_S3_STAGING_DIR environment variable is set, pass s3_staging_dir="" to explicitly disable the fallback. Otherwise the API will reject the request because ResultConfiguration and ManagedQueryResultsConfiguration cannot be set together.

cursor = connect(work_group="YOUR_MANAGED_WORK_GROUP",
                 s3_staging_dir="",
                 region_name="us-west-2").cursor()

Note

With managed query result storage, query results are retrieved via the GetQueryResults API (1000 rows per request) instead of reading S3 files directly. This may be slower for large result sets. For large datasets, consider using customer-managed storage or the UNLOAD statement. The pandas, Arrow, and Polars cursors read these rows as they read a CSV result file, with the same types and converters, but not in chunks.

Cursor iteration

from pyathena import connect

cursor = connect(s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
                 region_name="us-west-2").cursor()
cursor.execute("SELECT * FROM many_rows LIMIT 10")
for row in cursor:
    print(row)

After a fetch raises an exception, including KeyboardInterrupt, later fetches of the same result raise ProgrammingError. An invalid argument, such as a fetchmany() size that is not an integer, raises its error without that effect. Execute the query again to read its result.

As pyathena.threadsafety = 2 declares, threads can share connections, but not cursors. Calling the methods of one cursor or its result set from several threads, including close() and cancel(), is not supported.

Query with parameters

Supported DB API paramstyle is only PyFormat. PyFormat only supports named placeholders with old % operator style and parameters specify dictionary format.

from pyathena import connect

cursor = connect(s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
                 region_name="us-west-2").cursor()
cursor.execute("""
               SELECT col_string FROM one_row_complex
               WHERE col_string = %(param)s
               """,
               {"param": "a string"})
print(cursor.fetchall())

if % character is contained in your query, it must be escaped with %% like the following:

SELECT col_string FROM one_row_complex
WHERE col_string = %(param)s OR col_string LIKE 'a%%'

A datetime parameter is rendered as a TIMESTAMP literal that keeps its microseconds. A value without a sub-millisecond part is rendered with three fractional digits as a timestamp(3) literal, and any other value with six as a timestamp(6) literal.

from datetime import datetime

cursor.execute("SELECT %(a)s, %(b)s",
               {"a": datetime(2024, 1, 1, 12, 0, 0, 789000),
                "b": datetime(2024, 1, 1, 12, 0, 0, 789012)})
# SELECT TIMESTAMP '2024-01-01 12:00:00.789', TIMESTAMP '2024-01-01 12:00:00.789012'

Athena pads a timestamp(3) value with zeros when it meets a timestamp(6) value, and truncates a value written to a lower precision. Iceberg TIMESTAMP columns store microseconds, while Hive TIMESTAMP columns store milliseconds and truncate sub-millisecond digits on write. A timestamp(6) value with a sub-millisecond part therefore does not equal the value stored in a Hive column. CREATE TABLE AS SELECT into a Hive table and UNLOAD with Parquet output, including the UNLOAD that cursors run with unload=True, reject a timestamp(6) output column with NOT_SUPPORTED: Incorrect timestamp precision for timestamp(6); the value can still be used in a WHERE clause. Cast such a column to TIMESTAMP(3) to write it with millisecond precision:

SELECT CAST(%(param)s AS TIMESTAMP(3)) AS col_timestamp

Use parameterized queries

If you want to use Athena’s parameterized queries, you can do so by changing the paramstyle to qmark as follows.

import pyathena
from pyathena import connect

pyathena.paramstyle = "qmark"
cursor = connect(s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
                 region_name="us-west-2").cursor()
cursor.execute("""
               SELECT col_string FROM one_row_complex
               WHERE col_string = ?
               """,
               ["'a string'"])
print(cursor.fetchall())

You can also specify the paramstyle using the execute method when executing a query.

from pyathena import connect

cursor = connect(s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
                 region_name="us-west-2").cursor()
cursor.execute("""
               SELECT col_string FROM one_row_complex
               WHERE col_string = ?
               """,
               ["'a string'"],
               paramstyle="qmark")
print(cursor.fetchall())

You can find more information about the considerations and limitations of parameterized queries in the official documentation.

Execution options

The execute() method of every SQL cursor (Cursor, AsyncCursor, the aio cursors, and their pandas, Arrow, Polars, and S3FS variants) accepts the same set of shared keyword arguments, such as work_group, s3_staging_dir, cache_size, cache_expiration_time, result_reuse_enable, result_reuse_minutes, paramstyle, and result_set_type_hints. The synchronous and aio cursors also accept on_start_query_execution. AsyncCursor and its variants return the query ID from execute() instead; they do not take on_start_query_execution as a keyword argument and ignore it in options. The Spark cursors execute calculations instead of SQL queries and do not accept these arguments.

These arguments can also be passed together as an ExecuteOptions instance using the options keyword argument.

from pyathena import ExecuteOptions, connect

cursor = connect(s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
                 region_name="us-west-2").cursor()
options = ExecuteOptions(work_group="YOUR_WORK_GROUP",
                         result_reuse_enable=True,
                         result_reuse_minutes=60)
cursor.execute("SELECT * FROM one_row", options=options)

When both options and individual keyword arguments are specified, the individual keyword arguments take precedence.

# Executes with work_group="ANOTHER_WORK_GROUP"; the other fields of options still apply.
cursor.execute("SELECT * FROM one_row",
               options=options,
               work_group="ANOTHER_WORK_GROUP")

Passing None for an individual keyword argument is treated as “not specified” and does not reset the corresponding options field. To run a single query without a field set on options, construct a new instance without that field.

ExecuteOptions is immutable. To create a variation of an existing instance, use the merge() method, which returns a new instance with the specified fields applied.

adhoc_options = options.merge(work_group="ANOTHER_WORK_GROUP")

The on_start_query_execution field is invoked by the synchronous and aio cursors; AsyncCursor-based cursors return the query ID directly through their execution model and do not invoke it.

Quickly re-run queries

Result reuse configuration

Athena engine version 3 allows you to reuse the results of previous queries.

It is available by specifying the arguments result_reuse_enable and result_reuse_minutes in the connection object.

from pyathena import connect

conn = connect(s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
               region_name="us-west-2",
               work_group="YOUR_WORK_GROUP",
               result_reuse_enable=True,
               result_reuse_minutes=60)

You can also specify result_reuse_enable and result_reuse_minutes when executing a query.

from pyathena import connect

cursor = connect(s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
                 region_name="us-west-2").cursor()
cursor.execute("SELECT * FROM one_row",
               work_group="YOUR_WORK_GROUP",
               result_reuse_enable=True,
               result_reuse_minutes=60)

If the following error occurs, please use a workgroup configured with Athena engine version 3.

pyathena.error.DatabaseError: An error occurred (InvalidRequestException) when calling the StartQueryExecution operation: This functionality is not enabled in the selected engine version. Please check the engine version settings or contact AWS support for further assistance.

If for some reason you cannot use the reuse feature of Athena engine version 3, please use the Cache configuration implemented by PyAthena.

Note

Semantic-equivalence matching (November 2025)

As of November 2025, Athena’s result reuse no longer requires byte-for-byte identical query text. Result reuse is now triggered when a query is semantically equivalent to a previously executed query — cosmetic differences such as whitespace, comments, or keyword casing no longer prevent a cache hit.

This behavior change happens server-side; PyAthena does not need to be updated, and existing code using result_reuse_enable continues to work unchanged. Users who relied on exact-string matching for cache invalidation should be aware that more queries may now reuse prior results than before.

See the Athena documentation for the latest rules on what counts as semantically equivalent.

Cache configuration

Please use the Result reuse configuration.

You can attempt to re-use the results from a previously executed query to help save time and money in the cases where your underlying data isn’t changing. Set the cache_size or cache_expiration_time parameter of cursor.execute() to a number larger than 0 to enable caching. cache_size is the number of the most recent query executions in the work group to search, including executions by other clients of the same work group. In a busy work group, a previous execution may no longer be among them.

from pyathena import connect

cursor = connect(s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
                 region_name="us-west-2").cursor()
cursor.execute("SELECT * FROM one_row")  # run once
print(cursor.query_id)
cursor.execute("SELECT * FROM one_row", cache_size=10)  # re-use earlier results
print(cursor.query_id)  # You should expect to see the same Query ID

The unit of cache_expiration_time is seconds. To use the results of queries executed up to one hour ago, specify like the following.

from pyathena import connect

cursor = connect(s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
                 region_name="us-west-2").cursor()
cursor.execute("SELECT * FROM one_row", cache_expiration_time=3600)  # Use queries executed within 1 hour as cache.

If cache_size is not specified, the value of sys.maxsize will be automatically set and all query results executed up to one hour ago will be checked. Therefore, it is recommended to specify cache_expiration_time together with cache_size like the following.

from pyathena import connect

cursor = connect(s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
                 region_name="us-west-2").cursor()
cursor.execute("SELECT * FROM one_row", cache_size=100, cache_expiration_time=3600)  # Use the last 100 queries within 1 hour as cache.

Results will only be re-used from a succeeded DML query (the assumption being that you always want to re-run queries like CREATE TABLE and DROP TABLE) whose query string (with pyformat parameters substituted) matches exactly, and that ran with the same schema and catalog as the cursor. The cache is not used for a qmark query with parameters. With unload=True on the pandas, Arrow, and Polars cursors, a query that is wrapped in UNLOAD is written to a new location each time, so the cache never matches it.

The S3 staging directory is not checked, so it’s possible that the location of the results is not in your provided s3_staging_dir.

Federated passthrough queries

Athena’s federated query feature lets you query data in external sources (PostgreSQL, MySQL, Snowflake, etc.) through Lambda-based connectors. By default Athena’s planner translates your SQL into the source dialect, but for complex queries — or when you need a feature only the source supports — you can use passthrough queries to push native SQL directly to the source.

Passthrough queries use the TABLE(<connector>.system.query(query => '...')) syntax. Because PyAthena passes the SQL string through to Athena unchanged, no client-side changes are required — these queries work out of the box.

from pyathena import connect

cursor = connect(s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
                 region_name="us-west-2").cursor()

cursor.execute("""
    SELECT * FROM TABLE(
        my_postgres_connector.system.query(
            query => 'SELECT id, name FROM public.users WHERE created_at > NOW() - INTERVAL ''7 days'''
        )
    )
""")
print(cursor.fetchall())

The connector (my_postgres_connector above) must be registered in Athena as a data source connector first; see the Athena documentation for the connector setup steps and the list of connectors that support passthrough.

Note

Result columns from a passthrough query come back with the source system’s types, which Athena maps to its own type system. When using PandasCursor or ArrowCursor, verify that the resulting dtypes match your expectations — connector-specific types (e.g., PostgreSQL json, MySQL geometry) may be returned as strings.

Query execution callback

PyAthena provides a callback mechanism that allows you to get immediate access to the query ID as soon as the start_query_execution API call is made, before waiting for query completion. This is useful for monitoring, logging, or stopping long-running queries by their ID, for example with AsyncCursor.cancel(query_id). When cache_size finds a reusable query, no new query starts, and the callback receives the reused query’s ID.

The on_start_query_execution callback can be configured at both the connection level and the execute level. When both are set, both callbacks will be invoked.

Connection-level callback

You can set a default callback for all queries executed through a connection:

from pyathena import connect

def query_callback(query_id):
    print(f"Query started with ID: {query_id}")
    # You can use query_id for monitoring or cancellation

cursor = connect(
    s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
    region_name="us-west-2",
    on_start_query_execution=query_callback
).cursor()

cursor.execute("SELECT * FROM many_rows")  # Callback will be invoked

Execute-level callback

You can also specify a callback for individual query executions:

from pyathena import connect

def specific_callback(query_id):
    print(f"Specific query started: {query_id}")

cursor = connect(
    s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
    region_name="us-west-2"
).cursor()

cursor.execute(
    "SELECT * FROM many_rows",
    on_start_query_execution=specific_callback
)

Multiple callbacks

When both connection-level and execute-level callbacks are specified, both callbacks will be invoked:

from pyathena import connect

def connection_callback(query_id):
    print(f"Connection callback: {query_id}")
    # Log to monitoring system

def execute_callback(query_id):
    print(f"Execute callback: {query_id}")
    # Store for cancellation if needed

cursor = connect(
    s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
    region_name="us-west-2",
    on_start_query_execution=connection_callback
).cursor()

# This will invoke both connection_callback and execute_callback
cursor.execute(
    "SELECT 1",
    on_start_query_execution=execute_callback
)

Supported cursor types

The on_start_query_execution callback is supported by the following cursor types:

  • Cursor (default cursor)

  • DictCursor

  • ArrowCursor

  • PandasCursor

  • PolarsCursor

  • S3FSCursor

  • AioCursor, AioDictCursor, AioArrowCursor, AioPandasCursor, AioPolarsCursor, AioS3FSCursor

Note: AsyncCursor and its variants do not support this callback as they already return the query ID immediately through their different execution model.

Query cancellation on interrupt

With kill_on_interrupt enabled, which is the default, a KeyboardInterrupt while execute() waits for the query requests cancellation, waits until the query reaches a terminal state, and then propagates. In the synchronous cursors, a KeyboardInterrupt raised in an on_start_query_execution callback cancels the query in the same way. Cancellation is a best-effort request, so the query can still end as SUCCEEDED or FAILED. The query_id property keeps the ID of the interrupted query. If the cancellation request or that wait fails, the KeyboardInterrupt propagates with the error as its cause.

A KeyboardInterrupt while execute() is still starting the query first waits for the StartQueryExecution request to finish, and then cancels the query it started in the same way. The query_id property returns that query’s ID. If execute() has not begun the request when the interrupt is handled, the request is never sent. AsyncCursor and its variants also stop a query whose start is interrupted in execute(), but they have no query_id property, so the ID of that query is not available. They wait for queries on worker threads, which do not receive KeyboardInterrupt.

A second KeyboardInterrupt during the cancellation request or these waits propagates immediately, and the query can keep running. With kill_on_interrupt=False, the KeyboardInterrupt propagates immediately, and a query that has already started keeps running.

from pyathena import connect

cursor = connect(s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
                 region_name="us-west-2").cursor()
try:
    cursor.execute("SELECT * FROM many_rows")
except KeyboardInterrupt:
    print(f"Query {cursor.query_id} was interrupted")
    raise

For the native asyncio cursors, see Task cancellation.

Query polling callback

PyAthena provides an on_poll callback that is invoked once per poll iteration with the current query execution object, while PyAthena waits for the query to finish. This is useful for rendering live query progress (state, elapsed time, data scanned) in interactive environments such as Jupyter notebooks.

The callback is optional (None by default), so there is no impact on existing behaviour or performance when it is not used. It must be a synchronous function with the signature Callable[[AthenaQueryExecution], None] (for Spark calculations it receives an AthenaCalculationExecutionStatus). It is invoked on every poll, including the final iteration that observes the terminal state (SUCCEEDED, FAILED, or CANCELLED).

Unlike on_start_query_execution, on_poll is configured at the connection or cursor level only (there is no execute-level override).

Connection-level callback

from pyathena import connect

def on_poll(query_execution):
    print(
        f"State: {query_execution.state}, "
        f"scanned: {query_execution.data_scanned_in_bytes} bytes"
    )

cursor = connect(
    s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
    region_name="us-west-2",
    on_poll=on_poll,
).cursor()

cursor.execute("SELECT * FROM many_rows")  # on_poll is invoked on each poll

Cursor-level callback

from pyathena import connect

def on_poll(query_execution):
    print(f"State: {query_execution.state}")

conn = connect(
    s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
    region_name="us-west-2",
)
cursor = conn.cursor(on_poll=on_poll)
cursor.execute("SELECT * FROM many_rows")

Asynchronous cursors

on_poll also works with the asynchronous cursors. Because polling runs in a background thread (or event loop), the callback runs there too, so keep it lightweight and thread-safe:

from pyathena import connect
from pyathena.pandas.async_cursor import AsyncPandasCursor

def on_poll(query_execution):
    print(f"State: {query_execution.state}")

conn = connect(
    s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
    region_name="us-west-2",
)
cursor = conn.cursor(AsyncPandasCursor, on_poll=on_poll)
query_id, future = cursor.execute("SELECT * FROM many_rows")
result = future.result()

Supported cursor types

on_poll is supported by all cursor types, since polling is shared by the base cursor: the synchronous cursors, the Async* cursors, the native-async Aio* cursors, and the Spark cursors. For Spark cursors the callback receives the per-poll AthenaCalculationExecutionStatus rather than an AthenaQueryExecution.

Type hints for complex types

New in version 3.30.0.

The Athena API does not return element-level type information for complex types (array, map, row/struct). PyAthena parses the string representation returned by Athena, but without type metadata the converter can only apply heuristics — which may produce incorrect Python types for nested values (e.g. integers left as strings inside a struct).

The result_set_type_hints parameter solves this by letting you provide Athena DDL type signatures for specific columns. The converter then uses precise, recursive type-aware conversion instead of heuristics.

from pyathena import connect

cursor = connect(s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
                 region_name="us-west-2").cursor()
cursor.execute(
    "SELECT col_array, col_map, col_struct FROM one_row_complex",
    result_set_type_hints={
        "col_array": "array(integer)",
        "col_map": "map(integer, integer)",
        "col_struct": "row(a integer, b integer)",
    },
)
row = cursor.fetchone()
# col_struct values are now integers, not strings:
# {"a": 1, "b": 2} instead of {"a": "1", "b": "2"}

Column name matching is case-insensitive. Type hints support arbitrarily nested types:

cursor.execute(
    """
    SELECT CAST(
      ROW(ROW('2024-01-01', 123), 4.736, 0.583)
      AS ROW(header ROW(stamp VARCHAR, seq INTEGER), x DOUBLE, y DOUBLE)
    ) AS positions
    """,
    result_set_type_hints={
        "positions": "row(header row(stamp varchar, seq integer), x double, y double)",
    },
)
row = cursor.fetchone()
positions = row[0]
# positions["header"]["seq"] == 123 (int, not "123")
# positions["x"] == 4.736 (float, not "4.736")

Hive-style syntax

You can paste type signatures from Hive DDL or DESCRIBE TABLE output directly. Hive-style angle brackets and colons are automatically converted to Trino-style syntax:

# Both are equivalent:
result_set_type_hints={"col": "array(struct(a integer, b varchar))"}   # Trino
result_set_type_hints={"col": "array<struct<a:int,b:varchar>>"}        # Hive

The int alias is also supported and resolves to integer.

Index-based hints for duplicate column names

When a query produces columns with the same alias (e.g. SELECT a AS x, b AS x), name-based hints cannot distinguish between them. Use integer keys to specify hints by zero-based column position:

cursor.execute(
    "SELECT a AS x, b AS x FROM my_table",
    result_set_type_hints={
        0: "array(integer)",   # first "x" column
        1: "map(varchar, integer)",  # second "x" column
    },
)

Integer (index-based) hints take priority over string (name-based) hints for the same column. You can mix both styles in the same dictionary.

Constraints

  • Nested arrays in native format — Athena’s native (non-JSON) string representation does not clearly delimit nested arrays. If your query returns nested arrays (e.g. array(array(integer))), use CAST(... AS JSON) in your query to get JSON-formatted output, which is parsed reliably.

  • Arrow, pandas, and Polars cursors — These cursors accept result_set_type_hints but their converters do not currently use the hints because they rely on their own type systems. The parameter is passed through for forward compatibility.

Breaking change in 3.30.0

Prior to 3.30.0, PyAthena attempted to infer Python types for scalar values inside complex types using heuristics (e.g. "123" → 123). Starting with 3.30.0, values inside complex types are kept as strings unless result_set_type_hints is provided. This change avoids silent misconversion but means existing code that relied on the heuristic behavior may see string values where it previously saw integers or floats.

To restore typed conversion, pass result_set_type_hints with the appropriate type signatures for the affected columns.

Table and database metadata

get_table_metadata(), list_table_metadata(), and list_databases() call the Athena metadata API. Athena applies its metadata API rate limits per account, and they are not listed in Service Quotas. In AwsDataCatalog and S3 Tables catalogs (s3tablescatalog/<table-bucket>), a throttled request is answered from the AWS Glue Data Catalog instead, and a warning is logged. The request goes to Glue on the first throttled response, without waiting for the retry policy. Glue throttling that Athena reports inside a MetadataException counts as throttled. The fallback calls these Glue APIs with the connection’s credentials:

Cursor method

Glue API

IAM action

get_table_metadata()

GetTable

glue:GetTable

list_table_metadata()

GetTables

glue:GetTables

list_databases()

GetDatabases

glue:GetDatabases

Glue’s report that the table does not exist, for get_table_metadata(), raises OperationalError, as Athena’s does. If the Glue request fails for any other reason, for example for lack of permission or because the Glue endpoint cannot be reached, a second warning is logged and the Athena request runs again with the retry policy. A request that cannot connect to Glue, such as from a network with an Athena VPC endpoint but no route to Glue, or that finds no Glue endpoint for the connection’s region and endpoint options, also turns the fallback off for the rest of that connection; a request that cannot connect first waits out the botocore connect timeout and retries of the connection’s config. For list_table_metadata() and list_databases(), Glue’s report that the database or catalog does not exist also returns to the Athena request. Requests in other catalogs use the Athena API with the retry policy only.

The connection builds one Glue client on first use from its session, region, and botocore config, but not its endpoint_url. Pass glue_metadata_fallback=False to connect() to turn the fallback off. The Glue request does not carry the connection’s workgroup; turn the fallback off where access depends on the workgroup, such as a workgroup enabled for IAM Identity Center.

S3 client

A connection builds one S3 client on first use and shares it with the result sets of its cursors, the filesystems created with pyathena.filesystem.s3_filesystem(), and the Spark cursors. The result sets of Cursor, DictCursor, and their asynchronous versions do not use it, so these cursors build no S3 client. ArrowCursor, and PolarsCursor for Parquet (unload=True) and chunked results, read the query results through their libraries’ own S3 clients and use the shared client for their other S3 requests.

The S3 client is built from the connection’s session, region, and client arguments, with the botocore config merged with s3_config. It does not use the connection’s endpoint_url and api_version, which are Athena’s, and neither do the S3 requests of pyathena.pandas.util.to_sql(). To send S3 requests to another endpoint, use botocore’s service-specific endpoint settings, such as the AWS_ENDPOINT_URL_S3 environment variable. Options set in s3_config take precedence over those in config for the S3 client only:

from botocore.config import Config
from pyathena import connect

conn = connect(
    s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
    region_name="us-west-2",
    s3_config=Config(max_pool_connections=50),
)

The client keeps up to max_pool_connections connections per host for reuse, 10 by default. Concurrent requests beyond that open more connections, which urllib3 closes after use, logging a “Connection pool is full” warning.

Connection.close() closes the network connections of the connection’s Athena client, and of its Glue and S3 clients if they were built. A client used after that opens new connections.

Checksums

Two botocore options of the S3 client control the checksums of uploads and downloads: request_checksum_calculation and response_checksum_validation. Both default to "when_supported". Set them in s3_config to change them for the S3 clients of the connection, including those that pyathena.pandas.util.to_sql() builds:

from botocore.config import Config
from pyathena import connect

conn = connect(
    s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
    region_name="us-west-2",
    s3_config=Config(
        request_checksum_calculation="when_required",
        response_checksum_validation="when_required",
    ),
)

An S3FileSystem built from s3fs-style arguments takes them in config_kwargs, and one given client uses the configuration of that client:

from pyathena.filesystem.s3 import S3FileSystem

fs = S3FileSystem(
    config_kwargs={
        "request_checksum_calculation": "when_required",
        "response_checksum_validation": "when_required",
    },
)

botocore also reads them from the AWS_REQUEST_CHECKSUM_CALCULATION and AWS_RESPONSE_CHECKSUM_VALIDATION environment variables and from the shared AWS config file.

A PutObject or UploadPart request that sets ChecksumAlgorithm, such as ChecksumAlgorithm="SHA256", or a checksum value, such as ChecksumSHA256, carries that checksum with either setting. For the other PutObject and UploadPart requests, also those that set ContentMD5, request_checksum_calculation="when_supported" makes the client compute a CRC32 checksum of the body and send it with the request. S3 checks the received data against it and stores it with an object written by PutObject. With "when_required", the client computes checksums only for the operations that require one, such as DeleteObjects. Those PutObject and UploadPart requests then include the SHA-256 hash of their body in their signature, which S3 also checks against the received data. Over HTTPS, the client leaves the hash out of a request that sets ContentMD5, which S3 checks instead, unless its s3 options set payload_signing_enabled to True. It also leaves the hash out if its s3 options set payload_signing_enabled to False or if it sends unsigned requests, such as S3FileSystem(anon=True).

In the benchmark run of 2026-11-15, on one c8gn.2xlarge host with 40 workers and 8 MiB parts, S3FileSystem wrote a 1 GiB object at 836 MiB/s with the defaults, at 1,002 MiB/s with request_checksum_calculation="when_required", and at 1,216 MiB/s when its s3 options also set payload_signing_enabled to False. It wrote a 6 GiB object at 1,119, 1,623, and 1,836 MiB/s with the same settings, taking 11.0, 10.8, and 7.1 seconds of CPU time. Its writes of a 256 MiB object and its reads and downloads ran within 5% of the defaults with either setting.

With response_checksum_validation="when_supported", the client requests the checksum of the object with every GetObject request. It validates the body it reads when the response carries a full-object checksum of an algorithm that botocore supports, and reads the body without validation otherwise, such as with the composite checksum of an object uploaded in parts. botocore supports CRC32, SHA-1, SHA-256, and SHA-512, and with botocore[crt] also CRC32C, CRC64NVME, XXHASH64, XXHASH3, and XXHASH128. S3 calculates a CRC64NVME checksum for an object uploaded without a checksum. S3 returns no checksum for a byte range, so the range reads of S3FileSystem, such as those of its files and of get(), are not validated with either setting. With "when_required", the client validates only the responses to requests that set ChecksumMode="ENABLED". Over HTTPS, TLS protects the data in transit with either setting.

Environment variables

Support Boto3 environment variables.

Additional environment variables

AWS_ATHENA_S3_STAGING_DIR

The S3 location where Athena automatically stores the query results and metadata information. Required if you have not set up workgroups. Not required if a workgroup has been set up. When connecting to a workgroup with managed query result storage, pass s3_staging_dir="" to explicitly disable this environment variable fallback (see Managed query result storage).

AWS_ATHENA_WORK_GROUP

The workgroup used to execute queries when the connection’s work_group argument is omitted or None. Pass work_group="" to disable this fallback.

Credentials

Support Boto3 credentials.

Examples

Passing credentials as parameters

from pyathena import connect

cursor = connect(aws_access_key_id="YOUR_ACCESS_KEY_ID",
                 aws_secret_access_key="YOUR_SECRET_ACCESS_KEY",
                 s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
                 region_name="us-west-2").cursor()
from pyathena import connect

cursor = connect(aws_access_key_id="YOUR_ACCESS_KEY_ID",
                 aws_secret_access_key="YOUR_SECRET_ACCESS_KEY",
                 aws_session_token="YOUR_SESSION_TOKEN",
                 s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
                 region_name="us-west-2").cursor()

Multi-factor authentication

You will be prompted to enter the MFA code. The program execution will be blocked until the MFA code is entered. The credentials last duration_seconds; before they expire, the connection requests new ones and prompts for an MFA code again, in the thread that then calls AWS.

from pyathena import connect

cursor = connect(duration_seconds=3600,
                 serial_number="arn:aws:iam::ACCOUNT_NUMBER_WITHOUT_HYPHENS:mfa/MFA_DEVICE_ID",
                 s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
                 region_name="us-west-2").cursor()

Shared credentials file

The shared credentials file has a default location of ~/.aws/credentials.

If you use the default profile, there is no need to specify credential information.

from pyathena import connect

cursor = connect(s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
                 region_name="us-west-2").cursor()

You can also specify a profile other than the default.

from pyathena import connect

cursor = connect(profile_name="YOUR_PROFILE_NAME",
                 s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
                 region_name="us-west-2").cursor()

Assume role provider

The connection requests the role’s credentials when it is created and again before they expire, so a long-lived connection keeps working past duration_seconds. The profile and credential arguments sign the AssumeRole requests.

from pyathena import connect

cursor = connect(role_arn="YOUR_ASSUME_ROLE_ARN",
                 role_session_name="PyAthena-session",
                 duration_seconds=3600,
                 s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
                 region_name="us-west-2").cursor()

Assume role provider with MFA

You will be prompted to enter the MFA code. The program execution will be blocked until the MFA code is entered. Each request for the role’s credentials, before the previous ones expire, prompts for an MFA code again.

from pyathena import connect

cursor = connect(role_arn="YOUR_ASSUME_ROLE_ARN",
                 role_session_name="PyAthena-session",
                 duration_seconds=3600,
                 serial_number="arn:aws:iam::ACCOUNT_NUMBER_WITHOUT_HYPHENS:mfa/MFA_DEVICE_ID",
                 s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
                 region_name="us-west-2").cursor()

Instance profiles

No need to specify credential information.

from pyathena import connect

cursor = connect(s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
                 region_name="us-west-2").cursor()

Unsupported: JWT Trusted Identity Propagation

Amazon Athena supports JWT-based Trusted Identity Propagation (TIP) for the official JDBC and ODBC drivers, allowing enterprise SSO identities (Okta, Entra ID, etc.) to be propagated to Athena and Lake Formation for fine-grained access control.

PyAthena does not support JWT TIP, because this auth flow is not exposed through the AWS SDK (boto3 / botocore). PyAthena builds its Athena client via boto3 and therefore relies on standard IAM-based credentials.

If your environment requires JWT TIP, the options are:

  • Use the Athena JDBC driver or ODBC driver directly.

  • Use IAM Identity Center with role-based access (assume-role flow) — see the Assume role provider examples above. This is not byte-equivalent to TIP but satisfies most SSO-driven access-control requirements.

This is a limitation of the AWS SDK, not of PyAthena. If boto3/botocore adds JWT TIP support in the future, PyAthena will expose it via Connection.