Data processing method, storage medium and device of database

Abstract

This invention provides a database data processing method, storage medium, and device. The data processing method includes parsing transactions to obtain parsing results; filtering transactions to be processed based on the parsing results; determining whether the transactions to be processed are target transactions based on the statements in the transactions to be processed; adding a preset processing mark to a preset position of the target transaction and packaging it; and sending the packaged target transaction to the target end. This database data processing method of the present invention pre-marks transaction attributes, enabling the early identification of transactions containing preset processing marks during the source-end parsing process. By marking these transactions on the transaction attributes, when such transactions are transmitted to the target end, the target end can select a faster data entry method based on these attributes, thereby accelerating data processing efficiency.

Description

Technical Field

[0001] This invention relates to the field of databases, and in particular to a database processing method, storage medium, and device. Background Technology

[0002] Real-time data synchronization using data synchronization software is divided into three stages. The first stage involves initializing and loading existing data to establish a baseline for data synchronization. The second stage uses this baseline as a benchmark for incremental data synchronization. The third stage periodically compares and verifies the source and target data to ensure no data is lost during the synchronization process. The second and third stages often run in parallel for extended periods.

[0003] In the second phase of incremental data synchronization, a common real-time data replication technique involves analyzing database logs (REDO LOG) to obtain incremental data, thereby achieving real-time data synchronization. This technique parses the source database's online log or archive log to obtain data additions, deletions, and changes. These changes are then converted into a specific message format within the synchronization software, transaction by transaction, and sent to the target synchronization software via its proprietary transport protocol. Finally, the target synchronization software reconstructs the obtained transaction logs into SQL statements supported by the target database and executes them on the target database to achieve real-time data synchronization and maintain data consistency between the source and target databases.

[0004] For some analytical database systems, operations such as INSERT INTO SELECT or CREATETABLE AS are common. These operations are characterized by generating very large transactions in a short period of time, all of which are newly added data.

[0005] Data synchronization software is a general-purpose software that needs to handle INSERT, UPDATE, DELETE, and DDL operations simultaneously. When inserting data into the target database, it cannot predict the attributes of the next statement (INSERT, UPDATE, DELETE), so it often processes statements one by one. If it encounters a very large transaction generated by operations such as INSERT INTO SELECT or CREATE TABLE AS, even if these operations are parsed into a transaction, they will be treated as a series of independent INSERT operations.

[0006] For tables with tens of millions of records, copying using INSERT INTO SELECT might only take a few minutes at the source, but could take several hours to insert into the target database. Summary of the Invention

[0007] One object of the present invention is to provide a database data processing method, storage medium and device that can solve any of the above problems.

[0008] A further objective of this invention is to improve data processing efficiency.

[0009] Specifically, the present invention provides a data processing method for a database, the data processing method comprising:

[0010] The transaction is parsed to obtain the parsing result;

[0011] The tasks to be processed are selected based on the analysis results;

[0012] Determine whether the pending transaction is the target transaction based on the statements in the pending transaction;

[0013] Add a preset processing mark to the preset location of the target transaction and package it;

[0014] Send the packaged target transaction to the target end.

[0015] Furthermore, the steps for filtering out the transactions to be processed based on the parsing results include:

[0016] Determine whether the parsed result contains the setting flags used for submission;

[0017] If so, then the transaction is determined to be a pending transaction.

[0018] Furthermore, the transaction to be processed includes multiple statements. The steps to determine whether the transaction to be processed is the target transaction based on the statements in the transaction to be processed include:

[0019] Add correct tags to the transactions being processed;

[0020] Determine the statement types of the statements within the transaction to be processed sequentially from beginning to end; and

[0021] Update the flags of the transaction to be processed based on the statement type;

[0022] If the tail marker of the pending transaction is still correctly marked, then the pending transaction is identified as the target transaction.

[0023] Furthermore, the steps for updating the flags of the transaction to be processed based on the statement type include:

[0024] If the statement is a data manipulation statement, then determine whether the data manipulation statement is an insert statement;

[0025] If so, then determine whether the table name involved in the data manipulation statement is the same as the table name in the previous data manipulation statement executed by the database;

