A metadata and metadata relationship automatic extraction method, system and product

Abstract

This invention provides a method, system, and product for automatically extracting metadata and metadata relationships, relating to the field of big data technology. The method includes: extracting table information, table field information, and foreign key relationships between tables in the current database to obtain explicit metadata; extracting multiple sample data entries from each table; assembling the explicit metadata and the sample data entries to obtain model instruction prompts; using these prompts to call a large language model to output structured table semantic descriptions and field semantic descriptions, and outputting these as metadata; extracting semantic relationships between tables based on the table semantic descriptions, and extracting semantic relationships between fields based on the field semantic descriptions; and outputting the inter-table semantic relationships and inter-field semantic relationships as metadata relationships. This invention solves the problem that existing technologies mainly focus on explicit structured information while neglecting deep semantic mining, significantly improving the efficiency and fault tolerance of data querying, integration, and modeling processes.

Description

Technical Field

[0001] This invention relates to the field of big data technology, and in particular to a method, system, and product for automatically extracting metadata and metadata relationships from database data. Background Technology

[0002] With the rapid development of informatization and digitalization, data plays an increasingly prominent role in enterprises and institutions, with almost all business data relying on database storage and management. Metadata, as data describing business data, provides background information and characteristic descriptions of business data. This includes descriptive metadata for discovering and identifying resources, structured metadata for describing data containers, and administrative metadata for helping to manage resources. Metadata is often used to describe, interpret, and locate data, making it easier to retrieve, use, and manage. It has become an indispensable element in all aspects of database governance, data integration, data quality control, and data security.

[0003] Currently, mainstream metadata extraction methods primarily rely on explicitly defined structural information in the database, such as table names, field names, field types, field comments, primary keys, and foreign key constraints. This information is typically obtained through metadata tables or database schemas. However, existing methods have significant shortcomings in practical applications, mainly including the following:

[0004] (1) Missing comments during the database design phase; such as missing table comments and field comments, which makes it difficult to understand and utilize the data semantics, bringing additional difficulties to data analysis, query and data governance, and increasing business costs.

[0005] (2) Foreign keys are not explicitly created. In order to improve query efficiency or due to development habits, foreign keys are usually not explicitly created in the database. However, this is the key to achieving data consistency. The lack of explicit association will weaken the contextual association between data, which will lead to data integration errors, data redundancy, and even more difficulty in tracing the data source.

[0006] (3) Data has semantic overlap; data from different databases have semantic overlap, but due to differences in field naming and storage structure, it is difficult to discover the semantic relationship between the data, which hinders data fusion and knowledge graph construction, making it difficult for data from different databases to work together.

[0007] (4) Insufficient semantic metadata; In scenarios such as data fusion and knowledge graph construction driven by artificial intelligence, data-driven analysis and decision-making can no longer rely entirely on structured metadata. Insufficient semantic metadata will not be able to support efficient analysis and modeling, and will limit data-driven analysis and decision-making. Therefore, richer and deeper semantic metadata is urgently needed as support.

[0008] The root of these problems lies in the fact that existing methods focus on the "appearance" rather than the "essence," concentrating on explicit structured information while neglecting deep semantic mining. This results in inefficiency and poor fault tolerance in data querying, integration, and modeling. Therefore, there is an urgent need for a method that can automatically complete, generate, and discover implicit metadata and its relationships. Summary of the Invention

[0009] To address the aforementioned problems, this invention provides a method, system, and product for automatically extracting metadata and metadata relationships. This addresses the technical issues of existing technologies that primarily focus on explicit structured information while neglecting deep semantic mining, resulting in low efficiency and poor fault tolerance in data querying, integration, and modeling processes.

[0010] In a first aspect, the present invention provides a method for automatically extracting metadata and metadata relationships, the method comprising:

[0011] Extract table information, table field information, and foreign key relationships between tables from all tables in the current database to obtain explicit metadata; extract multiple table sample data from each table.

[0012] The explicit metadata and multiple table sample data are assembled to obtain model instruction prompts. The model instruction prompts are used to call the large language model to output structured table semantic descriptions and field semantic descriptions, and output them as metadata.

[0013] Based on the semantic description of the tables, semantic relationships between tables are extracted; based on the semantic description of the fields, semantic relationships between fields are extracted; and the semantic relationships between tables and between fields are output as metadata relationships.

[0014] The semantic relationships between tables include one or more of the following: semantic similarity relationship, parent-child table relationship, and dictionary table relationship; the semantic relationships between fields include one or more of the following: primary-foreign key relationship and semantic equivalence relationship.

