A table association relationship automatic identification method based on field semantic recognition

Abstract

The application relates to the technical field of data processing, and discloses a table association relation automatic discrimination method based on field semantic recognition, which comprises the following steps: step S1: obtaining table metadata information and field metadata information from a database; step S2: dividing fields into five type groups; step S3: generating a field relation candidate set; step S4: judging the quantity relation of a field relation pair according to a trusted relation mark between two fields in the field relation; step S5: calculating the sampling matching degree of the two fields in the field relation pair; step S6: judging whether the field relation pair belongs to a real association relation pair; and step S7: extracting the field relation pair belonging to the real association relation pair, and querying a field association relation between association relation tables based on the field relation pair. The application can automatically find corresponding association relation tables under the condition that a main table or a slave table lacks key bit mark information.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of data processing, more particularly, it relates to a table association relationship automatic identification method based on field semantic recognition. BACKGROUND

[0002] Data in the database often appears in the form of tables and fields, when the data table is constantly split and reused in the engineering project of the code, the master-slave table relationship is derived. The primary key of the master table and the foreign key of the slave table are the association marks between the master-slave tables, however, due to the fact that the master-slave key information of the table cannot be effectively maintained in time in engineering, it is difficult to find the corresponding association relationship table in the case of missing key mark information of the master table or the slave table. Quickly identifying the corresponding association relationship table has a multiplier effect on the management of the database, the development of the project and the operation and maintenance of the data in engineering, which can greatly facilitate the development of business. SUMMARY

[0003] The present application provides a table association relationship automatic identification method based on field semantic recognition, which solves the technical problem that it is difficult to find the corresponding association relationship table in the case of missing key mark information of the master table or the slave table in the related art.

[0004] The present application provides a table association relationship automatic identification method based on field semantic recognition, which includes the following steps:

[0005] Step S1: obtaining table metadata information and field metadata information from the database;

[0006] Step S2: defining field characteristics for each field and calculating to form a field data set, using a random forest algorithm model to classify and identify the field data set and dividing it into five type groups;

[0007] Step S3: generating a field relationship candidate set according to the five groups of field data sets obtained in step S2, the field relationship pairs in the field relationship candidate set meet the following conditions: the two fields of a field relationship pair are not repeated and are not in the same table;

[0008] A field relationship pair includes field A and field B, the number of matching data of the sampling data of field A in the database of field B is counted as the matching number of field A, and the number of matching data of the sampling data of field B in the database of field A is counted as the matching number of field B;

[0009] The number of non-repeated data in the matching data of the sampling data of field A in the database of field B is counted as the de-duplication matching number of field A, and the number of non-repeated data in the matching data of the sampling data of field B in the database of field A is counted as the de-duplication matching number of field B;

[0010] When the number of matches of field A is greater than the set first matching threshold or the number of matches of field A is greater than or equal to the number of sampling data of field B, it is determined that field A has a relationship with field B, and a trusted relationship mark is marked;

[0011] When the number of matches of field B is greater than the set first matching threshold or the number of matches of field B is greater than or equal to the number of sampling data of field A, it is determined that field B has a relationship with field A, and a trusted relationship mark is marked;

[0012] Step S4: Determine the quantity relationship of the field relationship pair according to the trusted relationship mark between the two fields in the field relationship;

[0013] Mark the quantity relationship of all field relationship pairs as one-to-one, one-to-many, many-to-many, and no quantity relationship. One-to-one relationship requires that the sampling data of the field relationship pair is a unique value, and the field relationship pair has a trusted relationship mark. One-to-many relationship requires that one of the sampling data of the field relationship pair has a unique value attribute and has a trusted relationship mark. Many-to-many relationship requires that the sampling data of the field relationship pair has no unique value attribute and has no trusted relationship mark. In addition to the above three, other field relationship pairs are no quantity relationship;

[0014] Step S5: Calculate the sampling matching degree of the two fields in the field relationship pair, as follows:

[0015] MRoword=UCoword / SCrword

[0016] MRrword=UCrword / SCoword

[0017] Wherein, MC oword and MC rword respectively represent the sampling matching degree of field A and associated field B; UC oword and UC rword respectively represent the number of de-duplication matches of field A and associated field B; SC oword and SC rword respectively represent the number of sampling data of field A and associated field B;

[0018] Step S6: Determine whether the field relationship pair belongs to the real association relationship pair in combination with the data type group to which the field relationship pair belongs, the trusted relationship mark, and the sampling matching degree;

[0019] Step S7: Extract the field relationship pair belonging to the real association relationship pair, and query the field association relationship from the association relationship table based on the field relationship pair.

