Querying Revision History - Amazon Quantum Ledger Database (Amazon QLDB)

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'

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 timestamp literals that can be denoted with backticks (`...`). To learn more, see Querying Ion with PartiQL.

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 that must be enclosed in single quotation marks. To learn more, see Querying the History of Dropped Tables.

The start and end times specify the time range during which any revisions were active. They don't specify the transaction time range during which revisions were committed to the journal. The start and end time parameters have the following behavior:

  • The start-time and end-time are optional parameters that are both inclusive. 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.

To query the document's history, use the id that you previously saved in Updating and Deleting Documents. For example, the following query returns the revision history for document 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. This is a UUID that is represented in a Base62-encoded string.

  • 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.

To learn how to query the system catalog, proceed to Querying the System Catalog.