CN109408589A

CN109408589A - Method of data synchronization and device

Info

Publication number: CN109408589A
Application number: CN201811074438.9A
Authority: CN
Inventors: 李日光; 丁远普
Original assignee: New H3C Big Data Technologies Co Ltd
Current assignee: New H3C Big Data Technologies Co Ltd
Priority date: 2018-09-14
Filing date: 2018-09-14
Publication date: 2019-03-01
Anticipated expiration: 2038-09-14
Also published as: CN109408589B

Abstract

The disclosure provides a kind of method of data synchronization and device, for the data in source database to be synchronized to MPP database, method includes: to obtain preset quantity action statement from the journal file of source database, and every action statement includes the version information of the action statement；Search the cancel statement in preset quantity action statement, and when finding cancel statement, the version information object run sentence older than the cancel statement is determined in preset quantity action statement, and the insertion sentence for the deletion condition for meeting the cancel statement is searched in object run sentence as sentence to be deleted；The sentence to be deleted is deleted from preset quantity action statement, the insertion sentence in remaining action statement is merged, and the data of MPP database are updated according to the action statement after merging.So, it is possible to reduce the number of operations executed when more new data, so that the time delay of more new data is reduced, to improve the consistency of source database and the data in MPP database.

Description

Data synchronization method and device

Technical Field

The present disclosure relates to the field of database technologies, and in particular, to a data synchronization method and apparatus.

Background

In practical applications, it is sometimes necessary to synchronize data in one database (often referred to as a "source database") to another database (often referred to as a "target database"). Currently, implementation is mainly achieved by OracleCDC (ChangeDataCapture ) technology.

The OracleCDC technology mainly includes the following two implementation modes: firstly, in a synchronization mode, triggers are respectively set in a source database for operations such as insertion, modification, deletion and the like, once data in the source database is changed, the corresponding triggers can be triggered to write the changed data into a temporary table, and then the data in the temporary table is updated into a target database through an Extract-Transform-Load (ETL) tool. Secondly, in an asynchronous mode, the operation statement is analyzed from the log file in the source database, and then the operation statement is repeatedly executed in the target database, so that the data in the source database is synchronized into the target database.

Research shows that the two modes are applicable to the situation that the source database and the target database are both traditional databases (such as Oracle, SQLserver, MySQL and the like), but when the target database is a distributed parallel (MPP) database, serious time delay exists in the process of performing data synchronization by adopting the two modes, so that data in the source database and the target database are inconsistent.

Disclosure of Invention

In view of the above, the present disclosure is directed to a data synchronization method and apparatus to at least partially solve the above problems.

In order to achieve the purpose, the technical scheme is as follows:

in a first aspect, the present disclosure provides a data synchronization method, including:

obtaining a preset number of operation statements from a log file of a source database, wherein each operation statement comprises version information of the operation statement;

inquiring deletion sentences in the preset number of operation sentences, determining target operation sentences of which the version information is older than the deletion sentences in the preset number of operation sentences when the deletion sentences are found, and searching insertion sentences which meet deletion conditions of the deletion sentences in the target operation sentences as sentences to be deleted;

and deleting the sentences to be deleted from the preset number of operation sentences, merging the rest operation sentences, and updating the data of the MPP database according to the merged operation sentences.

In a second aspect, the present disclosure provides a data synchronization apparatus, the apparatus comprising:

an operation statement obtaining module, configured to obtain a preset number of operation statements from a log file of a source database, where each operation statement includes version information of the operation statement;

the searching module is used for inquiring the deletion sentences in the preset number of operation sentences, determining the target operation sentences of which the version information is older than the deletion sentences in the preset number of operation sentences when the deletion sentences are found, and searching the insertion sentences which meet the deletion conditions of the deletion sentences in the target operation sentences as the sentences to be deleted;

the deleting module is used for deleting the sentences to be deleted from the preset number of operation sentences;

the merging module is used for merging the rest operation sentences; and

and the updating module is used for updating the data of the MPP database according to the merged operation statement.

Compared with the prior art, the method has the following beneficial effects:

according to the data synchronization method and device, a preset number of operation statements are obtained from a log file of a source database, and each operation statement comprises version information of the operation statement. And searching a deletion statement in a preset number of operation statements, determining a target operation statement with version information older than the deletion statement in the preset number of operation statements when the deletion statement is searched, and searching an insertion statement meeting the deletion condition of the deletion statement in the target operation statement as a to-be-deleted statement. And deleting the sentences to be deleted from a preset number of operation sentences, merging the rest operation sentences, and updating the data of the MPP database according to the merged operation sentences. Therefore, the number of operations executed during data updating can be reduced, and the time delay of data updating is reduced, so that the consistency of the data in the source database and the MPP database is improved.

Drawings

To more clearly illustrate the technical solutions of the present disclosure, the drawings needed for the embodiments will be briefly described below, it should be understood that the following drawings only illustrate some embodiments of the present disclosure, and therefore should not be considered as limiting the scope, and those skilled in the art can also obtain other related drawings based on the drawings without inventive efforts.

Fig. 1 is a schematic view of an application scenario provided by the present disclosure;

fig. 2 is a schematic flow chart of a data synchronization method provided in the present disclosure;

