Methods for discovering associationable tables based on dynamic data, electronic devices and storage media

Abstract

This invention discloses a method, electronic device, and storage medium for discovering associative tables based on dynamic data. The method includes: constructing a static similarity matrix based on field data content; acquiring database operation logs and dynamically dividing them into time slices; calculating the dynamic similarity between fields based on logs within each time slice to generate a dynamic similarity matrix; statistically analyzing the co-occurrence frequency of different data tables within each time slice to generate a co-occurrence matrix; and fusing the above three matrices to calculate the comprehensive correlation between table pairs and field pairs, thereby determining the correlation between tables. The system includes corresponding functional modules. This invention solves the technical problem of automatically and accurately identifying truly business-related tables and filtering out non-business-meaningful similarities in massive datasets. It is mainly used for table association analysis in enterprise-level database systems and can significantly improve the efficiency and accuracy of association discovery.

Description

Technical Field

[0001] This invention relates to the fields of data analysis and data application technology, specifically a method, system, and storage medium for discovering associative tables based on dynamic data. Background Technology

[0002] In the field of industrial information technology, especially in data integration, data analysis, business interface development, and data element applications, accurately identifying the business relationships between database tables is a crucial and fundamental task. However, in legacy industrial systems, this task faces significant challenges, with its complexity far exceeding that of conventional information systems, primarily for the following reasons:

[0003] 1) System complexity and scale. Industrial information systems (such as ERP and MES) carry extremely complex business logic and processes, resulting in massive underlying databases with thousands of tables (for example, large ERP systems often have over 9,000 business tables, and MES systems often have over 6,000). Such a massive scale and high complexity make it extremely difficult, time-consuming, labor-intensive, and prone to errors to manually clarify the relationships between tables.

[0004] 2) Lack of documentation and inconsistent standards. Many legacy systems lack comprehensive database design documentation (such as data dictionaries), causing information such as field meanings and table structure design intentions to be obscured. Furthermore, the various subsystems of these systems are often built in phases by different developers, resulting in significant differences in design standards and naming conventions, further complicating the process of reverse-engineering relationships from the structural level.

[0005] 3) Ambiguous data instance characteristics. To meet the needs of rapid business iteration, industrial systems widely adopt non-standard techniques such as auto-incrementing primary keys, causing the primary and foreign key relationships between tables to lose their explicit characteristics at the data instance level. This means that relying solely on traditional methods based on primary and foreign key constraints or field name similarity is insufficient to effectively discover true business relationships.

[0006] Currently, the technical methods for addressing the above problems mainly fall into two categories:

[0007] Schema-level analysis: This method relies on the structured metadata of the database. However, in the context of severe lack of documentation in legacy industrial systems, this method is largely ineffective and often degenerates into manual judgment by experts who rely heavily on personal experience, resulting in low efficiency and difficulty in scaling.

[0008] Instance-Level Analysis: This approach shifts to focusing on the data instances themselves. Common techniques include field clustering based on set similarity (such as Jaccard indexes) or association mining using pre-trained models. While the former does not require documents, it incurs huge computational overhead and struggles with massive tables; the latter faces bottlenecks such as high model training costs, poor generalization ability, and the need for specific training for different data.

[0009] More importantly, existing methods generally suffer from a fundamental limitation: they fail to clearly distinguish between "data similarity" and "business relevance." While related, they are not equivalent. For example, the primary key of a sales order and the primary key of an employee attendance record may be highly similar in data type and value range distribution, but they have no actual business connection. This misjudgment will directly lead to a large amount of invalid associations and noise in data cleaning, integration, and business interface development. Therefore, industrial practice urgently needs an automated method that can integrate multi-dimensional information and efficiently identify true business relationships. Summary of the Invention

[0010] To overcome the above-mentioned shortcomings, this invention aims to provide an automated and efficient method for discovering relationships between business data tables. This method integrates multi-dimensional information to identify field pairs with strong correlations between different table pairs from massive data tables in industrial systems, so as to quickly and efficiently identify data tables with real business logic relationships and effectively filter out invalid relationships that have data similarity but no business relevance.

[0011] To achieve the above objectives, the present invention adopts the following technical solution:

[0012] The first objective of this invention is to propose a method for discovering associative tables based on dynamic data, comprising the following steps:

[0013] 1) Construct a static similarity matrix based on the content similarity of data sets in each field of the database;

[0014] 2) Obtain the database operation log and store it in the message queue. Based on the timestamp sequence of the database operation log, dynamically divide the time into time slices by calculating the timestamp interval, and divide the database operation log records into the corresponding time slices.

[0015] 3) Based on the database operation log records within the time slice, calculate the dynamic similarity between fields and generate a dynamic similarity matrix through incremental updates;

[0016] 4) Based on time slicing, count the co-occurrence frequency of different data tables within the same time slice, and generate a co-occurrence matrix;

[0017] 5) Based on the static similarity matrix, dynamic similarity matrix, and co-occurrence matrix, calculate the comprehensive correlation degree of field pairs in different tables to obtain the correlation of different table pairs.

[0018] Preferably, constructing the static similarity matrix includes the following steps:

[0019] 1) Obtain the field metadata of the database table, filter out the fields suitable for establishing relationships, and construct an array of field metadata;

[0020] 2) Create a hash value signature table, and concatenate the table name and field names into a long string and store it in the information field of the hash value signature table;

[0021] 3) Obtain all data values ​​corresponding to each field to form a data set, use the MinHash algorithm to generate a K-dimensional MinHash signature vector for each data set, and fill it back into the hash value signature table;

[0022] 4) Using the Locality Sensitive Hash (LSH) method, the MinHash signature vector is mapped to buckets in multiple hash tables, and fields mapped to the same bucket are identified as candidate similar field pairs;

[0023] 5) Calculate the similarity of the similar field pairs and generate the static similarity matrix.

[0024] Preferably, the steps of acquiring database operation logs and storing them in a message queue, and dynamically dividing the database operation log records into corresponding time slices based on the timestamp sequence of the database operation logs by calculating the timestamp interval, include the following steps:

[0025] 1) Obtain the database operation log and store the parsed database operation log records into the message queue;

[0026] 2) After the last timestamp of the last processing, obtain the timestamp sequence of the database operation log in the message queue, and filter out the valid timestamp sequence from the timestamp sequence according to the preset minimum time interval and maximum time interval;

[0027] 3) Calculate the interval between adjacent timestamps in the effective timestamp sequence, determine the maximum time interval, and divide the database operation log records into corresponding time slices using the adjacent timestamps corresponding to the maximum time interval as the dividing points.

[0028] Preferably, generating the dynamic similarity matrix includes the following steps:

[0029] 1) Based on each time slice, create an initially empty set of fields and a set of data values;

[0030] 2) Iterate through the database operation log records in each time slice, extract the fields and data values ​​involved according to the operation type, and update the field set and data value set of each slice;

[0031] 3) Based on the updated set of data values, calculate the similarity between any two fields within each time slice;

[0032] 4) Based on the field similarity within each time slice, the dynamic similarity matrix is ​​incrementally updated to generate the final dynamic similarity matrix.

[0033] Preferably, generating the co-occurrence matrix includes the following steps:

[0034] 1) Traverse each time shard, extract all data tables involved in the database operation log within that shard, and form the table set for that shard;

[0035] 2) For each time slice, traverse all possible data table pairs in its table set. If a table pair appears simultaneously in the slice, increment the count of the table pair in the co-occurrence matrix by 1.

[0036] 3) Output the calculated co-occurrence matrix.

[0037] Preferably, the step of calculating the comprehensive correlation degree of field pairs between different tables based on the static similarity matrix, dynamic similarity matrix, and co-occurrence matrix to obtain the correlation of different table pairs includes the following steps:

[0038] 1) For the fields in the database table to be queried, extract candidate related fields from any other tables in the database from the static similarity matrix and the dynamic similarity matrix to form a comprehensive candidate set;

[0039] 2) Based on the comprehensive candidate set, calculate the comprehensive correlation degree of field pairs in different tables to obtain the correlation of different table pairs.

[0040] Preferably, the calculation of the overall correlation degree of field pairs in different tables includes the following steps:

[0041] 1) Obtain the co-occurrence frequency of different table pairs and the static and dynamic similarity of the field pairs of these different table pairs;

[0042] 2) Calculate the co-occurrence probability of different table pairs;

[0043] 3) Calculate the overall correlation between field pairs in different tables based on the co-occurrence probability.

[0044] The second objective of this invention is to provide a system for discovering associative tables based on dynamic data, comprising:

[0045] The static similarity matrix construction module is used to construct a static similarity matrix based on the content similarity of data sets in each field of the database.

[0046] The time sharding module is used to obtain database operation logs and store them in a message queue, and dynamically divide the database operation logs into time shards based on the timestamps of the database operation logs, dividing the database operation log records into the corresponding time shards.

