Data merging optimization method and device for incremental data migration, equipment and storage medium

Abstract

The invention discloses a data merging optimization method and device for incremental data migration, equipment and a storage medium, and relates to the technical field of databases, and the method comprises the steps: carrying out the data collection of a source end database, and sequentially sending the collected data to a message queue; sequentially pulling the data from the message queue, merging the data, and converting the data into a data packet; performing execution sequence number distribution on the data packets; and based on the execution sequence number, sequentially playing back the data of the data packet to a target end database. According to the method, the effect of concurrent execution of the single-table data is achieved through data merging, the problem that the single-table incremental data cannot be executed in parallel due to the time sequence problem is solved, the method does not depend on the characteristics of the database, can be suitable for data migration synchronization of different database types, and is good in compatibility.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a data merging optimization method, device, equipment and storage medium for incremental data migration. Background Art

[0002] With the development of online Internet services, the concurrent volume of business access has increased exponentially, and the demand for massive data computing and analysis has become more and more common. Traditional stand-alone systems face huge challenges in terms of business, cost and openness. The emergence of distributed databases can better meet these challenges. Therefore, distributed databases are becoming more and more popular, and data interactions with other databases are increasing. Data migration to distributed databases is also becoming more and more common. As the country deepens its independent and controllable strategy, more and more companies are responding to the call to comprehensively accelerate the process of replacing localized databases. In order to ensure the integrity, consistency and standardization of data operations, data migration tools play an important role. How to accurately and efficiently migrate data to a new database is of great significance to distributed databases.

[0003] The existing database data migration methods are generally customized according to the database's own structural characteristics, and can only be applied to databases with fixed architectures; they are not very compatible with other types of databases. There are also methods that analyze the data characteristics of certain business scenarios (electricity field) and then perform corresponding processing based on the data characteristics, but they are not very universal. Summary of the invention

[0004] The technical problem to be solved by the present invention is to overcome the shortcomings of the prior art and provide a data merging optimization method, device, equipment and storage medium for incremental data migration.

[0005] In order to solve the above technical problems, the technical solution of the present invention is as follows: A data merging optimization method for incremental data migration, comprising: Collect data from the source database and send the collected data to the message queue in sequence; Pulling data from the message queue in sequence, merging the data, and converting the data into data packets; Allocating execution sequence numbers to the data packets; The data packets are replayed to the target database in sequence based on the execution sequence number.

[0006] As a preferred solution of the data merging optimization method for incremental data migration of the present invention, the step of sequentially pulling data from the message queue, merging the data, and converting the data into a data packet includes: Check whether the displacement identifier of the data pulled out from the message queue has been changed. If so, the adjacent merging algorithm is used to merge the data and convert the data into a data packet. If not, the head-to-tail merging algorithm is used to merge the data and convert the data into a data packet.

[0007] As a preferred solution of the data merging optimization method for incremental data migration of the present invention, wherein: the use of the adjacent merging algorithm to merge the data and convert the data into a data packet includes: Initialize a map<key,value> Structure, where key corresponds to the unique identifier of the data and value corresponds to the data; Traverse the pulled data. If the corresponding key does not exist in the map, put the corresponding data into the map. If the corresponding key exists in the map, take out the corresponding value data, merge it with the traversed data, and put it back into the map collection. After the data traversal is completed, a new mergeMap collection is initialized, the key is of dml type, the value is a linked list structure, and then the map collection is traversed, and the data is distributed to different linked list collections according to the dml type of the value.

