AWS Glue | CSV to Parquet transformation | Getting started
Tags: aws, awsglue, data, tutorial
Published: 2023-01-24 04:24:20

AWS Glue is a fully managed serverless ETL service. It makes it easy to discover, transform and load data that would be consumed by various processes and applications. If you want to learn more about AWS Glue then please refer to the video on [AWS Glue Overview](https://youtu.be/ZSryMLGMLLw)

![Objective (CSV to Parquet)](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7oacipe3k061ma84hkeh.png)

In this article, we will go through the basic end-to-end CSV to Parquet transformation using AWS Glue. We will use multiple services to implement the solution like IAM, S3 and AWS Glue. As a part of AWS Glue, we will use crawlers, Data Catalog including Database & Tables and ETL jobs.

## Architecture

![Workflow](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2tk1al1zm1r726my9os6.png)

Let’s understand the above flow.

1. Create a crawler, which will connect to the S3 data store
2. Post successful connection, it will infer or determine the structure of the CSV file using a built-in classifier
3. The crawler will write the metadata in the form of a table in the AWS Glue Data Catalog
4. After populating the data catalog, create the ETL job to transform CSV into parquet
5. The data source for the ETL job will be the AWS Glue Data Catalog table and as a part of the transformation, we will apply the mappings. Post transformation, the data will be stored in S3 as a parquet file

## Hands-On

Enough of text, let’s jump to the AWS Management Console

### Create IAM role

As a first step, create the IAM role to provide the necessary permissions to AWS Glue to access various services. (For ex: S3)

- Go to _IAM Management Console → Roles → Create role_
- Select Glue as a service & add _AWSGlueServiceRole_ permission
- Create the role

With the above permission, AWS Glue will be able to access the S3 buckets which contain aws-glue as a part of the bucket name for the defined permissions in this policy. Also, it contains additional permissions.

### Create S3 Bucket

Create the S3 bucket with the name which contains _aws-glue_ as a part of the bucket name Or else you have to modify the policy in the above step.

Post bucket creation, create the following folder structure

```
s3-bucket
├── data-store/
│   └── annual-reports/
│       └── csv_reports
└── target-data-store/
    └── parquet-reports/
```
We will create the database in Glue with the same name as _annual_reports_, and the table name would be _csv_reports_. However, it is not necessary that the database & table should have the same name as the folders. It’s just that, it is easy to map things with this kind of naming convention.

As a next step, upload the CSV file in the _csv_reports_ folder. I have used the CSV data from [here](https://stats.govt.nz/large-datasets/csv-files-for-download/)

### Create database

Go to _AWS Glue Console → Click Databases → Add database_ as follows

![Create database](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jphjd86wiqsfnr270v09.png)

### Create Crawler to populate Table

The table can be created manually or via a crawler. The crawler is a program that connects to the data store, infers structure/schema using a built-in or custom classifier and writes the Metadata as a table.

To create a crawler → Click on _Crawlers_ from the left panel → Configure the details as follows and create the crawler

![Crawler config](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o8of5zvb0z88k4zv16ml.png)

After creating the crawler, run the crawler and if everything is configured as above, it will populate the table under the selected database.

### Create ETL job

Once the table is populated, create the ETL job to transform & load the CSV file as a parquet file.

To create an ETL job → Click on _Jobs_ from the left panel

To create a job, there are a couple of options and we will select Visual with a blank canvas. Apart from that it also allows you to write & upload the python shell scripts, spark scripts and interactive development with jupyter notebook. So, you can try these options.

Here we have two side-by-side views, _Visual_ on the left side and its respective options on the right side.

Click on Source under Visual and select the data source. In our case, it’s _AWS Glue Data Catalog_. Select the node and configure the respective properties on the right side which are _Database_ & _Table_.

![Config Glue Job](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e2m4d1zylw0svwlc3f3h.png)

The next step is the transformation, click on _Transform_ → Select _Apply Mapping_ or any other transformation based on the requirements. For the purpose of this article, _Apply Mapping_ is selected.

![Config Glue Job1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1r9b46n86b5d4mojmuua.png)

Finally, click on _Target_ → select _S3_ to load the data as a target data store. Configure the properties.

![Config Glue Job2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e6qe4zvd0870m59b8wpn.png)

After this configuration, you can check the generated script under the _Script_ tab. The script can be modified as per the requirement. As a next step, click on _Job details_ and update the job name and select the _IAM_ role. Save the job and click on _Run_.

![Job details](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/n7ckxochlbx7vndvxvtq.png)

Check the job Run details under _Runs_ and check the CloudWatch logs.

![Job run](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wlh0ugcfgeb0p1vf8yt3.png)

At this point, the CSV to Parquet transformation is successful. Check the output in the _S3 Target Location_ configured in the above step.

For a detailed step-by-step tutorial and the implementation of the above use case please refer to the below video.

{% embed https://youtu.be/nMtvGkSSWRo %}

If you have any questions, comments, or feedback then please leave them below. [Subscribe to my channel](https://www.youtube.com/srcecde?sub_confirmation=1) for more.