How to ETL with AWS Glue and AWS Glue Studio to transform your data - Part 1
Tags: aws, glue, tutorial, beginners
Published: 2022-06-27 12:01:00


## How do you clean data quickly?

Real world data is very dirty - from startups, consulting, construction, government and insurance, the first use case for cloud adoption could be how to integrate data and make it clean. The journey to cloud is a long transformation project demonstrating the different data analytics life cycle maturity of customers from descriptive, prescriptive and predictive.

Different organizational structures will determine if there are budgets and therefore headcount for data engineers available to prepare data for analytics and machine learning which is the future state.

If an organization does not have FTE for data engineers, data scientists and business analysts may complete ETL (extract, transform and load) jobs to clean and transform small datasets using various tools such as Tableau Prep, Alteryx, Microsoft Power Query.

## Learning Outcomes

In the AWS Management console, you will learn:

* Create IAM permissions to use multiple AWS services

* Use AWS Glue and AWS Glue Studio to clean and prepare your data for analytics

* Create an external table using AWS Athena

* Create visualizations to draw business insights using Amazon QuickSight

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

## What is AWS Glue?

[AWS Glue] (https://aws.amazon.com/glue/?whats-new-cards.sort-by=item.additionalFields.postDateTime&whats-new-cards.sort-order=desc) is a serverless data integration service which means that you do not need to preconfigure servers in advance for ETL jobs for cloud data warehouse Amazon Redshift or an Amazon S3 data lake.

**Data Engineer and Developers**: [Glue Studio](https://docs.aws.amazon.com/glue/latest/ug/what-is-glue-studio.html) may use a visual editor to create an ETL job which renders a Python script and schedule ETL jobs. AWS Glue may be used to add crawlers to identify the schema of datasets, create a database and table from data stored in an AWS S3 bucket location.

**Data Scientists**: [AWS Glue DataBrew](https://aws.amazon.com/glue/features/databrew/) is a tool for visual data preparation that may be used to normalize data such as changing date formats, view distribution, create logical statements and inspect the data quality of missing values.

**Business Analysts**: May use all three services AWS Glue DataBrew, Glue Studio and Glue for different business use cases for their stakeholders.

### [AWS Glue - High Level Architecture]( https://aws.amazon.com/glue/?whats-new-cards.sort-by=item.additionalFields.postDateTime&whats-new-cards.sort-order=desc)

**a) AWS Glue** - create a unified catalog to access data from data stores

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

**b) AWS Glue Studio** - monitor ETL jobs without coding

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

## Tutorial 1: Getting started in ETL with AWS Glue 

#### Pre-requisites

* The recommended reading for this tutorial is based on the AWS Skill Builder course [Getting started with Glue](https://explore.skillbuilder.aws/learn/course/8171/play/36650/getting-started-with-aws-glue;lp=97) 

**Step 1:** Read the blog [Create an AWS account](https://dev.to/aws-builders/getting-started-with-aws-a-sweet-journey-5cjj)

**Step 2:** Read the blog [Create IAM access for Administrator](https://dev.to/aws-builders/creating-your-first-iam-admin-user-and-user-group-in-your-aws-account-machine-learning-part-1-3cne)

**Step 3:** To run a AWS Glue ETL job, use AWS Glue Studio, Amazon Athena and Amazon QuickSight you will need to attach additional policies to the role for permissions to access these AWS Services.

**Step 4:** To run an ETL job using AWS Glue you will need to read the official AWS documentation and follow the steps listed below to [Create an IAM Role](https://docs.aws.amazon.com/glue/latest/ug/setting-up.html)
to create a role 'AWSGlueServiceRole' which can access the crawler and create a role for ETL jobs:

a) Sign in to the AWS Management Console and open the IAM console at https://console.aws.amazon.com/iam/.


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

Click on the left hand-side for Users. There will be an existing administrator role.

b) In the left navigation pane, choose **Roles**.

c) Choose **Create** role.

d) For role type,find and choose **Glue**, and choose **Next: Permissions**.


![Glue Service Role](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m9di3o8cfsuxdt6hfn9s.jpg)

e) On the Attach permissions policy page, choose the policies that contain the required permissions. E.g. **AWSGlueServiceRole** for general AWS Glue Studio and AWS Glue permissions and the AWS managed policy **AmazonS3FullAccess** for access to Amazon S3 resources.


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


f) Add additional policies as needed for additional data stores or services.

g) Choose **Next: Review**.

h) For Role name, enter a name for your role; for example, **AWSGlueServiceRole**. 

![Glue Service Role](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/n9jmwxe6rmtnrcto45k8.jpg)

i) Choose **Create Role** to finish creating the role.

**Step 5:** Read the AWS documentation and follow the steps [Set up IAM permissions for AWS Glue Studio](https://docs.aws.amazon.com/glue/latest/ug/setting-up.html#getting-started-iam-permissions) to create a Glue Studio role.

a) Sign in to the AWS Management Console and open the IAM console at https://console.aws.amazon.com/iam/.

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

