Multi-source data relation acquisition method based on database kernel

Abstract

The invention relates to the technical field of database systems and data management, in particular to a multi-source data relation obtaining method based on a database kernel. The method comprises the following steps: in an analysis and analysis stage, extracting table and column information of a target SQL query statement, and identifying a table-level and column-level initial dependency relationship; in a rewriting stage, analyzing whether rewriting is influenced or not, and if so, updating the initial dependency relationship to obtain an intermediate dependency relationship; in the optimization stage, a target path is determined according to the initial cost, the blood relationship complexity, the blood relationship capture integrity degree and a preset blood relationship influence coefficient of each path corresponding to the query tree, and the intermediate dependency relationship is replaced or supplemented according to the table-level and column-level blood relationship extracted from the target path to obtain a static dependency relationship; and in an execution stage, capturing dynamic blood relationship information, and merging the dynamic blood relationship information and the static dependency relationship to obtain a target dependency relationship. According to the invention, the accuracy and integrity of the obtained data blood relationship can be improved.

Description

Technical Field

[0001] This invention relates to the fields of database systems and data governance technology, and in particular to a method for obtaining multi-source data relationships based on a database kernel. Background Technology

[0002] In the era of big data, data has become a core asset for enterprises. To provide better services, enterprises have built multiple business systems, which often come from different vendors and use different databases and data standards, resulting in serious data silos. Data sharing and integration have become urgent needs.

[0003] To achieve data sharing and integration, it is necessary to obtain the data lineage of different databases (i.e., multi-source databases). Data lineage is a key component of the data governance system, systematically recording the complete flow path of data from its origin to its destination, including data source, transformation and processing processes, and final destination. In the era of big data, data lineage has indispensable technical value for data quality management, change impact analysis, compliance auditing, troubleshooting, and system change management.

[0004] However, current mainstream methods for obtaining data lineage information suffer from significant technical bottlenecks, particularly in their integration with database execution mechanisms. First, traditional external collection methods (such as SQL log parsing, audit log capture, and metadata repositories) struggle to reflect the actual processing path of queries by the database optimizer. The raw text of the SQL query may differ significantly from the final execution path because the database optimizer selects the optimal execution strategy based on factors like statistics, index availability, and system resources when generating the execution plan. This includes a series of optimization transformations such as view expansion, subquery rewriting, and join order adjustment. This results in a severe deviation between the lineage relationship analyzed from the raw SQL text and the actual data flow path, with the accuracy of lineage relationships potentially dropping as low as 40%-60% in complex query scenarios. Second, current lineage collection and query optimization are completely disconnected. When generating the execution plan, the database optimizer focuses solely on query execution efficiency, completely ignoring the need for lineage information collection. This disconnect means that kinship tracing can only be a passive, post-hoc process, unable to actively influence the choice of data access paths, thus failing to achieve a balance between performance and the integrity of kinship, resulting in a lower degree of completeness in the obtained kinship.

[0005] Improving the accuracy and completeness of the obtained bloodline data is an urgent problem to be solved. Summary of the Invention

[0006] The purpose of this invention is to provide a method for obtaining multi-source data relationships based on a database kernel, so as to improve the accuracy and completeness of the obtained data lineage relationships.

[0007] According to the present invention, a method for obtaining multi-source data relationships based on a database kernel is provided, the method comprising the following steps: S100, during the stage of parsing and analyzing the target SQL query statement in the target database, extract the table and column information of the target SQL query statement, and identify the initial table-level and column-level dependencies of the target SQL query statement; the target database is any database in the multi-source database.

[0008] S200: During the stage of rewriting the target SQL query statement in the target database, analyze whether the rewriting affects the table-level and column-level dependencies of the target SQL query statement. If it does, update the initial table-level and column-level dependencies of the target SQL query statement to obtain the intermediate table-level and column-level dependencies of the target SQL query statement.

[0009] S300, in the stage of optimizing the query tree corresponding to the target SQL query statement in the target database, the target path corresponding to the query tree is determined according to the initial cost, lineage complexity, lineage capture completeness and preset lineage influence coefficient of each path corresponding to the query tree. The table-level and column-level intermediate dependencies of the target SQL query statement are replaced or supplemented according to the table-level and column-level lineage relationships extracted from the target path to obtain the table-level and column-level static dependencies of the target SQL query statement.

