AWS Athena: Everything you need to know

AWS Athena is a flexible and cost-effective query service for data stored in AWS S3.

AWS is one of the world’s largest cloud providers. It offers a number of services for your cloud storage and computing needs. AWS S3 is one of the most popular services on the AWS platform. It offers unparalleled durability and data availability while being one of the cheapest storage options in the cloud.

Given its numerous features and theoretically unlimited storage, it’s possible to store terabytes or petabytes of data in an S3 bucket. It would be nearly impossible to analyze such data if you opened every file and read petabytes of data manually. This is where the AWS Athena service comes into play.

Simply put, AWS Athena is used as a data analysis service by simply using SQL queries to access the available data in your S3 buckets. So, as long as you understand the basics of SQL, you can start analyzing S3 data using AWS Athena.

Let’s understand this with a short example. Assume that you have configured one of your buckets as the access log bucket for all balancers in multiple accounts in your organization. How can you query years of log data and gain important, meaningful insights from these log files? The answer is AWS Athena.

Features of AWS Athena

• SQL-based tools: AWS Athena is a very easy-to-use SQL-based service. Simply point Athena to one of your buckets, define a schema for your data, and start using SQL queries within your bucket.
• Serverless: You don’t need to maintain any infrastructure to run AWS Athena. Athena is serverless and optimized to automatically use multiple computing resources depending on your requirements.
• Fast and optimized: Athena is optimized to use an efficient number of resources to deliver query results as quickly as possible. Ideal for small and complex analyzes of S3 data.
• Cost-effective: Athena is a pay-as-you-go service. This means there are no base costs to use AWS Athena. You pay only for the queries you run on the Athena service.
• Data durability and availability: Athena relies on data in S3 buckets to ensure that your data is highly available and durable.
• Support: Athena supports several file formats including JSON, CSV, Avro, and ORC.
• Security: Athena leverages security features such as IAM, bucket policies, and ACLs to provide a high level of safety.
• Athena backend: Athena uses the open source Presto as its backend. Presto is a distributed SQL engine for querying and analyzing big data workloads.

AWS Athena Pricing and Optimization

If you use AWS Athena, there is a $5 fee for each terabyte scanned when using AWS Athena. This price may vary slightly in some AWS Regions.

• Efficient queries : If you are familiar with SQL, you should know that there are multiple ways to use SQL to get specific results from your data. You can optimize Athena by using efficient queries that reduce the time it takes to execute a query.
• Data transformation: If you want to further optimize your queries, you can compress, split, or transform your data into smaller datasets to further reduce query execution time. Using data transformations, you can optimize your queries by up to 90%.
• Joining virtual tables: Joining tables is a very important feature of SQL. Although it may seem like a simple operation, it can become quite complex. We recommend placing large tables on the left and tables with less data on the right.

Differences between AWS Athena and Redshift Spectrum

Redshift Spectrum is another service you can use to query your AWS S3 buckets. Both Redshift Spectrum and Athena are serverless, can run complex queries on S3, and are priced at 5% per terabyte of data processed. So what’s the difference?

performance

AWS Athena uses compute resources from a pool of resources provided by AWS. In contrast, Redshift Spectrum uses resources that are allocated according to the size of your Redshift cluster. This gives you more control over the resources used by the Redshift Spectrum service and allows you to increase the size of your Redshift cluster at any time if you want to improve performance.

Load data for processing

Both services use virtual tables to run SQL queries on your data. Virtual tables are created using Glue Data Catalog for schema management. Athena can use data directly from the Glue Data Catalog schema, but when using Redshift Spectrum, you must populate external tables from the Glue Data Catalog schema.

The main differences between these two services are the main differences when choosing between Redshift spectrum and Athena. If you want to query data in S3 along with data stored in a Redshift data warehouse, or if you are willing to pay higher costs for better query performance in S3, you should use Redshift Spectrum. . Athena is useful when all your data is only in S3 buckets.

Differences between AWS Athena and S3 Select

S3 select is another serverless service from AWS for querying data in S3 using SQL. However, the main difference between S3 Select and Athena is that when using S3 Select, you can only use SQL SELECT queries, whereas Athena can be used for any type of SQL query. Another limitation of S3 select is that you can only perform a SELECT operation on one object at a time.

Therefore, if your requirement is only to retrieve data or a subset of data from an S3 object, you should use S3 Select. To process complex queries and operations such as JOINs or data across S3 buckets, you should use AWS Athena.

Benefits of using AWS Athena

• Athena eliminates the need to develop complex and expensive data analysis tools for your data.
• Athena is serverless, making it a very easy-to-use service. No need to maintain any infrastructure.
• AWS has optimized Athena so that you can get query results within seconds of running an Athena query.
• Athena is serverless, so you don’t have to pay for Athena services. You only pay for the queries you choose to run. Even if you cancel a query, you will only be charged for the data processed, not the entire query.
• Athena easily integrates with other AWS services. One of the most important and valuable integrations of AWS Athena is with the AWS Glue service. AWS Glue is an ETL service that you can use to transform your data into a more efficient and readable format that you can then analyze with AWS Athena.
• Athena allows you to run multiple queries simultaneously.

AWS Athena limitations

• Row size: The row size of a virtual AWS Athena table cannot exceed 32 megabytes. For CSV and JSON files, this limit can be increased up to 100 MB in very limited cases, but we strongly recommend that you limit the row size to 32 MB to avoid unwanted errors. .
• Hidden files: Files whose names start with an underscore (_) or a dot (.) are treated as hidden by the Athena service. This can be used as a feature to avoid processing unnecessary files.
• Athena cannot process data from S3 Glacier or S3 Glacier Deep Archive . It is understood that AWS Athena cannot retrieve data from these storage classes because they are dedicated to data archive options and take minutes to hours to retrieve.
• Athena does not support stored procedures .
• Athena version 1 does not support parameterized queries . This is supported in Athena version 2.
• Statements such as MERGE, UPDATE, CREATE TABLE LIKE, DESCRIBE INPUT , DESCRIBE OUTPUT are not supported.

conclusion

This article introduced AWS Athena, AWS’s data analysis tool, its features, benefits, and some limitations. Athena is one of the most powerful tools for processing and analyzing data in S3 buckets. Service limitations are also very simple and can be circumvented if necessary.

Also see Best Practices for Securing AWS S3 Storage.