Data synchronization method, device, electronic device, and storage medium

Abstract

The present application discloses a data synchronization method, system, electronic device, and storage medium, the method comprising: a first message integration module obtains first schema information after a table structure change of an upstream database and registers it with a schema registration center; the first message integration module stores a second message record with a first schema identifier after serializing the first message record into a message queue with a third message record with identification information before the change; a second message integration module monitors the first schema information registered by the schema registration center, puts it into a schema queue, obtains a reference message record with a reference schema identifier in the message queue, and target schema information corresponding to the reference schema identifier in the schema queue, generates and sends a DML statement or a DDL statement to a downstream database based on the target schema information and the reference message record, so as to monitor changes in the upstream database table structure without interrupting ETL, and automatically realize synchronous changes in the downstream table structure and data.

Description

Technical Field

[0001] The present application relates to, but is not limited to, the field of computer technology, and in particular to a data synchronization method, system, electronic device, and storage medium. Background Art

[0002] Data warehouses extract data from various data sources, cleanse it, transform it, and integrate it before storing it in a unified location. This process is called the Extract-Transform-Load (ETL) process. During the ETL process, if a business change results in an update to the upstream table structure, the ETL process must be paused, the downstream table structure modified to match the latest structure of the upstream source table, and then resumed. During this pause, both upstream and downstream tables must be suspended, preventing normal service. Furthermore, if table structures need to be frequently adjusted due to business needs, data synchronization must be repeatedly stopped, resulting in significant operational costs. Summary of the Invention

[0003] The embodiments of the present application provide a data synchronization method, system, electronic device, and storage medium, which can enable the system to synchronize data with table structure changes without interrupting the ETL process, thereby ensuring the normal service functions of the system while reducing system operation and maintenance costs.

[0004] In a first aspect, an embodiment of the present application provides a data synchronization method, which is applied to a data synchronization system, wherein the data synchronization system includes an upstream database, a first message integration module, a pattern registration center, a message queue, a second message integration module, and a downstream database, and the method includes:

[0005] The first message integration module integrates upstream data from the upstream database, and upon detecting that a table structure of the upstream database has changed, obtains first schema information corresponding to the changed table structure, and registers the first schema information with the schema registration center, wherein the first schema information is used to describe the data structure of the changed table structure;

[0006] The first message integration module serializes the upstream data to obtain a first message record, and saves the first message record to the message queue, wherein the first message record carries a first mode identifier, and the first mode identifier is used to uniquely identify first mode information of the table structure of the upstream database corresponding to the first message record. The message queue also includes a second message record sorted before the first message record, and the second message record carries the mode identifier before the table structure is changed;

[0007] When the second message integration module monitors that the pattern registration center has registered new pattern information, it obtains the first pattern information from the pattern registration center and saves the first pattern information to a pattern queue, wherein the first pattern information is the pattern information with the shortest registration time in the pattern registration center, and the pattern queue also includes the second pattern information that is sorted before the first pattern information;

[0008] The second message integration module obtains a reference message record carrying a reference mode identifier from the message queue, and obtains target mode information corresponding to the reference mode identifier from the mode queue, wherein the reference message record is the first message record or the second message record;

[0009] The second message integration module parses the reference message record based on the target schema information to obtain a target DML statement, wherein the target DML statement is used to modify data in the downstream database;

[0010] The second message integration module generates a target DDL statement based on the target schema information, wherein the target DDL statement is used to modify the table structure of the downstream database;

[0011] The second message integration module sends the target DML statement or the target DDL statement to the downstream database;

[0012] The downstream database executes the target DML statement to complete data synchronization with the upstream database, or executes the target DDL statement to complete table structure change synchronization with the upstream database.

[0013] In some embodiments, the second message integration module obtains a reference message record carrying a reference mode identifier from the message queue, and obtains target mode information corresponding to the reference mode identifier from the mode queue, including:

[0014] Acquire the reference mode information at the head position in the mode queue;

[0015] When the reference mode information does not correspond to the reference mode identifier, the reference mode information in the mode queue is deleted, and new reference mode information at the head position in the mode queue is reacquired until the new reference mode information corresponds to the reference mode identifier, and the new reference mode information is determined as the target mode information.

[0016] In some embodiments, the second message integration module obtains a reference message record carrying a reference mode identifier from the message queue, and obtains target mode information corresponding to the reference mode identifier from the mode queue, including:

[0017] Acquire the reference mode information at the head position in the mode queue;

[0018] When the reference pattern information is the only pattern information in the pattern queue and the reference pattern information does not correspond to the reference pattern identifier, discarding the reference message record and the reference pattern information, and not submitting the consumption offset to the message queue;

[0019] When new reference mode information at the head position in the mode queue is obtained, and the new reference mode information corresponds to the reference mode identifier, the consumption offset is submitted to the message queue, and the new reference mode information is determined as the target mode information.

[0020] In some embodiments, the second message integration module further includes a deserializer, an SQL builder, and a schema parser. The second message integration module parses the reference message record based on the target schema information to obtain a target DML statement, including:

[0021] The deserializer deserializes the reference message record based on the target schema information to obtain an intermediate message record, and sends the intermediate message record to the SQL builder;

[0022] The SQL builder generates the target DML statement based on the intermediate message record;

[0023] The second message integration module generates a target DDL statement based on the target schema information, including:

[0024] The schema parser obtains a target data mapping relationship based on the target schema information and a preset data type mapping table, wherein the target data mapping relationship is used to indicate a mapping relationship between the target schema information and a table field type corresponding to the upstream database and a table field type corresponding to the downstream database;

[0025] The schema parser obtains target change information based on the target schema information and a reference table structure in a preset table structure cache space, wherein the reference table structure is a table structure of the current downstream database;

[0026] The pattern parser sends the target data mapping relationship and the target change information to the SQL builder;

[0027] The SQL builder generates the target DDL statement based on the target data mapping relationship and the target change information.

[0028] In a second aspect, an embodiment of the present application further provides a data synchronization system, the data synchronization system comprising an upstream database, a first message integration module, a pattern registration center, a message queue, a second message integration module, and a downstream database, the first message integration module comprising a first data processing module and a second data processing module, the second message integration module comprising a third data processing module, a fourth data processing module, and a fifth data processing module;

