Method, system and device for synchronizing data in whole database in real time, medium and program product

Abstract

The invention provides a method, system and device for synchronizing data in a whole database in real time, a medium and a program product. The method comprises the steps that pre-configured service database connection information and downstream storage database connection information are obtained; connecting business databases of subordinate institutions through the FlinkCDC component according to the business database information, and obtaining table names, in-table field names and in-field data stored in each business database; and performing connection through the FlinkCDC component according to the downstream storage database connection information, and storing the table name, the in-table field name and the in-field data in the downstream storage database according to a preset format. The method and the device are simple and convenient to operate, the table data in the multiple business databases can be extracted in the whole database by one task and stored in the downstream storage database according to the preset format, and meanwhile, the subordinate institutions to which the business databases belong can be identified through the extended fields, so that the institution sources of the data stored in the downstream storage database are distinguished.

Description

TECHNICAL FIELD

[0001] The present disclosure relates to the technical field of database, and particularly relates to a method, system, device, medium and program product for real-time whole-database synchronization data. BACKGROUND

[0002] With the development of enterprises, large enterprises have their own systems and databases in their subordinate institutions. When all subordinate institutions' same business data is displayed, the data is distributed in each subordinate institution, which brings great difficulty to data display and decision support. However, the actual business requirements need to aggregate and display the data of each subordinate institution. Therefore, it is necessary to synchronize the data of each subordinate institution to the same database.

[0003] The traditional batch synchronization data uses ETL (Extract-Transform-Load) tools to extract data at a fixed time. Real-time data synchronization uses FlinkCDC (Full Incremental Integrated Reading Data Integration Framework) technology. The common technical solution for whole-database synchronization data is the extraction of ETL tools batch data + FlinkCDC technology for real-time data collection of each table. By extracting all tables in the database, the effect of whole-database synchronization is achieved. One FlinkCDC task needs to be started for each table synchronization. Since many technical tools are used, a large amount of development work is required to achieve whole-database synchronization. When all tables are expanded with special field attributes, a large amount of customized development is also required. Since the technology used in whole-database synchronization data is relatively complex, it increases the difficulty of data collection and cannot distinguish which institution the data belongs to, which cannot meet the actual business requirements. SUMMARY

[0004] The technical problem to be solved by the present disclosure is to overcome the defects of complex and inefficient whole-database synchronization data operation in the prior art, and to provide a method, system, device, medium and program product for real-time whole-database synchronization data.

[0005] The present disclosure solves the above technical problems by the following technical solutions:

[0006] In a first aspect, the present disclosure provides a method for real-time whole-database synchronization data, which is realized based on a FlinkCDC component in a Java application environment, and the method comprises the following steps:

[0007] obtaining preconfigured business database connection information and downstream storage database connection information;

[0008] connecting each business database of a subordinate institution according to the business database information through the FlinkCDC component, and obtaining the table name, field name in the table and data in the field stored in each business database;

[0009] Connect a downstream storage database according to the downstream storage database connection information through the FlinkCDC component, and store the table name, the table field name and the field data in the downstream storage database in a preset format.

[0010] Optionally, the method further comprises:

[0011] Obtaining a preconfigured extension field and a value limit of the extension field, wherein the extension field is used to identify the institutional source of data;

[0012] Determining the value of the extension field corresponding to the table name according to the subordinate institution to which the business database belongs;

[0013] Storing the correspondence between the table name and the value of the extension field in the downstream storage database in a preset format through the FlinkCDC component.

[0014] Optionally, the obtaining of the table name, the table field name and the field data stored by each business database comprises:

[0015] Obtaining log data of each business database;

[0016] Parsing the log data and extracting the table name and the table field name, and obtaining the field data according to the table name and the table field name.

[0017] Optionally, the business database supports at least one of MySQL, Oracle, SQL Server, Hive and Kafka.

[0018] And / or, the downstream storage database supports at least one of MySQL, Oracle, SQL Server, Hive and Kafka.

[0019] In a second aspect, the present disclosure provides a system for synchronizing data in real time, which is implemented based on a FlinkCDC component in a Java application environment, and comprises an obtaining module, a business interface module and a downstream interface module.

