Amazon Athena: Querying S3 with SQL

Amazon Athena lets you run SQL on files that sit in Amazon S3. There is no cluster to start, no data to load and nothing to switch off afterwards. AWS describes it as an interactive query service for analysing data directly in S3 using standard SQL, serverless, where you pay only for the queries you run (Athena docs: what is Athena).

That simplicity makes it the usual first query tool on an AWS data lake. It also hides how the service works, which is what determines whether queries are fast and cheap or slow and expensive.

The idea: a table is only a description

In a database, creating a table allocates storage and loading puts data in it. In Athena neither happens. A table is metadata: it tells Athena where the data is located in S3 and what its structure is, column names and types (Athena docs: tables and catalogs). The files stay where they are, in whatever format they were written.

This is schema-on-read. The schema is applied when a query reads the files, not when the files arrive. Three consequences follow:

• Creating or dropping a table does not touch the data. Drop an external table and the files remain.
• New files appear in query results automatically, as long as they land under the table's location (and, for partitioned tables, in a registered partition).
• A wrong schema fails at query time, as nulls or errors, not at load time.

Table definitions are stored in the AWS Glue Data Catalog, which other services such as Glue jobs and Redshift Spectrum share. You can write the definition yourself or let a Glue crawler infer it from the files.

A first table and query

The following is written from the documentation and has not been run:

CREATE EXTERNAL TABLE trips (
  vendorid int,
  tpep_pickup_datetime timestamp,
  passenger_count bigint,
  trip_distance double,
  payment_type bigint,
  fare_amount double
)
STORED AS PARQUET
LOCATION 's3://amzn-s3-demo-bucket/trips/';

SELECT payment_type,
       count(*) AS trips,
       round(sum(fare_amount), 2) AS fares
FROM trips
WHERE tpep_pickup_datetime >= timestamp '2024-01-15 00:00:00'
  AND tpep_pickup_datetime <  timestamp '2024-01-16 00:00:00'
GROUP BY payment_type
ORDER BY trips DESC;

The first statement registers the table. The second is ordinary SQL. Athena's engine incorporates functionality from the open-source Trino project (Athena docs: engine version 3), so joins, window functions, common table expressions and a large function library are available.

What happens when a query runs

1. Athena reads the table definition from the catalog.
2. It lists the files under the table's location, or under the partitions the query selects.
3. Workers read those files in parallel, apply the filters and aggregations, and combine the results.
4. The result is written as a file to a query result location in S3, and shown in the console.

Step 4 surprises people. Every query writes its output to S3, either to a bucket you specify or to storage Athena manages for you. On your own bucket those files accumulate until a lifecycle rule or a person removes them.

How you pay

By default, billing is per query, on the data scanned. The pricing page states that you are charged for the bytes scanned by each query, rounded up to the nearest megabyte, with a 10 MB minimum (AWS: Athena pricing). Statements such as CREATE TABLE and failed queries are not charged; a cancelled query is charged for what it scanned before cancellation.

Bytes scanned means the bytes read from your files as stored. A query over a compressed, columnar, partitioned table reads a small part of what the same query would read from raw CSV. Two companion articles measure this: Parquet and compression for cheaper Athena queries and partitioning data in S3 for Athena.

For steady, heavy use there is a second model, capacity reservations, which bill for dedicated compute by the hour and not for data scanned.

Guard rails: workgroups

A single careless SELECT * on a large raw table can scan terabytes. Workgroups are the control. A workgroup groups queries, for a team or an application, with its own settings, history and metrics. Two limits are available (Athena docs: data usage controls):

• A per-query limit: any query in the workgroup that scans more than the set amount is cancelled.
• Workgroup-wide limits: thresholds on the total scanned per hour or day, which can send a notification.

Setting a per-query limit on the workgroup analysts use is a sensible first step on any new account.

Where Athena fits

AWS's own guidance draws the lines this way (Athena docs: when to use Athena):

Athena is strong for exploration, log analysis, occasional reports, and queries over a data lake's curated layer. It is a weaker fit for dashboards with many concurrent users needing consistent sub-second answers, and for workloads that update rows frequently. Athena vs Redshift goes through the choice.

Beyond the basics

• Other sources. Federated queries reach databases outside S3 through connectors.
• Writing data. CREATE TABLE AS SELECT and INSERT INTO write query results back to S3 as new tables, which makes Athena usable for light transformation.
• Table formats. Athena can create and query Apache Iceberg tables, which add row-level updates and deletes.
• Spark. Athena can also run Apache Spark code in notebooks, billed by compute time.

Common mistakes

• Querying raw CSV or JSON for regular work. Convert to Parquet.
• No partition filter. The query scans every partition.
• Selecting every column on wide tables. Columnar formats only help if you name columns.
• Many small files. Listing and opening them dominates the run time.
• Forgetting the results bucket. It grows with every query.
• Sharing one workgroup for everything, with no limit.

Athena reads from S3, so the layout of the bucket matters as much as the SQL; Amazon S3 explained covers the storage side.

Amazon S3, AWS Glue and querying data lakes on AWS are covered in the Data Engineering course.

Sources

• What is Amazon Athena? (Amazon Athena User Guide) — AWS Documentation
• Understanding tables, databases, and data catalogs in Athena (Amazon Athena User Guide) — AWS Documentation
• Amazon Athena pricing (Amazon Web Services) — Amazon Web Services
• Configure per-query and per-workgroup data usage controls (Amazon Athena User Guide) — AWS Documentation
• Athena engine version 3 (Amazon Athena User Guide) — AWS Documentation
• When should I use Athena? (Amazon Athena User Guide) — AWS Documentation