[0015] Furthermore, the table information includes: table name and table type;

[0016] The table field information includes: table name, field name, data type, field type, whether nullable, field key, and field value;

[0017] The foreign key relationship between tables includes: table name, field name, referenced table name, and referenced field name.

[0018] Furthermore, the extraction of semantically similar relationships based on the retrieval enhancement method includes:

[0019] For each table's explicit metadata and sample data, a semantic embedding model is invoked to generate a table semantic vector, with one table semantic vector generated for each table.

[0020] All generated table semantic vectors are stored in the vector search engine;

[0021] The system sets a prompt to determine whether tables have the same semantics, and calls the vector search engine to filter out tables with the same semantics as the current table, thus obtaining semantically identical relationships.

[0022] Furthermore, the step of calling the vector search engine to filter out tables with the same semantics as the current table and obtaining semantically identical relationships includes:

[0023] The search function of the vector search engine is invoked to retrieve a candidate similarity table with a semantic similarity threshold; the current table, the candidate similarity table, and the set prompts are concatenated and assembled, input into the large language model, and the semantic similarity relationship is output.

[0024] Furthermore, the extraction of the parent-child table relationship includes:

[0025] The set of explicit first parent-child table relationships can be inferred by examining the names of each table.

[0026] By inferring the reference relationships between various tables based on data types, the second set of parent-child table relationships can be obtained.

[0027] The hidden set of third parent-child table relationships is inferred by considering the various table types;

[0028] Find the union of the first set of parent-child table relationships, the second set of parent-child table relationships, and the third set of parent-child table relationships to obtain the parent-child table relationships.

[0029] Furthermore, the extraction of the dictionary table relations includes:

[0030] First determination: Based on the table name and field names, determine whether the current table conforms to the typical naming pattern to infer whether there is a relationship between the current table and the dictionary table; if it conforms to the typical naming pattern, the first determination result is that there is a relationship.

[0031] Second determination: Determine whether the field values ​​of the current table are completely within the range of the code field values ​​of the dictionary table; if so, the second determination result is that the current table and the dictionary table are related; otherwise, use an SQL statement to determine whether there is a relationship. If the SQL statement returns null, the second determination result is that there is no relationship; otherwise, the second determination result is that there may be a relationship.

[0032] Third determination: Count the number of unique values ​​in the current table's fields. If the number of unique values ​​is less than the set threshold, the third determination result is that there may be a relationship.

[0033] The first determination result, the second determination result, and the third determination result are combined to obtain the dictionary table relationship.

[0034] Furthermore, the extraction of the primary-foreign key relationship includes:

[0035] Filter by table name and field name to select field names ending with _id;

[0036] Get the number of records that do not meet the conditions, perform a data value inclusion check based on the number of records, determine foreign key constraints, and verify whether the references between data are complete;

[0037] Get the total number of records and the number of matched records, calculate the matching rate, and output the primary and foreign key relationships.

[0038] Furthermore, the extraction of the semantic equivalence relation includes:

[0039] Candidate field pairs are obtained by matching the similarity of field names, data types, and lengths between the two tables.

[0040] Calculate the overlap between two candidate fields to preliminarily determine whether semantic equivalence exists;

[0041] For each candidate field pair, sample the data and use a large language model to determine whether the semantics of the two candidate field pairs are the same, and then determine whether there is semantic equivalence.

[0042] When semantic equivalence exists in both determinations, the semantic equivalence relation is obtained.

[0043] In a second aspect, the present invention provides an automatic metadata and metadata relationship extraction system, including a memory, a processor, and a computer program stored in the memory, wherein the processor executes the computer program to implement the steps of any of the methods described above.

[0044] Thirdly, the present invention provides a computer program product, including a computer program / instructions that, when executed by a processor, implement the steps of any of the methods described above.

[0045] In summary, this invention provides a method, system, and product for automatically extracting metadata and metadata relationships. Compared with existing technologies, the technical solution conceived in this invention can achieve the following beneficial effects:

[0046] (1) The present invention is a database semantic metadata generation set relationship extraction method based on data sampling and large language model. It combines explicit metadata with multiple table sample data extracted from the table to assemble model instruction prompts, and uses the model instruction prompts to call the large language model to output structured table semantic descriptions and field semantic descriptions. It breaks through the limitation of traditional methods that can only extract explicit metadata, realizes the generation of metadata for semantic descriptions of tables and fields in the database, and automatically identifies semantic similarity relationships, parent-child table relationships, dictionary table relationships, primary and foreign key relationships, semantic equivalence relationships, etc. between tables and fields, supports more efficient analysis and modeling, as well as more intelligent and semantic data understanding and integration. It can automatically complete, generate and discover implicit metadata and its relationships. It solves the problem that the existing technology mainly focuses on explicit structured information and ignores deep semantic mining, which greatly improves the efficiency and fault tolerance of data query, integration and modeling.

[0047] (2) This invention combines naming heuristics rules, data types, and table types to infer the parent-child relationship between tables, which can effectively make up for the shortcomings of a single method, increase the contextual association between data, and more comprehensively and accurately identify the parent-child relationship. It is especially suitable for complex scenarios where field naming is not standardized, data values ​​are incomplete, or foreign keys are not explicitly defined in the table structure, thereby improving the accuracy and reliability of relationship inference.

[0048] (3) This invention combines field naming rule matching, field value range matching, and unique value quantity to infer dictionary table relationships. At the same time, it combines these three judgment results to improve the accuracy and reliability of the judgment, avoid misjudgment or omission, and is more flexible and efficient.

[0049] (4) This invention combines field name matching with matching rate / overlap count to reason about and analyze potential primary and foreign key relationships and infer semantic equivalence relationships between fields. This can quickly filter out potential associations and verify the rationality of these relationships through matching rate or overlap count. The combination of the two methods complements each other, improving the accuracy and reliability of semantic relationship extraction between fields. Even in the absence of explicit definitions, semantic discovery and data matching can enhance the database association mining, help discover potential semantic relationships between data, provide richer semantic metadata support for scenarios such as data fusion and knowledge graph construction, and increase the possibility of collaborative work between data from different databases. Attached Figure Description

[0050] To more clearly illustrate the technical solutions in this invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of this invention. For those skilled in the art, other drawings can be obtained from these drawings without creative effort.

[0051] Figure 1 This is a schematic diagram of the method steps for an automatic extraction method, system, and product of metadata and metadata relationships provided by the present invention;

[0052] Figure 2 This invention provides an SQL query statement for explicit metadata of a method, system, and product for automatically extracting metadata and metadata relationships.

[0053] Figure 3 This invention provides an automatic extraction method, system, and product for metadata and metadata relationships, along with an SQL query statement for parent-child table relationships.

[0054] Figure 4 This invention provides a first-determination SQL query statement for an automatic extraction method, system, and product of metadata and metadata relationships.

[0055] Figure 5 This invention provides a second-determination SQL query statement for an automatic extraction method, system, and product of metadata and metadata relationships.

[0056] Figure 6 This invention provides a third-determination SQL query statement for an automatic extraction method, system, and product of metadata and metadata relationships.

[0057] Figure 7 This invention provides an automatic extraction method, system, and product for metadata and metadata relationships, and includes an SQL query statement for filtering field names ending in _id.

[0058] Figure 8 This invention provides an SQL query statement for checking the data value inclusion of a method, system, and product for automatically extracting metadata and metadata relationships.

[0059] Figure 9 This invention provides an automatic extraction method, system, and product matching degree SQL query statement for metadata and metadata relationships.

[0060] Figure 10 This invention provides an SQL query statement for the number of overlapping metadata and metadata relationships in an automatic extraction method, system, and product. Detailed Implementation

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

[0062] It should be noted that, in the description of the embodiments of the present invention, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a method, step, or apparatus that includes a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to the method, step, or apparatus. Without further limitations, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the method, step, or apparatus that includes that element.

[0063] Firstly, this invention proposes an automatic metadata and metadata relationship extraction method, system, and product, which breaks through the limitation of traditional methods that can only extract explicit metadata. It realizes the generation of metadata that semantically describes tables and fields in the database, and automatically identifies semantic similarity relationships, parent-child table relationships, dictionary table relationships, primary-foreign key relationships, semantic equivalence relationships, etc. between tables and fields, supporting more efficient analysis and modeling, as well as more intelligent and semantic data understanding and integration. It solves the problem that existing technologies mainly focus on explicit structured information while neglecting deep semantic mining, and greatly improves the efficiency and fault tolerance of data query, integration, modeling and other links.

[0064] Specifically, such as Figure 1 As shown, the method includes:

[0065] S100: Extract table information, table field information, and foreign key relationships between tables in the current database to obtain explicit metadata; extract multiple table sample data from each table.

[0066] Specifically, table information includes: table name (table_name) and table type (table_type);

