A distributed database real-time collection into a lake method and system

Abstract

The application discloses a kind of distributed database real-time collection lake-entering method and system, belong to data transmission technical field, method includes: in configuration center definition database server node and collection lake-entering task node;CDC collection service program is according to preset time interval to database server node initiates the request of modification heartbeat time, updates heartbeat field in database server node;The database and corresponding data table information that need to be collected lake-entering are added to collection lake-entering task node;CDC collection service program accesses collection lake-entering task node, finds the task information that needs to be collected lake-entering;CDC collection service program is collected according to the database and corresponding data table that needs to be collected lake-entering and sends to distributed message platform in task;Lake-entering service program accesses each collection lake-entering task node, reads database and corresponding data table from distributed message platform and carries out lake-entering processing.

Description

Technical Field

[0001] This invention belongs to the field of data transmission technology, specifically relating to a method and system for real-time data collection from a distributed database into a lake. Background Technology

[0002] Change Data Capture (CDC) technology has become the mainstream technique in database synchronization and acquisition. CDC captures incremental changes to data and data structures in the source database and propagates these changes to other databases or applications in near real-time. In this way, CDC can provide efficient, low-latency data transmission to the data warehouse, so that information can be transformed and delivered to applications dedicated to analysis in a timely manner.

[0003] Data lake technology, as a new data warehouse storage technology, has begun to be adopted by many companies in their big data platforms due to its efficient real-time ingestion and retrieval capabilities and good compatibility with traditional big data warehouse technologies. Hudi is a typical data lake technology.

[0004] Using database data collection and importing into Hudi via CDC (Database Data Collection and Integration) technology has become a mainstream database data collection solution. The typical approach involves first importing the data from CDC into Kafka or Pulsar using CDC tools, and then streaming it into Hudi using Flink or Spark.

[0005] However, current mainstream CDC (Data Collection Center) technology for importing data into the lake faces several challenges. Firstly, because CDC involves real-time data collection, CDC tools are typically provided as services throughout the collection process. Furthermore, since the collection sources are often distributed across various network locations, each database often corresponds to a separate CDC service. The large number of such independent services makes configuration changes and maintenance extremely cumbersome. Secondly, the multiple CDC services and the lake import service are relatively independent. The schema definition of the lake tables is often pre-defined and hard-coded during the import process, making changes to the schema of the source data tables imperceptible. Summary of the Invention

[0006] The purpose of this invention is to provide a method and system for real-time data collection and import into a distributed database, which can solve the technical problems of existing data import technologies, such as the cumbersome work of changing and maintaining the collection configuration of a large number of independent CDC services, the fact that multiple CDC services and import services are relatively independent, and the inability to perceive changes in the schema of the source data table during the import process, resulting in redundant and inefficient data import processes.

[0007] To solve the above-mentioned technical problems, the present invention is implemented as follows:

[0008] First aspect

[0009] This invention provides a method for real-time data collection into a lake using a distributed database, comprising:

[0010] S101: Define the database server node and the data collection task node in the configuration center;

[0011] S102: Deploy the Change Data Capture CDC Acquisition Service program on the acquisition database server;

[0012] S103: The CDC data collection service program sends a request to the configuration center to create a database server node based on the configured configuration center information;

[0013] S104: Once the database server node is created, the CDC data collection service program sends a request to the database server node to modify the heartbeat time at preset time intervals, updating the heartbeat field in the database server node.

[0014] S105: Add the database to be collected and the corresponding data table information to the lake collection task node;

[0015] S106: The CDC data collection service program accesses the data collection task node, searches for the task information that needs to be collected into the lake, and compares it with the data stored in the data collection database server to determine whether there is a database and corresponding data table that need to be collected into the lake.

[0016] S107: If there is a database and corresponding data table that need to be collected into the lake, the CDC collection service program obtains the schema information of the data table to be collected and sends it to the collection into the lake task node so that the schema information can be written into the collection into the lake task node.

[0017] S108: The CDC data collection service program performs data collection tasks as needed, collecting data from the database and corresponding data tables of the lake.

[0018] S109: The CDC data collection service program sends the collected database and corresponding data tables to the distributed messaging platform;

[0019] S110: The lake entry service program accesses each lake entry task node, creates a lake entry task thread based on the schema information, reads the database and corresponding data tables from the distributed message platform, and performs lake entry processing.

[0020] Furthermore, the name of the database server node is the address of the database server being collected, and the content of the database server node is the connection time and heartbeat time of the database server being collected.

[0021] Furthermore, the name of the lake entry task node is the name of the collection database and the corresponding collection data table. The content of the database server node is the schema information of the collection data table. The schema information includes the name, type, length, and order of each field, as well as the database and table of the lake entry task.

