CN115470290A - Increment synchronization method and device based on materialized view logs and computer equipment - Google Patents

Abstract

The invention relates to the technical field of data synchronization, and discloses an incremental synchronization method, device and computer equipment based on materialized view logs. Wherein, the method includes: obtaining the data source table in the source database, creating a materialized view log corresponding to the data source table, the materialized view log includes a plurality of attribute columns, and the attribute columns are used to determine the data source table involved in the incremental operation Fields and update values; when an incremental operation is generated for the data source table, the field corresponding to the incremental operation is determined based on the metadata of the data source table; when the field corresponding to the incremental operation is in the attribute column, the materialized view log Extract the incremental data corresponding to the incremental operation; synchronize the incremental data to the target database. Through the implementation of the invention, the number of table return queries is reduced, the synchronization efficiency of incremental data is improved, and the performance of database incremental synchronization is improved.

Description

Incremental synchronization method, device and computer equipment based on materialized view log

technical field

The present invention relates to the technical field of data synchronization, in particular to an incremental synchronization method, device and computer equipment based on materialized view logs.

Background technique

Incremental synchronization of the database needs to extract the incremental data from the source database, analyze and convert it, and write it into a database script file and operating language suitable for the target database, so as to store the incremental data in the target database.

The Oracle database is one of the commonly used database types, and it is also the database type that must be supported by the database synchronization component. At present, for the incremental data synchronization of Oracle database, when data synchronization is performed based on the materialized view log technology, it is necessary to read the materialized view log, and then query the data source table according to the primary key in the materialized view log. The complete incremental data is obtained from the source table. However, each log data in the materialized view log needs to be queried back to the table. Due to the influence of network transmission and database performance, the extraction efficiency of incremental data is low, which in turn affects the synchronization efficiency of incremental data.

Contents of the invention

In view of this, the embodiments of the present invention provide a materialized view log-based incremental synchronization method, device, and computer equipment to solve the problem of low incremental data extraction efficiency resulting in low incremental data synchronization efficiency.

According to the first aspect, an embodiment of the present invention provides an incremental synchronization method based on a materialized view log, including: obtaining a data source table in a source database, creating a materialized view log corresponding to the data source table, and the materialized view The log includes a plurality of attribute columns, and the attribute columns are used to determine the fields and update values involved in the incremental operation of the data source table; when an incremental operation for the data source table is generated, based on the data The metadata of the source table determines the field corresponding to the incremental operation; when the field corresponding to the incremental operation is in the attribute column, extract the incremental data corresponding to the incremental operation from the materialized view log ; Synchronize the incremental data to the target database.

In the incremental synchronization method based on the materialized view log provided by the embodiment of the present invention, by specifying multiple attribute columns in the materialized view log corresponding to the data source table, when generating an incremental operation, combine multiple attribute columns and data source table The metadata can determine the field corresponding to the incremental operation. If the field is in the attribute column, the incremental data is extracted from the materialized view log, thus eliminating the need to return to the table for query, reducing the number of table return queries, improving the synchronization efficiency of incremental data, and improving the performance of database incremental synchronization .

With reference to the first aspect, in the first implementation manner of the first aspect, the plurality of attribute columns include a field change vector column, an increment operation type column, and a data column, and when generating an increment for the data source table When performing an incremental operation, determining the field corresponding to the incremental operation includes: when generating an incremental operation for the data source table, updating the incremental operation in the incremental operation type column; based on the The incremental operation type column, the field change vector column and the data column determine the field corresponding to the incremental operation.

In the incremental synchronization method based on the materialized view log provided by the embodiment of the present invention, the incremental operation is updated in the incremental operation type column, so as to determine the field generating the data update according to the incremental operation type, the field change vector column, and the data column. That is, the field that generates the incremental operation is determined from the materialized view log. In this way, the updated data can be confirmed according to the incremental operation, and there is no need to return to the data source table for data query, which reduces the query time of incremental data back to the table, and improves the efficiency of data synchronization.

With reference to the first implementation of the first aspect, in the second implementation of the first aspect, based on the increment operation type column, the field change vector column, and the data column, determine the The field of operation, including: based on the primary key information of the materialized view, determine the incremental operation corresponding to the same primary key information; when there are multiple incremental operations, merge multiple incremental operations based on preset rules , to obtain a target incremental operation; based on the field change vector column, the target incremental operation, and the data column, determine the field of the target incremental operation in the materialized view log.

The incremental synchronization method based on the materialized view log provided by the embodiment of the present invention facilitates confirmation of the final state of the incremental data by merging multiple incremental operations, and further reduces the number of queries back to the table and the scale of the incremental data, facilitating Follow up.

With reference to the second implementation manner of the first aspect, in the third implementation manner of the first aspect, the type of the incremental operation includes an insert operation, an update operation, and a delete operation, and the multiple incremental operations based on preset rules Combining operations to obtain a target incremental operation, including: when multiple incremental operations are insert operations and update operations in sequence, merging multiple incremental operations based on the preset rules to determine the target incremental operation The operation is an insert operation; when multiple incremental operations are insert operations and delete operations in sequence, merge the incremental operations based on the preset rules, and determine that the target incremental operation is a delete operation; when multiple incremental operations When the incremental operations are all update operations, merge the incremental operations based on the preset rules, and determine that the target incremental operation is an update operation; when multiple incremental operations are update operations and delete operations in sequence merging the incremental operations based on the preset rules, determining that the target incremental operation is a delete operation; when multiple incremental operations are delete operations and insert operations in sequence, merging the incremental operations based on the preset rules the incremental operation, and determine that the target incremental operation is an insert operation.

