SQL (Structured Query Language) blood relationship analysis method, system, equipment and medium

Abstract

The invention discloses an SQL blood relationship analysis method, system and device and a medium, and relates to the technical field of data processing. According to the method, a standardized blood relationship sample is generated through multi-dialect SQL script labeling, and a pre-training large model is finely adjusted to obtain a blood relationship analysis large model; generating an abstract syntax tree for the original SQL, extracting constraint prompt blocks, constructing prompt words in combination with SQL statements, and inputting the prompt words into a large model to obtain a structured blood relationship intermediate representation; static blood relationship extraction is executed in parallel, conflicts between the two are resolved through a decision tree, and a standardized blood relationship analysis result is output. According to the method, large model semantic comprehension and static analysis preciseness are fused, the problems that complex and multi-dialect SQL blood relationship analysis is low in accuracy, incomplete in coverage and poor in reliability are solved, and the analysis quality is improved.

Description

Technical Field

[0001] This invention relates to the field of data processing technology, specifically to an SQL lineage parsing method, system, device, and medium. Background Technology

[0002] With the popularization of big data and cloud computing technologies, data has become a core asset for enterprise decision-making and operations. Structured Query Language (SQL), as the mainstream tool for data access, processing, and analysis, contains massive data flow relationships (data lineage) in its execution scripts, which are crucial for data quality assurance, change impact analysis, problem tracing, and compliance auditing. Field-level lineage, as the finest parsing granularity, can accurately reveal the source and transformation path of data fields.

[0003] Early lineage resolution techniques primarily relied on static parsing. This method constructs an Abstract Syntax Tree (AST) of the SQL statement and traverses the tree nodes to deduce the dependencies between fields. Such methods are highly efficient and accurate when processing syntactically sound and structurally simple SQL. However, in real-world industrial applications, the complexity of SQL far exceeds standard paradigms. Static analysis methods suffer a sharp decline in parsing capability and coverage when faced with multi-level nested subqueries, Common Table Expressions (CTEs), window functions, dynamic SQL, and dialects specific to different database systems (such as Hive, Spark, and Doris), often leading to parsing failures or significant omissions of lineage relationships.

[0004] To compensate for the shortcomings of static analysis, rule-based parsing engines have been proposed. However, such systems rely on a large and continuously iterative expert rule base to match various SQL patterns. Their drawbacks are also quite obvious: the maintenance cost of the rules is extremely high, and they can never fully cover all marginal, non-standard, or newly emerging SQL syntax variations, thus limiting the system's scalability and generalization capabilities.

[0005] In recent years, generative artificial intelligence technologies, represented by Large Language Models (LLM), have demonstrated powerful code understanding and generation capabilities, providing new ideas for SQL lineage parsing. However, their core flaw lies in the "hallucination" problem, which means that the model may generate tables or fields that do not exist in the original SQL context, or incorrectly establish dependencies.

[0006] Therefore, there is an urgent need for a method that combines the advantages of LLM semantic understanding with the rigor of traditional technologies to achieve high-precision, high-reliability field-level lineage parsing of complex, multi-dialect SQL. Summary of the Invention

[0007] This invention provides a method, system, device, and medium for SQL lineage parsing, addressing the problems of low accuracy, incomplete coverage, and poor reliability in existing technologies for parsing complex, multi-dialect SQL lineages. The method constructs a deep collaborative parsing framework based on preprocessing, collaborative parsing, conflict resolution, and closed-loop optimization. The core of this framework lies in: firstly, using a large language model specifically fine-tuned for the lineage parsing task to perform preliminary semantic parsing of SQL statements; secondly, extracting strong constraint information from the SQL structure through static syntax analysis to guide and verify the output of the large language model; thirdly, designing a dynamic decision-making mechanism to intelligently adjudicate conflicts arising between semantic parsing and syntax analysis; and fourthly, combining lineage invariance verification rules to generate a quantitative confidence score for each extracted lineage relationship, thereby achieving closed-loop optimization of the parsing process and evaluation of result reliability.

[0008] This invention is achieved through the following technical solution:

[0009] A first aspect of the present invention provides an SQL lineage parsing method, comprising:

[0010] Obtain multi-dialect SQL scripts, annotate each SQL script with a predefined format, and obtain standardized lineage samples;

[0011] The pre-trained large language model was supervised and fine-tuned using the aforementioned kinship samples to obtain a large kinship resolution model.

[0012] Perform syntax parsing on the original SQL statement to generate an abstract syntax tree;

[0013] The symbol table, scope, and alias mapping information are extracted from the abstract syntax tree, and the symbol table, scope, and alias mapping information are encoded into constraint hint blocks;

[0014] Using the original SQL statement, constraint hint block, task instructions, and structured output requirements, a hint word is constructed and input into the bloodline parsing model to obtain a structured bloodline intermediate representation;

[0015] The original SQL statement is subjected to a rule-based static lineage extraction algorithm to obtain static lineage results;

[0016] The structured bloodline intermediate representation is compared with the static bloodline result by field comparison. For bloodline relationships that do not match, a decision tree is used to resolve conflicting bloodlines, and the bloodline parsing result of the original SQL statement is obtained.

[0017] Furthermore, each of the bloodline samples contains the bloodline relationship for each field in the corresponding SQL script, wherein each bloodline relationship is stored as a key-value pair, and the storage objects include: target table, target field, source table, and source field.

[0018] Furthermore, before extracting the symbol table, scope, and alias mapping information from the abstract syntax tree, the method further includes:

[0019] Calculate the complexity of the abstract syntax tree. If the complexity exceeds a set threshold, divide the original SQL statement into blocks to generate at least two sub-SQL blocks. Assign a unique block identifier to each sub-SQL block and record the dependency relationship. Regenerate the abstract syntax tree for each sub-SQL block.

[0020] Furthermore, before comparing the structured kinship intermediate representation with the static kinship result by field, the method further includes:

[0021] Verify whether the structured bloodline intermediate representation conforms to the predefined format;

[0022] Verify whether the source table and source field in the structured bloodline intermediate representation can be found in the constraint hint block;

[0023] If any check fails, the error message is fed back to the bloodline parsing model, and a corrected structured bloodline intermediate representation is regenerated; if all checks pass, the structured bloodline intermediate representation is compared with the static bloodline result by field comparison.

[0024] Furthermore, the decision tree executes each decision item sequentially according to a preset priority until a decision result is obtained, and the conflicting lineage is resolved based on the decision result; wherein, the decision items include:

[0025] First priority: If a lineage relationship in the static lineage result has clear path evidence in the abstract syntax tree, while the structured lineage result indicates a missing or insufficient corresponding relationship, then the static lineage result shall be adopted.

[0026] Second priority: Apply the expert rule base to adjudicate conflicts and adopt the blood relationship verified by the expert rule base;

[0027] Third priority: Use alias mapping information to perform precise scope and reachability verification on conflicting fields, and adopt the verified lineage relationship;

[0028] Fourth priority: Construct an enhanced hint containing conflict descriptions and negative examples, requiring the lineage resolution model to adjudicate the conflict points and provide explanations.

[0029] Furthermore, after obtaining the lineage parsing result of the original SQL statement, the method also includes performing a lineage invariance check on each lineage relationship in the lineage parsing result;

[0030] The bloodline invariance check is achieved by applying a logical equivalence transformation to the original SQL statement, re-parseing it, and comparing whether the bloodline relationship has changed.

[0031] If the blood relationship changes, the confidence score of the blood relationship will be downweighted according to the type and severity of the change.

[0032] Furthermore, the method also includes:

[0033] Sort each blood relationship according to its confidence score and output a standardized list of blood relationships including source, confidence score and resolution explanation;

[0034] A visual kinship graph is constructed using databases, tables, and fields as nodes and blood relations as edges.

[0035] A second aspect of the present invention provides an SQL lineage resolution system, comprising:

[0036] The model pre-training module is configured to use standardized kinship samples to perform supervised fine-tuning training on the pre-trained large language model to obtain the kinship resolution large model;

[0037] The preprocessing and analysis module is configured to perform syntax parsing on the original SQL statement, generate an abstract syntax tree, extract symbol table, scope and alias mapping information from the abstract syntax tree, and encode the symbol table, scope and alias mapping information into constraint hint blocks;

[0038] The large model parsing module is configured to construct prompt words using the original SQL statement, constraint prompt block, task instructions and structured output requirements, and input them into the bloodline parsing large model to obtain a structured bloodline intermediate representation;

[0039] The static lineage parsing module is configured to execute a rule-based static lineage extraction algorithm on the original SQL statement to obtain static lineage results;

[0040] The conflict resolution module is configured to compare the structured lineage intermediate representation with the static lineage result by field comparison, and to use a decision tree to resolve conflicting lineages for lineage relationships that do not match, thereby obtaining the lineage parsing result of the original SQL statement.

[0041] A third aspect of the present invention provides an electronic device including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor, when executing the computer program, implements the SQL lineage parsing method according to any one of the first aspects of the present invention.

[0042] A fourth aspect of the present invention provides a computer-readable storage medium having a computer program stored thereon, wherein the computer program, when executed by a processor, implements the SQL lineage resolution method according to any one of the first aspects of the present invention.

[0043] Compared with the prior art, the present invention has the following advantages and beneficial effects:

[0044] This invention's lineage parsing method organically integrates fine-tuning of a large language model with static parsing techniques. It trains a lineage parsing model adapted to multiple dialects and complex scenarios through supervised fine-tuning, uses abstract syntax trees to extract constraint information to guide model reasoning, and combines static lineage extraction results with a decision tree conflict resolution mechanism to effectively compensate for the shortcomings of single techniques. It leverages the deep understanding of complex semantics provided by LLM while avoiding model illusion risks through the rigor of static analysis. This achieves accurate field-level lineage parsing for complex SQL queries involving multiple nesting layers and multiple dialects, significantly improving the accuracy, coverage, and reliability of parsing, and meeting the stringent requirements of data governance for lineage parsing. Attached Figure Description

[0045] To more clearly illustrate the technical solutions of the exemplary embodiments of the present invention, the accompanying drawings used in the embodiments will be briefly described below. It should be understood that the following drawings only show some embodiments of the present invention and should not be considered as a limitation of the scope. For those skilled in the art, other related drawings can be obtained based on these drawings without creative effort. In the drawings:

[0046] Figure 1 This is a flowchart of an SQL lineage parsing method according to an embodiment of the present invention;

[0047] Figure 2 This is a schematic diagram of the conflict resolution logic of a decision tree according to an embodiment of the present invention;

[0048] Figure 3 This is a visual flowchart of an embodiment of the present invention;

[0049] Figure 4 This is a structural diagram of an SQL lineage parsing system according to an embodiment of the present invention. Detailed Implementation

[0050] To make the objectives, technical solutions, and advantages of the present invention clearer, the present invention will be further described in detail below with reference to the embodiments and accompanying drawings. The illustrative embodiments and descriptions of the present invention are only used to explain the present invention and are not intended to limit the present invention.

[0051] It should be noted that the terms "comprising" and "having" and any variations thereof in the specification, claims, and accompanying drawings of this invention are intended to cover non-exclusive inclusion, for example, a process, method, system, product, or device that includes a series of steps or units is not necessarily limited to other steps or units inherent in the device.

[0052] The terminology used in the various embodiments of the invention is for the purpose of describing particular embodiments only and is not intended to limit the various embodiments of the invention. As used herein, the singular form is intended to include the plural form as well, unless the context clearly indicates otherwise. Unless otherwise defined, all terms used herein (including technical and scientific terms) have the same meaning as commonly understood by one of ordinary skill in the art to which the various embodiments of the invention pertain. The terms (such as those defined in commonly used dictionaries) are to be interpreted as having the same meaning as in the context of the relevant technical field and are not to be interpreted as having an idealized or overly formal meaning, unless clearly defined in the various embodiments of the invention.

