Inter-table relation analysis method and device and storage medium

Abstract

The invention discloses an inter-table relation analysis method and device and a storage medium, and relates to the technical field of electrical digital data process.The method comprises the steps that a to-be-processed SQL statement is converted into an abstract syntax tree; performing back root traversal on the abstract syntax tree, extracting a table connection condition and constructing a field mapping relation between a target table and a source table node by node for each traversed node according to a preset processing logic corresponding to a node type, the field mapping relationship comprises a target table field, a source table field and a processing function for performing value conversion or aggregation on the source table field; and determining an inter-table relationship between the target table and the source table according to the table connection condition and / or the field mapping relationship, wherein the inter-table relationship comprises at least one of an association relationship, a granularity relationship and a data set relationship. According to the method, the identification accuracy of the inter-table relationship between the target table and the source table is improved.

Description

Technical Field

[0001] This application relates to the field of electronic digital data processing technology, and in particular to a method, device and storage medium for analyzing inter-table relationships. Background Technology

[0002] In modern data warehouse modeling practices, to balance system performance and business flexibility, a design paradigm without foreign key constraints is commonly adopted, relying on ETL (Extract Transform Load) processes to implicitly ensure data consistency. However, this design results in a lack of explicit declarations of relationships between tables, making them difficult for the system to automatically identify and maintain.

[0003] Existing technical solutions often lack the ability to recursively trace field-level lineage when dealing with complex ETL jobs involving subqueries, WITH statements, nested UNIONs, and multiple SQL queries, making it difficult to construct a complete relationship graph across statements. This results in the identification of inter-table relationships heavily relying on manual analysis, making it difficult to guarantee the accuracy of such identification. Summary of the Invention

[0004] The main purpose of this application is to provide a method, device and storage medium for analyzing inter-table relationships, which aims to solve the technical problem that inter-table relationships rely on manual sorting and the accuracy of inter-table relationship identification is difficult to guarantee.

[0005] To achieve the above objectives, this application proposes a method for analyzing inter-table relationships, the method comprising: Convert the SQL statement to be processed into an abstract syntax tree; The abstract syntax tree is traversed back-root. For each node traversed, the table join conditions are extracted according to the preset processing logic corresponding to the node type, and the field mapping relationship between the target table and the source table is constructed node by node. The field mapping relationship includes the target table fields, the source table fields, and the processing functions that perform value transformation or aggregation on the source table fields. The table relationships between the target table and the source table are determined based on the table join conditions and / or the field mapping relationships. The table relationships include at least one of association relationships, granular relationships, and data set relationships.

[0006] In one embodiment, the step of determining the inter-table relationship between the target table and the source table based on the table join condition and the field mapping relationship includes: When it is determined that the source table field corresponds to the temporary table based on the field mapping relationship, the source table field is replaced with the corresponding physical source table field by tracing back according to the field mapping relationship corresponding to the temporary table. Based on the physical source table fields, the table join conditions, and the field mapping relationships, the inter-table relationships between the target table and the source table are determined.

[0007] In one embodiment, the steps of extracting table join conditions and constructing field mapping relationships between the target table and the source table node by node based on preset processing logic corresponding to the node type include: When the node type represents a data write operation, a field mapping relationship is constructed between the target table fields and the source table fields; When the node type represents a field projection operation, the field mapping relationship is constructed based on the corresponding field list; When the node type represents a table join operation, the table join condition is extracted based on the corresponding equality expression.

[0008] In one embodiment, the equality expression includes at least one of: a direct equality expression, an equality expression wrapped by a type conversion function, and an equality expression wrapped by a null value handling function.

[0009] In one embodiment, the step of determining the inter-table relationship between the target table and the source table based on the table join condition and the field mapping relationship includes: Based on the table join conditions and field mapping relationships corresponding to a single SQL statement, determine the granularity analysis results between the target table and the source table; Based on the execution order of each SQL statement within a single job, the granularity analysis results of each SQL statement are summarized, and job-level granularity relationships are generated based on the summarized granularity analysis results of each SQL statement.

[0010] In one embodiment, the step of determining the granularity analysis result between the target table and the source table based on the table join condition and the field mapping relationship corresponding to a single SQL statement includes: If, based on the field mapping relationship, it is determined that all fields in the target table are mapped to the primary key field of the same source table and there are no processing functions, then the granularity of the source table and the target table is consistent. If the fields of the target table are determined to be mapped only to the primary key fields of the main table of the source table and have no processing functions, and the table join condition includes an equi-value association between the primary key fields of the secondary table of the source table and the fields of the main table, then the granularity of the main table of the source table and the target table is determined to be consistent.

[0011] In one embodiment, the step of determining the inter-table relationship between the target table and the source table based on the table join condition and the field mapping relationship includes: Based on the business primary key field of the target table in the field mapping relationship, the processing function, and whether the table join condition includes a filtering operation, the data set relationship between the source table and the target table is determined; wherein, the data set relationship includes: inclusion, equality, or irrelevance.

[0012] In one embodiment, prior to the step of converting the SQL statement to be processed into an abstract syntax tree, the method further includes: Obtain input data for inter-table relationship analysis, the input data including at least one of the following: ETL-type stored procedure code, ETL job execution log, and a single SQL statement; The input data is cleaned to extract the SQL statements to be processed; If the input data is ETL-type stored procedure code, then conditional, loop, and dynamic SQL control statements are removed; if the input data is the ETL job's execution log, then statements in the execution log other than the executed SQL statements are removed.

[0013] In addition, to achieve the above objectives, this application also proposes an inter-table relationship analysis device, the device comprising: a memory, a processor, and a computer program stored in the memory and executable on the processor, the computer program being configured to implement the steps of the inter-table relationship analysis method as described above.