[0022] Furthermore, following S104, it also includes:

[0023] S104A: The alarm program traverses all database server nodes, compares the difference between the current time and the last heartbeat time of each data collection database server, and determines whether there is a service anomaly in the data collection database server based on the comparison result of the difference and the preset timeout time.

[0024] Furthermore, S108 specifically refers to:

[0025] The CDC data collection service program uses a combination of full snapshots and incremental binlogs to collect the databases and corresponding data tables that need to be entered into the lake.

[0026] Second aspect

[0027] This invention provides a distributed database real-time data collection system for lakes, comprising:

[0028] The definition module is used to define the database server node and the data collection task node in the configuration center;

[0029] The deployment module is used to deploy the Change Data Capture CDC acquisition service program on the acquisition database server;

[0030] The first request module is used by the CDC data collection service program to send a request to the configuration center to create a database server node based on the configured configuration center information.

[0031] The second request module is used to send a request to the database server node to modify the heartbeat time at a preset time interval after the database server node has been created, and to update the heartbeat field in the database server node.

[0032] Add a module to add the database and corresponding data table information that need to be collected into the lake to the lake collection task node;

[0033] The comparison module is used by the CDC collection service program to access the collection entry task node, find the task information that needs to be collected into the lake, and compare it with the data stored in the collection database server to determine whether there is a database and corresponding data table that need to be collected into the lake.

[0034] The writing module is used to obtain the schema information of the data table to be collected when there is a database and corresponding data table that need to be collected into the lake, and send it to the collection into the lake task node so that the schema information can be written into the collection into the lake task node.

[0035] The data acquisition module is used by the CDC data acquisition service program to perform data acquisition tasks by collecting data from the database and corresponding data tables of the lake as needed.

[0036] The sending module is used by the CDC data collection service program to send the collected database and corresponding data tables to the distributed messaging platform.

[0037] The lake entry module is used by the lake entry service program to access each data collection and lake entry task node, create lake entry task threads based on schema information, read the database and corresponding data tables from the distributed messaging platform, and perform lake entry processing.

[0038] Furthermore, the name of the database server node is the address of the database server being collected, and the content of the database server node is the connection time and heartbeat time of the database server being collected.

[0039] Furthermore, the name of the lake entry task node is the name of the collection database and the corresponding collection data table. The content of the database server node is the schema information of the collection data table. The schema information includes the name, type, length, and order of each field, as well as the database and table of the lake entry task.

[0040] Furthermore, the distributed database real-time data collection system for the lake also includes:

[0041] The alarm module is used by the alarm program to traverse all database server nodes, compare the difference between the current time and the last heartbeat time of each data collection database server, and determine whether there is a service anomaly of the data collection database server based on the comparison result of the difference and the preset timeout time.

[0042] Furthermore, the data acquisition module is specifically used for:

[0043] The CDC data collection service program uses a combination of full snapshots and incremental binlogs to collect the databases and corresponding data tables that need to be entered into the lake.

[0044] The present invention has at least the following beneficial effects:

[0045] In this embodiment of the invention, by defining database server nodes and data collection and lake entry task nodes in the configuration center, and then using the database server nodes and data collection and lake entry task nodes to uniformly manage all data collection and lake entry services, the data collection configuration changes and maintenance work are simplified, reducing operation and maintenance management costs. During the lake entry process, the data collection and lake entry task nodes dynamically sense changes in the schema of the source data table and adjust the lake entry strategy according to the changes in the schema, simplifying the data lake entry process and improving the efficiency of data lake entry. Attached Figure Description

[0046] Figure 1 This is a flowchart illustrating a method for real-time data collection into a lake using a distributed database, as provided in an embodiment of the present invention.

[0047] Figure 2 This is a schematic diagram of a distributed database real-time data collection system for lakes provided in an embodiment of the present invention.

[0048] The realization of the objective, functional characteristics and advantages of the present invention will be further explained in conjunction with the embodiments and with reference to the accompanying drawings. Detailed Implementation

[0049] To make the objectives, technical solutions, and advantages of this invention clearer, the technical solutions of the embodiments of this invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some, not all, of the embodiments of this invention. Based on the embodiments of this invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this invention.

[0050] The following description, in conjunction with the accompanying drawings, details the distributed database real-time data collection method and system for lakes provided by the present invention through specific embodiments and application scenarios.

[0051] Example 1

[0052] Reference Figure 1 The diagram illustrates a flowchart of a distributed database real-time data collection method for lakes provided by an embodiment of the present invention.

