Real-time whole-database synchronization method, system, device, and storage medium based on FlinkCDC

Abstract

The present invention belongs to the technical field of database synchronization, and specifically discloses a real-time whole-database synchronization method, system, device, and storage medium based on FlinkCDC. The method pulls the real-time log change data of the entire business database through the FlinkCDC component to extract the table name and field name corresponding to the current log change data. It determines whether the corresponding table name and field name exist in the downstream database and the special table name set that does not need to be synchronized. If not, the corresponding field information is queried from the business database to obtain the adapted field information of the downstream database through mapping and conversion. Then, through constructing statement instructions, the downstream database is caused to create tables and fields to meet the entry conditions of the current log change data. Finally, the current log change data of the business database is synchronized to the downstream database. This method can efficiently realize the real-time data stream and batch synchronization from the business database to the downstream database, greatly improving the convenience and timeliness of database whole-database synchronization.

Description

Technical Field

[0001] The present invention belongs to the technical field of database synchronization, and in particular relates to a real-time whole-database synchronization method, system, device, and storage medium based on FlinkCDC. Background Art

[0002] With the advancement of technology and business, enterprises are increasingly integrating diverse systems. These systems are distributed across various platforms, using different database types, and generating an ever-increasing amount of data. This fragmented system structure makes real-time correlation of diverse data challenging. However, business needs necessitate unified computation and processing of large amounts of data across various systems. Traditional batch synchronization methods (such as scheduled synchronization using traditional ETL tools) significantly impact data real-time performance and require manual creation of downstream database tables. Conventional real-time synchronization solutions (such as scheduled synchronization using traditional ETL tools combined with Flink CDC real-time synchronization of key tables, or scheduled synchronization using traditional ETL tools combined with Otter real-time synchronization of key tables) fail to effectively improve global data real-time performance. Furthermore, when numerous tables require real-time synchronization, each table must be processed individually, requiring manual creation of downstream database tables, placing significant pressure on database connections. Therefore, an efficient solution for real-time, entire-database synchronization is urgently needed. Summary of the Invention

[0003] The purpose of the present invention is to provide a real-time whole-database synchronization method, system, device and storage medium based on FlinkCDC to solve the above-mentioned problems existing in the prior art.

[0004] In order to achieve the above object, the present invention adopts the following technical solutions:

[0005] First, we provide a real-time whole-database synchronization method based on FlinkCDC, including:

[0006] Use the FlinkCDC component to pull real-time log change data from the entire business database.

[0007] Extract the corresponding table name and field name in the current log change data, and compare the extracted table name and field name with the table name and field name in the cached downstream database to determine whether the table name and field name corresponding to the current log change data exist in the downstream database;

[0008] When it is determined that the table name and field name corresponding to the current log change data do not exist in the downstream database, the extracted table name is compared with each table name in the pre-stored special table name set that does not need to be synchronized, to determine whether the table name corresponding to the current log change data exists in the special table name set that does not need to be synchronized;

[0009] When it is determined that the table name corresponding to the current log change data does not exist in the special table name set that does not need to be synchronized, the field information of the table corresponding to the table name is queried from the business database according to the table name corresponding to the current log change data, and the field information includes the field type and field length;

[0010] Perform mapping conversion processing on the field information according to the set mapping conversion relationship to obtain the field type and field length corresponding to the downstream database;

[0011] Assemble the build statement according to the field type and field length corresponding to the downstream database, and transmit the build statement to the downstream database so that the downstream database executes the build statement to create tables and fields;

[0012] The current log change data is transmitted to the downstream database after the table and field are created, so that the downstream database writes the current log change data according to the constructed table and field.

[0013] In one possible design, the method further includes:

[0014] When it is determined that the table name and field name corresponding to the current log change data exist in the downstream database, the current log change data is directly transmitted to the downstream database.

[0015] In one possible design, the method further includes:

[0016] When it is determined that the table name corresponding to the current log change data exists in the special set of table names that do not need to be synchronized, the synchronization process of the current log change data is terminated.

[0017] In a possible design, performing mapping conversion processing on the field information according to the set mapping conversion relationship to obtain the field type and field length corresponding to the downstream database includes:

[0018] The field information is imported into a preset field mapping information template to perform mapping conversion processing on the field type and field length, so as to obtain the field type and field length corresponding to the downstream database.

[0019] In one possible design, before comparing the extracted table names and field names with the table names and field names in the cached downstream database, the method further includes:

[0020] Query and load the table names and corresponding field names in the downstream database and cache them in Flink's MapState.

[0021] In one possible design, after transmitting the construction statement to the downstream database so that the downstream database executes the construction statement to create the table and the field, the method further includes:

[0022] Update the corresponding table and field names after the downstream database executes the build statement to create the table and field into Flink's MapState.

[0023] In a possible design, when querying and obtaining field information from a business database, a connection is made to the business database through a Java database connection; when transmitting a constructed statement to a downstream database, a connection is made to the downstream database through a Java database connection.

[0024] Secondly, a real-time whole-database synchronization system based on FlinkCDC is provided, including a pull unit, a first comparison unit, a second comparison unit, a query unit, a conversion unit, a building unit, and a transmission unit, wherein:

[0025] The pull unit is used to pull real-time log change data from the entire business database through the FlinkCDC component;

[0026] The first comparison unit is used to extract the table name and field name corresponding to the current log change data, and compare the extracted table name and field name with the table name and field name in the cached downstream database to determine whether the table name and field name corresponding to the current log change data exist in the downstream database;

[0027] The second comparison unit is used to compare the extracted table name with each table name in a pre-stored set of special table names that do not need to be synchronized, when it is determined that the table name and field name corresponding to the current log change data do not exist in the downstream database, to determine whether the table name corresponding to the current log change data exists in the set of special table names that do not need to be synchronized;

[0028] A query unit, configured to query and obtain field information of a table corresponding to the current log change data from a business database based on the table name corresponding to the current log change data when determining that the table name corresponding to the current log change data does not exist in the set of special table names that do not need to be synchronized, wherein the field information includes a field type and a field length;

[0029] A conversion unit, configured to perform mapping conversion processing on the field information according to a set mapping conversion relationship to obtain a field type and field length corresponding to a downstream database;

[0030] The assembly unit is used to assemble a construction statement according to the field type and field length corresponding to the downstream database, and transmit the construction statement to the downstream database so that the downstream database executes the construction statement to create a table and a field;

[0031] The transmission unit is used to transmit the current log change data to the downstream database after the table and field are built, so that the downstream database writes the current log change data according to the built table and field.

[0032] Third, we provide a real-time whole-database synchronization device based on FlinkCDC, including:

[0033] a memory for storing instructions;

[0034] A processor is configured to read instructions stored in the memory and execute, according to the instructions, any one of the FlinkCDC-based real-time whole-database synchronization methods described in the first aspect.

[0035] In a fourth aspect, a computer-readable storage medium is provided, wherein the computer-readable storage medium stores instructions that, when executed on a computer, cause the computer to execute any one of the methods described in the first aspect. Also provided is a computer program product containing instructions that, when executed on a computer, cause the computer to execute any one of the FlinkCDC-based real-time whole-database synchronization methods described in the first aspect.

[0036] Beneficial effects: The present invention uses the FlinkCDC component to pull the real-time log change data of the entire business database to extract the table name and field name corresponding to the current log change data, and determines whether the corresponding table name and field name exist in the downstream database and the special table name set that does not need to be synchronized. If not, the corresponding field information is queried from the business database to map and convert to obtain the adaptation field information of the downstream database. Then, through the construction statement instruction, the downstream database is caused to create tables and fields to meet the entry conditions of the current log change data. Finally, the current log change data of the business database is synchronized to the downstream database, which can efficiently realize the real-time data stream and batch synchronization from the business database to the downstream database, greatly improving the convenience and timeliness of the whole database synchronization. BRIEF DESCRIPTION OF THE DRAWINGS

