A Data Lineage Analysis Method and System Based on Kettle Executor

Abstract

This invention discloses a data lineage analysis method and system based on the Kettle executor, belonging to the field of data analysis technology. The method includes: obtaining transformation script information from the Kettle configuration library and parsing component information; obtaining the connection order between components, and analyzing and integrating the parsed component information through logical judgment to generate data lineage information from source fields to target fields; obtaining job script information from the Kettle configuration library, sequentially reading the script information nested within the job script, and if the read script is a job script, looping through its script information until a transformation script is read and data lineage information is parsed; obtaining the connection order between scripts, and analyzing and integrating all parsed data lineage information through logical judgment to generate field-level data lineage information; and visually displaying the data lineage information on a front-end page for user viewing. This invention solves the problems of complex data tracing and difficulty in locating and investigating problematic data.

Description

Technical Field

[0001] This invention relates to the field of data analysis technology, specifically a data lineage analysis method and system based on the Kettle executor. Background Technology

[0002] Kettle performs data extraction, transformation, and loading operations through script design. Since Kettle is a client program, it cannot provide an external interface and cannot automatically perform lineage analysis after the Kettle script is executed. Current technology can only export Kettle scripts in XML format and then manually parse and process the scripts to analyze the lineage relationships between data. It cannot achieve automatic lineage analysis after the Kettle script is executed. Furthermore, it only supports mechanically analyzing the flow of node data between databases by traversing each node of the Kettle script in XML format. The parsing is not intelligent enough and cannot guarantee the accuracy and completeness of data lineage. Summary of the Invention

[0003] Currently, Kettle is used to extract, transform, and load data to achieve data sharing. However, with the increasing complexity of business logic, Kettle script design, data flow logic, data tracing, and the location of problematic data are becoming increasingly difficult. This invention addresses the needs and shortcomings of current technological development by providing a data lineage analysis method and system based on the Kettle executor, solving the problems of complex data tracing and difficulty in locating and troubleshooting problematic data.

[0004] Firstly, the present invention provides a data lineage analysis method based on the Kettle executor, and the technical solution adopted to solve the above-mentioned technical problems is as follows:

[0005] A data lineage analysis method based on the Kettle executor includes the following steps:

[0006] S1. Connect to the Kettle configuration library through the Kettle native Java-API interface to obtain the transformation script information, and parse out the input / output component information and field transformation component information contained in the transformation script;

[0007] The connection order between components is obtained by using the native Java API interface of Kettle. The parsed input and output component information and field transformation component information are analyzed and integrated through logical judgment to generate data lineage information from source field to target field. The lineage information is then stored in the database for persistence.

[0008] S2. Connect to the Kettle configuration library through the native Kettle Java API interface to obtain job script information. Read the nested script information in the job script in sequence, determine the script type, and if the script read is a job script, read the script information in it in a loop until the conversion script is read and the data lineage information is parsed out.

[0009] The connection order between scripts is obtained by using Kettle's native Java API interface. After logical judgment, all parsed data lineage information is analyzed and integrated to generate field-level data lineage information and store the lineage information in the database for persistence.

[0010] S3. Visually display the bloodline information on the front-end page for users to view.

[0011] Optionally, in step S1, after connecting to the Kettle configuration library via the native Kettle Java API interface, the Kettle configuration library information is obtained through the KettleRepository related interface, the transformation script information is obtained through the loadTransformation interface, the input / output components and field transformation components are obtained through the TransMeta.getSteps interface, and the component ID is obtained through the StepMeta.getStepID interface. Then, the component ID is parsed to obtain the input / output component information and field transformation component information.

[0012] After parsing and obtaining the input / output component information and field transformation component information, based on the connection order between components, each field, combined with its input and output, generates a data lineage information from the source field to the target field, and stores the lineage information in the database for persistence.

[0013] Further optional, the input / output components include database input / output components, file input / output components, and interface input / output components;

[0014] The database input / output components include table input components, table output components, update components, insert / update components, and database query components; database connection information, table information, and field information are parsed from the database input / output components, where the database connection information includes database type, IP address, port number, database name, username, and password;

