Method and device for automatically generating traceability information in ETL, and electronic device
By automatically generating and storing traceability information during the ETL process, the problem of ETL's inability to trace target data is solved, achieving non-intrusive traceability and efficient querying.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- QINGDAO HISENSE TRANS TECH
- Filing Date
- 2022-02-28
- Publication Date
- 2026-05-15
AI Technical Summary
Existing ETL tools cannot track the source and transformation process of target data during data governance, which requires additional structural changes to the target database, impacting performance and storage space.
During the ETL process, traceability information is automatically generated and stored in a separate database. By parsing the instructions in the ETL task, traceability information of the target data is generated, including the relationship between the source data, the transformation process, and the target data. This information is then stored and displayed using a graph database.
Without requiring structural changes to the target database, it enables traceability of target data, avoids intrusive impacts on performance and storage space, and improves the efficiency of data querying and display.
Smart Images
Figure CN116701500B_ABST
Abstract
Description
Technical Field
[0001] This invention relates to the field of data management technology, and in particular to a method, apparatus, and electronic device for automatically generating traceability information in ETL. Background Technology
[0002] In recent years, with the rapid development of technologies such as computers, artificial intelligence, and big data, enterprises and government departments have increasingly strong demands for data governance. ETL (Extract-Transform-Load) is an indispensable tool in the data governance process. Performing ETL tasks allows data to be extracted from source data sources, processed and transformed, and then transferred to target data sources. Currently, in the data governance process, it is necessary to examine the source of a specific piece of data and how it was transformed into target data. However, current ETL methods do not support data tracking or require additional tracking functionality to be added to the target database storing the target data, which can lead to changes in the target database structure. Summary of the Invention
[0003] The purpose of this application is to provide a method, apparatus, and electronic device for automatically generating traceability information in ETL. This addresses the problem that in the current ETL data management process, it is necessary to view the source data and transformation process of a target data, but current ETL methods do not support target data tracing.
[0004] In a first aspect, embodiments of this application provide a method for automatically generating traceability information in ETL, the method comprising:
[0005] Receive a request for ETL data transmission and execute a pre-configured ETL task. The ETL task includes multiple subtasks, each of which generates a corresponding target data containing complete information items.
[0006] The source data acquisition instructions of each subtask are analyzed to determine the source data related information items; the transformation instructions in each subtask that transform the source data are analyzed to determine the transformation process related information items; and the target data generation instructions in each subtask are analyzed to determine the target data related information items.
[0007] Establish the relationship between the source data-related items, the transformation process-related items, and the target data-related items corresponding to the same subtask, and obtain the source information of the target data corresponding to each subtask;
[0008] The target data generated by the ETL task is stored in the first database, and the traceability information corresponding to different target data is stored in the second database.
[0009] In some possible embodiments, parsing the source data acquisition instructions of each subtask to determine source data-related information items, parsing the transformation instructions in each subtask that transform the source data to determine transformation process-related information items, and parsing the target data generation instructions in each subtask to determine target data-related information items, including:
[0010] Parse the source data acquisition instructions of each subtask to determine the data identifier of the source data and the first address information of the source data storage location;
[0011] Parse the conversion instructions that convert the source data in each subtask, and determine the second address information, conversion type, and parameter information for performing the conversion in the source data to locate the information item that has been converted.
[0012] Parse the target data generation instructions in each subtask to determine the data identifier and third address information of the storage location of the generated target data.
[0013] In some possible embodiments, parsing the source data acquisition instructions of each subtask to determine the data identifier of the source data and the first address information of the source data storage location includes at least one of the following:
[0014] Parse the source data acquisition instructions of each subtask to determine that the source data has a first primary key field that uniquely identifies the source data, and determine that the target data has a second primary key field that uniquely identifies the target data based on the parsing of the target data generation instructions in each subtask. If the field values of the first primary key field and the second primary key field are the same, determine that the address of the first primary key field is used as the first address information, and determine that the hash value obtained by performing SHA2 calculation on the identifiers of each field of the source data is the data identifier of the source data.
[0015] Parse the source data acquisition instructions of each subtask, determine that the source data has a first primary key field that uniquely identifies the source data, and determine that the target data does not have a second primary key field that uniquely identifies the target based on the parsing of the target data generation instructions in each subtask. Determine that the address of the first primary key field is used as the first address information, and determine that the hash value obtained by performing SHA2 calculation on the identifiers of each field of the source data is the data identifier of the source data.
[0016] Parse the source data acquisition instructions of each subtask to determine that the source data has a first primary key field that uniquely identifies the source data, and determine that the target data has a second primary key field that uniquely identifies the target data based on the parsing of the target data generation instructions in each subtask. If the field values of the first primary key field and the second primary key field are different, determine that the data type and content of the first primary key field are used as the first address information, and determine that the hash value obtained by performing SHA2 calculation on the identifiers of each field of the source data is the data identifier of the source data.
[0017] Parse the source data acquisition instructions for each subtask, determine that the source data does not have a first primary key field that uniquely identifies the source data, determine that the address of the column containing at least one information item in the source data is used as the first address information, and determine that the hash value obtained by performing SHA2 calculation on the identifiers of each field of the source data is the data identifier of the source data.
[0018] In some possible embodiments, parsing the target data generation instructions in each subtask to determine the data identifier and third address information of the storage location of the generated target data includes at least one of the following steps:
[0019] Parse the target data generation instructions of each subtask, determine that the target data has a second primary key field that uniquely identifies the source data, determine that the address of the second primary key field is used as the third address information, and determine that the hash value obtained by performing SHA2 calculation on the identifiers of each field of the target data is the data identifier of the target data.
[0020] Parse the target data generation instructions for each subtask, determine that the target data does not have a second primary key field that uniquely identifies the target data, determine that the address of the column containing at least one information item in the target data is used as the third address information, and determine that the hash value obtained by performing SHA2 calculation on the identifier of each information item field of the target data is the data identifier of the source data.
[0021] In some possible embodiments, the relationships between source data-related items, transformation process-related items, and target data-related items corresponding to the same subtask are established to obtain the source information of the target data corresponding to each subtask, including:
[0022] When the same subtask transforms data from multiple sources, a record is output for each source data. The record includes source data-related information items, transformation process-related information items, and target data-related information items.
[0023] By merging records corresponding to multiple sources of data transformed from the same subtask, the source information of the target data corresponding to each subtask is obtained.
[0024] In some possible embodiments, the source data is stored in a corresponding table in the form of rows, and the target data is stored in a corresponding table in the form of rows, wherein each subtask is used to generate the target data for the corresponding row, and the source information of the target data corresponding to each subtask is also provided.
[0025] In some possible embodiments, the tracing information corresponding to different target data is stored in a second database, including:
[0026] Based on the source data-related information items in the traceability information, determine the row, table, and database where the source data is located, and store them as different types of first entities in the graph database, and establish the association relationship between different types of first entities;
[0027] Based on the target data-related information items in the traceability information, determine the row, table, and database where the target data is located, and store them as different types of second entities in the graph database, and establish the association relationship between different types of second entities;
[0028] Based on the transformation process-related information items in the traceability information, establish the association relationship between entities of type row with transformation relationship.
[0029] In some possible embodiments, the method further includes: responding to a traceability information viewing instruction, displaying the traceability information corresponding to the target data in the form of a graph structure, wherein the graph structure includes first entities of different types and their relationships, second entities of different types and their relationships, relationships between entities with transformation relationships of type row, and displaying relevant information items related to the transformation process between the corresponding related entities.
[0030] Secondly, embodiments of this application provide an apparatus for automatically generating traceability information in ETL, the apparatus comprising:
[0031] The ETL task execution module is used to receive ETL data transmission requests and execute pre-configured ETL tasks. The ETL task includes multiple sub-tasks, each of which is used to generate a corresponding target data containing complete information items.
[0032] The parsing module is used to parse the source data acquisition instructions of each subtask to determine the source data related information items, parse the transformation instructions in each subtask to transform the source data to determine the transformation process related information items, and parse the target data generation instructions in each subtask to determine the target data related information items.
[0033] The traceability information generation module is used to establish the relationship between source data-related items, conversion process-related items and target data-related items corresponding to the same subtask, so as to obtain the traceability information of the target data corresponding to each subtask;
[0034] The storage module is used to store the target data generated by the execution of the ETL task into the first database, and to store the traceability information corresponding to different target data into the second database.
[0035] Thirdly, embodiments of this application provide an electronic device, including at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, the instructions being executed by the at least one processor to enable the at least one processor to execute the method for automatically generating traceability information in ETL provided in the first aspect above.
[0036] Fourthly, embodiments of this application provide a computer storage medium storing a computer program for causing a computer to execute the method for automatically generating traceability information in the ETL provided in the first aspect.
[0037] In this embodiment of the application, in order to solve the problem that during the governance process of ETL transmission data, it is necessary to view the source data and transformation process of a certain target data, but current ETL does not support the tracking of target data, this embodiment of the application automatically generates the source information of the target data and stores it separately in the database without complicated configuration during the process of extracting, transforming and loading source data using ETL transmission data. This ensures that the source information of the target data is automatically generated according to the task configuration or database information during the ETL transmission process.
[0038] Other features and advantages of this application will be set forth in the description which follows, and will be apparent in part from the description, or may be learned by practicing the application. The objectives and other advantages of this application may be realized and obtained by means of the structures particularly pointed out in the written description, claims, and drawings. Attached Figure Description
[0039] To more clearly illustrate the technical solutions of the embodiments of this application, the drawings used in the embodiments of this application will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.
[0040] Figure 1 This is a schematic diagram of an application environment according to an embodiment of this application;
[0041] Figure 2 This is a flowchart illustrating a method for automatically generating traceability information in an ETL according to an embodiment of this application.
[0042] Figure 3 This is a schematic diagram illustrating the automatic generation of traceability information during a transformation in an ETL process according to an embodiment of this application;
[0043] Figure 4This is a schematic diagram illustrating the automatic generation of traceability information in an ETL process according to an embodiment of this application;
[0044] Figure 5 This is another schematic diagram illustrating the automatic generation of traceability information in an ETL according to an embodiment of this application;
[0045] Figure 6 This is another schematic diagram illustrating the automatic generation of traceability information in an ETL according to an embodiment of this application;
[0046] Figure 7 This is a schematic diagram illustrating the storage of automatically generated traceability information in an ETL process according to an embodiment of this application;
[0047] Figure 8 This is a schematic diagram of the structure of an ETL device for automatically generating traceability information according to an embodiment of this application;
[0048] Figure 9 This is a schematic diagram of an electronic device structure according to an embodiment of this application. Detailed Implementation
[0049] The technical solutions in the embodiments of this application will be clearly and thoroughly described below with reference to the accompanying drawings. In the description of the embodiments of this application, unless otherwise stated, " / " means "or", for example, A / B can mean A or B; "and / or" in the text is merely a description of the relationship between related objects, indicating that there can be three relationships, for example, A and / or B can mean: A exists alone, A and B exist simultaneously, and B exists alone. In addition, in the description of the embodiments of this application, "multiple" means two or more.
[0050] In the description of the embodiments of this application, unless otherwise stated, the term "multiple" refers to two or more, and other quantifiers are similarly understood. The preferred embodiments described herein are for illustration and explanation only and are not intended to limit this application. Furthermore, the embodiments and features in the embodiments of this application can be combined with each other without conflict.
[0051] To further illustrate the technical solutions provided in the embodiments of this application, a detailed description is provided below in conjunction with the accompanying drawings and specific implementation methods. Although the embodiments of this application provide method operation steps as shown in the following embodiments or drawings, more or fewer operation steps may be included in the method based on conventional or non-inventive effort. For steps that do not logically have a necessary causal relationship, the execution order of these steps is not limited to the execution order provided in the embodiments of this application. In actual processing or when the control device executes the method, it may be executed sequentially or in parallel according to the method shown in the embodiments or drawings.
[0052] Given that related technologies require viewing the source data and transformation process of a target data during the ETL data management process, but current ETL methods do not support target data tracing, this application proposes a method, apparatus, and electronic device for automatically generating traceability information in ETL, which can automatically generate traceability information of target data based on task configuration or database information during ETL transmission.
[0053] In view of this, the inventive concept of this application is: during the process of extracting, transforming and loading source data using ETL data transmission, no complicated configuration is required, and the traceability information of the target data is automatically generated and stored separately in the database, thereby ensuring that the traceability information of the target data is automatically generated according to the task configuration or database information during the ETL transmission process.
[0054] Other features and advantages of this application will be set forth in the description which follows, and will be apparent in part from the description, or may be learned by practicing the application. The objectives and other advantages of this application may be realized and obtained by means of the structures particularly pointed out in the written description, claims, and drawings.
[0055] The method for automatically generating traceability information in ETL in the embodiments of this application will be described in detail below with reference to the accompanying drawings.
[0056] See Figure 1 This is a schematic diagram of an application environment according to an embodiment of this application.
[0057] Upon receiving a request for ETL data transfer, the ETL data transfer process begins. The ETL module primarily consists of four parts: the input module, ETL memory, the transformation stream module, and the output module. Specifically, the input module reads data from the source data source; the ETL memory converts the read source data into a buffer within the ETL process; the transformation stream module performs streaming transformations on the read source data; and the output module writes the transformed target data into the target database.
[0058] Existing ETL technology does not record traceability information of the source data to the target data during the transmission process, and ETL does not support the tracking of the target data.
[0059] Figure 2 This application provides a schematic diagram of a method for automatically generating traceability information in ETL according to an embodiment of the present application, including:
[0060] Step 201: Receive the request for ETL data transmission and execute the pre-configured ETL task. The ETL task includes multiple sub-tasks, each of which is used to generate a corresponding target data containing complete information items.
[0061] A pre-configured ETL task mainly includes the source data as input, the target data in the output file, and the transformation process between the source data and the target data. The source data as input includes, but is not limited to, relational databases, and can also be large data types or data stored with a certain structure, such as data from an Excel file, which, although not structured data, is stored with a certain structure.
[0062] An ETL task consists of multiple subtasks that transform source data into target data, each of which generates a corresponding target data item containing complete information.
[0063] A complete information item refers to the information corresponding to the complete process of transforming source data into target data. The source data transmitted to the target data may have the following possible organization processes: it comes from a single source of data; it is source data that has been horizontally or vertically spliced together from the corresponding data of two or more data sources; or it has undergone any number of transformation processes such as desensitization, repair, and mapping of the source data.
[0064] Step 202: Parse the source data acquisition instructions of each subtask to determine the source data related information items, parse the conversion instructions in each subtask to convert the source data to determine the conversion process related information items, and parse the target data generation instructions in each subtask to determine the target data related information items.
[0065] Following the pre-configured ETL tasks in step 201 above, each subtask contains, but is not limited to, three types of instructions: source data acquisition instructions, transformation instructions, and target data generation instructions. These three instructions are used to obtain relevant information items related to the source data, target data, and transformation process throughout the complete data transmission process of each subtask. This is to facilitate the establishment of the relationship link between the source data, target data, and intermediate transformation processes in the following text through different information items.
[0066] As an optional implementation, the source data acquisition instructions of each subtask are parsed to determine source data-related information items, the transformation instructions in each subtask that transform the source data are parsed to determine transformation process-related information items, and the target data generation instructions in each subtask are parsed to determine target data-related information items, including:
[0067] Parse the source data acquisition instructions of each subtask to determine the data identifier of the source data and the first address information of the source data storage location;
[0068] Parse the conversion instructions that convert the source data in each subtask, and determine the second address information, conversion type, and parameter information for performing the conversion in the source data to locate the information item that has been converted.
[0069] Parse the target data generation instructions in each subtask to determine the data identifier and third address information of the storage location of the generated target data.
[0070] Specifically, the data identifier of the source data is used to uniquely identify the source data. The source data can be traced through the data identifier of the source data. The data identifier of the source data can be specified by the user as a data column or the system can parse the unique identifier key pre-configured for this source data. Alternatively, when the user does not specify or the source data itself is not configured with a unique identifier key, the SHA2 secure hash algorithm (including multiple standards such as SHA-224 and SHA-256) can be used as the data identifier.
[0071] The data identifier of the target data is used to uniquely identify the target data. The target data can be traced through the data identifier. The data identifier of the target data can be specified by the user as a data column or the system can parse the unique identifier key pre-configured for the target data. Alternatively, when the user does not specify or the target data itself is not configured with a unique identifier key, the SHA2 secure hash algorithm (including multiple standards such as SHA-224 and SHA-256) can be used as the data identifier.
[0072] The first address information value is used to locate the storage location of the source data in the database, and the third address information is used to locate the storage location of the target data in the database. The second address information specifically refers to the address of which field among several fields of the source data the transformed information item is located during the process of transforming the source data into the target data. In this application, the transformation types include, but are not limited to, mapping operations, de-identification operations, and repair operations. The parameter information for performing the transformation can be understood as the parameter information defined by the transformation operation. An example is given below, for instance... Figure 3 The source table of the source data includes four fields: ID1 (address 0 for the corresponding information item), name Zhang San (address 1 for the corresponding information item), age 24 (address 2 for the corresponding information item), and address (address 3 for the corresponding information item) in the target table of the target data. This operation maps the age in the source table. The conversion in this application is not limited to the conversion described in this example. The age 24 information item in the source data is converted to the age group as youth. Therefore, it can be seen that the second address information of the converted information item in the process of converting from the source table to the target table is operated on for the age column data. Therefore, the second address of the information item that undergoes the conversion operation is the second item of the source data table A. See the conversion part of the record content in the traceability information below the arrow. So the second address information of the converted information item in this process is 2, the conversion type is mapping operation, and the conversion parameter information is the parameter defined by the mapping operation: 24 is converted to youth.
[0073] Step 203: Establish the relationship between the source data-related items, the transformation process-related items, and the target data-related items corresponding to the same subtask, and obtain the source information of the target data corresponding to each subtask.
[0074] The traceability information in this application can be understood as the entire process of obtaining target data by tracing the source data during ETL transmission, forming a traceability link from the source data to the corresponding target data. This traceability link is called the traceability information of each target data. In other words, if you need to know how a certain target data was obtained, you can obtain the source data and the intermediate conversion process through the traceability information of the target data.
[0075] As an optional implementation, the association between source data-related items, transformation process-related items, and target data-related items corresponding to the same subtask is established to obtain the source information of the target data corresponding to each subtask, including:
[0076] When the same subtask transforms data from multiple sources, a record is output for each source data. The record includes source data-related information items, transformation process-related information items, and target data-related information items.
[0077] By merging records corresponding to multiple sources of data transformed from the same subtask, the source information of the target data corresponding to each subtask is obtained.
[0078] Specifically, when the source data is formed by fusing multiple data sources, the source of the target data obtained from these multiple source data needs to be recorded for each source data. In this case, the traceability information of the target data is the fusion of the records corresponding to each source data.
[0079] Step 204: Store the target data generated by the ETL task in the first database, and store the traceability information corresponding to different target data in the second database.
[0080] Specifically, in existing technologies, if traceability information for tracking target data is pre-configured for an ETL task, additional changes are needed to the database structure storing the target data. This involves adding extra fields to the pre-configured target data to store the traceability information. Since adding these extra fields impacts the performance and storage space of the target data source, it is highly intrusive to the target data source. In this application, the target data generated by the final ETL task is stored separately from the traceability information corresponding to different target data, thus avoiding intrusion on either the source or target data source.
[0081] In summary, this application enables ETL to automatically generate traceability information corresponding to the target data during the transmission process based on the task configuration or database information, without being intrusive to the source data source or the target data source, and without adding extra fields to the target data to affect the performance and storage space of the target data source.
[0082] Because the data fields of the source data may differ, the first address information of the source data storage location determined by the source data acquisition instruction of the parsing subtask will also differ; similarly, because the data fields of the target data may differ, the third address information of the target data storage location determined by the target data generation instruction of the parsing subtask will also differ. As can be seen from the above, the traceability information of the target data corresponding to the parsing subtask will also vary. This application broadly categorizes these differences into four cases (it should be noted that since the information to be recorded in the conversion part of the traceability information has already been described in step 202 above, for the sake of simplicity, the following four cases assume that the source data has not undergone a conversion process in obtaining the target data, and only describe the information to be recorded in the source information part and the traceability information of the target data):
[0083] Scenario 1: The source data has a first primary key field that uniquely identifies the source data, and the corresponding generated target data has a second primary key field that uniquely identifies the target data.
[0084] Specifically, the recording of traceability information in scenario one is the most concise. As an optional implementation method, scenario one can exist in two ways.
[0085] A. When the values of the first primary key field and the second primary key field are the same.
[0086] The source information indicates the source data as follows: the first primary key field address is used as the first address information, and the hash value obtained by performing SHA2 calculation on each field identifier of the source data is determined as the data identifier of the source data;
[0087] The part of the traceability information that represents the target data is: using the address of the second primary key field as the third address information, and determining the hash value obtained by performing SHA2 calculation on the identifiers of each field of the target data as the data identifier of the target data.
[0088] For example, see Figure 4The left side shows the source data, the right side shows the target data, the area above the arrow in the middle shows the pre-configured ETL transmission data, and the area below the arrow shows the traceability information. Since the primary key field ID of the source data is included in the ETL transmission process, its address in the transmission data is column 0. Therefore, the first address information and the third address information are both 0. In this way, when storing the traceability information of the target data, the source data with the corresponding value of 1 in the storage can be restored based on the first address information 0, thereby saving ETL memory space.
[0089] B. When the values of the first primary key field and the second primary key field are different.
[0090] The part of the traceability information that represents the source data is: the data type and content of the first primary key field are used as the first address information, and the hash value obtained by performing SHA2 calculation on the identifiers of each field of the source data is determined as the data identifier of the source data;
[0091] The part of the traceability information that represents the target data is: using the address of the second primary key field as the third address information, and determining the hash value obtained by performing SHA2 calculation on the identifiers of each field of the target data as the data identifier of the target data.
[0092] For example, see Figure 5 This process involves fusing two source data sets to generate a single target data set. As described in step 203 above, the source data portion of the traceability information needs to be recorded twice. Since the ID primary key in the target data is the same as that in source table 1, the first address information of source table 1 only needs to be the address of the first primary key field and the SHA2 value. However, the ID in source table 2 is not included in the target data. Therefore, the first address information of source table 2 needs to record the primary key content of this source data, namely "column name: ID, value 1000", its data type long integer, and the SHA2 value. By recording the two source data sets separately, it can be determined which two source data sets the target data was formed from.
[0093] Scenario 2: The source data has a first primary key field that uniquely identifies the source data, but the corresponding generated target data does not have a second primary key field that uniquely identifies the target data.
[0094] The source information indicates the source data as follows: the first primary key field address is used as the first address information, and the hash value obtained by performing SHA2 calculation on the identifiers of each field of the source data is determined as the data identifier of the source data.
[0095] The part of the traceability information that represents the target data is: using the address of at least one information item in the target data as the third address information, and determining the data identifier of the source data by performing SHA2 calculation on the hash value of the field identifier of each information item in the target data.
[0096] It should be noted that in this case, the first address information in the source information section of the traceability information can be the address of the first primary key field or the address of at least one information item in the column, as long as it serves to locate the source data. However, in general, if a primary key field exists, the address of the first primary key field will be used as the content of the source data section in the traceability information.
[0097] Scenario 3: The source data does not have a first primary key field that uniquely identifies the source data, but the corresponding generated target data has a second primary key field that uniquely identifies the target data.
[0098] The part of the traceability information that represents the source data is: taking the address of at least one information item in the column of the source data as the first address information, and determining the hash value obtained by performing SHA2 calculation on the identifiers of each field of the source data as the data identifier of the source data.
[0099] The part of the traceability information that represents the target data is: using the address of the second primary key field as the third address information, and determining the hash value obtained by performing SHA2 calculation on the identifiers of each field of the target data as the data identifier of the target data.
[0100] In this case, the source data section of the traceability information is similar to the target data section of the traceability information in case two, and will not be repeated here.
[0101] Scenario 4: The source data does not have a first primary key field that uniquely identifies the source data, and the corresponding generated target data does not have a second primary key field that uniquely identifies the target data.
[0102] The part of the traceability information that represents the source data is: taking the address of at least one information item in the column of the source data as the first address information, and determining the hash value obtained by performing SHA2 calculation on the identifiers of each field of the source data as the data identifier of the source data.
[0103] The part of the traceability information that represents the target data is: using the address of at least one information item in the target data as the third address information, and determining the data identifier of the source data by performing SHA2 calculation on the hash value of the field identifier of each information item in the target data.
[0104] The parts of the traceability information here that represent the source data and the parts that represent the target data are similar to the parts of the traceability information that represent the target data in Case 2, and will not be repeated here.
[0105] The following is through Figure 6 Explain situations where at least one of the source and target data lacks a primary key field, as described in scenarios two, three, and four. Figure 6Assuming neither the source nor the target data has a primary key field, the traceability information cannot be traced through the primary key. The left side represents the source data, the right side represents the target data, the area above the middle arrow represents the ETL transmission data, and the area below represents the traceability information. In the traceability information, the first address information in the source information section is the address of at least one information item in the column. For the target data, the address of at least one information item in the column is used as the third address information. In the example, two addresses are recorded: the address of "Zhang San" and the address of "24" to reduce the data during traceability.
[0106] As can be seen from the above situations, this application can also store and trace source information for source data or target data that do not have a primary key field.
[0107] As an optional implementation, each source data is stored in a corresponding table in the form of rows, and each target data is stored in a corresponding table in the form of rows. Each subtask is used to generate the target data for the corresponding row, and the source information of the target data for each subtask is also provided.
[0108] Specifically, in the above example diagram, both the source data and the target data are stored in the corresponding tables in the form of rows. ETL transmits and processes only one row of data. Each cell stores the data content and data type of the corresponding field of the row of data, which completely represents the row of data that needs to be transmitted in ETL. This allows ETL to automatically generate the corresponding row-level traceability information according to the task configuration or database information during the transmission process.
[0109] As an optional implementation, the traceability information corresponding to different target data is stored in a second database, including:
[0110] Based on the source data-related information items in the traceability information, determine the row, table, and database where the source data is located, and store them as different types of first entities in the graph database, and establish the association relationship between different types of first entities;
[0111] Based on the target data-related information items in the traceability information, determine the row, table, and database where the target data is located, and store them as different types of second entities in the graph database, and establish the association relationship between different types of second entities;
[0112] Based on the transformation process-related information items in the traceability information, establish the association relationship between entities of type row with transformation relationship;
[0113] The method also includes responding to the traceability information viewing instruction and displaying the traceability information corresponding to the target data in the form of a graph structure. The graph structure includes different types of first entities and their relationships, different types of second entities and their relationships, relationships between entities with transformation relationships of type row, and displaying relevant information items of the transformation process between the corresponding related entities.
[0114] See Figure 7 , Figure 7 This means that row A in table 1 of database 1 is one source data, and row 1 in table 2 of database 1 is another source data. The lower right corner of the diagram shows that the two source data, row A and row 1, are transformed to obtain the target data row A1, and the target data row A1 is stored in table 3 of database 2.
[0115] When storing row-level traceability information, both source and target data are primarily categorized into three main entity types: the database (database), the table (table), and the row (row). The database stores information at the data source level, such as connection information for a relational database, the bootstrap address of a Kafka database, or the storage address of a file. The table represents the next dimension below the data source type, such as a table within a database, the main body of a message queue, or a sheet in an Excel spreadsheet. However, the data source and table types can be broadly omitted and stored directly at the table entity level, depending on business requirements. The row is the lowest-level dimension, primarily storing the primary key field or SHA2 hash value of a data row to locate that row. The relationships between these entity types consist of three main types: the correspondence between data source and table (containing the table); the correspondence between table and row (containing the row); and the correspondence between rows (ETL). This ETL relationship is the primary relationship in this storage model, storing the mapping and transformation between two rows of data.
[0116] This application chooses to store the source information in a graph database instead of a traditional relational database. The source information of data is relational, typically exhibiting a chain-like or convergent structure. Using a graph database as the corresponding persistent data storage solution facilitates multi-level queries and display when tracing the source information of target data. Furthermore, it offers higher performance and speed when performing multi-level data queries, such as those involving source data A being transferred via ETL to obtain data B, and data B being transferred via ETL to obtain target data C.
[0117] Based on the same inventive concept, this application also provides a device for automatically generating traceability information in ETL, such as... Figure 8 As shown, the device includes:
[0118] The ETL task execution module 801 is used to receive ETL data transmission requests and execute pre-configured ETL tasks. The ETL task includes multiple sub-tasks, each of which is used to generate a corresponding target data containing complete information items.
[0119] The parsing module 802 is used to parse the source data acquisition instructions of each subtask to determine the source data related information items, parse the conversion instructions in each subtask to convert the source data to determine the conversion process related information items, and parse the target data generation instructions in each subtask to determine the target data related information items.
[0120] The traceability information generation module 803 is used to establish the relationship between the source data-related items, the conversion process-related items, and the target data-related items corresponding to the same sub-task, so as to obtain the traceability information of the target data corresponding to each sub-task;
[0121] The storage module 804 is used to store the target data generated by the execution of the ETL task into the first database, and to store the traceability information corresponding to different target data into the second database.
[0122] Optionally, the parsing module 802 is specifically used for:
[0123] Parse the source data acquisition instructions of each subtask to determine the data identifier of the source data and the first address information of the source data storage location;
[0124] Parse the conversion instructions that convert the source data in each subtask, and determine the second address information, conversion type, and parameter information for performing the conversion in the source data to locate the information item that has been converted.
[0125] Parse the target data generation instructions in each subtask to determine the data identifier and third address information of the storage location of the generated target data.
[0126] Optionally, the parsing module 802 is specifically used to perform at least one of the following:
[0127] Parse the source data acquisition instructions of each subtask to determine that the source data has a first primary key field that uniquely identifies the source data, and determine that the target data has a second primary key field that uniquely identifies the target data based on the parsing of the target data generation instructions in each subtask. If the field values of the first primary key field and the second primary key field are the same, determine that the address of the first primary key field is used as the first address information, and determine that the hash value obtained by performing SHA2 calculation on the identifiers of each field of the source data is the data identifier of the source data.
[0128] Parse the source data acquisition instructions of each subtask, determine that the source data has a first primary key field that uniquely identifies the source data, and determine that the target data does not have a second primary key field that uniquely identifies the target based on the parsing of the target data generation instructions in each subtask. Determine that the address of the first primary key field is used as the first address information, and determine that the hash value obtained by performing SHA2 calculation on the identifiers of each field of the source data is the data identifier of the source data.
[0129] Parse the source data acquisition instructions of each subtask to determine that the source data has a first primary key field that uniquely identifies the source data, and determine that the target data has a second primary key field that uniquely identifies the target data based on the parsing of the target data generation instructions in each subtask. If the field values of the first primary key field and the second primary key field are different, determine that the data type and content of the first primary key field are used as the first address information, and determine that the hash value obtained by performing SHA2 calculation on the identifiers of each field of the source data is the data identifier of the source data.
[0130] Parse the source data acquisition instructions for each subtask, determine that the source data does not have a first primary key field that uniquely identifies the source data, determine that the address of the column containing at least one information item in the source data is used as the first address information, and determine that the hash value obtained by performing SHA2 calculation on the identifiers of each field of the source data is the data identifier of the source data.
[0131] Optionally, the parsing module 802 is specifically used to perform at least one of the following steps:
[0132] Parse the target data generation instructions of each subtask, determine that the target data has a second primary key field that uniquely identifies the source data, determine that the address of the second primary key field is used as the third address information, and determine that the hash value obtained by performing SHA2 calculation on the identifiers of each field of the target data is the data identifier of the target data.
[0133] Parse the target data generation instructions for each subtask, determine that the target data does not have a second primary key field that uniquely identifies the target data, determine that the address of the column containing at least one information item in the target data is used as the third address information, and determine that the hash value obtained by performing SHA2 calculation on the identifier of each information item field of the target data is the data identifier of the source data.
[0134] Optionally, the traceability information generation module 803 is specifically used for:
[0135] When the same subtask transforms data from multiple sources, a record is output for each source data. The record includes source data-related information items, transformation process-related information items, and target data-related information items.
[0136] By merging records corresponding to multiple sources of data transformed from the same subtask, the source information of the target data corresponding to each subtask is obtained.
[0137] Optionally, each source data is stored in a corresponding table in the form of rows, and each target data is stored in a corresponding table in the form of rows. Each subtask is used to generate the target data for the corresponding row, and the source information of the target data for each subtask is also provided.
[0138] Optionally, storage module 804 is specifically used for:
[0139] Based on the source data-related information items in the traceability information, determine the row, table, and database where the source data is located, and store them as different types of first entities in the graph database, and establish the association relationship between different types of first entities;
[0140] Based on the target data-related information items in the traceability information, determine the row, table, and database where the target data is located, and store them as different types of second entities in the graph database, and establish the association relationship between different types of second entities;
[0141] Based on the transformation process-related information items in the traceability information, establish the association relationship between entities of type row with transformation relationship.
[0142] Optionally, the storage module 804 is further configured to:
[0143] In response to the instruction to view traceability information, the traceability information corresponding to the target data is displayed in the form of a graph structure. The graph structure includes different types of first entities and their relationships, different types of second entities and their relationships, relationships between entities with transformation relationships of type row, and displays relevant information items related to the transformation process between the corresponding related entities.
[0144] Having described the apparatus for automatically generating traceability information in the ETL of an exemplary embodiment of this application, we will now describe an electronic device according to another exemplary embodiment of this application.
[0145] Those skilled in the art will understand that various aspects of this application can be implemented as a system, method, or program product. Therefore, various aspects of this application can be specifically implemented in the following forms: a completely hardware implementation, a completely software implementation (including firmware, microcode, etc.), or a combination of hardware and software implementations, collectively referred to herein as a "circuit," "module," or "system."
[0146] In some possible implementations, the electronic device according to this application may include at least one processor and at least one memory. The memory stores program code that, when executed by the processor, causes the processor to perform the steps in automatically generating traceability information in the ETL of the various exemplary embodiments of this application described above.
[0147] The following reference Figure 9 This application describes an electronic device 130 according to this embodiment, namely, the device for automatically generating traceability information in the aforementioned ETL. Figure 9 The electronic device 130 shown is merely an example and should not impose any limitations on the functionality and scope of use of the embodiments of this application.
[0148] like Figure 9 As shown, the electronic device 130 is presented in the form of a general-purpose electronic device. The components of the electronic device 130 may include, but are not limited to: at least one processor 131, at least one memory 132, and a bus 133 connecting different system components (including memory 132 and processor 131).
[0149] Bus 133 represents one or more of several bus structures, including a memory bus or memory controller, peripheral bus, processor, or local bus using any of the various bus structures.
[0150] The memory 132 may include a readable medium in the form of volatile memory, such as random access memory (RAM) 1321 and / or cache memory 1322, and may further include read-only memory (ROM) 1323.
[0151] The memory 132 may also include a program / utility 1325 having a set (at least one) of program modules 1324, including but not limited to: an operating system, one or more application programs, other program modules, and program data, each or some combination of these examples may include an implementation of a network environment.
[0152] Electronic device 130 can also communicate with one or more external devices 134 (e.g., keyboard, pointing device, etc.), and with one or more devices that enable a user to interact with electronic device 130, and / or with any device that enables electronic device 130 to communicate with one or more other electronic devices (e.g., router, modem, etc.). This communication can be performed via input / output (I / O) interface 135. Furthermore, electronic device 130 can also communicate with one or more networks (e.g., local area network (LAN), wide area network (WAN), and / or public networks, such as the Internet) via network adapter 136. As shown, network adapter 136 communicates with other modules used in electronic device 130 via bus 133. It should be understood that, although not shown in the figures, other hardware and / or software modules can be used in conjunction with electronic device 130, including but not limited to: microcode, device drivers, redundant processors, external disk drive arrays, RAID systems, tape drives, and data backup storage systems.
[0153] In some possible implementations, various aspects of the method for automatically generating traceability information in an ETL provided in this application can also be implemented in the form of a program product, which includes program code. When the program product is run on a computer device, the program code is used to cause the computer device to perform the steps of the method for automatically generating traceability information in an ETL according to the various exemplary embodiments of this application described above.
[0154] The program product may employ any combination of one or more readable media. A readable medium may be a readable signal medium or a readable storage medium. A readable storage medium may be, for example,—but not limited to—an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination thereof. More specific examples (a non-exhaustive list) of readable storage media include: electrical connections having one or more wires, portable disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fiber, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof.
[0155] The monitoring program product of the embodiments of this application may employ a portable compact disc read-only memory (CD-ROM) and include program code, and may run on an electronic device. However, the program product of this application is not limited thereto. In this document, the readable storage medium may be any tangible medium that contains or stores a program that may be used by or in conjunction with an instruction execution system, apparatus, or device.
[0156] A readable signal medium may include a data signal propagated in baseband or as part of a carrier wave, carrying readable program code. This propagated data signal may take various forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination thereof. A readable signal medium may also be any readable medium other than a readable storage medium, capable of sending, propagating, or transmitting a program for use by or in conjunction with an instruction execution system, apparatus, or device.
[0157] The program code contained on the readable medium may be transmitted using any suitable medium, including but not limited to wireless, wired, optical fiber, RF, etc., or any suitable combination thereof.
[0158] Program code for performing the operations of this application can be written in any combination of one or more programming languages, including object-oriented programming languages such as Java and C++, and conventional procedural programming languages such as C or similar languages. The program code can execute entirely on the user's electronic device, partially on the user's device, as a standalone software package, partially on the user's electronic device and partially on a remote electronic device, or entirely on a remote electronic device or server. In cases involving remote electronic devices, the remote electronic device can be connected to the user's electronic device via any type of network—including a local area network (LAN) or a wide area network (WAN)—or can be connected to an external electronic device (e.g., via the Internet using an Internet service provider).
[0159] It should be noted that although several units or sub-units of the device have been mentioned in the detailed description above, this division is merely exemplary and not mandatory. In fact, according to embodiments of this application, the features and functions of two or more units described above can be embodied in one unit. Conversely, the features and functions of one unit described above can be further divided and embodied by multiple units.
[0160] Furthermore, although the operations of the method of this application are described in a specific order in the accompanying drawings, this does not require or imply that these operations must be performed in that specific order, or that all the operations shown must be performed to achieve the desired result. Additionally or alternatively, certain steps may be omitted, multiple steps may be combined into one step, and / or one step may be broken down into multiple steps.
[0161] Those skilled in the art will understand that embodiments of this application can be provided as methods, systems, or computer program products. Therefore, this application can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, this application can take the form of a computer program product embodied on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.
[0162] This application is described with reference to flowchart illustrations and block diagrams of methods, apparatus (systems), and computer program products according to embodiments of this application. It will be understood that each block and / or block in the flowchart illustrations and block diagrams, as well as combinations of blocks and processes in the flowchart illustrations and block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the process. Figure 1 One or more processes and boxes Figure 1 A device that provides the functions specified in one or more boxes.
[0163] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and boxes Figure 1 The function specified in one or more boxes.
[0164] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and boxes Figure 1 The steps of the function specified in one or more boxes.
[0165] Although preferred embodiments of this application have been described, those skilled in the art, upon learning the basic inventive concept, can make other changes and modifications to these embodiments. Therefore, the appended claims are intended to be interpreted as including the preferred embodiments as well as all changes and modifications falling within the scope of this application.
[0166] Obviously, those skilled in the art can make various modifications and variations to this application without departing from the spirit and scope of this application. Therefore, if such modifications and variations fall within the scope of the claims of this application and their equivalents, this application also intends to include such modifications and variations.
Claims
1. A method for automatically generating traceability information in ETL, characterized in that, The method includes: Receive a request for ETL data transmission and execute a pre-configured ETL task. The ETL task includes multiple subtasks, each of which generates a corresponding target data containing complete information items. The source data acquisition instructions of each subtask are parsed to determine the relevant information items of the source data; the transformation instructions in each subtask that transform the source data are parsed to determine the relevant information items of the transformation process; and the target data generation instructions in each subtask are parsed to determine the relevant information items of the target data. Each source data is stored in the corresponding table in the form of rows. Establish the association between source data-related items, transformation process-related items, and target data-related items corresponding to the same subtask to obtain the traceability information of the target data corresponding to each subtask. Each target data is stored in the corresponding table in the form of rows; where each subtask is used to generate the target data of the corresponding row, and the traceability information of the target data corresponding to each subtask is also provided. The target data generated by the ETL task is stored in a first database. Based on the source data-related information items in the traceability information, the row, table, and database of the source data are determined and stored as different types of first entities in the graph database, and the relationships between different types of first entities are established. Based on the target data-related information items in the traceability information, the row, table, and database of the target data are determined and stored as different types of second entities in the graph database, and the relationships between different types of second entities are established. Based on the transformation process-related information items in the traceability information, the relationships between entities of type row with transformation relationships are established. The library is used to store information about the data source level of the source or target data, the table is used to store the next dimension of the data source type of the source or target data, and the row stores the data information used to locate the row.
2. The method according to claim 1, characterized in that, The process involves parsing the source data acquisition instructions for each subtask to determine source data-related information items, parsing the transformation instructions for converting source data in each subtask to determine transformation process-related information items, and parsing the target data generation instructions in each subtask to determine target data-related information items, including: Parse the source data acquisition instructions of each subtask to determine the data identifier of the source data and the first address information of the source data storage location; Parse the conversion instructions that convert the source data in each subtask, and determine the second address information, conversion type, and parameter information for performing the conversion in the source data to locate the information item that has been converted. Parse the target data generation instructions in each subtask to determine the data identifier and third address information of the storage location of the generated target data.
3. The method according to claim 2, characterized in that, Parse the source data acquisition instructions for each subtask to determine the data identifier of the source data and the first address information of the source data storage location, including at least one of the following: Parse the source data acquisition instructions of each subtask to determine that the source data has a first primary key field that uniquely identifies the source data, and determine that the target data has a second primary key field that uniquely identifies the target data based on the parsing of the target data generation instructions in each subtask. If the field values of the first primary key field and the second primary key field are the same, determine that the address of the first primary key field is used as the first address information, and determine that the hash value obtained by performing SHA2 calculation on the identifiers of each field of the source data is the data identifier of the source data. Parse the source data acquisition instructions of each subtask, determine that the source data has a first primary key field that uniquely identifies the source data, and determine that the target data does not have a second primary key field that uniquely identifies the target based on the parsing of the target data generation instructions in each subtask. Determine that the address of the first primary key field is used as the first address information, and determine that the hash value obtained by performing SHA2 calculation on the identifiers of each field of the source data is the data identifier of the source data. Parse the source data acquisition instructions of each subtask to determine that the source data has a first primary key field that uniquely identifies the source data, and determine that the target data has a second primary key field that uniquely identifies the target data based on the parsing of the target data generation instructions in each subtask. If the field values of the first primary key field and the second primary key field are different, determine that the data type and content of the first primary key field are used as the first address information, and determine that the hash value obtained by performing SHA2 calculation on the identifiers of each field of the source data is the data identifier of the source data. Parse the source data acquisition instructions for each subtask, determine that the source data does not have a first primary key field that uniquely identifies the source data, determine that the address of the column containing at least one information item in the source data is used as the first address information, and determine that the hash value obtained by performing SHA2 calculation on the identifiers of each field of the source data is the data identifier of the source data.
4. The method according to claim 2, characterized in that, Parsing the target data generation instructions in each subtask to determine the data identifier and third address information of the storage location of the generated target data includes at least one of the following steps: Parse the target data generation instructions of each subtask, determine that the target data has a second primary key field that uniquely identifies the source data, determine that the address of the second primary key field is used as the third address information, and determine that the hash value obtained by performing SHA2 calculation on the identifiers of each field of the target data is the data identifier of the target data. Parse the target data generation instructions for each subtask, determine that the target data does not have a second primary key field that uniquely identifies the target data, determine that the address of the column containing at least one information item in the target data is used as the third address information, and determine that the hash value obtained by performing SHA2 calculation on the identifier of each information item field of the target data is the data identifier of the source data.
5. The method according to claim 1, characterized in that, Establish the relationships between source data-related items, transformation process-related items, and target data-related items corresponding to the same subtask, to obtain the source information of the target data for each subtask, including: When the same subtask transforms data from multiple sources, a record is output for each source data. The record includes source data-related information items, transformation process-related information items, and target data-related information items. By merging records corresponding to multiple sources of data transformed from the same subtask, the source information of the target data corresponding to each subtask is obtained.
6. The method according to claim 1, characterized in that, The method also includes: In response to the instruction to view traceability information, the traceability information corresponding to the target data is displayed in the form of a graph structure. The graph structure includes different types of first entities and their relationships, different types of second entities and their relationships, relationships between entities with transformation relationships of type row, and displays relevant information items related to the transformation process between the corresponding related entities.
7. A device for automatically generating traceability information in ETL, characterized in that, The device includes: The ETL task execution module is used to receive ETL data transmission requests and execute pre-configured ETL tasks. The ETL task includes multiple sub-tasks, each of which is used to generate a corresponding target data containing complete information items. The parsing module is used to parse the source data acquisition instructions of each subtask to determine the source data related information items, parse the transformation instructions in each subtask to transform the source data to determine the transformation process related information items, and parse the target data generation instructions in each subtask to determine the target data related information items. Each source data is stored in the corresponding table in the form of rows. The traceability information generation module is used to establish the relationship between the source data-related items, the conversion process-related items, and the target data-related items corresponding to the same subtask, so as to obtain the traceability information of the target data corresponding to each subtask. Each target data is stored in the corresponding table in the form of rows; each subtask is used to generate the target data of the corresponding row, and the traceability information of the target data corresponding to each subtask. The storage module is used to store the target data generated by the ETL task into the first database. Based on the source data-related information items in the traceability information, the row, table, and database of the source data are determined and stored as different types of first entities in the graph database, and the relationships between different types of first entities are established; based on the target data-related information items in the traceability information, the row, table, and database of the target data are determined and stored as different types of second entities in the graph database, and the relationships between different types of second entities are established; based on the conversion process-related information items in the traceability information, the relationships between entities of type row with conversion relationships are established. The library is used to store information about the data source level of the source or target data, the table is used to store the next dimension of the data source type of the source or target data, and the row stores the data information used to locate the row.
8. An electronic device, characterized in that, The method includes at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor to enable the at least one processor to perform the method as described in any one of claims 1-6.
9. A computer storage medium, characterized in that, The computer storage medium stores a computer program that enables the computer to perform the method as described in any one of claims 1-6.