[0053] This invention provides a method for real-time data collection into a lake using a distributed database, comprising:

[0054] S101: Define the database server node and the data collection task node in the configuration center.

[0055] ZooKeeper can be used as the configuration center to define the database server node and the data collection task node.

[0056] Optionally, configure the root node / datacloud, the CDC collection source root node / datacloud / sources, and the collection synchronization task root node / datacloud / jobs in ZooKeeper.

[0057] The database server node is mainly used for functions such as database server registration, maintenance, and data source management.

[0058] The data collection task node is mainly used for: on the one hand, configuring the database and table names to be collected and entered into the lake in the management program; on the other hand, synchronizing the data source table information (schema) with the lake entry service to realize the management of the fields to be entered into the lake.

[0059] Optionally, the name of the database server node is the address of the data collection database server, and the content of the database server node is the connection time and heartbeat time of the data collection database server.

[0060] Optionally, the name of the lake entry task node is the name of the collection database and the corresponding collection data table. The content of the database server node is the schema information of the collection data table. The schema information includes the name, type, length, and order of each field, as well as the database and table of the lake entry task.

[0061] S102: Deploy the Change Data Capture CDC Acquisition Service on the acquisition database server.

[0062] After the CDC data collection service program is deployed, it can access the data collection database server.

[0063] Optionally, the configuration center for the CDC data collection service can be the service where the ZooKeeper configuration center is located, and the CDC data collection service can be deployed and started on the server where data collection is required.

[0064] S103: The CDC data collection service program sends a request to the configuration center to create a database server node based on the configured configuration center information.

[0065] S104: Once the database server node is created, the CDC data collection service program sends a request to the database server node to modify the heartbeat time at preset time intervals, updating the heartbeat field in the database server node.

[0066] Those skilled in the art can set specific preset time intervals according to actual needs, and the embodiments of the present invention do not impose limitations.

[0067] Optionally, the CDC data collection service program will automatically register the local IP address to / datacloud / sources in the configuration center and periodically send heartbeat messages to update the heartbeat field in the node.

[0068] S105: Add the database and corresponding data table information that need to be collected into the lake to the lake collection task node.

[0069] S106: The CDC data collection service program accesses the data collection task node, searches for the task information that needs to be collected into the lake, and compares it with the data stored in the data collection database server to determine whether there is a database and corresponding data table that need to be collected into the lake.

[0070] S107: If there is a database and corresponding data tables that need to be collected into the lake, the CDC collection service program obtains the schema information of the data tables to be collected and sends it to the collection into the lake task node so that the schema information can be written into the collection into the lake task node.

[0071] S108: The CDC data collection service program performs data collection tasks as needed, collecting data from the database and corresponding data tables of the lake.

[0072] In one possible implementation, S108 specifically involves the CDC data collection service program using a combination of full snapshot and incremental binlog data collection to collect the database and corresponding data tables that need to be entered into the lake.

[0073] In this embodiment of the invention, using a combination of full snapshot and incremental binlog data collection can improve the efficiency of data collection and ensure the accuracy of data collection.

[0074] S109: The CDC data collection service program sends the collected database and corresponding data tables to the distributed messaging platform.

[0075] The distributed messaging platform can be either Kafka or Pulsar.

[0076] S110: The lake entry service program accesses each lake entry task node, creates a lake entry task thread based on the schema information, reads the database and corresponding data tables from the distributed message platform, and performs lake entry processing.

[0077] Optionally, the configuration center for the data ingestion service is set to the ZooKeeper configuration center service, and the data ingestion service is started. The ingestion service automatically monitors newly added tasks under / datacloud / jobs. Users can add data collection and ingestion tasks under / datacloud / jobs through the configuration center management interface or the ZooKeeper command line.

[0078] The present invention has at least the following beneficial effects:

[0079] In this embodiment of the invention, by defining database server nodes and data collection and lake entry task nodes in the configuration center, and then using the database server nodes and data collection and lake entry task nodes to uniformly manage all data collection and lake entry services, the data collection configuration changes and maintenance work are simplified, reducing operation and maintenance management costs. During the lake entry process, the data collection and lake entry task nodes dynamically sense changes in the schema of the source data table and adjust the lake entry strategy according to the changes in the schema, simplifying the data lake entry process and improving the efficiency of data lake entry.

[0080] In one possible implementation, after S104, the following is also included:

[0081] S104A: The alarm program traverses all database server nodes, compares the difference between the current time and the last heartbeat time of each data collection database server, and determines whether there is a service anomaly in the data collection database server based on the comparison result of the difference and the preset timeout time.