[0067] Table field information includes: table name (table_name), column name (column_name), data type (data_type), column type (column_type), whether it is nullable (is_null-able), column key (column_key), and column value (column_default);

[0068] Foreign key relationships between tables include: table name (table_name), column name (column_name), referenced table name (referenced_table_name), and referenced column name (referenced_column_name).

[0069] As an example, such as Figure 2 The image shows an SQL query statement for explicit metadata. The fields that can be parsed include: field name, data type, nullability, field key (i.e., whether it is a primary key - PRI or a foreign key - MUL), and field value.

[0070] For each table, multiple sample data entries are extracted from each table, representing the representative data records. The extraction strategy can employ one or more of the following methods, such as random sampling, systematic sampling, or first-and-last sampling, combined with other techniques.

[0071] S200: Assemble explicit metadata and sample data from multiple tables to obtain model instruction prompts, use model instruction prompts to call the large language model to output structured table semantic descriptions and field semantic descriptions, and output them as metadata.

[0072] In other words, the semantic description generation method based on a large language model can be as follows: First, set up model instruction prompts for semantic description generation, such as: "Give semantic descriptions of tables and fields based on explicit metadata and the given sample data." Then, assemble the extracted explicit metadata such as tables and fields, along with the collected sample data, to obtain the model instruction prompts; use the model instruction prompts to call the large language model to output structured table semantic descriptions and field semantic descriptions.

[0073] Model instruction prompts provide the model with a combination of task instructions and relevant data information to guide the model in generating the required output. Specifically, during assembly, explicit metadata such as table names, field names, and data types need to be integrated with the sample data to form a clear task description. When calling the large language model, the model instruction prompts are used as input. Based on this, the large language model outputs structured table semantic descriptions and field semantic descriptions. Semantic descriptions can be generated from input prompts, and the prompts can be iteratively optimized based on the results until a satisfactory semantic description is output.

[0074] For example, suppose the table name is "users", and the fields include: id (INT, primary key), name (VARCHAR, string), and age (INT, not null); the sample data is:

[0075]

[0076] The assembly model instruction prompt could be: "Based on the explicit metadata and the given sample data, please generate a semantic description of the table and fields; the table name is users, and the fields include id (primary key, integer), name (string, can be null), and age (integer, not null); the sample data is ID | NAME | AGE, and the specific values ​​include (1, Alice, 25), (2, Bob, 30)."

[0077] S300: Extract semantic relationships between tables based on table semantic descriptions, and extract semantic relationships between fields based on field semantic descriptions; and output the semantic relationships between tables and between fields as metadata relationships.

[0078] Among them, the semantic relationships between tables include one or more of the following: semantic similarity relationship, parent-child table relationship, dictionary table relationship; the semantic relationships between fields include one or more of the following: primary-foreign key relationship, semantic equivalence relationship.

[0079] As an example, extracting semantically similar relationships based on retrieval enhancement methods includes:

[0080] For each table's explicit metadata and sample data, a semantic embedding model is invoked to generate a table semantic vector, with one semantic vector generated for each table. The semantic embedding model can be bge-large-zh-v1.5, BERT-base-zh, RoBERTa-large-zh, BGE-M3, etc.

[0081] All generated semantic vectors are stored in the vector search engine (Milvus). The vector search engine is a search technology based on vector embedding and similarity calculation, used to find other vectors that are most similar to a given vector. It is a mature existing technology and will not be described in detail here.

[0082] Configure a hint to determine if tables have the same semantics, and call the vector search engine to filter out tables with the same semantics as the current table, thus obtaining semantic similarity relationships. For example, the hint could be: "Please filter from the following candidate tables to find tables with the same semantics as the current table, that is, tables whose data describes the same type of object, differing only in fields or stored data."

[0083] Furthermore, the vector search engine is invoked to filter out tables with the same semantics as the current table, thus obtaining semantic similarity relationships. This includes: invoking the search function of the vector search engine to retrieve candidate similar tables with a semantic similarity threshold; concatenating and assembling the current table, candidate similar tables, and set prompts, inputting them into the large language model, and outputting semantic similarity relationships.

[0084] For example, you can set a semantic similarity threshold, such as 0.7, and then call Milvus's search function to return all tables whose semantic vectors are more similar to the current table than that threshold.

[0085] By combining the current table, candidate similarity tables, and setting hints, we can achieve the following:

[0086] "Current table: [Table A name], fields include [Field 1 name] ([Field 1 type]), [Field 2 name] ([Field 2 type]), etc."