[0010] S400, during the stage of executing the target path in the target database, capture dynamic lineage information and merge the dynamic lineage information with the table-level and column-level static dependencies of the target SQL query statement to obtain the table-level and column-level target dependencies of the target SQL query statement; the dynamic lineage information includes the actual number of rows processed, specific filter condition values, condition triggering conditions, and data flow path.

[0011] S500 retrieves the data lineage of the target SQL query statement based on the table-level and column-level target dependencies.

[0012] Compared with the prior art, the present invention has at least the following beneficial effects: This invention collects lineage information during the parsing, analysis, rewriting, planning, and execution phases of the target database. This ensures that the lineage information collection process comprehensively covers the entire process of the target database processing SQL queries, guaranteeing the completeness and accuracy of the acquired lineage dependencies. This is beneficial for constructing complete and accurate data lineage relationships. Furthermore, this invention modifies the target database planning phase. Compared to existing technologies that only consider initial costs (i.e., costs obtained based on existing optimizers), this invention also considers lineage factors. This results in a stronger degree of lineage capture in the execution path finally determined during the optimization phase, facilitating the acquisition of more complete lineage information and the construction of more complete and accurate data lineage relationships, while effectively controlling system performance overhead. Based on the more complete and accurate data lineage relationships of the target database obtained by this invention, better data sharing and integration between the target database and other databases in multi-source databases can be achieved. Attached Figure Description

[0013] To more clearly illustrate the technical solutions in the embodiments of the present invention, the accompanying drawings used in the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0014] Figure 1 A flowchart of a method for obtaining multi-source data relationships based on a database kernel, provided in an embodiment of the present invention. Detailed Implementation

[0015] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0016] According to this embodiment, a method for obtaining multi-source data relationships based on a database kernel is provided, the method comprising the following steps: S100, during the stage of parsing and analyzing the target SQL query statement in the target database, extract the table and column information of the target SQL query statement, and identify the initial table-level and column-level dependencies of the target SQL query statement; the target database is any database in the multi-source database.

[0017] In this embodiment, the multi-source database refers to multiple databases that are to be shared and merged, and the target database is one of the databases in the multi-source database.

[0018] As a specific implementation method, the target database is PostgreSQL. The standard query processing flow of the target database for SQL query statements goes through five main stages in sequence: parsing, analyzing, rewriting, optimizing / planning, and executing.

[0019] Those skilled in the art will recognize that SQL queries begin with the parsing and analysis phase, the goal of which is to convert the raw SQL text entered by the user into a structured representation that the database can understand and manipulate—a query tree (Query structure).

[0020] Capturing lineage information during the parsing and analysis phase is crucial because it allows for the most direct acquisition of the tables and columns explicitly specified in the SQL statement, as well as the preliminary relationships between them. As a preferred implementation, S100 includes: S110, when the main entry function for processing user SQL commands is called, insert a lineage collection initialization callback function; this lineage collection initialization callback function is used to assign a unique identifier to the target SQL query statement and initialize a memory structure for accumulating lineage information throughout the query processing lifecycle.

[0021] As a specific implementation, when the main entry function `exec_simple_query` for processing user SQL commands is called, this embodiment inserts a lineage collection initialization callback function `on_query_start_initialize_lineage_callback`. This callback function assigns a unique identifier (Query ID) to the current query and initializes a memory structure `LineageCollector` for accumulating lineage information throughout the query processing lifecycle. This structure acts as a context object, passed between different stages of query processing, continuously collecting and refining lineage information.

[0022] S120, after the target database completes lexical and syntactic analysis of the target SQL query statement and generates the original analysis tree, a lineage basic information extraction function is inserted. This lineage basic information extraction function is used to traverse the original analysis tree, extract basic information, and store it in the memory structure. The basic information includes the query type, the table names involved, and the column names.

[0023] As a specific implementation, after the pg_parse_query function calls the underlying raw_parser (generated by Lex and Yacc) to complete lexical and syntactic analysis and generate the raw parse tree (raw_parse_tree_list), the system inserts the lineage basic information extraction function on_raw_parse_extract_basic_lineage_callback, which traverses the raw parse tree, extracts basic information such as query type (SELECT, INSERT, etc.), table names involved, and column names, and stores it in LineageCollector.