[0020] The obtaining module is configured to obtain preconfigured business database connection information and downstream storage database connection information.

[0021] The business interface module is configured to connect each business database of a subordinate institution through the FlinkCDC component according to the business database information, and obtain a table name, a table field name and field data stored by each business database.

[0022] The downstream interface module is configured to connect a downstream storage database according to the downstream storage database connection information of the FlinkCDC component, and store the table name, the table field name and the field data in the downstream storage database in a preset format.

[0023] Optionally, the system further comprises an extension field determination module.

[0024] The acquisition module is further configured to acquire a preconfigured extension field and a value limit of the extension field, wherein the extension field is used to identify an institutional source of data.

[0025] The extension field determination module is configured to determine the value of the extension field corresponding to the table name according to an underlying institution to which the business database belongs.

[0026] The downstream interface module is further configured to store the correspondence between the table name and the value of the extension field in the downstream storage database in a preset format through the FlinkCDC component.

[0027] Optionally, the acquisition module is further configured to acquire log data of each business database.

[0028] The acquisition module is further configured to parse the log data and extract the table name and the table field name, and acquire the field data according to the table name and the table field name.

[0029] and / or,

[0030] The business database supports at least one of MySQL, Oracle, SQL Server, Hive and Kafka.

[0031] and / or,

[0032] The downstream storage database supports at least one of MySQL, Oracle, SQL Server, Hive and Kafka.

[0033] In a third aspect, the present disclosure provides an electronic device, comprising a memory, a processor and a computer program stored in the memory and used to run on the processor, wherein the processor executes the computer program to realize the method for real-time whole library synchronization data.

[0034] In a fourth aspect, the present disclosure provides a computer readable storage medium, which stores a computer program, wherein the computer program is executed by a processor to realize the method for real-time whole library synchronization data.

[0035] In a fifth aspect, the present disclosure provides a computer program product comprising a computer program which, when executed by a processor, implements the method for real-time whole database synchronization data as described above.

[0036] On the basis of common knowledge in the art, the above-mentioned preferred conditions can be combined arbitrarily, i.e. to obtain each preferred example of the present disclosure.

[0037] The positive progress effect of the present disclosure is that: based on the FlinkCDC component, the real-time whole database synchronization data operation is simple and convenient, and the maintenance is convenient, which improves the current situation of insufficient FlinkCDC expansion capability and great development difficulty, a task can extract table data in multiple business databases for whole database, and store the table data of the business database in the downstream storage database according to the preset format, which improves the synchronization efficiency, and at the same time, the field can be extended to identify the subordinate institutions to which the business database belongs, that is, to identify the agency source of the data, so as to conveniently and quickly distinguish the agency source of each data stored in the downstream storage database to avoid confusion. BRIEF DESCRIPTION OF DRAWINGS

[0038] Figure 1 A flowchart of a method for real-time whole database synchronization data provided by Embodiment 1 of the present disclosure;

[0039] Figure 2 A flowchart of a specific implementation of a method for real-time whole database synchronization data provided by Embodiment 1 of the present disclosure;

[0040] Figure 3 A flowchart of a specific implementation of step S12 of a method for real-time whole database synchronization data provided by Embodiment 1 of the present disclosure;

[0041] Figure 4 A module schematic diagram of a system for real-time whole database synchronization data provided by Embodiment 2 of the present disclosure;

[0042] Figure 5 A structural schematic diagram of an electronic device provided by Embodiment 3 of the present disclosure. DETAILED DESCRIPTION

[0043] The present disclosure will be further described below by way of examples, but the present disclosure is not limited in the scope of the examples.

[0044] The prefix words such as "first", "second" in the embodiments of the present disclosure are only used to distinguish different description objects, and have no limiting effect on the position, order, priority, quantity or content of the described objects. The use of ordinal words such as prefixes in the embodiments of the present disclosure does not limit the described objects, and the description of the described objects should be referred to the description of the context in the claims or embodiments, and should not be limited by the use of such prefix words. In addition, in the description of the embodiments, unless otherwise stated, the meaning of "a plurality of" is two or more.