[0026] If not, mark the transaction to be processed as an error;

[0027] If so, the transaction to be processed is correctly marked and recorded as the target transaction.

[0028] Furthermore, after determining that the data manipulation statement is an insert statement, the following steps are also included:

[0029] If the data manipulation statement is not an insert statement, mark the transaction to be processed as an error.

[0030] Furthermore, the steps for updating the flags of the transaction to be processed based on the statement type include:

[0031] If the statement is a data definition statement, then an error flag is set for the transaction to be processed.

[0032] Furthermore, the preset position is the header position of the target transaction;

[0033] Furthermore, the default processing flag is the batch processing flag.

[0034] According to another aspect of the present invention, a machine-readable storage medium is also provided, on which a machine-executable program is stored, which, when executed by a processor, implements the data processing method of any of the above-described database clusters.

[0035] According to another aspect of the present invention, a computer device is also provided, including a memory, a processor, and a machine-executable program stored in the memory and running on the processor, wherein the processor executes the machine-executable program to implement the data processing method of any of the above-described database clusters.

[0036] The database data processing method of the present invention includes parsing transactions to obtain parsing results; filtering transactions to be processed based on the parsing results; determining whether the transactions to be processed are target transactions based on the statements in the transactions to be processed; adding preset processing marks to preset positions of the target transactions and packaging them; and sending the packaged target transactions to the target end. When data synchronization software encounters extremely large transactions involving only newly added data in a single table, the data processing method of the present invention can fully utilize the batch data import tools or similar methods provided by the database to quickly load such transactions during the data import process on the target end, thereby improving the synchronization performance of the data synchronization software.

[0037] The above and other objects, advantages and features of the present invention will become more apparent to those skilled in the art from the following detailed description of specific embodiments of the invention in conjunction with the accompanying drawings. Attached Figure Description

[0038] The following sections will describe some specific embodiments of the invention in detail by way of example and not limitation, with reference to the accompanying drawings. The same reference numerals in the drawings denote the same or similar parts or portions. Those skilled in the art should understand that these drawings are not necessarily drawn to scale. In the drawings:

[0039] Figure 1 This is a schematic diagram illustrating the steps of a database data processing method according to an embodiment of the present invention;

[0040] Figure 2 This is a schematic diagram illustrating the addition of start / commit identifiers to SQL statements in a database data processing method according to another embodiment of the present invention;

[0041] Figure 3 This is a schematic diagram illustrating transaction serialization in a database data processing method according to yet another embodiment of the present invention;

[0042] Figure 4 This is a schematic diagram illustrating the addition of a preset processing flag to a transaction in the form of an SQL statement in a database data processing method according to another embodiment of the present invention;

[0043] Figure 5 A schematic diagram illustrating the addition of a preset processing flag to a serialized transaction in a database data processing method according to another embodiment of the present invention;

[0044] Figure 6 This is a schematic diagram illustrating the steps of a database data processing method according to another embodiment of the present invention;

[0045] Figure 7 This is a schematic diagram illustrating the steps of a database data processing method according to another embodiment of the present invention;

[0046] Figure 8 This is a schematic diagram illustrating the steps of a database data processing method according to another embodiment of the present invention;

[0047] Figure 9 This is a schematic block diagram of a machine-readable storage medium according to an embodiment of the present invention; and

[0048] Figure 10 This is a schematic block diagram of a computer device according to an embodiment of the present invention. Detailed Implementation

[0049] Figure 1This is a schematic diagram illustrating the steps of a database data processing method according to an embodiment of the present invention. The database data processing method of this embodiment can generally be applied to logical synchronization software. Logical synchronization software typically synchronizes data in units of transactions. When parsing data, the source end of the logical synchronization software packages each transaction together and transmits it to the target end. The packaging behavior is quite flexible; it can be achieved by adding BEGIN / COMMIT markers before and after SQL (Structured Query Language) statements, serializing the entire transaction into binary, or using structured expressions such as XML (eXtensible Markup Language) or JSON (JavaScript Object Notation). For example, adding markers to SQL statements... Figure 2 As shown. The solution for transaction serialization is to define a transaction structure, such as an object in an object-oriented language, or a structure description similar to Protobuf encoding, like... Figure 3 As shown, operation 1 can be DML or DDL, and the operation type can be INSERT, UPDATE, DELETE, etc.

