Create the MySQL Schema

QLDB is schema-less and only requires tables and indexes. MySQL requires a schema that contains data dictionary tables and system tables used for operational purposes. In this section, the schema for MySQL will be created.

Nearly every different QLDB streaming use case will benefit from a unique MySQL schema. For example, if only the QLDB user view is to be replicated then having the QLDB documentId be the MySQL primary key will work. If all QLDB document revisions are to be replicated, then using the QLDB documentId and revision version as a MySQL composite key will be a better fit. Both cases will be covered in this lab.

Create the Database

From the AWS console, start typing Aurora and click on RDS when the service appears.

From the RDS service homepage, click on Databases that is on the left Amazon RDS menu.

Take a note of the DB identifier name as it will be used in the next step. Before moving on, make sure that the Status shows Available like below.

Now, click on the Query Editor that is on the left Amazon RDS menu.

A benefit of using serverless Aurora, the Query Editor will allow us to send SQL commands directly form the AWS Console. This is helpful for many dev and test scenarios.

As this is the first time using the Query Editor, we will need to connect to the database.

In the Connect to database block, select the Database cluster.

For the Database username, click on the dropdown and select Add new database credentials.

Now enter the databases username and password. For this lab, the default username is username and the default password is password. Leave the Enter the name of the database or schema block blank and click on Connect to database.

You might get a error similar to the following: “Connection failed, please double check your database username, password, and assure the database is available.” The serverless Aurora cluster might be scaling up. Give it a minute and try again.

Before the schema can be setup, a database needs to be created. From the Console Editor, copy and paste the below sql command. Ensure that you insert the database name to the command and you can the same name as the QLDB Ledger Name for simplicity but ensure that all lower case is used. Copy and paste the database name to your notes.

Make sure that a lower-case MySQL database name is used.

create database <Lower-case QLDB Ledger Name>

and, Click Run.

In the Statement Output, you should see that the Status shows Success.

You might get a Response similar to the following: “Communications link failure the last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.” The serverless Aurora cluster might be scaling up. Give it a minute and try again.

Setup the Tables

Now that the database has been created individual tables can be created.

In the Console Editor, copy and paste the below sql command. Ensure that you insert the database name.

CREATE TABLE <Lowercase QLDB Ledger Name>.driverslicense
  (
     docid         VARCHAR(22) NOT NULL,
     docv          INT NOT NULL,
     personid      VARCHAR(32),
     licensenumber VARCHAR(14),
     licensetype   ENUM('Probationary', 'Learner', 'Full'),
     validfromdate DATETIME,
     validtodate   DATETIME,
     blobkb        MEDIUMTEXT,
     updateuuid    VARCHAR(32),
     deletebool    BOOL,
     INDEX (docv),
     INDEX (personid),
     INDEX (licensenumber),
     PRIMARY KEY (docid)
  )

Take a look at the sql command that was just executed. The Primary Key is the QLDB DocumentID so both QLDB and the Aurora Serverless database will maintain uniqueness per row. If revisions would like to be stored in the databases, then a composite key of docid and docv would be used.

and, Click Run.

In the Statement Output, you should see that the Status shows Success.

Enter the below SQL statements to create the remaining tables. Ensure that you insert the database name.

Copy and paste into the Console Editor to create the person table.

CREATE TABLE <Lowercase QLDB Ledger Name>.person
  (
     docid      VARCHAR(22) NOT NULL,
     docv       INT NOT NULL,
     personid   VARCHAR(32),
     firstname  VARCHAR(100),
     lastname   VARCHAR(100),
     dob        DATETIME,
     govid      VARCHAR(14),
     govidtype  ENUM('Passport', 'SSN', 'Driver License'),
     address    VARCHAR(100),
     blobkb     MEDIUMTEXT,
     updateuuid VARCHAR(32),
     deletebool BOOL,
     INDEX (docv),
     INDEX (personid),
     INDEX (govid),
     PRIMARY KEY (docid)
  )

After conformation that the statement was successful, insert the below sql command to create the vehicle table.

CREATE TABLE <Lowercase QLDB Ledger Name>.vehicle
  (
     docid      VARCHAR(22) NOT NULL,
     docv       INT NOT NULL,
     vin        VARCHAR(17),
     type       VARCHAR(100),
     year       INT,
     make       VARCHAR(100),
     model      VARCHAR(100),
     color      VARCHAR(100),
     blobkb     MEDIUMTEXT,
     updateuuid VARCHAR(32),
     deletebool BOOL,
     INDEX (docv),
     INDEX (vin),
     PRIMARY KEY (docid)
  )

After success, insert the sql command to create the vehicleregistartion table.

CREATE TABLE <Lowercase QLDB Ledger Name>.vehicleregistration
  (
     docid                      VARCHAR(22) NOT NULL,
     docv                       INT NOT NULL,
     vin                        VARCHAR(17),
     licenseplatenumber         VARCHAR(10),
     state                      VARCHAR(2),
     city                       VARCHAR(100),
     validfromdate              DATETIME,
     validtodate                DATETIME,
     pendingpenaltyticketamount DOUBLE,
     owners                     MEDIUMTEXT,
     blobkb                     MEDIUMTEXT,
     updateuuid                 VARCHAR(32),
     deletebool                 BOOL,
     INDEX (docv, vin, licenseplatenumber),
     PRIMARY KEY (docid)
  )

Finally, run the below command to see the newly created tables. Insert the database name.

show TABLES FROM <Lowercase QLDB Ledger Name>

The Output should match per below:

Everything is now setup and you are ready to start ingesting data from QLDB streams into the Aurora Serverless database.

Again, QLDB is schema-less so adding additional columns to data being inserted into QLDB will work as expected. Only when the data is being written to MySQL will the insert fail. In a production application, consider validating the schema before writing to a downstream database.


Before we move over to the next step. We will need some details on the Aurora Serverless databases networking configuration.

From the service page left menu, click on Databases.

Now select the database used in this lab.

In the Connectivity & security block. The Endpoint, Subnets, and VPC security group will be needed as inputs in the next step. Either copy and paste the fields to your notes or simply keep the current browser tab open.

Head over to the next step.