Data blood relationship analysis method and device, computer equipment and readable storage medium

Abstract

The invention relates to a data blood relationship analysis method and device, computer equipment and a readable storage medium. The method comprises the steps of executing SQL query, obtaining storage process data, converting the storage process data into a standardized execution plan, extracting a table name of a scanning operator and a table name of an insertion operator from the execution plan, constructing a table data reading file based on the table name of the scanning operator, and constructing a table data writing file based on the table name of the insertion operator. The table data reading file comprises an execution plan name and records a data table required by an execution plan, the table data writing file comprises the execution plan name and a data table recorded with execution plan writing data, and based on the execution plan name, performing association operation on the table data reading file and the table data writing file; and obtaining the data blood relationship in the storage process. By adopting the method, the data analysis efficiency can be effectively improved.

Description

Technical Field

[0001] The present application relates to the field of data analysis technology, and in particular to a data lineage analysis method, apparatus, computer equipment, computer-readable storage medium, and computer program product. Background Art

[0002] In the specific practice of business intelligence, it is often necessary to integrate and summarize the data generated by the actual business according to business logic, and calculate various indicators to support the business department's needs for data analysis.

[0003] However, the complex relationship between a large number of data tables in the database increases the difficulty of data analysis. For example, table A plus table B generates table C, and table C plus table D plus table E generates table F. This can easily cause inconvenience when searching for data anomalies. For example, when the data in table F is abnormal, the problem may be in table A, but in the actual search process, you may need to first check the generated SQL (Structured Query Language) logic code of table F, and then check the generated SQL logic code of table C to locate the problem. This greatly affects the efficiency of data analysis. Summary of the invention

[0004] Based on this, it is necessary to provide an efficient data lineage analysis method, device, computer equipment, computer-readable storage medium and computer program product to address the above technical problems.

[0005] In a first aspect, the present application provides a data lineage analysis method, comprising:

[0006] Execute SQL query to obtain stored procedure data;

[0007] Converting the stored procedure data into a standardized execution plan;

[0008] Extracting a table name of a scan operator and a table name of an insert operator from the execution plan;

[0009] A table data reading file is constructed based on the table name of the scan operator, and a table data writing file is constructed based on the table name of the insert operator, wherein the table data reading file includes an execution plan name and records a data table required for the execution plan, and the table data writing file includes an execution plan name and records a data table for writing data to the execution plan;

[0010] Based on the execution plan name, an association operation is performed on the table data reading file and the table data writing file to obtain the data lineage relationship of the stored procedure.

[0011] In one embodiment, converting the stored process data into a standardized execution plan includes:

[0012] Converting the stored process data from a character string into first formatted structure data;

[0013] Performing query rewriting on the first formatted structure data to obtain optimized second formatted structure data;

[0014] An execution plan of the stored procedure is determined according to the second formatted structure data and preset database statistical information.

[0015] In one embodiment, converting the stored process data from a character string into first formatted structure data includes:

[0016] Performing lexical analysis on the stored procedure data to decompose the stored procedure into multiple tokens;

[0017] According to preset SQL grammar rules, the multiple tags are combined into first formatted structure data.

[0018] In one embodiment, before performing query rewriting on the first formatted structure data, the method includes:

[0019] Performing semantic analysis on the first formatted structure data to verify the logical correctness and feasibility of the first formatted structure data;

[0020] The query rewriting of the first formatted structure data includes:

[0021] When the first formatted structure data passes the verification of logical correctness and feasibility, query rewriting is performed on the first formatted structure data.

[0022] In one embodiment, determining the execution plan of the stored procedure according to the second formatted structure data and preset database statistical information includes:

[0023] Based on preset database statistical information, evaluating different versions of execution plans constructed based on the second formatted structure data to obtain an execution plan evaluation result;

[0024] Filter out the target execution plan with the best execution plan evaluation result from different versions of execution plans;

[0025] The target execution plan is determined as the execution plan of the stored procedure.

[0026] In one embodiment, after obtaining the data lineage relationship of the stored process, the method further includes:

[0027] The data lineage relationship is connected multiple times to obtain a multi-layer data lineage relationship.

[0028] In one embodiment, extracting the table name of the scan operator and the table name of the insert operator from the execution plan includes:

[0029] The table name of the scan operator and the table name of the insert operator are extracted from the execution plan through a regular expression.

[0030] In a second aspect, the present application also provides a data lineage analysis device. The device comprises:

[0031] Data acquisition module, used to execute SQL queries and obtain stored procedure data;

[0032] A data conversion module, used to obtain stored process data and convert the stored process data into a standardized execution plan;

[0033] A data extraction module, used to extract the table name of the scan operator and the table name of the insert operator from the execution plan;

[0034] A data construction module, used to construct a table data reading file based on the table name of the scan operator, and to construct a table data writing file based on the table name of the insert operator, wherein the table data reading file includes an execution plan name and records a data table required for the execution plan, and the table data writing file includes an execution plan name and records a data table for writing data to the execution plan;

[0035] The data analysis module is used to perform an association operation on the table data reading file and the table data writing file based on the execution plan name to obtain the data lineage relationship of the stored procedure.

[0036] In a third aspect, the present application further provides a computer device, including a memory and a processor, wherein the memory stores a computer program, and the processor implements the steps in any one of the above-mentioned data lineage analysis method embodiments when executing the computer program.

[0037] In a fourth aspect, the present application further provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps in any one of the above-mentioned data lineage analysis method embodiments.

[0038] In a fifth aspect, the present application also provides a computer program product, including a computer program, which, when executed by a processor, implements the steps in any one of the above-mentioned data lineage analysis method embodiments.

[0039] The above-mentioned data lineage analysis method, device, computer equipment, computer-readable storage medium and computer program product can improve the consistency and accuracy of the data analysis process by converting the stored process data into a unified and standardized execution plan, reduce the difficulty of data analysis caused by different developer code writing habits, and help improve data analysis efficiency. By accurately extracting the table names of the scan operator and the insert operator from the execution plan, constructing table data reading files and table data writing files, each file contains the execution plan name and the relevant table name, clearly records the source and destination of the data, provides complete data lineage information, and facilitates tracing the flow path of the data. Finally, by associating the table data reading file and the table data writing file, the data lineage relationship of the stored process data can be obtained, and the flow of data in the system can be fully displayed, effectively improving the efficiency of data analysis. Furthermore, by analyzing the data lineage relationship, potential performance bottlenecks can be discovered, the storage process and database design can be optimized, and when problems occur in the system, the source and destination of the data can be quickly located based on the data lineage relationship, accelerating the troubleshooting and repair speed. BRIEF DESCRIPTION OF THE DRAWINGS

[0040] In order to more clearly illustrate the technical solutions in the embodiments of the present application or related technologies, the drawings required for use in the embodiments of the present application or related technical descriptions will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other related drawings can be obtained based on these drawings without paying creative work.

[0041] Figure 1 is an application environment diagram of a data lineage analysis method in an embodiment;

[0042] Figure 2 A schematic diagram of a process of a data lineage analysis method in one embodiment;

[0043] Figure 3 A schematic diagram of a flow chart for determining the steps of executing a plan in one embodiment;

[0044] Figure 4 A flowchart of determining the execution plan step in another embodiment;

[0045] Figure 5 A flowchart of determining the steps of executing a plan in another embodiment;

[0046] Figure 6 A flowchart of a method for selecting an execution plan based on database statistical information in one embodiment;

[0047] Figure 7 A schematic diagram of a flow chart of a data lineage analysis method in another embodiment;

[0048] Figure 8 is a structural block diagram of a data lineage analysis device in one embodiment;

[0049] Fig. 9 FIG. 4 is a diagram showing the internal structure of a computer device in one embodiment. DETAILED DESCRIPTION

[0050] In order to make the purpose, technical solution and advantages of the present application more clearly understood, the present application is further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present application and are not used to limit the present application.

[0051] The data lineage analysis method provided in the embodiment of the present application can be applied to Figure 1 In the application environment shown. Among them, the terminal 102 communicates with the server 104 through the network. The data storage system can store the data that the server 104 needs to process. The data storage system can be integrated on the server 104, or it can be placed on the cloud or other network servers. The server 104 has the authority to access the database and configures the database connection parameters (such as host address, port, user name, password, etc.).

[0052] Specifically, the operator may send a data analysis instruction to the server 104 through the terminal 102, and the server 104 executes an SQL query, reads the stored stored process data from the database, and converts the stored process data into a standardized execution plan. Subsequently, the table name of the scan operator and the table name of the insert operator are extracted from the execution plan, and a table data read file is constructed based on the table name of the scan operator, and a table data write file is constructed based on the table name of the insert operator, wherein the table data read file includes the execution plan name and records the data table required for the execution plan, and the table data write file includes the execution plan name and records the data table of the execution plan write data. Finally, based on the execution plan name, the table data read file and the table data write file are associated to obtain the data lineage relationship of the stored process.

