Articles → AWS → Athena In AWS

Athena In AWS

In this article, we will discuss Athena in AWS.

Purpose

Athena is a query service used to analyze data in Amazon S3 buckets using standard SQL. Amazon Athena uses Presto (which is the open-source SQL query engine) with full standard SQL support. Athena works with various standard data formats, including CSV, JSON, etc.

Steps

For this article, we will work on the following steps: -

Create a sample CSV file
Upload the CSV file on the S3 bucket
Bind Athena with S3 bucket
Create a database and table from S3 bucket
Query the data using SQL

Let us work on each step.

Create A Sample CSV File

First, create a sample CSV file. For this article, I am creating this file.

Click to Enlarge

Upload The CSV File On The S3 Bucket.

In the second step, we will upload the CSV file on the S3 bucket. For creating the S3 bucket, you can refer to this article.

Picture showing the CSV file uploaded on the S3 bucket

Click to Enlarge

Bind Athena With S3 Bucket

First, search the word athena in the search box and click on the Athena service in the search result.

Picture showing Athena in the search result of the AWS console

Click to Enlarge

An Athena service screen will appear. Click on the Explore the query editor button.

Picture showing the Explore the query editor button for opening the query editor

Click to Enlarge

In the next screen, click on the View settings button to navigate to the settings screen.

Picture showing the View settings button for opening the settings screen

Click to Enlarge

In the settings screen, click on the Manage button to navigate to the manage settings screen.

Picture showing the Manage button for editing the settings

Click to Enlarge

In the manage settings screen, enter the location of the S3 bucket and click on the Save button.

Picture showing specifying the bucket address from which CSV will be read

Click to Enlarge

Create A Database And Table From S3 Bucket

To create the database, click on the Create → S3 bucket data in the Editor tab in the Athena service screen.

Picture showing creating the data source from the S3 bucket

Click to Enlarge

A screen appears to enter the Table name.

Picture showing creating the table in the S3 bucket data source

Click to Enlarge

Scroll down to enter the Database name and the S3 bucket location.

Picture showing adding the database name and the S3 bucket address while creating the table

Click to Enlarge

Scroll down further to select the Data Format as CSV.

Picture showing selecting the file format while creating the table

Click to Enlarge

Scroll further down to enter the column details

Picture showing entering the column details for the table

Click to Enlarge

Scroll to the bottom and click on the Create table button.

Picture showing the Create table button for creating the new table

Click to Enlarge

Query The Data Using SQL

To create the data, write the SQL query and click on the Run button.

Picture showing firing the select query on the data source

Click to Enlarge

You will get the following results.

Picture showing the result of the select query

Click to Enlarge

Is Athena Serverless?

Yes, Athena is serverless because you do not manage any infrastructure like EC2, EBS etc.

Limitations Of Athena

Hosting in a multi-tenant environment

Athena is hosted in a multi-tenant environment. A multi-tenant environment is a software architecture where a single instance of an application serves multiple customers. As one instance is serving various customers, throttling might trigger from time to time.

Data manipulation interface

Athena does not have any data manipulation interface.

Data Partition

We might need to partition the dataset stored in the S3 bucket to run the SQL queries efficiently.

Indexing

Indexing is not supported in Amazon Athena.

Posted By -	Karan Gupta

Posted On -	Monday, November 1, 2021

Updated On -	Thursday, September 19, 2024

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**