The incremental synchronization method based on the materialized view log provided by the embodiment of the present invention merges multiple incremental operations according to the generation order of multiple incremental operations through preset rules, so as to ensure that multiple incremental operations for the same primary key can be merged, maximizing Reduced the number of queries back to the table.

With reference to the first aspect, in the fourth implementation manner of the first aspect, after determining the field corresponding to the incremental operation based on the metadata of the data source table, it further includes: judging the field corresponding to the incremental operation Whether the field is a large data field; when the field corresponding to the incremental operation is not the large data field, determine whether the field corresponding to the incremental operation is in the attribute column.

In the incremental synchronization method based on materialized view logs provided by the embodiment of the present invention, for incremental operations on non-big data fields, the number of query times for non-big data fields is reduced by determining the fields corresponding to the incremental operations. The data source table of the data field, or the scene where the large data field reads more and writes less, improves the extraction performance of incremental data.

With reference to the first aspect, in the fifth implementation manner of the first aspect, the acquiring the data source table in the source database and creating the materialized view log corresponding to the data source table includes: acquiring metadata of the data source table , the metadata includes a data primary key, a data name, and a data type; based on the data primary key, data name, and data type of the metadata, a materialized view log corresponding to the metadata is generated.

The incremental synchronization method based on the materialized view log provided by the embodiment of the present invention constructs the corresponding materialized view log through the data primary key, data name and data type corresponding to the metadata, so as to accurately locate the data that generates the incremental operation.

With reference to the first aspect, in the sixth implementation manner of the first aspect, after synchronizing the incremental data to the target database, further includes: performing data cleaning on the materialized view log.

In the incremental synchronization method based on the materialized view log provided by the embodiment of the present invention, after the synchronization of the incremental data is completed, the log data that has been synchronized is cleared from the materialized view log to release the data space and save the space occupied by the data.

According to the second aspect, an embodiment of the present invention provides an incremental synchronization device based on a materialized view log, including: an acquisition module, configured to acquire a data source table in a source database, and create a materialized view log corresponding to the data source table , the materialized view log includes an attribute column, and the attribute column is used for updating the metadata of the data source table; the first determination module is configured to determine when an incremental operation for the data source table is generated The field corresponding to the incremental operation; an extraction module, configured to extract the incremental data corresponding to the incremental operation from the materialized view log when the field corresponding to the incremental operation is in the attribute column; A synchronization module, configured to synchronize the incremental data to the target database.

According to a third aspect, an embodiment of the present invention provides a computer device, including: a memory and a processor, the memory and the processor are connected to each other in communication, the memory stores computer instructions, and the processor By executing the computer instructions, the materialized view log-based incremental synchronization method described in the first aspect or any implementation manner of the first aspect is executed.

According to the fourth aspect, an embodiment of the present invention provides a computer-readable storage medium, the computer-readable storage medium stores computer instructions, and the computer instructions are used to make the computer execute the first aspect or any implementation of the first aspect. The incremental synchronization method based on the materialized view log described in the method.

It should be noted that, for the corresponding beneficial effects of the materialized view log-based incremental synchronization device, computer equipment, and computer-readable storage medium provided by the embodiment of the present invention, please refer to the description of the corresponding content in the materialized view log-based incremental synchronization method. , which will not be repeated here.

Description of drawings

In order to more clearly illustrate the specific implementation of the present invention or the technical solutions in the prior art, the following will briefly introduce the accompanying drawings that need to be used in the specific implementation or description of the prior art. Obviously, the accompanying drawings in the following description The drawings show some implementations of the present invention, and those skilled in the art can obtain other drawings based on these drawings without any creative work.

Fig. 1 shows the architecture diagram of the data synchronization system in the embodiment of the present invention;

Fig. 2 is the flowchart of the incremental synchronization method based on the materialized view log of the embodiment of the present invention;

Fig. 3 is another flowchart of the incremental synchronization method based on the materialized view log according to the embodiment of the present invention;

Fig. 4 is another flowchart of the incremental synchronization method based on the materialized view log according to the embodiment of the present invention;

FIG. 5 is a structural block diagram of an incremental synchronization device based on a materialized view log according to an embodiment of the present invention;

FIG. 6 is a schematic diagram of a hardware structure of a computer device provided by an embodiment of the present invention.

detailed description

In order to make the purpose, technical solutions and advantages of the embodiments of the present invention clearer, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments It is a part of embodiments of the present invention, but not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without making creative efforts belong to the protection scope of the present invention.