[0053] The terminal 102 may be, but is not limited to, various personal computers, laptops, smart phones, tablet computers, IoT devices, and portable wearable devices. The IoT devices may be smart speakers, smart TVs, smart air conditioners, smart car devices, projection devices, etc. Portable wearable devices may be smart watches, smart bracelets, head-mounted devices, etc. Head-mounted devices may be virtual reality (VR) devices, augmented reality (AR) devices, smart glasses, etc. The server 104 may be an independent physical server, a server cluster or distributed system consisting of multiple physical servers, or a cloud server that provides cloud computing services.

[0054] In an exemplary embodiment, Figure 2 As shown, a data lineage analysis method is provided, which is applied to Figure 1 The example in is used to illustrate, including the following steps S200 to S800. Among them:

[0055] S200, executing SQL query to obtain stored procedure data.

[0056] Stored procedure data can also be called stored procedures, which specifically refers to pre-compiled SQL code blocks stored in the database. These code blocks can accept parameters and return results. The main features of stored procedures include: Pre-compilation: The stored procedure is compiled and stored in the database when it is created. When executed, only the compiled code needs to be called, which improves execution efficiency. Parameterization: It can accept input parameters and perform different operations based on these parameters. Transaction control: Transaction control statements can be included in the stored procedure to ensure data consistency and integrity. Modularity: Complex business logic can be encapsulated in the stored procedure to improve the maintainability and reusability of the code.

[0057] In specific implementation, the server can use Python's database connection library (such as psycopg2, pyodbc, etc.) to connect to the target database, and then execute SQL queries to obtain the names and related information (stored procedure data) of all stored procedures from the target database.

[0058] S400, converting the stored procedure data into a standardized execution plan.

[0059] An execution plan is an internal representation generated by a database management system when executing a SQL query. It describes how the database engine will specifically execute the query to obtain the required data. An execution plan is a tree structure consisting of multiple execution operators, each of which represents a specific database operation, such as table scan, index lookup, join operation, etc. An execution plan can optimize query performance. The database can minimize the execution time and resource consumption of queries. In addition, database administrators can understand the actual execution steps of a query by viewing the execution plan, so as to perform targeted optimization.

[0060] In specific implementation, after obtaining the stored procedure data, the server can convert each obtained stored procedure into a standardized and unified execution plan by executing relevant commands to obtain the execution plan of the stored procedure.

[0061] S600: Extract the table name of the scan operator and the table name of the insert operator from the execution plan.

[0062] An execution operator is an operator used to read table data in an execution plan, such as Seq Scan, CStore Scan, etc. An insert operator is an operator used to write table data in an execution plan, such as Vector Insert, etc. A table name is the name of a table in the database.

[0063] In specific implementation, after the server converts the stored procedure data into a standardized execution plan, the table name of the scan operator and the table name of the insert algorithm can be extracted from the text of the execution plan by keyword matching. Furthermore, the extracted table names can be stored in a dictionary or data structure for subsequent processing.

[0064] S800, construct a table data read file based on the table name of the scan operator, and construct a table data write file based on the table name of the insert operator. The table data read file includes the execution plan name and records the data table required by the execution plan. The table data write file includes the execution plan name and records the data table where the execution plan writes data.

[0065] Table data read files (also called from files) are files used to record which tables the execution plan (stored procedure) reads data from, including the execution plan name and the corresponding table name. Table data write files are files used to record which tables the execution plan (stored procedure) writes data to, including the execution plan name and the corresponding table name.

[0066] In specific implementation, the server can save the extracted table name of the scan operator as a from-Excel file, where the from file has two columns, the first column is the execution plan name (stored procedure name), and the second column is which data tables in the database need to read data during the execution plan processing. The extracted table name of the insert operator is saved as a to-Excel file, where the to file has two columns, the first column is the execution plan name (stored procedure name), and the second column is which tables in the database need to write data during the execution plan processing.

[0067] S900, based on the execution plan name, an association operation is performed on the table data reading file and the table data writing file to obtain the data lineage relationship of the stored procedure.

[0068] Data lineage refers to the description of the flow relationship between data in different storage processes, including the source and destination of the data.

