Multi-model based database table relationship analysis method and device, equipment and medium

Abstract

The application relates to the technical field of database management, and discloses a database table relationship analysis method and device based on multiple models, equipment and a medium. The method comprises the following steps: collecting a standard structured statement and corresponding database metadata; calling at least two independent analysis models to analyze the standard structured statement in parallel, and outputting multiple initial table relationships; checking each initial table relationship based on the database metadata to form a transition table relationship containing a checking result; aggregating each transition table relationship according to a preset rule to obtain a table relationship to be examined and approved, and calculating the accuracy of the table relationship to be examined and approved; determining each effective table relationship through an examination and approval strategy according to the accuracy and the number of all transition table relationships included in each table relationship to be examined and approved; and finally constructing a table relationship knowledge base to provide application services. The application combines the technical solutions of multiple model collaborative analysis, metadata intelligent checking, result traceability and man-machine collaborative decision-making, and realizes efficient, accurate and automatic carding of database table relationships.

Description

Technical Field

[0001] This application relates to the field of database management technology, specifically to a method, apparatus, device, and medium for database table relational analysis based on multiple models. Background Technology

[0002] In the development and maintenance of enterprise-level systems, the database, as the core data storage carrier, directly impacts software engineering efficiency and system stability through the clear understanding and precise management of table relationships. With the continuous increase in business complexity, enterprise database architectures exhibit characteristics such as "a large number of tables, multiple relationship levels, and deep nesting of business logic." Furthermore, due to the lack of standardized documentation accumulated during historical development, current enterprise-level systems face three core problems:

[0003] First, test data preparation is inefficient. During the system testing phase, testers need to manually trace multi-level table relationships to construct test data that conforms to business logic. This process is not only time-consuming but also prone to incomplete test data due to human error in missing key relationship paths, which in turn affects test coverage and the accuracy of test results.

[0004] Secondly, the transfer of system knowledge relies heavily on personal experience. Understanding and organizing database table relationships depends extensively on the personal experience of senior developers. New employees may need several months to master the core data architecture of the system. If key personnel leave, it can easily lead to knowledge gaps and increase the risk of system maintenance problems.

[0005] Third, continuous delivery bottlenecks are prominent. Cross-table long-link testing scenarios require frequent construction of related data. Manual processing leads to extended testing cycles, becoming a key bottleneck in the continuous delivery process. At the same time, technical debt continues to accumulate, further increasing system iteration costs.

[0006] To address the above problems, existing technologies have proposed various database table relational analysis schemes, but all of them have significant drawbacks:

[0007] First, reliance on a single large model leads to insufficient accuracy. Existing solutions generally use a single large language model to handle SQL parsing and table relationship generation, lacking a multi-source verification mechanism. When dealing with complex multi-table joins, nested queries, and temporary table references, the model is prone to producing "illusory outputs" and cannot effectively identify erroneous results.

[0008] Secondly, static adaptation capabilities cannot cope with system changes. Some solutions use static rule engines or pre-built knowledge bases to identify table relationships, and the rules and knowledge bases need to be updated manually periodically. When the database table structure is adjusted or the business logic is iterated, static solutions cannot adapt dynamically and require a large amount of manpower for maintenance.

[0009] Third, there is a lack of structured verification and traceability. Existing solutions have not established a complete metadata verification system, making it impossible to verify the authenticity of table names and field names in the analysis results, as well as the compatibility of data types. At the same time, the analysis process lacks records, making it impossible to trace the source of the generation of a certain table relationship, which is difficult to meet the needs of scenarios such as finance and government affairs where data reliability requirements are extremely high.

[0010] In summary, existing technologies suffer from systemic deficiencies in terms of accuracy, dynamic adaptability, verifiability, and traceability. Therefore, there is an urgent need for a technical solution that integrates multi-model collaborative analysis, intelligent metadata verification, result traceability, and human-machine collaborative decision-making to overcome existing technological bottlenecks and achieve efficient, accurate, and automated organization of database table relationships. Summary of the Invention

[0011] To address the aforementioned issues, this application provides a multi-model-based database table relationship analysis method, apparatus, device, and medium, which solves the problems of low efficiency, high cost, and error-proneness in database table relationship analysis in the prior art.

[0012] The embodiments of this application adopt the following technical solutions:

[0013] Firstly, this application provides a database table relationship analysis method based on multiple models, including:

[0014] Collect standard structured statements and their corresponding database metadata;

[0015] Call at least two independent analysis models to perform table relationship analysis on standard structured statements in parallel, so that each analysis model outputs multiple initial table relationships;

[0016] Based on the database metadata, each initial table relationship is validated to form a transitional table relationship containing the validation results;

[0017] Aggregate the relationships of each transition table according to preset rules to obtain the relationships of each pending approval table. Based on the analysis model and model confidence weight of each pending approval table relationship, the verification results of each pending approval table relationship, and the standard structured statement that supports deduplication of the source information of each pending approval table relationship, calculate the accuracy of each pending approval table relationship.

[0018] Based on the accuracy and the number of all transitional table relationships included in each pending table relationship, the approval strategy is used to perform approval on each pending table relationship to determine each valid table relationship.

[0019] A table relationship knowledge base is constructed based on the relationships between each valid table, and application services are provided based on the table relationship knowledge base.

[0020] Secondly, this application also provides a database table relational analysis device based on a multi-model approach, comprising:

[0021] The collection unit is used to collect standard structured statements and their corresponding database metadata.

[0022] The analysis unit is used to call at least two independent analysis models to perform table relationship analysis on standard structured statements in parallel, so that each analysis model outputs multiple initial table relationships;

[0023] The validation unit is used to perform validity checks on each initial table relationship based on the database metadata, forming a transitional table relationship containing the validation results;

