An ETL Job using AWS Glue Studio to inner join DynamoDB tables, Apply Queries and Store the result in S3
Tags: database, beginners, python, aws
Published: 2023-03-23 01:27:26


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xr4e4pllaytk3sk8gapb.png)

## ETL Jobs

**ETL** stands for **extract, transform, and load.** And this is the most common paradigm for combining data from various systems into a single database, data store, or warehouse for legacy storage or analytics.

> <u>**Extraction**</u>
> Extraction is the process of retrieving data from one or more sources. Following data retrieval, ETL is to compute work that loads the data into a staging area and prepares it for the next phase.
> <u>**Transformation**</u>
> Transformation is the process of mapping, reformatting and other operations to prepare data for consumption.
> <u>**Loading**</u>
> Loading involves successfully inserting the transformed data into the target database, data store or data warehouse.

All of this work is processed in what the business intelligent developers call an ETL job.

---

## AWS Glue

AWS Glue is the key service that integrates data over a data lake, data warehouse, and purpose-built data stores which simplifies data movements from multiple sources

## AWS Glue Studio

AWS Glue Studio is an easy-to-use graphical interface for creating, running, and monitoring AWS Glue extract, transform, and load (ETL) jobs.

---







![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hry0agn1pb9m9k934ngo.png)




Olive wants to purchase a finger ring with her birth stone. 
 
The details of Birth stones and  ring prices are stored in  two **DynamoDB tables**

Now, We are going to create an **ETL job** using AWS Glue Studio which **inner join** two DynamoDB tables **Birthstone** and **RingsTable** on condition **RingsTable.RingStoneName = Birthstone.Stone Name**

After that,  **apply a Query on the result table** to find the ring stone and its price matching to Olive's Birth Month  and then **save the result to the s3 bucket** named "gemstonejobresult"

We use **git** for the **source code management** so finally we are going to create a remote git repository on **AWS Code Commit** and  **push the job to the git repository**
 

## Prerequisites

- Two tables created using AWS DynamoDB Service: **Birthstone**, **RingsTable** 

(Recipe: https://dev.to/aws-builders/cook-a-recipe-with-aws-dynamo-db-table-1kn1)
- S3 Bucket to store the result - **gemstonejobresult**
- A git repository on AWS Code commit - **gemstone_job_repository**
- An IAM role named  **AWSGlueServiceRoledemo**- with permission to your Amazon S3 sources, targets, temporary directory, scripts, and any libraries used by the job.

Lets set up the **Prerequisites** first

<u> 1 : DynamoDB Tables</u>

- Here, I have created two Tables named "Birthstone" and "Birthstone" and some items to them


- Make sure to enable **PITR** on both the tables

The Table schema details provided below





![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sab022r42h9ok1n1xuzm.png)







**Table Data**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/c7pntmixoel5wmhf5u73.png)



![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4gccefyo2b2t6lteaoaz.png)


---

<u> 2: S3 Bucket for Storing the Query Result </u>

- Navigate to **S3 console**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4hho8vk56sf0nqjsafkv.png)

-  Click **Create Bucket** button 

- You will be navigated to bucket creation page
- Specify a **unique name** to the bucket, Leave the rest of the fields to default values 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z2410wufi6x91erk327y.png)

- Scroll down and click **create bucket** button

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jwvka7k1vtyc41pijrvo.png)







- You will be navigated back to bucket list page on S3 Console and the newly created bucket will be listed here

---


<u> 3: A git repository on AWS Code commit </u>

**AWS Code commit**

- **AWS CodeCommit** is a **fully-managed source control service** that makes it easy for companies to host secure and highly scalable private Git repositories. 
- **CodeCommit** eliminates the need to operate your own source control system or worry about scaling its infrastructure.

> <u>Repository</u>

> - In CodeCommit, the fundamental version control object is a repository. 
> - It's where you keep your project's code and files safe. It also keeps track of your project's history, from the first commit to the most recent changes. 
> - You can collaborate on a project by sharing your repository with other users.

> Before you can push changes to a CodeCommit repository, you must configure an IAM user in your Amazon Web Services account, or set up access for federated access or temporary credentials.
> **DO NOT USE ROOT USER**

- Navigate to code commit console

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qs3rw01hstgmpp6cdzqp.png)


-  Click **Create Repository** button to create a git repository for storing our code 


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mg7wp5sjtvshucwrhp2d.png)

- Specify the repository name, I am naming it as "gemstone_job_repository" and click **create repository** button


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3jp91n5jad5y04ioxod5.png)



- Lets Create a **Readme.md** file and commit it to the repository, Click Create file button

- Add some data to the file and save the file as **ReadMe.md**
- The file will be saved to the **main branch** 
- Specify the **Author name**,**Email address** and **commit message** (Though optional, its a good practice to provide commit message)
- Click the button **commit changes**


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wv2f7fgmdu551bcauwy4.png)




![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/itvcjk6k9z8neww38k9j.png)