[0045] Embodiment 1

[0046] Figure 1 A flowchart of a method for real-time whole database synchronization data provided by an exemplary embodiment of the present disclosure is provided, which is implemented based on FlinkCDC components in a Java application environment, and the method comprises:

[0047] S11, obtaining preconfigured business database connection information and downstream storage database connection information.

[0048] S12, connecting each business database of the subordinate agency according to the business database information through the FlinkCDC component, and obtaining the table name, table field name and field data stored in each business database.

[0049] S13, connecting the downstream storage database according to the downstream storage database connection information through the FlinkCDC component, and storing the table name, table field name and field data in the downstream storage database according to the preset format.

[0050] Among them, one business database belongs to only one subordinate agency, and the table data in the same business database belongs to the same subordinate agency.

[0051] The FlinkCDC component can be integrated in the Java application program, and the interface of FlinkCDC is called in the program to collect the whole database data, that is, the whole database synchronization program of flinkCDC data is written by using Java language. The business database connection information can be configured in the Java application program, and the downstream storage database connection information can be configured.

[0052] The application program can be deployed in different subordinate agencies at the same time, and after the program is started, different business library data can be saved and summarized to the downstream storage database, that is, the data is synchronized from different business libraries at the same time.

[0053] The business database connection information and the downstream storage database connection information are both necessary information for database connection, such as user name, password, IP (Internet Protocol) connection address, etc.

[0054] In this embodiment, the real-time full database synchronization operation based on the FlinkCDC component is simple and convenient, and easy to maintain. It improves the current situation of insufficient scalability and high development difficulty of FlinkCDC. A single task can extract table data from multiple business databases and store the table data of the business databases in the downstream storage database according to the preset format, thereby improving the synchronization efficiency.

[0055] In one embodiment, refer to Figure 2 The method also includes:

[0056] S21. Obtain the pre-configured extended fields and the value restrictions for the extended fields. Among them, the extended fields are used to identify the organizational origin of the data.

[0057] S22. Determine the values ​​of the extended fields corresponding to the table names based on the subordinate organizations to which the business database belongs.

[0058] S23. The correspondence between table names and extended field values ​​is stored in the downstream storage database according to a preset format using the FlinkCDC component.

[0059] In the downstream storage database, data sources can be distinguished using extended fields, which can be set according to business requirements. Except for the extended fields, all other field types and lengths in the same table in both the business database and the downstream storage database must be consistent.

[0060] Extended fields can be identifier fields created when storing each table in the downstream storage database to distinguish the data source. For example, an extended field with the value "AA" indicates that the data comes from subordinate organization XX. Specifically, extended fields are table-specific fields in the downstream storage database, created when the table is created, and do not exist in the business database.

[0061] It can read the extended fields set in the configuration file of a Java application and the values ​​of those extended fields.

[0062] In this embodiment, the real-time whole-database synchronization operation based on the FlinkCDC component is simple and convenient, and easy to maintain. It improves the current situation of insufficient scalability and high development difficulty of FlinkCDC. A single task can extract table data from multiple business databases and store the table data of the business databases in the downstream storage database according to a preset format, thereby improving synchronization efficiency. At the same time, it can also identify the subordinate organization to which the business database belongs by extending the field, that is, identify the organizational source of the data, so as to conveniently and quickly distinguish the organizational source of each data stored in the downstream storage database to avoid confusion.

[0063] In one embodiment, refer to Figure 3Step S12, "obtaining the table name, field names, and data within each business database", includes:

[0064] S121. Obtain log data for each business database.

[0065] S122. Parse the log data and extract the table name and field names. Retrieve the data in the fields based on the table name and field names.

[0066] The log data consists of logs generated when data in the business database changes. In a Java application integrated with FlinkCDC technology, real-time log data from the entire business database is captured, and the log data is parsed into a single row of data from the corresponding table based on the table name within the log data.

[0067] In one embodiment, the business database supports at least one of MySQL, Oracle, SQL Server, Hive, and Kafka.

