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'
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 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 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 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'

Undropping Tables

When you drop a table in QLDB, you are just inactivating it. You can still use the UNDROP statement to reactivate it.

First, find the table ID from information_schema.user_tables. For example, the following query returns the tableId of the VehicleRegistration table. The status should be INACTIVE.

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

Then, use this ID to reactivate the table. The following is an example that undrops table ID 5PLf9SXwndd63lPaSIa0O6. The table ID is a unique identifier that should be enclosed in double quotation marks.

UNDROP TABLE "5PLf9SXwndd63lPaSIa0O6"

The status of VehicleRegistration should now be ACTIVE.