[0024] The evaluation unit is used to aggregate the relationships of each transition table according to preset rules to obtain the relationships of each pending approval table. Based on the analysis model and model confidence weight of each pending approval table relationship, the verification results of each pending approval table relationship, and the standard structured statement that supports deduplication of the source information of each pending approval table relationship, the accuracy of each pending approval table relationship is calculated.

[0025] The approval unit is used to approve each pending table relationship based on the accuracy and the number of all transition table relationships included in each pending table relationship, and to determine each valid table relationship.

[0026] The application unit is used to build a table relationship knowledge base based on the relationships between each valid table, and to provide application services based on the table relationship knowledge base.

[0027] Thirdly, this application also provides an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement the steps of the above-described multi-model-based database table relational analysis method.

[0028] Fourthly, this application also provides a computer-readable storage medium storing a computer program that, when executed by a processor, implements the steps of the above-described multi-model-based database table relational analysis method.

[0029] The above-described technical solutions adopted in the embodiments of this application can achieve the following beneficial effects:

[0030] This application collects standard structured statements and their corresponding database metadata; calls at least two independent analysis models to perform table relationship analysis on the standard structured statements in parallel, so that each analysis model outputs multiple initial table relationships; performs validity checks on each initial table relationship based on the database metadata, forming transitional table relationships containing the check results; aggregates each transitional table relationship according to preset rules to obtain each pending approval table relationship; calculates the accuracy of each pending approval table relationship based on the analysis model and model credibility weights of each pending approval table relationship, the check results of each pending approval table relationship, and the standard structured statements that support deduplication of the source information of each pending approval table relationship; based on the accuracy and the number of all transitional table relationships included in each pending approval table relationship, performs approval on each pending approval table relationship through an approval strategy to determine each valid table relationship; constructs a table relationship knowledge base based on each valid table relationship, and provides application services based on the table relationship knowledge base.

[0031] This application achieves the following beneficial effects through a complete technical solution of multi-model collaborative analysis, intelligent metadata verification, result traceability, and human-machine collaborative decision-making.

[0032] (1) High accuracy. Multiple analysis models analyze table relationships independently and concurrently, combined with a dual mechanism of intelligent verification of metadata, which significantly improves the accuracy of analysis, resulting in a substantial improvement in accuracy compared to analysis using a single large model.

[0033] (2) High efficiency: Automated analysis replaces manual sorting, batch approval reduces manual workload, and shortens the traditional table relationship sorting work that takes months to complete in a few days.

[0034] (3) Traceability: Records complete analysis process data from data collection, model analysis, verification to approval. Each table relationship can be traced back to the specific analysis model, structured statement and intelligent verification process, meeting the requirements of high reliability scenarios.

[0035] The table relationship knowledge base formed through this application can be reused sustainably, providing a solid foundation for application scenarios such as enterprise data asset management, intelligent SQL generation, and data lineage analysis, and has significant practical value and broad application prospects. Attached Figure Description

[0036] The accompanying drawings, which are included to provide a further understanding of this application and form part of this application, illustrate exemplary embodiments and are used to explain this application, but do not constitute an undue limitation of this application. In the drawings:

[0037] Figure 1 A system architecture diagram of a multi-model-based database table relationship analysis method according to an embodiment of this application is shown;

[0038] Figure 2A flowchart illustrating a multi-model-based database table relationship analysis method according to an embodiment of this application is shown;

[0039] Figure 3 A schematic diagram of the structure of a multi-model-based database table relationship analysis apparatus according to an embodiment of this application is shown;

[0040] Figure 4 A schematic diagram of the resulting electronic device according to an embodiment of this application is shown. Detailed Implementation

[0041] To make the objectives, technical solutions, and advantages of this application clearer, the technical solutions of this application will be clearly and completely described below in conjunction with specific embodiments and corresponding drawings. Obviously, the described embodiments are only a part of the embodiments of this application, and not all of them. Based on the embodiments in this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0042] The purpose of this application is to provide a multi-model-based database table relationship analysis method to build a dual-engine platform integrating AI intelligent analysis and human intervention. Multiple analysis models collaboratively and automatically parse structured statements to identify table relationships, and intelligent verification is performed using pre-collected database metadata to effectively eliminate analysis model illusions and errors. By recording the analysis source, model response, and scoring mechanism, the analysis process is fully traceable, and a human review process ensures the accuracy of the knowledge. The resulting table relationship knowledge base can not only automatically generate lineage graphs and entity relationship models, but also support advanced application scenarios such as Text2SQL intelligent queries and automatic generation of long-link test data. This significantly improves the development efficiency and maintenance quality of database systems, reduces reliance on personal experience, and enables intelligent management and knowledge transfer of enterprise data assets.

[0043] Figure 1 A system architecture diagram of a multi-model-based database table relationship analysis method according to an embodiment of this application is shown. (Refer to...) Figure 1 As shown, the method proposed in this application can be implemented using a system architecture comprising a data source layer 110, an analysis and processing layer 120, a decision review layer 130, an application service layer 140, and a data storage layer 150. Each layer interacts with the other through standardized interfaces, forming a complete processing chain. To achieve the above objectives, Figure 2 A flowchart illustrating a multi-model-based database table relationship analysis method according to an embodiment of this application is shown, with reference to... Figure 2 As shown, this embodiment includes steps S210 to S260:

[0044] Step S210: Collect standard structured statements and corresponding database metadata.

[0045] This embodiment first acquires the structured statements and corresponding database metadata, providing high-quality input for subsequent table relationship analysis and a standard for table relationship verification. This step can be implemented by the data source layer 110 and the data storage layer 150.

[0046] In some optional implementations, step S210, collecting standard structured statements and corresponding database metadata, includes: obtaining the original statement from the data source and recording the source information of the original statement; wherein, the data source includes at least one of MyBatis' XML configuration file, database stored procedure definition, view creation statement, and historical SQL query; performing a unified standardized transformation on the original statement to form a standard SQL statement as a standard structured statement; collecting and recording database metadata; wherein, the database metadata includes: table name, field name, field data type, and comments.