[0050] The steps of the database data processing method in this embodiment include:

[0051] Step S202: parse the transaction to obtain the parsing result. The parsing result includes setting flags for commit, such as the COMMIT flag.

[0052] Step S204: Filter the transactions to be processed based on the parsing results.

[0053] Step S206: Determine whether the transaction to be processed is the target transaction based on the statements in the transaction to be processed.

[0054] Step S208: Add a preset processing flag to the preset position of the target transaction and package it. The preset position is the header position of the target transaction. The preset processing flag is a batch processing flag, such as the BATCH_INSERT flag. Figure 4 This is a schematic diagram illustrating the addition of markers to transactions in the form of SQL statements in a database data processing method according to another embodiment of the present invention. Figure 5 A schematic diagram illustrating the addition of a marker to a serialized transaction in a database data processing method according to another embodiment of the present invention.

[0055] Step S210: Send the packaged target transaction to the target end.

[0056] The database data processing method of the present invention pre-marks transaction attributes, so that transactions containing preset processing tags can be identified in advance during the source parsing process and marked on the transaction attributes. When such transactions are transmitted to the target end, the target end can select a faster data entry method based on the attribute.

[0057] When the target end receives a transaction from the source end, it can perform operations based on the markers in the transaction header. For transactions marked with preset processing markers in the transaction header, it can be determined that the current transaction involves all operations corresponding to the preset processing markers on the same table. For such transactions, the target end can perform targeted optimizations.

[0058] Figure 6 This is a schematic diagram illustrating the steps of a database data processing method according to another embodiment of the present invention. Figure 6 As shown, the step of filtering the transactions to be processed based on the parsing results in the database data processing method of this embodiment includes:

[0059] Step S302: Determine whether the parsed result contains a setting flag for submission. The setting flag can be a COMMIT flag. If yes, proceed to step S302.

[0060] Step S304: Determine that the transaction is a pending transaction.

[0061] Figure 7 This is a schematic diagram illustrating the steps of a database data processing method according to another embodiment of the present invention. Figure 7 As shown, the step of determining whether the transaction to be processed is the target transaction based on the statements in the transaction to be processed in the data processing method of this embodiment includes:

[0062] Step S402: Add a TRUE flag to the transaction to be processed;

[0063] Step S404: Determine the statement types of the statements within the transaction to be processed sequentially from beginning to end. Statement types include DML statements and DDL statements.

[0064] Step S406: Update the flags of the transaction to be processed according to the statement type.

[0065] In step S408, if the marker is still the correct marker when updating to the end of the pending transaction, then the pending transaction is determined to be the target transaction. If the target transaction consists entirely of INSERT statements on the same table, it is determined that the target transaction was generated by INSERT INTO SELECT or CREATE TABLE AS.

[0066] Figure 8This is a schematic diagram illustrating the steps of a database data processing method according to another embodiment of the present invention. Figure 8 As shown, the step in this embodiment of determining whether the transaction to be processed is the target transaction based on the statement type includes:

[0067] Step S504: Determine that the statement is a data manipulation statement, which is a DML statement.

[0068] Step S506: Determine if the data manipulation statement is an INSERT statement. If yes, proceed to step S508. If no, the DML statement is an UPDATE or DELETE statement, and an error flag (FALSE) is added.

[0069] Step S508: Determine whether the table name involved in the data manipulation statement is the same as the table name in the previous data manipulation statement. If yes, proceed to step S510; otherwise, proceed to step S512.

[0070] Step S510: Mark the transaction to be processed as TRUE.

[0071] Step S512: Mark the transaction to be processed as faulty (FALSE).

[0072] The step of updating the mark of the transaction to be processed according to the statement type in the data processing method of the database in this embodiment further includes: if the statement is a data definition statement, then the transaction to be processed is marked as an error.