[0014] In addition, to achieve the above objectives, this application also proposes a storage medium, which is a computer-readable storage medium, on which a computer program is stored, and when the computer program is executed by a processor, it implements the steps of the table relationship analysis method described above.

[0015] In addition, to achieve the above objectives, this application also provides a computer program product, which includes a computer program that, when executed by a processor, implements the steps of the table relationship analysis method described above.

[0016] One or more technical solutions proposed in this application have at least the following technical effects: By automatically converting SQL statements into abstract syntax trees and performing post-root traversal, and combining the processing logic corresponding to the nodes, a mapping relationship containing the field processing history is constructed. Based on this, the relationships, granular relationships, and data set relationships between data tables are deduced, improving the accuracy of identifying the inter-table relationships between the target table and the source table. This achieves deep semantic parsing of complex ETL processing logic, changes the lagging mode that relies on manual sorting, improves the accuracy and efficiency of SQL parsing, and reduces the data traceability cost caused by complex business logic, deep nesting levels, or personnel changes. It provides a reliable data foundation for data management and ETL job optimization. Attached Figure Description

[0017] The accompanying drawings, which are incorporated in and form part of this specification, illustrate embodiments consistent with this application and, together with the description, serve to explain the principles of this application.

[0018] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, for those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0019] Figure 1 This is a flowchart illustrating an embodiment of the method for analyzing relationships between tables in this application. Figure 2 This is a flowchart illustrating Embodiment 2 of the method for analyzing relationships between tables in this application. Figure 3 This is a flowchart illustrating the temporary table processing provided in Embodiment 2 of the table relationship analysis method of this application. Figure 4 This is a flowchart illustrating the third embodiment of the method for analyzing relationships between tables in this application. Figure 5 This is a schematic diagram of the abstract syntax tree structure provided in Embodiment 3 of the method for analyzing inter-table relationships in this application; Figure 6 A schematic diagram illustrating the traversal order of the abstract syntax tree provided in Embodiment 3 of the table relationship analysis method of this application; Figure 7 This is a schematic diagram of the node processing results of the abstract syntax tree provided in Embodiment 3 of the table relationship analysis method of this application; Figure 8 This is a flowchart illustrating the fourth embodiment of the method for analyzing relationships between tables in this application. Figure 9 This is a flowchart illustrating Embodiment 5 of the method for analyzing relationships between tables in this application. Figure 10 This is a flowchart illustrating Embodiment Six of the method for analyzing relationships between tables in this application. Figure 11 A simplified flowchart is provided for Embodiment Six of the Method for Analyzing Relationships Between Forms in this Application. Figure 12 This is a schematic diagram of the hardware operating environment involved in the table relationship analysis method in this application embodiment.

[0020] The purpose, features, and advantages of this application will be further explained in conjunction with the embodiments and with reference to the accompanying drawings. Detailed Implementation

[0021] It should be understood that the specific embodiments described herein are merely illustrative of the technical solutions of this application and are not intended to limit this application.

[0022] To better understand the technical solution of this application, a detailed description will be provided below in conjunction with the accompanying drawings and specific implementation methods.

[0023] Existing solutions lack recursive tracing capabilities for complex syntax structures including subqueries, WITH statements, nested UNIONs, and multiple SQL chaining. They are difficult to construct field-level lineage graphs across statements, resulting in reliance on manual sorting of inter-table relationships and making it difficult to guarantee the accuracy of inter-table relationship identification.

[0024] The main solution of this application embodiment is: converting the SQL statement to be processed into an abstract syntax tree; performing a back-root traversal on the abstract syntax tree, and for each node traversed, extracting table join conditions and constructing a field mapping relationship between the target table and the source table node by node according to the preset processing logic corresponding to the node type. The field mapping relationship includes target table fields, source table fields, and processing functions that perform value transformation or aggregation on the source table fields; determining the inter-table relationship between the target table and the source table based on the table join conditions and / or field mapping relationship. The inter-table relationship includes at least one of association relationship, granular relationship, and data set relationship.

[0025] In this embodiment, for ease of description, the following description uses the inter-table relationship analysis device as the execution subject.

[0026] This application provides a solution that automatically converts SQL statements into an abstract syntax tree and performs a post-root traversal. By combining the processing logic corresponding to each node, a mapping relationship containing the field processing history is constructed. Based on this, the relationships, granular relationships, and data set relationships between data tables are derived. This improves the accuracy of identifying the inter-table relationships between the target table and the source table, realizes deep semantic parsing of complex ETL processing logic, changes the lagging mode that relies on manual sorting, improves the accuracy and efficiency of SQL parsing, and reduces the data traceability cost caused by complex business logic, deep nesting levels, or personnel changes. It provides a reliable data foundation for data management and ETL job optimization.

[0027] It should be noted that the execution subject table relationship analysis device in this embodiment can be a computing service device with data processing, network communication, and program execution functions, such as a tablet computer, personal computer, mobile phone, etc., or an electronic device capable of performing the above functions. The following description uses a computing service device as an example to illustrate this embodiment and the subsequent embodiments.

[0028] Based on this, embodiments of this application provide a method for analyzing inter-table relationships, referring to... Figure 1 , Figure 1 This is a flowchart illustrating the first embodiment of the inter-table relationship analysis method of this application.

[0029] In this embodiment, the inter-table relationship analysis method includes steps S10 to S30: Step S10: Convert the SQL statement to be processed into an abstract syntax tree.

[0030] In this embodiment, an ETL job script, after cleaning, yields N independent SQL (Structured Query Language) statements. Each SQL statement is parsed into an AST (Abstract Syntax Tree), ultimately resulting in N abstract syntax trees.

