Kettle-based relational database field extraction method and device

Abstract

The invention provides a Kettle-based relational database field extraction method and device, and the method comprises the steps: forming a metadata configuration file through setting metadata information corresponding to a source table and a target table; when column updating information in the metadata configuration file indicates that a column needs to be updated, matching fields of the source table and the target table, and when a matching result shows that the fields of the source table and the target table are inconsistent, generating a table structure updating statement based on the matching result, updating columns of the target table based on the table structure updating statement to obtain an updated target table; and dynamically generating an extraction script of the Kettle on the basis of the metadata configuration file, so that the Kettle performs data extraction on the to-be-extracted column of the source table on the basis of the extraction script, and writes the extracted data into the corresponding column of the target table, and the stability and efficiency of a data extraction task can be effectively improved under the background of frequent change of a business system field.

Description

Technical Field

[0001] The present invention relates to the technical field of databases, and in particular, to a method and device for extracting relational database fields based on Kettle. Background Art

[0002] In the context of the rapid development of current informatization and digitalization, various enterprises increasingly rely on information systems to support business operations, data management, and decision-making analysis. With the continuous deepening and complexity of business processes, numerous independent business systems have been formed within enterprises, such as financial systems, procurement systems, customer relationship management (CRM) systems, production manufacturing systems, human resources systems, etc. These systems are developed and deployed at different times, by different departments, and with different technology stacks, resulting in significant differences in data structures, storage methods, access protocols, etc. between systems. This phenomenon is usually referred to as the "information silo" problem, that is, enterprise internal data is enclosed in their respective systems, unable to flow and integrate freely, restricting the unified management and in-depth utilization of enterprise data assets.

[0003] To solve the information silo problem, data integration technology has emerged as the times require. As the core technical route of data integration, ETL (Extract-Transform-Load) has become an important foundation for enterprises to build data middle platforms, data warehouses, and even data lakes. Among them, Kettle (full name Pentaho Data Integration, PDI), as an open-source ETL tool, is widely popular for its visual interface, rich connectors, flexible transformation rules, and nestable process logic. It can extract data from multiple heterogeneous source systems, perform cleaning, transformation, and standardization processing, and then load it into the target database or analysis platform to achieve data integration and reuse.

[0004] However, the current business systems of enterprises are iterating at a relatively fast pace, and the change frequency of the database structure, especially the table fields, is increasing continuously. For example, changes such as adding, deleting, renaming, and modifying data types of fields are extremely common. In this context, the configuration of ETL jobs faces severe challenges. Most of the field mapping, data conversion logic, table structure definition, etc. in Kettle rely on static configuration and lack dynamic perception and automatic adaptation mechanisms. Once the source database table structure changes, it is often necessary to manually trace back and modify the ETL process configuration, and readjust the field mapping relationship, conversion rules, verification logic, etc. This manual maintenance method is time-consuming and laborious, seriously affecting data synchronization efficiency and system stability, especially in scenarios of high-frequency updates or large-scale data synchronization. Therefore, there is an urgent need for a more intelligent and automated dynamic field extraction method based on Kettle, which can perceive changes in the source table structure in real time, automatically complete field matching and extraction SQL generation, so as to improve the flexibility, security, and execution efficiency of the data extraction process. Summary of the Invention

[0005] The present invention provides a method and device for extracting relational database fields based on Kettle to solve the defect of relying on static configuration and poor flexibility in the prior art.

[0006] The present invention provides a method for extracting relational database fields based on Kettle, including: Based on a field extraction request, set the metadata information corresponding to the source table and the target table to form a metadata configuration file; the metadata information includes the description information of the source table and the target table, the column information to be extracted, extraction conditions, column update information, and extraction batch information; When the column update information in the metadata configuration file indicates that a column needs to be updated, match the fields of the source table and the target table, and when the matching result shows that the fields of the source table and the target table are inconsistent, generate a table structure update statement based on the matching result, and update the columns of the target table based on the table structure update statement to obtain an updated target table; Dynamically generate an extraction script for Kettle based on the metadata configuration file, so that Kettle can perform data extraction on the columns to be extracted from the source table based on the extraction script and write the extracted data into the corresponding columns of the target table.

[0007] According to the method for extracting relational database fields based on Kettle provided by the present invention, the matching of the fields of the source table and the target table includes: Obtain the field attributes and field comment information of each field of the source table and the target table; the field attributes include the field name, field type, primary / foreign key information, a preset number of field values, and associated field information of the corresponding field; Match based on the field attributes and field comment information of each field in the source table and the target table to determine the matching result between the source table and the target table; wherein, the matching result includes the different fields between the source table and the target table and the mapping information between the different fields; the different fields between the source table and the target table are fields with inconsistent field names, field types or primary / foreign key information.

