- Lab
-
Libraries: If you want this lab, consider one of these libraries.
- Cloud
- Data

Preparing Data Using Amazon Athena and AWS Glue
Imagine you are the data engineer and you have been assigned the task to prepare the data and get it ready for the machine learning engineers to create a highly predictable model. Your corporation has been working with AWS and you have been encouraged to use AWS services. Your raw data has been uploaded to an input folder in an S3 bucket. You will use a Glue crawler to detect the schema structure. You will then upload the data to a database that will be queried using SQL to detect discrepancies. Then, you will use the Visual ETL tool from AWS Glue to check for any missing or duplicate data and upload the processed data to the output folder.

Lab Info
Table of Contents
-
Challenge
Create a Storage Area to Store the Input Files
- Download the input dataset
Employee_lab.csv
from the lab GitHub repo - Launch the AWS console and create an S3 bucket and two folders,
input
andoutput
.
- Download the input dataset
-
Challenge
Read the Raw Data to a Database
- Create a Glue Crawler.
- Configure the S3
input
folder as the data source. - When prompted to create a new IAM role, add the suffix
mlsc01
to the predefined role name so it's easy to find later. - Create and add a database.
- Run the crawler and write the raw data to this database.
-
Challenge
Run SQL Queries and Detect Data Discrepancies
- Launch Amazon Athena and run SQL queries to detect null values against the age feature, check the number of observations that fall outside $250,000, and determine the format of first-name and last-name features.
-
Challenge
Fix the Data Discrepancies
- Use AWS Glue Visual ETL to configure an input S3 bucket to read the raw data, change the schema, assign proper data types, fill missing values against the age feature, filter data, and ignore rows whose salary is greater than $250,000.
- Run a SQL query to convert the first and last names to lower case and remove the blank spaces in the fields.
- Finally, write the formatted data to the
output
folder of the S3 bucket.
About the author
Real skill practice before real-world application
Hands-on Labs are real environments created by industry experts to help you learn. These environments help you gain knowledge and experience, practice without compromising your system, test without risk, destroy without fear, and let you learn from your mistakes. Hands-on Labs: practice your skills before delivering in the real world.
Learn by doing
Engage hands-on with the tools and technologies you’re learning. You pick the skill, we provide the credentials and environment.
Follow your guide
All labs have detailed instructions and objectives, guiding you through the learning process and ensuring you understand every step.
Turn time into mastery
On average, you retain 75% more of your learning if you take time to practice. Hands-on labs set you up for success to make those skills stick.