EDIT: QLDB has been discontinued by Amazon : InfoQ – AWS Discontinues Amazon Quantum Ledger Database (QLDB)
I came across Amazon QLDB, which stands for Quantum Ledger DataBase. QLDB is a ‘serverless’ service on AWS. A Ledger DB keeps an immutable journal of all activities that happen with the data. The data is viewable to the user both as a history table where we can see all of the records, as well as the current view of the data. So if we were recording financial transactions, for example, we would have a journal of all of the transactions, and we could also view a table with the current balance for each user, as well as the ability to see the history.
We can use PartiQL (A SQL-like language) to query our data. QLDB is a NoSQL data store, with the data stored in a document format Amazon refers to as Ion.
We can use the AWS CLI to work with our data. There’s also an API available to use. For a basic setup, I’ll the web GUI.
Setup:
We can go to AWS to set up a QLDB instance. If you don’t already have an AWS account, you can set it up here, but you’ll need to submit credit card info for the account. You can go to Services, Databases, then Amazon QLDB to get started.
First we need to create a Ledger, which is comparable to a database in the relational DB world. From ‘Create Ledger’ we’ll supply a name (I used ‘test’), and give AWS a minute or two to create the ledger.
Once the ledger is available, click on the name, and we’ll see a Tables section. We can click ‘Create Table’. We’ll name the table then create it. At first I used the name ‘transaction’, which caused an error. Apparently this is a reserved keyword, so I used ‘test_table’ instead (Although I could still use ‘transaction’ as long as I delimit the table name each time I refer to it).
Adding Data:
Once the table has been created, we can click on the table name. Once we’re at the table detail page, we can click ‘Query’ at the top to get to the PartiQL query console. There’s also an entry for ‘PartiQL Editor’ in the left pane.
I’ll insert a record for a deposit to create an account. We’ll insert a JSON document into our table. In this case, there’s one record, but we can submit multiple documents in one insert.
UTCNOW() is a PartiQL function that will return the current datetime in UTC.
INSERT INTO test_table
<< {
'Customer': 'Jane Doe',
'AccountNumber': '12345',
'AccountType': 'Checking',
'Amount': 100.00,
'ModifiedAt': UTCNOW()
} >>
Once we have a document created, we can create an index. An index can only be created on one attribute, although we can create multiple indexes. The syntax is what we would expect in SQL:
CREATE INDEX ON test_table (AccountNumber)
And selecting all records in the table is standard SQL:
SELECT * FROM test_table
Modifying Data:
Changing data is a standard update:
UPDATE test_table SET Amount = Amount - 40.00, ModifiedAt = UTCNOW() WHERE AccountNumber = '12345'
View History:
When a document is inserted, there’s a generated ID for each record. We don’t see this ID when querying the table, but we can refer to a system table to get the ID. Each table we create has a committed view. So for the table test_table, we query _ql_committed_test_table.
SELECT metadata.id, data.AccountNumber FROM _ql_committed_test_table WHERE data.AccountNumber = '12345'
Once we have this ID, we query a history function to see our table’s history:
SELECT * FROM history(test_table) WHERE metadata.id = 'X'
We can see the data as it was before and after our update. There’s a metadata.version values that starts at 0 and can be used to order the records.
I didn’t see a way to view or query the journal records in the GUI, but at the Export menu item is functionality to export the journal records in order to analyze them.
Next Steps:
I didn’t find a way to create a primary key or a unique key for the tables. I tried the standard UNIQUE clause in creating an index, but that didn’t work. I assume that functionality isn’t available.
A next step would be looking into the API to understand how that works.
There’s also functionality to verify records, to ensure that someone hasn’t altered records and that the record history is correct.
Links:
Overview of Amazon QLDB
SQL Rob 