AWS Athena - Writing SQL Queries

How to Create Tables in Athena?

Before running any queries in AWS Athena, you need to create a table that references your data in Amazon S3. Athena uses a schema-on-read approach, which means you define the structure of your data when you query it, not when you store it.

Lets understand the steps to create a table in Athena −

Log in to AWS Athena Console

First, access Athena from your AWS Management Console.

Define a Table Schema

Write an SQL query that defines the table structure. For example −

CREATE EXTERNAL TABLE IF NOT EXISTS your_table_name (
   column1 STRING,
   column2 INT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LOCATION 's3://your-bucket/folder/';

Execute the Query

Now, run this query in the Athena query editor to create the table. This will allow you to reference data in the specified S3 bucket.

Running Basic SQL Queries in Athena

Once the table is created, you can start running SQL queries to analyze your data. Athena supports standard SQL which makes it easy for the users familiar with SQL to write queries. Given below is an example of a simple query −

SELECT * FROM your_table_name LIMIT 10;

This query will fetch the first 10 rows from the specified table. You can also filter data, join multiple tables, and use aggregate functions, just like in any SQL-based database.

Example

Lets understand it with the help of an example. Here, we created a database, a table, and then ran a query on that table in Athena Query Editor −