Multi-database SQL (Structured Query Language) blood relationship analysis method and system based on G4 file

Abstract

The invention provides a multi-database SQL blood relationship analysis method and system based on a G4 file, and relates to the technical field of database management.The method comprises the steps that SQL statements are analyzed based on a G4 grammar file, a front-back dependency relationship is constructed through a statement dependency directed graph, grammar rules are optimized through a dialect feature matrix and a Markov decision process, and a multi-database SQL blood relationship analysis result is obtained. According to the method, the SQL data extraction operation and the circulation sequence are sequentially analyzed, the field blood relationship link diagram is generated, and the structured result is output, so that multi-database SQL grammar features can be accurately recognized, the complex SQL analysis problem is solved, the blood relationship analysis accuracy and efficiency are effectively improved, and cross-database unified blood relationship management is supported.

Description

Technical Field

[0001] The present invention relates to the technical field of database management, and in particular to a multi-database SQL lineage analysis method and system based on G4 files. Background Art

[0002] As enterprise informatization deepens, data asset management and governance are becoming increasingly important. SQL, as the mainstream database query language, is widely used in various data processing and analysis scenarios. Data lineage tracks the entire flow of data from source to target, revealing dependencies and conversion operations between data, helping enterprises understand the source and flow of data, as well as changes that occur during the flow. Accurately establishing data lineage relationships in complex data environments is crucial for data quality management, compliance audits, impact analysis, and other tasks. Traditional SQL lineage analysis methods typically rely on specific database syntax parsers. Multiple parsing systems need to be developed and maintained for different database types, resulting in high development costs and difficult maintenance. Existing parsing technologies still have limited support for multi-database environments when processing complex SQL statements. They are unable to uniformly handle the SQL syntax characteristics of different databases and struggle to effectively process complex SQL syntax structures. This leads to broken or incorrect lineage relationships in actual application scenarios, an inability to accurately reflect data flow paths, and a lack of in-depth analysis capabilities for dependencies between SQL statements. This makes it difficult to accurately identify cross-statement reference relationships and data flow links, resulting in incomplete lineage analysis results that fail to meet actual business needs. Therefore, a solution is urgently needed to solve the problems existing in the prior art. Summary of the Invention

[0003] The embodiment of the present invention provides a multi-database SQL lineage analysis method and system based on G4 files, which can at least solve some of the problems existing in the prior art.

[0004] A first aspect of an embodiment of the present invention provides a multi-database SQL lineage parsing method based on a G4 file, comprising: Obtain the grammatical rule set for SQL statements based on the G4 grammar file, perform lexical cleaning and normalization on the SQL statements, and split them by semicolons to obtain a sequence of independent statements. Analyze the independent statement sequence to construct a statement dependency directed graph that describes the dependencies between the previous and next statements. Obtaining database configuration, operating parameters, and temporary table information to form basic parsing data, using the basic parsing data to construct a dialect feature matrix describing database grammatical features, establishing a grammatical rule probability transition matrix based on the dialect feature matrix, predicting an optimal rule chain in the probability transition matrix through a Markov decision process to obtain an optimized grammatical rule sequence, performing feature matching between the independent statement sequence and the optimized grammatical rule sequence, and generating a mapping relationship table corresponding to the parsing rules for each statement; Traverse the statement nodes in the statement dependency directed graph, select the corresponding parsing rules based on the mapping relationship table for parsing, extract field screening conditions and calculation expressions to generate data operation sequences, identify subquery nesting and temporary table flow to generate data flow sequences, and use data operation sequences and data flow sequences to build a field lineage link diagram; The field lineage link graph is structured to obtain a lineage analysis result and output it in a specified format.

[0005] In an optional embodiment, Obtain the grammatical rule set for SQL statements based on the G4 grammar file, perform lexical cleaning and normalization on the SQL statements, and split them by semicolon to obtain a sequence of independent statements. Analyze the independent statement sequence to construct a statement dependency directed graph that describes the dependencies between the previous and next statements, including: Parse the G4 grammar file, read the grammar definition file content, build the lexical analyzer and syntax analyzer, and generate a set of standard grammar rules for SQL statements; Receive an SQL statement to be processed, perform lexical analysis using the grammar rule set, identify keywords, identifiers, operators, and literals in the SQL statement, remove redundant whitespace characters and comments, unify capitalization and line breaks to obtain a normalized SQL text, and split the normalized SQL text into a sequence of independent statements based on semicolon delimiters; Traverse the independent statement sequence, parse the grammatical structure of each statement based on the grammatical rule set, extract the table name, field name and temporary table definition in the statement, identify the data references and dependencies between statements, construct each statement into a statement node containing statement content and reference information, establish directed connections between nodes based on the data references and dependencies, and generate a statement dependency directed graph that records the execution process of the SQL statement.

[0006] In an optional embodiment, Obtaining database configuration, operating parameters, and temporary table information to form parsing basic data, using the parsing basic data to construct a dialect feature matrix describing database grammatical features, establishing a grammatical rule probability transition matrix based on the dialect feature matrix, and predicting the optimal rule chain in the probability transition matrix through a Markov decision process to obtain an optimized grammatical rule sequence including: Obtain database configuration and operating parameters, standardize the connection strings in the database configuration and operating parameters, parse the execution plan of the SQL statement to obtain intermediate results and temporary table information, and organize the operating parameters, intermediate results and temporary table information into basic parsing data; Construct a feature vector space, which includes the dimensions of function syntax features, keyword usage features, and grammatical structure features. Based on the parsed basic data, calculate the frequency of occurrence and inverse document frequency of features in different databases. Determine feature weight scores based on the frequency of occurrence and inverse document frequency, and populate the feature weight scores into the dialect feature matrix. Based on the dialect feature matrix, a rule state set is constructed. The transition probabilities between the rule nodes in the rule state set are calculated to form a grammatical rule probability transition matrix. The initial probability distribution of the rule state set is calculated based on the historical frequency of rule usage. Based on the grammatical rule probability transition matrix and the initial probability distribution, the state value function is iteratively calculated using the Bellman equation. The action sequence that maximizes long-term benefits is selected to form an optimal rule chain. Redundant rules are eliminated on the optimal rule chain to obtain an optimized grammatical rule sequence. In an optional embodiment, The rule state set is constructed based on the dialect feature matrix, the transition probabilities between the rule nodes in the rule state set are calculated to form the grammatical rule probability transition matrix, and the initial probability distribution of the rule state set is calculated based on the historical usage frequency of the rules. Obtain the dialect feature matrix and construct a rule state set. Extract the basic operation complexity of each rule node in the rule state set and sum them to obtain the time complexity value. Read the memory usage value and processor utilization value of the rule node and add them to obtain the resource consumption value. Add the time complexity value and resource consumption value to calculate the comprehensive execution cost of the rule node. Traverse the rule nodes in the rule state set, select two adjacent rule nodes in the rule execution sequence as the starting rule node and the target rule node to form a rule node pair, count the historical execution frequencies of the rule node pair, calculate the sum of the historical execution frequencies of the target rule nodes corresponding to the starting rule node, divide the historical execution frequencies by the sum of the historical execution frequencies to obtain the basic transition probability, multiply the comprehensive execution cost of the target rule node by the attenuation coefficient, and then subtract the result from the negative exponential function to obtain the attenuation value, which is then multiplied by the basic transition probability to obtain the attenuated transition probability; The original usage frequency is calculated by counting the historical execution frequency and the sum of the rule nodes. The comprehensive execution cost of the rule node is multiplied by the adjustment coefficient, added with one and the inverse is taken to obtain the cost adjustment value. The original usage frequency is multiplied by the cost adjustment value and normalized to obtain the initial probability distribution of the rule state set. The attenuated transition probability is divided by the row normalization coefficient to construct the grammar rule probability transition matrix.

[0007] In an optional embodiment, Performing feature matching on the independent statement sequence and the optimized grammar rule sequence to generate a mapping relationship table corresponding to the parsing rule for each statement includes: Extract sentence sequence feature information from independent sentence sequences, obtain the part-of-speech sequence of each sentence to generate grammatical structure features, extract the keyword set of each sentence to generate lexical features, collect the preceding sentence information, current sentence information, and subsequent sentence information of each sentence to generate context features, and combine the grammatical structure features, lexical features, and context features to construct a sentence comprehensive feature vector; Extract the matching pattern of each rule from the optimized grammar rule sequence to form the rule pattern feature, extract the constraint condition of each rule to form the rule constraint feature, and combine the rule pattern feature and the rule constraint feature to construct the rule feature template vector; Calculate the matching degree between the sentence comprehensive feature vector and the rule feature template vector, divide the intersection cardinality of the grammatical structure features and the rule pattern features by the union cardinality to calculate the structural similarity, divide the sum of the products of the matching degrees of each keyword in the lexical feature and the rule by the number of keywords to calculate the lexical similarity, calculate the similarity between the context features and the rule constraint features to obtain the contextual similarity, and perform a weighted combination of the structural similarity, lexical similarity, and contextual similarity to obtain the comprehensive similarity; The rule with the highest comprehensive similarity is selected as the parsing rule corresponding to each statement, and the statement identifier, parsing rule identifier, and corresponding comprehensive similarity are combined to generate a mapping relationship table.