At present, for the synchronization of database incremental data, it is necessary to extract incremental data from the source database, analyze and convert it, and write it into a database script file and operating language suitable for the target database, so as to store the incremental data in the target database. However, the Oracle database is one of the commonly used database types. The synchronization of the Oracle database depends on the materialized view log. According to the primary key in the materialized view log, the table is returned to the data source table to obtain complete incremental data from the data source table. . However, each log data in the materialized view log needs to be queried back to the table. Due to the influence of network transmission and database performance, the extraction efficiency of incremental data is low, which in turn affects the synchronization efficiency of incremental data.

Based on this, this technical solution extracts incremental data from the materialized view log according to the operational characteristics between the materialized view log and the data source table, thereby eliminating the need for back-to-table queries, reducing the number of back-to-table queries, and improving incremental data. The synchronization efficiency improves the performance of database incremental synchronization.

An embodiment of the present invention provides a data synchronization system. As shown in FIG. 1 , the data synchronization system includes: a source database, a target database, a management node cluster, a synchronization node cluster, and a coordinator cluster. During data synchronization, the incremental data is extracted from the source database, and the incremental data is analyzed and converted through the management node cluster, synchronization node cluster, and coordinator cluster, and written into an operating language suitable for the target database database. The volume data is stored in the target database.

Wherein, the source database may be a distributed relational database, a distributed file system, or an unstructured database, and the type of the database is not specifically limited here.

Wherein, the target database may be a distributed relational database, a distributed file system, or an unstructured database, and the type of the database is not specifically limited here.

Among them, the management node cluster is used for data verification configuration, and the data verification configuration information is pushed to the synchronization node cluster. At the same time, it receives information such as data synchronization status and progress fed back by the synchronization node cluster.

Wherein, the synchronization node cluster is used to execute a specific data synchronization process.

Among them, the coordinator cluster is used to coordinate the data synchronization from the source data to the target database.

According to an embodiment of the present invention, an embodiment of incremental synchronization based on a materialized view log is provided. It should be noted that the steps shown in the flow chart of the accompanying drawings can be implemented in a computer system such as a set of computer-executable instructions and, although a logical order is shown in the flowcharts, in some cases the steps shown or described may be performed in an order different from that shown or described herein.

In this embodiment, an incremental synchronization method based on materialized view logs is provided, which can be used for data synchronization between the source database and the target database in the above-mentioned data synchronization system. In this implementation, the source database is a relational database Oracle, and the target database For any relational database, non-relational database or message queue, etc. Fig. 2 is the flow chart of the incremental synchronization method based on the materialized view log according to the embodiment of the present invention, as shown in Fig. 2, the process includes the following steps:

S11. Obtain a data source table in the source database, and create a materialized view log corresponding to the data source table. Wherein, the materialized view log includes a plurality of attribute columns, and the attribute columns are used to determine the fields and update values involved in the incremental operation of the data source table.

The data source table is a data table used to record source data in the source database, and the materialized view log is a log table used to record update operations (ie, add, delete, and modify) of the data source table. The source database usually includes multiple data source tables, and different data source tables correspond to different materialized view logs, that is, each data source table corresponds to a materialized view log and occupies a certain amount of storage space.

Specifically, after the data source table is obtained, a corresponding materialized view log is created. When creating the materialized view log table corresponding to the data source table, the rowid for each log record is automatically generated. At the same time, in the process of creating a materialized view log, you can specify the attribute column of the materialized view log, and use the attribute column to represent the update of the source data in the data source table. The attribute column can include filter columns, sequence, including new values, dmltype , oldnew, changevector, etc.

It should be noted that the filter columns are used to record the column values in the data source table, which can be any combination of non-big data (LOB) fields in the data source table; dmltype indicates the type of incremental operation, which can be an insert operation INSERT(I ), update operation UPDATE(U), delete operation DELETE(D); including new values indicates that the updated data is included; oldnew indicates whether the row log is a new value or an old value; changevector indicates the column where the data update occurs, and raw(255 ) is saved, and the updated column is marked as 1 in the corresponding binary bit; sequence indicates the order of the incremental operation. Of course, other attributes or features may also be specified according to actual needs, which are not specifically limited here.

S12. When generating an incremental operation on the data source table, determine a field corresponding to the incremental operation.

The data synchronization system can monitor in real time whether incremental operations have occurred on the data source table. When an incremental operation on the data source table is generated, the materialized view log will be refreshed in conjunction with the incremental operation on the data source table. By querying the metadata of the data source table, the field information (including name, type, order in the data source table, etc.) of the data source table can be obtained. The changevector column in the materialized view log can mark the changed fields in the data source table. Here, by comparing the field information in the metadata with the changevector column, the field corresponding to the incremental operation can be determined. That is, by detecting the changevector column in the materialized view log table, the field corresponding to the incremental operation in the materialized view log can be deduced.

S13. When the field corresponding to the incremental operation is in the attribute column, extract the incremental data corresponding to the incremental operation from the materialized view log.

The filter columns in the materialized view log record the column values in the data source table, and compare the field that generates the incremental operation with the fields contained in the filter columns in the attribute column to determine whether the field corresponding to the incremental operation is in the filter columns . If the field corresponding to the incremental operation is in the filter columns, it means that the incremental data corresponding to the incremental operation can be assembled and obtained directly from the materialized view log. At this time, there is no need to return to the data source table to query the incremental data, and the The incremental data corresponding to the incremental operation can be extracted from the view log.

