Hybrid transactional and analytical processing architecture for optimization of real-time analytical querying

Patent metadata

Field	Value
Publication number	US-12277138-B2
Application number	US-202217810314-A
Country	US
Kind code	B2
Filing date	Jun 30, 2022
Priority date	Jun 30, 2022
Publication date	Apr 15, 2025
Grant date	Apr 15, 2025

Abstract

Methods for replicating transactional tables of a transactional database to an analytical database and maintaining updates to those transactional table representations are disclosed. Snapshots of the transactional tables are provided to the analytical database via a transport mechanism, such as a data storage service or a data streaming service, and stored at the analytical database. Then, checkpoints comprising portions of a change-data-capture log that has recorded transactional changes to the transactional tables of the transactional database are provided to the analytical database via the same or different transport mechanism and used to commit those transactional changes to the snapshot representations. The snapshot representations may be used to respond to incoming analytical queries in order to provide real-time querying results.

First claim

What is claimed is:

1. A system, comprising:
one or more computing devices, wherein the one or more computing devices are configured to:
implement a transactional database;
maintain, at the transactional database, a table comprising data items; and
write, at the transactional database, transactional changes made to the table to a change-data-capture log;

a plurality of compute nodes organized into a node cluster, wherein the plurality of compute nodes are configured to:
implement an analytical database; and
maintain, at the analytical database, a representation of at least one portion of the table, wherein to maintain comprises committing respective ones of the transactional changes of the change-data-capture log, received at the analytical database, to the representation, wherein different portions of the representation are maintained at different ones of the plurality of compute nodes;

at least one transport mechanism configured to:
receive respective snapshots of segments of the at least one portion of the table, wherein each snapshot comprises a segment of the at least one portion of the table;
make the respective snapshots accessible to the analytical database;
receive checkpoints relative to the respective snapshots, wherein the checkpoints comprise respective portions of the transactional changes of the change-data-capture log; and
make the checkpoints accessible to the analytical database by applying respective portions of the checkpoints at different ones of the plurality of compute nodes according to the respective different portions of the representation maintained at the different ones of the plurality of compute nodes; and

wherein the plurality of compute nodes implementing the analytical database are further configured to, responsive to receiving an incoming analytical query pertaining to at least one data item in the representation, provide the results of the incoming analytical query.

2. The system of claim 1 , wherein:
the transactional changes of a given checkpoint comprise at least two delete events; and
the maintain the representation of the at least one portion of the table further comprises:
committing, at the analytical database, the at least two delete events into a shadow table; and
responsive to the shadow table having a given threshold of committed delete events, committing the committed delete events of the shadow table to the representation.

3. The system of claim 1 , wherein the snapshots are respectively distributed across respective ones of the plurality of compute nodes of the node cluster.

4. The system of claim 1 , wherein:
the transactional changes are labeled with respective primary keys corresponding to respective rows that the transactional changes occur at in the transactional database; and
the committing the respective ones of the transactional changes to the representation is based, at least in part, on use of the respective primary keys.

5. The system of claim 1 , wherein:
the transactional database and the analytical database are part of a service provider network;
a data storage service of the service provider network is used to implement a respective one of the at least one transport mechanism; and
the respective snapshots or the checkpoints are stored in at least one data store of the data storage service, wherein the at least one data store is accessible to the transactional database and the analytical database.

6. The system of claim 1 , wherein:
the transactional database and the analytical database are part of a service provider network; and
a data streaming service of the provider network is used to implement a respective one of the at least one transport mechanism; and
the respective snapshots or the checkpoints are communicated from the transactional database to the analytical database via the data streaming service.

7. A method, comprising:
maintaining, at an analytical database implemented by a plurality of compute nodes organized into a node cluster, a representation of at least one portion of a table comprising data items, wherein:
the table is stored at a transactional database, implemented by one or more computing devices;
different portions of the representation are stored at different ones of the plurality of compute nodes; and
the maintaining comprises:
writing, at the transactional database, transactional changes made to the table to a change-data-capture log;
making, via at least one transport mechanism, snapshots of segments of the at least one portion of the table accessible to the analytical database;
making, via a respective one of the at least one transport mechanism, checkpoints, relative to the respective snapshots, accessible to the analytical database, wherein the checkpoints comprise respective portions of the transactional changes of the change-data-capture log;
applying, at the analytical database, the respective snapshots and the checkpoints across the plurality of compute nodes of a node cluster, wherein said applying comprises applying respective portions of the checkpoints at different ones of the plurality of compute nodes according to the respective different portions of the representation stored at the different ones of the plurality of compute nodes; and
committing the transactional changes of the respective checkpoints to the representation; and

responsive to receiving an incoming query pertaining to at least one data item in the representation, providing the results of the incoming query using the analytical database.

8. The method of claim 7 , wherein the applying the transactional changes comprises:
committing at least two delete events of the transactional changes of a given checkpoint of the respective checkpoints into a shadow table; and
responsive to the shadow table having a given threshold of committed delete events, committing the committed delete events in the shadow table to the representation.

9. The method of claim 7 , wherein the method further comprises:
storing, in relation to committing the transactional changes of the respective checkpoints to the representation, an indication of a transaction applied at the analytical database having a latest transaction identifier; and
polling the transactional database to determine whether an additional checkpoint that comprises one or more transactional changes that have been applied at the transactional database have a later transaction identifier than the latest transaction identifier committed at the analytical database.

10. The method of claim 9 , wherein, responsive to the polling the transactional database,
making, via the respective one of the at least one transport mechanism, the additional checkpoint accessible to the analytical database; and
committing the one or more transactional changes that have the later transaction identifier than the latest transaction identifier committed at the analytical database to the representation.

11. The method of claim 7 , wherein the method further comprises:
determining, via a negotiation between the plurality of compute nodes of the node cluster and the one or more computing devices, one or more data definition language commands that are incompatible with the checkpoints.

12. The method of claim 11 , wherein the method further comprises:
responsive to determining, at the analytical database, that a given transactional change of a given checkpoint comprises a respective one of the one or more incompatible data definition language commands, providing an error message.

13. The method of claim 7 , wherein:
the transactional database and the analytical database are part of a service provider network; and

Assignees

• Amazon Tech Inc

Inventors

• Pandis Ippokratis
• Soundararajan Gokul
• Paliwal Gopal
• Rajgaria Punit
• Basu Sanuj
• Green Todd Jeffrey
• Saxena Gaurav
• Skipin Vadim
• Wust Johannes
• Satyanarayana Hemanth
• Abrams Matthew Perry
• Brahmadesam Murali

Classifications

• G06F16/23

  Updating · CPC title

• G06F16/275

  Synchronous replication · CPC title

• G06F16/273

  Asynchronous replication or reconciliation · CPC title

• G06F16/278

  Data partitioning, e.g. horizontal or vertical partitioning · CPC title

• G06F16/25

  Integrating or interfacing systems involving database management systems · CPC title