[0037] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the embodiments or the description of the prior art. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0038] Figure 1 Schematic diagram of the steps of the method in Example 1 of the present invention;

[0039] Figure 2 Schematic diagram of the system structure in Example 2 of the present invention;

[0040] Figure 3 This is a schematic diagram of the structure of the device in Example 3 of the present invention. DETAILED DESCRIPTION

[0041] It should be noted that the description of these embodiments is intended to help understand the present invention, but does not constitute a limitation of the present invention. The specific structural and functional details disclosed herein are merely intended to describe exemplary embodiments of the present invention. However, the present invention may be embodied in a variety of alternative forms, and should not be construed as being limited to the embodiments set forth herein.

[0042] It should be understood that, unless otherwise specified or limited, the term "connection" should be understood in a broad sense. For example, it can mean a fixed connection, a detachable connection, or an integral connection; it can mean a mechanical connection or an electrical connection; it can mean a direct connection or an indirect connection through an intermediate medium; it can mean internal communication between two components. Those skilled in the art will be able to understand the specific meanings of the above terms in the embodiments based on specific circumstances.

[0043] In the following description, certain details are provided to facilitate a complete understanding of the example embodiments. However, one of ordinary skill in the art will appreciate that the example embodiments can be practiced without these specific details. For example, a system may be shown in a block diagram to avoid obscuring the example with unnecessary detail. In other embodiments, well-known processes, structures, and techniques may be shown without unnecessary detail to avoid obscuring the embodiment.

[0044] Example 1:

[0045] This embodiment provides a real-time whole-database synchronization method based on FlinkCDC, which can be applied to corresponding database synchronization processors, such as Figure 1 As shown, the method includes the following steps:

[0046] S1. Use the FlinkCDC component to pull real-time log change data from the entire business database.

[0047] In specific implementation, the built-in features of the FlinkCDC component can be used to directly pull the real-time log change data of the entire business database through a single connection. The real-time log change data of the business database pulled is one by one for subsequent synchronous stream processing.

[0048] S2. Extract the table name and field name corresponding to the current log change data, and compare the extracted table name and field name with the table name and field name in the cached downstream database to determine whether the table name and field name corresponding to the current log change data exist in the downstream database.

[0049] In specific implementation, when performing stream processing, the table names and field names corresponding to the current log change data are first extracted. These extracted table names and field names are then compared with those in the cached downstream database to determine whether the table names and field names corresponding to the current log change data exist in the downstream database. Before comparing the extracted table names and field names with those in the cached downstream database, the corresponding default initialization program can be used to query and load each table name and corresponding field name in the downstream database into Flink's MapState cache. This load must exclude table names from the predefined set of special table names that do not need to be synchronized.

[0050] S3. When it is determined that the table name and field name corresponding to the current log change data do not exist in the downstream database, the extracted table name is compared with each table name in the pre-stored special table name set that does not need to be synchronized to determine whether the table name corresponding to the current log change data exists in the special table name set that does not need to be synchronized.

[0051] During specific implementation, if it is determined that the table name and field name corresponding to the current log change data exist in the downstream database, the current log change data is directly transmitted to the downstream database. For example, the current log change data can be forwarded to FlinkSink, and the current log change data is written to the downstream database through FlinkSink. If it is determined that the table name and field name corresponding to the current log change data do not exist in the downstream database, the extracted table name is compared with each table name in the pre-stored special table name set that does not need to be synchronized to determine whether the table name corresponding to the current log change data exists in the special table name set that does not need to be synchronized. The special table name set that does not need to be synchronized can be pre-defined according to actual needs.