S14, synchronizing the incremental data to the target database.

After the database synchronization system extracts the incremental data corresponding to the incremental operation from the materialized view log, it writes the incremental data into an operating language suitable for the target database, so as to synchronize the incremental data to the target database without going back to the table for query again The data in the target database can be incrementally updated.

In the incremental synchronization method based on the materialized view log provided in this embodiment, by specifying multiple attribute columns in the materialized view log corresponding to the data source table, when generating an incremental operation, combine multiple attribute columns and metadata of the data source table The data can determine the field corresponding to the incremental operation. If the field is in the attribute column, the incremental data is extracted from the materialized view log, thus eliminating the need to return to the table for query, reducing the number of table return queries, improving the synchronization efficiency of incremental data, and improving the performance of database incremental synchronization .

In this embodiment, an incremental synchronization method based on materialized view logs is provided, which can be used for data synchronization between the source database and the target database in the above data synchronization system. In this implementation, the source database is a relational database Oracle, and the target database is any relational database, non-relational database, or message queue. Fig. 3 is a flowchart of an incremental synchronization method based on a materialized view log according to an embodiment of the present invention. As shown in Fig. 3 , the process includes the following steps:

S21. Obtain a data source table in the source database, and create a materialized view log corresponding to the data source table. Wherein, the materialized view log includes a plurality of attribute columns, and the attribute columns are used to determine the fields and update values involved in the incremental operation of the data source table.

For details, refer to relevant descriptions corresponding to the foregoing embodiments, and details are not repeated here.

S22. When generating an incremental operation on the data source table, determine a field corresponding to the incremental operation.

Specifically, the multiple attribute columns include a field change vector column, an incremental operation type column, and a data column, and the field change vector column is used to indicate the fields involved in the current incremental operation of the data source table. The incremental operation type column is used to record the type of the current incremental operation, including insert, modify, and delete operations. Record the name of the non-big data field in the data source table and the latest field value through the data column.

Correspondingly, the above step S22 may include:

S221. When generating an incremental operation for the data source table, update the incremental operation in the incremental operation type column.

When an incremental operation is detected on the data source table, the materialized view log will be updated following the update of the source data in the data source table, and determine the incremental operation type corresponding to the current source data update, such as inserting new data, updating a row The value of the data, etc., and record the incremental operation in the incremental operation type column of the materialized view log.

S222. Based on the incremental operation type column, the field change vector column and the data column, determine the field corresponding to the incremental operation.

According to the field change vector column of the materialized view log, the location where the data update occurs in the data source table can be known. According to the incremental operation type column, you can know the data update method, such as inserting new data, deleting data, and updating data values. By querying the incremental operation type column, field change vector column, and data column of non-big data fields in the materialized view log, you can determine the field where the incremental operation currently occurs.

A specific example is used here to illustrate, as shown in Table 1, a materialized view log corresponding to Table 1 is created. Then perform the following incremental operations on Table 1:

1) Insert data with id=1;

2) Modify the price field with id=2.

Table 2 shows the materialized view logs after the above incremental operations. Among them, dmltype indicates the type of incremental operation, that is, the incremental operation type column; oldnew indicates whether the row log is a new value (N) or an old value (O); changvector is used to indicate the data column where the field change occurs, that is, the field Alter the vector columns.

Table 1 Data source table example

id(number) name(varchar) price(number) des(clob) 1 the book 10.0 text 2 pen 9 text

Table 2 Materialized view log example

rowid id name price dmltype oldnew changevector row1 1 the book 10.0 I N FEFF row2 2 pen 8 u o 0800 row2 2 pen 9 u N 0800

It should be noted that the field change vector column changevector uses binary, and the 0 and 1 of each bit indicate whether the corresponding column has changed. For example, if there is a change in the first example, it is 00000010, if the second column is changed, it is 00000100, and if the third column is changed It is 00001000, and the little endian mode is used to convert it into hexadecimal, which are 0200, 0400 and 0800 respectively. The changevector corresponding to the insert operation is FEFF. The changevector corresponding to the delete operation is 0000.

As an optional implementation manner, the above step S222 may include:

(1) Based on the primary key information of the materialized view log, determine the incremental operation corresponding to the same primary key information.

In the relationship between the data source table and the materialized view log, the primary key information (primary key) is used to indicate that the materialized view log corresponds to the data record of the data source table, that is, the primary key information is used to uniquely identify each data record.

According to the primary key information of the materialized view log, it can uniquely correspond to the data record in the data source table. Different primary key information corresponds to different data records, and the incremental operations for the data corresponding to each primary key information can be uniquely determined through the primary key information of the materialized view log.

(2) When there are multiple incremental operations, the multiple incremental operations are combined based on preset rules to obtain the target incremental operation.

The preset rule is a merge rule preset according to the generation sequence of multiple incremental operations. When multiple incremental operations are generated for data with the same primary key information, in order to facilitate the determination of the final incremental operation, each incremental operation can be combined according to the preset rules to obtain the target increment after the merger of multiple incremental operations operation, and update the target incremental operation to the incremental operation type column dmltype.