[0008] As a preferred solution of the data merging optimization method for incremental data migration of the present invention, wherein: the merging of data using the head-to-tail merging algorithm to convert the data into a data packet includes: Initialize a map<key,value> Structure, where key corresponds to the unique identifier of the data, and value corresponds to a linked list that stores data internally; Traverse and aggregate the pulled data. If the corresponding key does not exist in the map, initialize a linked list, add the element, and put the linked list into the map; if the corresponding key exists in the map, add the data to the corresponding value linked list; After the data traversal is completed, a new mergeMap collection is initialized, the key is of dml type, the value is a linked list structure, and then the map collection is traversed. If the size of the value linked list corresponding to the key is less than 2, the dml type of the data in the linked list is used as the key and stored in the mergeMap collection; if the size of the value linked list corresponding to the key is greater than or equal to 2, the first and last data in the linked list are merged, and after merging, they are stored in the mergeMap collection according to the dml type.

[0009] As a preferred solution of the data merging optimization method for incremental data migration of the present invention, the allocation of execution sequence numbers to data packets includes: After the data in the mergeMap collection is filled, the data of the same dml type in the map collection is split into data packets of equal data volume, and a sequence number is assigned to each data packet. After the assignment is completed, the data packet is passed to the downstream execution side.

[0010] As a preferred solution of the data merging optimization method for incremental data migration of the present invention, the step of assigning a sequence number to each data packet includes: The sequence numbers of data packets of the same DML type are the same, and the sequence numbers of data packets of different DML types increase in sequence.

[0011] As a preferred solution of the data merging optimization method for incremental data migration of the present invention, the step of replaying the data packet data to the target database in sequence based on the execution sequence number includes: The execution side receives the data packets generated upstream and competes for the sequence number lock according to the sequence number assigned to the data packet; If the serial number lock is obtained successfully, the data of the corresponding package will be executed; if the serial number lock is obtained unsuccessfully, the current execution thread will be suspended; After the thread that successfully acquires the signal lock finishes execution, the sequence lock is released and the suspended execution thread is awakened. After the suspended thread is awakened, it re-participates in the sequence lock competition until the lock is successfully acquired and the obtained data packet data is executed.

[0012] The present invention also provides a data merging optimization device for incremental data migration, comprising: The data collection module is used to collect data from the source database and send the collected data to the message queue in sequence; The data pulling module is used to pull data from the message queue in sequence; The ETL processing module is used to merge the data and convert the data into data packets; An allocation processing module, used for allocating execution sequence numbers to the data packets; The data playback module is used to play back the data packet data to the target end database in sequence based on the execution sequence number.

[0013] The present invention also provides a computer device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein when the processor executes the program, the method described in any of the above-mentioned data merging optimization methods for incremental data migration is implemented.

[0014] The present invention also provides a computer-readable storage medium having a computer program stored thereon, characterized in that: when the program is executed by a processor, the method described in any of the above-mentioned data merging optimization methods for incremental data migration is implemented.

[0015] The beneficial effects of the present invention are: (1) The present invention achieves the effect of concurrent execution of single-table data by merging data, which solves the difficulty that single-table incremental data cannot be executed in parallel due to timing problems. It is independent of the characteristics of the database itself and can be applied to data migration and synchronization of different database types, with good compatibility.

[0016] (2) The incremental data in the present invention not only realizes the parallel synchronization of single-table data, but also ensures the final consistency of the data, and improves the efficiency of data synchronization while maintaining accuracy.

[0017] (3) In the present invention, since single-table data can be executed concurrently, the data synchronization performance is improved, and the synchronization performance will not be slow due to hot table data. BRIEF DESCRIPTION OF THE DRAWINGS

[0018] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the accompanying drawings required for use in the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other accompanying drawings can be obtained based on these accompanying drawings without paying creative labor.

[0019] Figure 1 A schematic diagram of the process flow of the data merging optimization method for incremental data migration provided by the present invention; Figure 2 This is a logical diagram of the execution side competing for sequence number lock; Figure 3 A logical schematic diagram of cross-database data migration in the data merging and optimization device for incremental data migration provided by the present invention; Figure 4 An internal schematic diagram of a playback module in the data merging and optimization device for incremental data migration provided by the present invention; Figure 5 A schematic diagram of a computer device provided by the present invention. DETAILED DESCRIPTION