[0068] The business databases include, but are not limited to, MySQL, Oracle, SQL Server, Hive, and Kafka databases.

[0069] In one embodiment, the downstream storage database supports at least one of MySQL, Oracle, SQL Server, Hive, and Kafka.

[0070] Downstream storage databases include, but are not limited to, MySQL, Oracle, SQL Server, Hive, and Kafka databases.

[0071] The following three examples illustrate the implementation of a method for real-time full database synchronization.

[0072] Example 1

[0073] Take, for example, a business database and a downstream storage database that are both MySQL databases.

[0074] Step 1: Deploy the Java application, which integrates business database information, downstream storage data information, and FlinkCDC technology, to servers in different organizations.

[0075] Step 2: Access the business database (MySQL) using a Java application that integrates FlinkCDC data collection technology, and capture log data from the business database.

[0076] Step 3: Obtain the log data from the business database, analyze and extract the logs to obtain the table names, field names, and individual data entries in the logs;

[0077] Step 4: Read the configuration file in the Java application and obtain the default fields and default values ​​that exist in the configuration file.

[0078] Step 5: Save the data in S2 and S3 to the downstream MySQL database for conversion.

[0079] Example 2

[0080] Taking an Oracle database as the business database and a MySQL database as the downstream storage database as an example.

[0081] Step 1: Deploy the Java application, which integrates business database information, downstream storage data information, and FlinkCDC technology, to servers in different organizations.

[0082] Step 2: Access the Oracle database of the business database through a Java application that integrates FlinkCDC acquisition technology, and capture the log data of the business database.

[0083] Step 3: Obtain the log data from the business database, analyze and extract the logs to obtain the table names, field names, and individual data entries in the logs;

[0084] Step 4: Read the configuration file in the Java application and obtain the default fields and default values ​​that exist in the configuration file.

[0085] Step 5: Save the data in S2 and S3 to the downstream MySQL database for conversion.

[0086] Example 3

[0087] Taking Oracle and SQL Server databases as the business databases and MySQL databases as the downstream storage database as an example.

[0088] Step 1: Deploy the integrated business database information and downstream storage database information, including Oracle or SQL Server connection information such as account, password, IP connection information, and the Java application using FlinkCDC technology, to servers in different organizations.

[0089] Step 2: Access the business database (Oracle or SQL Server) using a Java application that integrates FlinkCDC data collection technology, and capture log data from the business database.

[0090] Step 3: Obtain the log data from the business database, analyze and extract the logs to obtain the table names, field names, and individual data entries in the logs;

[0091] Step 4: Read the configuration file in the Java application and obtain the default fields and default values ​​that exist in the configuration file.

[0092] Step 5: Save the data in S2 and S3 to the downstream MySQL database for conversion.

[0093] Example 2

[0094] Corresponding to the aforementioned method embodiments for real-time full database synchronization, this disclosure also provides embodiments of systems for real-time full database synchronization.

[0095] Figure 4 This is a schematic diagram of a system for real-time full-database synchronization provided as an exemplary embodiment of the present disclosure. The system is implemented based on the FlinkCDC component in a Java application environment and includes: an acquisition module 1, a business interface module 2, and a downstream interface module 3.

[0096] Module 1 is used to obtain pre-configured business database connection information and downstream storage database connection information.

[0097] Business Interface Module 2 is used to connect to the various business databases of subordinate organizations through the FlinkCDC component based on the business database information, and to obtain the table name, field name and data stored in each business database.

[0098] Downstream interface module 3 is used to connect to the downstream storage database through the FlinkCDC component based on the downstream storage database connection information, and store the table name, field name and data in the table in the downstream storage database according to a preset format.

[0099] In one embodiment, the system further includes: an extended field determination module 4.

[0100] The acquisition module 1 is also used to acquire pre-configured extended fields and the value restrictions of the extended fields. Among them, the extended fields are used to identify the organizational source of the data.

[0101] The extended field determination module 4 is used to determine the values ​​of the extended fields corresponding to the table names based on the subordinate organization to which the business database belongs.