[0008] According to a method for extracting relational database fields based on Kettle provided by the present invention, the step of matching based on the field attributes and field comment information of each field in the source table and the target table to determine the matching result between the source table and the target table includes: Use a data structure comparison algorithm based on a hash table, combine the field names, field types and primary / foreign key information of each field in the source table and the target table, and compare the source table and the target table field by field to obtain the different fields between the source table and the target table; Based on the preset number of field values, associated field information and field comment information of the different fields in the source table and the preset number of field values, associated field information and field comment information of the different fields in the target table, determine the mapping information between the different fields in the source table and the different fields in the target table.

[0009] According to a method for extracting relational database fields based on Kettle provided by the present invention, the step of determining the mapping information between the different fields in the source table and the different fields in the target table based on the preset number of field values, associated field information and field comment information of the different fields in the source table and the preset number of field values, associated field information and field comment information of the different fields in the target table includes: Based on the field name, associated field information and field comment information of any different field in the source table, extract the field semantic vector of the any different field in the source table; Based on the field name, associated field information and field comment information of any different field in the target table, extract the field semantic vector of the any different field in the target table; Based on the semantic similarity between the field semantic vector of any different field in the source table and the field semantic vector of any different field in the target table, the coincidence degree between the associated field information of any different field in the source table and the associated field information of any different field in the target table, and the statistical similarity between the preset number of field values of any different field in the source table and the preset number of field values of any different field in the target table, determine whether there is a mapping relationship between any different field in the source table and any different field in the target table.

[0010] A method for extracting relational database fields based on Kettle provided by the present invention, generating a table structure update statement based on the matching result, and updating the columns of the target table based on the table structure update statement to obtain an updated target table, including: Based on the difference fields between the source table and the target table and the mapping information between the difference fields included in the matching result, determining the change type corresponding to each difference field in the source table and the target table; the change types include column addition, column deletion, and field attribute change; Based on the change type corresponding to each difference field in the source table and the target table, generating a table structure update statement, and updating the columns of the target table based on the table structure update statement to obtain an updated target table with the same structure as the source table; Based on the change type corresponding to each difference field in the source table and the target table, generating a table structure change report and sending the table structure change report to the application associated with the target table.

[0011] A method for extracting relational database fields based on Kettle provided by the present invention, the method further includes: During the process of Kettle extracting data from the columns to be extracted of the source table based on the extraction script, detecting extraction exceptions in real time; When there are extraction exceptions, recording the exception information and performing a repair operation based on the preset repair strategy corresponding to the corresponding exception type.

[0012] A method for extracting relational database fields based on Kettle provided by the present invention, dynamically generating a Kettle extraction script based on the metadata configuration file, including: Automatically constructing a data extraction SQL statement based on the metadata information in the metadata configuration file; Generating a Kettle extraction script based on the data extraction SQL statement and customized data cleaning and data aggregation strategies.

[0013] The present invention also provides a device for extracting relational database fields based on Kettle, including: A configuration management module, configured to set the metadata information corresponding to the source table and the target table based on a field extraction request to form a metadata configuration file; the metadata information includes the description information of the source table and the target table, the columns to be extracted, extraction conditions, column update information, and extraction batch information; A comparison and update module, configured to match the fields of the source table and the target table when the column update information in the metadata configuration file indicates that a column needs to be updated, and generate a table structure update statement based on the matching result when the matching result shows that the fields of the source table and the target table are inconsistent, and update the columns of the target table based on the table structure update statement to obtain an updated target table; A script construction module, configured to dynamically generate an extraction script for Kettle based on the metadata configuration file, so that Kettle extracts data from the columns to be extracted of the source table based on the extraction script and writes the extracted data into the corresponding columns of the target table.

[0014] The present invention also provides an electronic device, including a memory, a processor, and a computer program stored on the memory and executable on the processor. When the processor executes the program, the method for extracting relational database fields based on Kettle as described in any one of the above is implemented.

[0015] The present invention also provides a non-transitory computer-readable storage medium, on which a computer program is stored. When the computer program is executed by a processor, the method for extracting relational database fields based on Kettle as described in any one of the above is implemented.

[0016] The present invention also provides a computer program product, including a computer program. When the computer program is executed by a processor, the method for extracting relational database fields based on Kettle as described in any one of the above is implemented.

