Considerations and limitations for SQL queries in Amazon Athena

Stored procedures – Stored procedures are not supported.

Maximum number of partitions – The maximum number of partitions you can create with CREATE TABLE AS SELECT (CTAS) statements is 100. For information, see CREATE TABLE AS. For a workaround, see Use CTAS and INSERT INTO to work around the 100 partition limit.

Unsupported statements – Unsupported statements include the following. For a complete list of unsupported DDL statements in Athena, see Unsupported DDL.

Trino and Presto connectors – Neither Trino nor Presto connectors are supported. Use Amazon Athena Federated Query to connect data sources. For more information, see Use Amazon Athena Federated Query.

Timeouts on tables with many partitions – Athena may time out when querying a table that has many thousands of partitions. This can happen when the table has many partitions that are not of type string. When you use type string, Athena prunes partitions at the metastore level. However, when you use other data types, Athena prunes partitions on the server side. The more partitions you have, the longer this process takes and the more likely your queries are to time out. To resolve this issue, set your partition type to string so that Athena prunes partitions at the metastore level. This reduces overhead and prevents queries from timing out.

S3 Glacier support – For information about querying restored Amazon S3 Glacier objects, see Query restored Amazon S3 Glacier objects.

Files treated as hidden – Athena treats source files that start with an underscore (_) or a dot (.) as hidden. To work around this limitation, rename the files.

Row or column size limitation – The size of a single row or its columns cannot exceed 200 megabytes. This limit can be exceeded when, for example, a row in a CSV or JSON file contains a single column of 300 megabytes. Exceeding this limit can also produce the error message TextLineLengthLimitExceededException: Too many bytes before newline. To work around this limitation, make sure that the sum of the data of the columns in any row is less than 200 MB.

LIMIT clause maximum – The maximum number of rows that can be specified for the LIMIT clause is

9223372036854775807. When using ORDER BY, the maximum number of supported rows for the LIMIT clause is 2147483647. Exceeding this limit results in the error message NOT_SUPPORTED: ORDER BY LIMIT > 2147483647 is not supported.

information_schema – Querying information_schema is most performant if you have a small to moderate amount of AWS Glue metadata. If you have a large amount of metadata, errors can occur. For information about querying the information_schema database for AWS Glue metadata, see Query the AWS Glue Data Catalog.

Array initializations – Due to a limitation in Java, it is not possible to initialize an array in Athena that has more than 254 arguments.

Hidden metadata columns – The Hive or Iceberg hidden metadata columns $bucket, $file_modified_time, $file_size, and $partition are not supported for views. For information about using the $path metadata column in Athena, see Getting the file locations for source data in Amazon S3 .