Migrating from a Relational Database to DynamoDB

1 hour
  • 5 Learning Objectives

About this Hands-on Lab

In this lab, we will work through the process of migrating a normalized relational database to an Amazon DynamoDB table and test to ensure our data was migrated successfully.

Learning Objectives

Successfully complete this lab by achieving the following learning objectives:

Investigate the Source Database

Log in to the provided bastion host:

   ssh cloud_user@<BASTION_PUBLIC_IP>

Then, connect to the source database:

   mysql -h <SOURCE_DATABASE_PUBLIC_IP> -u cloud_user -pbettertogether employees

Note: There’s no space between the -p parameter and the password bettertogether.

Design a Data Model and DMS Task Mapping Rules

With the information gathered from the source database, design your data model with consideration for the provided access patterns. Map this data model to the DynamoDB table partition/sort keys, as well as any local or global secondary indexes. Then, create DMS task mapping rules to map the data from the source database to your DynamoDB table.

References

Create a DynamoDB Table

Navigate to the DynamoDB web console and create a DynamoDB table called employees. Additionally, you will need to create three local secondary indexes called Fullname, hire_date, and title_from_date, as well as a global secondary index with a partition name of title and a sort key of salary_to_date.

Create and Start a DMS Task

Navigate to the Database Migration Service web console and create a new task using the mapping rules you created, and set the task to start automatically.

Test Access Patterns

Once the DMS task has completed, navigate to the DynamoDB web console and test that you have satisfied the requested access patterns.

You will need to satisfy the following access patterns:

  1. Search the table for all employees in the Development department with the title Senior Engineer and only include their most recent salary.
  2. Get all information about an employee in the Production department with the Fullname Hercules Benzmuller.
  3. Get information on all employees in the Production department hired between January 1, 1999 and January 1, 2001. Filter this data so there are no duplicate records.
  4. Get information on all employees in the Development department that had a title change in the 6 months before January 1, 2001. Filter this data so there are no duplicate records.
  5. Get information on all employees with the title Engineer and return only records that show their most recent salary date.

Additional Resources

Note: The lab provides the endpoints that are needed in order to complete the lab activity. (Upgrades to AWS DMS versions 3.4.7 and higher require that you configure AWS DMS to use VPC endpoints or use public routes. The requirement applies to source and target endpoints for: S3, Kinesis, Secrets Manager, DynamoDB, Amazon Redshift, and OpenSearch Service).

Files used in the solution videos for this lab can be found here.

We are stepping into the role of a data migration engineer. We've been tasked with migrating a database of employees from MySQL to DynamoDB. We have been provided with several common queries (access patterns) that need to be maintained and optimized through the migrations to DynamoDB. We need to investigate the source database and create a data model that will inform our Database Migration Service mapping rules and DynamoDB table structure. We will then create our DynamoDB table and a DMS task to move data from our source database to our newly created DynamoDB table. The source database has been prepared with a table named dms_source that contains the denormalized data that should be migrated to our DynamoDB table.

Our database was normalized to save space, but we need to move our data in a denormalized way. Fortunately for us, our database already includes denormalized data that was created from the normalized tables in the database. As such, we only need read-only access to our database. The following MySQL command was used to create our dms_source table and is included for your reference:

CREATE TABLE
    dms_source
SELECT
    employees.*,
    titles.title,
    titles.from_date AS title_from_date,
    titles.to_date AS title_to_date,
    salaries.salary,
    salaries.from_date AS salary_from_date,
    salaries.to_date AS salary_to_date,
    dept_emp.dept_no as department_number,
    departments.dept_name as department_name,
    dept_emp.from_date AS dept_from_date,
    dept_emp.to_date AS dept_to_date
FROM (SELECT * FROM employees LIMIT 500) employees
    INNER JOIN titles ON employees.emp_no=titles.emp_no
    INNER JOIN salaries ON employees.emp_no=salaries.emp_no
    INNER JOIN dept_emp ON employees.emp_no=dept_emp.emp_no
    INNER JOIN departments ON dept_emp.dept_no=departments.dept_no;

Frequent Queries (Access Patterns)