b) In the navigation pane, choose **Policies**.

c) In the list of policies, select the check box next to the **AWSGlueConsoleFullAccess**. You can use the Filter menu and the search box to filter the list of policies.

d) Choose Policy actions, and then choose **Attach**.

Repeat the previous steps to attach additional policies to the user.

Give the role a name e.g.**AWSGlueServiceRole-Studio**


![Give the role a name](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bte1jiw7e5xcqkh4dibn.jpg)

**Step 5:** Download the sales_records.csv file from the course [Getting Started with AWS Glue](https://explore.skillbuilder.aws/learn/course/8171/play/36650/getting-started-with-aws-glue;lp=97) in AWS Skillbuilder.


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

**Step 6**: Create an Amazon S3 bucket and upload the csv file for sales_records.csv

You can read the blog to [Create an Amazon S3 bucket](https://dev.to/aws-builders/creating-your-first-amazon-s3-bucket-and-new-feature-release-2c03)

a) In the search bar type 'S3'.

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

b) Click **Create bucket**

c) Under Bucket name provide details e.g. learn-glue-<enter your AWS account number> 

d) Create a folder and name it 'sales-data'


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

e) Within the sales-data folder, create two sub-folders and name them 'raw' and 'processed'.

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

f) Upload the sales_records.csv into the S3 bucket in the folder named 'raw'.


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

g) Type the word 'Glue' in the search bar to navigate to AWS Glue


![Glue service](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5p6y6wrdowhzh33gureo.jpg)

(Note: follow the recommended steps from AWS Skill Builder course 'Getting Started with Glue') [link](https://explore.skillbuilder.aws/learn/course/8171/play/36650/getting-started-with-aws-glue;lp=97)

h) Click 'Crawlers' from the menu on the left.

Click **Add Crawler**


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

i) Name the crawler 'learn-glue-crawler' and click **Next**.


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

j) Select the options **data stores** and **crawl all folders** within S3 buckets.Click **Next**.


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

k)  Navigate to your S3 bucket for the saved sales_records data and search for the path for raw folder.


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

l) Click **Yes** to create another data store.


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

m) Under the specified path details, navigate to the S3 bucket related to the processed folder.


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

n) Additional data stores will not be created. Click **No** and **Next**.

o) Select **Create an IAM role** and name it 'learn-glue-role'

p) Schedule to run the crawler on-demand.


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

q) Click **Add database** and name the database 'Sales-data' and click **Next**.

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

r) Inspect the details entered and click **Finish**.

s) Click 'learn-glue-crawler' and **Run Crawler**.


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


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

t) After the crawler has completed with a status of 'ready', click on the menu on the left **Table** and you will see a table created called **raw**.


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




## Tutorial 2: Getting started with ETL using AWS Glue Studio Note: This tutorial is based on this [link](https://explore.skillbuilder.aws/learn/course/8171/play/36650/getting-started-with-aws-glue;lp=97)

**Step 1**: On the search bar type 'Glue Studio' to navigate to  AWS Glue Studio.

**Step 2**: Click on **View Jobs**

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

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

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

**Step 4**: Under the S3 bucket node, select the option **Data catalog**, select the table 'raw'.

Choose **Apply Mapping node**.


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

**Step 5**: Click on the tab **Output Schema**, you can preview the variables within the dataset and you can drop  variables and change data types.


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

**Step 6**: Click on node **Data target - S3 bucket**. Under the drop-down selections choose Format as **Parquet**, Compression Type as **Snappy** and the S3 path for the 'processed' folder.


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

**Step 7**: Navigate to the tab 'Script' and you may inspect the Python script that was generated from the visual ETL editor. This script can be downloaded.


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

**Step 8**: Click on the tab 'Job Details', provide a name for the job and a description for the data transformation. Change the number of workers to **2**.


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

**Step 9**: Click **Save** and run the ETL job.


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

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


**Step 11**: Navigate to the AWS Glue console and select **Crawlers** on the left hand menu and re-run the'learn-glue-crawler' and after the status is 'ready' click on **Table** to inspect a 'processed' table in the Data catalog.


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


## Tutorial 3: Use AWS Athena to create an external table and create business insights using Amazon QuickSight

**Step 1:** Type 'IAM' in the search bar to navigate to Identity and Access Management (IAM).


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

**Step 2**: Manually attach the two policies 'AmazonAthenaFullAccess' and 'AWSQuicksightAthenaAccess' managed policies to the existing IAM administrator user [link](https://docs.aws.amazon.com/athena/latest/ug/setting-up.html)


![attach policies](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0yg1gy0qfzbxdytxas7j.jpg)

**Step 3**: Review the policies for:

* Amazon Athena
* Amazon QuickSight
* AWS Glue
* AWS Glue DataBrew
* AWS Glue Studio
* Amazon S3

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

**Step 4**: Type in the search bar 'Athena'


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


**Step 5**: Click on the **Settings** tab to ensure you have provided AWS Athena access to your S3 bucket.


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

**Step 6**: Click **Create** and select the option to create a table from **S3 bucket location**




**Step 7** Open a second tab of AWS Glue and navigate to **Tables** and inspect the schema of the 'processed' table.


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

**Step 8**: Create a database from this [link](https://docs.aws.amazon.com/athena/latest/ug/getting-started.html)

Provide the following details:

a) Create a table name

b) Under **Database Configuration**, select the option 'Create a database' and provide a database name.