FIG. 3 is a schematic diagram illustrating the sub-steps of step S21 shown in FIG. 2;

FIG. 4 is a schematic diagram illustrating the sub-steps of step S22 shown in FIG. 2;

FIG. 5 is a schematic flow chart of a data synchronization method provided by the present disclosure;

fig. 6 is a block schematic diagram of an electronic device provided by the present disclosure;

fig. 7 is a functional block diagram of a data synchronization apparatus provided in the present disclosure.

Icon: 10-an electronic device; 11-a machine-readable storage medium; 110-data synchronization means; 111-operation statement obtaining module; 112-a lookup module; 1121-traversing the sub-modules; 1122-a first lookup sub-module; 1123-a second lookup submodule; 113-a delete module; 114-an update module; 115-a merge module; 116-format handling module; 12-a processor; 13-a communication unit; 20-source database; 30-target database.

Detailed Description

To make the objects, technical solutions and advantages of the present disclosure more clear, the technical solutions of the present disclosure will be described clearly and completely below with reference to the accompanying drawings in the present disclosure, and it is obvious that the described embodiments are some, but not all embodiments of the present disclosure. The components of the present disclosure, as generally described and illustrated in the figures herein, may be arranged and designed in a wide variety of different configurations.

Thus, the following detailed description of the embodiments of the present disclosure, presented in the figures, is not intended to limit the scope of the claimed disclosure, but is merely representative of selected embodiments of the disclosure. All other embodiments, which can be derived by a person skilled in the art from the embodiments disclosed herein without making any creative effort, shall fall within the protection scope of the present disclosure.

It should be noted that: like reference numbers and letters refer to like items in the following figures, and thus, once an item is defined in one figure, it need not be further defined and explained in subsequent figures.

As shown in fig. 1, which is a schematic view of an application scenario provided by the present disclosure, an electronic device 10 may access a source database 20 and a target database 30 through a network, where the target database 30 is an MPP database, specifically may be greenplus, GBase, and the like, and the source database 20 may be a traditional database such as Oracle, SQLserver, MySQL, and the like, which is not limited in this embodiment.

In the present disclosure, the electronic device 10 may be any device capable of accessing and operating a database, and may be a server, for example.

Fig. 2 is a schematic flowchart of a data synchronization method provided by the present disclosure, which is used to synchronize data in a source database 20 to a target database 30, and the method may be executed by the electronic device 10 shown in fig. 1, and the steps included in the method will be described in detail below by taking the source database 20 as an Oracle and the target database as an MPP database as an example.

Step S21, obtaining a preset number of operation statements from the log file of the source database 20, where each operation statement includes version information of the operation statement.

The log file may be a redo log or an archive log of the source database 20, which includes operation statements of the source database 20 by the user, and the operation statements may specifically be INSERT (INSERT) statements, DELETE statements (DELETE), UPDATE (UPDATE) statements, and the like.

In this disclosure, each operation statement includes version information for uniquely representing the operation statement, where the version information may be an operation sequence number (SCN), and the operation sequence numbers of the operation statements in the log file are usually sequentially incremented, in other words, the operation sequence number of an operation statement in a time sequence before is smaller than the operation sequence number of an operation statement in a time sequence after.

In the present disclosure, step S21 may include sub-steps as shown in fig. 3.

Step S31, analyzing the log file by a log analysis tool to obtain the operation statement of the source database 20.

In the present disclosure, the log file is scanned by a log analysis tool, from which the operation statements that were executed on the source database 20 can be extracted. Wherein the log analysis tool may be an Oracle Loginer. Each operation statement that oracleloginer parses from the log file is usually an SQL statement, for example:

the insert statement may be: insert inter "logminer" for "emp" ("empno", "joba") values ("3333", "clerk");

the delete statement may be: deletefur "emp" where "empno" - "3333";

the update statement may be: update "emp" set jobo "manger" where "empno" 3333.

Step S32, in the operation statements of the source database 20, the preset number of operation statements are intercepted from the operation statement with the latest version information intercepted last time.

In one embodiment, the preset number of operation statements may refer to: after the source database 20 is updated from the last time, all the operation statements newly added in the log file.

In another embodiment, in consideration of the fact that the number of the operation statements newly added to the log file is large after the source database 20 is updated last time, the newly added operation statements may be updated in batches. In this case, the preset number may be flexibly set, for example, may be set to 50, 100, 200, or 500, etc.

In this disclosure, after intercepting the preset number of operation statements each time, it may be determined that the preset number of operation statements carry the operation statement with the latest version information, and the value of the pre-stored flag version information is updated to the latest version information. When the interception operation is performed next time, the preset number of operation statements can be intercepted from the operation statements carrying the sign version information.

Alternatively, the flag version information may be prestored in the electronic device 10, and its initial value may be NULL (NULL). In the implementation process, if the electronic device 10 detects that the initial value of the flag version information is null, it may be determined that the currently traversed delete statement is the first delete statement, so that all the operation statements before the currently traversed delete statement may be directly used as the target operation statements.

In the present disclosure, the preset number of operation statements obtained from the log file of the source database 20 typically include an insert statement, a delete statement, and some other statements. The deletion statement is an operation statement for deleting data that meets a specific deletion condition.

