Querying Data in S3 with Amazon Athena

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.

​

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';
    
    

​

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