[0052] S4. When it is determined that the table name corresponding to the current log change data does not exist in the special table name set that does not need to be synchronized, the field information of the table corresponding to the table name is queried from the business database according to the table name corresponding to the current log change data, and the field information includes the field type and field length.

[0053] During specific implementation, if it is determined that the table name corresponding to the current log change data exists in the special set of table names that do not need to be synchronized, the synchronization processing of the current log change data can be terminated. If it is determined that the table name corresponding to the current log change data does not exist in the special set of table names that do not need to be synchronized, the field information of the table corresponding to the table name is obtained from the business database according to the table name corresponding to the current log change data, and the field information includes the field type and field length. When the field information of the table corresponding to the table name is obtained from the business database according to the table name corresponding to the current log change data, the business database can be connected through Java Database Connectivity (JDBC for short), and then the field information of the table corresponding to the table name is queried from the business database.

[0054] S5. Perform mapping conversion processing on the field information according to the set mapping conversion relationship to obtain the field type and field length corresponding to the downstream database.

[0055] In specific implementation, after obtaining the field information of the corresponding table in the business database, the field information can be mapped and converted according to the set mapping conversion relationship, including: importing the field information into a preset field mapping information template to perform mapping and conversion processing on the field type and field length to obtain the field type and field length corresponding to the downstream database. The field mapping information template is a field type conversion tool defined within the Flink program and can be pre-set according to the business database type and downstream database type.

[0056] S6. Assemble a construction statement according to the field type and field length corresponding to the downstream database, and transmit the construction statement to the downstream database so that the downstream database executes the construction statement to create a table and a field.

[0057] In specific implementations, after the aforementioned field information conversion process, the corresponding field information of the downstream database obtained after conversion can be assembled into a build statement using a pre-configured program. This is then connected to the downstream database via Java Database Connectivity (JDBC). The assembled build statement is then transmitted to the downstream database, allowing it to execute the build statement to create tables and fields. After the downstream database completes the table and field creation process, the corresponding table and field names are updated in Flink's MapState.

[0058] S7. The current log change data is transmitted to the downstream database after the table and field are created, so that the downstream database writes the current log change data according to the created table and field.

[0059] In specific implementation, after the downstream database executes the construction statement to build the table and completes the construction of the field, the current log change data is transmitted to the downstream database, so that the downstream database writes the current log change data according to the constructed table and field. For example, the current log change data can be forwarded to FlinkSink, and the current log change data is written to the downstream database through FlinkSink.

[0060] This embodiment uses the inherent features of FlinkCDC to directly pull real-time log change data from the entire business database through a single connection, reducing the connection pressure on the source database. It also automatically synchronizes the table structure through preset field mapping relationships, achieving real-time data stream and batch synchronization. This greatly improves the convenience and timeliness of whole-database synchronization. When a new table is created in the downstream database, the new table is automatically loaded into the downstream database through the status, without restarting the new task.

[0061] Example 2:

[0062] This embodiment provides a real-time whole-database synchronization system based on FlinkCDC, such as Figure 2 As shown, it includes a pulling unit, a first comparison unit, a second comparison unit, a query unit, a conversion unit, a building unit and a transmission unit, wherein:

[0063] The pull unit is used to pull real-time log change data from the entire business database through the FlinkCDC component;

[0064] The first comparison unit is used to extract the table name and field name corresponding to the current log change data, and compare the extracted table name and field name with the table name and field name in the cached downstream database to determine whether the table name and field name corresponding to the current log change data exist in the downstream database;

[0065] The second comparison unit is used to compare the extracted table name with each table name in a pre-stored set of special table names that do not need to be synchronized, when it is determined that the table name and field name corresponding to the current log change data do not exist in the downstream database, to determine whether the table name corresponding to the current log change data exists in the set of special table names that do not need to be synchronized;