In some cases, there may be a deletion statement in a preset number of pieces of operation statements obtained from the log file, and there may be an insertion statement before the deletion statement that meets a deletion condition of the deletion statement, in other words, data inserted in an insertion statement before the deletion statement may be deleted in the deletion statement.

For example, assume that the following operation statements are obtained from the log file:

SCN1 inserts data into data table 1;

SCN2 inserts data into data table 1;

SCN3 inserts data into data table 2;

SCN4 inserts data into data table 1;

SCN5 deletes data table 1.

In the above operation statements, the data inserted in the operation statements SCN1, SCN2, and SCN4 are all deleted in the operation statement SCN5, so that when the data in the target database 30 is updated, the data in the source database 20 and the target database 30 can be kept consistent without executing any operation statement corresponding to the operation statements SCN1, SCN2, and SCN 4. In the present disclosure, this effect can be achieved by steps S22 to S24 described later.

As another example, assume that the following operation statements are obtained from the log file:

the SCN1 inserts a data record with field a ═ 1 into data table 1;

the SCN2 inserts data records with field a being 1 and field b being 2 into the data table 1;

the SCN3 inserts a data record with field c being 1 and field d being 3 into the data table 2;

the SCN4 inserts a data record with field c ═ 1 into data table 1;

SCN5 deletes data table 2;

SCN6 deletes the data statement with a ═ 1 from data table 1.

In the above-described operation statement, the data inserted by the operation statement SCN3 has been deleted in the operation statement SCN5, and therefore, the operation statement SCN3 may not be executed any more when the target database 30 is updated. Correspondingly, the data inserted in the operation statements SCN1 and SCN2 have been deleted in the operation statement SCN6, and it may not be necessary to execute the operation statements SCN1 and SCN2 any more when updating the target database 30.

Step S22, querying a deletion statement in the preset number of operation statements, determining a target operation statement with version information older than the deletion statement in the preset number of operation statements when the deletion statement is found, and searching an insertion statement meeting the deletion condition of the deletion statement in the target operation statement as a to-be-deleted statement.

In practical applications, the preset number of operation statements obtained from the log file may include one, two or more deletion statements. When there are a plurality of deletion statements, an insertion statement that meets the deletion condition of the deletion statement may exist before each deletion statement.

In this case, in one embodiment of the present disclosure, step S22 may include the steps shown in fig. 4.

And step S41, traversing the operation statements according to the sequence of the version information from old to new.

Step S42, when the currently traversed operation statement is a deletion statement, determining, as the target operation statement, an operation statement of the plurality of operation statements whose version information is older than the currently traversed deletion statement.

For example, assuming that there are two deletion statements, namely a first deletion statement and a second deletion statement, in the plurality of operation statements, the following situation may occur in practical applications: in an operation sentence preceding the first deletion sentence, there is an insertion sentence satisfying the deletion condition of the second deletion sentence. In this case, the operation statements before the currently traversed delete statement may all be the target operation statements. The method specifically comprises the following steps: when traversing to the first deletion statement, determining all operation statements before the first deletion statement as target operation statements; and when traversing to the second deletion statement, determining all the operation statements before the second deletion statement as target operation statements. It should be noted that, in the present disclosure, the statements before a certain operation statement all refer to statements whose version information is older than the operation statement, and the statements after a certain operation statement all refer to statements whose version information is newer than the operation statement.

Step S43, finding an insertion statement meeting the deletion condition of the currently traversed deletion statement in the target operation statement as the to-be-deleted statement.

In another embodiment of the present disclosure, step S22 can be implemented by the following sub-steps:

traversing the plurality of operation statements according to the sequence of the version information from old to new; when the currently traversed operation statement is a deletion statement, determining an operation statement of which the version information is between the currently traversed deletion statement and the last traversed deletion statement as the target operation statement in the plurality of operation statements;

and searching an insertion statement which meets the deletion condition of the currently traversed deletion statement in the target operation statement as the statement to be deleted.

Still taking the first deletion statement and the second deletion statement as examples, in this embodiment, when traversing to the second deletion statement, the operation statement whose version information is between the first deletion statement and the second deletion statement is determined as the target operation statement. For example, if the version information of the currently traversed delete statement (second delete statement) is V6 and the version information of the last traversed delete statement (first delete statement) is V2, then all the operation statements whose version information is newer than V2 and older than V6 are the target operation statements.

Specifically, taking the version information as the operation sequence number SCN as an example, if the operation sequence number of the currently traversed deletion statement (second deletion statement) is S1, and the operation sequence number of the last traversed deletion statement (first deletion statement) is S5, then the operation sequence number S satisfies S5< S1.

In this way, the statement to be deleted can be determined from the operation statements between adjacent deletion statements at a time.

Step S23, deleting the sentence to be deleted from the preset number of operation sentences.

Therefore, the number of insertion operations required to be executed when updating the data of the MPP database can be reduced.

In step S24, the insertion sentences in the remaining operation sentences are merged.

Wherein the remaining operation statements refer to: and deleting all the operation statements left after the statement to be deleted from the preset number of operation statements.

