Heterogeneous data source real-time data acquisition system

Abstract

The invention provides a heterogeneous data source real-time data acquisition system, which belongs to the field of data acquisition, and comprises a data middle platform configured to perform metadata interaction with a source database and a destination database and create a data acquisition task; and the distributed stream processing engine is configured to perform task and state interaction with the data medium station, receive the data acquisition task submitted by the data medium station, execute the data acquisition task and perform data processing and transmission on the acquired data. According to the method, efficient, reliable and real-time data synchronization among heterogeneous data sources is realized by combining the data middle table and the distributed stream processing engine, large-scale data streams can be processed, the consistency and integrity of the data can be ensured, flexible configuration management and monitoring capabilities are provided, and the data synchronization efficiency is improved. And the data integration efficiency and quality are greatly improved.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing, and in particular to a real-time data acquisition system for heterogeneous data sources. Background Art

[0002] In the data middle platform architecture, real-time data collection is a key link to ensure data timeliness and consistency. As a mainstream real-time data collection technology, FlinkCDC (Change Data Capture) is widely used to capture and extract change data from relational databases. Its working principle is to monitor the transaction log of the database and read data changes in real time in the order of log records. FlinkCDC technology currently supports a variety of mainstream relational databases, including but not limited to MySQL, Oracle, PostgreSQL, SQL Server, and DB2. This log-based change capture method can achieve low-latency, high-consistency data synchronization, providing strong support for data integration and real-time analysis.

[0003] In the existing technology, the operation of FlinkCDC is heavily dependent on the activation of database logs. The logging process may have a significant impact on database performance. When the data administrator is unwilling to enable or limit the database log function, it will directly affect the implementation possibility of FlinkCDC. In addition, with the strengthening of the trend of database localization, FlinkCDC has limited support for these emerging databases and cannot meet the diverse data source requirements.

[0004] Therefore, finding a method that can collect data without relying on database logs and can quickly realize real-time data collection is a technical problem that needs to be solved urgently by those skilled in the art. Summary of the invention

[0005] In view of this, the present invention proposes a real-time data acquisition system for heterogeneous data sources, which can process large-scale data streams and ensure the consistency and integrity of data, while providing flexible configuration management and monitoring capabilities, greatly improving the efficiency and quality of data integration.

[0006] The technical solution of the present invention is implemented as follows: The present invention provides a real-time data acquisition system for heterogeneous data sources, comprising:

[0007] The data center is configured to interact with the source and destination databases for metadata and create data collection tasks;

[0008] The distributed stream processing engine is configured to perform task and status interactions with the data center, receive data collection tasks submitted by the data center, execute data collection tasks, and process and transmit the collected data.

[0009] On the basis of the above technical solutions, preferably, the data middle platform includes a data security management module, a data quality management module, a data standard management module, a master data management module, a data acquisition management module and a metadata management module.

[0010] On the basis of the above technical solution, preferably, the creation of the data collection task specifically includes:

[0011] Step 1: Collect metadata from the source database and the destination database, and store the metadata in the data center;

[0012] Step 2: Determine the source data source and source table in the data center, and determine the fields to be synchronized and the primary key of the source table;

[0013] Step 3: Determine the destination data source and target table in the data center, and determine the fields of the synchronized data to be received and the primary key of the target table;

[0014] Step 4: Establish a mapping relationship between the source table and the target table based on the primary key of the source table and the primary key of the target table;

[0015] Step 5: Create a log table based on the operation type of the metadata in the source database. The log table includes all fields before modification, all fields after modification, operation type and operation time. Operation types include add operation, modify operation and delete operation.

[0016] Step 6: Create a trigger on the source table in the source database. The trigger records the data changes caused by the operation in the log table.

[0017] Step 7: Build the Source and Sink configurations of the distributed stream processing engine.

[0018] Based on the above technical solution, preferably, the metadata includes the Chinese name of the table, the English name of the table, the Chinese name of the field, the English name of the field, the data type, whether it is empty, the length and the primary key.

[0019] Based on the above technical solution, preferably, step 4 specifically includes:

[0020] Filter the metadata of the source database based on the primary key of the source table, determine the fields to be synchronized, and sort the fields to be synchronized;

[0021] Match the fields to be synchronized in the source table according to the primary key of the target table, and determine the target field corresponding to the field to be synchronized in the target table;

[0022] Build the mapping relationship between the source table and the target table based on the sorted fields to be synchronized and the target fields.

[0023] Based on the above technical solution, preferably, step six specifically includes:

[0024] Create a trigger on the source table in the source database;