[0082] Optionally, if the difference between the last heartbeat times of the data collection database server is greater than the preset timeout, it is determined that there is a service anomaly in the data collection database server, and an alarm message should be issued in a timely manner.

[0083] Optionally, the alarm message can be a voice warning or a pop-up warning window.

[0084] In this embodiment of the invention, based on the comparison result between the difference and the preset timeout, it is possible to determine in a timely and accurate manner whether there is a service anomaly in the data collection database server, and then take corresponding actions to uniformly manage the data collection service, making maintenance work more timely and simpler.

[0085] Example 2

[0086] Reference Figure 2 The diagram shows a schematic representation of a distributed database real-time data acquisition system for lakes, as provided in an embodiment of the present invention.

[0087] This invention provides a distributed database real-time data collection system 20 for lakes, comprising:

[0088] Define module 201, which is used to define the database server node and the data collection task node in the configuration center;

[0089] Deployment module 202 is used to deploy the Change Data Capture CDC acquisition service program on the acquisition database server;

[0090] The first request module 203 is used by the CDC data collection service program to send a request to the configuration center to create a database server node based on the configured configuration center information.

[0091] The second request module 204 is used to send a request to the database server node to modify the heartbeat time at a preset time interval after the database server node has been created, and update the heartbeat field in the database server node.

[0092] Add module 205 to add the database and corresponding data table information that need to be collected into the lake to the lake collection task node;

[0093] Comparison module 206 is used by the CDC collection service program to access the collection entry task node, search for the task information that needs to be collected into the lake, and compare it with the data stored in the collection database server to determine whether there is a database and corresponding data table that needs to be collected into the lake.

[0094] The writing module 207 is used to obtain the schema information of the data table to be collected when there is a database and corresponding data table that need to be collected into the lake, and send it to the collection into the lake task node so as to write the schema information into the collection into the lake task node.

[0095] The acquisition module 208 is used by the CDC acquisition service program to perform acquisition tasks by collecting data from the database and corresponding data tables of the lake as needed.

[0096] The sending module 209 is used by the CDC data collection service program to send the collected database and corresponding data tables to the distributed message platform.

[0097] The lake entry module 210 is used by the lake entry service program to access each data collection and lake entry task node, create a lake entry task thread based on schema information, read the database and corresponding data tables from the distributed message platform, and perform lake entry processing.

[0098] Furthermore, the name of the database server node is the address of the database server being collected, and the content of the database server node is the connection time and heartbeat time of the database server being collected.

[0099] Furthermore, the name of the lake entry task node is the name of the collection database and the corresponding collection data table. The content of the database server node is the schema information of the collection data table. The schema information includes the name, type, length, and order of each field, as well as the database and table of the lake entry task.

[0100] Furthermore, the distributed database real-time data collection system also includes an alarm module 211, which is used for the alarm program to traverse all database server nodes, compare the difference between the current time and the last heartbeat time of each data collection database server, and determine whether there is a service abnormality in the data collection database server based on the comparison result of the difference and the preset timeout time.

[0101] Furthermore, the acquisition module 208 is specifically used for: the CDC acquisition service program to acquire the database and corresponding data tables that need to be acquired into the lake using a full snapshot + incremental binlog method.

[0102] This invention provides a distributed database real-time data collection system 20 for lake entry, which can realize the various steps and effects of the above-described distributed database real-time data collection method for lake entry. To avoid repetition, these will not be repeated here.

[0103] The present invention has at least the following beneficial effects:

[0104] In this embodiment of the invention, by defining database server nodes and data collection and lake entry task nodes in the configuration center, and then using the database server nodes and data collection and lake entry task nodes to uniformly manage all data collection and lake entry services, the data collection configuration changes and maintenance work are simplified, reducing operation and maintenance management costs. During the lake entry process, the data collection and lake entry task nodes dynamically sense changes in the schema of the source data table and adjust the lake entry strategy according to the changes in the schema, simplifying the data lake entry process and improving the efficiency of data lake entry.

[0105] The virtual system provided by this invention can be a system, or a component, integrated circuit, or chip in a terminal.

[0106] The above are merely embodiments of the present invention and are not intended to limit the invention. Various modifications and variations can be made to the present invention by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principle of the present invention should be included within the scope of the claims of the present invention.

Claims