For example, it is assumed that there are an insertion statement 1 and an insertion statement 2, where the insertion statement 1 is a data record in which the field a is 1 and the field b is 1 is inserted into the data table 1, and the insertion statement 2 is a data record in which the field a is 1 and the field c is 3 is inserted into the data table 1. It can be determined that both the insert sentence 1 and the insert sentence 2 insert data into the same data table (data table 1), and therefore, the insert sentence 1 and the insert sentence 2 can be merged into one insert sentence.

It has been found that the MPP database is usually stored in a column, for example, a, b, c, d in the above example each represents a column (list) in the data table 1, and they belong to different storage spaces. Taking the insertion statement insert (a, b, c, d) values (1, 1, NULL) as an example, when the insertion statement is executed, a list corresponding to the field a and a list corresponding to the field b need to be searched for data insertion, which requires two IO operations. Thus, when each insert statement is executed, the corresponding list needs to be searched first.

Since the insertion statement 1 involves data insertion into the list corresponding to the field a, and the insertion statement 2 also involves data insertion into the list corresponding to the field a, the merging process can only need to search the list corresponding to the field a once, and the number of IO operations is reduced. When the number of the merged insertion sentences is large, the number of IO operations executed during updating of the data of the MPP database can be greatly reduced, and the updating speed is increased. In the implementation process, there may be multiple different insertion statements that are found to insert data into the same data table. In one embodiment, the found different insert sentences may all be combined into one insert sentence.

Alternatively, in an embodiment of the present disclosure, step S24 may be implemented by the following sub-steps:

and merging the insertion sentences with the same operation target data table into one insertion sentence in the rest operation sentences.

The target data table refers to a data table operated by the operation statement. Correspondingly, in the above step, all the insertion statements operating on the same data table in the remaining operation statements are merged into one insertion statement.

In another embodiment, step S24 may be implemented by the following sub-steps:

and merging the inserting sentences of which the version information is between the currently traversed deleting sentence and the last traversed deleting sentence and the same as the operated target data table into one inserting sentence in the rest operating sentences.

In particular, in this embodiment, for the deletion statement traversed for the first time, the insertion statements whose version information is before the deletion statement and whose target data table of operation is the same may be merged into one difference statement.

In practical applications, the remaining operation statements may be divided into a plurality of operation statement groups by a plurality of deletion statements. For example, it is assumed that the remaining operation statements are SCN1, SCN2, SCN5, SCN6, SCN7, SCN8 and SCN9, where SCN5 and SCN9 are deletion statements, the remaining operation statements are divided into 2 operation statement groups by deletion statements SCN5 and SCN9, where the 1 st operation statement group includes SCN1 and SCN2, and the 2 nd operation statement group includes SCN6, SCN7 and SCN 8.

In implementation, for each operation statement group, the insertion statements in the operation statement group that operate the same data table may be merged into one insertion statement.

Step S24 is described below by way of a specific example.

Assuming that after the to-be-deleted statement is deleted from the preset number of operation statements, the remaining operation statements are as follows:

the SCN2 inserts data records with field b being 1 and field c being 1 into the data table 1;

the SCN3 inserts data records with field b being 1 and field d being 2 into the data table 1;

SCN4 deletes the data record with field a ═ 1 from data table 1;

the SCN5 inserts a data record with field b being 1 and field d being 1 into the data table 1;

the SCN7 inserts data records with field a being 2 and field b being 1 into the data table 1;

SCN8 deletes the data record with field d-2 from data table 1.

In implementation, in an embodiment, it may be determined that, in the remaining operation statements, the target data tables operated by the insertion statements SCN2, SCN3, SCN5, and SCN7 are all data table 1, and therefore, the insertion statements SCN2, SCN3, SCN5, and SCN7 may be merged into one insertion statement.

In another embodiment, it may be determined that, in the remaining operation statements, the target data tables operated by the insertion statements SCN2 and SCN3 before the deletion statement SCN4 are both data table 1, and therefore, the insertion statements SCN2 and SCN3 may be merged into one insertion statement; the target data tables of the operations of the insert statements SCN5 and SCN7 between the delete statements SCN4 and SCN8 are both data table 1, and thus the insert statements SCN5 and SCN7 can be merged into one insert statement.

Optionally, in the present disclosure, to facilitate merging of the insert sentences, the data synchronization method may further include the steps illustrated in fig. 5 before performing step S22.

Step S51, for each insertion statement in the preset number of operation statements, determining a target data table operated by the insertion statement, and determining a field included in the target data table as a target field.

In step S52, it is determined whether or not there is a target field that the insert sentence does not include.

And step S53, if yes, adding the excluded target field in the insertion statement, and setting the insertion value of the added target field to null.

Wherein the inserted value is a value inserted to the added target field.

For example, if the preset number of operation statements include the insertion statement 1 and the insertion statement 2, the data table 1 operated by the insertion statement 1 may be determined as the target data table for the insertion statement 1, and further, the field a, the field b, the field c, and the field d included in the data table 1 (target data table) may be determined as the target fields. Then, it may be determined that the target field c and the target field d are not included in the insertion sentence 1, so that the target field c and the target field d are added in the insertion sentence 1, and the insertion values of the added target field c and the target field d are set to NULL.

Correspondingly, the process in the above process may be referred to for the insert statement 2, so that the target field b and the target field d are added to the insert statement 2, and the insert values of the target field b and the target field d are both set to NULL.