[0025] Use triggers to scan the log table, extract the log data in the log table and convert the format of the log data to obtain a log data queue;

[0026] Use the producer-consumer model to read data from the log data queue;

[0027] Use a distributed stream processing engine to write the read data to the destination database in real time.

[0028] Based on the above technical solution, preferably, the extracting log data and converting the format specifically includes:

[0029] When the trigger detects an unprocessed log entry in the log table, it reads the unprocessed data;

[0030] Convert the log data of the operation changes into the standardized RowData format;

[0031] An initialization queue is established, and log data in the RowData format is written into the initialization queue to obtain a log data queue.

[0032] Based on the above technical solution, preferably, the method of reading data from the log data queue using the producer-consumer mode specifically includes:

[0033] Create an InputFormat interface on the distributed stream processing engine;

[0034] Configure the InputFormat's consumer client parameters;

[0035] Use the nextRecord method to read the log data queue and use the consumer client to obtain the read data.

[0036] More preferably, the distributed stream processing engine includes a job manager, a task manager, a resource manager and a distributor.

[0037] On the basis of the above technical solution, preferably, the execution of the real-time data collection task specifically includes:

[0038] Step 1: The distributor transfers the real-time collection task to the job manager;

[0039] Step 2: The job manager applies to the resource manager for processing resource units and distributes the real-time collection tasks to the task manager, and the resource manager allocates resources to the task manager;

[0040] Step 3: The task manager registers resources with the job manager and starts executing the data collection task.

[0041] The real-time data acquisition system for heterogeneous data sources of the present invention has the following beneficial effects compared with the prior art:

[0042] (1) By combining the data center and the distributed stream processing engine, efficient, reliable, and real-time data synchronization between heterogeneous data sources is achieved. It can not only process large-scale data streams, but also ensure data consistency and integrity. It also provides flexible configuration management and monitoring capabilities, greatly improving the efficiency and quality of data integration.

[0043] (2) By automatically collecting and managing metadata and establishing precise mapping relationships between source and target tables based on metadata, human errors are reduced and the accuracy and efficiency of data synchronization are improved. This allows for better adaptation to complex heterogeneous data environments and rapid response to changes in data structures.

[0044] (3) By creating triggers and log tables in the source database, data changes can be captured in real time and converted into a standardized format, ensuring the integrity and order of data changes. This provides a reliable foundation for real-time data synchronization and can reflect the latest status of source data in a timely manner.

[0045] (4) By utilizing the distributed characteristics and componentized design of the distributed stream processing engine, it is possible to efficiently manage and schedule data collection tasks and dynamically allocate computing resources, thereby achieving high throughput and low latency in data processing. This not only improves scalability and fault tolerance, but can also automatically adjust resource allocation based on actual load conditions, ensuring stability and efficiency in large-scale data processing scenarios. BRIEF DESCRIPTION OF THE DRAWINGS

[0046] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. 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 creative work.

[0047] Figure 1 It is a block diagram of the architecture of the real-time data acquisition system for heterogeneous data sources of the present invention;

[0048] Figure 2 It is a flow chart of collection task management of the real-time data collection system of heterogeneous data sources of the present invention;

[0049] Figure 3A flowchart of the creation of a collection task flow for the real-time data collection system of heterogeneous data sources of the present invention;

[0050] Figure 4 It is a data collection flow chart of the real-time data collection system for heterogeneous data sources of the present invention. DETAILED DESCRIPTION

[0051] The following will be combined with the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0052] like Figure 1 As shown, the present invention provides a real-time data acquisition system for heterogeneous data sources, comprising:

[0053] The data center is configured to interact with the source and destination databases for metadata and create data collection tasks;

[0054] The distributed stream processing engine is configured to perform task and status interactions with the data center, receive data collection tasks submitted by the data center, execute data collection tasks, and process and transmit the collected data.

[0055] It can be understood that the data center is mainly responsible for the management, configuration and control of the real-time data collection system of heterogeneous data sources, providing infrastructure and management interfaces for data collection, and performing preliminary data preparation while processing data. The distributed stream processing engine (Flink) is responsible for performing data reading, conversion and writing operations, and uses distributed features to process large-scale real-time data streams to ensure high performance, reliability and consistency of data processing.

[0056] By combining the data middle platform and the distributed stream processing engine, efficient, reliable, and real-time data synchronization between heterogeneous data sources is achieved. It can not only process large-scale data streams, but also ensure the consistency and integrity of the data. At the same time, it provides flexible configuration management and monitoring capabilities, greatly improving the efficiency and quality of data integration.