[0047] The dynamic similarity matrix generation module is used to calculate the dynamic similarity between fields based on database operation log records within time slices, and generate a dynamic similarity matrix through incremental updates.

[0048] The co-occurrence matrix generation module is used to calculate the co-occurrence frequency of different data tables within the same time slice based on time slices and generate a co-occurrence matrix.

[0049] The comprehensive correlation calculation module is used to calculate the comprehensive correlation of field pairs in different tables based on the static similarity matrix, dynamic similarity matrix, and co-occurrence matrix, so as to obtain the correlation of different table pairs.

[0050] A third objective of this invention is to provide an electronic device comprising:

[0051] A memory and a processor, wherein the memory stores a computer program, wherein the computer program, when executed by the processor, implements any step of the aforementioned method for discovering associative tables based on dynamic data.

[0052] The fourth objective of this invention is to provide a computer-readable storage medium having a computer program stored thereon, wherein:

[0053] When the computer program is executed by the processor, it implements any of the steps of the aforementioned method for discovering associative tables based on dynamic data.

[0054] The beneficial effects of this invention are as follows:

[0055] 1) This invention constructs a multi-dimensional correlation determination model by comprehensively calculating three major indicators: static similarity, dynamic similarity, and business co-occurrence. This method effectively overcomes the limitations of single-dimensional analysis, efficiently identifying field pairs with strong correlations between different table pairs, thereby identifying data tables with real business logic connections, significantly improving the accuracy of correlation table discovery, and effectively filtering out interference items that only have data similarity but no business correlation.

[0056] 2) This invention innovatively introduces time slicing technology, which can automatically capture and aggregate database operation logs representing independent business events. Based on this, it quantifies the co-occurrence pattern of data tables in the business context through co-occurrence matrix, ensuring that the correlation determination closely revolves around the real business scenario, fundamentally eliminating accidental similarity associations without business meaning, and enhancing the robustness of the method in complex industrial environments.

[0057] 3) When dealing with massive industrial-grade data tables, this invention applies the MinHash algorithm to efficiently reduce the dimensionality of the field data set when calculating static similarity, transforming the problem of large-scale set similarity comparison into the similarity calculation of signature vectors. Furthermore, by employing Local Sensitive Hash (LSH) technology, only a small number of candidate similar pairs need to be precisely calculated, rather than comparing all fields pairwise. This reduces the algorithm complexity from O(N²) of traditional methods to near O(N), significantly improving computational efficiency and reducing storage and computational resource overhead, making association analysis of large-scale data tables feasible. Attached Figure Description

[0058] Figure 1 This is a schematic diagram of a method for discovering associative tables based on dynamic data according to an embodiment of the present invention.

[0059] Figure 2 This is a schematic diagram of the process for discovering associative tables based on dynamic data, according to an embodiment of the present invention. Detailed Implementation

[0060] Embodiments of the present invention will now be described in more detail with reference to the accompanying drawings. While some embodiments of the invention are shown in the drawings, it should be understood that the invention can be implemented in various forms and should not be construed as limited to the embodiments set forth herein. Rather, these embodiments are provided to provide a more thorough and complete understanding of the invention. It should be understood that the accompanying drawings and embodiments are for illustrative purposes only and are not intended to limit the scope of protection of the invention. All other embodiments obtained by those skilled in the art based on the embodiments of the present invention without inventive effort are within the scope of protection of the present invention.

[0061] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this invention pertains. The terminology used herein in the description of the invention is for the purpose of describing particular embodiments only and is not intended to be limiting of the invention. The term "or / and" as used herein includes any and all combinations of one or more of the associated listed items.

[0062] Terminology Explanation

[0063] Dynamic data:

[0064] Database operation log data, also known as time-series data.

[0065] Related tables:

[0066] In database design, multiple data tables are logically linked through specific fields, defining which database tables have business relationships. Generally, inner / left / right relationships are used to link and clean up tables with business relationships. Simultaneously, associative tables are developed in the business interface to clearly define the business logic.

[0067] Jaccard similarity:

[0068] A non-Euclidean distance metric for quantifying the similarity between two sets is expressed mathematically as J(A,B) = |A∩B∣ / |A∪B∣, with values ​​ranging from [0,1]. This metric characterizes the degree of overlap between two sets by calculating the ratio of the number of elements in their intersection to the number of elements in their union.

[0069] MinHash:

[0070] A similarity-preserving dimensionality reduction technique based on probabilistic hashing compresses features from high-dimensional datasets using a family of randomized hash functions, generating fixed-length signature vectors. Its core principle is that for two sets A and B, their Jaccard similarity equals the proportion of the minimum values ​​of their MinHash signatures across all hash functions. By selecting k independent hash functions, the original sets can be mapped to k-dimensional binary or integer signatures, preserving the similarity between sets while reducing computational complexity from O(n²) to O(k). This algorithm is widely used in large-scale dataset similarity detection (e.g., document deduplication, recommender systems), distributed computing frameworks (e.g., Spark MLlib implementations), and approximate nearest neighbor (ANN) search scenarios, making it an effective tool for handling high-dimensional sparse data.

[0071] Kafka:

[0072] A distributed publish-subscribe messaging system employing a partition and consumer group model, featuring high throughput, persistent storage, and horizontal scalability. Originally developed for building a real-time database operation log aggregation system, it is now widely used in data pipelines, stream processing (such as integration with Apache Flink / Spark), and event-driven architectures.

[0073] JSON:

[0074] A lightweight data exchange format based on JavaScript syntax, independent of any programming language, and widely used for network transmission, configuration files, and data storage.

[0075] LSH:

[0076] Locality Sensitive Hashing (LSH) is a fast nearest neighbor search algorithm for massive high-dimensional data. It enables the rapid identification of similarity sets. A probabilistic approximate nearest neighbor search (ANNS) framework designed for high-dimensional sparse data is proposed. By constructing a family of hash functions sensitive to similar data, it maps similar data points in the original space to the same hash bucket with a high probability, thereby achieving fast similarity retrieval under massive data conditions.

[0077] Change Data Capture (CDC):

[0078] CDC (Database Operation Log) is used to identify and track data changes (inserts, deletes, and updates) in a database and propagate these changes to downstream systems or services in real-time or near real-time. At its core, CDC monitors the database operation logs (such as MySQL's binlog, PostgreSQL's WAL, and Oracle's redo log). Almost all database systems use database operation logs to ensure data durability and consistency (and for recovery in the event of a crash).

[0079] Static similarity:

[0080] Similarity analysis and measurement refers to the analysis and measurement of data based on existing data structures and content, provided that the data remains unchanged (i.e., without real-time updates or dynamic changes). In the fields of databases or data analysis, it is typically used to assess the degree of similarity between data fields, records, or sets, achieving efficient comparison through pre-calculated features (such as hash signatures, statistical indicators, etc.), rather than processing streaming data in real time.

[0081] Dynamic similarity:

[0082] In scenarios where data is updated or changes in real-time or near real-time, this method dynamically assesses the similarity between different tables or fields by analyzing the correlation of data changes captured in database operation logs. Its core lies in leveraging the interconnected changes in table data during business operations to identify fields or tables with strong correlations, making it suitable for scenarios requiring real-time responses to dynamic data evolution.

[0083] Co-occurrence matrix:

[0084] A two-dimensional matrix used to quantify the relationships between data tables reflects the strength of business relationships between tables by counting the frequency of multiple tables appearing simultaneously within a shard at the same time. Its core lies in leveraging the real-time nature of database operation logs to capture the interconnected characteristics of dynamic data changes.

[0085] Example 1

[0086] This invention provides a method for discovering associative tables based on dynamic data. The technical solutions in this invention will be clearly and completely described below with reference to the accompanying drawings.

[0087] To illustrate the technical solution of the present invention in more detail, the following embodiments will be illustrated using a customer's production ERP database as an example, introducing the entire process of a method for discovering associative tables based on dynamic data.

[0088] This example uses an ERP database that has been running in a production environment for many years as the data source. It contains 5574 tables with a total of 94192 fields, an average of 68,000 records per table, and a total size of approximately 3GB. During the data cleaning process, the relationships between tables are mainly established through primary keys or business codes; therefore, non-relational fields such as time, file, and long text were removed. Ultimately, 6081 fields suitable for correlation were selected, involving 713 valid data tables, all of which maintained an average record count of 68,000.

[0089] This invention, through the construction of static similarity analysis, time segmentation, dynamic similarity analysis, and co-occurrence matrix, comprehensively determines the correlation of data tables, wherein:

[0090] 1) Static similarity matrix: Based on the data content of fields in the database table, the field data features are compressed using MinHash signature technology, and similar fields are efficiently filtered using Local Sensitive Hash (LSH) technology to generate a static similarity matrix that reflects the similarity of data value distribution;

[0091] 2) Dynamic similarity matrix: By collecting database operation logs, time segments are dynamically divided based on the timestamps of the database operation logs, and the operation linkage relationship between different fields is calculated in each segment. Based on this, a dynamic similarity matrix that reflects the dynamic interaction characteristics between fields is incrementally generated.

