Optimized database system with updated materialized view
The server hosting the database scans a binlog for database change events. When a log consumer identifies a change event indicating that certain database records were modified, the consumer pulls only the identifiers of the modified records from the binlog. The log consumer then populates and pushes only the identifiers of the modified records into a non-transitory storage location that is accessible to a database streaming bus. The streaming bus publishes the identifiers for consumption by instances of materialization workers. The hosting server invokes parallel processor threads to execute the materialization workers. The materialization worker rewrites a query script for constructing a materialized view of certain database records, including the modified database records indicated by the identifiers. The materialization worker executes the query script to construct the materialized view, which contains only the modified database records used for generating the database updates to commit to the database.
1 . A processor-implemented method comprising:
obtaining, by a computer, a change event indicating one or more modified database records of a database corresponding to one or more identifiers in a database bus stream;
identifying, by the computer, one or more dependencies between one or more database tables of the database associated with the one or more modified database records corresponding to the one or more identifiers in the database bus stream;
determining, by the computer, at least one database table that depends on the one or more modified database records indicated by the change event according to the one or more dependencies identified in the one or more database tables; and
updating, by the computer, a materialized view of the database according to the one or more modified database records corresponding to the one or more identifiers in the database bus stream and the at least one database table.
2 . The method according to claim 1 , wherein the database receives a plurality of database records including the one or more modified database records replicated from a source database.
3 . The method according to claim 1 , wherein the database receives the one or more modified database records according one or more user inputs entered at a user device.
4 . The method according to claim 1 , wherein obtaining the change event includes identifying, by the computer, the one or more modified database records according to change log data associated with the change event.
5 . The method according to claim 1 , further comprising, for each particular modified database record, determining, by the computer, an identifier associated with the particular modified database record indicated by the change event.
6 . The method according to claim 1 , further comprising updating, by the computer, the database bus stream to include the one or more identifiers corresponding to the one or more modified database records.
7 . The method according to claim 1 , further comprising executing, by the computer, a materialization worker assigned to the one or more modified database records as indicated by the database bus stream,
wherein the materialization worker identifies the at least one database table and updates the materialized view.
8 . The method according to claim 7 , wherein the computer invokes a separate processor thread for each respective materialization worker.
9 . The method according to claim 1 , wherein updating the materialized view includes updating, by the computer, a query for constructing the materialized view based upon the change event.
10 . The method according to claim 1 , further comprising:
responsive to identifying a many-to-many relationship between data values of a set of database records of the at least one database table and the data values of the one or more modified database records:
querying, by the computer, for a unique identifier key associated with a database key of the set of database records of the at least one database table and the database key of the one or more modified database records; and
normalizing, by the computer, the many-to-many relationship to a one-to-one relationship for the data values of the database key of the set of database records or the data values of the database key of the one or more modified database records.
11 . A system comprising:
a database hosted by non-transitory machine-readable media configured to store a plurality of database records; and
a computer comprising a processor configured to:
obtain a change event indicating one or more modified database records of the database corresponding to one or more identifiers in a database bus stream;
identify one or more dependencies between one or more database tables of the database associated with the one or more modified database records corresponding to the one or more identifiers in the database bus stream;
determine at least one database table that depends on the one or more modified database records indicated by the change event according to the one or more dependencies identified in the one or more database tables; and
update a materialized view of the database according to the one or more modified database records corresponding to the one or more identifiers in the database bus stream and the at least one database table.
12 . The system according to claim 11 , wherein the database receives a plurality of database records including the one or more modified database records replicated from a source database.
13 . The system according to claim 11 , wherein the database receives the one or more modified database records according one or more user inputs entered at a user device.
14 . The system according to claim 11 , wherein when obtaining the change event, the computer is further configured to identify the one or more modified database records according to change log data associated with the change event.
15 . The system according to claim 11 , wherein the computer is further configured to, for each particular modified database record, determine an identifier associated with the particular modified database record indicated by the change event.
16 . The system according to claim 11 , wherein the computer is further configured to update the database bus stream to include the one or more identifiers corresponding to the one or more modified database records.
17 . The system according to claim 11 , wherein the computer is further configured to execute a materialization worker assigned to the one or more modified database records as indicated by the database bus stream, wherein the materialization worker identifies the at least one database table and updates the materialized view.
18 . The system according to claim 17 , wherein the computer invokes a separate processor thread for each respective materialization worker.
19 . The system according to claim 11 , wherein, when updating the materialized view, the computer is further configured to update a query for constructing the materialized view based upon the change event.
20 . The system according to claim 11 , wherein the computer is further configured to:
responsive to identifying a many-to-many relationship between data values of a set of database records of the at least one database table and the data values of the one or more modified database records:
query for a unique identifier key associated with a database key of the set of database records of the at least one database table and the database key of the one or more modified database records; and
normalize the many-to-many relationship to a one-to-one relationship for the data values of the database key of the set of database records or the data values of the database key of the one or more modified database records.
21 . A non-transitory machine-readable storage medium having computer-executable instructions stored thereon that, when executed by one or more processors, cause the one or more processors to perform operations comprising:
obtaining a change event indicating one or more modified database records of a database corresponding to a one or more identifiers in a database bus stream;
identifying one or more dependencies between one or more database tables of the database associated with the one or more modified database records corresponding to the one or more identifiers in the database bus stream;
determining at least one database table that depends on the one or more modified database records indicated by the change event according to the one or more dependencies identified in the one or more database tables; and
updating a materialized view of the database according to the one or more modified database records corresponding to the one or more identifiers in the database bus stream and the at least one database table.