System and method of incremental real-time data synchronization gateway

Abstract

The invention discloses an incremental real-time data synchronization gateway system and method, and the system comprises a database drive agent module which carries out the agent, interception and analysis of SQL statements and binding parameters, recognition of data changes, and construction of an internal model for describing the data changes for JDBC drives of various database types; the drive loader module is used for loading database drive agents corresponding to different database types; the data change identification module is used for further analyzing the internal model from the database driving agent module and identifying specific data change; the data storage adaptation module is used for converting the identified specific data change into a format supported by corresponding target storage; and the data change asynchronous sending module is used for sending the converted specific data change to target storage in an asynchronous mode. According to the system and the method, newly adding, deleting and updating operations of database data are captured in real time, and changed data are synchronized to a message queue or Elasticsearch and the like in real time for a downstream system to use.

Description

Technical Field

[0001] The present invention relates to the field of incremental data synchronization of relational databases, and in particular to a system and method for an incremental real-time data synchronization gateway. Background Art

[0002] Incremental synchronization of relational databases is a common and important task. There are many options, but each has certain defects:

[0003] 1. Parse database logs for synchronization. For example, parse MySQL binlog logs and Oracle redo logs, and capture real-time incremental data by analyzing changes such as data addition, deletion, and modification. However, this method faces two major problems:

[0004] a) Log storage time is limited. For example, the default storage time of MySQL binlog is 7 days. If the time limit is exceeded, the earlier logs cannot be obtained and full synchronization cannot be achieved.

[0005] b) For security reasons, the enterprise application environment may prohibit access to database logs, resulting in the inability to obtain incremental data through this channel.

[0006] 2. Add add, delete, and modify triggers on the table to capture data changes and synchronize them to another table. However, this method also has two major drawbacks:

[0007] a) The maintenance cost is high, as triggers need to be created for each table and the changed data needs to be synchronized to the attached table.

[0008] b) Using triggers will put a lot of pressure on database performance.

[0009] 3. Incremental crawling based on timestamps or serial numbers. However, this method also has shortcomings:

[0010] a) In essence, incremental data is obtained through query statements, which puts great pressure on database performance.

[0011] b) For tables without timestamp or sequence number fields, it is impossible to obtain their incremental data. Summary of the invention

[0012] In order to solve the above problems existing in the prior art, the present invention provides a system and method for an incremental real-time data synchronization gateway, which can capture the addition, deletion and update operations of database data in real time, convert the changed data into the target storage format in real time, and asynchronously synchronize it to Elasticsearch or Kafka, etc. for use by downstream systems. It is non-invasive to the existing application code, applicable to various types of relational databases, and has good scalability.

[0013] To achieve the above purpose, the present invention adopts the following technical solutions:

[0014] In one embodiment of the present invention, a system of an incremental real-time data synchronization gateway is proposed, the system comprising:

[0015] The database driver proxy module acts as a proxy for JDBC drivers of various relational database types, intercepts each SQL statement issued by the application, parses the SQL statement and binds parameters, identifies data changes, and builds an internal model to describe data changes;

[0016] The driver loader module is used to load database driver agents corresponding to different relational database types;

[0017] The data change identification module is used to further analyze the internal model from the database driver agent module and identify specific data changes;

[0018] A data storage adapter module, used to convert the identified specific data changes into a format supported by the corresponding target storage;

[0019] The data change asynchronous sending module is used to send the converted specific data changes to the target storage in an asynchronous manner.

[0020] Furthermore, the database driver proxy module provides standard extension interfaces, including:

[0021] SQL statement parsing interface, customize SQL statement parsing logic according to different relational database types;

[0022] Data type mapping interface, which maps field values ​​of different relational database types to internal models;

[0023] Data comparison interface, custom comparison logic, for UPDATE operation, determine the change of field values ​​before and after the update.

[0024] Furthermore, for a new relational database type, it is only necessary to provide a database driver proxy module corresponding to the new relational database type, and to implement dynamic loading of the driver loader module by configuring hot loading.

[0025] In one embodiment of the present invention, a method for synchronizing a gateway with incremental real-time data is also proposed, the method comprising:

[0026] Proxy JDBC drivers for various relational database types, intercept each SQL statement issued by the application, parse SQL statements and bind parameters, identify data changes, and build an internal model to describe data changes;

[0027] Load the database driver agent corresponding to different relational database types;

[0028] Further analyze the internal model from the database-driven proxy module to identify specific data changes;

[0029] Convert the identified specific data changes into a format supported by the corresponding target storage;

[0030] Send the converted specific data changes to the target storage asynchronously.

[0031] Furthermore, the database driver proxy module provides standard extension interfaces, including:

[0032] SQL statement parsing interface, customize SQL statement parsing logic according to different relational database types;

[0033] Data type mapping interface, which maps field values ​​of different relational database types to internal models;

[0034] Data comparison interface, custom comparison logic, for UPDATE operation, determine the change of field values ​​before and after the update.

[0035] Furthermore, for a new relational database type, it is only necessary to provide a database driver proxy module corresponding to the new relational database type, and to implement dynamic loading of the driver loader module by configuring hot loading.

[0036] In one embodiment of the present invention, a computer device is also proposed, including a memory, a processor, and a computer program stored in the memory and executable on the processor, and a method for designing the aforementioned incremental real-time data synchronization gateway when the processor executes the computer program.

[0037] In one embodiment of the present invention, a computer-readable storage medium is also proposed, which stores a computer program for executing the method for synchronizing a gateway with incremental real-time data.

[0038] Beneficial effects:

[0039] 1. The present invention has strong real-time performance and can capture changes in database data in a timely manner, without the timeliness problem of traditional synchronization methods.

[0040] 2. The present invention is non-invasive and does not require any modification to existing application code.

[0041] 3. The present invention has good expansibility and adopts a driver loader module, so it can easily support new database types.

[0042] 4. The asynchronous transmission of the present invention does not affect the normal operation performance of the database. BRIEF DESCRIPTION OF THE DRAWINGS

[0043] Figure 1It is a schematic diagram of the system structure of the incremental real-time data synchronization gateway of the present invention;

[0044] Figure 2 It is a schematic diagram of the method flow of the incremental real-time data synchronization gateway of the present invention;

[0045] Figure 3 It is a schematic diagram of the computer device structure of the present invention. DETAILED DESCRIPTION

[0046] The principles and spirit of the present invention will be described below with reference to several exemplary embodiments. It should be understood that these embodiments are provided only to enable those skilled in the art to better understand and design the present invention, and are not intended to limit the scope of the present invention in any way. On the contrary, these embodiments are provided to make the present disclosure more thorough and complete, and to fully convey the scope of the present disclosure to those skilled in the art.

[0047] Those skilled in the art will appreciate that the embodiments of the present invention may be designed as a system, system, device, method or computer program product. Therefore, the present disclosure may be specifically designed in the following forms, namely: complete hardware, complete software (including firmware, resident software, microcode, etc.), or a combination of hardware and software.

[0048] According to an embodiment of the present invention, a system of incremental real-time data synchronization gateway is proposed, which can capture the addition, deletion and update operations of database data in real time, and synchronize the changed data in real time to a message queue or Elasticsearch, etc. for use by downstream systems.

[0049] The principle and spirit of the present invention are explained in detail below with reference to several representative embodiments of the present invention.

[0050] Figure 1 Schematic diagram of the system structure of the incremental real-time data synchronization gateway of the present invention. Figure 1 As shown, the system includes:

[0051] The database driver proxy module acts as a proxy for JDBC drivers of various relational database types, intercepts each SQL statement issued by the application, parses SQL statements and binds parameters, identifies data changes, and builds an internal model that describes data changes. JDBC (Java Database Connectivity) is a Java API that provides a standard method that allows Java programs to connect to databases and execute SQL statements. This API abstracts different database systems (such as MySQL, Oracle, PostgreSQL, etc.), so developers can use the same code logic to operate different types of databases.

[0052] The driver loader module is used to load database driver agents corresponding to different relational database types.

[0053] The data change identification module is used to further analyze the internal model from the database driver agent module and identify specific data changes, including additions, deletions and updates.

[0054] The data storage adapter module is used to convert the identified specific data changes into the format supported by the corresponding target storage. For example, for a relational database, it is assembled into SQL statements such as insert, update or delete; for elasticsearch, it is assembled into ES DSL statements, etc.

