Professional Level

Migrating from a Relational Database to DynamoDB

1 hour

6 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.

Learning Objectives

Successfully complete this lab by achieving the following learning objectives:

Investigate Source Database

Connect to the source database:

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

Use the following queries to gather information about the source database:
```
SHOW TABLES;
```
```
DESCRIBE dms_source;
```
```
SELECT * FROM dms_source LIMIT 10;
```

Design Data Model and DynamoDB Table

With the information gathered from the source database, design your data model with consideration to the provided access patterns, and map this to DynamoDB table partition/sort keys, as well as any local or global secondary indexes.

Create `employees` DynamoDB Table

Navigate to the DynamoDB web console and create the table. (It does not have to be named employees, but this is the table name used in the solution videos/files.)

Create DMS Task Mapping Rules

With the information from the source database, and the data model you’ve arrived at, create DMS task mapping rules to map the data from the source database to your DynamoDB table.

DMS Task Mapping Rule Reference

Create/Start DMS Task

Navigate to the DMS web console, create a new task with the mapping rules you created, and set the task to start on creation.

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.

Contact Sales See Pricing

Additional Resources

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, 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.

dms_source Table Creation

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)

Get the department name, title, employee ID, first name, last name, and salary for all employees in department Development with 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 Department Production with 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 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 six 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");

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

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.