Amazon Athena
Cloud service · AWS · Updated

Amazon Athena

In short

Amazon Athena is a query service for analyzing data in Amazon S3 using SQL, allowing direct data analysis in S3 without loading it into databases or warehouses.

Cloud Services Hub →

Free Amazon Athena tools

13 key features of Amazon Athena

01
Serverless

Automatically scales, requiring no infrastructure management, allowing users to focus on querying data

02
Direct SQL on S3

Enables direct querying of data stored in Amazon S3 using SQL, eliminating the need for data loading into separate databases.

03
Parallel Query Execution

Enhances performance by executing queries in parallel, ensuring fast results for large datasets and complex queries.

04
High Availability

Distributes queries across multiple facilities to ensure continuous service, even if one facility is down.

05
Interactive Querying

Built-in SQL editor for interactive analysis and immediate querying, with schema definition flexibility.

06
Support for Various Data Formats

Queries multiple data formats like CSV, JSON, ORC, Parquet, and Avro, accommodating diverse data analysis needs.

07
SQL with Trino and Presto

Supports complex SQL queries, including joins, window functions, and arrays.

08
Apache Spark Integration

Supports running Apache Spark applications, allowing for complex data processing without Spark cluster management.

09
Federated SQL Queries

Data source connectors enable federated SQL queries against data sources outside Amazon S3.

10
Partition Projection

Partition projection speeds up query processing for highly partitioned tables and automates partition management.

11
User-Defined Functions

SQL queries can use your own user-defined functions.

12
Machine Learning Inference in SQL

SQL statements can use machine learning inference from Amazon SageMaker AI.

13
Workgroup Data Usage Controls

Workgroups support a per-query scanned-data limit that cancels queries exceeding it, and workgroup-wide scanned-data thresholds over hourly or daily periods that can trigger Amazon SNS notifications.

Where Amazon Athena is used

  1. 01
    Log Analysis

    Useful for querying and analyzing logs from web servers, applications, and devices to troubleshoot and gain operational insights.

  2. 02
    Data Exploration

    Enables querying and exploration of large datasets in S3 to identify patterns and relationships.

  3. 03
    Report Generation

    Facilitates the creation of reports on metrics, trends, and aggregations, with integration into BI tools like AWS Quick for enhanced visualization.

  4. 04
    Serverless ETL

    Performs extract, transform, and load operations directly in S3 using SQL, eliminating the need for infrastructure management.

  5. 05
    Data Lake Analysis

    Supports comprehensive analysis of data lakes, merging data from various sources for unified insights.

  6. 06
    Ad-hoc Querying:

    Ideal for executing spontaneous queries to test hypotheses, detect anomalies, or troubleshoot.

  7. 07
    Cost Optimization

    Analyzes AWS billing and usage reports to identify cost-saving opportunities and optimize resource allocation.

  8. 08
    Research Support

    Assists in querying and analyzing scientific or experimental data for pattern recognition and hypothesis testing.

  9. 09
    Compliance Auditing

    Facilitates historical data analysis for regulatory compliance and auditing purposes.

  10. 10
    Machine Learning Preparation

    Prepares and refines data for machine learning, ensuring quality and structure compatibility.

How Amazon Athena is priced

Pay per query

Charges are incurred based on the volume of data each query scans, measured in terabytes. Costs decrease when data is compressed, partitioned, or stored in a columnar format, as these methods reduce the scanned data volume.

Capacity Reservations

You pay an hourly rate for reserved dedicated SQL computing capacity, with a minimum reservation duration of 1 minute and a minimum capacity of 4 Data Processing Units (DPUs). This offers more predictable pricing for steady query loads.

Apache Spark compute

Athena for Apache Spark charges for compute usage measured in DPU-hours rather than the volume of data scanned.

Services Amazon Athena works with

Amazon S3Amazon S3

Amazon Athena directly queries data stored in Amazon S3 using standard SQL, enabling you to analyze large-scale datasets without the need for ETL processes.

AWS GlueAWS Glue

AWS Glue provides a data catalog that helps Amazon Athena understand the schema and location of your data, making it easier to run queries and maintain metadata.

Amazon QuickSightAmazon QuickSight

Amazon QuickSight integrates with Amazon Athena to visualize data and generate business intelligence insights using the queried data.

Amazon CloudWatchAmazon CloudWatch

Amazon CloudWatch gathers logs, metrics, and events from Amazon Athena, enabling you to monitor query performance and set alarms for operational issues.

AWS LambdaAWS Lambda

AWS Lambda can be triggered by the completion of Amazon Athena queries, allowing for automation of functions such as data transformation and notification.

Amazon Data FirehoseAmazon Data Firehose

Amazon Data Firehose can deliver streaming data directly into Amazon S3, allowing Amazon Athena to query real-time data alongside historical data stored in S3.

Amazon RedshiftAmazon Redshift

Amazon Athena provides a built-in data source connector for querying data in Amazon Redshift.

Amazon AppFlowAmazon AppFlow