In this case, assume that insertion statement 1 is: insertintest (a, b) values (1, 1); the insert statement 2 is: insert notes (a, c) values (1, 3). Then, after processing in step S61-step S63, the insertion sentence 1 is changed to: insertintest (a, b, c, d) values (1, 1, NULL, NULL); insert statement 2 changes to: insertintest (a, b, c, d) values (1, NULL, 3, NULL).

Then, the processed insert statement 1 and insert statement 2 may be merged as: insertintest (a, b, c, d) values (1, 1, NULL) (1, NULL, 3, NULL).

Alternatively, in the present disclosure, the above steps S61-S63 may be performed on the insertion sentences in the remaining operation sentences before step S51.

And step S25, updating the data of the MPP database according to the merged operation statement.

The merged operation statement at least comprises the deletion statement and the merged insertion statement.

Correspondingly, if the preset number of operation statements further include an update statement and a query statement, the remaining operation statements also include the update statement and the query statement, and the combined operation statement also includes the update statement and the query statement.

It should be understood that the merged operation statement refers to all operation statements obtained after merging operation is performed on the remaining operation statements. For example, in some embodiments, only some of the remaining operation statements are merged, and the merged operation statement includes: the partial statements are combined to obtain the operation statements and the other partial operation statements which are not combined.

In implementation, the merged operation statement is executed in the MPP database, that is, the data in the MPP database (the target database 30) can be updated, so that the data in the source database 20 is synchronized to the target database 30.

It is found through research that in the synchronization mode of the Oracle CDC technology, a trigger needs to be set in the service table of the source database 20, and the trigger needs to repeatedly execute any operation on the source database 20 once to record data corresponding to the operation in the temporary table. This may occupy a large amount of resources of the source database 20, which may have an impact on the performance of the source database 20. In an OracleCDC asynchronous mode, a large number of insertion operations need to be performed On the MPP database, and the MPP database is suitable for performing OLAP (On-Line Analysis Processing), supports complex Analysis operations, and is not suitable for performing large-batch insertion operations. For example, a conventional database such as Oracle may perform tens of thousands of insert operations per second, whereas an MPP database typically can only perform hundreds of insert operations per second. If all the obtained operation statements are executed on the MPP database once, a large number of operation statements cannot be executed in time during actual operation, so that data synchronization of the source database and the MPP database (target database) is delayed, and data inconsistency is caused.

However, the data synchronization method provided by the present disclosure obtains the operation statements from the log file for data synchronization, and this way does not encroach on the resources of the source database 20, i.e., does not occupy the resources of the source database 20, and accordingly does not cause performance degradation of the source database 20. In addition, by deleting the insert statement before the delete statement, which meets the condition of the delete statement, the number of insert operations executed in the update process can be reduced, so that the time delay of the source database 20 and the target database 30 in the data synchronization process is alleviated, and the problem of data inconsistency caused by the time delay is alleviated.

In addition, by combining different insert statements operating on the same data table, the number of IO operations executed in the updating process can be reduced, and the data synchronization speed is further improved.

A specific example is given below to explain the above steps in detail.

It is assumed that a preset number of operation statements obtained from the log file are shown in table 1 below, where each row represents one operation statement and the sequence number represents version information of the operation statement.

TABLE 1

In table 1, insert inter test (a, b) values (1, 2) indicate that a data record having a field a of 1 and a field b of 2 is inserted into the data table test. Correspondingly, insertintest (a, c) values (1, 3) represent data records with fields a being 1 and c being 3 inserted into the data table test; insert values (1, 5) indicate that a data record with field c being 1 and field d being 5 is inserted into the data table test. The meaning of other similar statements may be determined with reference to the above examples.

delete from testwhere 1or b 6, indicates that the data record with field a 1 and the data record with field b 6 are deleted from the data table test.

In implementation, when the electronic device 10 obtains the preset number of operation sentences, the format of the preset number of operation sentences may be processed according to the steps S61-S63, so as to obtain the operation sentences as shown in table 2 below.

TABLE 2

Serial number	SQL
		1	insert into test(a，b，c，d)values(1，2，NULL，NULL)
2	insert into test(a，b，c，d)values(1，2，NULL，NULL)
		3	insert into test(a，b，c，d)values(1，NULL，3，NULL)
4	insert into test(a，b，c，d)values(1，NULL，NULL，4)
		5	insert into test(a，b，c，d)values(NULL，NULL，1，5)
6	insert into test(a，b，c，d)values(NULL，NULL，1，6)
		7	insert into test(a，b，c，d)values(1，7，NULL，NULL)
8	insert into test(a，b，c，d)values(1，8，NULL，NULL)
		9	insert into test(a，b，c，d)values(1，9，NULL，NULL)
10	delete from test where a＝1or b＝6
		11	insert into test(a，b，c，d)values(1，10，NULL，NULL)
12	insert into test(a，b，c，d)values(1，11，NULL，NULL)
		13	insert into test(a，b，c，d)values(1，12，NULL，NULL)
14	insert into test(a，b，c，d)values(1，13，NULL，NULL)
		15	insert into test(a，b，c，d)values(1，14，NULL，NULL)
16	insert into test(a，b，c，d)values(1，15，NULL，NULL)
		17	insert into test(a，b，c，d)values(1，16，NULL，NULL)