[0017] The method and device for extracting relational database fields based on Kettle provided by the present invention form a metadata configuration file by setting metadata information corresponding to the source table and the target table; when the column update information in the metadata configuration file indicates that a column needs to be updated, match the fields of the source table and the target table, and when the matching result shows that the fields of the source table and the target table are inconsistent, generate a table structure update statement based on the matching result, update the columns of the target table based on the table structure update statement to obtain an updated target table; dynamically generate an extraction script for Kettle based on the metadata configuration file, so that Kettle extracts data from the columns to be extracted of the source table based on the extraction script and writes the extracted data into the corresponding columns of the target table. In the context of frequent changes in business system fields, it can effectively improve the stability and efficiency of data extraction tasks, and at the same time reduce data anomalies and system failures caused by field mismatches. Description of the Drawings

[0018] To more clearly illustrate the technical solutions in the present invention or the prior art, the following will briefly introduce the accompanying drawings required for the description of the embodiments or the prior art. Obviously, the accompanying drawings in the following description are some embodiments of the present invention. For those of ordinary skill in the art, without creative efforts, other accompanying drawings can also be obtained based on these drawings.

[0019] Figure 1 It is a schematic flowchart of the method for extracting relational database fields based on Kettle provided by the present invention; Figure 2 It is a schematic flowchart of the method for updating the target table structure provided by the present invention; Figure 3 It is a schematic structural diagram of the device for extracting relational database fields based on Kettle provided by the present invention; Figure 4 It is a schematic structural diagram of the electronic device provided by the present invention. Detailed implementation manners

[0020] To make the objectives, technical solutions, and advantages of the present invention clearer, the following will clearly and completely describe the technical solutions in the present invention with reference to the accompanying drawings in the present invention. Obviously, the described embodiments are some, but not all, embodiments of the present invention. Based on the embodiments in the present invention, all other embodiments obtained by those of ordinary skill in the art without creative efforts belong to the scope of protection of the present invention.

[0021] Figure 1 It is a schematic flowchart of the method for extracting relational database fields based on Kettle provided by the present invention. As Figure 1 shown, the method includes: Step 110: Based on the field extraction request, set the metadata information corresponding to the source table and the target table to form a metadata configuration file; the metadata information includes the description information of the source table and the target table, the column information to be extracted, the extraction conditions, the column update information, and the extraction batch information; Step 120: When the column update information in the metadata configuration file indicates that a column needs to be updated, match the fields of the source table and the target table. When the matching result shows that the fields of the source table and the target table are inconsistent, generate a table structure update statement based on the matching result, and update the columns of the target table based on the table structure update statement to obtain an updated target table; Step 130: Dynamically generate an extraction script for Kettle based on the metadata configuration file, so that Kettle can perform data extraction on the columns to be extracted from the source table based on the extraction script and write the extracted data into the corresponding columns of the target table.

[0022] Specifically, after receiving a field extraction request, according to the information such as the table name, database identifier, data source type, etc. included in the request, the description information of the source table and the target table is respectively extracted from the data source management system, including basic metadata such as field name, data type, length, comment, primary key information, etc. Then, the system combines the extraction conditions specified in the request, the column information to be extracted, the extraction batch information, and the column update information, and sets the above metadata information in a specially set configuration interface to form a metadata configuration file in a unified format. This file can be in JSON or YAML format, and the embodiments of the present invention do not make specific limitations on this.

[0023] After the metadata configuration file is generated, obtain the "column update information" field in the metadata configuration file, and determine whether the target table needs to be automatically updated in structure according to the "column update information" field defined in the metadata information. If so, the field information of the source table and the target table is compared one by one in a programmatic way.

[0024] In some embodiments, in order to ensure the accuracy of the comparison, the field comparison algorithm not only compares whether the field names are the same, but also considers the differences in additional attributes such as field type, field length, whether it is a primary key or a foreign key. Specifically, the field attributes and field comment information of each field of the source table and the target table can be obtained, where the field attributes include the field name, field type, primary / foreign key information (indicating whether the corresponding field is the primary key or foreign key of the current table), a preset number of field values (partial field values of this column in the current table), and associated field information. Here, the associated field information of any field of any table can be the field attributes of the fields in other data tables that are associated with this field of this table. Subsequently, based on the field attributes and field comment information of each field of the source table and the target table, a match is made to determine the matching result between the source table and the target table. Among them, the matching result includes the different fields between the source table and the target table and the mapping information between the different fields, and the different fields between the source table and the target table are the fields with inconsistent field names, field types, or primary / foreign key information.