[0020] Further, the field characteristics are respectively whether containing data, whether containing valid data, whether the sampling data is unique, the maximum length value in the sampling data, the minimum length value in the sampling data, the average length value in the sampling data, the median length value in the sampling data, whether the data contains only numbers, whether the data contains only letters, whether the data contains only a combination of letters and numbers, whether the data contains only a combination of letters and dash characters, whether the data contains only a combination of letters, numbers and dash characters, whether the data contains Chinese, whether the data contains numbers, whether the data contains letters, whether the data contains dash characters, whether the data contains other special characters, whether the data starts with 0, the ratio of the maximum and minimum length in the sampling data, the frequency of each length in the sampling data, the total proportion of unique data in the sampling data, the average number of 0-9 numbers in the sampling data, the number of different data lengths in the sampling data, the ratio of the number of different lengths and the maximum and minimum length difference in the sampling data, whether the data is a self-increasing number, and the actual number of sampled data.

[0021] Further, the field data of the five type groups respectively belongs to the following five types: 1. pure English type; 2. English and dash symbol type, English and number type, English and number and dash symbol type; 3. mixed type of English and number and dash symbol random combination; 4. 0 starting number type; 5. non-0 starting number type and self-increasing number type;

[0022] The number of classifiers in the random forest algorithm model in step 2 is set to 100, and the Gini coefficient tree is used as a sub-tree. Train this set of random forest trainers on all database data to be identified table association relationship. The training process can be represented by the following formula:

[0023] S train =T n (C feature ,C category )

[0024] Where T n represents all sub-trees of the random forest trainer; C feature represents the calculated characteristic value of all fields; C category represents the type value corresponding to each field after manual division; S train is the final trained trainer.

[0025] Further, step S2 simultaneously performs the sampling work of the detailed data under the field, and the characteristics of the self-increasing number are represented by the following formula:

[0026] selfadd=S len_count ∩S len_ratio (S len_count >2,S len_ratio ≥0.6)

[0027] S len_count = len conut

[0028] S len_ratio = len conut / (MAX len(sample) -MIN len(sample) +1)

[0029] wherein selfadd represents a self-increment type data type; len conut represents a length value of each sampling data; S len_count represents a number of different data lengths of the sampling data under the same field; S len_ratio is a maximum-minimum length difference ratio of the sampling data under the same field; MAX len(sample) is a maximum length of the sampling data under the same field; MIN len(sample) is a minimum length thereof;

[0030] The formula means that the number of different data lengths of the sampling data under the same field is greater than or equal to 2, and the maximum-minimum length difference ratio of the sampling data under the same field is greater than or equal to 0.6, and then the self-increment type data is determined.

[0031] Further, the field relation pair meeting the following condition is deleted in step S3: the semantic similarity of two fields is greater than a set first semantic similarity threshold value, the intersection coincidence degree is greater than a set first intersection coincidence threshold value, or the sampling matching degree is greater than a set first sampling matching threshold value.

[0032] The semantic similarity calculation formula is as follows:

[0033] sms = 0.5(lcs (oword,rword) / min(len oword , len rword ))+0.5((1-dis (oword,rword) ) / max(len oword , len rword ))

[0034] wherein lcs is the longest common subsequence between two fields, dis is the edit distance between two fields, and len oword and len rword respectively represent the length value of the associated field and the length value of the associated field.

[0035] The first semantic similarity threshold value is set to 0.7, and only the relation pair greater than the threshold value can pass the semantic similarity verification.

[0036] Further, the maximum and minimum values of the sample data of the two fields in the field relation pair are extracted, and the three length intersection relations are divided into three types: fixed length-fixed length type, fixed length-variable length type and variable length-variable length type;

[0037] The definitions of fixed length and variable length are as follows:

[0038]

[0039] Wherein, len fixed represents fixed length, len chg represents variable length, according to the definition of the formula, the maximum and minimum length of the sample data under the field are equal, which is a fixed length data field; otherwise, it is a variable length data field.

[0040] Further, according to the division of three length intersection relations, the overlapping verification is carried out, including:

[0041] In the fixed length-fixed length type relation pair, since the maximum length of the field sample data is the same as the minimum length, the maximum length or the minimum length of the associated field sample data is equal to any one of the lengths of the former, and it is determined that the sample data length distribution of the relation pair has complete overlap, and other conditions of this type of relation pair are considered to have no overlap;

[0042] In the fixed length-variable length type relation pair, only when the sample data length of the fixed length field is contained in the maximum and minimum length interval range of the sample data of the variable length field, it is determined that the sample data length distribution of the relation pair has complete overlap, and otherwise, there is no overlap in the relation pair of this type;

[0043] In the variable length-variable length relation pair, when the maximum length or the minimum length of one variable length field sample data is within the maximum or minimum length interval range of the other variable length field sample data, it is determined that the length distribution of this type of relation pair has intersection, and on this basis, if the maximum or minimum length interval of a variable length field sample data is completely contained in the maximum or minimum length interval of another variable length field sample data, and the maximum length of the sample data of the two variable length fields is equal to 8; it is determined that the sample data length distribution of the relation pair has complete overlap;