[0047] Data source layer 110 is responsible for collecting raw statements from multiple data sources. These raw statements are the original structured statements to be analyzed. Data source layer 110 supports multiple sources of raw structured statements, including but not limited to XML mapping files from the MyBatis framework, stored procedure definitions from the database, view creation statements, and historical SQL queries (queries commonly used by technical personnel). The collected raw statements contain rich table relationship information, and the source information of the raw statements is recorded in data storage layer 150.

[0048] For example, by using a file scanning tool to traverse the mapping files (with the .XML extension) of the MyBatis framework, you can extract the SQL statements, record the file path and the line number of the SQL statement in the file, and also record the business modules associated with the SQL statement.

[0049] For example, connect to the database to obtain a list of stored procedures, extract the SQL statements in the stored procedure definitions, and record the database instance to which the stored procedure belongs and the associated business scenario.

[0050] For example, retrieve the SQL definition statements for all views and record the view names and associated departments.

[0051] For example, extract historical SQL statements from database audit logs, service logs, or sets of SQL statements frequently used by technical personnel, and record the execution time and the person who executed the SQL statement.

[0052] Since the original statements from different sources may have syntactic differences, the data source layer 110 can also be responsible for performing a unified standardization transformation on the original statements, thereby forming standard SQL statements as standard structured statements.

[0053] The data source layer 110 is also responsible for collecting database metadata, which includes at least: table name, field name, field data type, and comments. The database metadata collected by the data source layer 110 is recorded in the data storage layer 150.

[0054] For example, database metadata includes table structure information, such as table name, table type, and table comments.

[0055] For example, database metadata includes field structure information, such as the table to which it belongs, field name, field data type, field comments (whether nullables are allowed, etc.).

[0056] Step S220: Call at least two independent analysis models to perform table relationship analysis on standard structured statements in parallel so that each analysis model outputs multiple initial table relationships.

[0057] This embodiment then calls at least two independent large models to perform table relationship analysis. Standard structured statements are input into each analysis model using standard prompt templates to perform table relationship analysis, and each analysis model outputs multiple initial table relationships. This step can be implemented by the analysis processing layer 120 and the data storage layer 150.

[0058] In some optional implementations, step S220 involves calling at least two independent analysis models to perform parallel table relationship analysis on standard structured statements, so that each analysis model outputs multiple initial table relationships. This includes: constructing a standard prompt word template; wherein the standard prompt word template includes: task description, standard structured statements, and output requirements; connecting at least two analysis models through a plug-in design; and inputting the standard prompt word template into each analysis model in parallel, so that each analysis model outputs multiple corresponding initial table relationships in parallel and independently; wherein each initial table relationship includes: source table, source field, target table, target field, relationship type, and relationship description.

[0059] If the standard structured statement contains only one statement, the analysis and processing layer 120 analyzes and processes that statement. If the standard structured statement contains multiple statements, the analysis and processing layer 120 processes each statement in the standard structured statement iteratively.

[0060] To ensure that all analysis models receive consistent analysis tasks and avoid discrepancies in analysis results due to differences in prompts, a standard prompt template is constructed for each standard structured statement. The standard prompt template consists of three parts: task description, standard structured statement, and output requirements.

[0061] For example, a standard prompt template could be:

[0062] "Task Description: Please analyze the database table relationships in the following standard SQL statements, identify the source table, source field, target table, target field, determine the relationship type, and provide a relationship description."

[0063] Standard SQL statement:

[0064] Output requirements: Please return results in JSON format, without any other text.

[0065] Each element in the JSON array represents an initial table relationship, containing the following:

[0066] from_table_full: The source table;

[0067] from_field: The source field;

[0068] to_table_full: The target table;

[0069] to_field: The target field;

[0070] relation_type: Relation type (LOGIC);

[0071] "description: relation description".

[0072] The analysis and processing layer 120 uses a plug-in design to connect to at least two independent analysis models, supporting flexible expansion and replacement of these models. These models can be DeepSeek, Qwen, Wenxin Yiyan, etc.

[0073] For example, a unified interface for accessing analytical models can be defined, and each analytical model can access the system through this interface.

[0074] Each analysis model operates independently, receiving the same standard prompt word template and outputting the initial table relationships identified by the analysis. Each initial table relationship includes: source table, source field, target table, target field, relationship type, and relationship description. This parallel analysis mechanism improves processing efficiency and enhances the reliability of results through subsequent cross-validation by multiple analysis models.

[0075] Step S230: Perform validity checks on each initial table relationship based on database metadata to form a transitional table relationship containing the check results.

[0076] In this embodiment, the initial analysis results of each analysis model are then validated at four levels based on the database metadata, and transitional data containing the validation results is generated and recorded. This step can be implemented by the analysis processing layer 120 and the data storage layer 150.

[0077] In some optional implementations, step S230 involves performing validity checks on each initial table relationship based on database metadata to form a transitional table relationship containing the check results. This includes: performing table name checks on the source and target tables of each initial table relationship based on table names; performing field checks on the source and target fields of each initial table relationship based on field names; performing type checks on the source and target fields of each initial table relationship based on field data types; performing anomaly checks on the source and target fields of each initial table relationship based on comments; and recording each transitional table relationship containing the corresponding check results. Each transitional table relationship includes: the called analysis model, the standard prompt word template, the initial table relationship, the check result, and the source information.

[0078] The initial table relationships output by each analysis model must undergo strict database metadata verification. The verification of each initial table relationship based on database metadata includes four layers: table name verification, field verification, type verification, and exception verification.

[0079] The purpose of table name validation is to verify whether the source table (from_table_full) and target table (to_table_full) in the initial table relationship actually exist in the database. The validation process is as follows: for each table involved in each initial table relationship (including the source table and the target table), check whether it is in the table name set of the database metadata records.