[0102] Downstream interface module 3 is also used to store the correspondence between table names and extended field values ​​in a preset format in the downstream storage database through the FlinkCDC component.

[0103] In one embodiment, the acquisition module 1 is also used to acquire log data for each business database.

[0104] Module 1 is also used to parse log data and extract table names and field names, and then retrieve the data within the fields based on the table names and field names.

[0105] In one embodiment, the business database supports at least one of MySQL, Oracle, SQL Server, Hive, and Kafka.

[0106] In one embodiment, the downstream storage database supports at least one of MySQL, Oracle, SQL Server, Hive, and Kafka.

[0107] For the system embodiments, since they basically correspond to the method embodiments, the relevant parts can be referred to in the description of the method embodiments. The system embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate. The components as units may or may not be physical units, that is, they may be located in one place or distributed across multiple network units. Some or all of the modules can be selected to achieve the purpose of this disclosure according to actual needs.

[0108] Example 3

[0109] Figure 5 This is a schematic diagram of the structure of an electronic device according to an example embodiment of the present disclosure. The electronic device includes a memory, a processor, and a computer program stored in the memory and used to run on the processor. When the processor executes the computer program, it implements the method for real-time whole-database synchronization of any of the above embodiments. Figure 5 The electronic device 90 shown is merely an example and should not impose any limitation on the functionality and scope of use of the embodiments disclosed herein.

[0110] like Figure 5 As shown, the electronic device 90 can be manifested as a general-purpose computing device, such as a server device. The components of the electronic device 90 may include, but are not limited to: at least one processor 91, at least one memory 92, and a bus 93 connecting different system components (including memory 92 and processor 91).

[0111] Bus 93 includes a data bus, an address bus, and a control bus.

[0112] The memory 92 may include volatile memory, such as random access memory (RAM) 921 and / or cache memory 922, and may further include read-only memory (ROM) 923.

[0113] The memory 92 may also include a program tool 925 (or utility) having a set (at least one) program module 924, including but not limited to: an operating system, one or more application programs, other program modules, and program data, each or some combination of these examples may include an implementation of a network environment.

[0114] The processor 91 executes various functional applications and data processing by running computer programs stored in the memory 92, such as the real-time whole-database synchronization data method provided in any of the above embodiments.

[0115] Electronic device 90 can also communicate with one or more external devices 94 (e.g., keyboard, pointing device, etc.). This communication can be performed through input / output (I / O) interface 95. Furthermore, electronic device 90 can also connect to one or more networks (e.g., local area network (LAN), wide area network (WAN)) via network adapter 96. As shown in Figure 1, the computer 80, in some embodiments, includes a set of instructions executable by the processor 92 to implement multi-modal gesture recognition. In some embodiments, the instructions are tangibly embodied in a computer-readable medium, such as the memory 94. In other embodiments, the instructions are tangibly embodied in the memory 94 and / or the storage device 98, such as in the form of ​ Line 93 communicates with other modules of electronic device 90. It should be understood that, although not shown in the figure, other hardware and / or software modules may be used in conjunction with electronic device 90, including but not limited to: microcode, device drivers, redundant processors, external disk drive arrays, RAID (disk array) systems, tape drives, and data backup storage systems.

[0116] It should be noted that although several units / modules or sub-units / modules of the electronic device have been mentioned in the detailed description above, this division is merely exemplary and not mandatory. In fact, according to embodiments of this disclosure, the features and functions of two or more units / modules described above can be embodied in one unit / module. Conversely, the features and functions of one unit / module described above can be further divided and embodied by multiple units / modules.

[0117] Example 4

[0118] This disclosure also provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the method for real-time whole-database synchronization provided in any of the above embodiments.

[0119] The readable storage medium may be more specifically adopted, including but not limited to: portable disk, hard disk, random access memory, read-only memory, erasable programmable read-only memory, optical storage device, magnetic storage device, or any suitable combination thereof.

[0120] Example 5

[0121] This disclosure also provides a computer program product, including a computer program that, when executed by a processor, implements the method for real-time whole-database synchronization of any of the above-described embodiments.