[0087] Candidate similar table: [Table B name], with fields including [Field 3 name] ([Field 3 type]), [Field 4 name] ([Field 4 type]), etc.

[0088] Hint: Please determine whether table A and table B have the same semantic meaning, paying particular attention to whether there is a relationship between field [Field X Name] and field [Field Y Name].

[0089] After concatenation, the data is input into any major language model such as BERT, RoBERTa, or GPT, and the output will show semantically identical relationships.

[0090] Vector search engines excel at efficient vector-based retrieval, while large language models can deeply understand the semantics of natural language text. This combination can fully utilize the semantic understanding capabilities of large language models to transform semantically rich vector representations into text, thereby achieving more accurate semantic matching and improving efficiency and fault tolerance in data query, integration, and modeling processes.

[0091] To enhance the contextual relevance between data and more comprehensively and accurately identify parent-child table relationships, this invention combines naming heuristics, data types, and table types to infer parent-child table relationships, thereby improving the accuracy and reliability of relationship inference.

[0092] As an example, such as Figure 3 This is an SQL query statement representing a parent-child table relationship.

[0093] As an example, extracting the parent-child table relationship includes:

[0094] The explicit set of parent-child table relationships can be inferred by examining the table names. Since most database designs follow certain naming conventions, some parent-child table relationships can be inferred by using naming heuristics and based on the naming conventions.

[0095] By inferring the reference relationships between tables through data types, a second set of parent-child table relationships can be obtained; this is also known as the data comparison method, which infers reference relationships through data types. For example, if the value of A.column always appears in B.pk_column, then A can be considered a child table of B.

[0096] The hidden set of third-party parent-child table relationships is inferred by considering various table types. In other words, potential parent-child relationships are inferred based on table types, and preliminary inferences are made by analyzing the table structure, column names, and data types. For example, a column in the child table may have the same data type as the primary key column in the parent table; a column in the child table may have a name similar to the primary key name in the parent table; or there may be multiple combinations of fields in the child table.

[0097] Find the union of the first set of parent-child table relationships, the second set of parent-child table relationships, and the third set of parent-child table relationships to obtain the parent-child table relationships.

[0098] This invention combines naming heuristics, data types, and table types to infer parent-child table relationships, effectively compensating for the shortcomings of single methods, increasing the contextual relevance between data, and enabling more comprehensive and accurate identification of parent-child table relationships. It is especially suitable for complex scenarios with non-standard field naming, incomplete data values, or table structures that do not explicitly define foreign keys, thereby improving the accuracy and reliability of relationship inference.

[0099] In addition, this invention combines field naming rule matching, field value range matching, and the number of unique values ​​to infer dictionary table relationships. By combining these three judgment results, the accuracy and reliability of the judgment are improved, misjudgment or omission is avoided, and the process is more flexible and efficient.

[0100] As an example, extracting dictionary table relationships includes:

[0101] First determination: Based on the table name and field names, determine whether the current table conforms to the typical naming pattern to infer whether there is a relationship between the current table and the dictionary table; if it conforms to the typical naming pattern, the first determination result is that there is a relationship.

[0102] like Figure 4 As shown, there are some typical naming conventions for field names, such as: field names like status, gender, type, category, or field names ending with code. Potential relationships between the current table and the dictionary table are inferred through field matching. For example, a field named orders.status_code is associated with the dictionary table dict_status.code; a field named users.gender is associated with the dictionary table dict_gender.code.

[0103] The second determination is to check whether the field values ​​of the current table are entirely within the range of the code field values ​​in the dictionary table. If so, the second determination indicates that there is a relationship between the current table and the dictionary table. Otherwise, an SQL query is performed. If the query returns an empty string, the second determination indicates that there is no relationship. Otherwise, the second determination indicates that there may be a relationship. In other words, it means that some of the field values ​​of the current table are within the range of the code field values ​​in the dictionary table, and there may be a relationship between the two.

[0104] like Figure 5 The image shows the SQL query statement used in the second determination.

[0105] Third determination: Count the number of unique values ​​in the current table's fields. If the number of unique values ​​is less than the set threshold, the third determination result is that there may be a relationship.

[0106] Since the field values ​​are discrete short strings and their number is limited, a method such as... can be used. Figure 6 The SQL query shown performs the following steps: selects the table name and field names, calculates the number of unique values ​​for each field, and names this number `uniq_count`; specifies which table (`your_table`) to retrieve the data from, and groups the data by table name and field names to perform aggregation calculations on the values ​​of each field; finally, it returns only the grouped results where the number of unique values ​​is less than 10. This helps identify possible dictionary table fields or low-cardinality fields, which are typically used to store a finite, normalized set of values.