[0069] In specific implementation, after the server builds the table data read file and the table data write file, since both the table data read file and the table data write file contain the execution plan name, the execution plan name can be used as the association key to perform association operations on the table data read file and the table data write file, that is, merge the records in the data source file and the data destination file, and save the merged data as a new data lineage file, which is usually an Excel file that records the data source and destination of each stored procedure.

[0070] In the above data lineage analysis method, by converting the stored process data into a unified and standardized execution plan, the consistency and accuracy of the data analysis process can be improved, the difficulty of data analysis caused by different developer code writing habits is reduced, and the efficiency of data analysis is improved. By accurately extracting the table names of the scan operator and the insert operator from the execution plan, the table data reading file and the table data writing file are constructed. Each file contains the execution plan name and the relevant table name, clearly records the source and destination of the data, provides complete data lineage information, and facilitates tracing the flow path of the data. Finally, by associating the table data reading file and the table data writing file, the data lineage relationship of the stored process data can be obtained, and the flow of data in the system can be fully displayed, which effectively improves the efficiency of data analysis. Furthermore, by analyzing the data lineage relationship, potential performance bottlenecks can be discovered, the storage process and database design can be optimized, and when problems occur in the system, the source and destination of the data can be quickly located based on the data lineage relationship, which speeds up troubleshooting and repair.

[0071] In an exemplary embodiment, Figure 3 As shown, S400, including:

[0072] S420: Convert the stored process data from a character string into first formatted structure data.

[0073] S440: perform query rewriting on the first formatted structure data to obtain optimized second formatted structure data.

[0074] S460: Determine an execution plan of the stored procedure according to the second formatted structure data and preset database statistical information.

[0075] The first formatted structure data is the conversion of the stored procedure in string form into a structured representation, usually an Abstract Syntax Tree (AST). Query rewriting refers to the optimization of SQL queries to improve execution efficiency. Common optimization methods include subquery expansion, view merging, predicate pushdown, etc. The second formatted structure is the AST representation after query rewriting optimization. Database statistics include but are not limited to table size, index distribution, and unique values ​​of columns, which are used to evaluate different execution plans.

[0076] In actual applications, the original SQL code block of the stored procedure exists in the form of a string. Therefore, after obtaining all the stored procedure data, the server can use the SQL parsing library to convert the SQL code of the stored procedure from a string form to an AST (first formatted structure data). Subsequently, the AST of the stored procedure can be traversed to identify the parts that can be optimized, and the AST can be modified according to the preset optimization rules to generate an optimized AST (second formatted structure data). Next, the preset database statistics are obtained from the database, and the execution plan is evaluated based on the second formatted structure data and the preset database statistics to determine the optimal execution plan for the stored procedure.

[0077] In this embodiment, by converting the SQL code of the stored procedure into standardized formatted structure data, the consistency and accuracy of the parsing process are ensured, and by rewriting the query on the first formatted structure data, an optimized second formatted structure data with a clear structure is obtained, which can generate a more efficient SQL query statement, significantly improve the query execution efficiency, and reduce resource consumption.

[0078] There are many ways to format stored process data. Figure 4 As shown, in some embodiments, S420 includes:

[0079] S422, performing lexical analysis on the stored procedure data, and decomposing the stored procedure into multiple tokens.

[0080] S424: Combine multiple tags into first formatted structure data according to preset SQL grammar rules.

[0081] SQL syntax rules define the structure and syntax of the SQL language and are used to guide how to combine tokens into valid SQL statements.

[0082] In specific implementation, the conversion of stored procedure data from a string form to an abstract syntax tree can be: lexical analysis is performed on the stored procedure data, and the stored procedure data is decomposed into a series of tokens, such as keywords, identifiers, constants, etc. These tokens are the basic components of SQL statements. Subsequently, the tokens generated by the lexical analysis are traversed, and the tokens are combined into a tree structure, namely AST, according to the SQL syntax rules. In this process, the syntax of the SQL statement is checked for correctness, such as whether brackets are missing or keywords are misspelled.

[0083] In this embodiment, the SQL code of the stored procedure is automatically decomposed into tokens through the lexical analysis library, which reduces the tediousness and error rate of manual operations. In addition, the basic component units of the SQL statement can be more accurately identified through the lexical analysis.

[0084] like Figure 5 As shown, in some embodiments, before S440, the method includes:

[0085] S430: Perform semantic analysis on the first formatted structure data to verify the logical correctness and feasibility of the first formatted structure data.

[0086] S440 includes: S442, when the first formatted structure data passes the verification of logical correctness and feasibility, query rewriting the first formatted structure data.

[0087] In this embodiment, before query rewriting the first formatted structure data, in order to obtain better second formatted structure data, the logical correctness and feasibility of the first formatted structure data may be verified. Specifically, semantic analysis may be performed on the first formatted structure data to verify the logical correctness and feasibility of the first formatted structure data. The verification of logical correctness and feasibility mainly includes verifying the existence of objects in SQL statements, authority checks, and data type checks.

[0088] Among them, object existence verification includes verifying whether the database objects such as tables, views, and columns referenced in the SQL statement exist. Permission checking refers to verifying whether the user has the permission to execute the SQL statement, such as whether he has the permission to query a table or insert data. Data type checking verifies whether the data types in the SQL statement are consistent, such as whether the data types in the comparison operation are compatible.

[0089] If the first formatted structure data passes the verification of logical correctness and feasibility, query rewriting is performed on the first formatted structure data to optimize the SQL query and obtain the optimized second formatted structure data. If the first formatted structure data does not pass the verification of logical correctness and feasibility, the first formatted structure data is corrected and adjusted until the corrected first formatted structure data passes the verification of logical correctness and feasibility.

[0090] In this embodiment, by performing semantic analysis on the first formatted structure data to verify its logical correctness and feasibility, and then performing query rewrite optimization, a better execution plan can be further obtained, effectively improving the efficiency of data management and analysis.

[0091] like Figure 6 As shown, in some embodiments, S460 includes:

[0092] S462, based on preset database statistical information, evaluating different versions of execution plans constructed based on the second formatted structure data to obtain an execution plan evaluation result.

[0093] S464, selecting a target execution plan with the best execution plan evaluation result from the different versions of execution plans.

[0094] S466: Determine the target execution plan as the execution plan of the stored procedure.

[0095] In specific implementation, the generation of the execution plan for each stored procedure may be to first generate multiple different versions of the execution plan based on the optimized second formatted structure data, and then evaluate the cost of each version of the execution plan based on database statistical information, including table size, index distribution, column unique values, and other information. Specifically, the cost of the execution plan includes calculating the cost of a full table scan, the cost of an index scan, the cost of an insert operation, and the like. Finally, the cost of each version of the execution plan is comprehensively calculated, and the execution plan with the lowest cost is selected from multiple different versions of the execution plan as the execution plan of the stored procedure for actually executing the stored procedure.

[0096] In this embodiment, by using preset database statistical information to evaluate different versions of execution plans, the accuracy and reliability of the evaluation results can be improved, and then the optimal execution plan can be determined based on the reliable evaluation results, thereby improving execution efficiency.

[0097] like Figure 7 As shown, in some embodiments, after S900, the method further includes: S920, performing multiple connections on the data lineage relationship to obtain a multi-layer data lineage relationship.

[0098] In practical applications, after obtaining the data lineage relationship and storing the data lineage relationship in an Excel file, the data lineage relationship file can be read, parsed, and multiple join operations can be performed on the data lineage relationship file to generate a multi-layer data lineage relationship by merging all layers, deleting duplicate columns, etc. Subsequently, the generated multi-layer data lineage relationship is saved as a new Excel file.

[0099] In this embodiment, multiple join operations are performed to generate multi-layer data lineage relationships, which can more comprehensively display the flow of data between different storage processes. In addition, multi-layer data lineage relationships help to deeply analyze the source and destination of data and discover potential data dependencies.

[0100] like Figure 7 As shown, in some embodiments, S600 includes: S620, extracting the table name of the scan operator and the table name of the insert operator from the execution plan through a regular expression.

[0101] A regular expression is a pattern description tool used to match strings. It consists of a series of characters and special symbols and can be used to search, edit or manage text.

[0102] In actual applications, developers can pre-write regular expressions to match the table names of scan operators and insert operators. For example, the regular expression pattern for extracting the table name of the scan operator can be: scan_pattern=r'(Seq Scan|Index Scan)on(\w+)', and the regular expression pattern for extracting the table name of the insert operator can be: insert_pattern=r'(Insert|Batch Insert)on(\w+)'.