[0053] This invention aims to address the shortcomings of existing SQL lineage parsing technologies, such as low accuracy, incomplete coverage, and poor reliability when dealing with complex, multi-dialect SQL. In particular, it addresses the issue that a single technical approach cannot simultaneously achieve both semantic understanding flexibility and logical verification rigor. The invention proposes a SQL lineage parsing method, system, device, and medium. Its core lies in utilizing a large language model specifically tuned for lineage parsing tasks for initial semantic parsing. Simultaneously, it extracts strong constraint information through static syntactic analysis to guide and verify the LLM output. Furthermore, a dynamic decision-making mechanism is designed to intelligently resolve conflicts between the two, making it suitable for complex, multi-dialect SQL lineage parsing scenarios and improving the accuracy of the parsing results.

[0054] Please see Figure 1 The SQL lineage parsing method proposed in this invention includes the following steps.

[0055] S100: Obtain multi-dialect SQL scripts, label each SQL script with a predefined format to obtain standardized lineage samples, and use the lineage samples to perform supervised fine-tuning training on the pre-trained large language model to obtain the lineage resolution large model.

[0056] The sample data comes from multi-dialect databases, such as MySQL, PostgreSQL, HiveQL, Spark SQL, Redshift, and Doris. The extracted SQL scripts need to cover a variety of complex scenarios, including multi-table joins, deeply nested queries, common table expressions (CTEs), user-defined functions (UDFs), and window functions.

[0057] Each extracted SQL script is standardized and labeled to generate lineage samples for fine-tuning training of a large model. The labeling is accurate to the field level of lineage relationship and is standardized to a predefined data format.

[0058] Collect a sufficient number of lineage samples and use the parameter efficient fine-tuning technique (LoRA) to supervise the fine-tuning of the pre-trained large language model, so that it learns the ability to map from SQL statements to structured lineage relationships. That is, when a new SQL statement is input into the large model, it can generate the parsing result of the lineage relationship of the SQL statement in a predefined format according to the task prompt.

[0059] S200 performs syntax parsing on the original SQL statement, generates an abstract syntax tree, extracts symbol table, scope, and alias mapping information from the abstract syntax tree, and encodes the symbol table, scope, and alias mapping information into constraint hint blocks.

[0060] The raw SQL statement is the SQL statement to be parsed. It can be an SQL script from a lineage sample or a newly written SQL statement. Before syntax parsing, there may be preprocessing steps to standardize the input raw SQL statement, such as comment cleaning, case unification, and dialect normalization conversion.

[0061] This step utilizes a syntax parser to parse the original SQL statement or the preprocessed original SQL statement, generating an abstract syntax tree (AST) of the original SQL. Then, structured information such as symbol tables, scopes, and alias mappings are extracted from the AST and encoded into large model constraint-driven hint blocks to constrain and guide the reasoning process of the lineage large model.

[0062] In some specific implementations, the total number of tokens in the input original SQL statement may exceed the maximum context length of the lineage parsing large model. In this case, before performing syntax parsing on the original SQL statement, the total number of tokens in the original SQL statement is checked. If it exceeds the acceptance range of the large model, an adaptive block splitting procedure is triggered to split the original SQL statement into at least two independent sub-SQL blocks for separate processing.

[0063] The block splitting method can be as follows: for statements containing UNION or UNION ALL, split each subquery into independent blocks along the UNION keyword; for complex statements without UNION, split the field list into two parts according to the number of fields N in its outermost SELECT clause, following the rule of N / 2, and copy the remaining clauses such as FROM and WHERE to generate two simpler sub-SQL blocks.

[0064] The syntax parser is used to parse the original SQL statement or each sub-SQL block of the original SQL statement separately, generate the corresponding abstract syntax tree, and perform subsequent processing on each individual abstract syntax tree independently.

[0065] Furthermore, to ensure that the split sub-SQL blocks have a complete structure, a unique block identifier is assigned to each sub-SQL block consisting of an element SQL statement, and the dependencies between them are recorded. This identifier can be extended to the abstract syntax tree and constraint hint blocks for use. The original SQL statement can be traced based on the block identifier and dependencies.

[0066] S300 constructs prompt words using the original SQL statement, constraint prompt blocks, task instructions, and structured output requirements, and inputs them into the bloodline parsing model to obtain a structured intermediate bloodline representation.

[0067] This step uses the original SQL statement (optionally a sub-SQL block of the original SQL statement) and constraint hint blocks generated based on the abstract syntax tree to construct a dynamic prompt template, which includes:

[0068] (1) Task instruction: used to explicitly instruct the bloodline parsing large model to parse the field-level bloodline and trace it back to the lowest physical table.

[0069] (2) Constraint hint block: The extracted symbol table, scope information and alias mapping information are encoded into JSON format as a strong constraint on the reasoning range of the lineage analysis large model.

[0070] (3) Structured output requirements: Provide a predefined output format template and force the bloodline parsing large model to output the results in the specified JSON format.

[0071] (4) SQL statement to be parsed: the original SQL statement or the preprocessed original SQL statement, or a sub-SQL block after they are divided into blocks.

[0072] Standardized SQL statements, constraint hint blocks, and output formats are injected into the hint template, driving the fine-tuned LLM to generate an initial structured lineage intermediate representation.

[0073] S400 executes a rule-based static lineage extraction algorithm on the original SQL statement to obtain static lineage results.

[0074] A static lineage extraction algorithm based on deterministic rules is run in parallel on the abstract syntax tree of the original SQL statement generated in step S200 to generate static lineage results, which include at least the target table, target field, source table, and source field information.

[0075] The original SQL statement in this step is the same as the SQL statement in the injection prompt word template in step S300, but provides parsing results different from the bloodline analysis model.