[0015] The file input / output components include a text file input component, a text file output component, an EXCEL input component, an EXCEL output component, an XML input component, an XML output component, a JSON input component, and a JSON output component; file path information, file name information, and file header information are parsed from the file input / output components.

[0016] The interface components include HTTP client components, REST client components, and Web query service components; the interface address information, interface input parameter information, and interface output parameter information are parsed from the interface components.

[0017] The field transformation component includes a field selection component, a field splitting component, a row to column component, and a column to row component; the field transformation information between components is parsed from the field transformation component.

[0018] Optionally, in step S2, after connecting to the Kettle configuration repository via the native Kettle Java API, the Kettle configuration repository information is obtained through the KettleRepository related interfaces. The job script information is obtained through the loadJob interface. The nested script information within the job script is sequentially read through the JobMeta.getJobCopies interface. Finally, the JobEntryCopy.isTransformation interface is used to determine if it is a transformation script.

[0019] (a) If so, the transformation script is read out using loadTransformation, and the data lineage information is parsed out according to step S1;

[0020] (b) If it is not a conversion script, determine whether it is a job script by checking JobEntryCopy.isJob. (b1) If it is a job script, repeat the above steps until the conversion script is parsed. (b2) If it is not a job script, end the parsing.

[0021] Optionally, in step S3, the front end uses logical query to visualize and display all the data lineage information in the database, accurate to the field level, including the flow information of each field;

[0022] Clicking on a field allows you to view the database, file, or interface information to which the field belongs, as well as the lineage information related to that field, including source information and all upstream information of the source information, target information and all downstream information of the target information;

[0023] Clicking the lines between fields will display the task information for generating this lineage data.

[0024] Secondly, the present invention provides a data lineage analysis system based on the Kettle executor, and the technical solution adopted to solve the above-mentioned technical problems is as follows:

[0025] A data lineage analysis system based on the Kettle executor includes a Kettle script parsing engine and a data lineage visualization view. The Kettle script parsing engine further includes a transformation script parsing engine and a job script parsing engine, wherein:

[0026] The conversion script parsing engine first connects to the Kettle configuration library through the Kettle native Java-API interface to obtain the conversion script information, and then parses out the input and output component information and field conversion component information contained in the conversion script. Subsequently, it uses the Kettle native Java-API interface to obtain the connection order between components, and analyzes and integrates the parsed input and output component information and field conversion component information through logical judgment to generate data lineage information from source field to target field. The lineage information is then stored in the database for persistence.

[0027] The job script parsing engine first connects to the Kettle configuration library through the Kettle native Java-API interface to obtain job script information. It reads the nested script information in the job script in sequence, determines the script type, and if the script read is a job script, it reads the script information in the loop until it reads the conversion script and parses out the data lineage information. Then, it uses the Kettle native Java-API interface to obtain the connection order between the scripts, analyzes and integrates all the parsed data lineage information through logical judgment, generates field-level data lineage information, and stores the lineage information in the database for persistence.

[0028] The data lineage visualization view displays data lineage information visually on the front-end page for users to view.

[0029] Optionally, the transformation script parsing engine connects to the Kettle configuration library via the Kettle native Java API interface, obtains Kettle configuration library information through the KettleRepository related interface, obtains transformation script information through the loadTransformation interface, obtains input / output components and field transformation components through the TransMeta.getSteps interface, and obtains component IDs through the StepMeta.getStepID interface. Then, it parses based on the component IDs to obtain input / output component information and field transformation component information. According to the connection order between components, each field, combined with its input and output, generates a data lineage information from the source field to the target field, and stores the lineage information in the database for persistence.

[0030] Further optional, the input / output components include database input / output components, file input / output components, and interface input / output components;

[0031] The database input / output components include table input components, table output components, update components, insert / update components, and database query components; database connection information, table information, and field information are parsed from the database input / output components, where the database connection information includes database type, IP address, port number, database name, username, and password;