[0066] a query unit, configured to query and obtain field information of a table corresponding to the current log change data from a business database based on the table name corresponding to the current log change data when determining that the table name corresponding to the current log change data does not exist in the set of special table names that do not need to be synchronized, wherein the field information includes a field type and a field length;

[0067] A conversion unit, configured to perform mapping conversion processing on the field information according to a set mapping conversion relationship to obtain a field type and field length corresponding to a downstream database;

[0068] The assembly unit is used to assemble a construction statement according to the field type and field length corresponding to the downstream database, and transmit the construction statement to the downstream database so that the downstream database executes the construction statement to create a table and a field;

[0069] The transmission unit is used to transmit the current log change data to the downstream database after the table and field are built, so that the downstream database writes the current log change data according to the built table and field.

[0070] Example 3:

[0071] This embodiment provides a real-time whole-database synchronization device based on FlinkCDC, such as Figure 3 As shown, at the hardware level, it includes:

[0072] Data interface, used to establish data connection between the processor and business database and downstream database;

[0073] a memory for storing instructions;

[0074] The processor is configured to read the instructions stored in the memory and execute the real-time whole-database synchronization method based on FlinkCDC in Example 1 according to the instructions.

[0075] Optionally, the device further includes an internal bus. The processor, memory, and data interface may be interconnected via the internal bus, which may be an ISA (Industry Standard Architecture) bus, a PCI (Peripheral Component Interconnect) bus, or an EISA (Extended Industry Standard Architecture) bus. The bus may be divided into an address bus, a data bus, a control bus, and the like.

[0076] The memory may include, but is not limited to, random access memory (RAM), read-only memory (ROM), flash memory, first-in-first-out memory (FIFO) and / or first-in-last-out memory (FILO), etc. The processor may be a general-purpose processor, including a central processing unit (CPU), a network processor (NP), etc.; it may also be a digital signal processor (DSP), an application-specific integrated circuit (ASIC), a field-programmable gate array (FPGA), or other programmable logic devices, discrete gate or transistor logic devices, or discrete hardware components.

[0077] Example 4:

[0078] This embodiment provides a computer-readable storage medium having instructions stored thereon. When the instructions are executed on a computer, the computer executes the FlinkCDC-based real-time whole-database synchronization method described in Example 1. The computer-readable storage medium refers to a data storage medium, which may include, but is not limited to, a floppy disk, an optical disk, a hard disk, a flash memory, a USB flash drive, and / or a memory stick. The computer may be a general-purpose computer, a dedicated computer, a computer network, or other programmable system.

[0079] This embodiment further provides a computer program product comprising instructions, which, when executed on a computer, causes the computer to execute the FlinkCDC-based real-time whole-database synchronization method in Example 1. The computer may be a general-purpose computer, a special-purpose computer, a computer network, or other programmable system.

[0080] Finally, it should be noted that the above description is only a preferred embodiment of the present invention and is not intended to limit the scope of protection of the present invention. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the present invention shall be included in the scope of protection of the present invention.

Claims

1. The real-time whole-database synchronization method based on FlinkCDC is characterized by: include: Use the FlinkCDC component to pull real-time log change data from the entire business database. Extract the corresponding table name and field name in the current log change data, and compare the extracted table name and field name with the table name and field name in the cached downstream database to determine whether the table name and field name corresponding to the current log change data exist in the downstream database; When it is determined that the table name and field name corresponding to the current log change data do not exist in the downstream database, the extracted table name is compared with each table name in the pre-stored special table name set that does not need to be synchronized, to determine whether the table name corresponding to the current log change data exists in the special table name set that does not need to be synchronized; When it is determined that the table name corresponding to the current log change data does not exist in the special table name set that does not need to be synchronized, the field information of the table corresponding to the table name is queried from the business database according to the table name corresponding to the current log change data, and the field information includes the field type and field length; Perform mapping conversion processing on the field information according to the set mapping conversion relationship to obtain the field type and field length corresponding to the downstream database; Assemble the build statement according to the field type and field length corresponding to the downstream database, and transmit the build statement to the downstream database so that the downstream database executes the build statement to create tables and fields; The current log change data is transmitted to the downstream database after the table and field are created, so that the downstream database writes the current log change data according to the constructed table and field.