> **<u>Branches in git  </u>**
> Branches can be used to develop new features, save a specific version of your project from a specific commit, and much more.

 When you create your first commit in aws code commit console , a default branch **main** is created for you

Now, we have created a repository on aws console , made a first commit to it by saving a ReadMe file to the main branch 


<u> 4: IAM role</u>

- Navigate to IAM Console,

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/a79320x4c1u2jycx5ldp.png)

- Click on **Roles** link on Left Pane

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sem2i85zu6g6jkw07hnw.png)

- Click **Create Role** button to create a role

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qopfpelpl23mjgouw8p7.png)

- Select **AWS Service** as **Trusted entity type** 
- Select **glue** option from **Use cases for other services** dropdown


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jjgr2iao7xay3mwhj7lu.png)





- Add the below permissions to the role and  click **Next** button


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wtcup6exgy0veya3g3a3.png)

- Specify a **role name** "AWSGlueServiceRoledemo" and Click **Create Role** button 



---

## Create an ETL job using AWS Glue Studio to inner join DynamoDB Tables

- Navigate to **AWS Glue Studio** from aws console

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/013x3hvlhg0l0g40lw79.png)


- Click **Jobs** from Left pane

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u83ca6dti55oxkiqwnkx.png)

- You will be navigated to Jobs page, where you can create ETL Jobs
- By default, **Visual with source and target** option  would be selected

- Select **Source** as **DynamoDB** and **Target** as **Amazon S3** in **Create job** 

- Click **Create** button

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/25o6n0xewuwtmenzgl8f.png)

- An **untitled job**  with the source as DynamoDB and target as Amazon S3 will be created for you. And You will be navigated to the page of untitled job where you will be able to edit the configuratons

---

<u>**Visual Tab**</u>

- First You will be on the Visual tab

> Customers can create data integration jobs using the Amazon Glue API from a JSON object that displays a visual step-by-step workflow thanks to the API offered by AWS Glue. 
> Clients can then work with these jobs using the visual editor in Amazon Glue Studio.

- Now we are going to create a job which **reads data from two dynamodb tables** BirthStone table and Ring table.

-  We are going to **inner join** these two tables **based on name attribute** 
and **apply a query to find birtstone suitable for Olive and its price**

- First we will add the required **Source blocks, Action blocks and Target Blocks**



- Store the **Query result in S3 bucket**


 

  - **Nodes Required:**

      - Two DataSource DynamoDB (RingsTable, Birthstone)
      -  Transfrom Join (For applying inner join)
      - SQL Query ( For querying the result table)
      - Data target - S3 Bucket

- Click on the **Source** and add one more **DynamoDB table**
- Remove the ApplyMapping Block, if its already there,  since we don't need it for this job
(Click on the ApplyMapping Block and Click Remove)

- From the **Action**, Select **Join** and Add it
- From the **Action**, Select **SQL Query** and Add it
- **S3 bucket** will be there on the flow as target , leave it as it is 
 
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/a1ictyao89f01n6zp4jx.png)


**Now, Lets Configure the flow**

<u>Source Nodes</u>

- Click on One of the Data Source DynamoDB nodes, 
- On the Right pane, configure the node properties as shown in the picture below


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bazxzxbkc9hq0qc8taaw.png)

- Select the option Choose  the  Dynamodb Table directly radio button for DynamoDB source

- Select the Birthstone table from the Dropdown, Make sure PITR is enabled for the Birth stone table , if not the table will not be populated on the dropdown, (If you do not see the table in the dropdown, Go to the Dynamodb service, enable PITR for the Birthstone table and  Ring Table. Then Refresh this page to get the tables on this dropdown)



- Click on the  second DynamoTable Data source node, 
- On the Right pane, configure the node properties as shown in the picture below

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7a7n6zbw1j9gqhesd0ug.png)

- Now, We have configured the Source Nodes successfully

---

<u>Action Nodes</u>

-





![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f1inumrjeyu062o39x9d.png)


- Click on the Transform join node
-  On the Right pane,  Click on the Node Properties Tab
- Select the two DynamoDB tables as Node Parents


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8qtbl87yjfx8vi309439.png)


- Click on the Transform Tab, Make sure Inner join is selected as Join Type, Click Add Condition button


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lbq0cdvxpeu329r4tnl4.png)

- Specify the inner join condition as shown in the image below

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k7q03ywk94tb51d7lgta.png)

- Click on Transform SQL Query node
-  On the Right pane,  Click on the Node Properties Tab
- Select Transform Check box from the Node Parents dropdown list, You can see Join node getting selected 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/n2t81rtlu2i2t8pdu8o0.png)

- Click on the Transform Tab to specify the query on the Inner joined Table

- As You see here, Inner Joined table is alised as  myDataSource
- Now Lets Write the SQL Query to generate the Birth Stone name and Price of the Birst stone matching to olive's Birth Month



![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gf03a19hjtffx97kd3up.png)