DynamoDB will allow us to utilize the query function on our new table, but the following queries are shown as an example of what can be done through the CLI to obtain the requested access patterns. Below is a list of the frequent queries or access patterns requested for this lab:

  • Get the department name, title, employee ID, first name, last name, and salary for all employees in the Development department with the title Senior Engineer (only including most recent salary):

    SELECT
    departments.dept_name AS "Department Name",
    titles.title AS "Title",
    employees.emp_no AS "Employee ID",
    employees.first_name AS "First Name",
    employees.last_name AS "Last Name",
    salaries.salary AS "Salary"
    FROM employees
    INNER JOIN salaries ON employees.emp_no=salaries.emp_no
    INNER JOIN dept_emp ON employees.emp_no=dept_emp.emp_no
    INNER JOIN departments ON dept_emp.dept_no=departments.dept_no
    INNER JOIN titles on employees.emp_no=titles.emp_no
    WHERE
    salaries.to_date="9999-01-01"
    AND
    departments.dept_name="Development"
    AND
    titles.title="Senior Engineer";
  • Get all information about an employee in the Production department with the full name Hercules Benzmuller:

    SELECT
    employees.*,
    titles.title,
    titles.from_date AS "Title from",
    titles.to_date AS "Title to",
    salaries.salary,
    salaries.from_date AS "Salary from",
    salaries.to_date AS "Salary to",
    departments.dept_name,
    dept_emp.from_date AS "Department from",
    dept_emp.to_date AS "Department to"
    FROM employees
    INNER JOIN titles on employees.emp_no=titles.emp_no
    INNER JOIN salaries ON employees.emp_no=salaries.emp_no
    INNER JOIN dept_emp ON employees.emp_no=dept_emp.emp_no
    INNER JOIN departments ON dept_emp.dept_no=departments.dept_no
    WHERE
    departments.dept_name="Production"
    AND
    employees.first_name="Hercules"
    AND
    employees.last_name="Benzmuller";
  • Get all employees with the title Engineer, returning only most recent salary:

    SELECT
    employees.*,
    titles.title,
    titles.from_date AS "Title from",
    titles.to_date AS "Title to",
    salaries.salary,
    salaries.from_date AS "Salary from",
    salaries.to_date AS "Salary to",
    departments.dept_name,
    dept_emp.from_date AS "Department from",
    dept_emp.to_date AS "Department to"
    FROM employees
    INNER JOIN titles on employees.emp_no=titles.emp_no
    INNER JOIN salaries ON employees.emp_no=salaries.emp_no
    INNER JOIN dept_emp ON employees.emp_no=dept_emp.emp_no
    INNER JOIN departments ON dept_emp.dept_no=departments.dept_no
    WHERE
    titles.title="Engineer"
    AND
    salary.to_date="9999-01-01";
  • Get all employees in the Production department hired between 1999 and 2001:

    SELECT
    employees.*,
    titles.title,
    titles.from_date AS "Title from",
    titles.to_date AS "Title to",
    salaries.salary,
    salaries.from_date AS "Salary from",
    salaries.to_date AS "Salary to",
    departments.dept_name,
    dept_emp.from_date AS "Department from",
    dept_emp.to_date AS "Department to"
    FROM employees
    INNER JOIN titles on employees.emp_no=titles.emp_no
    INNER JOIN salaries ON employees.emp_no=salaries.emp_no
    INNER JOIN dept_emp ON employees.emp_no=dept_emp.emp_no
    INNER JOIN departments ON dept_emp.dept_no=departments.dept_no
    WHERE
    departments.dept_name="Production"
    AND
    UNIX_TIMESTAMP(employees.hire_date) BETWEEN UNIX_TIMESTAMP("1999-01-01") AND UNIX_TIMESTAMP("2001-01-01");
  • Get all employees in the Development department who had a title change in the 6 months before January 1, 2001:

    SELECT
    employees.*,
    titles.title,
    titles.from_date AS "Title from",
    titles.to_date AS "Title to",
    salaries.salary,
    salaries.from_date AS "Salary from",
    salaries.to_date AS "Salary to",
    departments.dept_name,
    dept_emp.from_date AS "Department from",
    dept_emp.to_date AS "Department to"
    FROM employees
    INNER JOIN titles on employees.emp_no=titles.emp_no
    INNER JOIN salaries ON employees.emp_no=salaries.emp_no
    INNER JOIN dept_emp ON employees.emp_no=dept_emp.emp_no
    INNER JOIN departments ON dept_emp.dept_no=departments.dept_no
    WHERE
    departments.dept_name="Development"
    AND
    UNIX_TIMESTAMP(titles.from_date) BETWEEN UNIX_TIMESTAMP("2000-06-01") AND UNIX_TIMESTAMP("2001-01-01");

What are Hands-on Labs

Hands-on Labs are real environments created by industry experts to help you learn. These environments help you gain knowledge and experience, practice without compromising your system, test without risk, destroy without fear, and let you learn from your mistakes. Hands-on Labs: practice your skills before delivering in the real world.

Sign In
Welcome Back!

Psst…this one if you’ve been moved to ACG!

Get Started
Who’s going to be learning?