[0103] In specific implementation, after obtaining the execution plan of the stored procedure, use a pre-written regular expression to extract the table name of the scan operator and the table name of the insert operator from the execution plan, and then save the extracted table names into a suitable structure such as an Excel file.

[0104] In this embodiment, by using regular expressions to extract the surfaces of scan operators and insert operators from the execution plan, the required table names can be matched simply and efficiently, and the regular expression pattern can be adjusted according to the specific execution plan format to adapt to different scenarios.

[0105] In order to make a clearer description of the data lineage analysis method provided by the present application, a specific embodiment is described below, and the specific embodiment includes the following steps:

[0106] S1, execute SQL query to obtain stored procedure data.

[0107] S2, performing lexical analysis on the stored procedure data, decomposing the stored procedure into multiple tags, and combining the multiple tags into first formatted structure data according to preset SQL grammar rules.

[0108] S3, performing semantic analysis on the first formatted structure data to verify the logical correctness and feasibility of the first formatted structure data.

[0109] S4, when the first formatted structure data passes the verification of logical correctness and feasibility, query rewrite the first formatted structure data to obtain optimized second formatted structure data.

[0110] S5, based on the preset database statistical information, evaluating different versions of the execution plan constructed based on the second formatted structure data to obtain an execution plan evaluation result.

[0111] S6, selecting a target execution plan with the best execution plan evaluation result from the different versions of execution plans, and determining the target execution plan as the execution plan of the stored procedure.

[0112] S7, extract the table name of the scan operator and the table name of the insert operator from the execution plan.

[0113] S8, construct a table data read file based on the table name of the scan operator, and construct a table data write file based on the table name of the insert operator. The table data read file includes the execution plan name and records the data table required by the execution plan. The table data write file includes the execution plan name and records the data table where the execution plan writes data.

[0114] S9, based on the execution plan name, the table data reading file and the table data writing file are associated to obtain the data lineage relationship of the stored procedure.

[0115] S10, multiple connections are made to the data lineage relationship to obtain a multi-layer data lineage relationship.

[0116] It should be understood that, although the various steps in the flowcharts involved in the above-mentioned embodiments are displayed in sequence according to the indication of the arrows, these steps are not necessarily executed in sequence according to the order indicated by the arrows. Unless there is a clear explanation in this article, the execution of these steps does not have a strict order restriction, and these steps can be executed in other orders. Moreover, at least a part of the steps in the flowcharts involved in the above-mentioned embodiments can include multiple steps or multiple stages, and these steps or stages are not necessarily executed at the same time, but can be executed at different times, and the execution order of these steps or stages is not necessarily to be carried out in sequence, but can be executed in turn or alternately with other steps or at least a part of the steps or stages in other steps.

[0117] Based on the same inventive concept, the embodiment of the present application also provides a data lineage analysis device for implementing the data lineage analysis method involved above. The implementation scheme for solving the problem provided by the device is similar to the implementation scheme recorded in the above method, so the specific limitations in one or more data lineage analysis device embodiments provided below can refer to the limitations of the data lineage analysis method above, and will not be repeated here.

[0118] In an exemplary embodiment, Figure 8 As shown, a data lineage analysis device 800 is provided, comprising: a data acquisition module 810, a data conversion module 820, a data extraction module 830, a data construction module 840 and a data analysis module 850, wherein:

[0119] The data acquisition module 810 is used to execute SQL query to acquire stored procedure data.

[0120] The data conversion module 820 is used to obtain the stored process data and convert the stored process data into a standardized execution plan.

[0121] The data extraction module 830 is used to extract the table name of the scan operator and the table name of the insert operator from the execution plan.

[0122] The data construction module 840 is used to construct a table data reading file based on the table name of the scan operator and to construct a table data writing file based on the table name of the insert operator. The table data reading file includes the execution plan name and records the data table required for the execution plan. The table data writing file includes the execution plan name and records the data table where the execution plan writes data.

[0123] The data analysis module 850 is used to perform association operations on the table data reading file and the table data writing file based on the execution plan name to obtain the data lineage relationship of the stored procedure.