Specifically, the type of incremental operation includes an insert operation, an update operation, and a delete operation. Taking two incremental operations as an example, the above step (2) may include:

(21) When multiple incremental operations are insert operations and update operations in sequence, multiple incremental operations are merged based on preset rules, and the target incremental operation is determined to be an insert operation.

(22) When multiple incremental operations are insertion operations and deletion operations in sequence, the incremental operations are combined based on preset rules, and the target incremental operation is determined to be a deletion operation.

(23) When multiple incremental operations are update operations, merge the incremental operations based on preset rules, and determine the target incremental operation as the update operation.

(24) When multiple incremental operations are update operations and delete operations in turn, merge the incremental operations based on preset rules, and determine the target incremental operation as the delete operation.

(25) When multiple incremental operations are deletion operations and insertion operations in sequence, the incremental operations are combined based on preset rules, and the target incremental operation is determined to be an insertion operation.

The merging of the above incremental operations can be expressed as:

1) INSERT+UPDATE=INSERT;

2) INSERT+DELETE=DELETE;

3) UPDATE+UPDATE=UPDATE;

4) UPDATE+DELETE=DELETE;

5) DELETE+INSERT=INSERT.

Specifically, when creating a materialized view log, specify the attribute sequence that records the sequence of incremental operations. For the merging of more than two incremental operations, pairwise merging can be performed sequentially to obtain the target incremental operation. Take 3 examples for illustration. When the three incremental operations are delete operation, insert operation and update operation in turn, the delete operation is first merged with the insert operation to obtain the insert operation; then, the insert operation is merged with the update operation to obtain the insert operation, that is, the target incremental operation is insert operate.

(3) Based on the field change vector column, the target incremental operation and the data column, determine the field of the target incremental operation in the materialized view log.

According to the field change vector column, the location of the source data where data update occurs in the data source table can be known, and then the final update value of the source data can be known according to the target incremental operation. The target incremental operation is recorded in the incremental operation type column, so the field where the current incremental operation occurs can be determined by querying the incremental operation type column, field change vector column, and non-big data field data column of the materialized view log.

According to the merged result of the incremental operation, it is judged whether the query needs to be returned to the table. Specifically, there are three types of operations after the incremental operation is merged:

1) After merging, it is an insert operation. If the attribute column filter columns of the materialized view log contains all the fields of the data source table, there is no need to return to the table, otherwise it is necessary to return to the table for query.

2) After merging, there is an update operation. The fields that have been updated multiple times are merged in turn according to each update operation. If the updated fields are included in the attribute column filter columns, there is no need to return to the table; otherwise, it is necessary to return to the table for query.

3) After the merge is a delete operation, it can be deleted according to the primary key information without returning to the table.

Here is a specific example for illustration. For the data source table shown in Table 1, after the materialized view log corresponding to Table 1 is created, multiple incremental operations are performed on the metadata in Table 1 as shown in Table 3. .

Table 3 Example of multiple incremental operations

In a batch of materialized view log data, the log data with the same primary key information are merged incrementally to obtain the materialized view log as shown in Figure 4.

Table 4 Example of materialized view logs after incremental operations are merged

rowid id name price dmltype oldnew changevector row2 2 pen 9 u o 0C row2 2 pencil 10 u N 0C row1 1 the book 10.0 D. o 00

S23. When the field corresponding to the incremental operation is in the attribute column, extract the incremental data corresponding to the incremental operation from the materialized view log.

For details, refer to relevant descriptions corresponding to the foregoing embodiments, and details are not repeated here.

S24, synchronizing the incremental data to the target database.

For details, refer to relevant descriptions corresponding to the foregoing embodiments, and details are not repeated here.

In the incremental synchronization method based on the materialized view log provided in this embodiment, the incremental operation is updated in the incremental operation type column, so as to determine the field that generates the data update according to the incremental operation type, the field change vector column, and the data column, that is, From the materialized view log, determine the field that generated the incremental operation. In this way, the updated data can be confirmed according to the incremental operation, and there is no need to return to the data source table for data query, which reduces the query time of incremental data back to the table, and improves the efficiency of data synchronization. By merging multiple incremental operations, it is easy to confirm the final state of the incremental data, minimize the number of query back to the table, reduce the scale of incremental data, and facilitate subsequent operations.

In this embodiment, an incremental synchronization method based on materialized view logs is provided, which can be used for data synchronization between the source database and the target database in the above data synchronization system. In this implementation, the source database is a relational database Oracle, and the target database is any relational database, non-relational database, or message queue. Fig. 4 is a flowchart of an incremental synchronization method based on a materialized view log according to an embodiment of the present invention. As shown in Fig. 4, the process includes the following steps:

S31. Obtain a data source table in the source database, and create a materialized view log corresponding to the data source table. Wherein, the materialized view log includes a plurality of attribute columns, and the attribute columns are used to determine the fields and update values involved in the incremental operation of the data source table.

As an optional implementation manner, the above steps may include:

S311. Obtain metadata of a data source table, where the metadata includes a data primary key, a data name, and a data type.