[0024] S130, when performing semantic analysis on the target database, after converting the original analysis tree into a query tree that has undergone semantic verification and type checking, the lineage recognition callback function is called. This lineage recognition callback function is used to deeply analyze the internal details of the query tree, extract the initial table-level and column-level dependencies, and add them to the memory structure.

[0025] As a specific implementation, during semantic analysis in the `parse_analyze` function, after converting the original parse tree into a query tree (Query structure) that has undergone semantic verification and type checking, the system calls the `on_analyze_identify_relations_callback` callback function to identify lineage relationships. This is the core of lineage collection during the syntax parsing and analysis phase. It will delve into the internal details of the Query structure. For example, for SELECT statements, it is necessary to carefully examine `targetList` (the list of target columns) to identify the output columns and their calculation sources (which may be columns directly from a table, or the results of complex expressions or function calls); at the same time, `fromClause` is analyzed to determine the table, view, or subquery from which the data originates; and `whereClause`, `groupClause`, `havingClause`, etc., are examined to identify the columns involved in filtering, grouping, aggregation, and other operations. For INSERT statements, it is necessary to record the target table and the list of inserted columns. If the data comes from the `VALUES` clause, the source of the constants needs to be recorded; if it comes from the `SELECT` clause, the lineage of the subquery needs to be recursively analyzed and a mapping relationship between the source table columns and the target table columns needs to be established. Similarly, for UPDATE and DELETE statements, it is also necessary to accurately record the target table, the update / delete conditions (whereClause), and the source expressions (targetList) of the target columns and new values ​​involved in the update operation. All these table-level and preliminary column-level mappings extracted from the Query structure will be added to the LineageCollector.

[0026] In this embodiment, the LineageCollector itself is designed to be efficient and scalable. It includes basic query information (Query ID, SQL text, user ID, etc.) and data structures for storing the relationship between source and target objects, such as using hash tables or linked lists to store the dependencies between tables and columns, as well as related transformation expressions or operation types.

[0027] In this embodiment, the bloodline information obtained in the parsing and analysis stage provides a foundation for subsequent stages. However, this information is often only preliminary and needs to be further improved and refined in subsequent stages of query processing.

[0028] S200: During the stage of rewriting the target SQL query statement in the target database, analyze whether the rewriting affects the table-level and column-level dependencies of the target SQL query statement. If it does, update the initial table-level and column-level dependencies of the target SQL query statement to obtain the intermediate table-level and column-level dependencies of the target SQL query statement.

[0029] Those skilled in the art will understand that after semantic analysis, the query tree enters the rewriting phase (pg_rewrite_query function). The main task of this phase is to process the rule system and the view.

[0030] Both rules and views can alter the original intent of a query, redirecting it to different tables or introducing more complex underlying logic. Therefore, accurately tracking lineage changes during the rewrite phase is crucial. As a specific implementation, after the `pg_rewrite_query` function has applied all rules and expanded views, a lineage change tracking function `on_rewrite_track_changes_callback` is inserted to analyze whether the rewrite operation affects the lineage relationships already recorded in the `LineageCollector`. If it does, corresponding updates are performed. As another specific implementation, S200 includes: S210, if the target SQL query statement references the view, it is determined that the rewriting affects the table-level and column-level dependencies of the target SQL query statement. When the view is expanded, the reference information of the view in the target SQL query statement, the underlying table and columns actually replaced after the view is expanded, and the mapping relationship between the output columns of the view and the columns of the underlying table are recorded. The mapping relationship is updated in the memory structure, and the lineage of the original columns pointing to the view is corrected to the columns pointing to the underlying table.

[0031] When a query references a view, the view definition is expanded, replacing the references to the view in the query tree. To correctly track lineage, this embodiment uses a ViewMappingTracker (part of LineageCollector). When a view expands, this tracker records the following information: references to the view in the original query, the underlying table and columns actually replaced by the expanded view, and the mapping between the output columns of the view and the columns of the underlying table. These mappings are then updated in the LineageCollector, correcting the lineage relationships that originally pointed to the view columns to point to the columns of the underlying table.

