A method for generating blood relationship based on ETL converted data

Abstract

This invention discloses a method for generating ETL transformation data lineage. The method loads transformation metadata from an ETL repository using a transformation ID; retrieves metadata related to table output steps from the transformation metadata and stores it in a collection; checks if the collection is empty; if empty, it indicates no target table, and the process terminates; if the collection is not empty, it retrieves metadata related to table input steps and stores it in a variable `input`; checks if the variable is empty; if empty, it indicates no source table, and the process terminates; if the variable is not empty, it iterates through the collection, constructing a table lineage object from `input` to `output` for each variable, and adds the lineage object to the table lineage collection to achieve lineage parsing for a single table output step; merges the field lineages involved in multiple table output steps for the same target table in the table lineage collection; and persists the table lineage collection to a database. This invention can support the identification of complex ETL data processing processes.

Description

Technical Field

[0001] This invention belongs to the field of data processing technology, and in particular relates to a method for generating data lineage based on ETL transformation. Background Technology

[0002] A frequently mentioned term in data governance is lineage analysis. Lineage analysis is a means to ensure data fusion, enabling traceability of data fusion processing. Big data lineage refers to the chain of data generation; simply put, it's how our data came about and what processes and stages it went through.

[0003] Data lineage, also known as data origin or data genealogy, refers to the natural relationships that form between data throughout their creation, ETL processing, manipulation, fusion, circulation, and eventual disappearance. Similar to interpersonal relationships in human society, this relationship between data is called data kinship. From a technical perspective, if data A generates data B through ETL processing, we would say that data A and data B are related. However, unlike human kinship, data kinship also possesses some personalized characteristics.

[0004] Existing data lineage generation methods have the following problems: they require manual maintenance, with dedicated personnel on-site to maintain the relationships between source and target fields, source and target tables, and source and target databases; they demand high levels of technical expertise from personnel; and they incur high manpower and time costs when maintaining large volumes of ETL processes. Based on SQL parsing, they can only identify fields and functions appearing in the SQL, and cannot handle ETL processes not supported by SQL. Data integration tools from Alibaba and Huawei pre-establish mapping relationships (lineage relationships) between source and target fields, and then generate ETL processes based on these relationships. However, they cannot generate complex ETL processes, and a single ETL task can only output to one target table. Summary of the Invention

[0005] To address the aforementioned issues, this invention proposes a method for generating data lineage based on ETL transformation, which can support the identification of complex ETL data processing processes. The result fields can originate from ETL data processing steps, rather than being limited to source tables; multiple source tables and multiple target tables are allowed in a single ETL process.

[0006] To achieve the above objectives, the technical solution adopted by this invention is: a method for generating lineage of ETL-converted data, comprising the following steps:

[0007] S10, Input the transformation ID, and load the transformation metadata (trans) from the ETL repository using the transformation ID;

[0008] S20, retrieve the metadata related to the table output steps from the transformation metadata trans, and store it in the collection outputs;

[0009] S30, check if the set outputs is empty. If it is empty, it means there is no target table, so the process ends directly. If outputs is not empty, retrieve the metadata of the steps related to the table input and store it in the variable input.

[0010] S40, check if the variable input is empty. If it is empty, it means there is no source table, so end directly; if input is not empty, iterate through the collection outputs, with the single variable output, construct the table lineage object lineageTable from input to output, and add the lineage object to the table lineage collection lineageTables to realize the lineage parsing of a single table output step;

[0011] S50, Merge the lineage of multiple tables in the same target table from the lineage collection lineageTables, and output the field lineage involved in the steps;

[0012] S60, write the lineage table set lineageTables to the database for persistence.

[0013] Furthermore, the lineage resolution step for a single table output includes the following steps:

[0014] S41, input parameter table output step metadata output, table input step metadata input, transformation metadata trans; check the table input to table output link process and write the process into linked list link; complete the construction of the table input to single table output step link;