[0055] The data change asynchronous sending module is used to send the converted specific data changes to the target storage in an asynchronous manner.

[0056] The term "module" used above may be a combination of software and / or hardware designed to implement a predetermined function. Although the system described in the following embodiments is preferably designed with software, hardware, or a combination of software and hardware, is also possible and contemplated.

[0057] It should be noted that although several modules of the system of the incremental real-time data synchronization gateway are mentioned in the above detailed description, this division is only exemplary and not mandatory. In fact, according to an embodiment of the present invention, the features and functions of two or more modules described above can be concretized in one module. Conversely, the features and functions of one module described above can be further divided into multiple modules for concretization.

[0058] In order to explain the incremental real-time data synchronization gateway system more clearly, a specific embodiment is described below. However, it should be noted that this embodiment is only for better illustrating the present invention and does not constitute an improper limitation to the present invention.

[0059] Example:

[0060] 1. Database driver proxy module

[0061] The main function of the database driver proxy module is to proxy the JDBC drivers of various relational database types and capture data changes when executing SQL statements.

[0062] The database driver proxy module intercepts each SQL statement issued by the application through the JDBC proxy mechanism. It parses the SQL statement and binds the parameters, identifies the addition, deletion and update operations, and the specific data changes. After appropriate processing, the data changes are passed to the data change asynchronous sending module.

[0063] The implementation details are as follows:

[0064] (1) Use Java's dynamic proxy mechanism (such as JDK dynamic proxy or Javassist bytecode manipulation library) to proxy the JDBC drivers provided by different database vendors.

[0065] (2) Obtain instances of key classes in the JDBC driver through the reflection mechanism and dynamically modify the values ​​of their member variables to implement proxy.

[0066] (3) Rewrite the implementation of various database operation interface methods (such as execute, executeQuery, etc.) in the JDBC driver. The details are as follows:

[0067] a) Before the method is executed, the type of SQL statement (SELECT / INSERT / UPDATE / DELETE, etc.) is parsed.

[0068] b) Parse metadata such as table names and column information in SQL statements.

[0069] c) Parse the value of the bound parameter.

[0070] d) For INSERT statements, record the inserted data.

[0071] e) For UPDATE statements, obtain the data before and after the update and compare them, and record the changes.

[0072] f) For DELETE statements, record the deleted data.

[0073] g) Build internal models or data structures that describe data changes.

[0074] h) Call the native JDBC driver code to execute SQL statements and return the actual operation results to the application.

[0075] i) The constructed internal model describing data changes is passed to the data change identification module for further processing.

[0076] Extension mechanism:

[0077] In order to support new database types, this module provides a standard extension interface. Developers only need to implement the interface for the new database driver, which mainly includes:

[0078] (1) SQL statement parsing interface: Depending on the type of relational database, the syntax of SQL statements may vary, requiring customized statement parsing logic.

[0079] (2) Data type mapping interface: When mapping database field values ​​to internal models, it is necessary to process the field value encoding methods of different relational database types.

[0080] (3) Data comparison interface: For UPDATE operations, custom comparison logic may be required to determine the changes in field values ​​before and after the update.

[0081] Through this extension mechanism, the database driver proxy module can easily support various mainstream relational databases.

[0082] 2. Driver loader module

[0083] The main function of the driver loader module is to load the corresponding driver agent module according to different relational database types, so as to support different relational database types.

[0084] Workflow:

[0085] (1) Startup initialization

[0086] When the system starts, the driver loader module will perform initialization operations. The main steps include:

[0087] a) Load the configuration file

[0088] This module reads configuration information from configuration files or other configuration sources, including a list of database types that need to be supported, the driver proxy module class name corresponding to each type, etc.

[0089] b) Register the driver agent module

[0090] Traverse the configured database type list, use the reflection mechanism to create an instance of the module according to the corresponding driver proxy module class name, and register the instance in the driver proxy module mapping table.

[0091] c) Create a ClassLoader for each database type