[0031] An SQL parser performs lexical and syntactic analysis on SQL statements. The parser identifies keywords, identifiers, operators, and expressions in the SQL and organizes them into an abstract syntax tree according to the SQL's grammatical rules. Optionally, keywords include fields such as SELECT, FROM, and JOIN, while identifiers include table names and field names.

[0032] Alternatively, the SQL parser can be Apache Calcite, ANTLR4, Druid, etc. Among them, Apache Calcite is an open-source data management framework that focuses on SQL parsing, query optimization, and execution; ANTLR4 is a powerful parser generator used to build language recognition tools; and Druid is a high-performance JDBC database connection pool with built-in SQL parsing and monitoring functions.

[0033] Abstract Syntax Tree (AST) transforms SQL statements from strings into structured objects, enabling programs to systematically access and analyze every logical part of the SQL statement.

[0034] Optionally, the abstract syntax tree includes a root node, intermediate nodes, and leaf nodes. Optionally, the root node of the abstract syntax tree represents the entire query statement, such as QueryNode or SelectNode; the intermediate nodes of the abstract syntax tree represent the main clauses and operations, such as JoinNode (join), ProjectNode (projection), FilterNode (filtering), UnionNode (union), and SubQueryNode (subquery); the leaf nodes of the abstract syntax tree represent specific operation objects, such as TableNode (table name), IdentifierNode (field name), and LiteralNode (constant value).

[0035] Before identifying relationships between tables, SQL statements need to be converted from text into an abstract syntax tree (AST). This conversion is done by the SQL parser. The general structure of an AST is shown in the table below, illustrating how table relationship identification is performed.

[0036]

[0037] Step S20: Perform a back-root traversal on the abstract syntax tree. For each node traversed, extract the table join conditions and construct the field mapping relationship between the target table and the source table node by node according to the preset processing logic corresponding to the node type. The field mapping relationship includes the target table fields, the source table fields, and the processing functions for value transformation or aggregation of the source table fields.

[0038] In this application, post-root traversal starts from the leaf nodes of the abstract syntax tree (AST), processes intermediate nodes level by level upwards, and finally reaches the root node of the AST. Post-root traversal ensures that information is aggregated and passed upwards from leaf nodes such as fields and table names, ultimately forming a complete table relationship at the root node representing the complete SQL operation.

[0039] Table join conditions are expressions in SQL statements that explicitly join two or more tables. Table join conditions define how data is matched between tables and are the direct source of the relationships between them. Table join conditions indicate the horizontal association between tables.

[0040] Field mapping relationships refer to the data flow and transformation from fields in the source table to fields in the target table. They describe the process by which fields are processed from the source table and ultimately written into the target table. Field mapping relationships represent the vertical data flow between fields.

[0041] Different node types in the abstract syntax tree correspond to different pre-defined processing logic. As shown in the table below:

[0042] Step S30: Determine the inter-table relationship between the target table and the source table based on the table join conditions and / or the field mapping relationship. The inter-table relationship includes at least one of association relationship, granular relationship and data set relationship.

[0043] Inter-table relationships represent the relationship between the target table and the source table. Optionally, the association relationship is determined based on the table join condition. Optionally, the granularity relationship or data set relationship is determined based on the table join condition and field mapping relationship.

[0044] In this application, the relationship is similar to the foreign key relationship in a table. If field A1 of table A and field B1 of table B satisfy the following two embodiments, it means that field A1 of table A and field B1 of table B are related.

[0045] In one embodiment, conditions similar to `tableA.A1 = tableB.B1` appear in the `JOIN`, `WHERE`, or `HIVING` clauses of SQL statements. Conditions like `tableA.A1 = tableB.B1` include: `tableA.A1 = tableB.B1`, indicating that `tableA.A1` and `tableB.B1` are directly equal; `tableA.A1 = cast(tableB.B1 as some type)`, where `cast` includes `try_cast`, indicating that after type conversion, `tableB.B1` is equal to `tableA.A1`; `tableA.A1 = coalesce(tableB.B1, constant / NULL)`, indicating that after handling null values ​​in `tableB.B1`, it is equal to `tableA.A1`. Example 1: `tableA JOIN tableB on tableA.A1 = tableB.B1`; Example 2: `tableA, tableB where tableA.A1 = tableB.B1`. Example 1 and Example 2 show how to join the rows in table A and table B that satisfy the condition that the value of field A1 is equal to the value of field B1.

[0046] In another embodiment, table A.A1 is added to field T1 of temporary table T without any processing. Field T1 of table T and field B1 of table B satisfy the conditions of the previous embodiment. For example, SQL1: Insert into table T(T1) select table A.A1 from table A, means to retrieve all data from field A1 of table A and insert it into field T1 of table T unchanged; SQL2: table T JOIN table B on table T.T1=table B.B1, means to join temporary table T with physical table B, the join condition is that the value of field T.T1 is equal to the value of field B.B1, and table T.T1 and table B.B1 have an equi-relationship.

[0047] In this application, the granularity of a table refers to the smallest unit of the entity described by each record in the table, and is the core dimension for measuring the level of detail in the data. Granularity determines the aggregability and analytical depth of the data. If the business entities described by two tables have the same smallest unit, i.e., consistent granularity, then the two tables are considered to have the same granularity; otherwise, the granularity is inconsistent.

[0048] In this application, if Table A and Table B have the same granularity, then Table A and Table B have the following data set relationship: if the business primary key data of Table A is a subset of the business primary key data of Table B, then Table A... Table B; if the business primary key data of Table A is the same as the business primary key data of Table B, then Table A = Table B; if the business primary key data of Table A and Table B are mutually exclusive, then their data sets are unrelated.