[0092] 3) Co-occurrence matrix: Calculate the frequency of simultaneous occurrence of different data tables within the same time shard and generate a co-occurrence matrix to quantify the business collaboration patterns between tables.

[0093] Ultimately, by integrating the three dimensions of indicators—static similarity matrix, dynamic similarity matrix, and co-occurrence matrix—and calculating the comprehensive correlation at the field level through weighted averages, we can accurately identify data tables with genuine business relationships and effectively eliminate the interference of data noise and accidental similarities.

[0094] See Figure 1 and Figure 2 . Figure 1This is a schematic diagram of an embodiment of the associative table discovery system based on dynamic data according to the present invention. Figure 2 This is a schematic diagram of an embodiment of the method for discovering associative tables based on dynamic data according to the present invention.

[0095] The following are the specific operation steps of the method according to the embodiments of the present invention. It should be noted that in this embodiment, steps S1 and S2 are not sequential, and steps S3 and S4 are not sequential.

[0096] S1: Construct a static similarity matrix based on the content similarity of each field data set in the database.

[0097] This step aims to analyze the content similarity of data sets across various fields in the database, constructing a static similarity matrix to reveal implicit relationships between fields based on data value distribution patterns, beyond the table structure, thus providing a basis for subsequent comprehensive judgment. This matrix represents the overall similarity relationships between fields in all tables of the database.

[0098] To efficiently determine the similarity between fields while avoiding excessive performance pressure on the database and application server, this embodiment reads and analyzes data on a field-by-field basis.

[0099] The specific method for this step is as follows:

[0100] S11: Retrieve the field metadata of the database table, filter out the fields suitable for establishing relationships, and construct an array of field metadata.

[0101] The specific method is as follows:

[0102] First, the structure information, i.e., field metadata, of all tables in the database is obtained by querying the database system tables. This includes metadata such as table name, field name, field length, field type, and whether it is a primary key. According to common database normalization rules, only certain field types are suitable for establishing relationships. These include Int, Bigint, Char, Varchar, and Nvarchar fields with a length exceeding 10 characters. Date, long text, binary, and short string fields are not suitable and are therefore excluded. The exclusion method is as follows: for each field, determine whether it should participate in the relationship based on its type, retain the metadata of the fields that meet the conditions, and simplify their organization into an array structure containing only the field names.

[0103] In this embodiment, assuming the database has tables A, B, and C, this step can obtain the field metadata of each table. All field metadata of A, B, and C are represented as corresponding field metadata arrays, specifically: A = [a1:Varchar, a2:Varchar, a3:Varchar, a4:Varchar, a5:Varchar, a6:Varchar, a7:Varchar, a8:Varchar, a9: Int, a10:Date]; B = [b1:Varchar, b2:Varchar, b3:Date]; C = [c1:Varchar]. When generating the arrays, the array name of each array is the table name of A, B, and C, and each piece of data in each array is concatenated using "field name:field type".

[0104] Then, the field removal operation is performed, and the metadata arrays corresponding to A, B, and C after the operation become: A = [a1, a2, a3, a4, a5, a6, a7, a8, a9]; B = [b1, b2]; C = [c1].

[0105] In this step, the field type information is removed from each item in the generated metadata array, and only the field name is retained. In this way, subsequent association operations only depend on the field name and do not need to use the field type information, which can effectively reduce data redundancy.

[0106] S12: Create a hash value signature table, and concatenate the table name and field name into a long string and store it in the information field of the hash value signature table.

[0107] The hash value signature table structure created in this embodiment includes: a unique identifier primary key field, an information field for storing query information, and a hash value field for storing hash values.

[0108] This step, based on the metadata array generated in step S11, concatenates the metadata array name and each data item in the array into a long string, which is then stored in the information field of the hash value signature table. This long string is a concatenation of all remaining field names from the field metadata, specifically in the form of fields="table name, field name 1", fields="table name, field name 2", and so on. The fields are then stored in the hash value signature table, and a unique ID is generated for this data, stored in the unique identifier primary key field. The hash value field is set to empty, to be used for storing the hash value of each calculated field data later. All field metadata used to establish relationships is traversed until all field names from the metadata are concatenated into fields and stored in the hash value signature table.

[0109] For example, in this embodiment, for tables A, B, and C in the database mentioned in step S11, the generated metadata arrays are: A = [a1, a2, a3, a4, a5, a6, a7, a8, a9]; B = [b1, b2]; C = [c1]. The following operations are performed:

[0110] 1) Concatenate the metadata array of table A into 9 long strings, namely fields=A,a1, fields=A,a2, fields=A,a3, ..., fields=A,a9; concatenate the metadata array of table B into 2 long strings fields=B,b1 and fields=B,b2; concatenate the metadata array of table C into 1 long string fields=C,c1, and store them in the information fields of the hash value signature table to form 12 new data rows.

[0111] 2) Generate unique ID identifiers 1, 2, 3... for each of the 12 data rows, forming the unique identifier primary key field in the hash value signature table;

[0112] 3) Set the hash value field of each row of data in the hash value signature table to empty.

[0113] After all steps are completed, the hash value signature table information is as follows: Table 1-1

[0114] Table 1-1 Hash value signature table after storing field metadata

[0115] Unique identification primary key field Information field Hash value field 1 A, a1 Empty 2 A, a2 Empty …… …… …… 9 A, a9 Empty 10 B, b1 Empty 11 B, b2 Empty 12 C, c1 Empty …… …… ……

[0116] S13: Obtain all data values ​​corresponding to each field to form a data set, use the MinHash algorithm to generate a K-dimensional MinHash signature vector for each data set, and fill it back into the hash value signature table.

[0117] The purpose of this step is to generate an efficient "fingerprint" (signature) for each field's data, which will be used for subsequent rapid similarity calculations. Specifically, it includes the following steps:

[0118] 1) Obtain the field data set: For each field selected in step S111, query the database to obtain the values ​​of all data rows under that field, forming the data set for that field. For example, the data set for field a1 can be represented as Data(a1).

[0119] 2) Calculate the MinHash signature: The MinHash algorithm is used to process the data set for each field. The core of this algorithm is to use a set of K hash functions (e.g., K=128) to operate on the data set, and take the minimum value under each hash function to form a K-dimensional MinHash signature vector.

[0120] For example, [min(h1(Data(a1))),min(h2(Data(a1))),…, min(h K (Data(a1)))]. This signature is a compressed representation of the original dataset, and the similarity between the two signature vectors can approximate the Jaccard similarity of their corresponding datasets.

[0121] 3) Backfill signature: The calculated MinHash signature vector for each field is backfilled into the corresponding "hash value field" in the hash value signature table.

[0122] S14: Using the Locality Sensitive Hash (LSH) method, the MinHash signature vector is mapped to storage buckets of multiple hash tables, and fields mapped to the same bucket are identified as candidate similar field pairs.

[0123] This step aims to address the significant computational overhead of directly calculating the pairwise similarity of all fields. It utilizes Locality Sensitive Hashing (LSH) to quickly filter out high-probability similar field pairs, calculating the similarity only for these candidate pairs, thereby greatly improving the efficiency of generating a static similarity matrix. The specific steps are illustrated below with examples:

[0124] For the previous example of database tables A, B, and C, assume that the hash signature table already contains the MinHash signatures of fields a2, b2, and c1 (signature dimension K=128). Our goal is to determine the similarity among a2, b2, and c1.

[0125] S141: Initialize the LSH hash table.

[0126] Using the Locality Sensitive Hashing (LSH) method, construct S groups of hash functions. Select L hash functions h1, h2, ..., h... L These are combined to form a hash function group, represented as G(x) = (h1(x), h2(x), ..., h L (x)), this hash function set can be represented as a set containing L hash functions. Repeat this construction process S times to obtain S independent hash function sets.

[0127] Next, an empty hash table is initialized for each of the aforementioned hash function groups. At this point, we have obtained S empty hash tables, each corresponding to a specific hash function group.

[0128] For example, for simplicity, let S=2. That is, define two hash function groups G1 and G2, each consisting of L hash functions. The specific value of L is a conventional choice in this field, set according to the number of fields and the amount of data in the database to be processed; for example, L can be 4, 5, or 6. Then, initialize two empty hash tables:

[0129] Table 1: Corresponding hash function group G1;

[0130] Table 2: Corresponding hash function group G2.

[0131] S142: Fill the LSH hash table.

[0132] Iterate through all the MinHash signature information stored in the hash value signature table. For each signature vector, calculate using the S hash function groups defined in step S131, and map it to the corresponding storage bucket in the S hash tables. After this process is completed, the constructed LSH hash table is used to store the static similarity information between fields.

[0133] For example, perform the following operation on the MinHash signature of fields a2, b2, and c1:

[0134] 1) The signature of field a2 is calculated by G1 and falls into bucket 5 of Table1, and is calculated by G2 and falls into bucket 9 of Table2.