[0025] In some other embodiments, when matching fields between the source table and the target table, a data structure comparison algorithm based on a hash table can be used. Combining the field names, field types, and primary and foreign key information of each field in the source table and the target table, the source table and the target table are compared field by field to obtain the different fields between the source table and the target table. It should be noted that for the source table, if the field name, field type, and primary and foreign key information of any of its fields are not completely consistent with all the fields in the target table, then that field is determined to be a different field in the source table; correspondingly, for the target table, if the field name, field type, and primary and foreign key information of any of its fields are not completely consistent with all the fields in the source table, then that field is determined to be a different field in the target table. Subsequently, based on the preset number of field values, associated field information, and field comment information of the different fields in the source table and the preset number of field values, associated field information, and field comment information of the different fields in the target table, the mapping information between the different fields in the source table and the different fields in the target table is determined. It should be noted that a different field in the source table may have a mapping relationship with a certain different field in the target table, or may not have a mapping relationship with any of the different fields in the target table; similarly, a different field in the target table may have a mapping relationship with a certain different field in the source table, or may not have a mapping relationship with any of the different fields in the source table.

[0026] Among them, in order to improve the accuracy of constructing the mapping relationship between the different fields in the source table and the different fields in the target table, the field semantic vector of a different field in the source table can be extracted based on the field name, associated field information, and field comment information of any different field in the source table. For example, the field semantic vector of the field can be extracted from the field name, associated field information, and field comment information of the corresponding field based on a pre-trained language model. Similarly, the field semantic vector of a different field in the target table can be extracted based on the field name, associated field information, and field comment information of any different field in the target table. Based on the semantic similarity between the field semantic vectors of the different fields in the source table and the field semantic vectors of the different fields in the target table, the coincidence degree between the associated field information of the corresponding different fields in the source table and the associated field information of the corresponding different fields in the target table (for example, the number of identical associated fields of the corresponding different fields in the source table and the corresponding different fields in the target table can be determined as the above coincidence degree), and the statistical similarity between the preset number of field values of the corresponding different fields in the source table and the preset number of field values of the corresponding different fields in the target table (the above statistical similarity can be determined based on the difference between the statistic of the preset number of field values of the corresponding different fields in the source table and the corresponding statistic of the preset number of field values of the corresponding different fields in the target table), it is determined whether there is a mapping relationship between the corresponding different fields in the source table and the corresponding different fields in the target table.

[0027] After completing the field comparison, if the matching result shows that the fields of the source table and the target table are inconsistent, that is, there are different fields between the source table and the target table, then based on the matching result, a table structure update statement for the target table is automatically constructed, and the columns of the target table are updated based on the table structure update statement to obtain the updated target table.

[0028] As Figure 2 shown, the generating a table structure update statement based on the matching result, and updating the columns of the target table based on the table structure update statement to obtain the updated target table includes: Step 210, determining the change type corresponding to each different field in the source table and the target table based on the different fields between the source table and the target table and the mapping information between the different fields included in the matching result; the change types include adding columns, deleting columns, and changing field attributes; Step 220, generating a table structure update statement based on the change type corresponding to each different field in the source table and the target table, and updating the columns of the target table based on the table structure update statement to obtain the updated target table with the same structure as the source table; Step 230, generating a table structure change report based on the change type corresponding to each different field in the source table and the target table, and sending the table structure change report to the application associated with the target table.

[0029] Here, if any different field in the source table does not have a mapped different field in the target table, it means that this different field is a newly added field in the source table, so it is determined that the change type corresponding to this different field is adding columns. If any different field in the source table has a mapped different field in the target table, it means that this different field is caused by a change in field attributes, so it is determined that the change type corresponding to this different field and the mapped different field in the target table is changing field attributes. If any different field in the target table does not have a mapped different field in the source table, it means that this different field is a field deleted from the source table, so it is determined that the change type corresponding to this different field is deleting columns.

[0030] Based on the change types corresponding to each different field in the source table and the target table, a table structure update statement can be generated. Among them, for the different fields in the source table with the change type of adding columns, an SQL statement such as ALTER TABLE target database.target table ADD COLUMN field name field type can be constructed as the above table structure update statement. For the different fields in the source table with the change type of field attribute change, if the field attribute change involves the field type or the primary / foreign key information, an ALTER TABLE statement can be constructed to modify the corresponding attributes of the fields mapped in the target table and ensure the compatibility of data conversion; if the field attribute change involves field renaming, two sections of SQL statements such as ALTER TABLE target database.target table ADD COLUMN field name field type and ALTER TABLE target database.target table DROP COLUMN field name can be constructed respectively as the table structure update statement. For the different fields in the target table with the change type of deleting columns, an SQL statement such as ALTER TABLE target database.target table DROP COLUMN field name can be constructed as the above table structure update statement.