The data synchronization system reads the data source table to be synchronized in the source database, and reads the metadata in the data source table, including the data primary key, data name and data type of the data source table, and can also include the data location, of course, it can also include Other information is not specifically limited here.

Among them, the data primary key is used to identify the uniqueness of the data, such as the data identification ID shown in the above table 1; the data name represents the name of each column of data, such as the name shown in the above table 1; the data type represents the type of data, such as the lob type , text type, etc., such as des(clob) shown in Table 1 above; the data position indicates the index position of the data table in the source database.

S312. Based on the data primary key, data name and data type of the metadata, generate a materialized view log corresponding to the metadata.

According to the data primary key, data name and data type based on the metadata, the data of the data source table described by the metadata and the field information of the data source table are determined. Then, based on the data in the data source table corresponding to the metadata, call the corresponding syntax to create a materialized view log, and specify multiple attribute columns of the materialized view log, including dmltype, oldnew, changevector, sequence, and filter columns.

It should be noted that, for filter columns, it may contain multiple fields, such as data identifier id, data name name, and other data information.

S32: When generating an incremental operation on the data source table, determine a field corresponding to the incremental operation.

For details, refer to relevant descriptions corresponding to the foregoing embodiments, and details are not repeated here.

S33. Determine whether the field corresponding to the incremental operation is a large data field.

The large data field indicates that the data corresponding to the incremental operation is lob type data. Materialized view logs are updated for data in non-lob fields. When data in lob fields is updated, table query still needs to be performed.

When an incremental operation is detected in the data source table, it is judged whether the field in which the incremental operation occurs is a large data field, and when the field corresponding to the incremental operation is not a large data field, step S34 is executed; otherwise, the query is performed back to the table.

S34. Determine whether the field corresponding to the increment operation is in the attribute column.

When the field corresponding to the incremental operation is not a big data field, compare the field corresponding to the incremental operation with the fields contained in the attribute column to determine whether the field corresponding to the incremental operation is in the filter columns of the attribute column. When the field corresponding to the incremental operation is in the filter columns of the attribute column, then perform the subsequent step S35, otherwise perform a query back to the table.

Specifically, the data synchronization system can sequentially analyze each row of data in the materialized view log, sequentially determine whether the data generated by the incremental operation needs to be returned to the table for query, and count the incremental data corresponding to the incremental operation. After the data summary is completed, query back to the table for the data that needs to be returned to the table for query, and directly extract from the materialized view log for the data that does not need to be returned to the table for query.

S35. When the field corresponding to the incremental operation is in the attribute column, extract the incremental data corresponding to the incremental operation from the materialized view log.

For details, refer to relevant descriptions corresponding to the foregoing embodiments, and details are not repeated here.

S36, synchronizing the incremental data to the target database.

For details, refer to relevant descriptions corresponding to the foregoing embodiments, and details are not repeated here.

S37. Perform data cleaning on the materialized view log.

After the target database completes the storage of incremental data, clear the log data that has completed data synchronization from the materialized view log, and continue to wait for the log data generated by the next incremental operation. When there is newly added log data in the materialized view log, return to step S32 to execute the incremental data synchronization process.

The incremental synchronization method based on the materialized view log provided by this embodiment constructs the corresponding materialized view log through the data primary key, data name and data type corresponding to the metadata, so as to accurately locate the data that generates the incremental operation. For incremental operations on non-big data fields, reduce the number of table return queries for non-big data fields by determining the fields corresponding to the incremental operations. For data source tables that do not contain large data fields, or read more for large data fields Write less scenes and improve the performance of incremental data extraction. After the synchronization of the incremental data is completed, the log data that has been synchronized is cleared from the materialized view log to release the data space and save the space occupied by the data.

In this embodiment, an incremental synchronization device based on materialized view logs is also provided, which is used to implement the above-mentioned embodiments and preferred implementation modes, and those that have already been explained will not be repeated. As used below, the term "module" may be a combination of software and/or hardware that realizes a predetermined function. Although the devices described in the following embodiments are preferably implemented in software, implementations in hardware, or a combination of software and hardware are also possible and contemplated.

This embodiment provides an incremental synchronization device based on materialized view logs, as shown in Figure 4, including:

The obtaining module 41 is configured to obtain a data source table in the source database, and create a materialized view log corresponding to the data source table. Wherein, the materialized view log includes a plurality of attribute columns, and the attribute columns are used to determine the fields and update values involved in the incremental operation of the data source table.

The first determining module 42 is configured to determine a field corresponding to the incremental operation when an incremental operation is generated on the data source table.

The extraction module 43 is configured to extract the incremental data corresponding to the incremental operation from the materialized view log when the field corresponding to the incremental operation is in the attribute column.

Synchronization module 44, configured to synchronize the incremental data to the target database.

As an optional implementation manner, the multiple attribute columns include a field change vector column, an incremental operation type column, and a data column, and the above-mentioned first determination module 42 may include:

The update sub-module is used to update the incremental operation in the incremental operation type column when the incremental operation for the data source table is generated.

The first determining submodule is used to determine the field corresponding to the incremental operation based on the incremental operation type column, the field change vector column and the data column.

As an optional implementation manner, the above-mentioned second determining submodule may include:

The acquisition subunit is used to determine multiple incremental operations corresponding to the same primary key information based on the primary key information of the materialized view log.

The merging subunit is used for merging multiple incremental operations based on preset rules to obtain a target incremental operation when there are multiple incremental operations.

Determine the subunit, used to determine the field in the materialized view log of the target incremental operation based on the field change vector column, the target incremental operation, and the data column.

Specifically, the type of incremental operation includes insert operation, update operation and delete operation, and the above-mentioned merging subunit is used for:

When multiple incremental operations are insert operations and update operations in sequence, the multiple incremental operations are combined based on preset rules, and the target incremental operation is determined to be an insert operation.

When multiple incremental operations are insertion operations and deletion operations in sequence, the incremental operations are combined based on preset rules, and the target incremental operation is determined to be a deletion operation.

When multiple incremental operations are update operations, the incremental operations are combined based on preset rules, and the target incremental operation is determined to be an update operation.

When multiple incremental operations are an update operation and a delete operation in sequence, the incremental operations are combined based on preset rules, and the target incremental operation is determined to be a delete operation.

When multiple incremental operations are deletion operations and insertion operations in sequence, the incremental operations are merged based on preset rules, and the target incremental operation is determined to be an insertion operation.

As an optional implementation manner, the above acquisition module 41 may include:

The first obtaining sub-module is used to obtain metadata of the data source table, and the metadata includes data primary key, data name and data type.

The generation sub-module is used to, based on the data primary key, data name and data type of the metadata, generate a materialized view log corresponding to the metadata.

As an optional implementation manner, the above-mentioned incremental synchronization device based on materialized view logs may also include:

The first judging module is used to judge whether the field corresponding to the incremental operation is a large data field.

The second judging module is used for judging whether the field corresponding to the incremental operation is in the attribute column when the field corresponding to the incremental operation is not a large data field.

As an optional implementation manner, the above-mentioned incremental synchronization device based on materialized view logs may also include:

The cleaning module is used for data cleaning of materialized view logs.

The materialized view log-based incremental synchronization device in this embodiment is presented in the form of a functional unit, where a unit refers to an ASIC circuit, a processor and a memory that execute one or more software or fixed programs, and/or other A device that can provide the above functions.

The further functional descriptions of the above modules are the same as those in the corresponding embodiments above, and will not be repeated here.

In the incremental synchronization device based on the materialized view log provided by the embodiment of the present invention, by specifying the attribute column in the materialized view log corresponding to the data source table, when an incremental operation is generated, the field corresponding to the incremental operation is determined. If the field is in the attribute column, the incremental data is extracted from the materialized view log, thus eliminating the need to return to the table for query, reducing the number of table return queries, improving the synchronization efficiency of incremental data, and improving the performance of database incremental synchronization .

An embodiment of the present invention also provides a computer device, which is provided with a source database or a target database, and has the above-mentioned incremental synchronization device based on the materialized view log shown in FIG. 5 .

Please refer to FIG. 6. FIG. 6 is a schematic structural diagram of a computer device provided in an optional embodiment of the present invention. As shown in FIG. 6, the computer device may include: at least one processor 501, such as a central processing unit (Central Processing Unit, CPU), at least one communication interface 503, memory 504, and at least one communication bus 502. Wherein, the communication bus 502 is used to realize connection and communication between these components. Wherein, the communication interface 503 may include a display screen (Display) and a keyboard (Keyboard), and the optional communication interface 503 may also include a standard wired interface and a wireless interface. The memory 504 may be a high-speed volatile random access memory (Random Access Memory, RAM), or a non-volatile memory (non-volatile memory), such as at least one disk memory. Optionally, the memory 504 may also be at least one storage device located away from the aforementioned processor 501 . The processor 501 may be combined with the device described in FIG. 5 , the memory 504 stores an application program, and the processor 501 invokes the program code stored in the memory 504 to execute any of the above method steps.

Wherein, the communication bus 502 may be a peripheral component interconnect standard (peripheral component interconnect, PCI) bus or an extended industry standard architecture (extended industry standard architecture, EISA) bus or the like. The communication bus 502 can be divided into an address bus, a data bus, a control bus, and the like. For ease of representation, only one thick line is used in FIG. 6 , but it does not mean that there is only one bus or one type of bus.

Wherein, the memory 504 may include a volatile memory (volatile memory), such as a random-access memory (random-access memory, RAM); the memory may also include a non-volatile memory (non-volatile memory), such as a flash memory (flash memory), a hard disk (hard disk drive, HDD) or a solid-state drive (solid-state drive, SSD); the storage 504 may also include a combination of the above types of storage.

Wherein, the processor 501 may be a central processing unit (central processing unit, CPU), a network processor (network processor, NP) or a combination of CPU and NP.

Wherein, the processor 501 may further include a hardware chip. The aforementioned hardware chip may be an application-specific integrated circuit (application-specific integrated circuit, ASIC), a programmable logic device (programmable logic device, PLD) or a combination thereof. The above-mentioned PLD may be a complex programmable logic device (complex programmable logic device, CPLD), a field-programmable logic gate array (field-programmable gate array, FPGA), a general array logic (generic array logic, GAL) or any combination thereof.

