Quick acquisition method for multi-source heterogeneous structured data

Abstract

The invention relates to the technical field of data acquisition, in particular to a quick acquisition method for multi-source heterogeneous structured data. The quick acquisition method comprises the following steps: acquiring acquisition configuration information including data source information, source table information, task basic information, data service classification, an acquisition mode and a mapping strategy of a source table and a target table by configuring a data connection source, a data table and a data acquisition template; storing the acquisition configuration information in an acquisition configuration table of the data acquisition template; then, automatically creating or updating a target table according to a table structure of a source table obtained from the source table information; and then, creating an ETL task node or calling the ETL task node, and executing an acquisition task. According to the method, the data acquisition templates meeting different acquisition scenes are developed by using the Kettle component, the automatic generation of the data acquisition task is realized, and the efficient and rapid acquisition of the heterogeneous structured data is completed.

Description

Technical Field

[0001] The present invention relates to the technical field of data collection, and in particular to a method for quickly collecting multi-source heterogeneous structured data. Background Art

[0002] Multi-source heterogeneous data often needs to be processed and integrated in scenarios such as data integration, data analysis, and data warehouse construction to support a wider range of analysis needs and decision support.

[0003] Using ETL (Extract, Transform, Load) tools for data collection is a common operation. Since each data source may have different access mechanisms, authentication methods, and data formats, when using traditional ETL tools to collect multiple heterogeneous data sources and large-scale data tables, it is necessary to write specific scripts or configurations for each source, thereby increasing the cost of development and maintenance. When it comes to a large number of data tables or frequent data updates, manually configuring each data collection task is not only time-consuming but also prone to errors. In order to solve the problem of low collection efficiency and high management cost caused by manual configuration, the prior art, such as: Chinese invention patent with application publication number CN117851487A, discloses a data collection method, calling the collection template corresponding to each collection task, and collecting target data from the data source corresponding to the collection template, wherein the collection template is generated based on the template configuration instruction, or is generated based on the collection item corresponding to the collection task.

[0004] In actual projects, it is found that the process of multi-source heterogeneous data collection also involves the problem of frequent data updates in a large number of data tables, as well as the need to conduct retrospective or supplementary collection of historical data on a regular basis. However, when using existing technologies to conduct retrospective and supplementary collection of frequently updated multi-source heterogeneous structured data, the collection efficiency is not ideal and the accuracy fluctuates greatly. On the other hand, common ETL tools include Kettle, Datastage, and Informatica. Although they can all be used for data collection, Kettle has become the first choice for multi-source heterogeneous data collection tools because it is written in pure JAVA, can be deployed in the JVM environment, is green and does not require installation, has good scalability, and has efficient and stable data extraction.

[0005] Therefore, the present invention proposes an innovative application method of the Kettle tool to meet the demand for quick collection of multi-source heterogeneous structured data. Summary of the invention

[0006] In view of the shortcomings of the prior art in retrospective collection or supplementary collection of frequently updated data tables, and in order to solve the problems of unsatisfactory collection efficiency and large fluctuations in accuracy, the present invention provides a quick collection method for multi-source heterogeneous structured data. The quick collection method, based on metadata management and parameterized design ideas, uses Kettle components to develop data collection templates that meet different collection scenarios, realizes automatic generation of data collection tasks, and completes efficient and quick collection of heterogeneous structured data.

[0007] First, the present invention provides a quick collection method for multi-source heterogeneous structured data, introduces Kettle components, and develops two types of data collection templates: ordinary quick data collection template and deduplication quick data collection template, which are used to perform ETL tasks; The quick collection method specifically refers to: obtaining collection configuration information including data source information, source table information, task basic information, data business classification, collection method, and mapping strategy between source table and target table by configuring data connection source, configuring data table, and configuring data collection template, and storing the collection configuration information in the collection configuration table of the data collection template; then, automatically creating or updating the target table according to the table structure of the source table obtained from the source table information; and then, creating an ETL task node or calling the ETL task node to execute the collection task.

[0008] When executing a collection task, select a data collection template from the common quick data collection template and the deduplication quick data collection template to perform full or incremental collection, depending on whether the collected data needs to be deduplicated. More specifically, if the data collection requirement is to deduplicate the data in the target table, select the deduplication quick data collection template; if deduplication is not required, select the common quick data collection template.

[0009] In some embodiments, configuring the data connection source refers to: organizing and configuring the data source information corresponding to the source database and the target database to ensure the connectivity of all data sources, and recording the configured source database and target database in a database list.

[0010] In some embodiments, the configuration data table refers to: explicitly specifying source table information corresponding to the data table to be collected, including the table name of the source table, the fields of the source table, the field types of the source table, and the field primary keys of the source table.

[0011] In some embodiments, configuring the data collection template means: configuring the basic information of the task including the task name, the collection task code, and the associated ETL task node through a graphical interface and filling in a form, defining the data business classification code through a dictionary management module, configuring the collection method, and setting the mapping strategy between the source table and the target table including the target table information, the business primary key, and the business timestamp after selecting a single or multiple source tables from the source database; The target table information includes: target table code, table name of the target table, and fields of the target table; The collection mode is divided into a full collection mode and an incremental collection mode. According to the data collection requirements in different business scenarios, a full collection mode or an incremental collection mode is configured for each ETL task node.