[0032] The file input / output components include a text file input component, a text file output component, an EXCEL input component, an EXCEL output component, an XML input component, an XML output component, a JSON input component, and a JSON output component; file path information, file name information, and file header information are parsed from the file input / output components.

[0033] The interface components include HTTP client components, REST client components, and Web query service components; the interface address information, interface input parameter information, and interface output parameter information are parsed from the interface components.

[0034] The field transformation component includes a field selection component, a field splitting component, a row to column component, and a column to row component; the field transformation information between components is parsed from the field transformation component.

[0035] Optionally, the job script parsing engine connects to the Kettle configuration library via the Kettle native Java API, obtains Kettle configuration library information through the KettleRepository related interface, retrieves job script information through the loadJob interface, sequentially reads the nested script information in the job script through the JobMeta.getJobCopies interface, and then determines whether it is a transformation script through the JobEntryCopy.isTransformation interface.

[0036] (a) If so, the transformation script is read out through loadTransformation and the transformation script parsing engine is called to parse out the data lineage information;

[0037] (b) If it is not a conversion script, determine whether it is a job script by checking JobEntryCopy.isJob. (b1) If it is a job script, repeat the above steps until the conversion script is parsed. (b2) If it is not a job script, end the parsing.

[0038] Optionally, the data lineage visualization view involved can be used to visualize and display all data lineage information in the database through logical queries, accurate to the field level, including the flow information of each field;

[0039] Clicking on a field allows you to view the database, file, or interface information to which the field belongs, as well as the lineage information related to that field, including source information and all upstream information of the source information, target information and all downstream information of the target information;

[0040] Clicking the lines between fields will display the task information for generating this lineage data.

[0041] The data lineage analysis method and system based on the Kettle executor of the present invention have the following advantages compared with the prior art:

[0042] This invention automatically triggers a task to read script information, obtain components, and parse data lineage information after the Kettle task is completed. Then, it uses a database to persist the parsed data lineage information so that it can be visualized on the front-end page for users to view. This solves the problems of complex data tracing and difficulty in locating and investigating problematic data. Attached Figure Description

[0043] Appendix Figure 1 This is a flowchart of the method according to Embodiment 1 of the present invention;

[0044] Appendix Figure 2 This is an execution flowchart of Embodiment 2 of the present invention. Detailed Implementation

[0045] To make the technical solution, the technical problem solved, and the technical effect of the present invention clearer, the technical solution of the present invention will be clearly and completely described below in conjunction with specific embodiments.

[0046] Example 1:

[0047] Combined with appendix Figure 1 This embodiment proposes a data lineage analysis method based on the Kettle executor, which includes the following steps:

[0048] S1. Connect to the Kettle configuration library through the native Kettle Java API interface, obtain Kettle configuration library information through the KettleRepository related interface, obtain transformation script information through the loadTransformation interface, and obtain input / output components and field transformation components through the TransMeta.getSteps interface;

[0049] The input / output components include database input / output components, file input / output components, and interface input / output components, wherein: (1) the database input / output components include table input components, table output components, update components, insert / update components, and database query components; (2) the file input / output components include text file input components, text file output components, EXCEL input components, EXCEL output components, XML input components, XML output components, JSON input components, and JSON output components; (3) the interface components include HTTP client components, REST client components, and Web query service components;

[0050] Field conversion components include field selection components, field splitting components, row to column components, and column to row components;

[0051] The component ID is obtained through the StepMeta.getStepID interface, and then parsed based on the component ID: Database connection information, table information, and field information are parsed from the database input / output component, including database type, IP address, port number, database name, username, and password; file path information, file name information, and file header information are parsed from the file input / output component; interface address information, interface input parameters, and interface output parameters are parsed from the interface component; and field transformation information between components is parsed from the field transformation component.

[0052] By using Kettle's native Java API interface to obtain the connection order between components, and through logical judgment, analyzing and integrating the input and output component information and field transformation component information, a data lineage information from the source field to the target field is generated for each field in combination with the field's input and output. The lineage information is then stored in the database for persistence.