[0057] Specifically, in one embodiment of the present application, the data middle platform includes a data security management module, a data quality management module, a data standard management module, a master data management module, a data acquisition management module and a metadata management module.

[0058] The data center consists of modules such as data security management, data quality management, data standard management, master data management, data collection management, and metadata management. Data security management is used to implement data access control and authority management to ensure data privacy and compliance; data quality management is used to monitor and evaluate the accuracy, integrity, and consistency of data; data standard management is used to formulate and maintain standards such as data formats and naming rules; master data management is used to manage authoritative data versions; data collection management is used to manage and execute collection tasks for real-time data streams; and metadata management is used to maintain data asset structures, relationships, and attribute relationships.

[0059] The data center is used to manage collection tasks in real-time data collection. The data source and data destination, as well as their mapping relationships, are configured in the collection task. After the real-time collection task is started, the data center starts scanning the log table and submitting the real-time collection task.

[0060] In an embodiment of the present application, creating a data collection task specifically includes:

[0061] Step 1: Collect metadata from the source database and the destination database, and store the metadata in the data center; the metadata includes the Chinese name of the table, the English name of the table, the Chinese name of the field, the English name of the field, the data type, whether it is empty, the length, and the primary key;

[0062] Step 2: Determine the source data source and source table in the data center, and determine the fields to be synchronized and the primary key of the source table;

[0063] Step 3: Determine the destination data source and target table in the data center, and determine the fields of the synchronized data to be received and the primary key of the target table;

[0064] Step 4: Establish a mapping relationship between the source table and the target table based on the primary key of the source table and the primary key of the target table;

[0065] Furthermore, step four specifically includes:

[0066] Filter the metadata of the source database based on the primary key of the source table, determine the fields to be synchronized, and sort the fields to be synchronized;

[0067] Match the fields to be synchronized in the source table according to the primary key of the target table, and determine the target field corresponding to the field to be synchronized in the target table;

[0068] Build the mapping relationship between the source table and the target table based on the sorted fields to be synchronized and the target fields;

[0069] Step 5: Create a log table based on the operation type of the metadata in the source database. The log table includes all fields before modification, all fields after modification, operation type and operation time. Operation types include add operation, modify operation and delete operation.

[0070] Step 6: Create a trigger on the source table in the source database. The trigger records the data changes caused by the operation in the log table.

[0071] Furthermore, step six specifically includes:

[0072] Create a trigger on the source table in the source database;

[0073] Use triggers to scan the log table, extract the log data in the log table and convert the format of the log data to obtain a log data queue;

[0074] Use the producer-consumer model to read data from the log data queue;

[0075] Use a distributed stream processing engine to write the read data to the destination database in real time.

[0076] Step 7: Build the Source and Sink configurations of the distributed stream processing engine.

[0077] Among them, the Source configuration is to define how to read data from the log table or log data queue, set the frequency of data reading, batch size and other parameters, and configure the data format conversion logic; the Sink configuration is to specify the connection information of the target database, define the data writing method (such as batch writing, single writing), set the concurrency of data writing, buffer strategy, etc.

[0078] As can be understood by those skilled in the art, the producer-consumer model is used to solve the data sharing and synchronization problems between multiple threads. The producer is the log table scan and trigger, detects data changes, reads unprocessed data, converts the data into RowData format, and writes the data in RowData format into the log data queue; the consumer is the Source component of the distributed stream processing engine (Flink), which reads data from the log data queue by implementing the nextRecord method of the InputFormat interface. The log data queue serves as a shared data structure between the producer and the consumer.

[0079] By automatically collecting and managing metadata and establishing precise mapping relationships between source and target tables based on metadata, the configuration process of data synchronization tasks is greatly simplified, which not only reduces human errors but also improves the accuracy and efficiency of data synchronization, enabling the real-time data acquisition system for heterogeneous data sources to better adapt to complex heterogeneous data environments and quickly respond to changes in data structures.

[0080] See also Figure 2 and 3 , using a specific example to illustrate the collection task management process and the collection task creation process:

[0081] (1) Add data source. Add source data source and destination data source in the data collection management module of the data center. Data source information includes: database IP, database port, account, password, database name, mode name, and instance name.

[0082] (2) Metadata collection. Collect metadata of the source data source and the destination data source, and save the metadata in the data center. Metadata includes: table Chinese name, table English name, field Chinese name, field English name, data type, whether it is empty, length, and primary key. Metadata is used to create real-time collection tasks.

[0083] (3) Create a collection task.