[0012] In some embodiments, creating an ETL task node means: first setting global parameters and loading database configuration information according to the collection configuration information, and then executing a full collection task or an incremental collection task according to the selected collection method.

[0013] When executing a full collection task, set the full information of the configuration table, extract the collection configuration table information from the data collection template, delete the data of the target table, obtain the fields of the target table and set them as variables after assembling the fields of the target table into strings, and then transfer the full data of the source table to the target table; traverse the data one by one until the task is completed, record all collection results and store them in the collection log table.

[0014] When executing an incremental collection task, first set the configuration table incremental information, obtain the collection configuration table information, determine whether to re-collect the entire table, clear the data in the entire table or delete the data in batches based on the determination result, and obtain the normal run / re-run time; then, set the business time parameters and where conditions, determine whether there is incremental data, insert incremental data or end this round of tasks based on the determination result; traverse the data one by one until the task is completed, record all collection results and store them in the collection log table.

[0015] In some embodiments, inserting the incremental data refers to: acquiring the incremental data according to the incremental data acquisition type, and inserting the incremental data of the source table into the target table.

[0016] The incremental data acquisition types are divided into two types: ordinary type and deduplication type: Common incremental data acquisition method, directly obtains incremental data based on business timestamps; Deduplicated incremental data acquisition method, which deduplicates incremental data according to business primary key partition and business timestamp in descending order.

[0017] In some embodiments, a numeric batch number is associated with each ETL task node, and the default value of the batch number is "0".

[0018] The collection method is determined by the batch number value: if the batch number value is "0", the full amount is collected, otherwise the incremental amount is collected.

[0019] Get the normal run / rerun time by the batch number in the collection log table: if the batch number exists in the collection log table, it is a rerun, and the start time and end time are obtained from the collection log table; if the batch number does not exist in the collection log table, it is a normal run, and the maximum time of previous collection is used as the start time and the current time is used as the end time.

[0020] In some embodiments, setting the business time parameters means: judging whether the current batch collection time is to collect data by time period through the start time and end time in the global parameters: if the start time and end time are passed in by the global parameters, the start time and end time of the current batch are set to the start time and end time passed in from the global parameters; otherwise, it is the start time and end time in the normal run / re-run time.

[0021] In some embodiments, setting the where condition means: splicing the where condition according to the field type of the start time, end time, database type and business timestamp and setting it as a where variable to meet the incremental collection of different database types.

[0022] In some embodiments, the quick collection method creates multiple ETL task nodes, each ETL task node runs independently or collaboratively, and when running collaboratively, multiple ETL task nodes run in series or in parallel.

[0023] Second, the present invention provides a quick collection system for multi-source heterogeneous structured data, which is used to execute the above-mentioned quick collection method for multi-source heterogeneous structured data.

[0024] The quick acquisition system includes a data source management module, a data model management module, an ELT task management module, a quick acquisition configuration module, a scheduling task management module and a task monitoring module.

[0025] Third, the present invention provides a device, specifically an electronic device.

[0026] The electronic device includes a memory and a processor; a computer program is stored in the memory; when the computer program is executed on the processor, the above-mentioned method for quickly collecting multi-source heterogeneous structured data is implemented.

[0027] Fourth, the present invention provides a medium, specifically a computer-readable storage medium.

[0028] The computer-readable storage medium stores computer instructions; when the computer instructions are executed on the electronic device, the method for quickly collecting multi-source heterogeneous structured data is implemented.

[0029] The present invention has the following beneficial effects.

[0030] (1) The method for quickly collecting multi-source heterogeneous structured data provided by the present invention develops two types of data collection templates, namely, ordinary quick data collection template and deduplicated quick data collection template, through the Kettle component, which simplifies the preparation work for data collection, realizes the automatic transmission from the source to the target source, and significantly shortens the task deployment cycle; moreover, the automated process reduces human intervention, thereby reducing the error rate caused by human negligence and improving overall work efficiency.

[0031] (2) The method for quickly collecting multi-source heterogeneous structured data provided by the present invention can quickly obtain the data source freely selected or adjusted by the user through the data collection template, making the data collection process more flexible and adaptable to different business scenarios with strong flexibility.

[0032] (3) The fast collection method for multi-source heterogeneous structured data provided by the present invention supports incremental collection and full collection. After encapsulating the collection business scenario, users can choose incremental collection or full collection according to actual needs, which not only ensures the real-time and accuracy of the data, but also takes into account the rational use of resources.

[0033] (4) The quick collection method of multi-source heterogeneous structured data provided by the present invention supports automatic creation of target tables.

[0034] (5) The method for quickly collecting multi-source heterogeneous structured data provided by the present invention supports the construction of a graphical configuration interface. Users do not need to write complex codes or scripts, but can complete the configuration of data collection tasks by simply selecting from a drop-down box and filling out a form. Users do not need to have an in-depth understanding of the underlying technical details, which lowers the operating threshold. Even non-technical personnel can easily complete data collection tasks. BRIEF DESCRIPTION OF THE DRAWINGS

[0035] Figure 1 This is a schematic diagram of the main process of a quick collection method for multi-source heterogeneous structured data described in Example 1.

[0036] Figure 2 This is the schematic diagram for data collection through the data collection template.