[0031] Based on the above table structure update statement, the columns of the target table are updated to obtain an updated target table with the same structure as the source table. In addition, based on the change types corresponding to each different field in the source table and the target table, a table structure change report can be generated and sent to the application associated with the target table.

[0032] After the update of the target table structure is completed, the system will dynamically generate an extraction script for Kettle based on the complete content of the metadata configuration file. Among them, the system will automatically configure the core components in Kettle, such as input, transformation, output, etc. For example, in the input part, the system will generate a query statement as the data extraction SQL statement according to the combination of the source table and the extraction conditions and set it in the Table Input component; in addition, in the data transformation part, the system will clean and aggregate the data obtained by querying the data extraction SQL statement based on the customized data cleaning and data aggregation strategies to obtain the data actually written into the target table; and in the output part, the system sets the Table Output component and specifies the target table and the field list so that the extracted data can accurately fall into the target database.

[0033] In some embodiments, the generation process of the entire script not only covers all the key steps of Kettle extraction, but also particularly considers the handling of various abnormal scenarios. Specifically, during the process of Kettle extracting data from the columns to be extracted in the source table based on the above extraction script, the system detects extraction anomalies in real time. When there are extraction anomalies, the anomaly information is recorded and repair operations are performed based on the preset repair strategies corresponding to the respective anomaly types. For example, if there are type incompatibilities between fields, the system will attempt to find the conversion path with the least changes and set default values or error record output paths when necessary. In addition, batch control logic is also supported in the extraction script to ensure that data duplication or loss does not occur in case of extraction failure. Moreover, all logs generated during the extraction process, including the number of extraction records, the number of successes and failures, the execution time, field-level anomalies, etc., will be written into the log system for subsequent auditing and problem troubleshooting.

[0034] In actual deployment, the system also supports the scheduled execution and dynamic adjustment of scripts. The scheduling platform can call the extraction script generated by the system and allocate execution resources. When the structures of the source table or target table change in the future, the system can automatically generate new extraction configurations and Kettle scripts by re-reading the metadata configuration file and comparing the differences, thereby achieving automatic response to field changes in the true sense and greatly reducing the costs and risks of traditional manual maintenance of ETL scripts.

[0035] In summary, the method provided by the embodiments of the present invention forms a metadata configuration file by setting the metadata information corresponding to the source table and the target table; when the column update information in the metadata configuration file indicates that a column needs to be updated, the fields of the source table and the target table are matched, and when the matching result shows that the fields of the source table and the target table are inconsistent, a table structure update statement is generated based on the matching result, and the columns of the target table are updated based on the table structure update statement to obtain an updated target table; a Kettle extraction script is dynamically generated based on the metadata configuration file for Kettle to perform data extraction on the columns to be extracted in the source table based on the extraction script and write the extracted data into the corresponding columns of the target table. In the context of frequent changes in business system fields, it can effectively improve the stability and efficiency of data extraction tasks, while reducing data anomalies and system failures caused by field mismatches.

[0036] The following describes the relational database field extraction device based on Kettle provided by the present invention. The relational database field extraction device based on Kettle described below can be correspondingly referred to with the relational database field extraction method based on Kettle described above.

[0037] Based on any of the above embodiments, Figure 3This is a schematic structural diagram of a relational database field extraction device based on Kettle provided by the present invention. As Figure 3 shown, the device includes: A configuration management module 310, configured to set metadata information corresponding to a source table and a target table based on a field extraction request, and form a metadata configuration file; the metadata information includes description information of the source table and the target table, column information to be extracted, extraction conditions, column update information, and extraction batch information; A comparison and update module 320, configured to match the fields of the source table and the target table when the column update information in the metadata configuration file indicates that a column needs to be updated, and generate a table structure update statement based on the matching result when the matching result shows that the fields of the source table and the target table are inconsistent, and update the columns of the target table based on the table structure update statement to obtain an updated target table; A script construction module 330, configured to dynamically generate an extraction script of Kettle based on the metadata configuration file, so that Kettle extracts data from the columns to be extracted of the source table based on the extraction script, and writes the extracted data into the corresponding columns of the target table.

[0038] The device provided by the embodiment of the present invention forms a metadata configuration file by setting metadata information corresponding to a source table and a target table; when the column update information in the metadata configuration file indicates that a column needs to be updated, it matches the fields of the source table and the target table, and when the matching result shows that the fields of the source table and the target table are inconsistent, it generates a table structure update statement based on the matching result, updates the columns of the target table based on the table structure update statement to obtain an updated target table; dynamically generates an extraction script of Kettle based on the metadata configuration file, so that Kettle extracts data from the columns to be extracted of the source table based on the extraction script, and writes the extracted data into the corresponding columns of the target table. In the context of frequent changes in business system fields, it can effectively improve the stability and efficiency of data extraction tasks, and at the same time reduce data anomalies and system failures caused by field mismatches.

