# Optimize Athena performance This topic provides general information and specific suggestions for improving the performance of your Athena queries, and how to work around errors related to limits and resource usage. Broadly speaking, optimizations can be grouped into service, query, and data structure categories. Decisions made at the service level, on how you write your queries, and on how you structure your data and tables can all influence performance. **Topics** + [Optimize service use](performance-tuning-service-level-considerations.md) + [Optimize queries](performance-tuning-query-optimization-techniques.md) + [Optimize data](performance-tuning-data-optimization-techniques.md) + [Use columnar storage formats](columnar-storage.md) + [Use partitioning and bucketing](ctas-partitioning-and-bucketing.md) + [Partition your data](partitions.md) + [Use partition projection with Amazon Athena](partition-projection.md) + [Prevent Amazon S3 throttling](performance-tuning-s3-throttling.md) + [Additional resources](performance-tuning-additional-resources.md) # Optimize service use Service level considerations include the number of workloads you run per account, service quotas not only for Athena, but across services, and thinking about how to reduce 'out of resource' errors. **Topics** + [Operate multiple workloads within the same account](#performance-tuning-service-quotas) + [Reduce 'out of resource' errors](#performance-tuning-resource-limits) ## Operate multiple workloads within the same account Athena uses quotas to limit query concurrency and API request rates at the account level. Exceeding these quotas can cause queries to fail during execution or at submission time. For more information about these quotas, see [Service Quotas](service-limits.md). If you operate multiple workloads within the same AWS account, your workloads compete for the same account-level quota. For example, if one workload experiences an unexpected burst of queries, another workload running in the same account may see elevated queue time, or in the worst case query submission failures due to throttling. We recommend that you use CloudWatch to monitor your service usage through graphs and dashboards. You can also configure CloudWatch alarms that alert you when your usage approaches the service quota for concurrent queries, allowing you to take action before reaching quota limits. For more information, see [Monitor Athena usage metrics with CloudWatch](monitoring-athena-usage-metrics.md). To control query concurrency and isolate workloads within your account, use capacity reservations. Capacity reservations provide dedicated query processing capacity within a single account. Capacity is measured in Data Processing Units (DPUs) and can be added or removed to increase or decrease query concurrency, respectively. Capacity reservations allow you to isolate workloads within your account from one another by assigning capacity to one or more workgroups. For more information, see [Manage query processing capacity](capacity-management.md). While you should isolate unrelated workloads in different AWS accounts (such as isolating development from production environments), this approach does not provide a scalable way to increase query concurrency. Instead, use capacity reservations to manage and scale your query processing needs within a single account. ### Consider quotas in other services When Athena runs a query, it can call other services that enforce quotas. During query execution, Athena can make API calls to the AWS Glue Data Catalog, Amazon S3, and other AWS services like IAM and AWS KMS. If you use [federated queries](federated-queries.md), Athena also calls AWS Lambda. All of these services have their own limits and quotas that can be exceeded. When a query execution encounters errors from these services, it fails and includes the error from the source service. Recoverable errors are retried, but queries can still fail if the issue does not resolve itself in time. Make sure to read error messages thoroughly to determine if they come from Athena or from another service. Some of the relevant errors are covered in this performance tuning section. For more information about working around errors caused by Amazon S3 service quotas, see [Avoid having too many files](performance-tuning-data-optimization-techniques.md#performance-tuning-avoid-having-too-many-files) later in this document. For more information about Amazon S3 performance optimization, see [Best practices design patterns: optimizing Amazon S3 performance](https://docs.aws.amazon.com/AmazonS3/latest/userguide/optimizing-performance.html) in the *Amazon S3 User Guide*. ## Reduce 'out of resource' errors Athena runs queries in a distributed query engine. When you submit a query, the Athena engine query planner estimates the compute capacity required to run the query and prepares a cluster of compute nodes accordingly. Some queries like DDL queries run on only one node. Complex queries over large data sets run on much bigger clusters. The nodes are uniform, with the same memory, CPU, and disk configurations. Athena scales out, not up, to process more demanding queries. Sometimes the demands of a query exceed the resources available to the cluster running the query. When this happens, the query fails with the error Query exhausted resources at this scale factor. The resource most commonly exhausted is memory, but in rare cases it can also be disk space. Memory errors commonly occur when the engine performs a join or a window function, but they can also occur in distinct counts and aggregations. Even if a query fails with an 'out of resource' error once, it might succeed when you run it again. Query execution is not deterministic. Factors such as how long it takes to load data and how intermediate datasets are distributed over the nodes can result in different resource usage. For example, imagine a query that joins two tables and has a heavy skew in the distribution of the values for the join condition. Such a query can succeed most of the time but occasionally fail when the most common values end up being processed by the same node. To prevent your queries from exceeding available resources, use the performance tuning tips mentioned in this document. In particular, for tips on how to optimize queries that exhaust the resources available, see [Optimize joins](performance-tuning-query-optimization-techniques.md#performance-tuning-optimizing-joins), [Reduce the scope of window functions, or remove them](performance-tuning-query-optimization-techniques.md#performance-tuning-optimizing-window-functions), and [Optimize queries by using approximations](performance-tuning-query-optimization-techniques.md#performance-tuning-optimizing-queries-by-using-approximations). # Optimize queries Use the query optimization techniques described in this section to make queries run faster or as workarounds for queries that exceed resource limits in Athena. ## Optimize joins There are many different strategies for executing joins in a distributed query engine. Two of the most common are distributed hash joins and queries with complex join conditions. ### In a distributed hash join, place large tables on the left, small tables on the right The most common type of join uses an equality comparison as the join condition. Athena runs this type of join as a distributed hash join. In a distributed hash join, the engine builds a lookup table (hash table) from one of the sides of the join. This side is called the *build side*. The records of the build side are distributed across the nodes. Each node builds a lookup table for its subset. The other side of the join, called the *probe side*, is then streamed through the nodes. The records from the probe side are distributed over the nodes in the same way as the build side. This enables each node to perform the join by looking up the matching records in its own lookup table. When the lookup tables created from the build side of the join don't fit into memory, queries can fail. Even if the total size of the build side is less than the available memory, queries can fail if the distribution of the records has significant skew. In an extreme case, all records could have the same value for the join condition and have to fit into memory on a single node. Even a query with less skew can fail if a set of values gets sent to the same node and the values add up to more than the available memory. Nodes do have the ability to spill records to disk, but spilling slows query execution and can be insufficient to prevent the query from failing. Athena attempts to reorder joins to use the larger relation as the probe side, and the smaller relation as the build side. However, because Athena does not manage the data in tables, it has limited information and often must assume that the first table is the larger and the second table is the smaller. When writing joins with equality-based join conditions, assume that the table to the left of the `JOIN` keyword is the probe side and the table to the right is the build side. Make sure that the right table, the build side, is the smaller of the tables. If it is not possible to make the build side of the join small enough to fit into memory, consider running multiple queries that join subsets of the build table. ### Use EXPLAIN to analyze queries with complex joins Queries with complex join conditions (for example, queries that use `LIKE` , `>`, or other operators), are often computationally demanding. In the worst case, every record from one side of the join must be compared to every record on the other side of the join. Because the execution time grows with the square of the number of records, such queries run the risk of exceeding the maximum execution time. To find out how Athena will execute your query in advance, you can use the `EXPLAIN` statement. For more information, see [Using EXPLAIN and EXPLAIN ANALYZE in Athena](athena-explain-statement.md) and [Understand Athena EXPLAIN statement results](athena-explain-statement-understanding.md). ## Reduce the scope of window functions, or remove them Because window functions are resource intensive operations, they can make queries run slow or even fail with the message Query exhausted resources at this scale factor. Window functions keep all the records that they operate on in memory in order to calculate their result. When the window is very large, the window function can run out of memory. To make sure your queries run within the available memory limits, reduce the size of the windows that your window functions operate over. To do so, you can add a `PARTITIONED BY` clause or narrow the scope of existing partitioning clauses. ### Use non-window functions Sometimes queries with window functions can be rewritten without window functions. For example, instead of using `row_number` to find the top `N` records, you can use `ORDER BY` and `LIMIT`. Instead of using `row_number` or `rank` to deduplicate records, you can use aggregate functions like [max\$1by](https://trino.io/docs/current/functions/aggregate.html#max_by), [min\$1by](https://trino.io/docs/current/functions/aggregate.html#min_by), and [arbitrary](https://trino.io/docs/current/functions/aggregate.html#arbitrary). For example, suppose you have a dataset with updates from a sensor. The sensor periodically reports its battery status and includes some metadata like location. If you want to know the last battery status for each sensor and its location, you can use this query: ``` SELECT sensor_id, arbitrary(location) AS location, max_by(battery_status, updated_at) AS battery_status FROM sensor_readings GROUP BY sensor_id ``` Because metadata like location is the same for every record, you can use the `arbitrary` function to pick any value from the group. To get the last battery status, you can use the `max_by` function. The `max_by` function picks the value for a column from the record where the maximum value of another column was found. In this case, it returns the battery status for the record with the last update time within the group. This query runs faster and uses less memory than an equivalent query with a window function. ## Optimize aggregations When Athena performs an aggregation, it distributes the records across worker nodes using the columns in the `GROUP BY` clause. To make the task of matching records to groups as efficient as possible, the nodes attempt to keep records in memory but spill them to disk if necessary. It is also a good idea to avoid including redundant columns in `GROUP BY` clauses. Because fewer columns require less memory, a query that describes a group using fewer columns is more efficient. Numeric columns also use less memory than strings. For example, when you aggregate a dataset that has both a numeric category ID and a category name, use only the category ID column in the `GROUP BY` clause. Sometimes queries include columns in the `GROUP BY` clause to work around the fact that a column must either be part of the `GROUP BY` clause or an aggregate expression. If this rule is not followed, you can receive an error message like the following: EXPRESSION\$1NOT\$1AGGREGATE: line 1:8: 'category' must be an aggregate expression or appear in GROUP BY clause To avoid having to add a redundant columns to the `GROUP BY` clause, you can use the [arbitrary](https://trino.io/docs/current/functions/aggregate.html#arbitrary) function, as in the following example. ``` SELECT country_id, arbitrary(country_name) AS country_name, COUNT(*) AS city_count FROM world_cities GROUP BY country_id ``` The `ARBITRARY` function returns an arbitrary value from the group. The function is useful when you know all records in the group have the same value for a column, but the value does not identify the group. ## Optimize top N queries The `ORDER BY` clause returns the results of a query in sorted order. Athena uses distributed sort to run the sort operation in parallel on multiple nodes. If you don't strictly need your result to be sorted, avoid adding an `ORDER BY` clause. Also, avoid adding `ORDER BY` to inner queries if they are not strictly necessary. In many cases, the query planner can remove redundant sorting, but this is not guaranteed. An exception to this rule is if an inner query is doing a top `N` operation, such as finding the `N` most recent, or `N` most common values. When Athena sees `ORDER BY` together with `LIMIT`, it understands that you are running a top `N` query and uses dedicated operations accordingly. **Note** Although Athena can also often detect window functions like `row_number` that use top `N`, we recommend the simpler version that uses `ORDER BY` and `LIMIT`. For more information, see [Reduce the scope of window functions, or remove them](#performance-tuning-optimizing-window-functions). ## Include only required columns If you don't strictly need a column, don't include it in your query. The less data a query has to process, the faster it will run. This reduces both the amount of memory required and the amount of data that has to be sent between nodes. If you are using a columnar file format, reducing the number columns also reduces the amount of data that is read from Amazon S3. Athena has no specific limit on the number of columns in a result, but how queries are executed limits the possible combined size of columns. The combined size of columns includes their names and types. For example, the following error is caused by a relation that exceeds the size limit for a relation descriptor: GENERIC\$1INTERNAL\$1ERROR: io.airlift.bytecode.CompilationException To work around this issue, reduce the number of columns in the query, or create subqueries and use a `JOIN` that retrieves a smaller amount of data. If you have queries that do `SELECT *` in the outermost query, you should change the `*` to a list of only the columns that you need. ## Optimize queries by using approximations Athena has support for [approximation aggregate functions](https://trino.io/docs/current/functions/aggregate.html#appro) for counting distinct values, the most frequent values, percentiles (including approximate medians), and creating histograms. Use these functions whenever exact values are not needed. Unlike `COUNT(DISTINCT col)` operations, [approx\$1distinct](https://trino.io/docs/current/functions/aggregate.html#approx_distinct) uses much less memory and runs faster. Similarly, using [numeric\$1histogram](https://trino.io/docs/current/functions/aggregate.html#numeric_histogram) instead of [histogram](https://trino.io/docs/current/functions/aggregate.html#histogram) uses approximate methods and therefore less memory. ## Optimize LIKE You can use `LIKE` to find matching strings, but with long strings, this is compute intensive. The [regexp\$1like](https://trino.io/docs/current/functions/regexp.html#regexp_like) function is in most cases a faster alternative, and also provides more flexibility. Often you can optimize a search by anchoring the substring that you are looking for. For example, if you're looking for a prefix, it is much better to use '*substr*%' instead of '%*substr*%'. Or, if you're using `regexp_like`, '^*substr*'. ## Use UNION ALL instead of UNION `UNION ALL` and `UNION` are two ways to combine the results of two queries into one result. `UNION ALL` concatenates the records from the first query with the second, and `UNION` does the same, but also removes duplicates. `UNION` needs to process all the records and find the duplicates, which is memory and compute intensive, but `UNION ALL` is a relatively quick operation. Unless you need to deduplicate records, use `UNION ALL` for the best performance. ## Use UNLOAD for large result sets When the results of a query are expected to be large (for example, tens of thousands of rows or more), use UNLOAD to export the results. In most cases, this is faster than running a regular query, and using `UNLOAD` also gives you more control over the output. When a query finishes executing, Athena stores the result as a single uncompressed CSV file on Amazon S3. This takes longer than `UNLOAD`, not only because the result is uncompressed, but also because the operation cannot be parallelized. In contrast, `UNLOAD` writes results directly from the worker nodes and makes full use of the parallelism of the compute cluster. In addition, you can configure `UNLOAD` to write the results in compressed format and in other file formats such as JSON and Parquet. For more information, see [UNLOAD](unload.md). ## Use CTAS or Glue ETL to materialize frequently used aggregations 'Materializing' a query is a way of accelerating query performance by storing pre-computed complex query results (for example, aggregations and joins) for reuse in subsequent queries. If many of your queries include the same joins and aggregations, you can materialize the common subquery as a new table and then run queries against that table. You can create the new table with [Create a table from query results (CTAS)](ctas.md), or a dedicated ETL tool like [Glue ETL](https://aws.amazon.com/glue). For example, suppose you have a dashboard with widgets that show different aspects of an orders dataset. Each widget has its own query, but the queries all share the same joins and filters. An order table is joined with a line items table, and there is a filter to show only the last three months. If you identify the common features of these queries, you can create a new table that the widgets can use. This reduces duplication and improves performance. The disadvantage is that you must keep the new table up to date. ## Reuse query results It's common for the same query to run multiple times within a short duration. For example, this can occur when multiple people open the same data dashboard. When you run a query, you can tell Athena to reuse previously calculated results. You specify the maximum age of the results to be reused. If the same query was previously run within that time frame, Athena returns those results instead of running the query again. For more information, see [Reuse query results in Athena](reusing-query-results.md) here in the *Amazon Athena User Guide* and [Reduce cost and improve query performance with Amazon Athena Query Result Reuse](https://aws.amazon.com/blogs/big-data/reduce-cost-and-improve-query-performance-with-amazon-athena-query-result-reuse/) in the *AWS Big Data Blog*. # 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](partitions.md). ## 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](#performance-tuning-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](#performance-tuning-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](#performance-tuning-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](partition-projection-dynamic-id-partitioning.md#partition-projection-injection) type instead. For more information about partition projection, see [Use partition projection with Amazon Athena](partition-projection.md). ## 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](https://docs.aws.amazon.com/glue/latest/dg/partition-indexes.html) in the *AWS Glue Developer Guide* and [Improve Amazon Athena query performance using AWS Glue Data Catalog partition indexes](https://aws.amazon.com/blogs/big-data/improve-amazon-athena-query-performance-using-aws-glue-data-catalog-partition-indexes/) in the *AWS Big Data Blog*. ## 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](https://docs.aws.amazon.com/AmazonS3/latest/userguide/object-lifecycle-mgmt.html)), 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](#performance-tuning-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](#performance-tuning-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](msck-repair-table.md#msck-repair-table-considerations)). Partitions are better managed manually using the [AWS Glue Data Catalog APIs](https://docs.aws.amazon.com/glue/latest/dg/aws-glue-api-catalog.html), [ALTER TABLE ADD PARTITION](alter-table-add-partition.md), or [AWS Glue crawlers](https://docs.aws.amazon.com/glue/latest/dg/crawler-running.html). 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](partition-projection.md). ## 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`](athena-explain-statement.md) 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](athena-explain-statement.md) and [Understand Athena EXPLAIN statement results](athena-explain-statement-understanding.md). ## 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](#performance-tuning-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](columnar-storage.md#convert-to-columnar). ## 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](https://www.gnu.org/software/gzip/) format provides good compression ratios and has wide range support across other tools and services. The [zstd](https://facebook.github.io/zstd/) (Zstandard) format is a newer compression format with a good balance between performance and compression ratio. 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](#performance-tuning-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](compression-formats.md). ## 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](ctas-partitioning-and-bucketing.md). ## 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](https://docs.aws.amazon.com/glue/latest/dg/grouping-input-files.html) in the *AWS Glue Developer Guide* and [How can I configure an AWS Glue ETL job to output larger files?](https://repost.aws/knowledge-center/glue-job-output-large-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](#performance-tuning-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](#performance-tuning-avoid-having-too-many-files). ## Use SymlinkTextInputFormat only when necessary Using the [https://athena.guide/articles/stitching-tables-with-symlinktextinputformat](https://athena.guide/articles/stitching-tables-with-symlinktextinputformat) technique can be a way to work around situations when the files for a table are not neatly organized into partitions. For example, symlinks can be useful when all files are in the same prefix or files with different schemas are in the same location. 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. # Use columnar storage formats [Apache Parquet](https://parquet.apache.org) and [ORC](https://orc.apache.org/) are columnar storage formats that are optimized for fast retrieval of data and used in AWS analytical applications. Columnar storage formats have the following characteristics that make them suitable for using with Athena: + *Compression by column, with compression algorithm selected for the column data type* to save storage space in Amazon S3 and reduce disk space and I/O during query processing. + *Predicate pushdown* in Parquet and ORC enables Athena queries to fetch only the blocks it needs, improving query performance. When an Athena query obtains specific column values from your data, it uses statistics from data block predicates, such as max/min values, to determine whether to read or skip the block. + *Splitting of data * in Parquet and ORC allows Athena to split the reading of data to multiple readers and increase parallelism during its query processing. To convert your existing raw data from other storage formats to Parquet or ORC, you can run [CREATE TABLE AS SELECT (CTAS)](ctas.md) queries in Athena and specify a data storage format as Parquet or ORC, or use the AWS Glue Crawler. ## Choose between Parquet and ORC The choice between ORC (Optimized Row Columnar) and Parquet depends on your specific usage requirements. Apache Parquet provides efficient data compression and encoding schemes and is ideal for running complex queries and processing large amounts of data. Parquet is optimized for use with [Apache Arrow](https://arrow.apache.org/), which can be advantageous if you use tools that are Arrow related. ORC provides an efficient way to store Hive data. ORC files are often smaller than Parquet files, and ORC indexes can make querying faster. In addition, ORC supports complex types such as structs, maps, and lists. When choosing between Parquet and ORC, consider the following: **Query performance** – Because Parquet supports a wider range of query types, Parquet might be a better choice if you plan to perform complex queries. **Complex data types** – If you are using complex data types, ORC might be a better choice as it supports a wider range of complex data types. **File size** – If disk space is a concern, ORC usually results in smaller files, which can reduce storage costs. **Compression** – Both Parquet and ORC provide good compression, but the best format for you can depend on your specific use case. **Evolution** – Both Parquet and ORC support schema evolution, which means you can add, remove, or modify columns over time. Both Parquet and ORC are good choices for big data applications, but consider the requirements of your scenario before choosing. You might want to perform benchmarks on your data and queries to see which format performs better for your use case. ## Convert to columnar formats Options for easily converting source data such as JSON or CSV into a columnar format include using [CREATE TABLE AS](ctas.md) queries or running jobs in AWS Glue. + You can use `CREATE TABLE AS` (CTAS) queries to convert data into Parquet or ORC in one step. For an example, see [Example: Writing query results to a different format](https://docs.aws.amazon.com/athena/latest/ug/ctas-examples.html#ctas-example-format) on the [Examples of CTAS queries](ctas-examples.md) page. + For information about using Athena for ETL to transform data from CSV to Parquet, see [Use CTAS and INSERT INTO for ETL and data analysis](ctas-insert-into-etl.md). + For information about running an AWS Glue job to transform CSV data to Parquet, see the section "Transform the data from CSV to Parquet format" in the AWS Big Data blog post [Build a data lake foundation with AWS Glue and Amazon S3](https://aws.amazon.com/blogs/big-data/build-a-data-lake-foundation-with-aws-glue-and-amazon-s3/). AWS Glue supports using the same technique to convert CSV data to ORC, or JSON data to either Parquet or ORC. # Use partitioning and bucketing Partitioning and bucketing are two ways to reduce the amount of data Athena must scan when you run a query. Partitioning and bucketing are complementary and can be used together. Reducing the amount of data scanned leads to improved performance and lower cost. For general guidelines about Athena query performance, see [Top 10 performance tuning tips for Amazon Athena](https://aws.amazon.com/blogs/big-data/top-10-performance-tuning-tips-for-amazon-athena/). **Topics** + [What is partitioning?](ctas-partitioning-and-bucketing-what-is-partitioning.md) + [What is bucketing?](ctas-partitioning-and-bucketing-what-is-bucketing.md) + [Additional resources](ctas-partitioning-and-bucketing-additional-resources.md) # What is partitioning? Partitioning means organizing data into directories (or "prefixes") on Amazon S3 based on a particular property of the data. Such properties are called partition keys. A common partition key is the date or some other unit of time such as the year or month. However, a dataset can be partitioned by more than one key. For example, data about product sales might be partitioned by date, product category, and market. ## Deciding how to partition Good candidates for partition keys are properties that are always or frequently used in queries and have low cardinality. There is a trade-off between having too many partitions and having too few. With too many partitions, the increased number of files creates overhead. There is also some overhead from filtering the partitions themselves. With too few partitions, queries often have to scan more data. ## Create a partitioned table When a dataset is partitioned, you can create a partitioned table in Athena. A partitioned table is a table that has partition keys. When you use `CREATE TABLE`, you add partitions to the table. When you use `CREATE TABLE AS`, the partitions that are created on Amazon S3 are automatically added to the table. In a `CREATE TABLE` statement, you specify the partition keys in the `PARTITIONED BY (column_name data_type)` clause. In a `CREATE TABLE AS` statement, you specify the partition keys in a `WITH (partitioned_by = ARRAY['partition_key'])` clause, or `WITH (partitioning = ARRAY['partition_key'])` for Iceberg tables. For performance reasons, partition keys should always be of type `STRING`. For more information, see [Use string as the data type for partition keys](data-types-timestamps.md#data-types-timestamps-partition-key-types). For additional `CREATE TABLE` and `CREATE TABLE AS` syntax details, see [CREATE TABLE](create-table.md) and [CTAS table properties](create-table-as.md#ctas-table-properties). ## Query partitioned tables When you query a partitioned table, Athena uses the predicates in the query to filter the list of partitions. Then it uses the locations of the matching partitions to process the files found. Athena can efficiently reduce the amount of data scanned by simply not reading data in the partitions that don't match the query predicates. ### Examples Suppose you have a table partitioned by `sales_date` and `product_category` and want to know the total revenue over a week in a specific category. You include predicates on the `sales_date` and `product_category` columns to ensure that Athena scans only the minimum amount of data, as in the following example. ``` SELECT SUM(amount) AS total_revenue FROM sales WHERE sales_date BETWEEN '2023-02-27' AND '2023-03-05' AND product_category = 'Toys' ``` Suppose you have a dataset that is partitioned by date but also has a fine-grained timestamp. With Iceberg tables, you can declare a partition key to have a relationship to a column, but with Hive tables the query engine has no knowledge of relationships between columns and partition keys. For this reason, you must include a predicate on both the column and the partition key in your query to make sure the query does not scan more data than necessary. For example, suppose the `sales` table in the previous example also has a `sold_at` column of the `TIMESTAMP` data type. If you want the revenue only for a specific time range, you would write the query like this: ``` SELECT SUM(amount) AS total_revenue FROM sales WHERE sales_date = '2023-02-28' AND sold_at BETWEEN TIMESTAMP '2023-02-28 10:00:00' AND TIMESTAMP '2023-02-28 12:00:00' AND product_category = 'Toys' ``` For more information about this difference between querying Hive and Iceberg tables, see [How to write queries for timestamp fields that are also time-partitioned](data-types-timestamps.md#data-types-timestamps-how-to-write-queries-for-timestamp-fields-that-are-also-time-partitioned). # What is bucketing? Bucketing is a way to organize the records of a dataset into categories called buckets. This meaning of bucket and bucketing is different from, and should not be confused with, Amazon S3 buckets. In data bucketing, records that have the same value for a property go into the same bucket. Records are distributed as evenly as possible among buckets so that each bucket has roughly the same amount of data. In practice, the buckets are files, and a hash function determines the bucket that a record goes into. A bucketed dataset will have one or more files per bucket per partition. The bucket that a file belongs to is encoded in the file name. ## Bucketing benefits Bucketing is useful when a dataset is bucketed by a certain property and you want to retrieve records in which that property has a certain value. Because the data is bucketed, Athena can use the value to determine which files to look at. For example, suppose a dataset is bucketed by `customer_id` and you want to find all records for a specific customer. Athena determines the bucket that contains those records and only reads the files in that bucket. Good candidates for bucketing occur when you have columns that have high cardinality (that is, have many distinct values), are uniformly distributed, and that you frequently query for specific values. **Note** Athena does not support using `INSERT INTO` to add new records to bucketed tables. ## Data types supported for filtering on bucketed columns You can add filters on bucketed columns with certain data types. Athena supports filtering on bucketed columns with the following data types: + BOOLEAN + BYTE + DATE + DOUBLE + FLOAT + INT + LONG + SHORT + STRING + VARCHAR ## Hive and Spark support Athena engine version 2 supports datasets bucketed using the Hive bucket algorithm, and Athena engine version 3 also supports the Apache Spark bucketing algorithm. Hive bucketing is the default. If your dataset is bucketed using the Spark algorithm, use the `TBLPROPERTIES` clause to set the `bucketing_format` property value to `spark`. **Note** Athena has a limit of 100 partitions in a `CREATE TABLE AS SELECT` ([CTAS](ctas.md)) query. Similarly, you can add only a maximum of 100 partitions to a destination table with an [INSERT INTO](insert-into.md) statement. If you exceed this limitation, you may receive the error message HIVE\$1TOO\$1MANY\$1OPEN\$1PARTITIONS: Exceeded limit of 100 open writers for partitions/buckets. To work around this limitation, you can use a CTAS statement and a series of `INSERT INTO` statements that create or insert up to 100 partitions each. For more information, see [Use CTAS and INSERT INTO to work around the 100 partition limit](ctas-insert-into.md). ## Bucketing CREATE TABLE example To create a table for an existing bucketed dataset, use the `CLUSTERED BY (column)` clause followed by the `INTO N BUCKETS` clause. The `INTO N BUCKETS` clause specifies the number of buckets the data is bucketed into. In the following `CREATE TABLE` example, the `sales` dataset is bucketed by `customer_id` into 8 buckets using the Spark algorithm. The `CREATE TABLE` statement uses the `CLUSTERED BY` and `TBLPROPERTIES` clauses to set the properties accordingly. ``` CREATE EXTERNAL TABLE sales (...) ... CLUSTERED BY (`customer_id`) INTO 8 BUCKETS ... TBLPROPERTIES ( 'bucketing_format' = 'spark' ) ``` ## Bucketing CREATE TABLE AS (CTAS) example To specify bucketing with `CREATE TABLE AS`, use the `bucketed_by` and `bucket_count` parameters, as in the following example. ``` CREATE TABLE sales WITH ( ... bucketed_by = ARRAY['customer_id'], bucket_count = 8 ) AS SELECT ... ``` ## Bucketing query example The following example query looks for the names of products that a specific customer has purchased over the course of a week. ``` SELECT DISTINCT product_name FROM sales WHERE sales_date BETWEEN '2023-02-27' AND '2023-03-05' AND customer_id = 'c123' ``` If this table is partitioned by `sales_date` and bucketed by `customer_id`, Athena can calculate the bucket that the customer records are in. At most, Athena reads one file per partition. # Additional resources + For a `CREATE TABLE AS` example that creates both bucketed and partitioned tables, see [Example: Creating bucketed and partitioned tables](https://docs.aws.amazon.com/athena/latest/ug/ctas-examples.html#ctas-example-bucketed). + For information on implementing bucketing on AWS data lakes, including using an Athena CTAS statement, AWS Glue for Apache Spark, and bucketing for Apache Iceberg tables, see the AWS Big Data Blog post [Optimize data layout by bucketing with Amazon Athena and AWS Glue to accelerate downstream queries](https://aws.amazon.com/blogs/big-data/optimize-data-layout-by-bucketing-with-amazon-athena-and-aws-glue-to-accelerate-downstream-queries/). # Partition your data By partitioning your data, you can restrict the amount of data scanned by each query, thus improving performance and reducing cost. You can partition your data by any key. A common practice is to partition the data based on time, often leading to a multi-level partitioning scheme. For example, a customer who has data coming in every hour might decide to partition by year, month, date, and hour. Another customer, who has data coming from many different sources but that is loaded only once per day, might partition by a data source identifier and date. Athena can use Apache Hive style partitions, whose data paths contain key value pairs connected by equal signs (for example, `country=us/...` or `year=2021/month=01/day=26/...`). Thus, the paths include both the names of the partition keys and the values that each path represents. To load new Hive partitions into a partitioned table, you can use the [MSCK REPAIR TABLE](msck-repair-table.md) command, which works only with Hive-style partitions. Athena can also use non-Hive style partitioning schemes. For example, CloudTrail logs and Firehose delivery streams use separate path components for date parts such as `data/2021/01/26/us/6fc7845e.json`. For such non-Hive style partitions, you use [ALTER TABLE ADD PARTITION](alter-table-add-partition.md) to add the partitions manually. ## Considerations and limitations When using partitioning, keep in mind the following points: + If you query a partitioned table and specify the partition in the `WHERE` clause, Athena scans the data only from that partition. + If you issue queries against Amazon S3 buckets with a large number of objects and the data is not partitioned, such queries may affect the `GET` request rate limits in Amazon S3 and lead to Amazon S3 exceptions. To prevent errors, partition your data. Additionally, consider tuning your Amazon S3 request rates. For more information, see [Best practices design patterns: Optimizing Amazon S3 performance ](https://docs.aws.amazon.com/AmazonS3/latest/userguide/request-rate-perf-considerations.html). + Partition locations to be used with Athena must use the `s3` protocol (for example, `s3://amzn-s3-demo-bucket/folder/`). In Athena, locations that use other protocols (for example, `s3a://amzn-s3-demo-bucket/folder/`) will result in query failures when `MSCK REPAIR TABLE` queries are run on the containing tables. + Make sure that the Amazon S3 path is in lower case instead of camel case (for example, `userid` instead of `userId`). If the S3 path is in camel case, `MSCK REPAIR TABLE` doesn't add the partitions to the AWS Glue Data Catalog. For more information, see [MSCK REPAIR TABLE](msck-repair-table.md). + Because `MSCK REPAIR TABLE` scans both a folder and its subfolders to find a matching partition scheme, be sure to keep data for separate tables in separate folder hierarchies. For example, suppose you have data for table 1 in `s3://amzn-s3-demo-bucket1` and data for table 2 in `s3://amzn-s3-demo-bucket1/table-2-data`. If both tables are partitioned by string, `MSCK REPAIR TABLE` will add the partitions for table 2 to table 1. To avoid this, use separate folder structures like `s3://amzn-s3-demo-bucket1` and `s3://amzn-s3-demo-bucket2` instead. Note that this behavior is consistent with Amazon EMR and Apache Hive. + If you are using the AWS Glue Data Catalog with Athena, see [AWS Glue endpoints and quotas](https://docs.aws.amazon.com/general/latest/gr/glue.html) for service quotas on partitions per account and per table. + Although Athena supports querying AWS Glue tables that have 10 million partitions, Athena cannot read more than 1 million partitions in a single scan. In such scenarios, partition indexing can be beneficial. For more information, see the AWS Big Data Blog article [Improve Amazon Athena query performance using AWS Glue Data Catalog partition indexes](https://aws.amazon.com/blogs/big-data/improve-amazon-athena-query-performance-using-aws-glue-data-catalog-partition-indexes/). + To request a partitions quota increase if you are using the AWS Glue Data Catalog, visit the [Service Quotas console for AWS Glue](https://console.aws.amazon.com/servicequotas/home?region=us-east-1#!/services/glue/quotas). ## Create and load a table with partitioned data To create a table that uses partitions, use the `PARTITIONED BY` clause in your [CREATE TABLE](create-table.md) statement. The `PARTITIONED BY` clause defines the keys on which to partition data, as in the following example. The `LOCATION` clause specifies the root location of the partitioned data. ``` CREATE EXTERNAL TABLE users ( first string, last string, username string ) PARTITIONED BY (id string) STORED AS parquet LOCATION 's3://amzn-s3-demo-bucket' ``` After you create the table, you load the data in the partitions for querying. For Hive style partitions, you run [MSCK REPAIR TABLE](msck-repair-table.md). For non-Hive style partitions, you use [ALTER TABLE ADD PARTITION](alter-table-add-partition.md) to add the partitions manually. ## Prepare Hive style and non-Hive style data for querying The following sections show how to prepare Hive style and non-Hive style data for querying in Athena. ### Scenario 1: Data stored on Amazon S3 in Hive format In this scenario, partitions are stored in separate folders in Amazon S3. For example, here is the partial listing for sample ad impressions output by the [https://awscli.amazonaws.com/v2/documentation/api/latest/reference/s3/ls.html](https://awscli.amazonaws.com/v2/documentation/api/latest/reference/s3/ls.html) command, which lists the S3 objects under a specified prefix: ``` aws s3 ls s3://elasticmapreduce/samples/hive-ads/tables/impressions/ PRE dt=2009-04-12-13-00/ PRE dt=2009-04-12-13-05/ PRE dt=2009-04-12-13-10/ PRE dt=2009-04-12-13-15/ PRE dt=2009-04-12-13-20/ PRE dt=2009-04-12-14-00/ PRE dt=2009-04-12-14-05/ PRE dt=2009-04-12-14-10/ PRE dt=2009-04-12-14-15/ PRE dt=2009-04-12-14-20/ PRE dt=2009-04-12-15-00/ PRE dt=2009-04-12-15-05/ ``` Here, logs are stored with the column name (dt) set equal to date, hour, and minute increments. When you give a DDL with the location of the parent folder, the schema, and the name of the partitioned column, Athena can query data in those subfolders. #### Create the table To make a table from this data, create a partition along 'dt' as in the following Athena DDL statement: ``` CREATE EXTERNAL TABLE impressions ( requestBeginTime string, adId string, impressionId string, referrer string, userAgent string, userCookie string, ip string, number string, processId string, browserCookie string, requestEndTime string, timers struct, threadId string, hostname string, sessionId string) PARTITIONED BY (dt string) ROW FORMAT serde 'org.apache.hive.hcatalog.data.JsonSerDe' LOCATION 's3://elasticmapreduce/samples/hive-ads/tables/impressions/' ; ``` This table uses Hive's native JSON serializer-deserializer to read JSON data stored in Amazon S3. For more information about the formats supported, see [Choose a SerDe for your data](supported-serdes.md). #### Run MSCK REPAIR TABLE After you run the `CREATE TABLE` query, run the `MSCK REPAIR TABLE` command in the Athena query editor to load the partitions, as in the following example. ``` MSCK REPAIR TABLE impressions ``` After you run this command, the data is ready for querying. #### Query the data Query the data from the impressions table using the partition column. Here's an example: ``` SELECT dt,impressionid FROM impressions WHERE dt<'2009-04-12-14-00' and dt>='2009-04-12-13-00' ORDER BY dt DESC LIMIT 100 ``` This query should show results similar to the following: ``` 2009-04-12-13-20 ap3HcVKAWfXtgIPu6WpuUfAfL0DQEc 2009-04-12-13-20 17uchtodoS9kdeQP1x0XThKl5IuRsV 2009-04-12-13-20 JOUf1SCtRwviGw8sVcghqE5h0nkgtp 2009-04-12-13-20 NQ2XP0J0dvVbCXJ0pb4XvqJ5A4QxxH 2009-04-12-13-20 fFAItiBMsgqro9kRdIwbeX60SROaxr 2009-04-12-13-20 V4og4R9W6G3QjHHwF7gI1cSqig5D1G 2009-04-12-13-20 hPEPtBwk45msmwWTxPVVo1kVu4v11b 2009-04-12-13-20 v0SkfxegheD90gp31UCr6FplnKpx6i 2009-04-12-13-20 1iD9odVgOIi4QWkwHMcOhmwTkWDKfj 2009-04-12-13-20 b31tJiIA25CK8eDHQrHnbcknfSndUk ``` ### Scenario 2: Data is not partitioned in Hive format In the following example, the `aws s3 ls` command shows [ELB](elasticloadbalancer-classic-logs.md) logs stored in Amazon S3. Note how the data layout does not use `key=value` pairs and therefore is not in Hive format. (The `--recursive` option for the `aws s3 ls` command specifies that all files or objects under the specified directory or prefix be listed.) ``` aws s3 ls s3://athena-examples-myregion/elb/plaintext/ --recursive 2016-11-23 17:54:46 11789573 elb/plaintext/2015/01/01/part-r-00000-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:46 8776899 elb/plaintext/2015/01/01/part-r-00001-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:46 9309800 elb/plaintext/2015/01/01/part-r-00002-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:47 9412570 elb/plaintext/2015/01/01/part-r-00003-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:47 10725938 elb/plaintext/2015/01/01/part-r-00004-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:46 9439710 elb/plaintext/2015/01/01/part-r-00005-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:47 0 elb/plaintext/2015/01/01_$folder$ 2016-11-23 17:54:47 9012723 elb/plaintext/2015/01/02/part-r-00006-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:47 7571816 elb/plaintext/2015/01/02/part-r-00007-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:47 9673393 elb/plaintext/2015/01/02/part-r-00008-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:48 11979218 elb/plaintext/2015/01/02/part-r-00009-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:48 9546833 elb/plaintext/2015/01/02/part-r-00010-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:48 10960865 elb/plaintext/2015/01/02/part-r-00011-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:48 0 elb/plaintext/2015/01/02_$folder$ 2016-11-23 17:54:48 11360522 elb/plaintext/2015/01/03/part-r-00012-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:48 11211291 elb/plaintext/2015/01/03/part-r-00013-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:48 8633768 elb/plaintext/2015/01/03/part-r-00014-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:49 11891626 elb/plaintext/2015/01/03/part-r-00015-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:49 9173813 elb/plaintext/2015/01/03/part-r-00016-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:49 11899582 elb/plaintext/2015/01/03/part-r-00017-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:49 0 elb/plaintext/2015/01/03_$folder$ 2016-11-23 17:54:50 8612843 elb/plaintext/2015/01/04/part-r-00018-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:50 10731284 elb/plaintext/2015/01/04/part-r-00019-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:50 9984735 elb/plaintext/2015/01/04/part-r-00020-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:50 9290089 elb/plaintext/2015/01/04/part-r-00021-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:50 7896339 elb/plaintext/2015/01/04/part-r-00022-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:51 8321364 elb/plaintext/2015/01/04/part-r-00023-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:51 0 elb/plaintext/2015/01/04_$folder$ 2016-11-23 17:54:51 7641062 elb/plaintext/2015/01/05/part-r-00024-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:51 10253377 elb/plaintext/2015/01/05/part-r-00025-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:51 8502765 elb/plaintext/2015/01/05/part-r-00026-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:51 11518464 elb/plaintext/2015/01/05/part-r-00027-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:51 7945189 elb/plaintext/2015/01/05/part-r-00028-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:51 7864475 elb/plaintext/2015/01/05/part-r-00029-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:51 0 elb/plaintext/2015/01/05_$folder$ 2016-11-23 17:54:51 11342140 elb/plaintext/2015/01/06/part-r-00030-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:51 8063755 elb/plaintext/2015/01/06/part-r-00031-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:52 9387508 elb/plaintext/2015/01/06/part-r-00032-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:52 9732343 elb/plaintext/2015/01/06/part-r-00033-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:52 11510326 elb/plaintext/2015/01/06/part-r-00034-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:52 9148117 elb/plaintext/2015/01/06/part-r-00035-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:52 0 elb/plaintext/2015/01/06_$folder$ 2016-11-23 17:54:52 8402024 elb/plaintext/2015/01/07/part-r-00036-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:52 8282860 elb/plaintext/2015/01/07/part-r-00037-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:52 11575283 elb/plaintext/2015/01/07/part-r-00038-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:53 8149059 elb/plaintext/2015/01/07/part-r-00039-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:53 10037269 elb/plaintext/2015/01/07/part-r-00040-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:53 10019678 elb/plaintext/2015/01/07/part-r-00041-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:53 0 elb/plaintext/2015/01/07_$folder$ 2016-11-23 17:54:53 0 elb/plaintext/2015/01_$folder$ 2016-11-23 17:54:53 0 elb/plaintext/2015_$folder$ ``` #### Run ALTER TABLE ADD PARTITION Because the data is not in Hive format, you cannot use the `MSCK REPAIR TABLE` command to add the partitions to the table after you create it. Instead, you can use the [ALTER TABLE ADD PARTITION](alter-table-add-partition.md) command to add each partition manually. For example, to load the data in s3://athena-examples-*myregion*/elb/plaintext/2015/01/01/, you can run the following query. Note that a separate partition column for each Amazon S3 folder is not required, and that the partition key value can be different from the Amazon S3 key. ``` ALTER TABLE elb_logs_raw_native_part ADD PARTITION (dt='2015-01-01') location 's3://athena-examples-us-west-1/elb/plaintext/2015/01/01/' ``` If a partition already exists, you receive the error Partition already exists. To avoid this error, you can use the `IF NOT EXISTS` clause. For more information, see [ALTER TABLE ADD PARTITION](alter-table-add-partition.md). To remove a partition, you can use [ALTER TABLE DROP PARTITION](alter-table-drop-partition.md). ## Consider partition projection To avoid having to manage partitions yourself, you can use partition projection. Partition projection is an option for highly partitioned tables whose structure is known in advance. In partition projection, partition values and locations are calculated from table properties that you configure rather than read from a metadata repository. Because the in-memory calculations are faster than remote look-up, the use of partition projection can significantly reduce query runtimes. For more information, see [Use partition projection with Amazon Athena](partition-projection.md). ## Additional resources + For information about partitioning options for Firehose data, see [Amazon Data Firehose example](partition-projection-kinesis-firehose-example.md). + You can automate adding partitions by using the [JDBC driver](connect-with-jdbc.md). + You can use CTAS and INSERT INTO to partition a dataset. For more information, see [Use CTAS and INSERT INTO for ETL and data analysis](ctas-insert-into-etl.md). # Use partition projection with Amazon Athena You can use partition projection in Athena to speed up query processing of highly partitioned tables and automate partition management. In partition projection, Athena calculates partition values and locations using the table properties that you configure directly on your table in AWS Glue. The table properties allow Athena to 'project', or determine, the necessary partition information instead of having to do a more time-consuming metadata lookup in the AWS Glue Data Catalog. Because in-memory operations are often faster than remote operations, partition projection can reduce the runtime of queries against highly partitioned tables. Depending on the specific characteristics of the query and underlying data, partition projection can significantly reduce query runtime for queries that are constrained on partition metadata retrieval. ## Understand partition pruning vs. partition projection Partition pruning gathers metadata and "prunes" it to only the partitions that apply to your query. This often speeds up queries. Athena uses partition pruning for all tables with partition columns, including those tables configured for partition projection. Normally, when processing queries, Athena makes a `GetPartitions` call to the AWS Glue Data Catalog before performing partition pruning. If a table has a large number of partitions, using `GetPartitions` can affect performance negatively. To avoid this, you can use partition projection. Partition projection allows Athena to avoid calling `GetPartitions` because the partition projection configuration gives Athena all of the necessary information to build the partitions itself. ## How to use partition projection To use partition projection, you specify the ranges of partition values and projection types for each partition column in the table properties in the AWS Glue Data Catalog or in your [external Hive metastore](connect-to-data-source-hive.md). These custom properties on the table allow Athena to know what partition patterns to expect when it runs a query on the table. During query execution, Athena uses this information to project the partition values instead of retrieving them from the AWS Glue Data Catalog or external Hive metastore. This not only reduces query execution time but also automates partition management because it removes the need to manually create partitions in Athena, AWS Glue, or your external Hive metastore. **Important** Enabling partition projection on a table causes Athena to ignore any partition metadata registered to the table in the AWS Glue Data Catalog or Hive metastore. ## Some use cases Scenarios in which partition projection is useful include the following: + Queries against a highly partitioned table do not complete as quickly as you would like. + You regularly add partitions to tables as new date or time partitions are created in your data. With partition projection, you configure relative date ranges that can be used as new data arrives. + You have highly partitioned data in Amazon S3. The data is impractical to model in your AWS Glue Data Catalog or Hive metastore, and your queries read only small parts of it. ### Projectable partition structures Partition projection is most easily configured when your partitions follow a predictable pattern such as, but not limited to, the following: + **Integers** – Any continuous sequence of integers such as `[1, 2, 3, 4, ..., 1000]` or `[0500, 0550, 0600, ..., 2500]`. + **Dates** – Any continuous sequence of dates or datetimes such as `[20200101, 20200102, ..., 20201231]` or `[1-1-2020 00:00:00, 1-1-2020 01:00:00, ..., 12-31-2020 23:00:00]`. + **Enumerated values** – A finite set of enumerated values such as airport codes or AWS Regions. + **AWS service logs** – AWS service logs typically have a known structure whose partition scheme you can specify in AWS Glue and that Athena can therefore use for partition projection. ### How to customize the partition path template By default, Athena builds partition locations using the form `s3://amzn-s3-demo-bucket//partition-col-1=/partition-col-2=/`, but if your data is organized differently, Athena offers a mechanism for customizing this path template. For steps, see [How to specify custom S3 storage locations](partition-projection-setting-up.md#partition-projection-specifying-custom-s3-storage-locations). ## Considerations and limitations The following considerations apply: + Partition projection eliminates the need to specify partitions manually in AWS Glue or an external Hive metastore. + When you enable partition projection on a table, Athena ignores any partition metadata in the AWS Glue Data Catalog or external Hive metastore for that table. + If a projected partition does not exist in Amazon S3, Athena will still project the partition. Athena does not throw an error, but no data is returned. However, if too many of your partitions are empty, performance can be slower compared to traditional AWS Glue partitions. If more than half of your projected partitions are empty, it is recommended that you use traditional partitions. + Queries for values that are beyond the range bounds defined for partition projection do not return an error. Instead, the query runs, but returns zero rows. For example, if you have time-related data that starts in 2020 and is defined as `'projection.timestamp.range'='2020/01/01,NOW'`, a query like `SELECT * FROM table-name WHERE timestamp = '2019/02/02'` will complete successfully, but return zero rows. + Partition projection is usable only when the table is queried through Athena. If the same table is read through another service such as Amazon Redshift Spectrum, Athena for Spark, or Amazon EMR, the standard partition metadata is used. + Because partition projection is a DML-only feature, `SHOW PARTITIONS` does not list partitions that are projected by Athena but not registered in the AWS Glue catalog or external Hive metastore. + Athena does not use the table properties of views as configuration for partition projection. To work around this limitation, configure and enable partition projection in the table properties for the tables that the views reference. ## Video The following video shows how to use partition projection to improve the performance of your queries in Athena. [![AWS Videos](http://img.youtube.com/vi/https://www.youtube.com/embed/iUD5pPpcyZk/0.jpg)](http://www.youtube.com/watch?v=https://www.youtube.com/embed/iUD5pPpcyZk) **Topics** + [Understand partition pruning vs. partition projection](#partition-projection-pruning-vs-projection) + [How to use partition projection](#partition-projection-using) + [Some use cases](#partition-projection-use-cases) + [Considerations and limitations](#partition-projection-considerations-and-limitations) + [Video](#partition-projection-video) + [Set up partition projection](partition-projection-setting-up.md) + [Supported types for partition projection](partition-projection-supported-types.md) + [Use dynamic ID partitioning](partition-projection-dynamic-id-partitioning.md) + [Amazon Data Firehose example](partition-projection-kinesis-firehose-example.md) # Set up partition projection Setting up partition projection in a table's properties is a two-step process: 1. Specify the data ranges and relevant patterns for each partition column, or use a custom template. 1. Enable partition projection for the table. **Note** Before you add partition projection properties to an existing table, the partition column for which you are setting up partition projection properties must already exist in the table schema. If the partition column does not yet exist, you must add a partition column to the existing table manually. AWS Glue does not perform this step for you automatically. This section shows how to set the table properties for AWS Glue. To set them, you can use the AWS Glue console, Athena [CREATE TABLE](create-table.md) queries, or [AWS Glue API](https://docs.aws.amazon.com/glue/latest/dg/aws-glue-api.html) operations. The following procedure shows how to set the properties in the AWS Glue console. **To configure and enable partition projection using the AWS Glue console** 1. Sign in to the AWS Management Console and open the AWS Glue console at [https://console.aws.amazon.com/glue/](https://console.aws.amazon.com/glue/). 1. Choose the **Tables** tab. On the **Tables** tab, you can edit existing tables, or choose **Add tables** to create new ones. For information about adding tables manually or with a crawler, see [Working with tables on the AWS Glue console](https://docs.aws.amazon.com/glue/latest/dg/console-tables.html) in the *AWS Glue Developer Guide*. 1. In the list of tables, choose the link for the table that you want to edit. ![\[In the AWS Glue console, choose a table to edit.\]](http://docs.aws.amazon.com/athena/latest/ug/images/partition-projection-1.png) 1. Choose **Actions**, **Edit table**. 1. On the **Edit table** page, in the **Table properties** section, for each partitioned column, add the following key-value pair: 1. For **Key**, add `projection.columnName.type`. 1. For **Value**, add one of the supported types: `enum`, `integer`, `date`, or `injected`. For more information, see [Supported types for partition projection](partition-projection-supported-types.md). 1. Following the guidance in [Supported types for partition projection](partition-projection-supported-types.md), add additional key-value pairs according to your configuration requirements. The following example table configuration configures the `year` column for partition projection, restricting the values that can be returned to a range from 2010 through 2016. ![\[Configuring partition projection for a partition column in the AWS Glue console table properties.\]](http://docs.aws.amazon.com/athena/latest/ug/images/partition-projection-3.png) 1. Add a key-value pair to enable partition projection. For **Key**, enter `projection.enabled`, and for its **Value**, enter `true`. **Note** You can disable partition projection on this table at any time by setting `projection.enabled` to `false`. 1. When you are finished, choose **Save**. 1. In the Athena Query Editor, test query the columns that you configured for the table. The following example query uses `SELECT DISTINCT` to return the unique values from the `year` column. The database contains data from 1987 to 2016, but the `projection.year.range` property restricts the values returned to the years 2010 to 2016. ![\[Querying a column that uses partition projection.\]](http://docs.aws.amazon.com/athena/latest/ug/images/partition-projection-5.png) **Note** If you set `projection.enabled` to `true` but fail to configure one or more partition columns, you receive an error message like the following: `HIVE_METASTORE_ERROR: Table database_name.table_name is configured for partition projection, but the following partition columns are missing projection configuration: [column_name] (table database_name.table_name)`. ## How to specify custom S3 storage locations When you edit table properties in AWS Glue, you can also specify a custom Amazon S3 path template for the projected partitions. A custom template enables Athena to properly map partition values to custom Amazon S3 file locations that do not follow a typical `.../column=value/...` pattern. Using a custom template is optional. However, if you use a custom template, the template must contain a placeholder for each partition column. Templated locations must end with a forward slash so that the partitioned data files live in a "folder" per partition. **To specify a custom partition location template** 1. Following the steps to [configure and enable partition projection using the AWS Glue console](#partition-projection-setting-up-procedure), add an additional a key-value pair that specifies a custom template as follows: 1. For **Key**, enter `storage.location.template`. 1. For **Value**, specify a location that includes a placeholder for every partition column. Make sure that each placeholder (and the S3 path itself) is terminated by a single forward slash. The following example template values assume a table with partition columns `a`, `b`, and `c`. ``` s3://amzn-s3-demo-bucket/table_root/a=${a}/${b}/some_static_subdirectory/${c}/ ``` ``` s3://amzn-s3-demo-bucket/table_root/c=${c}/${b}/some_static_subdirectory/${a}/${b}/${c}/${c}/ ``` For the same table, the following example template value is invalid because it contains no placeholder for column `c`. ``` s3://amzn-s3-demo-bucket/table_root/a=${a}/${b}/some_static_subdirectory/ ``` 1. Choose **Apply**. # Supported types for partition projection A table can have any combination of `enum`, `integer`, `date,` or `injected` partition column types. ## Enum type Use the `enum` type for partition columns whose values are members of an enumerated set (for example, airport codes or AWS Regions). Define the partition properties in the table as follows: **** | Property name | Example values | Description | | --- | --- | --- | | projection.columnName.type | `enum` | Required. The projection type to use for column columnName. The value must be enum (case insensitive) to signal the use of the enum type. Leading and trailing white space is allowed. | | projection.columnName.values | `A,B,C,D,E,F,G,Unknown` | Required. A comma-separated list of enumerated partition values for column columnName. Any white space is considered part of an enum value. | **Note** As a best practice we recommend limiting the use of `enum` based partition projections to a few dozen or less. Although there is no specific limit for `enum` projections, the total size of your table's metadata cannot exceed the AWS Glue limit of about 1 MB when gzip compressed. Note that this limit is shared across key parts of your table like column names, location, storage format, and others. If you find yourself using more than a few dozen unique IDs in your `enum` projection, consider an alternative approach such as bucketing into a smaller number of unique values in a surrogate field. By trading off cardinality, you can control the number of unique values in your `enum` field. ## Integer type Use the integer type for partition columns whose possible values are interpretable as integers within a defined range. Projected integer columns are currently limited to the range of a Java signed long (-263 to 263-1 inclusive). **** | Property name | Example values | Description | | --- | --- | --- | | projection.columnName.type | `integer` | Required. The projection type to use for column columnName. The value must be integer (case insensitive) to signal the use of the integer type. Leading and trailing white space is allowed. | | projection.columnName.range | `0,10` `-1,8675309` `0001,9999` | Required. A two-element comma-separated list that provides the minimum and maximum range values to be returned by queries on the column columnName. Note that the values must be separated by a comma, not a hyphen. These values are inclusive, can be negative, and can have leading zeroes. Leading and trailing white space is allowed. | | projection.columnName.interval | `1` `5` | Optional. A positive integer that specifies the interval between successive partition values for the column columnName. For example, a range value of "1,3" with an interval value of "1" produces the values 1, 2, and 3. The same range value with an interval value of "2" produces the values 1 and 3, skipping 2. Leading and trailing white space is allowed. The default is 1. | | projection.columnName.digits | `1` `5` | Optional. A positive integer that specifies the number of digits to include in the partition value's final representation for column columnName. For example, a range value of "1,3" that has a digits value of "1" produces the values 1, 2, and 3. The same range value with a digits value of "2" produces the values 01, 02, and 03. Leading and trailing white space is allowed. The default is no static number of digits and no leading zeroes. | ## Date type Use the date type for partition columns whose values are interpretable as dates (with optional times) within a defined range. **Important** Projected date columns are generated in Coordinated Universal Time (UTC) at query execution time. **** | Property name | Example values | Description | | --- | --- | --- | | projection.columnName.type | `date` | Required. The projection type to use for column columnName. The value must be date (case insensitive) to signal the use of the date type. Leading and trailing white space is allowed. | | projection.columnName.range | `201701,201812` `01-01-2010,12-31-2018` `NOW-3YEARS,NOW` `201801,NOW+1MONTH` | Required. A two-element, comma-separated list which provides the minimum and maximum `range` values for the column *columnName*. These values are inclusive and can use any format compatible with the Java `java.time.*` date types. Both the minimum and maximum values must use the same format. The format specified in the `.format` property must be the format used for these values. This column can also contain relative date strings, formatted in this regular expression pattern: `\s*NOW\s*(([\+\-])\s*([0-9]+)\s*(YEARS?\|MONTHS?\|WEEKS?\|DAYS?\|HOURS?\|MINUTES?\|SECONDS?)\s*)?` White spaces are allowed, but in date literals are considered part of the date strings themselves. | | projection.columnName.format | `yyyyMM` `dd-MM-yyyy` `dd-MM-yyyy-HH-mm-ss` | Required. A date format string based on the Java date format [DateTimeFormatter](https://docs.oracle.com/javase/8/docs/api/java/time/format/DateTimeFormatter.html). Can be any supported Java.time.\$1 type. | | projection.columnName.interval | `1` `5` | A positive integer that specifies the interval between successive partition values for column *columnName*. For example, a `range` value of `2017-01,2018-12` with an `interval` value of `1` and an `interval.unit` value of `MONTHS` produces the values 2017-01, 2017-02, 2017-03, and so on. The same `range` value with an `interval` value of `2` and an `interval.unit` value of `MONTHS` produces the values 2017-01, 2017-03, 2017-05, and so on. Leading and trailing white space is allowed. When the provided dates are at single-day or single-month precision, the `interval` is optional and defaults to 1 day or 1 month, respectively. Otherwise, `interval` is required. | | projection.columnName.interval.unit | `YEARS` `MONTHS` `WEEKS` `DAYS` `HOURS` `MINUTES` `SECONDS` `MILLIS` | A time unit word that represents the serialized form of a [ChronoUnit](https://docs.oracle.com/javase/8/docs/api/java/time/temporal/ChronoUnit.html). Possible values are `YEARS`, `MONTHS`, `WEEKS`, `DAYS`, `HOURS`, `MINUTES`, `SECONDS`, or `MILLIS`. These values are case insensitive. When the provided dates are at single-day or single-month precision, the `interval.unit` is optional and defaults to 1 day or 1 month, respectively. Otherwise, the `interval.unit` is required. | **Example – Partitioning by month** The following example table configuration partitions data by month from 2015 to the present. ``` 'projection.month.type'='date', 'projection.month.format'='yyyy-MM', 'projection.month.interval'='1', 'projection.month.interval.unit'='MONTHS', 'projection.month.range'='2015-01,NOW', ... ``` ## Injected type Use the injected type for partition columns with possible values that cannot be procedurally generated within some logical range but that are provided in a query's `WHERE` clause as a single value. It is important to keep in mind the following points: + Queries on injected columns fail if a filter expression is not provided for each injected column. + Queries with multiple values for a filter expression on an injected column succeed only if the values are disjunct. + Only columns of `string` type are supported. + When you use the `WHERE IN` clause with an injected partition column, there is a limit of 1,000 values that you can specify in the `IN` list. To query a dataset with more than 1,000 partitions for an injected column, split the query into multiple smaller queries, each with up to 1,000 values in the `WHERE IN` clause, and then aggregate the results. **** | Property name | Value | Description | | --- | --- | --- | | projection.columnName.type | `injected` | Required. The projection type to use for the column columnName. Only the string type is supported. The value specified must be injected (case insensitive). Leading and trailing white space is allowed. | For more information, see [When to use the `injected` projection type](partition-projection-dynamic-id-partitioning.md#partition-projection-injection). # Use dynamic ID partitioning When your data is partitioned by a property with high cardinality or when the values cannot be known in advance, you can use the `injected` projection type. Examples of such properties are user names, and IDs of devices or products. When you use the `injected` projection type to configure a partition key, Athena uses values from the query itself to compute the set of partitions that will be read. For Athena to be able to run a query on a table that has a partition key configured with the `injected` projection type, the following must be true: + Your query must include at least one value for the partition key. + The value(s) must be literals or expressions that can be evaluated without reading any data. If any of these criteria are not met, your query fails with the following error: CONSTRAINT\$1VIOLATION: For the injected projected partition column *column\$1name*, the WHERE clause must contain only static equality conditions, and at least one such condition must be present. ## When to use the `injected` projection type Imagine you have a data set that consists of events from IoT devices, partitioned on the devices' IDs. This data set has the following characteristics: + The device IDs are generated randomly. + New devices are provisioned frequently. + There are currently hundreds of thousands of devices, and in the future there will be millions. This data set is difficult to manage using traditional metastores. It is difficult to keep the partitions in sync between the data storage and the metastore, and filtering partitions can be slow during query planning. But if you configure a table to use partition projection and use the `injected` projection type, you have two advantages: you don't have to manage partitions in the metastore, and your queries don't have to look up partition metadata. The following `CREATE TABLE` example creates a table for the device event data set just described. The table uses the injected projection type. ``` CREATE EXTERNAL TABLE device_events ( event_time TIMESTAMP, data STRING, battery_level INT ) PARTITIONED BY ( device_id STRING ) LOCATION "s3://amzn-s3-demo-bucket/prefix/" TBLPROPERTIES ( "projection.enabled" = "true", "projection.device_id.type" = "injected", "storage.location.template" = "s3://amzn-s3-demo-bucket/prefix/${device_id}" ) ``` The following example query looks up the number of events received from three specific devices over the course of 12 hours. ``` SELECT device_id, COUNT(*) AS events FROM device_events WHERE device_id IN ( '4a770164-0392-4a41-8565-40ed8cec737e', 'f71d12cf-f01f-4877-875d-128c23cbde17', '763421d8-b005-47c3-ba32-cc747ab32f9a' ) AND event_time BETWEEN TIMESTAMP '2023-11-01 20:00' AND TIMESTAMP '2023-11-02 08:00' GROUP BY device_id ``` When you run this query, Athena sees the three values for the `device_id` partition key and uses them to compute the partition locations. Athena uses the value for the `storage.location.template` property to generate the following locations: + `s3://amzn-s3-demo-bucket/prefix/4a770164-0392-4a41-8565-40ed8cec737e` + `s3://amzn-s3-demo-bucket/prefix/f71d12cf-f01f-4877-875d-128c23cbde17` + `s3://amzn-s3-demo-bucket/prefix/763421d8-b005-47c3-ba32-cc747ab32f9a` If you leave out the `storage.location.template` property from the partition projection configuration, Athena uses Hive-style partitioning to project partition locations based on the value in `LOCATION` (for example, `s3://amzn-s3-demo-bucket/prefix/device_id=4a770164-0392-4a41-8565-40ed8cec737e`). # Amazon Data Firehose example When you use Firehose to deliver data to Amazon S3, the default configuration writes objects with keys that look like the following example: ``` s3://amzn-s3-demo-bucket/prefix/yyyy/MM/dd/HH/file.extension ``` To create an Athena table that finds the partitions automatically at query time, instead of having to add them to the AWS Glue Data Catalog as new data arrives, you can use partition projection. The following `CREATE TABLE` example uses the default Firehose configuration. ``` CREATE EXTERNAL TABLE my_ingested_data ( ... ) ... PARTITIONED BY ( datehour STRING ) LOCATION "s3://amzn-s3-demo-bucket/prefix/" TBLPROPERTIES ( "projection.enabled" = "true", "projection.datehour.type" = "date", "projection.datehour.format" = "yyyy/MM/dd/HH", "projection.datehour.range" = "2021/01/01/00,NOW", "projection.datehour.interval" = "1", "projection.datehour.interval.unit" = "HOURS", "storage.location.template" = "s3://amzn-s3-demo-bucket/prefix/${datehour}/" ) ``` The `TBLPROPERTIES` clause in the `CREATE TABLE` statement tells Athena the following: + Use partition projection when querying the table + The partition key `datehour` is of type `date` (which includes an optional time) + How the dates are formatted + The range of date times. Note that the values must be separated by a comma, not a hyphen. + Where to find the data on Amazon S3. When you query the table, Athena calculates the values for `datehour` and uses the storage location template to generate a list of partition locations. **Topics** + [How to use the `date` type](partition-projection-kinesis-firehose-example-using-the-date-type.md) + [How to choose partition keys](partition-projection-kinesis-firehose-example-choosing-partition-keys.md) + [How to use custom prefixes and dynamic partitioning](partition-projection-kinesis-firehose-example-using-custom-prefixes-and-dynamic-partitioning.md) # How to use the `date` type When you use the `date` type for a projected partition key, you must specify a range. Because you have no data for dates before the Firehose delivery stream was created, you can use the date of creation as the start. And because you do not have data for dates in the future, you can use the special token `NOW` as the end. In the `CREATE TABLE` example, the start date is specified as January 1, 2021 at midnight UTC. **Note** Configure a range that matches your data as closely as possible so that Athena looks only for existing partitions. When a query is run on the sample table, Athena uses the conditions on the `datehour` partition key in combination with the range to generate values. Consider the following query: ``` SELECT * FROM my_ingested_data WHERE datehour >= '2020/12/15/00' AND datehour < '2021/02/03/15' ``` The first condition in the `SELECT` query uses a date that is before the start of the date range specified by the `CREATE TABLE` statement. Because the partition projection configuration specifies no partitions for dates before January 1, 2021, Athena looks for data only in the following locations, and ignores the earlier dates in the query. ``` s3://amzn-s3-demo-bucket/prefix/2021/01/01/00/ s3://amzn-s3-demo-bucket/prefix/2021/01/01/01/ s3://amzn-s3-demo-bucket/prefix/2021/01/01/02/ ... s3://amzn-s3-demo-bucket/prefix/2021/02/03/12/ s3://amzn-s3-demo-bucket/prefix/2021/02/03/13/ s3://amzn-s3-demo-bucket/prefix/2021/02/03/14/ ``` Similarly, if the query ran at a date and time before February 3, 2021 at 15:00, the last partition would reflect the current date and time, not the date and time in the query condition. If you want to query for the most recent data, you can take advantage of the fact that Athena does not generate future dates and specify only a beginning `datehour`, as in the following example. ``` SELECT * FROM my_ingested_data WHERE datehour >= '2021/11/09/00' ``` # How to choose partition keys You can specify how partition projection maps the partition locations to partition keys. In the `CREATE TABLE` example in the previous section, the date and hour were combined into one partition key called datehour, but other schemes are possible. For example, you could also configure a table with separate partition keys for the year, month, day, and hour. However, splitting dates into year, month, and day means that the `date` partition projection type can't be used. An alternative is to separate the date from the hour to still leverage the `date` partition projection type, but make queries that specify hour ranges easier to read. With that in mind, the following `CREATE TABLE` example separates the date from the hour. Because `date` is a reserved word in SQL, the example uses `day` as the name for the partition key that represents the date. ``` CREATE EXTERNAL TABLE my_ingested_data2 ( ... ) ... PARTITIONED BY ( day STRING, hour INT ) LOCATION "s3://amzn-s3-demo-bucket/prefix/" TBLPROPERTIES ( "projection.enabled" = "true", "projection.day.type" = "date", "projection.day.format" = "yyyy/MM/dd", "projection.day.range" = "2021/01/01,NOW", "projection.day.interval" = "1", "projection.day.interval.unit" = "DAYS", "projection.hour.type" = "integer", "projection.hour.range" = "0,23", "projection.hour.digits" = "2", "storage.location.template" = "s3://amzn-s3-demo-bucket/prefix/${day}/${hour}/" ) ``` In the example `CREATE TABLE` statement, the hour is a separate partition key, configured as an integer. The configuration for the hour partition key specifies the range 0 to 23, and that the hour should be formatted with two digits when Athena generates the partition locations. A query for the `my_ingested_data2` table might look like this: ``` SELECT * FROM my_ingested_data2 WHERE day = '2021/11/09' AND hour > 3 ``` ## Understand partition key and partition projection data types Note that `datehour` key in the first `CREATE TABLE` example is configured as `date` in the partition projection configuration, but the type of the partition key is `string`. The same is true for `day` in the second example. The types in the partition projection configuration only tell Athena how to format the values when it generates the partition locations. The types that you specify do not change the type of the partition key — in queries, `datehour` and `day` are of type `string`. When a query includes a condition like `day = '2021/11/09'`, Athena parses the string on the right side of the expression using the date format specified in the partition projection configuration. After Athena verifies that the date is within the configured range, it uses the date format again to insert the date as a string into the storage location template. Similarly, for a query condition like `day > '2021/11/09'`, Athena parses the right side and generates a list of all matching dates within the configured range. It then uses the date format to insert each date into the storage location template to create the list of partition locations. Writing the same condition as `day > '2021-11-09'` or `day > DATE '2021-11-09'` does not work. In the first case, the date format does not match (note the hyphens instead of the forward slashes), and in the second case, the data types do not match. # How to use custom prefixes and dynamic partitioning Firehose can be configured with [custom prefixes](https://docs.aws.amazon.com/firehose/latest/dev/s3-prefixes.html) and [dynamic partitioning](https://docs.aws.amazon.com/firehose/latest/dev/dynamic-partitioning.html). Using these features, you can configure the Amazon S3 keys and set up partitioning schemes that better support your use case. You can also use partition projection with these partitioning schemes and configure them accordingly. For example, you could use the custom prefix feature to get Amazon S3 keys that have ISO formatted dates instead of the default `yyyy/MM/dd/HH` scheme. You can also combine custom prefixes with dynamic partitioning to extract a property like `customer_id` from Firehose messages, as in the following example. ``` prefix/!{timestamp:yyyy}-!{timestamp:MM}-!{timestamp:dd}/!{partitionKeyFromQuery:customer_id}/ ``` With that Amazon S3 prefix, the Firehose delivery stream would write objects to keys such as `s3://amzn-s3-demo-bucket/prefix/2021-11-01/customer-1234/file.extension`. For a property like `customer_id`, where the values may not be known in advance, you can use the partition projection type `injected` and use a `CREATE TABLE` statement like the following: ``` CREATE EXTERNAL TABLE my_ingested_data3 ( ... ) ... PARTITIONED BY ( day STRING, customer_id STRING ) LOCATION "s3://amzn-s3-demo-bucket/prefix/" TBLPROPERTIES ( "projection.enabled" = "true", "projection.day.type" = "date", "projection.day.format" = "yyyy-MM-dd", "projection.day.range" = "2021-01-01,NOW", "projection.day.interval" = "1", "projection.day.interval.unit" = "DAYS", "projection.customer_id.type" = "injected", "storage.location.template" = "s3://amzn-s3-demo-bucket/prefix/${day}/${customer_id}/" ) ``` When you query a table that has a partition key of type `injected`, your query must include a value for that partition key. A query for the `my_ingested_data3` table might look like this: ``` SELECT * FROM my_ingested_data3 WHERE day BETWEEN '2021-11-01' AND '2021-11-30' AND customer_id = 'customer-1234' ``` ## Use the DATE type for the day partition key Because the values for the `day` partition key are ISO formatted, you can also use the `DATE` type for the day partition key instead of `STRING`, as in the following example: ``` PARTITIONED BY (day DATE, customer_id STRING) ``` When you query, this strategy allows you to use date functions on the partition key without parsing or casting, as in the following example: ``` SELECT * FROM my_ingested_data3 WHERE day > CURRENT_DATE - INTERVAL '7' DAY AND customer_id = 'customer-1234' ``` **Note** Specifying a partition key of the `DATE` type assumes that you have used the [custom prefix](https://docs.aws.amazon.com/firehose/latest/dev/s3-prefixes.html) feature to create Amazon S3 keys that have ISO formatted dates. If you are using the default Firehose format of `yyyy/MM/dd/HH`, you must specify the partition key as type `string` even though the corresponding table property is of type `date`, as in the following example: ``` PARTITIONED BY ( `mydate` string) TBLPROPERTIES ( 'projection.enabled'='true', ... 'projection.mydate.type'='date', 'storage.location.template'='s3://amzn-s3-demo-bucket/prefix/${mydate}') ``` # Prevent Amazon S3 throttling Throttling is the process of limiting the rate at which you use a service, an application, or a system. In AWS, you can use throttling to prevent overuse of the Amazon S3 service and increase the availability and responsiveness of Amazon S3 for all users. However, because throttling limits the rate at which the data can be transferred to or from Amazon S3, it's important to consider preventing your interactions from being throttled. As pointed out in the [performance tuning](performance-tuning.md) chapter, optimizations can depend on your service level decisions, on how you structure your tables and data, and on how you write your queries. **Topics** + [Reduce throttling at the service level](performance-tuning-s3-throttling-reduce-throttling-at-the-service-level.md) + [Optimize your tables](performance-tuning-s3-throttling-optimizing-your-tables.md) + [Optimize your queries](performance-tuning-s3-throttling-optimizing-queries.md) # Reduce throttling at the service level To avoid Amazon S3 throttling at the service level, you can monitor your usage and adjust your [service quotas](https://docs.aws.amazon.com/general/latest/gr/s3.html#limits_s3), or you use certain techniques like partitioning. The following are some of the conditions that can lead to throttling: + **Exceeding your account's API request limits** – Amazon S3 has default API request limits that are based on account type and usage. If you exceed the maximum number of requests per second for a single prefix, your requests may be throttled to prevent overload of the Amazon S3 service. + **Insufficient partitioning of data** – If you do not properly partition your data and transfer a large amount of data, Amazon S3 can throttle your requests. For more information about partitioning, see the [Use partitioning](performance-tuning-s3-throttling-optimizing-your-tables.md#performance-tuning-s3-throttling-use-partitioning) section in this document. + **Large number of small objects** – If possible, avoid having a large number of small files. Amazon S3 has a limit of [5500 GET requests](https://docs.aws.amazon.com/AmazonS3/latest/userguide/optimizing-performance.html) per second per partitioned prefix, and your Athena queries share this same limit. If you scan millions of small objects in a single query, your query will likely be throttled by Amazon S3. To avoid excess scanning, you can use AWS Glue ETL to periodically compact your files, or you partition the table and add partition key filters. For more information, see the following resources. + [How can I configure an AWS Glue ETL job to output larger files?](https://aws.amazon.com/premiumsupport/knowledge-center/glue-job-output-large-files/) (*AWS Knowledge Center*) + [Reading input files in larger groups](https://docs.aws.amazon.com/glue/latest/dg/grouping-input-files.html) (*AWS Glue Developer Guide*) # Optimize your tables Structuring your data is important if you encounter throttling issues. Although Amazon S3 can handle large amounts of data, throttling sometimes occurs because of the way the data is structured. The following sections offer some suggestions on how to structure your data in Amazon S3 to avoid throttling issues. ## Use partitioning You can use partitioning to reduce throttling by limiting the amount of data that has to be accessed at any given time. By partitioning data on specific columns, you can distribute requests evenly across multiple objects and reduce the number of requests for a single object. Reducing the amount of data that must be scanned improves query performance and lowers cost. You can define partitions, which act as virtual columns, when you create a table. To create a table with partitions in a `CREATE TABLE` statement, you use the `PARTITIONED BY (column_name data_type)` clause to define the keys to partition your data. To restrict the partitions scanned by a query, you can specify them as predicates in a `WHERE` clause of the query. Thus, columns that are frequently used as filters are good candidates for partitioning. A common practice is to partition the data based on time intervals, which can lead to a multi-level partitioning scheme. Note that partitioning also has a cost. When you increase the number of partitions in your table, the time required to retrieve and process partition metadata also increases. Thus, over-partitioning can remove the benefits you gain by partitioning more judiciously. If your data is heavily skewed to one partition value, and most queries use that value, then you may incur the additional overhead. For more information about partitioning in Athena, see [What is partitioning?](ctas-partitioning-and-bucketing-what-is-partitioning.md) ## Bucket your data Another way to partition your data is to bucket the data within a single partition. With bucketing, you specify one or more columns that contain rows that you want to group together. Then, you put those rows into multiple buckets. This way, you query only the bucket that must be read, which reduces the number of rows of data that must be scanned. When you select a column to use for bucketing, select the column that has high cardinality (that is, that has many distinct values), is uniformly distributed, and is frequently used to filter the data. An example of a good column to use for bucketing is a primary key, such as an ID column. For more information about bucketing in Athena, see [What is bucketing?](ctas-partitioning-and-bucketing-what-is-bucketing.md) ## Use AWS Glue partition indexes You can use AWS Glue partition indexes to organize data in a table based on the values of one or more partitions. AWS Glue partition indexes can reduce the number of data transfers, the amount of data processing, and the time for queries to process. An AWS Glue partition index is a metadata file that contains information about the partitions in the table, including the partition keys and their values. The partition index is stored in an Amazon S3 bucket and is updated automatically by AWS Glue as new partitions are added to the table. When an AWS Glue partition index is present, queries attempt to fetch a subset of the partitions instead of loading all the partitions in the table. Queries only run on the subset of data that is relevant to the query. When you create a table in AWS Glue, you can create a partition index on any combination of partition keys defined on the table. After you have created one or more partition indexes on a table, you must add a property to the table that enables partition filtering. Then, you can query the table from Athena. For information about creating partition indexes in AWS Glue, see [Working with partition indexes in AWS Glue](https://docs.aws.amazon.com/glue/latest/dg/partition-indexes.html) in the *AWS Glue Developer Guide*. For information about adding a table property to enable partition filtering, see [Optimize queries with AWS Glue partition indexing and filtering](glue-best-practices-partition-index.md). ## Use data compression and file splitting Data compression can speed up queries significantly if files are at their optimal size or if they can be split into logical groups. Generally, higher compression ratios require more CPU cycles to compress and decompress the data. For Athena, we recommend that you use either Apache Parquet or Apache ORC, which compress data by default. For information about data compression in Athena, see [Use compression in Athena](compression-formats.md). Splitting files increases parallelism by allowing Athena to distribute the task of reading a single file among multiple readers. If a single file is not splittable, only a single reader can read the file while other readers are idle. Apache Parquet and Apache ORC also support splittable files. ## Use optimized columnar data stores Athena query performance improves significantly if you convert your data into a columnar format. When you generate columnar files, one optimization technique to consider is ordering the data based on partition key. Apache Parquet and Apache ORC are commonly used open source columnar data stores. For information on converting existing Amazon S3 data source to one of these formats, see [Convert to columnar formats](columnar-storage.md#convert-to-columnar). ### Use a larger Parquet block size or ORC stripe size Parquet and ORC have data storage parameters that you can tune for optimization. In Parquet, you can optimize for block size. In ORC, you can optimize for stripe size. The larger the block or stripe, the more rows that you can store in each. By default, the Parquet block size is 128 MB, and the ORC stripe size is 64 MB. If an ORC stripe is less than 8 MB (the default value of `hive.orc.max_buffer_size`), Athena reads the whole ORC stripe. This is the tradeoff Athena makes between column selectivity and input/output operations per second for smaller stripes. If you have tables with a very large number of columns, a small block or stripe size can cause more data to be scanned than necessary. In these cases, a larger block size can be more efficient. ### Use ORC for complex types Currently, when you query columns stored in Parquet that have complex data types (for example, `array`, `map`, or `struct`), Athena reads an entire row of data instead of selectively reading only the specified columns. This is a known issue in Athena. As a workaround, consider using ORC. ### Choose a compression algorithm Another parameter that you can configure is the compression algorithm on data blocks. For information about the compression algorithms supported for Parquet and ORC in Athena, see [Athena compression support](https://docs.aws.amazon.com/athena/latest/ug/compression-formats.html). For more information about optimization of columnar storage formats in Athena, see the section "Optimize columnar data store generation" in the AWS Big Data Blog post [Top 10 Performance Tuning Tips for Amazon Athena](https://aws.amazon.com/blogs/big-data/top-10-performance-tuning-tips-for-amazon-athena/). ## Use Iceberg tables Apache Iceberg is an open table format for very large analytic datasets that is designed for optimized usage on Amazon S3. You can use Iceberg tables to help reduce throttling in Amazon S3. Iceberg tables offer you the following advantages: + You can partition Iceberg tables on one or more columns. This optimizes data access and reduces the amount of data that must be scanned by queries. + Because Iceberg object storage mode optimizes Iceberg tables to work with Amazon S3, it can process large volumes of data and heavy query workloads. + Iceberg tables in object storage mode are scalable, fault tolerant, and durable, which can help reduce throttling. + ACID transaction support means that multiple users can add and delete Amazon S3 objects in an atomic manner. For more information about Apache Iceberg, see [Apache Iceberg](https://iceberg.apache.org/). For more information about using Apache Iceberg tables in Athena, see [Using Iceberg tables](https://docs.aws.amazon.com/athena/latest/ug/querying-iceberg.html). # Optimize your queries Use the suggestions in this section for optimizing your SQL queries in Athena. ## Use LIMIT with the ORDER BY clause The `ORDER BY` clause returns data in a sorted order. This requires Athena to send all rows of data to a single worker node and then sort the rows. This type of query can run for a long time or even fail. For greater efficiency in your queries, look at the top or bottom *N* values, and then also use a `LIMIT` clause. This significantly reduces the cost of the sort by pushing both sorting and limiting to individual worker nodes rather than to a single worker. ## Optimize JOIN clauses When you join two tables, Athena distributes the table on the right to worker nodes, and then streams the table on the left to perform the join. For this reason, specify the larger table on the left side of the join and the smaller table on the right side of the join. This way, Athena uses less memory and runs the query with lower latency. Also note the following points: + When you use multiple `JOIN` commands, specify tables from largest to smallest. + Avoid cross joins unless they are required by the query. ## Optimize GROUP BY clauses The `GROUP BY` operator distributes rows based on the `GROUP BY` columns to the worker nodes. These columns are referenced in memory and the values are compared as the rows are ingested. The values are aggregated together when the `GROUP BY` column matches. In consideration of the way this process works, it is advisable to order the columns from the highest cardinality to the lowest. ## Use numbers instead of strings Because numbers require less memory and are faster to process compared to strings, use numbers instead of strings when possible. ## Limit the number of columns To reduce the total amount of memory required to store your data, limit the number of columns specified in your `SELECT` statement. ## Use regular expressions instead of LIKE Queries that include clauses such as `LIKE '%string%'` on large strings can be very computationally intensive. When you filter for multiple values on a string column, use the [regexp\$1like()](https://trino.io/docs/current/functions/regexp.html#regexp_like) function and a regular expression instead. This is particularly useful when you compare a long list of values. ## Use the LIMIT clause Instead of selecting all columns when you run a query, use the `LIMIT` clause to return only the columns that you require. This reduces the size of the dataset that is processed through the query execution pipeline. `LIMIT` clauses are more helpful when you query tables that have a large of number of columns that are string-based. `LIMIT` clauses are also helpful when you perform multiple joins or aggregations on any query. # Additional resources For additional information about performance tuning in Athena, consider the following resources: + AWS Big Data blog post: [Top 10 performance tuning tips for Amazon Athena](https://aws.amazon.com/blogs/big-data/top-10-performance-tuning-tips-for-amazon-athena/). + AWS Big Data blog post: [Run queries 3x faster with up to 70% cost savings on the latest Amazon Athena engine](https://aws.amazon.com/blogs/big-data/run-queries-3x-faster-with-up-to-70-cost-savings-on-the-latest-amazon-athena-engine/) in the *AWS Big Data Blog*. + AWS Big Data blog post: [Improve federated queries with predicate pushdown in Amazon Athena](https://aws.amazon.com/blogs/big-data/improve-federated-queries-with-predicate-pushdown-in-amazon-athena/). + Amazon Simple Storage Service User Guide: [Best practices design patterns: optimizing Amazon S3 performance](https://docs.aws.amazon.com/AmazonS3/latest/userguide/optimizing-performance.html). + Other [Athena posts in the AWS big data blog](https://aws.amazon.com/blogs/big-data/tag/amazon-athena/). + Ask a question on [AWS re:Post](https://repost.aws/tags/TA78iVOM7gR62_QqDe2-CmiA/amazon-athena) using the **Amazon Athena** tag. + Consult the [Athena topics in the AWS knowledge center](https://aws.amazon.com/premiumsupport/knowledge-center/#Amazon_Athena). + Contact AWS Support (in the AWS Management Console, click **Support**, **Support Center**)