[0135] 2) The signature of field b2, calculated by G1, also falls into bucket 5 of Table1 (in the same bucket as a2), and calculated by G2, it falls into bucket 15 of Table2.

[0136] 3) The signature of field c1 is calculated by G1 and falls into bucket 22 of Table1, and is calculated by G2 and falls into bucket 9 of Table2 (in the same bucket as a2).

[0137] The completed hash table information is as follows:

[0138] Table 1: Bucket 5 → [a2, b2]; Bucket 22 → [c1]

[0139] Table 2: Bucket 9 → [a2, c1]; Bucket 15 → [b2]

[0140] S15: Calculate the similarity of the similar field pairs and generate a static similarity matrix.

[0141] S151: Calculate the similarity of the similar field pairs.

[0142] The LSH hash table information obtained through the above steps is processed to identify candidate similar field pairs hashed to the same storage bucket. For each candidate pair, the similarity of its complete MinHash signature is calculated. This similarity is a MinHash estimate of the Jaccard similarity, obtained by calculating the proportion of the number of dimensions with the same values ​​in the two signature vectors to the total dimension K (i.e., the MinHash signature dimension, as mentioned above, K=128). If this similarity value exceeds a preset threshold, the two fields are considered similar.

[0143] For example.

[0144] Candidate pairs were found in the hash table: (a2, b2), which co-occurs in bucket 5 of Table1; and (a2, c1), which co-occurs in bucket 9 of Table2. The total dimension K is 128.

[0145] Calculate the similarity of candidate pair (a2, b2): Compare the 128-dimensional MinHash signatures of a2 and b2. Assuming that statistical analysis shows 100 dimensions are identical, their static similarity Sim(a2, b2) = 100 / 128 ≈ 0.781. It should be noted that in the field of set similarity, based on experience, a similarity of 40% is generally considered sufficient for similarity. This embodiment assumes a preset threshold of 0.4; since 0.781 > 0.4, a2 and b2 are determined to be similar.

[0146] Calculate the similarity of candidate pair (a2, c1): Compare the 128-dimensional MinHash signatures of a2 and c1. Assuming that 40 dimensions have identical values, their similarity Sim(a2, c1) = 40 / 128 = 0.3125. Since 0.3125 < 0.4, a2 and c1 are determined to be dissimilar.

[0147] S152: Generate a static similarity matrix based on candidate pair similarity.

[0148] Create an N×N symmetric matrix (N is the total number of fields involved in the calculation) to represent the static similarity relationships between all pairs of fields. For field pairs determined to be similar in step S133, fill the corresponding positions in the matrix with their calculated similarity values; for candidate pairs that do not reach the similarity threshold and field pairs that are not included in any hash bucket, set their matrix element values ​​to 0; the elements on the main diagonal of the matrix (i.e., the similarity between a field and itself) are defined as 1.

[0149] It should be noted that all similarities calculated for candidate field pairs will be filled into the static similarity matrix.

[0150] For example: Based on the calculation results of step S133 (Sim(a2, b2) ≈ 0.781, Sim(a2, c1) = 0.3125, and the threshold is 0.4), the final 3×3 static similarity matrix is ​​as follows (the matrix rows and columns are a2, b2, and c1 respectively):

[0151]

[0152] This matrix accurately reveals the high similarity between fields a2 and b2.

[0153] S2: Obtain the database operation log and store it in the message queue. Based on the timestamp sequence of the database operation log, dynamically divide the time into time slices by calculating the timestamp interval, and divide the database operation log records into the corresponding time slices.

[0154] Analyze the database operation logs, construct time shards, and divide the database operation log data into the corresponding time shards.

[0155] During operation, databases continuously record various data operation information; these records are collectively referred to as database operation logs. In business systems, certain tables often undergo data changes within similar time periods due to business relationships (for example, sales order information tables, order detail tables, and outbound information tables are often updated during the processing of the same order). Therefore, a continuous time interval is called a "time slice," and a group of tables that change within this time slice may have strong business relationships.

[0156] The specific steps are as follows:

[0157] S21: Obtain the database operation log and store the parsed database operation log records into the message queue.

[0158] Mainstream databases (such as MySQL, Oracle, and SQL Server) all provide system database operation log tables to record data operations and transaction information such as insert, modify, and delete operations, forming the basic data source. To efficiently capture data changes in real time, this embodiment adopts a CDC (Change Data Capture)-based technical architecture. It uses the Debezium platform (a distributed platform based on change data capture open source by Red Hat) or the Apache StreamSets open source platform to monitor database operation logs in real time and extract table change operations, including INSERT, UPDATE, and DELETE.

[0159] For subsequent analysis, the captured database operation log data is parsed into a specific format, such as JSON, and stored in a message queue. In this embodiment, the captured database operation log data is parsed and stored in a high-throughput and persistent Kafka message queue, facilitating data consumption and analysis by the application at different time intervals.

[0160] In Kafka, database operation logs are displayed line by line. Each line records a data change to the corresponding table, including the table name, field name, field value, operation type, and time.

[0161] Operation types include insert, modify, and delete;

[0162] For modifications, it is necessary to capture both the values ​​before and after the modification, requiring the recording of two rows of data.

[0163] For example, in this embodiment, four database operation log entries were detected for tables A and B in the database. After parsing, the following four database operation log entries were obtained:

[0164] Insert log: {type:insert, table:'A', data:{a1:1, a2: 'common'}, time:'2025-08-21 12:12: 13.100'};

[0165] Insert log: {type:insert, table:'B', data:{b1:1, b2: 'common'}, time:'2025-08-21 12:12:14.200'};

[0166] Modification log: {type:update, table:'A', beforeData:{a1:1, a2: 'common'},afterData:{a1:1, a2: 'common1'}, time:'2025-08-21 12:12:14.550'};

[0167] Delete log: {type:delete, table:'A', data:{a1:1, a2: 'common1'}, time:'2025-08-21 12:12:15.050'}.

[0168] The parsed database operation log records are then stored in a Kafka message queue. It should be noted that, since the update log allows inserting two records into the Kafka message queue—one record before the update and one record after the update—there will be a total of five database operation log records in the Kafka message queue.

[0169] S22: Based on the database operation log records in the message queue, construct time shards and divide the database operation log records into the corresponding time shards.

[0170] Choosing the appropriate time slice is key to ensuring the accuracy of dynamic similarity, as the data within the time slice reflects the degree of correlation between modules. For example, after a user places an order, the order details, inventory, and shipment will all change because they are all interconnected.

[0171] First, we need to find the optimal split point, which is the maximum time interval between two database operation log records in the Kafka message queue after the last read. This optimal time shard split point is then used to retrieve data within that time shard range. The specific steps are as follows:

[0172] S221: Obtain the database operation log timestamp sequence from the message queue after the last timestamp of the last processing, and filter out the valid timestamp sequence from the timestamp sequence according to the preset minimum time interval and maximum time interval.

[0173] Let L be the last timestamp of the previous processing step, and let T = [t1, t2, …, t n [L] represents the timestamp sequence of database operation logs generated after L. To filter out suitable data ranges for sharding, a minimum time interval I is preset. min With the maximum time interval I max That is, time window [I] min I max ], where I min Used to filter out invalid short intervals caused by system noise or extremely fast operation, I max This is used to limit the maximum time span of the fragments to avoid fragments becoming too large. Based on this, valid timestamp sequences T* are selected from sequence T, that is, sequences after L and within the range of I are filtered out. min I max The mathematical expression for the data information between them is:

[0174] T* = [t i | t i > L ^ t i – L ≥ I min ^ ti – L ≤ I max ]

[0175] Suppose we set the time window to [I min =3 seconds, I max =60 seconds]. We filter out all t. i - L < 3 seconds and t i > 60 seconds of recording.

[0176] S222: Calculate the interval between adjacent timestamps in the effective timestamp sequence, determine the maximum time interval, and divide the database operation log records into corresponding time slices using the adjacent timestamps corresponding to the maximum time interval as the dividing points.

[0177] Calculate the time intervals between all adjacent timestamps in the sequence to form a time interval set. The maximum interval ΔTmax identifies the longest idle pause between adjacent timestamps in the valid sequence T*. The location of this maximum interval is the optimal split point for dividing the continuous database operation log stream.

[0178] The specific division method can be calculated using the following formula:

[0179]

[0180] Then, the maximum value is found from the set of time intervals ΔT, and denoted as the maximum time interval ΔT. max :

[0181] ΔT max = max(ΔT)

[0182] The ΔT max This is the identified optimal time segmentation point.

[0183] Let the maximum interval ΔT be generated. max Two adjacent timestamps are t k and t k+1 (i.e., t) k+1 - t k = ΔT max ),but:

[0184] 1) Starting from the last read expiration timestamp L, up to timestamp t k All database operation log records up to this point are divided into a complete time slice, which can be denoted as Slice_1.