[0107] Finally, the first, second, and third judgment results are combined to obtain the dictionary table relationship.

[0108] For example, the combined result could be: "First decision: The current table is related to the dictionary table. Second decision: The current table is not related to the dictionary table. Third decision: The current table is related to the dictionary table." This combined result can be input into any major language model such as BERT, RoBERTa, or GPT to output the dictionary table relationships.

[0109] This invention combines field name matching with matching rate / overlap count to infer potential primary and foreign key relationships and infer semantic equivalence relationships between fields. It can quickly filter out potential associations and verify the rationality of these relationships through matching rate or overlap count. The combination of the two methods complements each other, improving the accuracy and reliability of semantic relationship extraction between fields. Even in the absence of explicit definitions, it can enhance the database association mining through semantic discovery and data matching, helping to discover potential semantic relationships between data. This provides richer semantic metadata support for scenarios such as data fusion and knowledge graph construction, and increases the possibility of collaborative work between data from different databases.

[0110] As an example, extracting the primary-foreign key relationship includes:

[0111] Filter by table name and field name to select field names ending with _id;

[0112] Get the number of records that do not meet the conditions, perform a data value inclusion check based on the number of records, determine foreign key constraints, and verify whether the references between data are complete;

[0113] Get the total number of records and the number of matched records, calculate the matching rate, and output the primary and foreign key relationships.

[0114] It should be noted that you can use the field name similarity matching method to match the field name with the table name and primary key name to filter out field names ending with _id; you can also use the field naming rules, since field name = table name + _id, table name is field name minus _id, and the sub-field name is the same as the parent table's primary key field, so you can break down the field name and match the sub-field name with the main table name.

[0115] like Figure 7 As shown, first, specify the table name and field names to query; then specify the data source as the database's metadata table, which stores field information for all tables; finally, set filter conditions to find records where field names end with "_id". By finding all tables and fields in the database whose field names end with "_id", this helps identify potential foreign key or primary key fields, assisting database administrators or developers in understanding the reference relationships within the database.

[0116] Specifically, the data value inclusion check is as follows:

[0117] If the set of values ​​in `child_table.col` is ⊆ `parent_table.col`, then there is a primary-foreign key relationship. Otherwise, an SQL query is used to determine this. If the query result `ismatched=0`, it means that all `user_id` values ​​in `child_table` exist in the `id` field of `users` table, indicating a complete match in the `user_id` field, and thus a potential primary-foreign key relationship. If the query result `ismatched>0`, it means that some `user_id` values ​​in `child_table` are not in the `id` field of `users` table, and the returned value represents the number of mismatched records; therefore, there is no primary-foreign key relationship.

[0118] like Figure 8 As shown, retrieve the number of records that do not meet the conditions and name the result "ismatched"; specify the table to be queried as child_table; set the filter conditions to find all records in child_table whose user_id field value is not in the id field of the users table.

[0119] Get the total number of records and the number of matched records, calculate the matching rate, and output the primary and foreign key relationships. Furthermore, when the matching rate is higher than the matching threshold, a primary and foreign key relationship exists; otherwise, a primary and foreign key relationship does not exist.

[0120] like Figure 9 As shown, this query counts the total number of records in the `orders` table and the number of records where the `user_id` value in the `orders` table matches the `id` value in the `users` table. The `orders` table is specified as the query target. Assuming there are 100 records in the `orders` table, and 90 of them have a `user_id` value that also exists in the `id` field of the `users` table, the query result would be: total = 100 records, matched = 90 records. Therefore, the matching rate is: matched / total = 90 / 100 = 90%. Evaluating the data consistency between the `user_id` field in the `orders` table and the `id` field in the `users` table helps identify potential data problems or optimize database design.

[0121] As one example, extracting semantic equivalence relations includes:

[0122] Candidate field pairs are obtained by matching the similarity of field names, data types, and lengths between the two tables.

[0123] Calculate the overlap between two candidate fields to preliminarily determine whether semantic equivalence exists.

[0124] Specifically, the overlap number is calculated as follows: count the number of unique values ​​in two candidate fields, and the size of their intersection is the overlap number. If the overlap number is greater than the overlap threshold, it means that the value sets of the two fields almost completely overlap, and they have a semantic equivalence relationship; otherwise, there is no semantic equivalence relationship.