- Olive's Birth Month is December since she is born on a christmas day , Lets Create a query to get the Birth Stone gem and its price matching to the month 

`select RingStoneName, Price from myDataSource where BirthMonth = 'December' limit 1`



![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xujteshqzpcyk14um870.png)

Yes, Now we have configured Transform nodes successfully 

<u>Target Node</u>


- We need to store the query result in the s3 bucket 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0080ngkgnvc1zmb12lb6.png)

- Click on the Data target - S3 bucket node

- On the Right pane,  Click on the Node Properties Tab and Tick SQL Query checkbox

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nc8tiyfksixk5gj54sp3.png)

- Click on Data Target Properies - S3 Tab
- Configure it as shown in the below image

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/th2rwobf5qbxjufc46l4.png)

- As you see, Format should be **Parquet** 
- The bucket location should be selected from s3 so that a Parquet file with the result data will be saved on the S3 bucket once the job execution is completed successfully

- After all the configurations, The Visual Tab will be seen as below image


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/x6yyk4c2hsyh2vjs4e7l.png)

---

<u>**Script Tab**</u>

- Based on the Job Configuration, **Python script will be generated on the Script tab**


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/76nmtm3m8umxms66l839.png)

<u>**Job details**</u>

Now, Navigate to Job details tab to specify the job details

By default, Job name would be Untitled, Specify a job name in the Name Field.
Specify a description as well

Select the **AWSGlueServiceRole-demo** which we already created as a part of prerequisites

Leave the remaining to default settings 

Click **Save** button to save the Job


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/s8ck9ce8hwlbqzbw4xsr.png)





---

<u>**Version Control Tab**</u>

Navigate to **Version Control** Tab

Here, You need to do the git configurations
 Select **AWS Code commit option** for git service 


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2snlo1enjnhotnnk9u57.png)


In the **Repository Configuration** section, choose the **git repository**  that we created in aws code commit (Refer Prerequisites)

Choose the branch as **main**


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t12ewpmlnk4b3lc82jzs.png)


Click **Save** button on the top right

Click the **Actions** Button, Choose **Push to repository** option

The job will be pushed to the repository and a **JSON file** will be saved on to the code repository. 


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/megqrrmkyskq6e2i6pah.png)






![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nvcd4964v6ety96wkstk.png)


Navigate to aws code commit and verify if files are saved on to the repository


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ts1jjz4154s8i9jzdu0z.png)

---

## Executing the ETL Job and Verifying the result in S3 bucket

Once you have saved the job, its time to Run the job 

- Click the **Run** button on the Top Right pane 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/s06478l7a3fdtzcltbch.png)

- You will be getting the following message

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ge8coy2n1kjncm01zkdg.png)

- Navigate to **Runs** Tab to see the job status


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nx8ha8v84twrbq2gki5j.png)

- Once the Run status becomes succeeded,  Navigate to **S3 Console** and click on the **gemstonejobresult** bucket

- You will be able to see the result saved as a parquet  file


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1i99gh40hqevtnpztshr.png)

- Download the **Parquet** file and save the file locally by clicking the **Download** button


---


- Open the website https://www.parquet-viewer.com/ to read results from parquet file


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7ufrqb47o8xxuqe974jb.png)

- Now You will be able to view the result 


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/l7vkozfr731e7ebl1co3.png)

**<u>Reading the Parquet File object using S3 select</u>**

Alternatively, You can Query the Parquet file with **S3 select**

- Navigate to **S3 Console** and click on the **gemstonejobresult** bucket

- Click on the Parquet file name link where the result is saved


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5jkb76xjez36zt5eu4nk.png)

- Now, Click the **Object Actions** button and from the list, select the **Query With S3 Select** and give a click

- You will be navigated to Query with S3 Select Page, Now by default **Apache Parquet** will be chosen in input settings, Keep it as it is

- You can choose the Format in Output settings.

> S3 Select allows you to specify how the query results are serialized in the output. You can choose formats like JSON or CSV. This determines how the data will be structured in the output files. 

- Lets choose **JSON** here so that the result of the query will be displayed in JSON Format


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/35bqiphzf2z2o5dus4hu.png)


- We are going to read the Parquet File using the below Select Query which is displayed in the query editor by default.

`SELECT * FROM s3object s LIMIT 5`

- Now Click **Run SQL query** button to execute the query
 


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wrpk2q40et0ku6brjh9i.png)

- Once the Query is executed successfully, You will be able to view the result in JSON format (The format chosen in **Output settings**)



![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z8vztotdou5cpwkdwvy7.png)

- You can download the result by clicking the **Download results** button


---


Yaay , We found it , Olive needs to purchase a **Turquoise ring** and its price is **612$**


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m8srj7kj01v7thia1gea.png)


And that was Olive’s story.

---


## Challenge Task

- Set up an **ETL job** by following the above instructions 

- **Find out the month corresponding to the Birth stone which is priced highest**

- Post a screenshot of the answer in comment section