[0020] In order to make the content of the present invention more clearly understood, the present invention is further described in detail below according to specific implementation modes and in combination with the accompanying drawings.

[0021] Figure 1 A flow chart of a data merging optimization method for incremental data migration provided in an embodiment of the present application. The method comprises the following steps: Step S101: collect data from the source database and send the collected data to the message queue in sequence.

[0022] Specifically, by parsing the source database logs, real-time data changes in the source database can be obtained to achieve data collection of the source database. The parsed data will be sent to the message queue in real time.

[0023] Among them, the message queue has a good peak-cutting effect and can buffer a large amount of hot data generated in a short period of time.

[0024] Step S102: Pull data from the message queue in sequence, merge the data, and convert the data into data packets.

[0025] Specifically, after pulling data from the message queue, different data merging algorithms are selected based on whether a change operation occurs to the unique identifier of the data in the source database.

[0026] If the same identifier exists in a batch of data within a time period and the identifier changes, the adjacent merge algorithm is used to merge the data, that is, multiple operations on the same identifier data in a table within a time period are merged into one operation; if the same identifier exists in a batch of data within a time period and the identifier does not change, the head-to-tail merge algorithm is used to merge the data, that is, the first and last pieces of data are merged according to the identifier.

[0027] The logic of the data merging algorithm is as follows: The data is merged using the adjacent merging algorithm, and the data is converted into a data packet, which includes the following steps: Step a: Initialize a map<key,value> Structure, where key corresponds to the unique identifier of the data and value corresponds to the data.

[0028] Step b: Traverse the pulled data. If the corresponding key does not exist in the map, put the corresponding data into the map; if the corresponding key exists in the map, take out the corresponding value data, merge it with the traversed data, and put it back into the map collection.

[0029] Step c: After the data traversal is completed, initialize a new mergeMap collection with the key as the dml type and the value as a linked list structure. Then traverse the map collection and distribute the data to different linked list collections according to the dml type of the value.

[0030] The data is merged using the head-to-tail merging algorithm. Converting the data into a data packet includes the following steps: Step a′: Initialize a map<key,value> Structure, where key corresponds to the unique identifier of the data, and value corresponds to a linked list that stores data internally; Step b′: Traverse and aggregate the pulled data. If the corresponding key does not exist in the map, initialize a linked list, add the element, and put the linked list into the map; if the corresponding key exists in the map, add the data to the corresponding value linked list; Step c′: After the data traversal is completed, initialize a new mergeMap set, the key is of dml type, the value is a linked list structure, and then traverse the map set. If the size of the value linked list corresponding to the key is less than 2, the dml type of the data in the linked list is used as the key and stored in the mergeMap set; if the size of the value linked list corresponding to the key is greater than or equal to 2, take the first and last data in the linked list and merge them. After merging, store them in the mergeMap set according to the dml type.

[0031] In this embodiment, the unique identifier of the data in the above-mentioned source database includes but is not limited to: a primary key and a unique index.

[0032] Step S103: assigning execution sequence numbers to the data packets.

[0033] Specifically, after the data in the mergeMap collection is filled, the data of the same dml type in the map collection is split into data packets of equal data volume, that is, the amount of data contained in each split data packet is equal. This makes it easier for the downstream execution side to execute and process the data packets.

[0034] After the splitting, a sequence number is assigned to each split data packet. When the sequence number is assigned, the sequence numbers assigned to the data packets of the same DML type are the same, while the sequence numbers of the data packets of different DML types are in an increasing relationship.

[0035] The subsequent downstream execution side will determine the execution order of the data packets based on the sequence numbers assigned to the data packets. It should be noted that the smaller the sequence number assigned to the data packet, the higher its execution priority. Data packets with the same sequence number can be executed concurrently, while among data packets with different sequence numbers, the data packet with a larger sequence number needs to wait until the data packet with a smaller sequence number is executed before it can be executed.