[0029] The first data processing module is used to integrate upstream data from the upstream database, and when detecting that a table structure of the upstream database has changed, obtain first schema information corresponding to the changed table structure, and register the first schema information with the schema registration center, wherein the first schema information is used to describe the data structure of the changed table structure;

[0030] The second data processing module is configured to serialize the upstream data to obtain a first message record, and save the first message record to the message queue, wherein the first message record carries a first mode identifier, and the first mode identifier is used to uniquely identify first mode information of the table structure of the upstream database corresponding to the first message record. The message queue also includes a second message record that is sorted before the first message record, and the second message record carries the mode identifier before the table structure is changed.

[0031] The third data processing module is configured to, upon monitoring that new pattern information has been registered by the pattern registration center, obtain the first pattern information from the pattern registration center and save the first pattern information to a pattern queue, wherein the first pattern information is the pattern information with the shortest registration time in the pattern registration center, and the pattern queue also includes the second pattern information that is sorted before the first pattern information;

[0032] The fourth data processing module is configured to obtain a reference message record carrying a reference mode identifier from the message queue, and obtain target mode information corresponding to the reference mode identifier from the mode queue, wherein the reference message record is the first message record or the second message record;

[0033] The fifth data processing module is configured to parse the reference message record based on the target schema information to obtain a target DML statement, generate a target DDL statement based on the target schema information, and send the target DML statement or the target DDL statement to the downstream database, wherein the target DML statement is used to modify data in the downstream database, and the target DDL statement is used to modify a table structure in the downstream database;

[0034] The downstream database is used to execute the target DML statement to complete data synchronization with the upstream database, or execute the target DDL statement to complete table structure change synchronization with the upstream database.

[0035] In some embodiments, the fourth data processing module includes:

[0036] A first data acquisition module is used to acquire the reference mode information at the head position in the mode queue;

[0037] The first target mode information determination module is used to delete the reference mode information in the mode queue when the reference mode information does not correspond to the reference mode identifier, and re-acquire new reference mode information located at the head position in the mode queue until the new reference mode information corresponds to the reference mode identifier, and determine the new reference mode information as the target mode information.

[0038] In some embodiments, the fourth data processing module includes:

[0039] A second data acquisition module is used to acquire the reference mode information at the head position in the mode queue;

[0040] a sixth data processing module, configured to discard the reference message record and the reference pattern information, and not submit the consumption offset to the message queue when the reference pattern information is the only pattern information in the pattern queue and the reference pattern information does not correspond to the reference pattern identifier;

[0041] The second target mode information determination module is used to submit the consumption offset to the message queue and determine the new reference mode information as the target mode information when new reference mode information located at the head position in the mode queue is obtained and the new reference mode information corresponds to the reference mode identifier.

[0042] In some embodiments, the second message integration module further includes a deserializer, an SQL builder, and a schema parser, and the schema parser includes a mapping relationship determination module, a change information acquisition module, and a data sending module;

[0043] The deserializer is used to deserialize the reference message record based on the target schema information to obtain an intermediate message record, and send the intermediate message record to the SQL builder;

[0044] The mapping relationship determination module is configured to obtain a target data mapping relationship based on the target schema information and a preset data type mapping table, wherein the target data mapping relationship is configured to indicate a mapping relationship between the target schema information and a table field type corresponding to the upstream database and a table field type corresponding to the downstream database, respectively;

[0045] The change information acquisition module is used to obtain target change information based on the target schema information and a reference table structure in a preset table structure cache space, wherein the reference table structure is a table structure of the current downstream database;

[0046] The data sending module is used to send the target data mapping relationship and the target change information to the SQL constructor;

[0047] The SQL builder is used to generate the target DML statement based on the intermediate message record, and to generate the target DDL statement based on the target data mapping relationship and the target change information.

[0048] In a third aspect, an embodiment of the present application provides an electronic device comprising at least one control processor and a memory for communicating with the at least one control processor; the memory stores instructions that can be executed by the at least one control processor, and the instructions are executed by the at least one control processor to enable the at least one control processor to execute the data synchronization method as described in the first aspect.

[0049] In a fourth aspect, an embodiment of the present application further provides a computer-readable storage medium storing computer-executable instructions, wherein the computer-executable instructions are used to execute the data synchronization method as described in the first aspect.

[0050] An embodiment of the present application provides a data synchronization method, system, electronic device, and storage medium, the method comprising: the first message integration module integrates upstream data from the upstream database, and when detecting that the table structure of the upstream database has changed, obtains the first pattern information corresponding to the changed table structure, and registers the first pattern information to the pattern registration center, wherein the first pattern information is used to describe the data structure of the changed table structure; the first message integration module serializes the upstream data to obtain a first message record, and saves the first message record to the message queue, wherein the first message record carries a first pattern identifier, and the first pattern identifier is used to uniquely identify the first pattern information of the table structure of the upstream database corresponding to the first message record, and the message queue also includes a second message record sorted before the first message record, and the second message record carries the pattern identifier before the table structure is changed; when the second message integration module monitors that the pattern registration center has registered new pattern information, it obtains the first pattern information from the pattern registration center and saves the first pattern information to the pattern queue, wherein , the first pattern information is the pattern information with the shortest registration time in the pattern registration center, and the pattern queue also includes the second pattern information sorted before the first pattern information; the second message integration module obtains the reference message record carrying the reference pattern identifier from the message queue, and obtains the target pattern information corresponding to the reference pattern identifier from the pattern queue, wherein the reference message record is the first message record or the second message record; the second message integration module parses the reference message record based on the target pattern information to obtain a target DML statement, wherein the target DML statement is used to modify the data in the downstream database; the second message integration module generates a target DDL statement based on the target pattern information, wherein the target DDL statement is used to modify the table structure of the downstream database; the second message integration module sends the target DML statement or the target DDL statement to the downstream database; the downstream database executes the target DML statement to complete data synchronization with the upstream database, or executes the target DDL statement to complete table structure change synchronization with the upstream database. This application can perceive changes in the upstream database table structure in real time without interrupting the ETL process, and automatically synchronize changes in the target database table structure, ensuring the normal service functions of the system while reducing system operation and maintenance costs. BRIEF DESCRIPTION OF THE DRAWINGS