[0032] S220, if a system rule is triggered and applied, it is determined that the rewriting affects the table-level and column-level dependencies of the target SQL query statement, and the triggered rule name, conditions and executed operations are recorded. The data transformation or redirection logic caused by the rule is tracked, and the lineage in the memory structure is modified accordingly to reflect the actual data flow after the rule is applied.

[0033] When system-defined rules are triggered and applied (such as the ON INSERT DO INSTEAD rule), queries may be completely replaced or additional operations may be added. `on_rewrite_track_changes_callback` can identify rule application, recording the triggered rule name, conditions, and executed operations. More importantly, it's necessary to track the data transformation or redirection logic caused by the rule and modify the lineage relationships in `LineageCollector` accordingly to reflect the actual data flow after the rule is applied. For example, a rule that redirects INSERT operations to another table requires modifying the write lineage of the original target table to point to the write lineage of the new target table.

[0034] As a specific implementation, if the query rewriting phase involves processing subqueries, then it is determined that the rewriting affects the table-level and column-level dependencies of the target SQL query statement, and the changes in the data dependencies between the subquery and the main query are recorded to ensure that the lineage information from the subquery is merged in the LineageCollector and that the new direct dependencies that may be generated after the subquery is promoted are correctly reflected.

[0035] As a specific implementation, if the target SQL query is a simple query (i.e., the target SQL query does not reference views, system rules are not triggered, and subquery processing is not involved), the system will skip the complex query rewriting and expansion steps and directly extract table-level and column-level dependencies from the original SQL statement. Table-level dependencies are extracted directly from the FROM clause, and column-level dependencies are extracted from SELECT lists, WHERE conditions, GROUP BY, ORDER BY, and other clauses. Since there is no need to handle view expansion, rule triggering, or subquery rewriting, the system will not perform secondary updates or merging of the initially extracted dependencies, thereby improving processing efficiency. Despite the simplified processing flow, the system still ensures that complete table-level and column-level dependencies are captured, including all data sources and transformation operations involved in the query.

[0036] S300, in the stage of optimizing the query tree corresponding to the target SQL query statement in the target database, the target path corresponding to the query tree is determined according to the initial cost, lineage complexity, lineage capture completeness and preset lineage influence coefficient of each path corresponding to the query tree. The table-level and column-level intermediate dependencies of the target SQL query statement are replaced or supplemented according to the table-level and column-level lineage relationships extracted from the target path to obtain the table-level and column-level static dependencies of the target SQL query statement.

[0037] Those skilled in the art will understand that after query rewriting is completed, the query tree is passed to the optimizer / planner (the entry point is usually the planner function). The optimizer's goal is to generate an optimal execution plan (Plan structure) for the given query tree. This plan details the specific steps for retrieving data, including the method of accessing tables (sequential scan, index scan, etc.), the algorithm for table joins (nested loops, hash joins, merge joins, etc.), and the order of joins.

[0038] An execution plan is a blueprint for the actual database operations. Therefore, extracting lineage information from the execution plan can yield a more accurate and closer lineage relationship than a query tree, especially in determining column-level lineage. In this embodiment, during path evaluation, lineage factors are considered and adjusted based on the traditional execution cost calculation, and the optimal path is determined according to the adjusted cost. As a preferred implementation, determining the target path corresponding to the query tree based on the initial cost, lineage complexity, lineage capture completeness, and preset lineage influence coefficient for each path corresponding to the query tree includes: S310, obtain the initial cost of each path corresponding to the query tree, and select the preset number of paths with the smallest initial cost as candidate paths.

[0039] In this embodiment, the initial cost of the path is the path cost determined by the existing optimizer, which does not take lineage factors into account.

[0040] S320, for any candidate path, the initial cost, lineage complexity, lineage capture completeness, and preset lineage influence coefficient of the candidate path are determined as the target cost of the candidate path; the target cost of any candidate path is positively correlated with the initial cost, lineage complexity, and preset lineage influence coefficient of the candidate path, and the target cost of any candidate path is negatively correlated with the lineage capture completeness of the candidate path.

