It allows data to be stored in a secure way that guarantees it cannot be tampered with, as all changes to the database are easily traceable and these traces cannot be removed. This makes QLDB the perfect database when audit trails are required.<\/strong><\/p>\n\n\n\n The database, called a ledger in QLDB, stores documents that are the counterparts of table rows from relational databases. A full history is available for each of the documents so that any changes made to them can be easily traced. The data can be queried and manipulated using SQL-like language, making it easier to understand how to use QLDB if you come from the world of SQL databases.<\/p>\n\n\n\n In the article you will find information about the journal and its structure, querying and modifying data, examples, legal compliance and the pros and cons of this solution.<\/p>\n\n\n\n A key component of QLDB is a journal, which is structurally similar to a transaction log in a traditional SQL database, in that all data changes to the database are written to the journal first, in sequential order. However, the journal is append-only, meaning that data can only be inserted into the journal and cannot be deleted or modified. Therefore, the journal guarantees the transparency of the database<\/strong> and is one of the key features leading to the immutability of the database.<\/p>\n\n\n\n The journal is the source of truth in the database. All the data that users get from it comes from this component, and all data changes are recorded there before they are available to users. It also handles concurrency and guarantees the correct (sequential) order of data changes.<\/p>\n\n\n\n It is possible to retrieve data directly from the journal so that it can be exported if required. Another feature provided by QLDB is QLDB Stream<\/strong>, which can deliver the data to Kinesis Data Streams for processing in various ways, such as triggering Lambda functions. This results in near real-time processing of data changes introduced in QLDB.<\/p>\n\n\n\n QLDB’s journal is supported by cryptography. Its structure is very similar to that of blockchains. It consists of sequential blocks that are linked together as a chain. Each block consists of user data (actual content), metadata and queries executed in the transaction that committed the block.<\/p>\n\n\n\n The journal block is hashed using the SHA-256 cryptographic function, and its hash is used to calculate the hash of the next block. In this way, if one of the committed blocks is modified, its hash becomes incorrect and the hashes of the next blocks become incorrect as well. So, the verification of the whole chain will eventually fail. This is how the database provides the cryptographically verifiable transaction log.<\/p>\n\n\n\n The structure of the journal is illustrated in the following diagram.<\/p>\n\n\n\n Each block stores many hashes. The most important are:<\/p>\n\n\n\n Each time new data is written to the journal, it is materialised into a table, the name of which is specified by the user when inserting the data, just as in a normal SQL INSERT query. The materialised table provides views that allow easy retrieval of the current state of the data as well as the history of the data.<\/p>\n\n\n\n Data can be retrieved from the tables and views using a special SQL-like language called PartiQL<\/strong>, which is maintained by Amazon. This language can also be used to insert, update and delete data. Note that any queries that modify the data in any way do not directly modify the data in the tables<\/strong>. Instead, they are appended to the journal as new events, as described in the Journal section.<\/p>\n\n\n\n Each change to the same document results in the creation of a new document revision. Document revisions can be used to check the history of the document.<\/p>\n\n\n\n The data returned by PartiQL from QLDB is in a JSON-like format, which is actually an extension of JSON. The format is called Amazon Ion.<\/p>\n\n\n\n First, we can create tables using PartiQL like this:<\/p>\n\n\n There is no need to provide any kind of schema because QLDB does not enforce any schema, just like many other NoSQL databases.<\/p>\n\n\n\n We can also add some indexes to the created tables that will improve the performance of querying the data from them.Indexes are used whenever a table is queried to find a document in it using WHERE <\/em>operator with an equality <\/em>operator on an indexed field. If data is queried without using an index, a full table scan is performed, which significantly reduces performance and increases the cost of the operation<\/strong>. <\/p>\n\n\n\n There is one default index for each table \u2013 the document\u2019s ID, which is automatically generated for each document and is available in the document\u2019s metadata. The indexes can be added like this:<\/p>\n\n\n We can insert some data into the tables this way:<\/p>\n\n\n Basically, we can retrieve data just like in SQL databases \u2013 using SELECT <\/em>operator, e.g.:<\/p>\n\n\n This query returns details of the customer with the name John<\/em>:<\/p>\n\n\n We can also join data from two tables like this:<\/p>\n\n\n This query returns customer names along with their account numbers:<\/p>\n\n\n We can update data like in SQL databases \u2013 using UPDATE <\/em>operator, e.g.:<\/p>\n\n\n Returned data looks like this:<\/p>\n\n\n which is an array with IDs of the new document versions (updated ones).<\/p>\n\n\n\n We can query the committed view of the table to get more table details. The view returns documents from the system-defined table directly corresponding to the queried user table. Thus, the view consists of a list of documents from the user table in an extended version consisting of the following attributes:<\/p>\n\n\n\n Such queries are typically run to retrieve metadata of documents, in particular system-generated identifiers of the latest revisions of documents stored in a user table or the time at which they were stored (the transaction\u2019s time). Querying a committed view of a table is as simple as modifying the regular SELECT <\/em>query on the table by adding the _ql_committed_ <\/em>prefix to the table name, e.g.:<\/p>\n\n\n In general, the _ql_ <\/em>prefix is reserved for system objects in QLDB and committed views are an example of such objects.<\/p>\n\n\n\n The query returns a response that is an array of two elements, because there are two different documents in the BankAccount <\/em>table:<\/p>\n\n\n We can also retrieve the data history for a document using the history function, which returns a view containing a list of document revisions with all block details, similar to the use of the _qldb_committed_ <\/em>prefix usage, e.g.:<\/p>\n\n\n The returned data this time consists of three elements \u2013 two documents originally inserted into the BankAccount <\/em>table and an updated version of one of the documents:<\/p>\n\n\n Some data protection laws, such as the GDPR (General Data Protection Regulation) in the European Union, impose some restrictions and obligations on consumer rights, such as the right to be forgotten in the GDPR. The immutability of QLDB means that there is no way to comply with such a law, which effectively excludes this database from being used for such data<\/strong>. This significantly limits its usability.<\/p>\n\n\n\n To address this issue, an exception to the immutability rule has been added to QLDB \u2013 the data redaction<\/em> operation<\/strong>. This operation permanently deletes an inactive document from the QLDB, making it possible to permanently delete some user data. However, it only removes the actual content (data provided by the user), leaving the block itself undeleted. So the block’s hash, sequence and metadata cannot be deleted and this type of data is stored permanently. In this way, it is possible to combine as much immutability as possible with legal compliance.<\/p>\n\n\n\n Like any solution, QLDB has advantages and disadvantages. Among them are worth mentioning:<\/p>\n\n\n\n Benefits<\/strong><\/p>\n\n\n\n Disadvantages<\/strong><\/p>\n\n\n\n In general, it may be a good idea to use QLDB where audit trailing is required and the ability to verify data integrity is a must<\/strong>. For less important data, where the extra layer of protection is not a priority, it may be better to use other databases that do not have the overhead of the extra protection.<\/p>\n\n\n\n Examples of use cases where QLDB can be applied:<\/strong><\/p>\n\n\n\n This article is only an overview and contains the most important information about QLDB. However, there are many more details and information that can be found in the QLDB Developer Guide<\/a>.<\/p>\n\n\n\n Other knowledge sources:<\/p>\n\n\n\n ***<\/p>\n\n\n\n If you are interested in the subject of data warehouse, check out this article: “Implementation of the Modern Data Warehouse architecture in the Azure cloud<\/a>“. <\/p>\n\n\nJournal<\/strong><\/h2>\n\n\n\n
Structure of the journal<\/strong><\/h2>\n\n\n\n
![\"The](\"https:\/\/sii.pl\/blog\/wp-content\/uploads\/2023\/09\/Obraz1-2.png\")
<\/a>\n
Querying and modifying data<\/strong><\/h2>\n\n\n\n
Example<\/strong><\/h2>\n\n\n\n
Create tables<\/strong><\/h3>\n\n\n\n
\nCREATE TABLE BankAccount;\nCREATE TABLE Customer;\n<\/pre><\/div>\n\n\n
Create indexes<\/strong><\/h3>\n\n\n\n
\nCREATE INDEX ON BankAccount (AccountNumber);\nCREATE INDEX ON Customer (IdentityNumber);\n<\/pre><\/div>\n\n\n
Insert sample data<\/strong><\/h3>\n\n\n\n
\nINSERT INTO BankAccount\n<< {\n 'AccountNumber': '123456789',\n 'AccountType': 'Saving',\n 'Customer': { 'IdentityNumber': 'Customer1' }\n},\n{\n 'AccountNumber': '456789123',\n 'AccountType': 'Checking',\n 'Customer': { 'IdentityNumber': 'Customer2' }\n} >>;\n\nINSERT INTO Customer\n<< {\n 'IdentityNumber': 'Customer1',\n 'Name': 'John',\n 'Surname': 'Doe'\n},\n{\n 'IdentityNumber': 'Customer2',\n 'Name': 'Doris',\n 'Surname': 'Lin'\n} >>;\n<\/pre><\/div>\n\n\nQuery data<\/strong><\/h3>\n\n\n\n
\nSELECT * FROM Customer AS c\nWHERE c.Name = 'John';\n<\/pre><\/div>\n\n\n
\n[\n {\n "IdentityNumber": "Customer1",\n "Name": "John",\n "Surname": "Doe"\n }\n]\n<\/pre><\/div>\n\n\n\nSELECT c.Name, c.Surname, b.AccountNumber\nFROM Customer AS c, BankAccount as b\nWHERE c.IdentityNumber = b.Customer.IdentityNumber;\n<\/pre><\/div>\n\n\n
\n[\n {\n "Name": "John",\n "Surname": "Doe"\n "AccountNumber": "123456789"\n },\n {\n "Name": "Doris",\n "Surname": "Lin"\n "AccountNumber": "456789123"\n }\n]\n<\/pre><\/div>\n\n\nModify data<\/strong><\/h3>\n\n\n\n
\nUPDATE BankAccount AS b\nSET b.AccountType = 'SavingAccount'\nWHERE b.AccountType = 'Saving';\n<\/pre><\/div>\n\n\n
\n[\n {\n "documentId": "84MQvUwiL6I3QvjowvCW6x"\n }\n]\n<\/pre><\/div>\n\n\nView block\u2019s metadata \u2013 committed view<\/strong><\/h3>\n\n\n\n
\n
\nSELECT * FROM _ql_committed_BankAccount;\n<\/pre><\/div>\n\n\n
\n[\n {\n blockAddress: {\n strandId: "IXlQaOgH5XSBHHptx1Anyl",\n sequenceNo: 83\n },\n hash: {{LZEMR8w8dgk\/XIC\/U1VDR5C3b\/P2YBnwSvqlSOn4GVg=}},\n data: {\n AccountNumber: "123456789",\n Customer: {\n IdentityNumber: "Customer1"\n },\n AccountType: "SavingAccount"\n },\n metadata: {\n id: "84MQvUwiL6I3QvjowvCW6x",\n version: 1,\n txTime: 2023-08-29T23:05:23.544Z,\n txId: "42BHOyJI6wQCZ6orjQ0y4H"\n }\n },\n {\n blockAddress: {\n strandId: "IXlQaOgH5XSBHHptx1Anyl",\n sequenceNo: 31\n },\n hash: {{UG+YfHl3hwZofthfoPbWvG5c7k4agKjqtQr3rFYt0z8=}},\n data: {\n AccountNumber: "456789123",\n AccountType: "Checking",\n Customer: {\n IdentityNumber: "Customer2"\n }\n },\n metadata: {\n id: "BvtXPJzyhL0DNT262S6hVk",\n version: 0,\n txTime: 2023-08-29T22:47:34.673Z,\n txId: "72U8fHKd0YU4NI9l8Bg9ae"\n }\n }\n]\n<\/pre><\/div>\n\n\nView data history<\/strong><\/h3>\n\n\n\n
\nSELECT * FROM history(BankAccount)\n<\/pre><\/div>\n\n\n
\n[\n {\n blockAddress: {\n strandId: "IXlQaOgH5XSBHHptx1Anyl",\n sequenceNo: 31\n },\n hash: {{15G1wu8Pb3RWWOEsmPgEMP51qL89dC1q6PfxnV7q0+o=}},\n data: {\n AccountNumber: "123456789",\n AccountType: "Saving",\n Customer: {\n IdentityNumber: "Customer1"\n }\n },\n metadata: {\n id: "84MQvUwiL6I3QvjowvCW6x",\n version: 0,\n txTime: 2023-08-29T22:47:34.673Z,\n txId: "72U8fHKd0YU4NI9l8Bg9ae"\n }\n },\n {\n blockAddress: {\n strandId: "IXlQaOgH5XSBHHptx1Anyl",\n sequenceNo: 83\n },\n hash: {{LZEMR8w8dgk\/XIC\/U1VDR5C3b\/P2YBnwSvqlSOn4GVg=}},\n data: {\n AccountNumber: "123456789",\n Customer: {\n IdentityNumber: "Customer1"\n },\n AccountType: "SavingAccount"\n },\n metadata: {\n id: "84MQvUwiL6I3QvjowvCW6x",\n version: 1,\n txTime: 2023-08-29T23:05:23.544Z,\n txId: "42BHOyJI6wQCZ6orjQ0y4H"\n }\n },\n {\n blockAddress: {\n strandId: "IXlQaOgH5XSBHHptx1Anyl",\n sequenceNo: 31\n },\n hash: {{UG+YfHl3hwZofthfoPbWvG5c7k4agKjqtQr3rFYt0z8=}},\n data: {\n AccountNumber: "456789123",\n AccountType: "Checking",\n Customer: {\n IdentityNumber: "Customer2"\n }\n },\n metadata: {\n id: "BvtXPJzyhL0DNT262S6hVk",\n version: 0,\n txTime: 2023-08-29T22:47:34.673Z,\n txId: "72U8fHKd0YU4NI9l8Bg9ae"\n }\n }\n]\n<\/pre><\/div>\n\n\nLegal compliance<\/strong><\/h2>\n\n\n\n
Usage analysis<\/strong><\/h2>\n\n\n\n
\n
\n
\n
More information<\/strong><\/h2>\n\n\n\n
\n