[0008] In an optional embodiment, Traversing the statement nodes in the directed graph of statement dependencies, selecting the corresponding parsing rules according to the mapping relationship table for parsing, extracting field screening conditions and calculation expressions to generate data operation sequences, identifying subquery nesting and temporary table flow to generate data flow sequences, and using data operation sequences and data flow sequences to build a field lineage link graph, including: Traverse the statement nodes in the statement dependency directed graph, extract the field identifier and operation information from each statement node to generate field screening conditions and calculation expressions, and combine the field screening conditions and calculation expressions to generate a data operation sequence; Identify data dependencies between statement nodes, extract the mapping relationship from source fields to target fields in subquery statements to generate subquery nesting, extract the table identifier of the temporary table and its transfer fields to generate temporary table flow, extract the cross-statement field identifier and its reference type to generate cross-statement reference, and combine subquery nesting, temporary table flow, and cross-statement reference to generate a data flow sequence; Construct an intra-statement field transfer path based on the data operation sequence, construct an inter-statement field transfer path based on the data flow sequence, and merge the intra-statement field transfer path and the inter-statement field transfer path to form a complete field transfer path set; The field identifier, field type and field source are extracted from the field transfer path set to construct field nodes, the mutual conversion relationship between field nodes and their conversion operations are extracted to construct conversion edges, and the field nodes and conversion edges are combined to generate a field lineage link diagram that records the complete lineage relationship of the fields.

[0009] In an optional embodiment, Structural processing of the field lineage link graph to obtain lineage analysis results and outputting them into a specified format includes: Extract the lineage hierarchical relationship of field nodes from the field lineage link graph, identify the direct predecessor node and direct successor node of each field node, build a lineage transmission link, and group the field nodes according to the lineage hierarchical relationship to form a lineage hierarchical structure; Traversing the field nodes in the lineage hierarchical structure, extracting the conversion operation type and conversion parameters in the conversion edge corresponding to each field node, and combining the lineage hierarchical relationship of the field node with the conversion operation information to form a lineage analysis result; The field node identifiers, lineage levels, conversion operations, predecessor nodes, and successor nodes in the lineage analysis results are reorganized and output according to a preset structured format.

[0010] A second aspect of an embodiment of the present invention provides a multi-database SQL lineage analysis system based on G4 files, comprising: The first unit is used to obtain the grammatical rule set of SQL statements based on the G4 grammar file, perform lexical cleaning and normalization processing on the SQL statements, split them by semicolons to obtain independent statement sequences, and analyze the independent statement sequences to construct a statement dependency directed graph that describes the dependencies between the previous and next statements. The second unit is configured to obtain database configuration, operating parameters, and temporary table information to form basic parsing data, use the basic parsing data to construct a dialect feature matrix describing the grammatical features of the database, establish a grammatical rule probability transition matrix based on the dialect feature matrix, predict the optimal rule chain in the probability transition matrix through a Markov decision process to obtain an optimized grammatical rule sequence, perform feature matching on the independent statement sequence and the optimized grammatical rule sequence, and generate a mapping relationship table corresponding to the parsing rules for each statement; The third unit is used to traverse the statement nodes in the statement dependency directed graph, select the corresponding parsing rules according to the mapping relationship table, extract the field screening conditions and calculation expressions to generate data operation sequences, identify subquery nesting and temporary table flow to generate data flow sequences, and use the data operation sequences and data flow sequences to construct a field lineage link diagram; The fourth unit is used to process the field blood relationship link diagram in a structural manner to obtain a blood relationship analysis result and output it in a specified format.

[0011] According to a third aspect of an embodiment of the present invention, an electronic device is provided, including: A processor and a memory for storing processor-executable instructions, wherein the processor is configured to call the instructions stored in the memory to execute the aforementioned method.

[0012] According to a fourth aspect of an embodiment of the present invention, a computer-readable storage medium is provided, on which computer program instructions are stored. When the computer program instructions are executed by a processor, the method described above is implemented.

[0013] In the present invention, a multi-database SQL lineage parsing method based on G4 files is used to achieve unified lineage analysis of SQL statements in different database dialects, effectively solving the problem that the existing technology is difficult to handle SQL syntax differences in a multi-database environment. The Markov decision process is used to optimize the grammatical rule sequence, and the probability transfer matrix constructed in combination with the dialect feature matrix can intelligently identify and adapt to the grammatical characteristics of different databases, greatly improving the accuracy and efficiency of SQL parsing, especially significantly enhancing the processing ability of complex queries. By constructing a statement dependency directed graph and a field lineage link graph, the field transfer path and conversion operations in the SQL statement are systematically parsed, making the lineage analysis results more complete and accurate, and providing reliable technical support for data governance, security auditing and system optimization. BRIEF DESCRIPTION OF THE DRAWINGS

[0014] Figure 1 Schematic diagram of the process of a multi-database SQL lineage parsing method based on G4 files according to an embodiment of the present invention; Figure 2 This is a diagram of the SQL statement processing flow; Figure 3 Schematic diagram of the mapping process for matching SQL statements with grammar rules. DETAILED DESCRIPTION

[0015] To make the objectives, technical solutions, and advantages of the embodiments of the present invention more clear, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the accompanying drawings in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative efforts shall fall within the scope of protection of the present invention.

[0016] The following specific embodiments are used to describe the technical solution of the present invention in detail. The following specific embodiments can be combined with each other, and the same or similar concepts or processes may not be described in detail in some embodiments.

[0017] Figure 1FIG. 1 is a flow chart of a multi-database SQL lineage parsing method based on a G4 file according to an embodiment of the present invention. Figure 1 As shown, the method includes: Obtain the grammatical rule set for SQL statements based on the G4 grammar file, perform lexical cleaning and normalization on the SQL statements, and split them by semicolons to obtain a sequence of independent statements. Analyze the independent statement sequence to construct a statement dependency directed graph that describes the dependencies between the previous and next statements. Obtaining database configuration, operating parameters, and temporary table information to form basic parsing data, using the basic parsing data to construct a dialect feature matrix describing database grammatical features, establishing a grammatical rule probability transition matrix based on the dialect feature matrix, predicting an optimal rule chain in the probability transition matrix through a Markov decision process to obtain an optimized grammatical rule sequence, performing feature matching between the independent statement sequence and the optimized grammatical rule sequence, and generating a mapping relationship table corresponding to the parsing rules for each statement; Traverse the statement nodes in the statement dependency directed graph, select the corresponding parsing rules based on the mapping relationship table for parsing, extract field screening conditions and calculation expressions to generate data operation sequences, identify subquery nesting and temporary table flow to generate data flow sequences, and use data operation sequences and data flow sequences to build a field lineage link diagram; The field lineage link graph is structured to obtain a lineage analysis result and output it in a specified format.

[0018] In an optional embodiment, Obtain the grammatical rule set for SQL statements based on the G4 grammar file, perform lexical cleaning and normalization on the SQL statements, and split them by semicolon to obtain a sequence of independent statements. Analyze the independent statement sequence to construct a statement dependency directed graph that describes the dependencies between the previous and next statements, including: Parse the G4 grammar file, read the grammar definition file content, build the lexical analyzer and syntax analyzer, and generate a set of standard grammar rules for SQL statements; Receive an SQL statement to be processed, perform lexical analysis using the grammar rule set, identify keywords, identifiers, operators, and literals in the SQL statement, remove redundant whitespace characters and comments, unify capitalization and line breaks to obtain a normalized SQL text, and split the normalized SQL text into a sequence of independent statements based on semicolon delimiters; Traverse the independent statement sequence, parse the grammatical structure of each statement based on the grammatical rule set, extract the table name, field name and temporary table definition in the statement, identify the data references and dependencies between statements, construct each statement into a statement node containing statement content and reference information, establish directed connections between nodes based on the data references and dependencies, and generate a statement dependency directed graph that records the execution process of the SQL statement.

[0019] Figure 2 The following diagram shows the SQL statement processing flow. The G4 grammar definition file, which contains the complete lexical and grammatical rules for the SQL language, is read. By parsing these rules, lexical rules are extracted to identify basic SQL units, such as keywords, identifiers, and numbers. Grammar rules are extracted to define how these basic units are combined into valid SQL statements. During the parsing process, the rules in the G4 file are converted into an internal data structure, a syntax tree is constructed, and regular expression patterns are created for the lexical rules to subsequently identify various lexical units. For the grammatical rules, a set of grammar productions is constructed to form a parsing table, which guides the subsequent syntax analysis process.

[0020] When building a lexical analyzer, the recognition rules for all keywords, operators, punctuation marks, and other special characters are organized into a priority queue to ensure that longer patterns are matched first and avoid misrecognition. When building a parser, a top-down recursive descent analysis method or a bottom-up analysis method is used to generate a complete parsing table based on the grammatical rules. The parsing table defines the actions to be taken when a specific lexical unit is recognized. The result is a complete parsing system consisting of a lexical analyzer and a parser, capable of processing input SQL statements according to standard SQL grammar rules.

[0021] After receiving the SQL statement to be processed, it performs lexical analysis, traversing the input SQL text and identifying various lexical units according to the lexical rules constructed previously. The lexical analyzer scans the input text character by character. When a character sequence that matches a lexical rule is found, it marks it as the corresponding lexical unit type, such as keywords, identifiers, operators, comparison operators, string literals, numeric literals, etc., and performs normalization processing, including removing all comments, standardizing whitespace character processing, replacing multiple consecutive spaces, tabs, newlines, etc. with a single space, standardizing keyword case, usually converting to uppercase, standardizing identifier format, maintaining the original case or converting according to the configuration, and standardizing newline characters to ensure cross-platform consistency.