[0041] In a preferred embodiment, the target cost of the i-th candidate path is c. i c i =c i,0 ×(1+k× l i ×(1-p i )), c i,0 Let be the initial cost of the i-th candidate path, and k be the preset lineage influence coefficient. l i Let p be the lineage complexity of the i-th candidate path. i The value of i ranges from 1 to n, representing the completeness of lineage capture, where n is the number of candidate paths. This ensures that, given similar initial costs (i.e., costs obtained based on the existing optimizer), plans that capture more lineage information receive appropriate priority, achieving a balance between query efficiency and lineage capture completeness.

[0042] As a preferred embodiment, the lineage complexity of the i-th candidate path is l i , l i =α×T+β×C+γ×∑ m j=1 (w j ×complexity j ), where α, β, and γ are the weights corresponding to the number of tables, the number of columns, and the operation type, respectively; T is the number of tables involved in the normalized target SQL query; C is the number of columns involved in the normalized target SQL query; m is the number of operations in the target SQL query; and complexity j Let w be the complexity of the j-th operation in the target SQL query statement. j Let i be the weight of the j-th operation in the target SQL query statement, where i ranges from 1 to n, and n is the number of candidate paths.

[0043] In this embodiment, the operation complexity coefficient varies depending on the operation type, and the correspondence between operation type and operation complexity is known. For example, simple column references have a complexity of 0.2, conditional filtering has a complexity of 0.4, inner joins have a complexity of 0.6, outer joins have a complexity of 0.8, and aggregate functions have a complexity of 1, etc.

[0044] In this embodiment, the weights of different operations can be obtained in the following way: Preset weights based on operation type: The system presets basic weight values ​​for different types of operations. For example, join operations are given higher weights because they typically create more complex lineages; aggregation operations receive medium weights; while simple projection or filtering operations may receive lower weights.

[0045] Dynamic adjustments based on historical data: The system records the actual complexity and resource consumption of different operation types in lineage acquisition, and then periodically adjusts these weight values ​​through an adaptive optimization framework. For example, if it finds that a certain type of operation (such as window functions) generates particularly complex lineage relationships in actual execution, the system will automatically increase its weight.

[0046] Manual configuration based on business importance: Database administrators can manually adjust the weights of certain key operations according to the needs of specific business scenarios. For example, in a financial analysis system, the weights of operations related to financial calculations might be increased to ensure that the lineage of these operations is captured more completely.

[0047] Incremental weighting based on operation nesting depth: For nested operations (such as operations in subqueries), the system may increment their weights based on the nesting depth, reflecting that deeper nested operations usually bring more complex lineages.

[0048] As a specific implementation, the aforementioned weights are typically stored in a system configuration table and can be viewed and adjusted through a management interface. Simultaneously, the adaptive optimization framework periodically analyzes the effectiveness and performance impact of pedigree collection, proposing weight adjustment suggestions to help the system find the optimal balance between pedigree integrity and performance overhead.

[0049] As a preferred embodiment, the process of obtaining the lineage capture completeness of the i-th candidate path includes: S321, obtain the basic capture completeness of each node according to the type of each planned node of the i-th candidate path.

[0050] In this embodiment, the basic capture completeness of each type of node is known. For example, sequential scans receive the highest score because they can fully access the table data, while index scans receive a lower score because they may only access a portion of the data.

[0051] S322, the basic capture completeness of each node is corrected according to a preset correction factor to obtain the corrected capture completeness of each node; the preset correction factor includes the complexity of the filtering conditions, the complexity of the expression in the projection list, and the influence of the sub-plan.

[0052] In this embodiment, the core idea of ​​correcting the basic capture capability is to adjust the initially allocated capture rate through a systematic evaluation mechanism, making it more accurately reflect the lineage information collection capability of the execution plan nodes. As a specific implementation, the correction process mainly considers three types of factors: Filter condition complexity: Complex WHERE conditions provide richer inter-column relationship information, thus correction improves capture completeness; Complexity of expressions in the projection list: Expressions involving multi-column calculations reflect more data transformation paths, thus correction improves capture completeness; Impact of sub-plans: The lineage capture capability of a parent node is constrained by the capture rate of its child nodes; the corrected capture completeness of the parent node cannot exceed the highest score of all its child nodes. This correction mechanism ensures the consistency and accuracy of lineage capture scores, providing a reliable basis for execution plan selection. As an optional implementation, a specific correction process (including the situations requiring correction and the magnitude of correction under different situations) is pre-constructed for each preset correction factor, and the basic capture completeness is corrected based on this.