[0073] When the target end receives a transaction from the source end, it can operate based on the markers in the transaction header. For transactions marked with BATCH_INSERT, it can be determined that the current transaction is the target transaction, performing all INSERT operations on the same table. The target end can then perform targeted optimizations for this target transaction. For MySQL databases, the target transaction can be pre-generated into a CSV (Comma-Separated Values) file, and then the MySQL LOAD DATA INFILE command can be used to insert the data. For Oracle, the target transaction can be pre-generated into a CSV file, and then the Oracle sqlldr tool can be used to insert the data. For PostgreSQL, the target transaction can be pre-generated into a CSV file, and then the PostgreSQL copy statement can be used to insert the data. In other embodiments, external tools can be used for data insertion, and the transaction does not need to be converted to a CSV file. JDBC's batch insertion features can be used directly. For example, PostgreSQL's JDBC itself provides a special COPY interface, which can insert data in a data stream manner; SQL Server provides a bulk copy interface, etc. In some other embodiments, the target end can use a parallel insertion method. Because the target transaction is marked with a pre-defined processing flag, the target synchronization software knows from the beginning that the target transaction is for the same table and is a huge transaction consisting entirely of insert statements. Since the insert operations themselves do not have a specific order, the target transaction can be split when the target database is being accessed, for example, into 5 parts, and 5 threads or processes can be generated to access the 5 parts of data in parallel.

[0074] To prevent negative optimization, the target end of the synchronization software also needs to introduce a threshold to determine how many operations in the target transaction should trigger the aforementioned data insertion scheme. For example, if a transaction contains only two statements, writing it to a CSV file and then calling an external tool is obviously less efficient than directly executing two INSERT statements. However, if a transaction contains 20 million data entries, then writing to a CSV file and then calling an external loading tool can bring considerable performance gains.

[0075] The solution proposed in this embodiment can not only be applied to INSERT INTO SELECT statements and CREATE TABLEAS statements, but also produce beneficial effects on transactions that consist entirely of INSERT statements on the same table.

[0076] This embodiment also provides a machine-readable storage medium and a computer device. Figure 9 This is a schematic diagram of a machine-readable storage medium according to an embodiment of the present invention. Figure 10 This is a schematic diagram of a computer device according to an embodiment of the present invention.

[0077] The machine-readable storage medium 40 stores a machine-executable program 41 thereon, which, when executed by a processor, implements the data processing method of the database in any of the above embodiments.

[0078] Computer device 50 may include memory 520, processor 510, and machine-executable program 41 stored on memory 520 and running on processor 510, and processor 510 implements the data processing method of the database in any of the above embodiments when executing machine-executable program 41.

[0079] It should be noted that the logic and / or steps represented in the flowchart or otherwise described herein, for example, can be considered as a sequenced list of executable instructions for implementing logical functions, and can be specifically implemented in any machine-readable storage medium for use by, or in conjunction with, an instruction execution system, apparatus or device (such as a computer-based system, a processor-based system or other system that can fetch and execute instructions from, an instruction execution system, apparatus or device).

[0080] For the purposes of this embodiment, the machine-readable storage medium 40 can be any means capable of containing, storing, communicating, propagating, or transmitting a program for use by or in conjunction with an instruction execution system, apparatus, or device. More specific examples (a non-exhaustive list) of computer-readable media include: an electrical connection having one or more wires (electronic device), a portable computer disk drive (magnetic device), random access memory (RAM), read-only memory (ROM), erasable and editable read-only memory (EPROM or flash memory), fiber optic devices, and portable optical disc read-only memory (CDROM). Furthermore, the computer-readable medium 40 can even be paper or other suitable media on which the program can be printed, since the program can be obtained electronically, for example, by optically scanning the paper or other medium, followed by editing, interpreting, or otherwise processing as necessary, and then stored in a computer memory.

[0081] It should be understood that various parts of the present invention can be implemented using hardware, software, firmware, or a combination thereof. In the above embodiments, multiple steps or methods can be implemented using software or firmware stored in memory and executed by a suitable instruction execution system.