[0080] The purpose of field validation is to verify whether the source field (from_field) belongs to the source table (from_table_full) and whether the target field (to_field) belongs to the target table (to_table_full). The validation process is as follows: for each source field involved in the initial table relationship, check whether it is in the field name set of the source table to which it belongs in the database metadata record; similarly, for each target field involved in the initial table relationship, check whether it is in the field name set of the target table to which it belongs in the database metadata record.

[0081] The purpose of type validation is to verify whether the data types of the source field (from_field) and the target field (to_field) are compatible. The validation process is as follows: define field data type compatibility rules, and determine whether they are compatible based on the data types of the source field and the target field according to the compatibility rules.

[0082] The purpose of anomaly validation is to verify whether a field contains null values, abnormal default values, or other special cases. The validation process is as follows: based on the field comments in the database metadata records, check the consistency between the field (including the source field and the target field) and the field comments. For example, if the field comment is "not allowed to be null" and the corresponding field is "null", then an anomaly exists.

[0083] In actual verification operations, the above four layers of verification can adopt a progressive verification logic:

[0084] Perform table name validation; if the table name does not exist, record the table name error validation result.

[0085] If the table name exists, perform field validation; if the field does not exist, record the field error validation result.

[0086] If the field has an execution type validation, and the type does not match, the record will show a type error validation result.

[0087] If the type matches, perform exception verification; if an exception occurs, record the exception error verification result.

[0088] If no anomalies are detected, the verification will pass.

[0089] Regardless of the verification result, all verification results correspond to the respective initial table relationships, forming transition table relationships stored in data storage layer 150. Each transition table relationship includes: the called analysis model, the standard prompt word template, the initial table relationship, the verification result, and the source information.

[0090] For example, a transition table relationship includes:

[0091] "id: Auto-incrementing primary key;"

[0092] model_name: The analysis model being called;

[0093] prompt: Standard prompt word template;

[0094] from_table_full: The source table;

[0095] from_field: The source field;

[0096] to_table_full: The target table;

[0097] to_field: The target field;

[0098] relation_type: The type of relation;

[0099] description: Relationship description;

[0100] verify_table_result: The result of table name verification;

[0101] verify_field_result: Field validation result;

[0102] verify_type_result: Type validation result;

[0103] verify_abnormal_result: Abnormal verification result;

[0104] SQL_id: Source information;

[0105] Time: Records the time.

[0106] This ensures the integrity of the data records and provides a data foundation for subsequent optimization.

[0107] Step S240: Aggregate the relationships of each transition table according to preset rules to obtain the relationships of each pending approval table. Based on the analysis model and model confidence weight of each pending approval table relationship, the verification results of each pending approval table relationship, and the standard structured statement that supports deduplication of the source information of each pending approval table relationship, calculate the accuracy of each pending approval table relationship.

[0108] This embodiment continues to aggregate transition table relationships according to preset rules and calculate the accuracy of each pending approval table relationship. To reduce redundant review workload and to integrate the analysis results of multiple analysis models, transition table relationships are aggregated according to the preset rule of "unique table relationship". To quantify the accuracy of each aggregated pending approval table relationship, a multi-factor prediction accuracy calculation model is designed. This step can be implemented by the analysis processing layer 120 and the data storage layer 150.

[0109] In some optional implementations, step S240 involves aggregating each transition table relationship according to preset rules to obtain each pending approval table relationship. Based on the analysis model and model credibility weight of each pending approval table relationship, the verification results of each pending approval table relationship, and the standard structured statements that support deduplication of source information for each pending approval table relationship, the accuracy of each pending approval table relationship is calculated. This includes: aggregating the source table, target table, and transition table relationships with consistent source and target fields into a single pending approval table relationship; determining the analysis model factor score based on the analysis model, model credibility weight, and the total number of accessed analysis models; determining the verification pass rate factor score based on the number of all transition table relationships included in the pending approval table relationship and the number of transition table relationships with verified pass results; determining the SQL statement factor score based on the number of standard SQL statements that support deduplication of the source information for the pending approval table relationship and the total number of standard structured statements; determining the accuracy of the pending approval table relationship based on the analysis model factor score, verification pass rate factor score, SQL statement factor score, and the dynamically configured weight coefficients of the corresponding factors; and recording each pending approval table relationship containing the corresponding accuracy.

[0110] Define "source table - source field - target table - target field" as the aggregation key. When the source table, source field, target table, and target field of a transitional table relationship are completely identical, they are determined to be the same table relationship awaiting approval, and aggregation is performed.

[0111] After aggregation, the following parameters are calculated for each pending approval table relationship:

[0112] The analysis model that identifies the relationship between the pending approval forms is determined; that is, which analysis model analyzed the relationship between the pending approval forms. The more analysis models that analyze the relationship between the pending approval forms, the higher the reliability of the relationship.

[0113] The model credibility weights of the analytical models that analyze the relationships of the forms pending approval are determined. Initially, credibility weights can be assigned manually. As different analytical models are used, the credibility weights can be further optimized; for example, analytical models with higher accuracy have higher credibility weights, as explained below.

[0114] The pass rate of the validation for the pending approval table relationship is the proportion of transitional table relationships among all transitional table relationships that have passed all four levels of validation. The pass rate reflects the degree of matching between the pending approval table relationship and the database metadata.

[0115] The number of structured SQL statements that support the deduplication of source information for the relationship of the table to be approved, i.e., the number of standard SQL statements that analyze the different sources of the relationship of the table to be approved. The more standard SQL statements that analyze the source information to deduplicate the relationship of the table to be approved, the higher the credibility of the relationship of the table to be approved.

[0116] The analysis and processing layer 120 can calculate the accuracy of the relationship of the table to be approved by weighting the analysis model factors, the pass rate factors, and the SQL statement factors, providing a quantitative basis for subsequent decision review.

[0117] For example, the analysis and processing layer 120 can calculate the accuracy of a relationship in a pending approval form using the following formula:

[0118] ;Formula (1).

[0119] in, , , These represent the weighting coefficients for each dynamically configured factor score, used to adjust the degree of influence of each factor on accuracy. They can be configured according to the actual business scenario. .

[0120] In the above formula (1), the factor scores of the analysis model can be calculated using the following method:

[0121] The number of analytical models that can analyze the relationships of the pending approval forms is determined to be: indivual( ), of which The credibility weights of each analytical model are: ( ).

[0122] The total number of analytical models to be accessed is determined to be indivual( ).

[0123] but .

[0124] For example, the total number of analytical models connected. The relationship of the pending approval form was analyzed by three analytical models: Analytical Model 1: Credibility Weight Analyze the credibility weights of Model 2 Analyze the credibility weights of Model 3 Then the factor score of the analysis model = (0.85 + 0.80 + 0.75) ÷ 5 = 0.48.

[0125] In the above formula (1), the pass rate factor score can be calculated using the following method:

[0126] The number of all transitional table relationships in the pending approval table relationship is determined as follows: indivual( ).

[0127] The number of transitional table relationships in the pending approval table relationship that have passed four-level validation (validation result is "validation passed") is determined as follows. indivual( ).

[0128] but .

[0129] For example, all transition table relationships The transition table relationship through four layers of verification Therefore, the pass rate factor score is 8 ÷ 10 = 0.8.

[0130] In the above formula (1), the SQL statement factor score can be calculated using the following method:

[0131] The number of standard SQL statements used to determine the source for deduplication of the relationship in the pending approval table is [number]. indivual( ).

[0132] The total number of standard structured statements is determined as follows: indivual( ).

[0133] but .

[0134] For example, the total number of standard structured statements The relationship of the pending approval form was If the standard SQL statement supports deduplication of source information, then the SQL statement factor score = 15 ÷ 20 = 0.75.

[0135] In the above formula (1), for example, , , Therefore, the accuracy of the relationship in the pending approval form is 0.4 × 0.48 + 0.3 × 0.8 + 0.3 × 0.75 = 0.657.

[0136] Based on the above process, the accuracy of each pending approval table relationship is calculated, and the relationship of each pending approval table containing the corresponding accuracy is stored in the data storage layer 150.

[0137] Step S250: Based on the accuracy and the number of all transitional table relationships included in each pending table relationship, the approval strategy is used to perform approval on each pending table relationship to determine each valid table relationship.

[0138] This embodiment then executes an approval strategy based on the accuracy and the number of all transitional table relationships included in each pending approval table relationship to approve each pending approval table relationship. The approval strategy can be a human-machine collaborative approval strategy, i.e., automatic batch approval or manual approval. After approval, valid table relationships are recorded, and the credibility weights of the analysis model are dynamically updated based on the approval results. This step can be implemented by the decision review layer 130 and the data storage layer 150.

[0139] In some optional implementations, step S250 involves approving each pending table relationship based on its accuracy and the number of all transitional table relationships it includes, using an approval strategy to determine each valid table relationship. This includes: performing manual approval when the accuracy of a pending table relationship does not exceed a first preset threshold or the number of all transitional table relationships it includes does not exceed a second preset threshold; forming a valid table relationship if the manual approval passes; removing the pending table relationship if the manual approval fails; dynamically adjusting the credibility weight of the analysis model that analyzed the pending table relationship based on the manual approval result; automatically approving a pending table relationship when its accuracy exceeds the first preset threshold and the number of all transitional table relationships it includes exceeds the second preset threshold; and recording each valid table relationship.

[0140] The decision review layer 130 distinguishes between automatic approval and manual approval based on the accuracy of each pending table relationship and the number of all transition table relationships it includes.

[0141] If the accuracy of a pending table relationship exceeds the first preset threshold (e.g., accuracy exceeds 80%), it indicates that the analytical model's reliability is very high. If the number of all transitional table relationships included in a pending table relationship exceeds the second preset threshold (e.g., 5), it indicates that there is sufficient evidence to support the pending table relationship. Therefore, when both of the above conditions are met simultaneously, automatic approval is granted. This pending table relationship forms a valid table relationship and is stored in data storage layer 150.

[0142] If the accuracy of a pending table relationship does not exceed the first preset threshold (e.g., accuracy not exceeding 80%), it indicates insufficient credibility of the analytical model. If the number of all transitional table relationships included in a pending table relationship does not exceed the second preset threshold, it indicates insufficient evidence to support the pending table relationship. Therefore, manual review is triggered when either of the above two conditions is met.

[0143] During manual review, reviewers can view the complete analysis path of the relationships between the tables to be reviewed, including but not limited to which analysis models derived these relationships from which standard SQL statements. The manual review result includes "approved" and "rejected."

[0144] If the manual review is approved, the table relationship to be reviewed will form a valid table relationship and be stored in data storage layer 150.

[0145] If the manual review fails, the relationship in the pending review form will be removed.

[0146] Meanwhile, the credibility weights of the analysis model are dynamically adjusted based on the results of manual review. When the manual review is passed, the credibility weight of the analysis model that analyzes the relationship of the form to be approved is increased; when the manual review is failed, the credibility weight of the analysis model that analyzes the relationship of the form to be approved is decreased.

[0147] The dynamic adjustment mechanism of the credibility weight of the analysis model can identify which analysis models perform better on specific types of standard SQL statements, and give these analysis models higher credibility weights in subsequent accuracy calculations, thereby continuously improving the overall accuracy of the analysis and enhancing the adaptability of the analysis model.

[0148] Understandably, the criteria for distinguishing between automatic and manual review can be flexibly adjusted based on actual business needs.

[0149] Step S260: Construct a table relationship knowledge base based on the relationships between each valid table, and provide application services based on the table relationship knowledge base.

[0150] Finally, this embodiment constructs a structured table relationship knowledge base based on all valid table relationships, and provides multi-dimensional application services based on the table relationship knowledge base. This step can be implemented by the application service layer 140 and the data storage layer 150.