2. The real-time whole-database synchronization method based on FlinkCDC according to claim 1 is characterized in that: The method further comprises: When it is determined that the table name and field name corresponding to the current log change data exist in the downstream database, the current log change data is directly transmitted to the downstream database.

3. The real-time whole-database synchronization method based on FlinkCDC according to claim 1 is characterized in that: The method further comprises: When it is determined that the table name corresponding to the current log change data exists in the special set of table names that do not need to be synchronized, the synchronization process of the current log change data is terminated.

4. The real-time whole-database synchronization method based on FlinkCDC according to claim 1 is characterized in that: The mapping conversion process is performed on the field information according to the set mapping conversion relationship to obtain the field type and field length corresponding to the downstream database, including: The field information is imported into a preset field mapping information template to perform mapping conversion processing on the field type and field length, so as to obtain the field type and field length corresponding to the downstream database.

5. The real-time whole-database synchronization method based on FlinkCDC according to claim 1 is characterized in that: Before comparing the extracted table names and field names with the table names and field names in the cached downstream database, the method further includes: Query and load the table names and corresponding field names in the downstream database and cache them in Flink's MapState.

6. The real-time whole-database synchronization method based on FlinkCDC according to claim 5 is characterized in that: After transmitting the construction statement to the downstream database so that the downstream database executes the construction statement to create a table and a field, the method further includes: Update the corresponding table and field names after the downstream database executes the build statement to create the table and field into Flink's MapState.

7. The real-time whole-database synchronization method based on FlinkCDC according to claim 1 is characterized in that: When querying and obtaining field information from the business database, connect to the business database through the Java database connection method; when transmitting the constructed statement to the downstream database, connect to the downstream database through the Java database connection method.

8. The real-time whole-database synchronization system based on FlinkCDC is characterized by: It includes a pulling unit, a first comparison unit, a second comparison unit, a query unit, a conversion unit, a building unit and a transmission unit, wherein: The pull unit is used to pull real-time log change data from the entire business database through the FlinkCDC component; The first comparison unit is used to extract the table name and field name corresponding to the current log change data, and compare the extracted table name and field name with the table name and field name in the cached downstream database to determine whether the table name and field name corresponding to the current log change data exist in the downstream database; The second comparison unit is used to compare the extracted table name with each table name in a pre-stored set of special table names that do not need to be synchronized, when it is determined that the table name and field name corresponding to the current log change data do not exist in the downstream database, to determine whether the table name corresponding to the current log change data exists in the set of special table names that do not need to be synchronized; A query unit, configured to query and obtain field information of a table corresponding to the current log change data from a business database based on the table name corresponding to the current log change data when determining that the table name corresponding to the current log change data does not exist in the set of special table names that do not need to be synchronized, wherein the field information includes a field type and a field length; A conversion unit, configured to perform mapping conversion processing on the field information according to a set mapping conversion relationship to obtain a field type and field length corresponding to a downstream database; The assembly unit is used to assemble a construction statement according to the field type and field length corresponding to the downstream database, and transmit the construction statement to the downstream database so that the downstream database executes the construction statement to create a table and a field; The transmission unit is used to transmit the current log change data to the downstream database after the table and field are built, so that the downstream database writes the current log change data according to the built table and field.

9. Real-time whole-database synchronization device based on FlinkCDC, characterized by: include: a memory for storing instructions; A processor is configured to read the instructions stored in the memory and execute the real-time whole-database synchronization method based on FlinkCDC according to any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that The computer-readable storage medium stores instructions, and when the instructions are executed on a computer, the computer executes the real-time whole-database synchronization method based on FlinkCDC according to any one of claims 1 to 7.

Images