[0015] S42, Determine if the link from table input to table output exists. If it does not exist, end the lineage resolution process for a single table output step.

[0016] If the link exists, the field information involved in all steps before output is output through the trans preload table and stored in key-value pairs rows, where the key is the step name and the value is the row metadata, thus completing the preloading of step field metadata;

[0017] S43, Determine if the field information involved in the preloading step is abnormal. If there is an abnormality, end the lineage parsing process of the single table output step.

[0018] If there are no exceptions, traverse the linked list link from the beginning, with a single variable step. Obtain the corresponding row metadata row through step, use step and row metadata row to generate single step field source information, and store the field source information in key-value pairs fields, where the key is the step output field and the value is the field source information; complete the parsing of single step field source.

[0019] S44, obtain the target field set targets through the output table's metadata output;

[0020] S45, Traverse the collection targets and the single target field variable target. Recursively obtain all source fields and source steps corresponding to the source fields of target from fields, construct the field lineage object lineageField, and add it to the collection lineageFields to complete the lineage parsing of a single target field;

[0021] S46, Construct the lineage table object lineageTable.

[0022] Furthermore, the process of constructing the link from table input to single table output in step S41 includes the following steps:

[0023] S411, first add the table input step metadata input to the head of the linked list link;

[0024] S412, obtain the next set of input by transforming the metadata trans;

[0025] S413, determine if the next step set is empty. If it is empty, it means that the process link from table input to table output does not exist. If the next step set is not empty, then traverse the next step set.

[0026] S414: Determine if a single next step is equal to the output step metadata output; if they are equal, it means the link exists and the check is over, so add output to the end of the link; if a single next step is not equal to the output step metadata output, recursively execute S412 to S414 until the next step equals output or the next step set is empty; when the next step equals output, insert the successfully recursively executed intermediate step into the corresponding position of the link.

[0027] Furthermore, the preloading step field metadata process in step S42 includes the following steps:

[0028] S421, obtain the previous set of output through trans;

[0029] S422, iterate through all the metadata of the previous step in the previous step set; check if a single previous step has generated a new field; if a single previous step has generated a new field, add the field information to the row metadata row.

[0030] S423, retrieve the metadata of a single input field from the previous step and add it to row, and add the field source step name and row to rows;

[0031] S424, recursively execute S421 to S423 until the previous set is empty;

[0032] When the previous step is a table input step, the table input sends the SQL to the database for execution, retrieves the field metadata from the returned results, converts the database field metadata into unified field metadata in ETL, adds it to the row metadata row, and adds the table input step name and row to rows.

[0033] Furthermore, the single-step field source parsing process in step S43 includes the following steps:

[0034] S431, input parameters step metadata step, step row metadata row, cache key-value pairs fields; get field metadata collection values ​​from step row metadata row, traverse field metadata collection values, use a single field metadata value to construct field source information field, and add field to fields;

[0035] S432, determine whether the step metadata step is a table input. If it is a table input, parse the source field and source table of the SQL query result field in the table input; construct the field source information field and add field to fields;

[0036] S433, determine whether the step metadata step is related to table output. If it is related to table output, parse the input field corresponding to the target table field of step as the source field; construct the field source information field and add field to fields;

[0037] S434, determine whether the step metadata step is a step that will generate a new field lineage; if it is a step that will generate a new field lineage, parse the input field corresponding to the step output field as the source field; construct the field source information field and add field to fields.

[0038] Furthermore, the single target field lineage resolution process in step S45 includes the following steps:

[0039] S451, Start inputting the target field target cache key-value pairs fields, and determine whether the key-value pairs fields contain target;

[0040] If the key-value pair fields does not contain the target, the lineage resolution process for a single target field ends directly.

[0041] If the key-value pair fields contains target, the field lineage object field is obtained from fields through target;

[0042] S452, determine if the set of source fields in field is empty. If it is empty, it means that target comes from the field generated in step. Construct a lineageField object based on field and add it to the current lineageFields.

