Skip to main content
In this lesson we’ll demonstrate how to use Amazon Athena to query CSV data stored in Amazon S3. The workflow covers:
  • Uploading a CSV file to S3
  • Creating an external table in Athena that points to the CSV
  • Running SQL queries against the table
  • Cleaning up Athena metadata
This is a quick, practical walkthrough showing Athena’s schema-on-read model and how to get started with CSV files in S3.

​
Sample CSV

The CSV contains columns for ID, first name, last name, two email addresses, profession, join date, and a two-letter country code. Here is a small sample of the file:
I uploaded the full CSV (about 10,000 rows) to an S3 bucket and will point Athena at that location.

​
Create the Athena table from S3

In the Athena console choose: Create → Create table from S3 bucket data. Configure the table to match CSV format:
  • Data format: CSV (Delimited text)
  • Field delimiter: ,
  • Skip header: set to 1 (so Athena ignores the header row)
  • Declare columns and types that align to the CSV
Screenshot of the AWS Athena console showing the "Create table" form with the S3 dataset location field populated (s3://athena-demo-kodekloud) and Data format options set to Apache Hive / Apache Parquet.
Below is a suggested mapping from the CSV header to Athena column names and types. Adjust names/types to match your CSV and downstream queries.

​
CREATE EXTERNAL TABLE example

When you use the Athena GUI it will generate a DDL similar to the one below. This example uses the Hive CSV serde to parse the file and points to the S3 bucket location:
Note that the CSV header uses "date joined"; in the example above the column is renamed to date for convenience. If you prefer to keep the original header names, match the column name in the DDL accordingly.
Athena uses schema-on-read: creating an external table registers the schema and how to parse files in S3, but the underlying data remains in S3. To reduce costs and speed up queries, consider converting CSV to a columnar format like Parquet or partitioning by frequently filtered columns. See the Athena documentation for best practices: https://docs.aws.amazon.com/athena/latest/ug/best-practices.html

​
Run SQL queries

Once the table exists, query it using standard SQL in the Athena query editor.
  • Retrieve the first 10 rows:
  • Filter by country (example: United States / US):
The query editor displays the results and shows execution metadata including runtime and amount of data scanned. Use the query statistics to understand cost implications and to optimize (for example, partitioning or converting to Parquet).
A screenshot of the AWS Athena query editor showing query results: a table of user records with columns like ID, first/last name, email, profession, date, and country. The top shows query controls and status (Run again, Explain, completed with run time and data scanned).
Athena charges based on the amount of data scanned by each query. Scanning large CSV files can be costly. To lower cost and improve performance, convert data to compressed columnar formats (Parquet/ORC), use partitioning, and restrict SELECT columns where possible.

​
Cleanup

Removing the Athena table and database clears the metadata only; it does not delete the objects stored in S3. If you want to remove metadata, run:
If you also want to delete the S3 objects, remove them manually via the S3 console, AWS CLI, or an automation tool. That covers the end-to-end workflow: upload CSV to S3, create an external Athena table referencing that CSV, run SQL queries, and clean up metadata.

Watch Video