Amazon Athena: Querying S3 Data Using SQL | A Hands-on AWS Guide

Introduction

When working with large datasets in the cloud, analyzing data often requires setting up databases, servers, or data warehouses. Amazon Athena provides a simpler approach.

Amazon Athena is a serverless interactive query service that allows users to analyze data directly from Amazon S3 using standard SQL.

In this tutorial, we will:

Upload a dataset to Amazon S3
Create a database in Amazon Athena
Create a table for the dataset
Query the data using SQL
View and analyze the query results

For this demonstration, the Stranger Things Dialogue Dataset is used.

What is Amazon Athena?

Amazon Athena is a serverless query service provided by AWS that allows users to run SQL queries directly on data stored in Amazon S3.

Athena does not require:

Dedicated servers
Database installation
Infrastructure management
Database provisioning

You simply point Athena to your data in S3 and use SQL to query it.

Basic Architecture
User
|
↓
Amazon Athena
|
SQL Query
|
↓
Amazon S3 Dataset
|
↓
Query Result

Athena uses the AWS Glue Data Catalog to store information about databases and tables, while the actual data remains in Amazon S3.

Why Use Amazon Athena?

Athena is useful when you need to analyze data stored in S3 without creating and maintaining a traditional database.

Key advantages
Serverless – No servers to manage
SQL-based – Uses familiar SQL syntax
Direct S3 querying – Data can remain in S3
Scalable – Handles large datasets
Pay-per-query – Charges are based primarily on the amount of data scanned
Easy integration – Works with other AWS analytics services
Prerequisites

Before starting, you need:

An AWS account
An S3 bucket
A dataset stored in S3
Access to Amazon Athena
Basic knowledge of SQL

For this demonstration, the S3 bucket used is:

akshara-s3-15-09-2026

The dataset is stored under:

s3://akshara-s3-15-09-2026/stranger-things/

The uploaded files are:

episodes.csv
stranger-things-dialogue-dataset.zip
stranger_things_all_dialogue.csv

Note: In Athena, we will query the CSV file rather than the ZIP file.

Step 1: Upload the Dataset to Amazon S3

The dataset was first downloaded from Kaggle and extracted on an Amazon EC2 instance.

The extracted files were:

episodes.csv
stranger_things_all_dialogue.csv

They were then uploaded to S3 using the AWS CLI:

aws s3 cp . s3://akshara-s3-15-09-2026/stranger-things/ --recursive

To verify the uploaded files:

aws s3 ls s3://akshara-s3-15-09-2026/stranger-things/

The output confirmed that the files were successfully uploaded.

Step 2: Open Amazon Athena
Sign in to the AWS Management Console.
Search for Athena.
Open Amazon Athena.
Select the appropriate AWS Region.

You will be taken to the Athena Query Editor.

Screenshot:
Add screenshot of Amazon Athena Query Editor here.

Step 3: Configure the Query Result Location

Athena needs an S3 location where it can store the results of SQL queries.

In the Athena Query Editor:

Open Settings.
Find Query result location.
Specify an S3 path.

For example:

s3://akshara-s3-15-09-2026/athena-results/
Save the configuration.

The query results generated by Athena will be stored in this location.

Screenshot:
Add screenshot of Athena settings here.

Step 4: Create a Database

We can create a database using SQL.

Run:

CREATE DATABASE stranger_things_db;

After executing the query, select the database:

USE stranger_things_db;

The database provides a logical structure for organizing our tables.

Step 5: Create a Table

Now we need to tell Athena how the CSV file is structured.

Before creating the table, inspect the CSV file and identify its column names.

For example, if the dialogue dataset contains columns such as:

season
episode
character
dialogue

we can create a table using:

CREATE EXTERNAL TABLE stranger_things_dialogue (
season INT,
episode STRING,
character STRING,
dialogue STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
'separatorChar' = ',',
'quoteChar' = '"'
)
LOCATION 's3://akshara-s3-15-09-2026/stranger-things/'
TBLPROPERTIES ('skip.header.line.count'='1');
Important

The column definitions must match the actual CSV structure.

If your CSV has different column names or data types, modify the CREATE TABLE statement accordingly.

Step 6: Verify the Table

To see the table:

SHOW TABLES;

You should see:

stranger_things_dialogue

You can also inspect the table structure:

DESCRIBE stranger_things_dialogue;

This displays the column names and their data types.

Step 7: Run the First Query

Let's retrieve some records from the dataset.

SELECT *
FROM stranger_things_dialogue
LIMIT 10;

Athena reads the data from S3 and returns the matching records.

Screenshot:
Add screenshot showing the query and results.

Step 8: Count the Number of Dialogue Records