[0053] S323, obtain the lineage capture completeness of the i-th candidate path based on the corrected capture completeness and corresponding weight of each node.

[0054] In one specific implementation, the lineage capture completeness of the i-th candidate path is the sum of the products of the corrected capture completeness of each node and its corresponding weight. Optionally, the weight of each node in the i-th candidate path is determined according to the layer number of each node, wherein the weight of the bottom-level node in the i-th candidate path is greater than the weight of the middle-level node, the weight of the middle-level node is greater than the weight of the top-level node, and the weight of the top-level node is greater than the weight of the top-level node; the weights of the bottom-level node, the middle-level node, the top-level node, and the top-level node are preset and can be empirical values.

[0055] Based on S321-S323, the completeness of lineage capture for candidate paths can be accurately obtained.

[0056] S330: The candidate path with the lowest target cost is determined as the target path corresponding to the query tree.

[0057] S400, during the stage of executing the target path in the target database, capture dynamic lineage information and merge the dynamic lineage information with the table-level and column-level static dependencies of the target SQL query statement to obtain the table-level and column-level target dependencies of the target SQL query statement; the dynamic lineage information includes the actual number of rows processed, specific filter condition values, condition triggering conditions, and data flow path.

[0058] Those skilled in the art will understand that, guided by a mathematical model based on data lineage and execution plan, after the execution plan is generated, the query execution phase begins, where the executor is responsible for actually retrieving data from the storage engine, performing calculations, and returning results according to the instructions of the plan tree.

[0059] While the optimization phase provides a precise execution blueprint, some dynamic information (such as the actual number of rows processed, specific filter condition values, and condition triggering conditions) can only be determined at runtime. Capturing this information during the execution phase can further enrich the lineage record and provide a more complete execution context.

[0060] This embodiment inserts lineage collection points into the key functions and node processing logic of the executor. The specific implementation is as follows: First, in the executor startup function ExecutorStart, when initializing the execution state (EState) and plan state tree (PlanState) of the entire query, the lineage tracker ExecutionLineageTracker for the execution phase is initialized through the callback on_executor_start_initialize_tracker_callback. This tracker is associated with LineageCollector and is used to collect dynamic information during execution.

[0061] Secondly, lineage collection points need to be inserted within the core driver function `ExecutorRun` and the recursively called node processing functions (such as `ExecSeqScan`, `ExecIndexScan`, `ExecNestLoop`, `ExecHashJoin`, `ExecAgg`, etc.). These collection points are responsible for recording key runtime information in real time during node data processing. For example, for scan operations (such as `ExecSeqScan` and `ExecIndexScan`), it is necessary to record the actual table OID and column attribute number accessed, count the number of tuples actually scanned and the final number of tuples that meet the filtering conditions, and capture the specific parameter values ​​or comparison values ​​involved in the filtering conditions when necessary (performance overhead needs to be considered). For join operations (such as `ExecNestLoop`, `ExecHashJoin`, and `ExecMergeJoin`), it is necessary to record the actual join algorithm and execution details used, count the number of left and right input rows processed by the join operation and the number of final output rows, and capture the actual join conditions and filtering conditions applied. For aggregation and sorting operations (such as ExecAgg and ExecSort), it is necessary to record the actual execution of grouping and sorting (such as the number of buckets for hash aggregation and the memory / disk space used for sorting), count the changes in the number of rows before and after aggregation, and capture the details of the specific aggregation calculation process when needed. For data modification operations (INSERT / UPDATE / DELETE, usually handled by the ExecModifyTable node), it is necessary to record the actual tables and columns modified, accurately count the number of rows affected (insertion / update / deletion), and capture snapshots of data values ​​before and after modification. This is very useful for data auditing or backtracking scenarios, enabling value-level lineage tracing.

[0062] Finally, when the executor termination functions `ExecutorFinish` and `ExecutorEnd` complete all data processing and are ready to return results or clean up resources, a final summary is performed via `on_executor_end_finalize_lineage_callback`. The runtime dynamic information collected by `ExecutionLineageTracker` is merged with the static lineage information accumulated in `LineageCollector` to form a complete lineage record. Simultaneously, the `end_time` of the query, the final execution status, and possible performance statistics (such as actual execution time and number of rows affected) in the `pg_lineage.query_record` table are updated.