[0053] S2. Connect to the Kettle configuration repository via the native Kettle Java API, obtain Kettle configuration repository information through the KettleRepository related interfaces, retrieve job script information through the loadJob interface, sequentially read the script information nested in the job script through the JobMeta.getJobCopies interface, and then determine whether it is a transformation script through the JobEntryCopy.isTransformation interface.

[0054] (a) If it is a transformation script, the transformation script is read out through loadTransformation, and the data lineage information is parsed out according to step S1;

[0055] (b) If it is not a conversion script, determine whether it is a job script by checking JobEntryCopy.isJob. (b1) If it is a job script, repeat the above steps until the conversion script is parsed. (b2) If it is not a job script, end the parsing.

[0056] The connection order between scripts is obtained by using Kettle's native Java API interface. After logical judgment, all parsed data lineage information is analyzed and integrated to generate field-level data lineage information and store the lineage information in the database for persistence.

[0057] S3. Visually display pedigree information on the front-end page for users to view, specifically including:

[0058] The front end uses logical queries to visualize and display all the data lineage information in the database, accurate to the field level, including the flow information of each field;

[0059] Clicking on a field allows you to view the database, file, or interface information to which the field belongs, as well as the lineage information related to that field, including source information and all upstream information of the source information, target information and all downstream information of the target information;

[0060] Clicking the lines between fields will display the task information for generating this lineage data.

[0061] Example 2:

[0062] Combined with appendix Figure 2 This embodiment proposes a data lineage analysis system based on the Kettle executor, which includes a Kettle script parsing engine and a data lineage visualization view. The Kettle script parsing engine further includes a transformation script parsing engine and a job script parsing engine.

[0063] The conversion script parsing engine first connects to the Kettle configuration library through the Kettle native Java-API interface, obtains Kettle configuration library information through the KettleRepository related interface, obtains conversion script information through the loadTransformation interface, and obtains input / output components and field conversion components through the TransMeta.getSteps interface. The input / output components include database input / output components, file input / output components, and interface input / output components, of which: (1) Database input / output components include table input components, table output components, update components, insert / update components, and database query components; (2) File input / output components include text file input components, text file output components, EXCEL input components, EXCEL output components, XML input components, XML output components, JSON input components, and JSON output components; (3) Interface components include HTTP client components, REST client components, and Web query service components; Field conversion components include field selection components, field splitting components, row to column components, and column to row components;

[0064] The component ID is obtained through the StepMeta.getStepID interface, and parsing is performed based on the component ID: Database connection information, table information, and field information are parsed from the database input / output component, including database type, IP address, port number, database name, username, and password; file path information, file name information, and file header information are parsed from the file input / output component; interface address information, interface input parameter information, and interface output parameter information are parsed from the interface component; and field transformation information between components is parsed from the field transformation component.

[0065] Then, the connection order between components is obtained using Kettle's native Java API interface. After logical judgment, the parsed input and output component information and field transformation component information are analyzed and integrated. In this way, each field is combined with the input and output of the field to generate a data lineage information from the source field to the target field. The lineage information is then stored in the database for persistence.

[0066] The job script parsing engine first connects to the Kettle configuration repository via the native Kettle Java API, obtains Kettle configuration repository information through the KettleRepository related interfaces, retrieves job script information through the loadJob interface, sequentially reads the nested script information within the job script through the JobMeta.getJobCopies interface, and then determines whether it is a transformation script through the JobEntryCopy.isTransformation interface.

[0067] (a) If so, the transformation script is read out through loadTransformation and the transformation script parsing engine is called to parse out the data lineage information;

[0068] (b) If it is not a conversion script, determine whether it is a job script by checking JobEntryCopy.isJob. (b1) If it is a job script, read the script information in it in a loop until the conversion script is read and the data lineage information is parsed out. (b2) If it is not a job script, end the parsing.

[0069] Then, the connection order between scripts is obtained using Kettle's native Java API interface. After logical judgment, all parsed data lineage information is analyzed and integrated to generate field-level data lineage information, which is then stored in the database for persistence.

