Why does Amazon Athena time out when querying tables with many partitions?
Last updated: 2019-07-30
My Amazon Athena table has many partitions—tens or hundreds of thousands. Athena times out when querying the table. However, when the table is not partitioned, the query performs as expected. Why is this happening and how can I resolve it?
This happens when you have 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. When you do this, Athena prunes partitions at the metastore level. This reduces overhead and prevents queries from timing out.