QLDB Ledger

A new QLDB Ledger is needed and will be used throughout this lab. In this section, the QLDB Ledger will be created along with tables and indexes.

With QLDB, correctly setting up a Ledger with tables and indexes is important to QLDB performance and optimization. Knowing your complete read-write access patterns before Ledger creation is a best practice and should be done whenever possible.


Create QLDB ledger

From the AWS console, start typing QLDB and click on the service when it appears.

On the left of the screen, click on Ledgers under the Amazon QLDB menu.

Now click, Create Ledger

In the Ledger information block, insert an account unique QLDB Ledger Name, like StreamingLab, and then click Create ledger.

Copy or write the QLDB ledger name down. The name will be used throughout the rest of the lab.


Create a QLDB Table

In this lab, a modified DMV sample data set will be used but will follow the same table layout.

On the left Amazon QLDB menu, select the Query editor.

Now choose, the newly created Ledger using the dropdown.

In the query editor, insert the below statements to create the tables in QLDB.

CREATE TABLE Person

Click Run and a tableId will be displayed in the Output, as shown below.

The tableId is conformation that the query completed successfully and now continue to insert the below PartiQL statements to create the three remaining tables.

CREATE TABLE DriversLicense
CREATE TABLE Vehicle
CREATE TABLE VehicleRegistration

Create QLDB Table Indexes

Once the tables are created, we need to add the indexes. Run the below statements in the query editor.

Indexes are crucial to QLDB performance. After this lab has been completed, consider re-running this lab again but remove the indexes for one table. How do you think the table with no indexes when perform against INSERTS, UPDATES, and READS?

CREATE INDEX ON Person (PersonId)
CREATE INDEX ON Person (GovId)
CREATE INDEX ON DriversLicense (PersonId)
CREATE INDEX ON DriversLicense (LicenseNumber)
CREATE INDEX ON Vehicle (VIN)
CREATE INDEX ON VehicleRegistration (VIN)
CREATE INDEX ON VehicleRegistration (LicensePlateNumber)

Lets double check that the tables and indexes have been created as expected.

From the Ledger block, click on Ledger details.

Now click on Tables and Indexes.

Tables and indexes will appear here and should match the below image. If anything was missed, reinsert the missing statements in the query editor before moving on to the next step.

Once complete, head on over to the next section.