[0051] Figure 1 This is a flowchart of the steps of a data synchronization method provided by an embodiment of the present application;

[0052] Figure 2is a module diagram of a data synchronization system provided by another embodiment of the present application;

[0053] Figure 3 is a structural diagram of an electronic device provided by another embodiment of the present application;

[0054] Figure 4 is a schematic diagram of a method for executing data synchronization provided by another embodiment of the present application;

[0055] Figure 5 This is a schematic diagram of generating and sending a target DDL statement or a target DML statement to a downstream database provided by another embodiment of the present application. DETAILED DESCRIPTION

[0056] In order to make the purpose, technical solutions and advantages of this application more clear, the following further describes this application in detail with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain this application and are not intended to limit this application.

[0057] It is understood that although the device schematics illustrate functional module divisions and the flowcharts illustrate logical sequences, in certain circumstances, the steps shown or described may be performed in a sequence that differs from the module divisions in the device or the sequence in the flowcharts. The terms "first," "second," and the like in the specification, claims, or accompanying drawings are used to distinguish similar items and are not necessarily used to describe a specific sequence or precedence.

[0058] Data warehouses extract data from various data sources, cleanse it, transform it, and integrate it before storing it in a unified location. This process is called the data warehouse's ETL (Extract-Transform-Load) process. During the ETL process, if a business change causes an update to an upstream table, the ETL process must be paused, the downstream table's structure modified to match the latest structure of the upstream source table, and then the ETL process must be resumed. During this pause, both upstream and downstream tables must be suspended, preventing normal service. Furthermore, if table structures frequently need to be adjusted due to business needs, data synchronization must be repeatedly stopped, resulting in significant operational costs.

[0059] To solve the above-mentioned problems, an embodiment of the present application provides a data synchronization method, system, electronic device, and storage medium, and the method includes: the first message integration module integrates upstream data from the upstream database, and when it is detected that the table structure of the upstream database has changed, obtains the first pattern information corresponding to the changed table structure, and registers the first pattern information to the pattern registration center, wherein the first pattern information is used to describe the data structure of the changed table structure; the first message integration module serializes the upstream data to obtain a first message record, and saves the first message record to the message queue, wherein the first message record carries a first pattern identifier, and the first pattern identifier is used to uniquely identify the first pattern information of the table structure of the upstream database corresponding to the first message record. The message queue also includes a second message record sorted before the first message record, and the second message record carries the pattern identifier before the table structure is changed; when the second message integration module monitors that the pattern registration center has registered new pattern information, it obtains the first pattern information from the pattern registration center, and saves the first pattern information to the pattern queue, wherein the first pattern information is the pattern information with the shortest registration time in the pattern registration center, and the pattern queue also includes second pattern information sorted before the first pattern information; the second message integration module obtains a reference message record carrying a reference pattern identifier from the message queue, and obtains target pattern information corresponding to the reference pattern identifier from the pattern queue, wherein the reference message record is the first message record or the second message record; the second message integration module parses the reference message record based on the target pattern information to obtain a target DML statement, wherein the target DML statement is used to modify the data in the downstream database; the second message integration module generates a target DDL statement based on the target pattern information, wherein the target DDL statement is used to modify the table structure of the downstream database; the second message integration module sends the target DML statement or the target DDL statement to the downstream database; the downstream database executes the target DML statement to complete data synchronization with the upstream database, or executes the target DDL statement to complete table structure change synchronization with the upstream database. This application can perceive changes in the upstream database table structure in real time without interrupting the ETL process, and automatically synchronize changes in the target database table structure, ensuring the normal service functions of the system while reducing system operation and maintenance costs.

[0060] The embodiments of the present application are further described below with reference to the accompanying drawings.

[0061] refer to Figure 1 , Figure 1Yes, an embodiment of the present application provides a data synchronization method, which is applied to a data synchronization system. The data synchronization system includes an upstream database, a first message integration module, a pattern registration center, a message queue, a second message integration module, and a downstream database. The method includes but is not limited to the following steps:

[0062] Step S110: The first message integration module integrates upstream data from the upstream database. When detecting that a table structure of the upstream database has changed, the module obtains first schema information corresponding to the changed table structure and registers the first schema information with a schema registration center. The first schema information is used to describe the data structure of the changed table structure.

[0063] In step S120, the first message integration module serializes the upstream data to obtain a first message record, and saves the first message record to a message queue, wherein the first message record carries a first schema identifier, which is used to uniquely identify first schema information of the table structure of the upstream database corresponding to the first message record. The message queue also includes a second message record that is sorted before the first message record, and the second message record carries the schema identifier before the table structure change.

[0064] Step S130: When the second message integration module monitors that the pattern registration center has registered new pattern information, it obtains the first pattern information from the pattern registration center and saves the first pattern information to the pattern queue, wherein the first pattern information is the pattern information with the shortest registration time in the pattern registration center, and the pattern queue also includes the second pattern information that is sorted before the first pattern information;

[0065] Step S140: The second message integration module obtains a reference message record carrying a reference mode identifier from the message queue, and obtains target mode information corresponding to the reference mode identifier from the mode queue, wherein the reference message record is the first message record or the second message record;

[0066] Step S150: The second message integration module parses the reference message record based on the target schema information to obtain a target DML statement, wherein the target DML statement is used to modify data in a downstream database;

[0067] Step S160: The second message integration module generates a target DDL statement based on the target schema information, wherein the target DDL statement is used to modify the table structure of the downstream database;

[0068] Step S170: The second message integration module sends the target DML statement or target DDL statement to the downstream database;

[0069] In step S180 , the downstream database executes the target DML statement to complete data synchronization with the upstream database, or executes the target DDL statement to complete table structure change synchronization with the upstream database.