[0076] S500 compares the structured intermediate lineage representation with the static lineage result by field comparison. For lineage relationships that do not match, a decision tree is used to resolve conflicting lineages, and the lineage parsing result of the original SQL statement is obtained.

[0077] The structured bloodline intermediate representation obtained in step S300 is compared with the static bloodline result obtained in step S400. The comparison results can be divided into three categories: consistent set (shared by both), LLM-specific set (bloodline relationships that exist in the structured bloodline intermediate representation but not in the static bloodline result), and static-specific set (bloodline relationships that exist in the static bloodline result but not in the structured bloodline intermediate representation).

[0078] For inconsistent bloodline relationships in the latter two sets, a dynamic decision tree is activated for automatic adjudication. The result with the highest confidence level is selected as the final adjudication result to resolve the conflicting bloodlines and output the final bloodline analysis result.

[0079] Among them, a multi-level decision tree is set up from different adjudication dimensions. The adjudication dimensions may include multiple dimensions such as grammatical accuracy, rule validation, boundary constraints, and context constraints.

[0080] Preferably, see Figure 2 As shown, the decision tree executes each decision item in the following order of priority:

[0081] First priority (grammatical evidence adjudication): If a lineage relationship in the static lineage result has clear path evidence in the abstract syntax tree, while the structured lineage indicates a missing or insufficient corresponding relationship, then the static lineage result shall be adopted first.

[0082] Second priority (rule-first matching): Apply a small expert rule base (such as "aggregate function parameters must come from reachable fields of the FROM clause") to adjudicate conflicts and adopt the results verified by the expert rule base.

[0083] The third priority (constraint boundary verification): Utilize alias mapping information to perform precise scope and reachability verification on conflicting fields. For example, if the lineage resolution model resolves a field c1 from table t2, but the symbol table shows that table t2 is not visible in the current subquery scope, then the structured lineage intermediate representation is determined to be incorrect.

[0084] Fourth priority (contextual enhancement): If the above steps still cannot resolve the conflict, construct an enhanced hint containing a conflict description and negative examples, requiring the lineage resolution model to resolve the conflict point and provide an explanation.

[0085] In a specific adjudication, if a definitive adjudication result cannot be obtained for the first priority (e.g., both conditions are met or neither is met), the next priority is executed sequentially until a definitive adjudication result is obtained. If a decision result is obtained, the adjudication items of subsequent priorities are not executed. Based on the adjudication result obtained, the choice is made between adopting the blood relationship in the structured blood relationship intermediate representation or adopting the blood relationship in the static blood relationship result.

[0086] In a preferred embodiment, the above SQL lineage parsing method adopts a standard, structured lineage intermediate representation format defined in this invention, named JSON Schema-IR. This format mandates that each lineage relationship must contain the following objects: target table, target field, source table, and source field, and optionally include a transformation logic description. Each lineage relationship is stored in key-value pairs, and the data structure adopts JSON.

[0087] In the bloodline sample processing in step S100 and the structured output requirements in step S300, the JSONSchema-IR format is used.

[0088] In one specific implementation, the SQL lineage parsing method of the present invention includes the following steps:

[0089] S110 performs syntax parsing on the original SQL statement and generates an abstract syntax tree;

[0090] S120, calculates the complexity of the abstract syntax tree;

[0091] S130, If the complexity exceeds the set threshold, the original SQL statement is divided into blocks to generate at least two sub-SQL blocks, and a unique block identifier is assigned to each sub-SQL block and the dependency relationship is recorded. The sub-SQL blocks are used as the original SQL statement, and the process returns to step S110.

[0092] S140, if the complexity does not exceed the set threshold, extract the symbol table, scope and alias mapping information from the abstract syntax tree, and encode the symbol table, scope and alias mapping information into a constraint hint block;

[0093] S150 uses the original SQL statement, constraint hint block, task instructions and structured output requirements to construct hint words, which are then input into the bloodline parsing model to obtain a structured intermediate bloodline representation.

[0094] Before extracting the constraint hint block from the abstract syntax tree, this embodiment first judges the complexity of the abstract syntax tree. When the judgment result shows that the abstract syntax tree is relatively complex, the original SQL statement is first chunked and then processed to reduce the inference complexity of the lineage parsing large model.

[0095] It should be noted that before step S110, it may also include the fine-tuning training step of the lineage parsing large model. After step S1150, it may also include the steps of comparing and resolving conflicts between the structured lineage intermediate representation and the static lineage result. The specific implementation of these steps can refer to other embodiments in this invention.

[0096] The quantification method of complexity can be through a complexity scoring model to quantify and score the nesting depth, number of aggregation functions, number of operations, etc. of the abstract syntax tree, and compare the score with a set threshold to judge the complexity of the abstract syntax tree.

[0097] A complexity scoring model is expressed as follows:

[0098]

[0099] In the formula, is the complexity score, is the maximum nesting depth, is the number of JOIN operations, is the number of aggregation functions, , , are the weight coefficients of each item, and the sum of the weight coefficients of each item is 1.

[0100] It is defined that C≤3 is a simple SQL, 3<C≤6 is a medium SQL, and C>6 is a complex SQL. When the complexity score C of the abstract syntax tree exceeds 6, chunking is required.

[0101] Among them, the chunking method can be: for statements containing UNION or UNION ALL, split each subquery along the UNION keyword into independent chunks; for complex statements without UNION, according to the number of fields N in the outermost SELECT clause, split the field list into two parts according to the rule of N / 2, and copy the remaining clauses such as FROM and WHERE to generate two sub-SQL chunks with a simpler structure.

[0102] In a specific embodiment, the SQL lineage parsing method of the present invention includes the following steps:

[0103] S210, perform syntax parsing processing on the original SQL statement to generate an abstract syntax tree, extract symbol table, scope, and alias mapping information from the abstract syntax tree, and encode the symbol table, scope, and alias mapping information into a constraint hint block;