[0084] a. Determine the data source. First select the source data source, then select the source table to be synchronized, then select the fields to be synchronized, and finally select the primary key based on which the real-time synchronization data will be based.

[0085] b. Determine the destination of the data. First select the destination data source, then select the target table to be synchronized, then select the field to receive the synchronized data, and finally select the primary key based on which the real-time synchronized data will be based.

[0086] c. Establish a mapping relationship. Establish a mapping relationship between the source table fields and the target table fields, select the fields in the source table that need to be synchronized in order, select the fields in the target table that need to be synchronized in order, and establish the corresponding relationship for data synchronization in order. Perform synchronization according to this relationship.

[0087] The data mapping table is as follows:

[0088]

[0089]

[0090] d. Create a log table. Create a log table in the source database based on the metadata to record the changes of added, modified, and deleted data. The log table includes all fields before modification, all fields after modification, operation type, and operation time. The operation types are add, modify, and delete.

[0091] e. Create a trigger. Create a trigger on the source table in the source database to record data changes in the log table for addition, modification, and deletion operations.

[0092] f. Build the Source configuration. Use the source tables and fields of the source database as the input of the collection job source.

[0093] g. Build the Sink configuration and use the target table and fields of the database as the output of the collection job sink.

[0094] (4) Submit the collection task. The data center submits the collection task to Flink's task dispatcher Dispatcher. Flink consists of four components that work together to start executing real-time data collection tasks. The data center starts scanning the log table.

[0095] (5) Stop the collection task. The data center will submit a stop request to the Dispater. The data center checks the task status, which may be a successful stop or a failed stop. The data center stops scanning the log table.

[0096] In an embodiment of the present application, extracting log data and converting the format specifically includes:

[0097] When the trigger detects an unprocessed log entry in the log table, it reads the unprocessed data;

[0098] Convert the log data of the operation changes into the standardized RowData format;

[0099] An initialization queue is established, and log data in the RowData format is written into the initialization queue to obtain a log data queue.

[0100] Furthermore, the producer-consumer model is used to read data from the log data queue, including:

[0101] Create an InputFormat interface on the distributed stream processing engine;

[0102] Configure the InputFormat's consumer client parameters;

[0103] Use the nextRecord method to read the log data queue and use the consumer client to obtain the read data.

[0104] By creating triggers and log tables in the source database, the real-time data acquisition system for heterogeneous data sources can capture data changes in real time and convert these changes into a standardized format, ensuring the integrity and order of data changes, providing a reliable foundation for real-time data synchronization, and enabling the real-time data acquisition system for heterogeneous data sources to promptly reflect the latest status of the source data.

[0105] See also Figure 4 , using a specific example to illustrate the data collection process:

[0106] (1) Process the log table. Scan the log table. If there is unprocessed data in the log table, read the unprocessed data. Convert the log data of the add, modify, and delete operations into RowData format, and write the RowData format data into the log data queue. Change the log status to processed.

[0107] (2) Flink reads data. It uses the producer-consumer model to read data from the log data queue. It implements the nextRecord method in the Source core interface InputFormat to read data from the log data queue in the nextRecord method.

[0108] (3) Flink writes data. Transformation does not transform the data stream, but directly uses the data stream in the Source as the input of the Sink. Flink provides sinks such as MySQL, Oracle, SQLServer, Kafka, and Hive. It supports writing data to the above data destinations.

[0109] In one embodiment of the present application, the distributed stream processing engine includes a job manager, a task manager, a resource manager and a distributor.

[0110] The distributed stream processing engine (Flink) is run by four components, including the job manager (JobManager), task manager (TaskManager), resource manager (ResourceManager) and dispatcher (Dispatcher). JobManager is responsible for controlling the execution of all collection tasks. ResourceManager manages Flink's processing resource units. TaskManager is one or more Flink worker processes. Dispatcher provides WebUI to manage the operation of multiple collection tasks, receive collection tasks that need to be submitted for execution, and display and monitor the execution of all collection tasks.

[0111] In an embodiment of the present application, executing the real-time data collection task specifically includes:

[0112] Step 1: The distributor transfers the real-time collection task to the job manager;

[0113] Step 2: The job manager applies to the resource manager for processing resource units and distributes the real-time collection tasks to the task manager, and the resource manager allocates resources to the task manager;

[0114] Step 3: The task manager registers resources with the job manager and starts executing the data collection task.