[0070] It is understandable that the system architecture for realizing data synchronization of upstream and downstream databases in this application is as follows Figure 4 As shown, the process of the system using the message queue for ETL is as follows: the first message integration module integrates the upstream data in the synchronization table of the upstream database, serializes the upstream data into the first message record and passes it into the message queue, and registers the pattern information describing the message record data structure with the pattern registration center; the second message integration module consumes the message records in the message queue, obtains the pattern information from the pattern registration center, deserializes the consumed message records, and finally writes the deserialized data into the downstream database. During the ETL process, the second message integration module will monitor the schema changes of the schema registration center in real time. When the table structure of the upstream database synchronization table changes, the new schema information describing the table structure (i.e., the first schema information) will be registered in the schema registration center. When the second message integration module monitors the registration of new schema information, it will perceive the change in the source library table structure in real time, and then obtain the first schema information describing the table structure from the schema registration center, parse the first schema information, and obtain the target DDL statement for adjusting the target library table structure. The second message integration module will send the target DDL statement to the downstream database, so that the downstream database will execute the target DDL statement to complete the synchronization change of the downstream table structure; at the same time, the second message integration module will parse the reference message record based on the target schema information, generate a target DML (Data Manipulation Language) statement, and send it to the downstream database so that the downstream database will execute the target DML statement to complete data synchronization.

[0071] It should be noted that when using message queues for data synchronization, the data needs to be serialized and deserialized. When serializing and deserializing the data, schema information describing the data structure corresponding to the data is required. The schema information of this embodiment defines the fields, field types, primary keys and other attributes in the relevant data. The ETL process in the prior art does not introduce a schema registration center. Therefore, in the traditional ETL process, the schema information is carried in each message record after serialization, which makes each message record complicated and lengthy, and the data payload is not high, which in turn causes low data transmission efficiency and affects data synchronization efficiency. Considering that each piece of data in the same table has the same schema information due to the same data structure, the embodiment of the present application introduces a schema registration center to uniformly manage the schema information of the data circulating in the ETL process. When synchronizing data between upstream and downstream databases, each table in the upstream database registers its schema information with the schema registry and stores it there, along with a schema ID. Subsequently, when serializing each data entry in the table (i.e., upstream data), the serialized message record only needs to carry the schema ID. When deserializing the message record, the downstream second message integration module simply retrieves the schema information (i.e., the target schema information corresponding to the schema ID) from the schema registry based on the schema ID to correctly parse the message record. This makes each message record more concise, increases payload, and makes data transmission more efficient, improving the efficiency of upstream and downstream data synchronization. Furthermore, schema information, as a description of both the data structure and the table structure, allows the downstream second message integration module to detect changes in the table structure in the source database by monitoring changes in the schema information in the schema registry. When the table structure in the upstream database changes, the DDL statement that modifies the downstream database table structure can be generated by parsing the new schema information, which describes the new table structure. This provides an effective data foundation for subsequent synchronization of the table structures in the upstream and downstream databases. At the same time, the schema information contains a description of the primary key of the data table. If the source database change type is to create a new table, the primary key can be synchronized in the target database.

[0072] It should be noted that, in the embodiment of the present application, for each table undergoing data synchronization, the second message integration module maintains a pattern queue for it. When there are multiple tables undergoing data synchronization, the second message integration module maintains multiple pattern queues, such as Figure 5 As shown, pattern queue A, pattern queue B...pattern queue S correspond to different table structures respectively.

[0073] It should be noted that the embodiment of the present application does not limit the specific structure of the second message integration module. Figure 5As shown, it includes a pattern listener, a deserializer, an SQL constructor and a pattern parser, and a pattern queue is preset in the second message integration module, and a data type mapping table and a table structure cache space are preset in the pattern parser. Figure 5 The cooperation between the various module structures in the second message integration module is used to obtain the updated first mode information and generate target DDL statements and target DML statements, providing support for the subsequent synchronization of upstream and downstream database table structures and upstream and downstream data.

[0074] It is understandable that the mode queue is introduced in this embodiment because there may be a gap between the speed at which the mode listener obtains mode information from the mode registration center and the speed at which the deserializer consumes message records from the message queue. For example, when the table structure of the upstream database changes, the new mode has been registered with the mode registration center and the second message integration module has obtained the mode information. However, due to reasons such as excessive load, the deserializer has a delay in consuming message records from the message queue, and the old message record in the message queue (i.e., the second message record generated before the upstream database table structure is changed) has not yet been synchronized. If the table structure of the downstream database is changed at this time, the synchronization of the old data will fail, and the new mode will not be able to deserialize the old message record. Therefore, the introduction of the mode queue can cache the new mode information in this case until the second message record in the message queue is completely synchronized, and then synchronize the first message record and further change the table structure of the downstream database. Based on the pattern queue, after the second message integration module obtains the reference message record carrying the reference pattern identifier from the message queue, it obtains the target pattern information corresponding to the reference pattern identifier from the pattern queue, wherein the target pattern information can parse the reference message record, and the second message integration module generates a target DDL statement based on the target pattern information, and sends the target DDL statement to the downstream database so that the downstream database executes the target DDL statement and completes the synchronization change of the table structure with the upstream database. In addition, the second message integration module parses the reference message record based on the target pattern information to obtain a target DML statement, and sends the target DML statement to the downstream database so that the downstream database executes the target DML statement and completes data synchronization with the upstream database. The present application can perceive the changes in the upstream database table structure in real time without interrupting the ETL process, and automatically synchronize the changes in the target database table structure. Compared with the solution that needs to suspend the ETL process first, modify the table structure of the downstream database to the latest structure consistent with the upstream database table structure, and then resume the ETL process, and during the ETL stop period, the upstream and downstream database tables must suspend service, it can effectively ensure the normal service function of the system while reducing the system operation and maintenance costs.

[0075] Additionally, in some embodiments, Figure 1In step S140, the second message integration module obtains a reference message record carrying a reference mode identifier from the message queue, and obtains target mode information corresponding to the reference mode identifier from the mode queue, including but not limited to the following steps:

[0076] Step S141, obtaining the reference mode information at the head position in the mode queue;

[0077] In step S142, when the reference mode information does not correspond to the reference mode identifier, the reference mode information in the mode queue is deleted, and new reference mode information at the head position in the mode queue is reacquired until the new reference mode information corresponds to the reference mode identifier, and the new reference mode information is determined as the target mode information.