[0104] S220 uses the original SQL statement, constraint hint block, task instructions and structured output requirements to construct hint words, which are then input into the bloodline parsing big model to obtain a structured bloodline intermediate representation;

[0105] S230, perform formal verification on the structured bloodline intermediate representation. If the verification is successful, proceed to step S250; otherwise, proceed to step S240.

[0106] S240, trigger the retry mechanism, feed back the error information to the bloodline analysis model, request it to correct the analysis results, and return to step S230 to re-verify;

[0107] S250 compares the structured intermediate lineage representation with the static lineage result by field comparison. For lineage relationships that do not match, a decision tree is used to resolve conflicting lineages, and the lineage parsing result of the original SQL statement is obtained.

[0108] Before comparing the structured intermediate bloodline representation with the static bloodline result, this implementation method first performs a formal verification of the parsing result of the large bloodline parsing model to ensure the effectiveness of the subsequent comparison.

[0109] The formal verification content specifically includes:

[0110] (1) Verify whether the structured kinship intermediate representation conforms to the predefined format;

[0111] (2) Verify whether the source table and source field in the structured lineage intermediate representation can be found in the constraint hint block.

[0112] If any of the above checks fails, the error message will be fed back to the bloodline analysis model, and the corrected structured bloodline intermediate representation will be regenerated; if all checks pass, the subsequent steps will continue.

[0113] In one specific implementation, the SQL lineage parsing method of the present invention further includes the following steps:

[0114] S600 performs a bloodline invariance check on each bloodline relationship in the bloodline analysis results.

[0115] This step occurs after step S500. The lineage resolution result is the result after comparison and conflict resolution. The purpose is to perform a stability test on each lineage relationship after resolution. The specific method is as follows: apply a logical equivalence transformation to the original SQL statement, then re-execute the complete parsing process from S200 to S500, and compare whether the lineage relationships have changed between the two parsings. If a change has occurred, the confidence score of the lineage relationship is downgraded according to the transformation type and severity of the change; otherwise, the original confidence score is maintained.

[0116] Applying a logical equivalence transformation to the original SQL statement involves applying minor perturbations, such as randomly replacing table aliases or adjusting the predicate order of "AND" joins. If a lineage relationship changes after the perturbation, it is considered unstable, and its confidence level is reduced.

[0117] One weighting formula is as follows:

[0118]

[0119] In the formula, The original confidence score. The confidence score is calculated after weighting. To reduce the weighting, The value is ∈[0.15,0.5], and its value is dynamically determined based on the type of disturbance and the severity of the lineage change. Specifically, a smaller value can be taken for changes only in aliases, a middle value for structural changes, and a larger value for serious logical conflicts.

[0120] This implementation method tests the stability of bloodlines through logical equivalence transformations and combines a confidence weighting mechanism to provide a quantitative reliability reference for the analysis results, helping users to screen reliable blood relationships.

[0121] In one specific implementation, the SQL lineage parsing method of the present invention further includes the following steps:

[0122] S700, constructs and outputs a bloodline map.

[0123] This step occurs after step S600. Specifically, the kinship map is implemented as follows:

[0124] S710, sort the bloodline relationships according to their confidence scores, and output a standardized list of bloodlines including source, confidence score and resolution explanation;

[0125] S720 uses databases, tables, and fields as nodes and blood relations as edges to construct a visual bloodline graph.

[0126] Based on the confidence score calculated in step S600, all blood relations are sorted in descending order. The system can be configured with a confidence threshold (default 0.7); blood relations below this threshold will be marked as "low confidence" or filtered out directly. The final output is a standardized list of blood relations, with each record containing the field-blood relation pair, confidence score, resolution source (static / LLM / fusion), and an explanation of conflict resolution.

[0127] Standardized kinship relationships are loaded into a graph database (Neo4j), such as... Figure 3As shown, a global, versioned data lineage graph is constructed using databases, tables, and fields as nodes and lineage relationships as edges. Based on this graph, the system provides a visual front-end interface, allowing users to intuitively query the upstream sources and downstream influences of any field. Simultaneously, a standard REST / GraphQL API interface is provided for other data governance platforms or automation tools to access the lineage data.

[0128] Furthermore, all manually reviewed and corrected lineage records, as well as low-confidence lineage records that the decision tree cannot automatically resolve, along with their corresponding original SQL statements and context information, are collected and stored in a dedicated difficult sample library. This sample library will be used periodically to incrementally fine-tune the large-scale lineage resolution model in step S100, enabling the system to continuously learn and self-evolve.

[0129] The implementation of the present invention will be described in detail below with reference to a specific application example.

[0130] Step S1: Build the model and data foundation.

[0131] This implementation first constructs high-quality training data and a proprietary model to lay the foundation for subsequent analysis tasks.

[0132] 1.1 Multimodal Data Acquisition and Labeling

[0133] The database collected 10,103 SQL scripts covering various dialects of mainstream databases, including MySQL, PostgreSQL, HiveQL, Spark SQL, Redshift, and Doris. The samples cover 12 complex scenarios, such as multi-table joins, deeply nested queries, common table expressions (CTEs), user-defined functions (UDFs), and window functions.

[0134] For each SQL statement, its lineage is manually annotated down to the field level, and then standardized to the JSONSchema-IR format defined in this invention.

[0135] 1.2 Supervised Fine-tuning of Large Language Models

[0136] We selected the open-source pre-trained large language model Qwen3-32B as the base and used LoRA (Low-Rank Adaptation) (an efficient parameter fine-tuning technique) to supervise and fine-tune the model, making it specialize in SQL lineage parsing tasks.

[0137] The specific parameter configuration is as follows: The LoRA rank is set to 8, the LoRA scaling factor (alpha) is set to 16, and Dropout is set to 0.05; The AdamW optimizer is used in the training process, the initial learning rate is set to 1e-5, the batch size is 8, and a total of 128 epochs are trained.