[0063] As a specific implementation method, merging dynamic lineage information with static dependencies includes: for lineage paths that already exist in static dependencies, dynamic information is used to supplement and verify them, such as adding the actual amount of data processed, the actual effect of filtering conditions, etc.; for new lineage paths that were not captured by static analysis but were discovered at runtime (such as dynamic SQL, code paths for conditional execution), they are added as new lineage relationships; when there is a conflict between static and dynamic information, dynamic information is preferred because it reflects the actual execution situation.

[0064] S500 retrieves the data lineage of the target SQL query statement based on the table-level and column-level target dependencies.

[0065] In this embodiment, the table-level and column-level target dependencies of the target SQL query statement already contain complete data lineage information. When presenting it to the user, only the presentation format of the collected information needs to be adjusted. For example, the lineage relationship can be standardized to ensure format consistency and facilitate storage and querying; the lineage relationship can be associated with metadata in the database directory (such as table structure definition, column attributes, constraint information, etc.) to provide richer contextual information; the processed lineage relationship can be stored in a dedicated lineage table structure and appropriate indexes can be created to support efficient querying.

[0066] This embodiment collects lineage information during the parsing, analysis, rewriting, planning, and execution phases of the target database. This ensures that the lineage information collection process comprehensively covers the entire process of the target database processing SQL queries, guaranteeing the completeness and accuracy of the acquired lineage dependencies. This is beneficial for constructing complete and accurate data lineage relationships. Furthermore, this embodiment modifies the target database planning phase. Compared to existing technologies that only consider initial costs (i.e., costs obtained based on existing optimizers), this embodiment also considers lineage factors. This results in a stronger degree of lineage capture in the execution path finally determined during the optimization phase, facilitating the acquisition of more complete lineage information and the construction of more complete and accurate data lineage relationships, while effectively controlling system performance overhead. Based on the more complete and accurate data lineage relationships of the target database obtained in this embodiment, better data sharing and integration between the target database and other databases in multi-source databases can be achieved.

[0067] While specific embodiments of the invention have been described in detail by way of example, those skilled in the art should understand that the examples are for illustrative purposes only and not intended to limit the scope of the invention. It should also be understood that various modifications can be made to the embodiments without departing from the scope and spirit of the invention. The scope of the invention is defined by the appended claims.

Claims

1. A method for obtaining multi-source data relationships based on a database kernel, characterized in that, The method includes the following steps: S100, in the stage of parsing and analyzing the target SQL query statement in the target database, extract the table and column information of the target SQL query statement, and identify the initial table-level and column-level dependencies of the target SQL query statement; the target database is any database in the multi-source database; S200, during the stage of rewriting the target SQL query statement in the target database, analyze whether the rewriting affects the table-level and column-level dependencies of the target SQL query statement. If it does, update the initial table-level and column-level dependencies of the target SQL query statement to obtain the intermediate table-level and column-level dependencies of the target SQL query statement. S300, in the stage of optimizing the query tree corresponding to the target SQL query statement in the target database, the target path corresponding to the query tree is determined according to the initial cost, lineage complexity, lineage capture completeness and preset lineage influence coefficient of each path corresponding to the query tree. The table-level and column-level intermediate dependencies of the target SQL query statement are replaced or supplemented according to the table-level and column-level lineage relationships extracted from the target path to obtain the table-level and column-level static dependencies of the target SQL query statement. S400, during the stage of executing the target path in the target database, capture dynamic lineage information and merge the dynamic lineage information with the table-level and column-level static dependencies of the target SQL query statement to obtain the table-level and column-level target dependencies of the target SQL query statement; the dynamic lineage information includes the actual number of rows processed, specific filter condition values, condition triggering conditions, and data flow path; S500 retrieves the data lineage of the target SQL query statement based on the table-level and column-level target dependencies.

