Skip to content

Contact sales

By filling out this form and clicking submit, you acknowledge our privacy policy.
  • Labs icon Lab
  • A Cloud Guru
Google Cloud Platform icon
Labs

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.

Try for free Contact sales
Google Cloud Platform icon
Labs

Path Info

Level
Clock icon Beginner
Duration
Clock icon 1h 0m
Published
Clock icon Apr 25, 2024

Contact sales

By filling out this form and clicking submit, you acknowledge our privacy policy.

Table of Contents

  1. 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 and output.

  2. 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.

  3. 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.

  4. 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.
A Cloud Guru - Labs - Preparing Data Using Amazon Athena and AWS Glue
Author
A Cloud Guru

The Cloud Content team comprises subject matter experts hyper focused on services offered by the leading cloud vendors (AWS, GCP, and Azure), as well as cloud-related technologies such as Linux and DevOps. The team is thrilled to share their knowledge to help you build modern tech solutions from the ground up, secure and optimize your environments, and so much more!

What's a lab?

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.

Provided environment for hands-on practice

We will provide the credentials and environment necessary for you to practice right within your browser.

Guided walkthrough

Follow along with the author’s guided walkthrough and build something new in your provided environment!

Did you know?

On average, you retain 75% more of your learning if you get time for practice.

Start learning by doing today

View Plans