[0037] Figure 3 This is a flow chart of a faster data collection method encapsulated based on Kettle in Example 8.

[0038] Figure 4 for Figure 3 Schematic diagram of the sub-process for executing the full data collection task.

[0039] Figure 5 for Figure 3 Schematic diagram of the sub-process for executing incremental collection tasks.

[0040] Figure 6 A graphical user interface for configuring data source information.

[0041] Figure 7 It is a graphical user interface used to configure acquisition configuration information when designing test tasks. DETAILED DESCRIPTION

[0042] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. It should be understood that the described embodiments are only part of the embodiments of the present invention, not all of the embodiments, and therefore should not be regarded as limiting the scope of protection. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technical personnel in this field without making creative work are within the scope of protection of the present invention.

[0043] In the description of the present invention, it should be noted that, unless otherwise clearly specified and limited, the terms "disposed", "connected" and "connected" should be understood in a broad sense, for example, it can be a fixed connection, a detachable connection, or an integral connection; it can be a mechanical connection or an electrical connection; it can be a direct connection, or it can be an indirect connection through an intermediate medium, or it can be the internal communication of two components. For ordinary technicians in this field, the specific meanings of the above terms in the present invention can be understood according to specific circumstances.

[0044] Embodiment 1: This embodiment proposes a quick collection method for multi-source heterogeneous structured data, introduces the Kettle component, and develops two types of data collection templates: ordinary quick data collection template and deduplication quick data collection template, which are used to execute ETL tasks; The quick collection method, such as Figure 1 As shown, it specifically includes steps S1 to S5.

[0045] Step S1, introducing Kettle components to develop ETL templates. Specifically, it means introducing Kettle components to develop two types of data collection templates: ordinary quick data collection templates and deduplication quick data collection templates.

[0046] The common quick data collection template is used to collect full data or incremental data for data sources without deduplication requirements; The deduplication quick data collection template is used to collect full data or incremental data for a data source that contains duplicate records and needs to retain the latest data through a deduplication mechanism.

[0047] Step S2: Configure the data connection source. By configuring the data connection source, the data source information is obtained.

[0048] Step S3: Configure the data table. By configuring the data table, the data table information is obtained.

[0049] Step S4, configure the data collection template. By configuring the data collection template, basic task information, data service classification, collection method, and mapping strategy between source table and target table are obtained; on the one hand, the collection configuration information such as data source information, source table information, basic task information, data service classification, collection method, and mapping strategy between source table and target table are stored in the collection configuration table of the data collection template; on the other hand, the target table is automatically created or updated according to the table structure of the source table.

[0050] Step S5: Configure scheduling task management. The configuration scheduling task management includes creating an ETL task node or calling an ETL task node.

[0051] In this embodiment, the Kettle component is used to develop data collection templates that meet different collection scenarios: ordinary quick data collection template and deduplication quick data collection template. The ordinary quick data collection template is suitable for full and incremental collection scenarios without special deduplication requirements. It aims to achieve rapid data appending through efficient and concise processes, thereby improving data collection efficiency and flexibility. The deduplication quick data collection template is suitable for data sets that contain duplicate records and need to retain the latest data. Through an efficient deduplication mechanism, it can achieve fast and accurate collection of full and incremental data, and is particularly suitable for scenarios with high requirements for data timeliness and accuracy.

[0052] This embodiment provides a quick collection method for multi-source heterogeneous structured data, which has high data collection efficiency, strong flexibility, supports incremental collection and full collection, supports automatic creation of target tables, and has a low operating threshold. 1. Users can freely choose different data sources (source data sources and target data sources) according to their needs, making the data collection process more flexible and adaptable to different business scenarios. 2. Users can choose incremental collection or full collection according to actual needs, which not only ensures the real-time and accuracy of the data, but also takes into account the rational use of resources. 3. Users do not need to write complex codes or scripts, but can complete the configuration of data collection tasks by simply selecting from a drop-down box and filling out a form. Users do not need to have an in-depth understanding of the underlying technical details, which lowers the operating threshold. Even non-technical personnel can easily complete data collection tasks.

[0053] Embodiment 2: Based on Example 1, this example describes in detail the content of introducing the Kettle component to develop the ETL template in step S1.

[0054] First, install and configure the Kettle environment to ensure that necessary components such as table input, table output, SQL scripts, JavaScript scripts, etc. are available. Then, use the Kettle component to develop a general ETL template, which supports the introduction of global parameter definition functions for setting parameters such as batch number, collection task code, data business code, collection time period, etc. It is also used to set the driver, url, user, pwd, etc. of the source database and target database as variables for subsequent dynamic configuration.

[0055] The other parts of this embodiment are the same as those of Embodiment 1, and thus will not be described in detail.

[0056] Embodiment 3: This embodiment, based on Embodiment 1 or Embodiment 2, describes in detail the content of configuring the data connection source in step S2.

[0057] Configuring data connection sources means: organizing and configuring the data source information corresponding to the source database and target database to ensure the connectivity of all data sources, and recording the configured source database and target database in the database list.

[0058] In common scenarios, source databases such as MySQL, Oracle, PostgreSQL, etc. and target databases such as data warehouses. In order to ensure the connectivity of all data sources, data source information should be detailed and flexible, covering a variety of key parameters to support a variety of database systems, such as Oracle, MySQL, Postgresql, Hana, SQL Server, etc.