[0185] 2) Timestamp t k+1 The subsequent database operation log records will belong to the next time slice, which can be denoted as Slice_2.

[0186] This method segments the database operation log stream at the point where the natural pause is most significant, thereby ensuring that operations that are closely related in time and belong to the same business event cluster are retained in the same shard, achieving adaptive partitioning of business events.

[0187] It should be noted that the maximum interval ΔT in this embodiment is... max This result is dynamically calculated based on currently available data and reflects the natural pauses within its own business scenario. This maximum interval serves as the threshold for determining time-slicing. Accordingly, the algorithm automatically aggregates consecutive operations with time intervals less than this threshold, treating them as a continuous cluster of business activities.

[0188] For example, in this embodiment, let the last read's expiration timestamp L be '2025-08-21 12:12:10.000'. The subsequent 5 messages in the Kafka message queue (involving tables A and B, including 2 INSERTs, 2 UPDATEs, and 1 DELETE) have the following original timestamps:

[0189] t1 ='2025-08-21 12:12:13.100'

[0190] t2 ='2025-08-21 12:12:14.200'

[0191] t3 ='2025-08-21 12:12:14.550'

[0192] t4 ='2025-08-21 12:12:14.900'

[0193] t5 ='2025-08-21 12:12:15.050'

[0194] First, perform time window filtering. Calculate the difference between each timestamp and L:

[0195] t1 - L = 3.1 seconds (≥ I) min =3 seconds, ≤ I max =60 seconds, valid)

[0196] t2 - L = 4.2 seconds (valid)

[0197] t3 - L = 4.55 seconds (valid)

[0198] t4 - L = 4.9 seconds (valid)

[0199] t5 - L = 5.05 seconds (valid)

[0200] Therefore, the effective timestamp sequence T* = [t1, t2, t3, t4, t5].

[0201] Next, the adjacent intervals are calculated and the maximum interval is determined. The interval between adjacent timestamps in T* is calculated as follows:

[0202] Δt1 = t2 - t1 = 1.1 seconds

[0203] Δt2 = t3 - t2 = 0.35 seconds

[0204] Δt3 = t4 - t3 = 0.35 seconds

[0205] Δt4 = t5 - t4 = 0.15 seconds

[0206] The interval set ΔT = [1.1, 0.35, 0.35, 0.15]. The maximum time interval ΔT. max = max(ΔT) = 1.1 seconds.

[0207] Finally, the time is divided into time slices. The maximum interval ΔT calculated in this study is... max The maximum interval is 1.1 seconds. Iterating through all adjacent intervals [1.1 seconds, 0.35 seconds, 0.35 seconds, 0.15 seconds], each interval value is less than or equal to this maximum interval value. Therefore, the algorithm determines that these operations are temporally consecutive and belong to the same business activity cluster, and assigns them all to the same time slice.

[0208] S3: Based on the database operation log records within the time slice, calculate the dynamic similarity between fields, incrementally update the dynamic similarity matrix, and generate the final dynamic similarity matrix.

[0209] This step aims to construct a unified data set that comprehensively reflects all data changes within a single time segment by parsing the business semantics of different data operation types (insert, delete, update) based on the database operation log records within that time segment. Subsequently, within each time segment, the field-level similarity between different tables is calculated, and the dynamic similarity matrix is ​​incrementally updated to generate the final dynamic similarity matrix.

[0210] The specific steps are as follows:

[0211] S31: Based on each time slice, create an initially empty set of fields and a set of data values.

[0212] Fields collection: Used to store the names of all the business fields that are operated on within this shard.

[0213] Data(Fields): This is a mapping structure used to store a collection of all the specific data values ​​that have appeared for each field.

[0214] Under the initial conditions, the set of all fields, Fields, is empty, and there is no mapping relationship between any field and data value in Data(Fields), that is, it is empty.

[0215] S32: Iterate through the database operation log records within each time slice, extract the relevant fields and data values ​​based on the operation type, and update the field set and data value set for each slice:

[0216] Iterate through each database operation log record belonging to the current time shard, and take the corresponding strategy according to its operation type (INSERT, UPDATE, DELETE) to update the initialized field set Fields and data value set Data(Fields).

[0217] 1) For INSERT (new) operations

[0218] When an INSERT operation is recorded, the field values ​​in the database operation log are the newly added data information. Extract the fields and their values ​​of the newly added data recorded in the database operation log. Add the relevant field names to the Fields collection. Then, add the new data values ​​corresponding to each field to the corresponding field's data value collection in the Data(Fields) mapping.

[0219] 2) For UPDATE (modification) operations

[0220] When a record is an UPDATE operation, the database operation log field values ​​include both the data before and after the modification. Regarding relationships, if the previous data is relational, then the edited data can also be relational. Therefore, we add both the data before and after the edit to the field information and the data set. That is:

[0221] Extract the data fields and their values ​​from the database operation log, showing the data before (old_value) and after (new_value) modification. Add the relevant field names to the Fields collection. Then, add the corresponding before-and-after values ​​for each field to the data value collection of the corresponding field in the Data(Fields) mapping. This operation ensures that the correlation between the data state before and after the change is captured.

[0222] 3) For the DELETE operation

[0223] When a DELETE operation is recorded, the field values ​​in the database operation log contain data information from before the deletion. This data already shared similarities with certain columns before being deleted. Therefore, for deleted data, the actions performed are the same as for insert operations.

[0224] Extract the fields and their values ​​(values ​​before deletion) of the deleted data recorded in the database operation log. Add the field names to the Fields collection. Then, add the corresponding pre-deletion data values ​​of each field to the data value collection of the corresponding field in the Data(Fields) mapping.

[0225] S33: Based on the updated set of data values, calculate the similarity between any two fields within each time slice.

[0226] Since the number of fields in a time slice is limited, a traversal method is used to determine their similarity, thereby obtaining the similarity between fields within the slice.

[0227] After processing all database operation log records within the shard, based on the constructed data value set Data(fields), calculate the values ​​of any two fields (C) within that shard. i C j The similarity measure between ) i The calculation formula can use the set-based Jaccard similarity coefficient or other applicable similarity measures:

[0228] sim ij = | Data(C i ) ∩ Data(C j ) | / | Data(C i ) ∪ Data(C j ) |

[0229] Among them, Data(C i ) and Data(C j ) represent field C respectively i and C j The set of data values ​​constructed in this time slice.

[0230] For example.

[0231] Suppose there are two related tables, A and B, in a database. Table A contains fields a1 and a2, and table B contains fields b1 and b2. The system typically first inserts a new record into table A, and then inserts a related record into table B.

[0232] Assume that time slice 1 contains database operation logs for tables A and B:

[0233] 1) Insert a record into table A, with the value of field a2 being 'common';

[0234] 2) Insert a record into table B with the value 'common' for field b2.

[0235] After processing in steps S31-S32, we obtain:

[0236] Data(A.a2) = { 'common'}

[0237] Data(B.b2) = { 'common'}

[0238] Assuming a2 is the second field within a time slice and b2 is the fourth field within a time slice, the calculation yields: sim 13 (A.a2,B.b2) =|{ 'common'}∩{ 'common'}| / |{ 'common'}∪ { 'common'}| = 1.0

[0239] S34: Based on the field similarity within each time slice, incrementally update the dynamic similarity matrix to generate the final dynamic similarity matrix.

[0240] Data within a single time slice can only represent a local area, necessitating the fusion of similarities across multiple time slices. This step aims to address this locality problem by using an incremental update algorithm to integrate the field similarities calculated within the current slice with historical aggregation results, gradually forming a global and stable inter-column similarity measure.

[0241] Specifically, the dynamic similarity matrix is ​​incrementally updated based on the field similarity calculated within the current time slice. By continuously executing this step, the calculation results from multiple time slices can be fused, making the similarity metric of the dynamic similarity matrix tend to stabilize.

[0242] This embodiment uses a dynamic similarity matrix to continuously track and update the association strength between fields. Each element of this matrix is ​​a quadruple (C... i C j , sim ij , n ij ), used to record pairwise similarity between fields, where:

[0243] C i C j : Represents a pair of field names;

[0244] sim ij : Represents field C i and C jThe average similarity value between them;

[0245] n ij : Represents field C i and C j The cumulative number of time segments that co-occur and have their similarity calculated is initially 0, and is incremented by one for each co-occurrence.

[0246] For time slice L, similarity was calculated for each pair of fields (C). i C j The calculation results and the dynamic similarity matrix are incrementally updated according to the following steps:

[0247] S341: Initialize the dynamic similarity matrix.

[0248] The system initializes a dynamic similarity matrix to store and maintain the aggregated similarity information for all field pairs. Initially, the dynamic similarity matrix is ​​empty.

[0249] S342: Iterate through the field similarity pairs for each time slice and obtain the field similarity results for each time slice.