[0092] To prevent class loading conflicts between different driver agent modules, this module creates a separate ClassLoader for each database type. ClassLoader is a mechanism used in Java programs to dynamically load and link class files. It is responsible for reading class definitions from the file system, network, or other sources, and converting them into class objects at runtime, while ensuring that each class is a single instance. ClassLoader provides isolation for class loading, allowing different class loaders to load different class versions of the same name, and is a key component for Java to implement modularization and hot deployment. These ClassLoaders are all subloaders of the current ClassLoader.

[0093] (2) Loading the JDBC driver

[0094] When an application needs to access a certain type of database, it calls the standard JDBC DriverManager to load the driver of that type. JDBC DriverManager is a factory class in Java that manages JDBC drivers and provides database connections. It is responsible for identifying and loading the appropriate JDBC driver so that the application can interact with the database through the JDBC API. At this time, the driver loader module intercepts the request and performs the following operations:

[0095] a) Find the corresponding ClassLoader according to the database type

[0096] Look for the ClassLoader corresponding to this type in the ClassLoader mapping table created at startup.

[0097] b) Use the corresponding ClassLoader to load the JDBC driver

[0098] The real JDBC driver class is loaded through the found ClassLoader.

[0099] c) Get the driver proxy module instance

[0100] Get the driver proxy module instance corresponding to this type from the driver proxy module mapping table.

[0101] d) Create a JDBC driver proxy object

[0102] The proxy logic of the driver proxy module is used to create a JDBC driver proxy instance object.

[0103] e) Return the proxy object to the application

[0104] All subsequent operations of the application on the JDBC driver will be intercepted and data changes will be captured.

[0105] (3) Dynamically loading new drivers

[0106] If an application attempts to access a new database type that is not currently supported by the system, the module does the following:

[0107] a) Find the driver agent module information corresponding to this type in the configuration source

[0108] If it exists, the module instance is dynamically created and registered in the mapping table.

[0109] b) Create a new dedicated ClassLoader

[0110] Create a new child ClassLoader using the current ClassLoader.

[0111] c) Use the new ClassLoader to load the JDBC driver of this type

[0112] Through this dynamic loading method, the system can support new database types without restarting or modifying code.

[0113] Scalability design:

[0114] The design of this module makes the entire system highly extensible in supporting new database types. It only requires providing a driver proxy module corresponding to the new type, and by configuring hot loading, it can be integrated into the existing system at low cost.

[0115] 3. Data change identification module

[0116] The main function of the data change identification module is to further analyze and process the internal model passed by the database driver agent module, identify the specific data change details, and prepare for the subsequent asynchronous sending of data changes.

[0117] This module receives the internal model from the database driver proxy module, parses it, and obtains detailed information such as table name, operation type (insert / update / delete), record primary key value, all field values, etc. Depending on the operation type, the data change is processed differently and a standardized data change description object is output.

[0118] Implementation details:

[0119] (1) Analyze the internal model of data changes and obtain basic information

[0120] a) Table name, schema name.

[0121] b) Operation type.

[0122] c) Record the primary key value.

[0123] d) All metadata such as field names, field values, field types, etc.

[0124] (2) Different processing according to the operation type

[0125] a) Insert operation

[0126] Get the insert statements, including single insert, multiple batch insert, and insert of data from stock tables.

[0127] b) Update operation

[0128] Get updated SQL statements, including update, merge, replace and other operations involving data changes.

[0129] c) Delete operation

[0130] Get all field values ​​of deleted records, including delete, truncate and other data deletion statements.

[0131] (3) Call the extended interface for personalized customization

[0132] a) Using the extended interface, developers can customize the record data assembly logic.

[0133] b) For example, according to the database and table partitioning rules, the real physical table can be parsed from the complete table name.

[0134] c) Use the priority of the extended interface to determine the execution order when multiple extended logics exist at the same time.

[0135] 4. Data storage adapter module

[0136] Adapt the changed data according to the target storage type to which the changed data is sent.

[0137] For example, synchronize the changed data of MySQL to ClickHouse, convert the inserted, updated, and deleted data of MySQL into the SQL syntax of ClickHouse, and generate SQL statements suitable for execution in the ClickHouse database.

[0138] If you want to synchronize the changed data in MySQL to Elasitcsearch, since ES does not support inserting and updating data through SQL statements, you need to parse the changed data from the MySQL SQL statements, convert them into Elasitcsearch's DSL operations, and perform data operations on the ES side.

