Data lineage analysis and display method, device and electronic device based on ETL
By obtaining and analyzing the execution details of the data extraction and transformation task in real time, using the graph algorithm to draw a blood relationship diagram, solving the problem of untimely update of data blood relationships, real-time monitoring and visual display of data blood relationships, suitable for large data volume and Data analysis of complex relationships.
Patent Information
- Application Number
- CN202210762056.5
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2022-06-30
- Publication Date
- 2024-12-06
- Estimated Expiration
- 2042-06-30
AI Technical Summary
The existing data blood relationship is not updated in time, and it can only trace the blood relationship between database tables. The workload is huge and it is difficult to meet the data modification needs under large data volume and complex relationships.
By obtaining the task execution details of the data extraction and conversion task in real time, analyzing and obtaining data source information and data flow information, using a graph algorithm to draw a blood relationship diagram, displaying the blood relationship between data tables, including data inflow and outflow nodes and circulation directions, to achieve Real-time monitoring and analysis of data and blood ties.
Real-time update and visual display of data blood relationships is realized, which is stable, accurate and comprehensive, and supports data analysis under large data volume and complex relationships, without affecting the normal operation of data extraction and conversion tasks.
Smart Images

Figure CN115145988B_ABST
Abstract
Description
Technical Field
[0001] The present invention belongs to the field of computer technology, and specifically relates to a data lineage analysis display method, device and electronic equipment. Background Art
[0002] During the entire life cycle of data, data will be continuously processed to generate various intermediate data or data products. The relationship between these data is called data lineage or data lineage. For data obtained and generated in the business process, it is usually necessary to use Extraction, Transformation, and Loading (ETL) tools to process the original data in multiple steps and generate new data. In this process, many interrelated data tables will be generated. The link relationship between data tables is called data lineage. By identifying and displaying the path of data lineage, the flow logic and interrelationships of data can be quickly clarified, which is convenient for normalized maintenance.
[0003] With the rise of big data technology, the amount of data is getting bigger and bigger, and the relationship between data is getting more and more complicated. Modification of data often affects the whole system, so data lineage analysis is becoming more and more important. Existing data tracing usually involves testing the original database, which is a huge workload, the lineage relationship is not updated in time, and it can usually only trace the lineage relationship between database tables. Summary of the invention
[0004] The present invention provides a data lineage analysis and display method, device and electronic equipment to solve the problem of untimely update of existing data lineage relationships.
[0005] Based on the above-mentioned purpose, an embodiment of the present invention provides a data lineage analysis and display method, including: obtaining task execution details of data extraction and conversion tasks in real time, parsing and obtaining data lineage information including data source information and data flow information according to the task execution details and storing it in a database; obtaining information of any table to be analyzed, and searching the data lineage information of the table from the database according to the information of the table; applying a graph algorithm according to the data lineage information to obtain the data source information and the data flow information of each node with the table as the central node; drawing a lineage relationship diagram of the table according to the data source information and the data flow information of each node and displaying it, wherein the lineage relationship diagram at least includes data inflow nodes and data outflow nodes with the table as the central node, as well as the data flow direction.
[0006] Optionally, the real-time acquisition of task execution details of the data extraction and conversion task includes: real-time monitoring of ETL tool programs and logs to obtain the task execution status of the data extraction and conversion task executed by the ETL tool; obtaining the unique code and data storage location of the data extraction and conversion task; querying the database according to the unique code and the data storage location to obtain the task execution details of the data extraction and conversion task, wherein the task execution details include: the hierarchical relationship between databases, tables, and fields, and data operation information of extraction, conversion, and loading.
[0007] Optionally, the data lineage information including data source information and data conversion information is obtained based on the analysis of the task execution details and stored in a database, including: performing a full-link analysis and summary of the task execution details, restoring the complete configuration link of the data extraction and conversion task from the first step to the last step, and obtaining the data lineage information including the data source information and the data flow information; and storing the obtained data lineage information in the database.
[0008] Optionally, the data source information includes: a hierarchical relationship among databases, tables, fields, and files; and the data flow information includes: a data flow direction, a data flow volume, and a data operation type.
[0009] Optionally, storing the acquired data lineage information in the database includes: storing the data source information and the data flow information in the database according to the data operation type and the hierarchical relationship.
[0010] Optionally, applying a graph algorithm based on the data lineage information with the table as the central node to obtain the data source information and the data flow information of each node, includes: applying a graph algorithm with the table as the central node to traverse and search for the data source information and the data flow information of each node in the data lineage information, and the graph algorithm is one of a breadth-first algorithm and a depth-first algorithm.
[0011] Optionally, the blood relationship diagram of the table is drawn and displayed based on the data source information and the data flow information of each node, including: taking the table as the central node, the left side of the central node is the data inflow node of the central node, and the right side is the data outflow node of the central node, the arrow between the two nodes is the data flow direction, the connecting line information shows the field name of the data, and the blood relationship diagram of the table is drawn; the blood relationship diagram is visually displayed.
[0012] Based on the same inventive concept, an embodiment of the present invention also proposes a data lineage analysis and display device, including: a task parsing unit, used to obtain task execution details of data extraction and conversion tasks in real time, and obtain data lineage information including data source information and data flow information according to the task execution details and store them in a database; a lineage search unit, used to obtain information of any table to be analyzed, and search the data lineage information of the table from the database according to the information of the table; a lineage acquisition unit, used to apply a graph algorithm according to the data lineage information to obtain the data source information and the data flow information of each node with the table as the central node; a drawing and display unit, used to draw and display a lineage relationship diagram of the table according to the data source information and the data flow information of each node, wherein the lineage relationship diagram at least includes data inflow nodes and data outflow nodes with the table as the central node, and data flow direction.
[0013] Based on the same inventive concept, an embodiment of the present invention further proposes an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the aforementioned method when executing the program.
[0014] Based on the same inventive concept, an embodiment of the present invention further proposes a computer storage medium, in which at least one executable instruction is stored, and the executable instruction enables a processor to execute the aforementioned method.
[0015] The beneficial effects of the present invention are as follows: as can be seen from the above description, an embodiment of the present invention provides a data lineage analysis and display method, device and electronic device, the method comprising: obtaining task execution details of a data extraction and conversion task in real time, obtaining data lineage information including data source information and data flow information according to the task execution details and storing it in a database; obtaining information of any table to be analyzed, and searching the data lineage information of the table from the database according to the information of the table; applying a graph algorithm according to the data lineage information to obtain the data source information and the data flow information of each node with the table as the central node; drawing a lineage relationship diagram of the table according to the data source information and the data flow information of each node and displaying it, the lineage relationship diagram at least including a data inflow node and a data outflow node with the table as the central node, and a data flow direction, and monitoring and analyzing the operation of the data extraction and conversion task in real time by means of buried points, without affecting the normal operation of the data extraction and conversion task, and being stable, accurate, comprehensive and visual. BRIEF DESCRIPTION OF THE DRAWINGS
[0016] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings in the following description are only embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.
[0017] Figure 1 It is a flowchart of a data lineage analysis display method in an embodiment of the present invention;
[0018] Figure 2 A schematic diagram of a blood relationship diagram of a data blood relationship analysis and display method in an embodiment of the present invention;
[0019] Figure 3 It is a structural schematic diagram of a data lineage analysis and display device in an embodiment of the present invention;
[0020] Figure 4 Schematic diagram of an electronic device in an embodiment of the present invention. DETAILED DESCRIPTION
[0021] In order to make the objectives, technical solutions and advantages of the present disclosure more clearly understood, the present disclosure is further described in detail below in combination with specific embodiments and with reference to the accompanying drawings.
[0022] It should be noted that, unless otherwise defined, the technical terms or scientific terms used in the embodiments of the present invention should be understood by people with ordinary skills in the field to which the present disclosure belongs. The "first", "second" and similar words used in the embodiments of the present invention do not represent any order, quantity or importance, but are only used to distinguish different components. "Including" or "comprising" and similar words mean that the elements or objects appearing before the word cover the elements or objects listed after the word and their equivalents, without excluding other elements or objects. "Connect" or "connected" and similar words are not limited to physical or mechanical connections, but can include electrical connections, whether direct or indirect. "Up", "down", "left", "right" and the like are only used to indicate relative positional relationships. When the absolute position of the described object changes, the relative positional relationship may also change accordingly.
[0023] The embodiment of the present invention provides a data lineage analysis and display method. Figure 1 As shown, the data lineage analysis display method includes:
[0024] Step S11: acquiring task execution details of the data extraction and conversion task in real time, parsing and acquiring data lineage information including data source information and data flow information according to the task execution details, and storing the data in a database.
[0025] In an embodiment of the present invention, the data extraction and conversion task is an ETL task. Monitor the ETL tool program and log in real time to obtain the task execution status of the data extraction and conversion task executed by the ETL tool; obtain the unique code and data storage location of the data extraction and conversion task; query the database according to the unique code and the data storage location to obtain the task execution details of the data extraction and conversion task, and the task execution details include: the hierarchical relationship of databases, tables, and fields, and the data operation information of extraction, conversion, and loading. ETL tools include but are not limited to one of the tools such as informatica and kettle. By real-time monitoring of ETL tool programs, logs, etc., the task execution status of the ETL tool is obtained, and the unique code, data storage location, and other information of the data extraction and conversion task are obtained. By querying the database of the ETL tool, all detailed information of the data extraction and conversion task is obtained, including the hierarchical relationship of databases, tables, fields, and files, and data operation information of extraction, conversion, loading, etc.
[0026] After obtaining the task execution details of the data extraction and conversion task, the task execution details are analyzed and summarized in the entire link, the complete configuration link of the data extraction and conversion task from the first step to the last step is restored, and the data lineage information including the data source information and the data flow information is obtained; the obtained data lineage information is stored in the database.
[0027] The data source information includes: hierarchical relationships among databases, tables, fields, files, etc., and the data flow information includes: data flow direction, data flow volume, data operation type, etc. The data flow direction can be data inflow or data outflow, the data flow volume indicates the amount of data in circulation, and the data operation type can be data operations such as extraction, conversion, and loading. When storing data lineage information, the data source information and the data flow information are stored in the database according to the data operation type and the hierarchical relationship. The complete configuration link includes the data source information and data flow information of each node involved in the data extraction and conversion task from the first step to the last step. Parsing the complete configuration link can obtain the data lineage information including the data source information and the data flow information.
[0028] Step S12: Obtain information of any table to be analyzed, and search the database for data lineage information of the table based on the information of the table.
[0029] In an embodiment of the present invention, the information of any table to be analyzed includes but is not limited to: the database address, port, instance name, user name, password, table name, etc. of the table. When it is necessary to perform lineage analysis on any table, the data lineage information of the table recorded in the database is uniquely located based on the table information, and the data lineage information is passed to the graph algorithm for further processing. The data lineage information includes all relevant information of the table node, and all relevant information includes information about the table node, the data source information of the table node, and data flow information.
[0030] Step S13: applying a graph algorithm based on the data lineage information and taking the table as the central node to obtain the data source information and the data flow information of each node.
[0031] Optionally, a graph algorithm is applied to traverse and search the data source information and the data flow information of each node in the data lineage information with the table as the central node, and the graph algorithm is one of a breadth-first algorithm and a depth-first algorithm. Preferably, the breadth-first algorithm is used, that is, the data lineage information is traversed and searched through the breadth-first algorithm for each node that has data flow with the table node, and the data source information and data flow information of each node are obtained, specifically including all node information, connection information between nodes, data flow direction for data input or data output, etc.
[0032] Step S14: Draw and display a blood relationship diagram of the table based on the data source information and the data flow information of each node, wherein the blood relationship diagram at least includes data inflow nodes and data outflow nodes with the table as the central node, as well as the data flow direction.
[0033] Optionally, the table is used as the central node, the left side of the central node is the data inflow node of the central node, the right side is the data outflow node of the central node, the arrow between the two nodes is the data flow direction, the connection information shows the field name of the data, and the blood relationship diagram of the table is drawn; the blood relationship diagram is visualized. Figure 2 As shown, the central node is node SXT3, the data inflow nodes are nodes SXT1 and SXT2, and the data outflow node is node EXT. The data inflow node SXT1 includes data field names c1, c2, c3, and c4, the data inflow node SXT2 includes data field names c5 and c6, and the data outflow node EXT includes data field names c1, c2, c3, c4, c5, and c6.
[0034] The embodiment of the present invention performs data lineage analysis based on ETL for data lineage tracing and impact analysis, especially in big data scenarios with diverse data types, complex data relationships, and large data volumes. By monitoring ETL operations on data, the data flow relationships between databases, tables, and fields are obtained and visualized. By embedding points, the operation of ETL tasks can be monitored and analyzed in real time without affecting the normal operation of ETL tasks. It has the advantages of stability, accuracy, comprehensiveness, and visualization.
[0035] The data lineage analysis and display method of the embodiment of the present invention obtains the task execution details of the data extraction and conversion task in real time, parses and obtains the data lineage information including the data source information and the data flow information according to the task execution details and stores it in the database; obtains the information of any table to be analyzed, and searches the data lineage information of the table from the database according to the information of the table; applies the graph algorithm according to the data lineage information to obtain the data source information and the data flow information of each node with the table as the central node; draws the lineage relationship diagram of the table according to the data source information and the data flow information of each node and displays it, wherein the lineage relationship diagram at least includes the data inflow node and the data outflow node with the table as the central node, as well as the data flow direction, and can monitor and analyze the operation of the data extraction and conversion task in real time by means of point embedding, without affecting the normal operation of the data extraction and conversion task, and is stable, accurate, comprehensive and visual.
[0036] The above specific embodiments of the present invention are described. In some cases, the actions or steps recorded in the embodiments of the present invention can be performed in an order different from that in the embodiments and still achieve the desired results. In addition, the process depicted in the accompanying drawings does not necessarily require the specific order or continuous order shown to achieve the desired results. In some embodiments, multitasking and parallel processing are also possible or may be advantageous.
[0037] Based on the same concept, an embodiment of the present invention also provides a data lineage analysis display device. Applicable to an automatic driving device or an auxiliary driving device. Figure 3 As shown, the data lineage analysis and display device includes: a task analysis unit, a lineage search unit, a lineage acquisition unit and a drawing display unit.
[0038] A task parsing unit, used to obtain task execution details of data extraction and conversion tasks in real time, and to parse and obtain data lineage information including data source information and data flow information according to the task execution details and store them in a database;
[0039] A lineage search unit, used to obtain information of any table to be analyzed, and search the data lineage information of the table from the database according to the information of the table;
[0040] A lineage acquisition unit, used for acquiring the data source information and the data flow information of each node by applying a graph algorithm based on the data lineage information and taking the table as a central node;
[0041] A drawing display unit is used to draw and display a blood relationship diagram of the table based on the data source information and the data flow information of each node, wherein the blood relationship diagram at least includes data inflow nodes and data outflow nodes with the table as the central node, as well as the data flow direction.
[0042] For the convenience of description, the above device is described as various modules according to their functions. Of course, when implementing the embodiment of the present invention, the functions of each module can be implemented in the same or multiple software and / or hardware.
[0043] The device of the above embodiment is applied to the corresponding method in the above embodiment, and has the beneficial effects of the corresponding method embodiment, which will not be described in detail here.
[0044] Based on the same inventive concept, an embodiment of the present invention further provides an electronic device, which includes a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein when the processor executes the program, the method described in any one of the above embodiments is implemented.
[0045] An embodiment of the present invention provides a non-volatile computer storage medium, wherein the computer storage medium stores at least one executable instruction, and the computer executable instruction can execute the method described in any of the above embodiments.
[0046] Figure 4 A more specific schematic diagram of the hardware structure of an electronic device provided in this embodiment is shown, and the device may include: a processor 401, a memory 402, an input / output interface 403, a communication interface 404, and a bus 405. The processor 401, the memory 402, the input / output interface 403, and the communication interface 404 are connected to each other in communication within the device through the bus 405.
[0047] The processor 401 can be implemented by a general-purpose CPU (Central Processing Unit), a microprocessor, an application-specific integrated circuit (ASIC), or one or more integrated circuits, and is used to execute relevant programs to implement the technical solution provided by the method embodiment of the present invention.
[0048] The memory 402 can be implemented in the form of ROM (Read Only Memory), RAM (Random Access Memory), static storage device, dynamic storage device, etc. The memory 402 can store an operating system and other application programs. When the technical solution provided by the method embodiment of the present invention is implemented by software or firmware, the relevant program code is stored in the memory 402 and called and executed by the processor 401.
[0049] The input / output interface 403 is used to connect the input / output module to realize information input and output. The input / output module can be configured in the device as a component (not shown in the figure), or it can be externally connected to the device to provide corresponding functions. The input device may include a keyboard, a mouse, a touch screen, a microphone, various sensors, etc., and the output device may include a display, a speaker, a vibrator, an indicator light, etc.
[0050] The communication interface 404 is used to connect a communication module (not shown) to realize communication interaction between the device and other devices. The communication module can realize communication through a wired mode (such as USB, network cable, etc.) or a wireless mode (such as mobile network, WIFI, Bluetooth, etc.).
[0051] The bus 405 comprises a pathway for transmitting information between the various components of the device (eg, the processor 401 , the memory 402 , the input / output interface 403 , and the communication interface 404 ).
[0052] It should be noted that, although the above device only shows the processor 401, the memory 402, the input / output interface 403, the communication interface 404 and the bus 405, in the specific implementation process, the device may also include other components necessary for normal operation. In addition, it can be understood by those skilled in the art that the above device may also only include the components necessary for implementing the embodiment of the present invention, and does not necessarily include all the components shown in the figure.
[0053] Those skilled in the art should understand that the discussion of any of the above embodiments is merely illustrative and is not intended to imply that the scope of the present application is limited to these examples. Based on the concept of the present application, the technical features in the above embodiments or different embodiments may be combined, the steps may be implemented in any order, and there are many other variations of the different aspects of the present application as described above, which are not provided in detail for the sake of simplicity.
[0054] This application is intended to cover all such substitutions, modifications and variations that fall within the broad scope of the embodiments of the present invention. Therefore, any omissions, modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the embodiments of the present invention should be included in the scope of protection of this application.
Claims
1. A data lineage analysis and display method based on ETL, characterized in that: The method comprises: Obtaining task execution details of data extraction and conversion tasks in real time, parsing and obtaining data lineage information including data source information and data flow information according to the task execution details, and storing them in a database; Acquire information of any table to be analyzed, and search the data lineage information of the table from the database according to the information of the table; According to the data lineage information, a graph algorithm is applied to obtain the data source information and the data flow information of each node with the table as the central node; Draw a blood relationship diagram of the table according to the data source information and the data flow information of each node and display it, wherein the blood relationship diagram at least includes a data inflow node and a data outflow node with the table as a central node, as well as a data flow direction; The task execution details of the real-time acquisition of data extraction and conversion tasks include: Monitor ETL tool programs and logs in real time to obtain the task execution status of the data extraction and conversion tasks executed by the ETL tool; Obtain the unique code and data storage location of the data extraction and conversion task; Querying a database according to the unique code and the data storage location to obtain the task execution details of the data extraction and conversion task, wherein the task execution details include: the hierarchical relationship between databases, tables, and fields, and data operation information of extraction, conversion, and loading; The step of parsing and acquiring data lineage information including data source information and data conversion information according to the task execution details and storing the data lineage information in a database includes: Analyze and summarize the entire chain of task execution details, restore the complete configuration chain of the data extraction and conversion task from the first step to the last step, and obtain the data lineage information including the data source information and the data flow information; Storing the acquired data bloodline information in the database; The method of applying a graph algorithm based on the data lineage information to obtain the data source information and the data flow information of each node with the table as the central node includes: An application graph algorithm uses the table as a central node to traverse and search the data source information and the data flow information of each node in the data lineage information. The graph algorithm is one of a breadth-first algorithm and a depth-first algorithm.
2. The method according to claim 1, characterized in that: The data source information includes: the hierarchical relationship of databases, tables, fields, and files; the data flow information includes: data flow direction, data flow volume, and data operation type.
3. The method according to claim 2, characterized in that: The step of storing the acquired data lineage information in the database comprises: The data source information and the data flow information are stored in the database according to the data operation type and the hierarchical relationship.
4. The method according to claim 1, characterized in that: The step of drawing and displaying the blood relationship diagram of the table according to the data source information and the data flow information of each node includes: Take the table as the central node, the left side of the central node is the data inflow node of the central node, the right side is the data outflow node of the central node, the arrow between the two nodes is the data flow direction, the connection information shows the field name of the data, and draw the blood relationship diagram of the table; The blood relationship diagram is visually displayed.
5. A data lineage analysis and display device based on the method according to any one of claims 1 to 4, characterized in that: The device comprises: A task parsing unit, used to obtain task execution details of data extraction and conversion tasks in real time, and to parse and obtain data lineage information including data source information and data flow information according to the task execution details and store them in a database; A lineage search unit, used to obtain information of any table to be analyzed, and search the data lineage information of the table from the database according to the information of the table; A lineage acquisition unit, used for acquiring the data source information and the data flow information of each node by applying a graph algorithm based on the data lineage information and taking the table as a central node; A drawing display unit is used to draw and display a blood relationship diagram of the table based on the data source information and the data flow information of each node, wherein the blood relationship diagram at least includes data inflow nodes and data outflow nodes with the table as the central node, as well as the data flow direction.
6. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein: When the processor executes the program, the method according to any one of claims 1 to 4 is implemented.
7. A computer storage medium, characterized in that: The storage medium stores at least one executable instruction, and the executable instruction enables the processor to execute the method as described in any one of claims 1-4.
Citation Information
Patent Citations
Data blood relationship analysis method, device and equipment and storage medium
CN112434046A
Data-based blood relationship analysis method, apparatus, and device and computer-readable storage medium
WO2021218021A1