[0250] Obtain the field similarity results for each time slice calculated in step S33. These results include all field pairs within each time slice and their corresponding similarity values ​​(sim). ij (C i C j Iterate through each field pair in the result.

[0251] S343: Query the records of field pairs in the dynamic similarity matrix, determine and perform record updates.

[0252] For the currently being processed field pair (C) i C j ), query in the dynamic similarity matrix whether a record S corresponding to the field pair already exists. ij Based on the query results, perform one of the following two operations:

[0253] 1) If there are no field pairs (C) in the dynamic similarity matrix i C j For records containing the field ), a new record is created for that field pair in the dynamic similarity matrix and initialized to:

[0254] S ij = (C i C j, sim L , 1)

[0255] Among them, field C i With Cj Similarity sim L Initialize the similarity sim to the current time slice ij The number of times they appear together is n ij Initialize to 1.

[0256] 2) If the field pair (C) already exists in the dynamic similarity matrix i C j Record S ij If the similarity between the same columns is not found, then the similarity can be superimposed. This can be done using the following formula:

[0257] Based on the new similarity sim of the current fragment L L Incremental updates to existing records:

[0258] sim L = (sim ij * n ij + sim L ) / (n ij + 1)

[0259] n ij = n ij +1

[0260] Then, using the new value (sim) L , n ij Update the records for this field pair in the dynamic similarity matrix.

[0261] For example.

[0262] 1) Create a new record.

[0263] Assuming that after processing time partition 1, the similarity sim of the field pair (A.a1, B.b1) is obtained. 分片1 = 0.6. The system did not find a record for this field pair in the dynamic similarity matrix, therefore a new record is created for the field pair (A.a1, B.b1): (A.a1, B.b1, 0.6, 1);

[0264] 2) Update existing records (simple scenario).

[0265] Assuming that in the subsequent time slice 2, the similarity sim of the field pair (A.a1, B.b1) is calculated again. 分片2 If the record already exists, an incremental update will be performed:

[0266] sim L = (0.6 * 1 + 0.6) / (1 + 1) = 0.6

[0267] n ij = 1 + 1 = 2

[0268] The updated record is: (A.a1, B.b1, 0.6, 2)

[0269] 3) Update existing records (complex scenarios).

[0270] Suppose that when processing time slice 2, the system also calculates the similarity of another field pair (A.a2, B.b2), and obtains the similarity of sim. 分片2 (A.a2, B.b2) = 0.5.

[0271] The system queries the dynamic similarity matrix for this field pair and finds that its historical record [A.a2, B.b2, 0.9, 80] already exists (this record was generated from 80 historical slices before processing time slice 2, indicating that its historical average similarity is 0.9). The system then substitutes the current slice similarity and the historical record into the formula for incremental updates:

[0272] sim L = (0.9 * 80 + 0.5) / (80 + 1) = 72.5 / 81 ≈ 0.895

[0273] n ij = 80 + 1 = 81

[0274] After the update, the record becomes: (A.a2, B.b2, 0.895, 81)

[0275] Assuming this field is in the 2nd row and 4th column of the dynamic similarity matrix, sim L = 0.895 should be filled into the corresponding position in the dynamic similarity matrix. Because the dynamic similarity matrix is ​​a conjugate matrix, it is also necessary to adjust the sim... L = 0.895 should be filled into the 4th row and 2nd column of the matrix.

[0276] S344: Once all field pairs within all time slices have been calculated, a dynamic similarity matrix is ​​generated.

[0277] After incremental aggregation of all time slices, a dynamic similarity matrix is ​​generated.

[0278] The method described in this embodiment first captures all historical and real-time database operation logs using technologies such as CDC and stores them in a message queue. Then, based on the timestamps of the database operation logs, time shards are dynamically divided, with each shard representing a cluster of related data changes occurring within a specific time period. Finally, based on these shards, a dynamic similarity matrix is ​​incrementally calculated and generated using the methods described in steps S31 to S33. This matrix effectively measures the dynamic correlation of field-level data change behaviors between different tables. Its value is continuously updated as the shards are processed, and through weighted averaging, the similarity between historical and current changes is gradually integrated to ultimately form a stable and reliable field similarity relationship.

[0279] To clearly illustrate the matrix structure and typical values ​​generated by this method, the following uses some key fields as examples to demonstrate the dynamic similarity matrix; other fields are omitted. The values ​​range from 0 to 1, with 1 indicating perfect similarity. An example of this matrix is ​​shown in Table 3-1 below:

[0280] Table 3-1 Dynamic Similarity Matrix

[0281]

[0282] Note: The boxed values ​​(0.6, 0.895) in the table represent typical similarity values ​​calculated in the example, corresponding to field pairs (A.a1, B.b1) and (A.a2, B.b2), respectively. This matrix is ​​symmetric; only a portion of the fields shown here are taken from the complete matrix containing more fields. Field pairs within the same table do not require similarity calculations, so their values ​​are all 1.

[0283] S345: At this point, the incremental aggregation operation for this shard L is complete. The system will wait for the data for the next time shard to arrive and repeat steps S31 to S34 to process the new shard.

[0284] S4: Based on the time slice, count the co-occurrence frequency of different data tables in the same time slice and generate a co-occurrence matrix.

[0285] This step aims to quantify the strength of the relationship between tables from a business collaboration perspective by statistically analyzing the frequency of their co-occurrence within the same time shard. This generates a co-occurrence matrix. This matrix captures the characteristics of multiple tables being collaboratively operated on during the same business event, providing crucial information for the final comprehensive correlation determination.

[0286] When data in a table changes, related tables are often displayed synchronously in the context of the database operation log. This embodiment uses a co-occurrence matrix to represent the number of times tables appear simultaneously within a single shard. If two tables frequently appear synchronously within a shard, their co-occurrence value is larger, and the corresponding probability of correlation is also greater.

[0287] Based on the time slices already divided in step S2, this step constructs a co-occurrence matrix by counting the co-occurrence frequency of each pair of data tables across all time slices.

[0288] The specific steps are as follows:

[0289] S41: Traverse each time shard, extract all data tables involved in the database operation log within that shard, and form the table set for that shard.

[0290] S42: For each time slice, traverse all possible data table pairs in its table set. If a table pair appears simultaneously in the slice, increment the count of the table pair in the co-occurrence matrix by 1.

[0291] Initialize a co-occurrence matrix G to record the co-occurrence frequencies between all pairs of data tables, with initial values ​​of 0 for each pair. For each time slice T... i (i=1, 2, 3, ...) Perform the following operations:

[0292] 1) Obtain the set of tables for this shard;

[0293] 2) Iterate through all possible data table pairs in the set. For example, for tables A and B in the database, the data table pair is (A, B).

[0294] 3) For each pair of data tables such as (A, B), if A and B are in time slice T i If they appear simultaneously, then their corresponding co-occurrence frequency G is... AB Add 1:

[0295]

[0296] Otherwise, its co-occurrence frequency remains unchanged:

[0297]

[0298] Co-occurrence frequency G AB Initially 0, it increments by 1 whenever two tables appear simultaneously within the same time slice. G AB The larger the value, the higher the frequency of their collaboration in the same business event, the greater the likelihood that they belong to the same business module, and the stronger their correlation.

[0299] S43: Generate a co-occurrence matrix.

[0300] After traversing all time slices, the final co-occurrence matrix is ​​obtained, which is the co-occurrence matrix G calculated above. This matrix is ​​a symmetric matrix, and its elements G... ijThis represents the total number of times that table i and table j appear together across all time slices.

[0301] For example: Continuing with the previous embodiment, assume that three time slices have been processed:

[0302] The set of tables for partition 1 is {A, C};

[0303] The set of tables for partition 2 is {A, B};

[0304] The set of tables for shard 3 is {A, B, C}.

[0305] The co-occurrence matrix is ​​constructed as follows:

[0306] Handling partition 1: Table pair (A, C) co-occurrence, G AC = 1

[0307] Handling partition 2: Table pair (A, B) co-occurrence, G AB = 1

[0308] Handling shard 3: Table pairs (A, B), (A, C), and (B, C) co-occur, update:

[0309] G AB = 1 + 1 = 2

[0310] G AC = 1 + 1 = 2

[0311] G BC = 0 + 1 = 1

[0312] The final co-occurrence matrix is ​​shown in Table 4-1 below:

[0313] Table 4-1 Co-occurrence Matrix

[0314] A B C A 0 2 2 B 2 0 1 C 2 1 0

[0315] This result indicates that Table A and Table B co-occurred twice, Table A and Table C co-occurred twice, and Table B and Table C co-occurred once. A higher co-occurrence frequency indicates a greater probability of business collaboration between the two tables, and a stronger correlation.

[0316] S5: Based on the static similarity matrix, dynamic similarity matrix, and co-occurrence matrix, calculate the comprehensive correlation degree of field pairs in different tables to obtain the correlation of different table pairs.