[0059] like Figure 6 As shown, it is a graphical user interface for configuring data source information. The data source information that needs to be configured includes: data source type, service IP and port, database identifier, authentication information, connection URL, proxy settings, and advanced configuration.

[0060] The data source type is used to determine the specified database system type.

[0061] The service IP and port are used to determine the network address and access port of the data source service.

[0062] The database identifier includes a database name and / or a system identifier SID, which is used to locate a specific database instance.

[0063] The authentication information, including user name and password, is used to establish connection authentication.

[0064] The connection URL is based on the JDBC connection string generated by the configuration, supports custom adjustment, and maintains property consistency.

[0065] The proxy setting provides a proxy service address that can be selected when direct connection is limited, for realizing connection forwarding.

[0066] The advanced configuration includes the connection number setting of the connection pool and the SQL statement for connection verification, which is used to optimize connection management and performance.

[0067] The other parts of this embodiment are the same as those of Embodiment 1 or Embodiment 2, and thus will not be described in detail.

[0068] Embodiment 4: This embodiment, based on any one of Embodiments 1 to 3, describes in detail the content of the configuration data table in step S3.

[0069] Accurately select the source database and target database from the configured database list to ensure that the source and target of data flow are clear. Then configure the data table: clearly specify the source table information corresponding to the data table to be collected, including the table name of the source table, the fields of the source table, the field types of the source table, and the field primary keys of the source table. The obtained source table information is used to automatically generate the target table according to the structure of the source table.

[0070] The other parts of this embodiment are the same as any one of Embodiments 1 to 3, and thus will not be described in detail.

[0071] Embodiment 5: This embodiment, based on any one of Embodiments 1 to 4, describes in detail the configuration of the data collection template in step S4.

[0072] Configuring a data collection template means: configuring basic task information, defining data business classification codes, configuring collection methods, and defining mapping strategies between source tables and target tables through a graphical interface and filling out forms.

[0073] The basic task information includes the task name, collection task code, and associated ETL task node. The collection task code is used as a core identifier to facilitate subsequent rapid retrieval and reference.

[0074] The data service classification code is flexibly defined through a dictionary management module, supports custom classification by business function, data source or data sensitivity, and enhances the convenience of data management and heterogeneous collection.

[0075] The collection mode is divided into a full collection mode and an incremental collection mode. According to the data collection requirements in different business scenarios, a full collection mode or an incremental collection mode is configured for each ETL task node.

[0076] Specifically, data collection methods are divided into full and incremental modes. The full collection method is suitable for data initialization or when the data source changes significantly, ensuring that all data is fully captured. The incremental collection method focuses on data changes since the last collection, only captures new or updated data, and efficiently processes frequently updated data sources, reducing resource consumption and improving processing efficiency. The choice of these two modes is flexibly decided based on specific business scenarios and data update frequency to meet different data processing needs.

[0077] The mapping strategy between the source table and the target table is obtained by constructing a mapping relationship between the source table and the target table. First, select a single or multiple source table from the source database, and then set the target table information, business primary key, business timestamp and other key attributes to achieve efficient one-to-one or one-to-many mapping between the source table and the target table. The target table information includes: target table code, target table name, and target table fields.

[0078] On the one hand, the data source information, source table information, basic task information, data service classification, collection method, source table and target table mapping strategy and other collection configuration information are stored in the collection configuration table of the general quick data collection template and / or the deduplication quick data collection template. In another specific embodiment, a graphical user interface for configuring the collection configuration information when designing a test task is provided, such as Figure 7 shown.

[0079] On the other hand, based on the detailed configuration of the collection configuration information, the system can implement a highly automated target table structure creation and update mechanism to ensure that the table structure matches the data requirements.

[0080] The other parts of this embodiment are the same as any one of Embodiments 1 to 4, and thus will not be described in detail.

[0081] Embodiment 6: This embodiment, based on any one of Embodiments 1 to 5, describes in detail the creation of the ETL task node in step S5.

[0082] Creating an ETL task node means: first setting global parameters and loading database configuration information according to the collection configuration information, and then executing full collection tasks or incremental collection tasks according to the selected collection method. By creating an ETL task node, combined with the collection task code and data business classification code obtained in the above steps, it is possible to automatically associate and execute the collection task with the specified code, covering all data tables under the corresponding classification.

[0083] To further meet complex business needs, multiple ETL task nodes can be flexibly created. Each ETL task node runs independently or collaboratively. When running collaboratively, multiple ETL task nodes run in series or in parallel. The collaborative operation of multiple ETL task nodes can batch collect and process diverse data sources, effectively improving the flexibility and efficiency of data processing.

[0084] The schematic diagram of data collection through the data collection template, such as Figure 2 shown.

[0085] When performing data collection through the common quick data collection template, first load the database configuration information, and then extract the collection method information from the task configuration information; when the collection method is "full", set the configuration table full information, and execute the full collection task according to the common full collection method; when the collection method is "incremental", set the configuration table incremental information, and execute the incremental collection task according to the common incremental collection method; When performing data collection through the deduplication quick data collection template, first load the database configuration information, and then extract the collection method information from the task configuration information; when the collection method is "full", set the full information of the configuration table, and execute the full collection task according to the deduplication full collection method; when the collection method is "incremental", set the incremental information of the configuration table, and execute the incremental collection task according to the deduplication incremental collection method.