[0124] The above-mentioned data lineage analysis device can improve the consistency and accuracy of the data analysis process by converting the stored process data into a unified and standardized execution plan, and reduce the difficulty of data analysis caused by different developer code writing habits. By accurately extracting the table names of the scan operator and the insert operator from the execution plan, the table data reading file and the table data writing file are constructed. Each file contains the execution plan name and the relevant table name, clearly records the source and destination of the data, provides complete data lineage information, and facilitates tracing the flow path of the data. Finally, by associating the table data reading file and the table data writing file, the data lineage relationship of the stored process data can be obtained, and the flow of data in the system can be fully displayed. Furthermore, by analyzing the data lineage relationship, potential performance bottlenecks can be discovered, the storage process and database design can be optimized, and when problems occur in the system, the source and destination of the data can be quickly located based on the data lineage relationship, thereby accelerating the troubleshooting and repair speed.

[0125] In some embodiments, the data conversion module 820 is also used to convert the stored procedure data from a character string into first formatted structure data, perform query rewrite on the first formatted structure data to obtain optimized second formatted structure data, and determine the execution plan of the stored procedure based on the second formatted structure data and preset database statistical information.

[0126] In some embodiments, the data conversion module 820 is further used to perform lexical analysis on the stored procedure data, decompose the stored procedure into multiple tokens, and combine the multiple tokens into the first formatted structure data according to preset SQL grammar rules.

[0127] In one embodiment, the data conversion module 820 is further used to perform semantic analysis on the first formatted structure data, verify the logical correctness and feasibility of the first formatted structure data, and perform query rewriting on the first formatted structure data when the first formatted structure data passes the verification of logical correctness and feasibility.

[0128] In one embodiment, the data conversion module 820 is also used to evaluate different versions of execution plans constructed based on the second formatted structure data based on preset database statistical information, obtain execution plan evaluation results, filter out a target execution plan with the best execution plan evaluation results from the different versions of execution plans, and determine the target execution plan as the execution plan of the stored procedure.

[0129] In one embodiment, the data analysis module 850 is also used to perform multiple connections on the data lineage relationship to obtain a multi-layer data lineage relationship.

[0130] In one embodiment, the data extraction module 830 is further used to extract the table name of the scan operator and the table name of the insert operator from the execution plan through a regular expression.

[0131] Each module in the above-mentioned data lineage analysis device can be implemented in whole or in part by software, hardware and their combination. Each of the above-mentioned modules can be embedded in or independent of the processor in the computer device in the form of hardware, or can be stored in the memory of the computer device in the form of software, so that the processor can call and execute the corresponding operations of each of the above modules.

[0132] In an exemplary embodiment, a computer device is provided. The computer device may be a server, and its internal structure diagram may be as shown in FIG. Fig. 9 As shown. The computer device includes a processor, a memory, an input / output interface (Input / Output, referred to as I / O) and a communication interface. The processor, the memory and the input / output interface are connected through a system bus, and the communication interface is connected to the system bus through the input / output interface. The processor of the computer device is used to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, a computer program and a database. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The database of the computer device is used to store data such as SQL query statements, stored process data and data lineage relationships. The input / output interface of the computer device is used to exchange information between the processor and an external device. The communication interface of the computer device is used to communicate with an external terminal through a network connection. When the computer program is executed by the processor, a data lineage analysis method is implemented.

[0133] Those skilled in the art will understand that Fig. 9 The structure shown in the figure is only a block diagram of a part of the structure related to the solution of the present application, and does not constitute a limitation on the computer device to which the solution of the present application is applied. The specific computer device may include more or fewer components than those shown in the figure, or combine certain components, or have a different arrangement of components.

[0134] In an exemplary embodiment, a computer device is provided, including a memory and a processor, wherein a computer program is stored in the memory, and when the processor executes the computer program, the steps in any one of the above-mentioned data lineage analysis method embodiments are implemented.

[0135] In one embodiment, when the processor executes the computer program, it also implements the steps in any one of the above data lineage analysis method embodiments.

[0136] In one embodiment, a computer-readable storage medium is provided, on which a computer program is stored. When the computer program is executed by a processor, the steps in any one of the above-mentioned data lineage analysis method embodiments are implemented.

[0137] In one embodiment, a computer program product is provided, including a computer program, which, when executed by a processor, implements the steps in any one of the above-mentioned data lineage analysis method embodiments.

[0138] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, stored data, displayed data, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of relevant data must comply with relevant regulations.

