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 Using CTAS and INSERT INTO to work around the 100 partition limit.

Unsupported statements – The following statements are not supported:

Trino and Presto connectors – Neither Trino nor Presto connectors are supported. Use Amazon Athena Federated Query to connect data sources. For more information, see Using 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.

Amazon S3 Glacier storage – Athena does not support querying the data in the S3 Glacier flexible retrieval or S3 Glacier Deep Archive storage classes, or in the Archive access or deep archive access tiers of the S3 Intelligent Tiering storage class. Objects in the S3 Glacier Flexible Retrieval or S3 Glacier Deep Archive storage classes are ignored.

Data that is moved or transitioned to one of these classes are no longer readable or queryable by Athena even after storage class objects are restored. To make the restored objects that you want to query readable by Athena, copy the restored objects back into Amazon S3 to change their storage class. Alternatively, you can use the Amazon S3 Glacier Instant Retrieval storage class, which is queryable by Athena. For more information, see Amazon S3 Glacier instant retrieval storage class.

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 32 megabytes. This limit can be exceeded when, for example, a row in a CSV or JSON file contains a single column of 100 megabytes. Exceeding this limit can also produce the error message Line too long in text file. To work around this limitation, make sure that the sum of the data of the columns in any row is less than 32 MB.

LIMIT clause maximum – The maximum number of rows that can be specified 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 Amazon Glue metadata. If you have a large amount of metadata, errors can occur. For information about querying the information_schema database for Amazon Glue metadata, see Querying Amazon 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.