[0043] If not empty, iterate through the set of source fields in field, get a single source field as target, and recursively execute S451-S452 to get lineageFields;

[0044] S453, check if the recursively obtained lineageFields is empty. If it is empty, it means that the target comes from the table and has been recursively accessed to the source field of the table input. Construct a lineageField object based on the field and add it to the current lineageFields. If it is not empty, merge the recursively obtained lineageFields into the current lineageFields.

[0045] S454 returns the current lineageFields.

[0046] Furthermore, in step S60, the lineage set `lineageTables` is written to the database for persistence, including the following steps:

[0047] S61, iterate through the collection lineageTables, with a single variable being lineageTable; convert lineageTable into a database transformation table-level lineage relationship table entity object, and write it into the transformation table-level lineage relationship table;

[0048] S62, retrieve the lineage collection lineageFields from lineageTable, iterate through the lineageFields collection, and convert the single variable lineageField into a database conversion field-level lineage relationship table entity object, and write it into the conversion field-level lineage relationship table.

[0049] The beneficial effects of adopting this technical solution are:

[0050] This invention loads transformation metadata (trans) from an ETL repository via a transformation ID; then retrieves table output-related step metadata from the transformation metadata (trans) and stores it in a collection (outputs); by checking if the collection (outputs) is empty, if it is empty, it indicates that there is no target table, and the process ends directly; if outputs is not empty, it retrieves table input-related step metadata and stores it in a variable (input); then it checks if the variable (input) is empty, if it is empty, it indicates that there is no source table, and the process ends directly; if input is not empty, it iterates through the collection (outputs), with each variable being output, constructs a table lineage object (lineageTable) from input to outputs, and adds the lineage object to the table lineage collection (lineageTables), thus realizing lineage parsing for a single table output step; it merges the field lineages involved in multiple table output steps of the same target table in the table lineage collection (lineageTables); finally, it writes the table lineage collection (lineageTables) to the database for persistence. This method for data lineage generation can support the identification of complex ETL data processing processes; the result fields involved can come from ETL data processing steps, rather than only from the source table; multiple source tables and multiple target tables are allowed in an ETL process; it does not require a lot of manpower and resources, effectively reducing costs, and can cope with complex ETL processes, thus improving the adaptability of the data lineage generation process. Attached Figure Description

[0051] Figure 1 This is a schematic diagram of a method for generating lineage based on ETL-converted data according to the present invention;

[0052] Figure 2 This is a schematic diagram of the lineage analysis process for a single table output step in an embodiment of the present invention;

[0053] Figure 3 This is a schematic diagram of the process for parsing the source of a single field in an embodiment of the present invention;

[0054] Figure 4 This is a schematic diagram of the lineage analysis process for a single target field in an embodiment of the present invention. Detailed Implementation

[0055] To make the objectives, technical solutions, and advantages of this invention clearer, the invention will be further described below with reference to the accompanying drawings.

[0056] In this embodiment, see Figure 1 As shown, this invention proposes a method for generating lineage based on ETL-transformed data, including the following steps:

[0057] S10, Input the transformation ID, and load the transformation metadata (trans) from the ETL repository using the transformation ID;

[0058] Read and transform metadata from the repository into the ETL tool's built-in functionality.

[0059] S20, retrieve the metadata related to the table output steps from the transformation metadata trans, and store it in the collection outputs;

[0060] Retrieve the collection of all step metadata from trans.

[0061] Traverse the step metadata set to determine if it is a table output related step. Specifically, if the step type is one of "table output", "insert or update", or "synchronize after merge", then it is a table output related step.

[0062] If the steps are related to table output, then add the step metadata to the outputs collection.

[0063] S30, check if the set outputs is empty. If it is empty, it means there is no target table, so the process ends directly. If outputs is not empty, retrieve the metadata of the steps related to the table input and store it in the variable input.

[0064] Retrieve the collection of all step metadata from trans.