[0151] In some optional implementations, step S260 involves constructing a table relationship knowledge base based on the relationships between each valid table, and providing application services based on the table relationship knowledge base, including: constructing a table relationship knowledge base based on the relationships between each valid table; and based on the table relationship knowledge base, providing a generation link tracing service for each valid table relationship, a relationship graph service for visualizing the database table network, a Text2SQL service for generating database table query statements, a lineage analysis service for tracing the flow of data between database tables, and a test data preparation service for automatically generating long-link test data.

[0152] The application service layer 140 forms a table relationship knowledge base based on the valid table relationships after review and stores it in the data storage layer 150. The table relationship knowledge base provides a variety of intelligent application services as a knowledge output.

[0153] Generate Link Trace Service: In response to a user query for a valid table relationship, it can display the complete generation link of that valid table relationship, including source information, participating analysis models, standard prompt word templates, model response results, verification results of each dimension, approval process and results, etc.

[0154] Relationship Graph Service: Visualizes the relationships between database tables in a graph structure (tables as nodes, table relationships as edges), and supports operations such as node filtering, variable filtering, and hierarchical expansion.

[0155] Text2SQL service: Responding to users' natural language query needs, it identifies the tables and table relationships involved in the request based on a table relationship knowledge base, and automatically generates SQL query statements that conform to the syntax rules.

[0156] Lineage Analysis Service: Tracks the flow of data between database tables, supporting both forward and reverse lineage analysis. It can quickly assess the scope of impact when table structures change.

[0157] Test data preparation service: For long-chain testing scenarios, it automatically identifies the tables and table relationships required for testing based on a table relationship knowledge base, and generates compliant test data according to business logic, reducing test data preparation time from hours to minutes.

[0158] In the system architecture for implementing a multi-model-based database table relationship mining method:

[0159] The analysis and processing layer 120 is the core processing engine of the system architecture, which can include three key modules: a multi-model parallel analysis module, a metadata verification module, and an aggregation evaluation module. The multi-model parallel analysis module is specifically used to execute step S220, the metadata verification module is specifically used to execute step S230, and the aggregation evaluation module is specifically used to execute step S240.

[0160] The data storage layer 150 provides three core databases to support the system architecture. The basic database stores standard structured statements and corresponding database metadata; the transition table relationship database stores the relationships between initial tables and the relationships between transition tables containing verification results; and the valid table relationship database stores the relationships between tables pending approval, the relationships between valid tables, and a table relationship knowledge base.

[0161] The system architecture follows the principle of high cohesion and low coupling, with clear responsibilities at each level, making it easy to expand and maintain.

[0162] Figure 3 A multi-model-based database table relational analysis apparatus according to an embodiment of this application is shown, with reference to Figure 3 As shown, the multi-model-based database table relational analysis device 300 includes:

[0163] Collection unit 310 is used to collect standard structured statements and corresponding database metadata;

[0164] Analysis unit 320 is used to call at least two independent analysis models to perform table relationship analysis on standard structured statements in parallel, so that each analysis model outputs multiple initial table relationships.

[0165] The verification unit 330 is used to perform validity verification on each initial table relationship based on the database metadata, and form a transition table relationship containing the verification results;

[0166] Evaluation unit 340 is used to aggregate the relationships of each transition table according to preset rules to obtain the relationships of each pending approval table. Based on the analysis model and model confidence weight of each pending approval table relationship, the verification results of each pending approval table relationship, and the standard structured statement that supports deduplication of the source information of each pending approval table relationship, the accuracy of each pending approval table relationship is calculated.

[0167] Approval unit 350 is used to perform approval on each pending table relationship based on the accuracy and the number of all transition table relationships included in each pending table relationship, and to determine each valid table relationship.

[0168] Application Unit 360 is used to build a table relationship knowledge base based on the relationships between each valid table, and to provide application services based on the table relationship knowledge base.

[0169] In some optional embodiments, in the above apparatus, the collection unit 310 is used to: obtain the original statement from the data source and record the source information of the original statement; wherein the data source includes at least one of MyBatis XML configuration file, database stored procedure definition, view creation statement and historical SQL query; perform a unified standardized transformation on the original statement to form a standard SQL statement as a standard structured statement; collect database metadata and record it; wherein the database metadata includes: table name, field name, field data type and comments.

[0170] In some optional embodiments, in the above apparatus, the analysis unit 320 is used to: construct a standard prompt word template; wherein the standard prompt word template includes: task description, standard structured statements and output requirements; connect to at least two analysis models through a plug-in design; input the standard prompt word template into each analysis model in parallel, so that each analysis model outputs multiple corresponding initial table relationships in parallel and independently; wherein each initial table relationship includes: source table, source field, target table, target field, relationship type and relationship description.

[0171] In some optional embodiments, in the above apparatus, the verification unit 330 is used to: perform table name verification on the source table and target table of each initial table relationship based on the table name; perform field verification on the source field and target field of each initial table relationship based on the field name; perform type verification on the source field and target field of each initial table relationship based on the field data type; perform anomaly verification on the source field and target field of each initial table relationship based on the comments; and record each transition table relationship containing the corresponding verification results; wherein each transition table relationship includes: the called analysis model, the standard prompt word template, the initial table relationship, the verification result, and the source information.

[0172] In some optional embodiments, in the above apparatus, the evaluation unit 340 is used to: aggregate the source table, target table, and transition table relationships with consistent source and target fields into a single pending approval table relationship; determine the analysis model factor score based on the analysis model analyzed for the pending approval table relationship, the model confidence weight, and the total number of accessed analysis models; determine the verification pass rate factor score based on the number of all transition table relationships included in the pending approval table relationship and the number of transition table relationships whose verification results are verified as passed; determine the SQL statement factor score based on the number of standard SQL statements supporting source deduplication for the pending approval table relationship and the total number of standard structured statements; determine the accuracy of the pending approval table relationship based on the analysis model factor score, the verification pass rate factor score, the SQL statement factor score, and the weight coefficients of dynamically configured corresponding factors; and record each pending approval table relationship containing the corresponding accuracy.