[0082] Computer device 50 can be, for example, a server, desktop computer, laptop computer, tablet computer, or smartphone. In some examples, computer device 50 can be a cloud computing node. Computer device 50 can be described in the general context of computer system executable instructions (such as program modules) executed by a computer system. Typically, program modules can include routines, programs, object programs, components, logic, data structures, etc., that perform specific tasks or implement specific abstract data types. Computer device 50 can be implemented in a distributed cloud computing environment where tasks are performed by remote processing devices linked through a communication network. In a distributed cloud computing environment, program modules can reside on local or remote computing system storage media, including storage devices.

[0083] Computer device 50 may include a processor 510 adapted to execute stored instructions and a memory 520 that provides temporary storage space for the operation of said instructions during operation. The processor 510 may be a single-core processor, a multi-core processor, a computing cluster, or any other configuration. The memory 520 may include random access memory (RAM), read-only memory, flash memory, or any other suitable storage system.

[0084] The processor 510 can also be linked via a system interconnect to a display interface suitable for connecting the computer device 50 to a display device. The display device may include a display screen that is a built-in component of the computer device 50. The display device may also include an external computer monitor, television, or projector connected to the computer device 50. Furthermore, a network interface controller (NIC) may be adapted to connect the computer device 50 to a network via a system interconnect. In some embodiments, the NIC may use any suitable interface or protocol (such as an Internet Minicomputer System Interface) to transmit data. The network may be a cellular network, a radio network, a wide area network (WAN), a local area network (LAN), or the Internet, etc. Remote devices can connect to the computing device via the network.

[0085] The flowcharts provided in this embodiment are not intended to indicate that the operations of the method will be performed in any particular order, or that all operations of the method are included in all every case. Furthermore, the method may include additional operations. Within the scope of the technical concept provided by the method in this embodiment, additional variations can be made to the above method. Therefore, those skilled in the art should recognize that although many exemplary embodiments of the invention have been shown and described in detail herein, many other variations or modifications conforming to the principles of the invention can be directly determined or derived from the disclosure of the invention without departing from the spirit and scope of the invention. Therefore, the scope of the invention should be understood and considered to cover all such other variations or modifications.

Claims

1. A database data processing method, comprising: The transaction is parsed to obtain the parsing result; The transactions to be processed are selected based on the parsing results; Determine whether the transaction to be processed is the target transaction based on the statements in the transaction to be processed; A preset processing mark is added to the preset location of the target transaction, and then packaged. Send the packaged target transaction to the target end; The step of filtering the transactions to be processed based on the parsing results includes: Determine whether the parsing result contains a setting flag for submission; If so, then the transaction is determined to be a pending transaction; The pending transaction includes multiple statements, and the step of determining whether the pending transaction is the target transaction based on the statements in the pending transaction includes: Add the correct flag to the pending transaction; The statement types within the pending transaction are determined sequentially from beginning to end; and Update the flag of the pending transaction according to the statement type; When updating to the end of the pending transaction, if the tag is still the correct tag, then the pending transaction is determined to be the target transaction.

2. The data processing method according to claim 1, wherein, The steps for updating the flag of the pending transaction based on the statement type include: If the statement is a data manipulation statement, then determine whether the data manipulation statement is an insert statement; If so, determine whether the table name involved in the data manipulation statement is the same as the table name in the previous data manipulation statement; If not, then mark the pending transaction as an error; If so, the pending transaction is correctly marked.

3. The data processing method according to claim 2, wherein, Following the step of determining that the data manipulation statement is an insert statement, the following is also included: If the data manipulation statement is not an insert statement, the transaction to be processed is marked with the error flag.

4. The data processing method according to claim 3, wherein, The step of updating the marker of the pending transaction according to the statement type includes: If the statement is a data definition statement, then the error mark is applied to the transaction to be processed.

5. The data processing method according to claim 1, wherein, The preset position is the header position of the target transaction.

6. The data processing method according to claim 1, wherein, The preset processing flag is a batch processing flag.

7. A machine-readable storage medium having a machine-executable program stored thereon, the machine-executable program, when executed by a processor, implementing the data processing method for the database according to any one of claims 1 to 6.

8. A computer device comprising a memory, a processor, and a machine-executable program stored in the memory and running on the processor, wherein the processor, when executing the machine-executable program, implements a data processing method for a database according to any one of claims 1 to 6.

Images