[0022] After normalization, a uniformly formatted SQL text is obtained. Using semicolons as delimiters, the entire SQL text is split into multiple independent SQL statements to form a statement sequence. Each statement represents a complete SQL command, which may be operations such as creating a table, querying, and inserting. The independent statements are traversed and each statement is subjected to in-depth syntax analysis. The syntax analysis process uses the previously constructed syntax rule set to build a detailed syntax tree for each statement. The syntax tree construction process strictly follows the syntax rules and can analyze the statement structure from top to bottom or bottom to top. For example, for a query statement, the query clause, source clause, conditional clause, and other components will be identified, and the internal structure of each clause will be further analyzed.

[0023] After parsing is complete, key information is extracted from the syntax tree, including table names, field names, temporary table definitions, and table aliases. Table names can be extracted from source clauses, join clauses, insert, update, and delete statements; field names can be extracted from query clauses, conditional clauses, grouping, and sorting; temporary table definitions can be extracted from accompanying clauses or subqueries; and table aliases are mapped to table names by parsing table alias definitions. For each statement, a statement node is created, containing the statement content and various information extracted from the syntax tree, such as the statement type, involved tables and fields, and defined temporary tables or views.

[0024] After the statement nodes are created, a dependency graph between the statements is established by analyzing the data reference relationships between the statements. When a statement references a table or view created by another statement, a directed connection is established between the two statement nodes. When a statement modifies a table referenced by other statements, a corresponding dependency relationship is also established. For subqueries or temporary table references in complex queries, these internal dependencies are also identified and recorded. Dependency identification is mainly achieved by comparing the table definition and table reference information in each statement node. For example, if statement A creates table C, and statement B queries table C, a directed connection is established from statement A to statement B to generate a complete statement dependency directed graph.

[0025] For example, a complete SQL script creates a user table with fields for user ID, user name, email address, and creation time. It also creates an order table with fields for order ID, user ID, order date, and total amount, and sets the user ID as a foreign key to the user table. It then inserts two user records into the user table. It then executes an analytical query, using a temporary table to define active users, count the number of orders for each active user, and filter out users with a total order count greater than zero.

[0026] When parsing a G4 grammar file, the SQL grammar G4 file is read and the lexical and grammatical rules defined therein are analyzed. Lexical rules define how to recognize keywords such as "create," "table," and "query," as well as basic patterns such as identifiers, numbers, and strings. Syntax rules define how these basic elements are combined into a valid SQL statement structure. These rules are used to build a lexical analyzer capable of recognizing various elements in SQL statements and a grammatical analyzer capable of parsing complete SQL statement structures.

[0027] After receiving the sample SQL script, the lexical analyzer begins its work, removing all comments from the script, such as those preceding the user table creation and order table creation. It then identifies all lexical units, normalizes excess whitespace, and standardizes keyword case. The normalized SQL text is then split by semicolon delimiters, resulting in four separate statements: creating the user table, creating the order table, inserting user data, and analyzing active user queries.

[0028] Perform syntax analysis on each of the four statements. When parsing the statement to create the user table, identify the table name "User Table" and the fields "User Number", "User Name", "Email", and "Creation Time", as well as the data types and constraints of each field. When parsing the statement to create the order table, identify the table name "Order Table" and the fields "Order Number", "User Number", "Order Date", and "Total Amount", as well as the foreign key constraint referencing the "User Number" of the "User Table". When parsing the insert statement, identify the target table "User Table" and the inserted fields and corresponding values. When parsing the last query statement, identify the temporary table definition "Active Users", and find that the temporary table references the "User Table" and the "Order Table", while the main query references the temporary table "Active Users" and the table "Order Table".

[0029] Based on the parsing results, a directed dependency graph is constructed. The create user table statement serves as the foundation and has no dependencies on other statements. The create order table statement depends on the create user table statement because the order table's foreign key references the user table. The insert statement depends on the create user table statement because data is inserted into the user table. The query statement depends on all previous statements. The query involves both the user and order tables, and the query result depends on the inserted data, generating a directed dependency graph.

[0030] In this embodiment, the statement dependency directed graph clearly displays the execution process and data dependency of SQL statements, realizes comprehensive standardization of SQL statements, and can effectively eliminate the problems caused by differences in writing styles of different developers. The precise recognition capability can handle various complex SQL syntax structures, including subqueries, temporary table definitions, table joins and other advanced syntax features. By constructing a statement dependency directed graph, a visual expression of the SQL script execution process is achieved, which can help developers quickly grasp the entire data processing link and identify potential logical problems or optimization opportunities. By analyzing statement dependencies, possible performance bottlenecks and optimization opportunities can be identified, such as repeated calculations, redundant queries or unnecessary data conversions, which can guide developers to reconstruct and optimize SQL and improve query efficiency and resource utilization.

[0031] In an optional embodiment, Obtaining database configuration, operating parameters, and temporary table information to form parsing basic data, using the parsing basic data to construct a dialect feature matrix describing database grammatical features, establishing a grammatical rule probability transition matrix based on the dialect feature matrix, and predicting the optimal rule chain in the probability transition matrix through a Markov decision process to obtain an optimized grammatical rule sequence including: Obtain database configuration and operating parameters, standardize the connection strings in the database configuration and operating parameters, parse the execution plan of the SQL statement to obtain intermediate results and temporary table information, and organize the operating parameters, intermediate results and temporary table information into basic parsing data; Construct a feature vector space, which includes the dimensions of function syntax features, keyword usage features, and grammatical structure features. Based on the parsed basic data, calculate the frequency of occurrence and inverse document frequency of features in different databases. Determine feature weight scores based on the frequency of occurrence and inverse document frequency, and populate the feature weight scores into the dialect feature matrix. Based on the dialect feature matrix, a rule state set is constructed. The transition probabilities between rule nodes in the rule state set are calculated to form a grammatical rule probability transition matrix. The initial probability distribution of the rule state set is calculated based on the historical frequency of rule usage. Based on the grammatical rule probability transition matrix and the initial probability distribution, the state value function is iteratively calculated using the Bellman equation. The action sequence that maximizes long-term benefits is selected to form an optimal rule chain. Redundant rules are eliminated from the optimal rule chain to obtain an optimized grammatical rule sequence. Comprehensive database configuration and operating parameters, table structure definitions and field attributes, SQL statement intermediate results, and temporary table information are obtained. The connection string is extracted from the database connection configuration and standardized to remove environment-specific path and authentication information, while retaining the core connection parameters. By analyzing the driver identifier and service information in the connection string, the database type identifier (such as relational database, column-based database, or time series database) is extracted. Version information, including major and minor version numbers, is also obtained. This information is crucial for subsequent feature analysis.

[0032] In-depth collection of table structure definitions and field attribute information. Obtain the complete table structure definition, including table name, table type, storage engine, and partitioning strategy, by querying the database metadata view or system table. Collect detailed attributes for each table field, recording the field name, data type, length precision, default value, and null value handling strategy. Organize the information into an attribute mapping table to establish a correspondence between the field name and its complete attribute set. At the same time, extract the constraint relationships between tables, such as primary key constraints, foreign key constraints, unique constraints, and check constraints, to form a constraint relationship table, recording the constraint type, associated fields, and referenced table information. In addition, it is necessary to collect index information, including index name, index type, covering fields, and cardinality estimation, to construct an index feature table.

[0033] After collecting the table structure, you need to parse the SQL statement's execution plan, obtain the execution strategy generated by the query optimizer, and analyze intermediate results and temporary table information. The execution plan provides insight into the query optimizer's decision-making logic, including table access methods, join algorithm selection, and sorting implementation. Extract performance-related parameters from the execution plan, such as the estimated number of rows, cache usage, and parallelism settings. Focus on the generation and processing of intermediate results, recording the size of subquery result sets, the creation and use of temporary tables, and the data flow path. Integrate the execution information with the previously collected structural information to form complete analytical foundational data, providing data support for subsequent feature analysis.

[0034] Based on the parsed basic data, we begin constructing a feature vector space. This feature vector space consists of multiple dimensions. The function syntax dimension covers the usage characteristics of various functions, such as aggregation functions, window functions, date processing functions, and string manipulation functions. The keyword usage dimension includes the use of query keywords, conditional keywords, join keywords, and transaction control keywords. The syntax structure dimension describes advanced grammatical features such as complex query structures, nested subqueries, the use of common table expressions, and recursive queries. For each feature, its frequency of occurrence across different database dialects is calculated. This is the ratio of the number of times a feature appears in a specific database to the total number of features in that database, reflecting the feature's ubiquity. The inverse document frequency is also calculated to measure the feature's discriminative power. This is the inverse relationship between the number of databases containing a feature and the total number of databases. Combining the frequency of occurrence and the inverse document frequency, each feature is assigned a weight score. A higher weight indicates the greater importance of the feature in distinguishing different database dialects. The feature weights are then applied to the dialect feature matrix to form a mathematical representation of the database dialect.