We can use SQL aggregation to determine how many dialogue records exist.

SELECT COUNT(*) AS total_dialogues
FROM stranger_things_dialogue;

This demonstrates how Athena can perform analytical operations directly on S3 data.

Step 9: Find the Most Frequent Characters

We can use GROUP BY to determine which characters appear most frequently.

SELECT character, COUNT(*) AS dialogue_count
FROM stranger_things_dialogue
GROUP BY character
ORDER BY dialogue_count DESC;

This produces a ranking of characters based on the number of dialogue records.

Step 10: Filter Data

Athena supports standard SQL filtering.

For example:

SELECT *
FROM stranger_things_dialogue
WHERE season = 1;

This returns dialogue records belonging to Season 1.

We can also search for a particular word:

SELECT *
FROM stranger_things_dialogue
WHERE LOWER(dialogue) LIKE '%friends%';

This can be useful for text-based analysis.

Step 11: Aggregate Data by Season

We can calculate the number of dialogue records for each season.

SELECT season, COUNT(*) AS dialogue_count
FROM stranger_things_dialogue
GROUP BY season
ORDER BY season;

This allows us to compare dialogue volume across different seasons.

Step 12: Query Results

Athena displays the query results directly in the Query Editor.

The results can also be stored in Amazon S3 using the query result location configured earlier.

This allows the output of Athena queries to be reused by other AWS services or applications.

How Amazon Athena Works

The overall process can be summarized as:

    CSV Dataset
         |
         ↓
   Amazon S3
         |
         ↓
  AWS Glue Catalog
         |
         ↓
  Amazon Athena
         |
      SQL Query
         |
         ↓
   Query Results
         |
         ↓
   Amazon S3

Athena does not move the original dataset into a traditional database.

Instead, it reads the required data from S3 when a query is executed.

Athena and AWS Glue Data Catalog

Amazon Athena works with the AWS Glue Data Catalog to store metadata about datasets.

Metadata includes information such as:

Database name
Table name
Column names
Data types
S3 data location
File format

The actual dataset continues to reside in S3.

This separation between data and metadata makes Athena suitable for data-lake architectures.

Supported Data Formats

Athena can work with several data formats, including:

CSV
JSON
Apache Parquet
Apache ORC
Avro
Text files

For large-scale analytics, columnar formats such as Parquet can significantly improve query performance because Athena can scan only the required columns.

Advantages of Amazon Athena

1. Serverless

There is no infrastructure to provision or maintain.

1. Easy to Use

Users can analyze data using familiar SQL queries.

1. Direct S3 Integration

Athena can query data stored directly in Amazon S3.

1. Scalable

Athena can process datasets ranging from small files to very large data lakes.

1. Cost Efficient

There is no need to keep database servers running continuously.

1. Suitable for Data Lakes

Athena is particularly useful for analyzing large collections of data stored in S3.

Limitations

Although Athena is powerful, it has some limitations.

Query performance depends on data organization and file format.
Querying large CSV datasets can result in more data being scanned.
Poorly structured data can increase query cost.
Athena is primarily designed for analytics rather than high-frequency transactional workloads.

Using Parquet, partitioning, and efficient data organization can improve performance and reduce costs.

Real-World Applications

Amazon Athena can be used for:

Log analysis
Website analytics
Security analysis
Business intelligence
Data lake analytics
IoT data analysis
Financial data analysis
Application event analysis
Large-scale CSV/JSON data analysis

For example, an organization could store millions of application logs in S3 and use Athena to identify errors without maintaining a dedicated database server.

Amazon Athena vs Traditional Database
Feature Amazon Athena Traditional Database
Infrastructure Serverless Requires servers
Data Storage Amazon S3 Database storage
Query Language SQL SQL
Management Minimal Higher
Scaling Managed by AWS Usually configured
Best Use Analytics Transactional workloads
Data Lake Support Excellent Limited
Conclusion

Amazon Athena provides a simple and powerful way to analyze data stored in Amazon S3 using standard SQL.

In this hands-on demonstration, the Stranger Things dialogue dataset was:

Downloaded from Kaggle
Extracted on an EC2 instance
Uploaded to Amazon S3
Registered as an Athena table
Queried using SQL

The main advantage is that we can perform data analysis without creating or managing a database server.

This makes Amazon Athena an important service for serverless analytics and cloud-based data lake architectures.

Key Takeaways
Amazon S3 → Stores the data
AWS Glue → Stores metadata
Amazon Athena → Queries the data using SQL
Amazon S3 → Stores query results

Amazon Athena enables serverless SQL-based analytics directly on data stored in Amazon S3.

References
Amazon Athena Documentation
Amazon S3 Documentation
AWS Glue Data Catalog