[0086] The other parts of this embodiment are the same as any one of Embodiments 1 to 6, and thus will not be described in detail.

[0087] Embodiment 8: Based on the solution described in Example 1, this embodiment proposes a specific implementation plan of a system and method for quickly collecting multi-source heterogeneous structured data.

[0088] A quick collection system for multi-source heterogeneous structured data includes a data source management module, a data model management module, an ELT task management module, a quick collection configuration module, a scheduling task management module and a task monitoring module.

[0089] The data source management module is used to dynamically establish a connection pool for connecting to a specified database platform after creating a data source, and automatically create or reuse a data connection instance when the data source is subsequently used for connection access.

[0090] The data model management module is used to manage the physical tables of the data models that need to be synchronized, and supports adding, previewing, and editing the physical tables. The data table information can be configured through the data model management module.

[0091] The ELT task management module is used to manage ELT tasks, customize the ETL task execution logic and execution process in a graphical way, and debug the task in the designer. Two types of data collection templates can be developed through the ELT task management module: ordinary quick data collection template and deduplication quick data collection template.

[0092] The quick collection configuration module is used to introduce Kettle components to develop two types of data collection templates: ordinary quick data collection template and deduplicated quick data collection template; it supports the setting of information such as source and target source, mapping strategy of source table and target table, data classification, collection mode, business primary key, business timestamp, etc.; it is also used to automatically create a corresponding target table structure in the target database according to the collection configuration information.

[0093] The scheduling task management module is used for task scheduling management, and flexibly configures basic task information and arrangement logic. It supports graphical design tools, efficiently combines tasks, and realizes process scheduling of complex business logic. It covers a variety of task types, such as ETL, auditing, etc., and can directly query and bind from metadata to improve operation and maintenance efficiency.

[0094] The task monitoring module implements the task monitoring mechanism of Kettle jobs and self-developed log tables, monitors logs and exceptions in real time, and ensures transparent task execution. It provides a comprehensive monitoring list and graphical process monitoring, supports multi-dimensional query and statistics, and helps operation and maintenance to efficiently handle exceptions. In exception handling, it supports flexible operations in graphical processes, such as re-executing task links, passing parameters and supplementing sampling, etc., to enhance system robustness.

[0095] Based on the above quick collection system, this embodiment also provides a quick collection method.

[0096] Step S1, introducing Kettle components to develop ETL templates. Specifically, it means introducing Kettle components to develop two types of data collection templates: ordinary quick data collection templates and deduplication quick data collection templates.

[0097] like Figure 3 As shown, it is a flowchart of a faster data collection method based on Kettle encapsulation, and this process includes the following specific steps A-C.

[0098] Step A: Set global parameters according to the collected configuration information.

[0099] The global parameters include batch number, collection task code, data service classification code, target table code, file path, service timestamp, etc.

[0100] Batch number: The batch number is used as an identifier for each data collection. When collecting additional data, the batch number can be specified. If the task is executed normally, the default batch number is transmitted by the scheduling task in step S6, otherwise it is the specified batch number. The batch number is a numeric type, such as 20230101. When the batch number is 0, the default is to collect the entire table and must be transmitted. Each ETL task node is associated with a numeric batch number, and the default batch number is "0".

[0101] Collection task code and data service classification code: The collection task code and data service classification code are the data tables that need to be collected for positioning. When collecting supplementary data, the collection task code and data service classification code can be specified; if the task is executed normally, the default collection task code and data service classification code are transmitted from the scheduling task in step S6, otherwise the specified collection task code and data service classification code are used. The collection task code and data service classification code must exist in step S3; the collection task code and data service classification code must be transmitted.

[0102] Target table code: The target table code is used when supplementing data. You can specify the target table code for collection. If it is empty, it is taken from the collection configuration table by default, otherwise it is the specified target table code. Multiple tables are separated by semicolons and the default is empty.

[0103] File path: The default setting is the current path, no parameters are required.

[0104] Business timestamp: mainly marked by start time and end time. The start time is used when supplementing incremental data, and the data range to be collected can be specified; if it is empty, the default start time is the maximum time of the previous batch, otherwise it is the specified start time; the start time format is yyyy-mm-dd hh24:mi:ss, and the default is empty. The end time is used when supplementing incremental data, and the data range to be collected can be specified; if it is empty, the default end time is the current time, otherwise it is the specified end time; the end time format is yyyy-mm-dd hh24:mi:ss, and the default is empty.

[0105] Step B: Load database configuration information.

[0106] According to the uploaded parameters, the database connection information of the source database and the target database is obtained, such as database driver, connection URL, user name, and password. In order to ensure the security of the data, the password is encrypted.

[0107] Step C: Execute the full collection task or the incremental collection task according to the selected collection method.

[0108] When executing a full collection task, set the full information of the configuration table, extract the collection configuration table information from the data collection template, delete the data of the target table, obtain the fields of the target table and set them as variables after assembling the fields of the target table into strings, and then transfer the full data of the source table to the target table; traverse the data one by one until the task is completed, record all collection results and store them in the collection log table.