Optionally, the memory 504 is also used to store program instructions. The processor 501 may call a program instruction to implement the incremental synchronization method based on the materialized view log as shown in the embodiment of FIG. 2 to FIG. 4 of the present application.

An embodiment of the present invention also provides a non-transitory computer storage medium, the computer storage medium stores computer-executable instructions, and the computer-executable instructions can perform incremental synchronization based on materialized view logs in any of the above method embodiments method. Wherein, the storage medium can be a magnetic disk, an optical disk, a read-only memory (Read-OnlyMemory, ROM), a random access memory (Random Access Memory, RAM), a flash memory (FlashMemory), a hard disk (Hard Disk Drive) , abbreviation: HDD) or a solid-state hard drive (Solid-State Drive, SSD), etc.; the storage medium may also include a combination of the above-mentioned types of memory.

Although the embodiments of the present invention have been described in conjunction with the accompanying drawings, those skilled in the art can make various modifications and variations without departing from the spirit and scope of the present invention. within the bounds of the requirements.

Claims (10)

1. An increment synchronization method based on materialized view logs is characterized by comprising the following steps:

acquiring a data source table in a source database, and creating a materialized view log corresponding to the data source table, wherein the materialized view log comprises a plurality of attribute columns, and the attribute columns are used for determining fields and update values related to increment operation of the data source table;

when the increment operation aiming at the data source table is generated, determining a field corresponding to the increment operation;

when the field corresponding to the increment operation is in the attribute column, extracting increment data corresponding to the increment operation from the materialized view log;

synchronizing the incremental data to a target database.

2. The method of claim 1, wherein the plurality of attribute columns includes a field change vector column, an increment operation type column, and a data column, and wherein determining a field corresponding to an increment operation when the increment operation is generated for the data source table comprises:

updating the delta operation in the delta operation type column when the delta operation for the data source table is generated;

determining a field corresponding to the incremental operation based on the incremental operation type column, the field change vector column, and the data column.

3. The method of claim 2, wherein determining the field corresponding to the incremental operation based on the column of the type of incremental operation, the column of the field change vector, and the column of data comprises:

determining incremental operation corresponding to the same primary key information based on the primary key information of the materialized view log;

when a plurality of increment operations exist, merging the increment operations based on a preset rule to obtain a target increment operation;

determining a field of the target delta operation in the materialized view log based on the field change vector column, the target delta operation, and the data column.

4. The method of claim 3, wherein the types of the incremental operations include an insert operation, an update operation, and a delete operation, and the merging of the plurality of incremental operations based on the preset rule to obtain a target incremental operation comprises:

when the plurality of increment operations are insertion operations and updating operations in sequence, combining the plurality of increment operations based on the preset rule, and determining that the target increment operation is an insertion operation;

when the plurality of incremental operations are insertion operations and deletion operations in sequence, merging the incremental operations based on the preset rule, and determining that the target incremental operation is a deletion operation;

when the plurality of incremental operations are all updating operations, merging the incremental operations based on the preset rule, and determining that the target incremental operation is the updating operation;

when the plurality of increment operations are updating operations and deleting operations in sequence, merging the increment operations based on the preset rule, and determining that the target increment operation is the deleting operation;

when the plurality of increment operations are deletion operations and insertion operations in sequence, merging the increment operations based on the preset rule, and determining that the target increment operation is the insertion operation.

5. The method of claim 1, after determining the field corresponding to the delta operation based on the metadata of the data source table, further comprising:

judging whether the field corresponding to the increment operation is a big data field;

and when the field corresponding to the increment operation is not the big data field, judging whether the field corresponding to the increment operation is in the attribute column.

6. The method of claim 1, wherein the obtaining a data source table in a source database and creating a materialized view log corresponding to the data source table comprises:

obtaining metadata of the data source table, wherein the metadata comprises a data primary key, a data name and a data type;

generating a materialized view log corresponding to the metadata based on a data primary key, a data name, and a data type of the metadata.

7. The method of claim 1, after synchronizing the delta data to a target database, further comprising:

and performing data cleaning on the materialized view log.

8. An incremental synchronization apparatus based on materialized view logs, comprising:

the system comprises an acquisition module, a storage module and a processing module, wherein the acquisition module is used for acquiring a data source table in a source database and creating a materialized view log corresponding to the data source table, the materialized view log comprises a plurality of attribute columns, and the attribute columns are used for determining fields and update values related to increment operation of the data source table;

the first determination module is used for determining a field corresponding to the increment operation when the increment operation aiming at the data source table is generated;

the extracting module is used for extracting the incremental data corresponding to the incremental operation from the materialized view log when the field corresponding to the incremental operation is in the attribute column;

and the synchronization module is used for synchronizing the incremental data to the target database.

9. A computer device, comprising:

a memory and a processor, the memory and the processor being communicatively coupled to each other, the memory having stored therein computer instructions, the processor executing the computer instructions to perform the method for materialized view log based incremental synchronization of any of claims 1-7.

10. A computer-readable storage medium having stored thereon computer instructions for causing a computer to perform the materialized view log based incremental synchronization method of any one of claims 1 to 7.

Images