[0078] It is understandable that if there are multiple modes in the same mode queue, the reference mode information at the head of the queue is the mode information being used for deserialization. Figure 5 In the pattern queue S, if the current reference pattern information (i.e., pattern S1) does not correspond to the reference pattern identifier, it means that the current reference pattern information cannot deserialize the latest consumed reference message record, which means that the current old data has been completely synchronized. Delete the reference pattern information in the pattern queue, that is, delete pattern S1 from the pattern queue S, and use the next pattern S2 in the pattern queue S as the target pattern information to parse the new message record, and at the same time trigger the pattern parser to parse the new pattern information.

[0079] Additionally, in some embodiments, Figure 1 In step S140, the second message integration module obtains a reference message record carrying a reference mode identifier from the message queue, and obtains target mode information corresponding to the reference mode identifier from the mode queue, including but not limited to the following steps:

[0080] Step S143, obtaining the reference mode information at the head position in the mode queue;

[0081] Step S144: When the reference pattern information is the only pattern information in the pattern queue and the reference pattern information does not correspond to the reference pattern identifier, the reference message record and the reference pattern information are discarded, and the consumption offset is not submitted to the message queue;

[0082] Step S145 , when new reference mode information at the head position in the mode queue is obtained, and the new reference mode information corresponds to the reference mode identifier, the consumption offset is submitted to the message queue, and the new reference mode information is determined as the target mode information.

[0083] In addition, it should be noted that there are two situations in which the delay difference of the message records obtained by the second message integration module from the message queue exists: Situation 1: The delay in consuming the message record is greater than the delay in obtaining the pattern information that can be used to parse the consumption record; Situation 2: The delay in obtaining the pattern information is greater than the delay in consuming the message record.

[0084] Regarding the first case, when the source database table structure changes, the schema information describing the new table structure arrives at the second message integration module before the new message record after the table structure change. This situation is relatively common because in normal business scenarios, the frequency of table structure adjustment is low, while the amount of data in a single table during data synchronization is large, which makes the link load on the message queue side greater and more prone to data delay. Figure 5 In the pattern queue B, the new pattern information (pattern B2) after the table structure of the upstream database has been changed has arrived at the second message integration module, but the old message records generated before the upstream database table structure change have not been synchronized. At this time, the pattern B1 in the pattern queue B (the second pattern information before the upstream database table structure change) can still deserialize the latest consumed message records. At this time, pattern B2 is first cached in the pattern queue B, and no operation is performed, waiting for the synchronization of the old message records to be completed; when the synchronization of the old message records is completed, reference is made to Figure 5 At this time, pattern S1 at the head of the pattern queue S cannot deserialize the latest consumed message record. At this time, pattern S1 is discarded, and pattern S2 in the pattern queue is used to deserialize the latest consumed message record. When the deserialization is successful, it means that pattern S2 is the new pattern information after the table structure of the upstream database is changed. At this time, the pattern parser is triggered to parse pattern S2 and send the parsing result to the SQL builder. The SQL builder generates a target DDL statement for adjusting the target library table structure based on the parsing result, and sends the target DDL statement to the downstream database, so that the downstream database executes the target DDL statement to complete the synchronization of the table structure change.

[0085] Regarding the second situation, when the table structure of the upstream database changes, the new message record generated arrives at the second message integration module before the new schema information (i.e., the first schema information) describing the change in the source database table structure. At this time, the deserializer of the second message integration module consumes the new message record (i.e., the reference message record). When the reference schema information is the only schema information in the schema queue, and the reference schema information does not correspond to the reference schema identifier, it means that the currently existing schema information (i.e., the reference schema information) in the second message integration module cannot deserialize the reference message record. Moreover, since the new schema information has not yet arrived, the downstream database table structure has not yet completed the change synchronization, and the synchronization of the new message record cannot be performed at this time. Based on this situation, the following adjustments need to be made to the offset submission mechanism for consuming message records by the second message integration module: discard the reference message record and reference pattern information, and do not submit the consumption offset to the message queue; in this way, the next time a message record is consumed from the message queue, the message record that failed to be successfully deserialized last time is consumed according to the consumption offset until the target pattern information arrives; when new reference pattern information is obtained at the head of the pattern queue, and the new reference pattern information corresponds to the reference pattern identifier, it means that the new reference message record can be successfully deserialized, and only then is the consumption offset submitted to the message queue, and the new reference pattern information is determined as the target pattern information.

[0086] It is understandable that the embodiments of the present application propose a pattern queue and offset submission mechanism to achieve real-time and accurate synchronization of the upstream and downstream database table structures. Otherwise, during the real-time data synchronization process, when the table structure of the upstream database changes, if the downstream database table structure changes too early, the data before the upstream database table structure change will be lost, without interrupting the data synchronization process; if the downstream database table structure changes too late, the data after the upstream database table structure change will be lost. This embodiment introduces a pattern queue and offset submission mechanism simultaneously in the ETL process, which can effectively solve this problem.

[0087] In addition, in some embodiments, the second message integration module includes a deserializer, an SQL builder, and a schema parser. Figure 1 In step S150, the second message integration module parses the reference message record based on the target schema information to obtain the target DML statement, including but not limited to the following steps:

[0088] Step S151: The deserializer deserializes the reference message record based on the target schema information to obtain an intermediate message record, and sends the intermediate message record to the SQL builder;

[0089] Step S152: The SQL builder generates a target DML statement based on the intermediate message record.

[0090] Figure 1 In step S160, the second message integration module generates a target DDL statement based on the target schema information, including but not limited to the following steps:

[0091] Step S161: The schema parser obtains a target data mapping relationship based on the target schema information and a preset data type mapping table, wherein the target data mapping relationship indicates a mapping relationship between the target schema information and a table field type corresponding to an upstream database and a table field type corresponding to a downstream database.

[0092] Step S162: The schema parser obtains target change information based on the target schema information and a reference table structure in a preset table structure cache space, wherein the reference table structure is a table structure of the current downstream database.

[0093] Step S163: The schema parser sends the target data mapping relationship and target change information to the SQL builder;

