4

I have created Vehicle Table In the Ledger and added some vehicles in QLDB and I deleted the vehicle data.Now I am not able to fetch the metadata id because user table and committed table will have only non-deleted latest version of application data.So I am not able to fetch History of that deleted Data through metadata.Kindly help me with PartiQL query to fetch the History, if there is a way. Note: I don't have vehicle registration table which stores metadataId of vehicles.

History Query By MetadataIdHistory Query By VIN

Sanjeev Kumar
  • 135
  • 2
  • 2
  • 13
  • 1
    the QLDB `history` function returns all revisions that ever existed. From that, you should be able to find any document ever. History includes both the user data and the system provided metadata. If this is not enough information to help you, can you please update your question to include the query you're trying to run and then explain what data is missing? – Marc Nov 20 '19 at 18:18
  • For [history QUERY](https://docs.aws.amazon.com/qldb/latest/developerguide/getting-started-step-5.html) we need a metadata_id which is system generated data that we are not aware of.So if we want fetch the deleted document history we need metadata id how we can fetch that id once it is deleted qldb commited will also don't have that data – Sanjeev Kumar Nov 21 '19 at 14:07
  • You don't *need* an id. That's just one of the things you can filter on. – Marc Nov 22 '19 at 21:41
  • Data cannot be deleted from the history table. – Marc Nov 26 '19 at 21:52
  • No I mean that if we use others to filter on then we will not be able know that it is deleted or not from the history .So thats why we need the metadata id to fetch the entire history including the deleted state. – Sanjeev Kumar Nov 27 '19 at 11:28
  • I'm sorry, but I'm not able to understand your question. Can you please provide a worked example that shows where you're getting stuck? – Marc Nov 29 '19 at 21:28
  • I am trying to fetch the history of deleted vehicle through.if we filter by other means we won't be able to get the deleted one so we need metadata to fetch the entire evolution of data including it deleted version.Now we can fetch metadataId separately in history by filters and then we need to fetch the history through metadataId.I am asking is there a way to get the entire history in one query. – Sanjeev Kumar Dec 02 '19 at 09:15
  • Thanks for updating your question and clarifying. I understand what you mean now :). I don't think there is a way to do this in 1 query. You first need to get the id by filtering on VIN, then the history using that id. That's 2 select statements. In the future you might be able to do it with a subselect, but that is not currently supported: ` select * from history(Vehicle) as h where h.metadata.id = (select h2.metadata.id from history(Vehicle) as h2 where h2.data.vin = '11sPahckTf1Fc9KIbMnniq') ` – Marc Dec 06 '19 at 06:42

1 Answers1

1

The way you are doing it is correct. First, you filter on history by some known attribute (in this case, a user defined primary key such as 'VIN') and you retrieve the document id. After that, you can filter history using that document id.

The second query should return the same as the first but it will also contain the deletion information (the first query will not include it because the deletion removes the attribute).

Note that the document id is returned as part of the DELETE PartiQL statement.

Marc
  • 928
  • 5
  • 8