[0065] Traverse the step metadata set to determine if it is a table input related step. Specifically, if the step type is "table input", then it is a table output related step.

[0066] If the steps involve table input, then determine whether the table input and the table output in the outputs list are the same table. Iterate through the outputs list to obtain the metadata of a single table output. If the data source ID, schema, and table are the same in the metadata of the table input and table output, then they are considered to be the same table.

[0067] If the input is from the same table, ignore that table's input; if it is from a different table, assign the table's input metadata to the input.

[0068] In a transformation, there is only one input table. If the source tables are multiple tables from the same source database, the SQL in the table input uses join queries, subqueries, set processing, etc. If the source tables are multiple tables from different databases, the multiple tables from different databases are first synchronized to the same data warehouse (or data center), and then SQL join queries, subqueries, set processing, etc. are used from the same data warehouse.

[0069] The source tables involved in the SQL input (which supports multi-table joins, subqueries, and set merging) are obtained using the JSqlParser open-source technology.

[0070] S40, check if the variable input is empty. If it is empty, it means there is no source table, so end directly; if input is not empty, iterate through the collection outputs, with the single variable output, construct the table lineage object lineageTable from input to output, and add the lineage object to the table lineage collection lineageTables to realize the lineage parsing of a single table output step;

[0071] The object lineageTable contains a source library ID (one), a source table schema (multiple), a source table (multiple), a target library ID (one), a target table schema (one), a target table (one), and a field lineage collection lineageFields.

[0072] The source table schema and the source table are one-to-one, represented by a single string: source schema.source table.

[0073] S50, Merge the lineage of multiple tables in the same target table from the lineage collection lineageTables, and output the field lineage involved in the steps;

[0074] For example, when recording historical data and writing it to a history table, adding, modifying, and deleting data all use "table output" to insert the data into the history table. In this case, there are three "table outputs" for the same target table.

[0075] Iterate through the collection `lineageTables` and determine if the target library ID, target table schema, and target table are the same for two lineage objects. If the target library ID, target table schema, and target table are the same for two lineage objects, merge the two lineage objects into the first collection and discard the second collection.

[0076] Merge the source table schema and source table. If the source table in the second table's lineage object does not exist in the first table's lineage object, then add the source table schema and source table from the second table's lineage object to the first table.

[0077] Merge the field lineage sets. If the target field in the second field lineage set does not exist in the first field lineage set, directly add the second field lineage relationship to the first field lineage set. If it exists, merge the source field sets corresponding to the target field.

[0078] S60, write the lineage table set lineageTables to the database for persistence.

[0079] As an optimization of the above embodiment, in step S40, such as Figure 2 As shown, the bloodline resolution step in a single table output includes the following steps:

[0080] S41, input parameter table output step metadata output, table input step metadata input, transformation metadata trans; check the table input to table output link process and write the process into linked list link; complete the construction of the table input to single table output step link;

[0081] S42, Determine if the link from table input to table output exists. If it does not exist, end the lineage resolution process for a single table output step.

[0082] If the link exists, the field information involved in all steps before output is output through the trans preload table and stored in key-value pairs rows, where the key is the step name and the value is the row metadata, thus completing the preloading of step field metadata;

[0083] S43, Determine if the field information involved in the preloading step is abnormal. If there is an abnormality, end the lineage parsing process of the single table output step.

[0084] If there are no exceptions, traverse the linked list link from the beginning, with a single variable step. Obtain the corresponding row metadata row through step, use step and row metadata row to generate single step field source information, and store the field source information in key-value pairs fields, where the key is the step output field and the value is the field source information; complete the parsing of single step field source.

[0085] S44, obtain the target field set targets through the output table's metadata output;

[0086] For example:

[0087] If the table output step is "Table Output", then the newly added fields in the target table will be retrieved.

[0088] If the output step is "insert or update", then obtain the union of the fields involved in the table's unique key and the fields involved in the value.

