Considerations and limitations for SQL queries in Amazon Athena - Amazon Athena
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Considerations and limitations for SQL queries in Amazon Athena

When running queries in Athena, keep in mind the following considerations and limitations:

  • 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:

    • CREATE TABLE LIKE is not supported.

    • DESCRIBE INPUT and DESCRIBE OUTPUT is not supported.

    • The MERGE statement is supported only for transactional table formats. For more information, see MERGE INTO.

    • UPDATE 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.

  • S3 Glacier support – For information about querying restored Amazon S3 Glacier objects, see Querying 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 32 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 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

    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 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.

  • 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 .

For information about maximum query string length, quotas for query timeouts, and quotas for the active number of DML queries, see Service Quotas.