[0122] The program code for executing the computer program product of this disclosure can be written in any combination of one or more programming languages, and the program code can be executed entirely on a user device, partially on a user device, as a stand-alone software package, partially on a user device and partially on a remote device, or entirely on a remote device.

[0123] While specific embodiments of this disclosure have been described above, those skilled in the art should understand that these are merely illustrative examples, and the scope of protection of this disclosure is defined by the appended claims. Those skilled in the art can make various changes or modifications to these embodiments without departing from the principles and essence of this disclosure, but all such changes and modifications fall within the scope of protection of this disclosure.

Claims

1. A method for synchronizing data in real time across a warehouse, comprising: The method is implemented based on a FlinkCDC component in a Java application environment, and the method comprises: obtaining preconfigured business database connection information and downstream storage database connection information; connecting each business database of a subordinate institution according to the business database information through the FlinkCDC component, and obtaining a table name, a table field name and field data stored in each business database; connecting a downstream storage database according to the downstream storage database connection information through the FlinkCDC component, and storing the table name, the table field name and the field data in the downstream storage database in a preset format.

2. The method for synchronizing data across a library in real time of claim 1, wherein, The method further comprises: obtaining a preconfigured extension field and a value limit of the extension field; wherein the extension field is used to identify the institutional origin of data; determining the value of the extension field corresponding to the table name according to the subordinate institution to which the business database belongs; storing the correspondence between the table name and the value of the extension field in the downstream storage database in a preset format through the FlinkCDC component.

3. The method of Claim 1, wherein, The obtaining of the table name, the table field name and the field data stored in each business database comprises: obtaining log data of each business database; parsing the log data and extracting the table name and the table field name, and obtaining the field data according to the table name and the table field name.

4. The method of Claim 1, wherein, The business database supports at least one of MySQL, Oracle, SQL Server, Hive and Kafka; and / or, the downstream storage database supports at least one of MySQL, Oracle, SQL Server, Hive and Kafka.

5. A system for synchronizing data across a warehouse in real time, the system comprising: The system is implemented based on a FlinkCDC component in a Java application environment, and the system comprises an obtaining module, a business interface module and a downstream interface module; The obtaining module is used to obtain preconfigured business database connection information and downstream storage database connection information; The business interface module is used to connect each business database of a subordinate institution according to the business database information through the FlinkCDC component, and obtain a table name, a table field name and field data stored in each business database; The downstream interface module is used to connect a downstream storage database according to the downstream storage database connection information through the FlinkCDC component, and store the table name, the table field name and the field data in the downstream storage database in a preset format.

6. The system for synchronizing data across a library in real-time of claim 5, wherein, The system further comprises an extension field determination module; The obtaining module is also used to obtain a preconfigured extension field and a value limit of the extension field; wherein the extension field is used to identify the institutional origin of data; The extension field determination module is used to determine the value of the extension field corresponding to the table name according to the subordinate institution to which the business database belongs; The downstream interface module is further configured to store the correspondence between the table name and the value of the extended field in the downstream storage database in a preset format through the FlinkCDC component.

7. The system for synchronizing data across a library in real-time of claim 5, wherein, The acquisition module is further configured to acquire log data of each service database; The acquisition module is further configured to parse the log data and extract the table name and the field name in the table, and acquire the data in the field according to the table name and the field name in the table; And / or, The service database supports at least one of MySQL, Oracle, SQL Server, Hive and Kafka. And / or, The downstream storage database supports at least one of MySQL, Oracle, SQL Server, Hive and Kafka at least one of the following:

8. An electronic device comprising a memory, a processor, and a computer program stored on the memory for running on the processor, characterized in that, The processor executes the computer program to implement the method for synchronizing data in real time in the whole library according to any one of claims 1 to 4.

9. A computer readable storage medium having stored thereon a computer program, characterized in that, The computer program is executed by the processor to implement the method for synchronizing data in real time in the whole library according to any one of claims 1 to 4.

10. A computer program product comprising a computer program, characterized in that, The computer program is executed by the processor to implement the method for synchronizing data in real time in the whole library according to any one of claims 1 to 4.

Images