[0094] Step S164: The SQL builder generates a target DDL statement based on the target data mapping relationship and the target change information.

[0095] It should be noted that, referring to Figure 5 The schema parser has two important functional modules: the data type mapping table and the table structure cache. The schema parser maintains a data type mapping table that defines the relationship between database table field types and schema information. This allows downstream database table structure fields to accommodate the range of upstream database table structure data, ensuring accurate synchronization of field data types changed in upstream database tables with downstream database tables. The table structure cache is used to store the current structure information of downstream database tables. When new schema information needs to be parsed, the schema parser compares the new schema information with the reference table structure cached in the table structure cache space to obtain the target change information. Based on the target change information and the target data mapping relationship, it can accurately parse the changed fields and the data types of the changed fields, so that the SQL builder can construct the correct target DDL statement, providing effective support for the subsequent synchronization of table structure changes between upstream and downstream databases; the generation of the target DML statement depends on the parsing result of the deserializer. The deserializer obtains the target schema information corresponding to the reference schema identifier in the schema queue, parses the reference message, and sends the parsing result to the SQL builder. The SQL builder generates the target DML statement based on the parsing result, that is, the intermediate message record, providing effective support for the subsequent data synchronization between upstream and downstream databases.

[0096] In addition, refer to Figure 2In some embodiments, the present application further provides a data synchronization system 200, which includes an upstream database 220, a first message integration module 210, a pattern registration center 240, a message queue 230, a second message integration module 260, and a downstream database 250. The first message integration module 210 includes a first data processing module 211 and a second data processing module 212. The second message integration module 260 includes a third data processing module 262, a fourth data processing module 261, and a fifth data processing module 263.

[0097] The first data processing module 211 is used to integrate upstream data from the upstream database 220. When a change in the table structure of the upstream database 220 is detected, the module obtains first schema information corresponding to the changed table structure and registers the first schema information with the schema registration center 240. The first schema information is used to describe the data structure of the changed table structure.

[0098] The second data processing module 212 is configured to serialize the upstream data to obtain a first message record, and save the first message record to the message queue 230, wherein the first message record carries a first schema identifier, which is used to uniquely identify first schema information of the table structure of the upstream database 220 corresponding to the first message record. The message queue 230 also includes a second message record that is sorted before the first message record, and the second message record carries the schema identifier before the table structure change.

[0099] The third data processing module 262 is configured to, upon monitoring that the pattern registration center 240 has registered new pattern information, obtain first pattern information from the pattern registration center 240 and save the first pattern information to a pattern queue, wherein the first pattern information is the pattern information with the shortest registration time in the pattern registration center 240, and the pattern queue also includes the second pattern information that is sorted before the first pattern information;

[0100] The fourth data processing module 261 is configured to obtain a reference message record carrying a reference mode identifier from the message queue 230, and obtain target mode information corresponding to the reference mode identifier from the mode queue, wherein the reference message record is the first message record or the second message record;

[0101] The fifth data processing module 263 is configured to parse the reference message record based on the target schema information to obtain a target DML statement, generate a target DDL statement based on the target schema information, and send the target DML statement or target DDL statement to the downstream database, wherein the target DML statement is used to modify data in the downstream database, and the target DDL statement is used to modify the table structure of the downstream database;

[0102] The downstream database 250 is used to execute a target DML statement to complete data synchronization with the upstream database, or to execute a target DDL statement to complete table structure change synchronization with the upstream database.

[0103] In some embodiments, the fourth data processing module 261 includes:

[0104] The first data acquisition module 2611 is used to acquire the reference mode information at the head position in the mode queue;

[0105] The first target mode information determination module 2612 is used to delete the reference mode information in the mode queue when the reference mode information does not correspond to the reference mode identifier, and re-acquire new reference mode information at the head position in the mode queue until the new reference mode information corresponds to the reference mode identifier, and determine the new reference mode information as the target mode information.

[0106] In some embodiments, the fourth data processing module 261 includes:

[0107] The first data acquisition module 2611 is used to acquire the reference mode information at the head position in the mode queue;

[0108] The first target mode information determination module 2612 is used to delete the reference mode information in the mode queue when the reference mode information does not correspond to the reference mode identifier, and re-acquire new reference mode information at the head position in the mode queue until the new reference mode information corresponds to the reference mode identifier, and determine the new reference mode information as the target mode information.

[0109] In some embodiments, the fourth data processing module 261 includes:

[0110] The second data acquisition module 2613 is used to acquire the reference mode information at the head position in the mode queue;

[0111] The sixth data processing module 2614 is configured to discard the reference message record and the reference pattern information and not submit the consumption offset to the message queue 230 when the reference pattern information is the only pattern information in the pattern queue and the reference pattern information does not correspond to the reference pattern identifier;

[0112] The second target mode information determination module 2615 is used to submit the consumption offset to the message queue 230 and determine the new reference mode information as the target mode information when new reference mode information at the head position in the mode queue is obtained and the new reference mode information corresponds to the reference mode identifier.

[0113] In some embodiments, the second message integration module 260 includes a deserializer 266, an SQL builder 264, and a schema parser 265. The schema parser 265 includes a mapping relationship determination module 2651, a change information acquisition module 2652, and a data sending module 267.

[0114] The deserializer 266 is used to deserialize the reference message record based on the target schema information to obtain an intermediate message record, and send the intermediate message record to the SQL builder 264;

[0115] The mapping relationship determination module 2651 is used to obtain a target data mapping relationship based on the target schema information and a preset data type mapping table, wherein the target data mapping relationship is used to indicate the mapping relationship between the target schema information and the table field type corresponding to the upstream database 220 and the table field type corresponding to the downstream database 250;

[0116] The change information acquisition module 2652 is used to obtain target change information based on the target schema information and the reference table structure in the preset table structure cache space, wherein the reference table structure is the table structure of the current downstream database 250;

[0117] The data sending module 267 is used to send the target data mapping relationship and target change information to the SQL builder 264;

[0118] The SQL builder 264 is used to generate a target DML statement based on the intermediate message record, and to generate a target DDL statement based on the target data mapping relationship and the target change information.