[0036] Step S104: replaying the data packets to the target database in sequence based on the execution sequence number.

[0037] For details, see Figure 2After receiving the data packet generated by the upstream, the execution thread on the execution side competes for the sequence lock according to the sequence number assigned to the data packet. If the sequence lock is successfully obtained, the data packet is executed and the data in the data packet is replayed to the target source database; if the sequence lock is not obtained, the current execution thread is suspended. After the thread that successfully obtains the signal lock is executed, the sequence lock is released and other suspended execution threads are awakened. After waking up, the suspended thread re-participates in the sequence lock competition until the lock is successfully obtained, the obtained data packet data is executed, and the data in the data packet is replayed to the target source database.

[0038] Through the above processing, data of the same DML type in the same batch of data can be executed in parallel, removing the restriction that incremental data of a single table must be executed serially, thereby improving data synchronization performance.

[0039] The present application also provides a data merging optimization device for incremental data migration. The device includes a data acquisition module and a playback module. Figure 3 The acquisition module collects data from the source database, mainly by parsing database logs to obtain real-time data changes. The parsed data will be sent to the message queue in real time. The message queue has a good peak-cutting effect and can buffer a large amount of hot data generated in a short period of time. After that, the playback module pulls data from the message queue in sequence and plays it back to the target database after processing.

[0040] Among them, see Figure 4 ,The playback module includes a data pulling module, an ETL processing module, a distribution processing module and a data playback module.

[0041] Specifically, the data pulling module is used to sequentially pull data within the same time period from the message queue.

[0042] The ETL processing module is used to merge and optimize data and convert data into data packets. Among them, the ETL processing module selects different data merging algorithms based on whether the unique identifier of the data in the source database has changed. If the same identifier exists in a batch of data within a time period and the identifier changes, the adjacent merge algorithm is used to merge the data, that is, multiple operations on the same identifier data in a table within a time period are merged into one operation; if the same identifier exists in a batch of data within a time period and the identifier does not change, the head and tail merge algorithm is used to merge the data, that is, the first and last items of a certain data are merged according to the identifier. After the data is processed and merged by the ETL processing module, the effect of concurrent execution of data in the table can be achieved, which improves the synchronization performance of single-table data.

[0043] The allocation processing module is used to allocate execution sequence numbers to the data packets. Specifically, the sequence numbers of data packets of the same DML type are the same, and the sequence numbers of different DML types are in an increasing relationship.

[0044] The data playback module is used to play back the data packet data to the target database in sequence based on the execution sequence number. After the allocation is completed, the data packet is passed to the data playback module, and the data playback module determines the execution order by judging the sequence number assigned to the data packet. Data packets with the same sequence number can be executed concurrently, and packets with different sequence numbers, the data packet with a larger sequence number needs to wait for the data packet with a smaller sequence number to be executed before it can be executed.

[0045] See also Figure 5 This embodiment also provides a computer device, and the components of the computer device may include but are not limited to: one or more processors or processing units, a system memory, and a bus connecting different system components (including the system memory and the processing unit).

[0046] The term "bus" refers to one or more of several types of bus structures, including a memory bus or memory controller, a peripheral bus, an accelerated graphics port, a processor or a local bus using any of a variety of bus architectures. For example, these architectures include, but are not limited to, the Industry Standard Architecture (ISA) bus, the Micro Channel Architecture (MAC) bus, the Enhanced ISA bus, the Video Electronics Standards Association (VESA) local bus, and the Peripheral Component Interconnect (PCI) bus.

[0047] The computer system / server typically includes a variety of computer system readable media. These media can be any available media that can be accessed by the computer system / server, including volatile and non-volatile media, removable and non-removable media.