[0044] If there is intersection but no complete inclusion, the intersection coincidence degree of the sample data length interval of the two variable length fields in the relation pair needs to be further calculated, and the intersection coincidence degree is calculated according to the following formula:

[0045] area length_cross =(len couple -len cross ) / min(lenrange_oword len range_rword )

[0046] len couple = (MAX length(sample_oword) -MIN length(sample_oword) ) + (MAX length(sample_rword) -MIN length(sample_rword) )

[0047] len cross = max(MAX length(sample_oword) , MAX length(sample_rword) ) - min(MIN length(sample_oword) , MIN length(sample_rword) )

[0048] len range_oword = MAX length(sample_oword) -MIN length(sample_oword)

[0049] len range_rword = MAX length(sample_rword) -MIN length(sample_rword)

[0050] wherein MAX length(sample_oword) , MIN length(sample_oword) , MAX length(sample_rword) , MIN length(sample_rword) respectively represent the maximum length, the minimum length of the field sampling data and the maximum length, the minimum length of the associated field sampling data;

[0051] According to the above formula, len couple , len cross , len range_oword , len range_rword , which represent the sum of the length interval of two field sampling data, the total length interval of two fields after intersection on the sampling data, the sampling data length interval of the field and the sampling data length interval of the associated field, can be obtained according to the four data; length_cross , that is, the intersection coincidence degree of the last field relationship pair;

[0052] In addition, for the field relationship pair in this case, in addition to obtaining their intersection coincidence degree, their standard deviation on the sampling data also needs to be calculated, which is used to measure the consistency degree of the length distribution of two fields on the sampling data in the relationship pair, and finally the similarity formula of the length distribution of two fields on the sampling data is set as follows:

[0053] total cross = 0.8area length_cross + 0.2len std

[0054] wherein, len std is the standard deviation of the field relation pair on the sampling data; the field relation pair with the similarity of the sampling data on the length distribution greater than a set first similarity threshold value is defined as having complete overlapping consistency, otherwise it is determined as not having overlapping consistency.

[0055] Further, the step S6 comprises the following steps:

[0056] Step S61: using the random forest algorithm model to make a classification of the association relation pair type according to the feature values calculated by each field relation pair;

[0057] Step S62: using the random forest algorithm trainer to randomly extract features and samples for weighted training of classification, to form a group of trainers that can be used to identify the association type of the relation pair;

[0058] Step S63: putting the field relation pair into the random forest algorithm trainer mentioned above to make an association relation judgment and identification, to determine the association relation of the relation pair, the association relation of the relation pair being divided into the following three types: design relation pair, non-design relation pair and non-association relation pair;

[0059] wherein the design relation pair means that the random forest classifier considers that the relation pair belongs to a true association relation pair, the non-design relation pair means that the classifier shows that there is only a surface relation from the field values of the relation pair and no actual association relation, and the last one is the non-association relation pair without any meaningful association.

[0060] Further, the feature values are respectively the field semantic similarity, the data length overlapping consistency, the quantity relation type mark of the relation pair, the sampling matching degree, the data type group to which the field relation pair belongs, and the credible relation mark of the field relation pair.

[0061] The present application provides a table association relation automatic discrimination system based on field semantic recognition, which is used to execute the table association relation automatic discrimination method based on field semantic recognition.

[0062] The present application has the following beneficial effects:

[0063] The present application can automatically find the corresponding association relation table in the case that the primary table or the secondary table lacks key position mark information. BRIEF DESCRIPTION OF DRAWINGS

[0064] Fig. 1 is a flowchart of the table association relation automatic discrimination method based on field semantic recognition of the present application.

[0065] Fig. 2 is a flowchart of judging whether the field relation pair belongs to a true association relation pair or not. Detailed Implementation

[0066] The subject matter described herein will now be discussed with reference to exemplary embodiments. It should be understood that these embodiments are discussed only to enable those skilled in the art to better understand and implement the subject matter described herein, and changes may be made to the function and arrangement of the elements discussed without departing from the scope of this specification. Various processes or components may be omitted, substituted, or added as needed in the examples. Furthermore, features described in some examples may be combined in other examples.

[0067] Example 1

[0068] like Figs. 1-2 As shown, an automatic table relationship determination method based on data matching and field semantic recognition includes the following steps:

[0069] Step S1: Retrieve table metadata and field metadata from the database;

[0070] In one embodiment of the present invention, SQL statements are used to first obtain the metadata of all tables and fields from the corresponding database. Dirty data is removed from fields starting with "extend", fields without data type information are removed, invalid tables are filtered, and tables with too few records are deleted.

[0071] The data types of the fields are uniformly divided into numeric and text types. The original data types in the fields are summarized according to the definitions of numeric and text types. Fields that do not fall within the range of these two data types are directly filtered (such as date data types).