Athena can query Amazon AppFlow output in Amazon S3 using tables that AppFlow automatically registers in the AWS Glue Data Catalog. This lets you analyze transferred SaaS records with SQL without manually defining their table schemas.

Amazon AuroraAmazon Aurora

Athena can query Aurora MySQL and Aurora PostgreSQL databases through federated data source connectors. Queries can join operational records in Aurora with datasets stored in Amazon S3.

Amazon Athena exam questions

Real practice exam questions on Amazon Athena, with the correct answer and its explanation. Answer them with every option in the exam simulator.

CLF-C02

A company is selecting an AWS analytics service for occasional investigations of archived website activity. The finance team wants a serverless SQL query service whose query charges are based on the amount of source data scanned. Reducing the data scanned by each query should directly reduce query costs. Which service and pricing model meet these requirements?

Show answerCORRECT ANSWER

Amazon Athena, with standard SQL query pricing based on the amount of data scanned

Amazon Athena provides serverless SQL queries. Under its standard per-query pricing model, charges depend on the amount of data scanned, so scanning less data can directly lower query costs.

MLA-C02 (beta)

A machine learning team stores several terabytes of daily observations in Amazon S3 as CSV files partitioned by observation date. An Amazon Athena external table exposes all 180 columns. Feature engineering queries already filter on the date partition, but each query uses only 5–10 columns and still scans large amounts of data. Different experiments require different columns, so the curated dataset must retain all 180 columns. The team wants to use Athena SQL to create a reusable curated dataset that minimizes reading of unrequested columns during subsequent queries. Which solution best meets these requirements?

Show answerCORRECT ANSWER

Use an Athena CREATE TABLE AS SELECT statement to write all columns as compressed Parquet data to a new S3 location, preserving the observation-date partitions.

Parquet stores values in column chunks, allowing Athena to read the columns needed by each query without reading all 180 columns. The curated table retains every field for future experiments, while date partition pruning and compression provide complementary scan reductions. CTAS performs the conversion using Athena SQL.

SAP-C03

A company uses AWS Organizations with consolidated billing. Finance is building an Amazon Athena reporting pipeline from an organization-wide AWS Cost and Usage Report (CUR) delivered to Amazon S3.

Auditors require the company to retain every delivered report version so they can reproduce previously published financial results. Operational reports must instead use only the latest complete data for each billing month, including updates that AWS publishes after a month is finalized. During testing, engineers discover that successive deliveries contain overlapping month-to-date data. Corrections can change or remove previously reported line items.

Which solution meets these requirements without double-counting charges or retaining obsolete line items in operational reports?

Show answerCORRECT ANSWER

Configure CUR to create a new report version for each update and enable refreshes of closed reports. Retain the delivered versions in S3. For each completed delivery, use its manifest to load the complete set of report files into a separate snapshot location, and update the Athena partition for that billing month to reference only that snapshot.

CUR updates are report snapshots, not append-only usage increments. Retaining new versions preserves the audit history, and refreshing closed reports allows subsequent billing adjustments to appear. Processing all files identified by a delivery's manifest and replacing the operational partition's snapshot prevents both duplicate charges and obsolete records from earlier deliveries.

Practice Amazon Athena in a full exam

Questions on Amazon Athena in each practice exam, with every answer option and an explanation for each:

Start a free practice exam

What is Amazon Athena

1 min read

Amazon Athena, provided by AWS, is a serverless, interactive query service that enables the analysis of large-scale data directly in Amazon S3 using standard SQL. Users can execute queries on structured, semi-structured, or unstructured data in formats like CSV, JSON, ORC, Parquet, and Avro, without needing to load it into separate databases or warehouses. 

A query string can contain at most 262144 bytes when encoded in UTF-8. This quota cannot be increased.

Athena automatically scales, managing the query load efficiently across multiple servers, with pricing based on data processed or compute used. It supports SQL through Trino and Presto query engines, facilitating complex queries and integrates with tools like AWS Quick for visualization. 

For SQL workloads, Capacity Reservations let you request dedicated compute in Data Processing Units (DPUs) and assign it to your workloads. Capacity Reservations and per-query billing can be used in the same account for different performance, workload-isolation, and cost goals.

Active DML and DDL query quotas count both running and queued queries. Exceeding an active-query quota results in a TooManyRequestsException.

DML queries have a timeout after which they are canceled; you can request an increase up to a maximum of 240 minutes.

Additionally, Athena offers capabilities for running Apache Spark applications through Python or notebook APIs, enhancing its analytical power without requiring Spark cluster management. This service simplifies data analysis, allowing immediate querying with the option to define data schema manually or automatically, streamlining the process for quick, insightful decision-making.

Learn Amazon Athena in lessons

24 short lessons on Amazon Athena in 8 certification courses.

and 18 more lessons

Open the lessons

Concepts related to Amazon Athena

AWS architecture icons are the property of Amazon Web Services. BlowStack is not affiliated with or endorsed by AWS.