Query Aurora

This lab demonstrates how to replicate QLDB documents into a relational MySQL database. If other databases would like to be used as the target database. Then, simply change the code logic to support a different purpose built database like RedShift or Neptune. The fundamental principles of leveraging the target databases features and characteristics will still apply like demonstrated in this lab. QLDB streaming to MySQL is a solution, it is not the only solution.

Using the RDS Query Console

Finally, we are ready to query QLDB documents from the Aurora Serverless databases. Head back over to the RDS service page.

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

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

There should be no need to sign in back into the database. If credentials are needed, follow the same steps outlined in the earlier sections.

First, let’s make sure that data is being entered into the database. Insert the below query into the Console Editor and click Run. Ensure that the database name is entered.

SELECT count(*) FROM <Lowercase QLDB Ledger Name>.person;

A result greater than 0 should be in the Output section.

If zero was returned. Go back and check the inputs for each cloudformation template. Pay special attention to the case as inputs are case sensitive. Or, wait for a minute and try again. The infrastructure might still be getting provisioned.

Second, every document was to be updated one time in QLDB. Let’s make sure that we have counts for both version 0 and version 1.

Insert the below query in the Console Editor.

SELECT
COUNT(*) AS TOTAL_ROWS,
COUNT(IF(docv=0,1,null)) AS DOCV_0,
COUNT(IF(docv=1,1,null)) AS DOCV_1
FROM <Lowercase QLDB Ledger Name>.person

This time, the result should be returned for DOCV_0 and DOC_V that are greater than 0. This is a good sign as Kinesis does not guarantee order and can have duplicates. With the correct logic in place, making sure that the target database is correctly synced with QLDB can be done. To demonstrate that both the QLDB and Aurora database are in sync there will be no records with revision version of 0 in both the QLDB user view and the Aurora database once the simulation test is complete.

Now that we know data from QLDB is streaming into the Aurora Serverless database. Let’s do a few queries that we wouldn’t do in an at scale, production QLDB database.

Insert the below query to see the top 5 registered zip codes from Florida. This a non-indexed field in our QLDB Ledger and also using a like clause. Additionally, group by, order by, desc, and limit are used.

SELECT DISTINCT
   substring_index(address, ',', - 1) AS ZIP,
   COUNT(*) AS ZIP_CNT
FROM
   <Lowercase QLDB Ledger Name>.person
WHERE
   address LIKE '%FL,%'
GROUP BY
   ZIP
ORDER BY
   ZIP_CNT DESC LIMIT 5;

Now, lets find the PersonId that has the highest unpaid ticket amount.

SELECT
   docid,
   vin,
   owners,
   pendingpenaltyticketamount
FROM
   <Lowercase QLDB Ledger Name>.vehicleregistration
WHERE
   pendingpenaltyticketamount =
   (
      SELECT
         max(pendingpenaltyticketamount)
      FROM
         <Lowercase QLDB Ledger Name>.vehicleregistration
   )
LIMIT 1;

Take a note of the output and copy the vin down in your clipboard or notes.

QLDB

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, select the Ledger created for this lab and click on Query ledger.

In the QLDB Query editor, insert the following PartiQL command. Make sure and paste in the vin from the last step. This will delete the document.

DELETE FROM VehicleRegistration WHERE VIN = '<Paste in vin>'

When successful, a documentId will show in the output. The document ID will be used in the next step.

Just to be sure, let’s see if we can see the document after searching for it.

SELECT * FROM _ql_committed_VehicleRegistration WHERE metadata.id = '<Paste in docid>'

0 rows should be returned.

Think of a scenario that you would like to know what documents have been deleted in QLDB. This question can easily be answered from our Aurora Serverless database.

Back to Aurora

Head back over to the Aurora database’s query editor.

Now let’s find all of the deleted documents by running the below MySQL command.

SELECT * FROM <Lowercase QLDB Ledger Name>.vehicleregistration WHERE deletebool = true;

The documentId in the Aurora output will match the documentId from the last step.

Depending on the shard count and overall throughput, the deleted record might take a few seconds to a few minutes to replicate into the Aurora database. With 50 Kinesis shards, the replication should be near-real time.

And there is the document Id and revision version for the deleted document. That document Id can now be used to search the history() in QLDB.

By this time, all data should be replicated over to our Aurora database. From our simulation test, 50,000 documents per table were sent to QLDB with half being revision version 0 and the other half being version 1. That means that each table should have 25,000 rows in our Aurora database. Let’s run the below query to make sure we have ALL messages processed with ZERO conflicts.

Copy the below sql commands into the online query editor.

SELECT count(*) FROM <Lowercase QLDB Ledger Name>.person WHERE docv > '0';
SELECT count(*) FROM <Lowercase QLDB Ledger Name>.vehicle WHERE docv > '0';
SELECT count(*) FROM <Lowercase QLDB Ledger Name>.driverslicense WHERE docv > '0';
SELECT count(*) FROM <Lowercase QLDB Ledger Name>.vehicleregistration WHERE docv > '0';

Take a look at the returned outputs for each query. Our Aurora database is an exact replica of the QLDB user view. In the case that a message did not get delivered to Aurora, the failed message will go to a dead letter queue. Head on over the SQS to see the Dead Letter Queues created and used for this lab. It is a best practice to send all non-processed messages to a Dead Letter Queue.

Great Work, you have effectively streamed high load messages from QLDB into an Aurora Serverless database by use of INSERT, UPDATE, and DELETE. Head on over to the bonus lab or finish off with the clean up section.