2. The method for obtaining multi-source data relationships based on a database kernel according to claim 1, characterized in that, S100 includes: S110, when the main entry function for processing user SQL commands is called, a lineage collection initialization callback function is inserted; this lineage collection initialization callback function is used to assign a unique identifier to the target SQL query statement and initialize a memory structure for accumulating lineage information throughout the query processing lifecycle; S120, after the target database completes lexical and syntactic analysis of the target SQL query statement and generates the original parse tree, a lineage basic information extraction function is inserted. This lineage basic information extraction function is used to traverse the original parse tree, extract basic information, and store it in the memory structure. The basic information includes the query type, the table names involved, and the column names. S130, when performing semantic analysis on the target database, after converting the original analysis tree into a query tree that has undergone semantic verification and type checking, the lineage recognition callback function is called. This lineage recognition callback function is used to deeply analyze the internal details of the query tree, extract the initial table-level and column-level dependencies, and add them to the memory structure.

3. The method for obtaining multi-source data relationships based on a database kernel according to claim 2, characterized in that, S200 includes: S210, if the target SQL query statement references the view, it is determined that the rewriting affects the table-level and column-level dependencies of the target SQL query statement. When the view is expanded, the reference information of the view in the target SQL query statement, the underlying table and columns actually replaced after the view is expanded, and the mapping relationship between the output columns of the view and the columns of the underlying table are recorded. The mapping relationship is updated in the memory structure, and the lineage of the original columns pointing to the view is corrected to the columns pointing to the underlying table. S220, if a system rule is triggered and applied, it is determined that the rewriting affects the table-level and column-level dependencies of the target SQL query statement, and the triggered rule name, conditions and executed operations are recorded. The data transformation or redirection logic caused by the rule is tracked, and the lineage in the memory structure is modified accordingly to reflect the actual data flow after the rule is applied.

4. The method for obtaining multi-source data relationships based on a database kernel according to claim 1, characterized in that, The target path corresponding to the query tree is determined based on the initial cost, lineage complexity, lineage capture completeness, and preset lineage influence coefficient for each path. S310, obtain the initial cost of each path corresponding to the query tree, and select the path with the minimum initial cost of a preset number as the candidate path. S320, For any candidate path, the initial cost, lineage complexity, lineage capture completeness, and preset lineage influence coefficient of the candidate path are determined as the target cost of the candidate path; the target cost of any candidate path is positively correlated with the initial cost, lineage complexity, and preset lineage influence coefficient of the candidate path, and the target cost of any candidate path is negatively correlated with the lineage capture completeness of the candidate path. S330: The candidate path with the lowest target cost is determined as the target path corresponding to the query tree.

5. The method for obtaining multi-source data relationships based on a database kernel according to claim 4, characterized in that, The target cost of the i-th candidate path is c. i c i =c i,0 ×(1+k× l i ×(1-p i )), c i,0 Let be the initial cost of the i-th candidate path, and k be the preset lineage influence coefficient. l i Let p be the lineage complexity of the i-th candidate path. i The value of i ranges from 1 to n, representing the completeness of bloodline capture, where n is the number of candidate paths.

6. The method for obtaining multi-source data relationships based on a database kernel according to claim 4, characterized in that, The lineage complexity of the i-th candidate path is l i , l i =α×T+β×C+γ×∑ m j=1 (w j ×complexity j ), where α, β, and γ are the weights corresponding to the number of tables, the number of columns, and the operation type, respectively; T is the number of tables involved in the normalized target SQL query; C is the number of columns involved in the normalized target SQL query; m is the number of operations in the target SQL query; and complexity j Let w be the complexity of the j-th operation in the target SQL query statement. j Let i be the weight of the j-th operation in the target SQL query statement, where i ranges from 1 to n, and n is the number of candidate paths.

7. The method for obtaining multi-source data relationships based on a database kernel according to claim 4, characterized in that, The process of obtaining the lineage capture completeness of the i-th candidate path includes: S321, obtain the basic capture completeness of each node according to the type of each planned node of the i-th candidate path; S322, the basic capture completeness of each node is corrected according to a preset correction factor to obtain the corrected capture completeness of each node; the preset correction factor includes the complexity of the filtering conditions, the complexity of the expressions in the projection list, and the influence of the sub-plan. S323, obtain the lineage capture completeness of the i-th candidate path based on the corrected capture completeness and corresponding weight of each node.

Images