[0109] like Figure 4 As shown in the figure, the execution logic of the full collection task is as follows: P1. Set the full information of the configuration table: through the configuration table, set the source table and target table of the full collection into variables; P2. Get the information of the collection configuration table: Get the source table and target table in the collection configuration table; P3. Delete target table data: clear the data in the target table; P4. Get fields: Get the field information of the target table, spell the fields of the target table into a string, and set the string into a variable; P5. Insert target table data: insert the data from the source table into the target table; P6. Record successful collection: record the identifier, batch number and other information of successful collection in the collection log table.

[0110] When executing an incremental collection task, first set the configuration table incremental information, obtain the collection configuration table information, determine whether to re-collect the entire table, clear the data in the entire table or delete the data in batches based on the determination result, and obtain the normal run / re-run time; then, set the business time parameters and where conditions, determine whether there is incremental data, insert incremental data or end this round of tasks based on the determination result; traverse the data one by one until the task is completed, record all collection results and store them in the collection log table.

[0111] like Figure 5 As shown in the figure, the execution logic of the incremental collection task is as follows: Q1. Set the incremental information of the configuration table: through the configuration table, set the source table and target table of incremental collection as variables; Q2. Get the information of the collection configuration table: Get the source table, target table, incremental timestamp field and other information in the collection configuration table; Q3. Whether to re-collect the entire data: judge based on the batch number being "0". If the batch number is "0", collect the entire data; otherwise, collect the data incrementally. Clear data in the entire table: when the batch number is "0", the target table data will be cleared. Delete data by batch number: delete the target table data by batch number. Q4. Get the normal run / rerun time: Get the normal run / rerun time by the batch number in the collection log table: If the batch number exists in the collection log table, it is a rerun, and the start time and end time are obtained from the collection log table; if the batch number does not exist in the collection log table, it is a normal run, and the maximum time collected in the past is used as the start time, and the current time is used as the end time; Q5. Set business time parameters: Use the start time and end time passed in to determine whether the batch collection time is to collect data by time period; if the start time and end time are passed in the global parameters, the start time and end time of the batch are set to the start time and end time passed in from the global parameters, otherwise, the start time and end time set in the previous step "Get normal run / rerun time" are used; Q6. Set where conditions: Get the start time, end time and other information to form where conditions, and set them as where variables; To meet the incremental collection of different database types such as MySQL, Oracle, PostgreSQL, and SQL Server, this step needs to splice where conditions according to the database type and the field type of the business timestamp; Q7. Determine whether there is incremental data: When the collection end time is empty, there is no new data for this batch number; Q8. Get fields: Get the field information of the target table, spell the fields of the target table into a string, and set the string into a variable; Q9. Insert incremental data: Get incremental data according to the incremental data acquisition type, and insert the incremental data of the source table into the target table. There are two types of incremental data acquisition: ordinary type and deduplication type: Ordinary type: directly obtain incremental data based on business timestamps; Deduplication: Deduplication collects the latest data based on the business primary key partition and business timestamp in descending order, mainly involving the row_number window function; Q10. Record successful collection: record the successful collection identifier, batch number, the maximum time of the current batch target table as the collection end time and other information in the collection log table.

[0112] When using Kettle to encapsulate the data collection process, the design concepts of metadata management and global parameterized management are used, which simplifies the collection work for different data sources, making the configuration more flexible and easy to manage and adjust; moreover, when processing multiple data tables, unified management and batch operations can be achieved, which improves data collection efficiency and flexibility, and ensures data consistency and accuracy, so that users can obtain valuable insights more quickly.

[0113] Step S2: Configure the data connection source. By configuring the data connection source, the data source information is obtained.

[0114] Step S3: Configure the data table. By configuring the data table, the data table information is obtained.

[0115] The operation of configuring the data table supports functions such as creating database physical tables, previewing data table scripts, viewing sample data, database reverse engineering, and PDM model import. In this step, you can build and manage database models, covering the compatibility of multiple source databases such as Oracle, MySQL, DB2, and SQL Server. Through management tools such as the data model management module, you can customize the data table model, set the field type according to the characteristics of different database systems, and achieve efficient table structure design and management.

[0116] The physical table generation means that the system can automatically generate an SQL table creation script based on the user-defined data table specifications (including name, fields, etc.), or generate a modification script for an existing table. These scripts will automatically adjust the syntax according to the specific type of the connected database (such as Oracle, MySQL, etc.), and directly submit them to the database for execution to complete the creation or modification of the physical table.

[0117] The database reverse engineering refers to supporting reverse analysis functions, automatically extracting and displaying structural information of existing data tables by reading and analyzing metadata of deployed databases.

[0118] The PDM model import means that the system integrates support for Power Designer (PDM) design files, allowing users to directly import pre-designed database models (PDM files) and automatically convert the models into deployable database table structures.

[0119] Step S4, configure the data collection template. By configuring the data collection template, basic task information, data service classification, collection method, and mapping strategy between source table and target table are obtained; on the one hand, the collection configuration information such as data source information, source table information, basic task information, data service classification, collection method, and mapping strategy between source table and target table are stored in the collection configuration table of the data collection template; on the other hand, the target table is automatically created or updated according to the table structure of the source table.