[0125] like Figure 10 As shown, count the number of unique values ​​in field Aa of table A and name the result count_a; count the number of unique values ​​in field Bb of table B and name the result count_b; use the CASE statement to match the equal values ​​of field Aa in table A and field Bb in table B. When the values ​​of Aa and Bb are equal, select the value of Aa, count the number of unique values ​​in the intersection of the values ​​of fields Aa and Bb, and name the result intersection; specify the tables A and B involved in the query, and use the Cartesian product to obtain the overlap number.

[0126] For example, suppose table A has field Aa with the values ​​1, 2, 3, 4, and 5, which have 5 unique values; suppose table B has field Bb with the values ​​4, 5, 6, 7, and 8, which also have 5 unique values; and the intersection of Aa and Bb contains two values, 4 and 5, then the number of overlapping values ​​in the query result is 2.

[0127] For each candidate field pair, sample the data and use a large language model to determine whether the two candidate field pairs have the same semantics, and then determine whether there is semantic equivalence. The large model's judgment prompt can be: "Please find the field with the same semantics as the current field from the list of candidate similar fields, that is, the stored target data is the same."

[0128] When both determinations are semantically equivalent, a semantic equivalence relation is obtained.

[0129] In a second aspect, the present invention also provides an automatic metadata and metadata relationship extraction system, including a memory, a processor, and a computer program stored in the memory, wherein the processor executes the computer program to implement the steps of any of the methods described above.

[0130] Furthermore, the processor may include an explicit metadata and sample data extraction module, a semantic recognition module, and a semantic relationship extraction module, used to identify table semantics and field semantics in the database, as well as extract semantic relationships between tables and between fields.

[0131] Thirdly, the present invention also provides a computer program product, including a computer program / instructions that, when executed by a processor, implement the steps of any of the methods described above.

[0132] The specific technical features of the system and product are consistent with the technical features of the method, and will not be repeated here.

[0133] In summary, this invention supports more efficient analysis and modeling, as well as more intelligent and semantic data understanding and integration. It breaks through the limitations of traditional methods that can only extract explicit metadata, enabling the generation of metadata that semantically describes tables and fields in a database. It automatically identifies semantic relationships between tables and fields, parent-child table relationships, dictionary table relationships, primary-foreign key relationships, semantic equivalence relationships, etc., supporting more efficient analysis and modeling, and more intelligent and semantic data understanding and integration. It can automatically complete, generate, and discover implicit metadata and their relationships. It addresses the problem that existing technologies mainly focus on explicit structured information while neglecting deep semantic mining, significantly improving the efficiency and fault tolerance of data querying, integration, and modeling processes.

[0134] It should be noted that, for the sake of simplicity, the foregoing embodiments are all described as a series of actions. However, those skilled in the art should understand that this application is not limited to the described order of actions, as some steps may be performed in other orders or simultaneously according to this application. Furthermore, those skilled in the art should also understand that the embodiments described in the specification are preferred embodiments, and the actions and modules involved are not necessarily essential to this application.

[0135] In the above embodiments, the descriptions of each embodiment have different focuses. For parts not described in detail in a certain embodiment, please refer to the relevant descriptions in other embodiments.

[0136] In the several embodiments provided in this application, it should be understood that the disclosed methods or systems can be implemented in other ways. For example, the embodiments described above are merely illustrative. For instance, the division of units is only a logical functional division, and in actual implementation, there may be other division methods. For example, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed.

[0137] The foregoing description is merely an exemplary embodiment of this disclosure and should not be construed as limiting the scope of this disclosure. Any equivalent changes and modifications made in accordance with the teachings of this disclosure shall still fall within the scope of this disclosure. Those skilled in the art will readily conceive of embodiments of this disclosure upon considering the specification and practicing the disclosure herein. This application is intended to cover any variations, uses, or adaptations of this disclosure that follow the general principles of this disclosure and include common knowledge or customary techniques in the art not described herein. The specification and embodiments are to be considered exemplary only, and the scope and spirit of this disclosure are defined by the claims.

[0138] The technical features of the above embodiments can be combined in any way. For the sake of brevity, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.

[0139] Those skilled in the art will readily understand that the above description is merely a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of the present invention should be included within the scope of protection of the present invention.

Claims