[0089] If the table output step is "Synchronize after merging", then obtain the union of the fields involved in the unique key and the fields involved in the value.

[0090] S45, Traverse the collection targets and the single target field variable target. Recursively obtain all source fields and source steps corresponding to the source fields of target from fields, construct the field lineage object lineageField, and add it to the collection lineageFields to complete the lineage parsing of a single target field;

[0091] For example:

[0092] The lineage field object `lineageField` contains a set of source fields and a target field.

[0093] The source field comes from a database table and is represented as: source table schema.source table.source field.

[0094] The source field is the step, which is represented as: source step > source field.

[0095] S46, Construct the lineage table object lineageTable.

[0096] For example:

[0097] The conversion information comes from trans.

[0098] Source library, source table schema, source table from output.

[0099] The target library, target table schema, and target table originate from input.

[0100] The field lineage comes from lineageFields.

[0101] As an optimization of the above embodiment, the process of constructing the link from table input to single table output in step S41 includes the following steps:

[0102] S411, first add the table input step metadata input to the head of the linked list link;

[0103] S412, obtain the next set of input by transforming the metadata trans; the previous step can directly transmit data to multiple subsequent steps, so the next step is a set;

[0104] S413, determine if the next step set is empty. If it is empty, it means that the process link from table input to table output does not exist. If the next step set is not empty, then traverse the next step set.

[0105] S414: Determine if a single next step is equal to the output step metadata output; if they are equal, it means the link exists and the check is over, so add output to the end of the link; if a single next step is not equal to the output step metadata output, recursively execute S412 to S414 until the next step equals output or the next step set is empty; when the next step equals output, insert the successfully recursively executed intermediate step into the corresponding position of the link.

[0106] As an optimization of the above embodiment, the preloading step field metadata process in step S42 includes the following steps:

[0107] S421, obtain the previous set of output through trans;

[0108] S422, iterate through all the metadata of the previous step in the previous step set; check if a single previous step has generated a new field; if a single previous step has generated a new field, add the field information to the row metadata row.

[0109] Field information includes field name, data type, length, precision, and the name of the step from which the field originated.

[0110] S423, retrieve the metadata of a single input field from the previous step and add it to row, and add the field source step name and row to rows;

[0111] S424, recursively execute S421 to S423 until the previous set is empty;

[0112] In the previous step, when it was a table input step, the table input process sends the SQL statement to the database for execution (via the JDBC protocol). It then retrieves the field metadata from the returned results (which can be obtained via JDBC), converts the database field metadata to a unified field metadata standard used in ETL (different databases have different field metadata definitions, so this conversion is necessary), and adds it to the row metadata. Finally, it adds the table input step name and the row to the rows.

[0113] As an optimization of the above embodiments, such as Figure 3 As shown, the single-step field source parsing process in step S43 includes the following steps:

[0114] S431, input parameters step metadata step, step row metadata row, cache key-value pairs fields; get field metadata collection values ​​from step row metadata row, traverse field metadata collection values, use a single field metadata value to construct field source information field, and add field to fields;

[0115] For example:

[0116] The field source information includes the source step, the set of source fields, and the output fields.

[0117] At this point, the source information for the field can only be obtained from the field metadata value, which contains the source steps and output fields.

[0118] Before adding fields, first check if there exists a field whose key is an output field. If not, add it; otherwise, ignore it.

[0119] S432, determine whether the step metadata step is a table input. If it is a table input, parse the source field and source table of the SQL query result field in the table input; construct the field source information field and add field to fields;

[0120] For example:

[0121] Retrieve the SQL statement from the table's input metadata.

[0122] Based on the other patent "Database Query SQL Field Lineage Relationship Generation Method", the source fields and source tables of the SQL query result fields are obtained.

[0123] The constructed field source information field includes the source step (the table input step name), the source field set (multiple fields from multiple source tables, in the format source table schema.source table.source field), and the output field (SQL result field).