[0049] In one embodiment, after step S30, the method further includes: generating a relationship graph between the target table and the source table based on the association relationship, granularity relationship, and data set relationship.

[0050] In the technical solution of this embodiment, by automatically converting SQL statements into abstract syntax trees and performing post-root traversal, and combining the processing logic corresponding to the nodes, a mapping relationship containing the field processing history is constructed. Based on this, the association relationship, granular relationship and data set relationship between data tables are derived, which improves the accuracy of identifying the inter-table relationship between the target table and the source table, realizes deep semantic parsing of complex ETL processing logic, changes the lagging mode that relies on manual sorting, improves the accuracy and efficiency of SQL parsing, reduces the data traceability cost caused by complex business logic, deep nesting levels or personnel changes, and provides a reliable data foundation for data management and ETL job optimization.

[0051] Based on any embodiment of this application, in the second embodiment of this application, the content that is the same as or similar to the above embodiments can be referred to the above description, and will not be repeated hereafter. Based on this, please refer to... Figure 2 Step S30 also includes: Step S31: When it is determined that the source table field corresponds to the temporary table according to the field mapping relationship, trace back according to the field mapping relationship corresponding to the temporary table, and replace the source table field with the corresponding physical source table field; Step S32: Based on the physical source table fields, the table join conditions, and the field mapping relationship, determine the table relationship between the target table and the source table.

[0052] The processing scripts heavily utilize temporary tables to store intermediate results, and extracting the relationships between these temporary tables and other tables is meaningless to the user. Ignoring temporary tables directly will result in the loss of crucial relationships between physical tables, leading to incomplete relationship identification.

[0053] By tracing the execution order of SQL statements and the corresponding field mapping relationships in the temporary tables, the source table fields are replaced with their corresponding physical source table fields, thereby constructing the relationships between the physical tables. Figure 3 This demonstrates the complete data flow from the source table through joins, transformations, and mappings before being inserted into the target temporary table. SQL1 shows the field mapping relationship from physical table 1 to the temporary table. SQL2 filters the customer data linked between the two physical tables and stores it in the temporary table. It then joins physical table 1 and physical table 2 using an INNER JOIN with the join condition T6.Core_Cust_Id = T5.Orig_Cust_Id, and uses the coalesce function to process the fields. The diagram clearly illustrates the table relationship between physical table 1 and physical table 2, showing that the two physical tables are connected via an INNER JOIN using the Core_Cust_Id and Orig_Cust_Id fields.

[0054] In the technical solution of this embodiment, when a field is identified as originating from a temporary table, it automatically traces back to the original physical source table along its field mapping relationship. This effectively penetrates the data gap caused by the use of temporary tables in the ETL process, eliminates the interference of intermediate carriers on the analysis of inter-table relationships, and completely and accurately restores the real data association and business semantics between the originally obscured and fragmented physical tables. Finally, it generates inter-table relationships and improves the accuracy of relationship identification in complex data processing environments.

[0055] Based on any embodiment of this application, in the third embodiment of this application, the content that is the same as or similar to the above embodiments can be referred to the above description, and will not be repeated hereafter. Based on this, please refer to... Figure 4 Step S20 also includes: Step S21: When the node type is a node representing a data write operation, construct a field mapping relationship between the target table fields and the source table fields; Step S22: When the node type is a node representing a field projection operation, construct the field mapping relationship based on the corresponding field list; Step S23: When the node type is a node representing a table join operation, extract the table join condition based on the corresponding equality expression.

[0056] Optionally, the node representing a data write operation includes CreateTableAsNode, InsertNode, or MergeNode; the node representing a field projection operation is ProjectNode; and the node representing a table join operation is JoinNode.

[0057] In one embodiment, for CreateTableAsNode, InsertNode, and MergeNode nodes, a field mapping relationship between the target table fields and the source table fields is constructed; for ProjectNode nodes, the output field names and referenced lower-level node fields are obtained from the SELECT field list to construct a field mapping relationship; for UnionNode nodes, the output field names are obtained from the first child node, and the source table fields of each child node are merged; for JoinNode nodes, equivalent expressions are extracted from the ON, USING, and WHERE conditions to output the inter-table relationships of the physical tables.

[0058] In an optional embodiment, at least one of a direct equality expression, an equality expression wrapped in a type conversion function, and an equality expression wrapped in a null value handling function.

[0059] Identifying table relationships from the processing logic of data warehouse workflow scripts presents a challenge due to complex SQL syntax structures. Some SQL statements contain complex structures such as subqueries and WITH statements, hiding the true field relationships. It is necessary to penetrate these complex syntax structures to extract meaningful table relationships.

[0060] To address this issue, a two-phase process is employed. In the first phase, relationships are extracted from the abstract syntax tree of a single SQL statement using a post-root traversal. The following example illustrates the entire process using a single SQL statement.

[0061] For example, the SQL is as follows: Select a.id, b.name from tableA a left join tableB b on a.id=b.id Union all Select c.id, d.name from tableC c left join tableD d on c.id=d.id The SQL query consists of two parts, connected by UNION ALL. In the first part, tableA and tableB are left-joined. Using tableA as the primary table, all records are retrieved. For each record in tableA, a matching ID is searched in tableB. If a match is found, tableB.name is retrieved; otherwise, tableB.name is set to NULL. The result set contains all tableA IDs and their matching tableB names; otherwise, NULL is returned. In the second part, tableC and tableD are left-joined. Using tableC as the primary table, all records are retrieved. For each tableC record, a matching ID is searched in tableD. If a match is found, tableD.name is retrieved; otherwise, tableD.name is set to NULL. The result set contains all tableC IDs and their matching tableD names; otherwise, NULL is returned.