[0173] In some optional embodiments, in the above-described apparatus, the approval unit 350 is configured to: perform manual approval when the accuracy of a pending table relationship does not exceed a first preset threshold or the number of all included transition table relationships does not exceed a second preset threshold; if the manual approval passes, a valid table relationship is formed; if the manual approval fails, the pending table relationship is removed; dynamically adjust the credibility weight of the analysis model analyzing the pending table relationship based on the manual approval result; automatically approve a pending table relationship when the accuracy of a pending table relationship exceeds the first preset threshold and the number of all included transition table relationships exceeds the second preset threshold, forming a valid table relationship; and record each valid table relationship.

[0174] In some optional implementations, in the above-described apparatus, the application unit 360 is used to: construct a table relationship knowledge base based on the relationships between each valid table; based on the table relationship knowledge base, provide a generation link tracing service for the relationships between each valid table, a relationship graph service for visualizing the database table network, a Text2SQL service for generating database table query statements, a lineage analysis service for tracing the flow of data between database tables, and a test data preparation service for automatically generating long-link test data.

[0175] It should be noted that the aforementioned multi-model-based database table relational analysis device 300 can implement the aforementioned multi-model-based database table relational analysis methods one by one, and will not be elaborated further.

[0176] Figure 4 This invention illustrates a schematic diagram of the structure of an electronic device according to an embodiment of the present application. Figure 4 As shown, the electronic device includes a processor, memory, network interface, and database connected via a system bus. The processor provides computing and control capabilities. The memory includes non-volatile and / or volatile storage media and internal memory. The non-volatile storage media stores the operating system, computer programs, and database. The internal memory provides an environment for the operation of the operating system and computer programs stored in the non-volatile storage media. The network interface is used for communication with external devices via a network connection. When the computer program is executed by the processor, it implements the functions or steps of a multi-model database table relational analysis method.

[0177] In one embodiment, the electronic device provided in this application includes a memory and a processor. The memory stores a database and a computer program that can run on the processor. When the processor executes the computer program, it implements the steps of the aforementioned multi-model-based database table relationship analysis method.

[0178] The above is as stated in this application. Figure 3The method executed by the multi-model database table relationship analysis device disclosed in the illustrated embodiment can be applied to a processor or implemented by a processor. During implementation, each step of the above method can be completed by integrated logic circuits in the processor's hardware or by software instructions. The processor can be a general-purpose processor, including a Central Processing Unit (CPU), a Network Processor (NP), etc.; it can also be a Digital Signal Processor (DSP), an Application Specific Integrated Circuit (ASIC), a Field-Programmable Gate Array (FPGA), or other programmable logic devices, discrete gate or transistor logic devices, or discrete hardware components. The steps of the method disclosed in the embodiments of this application can be directly embodied as being executed by a hardware decoding processor, or executed by a combination of hardware and software modules in the decoding processor. The software modules can reside in random access memory, flash memory, read-only memory, programmable read-only memory, electrically erasable programmable memory, registers, or other mature storage media in the art. This storage medium is located in memory, and the processor reads information from the memory and, in conjunction with its hardware, completes the steps of the above method.

[0179] In one embodiment, a computer-readable storage medium is also provided, on which a computer program is stored, which, when executed by a processor, implements the steps of the aforementioned multi-model-based database table relational analysis method.

[0180] It should be noted that the functions or steps that the above-mentioned electronic devices or computer-readable storage media can achieve can be referred to the relevant descriptions in the foregoing method embodiments. To avoid repetition, they will not be described one by one here.

[0181] Those skilled in the art will understand that all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware. The computer program can be stored in a non-volatile computer-readable storage medium. When executed, the computer program can include the processes of the embodiments of the above methods. Any references to memory, storage, databases, or other media used in the embodiments provided in this application can include non-volatile and / or volatile memory. Non-volatile memory may include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM), or flash memory. Volatile memory may include random access memory (RAM) or external cache memory. By way of illustration and not limitation, RAM is available in a variety of forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), dual data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronous link DRAM (SLDRAM), RAMbus direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM), etc.

[0182] Those skilled in the art will clearly understand that, for the sake of convenience and brevity, the above-described division of functional units and modules is used as an example. In practical applications, the above functions can be assigned to different functional units and modules as needed, that is, the internal structure of the system can be divided into different functional units or modules to complete all or part of the functions described above.

[0183] The above-described embodiments are only used to illustrate the technical solutions of the present invention, and are not intended to limit it. Although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of the present invention, and should all be included within the protection scope of the present invention.

Claims

1. A database table relational analysis method based on multiple models, characterized in that, include: Collect standard structured statements and their corresponding database metadata; Call at least two independent analysis models to perform table relationship analysis on standard structured statements in parallel, so that each analysis model outputs multiple initial table relationships; Based on the database metadata, each initial table relationship is validated to form a transitional table relationship containing the validation results; Aggregate the relationships of each transition table according to preset rules to obtain the relationships of each pending approval table. Based on the analysis model and model confidence weight of each pending approval table relationship, the verification results of each pending approval table relationship, and the standard structured statement that supports deduplication of the source information of each pending approval table relationship, calculate the accuracy of each pending approval table relationship. Based on the accuracy and the number of all transitional table relationships included in each pending table relationship, the approval strategy is used to perform approval on each pending table relationship to determine each valid table relationship. A table relationship knowledge base is constructed based on the relationships between each valid table, and application services are provided based on the table relationship knowledge base. The process involves aggregating the relationships of each transition table according to preset rules to obtain the relationships of each pending approval table. Based on the analysis model and model confidence weights of each pending approval table relationship, the verification results of each pending approval table relationship, and standard structured statements that support deduplication of source information for each pending approval table relationship, the accuracy of each pending approval table relationship is calculated, including: Aggregate the source table, target table, and transition table relationships with consistent source and target fields into a single pending approval table relationship; Based on the analysis model that identifies the relationship between the pending approval forms, the model's credibility weight, and the total number of analysis models accessed, the analysis model factor score is determined. The pass rate factor score is determined based on the number of all transition table relationships included in the pending approval table relationship and the number of transition table relationships that passed the verification. The SQL statement factor score is determined based on the number of standard structured statements that support the deduplication of the source information of the table to be approved and the total number of standard structured statements. The accuracy of the relationship of the table to be approved is determined based on the analysis model factor score, the verification pass rate factor score, the SQL statement factor score, and the weight coefficients of the corresponding dynamically configured factors. Record the relationships between each pending approval form, including the corresponding accuracy level.

