> ## Documentation Index
> Fetch the complete documentation index at: https://cloud-architect.ipoint-labs.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Querying Data in S3 with Amazon Athena
> Hands-on lab · 45m
Launch this lab in the IP Lab Portal, then follow the steps below in the AWS console.
Open IP Lab Portal
## Overview
### Lab details
This lab allows users to query and analyse data stored in an S3 bucket using SQL statements. The lab aims to help users understand the process of creating a table in Amazon Athena, configuring the necessary settings, and executing SQL queries.
You will practice using Amazon Athena to create a table with a CSV file present in the S3 bucket and query using SQL statements.
Duration: **1 hour**
AWS Region: **us-east-1 (N. Virginia).**
### Introduction
#### What is Amazon Athena?
Amazon Athena is a serverless query service offered by Amazon Web Services (AWS), designed for the analysis of data stored in Amazon S3 using standard SQL queries. The underlying technology of Athena is Presto, an open-source distributed SQL query engine. Supporting various data formats such as CSV, ORC, JSON, Avro, and Apache Parquet, Athena enables users to access it through the AWS Management Console, ODBC/JDBC drivers, or API.
Operated on a pay-per-query model, Athena ensures that users are billed only for the executed queries. The service facilitates data analysis through an interactive query editor within the AWS Management Console, allowing users to create tables, define schemas, and execute SQL statements for data exploration and querying.
Athena further integrates with other AWS services, such as AWS Glue, for metadata management and data cataloguing. Its notable features include quick query execution and automatic scaling based on the volume and complexity of the data being analysed. This comprehensive functionality makes Amazon Athena a versatile and efficient tool for data analysis within the AWS ecosystem.
### Architecture diagram
### Task details
1. Sign in to AWS Management Console.
2. Setup workgroup
3. Create a database in Glue.
4. Query table in Athena
5. Validation of the lab
6. Deleting AWS Resources
### Launching the lab environment
1. To launch the lab environment, Click on the Launch lab button.
2. Please wait until the cloud environment is provisioned. It will take less than a minute to provision.
3. Once the Lab is started, you will be provided with IAM username, Password, Access Key, and Secret Access Key.
You can only start one lab at any given time
## Lab guide
### Lab steps
#### Task 1: Sign in to AWS Management Console
1. Click on the **Open console** button, and you will get redirected to AWS Console in a new browser tab.
2. On the AWS sign-in page,
* Leave the Account ID as default. Never edit/remove the 12-digit Account ID present in the AWS Console. Otherwise, you cannot proceed with the lab.
* Now copy your **Username** and **Password** in the Lab Console to the **IAM Username and Password** in AWS Console and click on the **Sign-in** button
3. Once Signed In to the AWS Management Console, make the default AWS Region as **US East (N. Virginia) us-east-1.**
#### Task 2: Setup workgroup
In this task, we are going to create a workgroup in Amazon Athena. The workgroup allows users to define specific configurations and settings for their query execution environment. By creating a workgroup, users can customize parameters such as query result location, encryption settings, and query execution options.
1. Make sure you are in the **N.Virginia** Region.
2. Navigate to the **Services** menu at the top, then click on **Athena** in the **Analytics** section.
3. In the left sidebar, under **Administration** select **Workgroup** and then click on **Create Workgroup** button.
4. Provide details to create a workgroup:
* Workgroup Name: Enter **WhizWorkgroup**
* Description: Enter **Workgroup for Athena lab**
* Analytics engine:
* Choose the type of engine : Select **Athena SQL**
* Upgrade Query engine: Select **Automatic**
* Under **Query result configuration:** Select **Customer Managed**. Then Click on **Browse S3** button to select the S3 bucket, whose name is starting with **whizlabs... .**
* Leave other settings as default
* Click on the **Create Workgroup** button.
5. Workgroup has been created successfully.
#### Task 3: Create a database in Glue
In this task, we are going to create a database in AWS Glue, a fully managed extract, transform, and load (ETL) service. The database serves as a container for organizing and storing metadata related to the data tables used in Athena. It enables efficient data cataloging and data management for the subsequent tasks.
1. Make sure you are in the **N.Virginia** Region.
2. Navigate to the **Services** menu at the top, then click on **AWS Glue** in the **Analytics** section.
3. In the left sidebar, Under Data catalog, Click on **Databases**
4. Click on the **Add Database** button
5. In the pop-up menu, enter the database name as **whizgluedatabase** and click on the **Create database** button.
6. The database is now created.
#### Task 4: Create a table in Glue
In this task, we are going to create a table in AWS Glue, which represents the structure and schema of the data stored in the S3 bucket.
1. In the left sidebar, Under the **Data catalog**, Click on **Tables.**
2. To create a table, click on the **Add table** button
3. In the **Set table properties** section, do the following:
* Enter the **Table name** : **whiz-sample-table**
* **Database:** select **whizgluedatabase**
4. In the **Data store** section, do the following:
* Select the type of source: **S3 (default)**
* Data location is specified in: **my account (default)**
* Include path: Select the S3 bucket name starting with **whizlabs… .** Make sure you **add** / in the end.
5. In the **Choose a data format** section, do the following:
* Select Classification as **CSV**
* Choose delimiter as **Comma (,)**
* Click on the **Next** button.
6. In the Schema section, we will add two columns by clicking on **Add** button.
* Column # : **1**
* Column name: Enter **Expense\_Type**
* Data Type: Select **string**
* Click on the **Save button** below.
* Click on the **Add** button again.
* Column #: **2**
* Column name: Enter **Expense\_Category**
* Data Type: Select **string**
* Click on the **Save button** below.
* After adding both columns, click on the **Next** button.
7. Review the configuration of the table and click on the **Create** button. The table is now created.
#### Task 5: Query table in Athena
In this task, we are going to use the SQL query capabilities of Amazon Athena to interact with and analyze the data stored in the created table. By executing SQL statements in the Athena Query Editor.
1. Make sure you are in the **N.Virginia** Region.
2. Navigate to **the Services** menu in the top, then click on **Athena** in the **Analytics** section.
3. In the left sidebar, Click on **Query Editor**
4. Switch to **WhizWorkgroup** at the top right.
5. In the left sidebar, under data source, select the **database** as **whizgluedatabase.**
6. Then you will see our table, **whiz-sample-table**.
7. To **preview** click on
**three dots** next to the **whiz-sample-table** table name and select the **Preview Table.**
8. The query editor will automatically generate the SQL statement for querying the first ten columns.
9. The result of the query is shown below.
10. To get the results of all expenses types under **expense\_category** of **Food**, paste the following SQL statement into the same **query editor** or open new query editor by clicking on **+ symbol** and click on **Run** button.
```
SELECT * FROM "whizgluedatabase"."whiz-sample-table" WHERE expense_category = 'Food';
```
11. You can play around with different queries like the following one.
12. To run the new query you can **replace** the **existing one**, or you can add a **new query** by clicking on the **+ symbol** from the query editor.
13. Getting the total number of rows present by running the following SQL statement in the query editor.
```
SELECT * FROM "whizgluedatabase"."whiz-sample-table" WHERE expense_category = 'Transportation';
```
**Do you know?**
Athena's serverless nature allows users to analyze data stored in S3 without the need for any infrastructure provisioning or management. This means that users can focus solely on querying and analyzing their data without worrying about setting up and maintaining servers or clusters. The pay-per-query pricing model of Amazon Athena ensures cost efficiency, as users are only charged for the actual queries they run. This flexibility and cost-effectiveness make Amazon Athena a powerful and convenient tool for data analysis tasks in AWS.
#### Task 6: Validation Test
1. Once the lab steps are completed, please click on the **Check my work** button on the left side panel.
2. This will validate the resources in the AWS account and display whether you have completed this lab successfully or not.
3. Sample output :
#### Task 7: Delete AWS Resources
##### Deleting the database and table in Glue
1. Navigate to the **Services** menu at the top, then click on **AWS Glue** in the **Analytics** section.
2. By default, you will be able to see the tables present. In the left sidebar, Under **Data catalog**, Click on **Databases**.
3. Databases will be present here, our database is whizgluedatabase.
4. To delete the database, perform the following tasks:
* Select the database, **whizgluedatabase**
* Choose **Delete** from the menu.
5. Confirm the deletion on the pop-up by clicking on the **Delete** button
6. The database is successfully deleted now.
##### Deleting workgroup
1. Navigate to **Services** menu in the top, then click on **Athena** in the **Analytics** section.
2. **Click** on the workgroup.
3. Select **WhizWorkgroup** and Click on **Actions**
4. Click on **Delete**
5. Confirm the deletion on the pop-up by entering workgroup name i.e., **WhizWorkgroup** and click on the **Delete** button
### Completion and conclusion
1. You have successfully created and configured a workgroup in Amazon Athena.
2. You have successfully created a table in Amazon Athena with S3
3. You have successfully queried the SQL statements in the Query editor of Athena.
4. You have successfully created a webpage and published it.
### End lab
1. Sign out of AWS Account.
2. You have successfully completed the lab.
3. Once you have completed the steps, click on **End lab** in the IP Lab Portal.
## What gets checked
When you press **Check my work**, the platform verifies each of these:
* **Create a Glue Database** — Check whether a Glue Database is created or not.
* **Create a Glue Table** — Check whether an AWS Glue Table exists in any database or not
* **Create an Amazon Athena Workgroup** — Check whether an Amazon Athena Workgroup is created or not