Migration Database On AWS and Visualize data through QuickSight

Architecture Diagram

Migration

Data Warehouse

Agenda

• Migrate MySQL Database on EC2 Database to AWS RDS (Relational Database Service)
  • 1 - Setup Networking on AWS
  • 2 - Setup Your Database Server
  • 3 - Create RDS Database
  • 4 - Migrate the Database
• Offload RDS Database to Data Lake and Data Warehouse
  • 1 - Create Database Connection
  • 2 - Integrate Database to Data Lake
  • 3 - Create Data Warehouse (Redshift)
  • 4 - ETL (Extract, Transform, Load) from Data Lake to Data Warehouse
• Visualize data from Data Warehouse using BI tools
  • 1 - Create View table for data visualization
  • 2 - Visual Data from Data Warehouse using BI Tools (QuickSight)
• Testing Data Pipeline
  • 1 - Insert EC2 database a new row
  • 2 - Run Incremental Data Lake Snapshot
  • 3 - Run ETL Job
  • 4 - Check Data Visualization

Scope of Project

• Migration will be done from EC2 MySQL to RDS MySQL in version 5.7.31
• All services will be on region us-east-1 (North Virginia)
• The instruction is for Mac User. Please use PuTTY to connect to terminal if you are using Windows.
• This is the Entity Relation Diagram (ERD) of the database we will use for this migration