[0317] True business relationships are established through the fields between tables; that is, the relationship between tables is essentially reflected in the mapping relationship between their contained fields. This embodiment comprehensively judges the relationship between different table pairs by examining the relationship between their field pairs. A high relationship between field pairs can directly determine that there is a strong business relationship between their respective data tables, thereby accurately identifying the true relationship and effectively eliminating the interference of data noise and accidental similarities. This is also the innovation of this embodiment of the invention.

[0318] This step aims to overcome the inherent limitations of single-dimensional similarity determination. It innovatively integrates information from three dimensions: static similarity (representing the structural features of data content), dynamic similarity (representing the behavioral patterns of business operations), and business co-occurrence (representing the patterns of collaborative occurrence within time slices). A weighted fusion strategy is then used to calculate the comprehensive correlation of field pairs.

[0319] The specific method for this step is as follows:

[0320] S51: For the fields in the database table to be queried, extract candidate related fields from any other tables in the database from the static similarity matrix and the dynamic similarity matrix to form a comprehensive candidate set.

[0321] This step aims to find candidate related fields in other tables of the database based on two dimensions: the static similarity matrix obtained in step S1 and the dynamic similarity matrix obtained in step S3, for the fields in the database table to be queried. The union of the two is then used as the candidate set.

[0322] Taking database tables A and B as an example. For the field 'a' in database table A to be queried... i Search for candidate related fields b in database table B from both static and dynamic perspectives. j :

[0323] 1) From the static similarity matrix generated in step S1, find the match between a and a. i Static similarity s(a i , b j All fields b that are above the preset threshold θ_static j This constitutes the static candidate set Candidate_static.

[0324] 2) From the dynamic similarity matrix generated in step S3, find the match with a. i Dynamic similarity d(a) i , b j All fields b that are above the preset threshold θ_dynamic j This constitutes the dynamic candidate set Candidate_dynamic.

[0325] 3) Take the union of the two candidate sets to form the final comprehensive candidate set Candidate_final:

[0326] Candidate_final = Candidate_static ∪ Candidate_dynamic

[0327] This step ensures that no potentially related fields that show similarity on any dimension are overlooked.

[0328] S52: Based on the comprehensive candidate set, calculate the comprehensive correlation degree of different table pairs and field pairs to obtain the correlation of different table pairs.

[0329] In this embodiment, for each field b in the comprehensive candidate set Candidate_final j Calculate its relationship with the field to be queried, a, using the following steps. i Overall correlation:

[0330] S521: Obtain the co-occurrence frequency of different table pairs, as well as the static and dynamic similarity of the field pairs in these different table pairs. The steps are as follows:

[0331] 1) Obtain (a) from the static similarity matrix i , b j Static similarity s(a) between field pairs i , b j );

[0332] 2) Obtain (a) from the dynamic similarity matrix i , b j The dynamic similarity d(a) of the field pairs i , b j );

[0333] 3) Obtain the co-occurrence frequency G of Table A and Table B from the co-occurrence matrix. AB .

[0334] S522: Calculate the co-occurrence probability of different table pairs.

[0335] Calculated field a i and b j The co-occurrence probability σ of Table A and Table B across all time slices can be calculated using the following formula:

[0336] σ = G AB / N

[0337] Where N is the total number of time slices processed. σ represents the frequency with which the two tables interact in business operations.

[0338] S523: Calculate the overall correlation degree of field pairs in different tables based on co-occurrence probability.

[0339] In this embodiment, a weighted summation method is used to calculate the field pair (a i , b j The overall correlation y(a) i , b j ):

[0340] y(a i , b j ) = σ * s(a i , b j ) + (1 - σ) * d(a i , b j )

[0341] The formula uses the business co-occurrence probability σ as a weight to reconcile static and dynamic similarity. The larger σ is, the stronger the business synergy, and the more the formula relies on static similarity (representing long-term stable structural features); the smaller σ is, the weaker the business synergy, and the more the formula relies on dynamic similarity (representing short-term operational features), but the absolute value of the correlation will also decrease due to the weight (1-σ).

[0342] Co-occurrence probability is a crucial parameter for determining whether two tables are related. The co-occurrence probability σ represents the probability that any two tables will appear simultaneously across all time slices. For example, during the generation of a sales order table, there is no direct association with the meeting information table. However, it's possible that the data in the sales order table and the meeting information table might share similarities. Since they don't co-occur, the co-occurrence probability σ = 0, and the dynamic similarity d(a) is zero. i , b j The overall correlation degree y(a) of the field pair is also 0. i ,b j The value is also 0. Although they are similar, they are not related. This method can exclude tables that have no business relationship.

[0343] If any two tables A and B have a business relationship, when the number of time slices reaches a certain level, then σ≠0. The more time slices there are, the higher the dynamic similarity d(a) becomes. i , b j The closer it gets to the static similarity s(a) i , b j ), at this time the field pair (a i , b j The similarity between tables A and B can represent the relationship between them.

[0344] S524: Determine the correlation between field pairs of different table pairs to obtain the correlation between different table pairs.

[0345] In this embodiment, the correlation between any two table pairs can be determined using the following steps:

[0346] The overall correlation degree y(a) of the field pairs i , b j ) is compared with the preset correlation threshold θ_global. If y(a i , b j If ) ≥ θ_global, then the field a is determined. i and b j The fields must be related in terms of business function. Otherwise, the field pair is considered unrelated. Since there are field pairs with strong business function relationships in Table A and Table B, it can be determined that Table A and Table B have a strong business function relationship.

[0347] For example.

[0348] Assumptions: The field to be queried is A.a2; its static candidate set contains B.b2 (s(A.a2, B.b2) = 0.781); its dynamic candidate set also contains B.b2 (d(A.a2, B.b2) = 0.895); the total frequency G of table A and table B is... AB = 2, total number of time slices N = 3; correlation threshold θ_global = 0.4.

[0349] The overall correlation between B.b2 and A.a2 is calculated using the following steps:

[0350] 1) σ = G AB / N = 2 / 3 ≈ 0.667

[0351] 2) y(A.a2, B.b2) = 0.667 * 0.781 + (1 - 0.667) * 0.895 ≈ 0.521 +0.297 = 0.818

[0352] 3) Determination: 0.818 > 0.4, therefore, it is finally determined that fields A.a2 and B.b2 have a strong business relationship. Since there are pairs of fields with a strong business relationship in tables A and B, it can be determined that tables A and B have a strong business relationship.

[0353] It should be noted that when determining the relationship between different table pairs, the determination can be made automatically according to preset rules based on the overall relationship between their field pairs. Specific rules include, but are not limited to, the following:

[0354] 1) If the overall correlation of any pair of fields in a table exceeds a preset threshold, the table pair can be determined to have business correlation.

[0355] 2) The highest correlation value among all field pairs can be used as a measure of the correlation between table pairs;

[0356] 3) The higher the correlation between field pairs, the stronger the correlation between table pairs.

[0357] Furthermore, judgment rules can be further set based on the number of related field pairs and the strength of the association. For example:

[0358] Count the number M of all valid field pairs that are related between table A and table B.

[0359] If M ≥ 1 and there exists at least one valid pair of related fields, the overall correlation degree y(a) is... i , b j If the correlation exceeds a higher strong correlation threshold (which can be represented as θ_strong), then it is directly determined that table A and table B have a strong business correlation.

[0360] Otherwise, if the number of valid related field pairs M exceeds the preset related number threshold (which can be represented as θ_table), for example, if the related number threshold is 5 and M >= 5, then it is determined that table A and table B have a strong business relationship.

[0361] If none of the above conditions are met, then it is determined that Table A and Table B have no business relationship.

[0362] Example 2

[0363] This embodiment provides a dynamic data-based associative table discovery system to implement the steps described in the above method embodiments. The following is in conjunction with... Figures 1-2 The structure and functions of this system are described in detail.

[0364] This system is deployed in a distributed computing environment and mainly includes the following five core modules: a static similarity matrix construction module, a time sharding module, a dynamic similarity matrix generation module, a co-occurrence matrix generation module, and a comprehensive correlation calculation module. The modules interact and synchronize their states through message queues (such as Kafka) and data storage (such as Redis and relational databases).

[0365] 1. Static similarity matrix construction module

[0366] This module is responsible for extracting field information from database metadata and efficiently calculating the static similarity between fields using MinHash and LSH techniques. Specifically, it includes:

[0367] Metadata Acquisition Unit: Connects to database system tables, reads table structure information, filters non-related fields, and generates a field metadata array.

[0368] Hash signature table construction unit: The table name and field name are concatenated and stored in the hash signature table.

[0369] MinHash signature unit: Read all data values ​​of each field, calculate its MinHash signature vector and fill it back into the signature table.

[0370] LSH Mapping Unit: Maps the signature vector to multiple hash buckets to identify candidate similar field pairs.

[0371] Static similarity calculation unit: Calculates the Jaccard similarity of candidate pairs, constructs and outputs the static similarity matrix.

[0372] 2. Time Slicing Module

[0373] This module dynamically partitions time slices by monitoring database operation logs (implemented via Debezium or StreamSets). Specifically, it includes:

[0374] Database operation log acquisition and parsing unit: Captures database operation logs in real time, parses them into JSON format, and pushes them to a Kafka queue.

[0375] Timestamp filtering unit: Consumes database operation log records from Kafka and filters out valid timestamp sequences.

[0376] Sharding Unit: Calculate the maximum time interval and use it as the sharding boundary to divide the database operation log records into the corresponding time shards.

[0377] 3. Dynamic Similarity Matrix Generation Module

[0378] This module incrementally updates the dynamic similarity between fields based on database operation log records within time slices. Specifically, it includes:

[0379] Set initialization unit: Initializes an empty set of fields and a set of data values ​​for each time slice.

[0380] Database operation log traversal and update unit: Traverse the database operation log within the shard and update the field and data value set according to the operation type (INSERT / UPDATE / DELETE).

[0381] Intra-segment similarity calculation unit: Calculates the Jaccard similarity of field pairs within a segment.

[0382] Incremental update unit: Maintains a dynamic similarity matrix and updates the historical records incrementally based on the newly calculated similarity.

[0383] 4. Co-occurrence matrix generation module

[0384] This module calculates the co-occurrence frequency of different data tables within a time slice. Specifically, it includes:

[0385] Table set extraction unit: Extracts all table names involved in each time slice.

[0386] Co-occurrence frequency statistics unit: Traverse all table pairs, count the number of co-occurrences, and update the co-occurrence matrix.

[0387] Matrix output unit: Outputs the final co-occurrence matrix.

[0388] 5. Comprehensive Relevance Calculation Module

[0389] This module integrates information from three dimensions: static, dynamic, and co-occurrence, to calculate the overall correlation between field pairs. Specifically, it includes:

[0390] Candidate set generation unit: Extracts candidate association fields from static and dynamic matrices.

[0391] Association Calculation Unit: Calculates co-occurrence probability, weights and fuses static and dynamic similarities, and outputs comprehensive association degree.

[0392] The specific implementation methods of the above modules are the same as the steps of the aforementioned methods, and will not be repeated here.

[0393] This system is suitable for large enterprise database environments and can be deployed on Kubernetes clusters. It features high availability, elastic scaling, and high concurrency processing capabilities, and can effectively support the data table association and discovery needs in both real-time and offline scenarios.

[0394] Example 3

[0395] This embodiment proposes an electronic device, including:

[0396] Memory, used to store computer programs;

[0397] The processor is used to execute the program stored in the memory to implement the steps of the above embodiments of the method for discovering associative tables based on dynamic data.

[0398] For details on the specific implementation of each step and related explanations, please refer to the aforementioned implementation example of the method for discovering associative tables based on dynamic data, which will not be repeated here.

[0399] The memory of the electronic device mentioned in this embodiment may include random access memory (RAM) or non-volatile memory (NVM), such as at least one disk storage device.

[0400] The processors mentioned above can be general-purpose processors, including central processing units (CPUs), network processors (NPs), etc.; they can also be digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, or discrete hardware components.

[0401] Example 4

[0402] This invention also proposes a computer-readable storage medium storing a computer program. When executed by a processor, this computer program implements the steps of the above embodiments of the method for discovering associative tables based on dynamic data. For specific implementation details and explanations of each step, please refer to the foregoing embodiments of the method for discovering associative tables based on dynamic data; further elaboration will not be repeated here.

[0403] It should be noted that all embodiments in this specification are described in a related manner, and the same or similar parts between the embodiments can be referred to each other. Each embodiment focuses on describing the differences from other embodiments.

[0404] In particular, the embodiments of apparatus, electronic devices, and computer-readable storage media are basically similar to the method embodiments, so the description is relatively simple, and relevant details can be found in the description of the method embodiments.

[0405] The above description is merely an embodiment of the present invention and is not intended to limit the invention. Various modifications and variations can be made to the present invention by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principle of the present invention should be included within the scope of the claims of the present invention.

Claims

1. A method for discovering associable tables based on dynamic data, characterized in that The method comprises the following steps: 1) constructing a static similarity matrix based on the content similarity of each field data set in the database; 2) obtaining database operation logs and storing them in a message queue, and based on the timestamp sequence of the database operation logs, dynamically dividing time slices by calculating the timestamp interval, and dividing the database operation log records into corresponding time slices; 3) calculating the dynamic similarity between fields based on the database operation log records in the time slices, and generating a dynamic similarity matrix through incremental updating; 4) based on the time slices, counting the co-occurrence frequency of different data tables in the same time slice to generate a co-occurrence matrix; 5) based on the static similarity matrix, the dynamic similarity matrix and the co-occurrence matrix, calculating the comprehensive correlation degree of different table pairs of field pairs to obtain the correlation of different table pairs.

2. The method of claim 1, wherein, The method comprises the following steps: 1) obtaining the field metadata of the database table, screening out the fields suitable for establishing the association relationship, and constructing a field metadata array; 2) creating a hash value signature table, concatenating the table name and the field name into a long string and storing it in the information field of the hash value signature table; 3) obtaining all data values corresponding to each field to form a data set, generating a K-dimensional MinHash signature vector for each data set using the MinHash algorithm, and backfilling it into the hash value signature table; 4) using the local sensitive hashing (LSH) method, mapping the MinHash signature vector into the storage buckets of multiple hash tables, and identifying the fields mapped into the same bucket as candidate similar field pairs; 5) calculating the similarity of the similar field pairs to generate the static similarity matrix.

3. The method of claim 1, wherein, The method comprises the following steps: 1) obtaining the database operation logs, and storing the parsed database operation log records in the message queue; 2) obtaining the database operation log timestamp sequence in the message queue from the last timestamp of the last processing, and according to the preset minimum time interval and maximum time interval, screening out the valid timestamp sequence from the timestamp sequence; 3) calculating the interval of adjacent timestamps in the valid timestamp sequence, determining the maximum time interval, and taking the adjacent timestamp corresponding to the maximum time interval as the division point to divide the database operation log records into corresponding time slices.

4. The method of claim 1, wherein, The method comprises the following steps: 1) based on each time slice, creating an initially empty field set and data value set; 2) traversing the database operation log records in each time slice, and extracting the involved fields and data values according to the operation type, updating the field set and data value set of each slice; 3) based on the updated data value set, calculating the similarity between any two fields in each time slice; 4) based on the field similarity in each time slice, incrementally updating the dynamic similarity matrix to generate the final dynamic similarity matrix.

5. The method of claim 1, wherein, The method comprises the following steps: 1) traversing each time slice, extracting all data tables involved in the slice within the database operation log, forming the table set of the slice; 2) for each time slice, traversing all possible data table pairs in its table set, if a table pair appears simultaneously in the slice, then the count value of the table pair in the co-occurrence matrix is added by 1; 3) output the calculated co-occurrence matrix.

6. The method of claim 1, wherein, Based on the static similarity matrix, the dynamic similarity matrix and the co-occurrence matrix, the comprehensive correlation degree of different table pair field pairs is calculated to obtain the correlation of different table pairs, including the following steps: 1) for the fields in the database table to be queried, extract the candidate associated fields in other arbitrary tables in the database from the static similarity matrix and the dynamic similarity matrix to form a comprehensive candidate set; 2) based on the comprehensive candidate set, the comprehensive correlation degree of different table pair field pairs is calculated to obtain the correlation of different table pairs.

7. The method of claim 6, wherein, The calculation of the comprehensive correlation degree of different table pair field pairs includes the following steps: 1) obtaining the co-occurrence frequency of different table pairs and the static similarity and dynamic similarity of the field pairs of the different table pairs; 2) calculating the co-occurrence probability of different table pairs; 3) calculating the comprehensive correlation degree of different table pair field pairs according to the co-occurrence probability.

8. A dynamically data-based joinable table discovery system, comprising: It includes: A static similarity matrix construction module for constructing a static similarity matrix based on the content similarity of the data set of each field in the database; A time slice division module for obtaining database operation logs and storing them in a message queue, and dynamically dividing time slices based on the timestamps of the database operation logs, and dividing database operation log records into corresponding time slices; A dynamic similarity matrix generation module for calculating the dynamic similarity between fields based on database operation log records in a time slice, and generating a dynamic similarity matrix through incremental updating; A co-occurrence matrix generation module for generating a co-occurrence matrix based on time slices, and counting the co-occurrence frequency of different data tables in the same time slice; A comprehensive correlation degree calculation module for calculating the comprehensive correlation degree of different table pair field pairs based on the static similarity matrix, the dynamic similarity matrix and the co-occurrence matrix to obtain the correlation of different table pairs.

9. An electronic device comprising: a memory for storing a computer program; a processor for executing the program stored on the memory to implement the method steps of any one of claims 1-7.

10. A computer-readable storage medium, characterized in that, The computer readable storage medium stores a computer program, which is executed by the processor to implement the method steps of any one of claims 1-7.

Images