18	insert into test(a，b，c，d)values(1，17，NULL，NULL)
		19	insert into test(a，b，c，d)values(1，18，NULL，NULL)
20	insert into test(a，b，c，d)values(1，19，NULL，NULL)
		21	delete from test where b＝10

When the operation sentences with the sequence numbers of 10 are traversed, the operation sentences (i.e. the operation sentences in table 2) after the processing are traversed in the order from small to large, and in this case, no other deletion sentence exists before the deletion sentence, and the insertion sentences meeting the deletion condition (i.e. a-1 or b-6) of the deletion sentence can be searched for in all the operation sentences before the deletion sentence, so that the insertion sentences with the sequence numbers of 1-4 and 7-9 can be determined to all meet the deletion condition of the deletion sentence with the sequence number of 10, and the insertion sentences with the sequence numbers of 1-4 and 7-9 can be deleted. Wherein, the insert sentences with the sequence numbers of 1-4 and 7-9 can serve as the sentences to be deleted in the disclosure.

When traversing to the operation statement with sequence number 21, the operation statement may be determined to be a delete statement. In the first manner, it may be determined that the last traversed delete sentence is an operation sentence with a sequence number of 10 (hereinafter referred to as delete sentence 10), so that an insert sentence that meets the delete condition (a ═ 1or c ═ 1) of the delete sentence 21 may be searched for as a to-be-deleted sentence in operation sentences with sequence numbers between 10 and 21 (i.e., operation sentences with sequence numbers 11 and 20), and thus the electronic device 10 regards the insert sentence with sequence number 11 as a to-be-deleted sentence and deletes the to-be-deleted sentence by searching for the insert sentence.

In the second way, when the operation statement of the sequence number 21 is traversed and it is determined that the operation statement is a delete statement, all the operation statements of the sequence numbers 1 to 20 may be determined as target operation statements and deleted. The following process is described in detail by taking the target operation statement determined in the first manner as an example.

In the first manner, the remaining operation statements in the preset number of operation statements are as shown in table 3 below:

TABLE 3

It can be determined by the lookup that there are different insertion statements for inserting data into the same data table, specifically, insertion statements 5, 6, and 11-20 for inserting data into the data table test, in the remaining operation statements.

In one embodiment, insert statements 5, 6, and 11-20 may be merged into one insert statement, resulting in: insertintertest (a, b, c, d) values (NULL, 1, 5) (NULL, 1, 6) (1, 11, NULL) (1, 12, NULL) (1, 13, NULL) (1, 14, NULL) (1, 15, NULL) (1, 16, NULL) (1, 17, NULL) (1, 18, NULL) (1, 19, NULL).

Through the above process, the finally obtained merged operation statement is shown in table 4 below:

TABLE 4

In another embodiment, insert statement 5 and insert statement 6 may be combined to obtain insert entries (a, b, c, d) values (NULL, 1, 5) (NULL, 1, 6).

Insert statements 12-20 are merged to obtain insert inter test (a, b, c, d) values (1, 11, NULL, NULL) (1, 12, NULL, NULL) (1, 13, NULL, NULL) (1, 14, NULL, NULL) (1, 15, NULL, NULL) (1, 16, NULL, NULL) (1, 17, NULL, NULL) (1, 18, NULL, NULL) (1, 19, NULL, NULL).

Through the above process, the finally obtained merged operation statement is shown in the following table 5:

TABLE 5

The electronic device 10 executes the merged operation statements in table 4 or table 5 on the target database 30 (i.e., the MPP database), so as to synchronize the corresponding data in the source database 20 into the MPP database.

Referring to fig. 6, fig. 6 is a block schematic diagram of an electronic device 10 provided in the present disclosure, where the electronic device 10 includes a machine-readable storage medium 11, a processor 12, and a communication unit 13.

The machine-readable storage medium 11, the processor 12, and the communication unit 13 are electrically connected directly or indirectly. For example, the components may be electrically connected to each other via one or more communication buses or signal lines. The machine-readable storage medium 11 stores therein machine-executable instructions for implementing the data synchronization method provided by the present disclosure, which may be implemented by the processor 12 by reading and executing the machine-executable instructions on the machine-readable storage medium 11. The communication unit 13 is used for establishing a communication connection with an external device, for example, a communication connection with a server where the source database 20 or the target data 30 is located.

The machine-readable storage medium 11 referred to herein may be any electronic, magnetic, optical, or other physical storage device that can contain or store information such as executable instructions, data, and the like. For example, the machine-readable storage medium 11 may be: RAM (random access memory), volatile memory, any type of storage disk (such as optical disks, DVDs, etc.), or similar storage media, or combinations thereof.

Referring to fig. 7, fig. 7 is a functional module block diagram of a data synchronization apparatus 110 provided in the present disclosure, where the data synchronization apparatus 110 may include at least one software functional module that may be stored on a machine-readable storage medium 11 in the form of software or Firmware (Firmware) or solidified in an Operating System (OS) of the electronic device 10. The processor 12 may execute executable modules on the machine-readable storage medium 11, such as software functional modules and computer programs included in the data synchronization apparatus 110. The data synchronization apparatus 110 may include an operation statement obtaining module 111, a lookup module 112, a deletion module 113, a merging module 114, and an update module 115.