[0070] The data lineage visualization view displays data lineage information visually on the front-end page for users to view. In this process:

[0071] The data lineage visualization view displays all data lineage information in the database through logical queries, accurate to the field level, including the flow information of each field;

[0072] Clicking on a field allows you to view the database, file, or interface information to which the field belongs, as well as the lineage information related to that field, including source information and all upstream information of the source information, target information and all downstream information of the target information;

[0073] Clicking the lines between fields will display the task information for generating this lineage data.

[0074] In summary, the data lineage analysis method and system based on the Kettle executor of this invention can solve the problems of complex data tracing and difficulty in locating and investigating problematic data.

[0075] The above specific examples illustrate the principles and implementation methods of the present invention in detail. These embodiments are merely for the purpose of helping to understand the core technical content of the present invention. Based on the above specific embodiments of the present invention, any improvements and modifications made to the present invention by those skilled in the art without departing from the principles of the present invention should fall within the patent protection scope of the present invention.

Claims

1. A data lineage analysis method based on the Kettle executor, characterized in that, Includes the following steps: S1. Connect to the Kettle configuration library via the native Kettle Java-API interface to obtain transformation script information, and parse out the input / output component information and field transformation component information contained in the transformation script. Specifically, after connecting to the Kettle configuration library via the native Kettle Java-API interface, obtain Kettle configuration library information through the KettleRepository related interface, obtain transformation script information through the loadTransformation interface, obtain input / output components and field transformation components through the TransMeta.getSteps interface, obtain component IDs through the StepMeta.getStepID interface, and then parse based on the component IDs to obtain the input / output component information and field transformation component information. After parsing and obtaining the input / output component information and field transformation component information, the connection order between components is obtained using the Kettle native Java API interface. Based on the connection order between components, the parsed input / output component information and field transformation component information are analyzed and integrated through logical judgment. For each field, a data lineage information from the source field to the target field is generated by combining the input and output of the field. The lineage information is stored in the database for persistence. S2. Connect to the Kettle configuration library through the Kettle native Java-API interface to obtain job script information. Read the nested script information in the job script in sequence, determine the script type, and if the read script is a job script, read the script information in it in a loop until the transformation script is read and the data lineage information is parsed. Specifically, after connecting to the Kettle configuration library through the Kettle native Java-API interface, obtain the Kettle configuration library information through the KettleRepository related interface, obtain the job script information through the loadJob interface, read the nested script information in the job script in sequence through the JobMeta.getJobCopies interface, and then determine whether it is a transformation script through the JobEntryCopy.isTransformation interface. (a) If it is, read the transformation script through loadTransformation and parse the data lineage information according to step S1; (b) If it is not a transformation script, determine whether it is a job script through JobEntryCopy.isJob. (b1) If it is a job script, repeat the above steps until the transformation script is parsed; (b2) If it is not a job script, end the parsing. The connection order between scripts is obtained by using Kettle's native Java API interface. After logical judgment, all parsed data lineage information is analyzed and integrated to generate field-level data lineage information and store the lineage information in the database for persistence. S3. Visually display data lineage information on the front-end page for users to view. Specifically, the front-end uses logical queries to visualize all data lineage information of the database, accurate to the field level, including the flow information of each field; clicking on a field allows users to view the database, file, or interface information to which the field belongs, as well as the lineage information related to that field, including source information and all upstream information of the source information, target information and all downstream information of the target information; clicking on the connection between fields allows users to view the task information that generated the data lineage information.

2. The data lineage analysis method based on the Kettle executor according to claim 1, characterized in that, Input / output components include database input / output components, file input / output components, and interface input / output components; The database input / output components include table input components, table output components, update components, insert / update components, and database query components; database connection information, table information, and field information are parsed from the database input / output components, where the database connection information includes database type, IP address, port number, database name, username, and password; The file input / output components include a text file input component, a text file output component, an EXCEL input component, an EXCEL output component, an XML input component, an XML output component, a JSON input component, and a JSON output component; file path information, file name information, and file header information are parsed from the file input / output components. The interface components include HTTP client components, REST client components, and Web query service components; the interface address information, interface input parameter information, and interface output parameter information are parsed from the interface components. Field conversion components include field selection components, field splitting components, row to column components, and column to row components; Extract field transformation information between components from the field transformation component.

