Amazon Quantum Ledger Database (Amazon QLDB)
Developer Guide

The AWS Documentation website is getting a new look!
Try it now and let us know what you think. Switch to the new look >>

You can return to the original look by selecting English in the language selector above.

Querying Revision History

Amazon QLDB stores the complete history of every document in a table. You can see all three revisions of the vehicle registration document you previously inserted, updated, and deleted in Updating and Deleting Documents by querying the built-in history function.

History Function

The history function in QLDB is a PartiQL extension that returns revisions from the system-defined view of your table. So, it includes both your data and the associated metadata in the same schema as the committed view.

The history function has the following syntax.

SELECT * FROM history( table [, `start-time`, `end-time`] ) AS h WHERE h.metadata.id = 'id'
  • The table parameter can be either a table name or a table ID. A table name is a PartiQL identifier that you can denote with double quotation marks or no quotation marks. A table ID is a string literal value that must be enclosed in single quotation marks. To learn more, see Querying the History of Dropped Tables.

  • The start-time and end-time parameters are both optional. They must be in ISO 8601 date and time format and in Coordinated Universal Time (UTC).

  • The start-time must be less than or equal to end-time and can be any arbitrary date in the past.

  • The end-time must be less than or equal to the current UTC date and time.

  • If you specify a start-time but not an end-time, your query defaults the end-time to the current date and time. If you specify neither, your query returns the entire history.

Note

As a best practice, qualify a history query with a document id. This helps to avoid inefficient queries.

The start-time and end-time are Ion literal values that can be denoted with backticks (`...`). To learn more, see Querying Ion with PartiQL.

Use the id that you previously saved to query the document's history. For example, the following query returns the revision history for id = ADR2Ll1fGsU4Jr4EqTdnQF between 2000T and 2019-06-05T23:59:59Z. Be sure to replace the id, start time, and end time with your own values as appropriate.

SELECT * FROM history(VehicleRegistration, `2000T`, `2019-06-05T23:59:59Z`) AS h WHERE h.metadata.id = 'ADR2Ll1fGsU4Jr4EqTdnQF' --replace with your id

Your query results should look similar to the following.

{ blockAddress:{ strandId:"JdxjkR9bSYB5jMHWcI464T", sequenceNo:14 }, hash:{{B2wYwrHKOWsmIBmxUgPRrTx9lv36tMlod2xVvWNiTbo=}}, data: { VIN: "1HVBBAANXWH544237", LicensePlateNumber: "LS477D", State: "WA", City: "Tacoma", PendingPenaltyTicketAmount: 42.20, ValidFromDate: 2011-10-26T, ValidToDate: 2023-09-25T, Owners: { PrimaryOwner: { PersonId: "KmA3XPKKFqYCP2zhR3d0Ho" }, SecondaryOwners: [] } }, metadata:{ id:"ADR2Ll1fGsU4Jr4EqTdnQF", version:0, txTime:2019-06-05T20:53:321d-3Z, txId:"HgXAkLjAtV0HQ4lNYdzX60" } }, { blockAddress:{ strandId:"JdxjkR9bSYB5jMHWcI464T", sequenceNo:17 }, hash:{{LGSFZ4iEYWZeMwmAqcxxNyT4wbCtuMOmFCj8pEd6Mp0=}}, data: { VIN: "1HVBBAANXWH544237", LicensePlateNumber: "LS477D", State: "WA", PendingPenaltyTicketAmount: 42.20, ValidFromDate: 2011-10-26T, ValidToDate: 2023-09-25T, Owners: { PrimaryOwner: { PersonId: "KmA3XPKKFqYCP2zhR3d0Ho" }, SecondaryOwners: [] }, City: "Bellevue" }, metadata:{ id:"ADR2Ll1fGsU4Jr4EqTdnQF", version:1, txTime:2019-06-05T21:01:442d-3Z, txId:"9cArhIQV5xf5Tf5vtsPwPq" } }, { blockAddress:{ strandId:"JdxjkR9bSYB5jMHWcI464T", sequenceNo:19 }, hash:{{7bm5DUwpqJFGrmZpb7h9wAxtvggYLPcXq+LAobi9fDg=}}, metadata:{ id:"ADR2Ll1fGsU4Jr4EqTdnQF", version:2, txTime:2019-06-05T21:03:76d-3Z, txId:"9GslbtDtpVHAgYghR5FXbZ" } }

The output includes metadata attributes that provide details on when each item was modified, and by which transaction. From this data, you can see the following:

  • The document is uniquely identified by its system-assigned id: ADR2Ll1fGsU4Jr4EqTdnQF.

  • An INSERT statement creates the initial revision of a document (version 0).

  • Each subsequent update creates a new revision with the same document id and an incremented version number.

  • The txId field indicates the transaction that committed each revision, and txTime shows when each was committed.

  • A DELETE statement creates a new, but final revision of a document. This final revision has metadata only.

Querying the History of Dropped Tables

In addition to a table name, you can also query the QLDB history function with a table ID as the first input parameter. This enables you to query the history of dropped tables. After a table is dropped, you can no longer query its history with the table name.

First, find the table ID by querying the Table Metadata. For example, the following query returns the tableId of the VehicleRegistration table.

SELECT tableId FROM information_schema.user_tables WHERE name = 'VehicleRegistration'

Then, you can use this ID to run the same history query from the previous section. The following is an example that queries the history of document ID ADR2Ll1fGsU4Jr4EqTdnQF from table ID 5PLf9SXwndd63lPaSIa0O6. The table ID is a string literal value that must be enclosed in single quotation marks.

--replace both the table and document IDs with your values SELECT * FROM history('5PLf9SXwndd63lPaSIa0O6', `2000T`, `2019-06-05T23:59:59Z`) AS h WHERE h.metadata.id = 'ADR2Ll1fGsU4Jr4EqTdnQF'