[0119] It should be noted that the specific implementation of the data synchronization system is basically the same as the specific embodiment of the above-mentioned data synchronization method, and will not be repeated here.

[0120] like Figure 3 As shown, Figure 3 : is a structural diagram of an electronic device provided in one embodiment of the present application. The present invention also provides an electronic device 300, including:

[0121] The processor 310 may be implemented as a general-purpose central processing unit (CPU), a microprocessor, an application-specific integrated circuit (ASIC), or one or more integrated circuits, and is configured to execute relevant programs to implement the technical solutions provided in the embodiments of the present application.

[0122] The memory 320 can be implemented in the form of a read-only memory (ROM), a static storage device, a dynamic storage device, or a random access memory (RAM). The memory 320 can store an operating system and other application programs. When the technical solutions provided in the embodiments of this specification are implemented through software or firmware, the relevant program code is stored in the memory 320 and is called by the processor 310 to execute the data synchronization method of the embodiments of this application.

[0123] Input / output interface 330, used to implement information input and output;

[0124] Communication interface 340, used to implement communication interaction between the apparatus and other devices, which can be achieved through wired means (such as USB, network cable, etc.) or wireless means (such as mobile network, WiFi, Bluetooth, etc.);

[0125] bus 350 , which transmits information between the various components of the device (e.g., processor 310 , memory 320 , input / output interface 330 , and communication interface 340 );

[0126] The processor 310 , the memory 320 , the input / output interface 330 and the communication interface 340 are connected to each other in communication within the device via the bus 350 .

[0127] In addition, an embodiment of the present application further provides a storage medium, which is a computer-readable storage medium and stores a computer program. When the computer program is executed by a processor, the above-mentioned data synchronization method is implemented.

[0128] The memory, as a non-transient computer-readable storage medium, can be used to store non-transient software programs and non-transient computer executable programs. In addition, the memory may include a high-speed random access memory, and may also include a non-transient memory, such as at least one disk storage device, a flash memory device, or other non-transient solid-state storage device. In some embodiments, the memory optionally includes a memory remotely located relative to the processor, and these remote memories can be connected to the processor via a network. Examples of the above-mentioned networks include but are not limited to the Internet, an intranet, a local area network, a mobile communication network and a combination thereof. The device embodiments described above are merely schematic, wherein the units described as separate components may or may not be physically separated, and are located in one place, or may be distributed to multiple network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the present embodiment.

[0129] Those skilled in the art will appreciate that all or some of the steps and systems in the method disclosed above can be implemented as software, firmware, hardware, and appropriate combinations thereof. Some physical components or all physical components can be implemented as software executed by a processor, such as a central processing unit, a digital signal processor, or a microprocessor, or implemented as hardware, or implemented as an integrated circuit, such as an application-specific integrated circuit. Such software can be distributed on a computer-readable medium, and the computer-readable medium can include computer storage media (or non-transitory media) and communication media (or temporary media). As known to those skilled in the art, the term computer storage media is included in any method or technology for storing information (such as computer-readable instructions, data structures, program modules, or other data) and is volatile and non-volatile, removable, and non-removable. Computer storage media includes, but is not limited to, RAM, ROM, EEPROM, flash memory, or other memory technology, CD-ROM, digital versatile disks (DVD), or other optical disk storage, magnetic cassettes, magnetic tapes, disk storage, or other magnetic storage devices, or any other medium that can be used to store desired information and can be accessed by a computer. Furthermore, as is well known to those skilled in the art, communication media typically includes computer-readable instructions, data structures, program modules, or other data in a modulated data signal such as a carrier wave or other transport mechanism, and may include any information delivery media.

[0130] The above is a specific description of the preferred implementation of the present invention, but the present invention is not limited to the above implementation. Those skilled in the art can also make various equivalent modifications or substitutions under the shared conditions that do not violate the spirit of the present invention. These equivalent modifications or substitutions are all included in the scope defined by the claims of the present invention.

Claims

1. A data synchronization method, characterized in that: Applied to a data synchronization system, the data synchronization system includes an upstream database, a first message integration module, a pattern registration center, a message queue, a second message integration module and a downstream database, the method includes: The first message integration module integrates upstream data from the upstream database, and upon detecting that a table structure of the upstream database has changed, obtains first schema information corresponding to the changed table structure, and registers the first schema information with the schema registration center, wherein the first schema information is used to describe the data structure of the changed table structure; The first message integration module serializes the upstream data to obtain a first message record, and saves the first message record to the message queue, wherein the first message record carries a first mode identifier, and the first mode identifier is used to uniquely identify first mode information of the table structure of the upstream database corresponding to the first message record. The message queue also includes a second message record sorted before the first message record, and the second message record carries the mode identifier before the table structure is changed; When the second message integration module monitors that the pattern registration center has registered new pattern information, it obtains the first pattern information from the pattern registration center and saves the first pattern information to a pattern queue, wherein the first pattern information is the pattern information with the shortest registration time in the pattern registration center, and the pattern queue also includes the second pattern information that is sorted before the first pattern information; The second message integration module obtains a reference message record carrying a reference mode identifier from the message queue, and obtains target mode information corresponding to the reference mode identifier from the mode queue, wherein the reference message record is the first message record or the second message record; The second message integration module parses the reference message record based on the target schema information to obtain a target DML statement, wherein the target DML statement is used to modify data in the downstream database; The second message integration module generates a target DDL statement based on the target schema information, wherein the target DDL statement is used to modify the table structure of the downstream database; The second message integration module sends the target DML statement or the target DDL statement to the downstream database; The downstream database executes the target DML statement to complete data synchronization with the upstream database, or executes the target DDL statement to complete table structure change synchronization with the upstream database.

2. The data synchronization method according to claim 1, characterized in that: The second message integration module obtains a reference message record carrying a reference mode identifier from the message queue, and obtains target mode information corresponding to the reference mode identifier from the mode queue, including: Acquire the reference mode information at the head position in the mode queue; When the reference mode information does not correspond to the reference mode identifier, the reference mode information in the mode queue is deleted, and new reference mode information at the head position in the mode queue is reacquired until the new reference mode information corresponds to the reference mode identifier, and the new reference mode information is determined as the target mode information.