[0138] Step S2: SQL preprocessing and structural analysis.

[0139] In this embodiment, a series of standardization processes are performed on the input original SQL to facilitate the precise parsing of subsequent modules.

[0140] 2.1 Standardization process

[0141] For the input SQL statement, perform comment cleaning and keyword capitalization unification processing.

[0142] 2.2 Structural analysis and complexity evaluation

[0143] Use a grammar parser for a specific dialect to parse the standardized SQL and generate an abstract syntax tree (AST). If the parsing fails, it is marked as "statically unparseable". If successful, calculate its complexity score C based on the AST.

[0144] The complexity scoring model is defined as:

[0145]

[0146] Where, is the maximum nesting depth of the AST, is the number of JOIN operation nodes, is the number of aggregate function nodes, , , are the weight coefficients for each item.

[0147] In this embodiment, the weight coefficients are set as: , , .

[0148] According to the score C, the SQL is divided into three categories: simple (C ≤ 3), medium (3 < C ≤ 6), and complex (C > 6).

[0149] 2.3 Adaptive chunking

[0150] When the total number of tokens in the SQL exceeds the maximum context length of the LLM (4096 in this embodiment), or its complexity is rated as "complex" (C > 6), trigger the adaptive chunking module:

[0151] (1) For statements containing UNION or UNION ALL, split each subquery into independent blocks along the UNION keyword;

[0152] (2) For complex statements without UNION, the field list is split into two parts according to the number of fields N in the outermost SELECT clause, and the remaining clauses such as FROM and WHERE are copied to generate two simpler sub-SQL blocks.

[0153] Assign a unique treeId to each block and record the dependencies between them.

[0154] Step S3: Constraint-driven co-resolution.

[0155] This implementation combines the semantic understanding of LLM with the structured information of AST for collaborative parsing.

[0156] 3.1 Constraint Information Extraction

[0157] Traverse the AST generated in step S2, extracting all tables, fields, aliases, and their scope information to construct a symbol table. Assign a globally unique node reference (NodeRef) to each key node in the AST (such as table names and field names). The format of NodeRef is defined as treeId:path:offset, where treeId corresponds to the block ID, path is the path index sequence from the AST root to the node, and offset is the start and end positions of the node in the original SQL text. This is used to map each key node in the AST to the text of the original SQL statement.

[0158] 3.2 Constraint-Driven Hint Engineering

[0159] Construct a dynamic prompt containing multiple parts. The prompt template includes:

[0160] Task instruction: Explicitly instruct the LLM to parse field-level lineage and trace it back to the lowest-level physical table.

[0161] Constraint hint block: Encodes the symbol table, scope information and NodeRef extracted in step 3.1 into JSON format as a strong constraint on the scope of LLM inference.

[0162] Structured output requirements: Provide the JSON Schema-IR defined in step 1.2 to force LLM to output results in the specified JSON format.

[0163] SQL to be parsed: Put in the SQL text processed in step S2.

[0164] 3.3 LLM Parsing and Formal Verification

[0165] The constructed complete hints are input into the LLM after fine-tuning in step S1 to obtain the generated JSON text. This text is the initial semantic lineage intermediate representation (IR-L). Subsequently, the IR-L undergoes rigorous formal validation: first, it is verified whether it fully conforms to the structure of JSON Schema-IR; second, it is checked whether every source table and source field appearing in the IR-L can be found in the symbol table of the constraint hint block with a corresponding entry containing a valid NodeRef (node ​​existence check). If the validation fails, the system will automatically trigger a retry mechanism, sending error information back to the LLM for correction.

[0166] Step S4: Conflict resolution and confidence assessment.

[0167] This implementation method is the core step in ensuring the reliability of the final result, refining blood relations through comparison, adjudication, and re-verification.

[0168] 4.1 Static bloodline extraction and comparison:

[0169] A static lineage extraction algorithm based on deterministic rules is run in parallel on the AST generated in step S2 to generate the static lineage result IR-S. Then, the lineage relationships (represented as tuples {source_table, source_field, target_table, target_field}) in IR-L and IR-S are compared and divided into three categories: consistent set (shared by both), LLM-specific set, and static-specific set.

[0170] 4.2 Dynamic Decision Tree Conflict Resolution:

[0171] For inconsistent blood relations (the latter two categories), a dynamic decision tree is activated for automatic adjudication. This decision tree is executed sequentially according to priority:

[0172] Grammatical evidence adjudication: If there is clear AST path evidence for a lineage in IR-S, while the corresponding IR-L relationship is missing or lacks sufficient evidence, then IR-S shall be given priority.

[0173] Rule-based matching: A small expert rule base (such as "aggregate function arguments must come from reachable fields of the FROM clause") is applied to adjudicate conflicts.

[0174] Constraint boundary verification: NodeRef is used to perform precise scope and reachability verification on conflicting fields. For example, if the LLM resolves a field c1 from table t2, but the symbol table shows that table t2 is not visible in the current subquery scope, then the LLM resolution is considered incorrect.

[0175] Contextual Enhancement and Re-reasoning: If the above steps still cannot resolve the issue, an enhanced hint containing a conflict description and negative examples is constructed, requiring the LLM to "rethink" the conflict point and provide an explanation.

[0176] 4.3 Verification of Bloodline Invariance and Confidence Rescoring

[0177] A stability test is performed on each resolved lineage relationship. Specifically, a minor logical perturbation is applied to the original SQL (e.g., randomly replacing the table alias u -> tbl_123, or adjusting the predicate order of the AND join), and then the complete parsing process is re-executed. If a lineage relationship changes after the perturbation, it is considered unstable, and its confidence level is reduced. The reduction formula is:

[0178]

[0179] In this embodiment, the weight reduction magnitude δ∈[0.15,0.5] is dynamically determined according to the type of disturbance and the severity of the lineage change. For example, alias change δ=0.15, structural change δ=0.3, and serious logical conflict δ=0.5.