[0139] Extension mechanism:

[0140] Record data assembly extension: For different business scenarios, users can customize the assembly method of change record data by implementing extension interfaces.

[0141] Record construction extension: Users can modify or add other additional information through the extension interface during the process of constructing insert / update / delete record description objects.

[0142] Through the above extension mechanism, the data change identification module can be compatible with various database systems and business scenarios, providing higher flexibility.

[0143] 5. Data change asynchronous sending module

[0144] The main responsibility of the data change asynchronous sending module is to send the standardized data change records output by the previous data change identification module to a message queue (such as Kafka) or Elasticsearch and other destinations in an asynchronous manner to avoid affecting normal database operations.

[0145] This module needs to efficiently send out a large number of data change records, and implement error retry, flow control and other strategies to ensure reliability during the sending process. At the same time, it needs to support multiple sending targets, including Elasticsearch, Kafka message queues, etc.

[0146] Core implementation:

[0147] (1) Transmitter interface

[0148] Define a unified sender interface, and different sending targets implement this interface.

[0149] The interface methods include initialization, sending data change records, retrying sending, etc.

[0150] (2) Sending thread pool

[0151] Use the thread pool to send data change records asynchronously.

[0152] The thread pool size is configurable. It is recommended to use a bounded thread pool to avoid excessive thread creation.

[0153] (3) Failure retry mechanism

[0154] After sending fails, the failed record is stored in the memory queue.

[0155] A separate asynchronous thread periodically retries sending failed records.

[0156] The number of failed retries is configurable, and error logs are captured if the limit is exceeded.

[0157] (4) Flow Control Mechanism

[0158] Monitor the size of the send buffer queue and implement flow control by dynamically adjusting the size of the send thread pool.

[0159] When the buffer queue is too large, increase the number of threads to speed up the sending.

[0160] When the buffer queue is too small, reduce the number of threads to avoid wasting resources.

[0161] (5) Data persistence

[0162] Supports persisting received data change records to the file system as a backup.

[0163] New files can be automatically generated at hourly or daily granularity, and new files can be generated in a rolling manner.

[0164] The file format is pluggable, such as json, protobuf, etc.

[0165] (6) Multi-objective achievement

[0166] Implement senders for a variety of different sending targets, such as Kafka, relational databases, and Elasticsearch.

[0167] Scalability:

[0168] By implementing the sender interface, it is very convenient to expand new sending targets, such as sending data to MySQL, ElasticSearch engines, etc. Each sender can be loaded and unloaded by plugging and unplugging. When multiple senders are enabled, data will be copied to multiple targets.

[0169] The module is highly flexible and scalable, able to meet the needs of different scenarios, and is the key to ensuring high performance and high reliability of the entire system.

[0170] Based on the same inventive concept, the present invention also proposes a method for synchronizing a gateway with incremental real-time data. The implementation of the method can refer to the implementation of the above system, and the repeated parts will not be repeated.

[0171] Figure 2 This is a flow chart of the incremental real-time data synchronization gateway method of the present invention. Figure 2 As shown, the method includes:

[0172] 1. Start: The starting point of the process.

[0173] 2. Synchronize all data: Before the system is started for the first time, synchronize all the database data to the target storage.

[0174] 3. The driver loader loads the database driver agent: The driver loader module loads the corresponding database driver agent.

[0175] 4. The application performs database operations: The application performs normal database operations.

[0176] 5. Database-driven proxy module intercepts SQL: The proxy module intercepts SQL statements and prepares for parsing.

[0177] 6. Data change identification module parses SQL and bind variables: further parses SQL statements and bind variables to identify data changes.

[0178] 7. The data storage adapter module converts the data into the target format: the identified data changes are converted into the format required by the target storage.

[0179] 8. The data change asynchronous sending module sends data to the target storage: The data change asynchronous sending module sends the converted data to the target storage asynchronously.

[0180] 9. End: The end point of the data synchronization process.