3. The data synchronization method according to claim 1, wherein: The second message integration module obtains a reference message record carrying a reference mode identifier from the message queue, and obtains target mode information corresponding to the reference mode identifier from the mode queue, including: Acquire the reference mode information at the head position in the mode queue; When the reference pattern information is the only pattern information in the pattern queue and the reference pattern information does not correspond to the reference pattern identifier, discarding the reference message record and the reference pattern information, and not submitting the consumption offset to the message queue; When new reference mode information at the head position in the mode queue is obtained, and the new reference mode information corresponds to the reference mode identifier, the consumption offset is submitted to the message queue, and the new reference mode information is determined as the target mode information.

4. The data synchronization method according to claim 1, wherein: The second message integration module further includes a deserializer, an SQL builder, and a schema parser. The second message integration module parses the reference message record based on the target schema information to obtain a target DML statement, including: The deserializer deserializes the reference message record based on the target schema information to obtain an intermediate message record, and sends the intermediate message record to the SQL builder; The SQL builder generates the target DML statement based on the intermediate message record; The second message integration module generates a target DDL statement based on the target schema information, including: The schema parser obtains a target data mapping relationship based on the target schema information and a preset data type mapping table, wherein the target data mapping relationship is used to indicate a mapping relationship between the target schema information and a table field type corresponding to the upstream database and a table field type corresponding to the downstream database; The schema parser obtains target change information based on the target schema information and a reference table structure in a preset table structure cache space, wherein the reference table structure is a table structure of the current downstream database; The pattern parser sends the target data mapping relationship and the target change information to the SQL builder; The SQL builder generates the target DDL statement based on the target data mapping relationship and the target change information.

5. A data synchronization system, characterized in that: The data synchronization system includes an upstream database, a first message integration module, a pattern registration center, a message queue, a second message integration module and a downstream database, the first message integration module includes a first data processing module and a second data processing module, the second message integration module includes a third data processing module, a fourth data processing module and a fifth data processing module; The first data processing module is used to integrate upstream data from the upstream database, and when detecting that a table structure of the upstream database has changed, obtain first schema information corresponding to the changed table structure, and register the first schema information with the schema registration center, wherein the first schema information is used to describe the data structure of the changed table structure; The second data processing module is configured to serialize the upstream data to obtain a first message record, and save the first message record to the message queue, wherein the first message record carries a first mode identifier, and the first mode identifier is used to uniquely identify first mode information of the table structure of the upstream database corresponding to the first message record. The message queue also includes a second message record that is sorted before the first message record, and the second message record carries the mode identifier before the table structure is changed. The third data processing module is configured to, upon monitoring that new pattern information has been registered by the pattern registration center, obtain the first pattern information from the pattern registration center and save the first pattern information to a pattern queue, wherein the first pattern information is the pattern information with the shortest registration time in the pattern registration center, and the pattern queue also includes the second pattern information that is sorted before the first pattern information; The fourth data processing module is configured to obtain a reference message record carrying a reference mode identifier from the message queue, and obtain target mode information corresponding to the reference mode identifier from the mode queue, wherein the reference message record is the first message record or the second message record; The fifth data processing module is configured to parse the reference message record based on the target schema information to obtain a target DML statement, generate a target DDL statement based on the target schema information, and send the target DML statement or the target DDL statement to the downstream database, wherein the target DML statement is used to modify data in the downstream database, and the target DDL statement is used to modify a table structure in the downstream database; The downstream database is used to execute the target DML statement to complete data synchronization with the upstream database, or execute the target DDL statement to complete table structure change synchronization with the upstream database.

6. The data synchronization system according to claim 5, characterized in that: The fourth data processing module includes: A first data acquisition module is used to acquire the reference mode information at the head position in the mode queue; The first target mode information determination module is used to delete the reference mode information in the mode queue when the reference mode information does not correspond to the reference mode identifier, and re-acquire new reference mode information located at the head position in the mode queue until the new reference mode information corresponds to the reference mode identifier, and determine the new reference mode information as the target mode information.

7. The data synchronization system according to claim 5, characterized in that: The fourth data processing module includes: A second data acquisition module is used to acquire the reference mode information at the head position in the mode queue; a sixth data processing module, configured to discard the reference message record and the reference pattern information, and not submit the consumption offset to the message queue when the reference pattern information is the only pattern information in the pattern queue and the reference pattern information does not correspond to the reference pattern identifier; The second target mode information determination module is used to submit the consumption offset to the message queue and determine the new reference mode information as the target mode information when new reference mode information located at the head position in the mode queue is obtained and the new reference mode information corresponds to the reference mode identifier.

8. The data synchronization system according to claim 5, characterized in that: The second message integration module further includes a deserializer, an SQL constructor and a schema parser, wherein the schema parser includes a mapping relationship determination module, a change information acquisition module and a data sending module; The deserializer is used to deserialize the reference message record based on the target schema information to obtain an intermediate message record, and send the intermediate message record to the SQL builder; The mapping relationship determination module is configured to obtain a target data mapping relationship based on the target schema information and a preset data type mapping table, wherein the target data mapping relationship is configured to indicate a mapping relationship between the target schema information and a table field type corresponding to the upstream database and a table field type corresponding to the downstream database, respectively; The change information acquisition module is used to obtain target change information based on the target schema information and a reference table structure in a preset table structure cache space, wherein the reference table structure is a table structure of the current downstream database; The data sending module is used to send the target data mapping relationship and the target change information to the SQL builder; The SQL builder is used to generate the target DML statement based on the intermediate message record, and to generate the target DDL statement based on the target data mapping relationship and the target change information.

9. An electronic device, characterized in that: It includes at least one control processor and a memory for communicating with the at least one control processor; the memory stores instructions that can be executed by the at least one control processor, and the instructions are executed by the at least one control processor to enable the at least one control processor to execute the data synchronization method as described in any one of claims 1 to 4.

10. A computer-readable storage medium, characterized in that The computer-readable storage medium stores computer-executable instructions, and the computer-executable instructions are used to enable a computer to execute the data synchronization method according to any one of claims 1 to 4.

Images