[0180] Finally, the system calculates the final confidence score based on multi-source evidence. :

[0181]

[0182] in, This is the base score (e.g., LLM output defaults to 0.5, static output defaults to 0.6). It is the cumulative penalty value for the failure of the above invariance check (i.e., the sum of δ in multiple experiments). It is the grammar path confidence provided by static analysis. It is the logical consistency of LLM re-inference. This is the Jaccard similarity between IR-L and IR-S within the current subquery range. In this embodiment, the weight is set to... , , This is to balance the contributions of different sources of evidence.

[0183] 4.4 Closed-loop feedback

[0184] All bloodlines that have been manually reviewed and corrected, as well as low-confidence bloodlines that cannot be automatically resolved by the decision tree and are marked as "pending review" ( Data points <0.4, along with their corresponding SQL and context information, are automatically collected and stored in a dedicated database of challenging samples. This database will be used periodically to incrementally fine-tune the LLM in step S1, thereby enabling the system to continuously learn and evolve.

[0185] Step S5: Bloodline Map Construction and Output

[0186] This implementation method integrates, stores, and provides services based on the final parsing results.

[0187] 5.1 Result Filtering and Output

[0188] Based on the confidence scores calculated in step 4.3, all blood relations are sorted in descending order. The system is configured with a confidence threshold of 0.7, marking blood relations below this threshold as "low confidence." The final output is a standardized list of blood relations, with each record containing the field blood relation pair, confidence score, resolution source (static / LLM / fusion), and an explanation of conflict resolution.

[0189] 5.2 Map Construction and Services

[0190] Standardized lineage relationships are loaded into a graph database (Neo4j), with databases, tables, and fields as nodes and lineage relationships as edges, to construct a global, versioned data lineage graph.

[0191] Based on the same concept, the present invention also provides an SQL lineage resolution system, such as... Figure 4 As shown, the system includes:

[0192] The model pre-training module 410 is configured to perform supervised fine-tuning training on the pre-trained large language model using standardized kinship samples to obtain a kinship resolution large model.

[0193] The preprocessing and analysis module 420 is configured to perform syntax parsing on the original SQL statement, generate an abstract syntax tree, extract symbol table, scope and alias mapping information from the abstract syntax tree, and encode the symbol table, scope and alias mapping information into constraint hint blocks;

[0194] The large model parsing module 430 is configured to construct prompt words using the original SQL statement, constraint prompt block, task instructions and structured output requirements, and input them into the bloodline parsing large model to obtain a structured bloodline intermediate representation;

[0195] The static lineage parsing module 440 is configured to execute a rule-based static lineage extraction algorithm on the original SQL statement to obtain static lineage results;

[0196] The conflict resolution module 450 is configured to compare the structured lineage intermediate representation with the static lineage result by field comparison. For lineage relationships that do not match, a decision tree is used to resolve the conflict lineage and obtain the lineage parsing result of the original SQL statement.

[0197] Furthermore, the SQL lineage parsing system also includes a lineage invariance verification module 460, which is configured to apply a logical equivalence transformation to the original SQL statement and then send it to the preprocessing and analysis module to re-execute the complete parsing process and compare whether the lineage relationship of the two parsing results has changed; if a change has occurred, the confidence score of the lineage relationship is downgraded according to the transformation type and the severity of the change.

[0198] Furthermore, the SQL lineage analysis system also includes a lineage graph construction module 470, which is configured to sort the lineages according to the confidence score of each lineage relationship and output a standardized lineage list containing the source, confidence score and resolution explanation; and to construct a visual lineage graph with databases, tables and fields as nodes and lineage relationships as edges.

[0199] Furthermore, the SQL lineage parsing system also includes an SQL preprocessing module 480, which is configured to preprocess the SQL sample data and the SQL data to be parsed to obtain prepared data for use by the model pretraining module 410, the preprocessing and analysis module 420, and the static lineage parsing module 440.

[0200] Furthermore, the preprocessing and analysis module 420 is also configured to: calculate the complexity of the abstract syntax tree before extracting symbol table, scope and alias mapping information from the abstract syntax tree; if the complexity exceeds a set threshold, divide the original SQL statement into blocks, generate at least two sub-SQL blocks, assign a unique block identifier to each sub-SQL block and record the dependency relationship, and regenerate the abstract syntax tree of each sub-SQL block.

[0201] Embodiments of the present invention also provide an electronic device including a processor and a memory, wherein the number of processors may be one or more. The memory, as a computer-readable storage medium, can be used to store software programs, computer-executable programs, and modules. The processor executes various functional applications and data processing of the electronic device by running the software programs, instructions, and modules stored in the memory, thereby implementing the SQL lineage parsing method of any of the above embodiments of the present invention.

[0202] The memory may primarily comprise a program storage area and a data storage area. The program storage area may store the operating system and at least one application program required for a given function; the data storage area may store data created based on terminal usage. Furthermore, the memory may include high-speed random access memory (RAM) and non-volatile memory, such as at least one disk storage device, flash memory, or other non-volatile solid-state storage device. In some instances, the memory may further include memory remotely located relative to the processor, which can be connected to the electronic device via a network. Examples of such networks include, but are not limited to, the Internet, intranets, local area networks (LANs), mobile communication networks, and combinations thereof.

[0203] Embodiments of the present invention also provide a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the SQL lineage resolution method of any embodiment of the present invention.

[0204] The computer storage medium of this invention can be any combination of one or more computer-readable media. A computer-readable medium can be a computer-readable signal medium or a computer-readable storage medium. A computer-readable storage medium can be, for example, but not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination thereof. More specific examples of computer-readable storage media (a non-exhaustive list) include: an electrical connection having one or more wires, a portable computer disk, a hard disk, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fiber, portable compact disk read-only memory (CD-ROM), optical storage device, magnetic storage device, or any suitable combination thereof. In this invention, a computer-readable storage medium can be any tangible medium containing or storing a program that can be used by or in conjunction with an instruction execution system, apparatus, or device.

[0205] Computer-readable signal media may include data signals propagated in baseband or as part of a carrier wave, carrying computer-readable program code. Such propagated data signals may take various forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination thereof. Computer-readable signal media may also be any computer-readable medium other than computer-readable storage media, capable of sending, propagating, or transmitting programs for use by or in connection with an instruction execution system, apparatus, or device.

[0206] Embodiments of the present invention also provide a computer program product that, when run on a computer, causes the computer to execute the SQL lineage resolution method of any of the above embodiments of the present invention.

[0207] The above embodiments are merely preferred embodiments of the present invention, but the scope of protection of the present invention is not limited thereto. Any variations or substitutions that can be easily conceived by those skilled in the art within the scope of the technology disclosed in the present invention should be included within the scope of protection of the present invention. Therefore, the scope of protection of the present invention should be determined by the scope of the appended claims.

Claims

1. A method for resolving SQL lineage, characterized in that, include: Obtain multi-dialect SQL scripts, annotate each SQL script with a predefined format to obtain standardized lineage samples, and use the lineage samples to perform supervised fine-tuning training on a pre-trained large language model to obtain a large lineage resolution model; The original SQL statement is parsed to generate an abstract syntax tree. Symbol table, scope and alias mapping information are extracted from the abstract syntax tree and encoded into constraint hint blocks. Using the original SQL statement, constraint hint block, task instructions, and structured output requirements, a hint word is constructed and input into the bloodline parsing model to obtain a structured bloodline intermediate representation; The original SQL statement is subjected to a rule-based static lineage extraction algorithm to obtain static lineage results; The structured bloodline intermediate representation is compared with the static bloodline result by field comparison. For bloodline relationships that do not match, a decision tree is used to resolve conflicting bloodlines, and the bloodline parsing result of the original SQL statement is obtained.

2. The SQL lineage parsing method according to claim 1, characterized in that, Each of the aforementioned bloodline samples contains the bloodline relationship for each field in the corresponding SQL script, wherein each bloodline relationship is stored as a key-value pair, and the storage objects include: target table, target field, source table, and source field.

3. The SQL lineage resolution method according to claim 1, characterized in that, Before extracting the symbol table, scope, and alias mapping information from the abstract syntax tree, the method further includes: Calculate the complexity of the abstract syntax tree. If the complexity exceeds a set threshold, divide the original SQL statement into blocks to generate at least two sub-SQL blocks. Assign a unique block identifier to each sub-SQL block and record the dependency relationship. Regenerate the abstract syntax tree for each sub-SQL block.

4. The SQL lineage resolution method according to claim 1, characterized in that, Before performing a field comparison between the structured kinship intermediate representation and the static kinship result, the method further includes: Verify whether the structured bloodline intermediate representation conforms to the predefined format; Verify whether the source table and source field in the structured bloodline intermediate representation can be found in the constraint hint block; If any check fails, the error message is fed back to the bloodline parsing model, and a corrected structured bloodline intermediate representation is regenerated; if all checks pass, the structured bloodline intermediate representation is compared with the static bloodline result by field comparison.

5. The SQL lineage resolution method according to claim 1, characterized in that, The decision tree executes each decision item sequentially according to a preset priority until a decision result is obtained, and the conflicting lineages are resolved based on the decision result; wherein, the decision items include: First priority: If a lineage relationship in the static lineage result has clear path evidence in the abstract syntax tree, while the structured lineage result indicates a missing or insufficient corresponding relationship, then the static lineage result shall be adopted. Second priority: Apply the expert rule base to adjudicate conflicts and adopt the blood relationship verified by the expert rule base; Third priority: Use alias mapping information to perform precise scope and reachability verification on conflicting fields, and adopt the verified lineage relationship; Fourth priority: Construct an enhanced hint containing conflict descriptions and negative examples, requiring the lineage resolution model to adjudicate the conflict points and provide explanations.

6. The SQL lineage resolution method according to claim 1, characterized in that, After obtaining the lineage parsing result of the original SQL statement, the method also includes performing a lineage invariance check on each lineage relationship in the lineage parsing result; The bloodline invariance check is achieved by applying a logical equivalence transformation to the original SQL statement, re-parseing it, and comparing whether the bloodline relationship has changed. If the blood relationship changes, the confidence score of the blood relationship will be downweighted according to the type and severity of the change.

7. The SQL lineage parsing method according to claim 6, characterized in that, The method further includes: Sort each blood relationship according to its confidence score and output a standardized list of blood relationships including source, confidence score and resolution explanation; A visual kinship graph is constructed using databases, tables, and fields as nodes and blood relations as edges.

8. An SQL lineage resolution system, characterized in that, include: The model pre-training module is configured to use standardized kinship samples to perform supervised fine-tuning training on the pre-trained large language model to obtain the kinship resolution large model; The preprocessing and analysis module is configured to perform syntax parsing on the original SQL statement, generate an abstract syntax tree, extract symbol table, scope and alias mapping information from the abstract syntax tree, and encode the symbol table, scope and alias mapping information into constraint hint blocks; The large model parsing module is configured to construct prompt words using the original SQL statement, constraint prompt block, task instructions and structured output requirements, and input them into the bloodline parsing large model to obtain a structured bloodline intermediate representation; The static lineage parsing module is configured to execute a rule-based static lineage extraction algorithm on the original SQL statement to obtain static lineage results; The conflict resolution module is configured to compare the structured lineage intermediate representation with the static lineage result by field comparison, and to use a decision tree to resolve conflicting lineages for lineage relationships that do not match, thereby obtaining the lineage parsing result of the original SQL statement.

9. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements the SQL lineage resolution method according to any one of claims 1-7.

10. A storage medium having a computer program stored thereon, characterized in that, When the program is executed by the processor, it implements the SQL lineage resolution method as described in any one of claims 1-7.

Images