Optimize data
Performance depends not only on queries, but also importantly on how your dataset is organized and on the file format and compression that it uses.
Partition your data
Partitioning divides your table into parts and keeps the related data together based on properties such as date, country, or region. Partition keys act as virtual columns. You define partition keys at table creation and use them for filtering your queries. When you filter on partition key columns, only data from matching partitions is read. For example, if your dataset is partitioned by date and your query has a filter that matches only the last week, only the data for the last week is read. For more information about partitioning, see Partition your data.
Pick partition keys that will support your queries
Because partitioning has a significant impact on query performance, be sure to consider how you partition carefully when you design your dataset and tables. Having too many partition keys can result in fragmented datasets with too many files and files that are too small. Conversely, having too few partition keys, or no partitioning at all, leads to queries that scan more data than necessary.
Avoid optimizing for rare queries
A good strategy is to optimize for the most common queries and avoid optimizing for rare queries. For example, if your queries look at time spans of days, don't partition by hour, even if some queries filter to that level. If your data has a granular timestamp column, the rare queries that filter by hour can use the timestamp column. Even if rare cases scan a little more data than necessary, reducing overall performance for the sake of rare cases is usually not a good tradeoff.
To reduce the amount of data that queries have to scan, and thereby improve performance, use a columnar file format and keep the records sorted. Instead of partitioning by hour, keep the records sorted by timestamp. For queries on shorter time windows, sorting by timestamp is almost as efficient as partitioning by hour. Furthermore, sorting by timestamp does not typically hurt the performance of queries on time windows counted in days. For more information, see Use columnar file formats.
Note that queries on tables with tens of thousands of partitions perform better if there are predicates on all partition keys. This is another reason to design your partitioning scheme for the most common queries. For more information, see Query partitions by equality.
Use partition projection
Partition projection is an Athena feature that stores partition information not in the AWS Glue Data Catalog, but as rules in the properties of the table in AWS Glue. When Athena plans a query on a table configured with partition projection, it reads the table's partition projection rules. Athena computes the partitions to read in memory based on the query and the rules instead of looking up partitions in the AWS Glue Data Catalog.
Besides simplifying partition management, partition projection can improve performance for datasets that have large numbers of partitions. When a query includes ranges instead of specific values for partition keys, looking up matching partitions in the catalog takes longer the more partitions there are. With partition projection, the filter can be computed in memory without going to the catalog, and can be much faster.
In certain circumstances, partition projection can result in worse performance. One example occurs when a table is "sparse." A sparse table does not have data for every permutation of the partition key values described by the partition projection configuration. With a sparse table, the set of partitions calculated from the query and the partition projection configuration are all listed on Amazon S3 even when they have no data.
When you use partition projection, make sure to include predicates on all partition keys. Narrow the scope of possible values to avoid unnecessary Amazon S3 listings. Imagine a partition key that has a range of one million values and a query that does not have any filters on that partition key. To run the query, Athena must perform at least one million Amazon S3 list operations. Queries are fastest when you query on specific values, regardless of whether you use partition projection or store partition information in the catalog. For more information, see Query partitions by equality.
When you configure a table for partition projection, make sure that the ranges
that you specify are reasonable. If a query doesn't include a predicate on a
partition key, all the values in the range for that key are used. If your dataset
was created on a specific date, use that date as the starting point for any date
ranges. Use NOW as the end of date ranges. Avoid numeric ranges that
have large number of values, and consider using the injected type
instead.
For more information about partition projection, see Use partition projection with Amazon Athena.
Use partition indexes
Partition indexes are a feature in the AWS Glue Data Catalog that improves partition lookup performance for tables that have large numbers of partitions.
The list of partitions in the catalog is like a table in a relational database. The table has columns for the partition keys and an additional column for the partition location. When you query a partitioned table, the partition locations are looked up by scanning this table.
Just as with relational databases, you can increase the performance of queries by
adding indexes. You can add multiple indexes to support different query patterns.
The AWS Glue Data Catalog partition index supports both equality and comparison operators like
>, >=, and < combined with the
AND operator. For more information, see Working with partition indexes in
AWS Glue in the AWS Glue Developer Guide and Improve Amazon Athena query performance using AWS Glue Data Catalog partition indexes
Always use STRING as the type for partition keys
When you query on partition keys, remember that Athena requires partition keys to
be of type STRING in order to push down partition filtering into AWS Glue.
If the number of partitions is not small, using other types can lead to worse
performance. If your partition key values are date-like or number-like, cast them to
the appropriate type in your query.
Remove old and empty partitions
If you remove data from a partition on Amazon S3 (for example, by using Amazon S3 lifecycle), you should also remove the partition entry from the AWS Glue Data Catalog. During query planning, any partition matched by the query is listed on Amazon S3. If you have many empty partitions, the overhead of listing these partitions can be detrimental.
Also, if you have many thousands of partitions, consider removing partition metadata for old data that is no longer relevant. For example, if queries never look at data older than a year, you can periodically remove partition metadata for the older partitions. If the number of partitions grows into the tens of thousands, removing unused partitions can speed up queries that don't include predicates on all partition keys. For information about including predicates on all partition keys in your queries, see Query partitions by equality.
Query partitions by equality
Queries that include equality predicates on all partition keys run faster because the partition metadata can be loaded directly. Avoid queries in which one or more of the partition keys does not have a predicate, or the predicate selects a range of values. For such queries, the list of all partitions has to be filtered to find matching values. For most tables, the overhead is minimal, but for tables with tens of thousands or more partitions, the overhead can become significant.
If it is not possible to rewrite your queries to filter partitions by equality, you can try partition projection. For more information, see Use partition projection.
Avoid using MSCK REPAIR TABLE for partition maintenance
Because MSCK REPAIR TABLE can take a long time to run, only adds new
partitions, and does not remove old partitions, it is not an efficient way to manage
partitions (see Considerations and
limitations).
Partitions are better managed manually using the AWS Glue Data Catalog APIs, ALTER TABLE ADD PARTITION, or AWS Glue crawlers. As an alternative, you can use partition projection, which removes the need to manage partitions altogether. For more information, see Use partition projection with Amazon Athena.
Validate that your queries are compatible with the partitioning scheme
You can check in advance which partitions a query will scan by using the EXPLAIN statement. Prefix your query with the
EXPLAIN keyword, then look for the source fragment (for example,
Fragment 2 [SOURCE]) for each table near the bottom of the
EXPLAIN output. Look for assignments where the right side is
defined as a partition key. The line underneath includes a list of all the values
for that partition key that will be scanned when the query is run.
For example, suppose you have a query on a table with a dt partition
key and prefix the query with EXPLAIN. If the values in the query are
dates, and a filter selects a range of three days, the EXPLAIN output
might look something like this:
dt := dt:string:PARTITION_KEY :: [[2023-06-11], [2023-06-12], [2023-06-13]]
The EXPLAIN output shows that the planner found three values for this
partition key that matched the query. It also shows you what those values are. For
more information about using EXPLAIN, see Using EXPLAIN and EXPLAIN ANALYZE in
Athena
and Understand Athena EXPLAIN statement
results.
Use columnar file formats
Columnar file formats like Parquet and ORC are designed for distributed analytics workloads. They organize data by column instead of by row. Organizing data in columnar format offers the following advantages:
-
Only the columns needed for the query are loaded
-
The overall amount of data that needs to be loaded is reduced
-
Column values are stored together, so data can be compressed efficiently
-
Files can contain metadata that allow the engine to skip loading unneeded data
As an example of how file metadata can be used, file metadata can contain information about the minimum and maximum values in a page of data. If the values queried are not in the range noted in the metadata, the page can be skipped.
One way to use this metadata to improve performance is to ensure that data within
the files are sorted. For example, suppose you have queries that look for records
where the created_at entry is within a short time span. If your data is
sorted by the created_at column, Athena can use the minimum and maximum
values in the file metadata to skip the unneeded parts of the data files.
When using columnar file formats, make sure that your files aren't too small. As noted in Avoid having too many files, datasets with many small files cause performance issues. This is particularly true with columnar file formats. For small files, the overhead of the columnar file format outweighs the benefits.
Note that Parquet and ORC are internally organized by row groups (Parquet) and stripes (ORC). The default size for row groups is 128 MB, and for stripes, 64 MB. If you have many columns, you can increase the row group and stripe size for better performance. Decreasing the row group or stripe size to less than their default values is not recommended.
To convert other data formats to Parquet or ORC, you can use AWS Glue ETL or Athena. For more information, see Convert to columnar formats.
Compress data
Athena supports a wide range of compression formats. Querying compressed data is faster and also cheaper because you pay for the number of bytes scanned before decompression.
The gzip
When compressing text files such as JSON and CSV data, try to achieve a balance between the number of files and the size of the files. Most compression formats require the reader to read files from the beginning. This means that compressed text files cannot, in general, be processed in parallel. Big uncompressed files are often split between workers to achieve higher parallelism during query processing, but this is not possible with most compression formats.
As discussed in Avoid having too many files, it's better to have neither too many files nor too few. Because the number of files is the limit for how many workers can process the query, this rule is especially true for compressed files.
For more information about using compression in Athena, see Use compression in Athena.
Use bucketing for lookups on keys with high cardinality
Bucketing is a technique for distributing records into separate files based on the value of one of the columns. This ensures that all records with the same value will be in the same file. Bucketing is useful when you have a key with high cardinality and many of your queries look up specific values of the key.
For example, suppose you query a set of records for a specific user. If the data is bucketed by user ID, Athena knows in advance which files contain records for a specific ID and which files do not. This enables Athena to read only the files that can contain the ID, greatly reducing the amount of data read. It also reduces the compute time that otherwise would be required to search through the data for the specific ID.
Avoid bucketing when queries frequently search for multiple values in a column
Bucketing is less valuable when queries frequently search for multiple values in the column that the data is bucketed by. The more values queried, the higher the likelihood that all or most files will have to be read. For example, if you have three buckets, and a query looks for three different values, all files might have to be read. Bucketing works best when queries look up single values.
For more information, see Use partitioning and bucketing.
Avoid having too many files
Datasets that consist of many small files result in poor overall query performance. When Athena plans a query, it lists all partition locations, which takes time. Handling and requesting each file also has a computational overhead. Therefore, loading a single bigger file from Amazon S3 is faster than loading the same records from many smaller files.
In extreme cases, you might encounter Amazon S3 service limits. Amazon S3 supports up to 5,500 requests per second to a single index partition. Initially, a bucket is treated as a single index partition, but as request loads increase, it can be split into multiple index partitions.
Amazon S3 looks at request patterns and splits based on key prefixes. If your dataset consists of many thousands of files, the requests coming from Athena can exceed the request quota. Even with fewer files, the quota can be exceeded if multiple concurrent queries are made against the same dataset. Other applications that access the same files can contribute to the total number of requests.
When the request rate limit is exceeded, Amazon S3 returns the following
error. This error is included in the status information for the query in
Athena.
SlowDown: Please reduce your request rate
To troubleshoot, start by determining if the error is caused by a single query or by multiple queries that read the same files. If the latter, coordinate the running of queries so that they don't run at the same time. To achieve this, add a queuing mechanism or even retries in your application.
If running a single query triggers the error, try combining data files or modifying the query to read fewer files. The best time to combine small files is before they are written. To do so, consider the following techniques:
-
Change the process that writes the files to write larger files. For example, you could buffer records for a longer time before they are written.
-
Put files in a location on Amazon S3 and use a tool like Glue ETL to combine them into larger files. Then, move the larger files into the location that the table points to. For more information, see Reading input files in larger groups in the AWS Glue Developer Guide and How can I configure an AWS Glue ETL job to output larger files?
in the AWS re:Post Knowledge Center. -
Reduce the number of partition keys. When you have too many partition keys, each partition might have only a few records, resulting in an excessive number of small files. For information about deciding which partitions to create, see Pick partition keys that will support your queries.
Avoid additional storage hierarchies beyond the partition
To avoid query planning overhead, store files in a flat structure in each partition location. Do not use any additional directory hierarchies.
When Athena plans a query, it lists all files in all partitions matched by the
query. Although Amazon S3 doesn't have directories per se, the convention is to interpret
the / forward slash as a directory separator. When Athena lists
partition locations, it recursively lists any directory it finds. When files within
a partition are organized into a hierarchy, multiple rounds of listings
occur.
When all files are directly in the partition location, most of the time only one list operation has to be performed. However, multiple sequential list operations are required if you have more than 1000 files in a partition because Amazon S3 returns only 1000 objects per list operation. Having more than 1000 files in a partition can also create other, more serious performance issues. For more information, see Avoid having too many files.
Use SymlinkTextInputFormat only when necessary
Using the SymlinkTextInputFormat
However, using symlinks adds levels of indirection to the query execution. These
levels of indirection impact overall performance. The symlink files have to be read,
and the locations they define have to be listed. This adds multiple round trips to
Amazon S3 that usual Hive tables do not require. In conclusion, you should use
SymlinkTextInputFormat only when better options like reorganizing
files are not available.