[0072] Step S2: Define field features for each field and perform calculations to form a field dataset. Use the random forest algorithm model to classify and identify the field dataset and divide it into five type groups.

[0073] The field features are respectively whether to contain data, whether to contain valid data, whether the sampling data is unique, the maximum length value in the sampling data, the minimum length value in the sampling data, the average length value in the sampling data, the median length value in the sampling data, whether the data only contains numbers, whether the data only contains letters, whether the data only contains a combination of letters and numbers, whether the data only contains a combination of letters and dash characters, whether the data only contains a combination of letters, numbers and dash characters, whether the data contains Chinese, whether the data contains numbers, whether the data contains letters, whether the data contains dash characters, whether the data contains other special characters, whether the data is 0-started, the ratio of the maximum and minimum length in the sampling data, the frequency of each length in the sampling data, the total proportion of unique data in the sampling data, the average number of 0-9 numbers in the sampling data, the number of different data lengths in the sampling data, the ratio of the number of different lengths and the maximum and minimum length difference, whether the data is a self-increasing number, and the actual number of sampled data.

[0074] The field data of the five type groups respectively belongs to the following five types: 1, pure English type; 2, English and dash symbol type, English and number type, English and number and dash symbol type; 3, mixed type of English and number and dash symbol random combination; 4, 0-started number type; 5, non-0-started number type and self-increasing number type.

[0075] The number of classifiers (subtrees) of the random forest algorithm model is set to 100, the Gini coefficient tree is used as a subtree, and the whole set of random forest trainers is trained on all database data to be identified table association relationship. The training process can be represented by the following formula:

[0076] S train =T n (C feature ,C category )

[0077] Wherein, T n represents all the subtrees of the random forest trainer; C feature represents the calculated feature value of all fields; C category represents the type value (marked as 0-7) corresponding to each field after artificial division; S train is the final trained trainer.

[0078] Marked as 0-7, respectively corresponding to 1, pure English type; 2, English and dash symbol type; 3, English and number type; 4, English and number and dash symbol type; 5, mixed type of English and number and dash symbol random combination; 6, 0-started number type; 7, non-0-started number type; 8, self-increasing number type.

[0079] The detailed data sampling work under the field is performed simultaneously in step S2, wherein the characteristic of the self-increment type number is represented by using the following formula:

[0080] selfadd=S len_count ∩S len_ratio (S len_count >2,S len_ratio ≥0.6)

[0081] S len_count =len conut

[0082] S len_ratio =len conut / (MAX len(sample) -MIN len(sample) +1)

[0083] Wherein, selfadd represents the self-increment type data; len conut represents the length value of each sampling data; S len_count represents the number of different data lengths of the sampling data under the same field; S len_ratio is the maximum-minimum length difference ratio of the sampling data under the same field; MAX len(sample) is the maximum length of the sampling data under the same field; MIN len(sample) is the minimum length thereof;

[0084] The formula means that the number of different data lengths of the sampling data under the same field is greater than or equal to 2, and the maximum-minimum length difference ratio of the sampling data under the same field is greater than or equal to 0.6, so that the self-increment type number is determined.

[0085] In an embodiment of the present application, the field data set without effective data is deleted, the field under the sampling data containing Chinese or other special characters is excluded, the field with an average length of sampling data greater than 64 is removed, and the field containing less than a threshold value of sampling data is filtered out.

[0086] Step S3: generating a field relation candidate set according to the five groups of field data sets obtained in step S2, wherein the field relation pairs in the field relation candidate set satisfy the following conditions: the two fields of a field relation pair are not repeated and are not same tables;

[0087] The same table means that the two fields belong to the same table.

[0088] In an embodiment of the present application, the field relation pair satisfying the following conditions is deleted in step S3: the semantic similarity of the two fields is greater than a set first semantic similarity threshold value, the intersection coincidence degree is greater than a set first intersection coincidence threshold value, or the sampling matching degree is greater than a set first sampling matching threshold value;

[0089] A field relation pair includes field A and field B, the number of matching data of the sampling data of field A in the database of field B is counted as the matching number of field A, and the number of matching data of the sampling data of field B in the database of field A is counted as the matching number of field B;

[0090] In an embodiment of the present application, the semantic similarity calculation formula is as follows:

[0091] sms = 0.5(lcs (oword,rword) / min(len oword ,len rword ))+0.5((1-dis (oword,rword) ) / max(len oword ,len rword )

[0092] Wherein, lcs is the longest common subsequence between two fields, dis is the edit distance between two fields, and len oword and len rword respectively represent the length value of the associated field and the length value of the associated field. The above formula can be seen that the semantic similarity sms between two fields is weighted by half of the longest common subsequence and the edit distance.

[0093] In an embodiment of the present application, the first semantic similarity threshold is set to 0.7, and only the relation pair greater than the threshold can pass the semantic similarity verification. In addition, if a field in the relation pair meets the condition that all sampling data are unique in features, the field is identified as a candidate primary key, and the relation pair can directly bypass the semantic similarity verification.

[0094] In an embodiment of the present application, the maximum and minimum values of the sampling data of the two fields in the field relation pair are extracted, and three length intersection relations are divided: fixed length-fixed length type, fixed length-variable length type and variable length-variable length type.

[0095] The definition of fixed length and variable length is as follows:

[0096]

[0097] Wherein, len fixed represents fixed length, and len chg represents variable length. According to the formula definition, the maximum and minimum length of the sampling data under the field are equal, which is a fixed length data field; otherwise, it is a variable length data field.

[0098] According to the division of three length intersection relations, the overlapping verification is carried out, including:

[0099] In the fixed-length-fixed-length type of relation pair, since the maximum length of the field sample data is the same as the minimum length, the maximum length or the minimum length of the associated field sample data is equal to either of the lengths of the former, and it is determined that the sample data length distribution of the relation pair has complete overlap. In addition, other cases of this type of relation pair are considered to have no overlap.

[0100] In the fixed-length-variable-length type of relation pair, the sample data length distribution of the relation pair is determined to have complete overlap only when the sample data length of the fixed-length field is contained in the maximum and minimum length interval range of the variable-length field sample data. In addition, in other cases of this type of relation pair, there is no overlap.

[0101] In the variable-length-variable-length type of relation pair, when the maximum length or the minimum length of the sample data of one variable-length field is within the maximum or minimum length interval range of the sample data of the other variable-length field, it is determined that the length distribution of this type of relation pair has an intersection. On this basis, if the maximum or minimum length interval of the sample data of a variable-length field is completely contained in the maximum or minimum length interval of the sample data of the other variable-length field, and the maximum length of the sample data of the two variable-length fields is greater than or equal to 8, it is determined that the sample data length distribution of the relation pair has complete overlap. If there is an intersection but not complete containment, the intersection overlap degree of the length interval of the sample data of the two variable-length fields in the relation pair needs to be calculated, and the intersection overlap degree is normalized according to the following formula:

[0102] area length_cross =(len couple -len cross ) / min(len range_oword ,len range_rword )

[0103] len couple =(MAX length(sample_oword) -MIN length(sample_oword ))+(MAX length(sample_rword) -MIN length(sample_rword) )

[0104] len cross =max(MAX length(sample_oword) ,MAX length(sample_rword) )-min(MIN length(sample_oword) ,MIN length(sample_rword) )

[0105] len range_oword =MAX length(sample_oword) -MIN length(sample_oword)

[0106] lenrange_rword = MAX length(sample_rword) - MIN length(sample_rword)

[0107] wherein MAX length(sample_oword) , MIN length(sample_oword) , MAX length(sample_rword) , MIN length(sample_rword) respectively represent the maximum length, the minimum length of the field sampling data and the maximum length, the minimum length of the associated field sampling data;

[0108] According to the above formula, len couple , len cross , len range_oword , len range_rword , the sum of the length interval of the two field sampling data, the total length interval of the two fields on the sampling data after intersection, the sampling data length interval of the field and the sampling data length interval of the associated field are represented by the four lengths; length_cross , that is, the intersection coincidence degree of the last field relationship pair;

[0109] In addition, for the field relationship pair in this case, in addition to obtaining their intersection coincidence degree, it is also necessary to calculate their standard deviation on the sampling data, which is used to measure the consistency degree of the length distribution of the two fields on the sampling data, and the similarity formula of the length distribution of the two fields on the sampling data is finally set as follows:

[0110] total cross = 0.8area length_cross + 0.2len std

[0111] wherein len std is the standard deviation of the field relationship pair on the sampling data; the field relationship pair with the similarity of the length distribution of the two fields on the sampling data greater than the set first similarity threshold is defined as having complete overlapping, otherwise it is recognized as not having overlapping.

[0112] In an embodiment of the present application, the statistics are based on the sampling data of fields A and B, and the count function in the SQL statement is used to complete.

[0113] The number of non-repeated data in the matching data of the sampling data of field A in the database of field B is counted as the de-duplication matching number of field A; the number of non-repeated data in the matching data of the sampling data of field B in the database of field A is counted as the de-duplication matching number of field B;

[0114] When the number of matches of field A is greater than the set first match threshold or the number of matches of field A is greater than or equal to the number of sampling data of field B, it is determined that field A has a relationship with field B, and a trusted relationship mark is marked.

[0115] When the number of matches of field B is greater than the set first match threshold or the number of matches of field B is greater than or equal to the number of sampling data of field A, it is determined that field B has a relationship with field A, and a trusted relationship mark is marked.

[0116] The reason why such bidirectional matching verification is needed is that the relationship determination in the relationship pair is a primary-foreign key relationship, which belongs to a one-way determination case, so bidirectional verification is needed to ensure that the relationship pair is completely verified. Thus, the data conditions needed for further determination of the specific relationship type after the mutual matching degree calculation of the field relationship pair are obtained.

[0117] Step S4: Determine the quantity relationship of the field relationship pair according to the trusted relationship mark between the two fields in the field relationship;

[0118] The quantity relationship of all field relationship pairs is marked as one-to-one, one-to-many, many-to-many, and no quantity relationship. One-to-one relationship needs to meet that the sampling data of the field relationship pair are unique values, and the field relationship pair has a trusted relationship mark; one-to-many relationship needs to meet that one of the sampling data of the field relationship pair has a unique value attribute and has a trusted relationship mark; many-to-many relationship needs to meet that the sampling data of the field relationship pair have no unique value attribute and have no trusted relationship mark; other field relationship pairs are no quantity relationship.

[0119] The sampling data has a unique value attribute, such as a table storing user information that needs to ensure that the mobile phone number of each record is different. At this time, it is necessary to set the unique value attribute.

[0120] Step S5: Calculate the sampling matching degree of the two fields in the field relationship pair, as shown in the following formula:

[0121] MR oword =UC oword / SC rword

[0122] MR rword =UC rword / SC oword

[0123] Wherein, MC oword and MC rword respectively represent the sampling matching degree of field A and associated field B; UC oword and UC rword respectively represent the number of de-duplication matches of field A and associated field B; SCoword with SC rword respectively represent the number of sampling data of field A and associated field B;

[0124] Step S6: judging whether the field relation pair belongs to the real associated relation pair according to the data type group to which the field relation pair belongs, the trust relation mark and the sampling matching degree.

[0125] In an embodiment of the present application, step S6 includes the following steps:

[0126] Step S61: using the random forest algorithm model to classify the association relation pair type according to the feature value calculated for each field relation pair;

[0127] The feature values are respectively field semantic similarity, data length overlap (different types of overlap are defined differently), number relation type mark of the relation pair, sampling matching degree, data type group to which the field relation pair belongs, and field relation pair trust relation mark.

[0128] Step S62: using the random forest algorithm trainer to randomly extract features and samples for weighted training of classification, to form a set of trainers that can be used to identify the association type of the relation pair.

[0129] Step S63: putting the field relation pair into the random forest algorithm trainer mentioned above to judge and identify the association relation, so as to determine the association relation of the relation pair, which is divided into the following three types: design relation pair, non-design relation pair and unrelated relation pair.

[0130] The design relation pair means that the random forest classifier considers that the relation pair belongs to the real associated relation pair, the non-design relation pair means that the classifier indicates that there is only a superficial relation from the field value of the relation pair and no actual associated relation, and the last one is the unrelated relation pair without any meaningful association.

[0131] In an embodiment of the present application, the random forest algorithm trainer is a Gini coefficient sub-tree classifier.

[0132] In an embodiment of the present application, the field semantic similarity, data length overlap, relation pair design type and number relation type, and data type group to which the field relation pair belongs are weighted processed, the final confidence for each field relation pair is calculated as a reference value of the association relation trust degree of the field relation pair, and the trusted classification relation pair is further determined.

[0133] Step S6 also measures the confidence of each association relation pair;

[0134] For example, a design relationship pair with a confidence level above 0.8 is a trusted design relationship pair, a non-design relationship pair with a confidence level between 0.6 and 0.8 is a trusted non-design relationship pair, and a non-correlation relationship pair with a confidence level below 0.6 is a trusted non-correlation relationship pair. Then, the relationship pairs corresponding to the confidence level division are all untrusted divisions.

[0135] The identified field relationship pairs belonging to the true correlation relationship pairs actually correspond to the correlation relationship of the fields between different tables;

[0136] Step S7: Extract the field relationship pairs belonging to the true correlation relationship pairs, query the field correlation relationship between the correlation relationship tables based on the field relationship pairs, and mark the fields of the tables in the field relationship pairs based on the queried field correlation relationship.

[0137] One method of marking is to mark the foreign keys.

[0138] The above describes the embodiments of the present embodiment, but the present embodiment is not limited to the specific embodiments described above, and the specific embodiments described above are only illustrative and not restrictive. Those skilled in the art can make many forms under the inspiration of the present embodiment, which are all within the protection of the present embodiment.

Claims

1. An automatic table association relationship identification method based on field semantic recognition, characterized in that, Comprise the following steps: Step S1: Obtain table metadata information and field metadata information from the database; Step S2: Define field characteristics for each field and calculate to constitute a field dataset, use a random forest algorithm model to classify and divide the field dataset into five type groups; Step S3: Generate a field relationship candidate set according to the five groups of field dataset obtained in step S2, the field relationship pairs in the field relationship candidate set meet the following conditions: the two fields of a field relationship pair are not repeated and are not in the same table; A field relationship pair includes field A and field B, count the number of matching data of the sampling data of field A in the database of field B as the matching number of field A, count the number of matching data of the sampling data of field B in the database of field A as the matching number of field B; Count the number of non-repeated data in the matching data of the sampling data of field A in the database of field B as the de-duplication matching number of field A; Count the number of non-repeated data in the matching data of the sampling data of field B in the database of field A as the de-duplication matching number of field B; When the matching number of field A is greater than the set first matching threshold or the matching number of field A is greater than or equal to the number of sampling data of field B, it is determined that field A has a relationship with field B, and a trusted relationship mark is marked; When the matching number of field B is greater than the set first matching threshold or the matching number of field B is greater than or equal to the number of sampling data of field A, it is determined that field B has a relationship with field A, and a trusted relationship mark is marked; Step S4: Determine the quantity relationship of the field relationship pair according to the trusted relationship mark between the two fields in the field relationship; Mark the quantity relationship of all field relationship pairs, divided into one-to-one, one-to-many, many-to-many, and no quantity relationship, one-to-one relationship needs to meet that the sampling data of the field relationship pair is unique value, and the field relationship pair has a trusted relationship mark; One-to-many relationship needs to meet that one of the sampling data of the field relationship pair has a unique value attribute, and has a trusted relationship mark; Many-to-many relationship needs to meet that the sampling data of the field relationship pair has no unique value attribute, and does not have a trusted relationship mark; In addition to the above three, other field relationship pairs are no quantity relationship; Step S5: Calculate the sampling matching degree of the two fields in the field relationship pair, see the following formula: ； wherein, denote the sampling matching degree of field A and associated field B, respectively; denote the sampling matching degree of field A and associated field B, respectively; denote the deduplicated matching number of field A and associated field B, respectively; denote the deduplicated matching number of field A and associated field B, respectively; denote the sampling data number of field A and associated field B, respectively; denote the sampling data number of field A and associated field B, respectively; Step S6: Determine whether the field relationship pair belongs to the true association relationship pair in combination with the data type group to which the field relationship pair belongs, the trusted relationship mark, and the sampling matching degree; Step S7: Extract the field relationship pairs belonging to the true association relationship pair, and query the field association relationship between the association relationship tables based on the field relationship pair.

2. The automatic table join relationship identification method based on field semantic recognition according to claim 1, characterized in that, The field characteristics are respectively whether the data is contained, whether the valid data is contained, whether the sampling data is unique, the maximum length value in the sampling data, the minimum length value in the sampling data, the average length value in the sampling data, the median length value in the sampling data, whether the data only contains numbers, whether the data only contains letters, whether the data only contains a combination of letters and numbers, whether the data only contains a combination of letters and dash characters, whether the data only contains a combination of letters, numbers and dash characters, whether the data contains Chinese, whether the data contains numbers, whether the data contains letters, whether the data contains dash characters, whether the data contains other special characters, whether the data is 0-started, the ratio of the maximum and minimum length in the sampling data, the frequency of each length in the sampling data, the total proportion of unique data in the sampling data, the average number of 0-9 numbers in the sampling data, the number of different data lengths in the sampling data, the ratio of the number of different lengths and the maximum and minimum length difference, whether the data is a self-increasing number, and the actual number of sampled data.

3. The automatic table join relationship identification method based on field semantic recognition according to claim 1, characterized in that, The field data of the five type groups respectively belongs to the following five types:

1. pure English type; 2. English and dash symbol type, English and number type, English and number and dash symbol type; 3. mixed type of English and number and dash symbol random combination; 4. 0-started number type; 5. non-0-started number type and self-increasing number type; The number of classifiers of the random forest algorithm model in step 2 is set to 100, and the Gini coefficient tree is used as a sub-tree. The whole set of random forest trainers is trained on all database data of the table association relationship to be identified, and the training process can be represented by the following formula: ； wherein, represents all sub-trees of the random forest trainer; represents the feature values calculated after all fields; represents the type values corresponding to each field after artificial division; is the final trained trainer.

4. The automatic table join relationship identification method based on field semantic recognition according to claim 1, characterized in that, Step S2 simultaneously performs the detailed data sampling work under the field, wherein the characteristics of the self-increasing number are represented by the following formula: ； wherein, represents a self-increment type data type; represents a length value of each sampling data; represents a number of different data lengths of the sampling data under the same field; is a maximum-minimum length difference ratio of the sampling data under the same field; is a maximum length of the sampling data in the same field; is a minimum length thereof; The formula means that the number of different data lengths of the sampling data under the same field is greater than or equal to 2, and the ratio of the maximum and minimum length difference of the sampling data under the same field is greater than or equal to 0.6, and then it is judged as a self-increasing number.

5. The automatic table join relationship identification method based on field semantic recognition according to claim 1, characterized in that, In step S3, the field relationship pairs meeting the following conditions are deleted: the semantic similarity of the two fields is greater than the set first semantic similarity threshold, the intersection coincidence degree is greater than the set first intersection coincidence threshold, or the sampling matching degree is greater than the set first sampling matching degree threshold. The semantic similarity calculation formula is as follows: ； wherein, is the longest common subsequence between two fields, is the edit distance between two fields, and and denote the length value of the associated field and the length value of the associated field, respectively; The first semantic similarity threshold is set to 0.7, and only the relationship pairs greater than the threshold can pass the semantic similarity verification.

6. The automatic table join relationship identification method based on field semantic recognition according to claim 1, characterized in that, The maximum and minimum values of the sampling data of the two fields in the field relationship pair are extracted, and three kinds of length intersection relationships are divided according to the maximum and minimum values: fixed length-fixed length type, fixed length-variable length type and variable length-variable length type. The definitions of fixed length and variable length are as follows: ； wherein, represents fixed length, represents variable length, according to the definition of the formula, the maximum and minimum length of the sampling data under this field are equal, which is a fixed length data field; otherwise, it is a variable length data field.

7. The automatic table join relationship identification method based on field semantic recognition according to claim 6, characterized in that, According to the division of the three kinds of length intersection relationships, the overlapping verification is performed, including: In the relationship pair of fixed length-fixed length type, since the maximum length and the minimum length of the field sampling data are the same, the maximum length or the minimum length of the associated field sampling data is equal to any one of the lengths of the former, and it is determined that the sampling data length distribution of the relationship pair has complete overlapping, and other conditions of the type relationship pair are considered to have no overlapping. In the fixed-length and variable-length type of relationship pair, the sampling data length distribution of the relationship pair has complete consistent overlap only when the sampling data length of the fixed-length field is contained in the maximum and minimum length interval range of the sampling data of the variable-length field. Otherwise, there is no overlap in other cases; In the variable-length and variable-length type of relationship pair, if the maximum or minimum length of the sampling data of one variable-length field is within the maximum or minimum length interval range of the sampling data of the other variable-length field, it is determined that the relationship pair has intersection in the length distribution. On this basis, if the maximum or minimum length interval of the sampling data of a variable-length field is completely contained in the maximum or minimum length interval of the sampling data of the other variable-length field, and the maximum length of the sampling data of the two variable-length fields is greater than or equal to 8, it is determined that the sampling data length distribution of the relationship pair has complete consistent overlap. If there is intersection but not complete containment, the intersection overlap degree of the sampling data length interval of the two variable-length fields in the relationship pair needs to be further calculated, and the intersection overlap degree is normalized according to the following formula: ； ； ； ； ； wherein, , , , respectively represent the maximum length, the minimum length of the field sampling data and the maximum length, the minimum length of the associated field sampling data. According to the four data, the above formula can be obtained , , , , the sum of the length interval of the two field sampling data, the total length interval on the sampling data after the intersection of the two fields, the length interval of the field sampling data and the length interval of the associated field sampling data; the above four lengths are obtained , that is, the intersection coincidence degree of the last field relation pair; In addition, for the field relationship pair in this case, in addition to obtaining their intersection overlap degree, the standard deviation of their sampling data also needs to be calculated to measure the consistency degree of the length distribution of the two fields in the sampling data. Finally, the similarity formula of the length distribution of the two fields in the sampling data is as follows: ； wherein, is the standard deviation of the field relation pair on the sampled data; field relation pairs whose similarity in the length distribution of the sampled data is greater than a set first similarity threshold are defined as having complete overlap, otherwise no overlap is recognized.

8. The automatic table join relationship identification method based on field semantic recognition according to claim 1, characterized in that, Step S6 includes the following steps: Step S61: using the random forest algorithm model to classify the relationship pair type according to the feature values calculated for each field relationship pair; Step S62: using the random forest algorithm trainer to randomly extract features and samples for weighted training of classification, forming a set of trainers that can be used to identify the association type of the relationship pair; Step S63: putting the field relationship pair into the random forest algorithm trainer mentioned above to judge and identify the association relationship, so as to determine the association relationship of the relationship pair. The association relationship of the relationship pair is divided into the following three types: design relationship pair, non-design relationship pair and unrelated relationship pair. Among them, the design relationship pair means that the random forest classifier considers that the relationship pair belongs to the true association relationship pair, the non-design relationship pair means that the classifier shows that there is only a superficial relationship between the field values of the relationship pair and no actual meaningful association relationship, and finally there is no meaningful unrelated relationship pair.

9. The automatic table join relationship identification method based on field semantic recognition according to claim 1, characterized in that, The feature values are respectively the field semantic similarity, the data length overlap, the number relationship type label of the relationship pair, the sampling matching degree, the data type group to which the field relationship pair belongs, and the field relationship pair credible relationship label.

Images