[0035] A rule state set is constructed based on the dialect feature matrix. The rule state set contains multiple grammatical transformation rules, each of which represents a transformation from one grammatical form to another. For example, a date function in one database can be converted to a date function corresponding to another database, or a specific query structure can be rewritten into an equivalent but more efficient form. The transition probabilities between each rule node in the rule state set are calculated to form a grammatical rule probability transition matrix. The transition probabilities reflect which rules might be applied next after applying a rule, and their probability distribution. Based on historical usage data, the initial probability distribution of the rule state set is calculated, that is, the probability of selecting each rule at the beginning of optimization. Generally, more commonly used rules have higher initial probabilities.

[0036] Based on the constructed grammatical rule probability transition matrix and initial probability distribution, a Markov decision process is used to find the optimal rule sequence. At each decision step, a set of optional actions (i.e., applicable rule transitions) is calculated based on the current state. For each possible action, its immediate reward value and potential future benefit value are evaluated. The immediate reward reflects the performance improvement or code simplification immediately achieved after applying the rule, while the future benefit considers the long-term optimization effect. The Bellman equation is used to iteratively calculate the value function of each state, searching for the action sequence that maximizes long-term benefits and forming an optimal rule chain. The resulting optimal rule chain is then subjected to redundant rule elimination, removing mutually offsetting or duplicated rules, resulting in a streamlined and efficient optimized rule sequence for practical database dialect conversion and query optimization.

[0037] For example, in a cross-database migration scenario, an application system originally running on Database A is migrated to Database B. The connection configuration information for Database A is obtained, and the connection string is standardized. The database type is determined to be a relational database, the version is a mainstream commercial version, and it supports advanced analytics and stored procedures. By querying the system tables to obtain the complete table structure definition, it is found that the core business tables include user information, order, product, and transaction flow tables, as well as multiple statistical summary tables and log tables.

[0038] Conduct detailed field attribute collection and record the name, data type, and constraints of each field. For example, the user information table contains fields such as user number, user name, registration time, and account status. The user number is an integer primary key, the user name is a variable-length string with a unique index, the registration time uses a date and time type in a specific format, and the account status is an enumeration type. Organize the information into an attribute mapping table and establish a correspondence between the fields and their attributes. Extract the constraint relationships between tables. For example, the order table is linked to the user information table and the product table through foreign keys to form a constraint relationship table. In addition, the index information of each table is collected, including primary key index, unique index, and common index, to construct an index feature table.

[0039] Analyze typical SQL queries in the system and parse their execution plans to obtain intermediate results and temporary table information. For example, a complex sales report query involves multi-table joins, group aggregation, and sorting operations. The execution plan shows that the query uses index scans, hash joins, and sort merges, generating two temporary tables to store intermediate results. Analysis reveals that the query makes extensive use of date functions and window functions unique to Database A and employs a specific paging syntax. This execution information is integrated with the previously mentioned structural information to form a complete analytical foundation.

[0040] Based on the parsed basic data, a feature vector space was constructed. In the dimension of function syntax features, database A's unique date formatting functions, string concatenation functions, and statistical analysis functions were identified. In the dimension of keyword usage features, extensive use of specific concatenation syntax and paging keywords was observed. In the dimension of grammatical structure features, the usage patterns of multi-layer nested subqueries and recursive common table expressions were observed. The frequency of occurrence and inverse document frequency of features in databases A and B were calculated to determine feature weight scores. For example, date processing functions have significant syntax differences between the two databases and receive higher weights; however, basic query keywords are more common across databases and receive lower weights. These weights are then added to the dialect feature matrix.

[0041] Based on the dialect feature matrix, a rule state set is constructed, containing various grammatical transformation rules. Examples include rules for converting the date function in database A to the corresponding function in database B, rewriting specific join syntax to standard join syntax, and rewriting paging queries to a form suitable for database B. Transition probabilities between rule nodes are calculated to form a grammatical rule probability transition matrix. For example, applying the date function conversion rule requires applying the string processing rule, so the transition probability between these two rules is high. Based on historical optimization experience, the initial probability distribution of the rule state set is calculated, and commonly used basic conversion rules have higher initial probabilities.

[0042] A Markov decision process is used to find the optimal rule sequence. Starting from an initial state, the system evaluates optional rule transformations. For sales report query optimization, the date function transformation rule is considered, and its immediate reward and future benefits are calculated. The immediate reward reflects the compatibility issues that the transformation can directly resolve, while the future benefits consider the potential for subsequent optimization. Through iterative calculations using the Bellman equation, a series of rules are selected, including date function transformation, window function rewriting, join syntax standardization, and paging query rewriting, to form an optimal rule chain. This rule chain is then de-redundant, removing some mutually offsetting or duplicated transformation steps, resulting in a streamlined and efficient optimization rule sequence.

[0043] In this embodiment, by constructing a feature vector space and an optimization mechanism based on the Markov decision process, accurate recognition and automatic conversion of database dialects are achieved, and the efficiency and accuracy of cross-database migration are improved. Through the automatic generation of feature matrices and optimization rule sequences, the conversion process is systematized and standardized, which significantly reduces the migration cost and risk. By analyzing database characteristics and SQL execution plans, performance bottlenecks can be automatically identified and targeted optimization rules can be generated to enable queries to achieve optimal performance in the target database environment. A complete data structure view and dependency diagram can be provided to help developers and data managers better understand and manage data assets and support data governance and compliance requirements.

[0044] In an optional embodiment, The rule state set is constructed based on the dialect feature matrix, the transition probabilities between the rule nodes in the rule state set are calculated to form the grammatical rule probability transition matrix, and the initial probability distribution of the rule state set is calculated based on the historical usage frequency of the rules. Obtain the dialect feature matrix and construct a rule state set. Extract the basic operation complexity of each rule node in the rule state set and sum them to obtain the time complexity value. Read the memory usage value and processor utilization value of the rule node and add them to obtain the resource consumption value. Add the time complexity value and resource consumption value to calculate the comprehensive execution cost of the rule node. Traverse the rule nodes in the rule state set, select two adjacent rule nodes in the rule execution sequence as the starting rule node and the target rule node to form a rule node pair, count the historical execution frequencies of the rule node pair, calculate the sum of the historical execution frequencies of the target rule nodes corresponding to the starting rule node, divide the historical execution frequencies by the sum of the historical execution frequencies to obtain the basic transition probability, multiply the comprehensive execution cost of the target rule node by the attenuation coefficient, and then subtract the result from the negative exponential function to obtain the attenuation value, which is then multiplied by the basic transition probability to obtain the attenuated transition probability; The original usage frequency is calculated by counting the historical execution frequency and the sum of the rule nodes. The comprehensive execution cost of the rule node is multiplied by the adjustment coefficient, added with one and the inverse is taken to obtain the cost adjustment value. The original usage frequency is multiplied by the cost adjustment value and normalized to obtain the initial probability distribution of the rule state set. The attenuated transition probability is divided by the row normalization coefficient to construct the grammar rule probability transition matrix.

[0045] Obtain a dialect feature matrix and construct a rule state set. The dialect feature matrix contains the grammatical features and usage patterns of different database dialects and serves as the foundation for constructing the rule state set. Based on the dialect feature matrix, determine the set of grammatical rules that need to be supported. These rules cover various aspects of SQL statement parsing and transformation, such as expression parsing, conditional parsing, join parsing, and aggregate function parsing. Each rule represents a specific method for processing or transforming grammatical structures and has independent execution logic and resource requirements. Rules are organized into a rule state set, which serves as the state space of a Markov decision process.

