Optimization of application of transactional information for a hybrid transactional and analytical processing architecture

Patent metadata

Field	Value
Publication number	US-12007983-B2
Application number	US-202217810318-A
Country	US
Kind code	B2
Filing date	Jun 30, 2022
Priority date	Jun 30, 2022
Publication date	Jun 11, 2024
Grant date	Jun 11, 2024

Abstract

Methods for implementing insert, update, and delete events of a change-data-capture log in transactional order to a representation of a transactional table are disclosed. Insert events may be applied and committed to the representation, while delete events may be buffered and committed to a shadow table. Such buffering may allow the delete events to be applied and committed as a batch to the given representation as a later background operation, allowing for an optimized application of the change-data-capture log to the representation. Once the delete events are committed to the representation, they are removed from the shadow table. Update events may be similarly treated in which the insert event component of the update event is applied and committed to the representation while the delete event component is committed to the shadow table, before being later committed to the representation and removed from the shadow table.

First claim

What is claimed is:

1. A system, comprising:
one or more compute nodes organized into a node cluster, wherein the one or more compute nodes are configured to:
implement an analytical database; and
maintain, at the analytical database, a representation of at least a portion of a table of a separate transactional database implemented via one or more computing devices, wherein to maintain the representation of the at least a portion of the table, the one or more compute nodes of the node cluster are further configured to:
receive respective snapshots of segments of the at least a portion of the table of the separate transactional database and receive checkpoints relative to the respective snapshots, wherein:
the checkpoints comprise transactional changes that have been applied at the separate transactional database;
the transactional changes of a given checkpoint comprise two or more delete events and one or more insert events; and
the transactional changes are labeled with respective primary keys corresponding to respective rows that the transactional changes occur at in the separate transactional database; and

implement the transactional changes of the given checkpoint to its corresponding snapshot, wherein to implement comprises:
commit the one or more insert events to the corresponding snapshot;
commit the two or more delete events to a shadow table;
responsive to the shadow table having a given threshold of committed delete events of the two or more delete events, commit the committed delete events of the shadow table to the corresponding snapshot; and
responsive to the commit the committed delete events in the shadow table to the corresponding snapshot, remove the committed delete events from the shadow table; and

wherein, responsive to receiving an incoming analytical query, the one or more compute nodes provide the results of the incoming analytical query based, at least in part, on the committed delete events in the shadow table.

2. The system of claim 1 , wherein a given delete event of the two or more delete events is to delete a respective row of the representation.

3. The system of claim 1 , wherein:
the transactional changes of the given checkpoint further comprise an update event, comprising another delete event and another insert event, to modify at least one data item in a given row of the representation based, at least in part, on deleting the given row and inserting an updated version of the given row comprising the modified at least one data item; and
the implement the transactional changes of the given checkpoint to its corresponding the snapshot further comprises:
commit the other delete event to the shadow table; and
commit the other insert event to the corresponding snapshot.

4. The system of claim 1 , further comprising:
the transactional database, wherein the one or more computing devices that implement the transactional database are further configured to:
maintain the table; and
write the transactional changes to a change-data-capture log, wherein the change-data-capture log comprises the transactional changes that have been applied to the separate transactional database.

5. The system of claim 1 , wherein the respective primary keys are respective row identifiers of the representation.

6. The system of claim 1 , wherein the respective primary keys correspond to subsets of the data items within the respective rows that the transactional changes occur at in the separate transactional database.

7. The system of claim 1 , wherein the respective primary keys correspond to concatenations of all data items within the respective rows that the transactional changes occur at in the separate transactional database.

8. The system of claim 1 , wherein:
the analytical database is part of a service provider network; and
the checkpoints are made accessible to the analytical database via a transport mechanism of the service provider network.

9. A method, comprising:
maintaining, at an analytical database implemented by one or more compute nodes organized into a node cluster, a representation of at least a portion of a table comprising data items, wherein:
the table is stored in a separate transactional database, implemented via one or more computing devices; and
the maintaining comprises:
receiving respective snapshots of segments of the at least a portion of the table of the separate transactional database;
receiving checkpoints relative to the respective snapshots, wherein:
the checkpoints comprise transactional changes that have been applied at the separate transactional database;
the transactional changes of a given checkpoint comprise two or more delete events; and
the transactional changes are labeled with respective primary keys corresponding to respective rows that the transactional changes occur at in the separate transactional database; and

implementing the transactional changes of the given checkpoint to its corresponding snapshot, wherein the implementing comprises:
committing the two or more delete events to a shadow table; and
responsive to the shadow table having a given threshold of committed delete events of the two or more delete events, committing the committed delete events in the shadow table to the corresponding snapshot; and

providing, responsive to receiving an incoming analytical query, the results of the incoming analytical query based, at least in part, on the committed delete events in the shadow table.

10. The method of claim 9 , wherein:
the transactional changes of the given checkpoint further comprise an insert event; and
the method further comprises committing the insert event to the corresponding snapshot without committing the insert event to the shadow table.

11. The method of claim 9 , wherein:
the transactional changes of the given checkpoint further comprise an update event, comprising another delete event and an insert event, to modify at least one data item in a given row of the corresponding snapshot based, at least in part, on deleting the given row and inserting an updated version of the given row comprising the modified at least one data item; and
the implementing the transactional changes of the given checkpoint to the corresponding snapshot further comprises:
committing the other delete event to the shadow table; and
committing the insert event to the corresponding snapshot without committing the insert event to the shadow table.

12. The method of claim 9 , wherein the committing the committed delete events in the shadow table to the corresponding snapshot comprises:
determining respective rows of the corresponding snapshot that the committed delete events in the shadow table occur at using the respective primary keys;
applying the committed delete events in the shadow table to the corresponding snapshot; and
committing, as a batch, the applied delete events to the corresponding snapshot.

13. The method of claim 9 , wherein the method further comprises:
responsive to the committing the committed delete events in the shadow table to the corresponding snapshot, removing the committed delete events from the shadow table.

14. The method of claim 9 , wherein the providing, responsive to receiving the incoming analytical query, the results of the incoming analytical query based, at least in part, on the committed delete events in the shadow table comprises:
parsing through the corresponding snapshot;
parsing through the shadow table; and
providing the results of the incoming analytical query, wherein the results do not include rows corresponding to the committed delete events in the corresponding snapshot or

Assignees

• Amazon Tech Inc

Inventors

• Pandis Ippokratis
• Soundararajan Gokul
• Paliwal Gopal
• Skipin Vadim
• Basu Sanuj

Classifications

• G06F16/254

  Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses · CPC title

• G06F16/2358

  Change logging, detection, and notification (replication G06F16/27) · CPC title

• G06F16/2379 Primary

  Updates performed during online database operations; commit processing · CPC title

• G06F16/2365 Primary

  Ensuring data consistency and integrity · CPC title