3. A data lineage analysis system based on the Kettle executor, characterized in that, It includes the Kettle script parsing engine and a data lineage visualization view. The Kettle script parsing engine further includes a transformation script parsing engine and a job script parsing engine, among which: The transformation script parsing engine first connects to the Kettle configuration library via the Kettle native Java-API interface to obtain transformation script information. It then parses out the input / output component information and field transformation component information contained in the transformation script. Next, it uses the Kettle native Java-API interface to obtain the connection order between components. Through logical judgment, it analyzes and integrates the parsed input / output component information and field transformation component information to generate data lineage information from the source field to the target field. This lineage information is then stored in the database for persistence. In this process, after connecting to the Kettle configuration library via the Kettle native Java-API interface, the engine obtains Kettle configuration library information through the KettleRepository related interface, transformation script information through the loadTransformation interface, input / output components and field transformation components through the TransMeta.getSteps interface, and component IDs through the StepMeta.getStepID interface. Based on the component IDs, it parses to obtain input / output component information and field transformation component information. According to the connection order between components, each field, combined with its input and output, generates a data lineage information from the source field to the target field, which is then stored in the database for persistence. The job script parsing engine first connects to the Kettle configuration library via the Kettle native Java-API interface to obtain job script information. It reads the nested script information within the job script sequentially, determines the script type, and if the read script is a job script, it loops through its script information until it reads the transformation script and parses the data lineage information. Then, it uses the Kettle native Java-API interface to obtain the connection order between scripts, analyzes and integrates all parsed data lineage information through logical judgment, generates field-level data lineage information, and stores the lineage information in the database for persistence. During this process, after connecting to the Kettle configuration library via the Kettle native Java-API interface, the job script parsing engine uses KettleRepository... The system obtains Kettle configuration library information through the relevant interfaces, retrieves job script information through the loadJob interface, sequentially reads the script information nested in the job script through the JobMeta.getJobCopies interface, and then determines whether it is a transformation script through the JobEntryCopy.isTransformation interface. (a) If it is, the transformation script is read out through loadTransformation, and the transformation script parsing engine is called to parse out the data lineage information; (b) If it is not a transformation script, the system determines whether it is a job script through JobEntryCopy.isJob. (b1) If it is a job script, the above steps are repeated until the transformation script is parsed out; (b2) If it is not a job script, the parsing ends. The data lineage visualization view displays data lineage information visually on the front-end page for users to view. During this process, the data lineage visualization view uses logical queries to visualize all data lineage information of the database, accurate to the field level, including the flow information of each field. Clicking on a field allows users to view the database, file, or interface information to which the field belongs, as well as the lineage information related to that field, including source information and all upstream information of the source information, target information and all downstream information of the target information. Clicking on the connection between fields allows users to view the task information that generated that data lineage information.

4. A data lineage analysis system based on a Kettle executor according to claim 3, characterized in that, Input / output components include database input / output components, file input / output components, and interface input / output components; The database input / output components include table input components, table output components, update components, insert / update components, and database query components; database connection information, table information, and field information are parsed from the database input / output components, where the database connection information includes database type, IP address, port number, database name, username, and password; The file input / output components include a text file input component, a text file output component, an EXCEL input component, an EXCEL output component, an XML input component, an XML output component, a JSON input component, and a JSON output component; file path information, file name information, and file header information are parsed from the file input / output components. The interface components include HTTP client components, REST client components, and Web query service components; the interface address information, interface input parameter information, and interface output parameter information are parsed from the interface components. Field conversion components include field selection components, field splitting components, row to column components, and column to row components; Extract field transformation information between components from the field transformation component.

Images