[0115] By utilizing the distributed characteristics and componentized design of the distributed stream processing engine (Flink), data collection tasks can be efficiently managed and scheduled, computing resources can be dynamically allocated, and high throughput and low latency of data processing can be achieved. This not only improves the scalability and fault tolerance of the real-time data collection system for heterogeneous data sources, but also can automatically adjust resource allocation according to actual load conditions, ensuring stability and efficiency in large-scale data processing scenarios.

[0116] Specifically, the distributed stream processing engine (Flink) is explained with a specific example:

[0117] The data collection management of the data center submits the real-time collection tasks to the Dispatcher;

[0118] The distributor transfers the real-time collection task to a JobManager;

[0119] JobManager applies to ResourceManager for processing resource units;

[0120] After applying for sufficient processing resources, the task is distributed to TaskManager for actual execution;

[0121] After startup, the TaskManager registers the resources it uses with the JobManager.

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

Claims

1. A real-time data collection system for heterogeneous data sources, characterized in that: include: The data center is configured to interact with the source and destination databases for metadata and create data collection tasks; The distributed stream processing engine is configured to perform task and status interactions with the data center, receive data collection tasks submitted by the data center, execute data collection tasks, and process and transmit the collected data.

2. A real-time data collection system for heterogeneous data sources as claimed in claim 1, characterized in that: The data middle platform includes a data security management module, a data quality management module, a data standard management module, a master data management module, a data acquisition management module and a metadata management module.

3. A real-time data collection system for heterogeneous data sources as claimed in claim 1, characterized in that: The creation of the data collection task specifically includes: Step 1: Collect metadata from the source database and the destination database, and store the metadata in the data center; Step 2: Determine the source data source and source table in the data center, and determine the fields to be synchronized and the primary key of the source table; Step 3: Determine the destination data source and target table in the data center, and determine the fields of the synchronized data to be received and the primary key of the target table; Step 4: Establish a mapping relationship between the source table and the target table based on the primary key of the source table and the primary key of the target table; Step 5: Create a log table based on the operation type of the metadata in the source database. The log table includes all fields before modification, all fields after modification, operation type and operation time. Operation types include add operation, modify operation and delete operation. Step 6: Create a trigger on the source table in the source database. The trigger records the data changes caused by the operation in the log table. Step 7: Build the Source and Sink configurations of the distributed stream processing engine.

4. A real-time data collection system for heterogeneous data sources as claimed in claim 3, characterized in that: The metadata includes the Chinese name of the table, the English name of the table, the Chinese name of the field, the English name of the field, the data type, whether it is empty, the length and the primary key.

5. A real-time data collection system for heterogeneous data sources as claimed in claim 4, characterized in that: Step 4 specifically includes: Filter the metadata of the source database based on the primary key of the source table, determine the fields to be synchronized, and sort the fields to be synchronized; Match the fields to be synchronized in the source table according to the primary key of the target table, and determine the target field corresponding to the field to be synchronized in the target table; Build the mapping relationship between the source table and the target table based on the sorted fields to be synchronized and the target fields.

6. A real-time data collection system for heterogeneous data sources as claimed in claim 5, characterized in that: Step 6 specifically includes: Create a trigger on the source table in the source database; Use triggers to scan the log table, extract the log data in the log table and convert the format of the log data to obtain a log data queue; Use the producer-consumer model to read data from the log data queue; Use a distributed stream processing engine to write the read data to the destination database in real time.

7. A real-time data collection system for heterogeneous data sources as claimed in claim 6, characterized in that: The extracting of log data and performing format conversion specifically includes: When the trigger detects an unprocessed log entry in the log table, it reads the unprocessed data; Convert the log data of the operation changes into the standardized RowData format; An initialization queue is established, and log data in the RowData format is written into the initialization queue to obtain a log data queue.

8. A real-time data collection system for heterogeneous data sources as claimed in claim 7, characterized in that: The producer-consumer mode is used to read data from the log data queue, specifically including: Create an InputFormat interface on the distributed stream processing engine; Configure the InputFormat's consumer client parameters; Use the nextRecord method to read the log data queue and use the consumer client to obtain the read data.

9. The real-time data collection system for heterogeneous data sources according to claim 1, characterized in that: The distributed stream processing engine includes a job manager, a task manager, a resource manager and a distributor.

10. A real-time data collection system for heterogeneous data sources as claimed in claim 9, characterized in that: The execution of the real-time data collection task specifically includes: Step 1: The distributor transfers the real-time collection task to the job manager; Step 2: The job manager applies to the resource manager for processing resource units and distributes the real-time collection tasks to the task manager, and the resource manager allocates resources to the task manager; Step 3: The task manager registers resources with the job manager and starts executing the data collection task.

Images