[0120] The quick collection configuration template supports the setting of basic task information, including configuring the task name, task code (the code will be used as the core identifier of the task to support rapid retrieval and reference during subsequent batch heterogeneous metadata collection), and ETL task (select the ETL task associated with this collection task).

[0121] The quick collection configuration template supports source and target source settings. From the list of configured databases in step S2, select the source database and target database for this collection task, and ensure that the source database connection is stable and the permissions are configured correctly.

[0122] The quick collection configuration template supports the construction of the mapping relationship between the source table and the target table. Accurately configure the mapping relationship between the table name in the source database and the corresponding table name in the target database, supporting one-to-one or many-to-one mapping to ensure the accuracy of data flow.

[0123] The quick collection configuration template supports data service classification. The definition of data service classification code not only facilitates classification and management, but also provides strong support for subsequent batch heterogeneous metadata collection.

[0124] Step S5: Configure scheduling task management. The configuration scheduling task management includes creating an ETL task node or calling an ETL task node.

[0125] The scheduling task management model supports the setting of specific task information, including task name, description, execution cycle (such as daily, weekly), execution time, etc. The task node is bound to a specific ETL task and its global parameters, and supports serial or parallel execution between tasks to meet complex business logic requirements.

[0126] The scheduling task management model is the centralized control center for all scheduling tasks, responsible for unified management and configuration of various tasks. It supports flexible expansion of task types according to actual needs, and mainly focuses on setting basic information of tasks and task scheduling logic. Task information covers name, description, planned time, execution cycle, level, status, type and specific scheduling details. In terms of task scheduling, a graphical design tool is provided to facilitate operation and maintenance personnel to efficiently combine and schedule tasks and realize process-based operation scheduling. The design tool contains multiple process components such as start, end, action, conditional judgment, branch, merge, etc., and supports detailed binding and configuration of various subtasks in action components, such as ETL, audit, indicator calculation, report generation, data subscription, and Java / Spark / Python service tasks, etc. Metadata service tasks can be directly queried and selected from metadata objects.

[0127] Step S6: monitor the ETL task execution status.

[0128] The task monitoring module implements the task monitoring mechanism of Kettle jobs and self-developed log tables, monitors logs and exceptions in real time, and ensures transparent task execution. It provides a comprehensive monitoring list and graphical process monitoring, supports multi-dimensional query and statistics, and helps operation and maintenance to efficiently handle exceptions. In exception handling, it supports flexible operations in graphical processes, such as re-executing task links, passing parameters and supplementing sampling, etc., to enhance system robustness.

[0129] This process describes in detail several key steps of data collection and processing, aiming to achieve efficient, flexible and automated data management. From the configuration of data connection sources to the management and monitoring of scheduling tasks, each step has been carefully planned and designed. By configuring information of multiple database systems, managing different types of collection tasks, building mapping relationships between source tables and target tables, and selecting full or incremental collection methods, the system can comprehensively capture and process data. At the same time, the highly automated target table structure creation and update mechanism, as well as the flexible scheduling task management and monitoring functions, further improve the efficiency and accuracy of data processing. This process ensures the timely circulation and effective management of data, and meets the data processing needs in different business scenarios.

[0130] Embodiment 9: This embodiment proposes an electronic device, including a memory and a processor; a computer program is stored in the memory; when the computer program is executed on the processor, the quick collection method of multi-source heterogeneous structured data of the above embodiment is implemented.

[0131] The electronic device provided in this embodiment integrates efficient storage, processing and communication functions, and is designed for fast collection of heterogeneous structured data based on metadata. Its core components include memory, processor and transceiver, which work together to achieve fast identification, processing and transmission of data. The memory supports a variety of storage media to ensure stable storage of data; the processor uses a high-performance microprocessor, such as the STM32F105 series, to support complex data processing tasks; the transceiver ensures seamless communication between the device and other systems. In addition, the device is also equipped with auxiliary components such as power supply and display screen to support long-term operation and user interaction.

[0132] The other parts of this embodiment are the same as any one of the above-mentioned embodiments 1 to 8, so they will not be repeated here.

[0133] Embodiment 10: This embodiment provides a computer-readable storage medium, on which computer instructions are stored; when the computer instructions are executed on the above-mentioned electronic device, the above-mentioned method for quickly collecting multi-source heterogeneous structured data is implemented.

[0134] The computer-readable storage medium provided in this embodiment stores instructions for realizing quick collection of heterogeneous structured data. When these instructions are executed, they can guide the computer to efficiently complete the metadata analysis, structure recognition and collection process of the data. The storage medium is compatible with a variety of common formats, such as floppy disks, optical disks, hard disks, etc., ensuring wide applicability and convenient data transmission.

[0135] The other parts of this embodiment are the same as any one of the above-mentioned embodiments 1 to 8, so they will not be repeated here.

[0136] Embodiment 11: This embodiment proposes a computer program product. This embodiment introduces a computer program product containing specific instructions, which is intended to realize the quick collection of heterogeneous structured data through software. The program product is applicable to various computer platforms, including general-purpose computers, special-purpose computers, and computer networks, and can significantly improve the efficiency and accuracy of data collection. After installing and running the program, users can easily complete complex data collection tasks.