1. A method for automatically extracting metadata and metadata relationships, characterized in that, The method includes: Extract table information, table field information, and foreign key relationships between tables from all tables in the current database to obtain explicit metadata; extract multiple table sample data from each table. The explicit metadata and multiple table sample data are assembled to obtain model instruction prompts. The model instruction prompts are used to call the large language model to output structured table semantic descriptions and field semantic descriptions, and output them as metadata. Based on the semantic description of the tables, semantic relationships between tables are extracted; based on the semantic description of the fields, semantic relationships between fields are extracted; and the semantic relationships between tables and between fields are output as metadata relationships. The semantic relationships between tables include one or more of the following: semantic similarity relationship, parent-child table relationship, and dictionary table relationship; the semantic relationships between fields include one or more of the following: primary-foreign key relationship and semantic equivalence relationship. Extracting the parent-child table relationships includes: inferring the explicit first set of parent-child table relationships by inferring the reference relationships between the tables by inferring the data types to obtain the second set of parent-child table relationships; inferring the hidden third set of parent-child table relationships by inferring the table types; and taking the union of the first set of parent-child table relationships, the second set of parent-child table relationships, and the third set of parent-child table relationships to obtain the parent-child table relationships.

2. The method for automatically extracting metadata and metadata relationships according to claim 1, characterized in that, The table information includes: table name and table type; The table field information includes: table name, field name, data type, field type, whether nullable, field key, and field value; The foreign key relationship between tables includes: table name, field name, referenced table name, and referenced field name.

3. The method for automatically extracting metadata and metadata relationships according to claim 1, characterized in that, Extracting the semantically similar relationships based on retrieval enhancement methods includes: For each table's explicit metadata and sample data, a semantic embedding model is invoked to generate a table semantic vector, with one table semantic vector generated for each table. All generated table semantic vectors are stored in the vector search engine; The system sets a prompt to determine whether tables have the same semantics, and calls the vector search engine to filter out tables with the same semantics as the current table, thus obtaining semantically identical relationships.

4. The method for automatically extracting metadata and metadata relationships according to claim 3, characterized in that, The vector search engine is invoked to filter out tables with the same semantics as the current table, obtaining semantically similar relationships, including: The search function of the vector search engine is invoked to retrieve a candidate similarity table with a semantic similarity threshold; the current table, the candidate similarity table, and the set prompts are concatenated and assembled, input into the large language model, and the semantic similarity relationship is output.

5. The method for automatically extracting metadata and metadata relationships according to claim 1, characterized in that, Extracting the dictionary table relations includes: First determination: Based on the table name and field names, determine whether the current table conforms to the typical naming pattern, and infer whether there is a relationship between the current table and the dictionary table; if it conforms to the typical naming pattern, the first determination result is that there is a relationship. Second determination: Determine whether the field values ​​of the current table are completely within the range of the code field values ​​of the dictionary table; if so, the second determination result is that the current table and the dictionary table are related; otherwise, use an SQL statement to determine whether there is a relationship. If the SQL statement returns null, the second determination result is that there is no relationship; otherwise, the second determination result is that there is a relationship. Third determination: Count the number of unique values ​​in the field values ​​of the current table. If the number of unique values ​​is less than a set threshold, the third determination result is that there is a relationship. The first determination result, the second determination result, and the third determination result are combined to obtain the dictionary table relationship.

6. The method for automatically extracting metadata and metadata relationships according to claim 1, characterized in that, Extracting the primary-foreign key relationship includes: Filter by table name and field name to select field names ending with _id; Get the number of records that do not meet the conditions, perform a data value inclusion check based on the number of records, determine foreign key constraints, and verify whether the references between data are complete; Get the total number of records and the number of matched records, calculate the matching rate, and output the primary and foreign key relationships.

7. The method for automatically extracting metadata and metadata relationships according to claim 1, characterized in that, Extracting the semantic equivalence relation includes: Candidate field pairs are obtained by matching the similarity of field names, data types, and lengths between the two tables. Calculate the overlap between two candidate fields to preliminarily determine whether semantic equivalence exists; For each candidate field pair, sample the data and use a large language model to determine whether the semantics of the two candidate field pairs are the same, and then determine whether there is semantic equivalence. When semantic equivalence exists in both determinations, the semantic equivalence relation is obtained.

8. An automatic metadata and metadata relationship extraction system, comprising a memory, a processor, and a computer program stored in the memory, characterized in that, The processor executes the computer program to implement the steps of the method according to any one of claims 1 to 7.

9. A computer program product comprising a computer program / instructions, characterized in that, When the computer program / instructions are executed by the processor, they implement the steps of the method according to any one of claims 1 to 7.

Images