[0048] The system memory may include computer system readable media in the form of volatile memory, such as random access memory (RAM) and / or cache memory. The computer device may further include other removable / non-removable, volatile / non-volatile computer system storage media. By way of example only, the storage system may be used to read and write non-removable, non-volatile magnetic media. A disk drive for reading and writing removable non-volatile disks (such as "floppy disks") and an optical disk drive for reading and writing removable non-volatile optical disks (such as CD-ROMs, DVD-ROMs or other optical media) may be provided. In these cases, each drive may be connected to the bus via one or more data medium interfaces. The memory may include at least one program product having a set (e.g., at least one) of program modules that are configured to perform the functions of the various embodiments of the present invention.

[0049] A program / utility having a set (at least one) of program modules may be stored, for example, in a memory, such program modules including, but not limited to, an operating system, one or more application programs, other program modules, and program data, each of which or some combination may include an implementation of a network environment. The program modules generally perform the functions and / or methods of the embodiments described herein.

[0050] The computer device may also communicate with one or more external devices such as a keyboard, a pointing device, a display, etc. Such communication may be performed through an input / output (I / O) interface. Furthermore, the computer device may also communicate with one or more networks (such as a local area network (LAN), a wide area network (WAN) and / or a public network, such as the Internet) through a network adapter.

[0051] The processing unit executes the functions and / or methods described in the embodiments of the present invention by running the programs stored in the system memory.

[0052] The above-mentioned computer program can be set in a computer storage medium, that is, the computer storage medium is encoded with a computer program, and when the program is executed by one or more computers, it enables one or more computers to execute the method flow and / or device operation shown in the above-mentioned embodiments of the present invention.