[0039] Based on any of the above embodiments, the matching of the fields of the source table and the target table includes: Obtaining the field attributes and field comment information of each field of the source table and the target table; the field attributes include the field name, field type, primary / foreign key information, a preset number of field values, and associated field information of the corresponding field; Match based on the field attributes and field comment information of each field in the source table and the target table to determine the matching result between the source table and the target table; wherein, the matching result includes the different fields between the source table and the target table and the mapping information between the different fields; the different fields between the source table and the target table are fields with inconsistent field names, field types or primary / foreign key information.

[0040] Based on any of the above embodiments, the matching based on the field attributes and field comment information of each field in the source table and the target table to determine the matching result between the source table and the target table includes: Use a data structure comparison algorithm based on a hash table, combine the field names, field types and primary / foreign key information of each field in the source table and the target table, and compare the source table and the target table field by field to obtain the different fields between the source table and the target table; Based on the preset number of field values, associated field information and field comment information of the different fields in the source table and the preset number of field values, associated field information and field comment information of the different fields in the target table, determine the mapping information between the different fields in the source table and the different fields in the target table.

[0041] Based on any of the above embodiments, the determining the mapping information between the different fields in the source table and the different fields in the target table based on the preset number of field values, associated field information and field comment information of the different fields in the source table and the preset number of field values, associated field information and field comment information of the different fields in the target table includes: Based on the field name, associated field information and field comment information of any different field in the source table, extract the field semantic vector of the any different field in the source table; Based on the field name, associated field information and field comment information of any different field in the target table, extract the field semantic vector of the any different field in the target table; Based on the semantic similarity between the field semantic vector of any different field in the source table and the field semantic vector of any different field in the target table, the coincidence degree between the associated field information of any different field in the source table and the associated field information of any different field in the target table, and the statistical similarity between the preset number of field values of any different field in the source table and the preset number of field values of any different field in the target table, determine whether there is a mapping relationship between any different field in the source table and any different field in the target table.

[0042] Based on any of the above embodiments, generating a table structure update statement based on the matching result, and updating the columns of the target table based on the table structure update statement to obtain an updated target table, including: Based on the difference fields between the source table and the target table included in the matching result and the mapping information between the difference fields, determining the change type corresponding to each difference field in the source table and the target table; the change types include column addition, column deletion, and field attribute change; Based on the change type corresponding to each difference field in the source table and the target table, generating a table structure update statement, and updating the columns of the target table based on the table structure update statement to obtain an updated target table with the same structure as the source table; Based on the change type corresponding to each difference field in the source table and the target table, generating a table structure change report, and sending the table structure change report to the application associated with the target table.

[0043] Based on any of the above embodiments, the method further includes: During the process of Kettle extracting data from the columns to be extracted of the source table based on the extraction script, detecting extraction exceptions in real time; When there are extraction exceptions, recording the exception information and performing a repair operation based on the preset repair strategy corresponding to the corresponding exception type.

[0044] Based on any of the above embodiments, dynamically generating an extraction script for Kettle based on the metadata configuration file, including: Automatically constructing a data extraction SQL statement based on the metadata information in the metadata configuration file; Generating an extraction script for Kettle based on the data extraction SQL statement and customized data cleaning and data aggregation strategies.

[0045] Figure 4 It is a schematic structural diagram of the electronic device provided by the present invention, as Figure 4As shown in the figure, the electronic device may include: a processor 410, a memory 420, a communications interface 430, and a communication bus 440. Among them, the processor 410, the memory 420, and the communication interface 430 complete communication with each other through the communication bus 440. The processor 410 may call the logical instructions in the memory 420 to execute a method for extracting relational database fields based on Kettle. The method includes: setting metadata information corresponding to the source table and the target table based on a field extraction request to form a metadata configuration file; the metadata information includes description information of the source table and the target table, column information to be extracted, extraction conditions, column update information, and extraction batch information; when the column update information in the metadata configuration file indicates that a column needs to be updated, matching the fields of the source table and the target table, and when the matching result shows that the fields of the source table and the target table are inconsistent, generating a table structure update statement based on the matching result, and updating the columns of the target table based on the table structure update statement to obtain an updated target table; dynamically generating an extraction script for Kettle based on the metadata configuration file, so that Kettle extracts data from the columns to be extracted of the source table based on the extraction script and writes the extracted data into the corresponding columns of the target table.

