How to Query S3 Files with Amazon Athena

Amazon Athena is a serverless query service from AWS that runs standard SQL against files sitting in S3. There is no cluster to spin up, no warehouse to configure, and no data to move. You point Athena at a bucket, define a table, and run SELECT statements. Results appear in seconds to minutes depending on data volume, and you pay only for the bytes scanned.

Athena is particularly useful for teams that already store operational exports in S3 -- sales data from a CRM, event logs from an app, financial exports from accounting tools -- and want to analyze them without moving the data to a separate database.

What You Need Before Starting

You need an active AWS account with permission to access S3 and Athena, and at least one S3 bucket containing data you want to query. Supported formats include CSV, TSV, JSON, Parquet, ORC, and Avro. A second S3 bucket or prefix for storing query results is also required. If your data is already in S3, the full setup takes about 15 minutes.

Step 1: Create a Results Bucket

Athena writes every query result to S3. Before opening the Athena console, create a dedicated bucket or prefix for those results. In the S3 console, create a bucket named something like my-athena-results. Keep it in the same AWS region you plan to use for Athena. Cross-region traffic adds latency and cost.

Step 2: Configure the Query Results Location

Open the Athena console and navigate to Settings > Manage. Set the Query result location to s3://my-athena-results/ and click Save. If you skip this step, every query fails with a results location error. The setting applies per workgroup, so configure it once per workgroup.

Consider setting a query result retention policy in the same screen. Seven days is a reasonable default. Old results accumulate silently and add storage cost over time.

Step 3: Create a Database

Athena uses the AWS Glue Data Catalog to store table definitions. Run this in the Athena query editor to create a database namespace:

CREATE DATABASE my_data;

All tables you create live inside this database. If you work with multiple data sources or teams, use separate databases to keep schemas organized.

Step 4: Define a Table

This is where Athena differs from a traditional database. You are not loading data into anything -- you are pointing Athena at data that already lives in S3. The table definition maps your S3 files to a SQL schema.

Here is a working example for a CSV file with headers:

CREATE EXTERNAL TABLE my_data.sales_2025 (
  order_id STRING,
  customer_id STRING,
  product_name STRING,
  revenue DOUBLE,
  order_date STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
  'separatorChar' = ',',
  'quoteChar' = '"',
  'escapeChar' = '\\'
)
STORED AS TEXTFILE
LOCATION 's3://my-data-bucket/sales/'
TBLPROPERTIES ('skip.header.line.count'='1');

Two things to get right. First, all column names must be lowercase. Athena rejects tables with uppercase column names when interacting with the Glue Data Catalog. Second, use EXTERNAL TABLE rather than CREATE TABLE. An external table does not own the data -- dropping the table removes only the schema definition, not the files in S3.

Once the table is created, test it:

SELECT * FROM my_data.sales_2025 LIMIT 10;

If rows return, your table is working.

Step 5: Run Useful Queries

Athena supports standard SQL including aggregations, joins, window functions, and subqueries. A few practical starting points:

Total revenue by product:

SELECT product_name, SUM(revenue) AS total_revenue
FROM my_data.sales_2025
GROUP BY product_name
ORDER BY total_revenue DESC;

Orders per day:

SELECT order_date, COUNT(*) AS order_count
FROM my_data.sales_2025
GROUP BY order_date
ORDER BY order_date;

Join two S3 datasets:

SELECT s.order_id, s.revenue, c.customer_name
FROM my_data.sales_2025 s
JOIN my_data.customers c ON s.customer_id = c.customer_id
WHERE s.revenue > 500;

Each query scans the full file set in the table location unless you partition your data or convert to a columnar format.

Cutting Costs with Parquet

Athena charges $5 per terabyte of data scanned. A single query against a 10 GB CSV file costs roughly $0.05. That sounds negligible, but 50 analysts running 20 queries per day on a 100 GB dataset generates about 100 TB of scanning per month -- a $500 monthly bill from query costs alone.

Converting that same data to Parquet changes the math. Parquet is a columnar format, so Athena reads only the columns your query actually references rather than scanning every row. A query referencing 3 of 40 columns reads about 7.5 percent of the data volume. On a 100 GB dataset queried 1,000 times per day, that drops monthly query costs from around $500 to roughly $40.

You can convert CSV to Parquet locally with a short Python script:

import pandas as pd
df = pd.read_csv('sales_2025.csv')
df.to_parquet('sales_2025.parquet', index=False)

Upload the Parquet file to S3, then create a new table using STORED AS PARQUET instead of TEXTFILE. Query performance also improves because Parquet metadata lets Athena skip entire row groups that do not match your filter conditions.

Using Partitions

Partitioning splits your data into S3 subdirectories by column value, typically date or region. When a query filters on the partition column, Athena scans only the matching directories and ignores everything else.

A partitioned folder structure looks like:

s3://my-data-bucket/sales/year=2025/month=01/
s3://my-data-bucket/sales/year=2025/month=02/
s3://my-data-bucket/sales/year=2026/month=04/

A query filtered to year=2026 AND month=04 scans only that single directory. For large historical datasets, date partitioning reduces query scan volume by 90 percent or more on typical date-range filters.

Workgroups for Cost Control

Athena workgroups let you enforce data scan limits per query and separate billing by team or project. In the Athena console, navigate to Workgroups and create one with a per-query scan limit set to 1 GB. This prevents a single ad hoc query from accidentally scanning an entire multi-terabyte dataset.

Each workgroup can have its own results bucket, which keeps query history separated when multiple teams share the same AWS account.

Common Setup Issues

Three problems account for most early Athena failures.

Table not found after creation. If Athena returns a "table does not exist" error after you created the table, check that you selected the correct database in the dropdown on the left side of the query editor. Athena does not switch databases automatically between sessions.

Query results location not set. Every new workgroup requires a results location. The error reads "Query result location is required." Go to Workgroup settings and set the S3 output path before running any queries.

Uppercase column names. If your DDL contains a column named OrderID instead of order_id, Athena creates the table but queries against Glue-backed schemas fail with a metadata mismatch. Rename all columns to lowercase before creating your table.

What Athena Does Not Replace

Athena is a query layer, not a data warehouse. It handles exploratory analysis, ad hoc queries, and scheduled reports on relatively static S3 data well. It is not suited for frequent writes, transactional workloads, or sub-second dashboard response times. For those use cases, a warehouse like Redshift or BigQuery is a better fit.

If you want to skip the S3 and Glue setup entirely and go straight from a file upload to SQL-backed insights, VSLZ lets you get there from a single prompt with no infrastructure required.