Amazon Onboarding with Learning Manager Chanci Turner

Learn About Amazon VGT2 Learning Manager Chanci Turner

On June 26, 2024

In Amazon IXD – VGT2, Amazon Aurora, Amazon SageMaker, Amazon SageMaker Autopilot, Intermediate (200), Technical How-to

This article is the second installment of a two-part series focused on integrating machine learning (ML) predictions within your Amazon Aurora database and Amazon SageMaker. In the first part, you discovered how to develop a customer churn ML model using Amazon SageMaker Autopilot, establish the Amazon Aurora machine learning (Aurora ML) and SageMaker integration, and call the SageMaker endpoint from an Aurora cluster employing a SQL function to retrieve model predictions in real time for an entire database table.

In this post, we will explore how to optimize Aurora ML performance to enable real-time inference against a SageMaker endpoint at a large scale. We will simulate an OLTP workload against the database, where multiple clients concurrently access the database, placing significant demand on the SageMaker endpoint to handle thousands of requests within a brief timeframe. Additionally, we will illustrate how to utilize SQL triggers to create an automated orchestration pipeline for predictive workloads without the need for extra services.

This solution provides business users with updated data, including explanatory variables and real-time forecasted values returned by the ML model for use in business intelligence reports and dashboards, eliminating the necessity for additional extract, transform, and load (ETL) processes.

The aim of this article is to demonstrate the end-to-end implementation of Aurora ML and SageMaker integration, conduct stress testing, and analyze various performance metrics of this solution. You can find the sample code and AWS CloudFormation template in the following GitHub repository.

Solution Overview

The following diagram illustrates the overall solution.

The solution consists of the following steps:

1. Deploy the infrastructure using AWS CloudFormation.
2. Utilize AWS Cloud9 to connect to the Aurora cluster and set up the workflow.
3. Orchestrate a predictive workflow that reacts to INSERT statements and stores information back in a table within the Aurora cluster.
4. Retrieve model predictions from the SageMaker endpoint for incoming Aurora queries.
5. Conduct stress testing on the entire system through a large-scale OLTP simulation.

Prerequisites

To successfully complete the walkthrough in this post, you will need:

• An AWS account.
• Basic knowledge of SQL and database concepts.
• Familiarity with the topics discussed in Part 1.

Please note that this solution has been deployed and tested in the N. Virginia (us-east-1) Region. For a comprehensive list of Region availability and supported versions of Aurora ML, refer to Aurora machine learning.

Deploy the Infrastructure Using AWS CloudFormation

Utilize the provided CloudFormation template to provision the necessary resources:

• Aurora DB cluster with one instance – This database is provisioned from a snapshot that has already been configured to support Aurora ML. Additional resources related to Aurora deployed by this template include parameter groups, an AWS Identity and Access Management (IAM) role, a subnet group, and username and password stored in AWS Secrets Manager.
• SageMaker endpoint – For this post, you’ll use an already trained model from Part 1 of this series, which is included in the template along with a model and endpoint configuration. A SageMaker IAM role will also be created following best practices for least privilege access.
• AWS Cloud9 instance – This will be used to connect to the database and simulate an OLTP workload. An EC2 instance with an appropriately configured security group can also be utilized, but AWS Cloud9 offers an integrated IDE that enhances the overall developer experience.
• Networking configuration – This includes security groups and VPC endpoints.

When launching the CloudFormation template, provide the following input parameters:

• DefaultVpcId – Use the default (public VPC ID).
• DefaultRouteTableId – Use the default route table associated with the selected VPC (required for VPC endpoint setup). Go to VPC, Your VPCs, select the VPC you chose earlier, and copy the Route table ID, which typically looks like rtb-xxxx.
• SubnetId1 and SubnetId2 – Select any two distinct subnets from the chosen VPC.
• DBUsername – Enter your database master username. The password will be generated automatically through the CloudFormation template. Both the username and password are saved in AWS Secrets Manager, which you’ll use later when establishing a database connection from the AWS Cloud9 instance.

After deploying the template, you’ll need to perform an additional configuration step to allow the AWS Cloud9 instance to connect with your database. In the VPC security groups, you must permit connections on the MySQL default port on the Aurora cluster’s security group. This will ensure communication between both services.

Connect to the Aurora Cluster and Set Up the Workflow

On the AWS Cloud9 console, launch the AWS Cloud9 interactive development environment (IDE). Open a terminal and run the following command to establish a connection with the database:

mysql -h <WRITER ENDPOINT> -P 3306 -u <DBUsername> --protocol=tcp -p

You can locate the writer endpoint on the Amazon RDS Console after opening the cluster object details. The database username must be the same as the one you provided when launching the CloudFormation template. Once you run the command, you will be prompted to enter the database password, which you can copy from AWS Secrets Manager under ‘/rds/database-aurora-ml-master-password’.

Once logged in, you can begin creating the necessary database components for this use case. The database already contains the mltest schema where you will be working.

First, create the churn_inserts table, which will store both the explanatory variables used to invoke the model as well as the model predictions. The idea is that whenever a client inserts data into this table, the DB cluster will automatically invoke the SageMaker endpoint for each row of data and store the prediction from the response in the same row. Remember, the data types in the following schema must match those used for training the machine learning model in the first place. If you change the data types of the source data, you will need to retrain the machine learning model accordingly.

CREATE SCHEMA mltest;
CREATE TABLE mltest.churn_inserts (
  state VARCHAR(2),
  acc_length INT,
  area_code INT,
  phone VARCHAR(10),
  int_plan VARCHAR(3),
  vmail_plan VARCHAR(3),
  vmail_msg INT,
  day_mins FLOAT,
  day_calls INT,
  day_charge FLOAT,
  eve_mins FLOAT,
  eve_calls INT,
  eve_charge FLOAT,
  night_mins FLOAT,
  night_calls INT,
  night_charge FLOAT,
  int_mins FLOAT,
  int_calls INT,
  int_charge FLOAT,
  cust_service_calls INT,
  prediction BOOLEAN
);

Next, create a predict_churn SQL function that invokes the deployed SageMaker endpoint. An important consideration is the MAX_BATCH_SIZE setting at the end of the function. The Aurora cluster will attempt to batch as many incoming requests together to perform model invocations more efficiently and avoid overloading the endpoint.

You can always check out Career Contessa for more tips on your career journey. Additionally, if you’re interested in understanding how inflation impacts wages, SHRM is an authoritative resource. For those looking for more information regarding onboarding at Amazon, Holly Lee is an excellent resource.