[0046] In addition, when the logical instructions in the above-mentioned memory 420 can be implemented in the form of software function units and sold or used as an independent product, they can be stored in a computer-readable storage medium. Based on such an understanding, the technical solution of the present invention, in essence, or the part that contributes to the prior art, or a part of this technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions for causing a computer device (which may be a personal computer, a server, or a network device, etc.) to execute all or part of the steps of the methods described in various embodiments of the present invention. The foregoing storage medium includes: various media such as USB flash drives, mobile hard disks, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical discs that can store program codes.

[0047] On the other hand, the present invention also provides a computer program product. The computer program product includes a computer program stored on a non-transitory computer-readable storage medium. The computer program includes program instructions. When the program instructions are executed by a computer, the computer can execute the Kettle-based relational database field extraction method provided by the above-mentioned various methods. The method includes: based on a field extraction request, setting metadata information corresponding to a source table and a target table to form a metadata configuration file; the metadata information includes description information of the source table and the target table, column information to be extracted, extraction conditions, column update information, and extraction batch information; when the column update information in the metadata configuration file indicates that a column needs to be updated, matching the fields of the source table and the target table, and when the matching result shows that the fields of the source table and the target table are inconsistent, generating a table structure update statement based on the matching result, and updating the columns of the target table based on the table structure update statement to obtain an updated target table; dynamically generating an extraction script of Kettle based on the metadata configuration file for Kettle to perform data extraction on the columns to be extracted of the source table according to the extraction script, and writing the extracted data into the corresponding columns of the target table.

[0048] On another aspect, the present invention also provides a non-transitory computer-readable storage medium, on which a computer program is stored. When the computer program is executed by a processor, it is configured to execute the Kettle-based relational database field extraction method provided by the above-mentioned various methods. The method includes: based on a field extraction request, setting metadata information corresponding to a source table and a target table to form a metadata configuration file; the metadata information includes description information of the source table and the target table, column information to be extracted, extraction conditions, column update information, and extraction batch information; when the column update information in the metadata configuration file indicates that a column needs to be updated, matching the fields of the source table and the target table, and when the matching result shows that the fields of the source table and the target table are inconsistent, generating a table structure update statement based on the matching result, and updating the columns of the target table based on the table structure update statement to obtain an updated target table; dynamically generating an extraction script of Kettle based on the metadata configuration file for Kettle to perform data extraction on the columns to be extracted of the source table according to the extraction script, and writing the extracted data into the corresponding columns of the target table.

[0049] The device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed to multiple network units. Some or all of the modules can be selected according to actual needs to achieve the purpose of the solution of this embodiment. A person of ordinary skill in the art can understand and implement it without creative work.

[0050] Through the description of the above embodiments, those skilled in the art can clearly understand that each embodiment can be implemented by means of software plus a necessary general hardware platform, and of course, it can also be implemented by hardware. Based on this understanding, the essence of the above technical solution, or the part that contributes to the prior art, can be embodied in the form of a software product. The computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, magnetic disk, optical disk, etc., and includes several instructions for causing a computer device (which can be a personal computer, server, or network device, etc.) to execute the methods described in each embodiment or some parts of the embodiments.

[0051] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention and are not intended to limit them. Although the present invention has been described in detail with reference to the foregoing embodiments, those of ordinary skill in the art should understand that they can still modify the technical solutions described in the foregoing embodiments, or perform equivalent replacements for some of the technical features. And these modifications or replacements do not make the essence of the corresponding technical solutions deviate from the spirit and scope of the technical solutions of the embodiments of the present invention.

Claims

1. A method for extracting relational database fields based on Kettle, characterized in that, Including: Based on a field extraction request, set the metadata information corresponding to the source table and the target table to form a metadata configuration file; The metadata information includes description information of the source table and the target table, column information to be extracted, extraction conditions, column update information, and extraction batch information; When the column update information in the metadata configuration file indicates that a column needs to be updated, match the fields of the source table and the target table, and when the matching result shows that the fields of the source table and the target table are inconsistent, generate a table structure update statement based on the matching result, and update the columns of the target table based on the table structure update statement to obtain an updated target table; Dynamically generate an extraction script for Kettle based on the metadata configuration file, so that Kettle can perform data extraction on the columns to be extracted from the source table based on the extraction script and write the extracted data into the corresponding columns of the target table.