[0124] Before adding fields, first check if there exists a key that is an output field. If not, add the new field. If it exists, retrieve the previous field and add the new source field to the source field set.

[0125] S433, determine whether the step metadata step is related to table output. If it is related to table output, parse the input field corresponding to the target table field of step as the source field; construct the field source information field and add field to fields;

[0126] For example:

[0127] If the table output step is "Table Output", then the newly added fields in the target table for obtaining the configuration are the target fields, and the corresponding source fields from the previous step are the source fields.

[0128] If the output step is "insert or update", then the fields involved in obtaining the table's unique key and the fields involved in updating the value are the target fields, and the corresponding source fields from the previous step are the source fields.

[0129] If the output step is "Synchronize after merging", then the fields involved in obtaining the table's unique key and the fields involved in updating the value are the target fields, and the corresponding source fields from the previous step are the source fields.

[0130] Before adding fields, first check if there exists a field whose key is the target field. If not, add the new field. If it exists, retrieve the previous field and add the new source field to the source field set.

[0131] S434, determine whether the step metadata step is a step that will generate a new field lineage; if it is a step that will generate a new field lineage, parse the input field corresponding to the step output field as the source field; construct the field source information field and add field to fields.

[0132] For example: What is the step to generate a new field lineage? After the step processing, the newly added output field comes from one or more input fields.

[0133] The field is renamed so that the input field A is the output field B, only the name is changed, and the output field B originates from field A.

[0134] Calculate the result of A+B+C, given input fields A, B, and C. The output field D is derived from fields A, B, and C.

[0135] Split fields: Take a date string field A as input, split it into year B, month C, and day D, and output fields B, C, and D which are derived from field A.

[0136] Currently supported steps include: field selection, value mapping, numerical range, string manipulation, string trimming, string replacement, calculator, column to row conversion, row to column conversion, column splitting into multiple rows, field splitting, row flattening, setting field values, data anonymization, Java scripts, and JavaScript scripts. Extensions can be added to handle additional steps as needed.

[0137] Because the metadata definitions are different for each step, the specific methods for constructing the source information of the fields also differ. These will not be explained in detail here.

[0138] Before adding fields, first check if there is a field whose key is the output field. If not, add the new field. If it exists, retrieve the previous field and add the new source field to the source field set.

[0139] As an optimization of the above embodiments, such as Figure 4 As shown, the single target field lineage resolution process in step S45 includes the following steps:

[0140] S451, Start inputting the target field target cache key-value pairs fields, and determine whether the key-value pairs fields contain target;

[0141] If the key-value pair fields does not contain the target, the lineage resolution process for a single target field ends directly.

[0142] If the key-value pair fields contains target, the field lineage object field is obtained from fields through target;

[0143] S452, determine if the set of source fields in field is empty. If it is empty, it means that target comes from the field generated in step. Construct a lineageField object based on field and add it to the current lineageFields.

[0144] If not empty, iterate through the set of source fields in field, get a single source field as target, and recursively execute S451-S452 to get lineageFields;

[0145] S453, check if the recursively obtained lineageFields is empty. If it is empty, it means that the target comes from the table and has been recursively accessed to the source field of the table input. Construct a lineageField object based on the field and add it to the current lineageFields. If it is not empty, merge the recursively obtained lineageFields into the current lineageFields.

[0146] S454 returns the current lineageFields.

[0147] As an optimization of the above embodiment, in step S60, the lineage set `lineageTables` is written to the database for persistence, including the following steps:

[0148] Table structure design (using MySQL as an example).

[0149] Table 1. Conversion Table of Blood Relationships

[0150] Field Chinese Name field name type illustrate primary key id bigint Snowflake algorithm generates ID Convert ID trans_id bigint Change name trans_name varchar(255) Redundant fields Source library ID source_ds_id bigint Source library name source_ds_name varchar(255) Redundant fields Source table pattern source_schema varchar(255) Source Table source_table varchar(255) Target library ID target_ds_id bigint target library name target_ds_name varchar(255) Redundant fields Target table pattern target_schema varchar(255) Target table target_table varchar(255) Generation time crt_time datetime