[0046] For each rule node in the rule state set, a detailed performance characteristic analysis is performed to extract the basic operation complexity of the rule node, including time complexity and space complexity. The basic operation complexity reflects the algorithm complexity when the rule is executed, usually described in big O notation, such as O(n), O(n 2 ), O(log n), etc. Different types of rule nodes have different complexity characteristics. For example, a simple field mapping rule may have a complexity of O(1), while a complex subquery parsing rule has a complexity of O(n 2 The complexity of each basic operation of the rule node is weighted and summed to obtain the overall time complexity value. A higher value indicates that more computing resources are required for rule execution.

[0047] Read the memory usage value of the rule node. This value reflects the memory resource requirements during rule execution, including the storage space for the rule itself and temporary data storage during execution. Memory requirements vary significantly between rules. For example, rules that process large datasets may require more memory to store intermediate results. Read the processor utilization value of the rule node. This value reflects the CPU resource utilization of the rule execution, typically expressed as a percentage. Processor-intensive rules, such as complex regular expression matching or string processing, often have high processor utilization. Add the memory usage and processor utilization values to obtain the resource consumption value.

[0048] Adding the time complexity value and the resource consumption value yields the comprehensive execution cost of the rule node. This comprehensive execution cost is a comprehensive metric that comprehensively considers the computational complexity and resource consumption of rule execution, providing a quantitative basis for subsequent probability calculations. Rules with higher execution costs should be given lower priority in the system to avoid resource bottlenecks and performance issues.

[0049] After completing the rule node execution cost analysis, the grammar rule probability transition matrix is constructed. All rule nodes in the rule state set are traversed, and two adjacent rule nodes are selected from the historical execution records. The rule node that is executed first is used as the starting rule node, and the rule node that is executed later is used as the target rule node. These rule node pairs are formed. Node pairs reflect the execution order relationship between rules and are the basic unit for constructing the transition matrix.

[0050] For each rule node pair, its historical execution frequency is counted. This is the number of times the starting rule node was immediately followed by the target rule node in the past rule execution sequence. This historical execution frequency reflects the degree of correlation between the two rules; a higher frequency indicates a greater likelihood that the two rules will be executed sequentially. For each starting rule node, the sum of the historical execution frequencies of all possible target rule nodes is calculated. This sum represents the total execution count of all possible rules following the starting rule node.

[0051] The base transition probability is calculated by dividing the historical execution frequency of a rule node pair by the total historical execution frequency. The base transition probability reflects the raw probability of transitioning from a starting rule node to a target rule node. It is based solely on historical execution patterns and does not consider execution cost factors. The base transition probability satisfies the probability distribution property, meaning that for a given starting rule node, the sum of the base transition probabilities of all possible target rule nodes is 1.

[0052] Multiply the comprehensive execution cost of the target rule node by the attenuation coefficient to obtain the attenuation factor. The attenuation coefficient is a system parameter used to adjust the impact of the execution cost on the transition probability. The larger the coefficient, the more significant the impact of the execution cost. Substitute the attenuation factor into the negative exponential function to calculate the attenuation value, that is, e^(-attenuation factor). The negative exponential function ensures that the higher the execution cost, the closer the attenuation value is to 0; the lower the execution cost, the closer the attenuation value is to 1. Multiply the basic transition probability by the attenuation value to obtain the attenuated transition probability. The attenuated transition probability comprehensively considers the historical execution pattern and execution cost, reducing the transition probability of high-cost rules and relatively increasing the transition probability of low-cost rules.

[0053] Because the sum of all transition probabilities from a specific starting rule node may no longer equal 1 after the execution cost decay mechanism is introduced, normalization is performed. All decayed transition probabilities corresponding to the starting rule node are accumulated to obtain the row normalization coefficient. Each decayed transition probability is divided by the row normalization coefficient to obtain the balanced transition probability.

[0054] Using the balanced transition probabilities, we construct a grammar rule probability transition matrix. This matrix is an N×N square matrix, where N is the number of rule nodes in the rule state set. The elements Pij in the matrix represent the probability of transitioning from rule node i to rule node j. The transition matrix satisfies the Markov property, meaning that the probability of transitioning from the current state to the next state depends only on the current state and is independent of the previous state.

[0055] After constructing the transition matrix, calculate the initial probability distribution of the rule state set. Count the frequency of each rule node being the starting rule in the historical execution sequence, as well as the sum of the frequencies of all rule nodes being the starting rule. Divide the frequency of each rule node by the total frequency to obtain the raw usage frequency.

[0056] To account for the impact of execution cost, a cost adjustment mechanism is introduced. The cost adjustment value is obtained by multiplying the comprehensive execution cost of a rule node by the adjustment coefficient, adding one, and taking the inverse. The adjustment coefficient is a system parameter used to control the degree to which the execution cost affects the initial probability. The cost adjustment value is calculated to ensure that higher execution costs have an adjustment value closer to 0, and lower execution costs have an adjustment value closer to 1. The original usage frequency is multiplied by the cost adjustment value to obtain the adjusted usage frequency. The adjusted usage frequency reduces the initial probability of high-cost rules and increases the initial probability of low-cost rules.

[0057] The adjusted usage frequencies are normalized to ensure that the sum of the initial probabilities of all rule nodes is 1, thus obtaining the initial probability distribution of the rule state set. This initial probability distribution determines the starting point of the rule sequence in the Markov decision process and directly affects subsequent rule selection and execution paths.

[0058] In this embodiment, through the Markov decision process and execution cost optimization, the optimal rule path can be adaptively selected according to the specific situation, and near-optimal performance can be obtained in different scenarios, which significantly improves the adaptability and robustness of the system. By combining historical execution frequency and execution cost analysis, excessive use of high-cost rules is avoided, and an organic combination of experience and efficiency is achieved, providing a more balanced and efficient solution. The refined optimization method still maintains good performance and resource utilization efficiency when processing large-scale or complex SQL statements, avoiding resource bottlenecks and performance crashes.

[0059] In an optional embodiment, Performing feature matching on the independent statement sequence and the optimized grammar rule sequence to generate a mapping relationship table corresponding to the parsing rule for each statement includes: Extract sentence sequence feature information from independent sentence sequences, obtain the part-of-speech sequence of each sentence to generate grammatical structure features, extract the keyword set of each sentence to generate lexical features, collect the preceding sentence information, current sentence information, and subsequent sentence information of each sentence to generate context features, and combine the grammatical structure features, lexical features, and context features to construct a sentence comprehensive feature vector; Extract the matching pattern of each rule from the optimized grammar rule sequence to form the rule pattern feature, extract the constraint condition of each rule to form the rule constraint feature, and combine the rule pattern feature and the rule constraint feature to construct the rule feature template vector; Calculate the matching degree between the sentence comprehensive feature vector and the rule feature template vector, divide the intersection cardinality of the grammatical structure features and the rule pattern features by the union cardinality to calculate the structural similarity, divide the sum of the products of the matching degrees of each keyword in the lexical feature and the rule by the number of keywords to calculate the lexical similarity, calculate the similarity between the context features and the rule constraint features to obtain the contextual similarity, and perform a weighted combination of the structural similarity, lexical similarity, and contextual similarity to obtain the comprehensive similarity; The rule with the highest comprehensive similarity is selected as the parsing rule corresponding to each statement, and the statement identifier, parsing rule identifier, and corresponding comprehensive similarity are combined to generate a mapping relationship table.

[0060] Part-of-speech (POS) tagging is performed on each SQL statement, identifying grammatical elements such as SQL keywords (such as SELECT, FROM, and WHERE), identifiers (table and field names), operators, function names, and literals. POS tagging yields a POS sequence for each SQL statement, which reflects the basic grammatical structure of the SQL statement. Combination patterns within the POS sequence can reveal the type and complexity of the SQL statement, such as simple queries, join queries, nested subqueries, or complex analytical queries.

[0061] Extract the keyword set for each SQL statement and generate lexical features. Identify core keywords in SQL statements, such as DML operation keywords (SELECT, INSERT, UPDATE, DELETE), DDL operation keywords (CREATE, ALTER, DROP), and query modifier keywords (DISTINCT, GROUP BY, HAVING, ORDER BY). Extract important identifiers from the statement, including table names, view names, field names, and aliases. Identifiers are crucial for understanding the SQL statement's operational objects and data flow. For different types of SQL statements, focus on specific functional keywords, such as aggregate functions (SUM, COUNT, AVG), window functions (ROW_NUMBER, RANK), and conditional expressions (CASE WHEN). These keywords reflect the specific operation type of the SQL statement.

[0062] Collect contextual information for each SQL statement to generate contextual features. Precursor statement information includes the content of related statements preceding the current SQL statement, table objects created or modified by the previous statement, and the output results of the previous statement. Current statement information includes the SQL statement type (query, insert, update, etc.), the primary operation object (table, view, temporary table, etc.), the complexity and nesting level of the statement, etc. Postcursor statement information includes the content of related statements following the current SQL statement and whether the current statement result is referenced by subsequent statements.

[0063] The grammatical structure features, lexical features, and contextual features are combined to construct a comprehensive feature vector for SQL statements. The comprehensive feature vector comprehensively describes the grammatical characteristics, operation types, and contextual relationships of the SQL statement, providing a multi-dimensional feature representation for subsequent rule matching.

[0064] From the optimized SQL grammar rule sequence, the matching pattern of each rule is extracted to form the rule pattern characteristics. The matching pattern of the SQL grammar rule describes the SQL statement types and structural characteristics to which the rule applies, including SQL keyword patterns, grammar structure patterns, and operation type patterns. The keyword pattern specifies the specific SQL keyword combination to which the rule applies, such as query statement rules containing the SELECT-FROM-WHERE combination. The grammar structure pattern defines the SQL grammar structure to which the rule applies, such as simple query rules, join query rules, subquery rules, and common table expression (CTE) rules. The operation type pattern specifies the SQL operation types to which the rule applies, such as data query rules, data modification rules, and table creation rules.

[0065] Extract the constraints of each rule to form the rule constraint characteristics. Constraints further define the conditions and requirements for rule application, ensuring that the rules are used correctly in appropriate situations. Constraints include database dialect constraints, syntax version constraints, and functional feature constraints. Database dialect constraints specify the database types to which the rule applies, such as MySQL rules, Oracle rules, and SQL Server rules. Syntax version constraints define the SQL syntax versions to which the rule applies, such as SQL-92 rules, SQL:2003 rules, and SQL:2011 rules. Functional feature constraints specify specific functional requirements for the rule to apply, such as window function parsing rules may only apply to SQL versions that support window functions.

[0066] Combine the rule pattern features with the rule constraint features to construct a rule feature template vector. The rule feature template vector is a mathematical representation of the rule features, including the matching conditions and constraint requirements of the rule. It is represented in the same feature space as the SQL statement comprehensive feature vector, facilitating subsequent similarity calculations and matching evaluations.

[0067] The matching degree between the SQL statement's comprehensive feature vector and the rule feature template vector is calculated, and the structural similarity between the grammatical structure features and the rule pattern features is calculated. Using the intersection-and-union ratio method from set theory, the structural similarity is calculated by dividing the cardinality of the intersection of two feature sets by the cardinality of their union. For example, for a SELECT query statement, whose grammatical structure features include elements such as SELECT, FROM, and WHERE, if the rule pattern features also include these elements, then the intersection between the two is large and the structural similarity is high.

[0068] Calculate the lexical similarity between lexical features and rule patterns. For each keyword and important identifier in the SQL statement, calculate its match with the rule. For example, for SQL statements containing aggregate functions, keywords such as GROUP BY, SUM, and COUNT may have a higher match if the rule is specifically designed to handle aggregate queries. The overall lexical similarity is calculated by multiplying the sum of the product of all keyword matches by the total number of keywords.

[0069] Calculate the contextual similarity between context features and rule constraint features. Evaluate whether the SQL statement's execution environment and contextual relationships meet the rule's constraint requirements. For example, for SQL statements that reference temporary tables, the rule must be able to handle temporary table references. For SQL statements that depend on the results of previous statements, the rule must be able to handle cross-statement dependencies.

[0070] The overall similarity is calculated by weighting structural similarity, lexical similarity, and contextual similarity. Different types of similarity can be assigned different weights based on specific application requirements, reflecting their relative importance in the rule matching process.

[0071] Based on the calculated comprehensive similarity, the most appropriate parsing rule is selected for each SQL statement. The statement's comprehensive similarity is compared with all rules, and the rule with the highest similarity is selected as the parsing rule for that statement. If multiple rules have similar comprehensive similarities, the final selection is determined through an additional priority mechanism or secondary similarity comparison. After the parsing rule is determined, the SQL statement identifier, parsing rule identifier, and corresponding comprehensive similarity are combined to generate a mapping table.

[0072] In this embodiment, by comprehensively considering multiple dimensions such as grammatical structure, keyword features and context environment, the most appropriate parsing rules are found for each SQL statement, the structure and intention of complex SQL statements can be correctly understood, and the parsing accuracy and reliability of lineage analysis are significantly improved. By constructing feature vectors and calculating multi-dimensional similarity, incomplete matching situations can be handled. For SQL statements with variant grammars or mixed characteristics, the closest parsing rules can be found. By considering the contextual relationships and dependencies of SQL statements, the data flow and reference relationships between statements can be better understood, providing more comprehensive and accurate basic information for subsequent lineage analysis.

[0073] Figure 3 Schematic diagram of the mapping process for matching SQL statements with grammar rules.

[0074] In an optional embodiment, Traversing the statement nodes in the directed graph of statement dependencies, selecting the corresponding parsing rules according to the mapping relationship table for parsing, extracting field screening conditions and calculation expressions to generate data operation sequences, identifying subquery nesting and temporary table flow to generate data flow sequences, and using data operation sequences and data flow sequences to build a field lineage link graph, including: Traverse the statement nodes in the statement dependency directed graph, extract the field identifier and operation information from each statement node to generate field screening conditions and calculation expressions, and combine the field screening conditions and calculation expressions to generate a data operation sequence; Identify data dependencies between statement nodes, extract the mapping relationship from source fields to target fields in subquery statements to generate subquery nesting, extract the table identifier of the temporary table and its transfer fields to generate temporary table flow, extract the cross-statement field identifier and its reference type to generate cross-statement reference, and combine subquery nesting, temporary table flow, and cross-statement reference to generate a data flow sequence; Construct an intra-statement field transfer path based on the data operation sequence, construct an inter-statement field transfer path based on the data flow sequence, and merge the intra-statement field transfer path and the inter-statement field transfer path to form a complete field transfer path set; The field identifier, field type and field source are extracted from the field transfer path set to construct field nodes, the mutual conversion relationship between field nodes and their conversion operations are extracted to construct conversion edges, and the field nodes and conversion edges are combined to generate a field lineage link diagram that records the complete lineage relationship of the fields.

[0075] Starting from the statement dependency directed graph, the algorithm traverses each statement node one by one, performing in-depth analysis of each node to extract field-level operation information and dependency relationships. For each statement node, the algorithm extracts field filter conditions. Field identifiers, operators, and operation values are identified from the WHERE clause, HAVING clause, and JOIN conditions to form a complete description of the filter conditions. Field identifiers can be table field names, expressions, or function call results; operators include comparison operators (e.g., equal to, greater than, less than), logical operators (e.g., AND, OR, NOT), and special operators (e.g., LIKE, IN, BETWEEN); and operation values can be constants, variables, field references, or subquery results. By identifying these elements and their combined relationships, the set of field filter conditions in the statement is constructed.

[0076] Extract calculation expressions, identifying the components involved in field calculations from the SELECT clause, WHERE clause, HAVING clause, and other expression locations. Extract field identifiers, function identifiers, and function parameters, and analyze the expression's calculation logic and dependencies. Field calculations may involve arithmetic operations (addition, subtraction, multiplication, and division), string manipulation (concatenation, truncation, and substitution), date processing (formatting, calculating intervals), or conditional expressions (CASE WHEN). For complex nested expressions, recursive analysis is performed to ensure that all calculation components and dependencies are correctly extracted.

[0077] Extract aggregate functions, identifying field identifiers, aggregate function types, and grouping conditions from GROUP BY clauses and aggregate function calls. Common aggregate functions include SUM, COUNT, AVG, MAX, MIN, and others, which aggregate multiple rows of data into a single result. The grouping conditions define how the data is grouped and typically come from the list of fields specified in the GROUP BY clause.

[0078] Extract window functions and identify the field identifier, window function type, and partition condition from the window function call. Window functions include ranking functions (ROW_NUMBER, RANK, DENSE_RANK), aggregate window functions (SUM OVER, AVGOVER), and offset functions (LAG, LEAD). The partition condition defines the scope of the window function and typically comes from the PARTITION BY clause and ORDER BY clause.

[0079] The extracted field screening conditions, calculation expressions, aggregate functions, and window functions are combined to generate a complete data operation sequence. Based on an understanding of the field operations within a single statement, the data dependencies between statement nodes are identified, subquery nesting relationships are extracted, the subquery structure within the SQL statement is analyzed, and the mapping relationship between the subquery's source and target fields is identified. Subqueries may appear in the FROM clause, WHERE clause, SELECT clause, and other locations, and the field mapping relationship may vary in each case. For example, a derived table in the FROM clause creates a direct mapping from source table fields to target fields, while a subquery in the WHERE clause may establish conditional associations between fields. By analyzing the structure and context of the subquery, complete field mapping information is extracted.

[0080] Extract temporary table flow information, identify instances of temporary table creation and use in SQL statements, and extract temporary table identifiers and transfer fields. Temporary tables serve as intermediate storage and data transfer during data processing. Understanding how fields are transferred from one statement to another through temporary tables is crucial for establishing a complete lineage relationship. Temporary tables may be created explicitly (e.g., CREATE TEMPORARY TABLE) or implicitly (e.g., Common Table Expressions (CTEs)). It is necessary to identify all types of temporary tables and their field transfer paths.

[0081] Extract cross-statement references, analyze field reference relationships between different SQL statements, and identify cross-statement field identifiers and reference types. Cross-statement references can be direct (e.g., a subsequent statement references a table created by a preceding statement), indirect (e.g., through a temporary table or view), or conditional (e.g., in an EXISTS clause). By analyzing inter-statement dependencies and reference patterns, complete cross-statement field reference information is extracted.

[0082] The extracted subquery nesting, temporary table transfer, and cross-statement references are combined to generate a data transfer sequence. The data transfer sequence describes the data transfer and conversion path between different SQL statements, providing a basis for building cross-statement field lineage relationships.

[0083] Based on the sequence of data operations, the statement's field transfer path is constructed. Field dependencies within field screening conditions, calculation expressions, aggregate functions, and window functions are analyzed to identify the transfer and conversion processes between fields. For example, in a calculation expression, the result field depends on the source fields used in the expression; in an aggregate function, the aggregation result depends on the aggregated field and the grouping field; and in a window function, the calculation result depends on the window function's parameter fields and partitioning fields. Dependencies are used to construct a directed graph describing the field transfer path within a single statement.

[0084] Based on the data flow sequence, the inter-statement field transfer path is constructed. Field mapping relationships in nested subqueries, temporary table transfers, and cross-statement references are analyzed to identify the field transfer and conversion processes between different statements. For example, when one statement creates a temporary table and another statement queries it, a mapping relationship exists between the output fields of the source statement and the input fields of the target statement. When a subquery in one statement references the results of another statement, a dependency relationship also exists between the related fields of the two statements. Using these mapping relationships, a directed graph is constructed that describes the field transfer path between different statements.

[0085] The intra-statement field transfer path and the inter-statement field transfer path are merged to form a complete field transfer path set. The field transfer path set includes the transfer and conversion relationships between all fields in the entire SQL script.

[0086] Extract field node information from the field transfer path collection, including field identifiers, field types, and field sources. A field identifier uniquely identifies a field and includes a table name or alias prefix. A field type describes the field's data type, such as integer, string, or date. A field source describes the source of the field's data, such as the original table field, calculated field, aggregated result, or temporary table field. These fields are organized into field nodes, which serve as the basic unit of the lineage link diagram.

[0087] Extract transformation edge information from the field transfer path set, including the mutual transformation relationships and transformation operations between field nodes. The transformation relationship describes the mapping method from the source field to the target field, which may be direct mapping, calculation transformation, aggregation transformation, or conditional filtering. The transformation operation describes the specific operation that implements this transformation, such as arithmetic operation, function call, aggregation calculation, or window function. These are woven into transformation edges to describe the connection relationship between field nodes.

[0088] Field nodes and transformation edges are combined to generate a complete field lineage graph. This graph is a directed graph, with nodes representing fields and edges representing transformations between fields. It comprehensively records field-level lineage relationships and data flow paths throughout the SQL script. This graph allows you to track the source and destination of fields and understand changes and transformations in data during processing.

[0089] In this embodiment, by analyzing SQL statements in detail and extracting information such as field screening conditions, calculation expressions, aggregate functions and window functions, a field-level lineage relationship diagram is constructed, which can accurately track the source, conversion path and final destination of each field, providing more refined support for data governance and quality management. By identifying complex relationships such as subquery nesting, temporary table flow and cross-statement references, it can accurately capture field dependencies in multi-level and multi-step data processing processes, so that lineage analysis can cope with various complex scenarios in actual business. By extracting and recording conversion operations between fields, it can not only display the source, destination and conversion method of the data, but also by constructing a complete field lineage chain diagram, it can quickly identify the potential impact of upstream changes on downstream applications, or trace the upstream root causes of downstream problems, greatly improving the security of data changes and the efficiency of problem troubleshooting.

[0090] In an optional embodiment, Structural processing of the field lineage link graph to obtain lineage analysis results and outputting them into a specified format includes: Extract the lineage hierarchical relationship of field nodes from the field lineage link graph, identify the direct predecessor node and direct successor node of each field node, build a lineage transmission link, and group the field nodes according to the lineage hierarchical relationship to form a lineage hierarchical structure; Traversing the field nodes in the lineage hierarchical structure, extracting the conversion operation type and conversion parameters in the conversion edge corresponding to each field node, and combining the lineage hierarchical relationship of the field node with the conversion operation information to form a lineage analysis result; The field node identifiers, lineage levels, conversion operations, predecessor nodes, and successor nodes in the lineage analysis results are reorganized and output according to a preset structured format.

[0091] Extract the hierarchical lineage relationships between field nodes, analyze the graph topology, and identify the direct predecessor and successor nodes of each field node. A predecessor node is an upstream field that directly provides data to the current node, representing the source of the data; a successor node is a downstream field that directly receives data from the current node, indicating the destination of the data. By traversing all transition edges in the graph, a complete mapping of predecessor and successor relationships between nodes is established. For each field node, the set of all its direct predecessor nodes and the set of all its direct successor nodes are recorded to form a local lineage view of the node.

[0092] Based on the predecessor-successor relationship, a lineage transmission link is constructed between fields. Starting from a source field without a predecessor node, the link is gradually transferred downstream along the transformation edge to form a complete lineage path. Multiple different lineage paths may exist regardless of the field. For example, when a field is generated by multiple source fields through different transformation paths, all possible lineage paths need to be recorded to ensure the integrity of the lineage analysis. At the same time, possible circular dependencies in the lineage chain are identified. This situation may occur in recursive queries or iterative calculations and needs to be handled separately to avoid infinite loops.

[0093] Based on the lineage transmission link, the lineage level of each field node is determined. Starting from the source field, fields without predecessor nodes are defined as the first level, and the level number is passed down layer by layer according to the data flow. If a field has multiple predecessor nodes at different levels, the level of the field is generally determined by the level of its deepest predecessor node plus one, ensuring that the hierarchical structure reflects the actual flow order of the data. In cases where there are multiple paths, it may be necessary to consider the level differences under different paths and select the most appropriate level representation.

[0094] Field nodes are grouped according to a certain lineage level to form a clear lineage hierarchy structure. The hierarchy structure organizes all fields into an orderly hierarchical system, and each level contains field nodes with the same lineage depth.

[0095] After building the lineage hierarchy, we traverse each field node to extract more detailed transformation operation information. We extract the transformation operation type and parameters from the transformation edges associated with the field node. The transformation operation type describes the basic method of data transformation, such as direct mapping, calculation transformation, aggregation transformation, and filtering transformation. The transformation parameters provide the specific details of the operation, such as the calculation formula, aggregation function, and filtering conditions.

[0096] For complex transformation operations, further analysis of the internal structure and logic is required. For example, a calculation expression containing multiple steps needs to be broken down into basic operation units; for nested conditional expressions, each branch condition and corresponding result needs to be identified.

[0097] The lineage hierarchy of field nodes is combined with the transformation operation information to form a complete lineage analysis result. The lineage analysis result includes basic information about each field (such as field identifier and field type), lineage relationship information (such as predecessor node, successor node, lineage hierarchy), and transformation information (such as transformation operation type and transformation parameters).

[0098] Reorganize and output the results of the lineage analysis in a preset structured format. Select an appropriate output format, such as a relational table, hierarchical tree structure, or network graphic representation, based on actual needs and application scenarios. For the table format, it includes columns such as field identifier, table to which it belongs, field type, lineage hierarchy, predecessor field list, conversion operation description, and successor field list; for the tree structure, the field nodes are organized according to the lineage hierarchy to display the derived relationship between fields; for the network graph, the complex connection relationships and conversion operations between fields are intuitively displayed. Different representation methods are suitable for different analysis needs and usage scenarios, and can be flexibly selected according to the specific situation.

[0099] In this embodiment, the hierarchical representation makes complex data processing flows intuitive and easy to understand, enabling users to easily understand the life cycle and transformation process of data, providing a global perspective for data asset management, and by supporting multiple structured output formats such as tables, tree structures, and network graphics, it can meet the needs of different user groups and application scenarios. By fully recording the lineage and transformation process of data, it provides an infrastructure for data quality management and provides comprehensive and in-depth technical support for modern data governance and intelligent data management.

[0100] A second aspect of an embodiment of the present invention provides a multi-database SQL lineage analysis system based on G4 files, comprising: The first unit is used to obtain the grammatical rule set of SQL statements based on the G4 grammar file, perform lexical cleaning and normalization processing on the SQL statements, split them by semicolons to obtain independent statement sequences, and analyze the independent statement sequences to construct a statement dependency directed graph that describes the dependencies between the previous and next statements. The second unit is configured to obtain database configuration, operating parameters, and temporary table information to form basic parsing data, use the basic parsing data to construct a dialect feature matrix describing the grammatical features of the database, establish a grammatical rule probability transition matrix based on the dialect feature matrix, predict the optimal rule chain in the probability transition matrix through a Markov decision process to obtain an optimized grammatical rule sequence, perform feature matching on the independent statement sequence and the optimized grammatical rule sequence, and generate a mapping relationship table corresponding to the parsing rules for each statement; The third unit is used to traverse the statement nodes in the statement dependency directed graph, select the corresponding parsing rules according to the mapping relationship table, extract the field screening conditions and calculation expressions to generate data operation sequences, identify subquery nesting and temporary table flow to generate data flow sequences, and use the data operation sequences and data flow sequences to construct a field lineage link diagram; The fourth unit is used to process the field blood relationship link diagram in a structural manner to obtain a blood relationship analysis result and output it in a specified format.

[0101] According to a third aspect of an embodiment of the present invention, an electronic device is provided, including: A processor and a memory for storing processor-executable instructions, wherein the processor is configured to call the instructions stored in the memory to execute the aforementioned method.

[0102] According to a fourth aspect of an embodiment of the present invention, a computer-readable storage medium is provided, on which computer program instructions are stored. When the computer program instructions are executed by a processor, the method described above is implemented.

[0103] The present invention may be a method, an apparatus, a system and / or a computer program product. The computer program product may include a computer-readable storage medium carrying computer-readable program instructions for executing various aspects of the present invention.

[0104] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it. Although the present invention has been described in detail with reference to the above embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the above embodiments, or replace some or all of the technical features therein with equivalents. However, these modifications or replacements do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present invention.

Claims

1. A multi-database SQL lineage parsing method based on G4 files, characterized by: include: Obtain the grammatical rule set for SQL statements based on the G4 grammar file, perform lexical cleaning and normalization on the SQL statements, and split them by semicolons to obtain a sequence of independent statements. Analyze the independent statement sequence to construct a statement dependency directed graph that describes the dependencies between the previous and next statements. Obtaining database configuration, operating parameters, and temporary table information to form basic parsing data, using the basic parsing data to construct a dialect feature matrix describing database grammatical features, establishing a grammatical rule probability transition matrix based on the dialect feature matrix, predicting an optimal rule chain in the probability transition matrix through a Markov decision process to obtain an optimized grammatical rule sequence, performing feature matching between the independent statement sequence and the optimized grammatical rule sequence, and generating a mapping relationship table corresponding to the parsing rules for each statement; Traverse the statement nodes in the statement dependency directed graph, select the corresponding parsing rules based on the mapping relationship table for parsing, extract field screening conditions and calculation expressions to generate data operation sequences, identify subquery nesting and temporary table flow to generate data flow sequences, and use data operation sequences and data flow sequences to build a field lineage link diagram; The field lineage link graph is structured to obtain a lineage analysis result and output it in a specified format.

2. The method according to claim 1, characterized in that Obtain the grammatical rule set for SQL statements based on the G4 grammar file, perform lexical cleaning and normalization on the SQL statements, and split them by semicolon to obtain a sequence of independent statements. Analyze the independent statement sequence to construct a statement dependency directed graph that describes the dependencies between the previous and next statements, including: Parse the G4 grammar file, read the grammar definition file content, build the lexical analyzer and syntax analyzer, and generate a set of standard grammar rules for SQL statements; Receive an SQL statement to be processed, perform lexical analysis using the grammar rule set, identify keywords, identifiers, operators, and literals in the SQL statement, remove redundant whitespace characters and comments, unify capitalization and line breaks to obtain a normalized SQL text, and split the normalized SQL text into a sequence of independent statements based on semicolon delimiters; Traverse the independent statement sequence, parse the grammatical structure of each statement based on the grammatical rule set, extract the table name, field name and temporary table definition in the statement, identify the data references and dependencies between statements, construct each statement into a statement node containing statement content and reference information, establish directed connections between nodes based on the data references and dependencies, and generate a statement dependency directed graph that records the execution process of the SQL statement.

3. The method according to claim 1, characterized in that Obtaining database configuration, operating parameters, and temporary table information to form parsing basic data, using the parsing basic data to construct a dialect feature matrix describing database grammatical features, establishing a grammatical rule probability transition matrix based on the dialect feature matrix, and predicting the optimal rule chain in the probability transition matrix through a Markov decision process to obtain an optimized grammatical rule sequence including: Obtain database configuration and operating parameters, standardize the connection strings in the database configuration and operating parameters, parse the execution plan of the SQL statement to obtain intermediate results and temporary table information, and organize the operating parameters, intermediate results and temporary table information into basic parsing data; Construct a feature vector space, which includes the dimensions of function syntax features, keyword usage features, and grammatical structure features. Based on the parsed basic data, calculate the frequency of occurrence and inverse document frequency of features in different databases. Determine feature weight scores based on the frequency of occurrence and inverse document frequency, and populate the feature weight scores into the dialect feature matrix. A rule state set is constructed based on the dialect feature matrix. The transition probabilities between rule nodes in the rule state set are calculated to form a grammatical rule probability transfer matrix. The initial probability distribution of the rule state set is calculated according to the historical usage frequency of the rules. Based on the grammatical rule probability transfer matrix and the initial probability distribution, the state value function is iteratively calculated using the Bellman equation. The action sequence that maximizes long-term benefits is selected to form the optimal rule chain. Redundant rules are eliminated on the optimal rule chain to obtain the optimized grammatical rule sequence.

4. The method according to claim 3, characterized in that The rule state set is constructed based on the dialect feature matrix, the transition probabilities between the rule nodes in the rule state set are calculated to form the grammatical rule probability transition matrix, and the initial probability distribution of the rule state set is calculated based on the historical usage frequency of the rules. Obtain the dialect feature matrix and construct a rule state set. Extract the basic operation complexity of each rule node in the rule state set and sum them to obtain the time complexity value. Read the memory usage value and processor utilization value of the rule node and add them to obtain the resource consumption value. Add the time complexity value and resource consumption value to calculate the comprehensive execution cost of the rule node. Traverse the rule nodes in the rule state set, select two adjacent rule nodes in the rule execution sequence as the starting rule node and the target rule node to form a rule node pair, count the historical execution frequencies of the rule node pair, calculate the sum of the historical execution frequencies of the target rule nodes corresponding to the starting rule node, divide the historical execution frequencies by the sum of the historical execution frequencies to obtain the basic transition probability, multiply the comprehensive execution cost of the target rule node by the attenuation coefficient, and then subtract the result from the negative exponential function to obtain the attenuation value, which is then multiplied by the basic transition probability to obtain the attenuated transition probability; The original usage frequency is calculated by counting the historical execution frequency and the sum of the rule nodes. The comprehensive execution cost of the rule node is multiplied by the adjustment coefficient, added with one and the inverse is taken to obtain the cost adjustment value. The original usage frequency is multiplied by the cost adjustment value and normalized to obtain the initial probability distribution of the rule state set. The attenuated transition probability is divided by the row normalization coefficient to construct the grammar rule probability transition matrix.

5. The method according to claim 1, wherein Performing feature matching on the independent statement sequence and the optimized grammar rule sequence to generate a mapping relationship table corresponding to the parsing rule for each statement includes: Extract sentence sequence feature information from independent sentence sequences, obtain the part-of-speech sequence of each sentence to generate grammatical structure features, extract the keyword set of each sentence to generate lexical features, collect the preceding sentence information, current sentence information, and subsequent sentence information of each sentence to generate context features, and combine the grammatical structure features, lexical features, and context features to construct a sentence comprehensive feature vector; Extract the matching pattern of each rule from the optimized grammar rule sequence to form the rule pattern feature, extract the constraint condition of each rule to form the rule constraint feature, and combine the rule pattern feature and the rule constraint feature to construct the rule feature template vector; Calculate the matching degree between the sentence comprehensive feature vector and the rule feature template vector, divide the intersection cardinality of the grammatical structure features and the rule pattern features by the union cardinality to calculate the structural similarity, divide the sum of the products of the matching degrees of each keyword in the lexical feature and the rule by the number of keywords to calculate the lexical similarity, calculate the similarity between the context features and the rule constraint features to obtain the contextual similarity, and perform a weighted combination of the structural similarity, lexical similarity, and contextual similarity to obtain the comprehensive similarity; The rule with the highest comprehensive similarity is selected as the parsing rule corresponding to each statement, and the statement identifier, parsing rule identifier, and corresponding comprehensive similarity are combined to generate a mapping relationship table.

6. The method according to claim 1, characterized in that Traversing the statement nodes in the directed graph of statement dependencies, selecting the corresponding parsing rules according to the mapping relationship table for parsing, extracting field screening conditions and calculation expressions to generate data operation sequences, identifying subquery nesting and temporary table flow to generate data flow sequences, and using data operation sequences and data flow sequences to build a field lineage link graph, including: Traverse the statement nodes in the statement dependency directed graph, extract the field identifier and operation information from each statement node to generate field screening conditions and calculation expressions, and combine the field screening conditions and calculation expressions to generate a data operation sequence; Identify data dependencies between statement nodes, extract the mapping relationship from source fields to target fields in subquery statements to generate subquery nesting, extract the table identifier of the temporary table and its transfer fields to generate temporary table flow, extract the cross-statement field identifier and its reference type to generate cross-statement reference, and combine subquery nesting, temporary table flow, and cross-statement reference to generate a data flow sequence; Construct an intra-statement field transfer path based on the data operation sequence, construct an inter-statement field transfer path based on the data flow sequence, and merge the intra-statement field transfer path and the inter-statement field transfer path to form a complete field transfer path set; The field identifier, field type and field source are extracted from the field transfer path set to construct field nodes, the mutual conversion relationship between field nodes and their conversion operations are extracted to construct conversion edges, and the field nodes and conversion edges are combined to generate a field lineage link diagram that records the complete lineage relationship of the fields.

7. The method according to claim 1, characterized in that Structural processing of the field lineage link graph to obtain lineage analysis results and outputting them into a specified format includes: Extract the lineage hierarchical relationship of field nodes from the field lineage link graph, identify the direct predecessor node and direct successor node of each field node, build a lineage transmission link, and group the field nodes according to the lineage hierarchical relationship to form a lineage hierarchical structure; Traversing the field nodes in the lineage hierarchical structure, extracting the conversion operation type and conversion parameters in the conversion edge corresponding to each field node, and combining the lineage hierarchical relationship of the field node with the conversion operation information to form a lineage analysis result; The field node identifiers, lineage levels, conversion operations, predecessor nodes, and successor nodes in the lineage analysis results are reorganized and output according to a preset structured format.

8. A multi-database SQL lineage analysis system based on G4 files, used to implement the method according to any one of claims 1 to 7, characterized in that: include: The first unit is used to obtain the grammatical rule set of SQL statements based on the G4 grammar file, perform lexical cleaning and normalization processing on the SQL statements, split them by semicolons to obtain independent statement sequences, and analyze the independent statement sequences to construct a statement dependency directed graph that describes the dependencies between the previous and next statements. The second unit is configured to obtain database configuration, operating parameters, and temporary table information to form basic parsing data, use the basic parsing data to construct a dialect feature matrix describing the grammatical features of the database, establish a grammatical rule probability transition matrix based on the dialect feature matrix, predict the optimal rule chain in the probability transition matrix through a Markov decision process to obtain an optimized grammatical rule sequence, perform feature matching on the independent statement sequence and the optimized grammatical rule sequence, and generate a mapping relationship table corresponding to the parsing rules for each statement; The third unit is used to traverse the statement nodes in the statement dependency directed graph, select the corresponding parsing rules according to the mapping relationship table, extract the field screening conditions and calculation expressions to generate data operation sequences, identify subquery nesting and temporary table flow to generate data flow sequences, and use the data operation sequences and data flow sequences to construct a field lineage link diagram; The fourth unit is used to process the field blood relationship link diagram in a structural manner to obtain a blood relationship analysis result and output it in a specified format.

9. An electronic device, characterized in that: include: processor; a memory for storing processor-executable instructions; The processor is configured to call the instructions stored in the memory to execute the method according to any one of claims 1 to 7.

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

Images