2. The method for extracting relational database fields based on Kettle according to claim 1, characterized in that The matching of the fields of the source table and the target table includes: Obtain the field attributes and field comment information of each field of the source table and the target table; the field attributes include the field name, field type, primary / foreign key information, a preset number of field values, and associated field information of the corresponding field; Match based on the field attributes and field comment information of each field of the source table and the target table to determine the matching result between the source table and the target table; wherein, the matching result includes the different fields between the source table and the target table and the mapping information between the different fields; the different fields between the source table and the target table are fields with inconsistent field names, field types, or primary / foreign key information.

3. The method for extracting relational database fields based on Kettle according to claim 2, characterized in that The determining of the matching result between the source table and the target table based on the field attributes and field comment information of each field of the source table and the target table includes: Use a data structure comparison algorithm based on a hash table, combine the field names, field types, and primary / foreign key information of each field of the source table and the target table, and compare the source table and the target table field by field to obtain the different fields between the source table and the target table; Based on the preset number of field values, associated field information, and field comment information of the different fields in the source table and the preset number of field values, associated field information, and field comment information of the different fields in the target table, determine the mapping information between the different fields in the source table and the different fields in the target table.

4. The method for extracting relational database fields based on Kettle according to claim 3, characterized in that, The determining of the mapping information between the different fields in the source table and the different fields in the target table based on the preset number of field values, associated field information, and field comment information of the different fields in the source table and the preset number of field values, associated field information, and field comment information of the different fields in the target table includes: Based on the field name, associated field information, and field comment information of any different field in the source table, extract the field semantic vector of the any different field in the source table; Based on the field name, associated field information, and field comment information of any different field in the target table, extract the field semantic vector of the any different field in the target table; Determine whether there is a mapping relationship between any differential field in the source table and any differential field in the target table based on the semantic similarity between the field semantic vectors of any differential field in the source table and the field semantic vectors of any differential field in the target table, the coincidence degree between the associated field information of any differential field in the source table and the associated field information of any differential field in the target table, and the statistical similarity between the preset number of field values of any differential field in the source table and the preset number of field values of any differential field in the target table.

5. The method for extracting relational database fields based on Kettle according to claim 2, wherein Generating a table structure update statement based on the matching result, and updating the columns of the target table based on the table structure update statement to obtain an updated target table, including: Based on the differential fields between the source table and the target table included in the matching result and the mapping information between the differential fields, determine the change types corresponding to each differential field in the source table and the target table; the change types include column addition, column deletion, and field attribute change; Based on the change types corresponding to each differential field in the source table and the target table, generate a table structure update statement, and update the columns of the target table based on the table structure update statement to obtain an updated target table with the same structure as the source table; Based on the change types corresponding to each differential field in the source table and the target table, generate a table structure change report, and send the table structure change report to the application associated with the target table.

6. The method for extracting relational database fields based on Kettle according to claim 1, characterized in that, The method further includes: During the process of Kettle performing data extraction on the columns to be extracted from the source table based on the extraction script, real-time detection of extraction anomalies is performed; When there are extraction anomalies, record the anomaly information and perform repair operations based on the preset repair strategies corresponding to the respective anomaly types.

7. The method for extracting relational database fields based on Kettle according to claim 1, characterized in that, The dynamically generating the extraction script of Kettle based on the metadata configuration file includes: Automatically construct a data extraction SQL statement based on the metadata information in the metadata configuration file; Generate an extraction script for Kettle based on the data extraction SQL statement and customized data cleaning and data aggregation strategies.

8. A relational database field extraction device based on Kettle, characterized in that, Including: A configuration management module for setting the metadata information corresponding to the source table and the target table based on a field extraction request to form a metadata configuration file; The metadata information includes the description information of the source table and the target table, the columns to be extracted, extraction conditions, column update information, and extraction batch information; A comparison and update module for, when the column update information in the metadata configuration file indicates that the columns need to be updated, matching the fields of the source table and the target table, and when the matching result shows that the fields of the source table and the target table are inconsistent, generating a table structure update statement based on the matching result, and updating the columns of the target table based on the table structure update statement to obtain an updated target table; A script construction module, which is used to dynamically generate an extraction script for Kettle based on the metadata configuration file, so that Kettle extracts data from the columns to be extracted in the source table based on the extraction script, and writes the extracted data into the corresponding columns of the target table.

9. An electronic device, comprising a memory, a processor, and a computer program stored on the memory and executable on the processor, wherein, When the processor executes the program, it implements the Kettle-based relational database field extraction method according to any one of claims 1 to 7.

10. A non-transitory computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by the processor, it implements the Kettle-based relational database field extraction method according to any one of claims 1 to 7.

Images