[0151] Transform field-level bloodline table

[0152]

[0153] S61, iterate through the collection lineageTables, with a single variable being lineageTable; convert lineageTable into a database transformation table-level lineage relationship table entity object, and write it into the transformation table-level lineage relationship table;

[0154] A lineageTable object is converted into multiple transformation table-level lineage table entity objects because the lineageTable contains multiple source tables.

[0155] S62, retrieve the lineage collection lineageFields from lineageTable, iterate through the lineageFields collection, and convert the single variable lineageField into a database conversion field-level lineage relationship table entity object, and write it into the conversion field-level lineage relationship table.

[0156] A lineageField object is converted into multiple transformation table-level lineage table entity objects because the lineageField has multiple source fields.

[0157] trans_id comes from lineageTable;

[0158] trans_table_id is generated from S61;

[0159] target_field comes from lineageField;

[0160] source_field comes from a single source field in the lineageField source field collection.

[0161] The source_type parameter determines whether the lineageField source field contains >. If it does, the value is 2 (from the step); otherwise, it is 1 (from the database table).

[0162] The foregoing has shown and described the basic principles, main features, and advantages of the present invention. Those skilled in the art should understand that the present invention is not limited to the above embodiments. The embodiments and descriptions in the specification are merely illustrative of the principles of the invention. Various changes and modifications can be made to the invention without departing from its spirit and scope, and all such changes and modifications fall within the scope of the present invention as claimed. The scope of protection of this invention is defined by the appended claims and their equivalents.

Claims

1. A method for generating lineage of ETL-transformed data, characterized in that, Including the following steps: S10, Input the transformation ID, and load the transformation metadata (trans) from the ETL repository using the transformation ID; S20, retrieve the metadata related to the table output steps from the transformation metadata trans, and store it in the collection outputs; S30, check if the set outputs is empty. If it is empty, it means there is no target table, and the process ends directly. If outputs is not empty, retrieve the metadata of the steps related to the table input and store it in the variable input; S40, check if the variable input is empty. If it is empty, it means there is no source table, and then the process ends directly. If input is not empty, iterate through the collection outputs, with output as the single variable, construct the lineage object lineageTable from input to output, and add the lineage object to the lineage collection lineageTables to realize the lineage parsing of a single table output step; The single table output step of lineage resolution includes the following steps: S41, Input parameter table output step metadata output, table input step metadata input, transformation metadata trans; Check the process from table input to table output and write the process into the linked list; complete the construction of the link from table input to a single table output step; S42, Determine if the link from table input to table output exists. If it does not exist, end the lineage resolution process for a single table output step. If the link exists, the field information involved in all steps before output is output through the trans preload table and stored in key-value pairs rows, where the key is the step name and the value is the row metadata, thus completing the preloading of step field metadata; S43, Determine if the field information involved in the preloading step is abnormal. If there is an abnormality, end the lineage parsing process of the single table output step. If there are no exceptions, traverse the linked list link from the beginning, with a single variable step. Obtain the corresponding row metadata row through step, use step and row metadata row to generate single step field source information, and store the field source information in key-value pairs fields, where the key is the step output field and the value is the field source information; complete the parsing of single step field source. S44, obtain the target field set targets through the output table's metadata output; S45, Traverse the collection targets and the single target field variable target. Recursively obtain all source fields and source steps corresponding to the source fields of target from fields, construct the field lineage object lineageField, and add it to the collection lineageFields to complete the lineage parsing of a single target field; S46, Construct the lineage table object lineageTable; S50, Merge the lineage of multiple tables in the same target table from the lineage collection lineageTables, and output the field lineage involved in the steps; S60, write the lineage table set lineageTables to the database for persistence.