[0137] The other parts of this embodiment are the same as any one of the above-mentioned embodiments 1 to 8, so they will not be repeated here.

[0138] The above description is only a preferred embodiment of the present invention and does not limit the present invention in any form. Any simple modification or equivalent change made to the above embodiment based on the technical essence of the present invention shall fall within the protection scope of the present invention.

Claims

1. A quick collection method for multi-source heterogeneous structured data, characterized in that: The quick data collection method introduces the Kettle component and develops two types of data collection templates: ordinary quick data collection template and deduplication quick data collection template, which are used to perform ETL tasks; The quick collection method obtains collection configuration information including data source information, source table information, basic task information, data service classification, collection method, and mapping strategy between source table and target table by configuring data connection source, data table, and data collection template, and stores the collection configuration information in the collection configuration table of the data collection template; then, automatically creates or updates the target table according to the table structure of the source table obtained from the source table information; then, creates an ETL task node or calls the ETL task node to execute the collection task; When executing a collection task, select a data collection template from the common quick data collection template and the deduplication quick data collection template according to whether the collected data needs to be deduplicated, and perform full collection or incremental collection.

2. The method for quickly collecting multi-source heterogeneous structured data according to claim 1, characterized in that: The configuration of the data connection source refers to: arranging and configuring the data source information corresponding to the source database and the target database to ensure the connectivity of all data sources, and recording the configured source database and target database in the database list.

3. The method for quickly collecting multi-source heterogeneous structured data according to claim 1, characterized in that: The configuration data table refers to: clearly specifying the source table information corresponding to the data table to be collected, including the table name of the source table, the fields of the source table, the field types of the source table, and the field primary keys of the source table.

4. The method for quickly collecting multi-source heterogeneous structured data according to claim 1, characterized in that: The configuration of the data collection template refers to: configuring the basic information of the task including the task name, collection task code, and associated ETL task node through a graphical interface and filling in a form, defining the data business classification code through a dictionary management module, configuring the collection method, and setting the mapping strategy between the source table and the target table including the target table information, business primary key, and business timestamp after selecting a single or multiple source tables from the source database; The target table information includes: target table code, table name of the target table, and fields of the target table; The collection mode is divided into a full collection mode and an incremental collection mode. According to the data collection requirements in different business scenarios, a full collection mode or an incremental collection mode is configured for each ETL task node.

5. A quick collection method for multi-source heterogeneous structured data according to any one of claims 1 to 4, characterized in that: The creation of the ETL task node means: first setting the global parameters and loading the database configuration information according to the collection configuration information, and then executing the full collection task or the incremental collection task according to the selected collection method; When executing a full data collection task, set the full information of the configuration table, extract the collection configuration table information from the data collection template, delete the data of the target table, obtain the fields of the target table and set them as variables after assembling them into strings, and then transfer the full data of the source table to the target table; traverse the data one by one until the task is completed, record all collection results and store them in the collection log table; When executing an incremental collection task, first set the configuration table incremental information, obtain the collection configuration table information, determine whether to re-collect the entire table, clear the data in the entire table or delete the data in batches based on the determination result, and obtain the normal run / re-run time; Then, set the business time parameters and where conditions to determine whether there is incremental data. Insert incremental data or end this round of tasks based on the determination results. Traverse the data one by one until the task is completed, record all collection results and store them in the collection log table.

6. The method for quickly collecting multi-source heterogeneous structured data according to claim 5, characterized in that: Inserting incremental data means: acquiring incremental data according to the incremental data acquisition type, and inserting the incremental data of the source table into the target table; The incremental data acquisition types are divided into two types: ordinary type and deduplication type: Common incremental data acquisition method, directly obtains incremental data based on business timestamps; Deduplicated incremental data acquisition method, which deduplicates incremental data according to the business primary key partition and business timestamp in descending order.

7. The method for quickly collecting multi-source heterogeneous structured data according to claim 5, characterized in that: Associate a numeric batch number with each ETL task node. The default batch number is "0". The collection method is determined by the batch number value: if the batch number value is "0", the full amount is collected, otherwise the incremental amount is collected; Get the normal run / rerun time by the batch number in the collection log table: if the batch number exists in the collection log table, it is a rerun, and the start time and end time are obtained from the collection log table; if the batch number does not exist in the collection log table, it is a normal run, and the maximum time of previous collection is used as the start time and the current time is used as the end time.

8. The method for quickly collecting multi-source heterogeneous structured data according to claim 5, characterized in that: Setting the business time parameters means: judging whether the current batch collection time is to collect data by time period through the start time and end time in the global parameters: if the start time and end time are passed in the global parameters, the start time and end time of the current batch are set to the start time and end time passed in from the global parameters; otherwise, the start time and end time in the normal run / re-run time are used.

9. The method for quickly collecting multi-source heterogeneous structured data according to claim 5, characterized in that: The setting of the where condition refers to: splicing the where condition according to the field type of the start time, the end time, the database type and the business timestamp and setting it as a where variable to meet the incremental collection of different database types.

10. The method for quickly collecting multi-source heterogeneous structured data according to claim 1, characterized in that: The quick collection method creates multiple ETL task nodes, each of which runs independently or collaboratively. When running collaboratively, multiple ETL task nodes run in series or in parallel.

Images