[0053] With the development of time and technology, the meaning of medium is becoming more and more extensive, and the propagation path of computer programs is no longer limited to tangible media, but can also be downloaded directly from the network, etc. Any combination of one or more computer-readable media can be used. Computer-readable media can be computer-readable signal media or computer-readable storage media. Computer-readable storage media can be, for example, - but not limited to - electrical, magnetic, optical, electromagnetic, infrared, or semiconductor systems, devices or devices, or any combination of the above. More specific examples of computer-readable storage media (non-exhaustive list) include: electrical connections with one or more wires, portable computer disks, hard disks, random access memories (RAM), read-only memories (ROM), erasable programmable read-only memories (EPROM or flash memory), optical fibers, portable compact disk read-only memories (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination of the above. In this document, computer-readable storage media can be any tangible medium containing or storing programs, which can be used by or in combination with instruction execution systems, devices or devices.

[0054] Computer-readable signal media may include a data signal propagated in baseband or as part of a carrier wave, which carries a computer-readable program code. Such propagated data signals may take a variety of forms, including, but not limited to, electromagnetic signals, optical signals, or any suitable combination of the above. Computer-readable signal media may also be any computer-readable medium other than a computer-readable storage medium, which may send, propagate, or transmit a program for use by or in conjunction with an instruction execution system, apparatus, or device.

[0055] Program code embodied on a computer readable medium may be transmitted using any appropriate medium, including but not limited to wireless, wireline, optical fiber cable, RF, etc., or any suitable combination of the foregoing.

[0056] Computer program code for performing the operations of the present invention may be written in one or more programming languages ​​or a combination thereof, including object-oriented programming languages, such as Java, Smalltalk, C++, and conventional procedural programming languages, such as "C" or similar programming languages. The program code may be executed entirely on the user's computer, partially on the user's computer, as a separate software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the case of a remote computer, the remote computer may be connected to the user's computer through any type of network, including a local area network (LAN) or a wide area network (WAN), or may be connected to an external computer (e.g., via the Internet using an Internet service provider).

[0057] In addition to the above embodiments, the present invention may also have other implementation modes; any technical solutions formed by equivalent replacement or equivalent transformation shall fall within the protection scope required by the present invention.

Claims

1. A data merging optimization method for incremental data migration, characterized by: include: Collect data from the source database and send the collected data to the message queue in sequence; Pulling data from the message queue in sequence, merging the data, and converting the data into data packets; Allocating execution sequence numbers to the data packets; The data packets are replayed to the target database in sequence based on the execution sequence number.

2. The data merging optimization method for incremental data migration according to claim 1 is characterized in that: Pulling data from the message queue in sequence, merging the data, and converting the data into a data packet includes: Check whether the displacement identifier of the data pulled out from the message queue has been changed. If so, the adjacent merging algorithm is used to merge the data and convert the data into a data packet. If not, the head-to-tail merging algorithm is used to merge the data and convert the data into a data packet.

3. The data merging optimization method for incremental data migration according to claim 2 is characterized in that: The step of merging the data using the adjacent merging algorithm and converting the data into a data packet comprises: Initialize a map<key,value> Structure, where key corresponds to the unique identifier of the data and value corresponds to the data; Traverse the pulled data. If the corresponding key does not exist in the map, put the corresponding data into the map. If the corresponding key exists in the map, take out the corresponding value data, merge it with the traversed data, and put it back into the map collection. After the data traversal is completed, a new mergeMap collection is initialized, the key is of dml type, the value is a linked list structure, and then the map collection is traversed, and the data is distributed to different linked list collections according to the dml type of the value.

4. The data merging and optimization method for incremental data migration according to claim 2, characterized in that: The method of merging the data using the head-to-tail merging algorithm to convert the data into a data packet includes: Initialize a map<key,value> Structure, where key corresponds to the unique identifier of the data, and value corresponds to a linked list that stores data internally; Traverse and aggregate the pulled data. If the corresponding key does not exist in the map, initialize a linked list, add the element, and put the linked list into the map; if the corresponding key exists in the map, add the data to the corresponding value linked list; After the data traversal is completed, a new mergeMap collection is initialized, the key is of dml type, the value is a linked list structure, and then the map collection is traversed. If the size of the value linked list corresponding to the key is less than 2, the dml type of the data in the linked list is used as the key and stored in the mergeMap collection; if the size of the value linked list corresponding to the key is greater than or equal to 2, the first and last data in the linked list are merged, and after merging, they are stored in the mergeMap collection according to the dml type.

5. The data merging optimization method for incremental data migration according to claim 2 is characterized in that: The performing sequence number allocation on the data packet comprises: After the data in the mergeMap collection is filled, the data of the same dml type in the map collection is split into data packets of equal data volume, and a sequence number is assigned to each data packet. After the assignment is completed, the data packet is passed to the downstream execution side.

6. The data merging optimization method for incremental data migration according to claim 5 is characterized in that: The assigning of a sequence number to each data packet comprises: The sequence numbers of data packets of the same DML type are the same, and the sequence numbers of data packets of different DML types increase in sequence.

7. The data merging optimization method for incremental data migration according to claim 6 is characterized in that: The step of sequentially replaying the data packet data to the target end database based on the execution sequence number includes: The execution side receives the data packets generated upstream and competes for the sequence number lock according to the sequence number assigned to the data packet; If the serial number lock is obtained successfully, the data of the corresponding package will be executed; if the serial number lock is obtained unsuccessfully, the current execution thread will be suspended; After the thread that successfully acquires the signal lock finishes execution, the sequence lock is released and the suspended execution thread is awakened. After the suspended thread is awakened, it re-participates in the sequence lock competition until the lock is successfully acquired and the obtained data packet data is executed.

8. A data merging and optimization device for incremental data migration, characterized in that: include: The data collection module is used to collect data from the source database and send the collected data to the message queue in sequence; The data pulling module is used to pull data from the message queue in sequence; The ETL processing module is used to merge the data and convert the data into data packets; An allocation processing module, used for allocating execution sequence numbers to the data packets; The data playback module is used to play back the data packet data to the target end database in sequence based on the execution sequence number.

9. A computer device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein: When the processor executes the program, the method according to any one of claims 1 to 7 is implemented.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the program is executed by a processor, the method according to any one of claims 1 to 7 is implemented.

Images