The operation statement obtaining module 111 is configured to obtain a preset number of operation statements from the log file of the source database 20, where each operation statement includes version information of the operation statement.

In the present disclosure, the description about the operation sentence obtaining module 111 may specifically refer to the detailed description of the step S21 shown in fig. 2, that is, the step S21 may be performed by the operation sentence obtaining module 111.

The searching module 112 is configured to query a deletion statement in the preset number of operation statements, determine, when the deletion statement is found, a target operation statement whose version information is older than the deletion statement in the preset number of operation statements, and search, in the target operation statement, an insertion statement that meets a deletion condition of the deletion statement as a to-be-deleted statement.

In the present disclosure, the description about the lookup module 112 may refer to the detailed description of step S22 shown in fig. 2, that is, step S22 may be performed by the lookup module 112.

Optionally, in the present disclosure, the lookup module 112 may include a traversal sub-module 1121, a first lookup sub-module 1122, and a second lookup sub-module 1123.

The traversal submodule 1121 is configured to traverse the preset number of operation statements according to an old-to-new sequence of the version information.

The first searching sub-module 1122 is configured to determine, as the target operation statement, an operation statement with version information older than the currently traversed deletion statement in the preset number of operation statements when the currently traversed operation statement is the deletion statement, or determine, as the target operation statement, an operation statement with version information between the currently traversed deletion statement and the last traversed deletion statement in the preset number of operation statements.

The second searching submodule 1123 is configured to search, in the target operation statement, an insertion statement that meets the deletion condition of the currently traversed deletion statement as the statement to be deleted.

The deleting module 113 is configured to delete the to-be-deleted statement from the preset number of operation statements.

In the present disclosure, the description of the deletion module 113 may refer to the detailed description of step S23 shown in fig. 2, that is, step S23 may be performed by the deletion module 113.

The merging module 114 is configured to merge the insertion statements in the remaining operation statements.

In the present disclosure, the merging module 114 may be configured to execute step S24 shown in fig. 2, and the detailed description of step S24 may be referred to for the description of the merging module 114.

In this disclosure, the merging module 114 may be specifically configured to merge the insertion statements identical to the target data table of the operation into one insertion statement in the remaining operation statements; or, in the remaining operation statements, merging the insertion statements, the version information of which is between the currently traversed deletion statement and the last traversed deletion statement and the same as the operated target data table, into one insertion statement.

The updating module 115 is configured to update data of the MPP database according to a merged operation statement, where the merged operation statement at least includes the delete statement and the merged insert statement.

In the present disclosure, the description of the updating module 115 may refer to the detailed description of step S25 shown in fig. 2, that is, step S25 may be performed by the updating module 115.

Optionally, in the present disclosure, the data synchronization apparatus 110 may further include a format processing module 116.

The format processing module 116 is configured to determine, for each insertion statement in the preset number of operation statements, a target data table operated by the insertion statement, and determine a field included in the target data table as a target field; and judging whether a target field which is not included in the insertion statement exists, if so, adding the target field which is not included in the insertion statement, and setting the insertion value of the added target field to be null.

In the present disclosure, the format processing module 116 may be configured to perform the steps S51, S52, and S53 shown in fig. 5, and the detailed description of the steps S51, S52, and S53 may be referred to for the description of the format processing module 116.

Optionally, in this disclosure, the operation statement obtaining module 111 may be specifically configured to parse the log file through a log analysis tool, obtain the operation statements of the source database, and in the operation statements of the source database, intercept the preset number of operation statements from an operation statement with the latest version information intercepted last time.

In summary, the present disclosure provides a data synchronization method and apparatus, which obtain a preset number of operation statements from a log file of a source database, where each operation statement includes version information of the operation statement. And searching a deleted statement in the plurality of operation statements, determining a target operation statement with version information older than the deleted statement in a preset number of operation statements when the deleted statement is searched, and searching an inserted statement meeting the deleting condition of the deleted statement in the target operation statement as a to-be-deleted statement. Deleting the sentence to be deleted from a preset number of operation sentences, merging the insertion sentences in the rest operation sentences, and updating the data of the MPP database according to the merged operation sentences. Therefore, the times of insertion operation and IO operation executed during data updating can be reduced, and the time delay of data updating is reduced, so that the consistency of data in the source database and the MPP database is improved.

In the embodiments provided in the present disclosure, it should be understood that the disclosed apparatus and method may be implemented in other ways. The apparatus embodiments described above are merely illustrative, and for example, the flowchart and block diagrams in the figures illustrate the architecture, functionality, and operation of possible implementations of apparatus, methods and computer program products according to various embodiments of the present disclosure. In this regard, each block in the flowchart or block diagrams may represent a module, segment, or portion of code, which comprises one or more executable instructions for implementing the specified logical function(s). It should also be noted that, in some alternative implementations, the functions noted in the block may occur out of the order noted in the figures. For example, two blocks shown in succession may, in fact, be executed substantially concurrently, or the blocks may sometimes be executed in the reverse order, depending upon the functionality involved. It will also be noted that each block of the block diagrams and/or flowchart illustration, and combinations of blocks in the block diagrams and/or flowchart illustration, can be implemented by special purpose hardware-based systems which perform the specified functions or acts, or combinations of special purpose hardware and computer instructions.

