Solution
1. RDS SETUP (MySQL)
Step 1: Create RDS Instance
Go to RDS > Create database
Standard Create
Engine: MySQL
Template: Free tier
DB cluster identifier: rds-customer-db
Master username: admin
Password: Awsuser1
DB instance class: [Link]
Public Access: Yes
Leave rest as default, Create Database
2. MySQL Workbench Setup
Step 2: Connect to RDS
Add OPT_LOCAL_INFILE=1 in Advanced tab.
Use RDS endpoint, port 3306, database name: dev , username: admin ,
password: Awsuser1 .
Step 3: Create Table
Execute this MySQL schema (corrected for typos):
sql
CopyEdit
CREATE TABLE customer (
Solution 1
Year_Birth VARCHAR(100),
Education VARCHAR(100),
Marital_Status VARCHAR(100),
Income VARCHAR(100),
Kidhome VARCHAR(100),
Teenhome VARCHAR(100),
Dt_Customer VARCHAR(100),
Recency VARCHAR(100),
MntWines INT,
MntFruits INT,
MntMeatProducts INT,
MntFishProducts INT,
MntSweetProducts INT,
MntGoldProds INT,
NumDealsPurchases INT,
NumWebPurchases INT,
NumCatalogPurchases INT,
NumStorePurchases INT,
NumWebVisitsMonth INT,
AcceptedCmp5 INT,
AcceptedCmp1 INT,
AcceptedCmp2 INT,
Complain INT,
Z_CostContact INT,
Z_Revenue INT,
Response INT
);
Step 4: Load Data
Assuming file is accessible, run:
sql
CopyEdit
LOAD DATA LOCAL INFILE '/home/labuser/Desktop/Project/wingst15-m1-cust
Solution 2
omer-challenge/inputfile/[Link]'
INTO TABLE customer
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
3. GLUE SETUP
Step 5: Create Glue Connection to RDS
Go to AWS Glue > Connections
Create connection
Type: MySQL
Connection name: mysql-rds-connection
Hostname: [your RDS endpoint]
Database name: dev
Username: admin
Password: Awsuser1
Test the connection
Step 6: Visual Job in Glue
Go to AWS Glue Studio > Visual ETL
Create a new job: customer_job
Source: MySQL (using your connection)
Table: customer
Transform:
Add Drop Null Fields node to remove rows with NaNs or empty strings.
Add a Transform node to calculate age:
Solution 3
sql
CopyEdit
CAST(2024 - CAST(Year_Birth AS INT)) AS age
Add another transform to group by age ranges:
sql
CopyEdit
CASE
WHEN age BETWEEN 18 AND 30 THEN '18-30'
WHEN age BETWEEN 31 AND 40 THEN '31-40'
WHEN age BETWEEN 41 AND 50 THEN '41-50'
WHEN age BETWEEN 51 AND 60 THEN '51-60'
WHEN age > 60 THEN '61+'
END AS age_range
Aggregate:
Group by age_range
Average of: Income , MntWines , MntFruits , MntMeatProducts , MntFishProducts ,
MntSweetProducts , MntGoldProds
Sink: Amazon S3
Path: s3://your-bucket-name/output/age_range_data/
Step 7: Marital Status + Household Analysis
Add another branch in the same job or separate job
Group by:
Marital_Status , Kidhome , Teenhome
Aggregate:
Solution 4
Avg of MntWines , MntFruits , MntMeatProducts , MntFishProducts , MntSweetProducts ,
MntGoldProds
Sink:
s3://your-bucket-name/output/marital_status_data/
4. Save and Run Glue Job
Attach IAM role: glue_role
Name: customer_job
Save and Run
What’s Next?
Once this is done, you can move on to the Machine Learning section using the
customer_cleaned_data.csv file from the S3 bucket and AWS SageMaker.
Great! You're working on a cloud-based loan default predictor using Amazon
SageMaker. Here's a step-by-step breakdown you can follow for each task. Let
me know if you'd like code snippets or troubleshooting for any step.
✅ Task 1: Launching an Amazon SageMaker Instance
1. Access SageMaker:
Go to AWS Console.
Search and open Amazon SageMaker from the Machine Learning
section.
2. Create Notebook Instance:
Name: loan-prediction-natebook
Instance type: [Link]
Solution 5
Permissions: Create/select a role with AmazonS3FullAccess (or restricted
access to your bucket if you prefer)
Click Create notebook instance.
3. Access the Notebook:
Wait until status is InService
Click Open Jupyter or JupyterLab
✅ Task 2: Upload [Link]
1. Inside JupyterLab:
Use the Upload Files button (upper left in the File Browser).
Upload the [Link] file from your local system.
✅ Task 3: Data Loading
import boto3
import pandas as pd
import numpy as np
from sklearn.model_selection import train_test_split
from sagemaker import get_execution_role
# Get execution role
role = get_execution_role()
# Construct the S3 path
bucket = 'loan-data123456' # replace with your actual bucket
folder = 'loan_cleaned_data'
file_name = 'loan_cleaned_data.csv'
s3_url = f's3://{bucket}/{folder}/{file_name}'
# Load dataset
Solution 6
df = pd.read_csv(s3_url)
[Link]()
✅ Task 4: Feature Engineering
# One-hot encoding for 'purpose'
df = pd.get_dummies(df, columns=['purpose'], dtype=int)
✅ Task 5: Data Preprocessing
from [Link] import resample
# Count classes
print(df['not_fully_paid'].value_counts())
# Split classes
df_majority = df[df['not_fully_paid'] == 0]
df_minority = df[df['not_fully_paid'] == 1]
# Upsample minority class
df_minority_upsampled = resample(df_minority,
replace=True,
n_samples=len(df_majority),
random_state=42)
# Combine
df = [Link]([df_majority, df_minority_upsampled])
print(df['not_fully_paid'].value_counts()) # should now be balanced
✅ Task 6: Model Training
from [Link] import RandomForestClassifier
Solution 7
# Define X and y
X = [Link](columns=['sl_no', 'not_fully_paid'])
y = df['not_fully_paid']
# Split data
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.4, random_stat
e=42)
# Train model
rf = RandomForestClassifier(random_state=42)
[Link](X_train, y_train)
✅ Task 7: Model Evaluation
from [Link] import classification_report
# Predict and evaluate
y_pred = [Link](X_test)
print(classification_report(y_test, y_pred))
✅ Task 8: Save Model to S3
import joblib
import tempfile
# Save model
with [Link]() as f:
[Link](rf, [Link])
# Upload to S3
s3 = [Link]('s3')
s3.upload_file([Link], 'loan-data123456', '[Link]') # replace with your b
ucket name
Solution 8
Let me know if you’d like me to walk through deployment on SageMaker, setting
up endpoints, or adding evaluation visualizations!
Solution 9