[0062] The abstract syntax tree corresponding to the above SQL is as follows: Figure 5 As shown, TableNode is the leaf node, JoinNode and ProjectNode are intermediate nodes, and UnionNode is the root node. The order of the root traversal of the abstract syntax tree is as follows: Figure 6As shown, the process first iterates through the TableNode, JoinNode, and ProjectNode to the left of the UnionNode, and then iterates through the TableNode, JoinNode, and ProjectNode to the right of the UnionNode.

[0063] Based on the processing logic corresponding to different nodes in the table above, the processing results of each node in the SQL are as follows: Figure 7 As shown. In the left-hand portion of the UnionNode, the processing results of the JoinNode include: TableA and TableB are associated through tableA.id and tableB.id. The processing results of the ProjectNode include: <id,[<TableA,[id]> ],[]>, <name,[<TableB,[name]> In the right-hand side of UnionNode, the processing results of JoinNode include: TableC and TableD are associated through tableC.id and tableD.id, and the processing results of ProjectNode include: <id,[<TableC,[id]> ],[]>, <name,[<TableD,[name]> The processing results of UnionNode include: <id,[<TableA,[id]> ], [<TableC,[id]> ],[]>, <name,[<TableB,[name]> ], [<TableD,[name]> ],[]>.

[0064] In this embodiment, by designing different processing rules for node types with different semantics, the complex SQL parsing task is decomposed into precise targeted analysis, achieving automated and structured parsing of the entire data processing chain. From source table fields and processing functions to table join conditions, a complete and accurate field mapping relationship and table join conditions are constructed, providing reliable and directly usable data support for subsequent data governance, impact analysis, and self-service data analysis.

[0065] Based on any embodiment of this application, in the fourth embodiment of this application, the content that is the same as or similar to the above embodiments can be referred to the above description, and will not be repeated hereafter. Based on this, please refer to... Figure 8 Step S30 also includes: Step S33: Determine the granularity analysis results between the target table and the source table based on the table join conditions and field mapping relationships corresponding to a single SQL statement; Step S34: Based on the execution order of each SQL statement within a single job, summarize the granularity analysis results of each SQL statement, and generate job-level granularity relationships based on the summarized granularity analysis results of each SQL statement.

[0066] Determine the syntactic features of a single SQL statement, including whether the SELECT clause contains only the primary key of the source table, whether JOIN exists and its type, and whether GROUP BY, WHERE, or HAVING filtering conditions exist. Based on the syntactic features, determine the granular analysis results of the source and target tables of the SQL statement.

[0067] During data processing, the granularity of tables and the relationships between data sets can change. For example, customer transaction details may be aggregated into summary data. Analyzing the consistency of table granularity can guide users in selecting tables with appropriate granularity and data sets during data analysis, avoiding analytical errors caused by ambiguous granularity.

[0068] In an optional embodiment, step S33 includes: determining that all fields of the target table are mapped to the primary key field of the same source table and have no processing functions based on the field mapping relationship, then the granularity of the source table and the target table is consistent; determining that only the fields of the target table are mapped to the primary key field of the main table of the source table and have no processing functions based on the field mapping relationship, and that the table join condition includes an equi-value association between the primary key field of the secondary table of the source table and the field of the main table, then the granularity of the main table of the source table and the target table is consistent. This effectively solves the risk of erroneous associations caused by ambiguous granularity in traditional data integration and analysis, provides key quality verification basis for data modeling, ETL development, and analysis queries, and significantly improves the accuracy and reliability of data application results.

[0069] The entire analysis process employs a three-stage analysis method. Stage one involves SQL-level granularity judgment. For each SQL type, corresponding rules are used to analyze the granularity relationships. The main rules for this stage are as follows: Rule 1: Does the SELECT clause in the SQL statement only contain the primary key of the source table? If so, the granularity of the source table and the target table is usually consistent, and it can be used as a candidate for further judgment.

[0070] Rule 2: Does the SQL statement contain a join statement, and does the primary key of the child table participate in the join? If so, combined with Rule 1, it can be determined that the granularity of the primary table and the target table is consistent.

[0071] In the process of identifying granular relationships and data set relationships using the above rules, similar to the method for identifying association relationships, we construct <target table field name, <source table name, source field list>[], processing function[]> output. The construction process will not be repeated below, but only the content related to granularity identification will be retained. For example, the granularity relationship identification between tables is shown in the following table:

[0072] Phase Two involves job-level granularity aggregation. Based on the SQL execution order, the granularity analysis results of each SQL statement are aggregated to identify the granularity relationship and data set relationship between the target table and the source table from the perspective of a single job. The main rule for identifying job-level granularity relationships in this process is: if the granularity analysis results of any single SQL statement are inconsistent, the job-level granularity relationship is determined to be inconsistent; if the granularity analysis results of all SQL statements are consistent, the job-level granularity relationship is determined to be consistent.

[0073] Phase three involves a summary analysis of multiple tasks. If multiple tasks process the same table simultaneously, the method from Phase two is used to merge and analyze all tasks, ensuring the accuracy of the granularity relationship analysis.

[0074] In this embodiment, a two-stage analysis model—combining SQL statement-level granularity analysis with job-level granularity relationship summarization—effectively solves the challenge of tracking and determining the dynamic evolution of data granularity during ETL processing. It accurately depicts the complete transmission and transformation path of data granularity from the source table to the final target table, not only identifying granularity changes caused by single-step operations but also comprehensively analyzing the cumulative impact of the entire workflow on data granularity based on the execution order. This fundamentally avoids the problem of misjudging overall data consistency risks by only examining local SQL statements, providing a reliable granularity consistency view across jobs and steps, ensuring the semantic correctness of data during integration and analysis, and improving the controllability of complex data processing workflows and the reliability of analysis results.

[0075] Based on any embodiment of this application, in the fifth embodiment of this application, the content that is the same as or similar to the above embodiments can be referred to the above description, and will not be repeated hereafter. Based on this, please refer to... Figure 9 Step S30 also includes: Step S35: Determine the data set relationship between the source table and the target table based on the business primary key field of the target table in the field mapping relationship, the processing function, and whether the table join condition includes a filtering operation; wherein, the data set relationship includes: inclusion, equality, or irrelevance.

[0076] In this embodiment, the INNER JOIN, WHERE, HAVING filtering conditions and UNION operations in the SQL are analyzed to determine the data set relationship between the source table and the target table. Specifically, if the data set corresponding to the business primary key field of the target table is a subset of the data set corresponding to the business primary key field of the source table, then the data set of the target table is determined to be contained within the data set of the source table. If the data set corresponding to the business primary key field of the target table is the same as the data set corresponding to the business primary key field of the source table, then the data set of the target table is determined to be equal to the data set of the source table. If the data set corresponding to the business primary key field of the target table and the data set corresponding to the business primary key field of the source table do not have an inclusion relationship, then the data sets of the two tables are determined to be unrelated.

[0077] In an optional embodiment, if the table join condition includes an INNER JOIN type join or a WHERE filter condition, the data set relationship is determined to be that the data set of the target table is a subset of the data set of the source table; if the table join condition includes a UNION operation, the data set relationship is determined to be that the data set of the target table is the parent set of the data sets of each source table participating in the UNION operation.

[0078] During data processing, the data sets in the tables may change. The entire analysis process employs a three-stage analysis method. Stage one involves SQL-level granularity judgment, using corresponding rules to analyze the relationships between the data sets based on the SQL type. The main rules for this stage are as follows: Rule 3: Does the SQL statement contain INNER JOIN or WHERE conditions? If so, the dataset in the main table is usually larger than that in the target table.

[0079] Rule 4: Does the SQL statement contain UNION clauses? If so, the dataset in the parent table is usually smaller than that in the target table. If each UNION clause contains data filtering, the filtering conditions need to be further analyzed to determine the relationships between the datasets.

[0080] In the process of identifying the data set using the above rules, similar to the method of identifying relationships, we construct <target table field name, <source table name, source field list>[], processing function[]> output. The construction process will not be repeated below, but only the content related to data set identification will be retained.

[0081]

[0082] Phase two is job-level aggregation. Based on the SQL execution order, the analysis results of the data set relationships of each SQL are summarized to identify the data set relationships between the target table and the source table from the perspective of a single job.

[0083] Phase 3 involves summarizing and analyzing multiple jobs. If multiple jobs process the same table simultaneously, the method from Phase 2 is used to merge and analyze all jobs to ensure the accuracy of the dataset analysis.

[0084] In this embodiment, the technical solution achieves automated and refined determination of data set relationships by comprehensively analyzing the business primary key field, processing functions, and filtering semantics in the table join conditions within the field mapping relationship. It accurately infers changes in the set range of data during processing, whether due to subset relationships caused by filtering, superset relationships caused by merging, or disjoint relationships caused by transformation. This reveals the data coverage logic between tables without actually executing data queries.

[0085] Based on any embodiment of this application, in the sixth embodiment of this application, the content that is the same as or similar to the above embodiments can be referred to the above description, and will not be repeated hereafter. Based on this, please refer to... Figure 10 Before step S10, the following are also included: Step S40: Obtain input data for inter-table relationship analysis. The input data includes at least one of the following: ETL-type stored procedure code, ETL job execution log, and a single SQL statement. Step S50: Clean the input data and extract the SQL statements to be processed; wherein, if the input data is ETL-type stored procedure code, conditional, loop and dynamic SQL control statements are cleaned up; if the input data is the ETL job's running log, statements in the running log other than the executed SQL statements are cleaned up.

[0086] This embodiment is applicable to extracting table relationships from ETL processing job code of a single SQL statement or similar stored procedure running on a relational database or Hive using Hadoop.

[0087] Throughout the entire processing flow, refer to Figure 11 Different inputs require different processing methods. ETL stored procedure code typically contains control statements such as conditions, loops, and dynamic SQL. These statements are useless for identifying table joins and therefore need to be cleaned up. The ETL stored procedure code execution log is the execution log of the ETL job, containing every SQL statement executed and its results. Therefore, information other than the executed SQL statements needs to be cleaned up. Individual SQL statements do not require cleanup.

[0088] In this embodiment, by designing differentiated cleaning rules, it can automatically receive and uniformly process heterogeneous inputs from multiple sources, such as ETL stored procedures, job logs, and single SQL statements. This effectively removes noise irrelevant to analysis, such as control statements and log information, and accurately extracts a set of structured SQL statements. Unstructured production environment code and logs are transformed into standard inputs that can be directly consumed by subsequent automated analysis processes. This fundamentally solves the problem of parsing failures or misjudgments of relationships caused by the mixing of raw data, laying a solid and high-quality data foundation for end-to-end, highly reliable inter-table relationship analysis.

[0089] It should be noted that the above examples are only for understanding this application and do not constitute a limitation on the inter-table relationship analysis method of this application. Any simple transformations based on this technical concept are within the protection scope of this application.

[0090] This application provides an inter-table relationship analysis device, which includes: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, which are executed by the at least one processor to enable the at least one processor to perform the inter-table relationship analysis method in Embodiment 1 above.

[0091] The following is for reference. Figure 12 The diagram illustrates a structural schematic of a table relationship analysis device suitable for implementing embodiments of this application. The table relationship analysis device in these embodiments may include, but is not limited to, mobile terminals such as mobile phones, laptops, tablets (PADs), and fixed terminals such as desktop computers. Figure 12 The table relationship analysis device shown is merely an example and should not impose any limitations on the functionality and scope of use of the embodiments of this application.

[0092] like Figure 12As shown, the inter-table relationship analysis device may include a processing unit 1001 (e.g., a central processing unit, a graphics processing unit, etc.) that can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 1002 or a program loaded from a storage device 1003 into a random access memory (RAM) 1004. The RAM 1004 also stores various programs and data required for the operation of the inter-table relationship analysis device. The processing unit 1001, ROM 1002, and RAM 1004 are interconnected via a bus 1005. An input / output (I / O) interface 1006 is also connected to the bus. Typically, the following systems can be connected to the I / O interface 1006: input devices 1007 including, for example, a touchscreen, touchpad, keyboard, mouse, image sensor, microphone, accelerometer, gyroscope, etc.; output devices 1008 including, for example, a liquid crystal display (LCD), speaker, vibrator, etc.; storage devices 1003 including, for example, magnetic tape, hard disk, etc.; and communication devices 1009. Communication device 1009 allows the inter-table relationship analysis device to communicate wirelessly or wiredly with other devices to exchange data. Although the figure shows inter-table relationship analysis devices with various systems, it should be understood that it is not required to implement or possess all of the systems shown. More or fewer systems may be implemented alternatively.

[0093] Specifically, according to the embodiments disclosed in this application, the processes described above with reference to the flowcharts can be implemented as computer software programs. For example, embodiments disclosed in this application include a computer program product comprising a computer program carried on a computer-readable medium, the computer program containing program code for performing the methods shown in the flowcharts. In such embodiments, the computer program can be downloaded and installed from a network via a communication device, or installed from storage device 1003, or installed from ROM 1002. When the computer program is executed by processing device 1001, it performs the functions defined in the methods of the embodiments disclosed in this application.

[0094] The table relationship analysis device provided in this application, employing the table relationship analysis method in the above embodiments, can solve the technical problem that table relationships rely on manual sorting, making it difficult to guarantee the accuracy of table relationship identification. Compared with the prior art, the beneficial effects of the table relationship analysis device provided in this application are the same as those of the table relationship analysis method provided in the above embodiments, and other technical features in this table relationship analysis device are the same as those disclosed in the previous embodiment method, and will not be repeated here.

[0095] It should be understood that the various parts disclosed in this application can be implemented using hardware, software, firmware, or a combination thereof. In the description of the above embodiments, specific features, structures, materials, or characteristics can be combined in any suitable manner in one or more embodiments or examples.

[0096] The above description is merely a specific embodiment of this application, but the scope of protection of this application 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 this application should be included within the scope of protection of this application. Therefore, the scope of protection of this application should be determined by the scope of the claims.

[0097] This application provides a computer-readable storage medium having computer-readable program instructions (i.e., a computer program) stored thereon, the computer-readable program instructions being used to execute the table relationship analysis method in the above embodiments.

[0098] The computer-readable storage medium provided in this application may be, for example, a USB flash drive, but is not limited to, electrical, magnetic, optical, electromagnetic, infrared, or semiconductor systems or devices, or any combination thereof. More specific examples of computer-readable storage media may include, but are not limited to: electrical connections having one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fibers, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof. In this embodiment, the computer-readable storage medium may be any tangible medium containing or storing a program that can be used by or in conjunction with an instruction execution system, system, or device. The program code contained on the computer-readable storage medium may be transmitted using any suitable medium, including but not limited to: wires, optical cables, radio frequency (RF), etc., or any suitable combination thereof.

[0099] The aforementioned computer-readable storage medium may be included in the inter-table relationship analysis device; or it may exist independently and not be assembled into the inter-table relationship analysis device.

[0100] The aforementioned computer-readable storage medium carries one or more programs. When these programs are executed by the inter-table relationship analysis device, the device automatically converts SQL statements into an abstract syntax tree and performs a post-root traversal. Combining this with the processing logic corresponding to each node, it constructs a mapping relationship containing the field processing history. Based on this, it derives the association relationships, granular relationships, and data set relationships between data tables. This improves the accuracy of identifying the inter-table relationships between the target table and the source table, achieves deep semantic parsing of complex ETL processing logic, changes the lagging mode that relies on manual sorting, improves the accuracy and efficiency of SQL parsing, reduces the data traceability cost caused by complex business logic, deep nesting levels, or personnel changes, and provides a reliable data foundation for data management and ETL job optimization.

[0101] Computer program code for performing the operations of this application can be written in one or more programming languages ​​or a combination thereof, including object-oriented programming languages ​​such as Java, Smalltalk, and C++, as well as conventional procedural programming languages ​​such as the "C" language or similar programming languages. The program code can be executed entirely on the user's computer, partially on the user's computer, as a standalone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In cases involving remote computers, the remote computer can be connected to the user's computer via any type of network—including a local area network (LAN) or a wide area network (WAN)—or can be connected to an external computer (e.g., via the Internet using an Internet service provider).

[0102] The flowcharts and block diagrams in the accompanying drawings illustrate the architecture, functionality, and operation of possible implementations of systems, methods, and computer program products according to various embodiments of this application. In this regard, each block in a flowchart or block diagram may represent a module, segment, or portion of code containing one or more executable instructions for implementing a specified logical function. It should also be noted that in some alternative implementations, the functions indicated in the blocks may occur in a different order than those indicated in the drawings. For example, two consecutively indicated blocks may actually be executed substantially in parallel, and they may sometimes be executed in reverse order, depending on the functions involved. It should also be noted that each block in the block diagrams and / or flowcharts, and combinations of blocks in the block diagrams and / or flowcharts, can be implemented using a dedicated hardware-based system that performs the specified function or operation, or using a combination of dedicated hardware and computer instructions.

[0103] The modules described in the embodiments of this application can be implemented in software or hardware. The names of the modules do not necessarily limit the functionality of the unit itself.

[0104] The readable storage medium provided in this application is a computer-readable storage medium that stores computer-readable program instructions (i.e., a computer program) for executing the above-described inter-table relationship analysis method. This solves the technical problem that inter-table relationships rely on manual analysis, making it difficult to guarantee the accuracy of relationship identification. Compared with the prior art, the beneficial effects of the computer-readable storage medium provided in this application are the same as those of the inter-table relationship analysis method provided in the above embodiments, and will not be elaborated upon here.

[0105] This application also provides a computer program product, including a computer program that, when executed by a processor, implements the steps of the table relationship analysis method described above.

[0106] The computer program product provided in this application can solve the technical problem that the accuracy of identifying table relationships is difficult to guarantee due to reliance on manual sorting. Compared with the prior art, the beneficial effects of the computer program product provided in this application are the same as those of the table relationship analysis method provided in the above embodiments, and will not be repeated here.

[0107] The above description is only a part of the embodiments of this application and does not limit the patent scope of this application. All equivalent structural transformations made under the technical concept of this application and using the contents of the specification and drawings of this application, or direct / indirect applications in other related technical fields, are included in the patent protection scope of this application.

Claims

1. A method for analyzing inter-table relationships, characterized in that, The inter-table relationship analysis method includes: Convert the SQL statement to be processed into an abstract syntax tree; The abstract syntax tree is traversed back-root. For each node traversed, the table join conditions are extracted according to the preset processing logic corresponding to the node type, and the field mapping relationship between the target table and the source table is constructed node by node. The field mapping relationship includes the target table fields, the source table fields, and the processing functions that perform value transformation or aggregation on the source table fields. The table relationships between the target table and the source table are determined based on the table join conditions and / or the field mapping relationships. The table relationships include at least one of association relationships, granular relationships, and data set relationships.

2. The inter-table relationship analysis method as described in claim 1, characterized in that, The step of determining the inter-table relationship between the target table and the source table based on the table join condition and the field mapping relationship includes: When it is determined that the source table field corresponds to the temporary table based on the field mapping relationship, the source table field is replaced with the corresponding physical source table field by tracing back according to the field mapping relationship corresponding to the temporary table. Based on the physical source table fields, the table join conditions, and the field mapping relationships, the inter-table relationships between the target table and the source table are determined.

3. The method for analyzing inter-table relationships as described in claim 1, characterized in that, The steps of extracting table join conditions and constructing field mapping relationships between the target table and the source table node by node, based on preset processing logic corresponding to the node type, include: When the node type represents a data write operation, a field mapping relationship is constructed between the target table fields and the source table fields; When the node type represents a field projection operation, the field mapping relationship is constructed based on the corresponding field list; When the node type represents a table join operation, the table join condition is extracted based on the corresponding equality expression.

4. The inter-table relationship analysis method as described in claim 3, characterized in that, The equality expression includes at least one of the following: a direct equality expression, an equality expression wrapped in a type conversion function, and an equality expression wrapped in a null value handling function.

5. The method for analyzing inter-table relationships as described in claim 1, characterized in that, The step of determining the inter-table relationship between the target table and the source table based on the table join condition and the field mapping relationship includes: Based on the table join conditions and field mapping relationships corresponding to a single SQL statement, determine the granularity analysis results between the target table and the source table; Based on the execution order of each SQL statement within a single job, the granularity analysis results of each SQL statement are summarized, and job-level granularity relationships are generated based on the summarized granularity analysis results of each SQL statement.

6. The inter-table relationship analysis method as described in claim 5, characterized in that, The step of determining the granularity analysis results between the target table and the source table based on the table join conditions and field mapping relationships corresponding to a single SQL statement includes: If, based on the field mapping relationship, it is determined that all fields in the target table are mapped to the primary key field of the same source table and there are no processing functions, then the granularity of the source table and the target table is consistent. If the fields of the target table are determined to be mapped only to the primary key fields of the main table of the source table and have no processing functions, and the table join condition includes an equi-value association between the primary key fields of the secondary table of the source table and the fields of the main table, then the granularity of the main table of the source table and the target table is determined to be consistent.

7. The method for analyzing inter-table relationships as described in claim 1, characterized in that, The step of determining the inter-table relationship between the target table and the source table based on the table join condition and the field mapping relationship includes: Based on the business primary key field of the target table in the field mapping relationship, the processing function, and whether the table join condition includes a filtering operation, the data set relationship between the source table and the target table is determined; wherein, the data set relationship includes: inclusion, equality, or irrelevance.

8. The method for analyzing inter-table relationships as described in claim 1, characterized in that, Before the step of converting the SQL statement to be processed into an abstract syntax tree, the following steps are also included: Obtain input data for inter-table relationship analysis, the input data including at least one of the following: ETL-type stored procedure code, ETL job execution log, and a single SQL statement; The input data is cleaned to extract the SQL statements to be processed; If the input data is ETL-type stored procedure code, then conditional, loop, and dynamic SQL control statements are removed; if the input data is the ETL job's execution log, then statements in the execution log other than the executed SQL statements are removed.

9. A device for analyzing inter-table relationships, characterized in that, The device includes: a memory, a processor, and a computer program stored in the memory and executable on the processor, the computer program being configured to implement the steps of the table relationship analysis method as described in any one of claims 1 to 8.

10. A storage medium, characterized in that, The storage medium is a computer-readable storage medium, and a computer program is stored on the storage medium. When the computer program is executed by a processor, it implements the steps of the table relationship analysis method as described in any one of claims 1 to 8.

Images