2. The method according to claim 1, characterized in that, The collection of standard structured statements and corresponding database metadata includes: Obtain the original statement from the data source and record the source information of the original statement; the data source includes at least one of the following: MyBatis XML configuration file, database stored procedure definition, view creation statement, and historical SQL query; The original statements are transformed into standard structured statements using a unified standardized conversion process. Collect and record database metadata, which includes: table name, field name, field data type, and comments.

3. The method according to claim 2, characterized in that, The process involves calling at least two independent analysis models to perform table join analysis on standard structured statements in parallel, so that each analysis model outputs multiple initial table relationships, including: Construct a standard prompt word template; the standard prompt word template includes: task description, standard structured statements, and output requirements; Connect at least two analysis models through a plug-in design; The standard prompt word templates are input into each analysis model in parallel, so that each analysis model can output multiple initial table relationships in parallel and independently. Each initial table relationship includes: source table, source field, target table, target field, relationship type, and relationship description.

4. The method according to claim 3, characterized in that, The process of performing validity checks on each initial table relationship based on database metadata to form a transitional table relationship containing the check results includes: Perform table name validation on the source and target tables of each initial table relationship based on the table name; Perform field validation on the source and target fields of each initial table relationship based on the field name; Perform type validation on the source and target fields of each initial table relationship based on the field data type; Anomaly checks are performed on the source and target fields of each initial table relationship based on annotations; Record the relationships between each transition table containing the corresponding verification results; each transition table relationship includes: the called analysis model, the standard prompt word template, the initial table relationship, the verification result, and the source information.

5. The method according to claim 4, characterized in that, The approval process, based on accuracy and the number of all transitional table relationships included in each pending table relationship, employs an approval strategy to perform approvals on each pending table relationship, thereby determining each valid table relationship, including: When the accuracy of a pending approval table relationship does not exceed the first preset threshold or the total number of all transition table relationships does not exceed the second preset threshold, manual approval is performed. If the manual approval is successful, a valid table relationship is formed. If manual approval fails, remove the pending approval form from the list. The credibility weight of the analysis model that dynamically adjusts the relationship between the pending approval forms is based on the results of manual approval. When the accuracy of a pending table relationship exceeds the first preset threshold and the total number of all transition table relationships it includes exceeds the second preset threshold, it is automatically approved and a valid table relationship is formed. Record the relationships between the valid tables.

6. The method according to claim 5, characterized in that, The construction of a table relationship knowledge base based on each valid table relationship, and the provision of application services based on the table relationship knowledge base, include: A table relationship knowledge base is constructed based on the relationships between each valid table. Based on a table relationship knowledge base, it provides a service for generating link tracing of valid table relationships, a relationship graph service for visualizing database table networks, a Text2SQL service for generating database table query statements, a lineage analysis service for tracing the flow of data between database tables, and a test data preparation service for automatically generating long-link test data.

7. A database table relational analysis device based on multiple models, characterized in that, include: The collection unit is used to collect standard structured statements and their corresponding database metadata. The analysis unit is used to call at least two independent analysis models to perform table relationship analysis on standard structured statements in parallel, so that each analysis model outputs multiple initial table relationships; The validation unit is used to perform validity checks on each initial table relationship based on the database metadata, forming a transitional table relationship containing the validation results; The evaluation unit is used to aggregate the relationships of each transition table according to preset rules to obtain the relationships of each pending approval table. Based on the analysis model and model confidence weight of each pending approval table relationship, the verification results of each pending approval table relationship, and the standard structured statement that supports deduplication of the source information of each pending approval table relationship, the accuracy of each pending approval table relationship is calculated. The approval unit is used to approve each pending table relationship based on the accuracy and the number of all transition table relationships included in each pending table relationship, and to determine each valid table relationship. The application unit is used to build a table relationship knowledge base based on each valid table relationship, and to provide application services based on the table relationship knowledge base. The evaluation unit is used to aggregate the source table, target table, and transition table relationship where the source field and target field are consistent into a single table relationship to be approved. Based on the analysis model that identifies the relationship between the pending approval forms, the model's credibility weight, and the total number of analysis models accessed, the analysis model factor score is determined. The pass rate factor score is determined based on the number of all transition table relationships included in the pending approval table relationship and the number of transition table relationships that passed the verification. The SQL statement factor score is determined based on the number of standard structured statements that support the deduplication of the source information of the table to be approved and the total number of standard structured statements. The accuracy of the relationship of the table to be approved is determined based on the analysis model factor score, the verification pass rate factor score, the SQL statement factor score, and the weight coefficients of the corresponding dynamically configured factors. Record the relationships between each pending approval form, including the corresponding accuracy level.

8. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements the steps of the multi-model-based database table relational analysis method as described in any one of claims 1 to 6.

9. A computer-readable storage medium storing a computer program, characterized in that, When the computer program is executed by the processor, it implements the steps of the multi-model-based database table relational analysis method as described in any one of claims 1 to 6.

Images