Multi-source heterogeneous data acquisition and storage method based on data medium table
Through the multi-source heterogeneous data acquisition and storage method based on the data middle platform, the problems of inefficiency and data quality of multi-source heterogeneous data acquisition and storage in the prior art are solved, and comprehensive coverage and efficient management of complex data environments are achieved, and the timeliness and flexibility of data acquisition and storage are improved.
Patent Information
- Application Number
- CN202411764649.0
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-12-04
- Publication Date
- 2025-03-25
AI Technical Summary
The existing technology is difficult to effectively deal with the collection and storage of multi-source heterogeneous data, resulting in low data collection efficiency, different data quality, and difficulty in supporting multiple types of data storage, affecting subsequent data analysis and business decisions.
Multi-source heterogeneous data acquisition and storage methods based on the data middle platform are adopted. By identifying the data source type, appropriate collection strategies (real-time or offline), combined with efficient task scheduler configuration and execution, the collected data is stored using the data lake warehouse.
It achieves comprehensive coverage and efficient management of complex data environments, improves the accuracy and timeliness of data collection, enhances the flexibility and real-timeness of data storage, and provides strong data support and guarantees for data-driven business decisions.
Smart Images
Figure CN119691037A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the field of data processing technology, and specifically to a multi-source heterogeneous data collection and storage method based on a data center. Background Art
[0002] With the rapid development of information technology, enterprises are facing increasingly diverse data sources, including structured data (such as database records), semi-structured data (such as JSON, XML files, etc.) and unstructured data (such as text, images, videos, etc.). These data are often distributed in different systems, platforms and devices, showing significant heterogeneity, and also facing the real-time requirements of data integration. Traditional data collection and storage methods are difficult to effectively cope with this complexity, resulting in low data collection efficiency, uneven data quality, and difficulty in supporting multiple types of data storage, which in turn affects subsequent data analysis and business decisions.
[0003] In the field of data processing and big data technology, multi-source heterogeneous data collection has always been one of the great challenges faced by enterprises. Existing technical solutions mostly rely on traditional ETL (Extract, Transform, Load) tools and CDC-based data change capture technology for data collection.
[0004] Data storage generally uses two storage methods to manage data: 1) Data warehouse, which mainly stores structured data organized in relational databases. Data is imported into the target table through extraction, conversion, and loading. Data warehouses have high storage costs and low flexibility; 2) Data lake, which can store any type of data, including unstructured data such as pictures and documents. The storage cost is low, but the real-time performance and data quality are poor.
[0005] Although traditional ETL tools can support data extraction, conversion, and loading, their processing efficiency and flexibility are insufficient when faced with large-scale, real-time, multi-source heterogeneous data. ETL usually requires extracting data from various source systems first, then going through complex conversion logic, and finally loading it into the target data warehouse. During this process, format differences and inconsistent structures between different data sources often make the conversion logic complex and difficult to maintain, while increasing the risk of data errors.
[0006] The core principle of the database real-time data collection method is to monitor and capture database changes, capture these changes in the order of occurrence, and write the captured data into the database. Common real-time data collection CDC (Change Data Capture) includes two methods: query-based CDC and log-based CDC. Among them, query-based CDC is batch processing, which obtains the latest data in the table through queries each time, and cannot guarantee real-time and data consistency. For example, DataX, Sqoop, kettle, etc. are all query-based CDC; log-based CDC uses the database log file binlog, which fully records the changes in the database, thus ensuring real-time and data consistency, and can achieve streaming consumption.
[0007] The data collected is often stored in a data warehouse. The data warehouse is centered on a highly structured relational database and focuses on storing pre-processed structured data. In contrast, the data lake is more open and accepts a wide range of data, but it mainly stores data in raw format and requires additional data processing. The lake-warehouse integrated architecture achieves the integration of the two, uniformly storing processed data and its original format, and supporting comprehensive and flexible data management and analysis.
[0008] Diversity of data sources and difficulty in integration: Existing data collection technologies often have difficulty in effectively dealing with diverse data sources, including relational databases, non-relational databases, file storage, Web API interfaces, and real-time data streams. This invention effectively solves the problem of integrating multi-source heterogeneous data by providing flexible collection strategy options, and enables one tool to cover almost all common data formats.
[0009] Low data collection efficiency: Traditional data collection methods often use a single collection mode and cannot flexibly adjust the collection strategy according to the characteristics of the data source, resulting in low data collection efficiency. This invention combines real-time and offline collection modes and uses an efficient task scheduling mechanism to significantly improve the efficiency and real-time performance of data collection.
[0010] Storage data redundancy: In traditional data warehouse and data lake architecture, data redundancy is serious, which increases storage costs. Data may be stored in multiple systems at the same time, resulting in resource waste. Through a unified data storage architecture, data redundancy can be significantly reduced, and centralized data management and optimized storage can be achieved. Through appropriate data tiering and lifecycle management strategies, overall storage costs can be reduced. Summary of the invention
[0011] In view of the above-mentioned defects or deficiencies in the prior art, the present application aims to provide a multi-source heterogeneous data collection and storage method based on a data center, and the multi-source heterogeneous data collection and storage method comprises the following steps:
[0012] Step 1: Determine the type of data source, create and configure the data source according to the type, and configure the data source information in the corresponding integrator;
[0013] Step 2: After completing the data source configuration, you need to create a task scheduler, which is used to define the execution mode of data collection tasks, task execution blocking processing strategy and routing strategy;
[0014] Step 3. After configuring the data source and task scheduler respectively, you can create specific data collection tasks. The offline data collection task requires ETL visual process design. The real-time data collection task is based on Flink CDC and requires Flink configuration. The ETL process design in the offline data collection task is used to extract data from the data source, convert the data format or perform data processing and calculation, and finally load the data into the target data storage system. Through the graphical configuration method, users do not need to pay attention to the internal details of each database to perform offline data collection.
[0015] Preferably, the data sources supported in step one are relational, non-relational, structured, unstructured and semi-structured data, including mainstream databases, message queues, and files such as Mysql, SQL server, Oracle, PostgreSQL, Doris, DaMeng, MongoDB, Kafka, excel, xml, csv, etc.
[0016] Preferably, for relational databases, the main configuration items include IP address, port, user name, password, database driver, etc.; for non-relational databases, the main configuration items include IP address, port, user name, password, etc.; for semi-structured data such as excel, txt, csv, etc., the corresponding configuration items include data communication protocol type, communication protocol information, file encoding, file source directory, local storage directory; for real-time data data sources, the configuration items include topics, broker lists, and consumer groups.
[0017] Preferably, the execution method in step 2 specifically includes: manual execution - manually execute the task as needed; scheduled scheduling - use CRON expression to configure the task execution time; fixed cycle - periodically execute the task at a given time; subtask execution - if other tasks need to be executed after the execution of this task is completed, and the tasks are interdependent and serially scheduled, then other tasks can be set as subtasks of this task. The task execution blocking processing strategy specifically includes: single-machine serial - the request enters the FIFO queue and runs in serial mode; discard subsequent scheduling - after blocking, the current request will be discarded and marked as failed; overwrite the previous scheduling - after blocking, the running task will be terminated and the queue will be cleared, and then the current request task will be run.
[0018] Preferably, the multi-source heterogeneous data collection and storage method uses a graphical interface to drag components to build an ETL process. The components include input, output, data conversion, data calculation and other components, as well as a target data storage connector. Then, the properties of each component are configured, such as data table name, field mapping, conversion logic, and calculation process. For file type data: semi-structured data is supported for warehousing, and unstructured data is supported for entering the lake. The ETL for warehousing file collection needs to configure configuration items such as file encoding format, data type, input field, field mapping, etc. Then, a data model is created in the data lake warehouse based on the input data field.
[0019] Preferably, the communication protocol information includes server IP, port, user name, and password.
[0020] Preferably, the data communication protocol types include ftp and sftp.
[0021] Preferably, the task scheduler is used to define the execution mode of the data collection task, and the execution mode includes manual execution, timed scheduling, fixed cycle, and subtask execution.
[0022] Preferably, the task execution blocking processing strategy includes single-machine serialization, discarding subsequent scheduling, and overwriting previous scheduling.
[0023] Preferably, the routing strategies include failover, busy-flip, round-robin, and random.
[0024] Beneficial effects:
[0025] 1. This multi-source heterogeneous data collection and storage method based on the data center identifies the data source type, flexibly selects the appropriate collection strategy (real-time or offline), combines efficient task scheduler configuration and execution, and uses the data lake warehouse to store the collected data, thereby achieving comprehensive coverage and efficient management of complex data environments. This method not only improves the accuracy and timeliness of data collection, but also enhances the flexibility and real-time performance of data storage, providing strong data support and guarantee for data-driven business decisions.
[0026] 2. This multi-source heterogeneous data collection and storage method based on the data center introduces real-time data collection in ETL. The real-time data collection task needs to configure the data source in Flink, specify the database and table to capture changes, configure database connection information, user permissions, configure Flink version, execution mode and resource parameters, etc. By integrating the real-time data integration task in ETL, the shortcomings of the previous framework are made up, and the database is synchronized to the message queue or data lake warehouse in real time or offline; it supports full and incremental streaming collection at the same time; it does not need to be deployed on the collected server like other CDC tools, which reduces maintenance costs and simplifies real-time links; the data stream obtained by CDC can be directly connected to ETL for data processing, and a set of code can complete the extraction, conversion and writing of data. BRIEF DESCRIPTION OF THE DRAWINGS
[0027] Other features, objects and advantages of the present application will become more apparent by reading the detailed description of non-limiting embodiments made with reference to the following drawings:
[0028] Figure 1 The figure is a schematic diagram of the data collection process of the present invention. DETAILED DESCRIPTION
[0029] The present application is further described in detail below in conjunction with the accompanying drawings and embodiments. It is to be understood that the specific embodiments described herein are only used to explain the relevant invention, rather than to limit the invention. It is also necessary to explain that, for ease of description, only the parts related to the invention are shown in the accompanying drawings.
[0030] The accompanying drawings in the embodiments of the present invention: different types of section lines in the drawings are not marked according to national standards, nor do they impose requirements on the materials of the components. Instead, they are used to distinguish the cross-sectional views of the components in the drawings.
[0031] See also Figure 1 A multi-source heterogeneous data collection and storage method based on a data center platform, the multi-source heterogeneous data collection and storage method comprises the following steps:
[0032] Step 1: Determine the type of data source, create and configure the data source according to the type, and configure the data source information in the corresponding integrator;
[0033] Step 2: After completing the data source configuration, you need to create a task scheduler, which is used to define the execution mode of data collection tasks, task execution blocking processing strategy and routing strategy;
[0034] Step 3. After configuring the data source and task scheduler respectively, you can create specific data collection tasks. The offline data collection task requires ETL visual process design. The real-time data collection task is based on Flink CDC and requires Flink configuration. The ETL process design in the offline data collection task is used to extract data from the data source, convert the data format or perform data processing and calculation, and finally load the data into the target data storage system. Through the graphical configuration method, users do not need to pay attention to the internal details of each database to perform offline data collection.
[0035] Preferably, the data sources supported in step one are relational, non-relational, structured, unstructured and semi-structured data, including mainstream databases, message queues, and files such as Mysql, SQL server, Oracle, PostgreSQL, Doris, DaMeng, MongoDB, Kafka, excel, xml, csv, etc.
[0036] Preferably, for relational databases, the main configuration items include IP address, port, user name, password, database driver, etc.; for non-relational databases, the main configuration items include IP address, port, user name, password, etc.; for semi-structured data such as excel, txt, csv, etc., the corresponding configuration items include data communication protocol type, communication protocol information, file encoding, file source directory, local storage directory; for real-time data data sources, the configuration items include topics, broker lists, and consumer groups.
[0037] Among them, the execution methods in step 2 specifically include: manual execution - manually execute tasks as needed; scheduled scheduling - use CRON expressions to configure task execution time; fixed cycle - periodically execute tasks at a given time; subtask execution - if other tasks need to be executed after the execution of this task is completed, and the tasks are interdependent and scheduled serially, then other tasks can be set as subtasks of this task. The task execution blocking processing strategy specifically includes: single-machine serial - the request enters the FIFO queue and runs in serial mode; discard subsequent scheduling - after blocking, this request will be discarded and marked as failed; overwrite the previous scheduling - after blocking, the running task will be terminated and the queue will be cleared, and then the current request task will be run.
[0038] Among them, the multi-source heterogeneous data collection and storage method uses a graphical interface to drag components to build the ETL process. The components include input, output, data conversion, data calculation and other components, as well as the target data storage connector, and then configure the properties of each component, such as data table name, field mapping, conversion logic, and calculation process. For file type data: semi-structured data can be put into the warehouse, and unstructured data can be put into the lake. The ETL for collecting files in the warehouse needs to configure the configuration items such as file encoding format, data type, input field, field mapping, etc. Then, according to the input data field, a data model is created in the data lake warehouse.
[0039] Among them, the communication protocol information includes server IP, port, user name, and password.
[0040] Among them, data communication protocol types include ftp and sftp.
[0041] Among them, the task scheduler is used to define the execution mode of the data collection task, which includes manual execution, scheduled scheduling, fixed cycle, and subtask execution.
[0042] Among them, the task execution blocking processing strategies include single-machine serial, discarding subsequent scheduling, and overwriting previous scheduling.
[0043] Among them, routing strategies include failover, busy transfer, polling, and random.
[0044] It should be noted that the present invention provides a set of real-time and offline collection methods that support multi-source heterogeneous data. This method identifies the data source type, flexibly selects a suitable collection strategy (real-time or offline), combines efficient task scheduler configuration and execution, and uses the data lake warehouse to store the collected data, thereby achieving comprehensive coverage and efficient management of complex data environments. This method not only improves the accuracy and timeliness of data collection, but also enhances the flexibility and real-time performance of data storage, providing strong data support and guarantee for data-driven business decisions.
[0045] The core principle of this database real-time data collection method is to monitor and capture database changes, capture these changes in the order they occur, and write the captured data into the database. Common real-time data collection CDC (Change Data Capture) uses the database log file binlog, which fully records database changes, thus ensuring real-time and data consistency, and can achieve streaming consumption. The process of real-time data collection can be expressed as the following formula:
[0046]
[0047] Among them, Collect rt(S, F, T) means data is collected from data source S at frequency F at time point T. D(T) is the collected data, which is a function of time T and represents the change of data over time. It means applying the processing function P to the collected data D(T) to obtain the processed data. R is the target storage location where the processed data will be sent.
[0048] In offline data collection tasks, the ETL process is designed to extract data from the data source, convert the data format or perform data processing and calculation, and finally load the data into the target data storage system. Through graphical configuration, users can collect data offline without paying attention to the internal details of each database. The process of offline data collection can be expressed as the following formula:
[0049]
[0050] Among them, Collect ol (S, Q) means extracting data from data source S according to query condition Q to obtain data D. It means that the processed data D is obtained by applying the processing or transformation rule F to the data D, and R is the target storage location where the processed data will be sent.
[0051] like Figure 1 This is a flow chart of an embodiment of the multi-source heterogeneous data collection method of the present invention, and the process is described in detail as follows:
[0052] First, you need to determine the type of data source, and create and configure the data source according to the type. Configure the data source information in the corresponding integrator. Supported data sources are relational, non-relational, structured, unstructured and semi-structured data, including Mysql, SQL server, Oracle, PostgreSQL, Doris, DaMeng, MongoDB, Kafka, excel, xml, csv and other mainstream databases, message queues, and files. For relational databases, the main configuration items include IP address, port, user name, password, database driver, etc.; for non-relational databases, the main configuration items include IP address, port, user name, password, etc.; for semi-structured data such as excel, txt, csv, etc., the corresponding configuration items include data communication protocol type (ftp / sftp), communication protocol information (server IP, port, user name, password), file encoding, file source directory, local storage directory, etc.; for real-time data data sources, configuration items include topics, broker lists, consumer groups, etc.
[0053] After completing the data source configuration, you need to create a task scheduler. The task scheduler is used to define the execution mode of data collection tasks (manual execution, scheduled scheduling, fixed cycle, subtask execution), task execution blocking processing strategy (single machine serial, discard subsequent scheduling, overwrite previous scheduling) and routing strategy (failover, busy transfer, polling, random, etc.). The execution modes include: manual execution - manually execute tasks as needed; scheduled scheduling - use CRON expressions to configure the task execution time; fixed cycle - periodically execute tasks at a given time; subtask execution - if other tasks need to be executed after the execution of this task, and the tasks are interdependent and serially scheduled, other tasks can be set as subtasks of this task. The task execution blocking processing strategy includes: single machine serial - the request enters the FIFO queue and runs in serial mode; discard subsequent scheduling - after blocking, this request will be discarded and marked as failed; overwrite previous scheduling - after blocking, the running task will be terminated and the queue will be cleared, and then the request task will be run.
[0054] After configuring the data source and task scheduler respectively, you can create specific data collection tasks. The offline data collection task requires ETL visual process design, and the real-time data collection task is implemented based on Flink CDC, which requires Flink configuration. In the offline data collection task, the ETL process design is used to extract data from the data source, convert the data format or perform data processing and calculation, and finally load the data into the target data storage system. Through the graphical configuration method, users can perform offline data collection without paying attention to the internal details of each database. Specifically, first use the graphical interface to drag and drop components to build the ETL process. These components usually include input, output, data conversion, data calculation and other components, as well as the target data storage connector. Then configure the properties of each component, such as data table name, field mapping, conversion logic, calculation process, etc. For file type data: support semi-structured data (such as excel, txt, csv and other files) into the library, and unstructured data into the lake. The ETL for file collection into the library needs to configure configuration items such as file encoding format (utf-8, gbk, unicode, etc.), data type (JSON, XML, symbol segmentation), input field, field mapping, etc. Then, a data model is created in the data lake warehouse according to the input data field. The data will be stored in its original format to retain its integrity and flexibility so that various types of data processing and analysis can be performed later. Since most ETL tools do not have real-time data perception of the database, they cannot meet the scenario of real-time processing of big data. Therefore, the present invention introduces real-time data acquisition in ETL. The real-time data acquisition task needs to configure the data source in Flink, specify the database and table to capture the changes, configure the database connection information, user permissions, configure the Flink version, execution mode and resource parameters, etc. By integrating the real-time data integration task in ETL, the shortcomings of the previous framework are made up, and the database is synchronized to the message queue or data lake warehouse in real time or offline; it also supports full and incremental streaming acquisition; it does not need to be deployed on the collected server like other CDC tools, which reduces maintenance costs and simplifies real-time links; the data stream obtained by CDC can be directly connected to ETL for data processing, and a set of code can complete the extraction, conversion and writing of data.
[0055] 1. Collection strategy combining real-time and offline data
[0056] The present invention proposes a collection method that combines real-time and offline data. This innovation achieves comprehensive coverage of high-timeliness data and static data by combining real-time data stream processing and scheduled task scheduling, significantly improving the efficiency and flexibility of data collection.
[0057] 2. Integrated data cleaning and conversion module
[0058] In order to ensure the accuracy and consistency of data, the present invention integrates data cleaning and conversion modules in ETL. By preprocessing the original data, noise, errors and inconsistent formats are removed, providing a high-quality data source for subsequent data analysis and application.
[0059] 3. Flexible and low-cost storage strategy
[0060] The present invention utilizes a lake-warehouse integrated design, combining the standardization of data warehouses and the flexibility of data lakes. Through a unified data storage architecture, it can significantly reduce data redundancy and achieve centralized management and optimized storage of data.
[0061] It should be noted that, in the absence of conflict, the embodiments and features in the embodiments of the present application can be combined with each other. The present application will be described in detail below with reference to the accompanying drawings and in combination with the embodiments.
[0062] The above description is only a preferred embodiment of the present application and an explanation of the technical principles used. Those skilled in the art should understand that the scope of the invention involved in the present application is not limited to the technical solution formed by a specific combination of the above technical features, but should also cover other technical solutions formed by any combination of the above technical features or their equivalent features without departing from the inventive concept. For example, the above features are replaced with (but not limited to) technical features with similar functions disclosed in the present application.
Claims
1. A multi-source heterogeneous data collection and storage method based on a data center, characterized by: The multi-source heterogeneous data collection and storage method comprises the following steps: Step 1: Determine the type of data source, create and configure the data source according to the type, and configure the data source information in the corresponding integrator; Step 2: After completing the data source configuration, you need to create a task scheduler, which is used to define the execution mode of data collection tasks, task execution blocking processing strategy and routing strategy; Step 3. After configuring the data source and task scheduler respectively, you can create specific data collection tasks. The offline data collection task requires ETL visual process design. The real-time data collection task is based on Flink CDC and requires Flink configuration. The ETL process design in the offline data collection task is used to extract data from the data source, convert the data format or perform data processing and calculation, and finally load the data into the target data storage system. Through the graphical configuration method, users do not need to pay attention to the internal details of each database to perform offline data collection.
2. According to claim 1, a multi-source heterogeneous data collection and storage method based on a data middle station is characterized in that: The data sources supported in step 1 are relational, non-relational, structured, unstructured, and semi-structured data, including mainstream databases, message queues, and files such as Mysql, SQL server, Oracle, PostgreSQL, Doris, DaMeng, MongoDB, Kafka, excel, xml, and csv.
3. According to claim 2, a multi-source heterogeneous data collection and storage method based on a data middle station is characterized in that: For relational databases, the main configuration items include IP address, port, user name, password, database driver, etc.; for non-relational databases, the main configuration items include IP address, port, user name, password, etc.; for semi-structured data such as Excel, TXT, CSV, etc., the corresponding configuration items include data communication protocol type, communication protocol information, file encoding, file source directory, local storage directory; for real-time data data sources, the configuration items include topics, broker lists, and consumer groups.
4. According to claim 1, a multi-source heterogeneous data collection and storage method based on a data middle station is characterized in that: The execution methods in step 2 specifically include: manual execution - manually execute tasks as needed; scheduled scheduling - use CRON expressions to configure task execution time; fixed cycle - periodically execute tasks at a given time; subtask execution - if other tasks need to be executed after the execution of this task is completed, and the tasks are interdependent and scheduled serially, other tasks can be set as subtasks of this task. The task execution blocking processing strategy specifically includes: single-machine serial - the request enters the FIFO queue and runs in serial mode; discard subsequent scheduling - after blocking, this request will be discarded and marked as failed; overwrite the previous scheduling - after blocking, the running task will be terminated and the queue will be cleared, and then the current request task will be run.
5. According to claim 1, a multi-source heterogeneous data collection and storage method based on a data middle station is characterized in that: This multi-source heterogeneous data collection and storage method uses a graphical interface to drag and drop components to build the ETL process. The components include input, output, data conversion, data calculation and other components, as well as the target data storage connector. Then, the properties of each component are configured, such as the data table name, field mapping, conversion logic, and calculation process. For file type data: semi-structured data can be put into the warehouse, and unstructured data can be put into the lake. The ETL for file collection needs to configure the file encoding format, data type, input field, field mapping and other configuration items. Then, according to the input data field, a data model is created in the data lake warehouse.
6. According to claim 1, a multi-source heterogeneous data collection and storage method based on a data middle station is characterized in that: Communication protocol information includes server IP, port, user name, and password.
7. According to claim 1, a multi-source heterogeneous data collection and storage method based on a data middle station is characterized in that: Data communication protocol types include ftp and sftp.
8. According to claim 1, a multi-source heterogeneous data collection and storage method based on a data middle station is characterized in that: The task scheduler is used to define the execution mode of data collection tasks, including manual execution, scheduled scheduling, fixed cycle, and subtask execution.
9. According to claim 1, a multi-source heterogeneous data collection and storage method based on a data middle station is characterized in that: Task execution blocking processing strategies include single-machine serialization, discarding subsequent scheduling, and overwriting previous scheduling.
10. According to claim 1, a multi-source heterogeneous data collection and storage method based on a data middle station is characterized in that: Routing strategies include failover, busy-follow, round-robin, and random.