In addition, functional modules in the embodiments of the present disclosure may be integrated together to form an independent part, or each module may exist separately, or two or more modules may be integrated to form an independent part.

The functions, if implemented in the form of software functional modules and sold or used as a stand-alone product, may be stored in a computer readable storage medium. Based on such understanding, the technical solution of the present disclosure may be embodied in the form of a software product, which is stored in a storage medium and includes several instructions for causing a computer device (which may be a personal computer, a server, or a network device) to execute all or part of the steps of the method according to the embodiments of the present disclosure. And the aforementioned storage medium includes: a U-disk, a removable hard disk, a Read-Only Memory (ROM), a Random Access Memory (RAM), a magnetic disk or an optical disk, and other various media capable of storing program codes.

It is noted that, herein, relational terms such as first and second, and the like may be used solely to distinguish one entity or action from another entity or action without necessarily requiring or implying any actual such relationship or order between such entities or actions. Also, the terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising an … …" does not exclude the presence of other identical elements in a process, method, article, or apparatus that comprises the element.

The above description is only for the specific embodiments of the present disclosure, but the scope of the present disclosure is not limited thereto, and any person skilled in the art can easily conceive of the changes or substitutions within the technical scope of the present disclosure, and all the changes or substitutions should be covered within the scope of the present disclosure. Therefore, the protection scope of the present disclosure shall be subject to the protection scope of the claims.

Claims

1. A method for synchronizing data, the method comprising:

deleting the sentences to be deleted from the preset number of operation sentences, merging the insertion sentences in the rest operation sentences, and updating the data of the MPP database according to the merged operation sentences, wherein the merged operation sentences at least comprise the deletion sentences and the merged insertion sentences.

2. The method according to claim 1, wherein querying a deletion statement in the preset number of operation statements, and when finding the deletion statement, determining a target operation statement in the preset number of operation statements whose version information is older than the deletion statement, and finding an insertion statement in the target operation statement that meets a deletion condition of the deletion statement as a to-be-deleted statement comprises:

traversing the preset number of operation statements according to the sequence of version information from old to new;

when the currently traversed operation statement is a deletion statement, determining an operation statement with version information older than the currently traversed deletion statement as the target operation statement in the preset number of operation statements, or determining an operation statement with version information between the currently traversed deletion statement and the last traversed deletion statement as the target operation statement in the preset number of operation statements;

3. The method of claim 2, wherein prior to querying for a delete statement in the preset number of operational statements, the method further comprises:

determining a target data table operated by the insert statement for each insert statement in the preset number of operation statements, and determining fields included in the target data table as target fields;

judging whether a target field which is not included by the insertion statement exists;

and if so, increasing the target field which is not included in the insertion statement, and setting the insertion value of the increased target field to be null.

4. The method of claim 3, wherein merging the insert statements in the remaining operation statements comprises: merging the insertion sentences with the same operation target data table into one insertion sentence in the rest operation sentences; or,

5. The method according to any one of claims 1-4, wherein obtaining a preset number of operation statements from the log file of the source database comprises:

analyzing the log file through a log analysis tool to obtain an operation statement of the source database;

and intercepting the preset number of operation statements from the operation statement with the latest version information intercepted last time in the operation statements of the source database.

6. A data synchronization apparatus, the apparatus comprising:

the merging module is used for merging the insertion sentences in the rest operation sentences; and

and the updating module is used for updating the data of the MPP database according to the merged operation statement, wherein the merged operation statement at least comprises the deletion statement and the insertion statement merged by the merging module.

7. The apparatus of claim 6, wherein the lookup module comprises:

the traversal submodule is used for traversing the preset number of operation statements according to the sequence of the version information from old to new;

a first searching sub-module, configured to determine, when a currently traversed operation statement is a deletion statement, an operation statement, of which version information is older than the currently traversed deletion statement, as the target operation statement in the preset number of operation statements, or determine, as the target operation statement, an operation statement, of which version information is located between the currently traversed deletion statement and a last traversed deletion statement, in the preset number of operation statements;

and the second searching submodule is used for searching the insertion statement which meets the deleting condition of the currently traversed deleting statement in the target operation statement as the statement to be deleted.

8. The apparatus of claim 7, further comprising:

a format processing module, configured to determine, for each insertion statement in the preset number of operation statements, a target data table operated by the insertion statement, and determine a field included in the target data table as a target field; and judging whether a target field which is not included in the insertion statement exists, if so, adding the target field which is not included in the insertion statement, and setting the insertion value of the added target field to be null.

9. The apparatus of claim 8, wherein the merging module is specifically configured to: merging the insertion sentences with the same operation target data table into one insertion sentence in the rest operation sentences; or, in the remaining operation statements, merging the insertion statements, the version information of which is between the currently traversed deletion statement and the last traversed deletion statement and the same as the operated target data table, into one insertion statement.

10. The apparatus according to any one of claims 6 to 9, wherein the operation statement obtaining module is specifically configured to parse the log file through a log analysis tool, obtain the operation statements of the source database, and intercept, in the operation statements of the source database, the preset number of operation statements from an operation statement with the latest version information intercepted last time.