c) Dataset - provide the S3 bucket path for the 'processed' folder

d) Select the data format 'csv'.

e) Enter the column details and their data type.


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

f) Preview the SQL code generated for table and click **Create table**

g) Save the query

![AThena table created](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gimd3ub0vg36tzmio0cr.jpg)

**Step 9**: Create dashboards using Amazon QuickSight

a) Check that you have the correct policy attached to IAM for Amazon QuickSight.


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

b) Navigate to the search bar and type 'QuickSight'


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

c) Create a QuickSight account


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

d) Select an appropriate support plan 


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

e) You will receive a confirmation after completing the sign-up process.


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

f) Click **New dataset** 

![create a new dataset](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/99frtyc9hkr3scu0ysvs.jpg)

g) Click on the data source **Athena** 


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

h) Click on the data catalog and select the 'processed sales' table from Athena

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

![choose database table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vu86xah40mfgboyzud3z.jpg)

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

i) Click **Visualize** to finish creating the data set

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

j) If you need to trouble-shoot to connect AWS Athena table to Amazon QuickSight, navigate to your Amazon account on the right-handside and click **Manage QuickSight** then click **Security and Permissions**


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


Click **manage**

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


Ensure you check the boxes that relate to the connection to the **S3 bucket**


![AWS servicen](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7namzowdi5u4778gtwv1.jpg)


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

k) Data is successfully imported using SPICE


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

l) Click **new analysis** to commence creating visuals for your dashboard


![new analysis](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2ryhl7t8zxrvbszku6o3.jpg)

m) Create your dashboard, publish it and share the analysis.


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

n) Clean up and delete resources. Delete the glue crawler.

Navigate to Glue and on the left-hand side click 'crawler' and check the box. Under the drop-down menu **Actions** select 'Delete crawler. It's important to delete the crawler to ensure that you do not incur any necessary costs.

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

Confirm the step and click **Delete**

![confirm delete](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0kpevn2ianplx32k64di.jpg)

The crawler is deleted.


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

o) The final step is to delete S3 bucket that stores input and output files for Amazon Glue

Navigate to Amazon S3 and delete the two S3 buckets:

* aws-glue-assets (i.e. used to transform data with Amazon Glue Studio)
* learn-glue (i.e. used to transform data with Amazon Glue)

Empty the S3 bucket

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

Delete the S3 bucket. Repeat the same steps for any remaining S3 bucket.

Firstly, select the S3 bucket and click **Empty**. Type the word in the box 'permanently delete' and click the button 'Empty'.

![Empty 2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7mur3ju9imd46w5k2emy.jpg)

Return to the main menu and click on the radio button to select the S3 bucket and click **Delete**.


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


### Final Note

* Amazon QuickSight Q is not currently available in the region **Asia-Pacific (Sydney) - ap-southeast-2** at the time this blog was published.

Happy Learning! 😁

**Next Tutorial: How to ETL with AWS Glue and Amazon Glue Studio to transform your data - Part 2**

In this next tutorial it will describe ETL using AWS Glue with your own dataset

### Register for Australia's biggest data engineering conference DataEngBytes:

* Melbourne: 27 September 2022
* Sydney: 29 September 2022

Register here: https://dataengconf.com.au/


![Data Eng Bytes](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qzzqs2s8tte2hzqrf6n8.jpg)

### References

* [Use Amazon Athena and Amazon QuickSight to build custom reports of AWS Well-Architected Reviews](https://aws.amazon.com/blogs/mt/use-amazon-athena-and-amazon-quicksight-to-build-custom-reports-of-aws-well-architected-reviews/)

* [IAM identity-based policies for Amazon QuickSight](https://docs.aws.amazon.com/quicksight/latest/user/iam-policy-examples.html)

* [Data source connectivity issues for Amazon QuickSight] (https://docs.aws.amazon.com/quicksight/latest/user/troubleshoot-connect-to-datasources.html)

* [Best practices when using Athena with AWS Glue](https://docs.aws.amazon.com/athena/latest/ug/glue-best-practices.html)

* [Getting started with Athena](https://docs.aws.amazon.com/athena/latest/ug/getting-started.html)

* [Getting started with AWS Glue] (https://explore.skillbuilder.aws/learn/course/external/view/elearning/8171/getting-started-with-aws-glue?trk=4d20ac45-e3d9-43b8-89c9-e59f0e17345c&sc_channel=el)

* [AWS Glue Architecture] (https://aws.amazon.com/glue/?whats-new-cards.sort-by=item.additionalFields.postDateTime&whats-new-cards.sort-order=desc)