Data preparation using an Amazon RDS for MySQL database with AWS Glue DataBrew

With AWS Glue DataBrew, data analysts and data scientists can easily access and visually explore any amount of data across their organization directly from their Amazon Simple Storage Service (Amazon S3) data lake, Amazon Redshift data warehouse, or Amazon Aurora and Amazon Relational Database Service (Amazon RDS) databases. You can choose from over 250 built-in functions to merge, pivot, and transpose the data without writing code.

Now, with added support for JDBC-accessible databases, DataBrew also supports additional data stores, including PostgreSQL, MySQL, Oracle, and Microsoft SQL Server. In this post, we use DataBrew to clean data from an RDS database, store the cleaned data in an S3 data lake, and build a business intelligence (BI) report.

Use case overview

For our use case, we use three datasets:

• A school dataset that contains school details like school ID and school name
• A student dataset that contains student details like student ID, name, and age
• A student study details dataset that contains student study time, health, country, and more

The following diagram shows the relation of these tables.

For our use case, this data is collected by a survey organization after an annual exam, and updates are made in Amazon RDS for MySQL using a Java script-based frontend application. We join the tables to create a single view and create aggregated data through a series of data preparation steps, and the business team uses the output data to create BI reports.

Solution overview

The following diagram illustrates our solution architecture. We use Amazon RDS to store data, DataBrew for data preparation, Amazon Athena for data analysis with standard SQL, and Amazon QuickSight for business reporting.

Prerequisites

To complete this solution, you should have an AWS account.

Prelab setup

Before beginning this tutorial, make sure you have the required permissions to create the resources required as part of the solution.

For our use case, we use three mock datasets. You can download the DDL code and data files from GitHub.

1. Create the RDS for MySQL instance to capture the student health data.
2. Make sure you have set up the correct security group for Amazon RDS. For more information, see Setting Up a VPC to Connect to JDBC Data Stores.
3. Create three tables: student_tbl, study_details_tbl, and school_tbl. You can use DDLsql to create the database objects.
4. Upload the student.csv, study_details.csv, and school.csv files in their respective tables. You can use student.sql, study_details.sql, and school.sql to insert the data in the tables.

Create an Amazon RDS connection

To create your Amazon RDS connection, complete the following steps:

1. On the DataBrew console, choose Datasets.
2. On the Connections tab, choose Create connection.

3. For Connection name, enter a name (for example, student_db-conn).
4. For Connection type, select JDBC.
5. For Database type, choose MySQL.

6. Provide other parameters like RDS endpoint, port, database name, and database login credentials.

7. In the Network options section, choose the VPC, subnet, and security group of your RDS instance.
8. Choose Create connection.

Create your datasets

We have three tables in Amazon RDS: school_tbl, student_tbl, and study_details_tbl. To use these tables, we first need to create a dataset for each table.

To create the datasets, complete the following steps (we walk you through creating the school dataset):

1. On the Datasets page of the DataBrew console, choose Connect new dataset.

2. For Dataset name, enter school-dataset.
3. Choose the connection you created (AwsGlueDatabrew-student-db-conn).
4. For Table name, enter school_tbl.
5. Choose Create dataset.

6. Repeat these steps for the student_tbl and study_details_tbl tables, and name the new datasets student-dataset and study-detail-dataset, respectively.

All three datasets are available to use on the Datasets page.

Create a project using the datasets

To create your DataBrew project, complete the following steps:

1. On the DataBrew console, choose Projects.
2. Choose Create project.
3. For Project Name, enter my-rds-proj.
4. For Attached recipe, choose Create new recipe.

The recipe name is populated automatically.

5. For Select a dataset, select My datasets.
6. For Dataset name, select study-detail-dataset.

7. For Role name, choose your AWS Identity and Access management (IAM) role to use with DataBrew.
8. Choose Create project.

You can see a success message along with our RDS study_details_tbl table with 500 rows.

After the project is opened, a DataBrew interactive session is created. DataBrew retrieves sample data based on your sampling configuration selection.

Open an Amazon RDS project and build a transformation recipe

In a DataBrew interactive session, you can cleanse and normalize your data using over 250 built-in transforms. In this post, we use DataBrew to identify top performing students by performing a few transforms and finding students who got marks greater than or equal to 60 in the last annual exam.

First, we use DataBrew to join all three RDS tables. To do this, we perform the following steps:

1. Navigate to the project you created.
2. Choose Join.

3. For Select dataset, choose student-dataset.
4. Choose Next.

5. For Select join type, select Left join.
6. For Join keys, choose student_id for Table A and deselect student_id for Table B.
7. Choose Finish.

Repeat the steps for school-dataset based on the school_id key.

8. Choose MERGE to merge first_name and last_name.
9. Enter a space as a separator.
10. Choose Apply.

We now filter the rows based on marks value greater than or equal to 60 and add the condition as a recipe step.

11. Choose FILTER.

12. Provide the source column and filter condition and choose Apply.

The final data shows the top performing students’ data who had marks greater than or equal to 60.

Run the DataBrew recipe job on the full data

Now that we have built the recipe, we can create and run a DataBrew recipe job.

1. On the project details page, choose Create job.
2. For Job name¸ enter top-performer-student.

For this post, we use Parquet as the output format.

3. For File type, choose PARQUET.
4. For S3 location, enter the S3 path of the output folder.

5. For Role name, choose an existing role or create a new one.
6. Choose Create and run job.

7. Navigate to the Jobs page and wait for the top-performer-student job to complete.

8. Choose the Destination link to navigate to Amazon S3 to access the job output.

Run an Athena query

Let’s validate the aggregated table output in Athena by running a simple SELECT query. The following screenshot shows the output.

Create reports in QuickSight

Now let’s do our final step of the architecture, which is creating BI reports through QuickSight by connecting to the Athena aggregated table.

1. On the QuickSight console, choose Athena as your data source.

2. Choose the database and catalog you have in Athena.
3. Select your table.
4. Choose Select.

Now you can create a quick report to visualize your output, as shown in the following screenshot.

If QuickSight is using SPICE storage, you need to refresh the dataset in QuickSight after you receive notification about the completion of the data refresh. We recommend using SPICE storage to get better performance.

Clean up

Delete the following resources that might accrue cost over time:

• The RDS instance
• The recipe job top-performer-student
• The job output stored in your S3 bucket
• The IAM roles created as part of projects and jobs
• The DataBrew project my-rds-proj and its associated recipe my-rds-proj-recipe
• The DataBrew datasets

Conclusion

In this post, we saw how to create a JDBC connection for an RDS database. We learned how to use this connection to create a DataBrew dataset for each table, and how to reuse this connection multiple times. We also saw how we can bring data from Amazon RDS into DataBrew and seamlessly apply transformations and run recipe jobs that refresh transformed data for BI reporting.

About the Author

Dhiraj Thakur is a Solutions Architect with Amazon Web Services. He works with AWS customers and partners to provide guidance on enterprise cloud adoption, migration, and strategy. He is passionate about technology and enjoys building and experimenting in the analytics and AI/ML space.