Query Data in S3 with Athena

You have terabytes of CSV, JSON, or Parquet data sitting in S3, and the moment you need to answer a business question—"What were our top-selling products last quarter?"—you realize you can't just open it. Downloading and processing it locally is slow, expensive, and inefficient. What if you could run SQL directly against your data in S3, without loading it anywhere? That's exactly what Amazon Athena allows you to do: serverless, pay-per-query, and you only pay for the data you actually scan.

The Problem This Lesson Solves

Data engineers and developers often hit a wall when they need to analyze data stored in S3. You might have log files from your application, CSV exports from a database, or JSON payloads from an API—all sitting in S3, but not in a database you can easily query. The traditional answer is to build a data warehouse, but that means moving the data, managing infrastructure, and paying for compute you may not always need.

Amazon Athena removes that friction. It gives you a SQL interface directly on top of S3, with no servers to provision, no clusters to maintain, and no schema to load in advance. You create a table definition—called a table—that tells Athena how to interpret your files, and then you can run SQL queries just like you would on a relational database.

By the end of this lesson, you'll be able to:

• Explain the core idea behind querying data in S3 with Athena
• Complete a practical exercise that runs a query on real S3 data

Core Concept / Mental Model

Think of Athena as a SQL translator between you and your data files in S3. It doesn't store your data; it reads it on the fly. You define a schema (via a table in the AWS Glue Data Catalog) that tells Athena the format, data types, and location of your files. When you run a query, Athena launches a behind-the-scenes distributed engine (Presto/Trino) that scans only the files relevant to your query.

A useful analogy: consider a library where books are stored in boxes in a warehouse. Without a catalog, you'd have to open every box to find what you need. Athena is like a smart catalog that knows exactly which box to open and which page to read, without moving the books anywhere.

Key components you'll work with:

• S3 bucket – where your raw data lives
• Glue Data Catalog – metadata store that holds table definitions (databases, tables, columns)
• Athena – query engine that executes SQL against the catalog and S3

How It Works Step by Step

Step 1 – Store your data in S3

Your data should be in a structured format like CSV, JSON, Parquet, or ORC. While CSV works, columnar formats like Parquet drastically reduce scan costs and improve performance.

Step 2 – Define a table in the Data Catalog

In Athena's console or via API, you create a database and a table. The table includes column names, data types, and the S3 path where the files live. You also specify a serde (serializer/deserializer) that tells Athena how to parse the files.

Step 3 – Run SQL queries

With the table defined, you can run standard SQL—SELECT, JOIN, GROUP BY, and even UNNEST for arrays. Athena handles partitioning, data skipping, and columnar reads automatically.

Step 4 – Pay only for what you scan

Athena bills you based on the amount of data scanned per query (in TB increments). By partitioning your data (e.g., by date), you can limit scans to specific subsets, saving money.

Hands-On Walkthrough

Let's get your hands dirty. We'll use the AWS CLI and Athena SDK to query a public dataset. This example assumes you have AWS CLI configured and an S3 bucket for query results.

Step 1: Set up S3 and upload a sample CSV

# Create your bucket (replace with your unique name)
aws s3 mb s3://my-athena-demo-bucket

# Create sample data locally
echo "date,product,units,revenue
2024-01-01,Apple,10,30
2024-01-02,Banana,20,40
2024-01-03,Apple,15,45" > sales.csv

# Upload to S3
aws s3 cp sales.csv s3://my-athena-demo-bucket/sales/sales.csv

Step 2: Create a database and table in Athena

You can do this via the Athena console or using the AWS Glue APIs. Here's a manual SQL run in the Athena console (or via aws athena start-query-execution):

CREATE DATABASE IF NOT EXISTS demo_db;

CREATE EXTERNAL TABLE demo_db.sales (
  date STRING,
  product STRING,
  units INT,
  revenue FLOAT
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION 's3://my-athena-demo-bucket/sales/';

Step 3: Run a query

SELECT product, SUM(revenue) AS total_revenue
FROM demo_db.sales
GROUP BY product;

Expected output:

Apple   75.0
Banana  40.0

Step 4: Use boto3 to query programmatically

import boto3

client = boto3.client('athena')

# Start query
response = client.start_query_execution(
    QueryString="SELECT * FROM demo_db.sales LIMIT 5",
    QueryExecutionContext={'Database': 'demo_db'},
    ResultConfiguration={'OutputLocation': 's3://my-athena-demo-bucket/results/'}
)

query_execution_id = response['QueryExecutionId']
print(f"Query ID: {query_execution_id}")

To fetch results, you'll need to poll the query status and then read the output from S3.

Compare Options / When to Choose What

When you need to query data in S3, you have several options. Here's a quick comparison:

When to choose Athena:

• You need a serverless solution with zero maintenance
• Your data volume is moderate (say, GB to TB) and you can afford scan costs
• You want standard SQL without moving data
• You perform interactive queries—not high-frequency sub-second queries

When to avoid Athena:

• You run thousands of queries per day—costs can explode
• You need sub-second query latency
• Your data is highly compressed but unpartitioned—scan costs are high

Troubleshooting & Edge Cases

Problem: Query fails with 'HIVE_BAD_DATA: Field ...'

This means Athena couldn't parse a value in your data. Ensure all values match the schema data types. For example, a text "30" in a CSV with INT column will fail. Use CAST in queries only if you can't fix the source.

Problem: It says 'Table not found'

Make sure the DATABASE and table name are correct, and the LOCATION points to an S3 path that exists. Also, check that the bucket is in the same region as Athena.

Problem: Query runs forever or high execution time

Likely causes: unpartitioned data, scanning entire dataset, or too many files to list. Optimize by:

• Partitioning your table by date/region
• Using columnar formats (Parquet/ORC)
• Limiting the SELECT columns to only those needed

Problem: Permissions denied

Athena needs s3:GetObject on the data location and s3:PutObject on the results bucket. Also, the IAM role must have athena:StartQueryExecution and glue:* permissions.

Pro tip: Always set up a Glue Crawler to automatically infer schemas from S3 data. It saves time and avoids manual table creation errors.

What You Learned & What's Next

You now understand the core idea behind querying data in S3 with Athena: define a table, then run SQL directly on S3 files without moving them. You can now:

• Create tables in the Glue Data Catalog from S3 data
• Run ad-hoc and aggregating queries with standard SQL
• Programmatically query Athena using the AWS SDK
• Know when to use Athena versus other S3 query options

Next step: In the next lesson, you'll learn how to automate Athena queries—scheduling them with AWS Lambda and triggering them on data arrival. That will turn your one-off queries into production data pipelines.

Remember: Athena is your go-to for serverless SQL on S3. Use partitioning and columnar formats to keep costs low and performance high.