2. The method for generating lineage based on ETL-transformed data according to claim 1, characterized in that, The process of constructing the link from table input to single table output in step S41 includes the following steps: S411, first add the table input step metadata input to the head of the linked list link; S412, obtain the next set of input by transforming the metadata trans; S413, determine if the next step set is empty. If it is empty, it means that the process link from table input to table output does not exist. If the next step set is not empty, then traverse the next step set. S414, determine whether a single next step is equal to the output step metadata output; If they are equal, it means the link exists and the check is complete, so add output to the end of the link; if a single next step is not equal to the output step metadata output, then recursively execute S412 to S414 until the next step is equal to output or the next step set is empty; when the next step is equal to output, insert the successfully recursively executed intermediate step into the corresponding position of the link.

3. The method for generating lineage based on ETL-transformed data according to claim 1, characterized in that, The preloading step field metadata process in step S42 includes the following steps: S421, obtain the previous set of output through trans; S422, iterate through all the metadata of the previous step in the previous step set; check if a single previous step has generated any new fields; If a new field is generated in the previous step, add that field's information to the row metadata. S423, retrieve the metadata of a single input field from the previous step and add it to row, and add the field source step name and row to rows; S424, recursively execute S421 to S423 until the previous set is empty; When the previous step is a table input step, the table input sends the SQL to the database for execution, retrieves the field metadata from the returned results, converts the database field metadata into unified field metadata in ETL, adds it to the row metadata row, and adds the table input step name and row to rows.

4. The method for generating lineage based on ETL-transformed data according to claim 1, characterized in that, The single-step field source parsing process in step S43 includes the following steps: S431, Input parameters: step metadata, row metadata, and cached key-value pairs fields; Retrieve the field metadata collection values ​​from the step row metadata row, iterate through the field metadata collection values, construct the field source information field using a single field metadata value, and add the field to fields; S432, determine whether the step metadata step is a table input. If it is a table input, parse the source field and source table of the SQL query result field in the table input; construct the field source information field and add field to fields; S433, determine whether the step metadata step is related to table output. If it is related to table output, parse the input field corresponding to the target table field of step as the source field; construct the field source information field and add field to fields; S434, determine whether the step metadata step is a step that will generate a new field lineage; if it is a step that will generate a new field lineage, parse the input field corresponding to the step output field as the source field; construct the field source information field and add field to fields.

5. The method for generating lineage based on ETL-transformed data according to claim 1, characterized in that, The single target field lineage resolution process in step S45 includes the following steps: S451, Start inputting the target field target cache key-value pairs fields, and determine whether the key-value pairs fields contain target; If the key-value pair fields does not contain the target, the lineage resolution process for a single target field ends directly. If the key-value pair fields contains target, the field lineage object field is obtained from fields through target; S452, determine if the set of source fields in field is empty. If it is empty, it means that target comes from the field generated in step. Construct a lineageField object based on field and add it to the current lineageFields. If not empty, iterate through the set of source fields in field, get a single source field as target, and recursively execute S451-S452 to get lineageFields; S453, check if the recursively obtained lineageFields is empty. If it is empty, it means that the target comes from the table and has been recursively recursively to the source field of the table input. Construct a lineageField object based on the field and add it to the current lineageFields. If not empty, merge the recursively obtained lineageFields into the current lineageFields; S454 returns the current lineageFields.

6. The method for generating lineage based on ETL-transformed data according to claim 1, characterized in that, In step S60, the lineage table set lineageTables is written to the database for persistence, including the following steps: S61, iterate through the collection lineageTables, with a single variable being lineageTable; convert lineageTable into a database transformation table-level lineage relationship table entity object, and write it into the transformation table-level lineage relationship table; S62, retrieve the lineage collection lineageFields from lineageTable, iterate through the lineageFields collection, and convert the single variable lineageField into a database conversion field-level lineage relationship table entity object, and write it into the conversion field-level lineage relationship table.

Images