1. A method for real-time data collection from a distributed database into a lake, characterized in that, include: S101: Define the database server node and the data collection task node in the configuration center; S102: Deploy the Change Data Capture CDC Acquisition Service program on the acquisition database server; S103: The CDC data collection service program, based on the configured configuration center information, initiates a request to the configuration center to create the database server node; S104: Once the database server node is created, the CDC data collection service program sends a request to the database server node to modify the heartbeat time at preset time intervals, updating the heartbeat field in the database server node. S105: Add the database and corresponding data table information to be collected into the lake to the lake collection task node; S106: The CDC collection service program accesses the collection entry task node, searches for the task information that needs to be collected into the lake, and compares it with the data stored in the collection database server to determine whether there is a database and corresponding data table that need to be collected into the lake. S107: If there is a database and corresponding data table that need to be collected into the lake, the CDC collection service program obtains the schema information of the data table to be collected and sends it to the collection into the lake task node so as to write the schema information into the collection into the lake task node. S108: The CDC data collection service program performs data collection tasks as needed, collecting the database of data entering the lake and the corresponding data tables. S109: The CDC data collection service program sends the collected database and corresponding data tables to the distributed message platform; S110: The lake entry service program accesses each of the aforementioned lake entry task nodes, creates a lake entry task thread based on the schema information, reads the database and corresponding data tables from the distributed message platform, and performs lake entry processing.

2. The distributed database real-time data collection method for lake entry according to claim 1, characterized in that, The name of the database server node is the address of the data acquisition database server, and the content of the database server node is the connection time and heartbeat time of the data acquisition database server.

3. The distributed database real-time data collection method for lake entry according to claim 1, characterized in that, The name of the lake entry task node is the name of the collection database and the corresponding collection data table. The content of the database server node is the schema information of the collection data table. The schema information includes the name, type, length, and order of each field, as well as the database and table of the lake entry task.

4. The distributed database real-time data collection method for lake entry according to claim 1, characterized in that, Following S104, the following is also included: S104A: The alarm program traverses all the database server nodes, compares the difference between the current time and the last heartbeat time of each of the data collection database servers, and determines whether there is a service abnormality in the data collection database server based on the comparison result of the difference and the preset timeout time.

5. The distributed database real-time data collection method for lake entry according to claim 1, characterized in that, Specifically, S108 is: The CDC data collection service program uses a combination of full snapshot and incremental binlog data collection to collect the database and corresponding data tables that need to be entered into the lake.

6. A distributed database real-time data acquisition system for lakes, characterized in that, include: The definition module is used to define the database server node and the data collection task node in the configuration center; The deployment module is used to deploy the Change Data Capture CDC acquisition service program on the acquisition database server; The first request module is used by the CDC data collection service program to send a request to the configuration center to create the database server node based on the configured configuration center information. The second request module is used to, when the database server node is created, have the CDC data collection service program send a request to the database server node at a preset time interval to modify the heartbeat time and update the heartbeat field in the database server node. An add module is used to add the database and corresponding data table information that need to be collected into the lake to the lake collection task node; The comparison module is used by the CDC collection service program to access the collection task node in the lake, search for the task information that needs to be collected in the lake, and compare it with the data stored in the collection database server to determine whether there is a database and corresponding data table that need to be collected in the lake. The writing module is used to, when there is a database and corresponding data tables that need to be collected into the lake, have the CDC collection service program obtain the schema information of the data table to be collected and send it to the collection into the lake task node so as to write the schema information into the collection into the lake task node. The data acquisition module is used by the CDC data acquisition service program to perform data acquisition tasks by collecting data from the database and corresponding data tables of the lake as needed. The sending module is used by the CDC data collection service program to send the collected database and corresponding data tables to the distributed messaging platform. The lake entry module is used by the lake entry service program to access each of the data collection and lake entry task nodes, create lake entry task threads according to the schema information, read the database and corresponding data tables from the distributed message platform, and perform lake entry processing.

7. The distributed database real-time data acquisition system for lakes according to claim 6, characterized in that, The name of the database server node is the address of the data acquisition database server, and the content of the database server node is the connection time and heartbeat time of the data acquisition database server.

8. The distributed database real-time data acquisition system for lakes according to claim 6, characterized in that, The name of the lake entry task node is the name of the collection database and the corresponding collection data table. The content of the database server node is the schema information of the collection data table. The schema information includes the name, type, length, and order of each field, as well as the database and table of the lake entry task.

9. The distributed database real-time data acquisition system for lakes according to claim 6, characterized in that, Also includes: The alarm module is used by the alarm program to traverse all the database server nodes, compare the difference between the current time and the last heartbeat time of each of the data collection database servers, and determine whether there is a service abnormality of the data collection database server based on the comparison result of the difference and the preset timeout time.

10. The distributed database real-time data acquisition system for lakes according to claim 6, characterized in that, The acquisition module is specifically used for: The CDC data collection service program uses a combination of full snapshot and incremental binlog data collection to collect the database and corresponding data tables that need to be entered into the lake.

Images