[0181] It should be noted that, although the operations of the method of the present invention are described in a specific order in the above embodiments and the accompanying drawings, this does not require or imply that the operations must be performed in the specific order, or that all the operations shown must be performed to achieve the desired results. Additionally or alternatively, some steps may be omitted, multiple steps may be combined into one step, and / or one step may be decomposed into multiple steps.

[0182] Based on the above invention concept, Figure 3 As shown, the present invention also proposes a computer device 200, including a memory 210, a processor 220, and a computer program 230 stored in the memory 210 and executable on the processor 220, and a method for designing the aforementioned incremental real-time data synchronization gateway when the processor 220 executes the computer program 230.

[0183] Based on the aforementioned inventive concept, the present invention further proposes a computer-readable storage medium, which stores a computer program for executing the aforementioned incremental real-time data synchronization gateway method.

[0184] The system and method of the incremental real-time data synchronization gateway proposed in the present invention have the following highlights:

[0185] 1. It has strong real-time performance and can capture changes in database data in a timely manner. It does not have the timeliness problem of traditional synchronization methods.

[0186] 2. Non-invasive, no modification is required to the existing application code.

[0187] 3. Good scalability. The driver loader module can easily support new database types.

[0188] 4. Asynchronous transmission does not affect the normal operation performance of the database.

[0189] Although the spirit and principle of the present invention have been described with reference to several specific embodiments, it should be understood that the present invention is not limited to the disclosed specific embodiments, and the division of various aspects does not mean that the features in these aspects cannot be combined to benefit, and such division is only for the convenience of expression. The present invention is intended to cover various modifications and equivalent arrangements contained in the spirit and scope of the attached claims.

[0190] Regarding the limitation of the protection scope of the present invention, those skilled in the art should understand that, based on the technical solution of the present invention, various modifications or variations that can be made by those skilled in the art without creative work are still within the protection scope of the present invention.

Claims

1. A system for an incremental real-time data synchronization gateway, characterized in that: The system includes: The database driver proxy module acts as a proxy for JDBC drivers of various relational database types, intercepts each SQL statement issued by the application, parses the SQL statement and binds parameters, identifies data changes, and builds an internal model to describe data changes; The driver loader module is used to load database driver agents corresponding to different relational database types; The data change identification module is used to further analyze the internal model from the database driver agent module and identify specific data changes; A data storage adapter module, used to convert the identified specific data changes into a format supported by the corresponding target storage; The data change asynchronous sending module is used to send the converted specific data changes to the target storage in an asynchronous manner.

2. The incremental real-time data synchronization gateway system according to claim 1, characterized in that: The database driver proxy module provides a standard extension interface, including: SQL statement parsing interface, customize SQL statement parsing logic according to different relational database types; Data type mapping interface, which maps field values ​​of different relational database types to internal models; Data comparison interface, custom comparison logic, for UPDATE operation, determine the change of field values ​​before and after the update.

3. The incremental real-time data synchronization gateway system according to claim 1, characterized in that: For a new relational database type, it is only necessary to provide a database driver proxy module corresponding to the new relational database type, and implement dynamic loading of the driver loader module by configuring hot loading.

4. A method for synchronizing a gateway with incremental real-time data, characterized in that: The method includes: Proxy JDBC drivers for various relational database types, intercept each SQL statement issued by the application, parse SQL statements and bind parameters, identify data changes, and build an internal model to describe data changes; Load the database driver agent corresponding to different relational database types; Further analyze the internal model from the database-driven proxy module to identify specific data changes; Convert the identified specific data changes into a format supported by the corresponding target storage; Send the converted specific data changes to the target storage asynchronously.

5. The method of incremental real-time data synchronization gateway according to claim 4, characterized in that: The database driver proxy module provides a standard extension interface, including: SQL statement parsing interface, customize SQL statement parsing logic according to different relational database types; Data type mapping interface, which maps field values ​​of different relational database types to internal models; Data comparison interface, custom comparison logic, for UPDATE operation, determine the change of field values ​​before and after the update.

6. The method of incremental real-time data synchronization gateway according to claim 4, characterized in that: For a new relational database type, it is only necessary to provide a database driver proxy module corresponding to the new relational database type, and implement dynamic loading of the driver loader module by configuring hot loading.

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

8. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program for executing the method according to any one of claims 4 to 6.

Images