How to ETL with AWS Glue and Amazon Glue Studio to transform your data - Part 2
Tags: tutorial, glue, aws, beginners
Published: 2022-08-09 13:15:00

## Introduction 

In the previous [blog](https://dev.to/aws-builders/how-to-etl-with-aws-glue-and-amazon-glue-studio-to-transform-your-data-328b) you mastered ETL using sample *sales data* for both Amazon Glue Studio and AWS Glue. 

The third tutorial explored how you may query data from your Amazon S3 bucket using Amazon Athena and create data visualizations from an Amazon Athena table in Amazon QuickSight that references the sales database stored in the AWS Data Catalog.

In this new tutorial, you will learn how to:

a) Locate open source datasets for your passion project, build a test or production workload that you can use with AWS services.

b) Complete data transformation with your own data using both Amazon Glue and Amazon Glue Studio.

### Where can you find interesting open source datasets for analytics and machine learning projects?

* [Kaggle.com] (https://www.kaggle.com/datasets)

![kaggle](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sh3uhrweia500w3l72iy.jpg)
* Government websites e.g. data.gov, data.gov.uk, data.gov.au.

![gov](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/230dz1ozh0acusfjsjak.jpg)
 
* [Google data search] (https://datasetsearch.research.google.com/)


![google](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wy720g4ajak907nyb3dn.jpg)

* Census data e.g.
[Australian census](https://www.abs.gov.au/census)


![abs](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hxecsymtuz97lqm5iqey.jpg)

* [UCI Machine Learning Repository](https://archive.ics.uci.edu/ml/index.php)


![Iuci](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h8alg0ipmd64f24mvq4s.jpg)

### Solution Architecture overview - AWS Glue


![glue](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g7z6x0z271o7elnvbrg5.jpg)

### Tutorial 1: Data Transformation with AWS Glue

Step 1: In this tutorial, download the DOHMH New York City Restaurant Inspections data as a csv file from this [link](https://data.cityofnewyork.us/Health/DOHMH-New-York-City-Restaurant-Inspection-Results/43nn-pn8j)


![ny data](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ul9xnjm65izptulv6s8k.jpg)

A preview of the raw dataset:

![excel](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fhbadb8g82voosmj737q.jpg)

Step 2:  Log into your AWS account. If you do not have an AWS  account you may create one [here](https://dev.to/aws-builders/getting-started-with-aws-a-sweet-journey-5cjj)


![account](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/v10maptylq5oltuisxbq.jpg)

Step 3: You may follow the instructions [here](https://dev.to/aws-builders/creating-your-first-amazon-s3-bucket-and-new-feature-release-2c03) to create your first S3 bucket  and give it a name **new-york-restaurant-inspections**.


![s3](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/aw5x38nm5jn6und9ebz4.jpg)

Step 4:  Click the bucket and select **Create Folder**.

Create two folders within the Amazon S3 bucket and name them **raw** and **processed** respectively as shown below:


![folders](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dnscc7gpdhmwmub4ihgd.jpg)

Step 5: **Upload** and click **add file** to the folder **raw**



![upload file](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/s1217qd14k4f4ylg11kr.jpg)


![successful](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/p2poi0s25c9rdbiy1831.jpg)

Step 6: Set up basic IAM permissions to use AWS Glue console. You may follow the [blog](https://dev.to/aws-builders/how-to-etl-with-aws-glue-and-amazon-glue-studio-to-transform-your-data-328b) for more details.

If you need to access more Amazon resources such as Amazon Sagemaker Notebooks, you may follow the instructions [here](https://docs.aws.amazon.com/glue/latest/dg/getting-started-access.html).

Step 7: Navigate to the Amazon Glue console and on the left-hand menu, click **crawler** followed by **Add crawler**.


![glue get started](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vvzx0njf8qlvsmbmbvsi.jpg)


![add crawlern](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zcu65zpqpubfcmqirr7m.jpg)

Step 8: Provide a name for the crawler. For this example, the we will name the crawler **new york-restaurant-inspection-crawler** and click **Next**.


![name crawler](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/aafmkygrdt54ytwc1b7g.jpg)
Step 9: For crawler source type, select **Data stores** and then **crawler all folders**.


![specifify](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/adri5bnwyx0htmr2lui7.jpg)

Step 10: In this section, add the S3 bucket path and selecting the csv file from the **raw** folder and click **Next**.


![path](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/igtrz2nsjtgvkso0yqk3.jpg)


![add data store](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uhem3s5518ufvu133t6c.jpg)

Step 11: Click **Yes** to add another data store and **Next**.


![yes](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2x7gzniyxdt7bp1rygds.jpg)

Step 12: Repeat Step 10 but select the *Processed** folder for the S3 bucket path. Click **Next**.


![processed](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jhmqh3nnkeyosuhaa4o2.jpg)


![processed path](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r0lksok48nngvgmn25ey.jpg)

Step 13: There are no more data stores to be created, click **No**. Click **Next**.


![no data store](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tdxy9m54h3gh7duaomxz.jpg)

Step 14: You may select an existing IAM role for AWS Glue that was created earlier for example 'AWSGlueServiceRole-learn-glue-role'. Click **Next**.


![iAM](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4x7xnj2z9oldrhnurrk1.jpg)

Step 15: Create a schedule for the crawler, select from the drop-down menu **Run on demand**.


![run on demand](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/n8gvyk9202xmj44eg242.jpg)

Step 16: Configure the crawler output. Click **Add database** and provide a name for the database 'restaurant-inspections'. Add a prefix for the table name called **Raw**.  Click **Next**.


![add db](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/85308t7vvgy0cklwma74.jpg)


![rest](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m27b4hfd450bjjowmcr2.jpg)


![outpout](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sfybgpiq7ckrwzm9sezd.jpg)
Step 17: Check the input details and click **Finish**.

![check details](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q3zdiot0vh7fh4f2y49j.jpg)

The crawler details are show below:

![crawler details](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i13pt5qwu1trthy1fh8i.jpg)

Step 18: Click the crawler'new-york-restaurant-inspection-crawler' and select **Run crawler** to execute the job.


![run crawler](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/un2raek6gdadtzs5zree.jpg)


![run crawler now](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/05eazenyokuf4s9x9hex.jpg)

Step 19: After the crawler job is completed with a 'ready' status , you can inspect the meta data by clicking **Tables** on the left-hand side.


![logs](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vg0tqxxpzpe98zcusf9s.jpg)

You may inspect the following schema from the data catalog:


![schema](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vbwcbuu9k9t7jsedc8ns.jpg)


### Solution Architecture overview - Amazon Glue Studio


![architecture](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/s96y4z34iow9iup9y8ju.jpg)

### Tutorial 2: Data Transformation with Amazon Glue Studio

Step 1:  Access 'AWS Glue Studio' from the AWS Glue console.


![glue 2 console](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bxo8sjujzktpwzjxn225.jpg)

Step 2: From the AWS Glue Studio console, click **View jobs**.


![jobs](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3emordu0w1grulcu3wiu.jpg)

Step 3: Select the option **Visual with a source and target** and click **Create**.


![visualn](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ibtepcdtj5lva16chlxq.jpg)

Step 4: Under **Node Properties** select **S3 bucket** followed by **Amazon S3**


![node](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mdmn03ynlf0f4j2zzldu.jpg)


Step 5: Under the S3 bucket node, select from the option Data catalog the database name followed by the table name as shown in the image below.


![Imapply mapping](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wazm2af0wlhlh98t1f7o.jpg)

Choose **Apply Mapping node**.

Step 6: Under the'Apply Mapping node, click on the tab **Transform**.

Are there any variables that have been incorrectly categorized in the data catalog? 

Are there any variables that you need to change the data type? 

Are there any columns that you need to drop from your dataset that won't be used in your data analysis or for machine learning?


![transform](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ckppohamack3l3dotchu.jpg)

You may complete some tranformatons such as changing date variables from the data type 'string' to 'date'.


![date change](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ocyjfgu3a0p2r6q4fbqh.jpg)

You may view a listing of common data types in AWS Glue in the [documentation](https://docs.aws.amazon.com/glue/latest/dg/aws-glue-api-common.html)

Step 7: Click on the tab Output Schema, check the data transformations.


![output schema](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5a3dvj920s2txg50uj4q.jpg)

Step 8: Click on **S3 bucket- data target** node, followed by **Parquet** format, Compression Type as **Snappy** and lastly select the S3 bucket path for the **processed** data folder to output the transformed data.


![S3 location](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e4bten5eba66c4w1g81e.jpg)

Select **Create a data catalog on subsequent runs** and name this table **processed**.

![processed](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mchsqb54k5gnpvk2l3c8.jpg)

Step 9: Click on the **Script** tab to view the Python generated code for this ETL job.


![script](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y5urndef12kcfzu0i89g.jpg)

Step 10: Click on the tab 'Job Details', provide a name for the job e.g.restaurant inspection job  and a description for the data transformation. Change the number of workers to 2.


![job name](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9zsncl79ojyieoarykc6.jpg)

Step 11: Click **Save** and **Run** to commence the ETL job.


![run](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/feg1i3y0y4krxwgsdbz8.jpg)

Step 12: The ETL job will take a few minutes to run and will change to a status of 'Succeeded'.


![successful](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/asthvry28z811ncilfd8.jpg)

Step 13: Navigate back to the **AWS Glue console** and select **Crawlers** on the left hand menu and re-run the'restaurant-inspection-crawler' by clicking **Run crawler**.

![ran crawler](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i5mnd1077xdzixnftkvr.jpg)

After the status changes to 'ready' click on **Table** to inspect a **processed** table in the Data catalog for transformed data types.


![processed table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/svo1jr04idl6awwvw2ms.jpg)

### Resources

[Setting up IAM permissions for AWS Glue](https://docs.aws.amazon.com/glue/latest/dg/getting-started-access.html)

[Get started with AWS Glue](https://explore.skillbuilder.aws/learn/course/external/view/elearning/8171/getting-started-with-aws-glue)

[Data types for AWS Glue](https://docs.aws.amazon.com/glue/latest/dg/aws-glue-api-common.html)

[Working with geospatial data in QuickSight](https://docs.aws.amazon.com/quicksight/latest/user/geospatial-data-prep.html)

[Best practice working with geospatial data in Amazon Athena and Glue](https://docs.aws.amazon.com/athena/latest/ug/glue-best-practices.html)

[Common data types](https://docs.aws.amazon.com/glue/latest/dg/aws-glue-api-common.html)

###  AWS Innovate Online Conference APJ - 23 & 25 August 2022


![AWS Innovate](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/st6r8cnixap7o4log7yw.jpg)

Register at this [link](https://aws.amazon.com/events/aws-innovate/apj/data/)


### 🚀 Hot off the press: Introducing AWS Glue Flex jobs: Cost savings on ETL workloads

AWS Glue Flex jobs is perfect for use cases including:

* Scheduling ETL jobs to run overnight 
* Scheduling test workloads
* One-time bulk upload
* Time-sensitive workloads

You can read about the new feature and flex pricing from the AWS Big Data Blog [here](https://aws.amazon.com/blogs/big-data/introducing-aws-glue-flex-jobs-cost-savings-on-etl-workloads/).


![Iflex](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o843m4zuv2r3h3gd09pa.jpg)

Until the next lesson, happy learning ! 😁