[0139] A person of ordinary skill in the art can understand that all or part of the processes in the above-mentioned embodiment method can be completed by instructing the relevant hardware through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, any reference to the memory, database or other medium used in the embodiments provided in the present application can include at least one of non-volatile memory and volatile memory. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical memory, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetic random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory can include random access memory (RAM) or external cache memory, etc. As an illustration and not limitation, RAM can be in various forms, such as static random access memory (SRAM) or dynamic random access memory (DRAM). The database involved in each embodiment provided in this application may include at least one of a relational database and a non-relational database. Non-relational databases may include distributed databases based on blockchains, etc., but are not limited to this. The processor involved in each embodiment provided in this application may be a general-purpose processor, a central processing unit, a graphics processor, a digital signal processor, a programmable logic device, a data processing logic device based on quantum computing, an artificial intelligence (AI) processor, etc., but are not limited to this.

[0140] The technical features of the above embodiments may be combined arbitrarily. To make the description concise, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this application.

[0141] The above-described embodiments only express several implementation methods of the present application, and the descriptions thereof are relatively specific and detailed, but they cannot be understood as limiting the scope of the present application. It should be pointed out that, for a person of ordinary skill in the art, several variations and improvements can be made without departing from the concept of the present application, and these all belong to the protection scope of the present application. Therefore, the protection scope of the present application shall be subject to the attached claims.

Claims

1. A data lineage analysis method, characterized in that: The method comprises: Execute SQL query to obtain stored procedure data; Converting the stored procedure data into a standardized execution plan; Extracting a table name of a scan operator and a table name of an insert operator from the execution plan; A table data reading file is constructed based on the table name of the scan operator, and a table data writing file is constructed based on the table name of the insert operator, wherein the table data reading file includes an execution plan name and records a data table required for the execution plan, and the table data writing file includes an execution plan name and records a data table for writing data to the execution plan; Based on the execution plan name, an association operation is performed on the table data reading file and the table data writing file to obtain the data lineage relationship of the stored procedure.

2. The method according to claim 1, characterized in that The step of converting the stored process data into a standardized execution plan includes: Converting the stored process data from a character string into first formatted structure data; Performing query rewriting on the first formatted structure data to obtain optimized second formatted structure data; An execution plan of the stored procedure is determined according to the second formatted structure data and preset database statistical information.

3. The method according to claim 2, characterized in that The step of converting the stored process data from a character string into first formatted structure data comprises: Performing lexical analysis on the stored procedure data to decompose the stored procedure into multiple tokens; According to preset SQL grammar rules, the multiple tags are combined into first formatted structure data.

4. The method according to claim 2, characterized in that: Before querying and rewriting the first formatted structure data, the method includes: Performing semantic analysis on the first formatted structure data to verify the logical correctness and feasibility of the first formatted structure data; The query rewriting of the first formatted structure data includes: When the first formatted structure data passes the verification of logical correctness and feasibility, query rewriting is performed on the first formatted structure data.

5. The method according to claim 2, characterized in that: Determining the execution plan of the stored procedure according to the second formatted structure data and preset database statistical information includes: Based on preset database statistical information, evaluating different versions of execution plans constructed based on the second formatted structure data to obtain an execution plan evaluation result; Filter out the target execution plan with the best execution plan evaluation result from different versions of execution plans; The target execution plan is determined as the execution plan of the stored procedure.

6. The method according to any one of claims 1 to 5, characterized in that: After obtaining the data lineage relationship of the stored process, the method further includes: The data lineage relationship is connected multiple times to obtain a multi-layer data lineage relationship.

7. The method according to any one of claims 1 to 5, characterized in that: The table name of the scan operator and the table name of the insert operator are extracted from the execution plan, including: The table name of the scan operator and the table name of the insert operator are extracted from the execution plan through a regular expression.

8. A data lineage analysis device, characterized in that: The device comprises: Data acquisition module, used to execute SQL queries and obtain stored process data; A data conversion module, used to obtain stored process data and convert the stored process data into a standardized execution plan; A data extraction module, used to extract the table name of the scan operator and the table name of the insert operator from the execution plan; A data construction module, used to construct a table data reading file based on the table name of the scan operator, and to construct a table data writing file based on the table name of the insert operator, wherein the table data reading file includes an execution plan name and records a data table required for the execution plan, and the table data writing file includes an execution plan name and records a data table for writing data to the execution plan; The data analysis module is used to perform an association operation on the table data reading file and the table data writing file based on the execution plan name to obtain the data lineage relationship of the stored procedure.

9. A computer device comprising a memory and a processor, wherein the memory stores a computer program, wherein: When the processor executes the computer program, the steps of the method according to any one of claims 1 to 7 are implemented.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 7 are implemented.

Images