Differential positioning method, device and equipment of heterogeneous database table and medium

Abstract

This application relates to a method, apparatus, device, and storage medium for locating differences in heterogeneous database tables. The method includes: obtaining configuration information for a source table in a source database and a target table in a target database; extracting common columns from the source and target tables respectively based on the configuration information; normalizing the data in the common columns to obtain normalized common column data; performing a difference query independent of the primary key based on the normalized common column data to determine a first set of differing rows and a second set of differing rows, and labeling the first and second sets of differing rows with source identifiers; obtaining a third set of differing rows based on the common row set in the source and target tables excluding the first and second sets of differing rows; and generating the final difference row result based on the first set of differing rows, the second set of differing rows, and the third set of differing rows. This application can solve the problem in the prior art of accurately locating row-level differences in scenarios without a primary key.

Description

Technical Field

[0001] This application relates to the field of database data verification technology, and in particular to a method, apparatus, device and medium for locating differences in heterogeneous database tables. Background Technology

[0002] In heterogeneous database data consistency verification scenarios, source and target tables often lack primary key constraints or have non-unique primary keys due to differences in business system design, making it impossible to directly use primary key-based row matching methods for comparison.

[0003] Existing data comparison tools generally perform row matching based on primary keys. Tables with missing primary keys, non-unique primary keys, or uncertain primary keys cannot be directly compared or require manual specification of the comparison key. Some verification schemes use total statistics or full-table fingerprint-level comparison, which can only answer whether the data in two tables are consistent, but cannot locate specific discrepancies in rows. Furthermore, methods that generate row fingerprints by checking and adding columns are prone to collision risks and can easily lead to misjudgments when the column positions of field content are changed. Summary of the Invention

[0004] In view of the above, this application provides a method, apparatus, device and storage medium for differential location of heterogeneous database tables, the purpose of which is to solve the above-mentioned technical problems.

[0005] Firstly, this application provides a method for locating differences in heterogeneous database tables, the method comprising: Obtain the configuration information of the source table in the source database and the target table in the target database, and extract common columns from the source table and the target table respectively according to the configuration information; The data in the common column is normalized to obtain normalized common column data; Based on the normalized common column data, a bidirectional difference query that does not depend on the primary key is executed to determine the first set of difference rows that exists only in the source table but not in the target table, and the second set of difference rows that exists only in the target table but not in the source table, and the source identifier is marked for the first set of difference rows and the second set of difference rows; Based on the normalized common column data, row-level difference positioning is performed on the common row set in the source table and the target table, excluding the first difference row set and the second difference row set, to obtain the third difference row set. Based on the first set of difference rows, the second set of difference rows, and the third set of difference rows, a final difference row result is generated; wherein each difference row in the final difference row result carries a difference type label.

[0006] In some embodiments, the step of performing row-level difference location on the common row set in the source table and the target table, excluding the first and second difference row sets, based on the normalized common column data, to obtain a third difference row set includes: Determine whether a primary key has been configured based on the configuration information; If no real primary key is configured in the configuration information, then perform a no-primary-key difference location; The keyless difference location includes: For each row in the common row set, all values ​​in the normalized common column data are concatenated in a preset order, and a hash value is calculated for the concatenated string. The calculated hash value is used as the self-defined primary key for that row. Using the self-defined primary key as the grouping criterion, the occurrence frequency of each hash value in the source table and the target table is counted respectively, and a full outer join comparison is performed on the source table and the target table based on the self-defined primary key; The rows corresponding to hash values ​​that appear inconsistently in the source table and the target table during the full outer join comparison are determined as the third set of differing rows.

[0007] In some embodiments, the step of counting the occurrence counts of each hash value in the source table and the target table based on the self-defined primary key, and performing a full outer join comparison on the source table and the target table based on the self-defined primary key, includes: The rows with the same self-defined primary key in the source table and the target table are grouped and counted respectively to determine the source count and the target count; The source count and the target count are associated using a full outer join based on the user-defined primary key; If the source count is less than the target count, the row corresponding to the self-defined primary key is determined as a redundant difference in the target. If the source count is greater than the target count, the row corresponding to the self-defined primary key is identified as a redundant difference in the source. If the source count is equal to the target count, then the row-level content difference determination is performed on the equal count groups based on the self-defined primary key.

[0008] In some embodiments, after determining the source count and the destination count, the method further includes: Based on the specific values ​​of the source count and the target count corresponding to the self-defined primary key, the difference type is determined, including: When the source count and the target count are equal and both equal to 1, the two rows of data with the self-defined primary key in the source table and the target table are compared item by item to locate the specific field where the content has changed, and the row of data is marked as the content change difference. When the source count and the target count are equal and both are greater than 1, the values ​​of all rows with the self-defined primary key in the source table and the target table are extracted respectively to form the source row set and the target row set; Perform a symmetric difference operation on the source row set and the target row set to determine which specific row in the duplicate rows has been changed or is missing, and mark the determined row as the difference within the duplicate row.

[0009] In some embodiments, performing a symmetric difference operation on the source row set and the target row set to determine which specific row in the duplicate rows has been changed or is missing includes: Under the self-defined primary key, a first in-row sequence number is assigned to each row in the source row set, and a second in-row sequence number is assigned to each row in the target row set; The first comparison key is the combination of all values ​​in the source row set except for the self-defined primary key, and the second comparison key is the combination of all values ​​in the target row set except for the self-defined primary key. The first alignment key and the second alignment key are matched one by one. If there is a first alignment key that cannot be matched, the corresponding row is marked as the source end being redundant in the repeating group. If there is a second alignment key that cannot be matched, the corresponding row will be marked as the target end being redundant in the repeating group; If the first comparison key and the second comparison key can both be matched but the content is not exactly the same, then the two matched lines will be marked as content changes within the duplicate group.

[0010] In some embodiments, the method further includes: Determine whether a primary key has been configured based on the configuration information; If a real primary key is configured in the configuration information, then a primary key difference location is performed; The execution of primary key difference location includes: A row hash fingerprint cache table and a primary key fingerprint cache table are pre-built. The row hash fingerprint cache table is used to store the row hash value and the corresponding version timestamp of each row of data. The primary key fingerprint cache table is used to store the aggregate fingerprint and the corresponding version timestamp of each row of primary key value. When performing the primary key difference location, for data shards in the source table and the target table whose data change timestamps are greater than the version timestamps in the cache table, recalculate the row hash value or primary key aggregation fingerprint and compare them. Add the difference rows found in the comparison as new difference rows to the final difference row result and update the cache table synchronously.

[0011] In some embodiments, when performing the primary key difference location, for data shards in the source table and the target table whose data change timestamps are greater than the version timestamps in the cache table, recalculating the row hash value or primary key aggregate fingerprint and comparing them, the process includes: Compare the block version number or block combination hash value of the corresponding data item in the source table and the target table; If the block version number of the corresponding data item is the same and the block combination hash value is consistent, it is determined that the data item has not changed, and the recalculation of the data item is skipped directly. If the block version number of the corresponding data item is different or the block combination hash value is inconsistent, it is determined that the data item has undergone data change; Mark all data rows covered by the data item as invalid, and recalculate the row hash value or primary key aggregate fingerprint for the data rows within the scope of the data item; The recalculated fingerprint is compared line by line with the peer database.

[0012] Secondly, this application provides a differential location device for heterogeneous database tables, the differential location device for heterogeneous database tables comprising: The acquisition module is used to acquire configuration information of the source table in the source database and the target table in the target database, and extract common columns from the source table and the target table respectively according to the configuration information; The processing module is used to normalize the data in the common column to obtain normalized common column data; The first determining module is used to perform a bidirectional difference query that does not depend on the primary key based on the normalized common column data, to determine a first set of difference rows that exists only in the source table but not in the target table, and a second set of difference rows that exists only in the target table but not in the source table, and to mark the source identifier for the first set of difference rows and the second set of difference rows; The second determining module is used to perform row-level difference positioning on the common row set in the source table and the target table, excluding the first difference row set and the second difference row set, based on the normalized common column data, to obtain the third difference row set. The generation module is used to generate a final difference row result based on the first difference row set, the second difference row set, and the third difference row set; wherein each difference row in the final difference row result carries a difference type label.

[0013] Thirdly, this application provides an electronic device, including a processor, a communication interface, a memory, and a communication bus, wherein the processor, the communication interface, and the memory communicate with each other through the communication bus; Memory, used to store computer programs; When a processor executes a program stored in memory, it implements the steps of the differential location method for heterogeneous database tables as described in any embodiment of the first aspect.

[0014] Fourthly, a computer-readable storage medium is provided having a computer program stored thereon, which, when executed by a processor, implements the steps of the differential location method for heterogeneous database tables as described in any embodiment of the first aspect.

[0015] The technical solutions provided in this application have the following advantages compared with the prior art: By sequentially performing common column extraction, normalization processing, bidirectional difference queries independent of primary keys, and row-level difference location, and by including precise difference type annotations for each differing row in the final result, this technology solves the problem of inability to perform precise row-level matching due to missing or non-unique primary keys in the table structure. It also eliminates false positives caused by cross-database field type differences and semantic inconsistencies in null values. The comparison results are upgraded from simply indicating whether two tables are consistent to precisely locating each differing row and its specific direction of difference, resolving the issues of not being able to locate specific differing rows and the potential for missed or false positives in scenarios with duplicate primary keys and null values. Attached Figure Description

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

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

[0018] Figure 1 This is a flowchart illustrating a preferred embodiment of the differential location method for heterogeneous database tables in this application; Figure 2 This is an exemplary flowchart illustrating the execution of keyless difference location in some embodiments of this application; Figure 3 This is an exemplary flowchart illustrating the execution of primary key difference location according to some embodiments of this application; Figure 4 This is a schematic diagram of a preferred embodiment of the differential positioning device for heterogeneous database tables in this application; Figure 5 This is a schematic diagram of a preferred embodiment of the electronic device of this application; The realization of the purpose, functional features and advantages of this application will be further explained in conjunction with the embodiments and with reference to the accompanying drawings. Detailed Implementation

[0019] To make the objectives, technical solutions, and advantages of this application clearer, the following detailed description is provided in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the scope of this application. All other embodiments obtained by those skilled in the art based on the embodiments in this application without inventive effort are within the scope of protection of this application.

[0020] It should be noted that the use of terms such as "first" and "second" in this application is for descriptive purposes only and should not be construed as indicating or implying their relative importance or implicitly specifying the number of technical features indicated. Therefore, a feature defined as "first" or "second" may explicitly or implicitly include at least one of those features. Furthermore, the technical solutions of the various embodiments can be combined with each other, but this must be based on the ability of those skilled in the art to implement them. If the combination of technical solutions is contradictory or impossible to implement, such a combination of technical solutions should be considered non-existent and not within the scope of protection claimed in this application.

[0021] Reference Figure 1 The diagram shown is a flowchart illustrating an embodiment of the differential location method for heterogeneous database tables according to this application. The method is executed by an electronic device, which can be implemented by a software system and / or a hardware system. The differential location method for heterogeneous database tables includes: Step 101: Obtain the configuration information of the source table in the source database and the target table in the target database, and extract common columns from the source table and the target table respectively according to the configuration information.

[0022] Configuration information refers to structured data used to describe parameters such as database connection attributes, table names, field lists, filtering conditions, exclusion columns, and primary key settings. For example, configuration information may include the Uniform Resource Locator (URL) of the source database, the driver class name of the target database, the names of the tables to be compared, the names of the timestamp columns to be skipped, and the names of the actual primary key fields specified by the user.

[0023] The source database refers to the database instance that stores the original data tables to be verified. The target database refers to the database instance that stores the target data tables to be verified. For example, the source database could be a MySQL instance deployed in a production environment, and the target database could be an Oracle instance deployed in a disaster recovery environment or a data warehouse.

[0024] The source table refers to the data table in the source database that needs to be differentiated. The target table refers to the data table in the target database that needs to be differentiated. For example, the source table could be an InnoDB engine business table named "employee", and the target table could be a Parquet external table named "employee".

[0025] Common columns refer to a set of fields in the source and target tables that have the same name and are compatible in terms of data type, length, and precision. For example, if the source table contains three columns: id, name, and age, and the target table contains three columns: id, name, and salary, then the common columns are id and name.

[0026] In some embodiments, the processor acquiring configuration information and extracting common columns based on the configuration information includes: acquiring a configuration file submitted by the user or receiving input connection parameters and table names through a graphical interface; establishing connections with the source database and the target database respectively based on the database connection parameters in the configuration information; querying the system metadata views of the source table and the target table respectively to obtain the full field names, field types, field lengths, and numerical precision of each table; matching the field sets of the source table and the field sets of the target table by name, and filtering out fields with the same name as candidate common columns; comparing the field type, length, and precision of each candidate common column one by one, and if there are differences in the type systems of the source and target ends but the data can be converted without loss, then the field is included in the common column set; and returning the finally determined common column set and storing it in memory.

[0027] Step 102: Normalize the data in the common column to obtain normalized common column data.

[0028] Normalization refers to the process of converting semantically equivalent but differently represented data values ​​in a common column across different database systems into string representations with the same literal format and null value identifier.

[0029] Normalized common column data refers to intermediate datasets that have been normalized from the original common column data, resulting in a unified format that allows for direct string-level equality comparisons.

[0030] In some embodiments, the processor normalizes the data in the common columns by: scanning the data in the source and target tables row by row according to the order of the common columns; for each common column field in the current row, first determining whether the field value is a null value in the database, and if it is, converting it to a uniform string constant NULL_PLACEHOLDER; if the field value is an empty string and the database type is Oracle or SQL Server, also converting the empty string to NULL_PLACEHOLDER so that null values ​​and empty strings are semantically equivalent; if the field value is a floating-point number, performing a rounding operation on the value according to the number of decimal places specified in the configuration information, and converting the result to a decimal string with a fixed number of decimal places; if the field value is a boolean type, converting true values ​​to the string 1 and false values ​​to the string 0; if the field value is a date and time type, converting it to a uniform Greenwich Mean Time string according to the date format template specified in the configuration information; concatenating all the converted field values ​​into a complete normalized string record according to the original column order, and storing the record in the normalized common column dataset.

[0031] Step 103: Based on the normalized common column data, perform a bidirectional difference query that does not depend on the primary key to determine the first set of difference rows that exists only in the source table but not in the target table, and the second set of difference rows that exists only in the target table but not in the source table, and label the first set of difference rows and the second set of difference rows with source identifiers.

[0032] A bidirectional difference query refers to performing a set subtraction operation simultaneously from two directions: retrieving the set of rows in the source table that are not in the target table, and retrieving the set of rows in the target table that are not in the source table. For example, a bidirectional difference query can be implemented using the EXCEPT or MINUS clauses in Structured Query Language, or it can be implemented by combining it with left and right joins and null checks.

[0033] The first set of differencing rows refers to the set of all rows obtained through a bidirectional difference query that exist only in the source table but not in the target table. For example, if there are three rows of user data in the source table whose normalized strings cannot be matched exactly in the normalized dataset of the target table, then these three rows together constitute the first set of differencing rows.

[0034] The second difference set refers to the set of all rows obtained through a bidirectional difference query that exist only in the target table but not in the source table with corresponding normalized data. For example, if there are two rows of order data in the target table whose normalized strings cannot be matched exactly in the normalized dataset of the source table, then these two rows together constitute the second difference set.

[0035] The source identifier is a status label used to indicate whether a difference row originates from the source database or the target database. For example, the source identifier can take the value SOURCE_ONLY or TARGET_ONLY, corresponding to the first and second difference row sets, respectively.

[0036] In some embodiments, the processor executes a bidirectional difference query independent of the primary key, comprising: using the normalized common column dataset of the source table as the left set and the normalized common column dataset of the target table as the right set; constructing a first difference query statement, which selects all records in the normalized dataset of the source table and excludes existing records in the right set using the EXCEPT clause, the result of which is the first difference row set; constructing a second difference query statement, which selects all records in the normalized dataset of the target table and excludes existing records in the left set using the EXCEPT clause, the result of which is the second difference row set; after obtaining the first difference row set, attaching a source identifier to each record therein, with the field value of the identifier uniformly set to SOURCE_ONLY; after obtaining the second difference row set, attaching a source identifier to each record therein, with the field value of the identifier uniformly set to TARGET_ONLY; and temporarily storing the two difference row sets carrying the source identifier in a memory buffer, awaiting subsequent merging.

[0037] Step 104: Based on the normalized common column data, perform row-level difference positioning on the common row set in the source table and the target table, excluding the first difference row set and the second difference row set, to obtain the third difference row set.

[0038] The common row set refers to the set of data rows that are considered to be possibly consistent in the coarse screening stage after subtracting the first difference row set from the full normalized data of the source table and subtracting the second difference row set from the full normalized data of the target table.

[0039] Row-level difference localization refers to the operation of further refining the comparison of each row or group of data rows with the same hash fingerprint on the basis of a common row set, in order to identify deep difference rows that were not captured by bidirectional difference set due to different data repetition times, partial changes in field values, or semantic differences in null values.

[0040] The third set of differing rows refers to the set of all rows that are newly identified from the common set of rows after row-level difference location, and which actually have data inconsistencies between the source table and the target table.

[0041] In some embodiments, the processor performs row-level difference location on the common row set, including: first, determining whether the configuration information has been specified by the user as a real primary key; if no real primary key is specified, then for each row in the common row set, concatenating all field values ​​in the row normalized common column data according to a predefined column order, and calling the SHA2_256 hash algorithm to calculate a fixed-length hash value for the concatenated complete string, and using the hash value as the self-defined primary key for that row.

[0042] Using the self-defined primary key as the grouping criterion, the common row sets of the source table and the common row sets of the target table are grouped and counted separately to obtain the occurrence count of each self-defined primary key value in the source and target tables respectively. The source count result and the target count result are compared by performing a full outer join according to the self-defined primary key value. In the result set of the full outer join, all rows corresponding to primary key values ​​whose source count and target count are not equal are determined as the third difference row set, and the difference type is further marked as source redundancy or target redundancy according to the count size relationship. For duplicate groups where the source count and target count are equal but both are greater than 1, the complete original data of each row in the group is extracted, and the specific duplicate row with content change is determined by comparing the fields in the row, and the row with change is also included in the third difference row set.

[0043] Step 105: Based on the first set of difference rows, the second set of difference rows, and the third set of difference rows, generate the final difference row result; wherein each difference row in the final difference row result carries a difference type label.

[0044] The final difference row result refers to the final dataset that fully reflects all row-level differences between the source table and the target table after merging and deduplicating the first, second, and third difference row sets.

[0045] Difference type labeling refers to the classification label assigned to each difference row in the final difference row result, which indicates what category of inconsistency the row belongs to.

[0046] In some embodiments, the processor generates the final difference row result by: creating an empty result dataset containing the original column fields, the source identifier field, and the difference type field; inserting each record from the first difference row set into the result dataset and setting the difference type label to SOURCE_ONLY; inserting each record from the second difference row set into the result dataset and setting the difference type label to TARGET_ONLY; inserting each record from the third difference row set into the result dataset and setting the difference type label to COUNT_MISMATCH or CONTENT_CHANGE, respectively, based on the specific reason for the row being identified during the row-level difference location stage; during the merging process, if a record is detected to appear in both the first and third difference row sets, its SOURCE_ONLY label is retained according to the rule of priority for the first difference row set, and duplicate records are removed; and writing the final merged and deduplicated result dataset to a target storage location, which is an ORC format columnar storage table on a physical disk, for direct reading by downstream data repair processes.

[0047] This application's embodiments solve the problem of inaccurate row-level matching due to missing or non-unique primary keys in existing technologies by sequentially extracting common columns, normalizing the data, performing bidirectional difference queries without primary keys, and locating row-level differences. The final results include precise difference type annotations for each differing row. It also eliminates false positives caused by cross-database field type differences and semantic inconsistencies in null values. The comparison results are upgraded from simply indicating whether two tables are consistent to precisely locating each differing row and its specific direction of difference, resolving the issues of not being able to locate specific differing rows and the potential for false positives and false negatives in scenarios with duplicate primary keys and null values.

[0048] Figure 2 This is an exemplary flowchart illustrating the execution of keyless differential location according to some embodiments of this application. Figure 2 As shown, the process may include the following operations: Step 201: Determine whether a primary key has been configured based on the configuration information.

[0049] A primary key is a field in a database table that is used to uniquely identify a row of data, or a combination of multiple fields.

[0050] In some embodiments, the processor determines whether a primary key has been configured based on configuration information by: reading a primary key configuration field from the configuration information, the primary key configuration field being used to record whether the user has specified a real primary key and which fields have been specified; checking whether the value of the primary key configuration field is null or a predefined null flag; if the field value is null or a null flag, determining that a real primary key has not been configured; if the field contains one or more specific field names, determining that a real primary key has been configured.

[0051] Step 202: If no real primary key is configured in the configuration information, then perform the no-primary-key difference location.

[0052] A true primary key refers to a primary key constraint field explicitly specified by the data definition language when creating a database table, or a combination of fields that can uniquely represent a row of business data and is explicitly specified by the user in the configuration information.

[0053] Keyless differential location refers to a differential location method that constructs a temporary row identifier by calculating the integrated hash value of all fields in the data row itself without relying on any native primary key constraints in the database or the real primary key specified by the user, and performs group statistics and comparison based on the temporary identifier.

[0054] In some embodiments, the processor performing keyless difference location includes: switching the routing label of the current comparison process to keyless mode; and performing row hash calculation, group statistics, and full outer join comparison operations.

[0055] Step 203: For each row in the common row set, concatenate all values ​​in the normalized common column data in a preset order, calculate the hash value of the concatenated string, and use the calculated hash value as the self-defined primary key of the row.

[0056] The preset order refers to the arrangement of common columns that is determined after the common columns are extracted and before normalization is performed. For example, the preset order can be an ascending order based on the alphabetical order of the common column names, an order based on the physical storage order of each common column in the source table, or a custom column order explicitly specified by the user through configuration information.

[0057] A self-created primary key is a fixed-length hash value obtained by concatenating all normalized field values ​​of a data row in a preset order into a complete string, and then performing a cryptographic hash operation on that string, for data rows that do not have a real primary key. This hash value is used as a temporary identifier to distinguish different data rows in the current comparison session.

[0058] In some embodiments, the processor calculates a self-defined primary key for each row in the common row set, including: reading all normalized common column data of the row from the common row set row by row; extracting each normalized field value in a preset order, inserting a predefined invisible separator between two adjacent field values, and concatenating them into a complete intermediate string; inputting the intermediate string into the SHA2_256 hash algorithm to calculate a fixed-length and irreversible hash value; and using the hash value as the self-defined primary key for the row.

[0059] Step 204: Using the self-defined primary key as the grouping basis, count the occurrence frequency of each hash value in the source table and the target table respectively, and perform a full outer join comparison on the source table and the target table based on the self-defined primary key.

[0060] Grouping criteria refers to the key value used when performing grouping and aggregation operations on a dataset.

[0061] The occurrence count refers to the number of records in the common row set of the source table or the common row set of the target table within the group corresponding to the same custom primary key value. For example, if three rows in the source table have the same custom primary key H, then the hash value H occurs 3 times in the source table.

[0062] In some embodiments, the processor performs grouping statistics and full outer join comparison as follows: using a self-defined primary key as the grouping key, performing a grouping aggregation operation in Structured Query Language on the common row set of the source table, counting the number of records corresponding to each self-defined primary key value in the source table, and storing the statistical results as a source-side count mapping table; using the same self-defined primary key as the grouping key, performing the same grouping aggregation operation on the common row set of the target table, counting the number of records corresponding to each self-defined primary key value in the target table, and storing the statistical results as a target-side count mapping table; using the self-defined primary key value as the join key, performing a full outer join operation on the source-side count mapping table and / or the target-side count mapping table to generate a temporary joint comparison result set, wherein each row in the result set contains three fields: self-defined primary key value, source-side count, and target-side count; for self-defined primary key values ​​in the full outer join result that appear only in the source-side mapping table or only in the target-side mapping table, automatically filling the count of the missing side with zero.

[0063] In some embodiments, the step of counting the occurrence counts of each hash value in the source table and the target table based on the self-defined primary key, and performing a full outer join comparison on the source table and the target table based on the self-defined primary key, includes the following operations: S11, group and count rows with the same self-defined primary key in the source table and the target table respectively, and determine the source end count and the target end count.

[0064] Group counting refers to using the hash value of a self-defined primary key as the grouping key to perform grouping and aggregation operations on a dataset, and counting the number of record rows contained under each grouping key.

[0065] Source-end count refers to the number of records corresponding to a specific self-defined primary key value in the common row set of the source table.

[0066] The target count refers to the number of records corresponding to the same specific self-defined primary key value in the common row set of the target table.

[0067] In some embodiments, the processor performs group counting on the source table and the target table respectively, including: reading the common row set of the source table from the memory cache, using the self-defined primary key value associated with each record as the group key, calling the grouping aggregation function to count the records in the common row set of the source table, and obtaining a source-side group counting result set, where each record in the result set contains the self-defined primary key value and the corresponding source-side count field; and reading the common row set of the target table from the memory cache, using the same self-defined primary key value as the group key, calling the grouping aggregation function to count the records in the common row set of the target table, and obtaining a target-side group counting result set, where each record in the result set contains the self-defined primary key value and the corresponding target-side count field.

[0068] S12, the source count and the target count are associated using a full outer join according to the self-defined primary key.

[0069] A full outer join is a join method that merges two datasets according to a specified join key. This method retains all records in both the left and right datasets. For records on the other side that do not match the join key, the corresponding field values ​​on the other side are filled with null values.

[0070] The two datasets in the full outer join are the source group count result set and the target group count result set. The join key is a custom primary key value. Each row in the result set contains three fields: the custom primary key value, the source count, and the target count.

[0071] In some embodiments, the processor associates the counts of both ends through a full outer join, which includes: performing a full outer join operation on the source-end group count result set and the target-end group count result set with a custom primary key value as the join key; in the join operation, the source-end group count result set is used as the left dataset of the full outer join, and the target-end group count result set is used as the right dataset; traversing each custom primary key value in the left dataset, searching for records with the same custom primary key value in the right dataset, if found, merging the count values ​​on both sides into one output record, if not found, outputting the custom primary key value and its corresponding source-end count value, while filling the target-end count field with the value zero; then traversing the custom primary key values ​​in the right dataset that have not yet been matched, outputting each unmatched record and its corresponding target-end count value, while filling the source-end count field with the value zero; and summarizing all output records to form a joint comparison result set.

[0072] S13, if the source count is less than the target count, the row corresponding to the self-defined primary key is determined as a redundant difference in the target.

[0073] Redundant differences on the target side refer to situations where, under a certain self-defined primary key value, the primary key value appears more often in the target table than in the source table, resulting in the target side having several more records with the same hash fingerprint compared to the source side.

[0074] In some embodiments, the processor determines the target-side redundant difference by: traversing each record in the comparison result set, reading the source-side count field value and the target-side count field value in the record; comparing the size relationship between the source-side count value and the target-side count value, and if the target-side count value is greater than the source-side count value, determining that the self-proposed primary key value has a target-side redundant difference; extracting a corresponding number of original row records with the self-proposed primary key from the common row set of the target table based on the self-proposed primary key value and the difference in the number of differences, the number of extracted records being equal to the difference between the target-side count and the source-side count; labeling these extracted row records as target-side redundant differences, and adding these row records to a third difference row set.

[0075] S14, if the source count is greater than the target count, the row corresponding to the self-defined primary key is determined as a source redundant difference.

[0076] Source-side redundant differences refer to situations where, under a certain self-defined primary key value, the primary key value appears more often in the source table than in the target table, resulting in the source table having several more records with the same hash fingerprint compared to the target table.

[0077] In some embodiments, the processor determines source-end redundant differences by: traversing the next unprocessed record in the comparison result set, reading the source-end count field value and the target-end count field value in the record; comparing the size relationship between the source-end count value and the target-end count value, and if the source-end count value is greater than the target-end count value, determining that the self-proposed primary key value has a source-end redundant difference; extracting a corresponding number of original row records with the self-proposed primary key from the common row set of the source table based on the self-proposed primary key value and the difference in the number of differences, the number of extracted records being equal to the difference between the source-end count and the target-end count; labeling these extracted row records as source-end redundant differences, and adding these row records to a third difference row set.

[0078] S15, if the source count is equal to the target count, then perform row-level content difference determination on the equal count groups based on the self-defined primary key.

[0079] Equal count grouping refers to the grouping of self-defined primary key values ​​in the joint alignment result set where the source count and the target count are numerically equal.

[0080] Row-level content difference determination refers to, under the premise of equal counts, further comparing multiple rows of records with the same self-defined primary key value row by row or field by field to find rows with the same count but different field contents.

[0081] In some embodiments, the processor performs row-level content difference determination on equal count groups, including: traversing the joint comparison result set to identify self-designed primary key values ​​where the source count is equal to the target count; for each self-designed primary key value with equal count, extracting all original row records corresponding to the self-designed primary key value from the common row set of the source table to form a source equal group; extracting all original row records corresponding to the same self-designed primary key value from the common row set of the target table to form a target equal group; comparing whether the total number of rows in the source equal group is equal to the total number of rows in the target equal group; if the number of rows is equal, then using an intra-row field-by-field comparison method, sequentially comparing each field of each row record with the same intra-row sorting position in the two groups; if a field value is found to be different, then marking the corresponding row in the source and the corresponding row in the target as a content change difference row and adding it to the third difference row set.

[0082] This application's embodiments solve the problems of row-level difference location in scenarios without a primary key or with a non-unique primary key, and the problem of missed reports caused by randomly selecting a row when duplicate rows are randomly selected. By using the hash value of the self-defined primary key as the grouping basis in the difference location process without a primary key, the source and target ends are accurately counted and full outer joins are performed. Then, the rows are classified and processed according to the size of the count.

[0083] In some embodiments, after determining the source count and the destination count, the method further includes: S21, based on the specific values ​​of the source end count and the target end count corresponding to the self-proposed primary key, determine the difference type.

[0084] Difference type refers to the classification and identification of the difference category to which a difference row under a self-defined primary key group belongs, based on the relationship between the source and target counts and the specific values ​​of the counts themselves, during the row-level difference location process. For example, difference type can include specific categories such as source-side redundant differences, target-side redundant differences, content change differences, and duplicate intra-row differences.

[0085] In some embodiments, the processor determines the difference type based on the specific values ​​of the source count and the target count, including: reading the current record in the comparison result set and obtaining the source count value and the target count value in the record; firstly determining whether the source count value and the target count value are equal; if they are not equal, then determining the difference type as target redundant difference or source redundant difference respectively; if they are equal, then further determining whether the equal count value is equal to the value 1; if it is equal to 1, then determining the difference type as content change difference; if it is greater than 1, then determining the difference type as repeated in-line difference and triggering the subsequent symmetric difference operation process.

[0086] S22, when the source count and the target count are equal and both equal to 1, compare the two rows of data with the self-defined primary key in the source table and the target table item by item, locate the specific field where the content has changed, and mark the row of data as the content change difference.

[0087] Item-by-item comparison refers to a refined row-level comparison operation that compares the normalized field values ​​of two rows of data with the same self-defined primary key value, according to the preset order of the common columns, to see if they are completely identical.

[0088] Content change difference refers to a situation where, when the source table's count and the target table's count are both equal and both are 1, a row of data in the source table has a different value in one or more fields compared to the corresponding row in the target table. For example, if two rows of data under the same custom primary key have the same name field, but the source table's name field is "Zhang San" while the target table's name field is "Zhang Si," then that row is marked as having a content change difference, indicating that the name field has been changed.

[0089] In some embodiments, the processor performs item-by-item comparison and marks content change differences as follows: based on the current self-defined primary key value, query the common row set of the source table to find the only row that matches the primary key value, and use it as a single row record on the source side; based on the same self-defined primary key value, query the common row set of the target table to find the only row that matches, and use it as a single row record on the target side; according to the preset order of the common columns, extract the normalized value of each common column field in the single row record on the source side, and simultaneously extract the normalized value of the corresponding common column field in the single row record on the target side, and compare whether the two values ​​are completely equal; record the names of all fields with unequal values, and form a list of changed fields from these field names; mark the difference type of the row data as a content change difference, and store the list of changed fields as an additional attribute in a third set of difference rows.

[0090] S23, when the source count and the target count are equal and both are greater than 1, extract the values ​​of all rows with the self-defined primary key in the source table and the target table respectively to form the source row set and the target row set.

[0091] The source row set refers to the set of all rows with a specific self-defined primary key value extracted from the common row set of the source table, provided that the source count and the target count are equal and both greater than 1.

[0092] The target row set refers to the set of all rows with the same self-defined primary key value extracted from the common row set of the target table under the same conditions.

[0093] In some embodiments, the processor extracts the source row set and the target row set by: performing a query operation in the common row set of the source table based on the current self-proposed primary key value, filtering out all records that match the self-proposed primary key value, and encapsulating all the filtered records into a data set, which is stored in memory as the source row set; performing a query operation in the common row set of the target table based on the same self-proposed primary key value, filtering out all records that match the self-proposed primary key value, and encapsulating all the filtered records into another data set, which is stored in memory as the target row set; after extraction, recording the number of records in the source row set and the target row set respectively, and verifying that the number of records is equal. If the verification passes, proceeding to the next step of symmetric difference operation; if the verification fails, determining that the data is inconsistent and abnormal.

[0094] S24, perform a symmetric difference operation on the source row set and the target row set to determine which row in the duplicate rows has been changed or is missing, and mark the determined row as the difference within the duplicate row.

[0095] Symmetric difference operation refers to performing a set operation on two row sets. The result of this operation includes all elements that belong only to the source row set and not to the target row set, as well as all elements that belong only to the target row set and not to the source row set, but does not include elements that appear in both sets.

[0096] When two sets of rows are of equal size, the symmetric difference operation can accurately reveal the unique rows of the source and target ends, thereby determining which rows in the duplicate set have undergone content changes or which side is missing a row.

[0097] Duplicate intra-row differences refer to situations where the source and target row counts are equal and both greater than 1, but the number of rows with the same self-defined primary key value is the same on both sides, yet the specific field content of some rows is inconsistent between the source and target sides, or a row on one side has no completely matching corresponding row on the other side. For example, if the source row set has three records with contents A, B, and C, and the target row set also has three records with contents A, B, and D, then the result of the symmetric difference operation is C and D, indicating that row C is missing on the target side and row D is missing on the source side. Both C and D are marked as duplicate intra-row differences.

[0098] In some embodiments, the processor performs a symmetric difference operation on the source and target row sets, including: calculating the difference between the source fingerprint set and the target fingerprint set to obtain row records that exist only in the source and have no matching fingerprints in the target; calculating the difference between the target fingerprint set and the source fingerprint set to obtain row records that exist only in the target and have no matching fingerprints in the source; merging the two difference results, marking each row record involved in the merge as a duplicate intra-row difference, and marking it as redundant in the source or redundant in the target according to its database source, and storing the marking result in a third difference row set.

[0099] This application's embodiments solve the problem of fuzzy positioning where it is impossible to distinguish between single-line changes and differences within duplicate rows, and the problem of difficulty in pinpointing which specific row changed in duplicate data scenarios, by employing different difference positioning strategies based on whether the specific value of the count is 1, provided that the source-end count and the target-end count are equal.

[0100] In some embodiments, performing a symmetric difference operation on the source row set and the target row set to determine which specific row in the duplicate rows has been changed or is missing includes the following operations: S31, under the self-defined primary key, assign a first in-row index to each row in the source row set, and assign a second in-row index to each row in the target row set.

[0101] The first row's sequence number is a temporary sequential number assigned within the source row set to distinguish different rows with the same custom primary key value. This number is used to uniquely identify each row in the source row set during subsequent pairwise matching.

[0102] The second row number refers to a temporary sequential number assigned within the target row set to distinguish different rows with the same custom primary key value. This number is used to uniquely identify each row in the target row set during subsequent pairwise matching.

[0103] In some embodiments, the processor assigns intra-row numbers to the source row set and the target row set by: reading all records in the source row set, assigning a first intra-row number starting from 1 to each record in accordance with the original physical storage order or query return order of these records in the current custom primary key group, and storing the number as an additional attribute in the corresponding record metadata; reading all records in the target row set, assigning a second intra-row number starting from 1 to each record in accordance with the same order rule, and storing the number as an additional attribute in the corresponding record metadata.

[0104] S32, the combination of values ​​in the source row set other than the self-defined primary key is used as the first comparison key, and the combination of values ​​in the target row set other than the self-defined primary key is used as the second comparison key.

[0105] The first comparison key is a string formed by concatenating the normalized values ​​of all common columns (excluding the self-defined primary key field) of each record in the source row set according to a preset common column order. This string serves as a unique content identifier for that row in the symmetric difference operation and is used to match the content of the corresponding row in the target row.

[0106] The second comparison key is a string formed by concatenating the normalized values ​​of all common columns in each record of the target row set, excluding the self-defined primary key field, according to a preset common column order identical to the first comparison key. This string serves as a unique content identifier for the target row in the symmetric difference operation and is used for content matching with the corresponding row in the source row.

[0107] In some embodiments, the processor constructs the first and second alignment keys by: reading the current record line by line from the source row set, extracting the normalized values ​​of all common column fields except the self-defined primary key field from the record; extracting these normalized values ​​sequentially according to a preset common column order, inserting predefined delimiters between adjacent values, concatenating them into a complete string, using this string as the first alignment key for the row, and storing the alignment key in association with the corresponding first row indices; reading the current record line by line from the target row set, extracting the normalized values ​​of all common column fields except the self-defined primary key field, concatenating them into a second alignment key according to the same preset order and delimiters, and storing the alignment key in association with the corresponding second row indices; after construction, the source alignment key set and the target alignment key set are obtained respectively.

[0108] S33, perform pairwise matching of the first matching key and the second matching key. If there is a first matching key that cannot be matched, mark the corresponding row as redundant in the repeating group.

[0109] Pairwise matching refers to an inter-line matching operation that compares the first pairwise key of each record in the source set with the second pairwise key of each record in the target set to determine which rows in the two sets have completely equal pairwise keys and which rows have no matching pairwise keys.

[0110] In some embodiments, the processor performs pairwise matching of the first alignment key and the second alignment key and marks the source end as redundant, which includes: traversing each of the first alignment keys in the source end alignment key set; for the currently traversed first alignment key, searching in the target end alignment key set for whether there is a second alignment key that is completely equal to the first alignment key; if there is an equal second alignment key, then marking the first alignment key as matched and removing the corresponding second alignment key from the target end alignment key set, indicating that the pairing is complete; if the currently traversed first alignment key cannot find any equal second alignment key in the target end alignment key set, then marking the first alignment key as unmatchable; marking the original row record corresponding to the unmatchable first alignment key as redundant in the source end repeating group, and storing the mark and its corresponding first row indices in the third difference row set.

[0111] S34, if there is a second matching key that cannot be matched, then the corresponding row is marked as the target end being redundant in the repeating group.

[0112] In some embodiments, the processor marking the target end as redundant in the repeating group includes: continuing to traverse the first matching keys in the source end matching key set that have not yet been matched; if the first matching key cannot be matched in the target end matching key set, then marking it as redundant in the source end; after completing the traversal of all first matching keys, checking whether there are any remaining unmatched and removed second matching keys in the target end matching key set; if there are remaining second matching keys, then marking these remaining second matching keys as unmatched, and marking their respective corresponding original row records as redundant in the target end in the repeating group; storing the mark and its corresponding second row indices in the third difference row set.

[0113] S35, if the first comparison key and the second comparison key can be matched but the content is not completely the same, then the two matched lines are marked as changes in the content within the duplicate group.

[0114] In some embodiments, the processor marking content changes within a repeating group includes: during the pairwise matching process, for each successfully matched first and second matching keys, the pair is not directly considered to be identical in content; instead, the source original row and the target original row corresponding to the pair are further extracted; according to a preset order of common columns, the source original row and the target original row are compared field by field to see if the original normalized value of each field is completely equal; if all fields are completely equal, the paired rows are determined to be identical in content, and no difference mark is generated; if at least one field value is not completely equal, the paired rows are determined to have content changes, the original record of the source row and the original record of the target row are jointly marked as content changes within a repeating group, and the pair of rows are simultaneously stored in the third set of difference rows.

[0115] This application's embodiments achieve precise differential location and classification down to each row within a repeating row group by introducing three differential labeling branches into the symmetric difference operation: in-row sequence allocation, comparison key construction, pairwise matching, and matching results.

[0116] Step 205: The rows corresponding to the hash values ​​that appear inconsistently in the source table and the target table during the full outer join comparison are determined as the third set of differing rows.

[0117] In some embodiments, the processor determines the third difference row set by: traversing each record in the generated full outer join comparison result set; for the currently traversed record, comparing whether the source-side count field value and the target-side count field value in the record are equal; if the two values ​​are not equal, it is determined that there is a difference in the data row corresponding to the self-proposed primary key, and based on the self-proposed primary key value, extracting all original row records with the self-proposed primary key from the common row set of the source table and the target table respectively; merging these extracted row records into a temporary subset, and marking all rows in the temporary subset as part of the third difference row set; continuing to traverse the next record in the full outer join result set until all records have been processed, and finally summarizing to obtain the complete third difference row set.

[0118] This application's embodiments introduce a self-defined primary key mechanism based on normalized full-column concatenation hashing for data rows without a real primary key. The self-defined primary key is used as the grouping basis for accurate counting and full outer join comparison, effectively solving the problem that tables without a primary key or with a non-unique primary key cannot be directly matched at the row level.

[0119] Figure 3 This is an exemplary flowchart illustrating the execution of primary key difference location according to some embodiments of this application. Figure 3 As shown, the process may include the following operations: Step 301: Determine whether a primary key has been configured based on the configuration information.

[0120] For a detailed explanation of step 301, please refer to step 201 above, which will not be repeated here.

[0121] Step 302: If the configuration information contains a real primary key, then perform primary key difference location.

[0122] Primary key difference location refers to a difference location method that, under the condition that the user has explicitly specified the real primary key field, uses the real primary key as the unique identifier of the data row, and performs grouping, aggregation, fingerprint calculation and comparison of rows with the same real primary key value in the source table and the target table.

[0123] In some embodiments, the processor performing keyed difference localization includes: switching the routing flag of the current comparison process to keyed mode; reading the user-specified list of real key fields from the configuration information; performing cache table construction and incremental fingerprint comparison operations.

[0124] In other embodiments, after determining that a real primary key has been configured, the processor first checks whether there are null records in the real primary key fields of the source table and the target table. If there are records with null primary keys, these null primary key records are isolated separately. Instead of performing primary key difference location on them, these records are rolled back to the non-primary key difference location process for processing, so as to ensure that all data rows can be covered and compared, and to avoid some records being missed due to null primary keys.

[0125] Step 303: Pre-construct a row hash fingerprint cache table and a primary key fingerprint cache table. The row hash fingerprint cache table is used to store the row hash value and the corresponding version timestamp of each row of data. The primary key fingerprint cache table is used to store the aggregate fingerprint and the corresponding version timestamp of each row of primary key value.

[0126] A row hash fingerprint cache table is a relational table pre-created in persistent storage to cache the complete fingerprint of each row of data. For example, a row hash fingerprint cache table can contain a source table identifier column, a target table identifier column, a real primary key value column, a row hash value column, and a version timestamp column. The row hash value column stores the hash fingerprint calculated by concatenating all fields of the current row, and the version timestamp column stores the time point when the data in this row was last verified or updated, used to determine whether the cached fingerprint of this row is still valid.

[0127] A primary key fingerprint cache table is a relational table pre-created in persistent storage to cache the aggregate fingerprints of each real primary key group. For example, a primary key fingerprint cache table can contain a table identifier column, a real primary key value column, an aggregate fingerprint column, and a version timestamp column. The aggregate fingerprint column stores the combined fingerprint information of all rows within the group corresponding to the real primary key value, and the version timestamp column stores the time point when the primary key group was most recently verified or updated, used to determine whether the aggregate fingerprint of the primary key group needs to be recalculated.

[0128] A version timestamp is a time-series marker used to indicate the latest calculation or update time of a fingerprint cache entry for a specific data row or primary key group. This marker can be taken from the data change timestamp field of the source or target table, or from the current system time.

[0129] A clustered fingerprint is a summary fingerprint obtained by combining the hash values ​​of multiple duplicate records that may exist under the same real primary key value according to a preset merging rule. This clustered fingerprint can reflect the overall status of all duplicate rows within the primary key group.

[0130] In some embodiments, the processor pre-constructs a row hash fingerprint cache table and a primary key fingerprint cache table, including: creating a row hash fingerprint cache table in persistent storage before the first execution of primary key difference location, the table structure of which includes a cache identifier column, a real primary key value column, a row hash value column, and a version timestamp column, wherein the cache identifier column is used to distinguish whether the cache entry belongs to the source table or the target table; creating a primary key fingerprint cache table in the same persistent storage, the table structure of which includes a cache identifier column, a real primary key value column, an aggregate fingerprint column, and a version timestamp column; setting the initial state of both cache tables to empty tables, and recording the current system time as the initial version timestamp in the metadata of the cache tables to identify the creation time of the cache tables.

[0131] Step 304: When performing the primary key difference location, for data shards in the source table and the target table whose data change timestamps are greater than the version timestamps in the cache table, recalculate the row hash value or primary key aggregation fingerprint and compare them. Add the identified difference rows as new difference rows to the final difference row result and update the cache table synchronously.

[0132] Data sharding refers to dividing data in a source or target table into multiple subsets according to a preset sharding strategy. For example, data sharding can be based on the range of primary key values, with each shard containing records where the primary key value ranges from 1 to 10000; or it can be based on the physical partitions of the data table, with each partition being a data shard.

[0133] Data change timestamp refers to the timestamp field indicating the last modification time of a data row recorded in the source or target table.

[0134] Newly added difference rows refer to records that are newly discovered in the current comparison round and were not present or whose content has changed at the previous cached comparison time. For example, a newly added difference row could be a row in the source table where the salary field has been updated, but the corresponding row in the target table has not been updated synchronously.

[0135] In some embodiments, the processor performs incremental comparison and updates the cache when performing primary key difference location, including: reading the latest version timestamps of the source table and the target table from the row hash fingerprint cache table and the primary key fingerprint cache table, respectively; using the version timestamp as a reference, querying all data fragments in the source table whose data change timestamps are greater than the version timestamp, and marking these fragments as source fragments to be recalculated; using the same version timestamp as a reference, querying all data fragments in the target table whose data change timestamps are greater than the version timestamp, and marking these fragments as target fragments to be recalculated; merging the source fragments to be recalculated and the target fragments to be recalculated to obtain the set of difference fragments that need to be recalculated in this round.

[0136] For each data shard in the differential shard set, reread the normalized common column data of each row in that shard, recalculate the row hash value, or recalculate the primary key aggregate fingerprint after grouping by the primary key value; compare the recalculated new fingerprint with the historical fingerprint stored in the peer cache table, and if the fingerprints do not match, the row is identified as a new differential row; add the new differential row to the final differential row result, and update the fingerprint value and version timestamp of the corresponding entry in the row hash fingerprint cache table or the primary key fingerprint cache table with the recalculated new fingerprint and the current system time.

[0137] This application's embodiments introduce a row hash fingerprint cache table and a primary key fingerprint cache table. When performing location with primary key differences, fingerprint recalculation and comparison are only performed on data fragments whose data change timestamps are greater than the cache version timestamps. This solves the problem of wasted computational resources in existing data verification schemes that still require row-by-row comparison of all data in scenarios with primary keys and cannot utilize the temporal locality of data changes for incremental verification.

[0138] In some embodiments, when performing the primary key difference location, for data shards in the source table and the target table whose data change timestamps are greater than the version timestamps in the cache table, the row hash value or primary key aggregate fingerprint is recalculated and compared, including the following operations: S41, compare the block version number or block combination hash value of the corresponding data item in the source table and the target table.

[0139] A data item refers to a data block or partition that is divided between the source and target tables according to a preset sharding strategy. This data block contains multiple rows of data records and serves as the basic unit for fast block-level comparison. For example, a data item can be a data page divided by the physical storage pages of the source and target tables, all data within a single day divided by date partitions, or all rows within a continuous primary key value range.

[0140] A block version number is a monotonically increasing version identifier used to identify whether the content of a data item has been modified. This version number is incremented every time any row of data in the data item is inserted, updated, or deleted. For example, the block version number can be an auto-incrementing numeric sequence number or a version identifier generated based on a timestamp, which increments by one each time the data within the data item changes.

[0141] A block combination hash value is a single hash value that represents the overall data state of a data item, calculated by aggregating the primary key fingerprints or row hash values ​​of all rows in the data item according to a preset aggregation method. For example, a block combination hash value can be the result of XORing the row hash values ​​of each row in the data item, or it can be the result of concatenating the aggregated primary key fingerprints of all rows in the data item and then calculating the SHA256 hash.

[0142] In some embodiments, the processor comparing the block version number or block combination hash value of corresponding data items in the source table and the target table includes: dividing the data in the source table and the target table into multiple corresponding data items, each data item having a one-to-one correspondence in the source table and the target table; obtaining the latest block version number of the current data item from the source table, and simultaneously obtaining the latest block version number of the same corresponding data item from the target table; comparing whether the block version number of the source table is the same as the block version number of the target table; if both block version numbers exist and have the same value, then further comparing whether the block combination hash value of the source table is consistent with the block combination hash value of the target table; if the block version numbers are different, then directly determining that the data item has been changed, and no further comparison of the block combination hash value is performed.

[0143] S42. If the block version number of the corresponding data item is the same and the block combination hash value is consistent, it is determined that the data item has not changed, and the recalculation of the data item is skipped directly.

[0144] In some embodiments, the processor determines that a data item has not changed and skips recalculation by: based on the comparison results, if it is determined that the source block version number of a data item is the same as the target block version number, and the source block combined hash value is consistent with the target block combined hash value, then the data item is marked as unchanged; for the data item, the row hash recalculation process is not performed, nor is the group statistics and full outer join comparison process performed; all rows within the range of the data item are directly skipped, and the data item is not included in the candidate range of newly added difference rows, thereby saving computing resources and accelerating the overall comparison process.

[0145] S43, if the block version number of the corresponding data item is different or the block combination hash value is inconsistent, then it is determined that the data item has undergone data change.

[0146] Data changes refer to changes in one or more rows of data within a data item compared to the last cache comparison, such as inserting a new record, deleting an existing record, or updating the content of an existing record. For example, a data change could be an update of the salary field in a row of a source table from 5000 to 6000, a new row being added to the target table, or a row being deleted from the source table.

[0147] In some embodiments, the processor determines that a data item has been modified by: based on the comparison results, if it is determined that the source block version number and the target block version number of a data item are different, or the source block combined hash value and the target block combined hash value are inconsistent, then the data item is marked as modified; confirming that at least one row of data within the scope of the data item has been inserted, deleted, or updated, and that subsequent fine-grained recalculation and comparison processes are required.

[0148] S44, mark all data rows covered by the data item as invalid, and recalculate the row hash value or primary key aggregate fingerprint for the data rows within the scope of the data item.

[0149] An expiration flag is a temporary status identifier set for each row of data in a data item. This identifier indicates that the historical fingerprint of that row in the cache table no longer represents its current latest data state.

[0150] Primary key aggregation fingerprint refers to a single hash value calculated by a preset aggregation method for multiple rows of data or multiple fields under the same primary key value when a real primary key is configured.

[0151] In some embodiments, the processor marks all data rows covered by the data item as invalid and recalculates the fingerprint by: reading the range boundary information of the changed data item and obtaining a list of primary key values ​​or row identifiers of all rows contained in the data item; traversing each row identifier in the list and updating the status field of the cache record corresponding to these rows to invalid status in the row hash fingerprint cache table; for these rows marked as invalid, rereading their latest normalized common column data row by row and recalculating the row hash value of each row; if the user has configured a real primary key, grouping according to the primary key value, aggregating all recalculated row hash values ​​under the same primary key value, and generating a new primary key aggregate fingerprint corresponding to the primary key value.

[0152] S45 compares the recalculated fingerprint with the peer database line by line.

[0153] In some embodiments, the processor compares the recalculated new fingerprint with the peer database line by line, including: comparing the new fingerprint from the source end with the current fingerprint from the target end one by one; if they match, the data in that line is determined to be consistent and no difference record is generated; if they do not match, the data in that line is determined to be different and the record in that line is identified as a new difference line; the identified new difference line is added to the final difference line result, and the fingerprint value and version timestamp of the corresponding record in the row hash fingerprint cache table or the primary key fingerprint cache table are updated synchronously.

[0154] This application's embodiments introduce a fast pre-inspection mechanism based on block version number or block combination hash value in the primary key difference location process, enabling a large number of unchanged data blocks to be skipped directly without recalculating fingerprints row by row. This solves the problem that existing data verification schemes require full fingerprint calculation for all data rows in large data volume scenarios, leading to performance bottlenecks and an inability to efficiently support the daily comparison needs of large-scale heterogeneous databases.

[0155] Reference Figure 4 The diagram shown is a functional module schematic of the heterogeneous database table difference positioning device 100 of this application.

[0156] The heterogeneous database table difference location device 100 described in this application is installed in an electronic device. Depending on its function, the heterogeneous database table difference location device 100 includes an acquisition module 110, a processing module 120, a first determination module 130, a second determination module 140, and a generation module 150. These modules can also be referred to as units, which are a series of computer program segments that can be executed by the processor of an electronic device and perform a fixed function, and are stored in the memory of the electronic device.

[0157] In this embodiment, the functions of each module / unit are as follows: The acquisition module 110 is used to acquire the configuration information of the source table in the source database and the target table in the target database, and extract common columns from the source table and the target table respectively according to the configuration information; Processing module 120 is used to normalize the data in the common column to obtain normalized common column data; The first determining module 130 is used to perform a bidirectional difference query that does not depend on the primary key based on the normalized common column data, to determine a first set of difference rows that exists only in the source table but not in the target table, and a second set of difference rows that exists only in the target table but not in the source table, and to mark the source identifier for the first set of difference rows and the second set of difference rows; The second determining module 140 is used to perform row-level difference positioning on the common row set in the source table and the target table, excluding the first difference row set and the second difference row set, based on the normalized common column data, to obtain a third difference row set. The generation module 150 is used to generate a final difference row result based on the first difference row set, the second difference row set, and the third difference row set; wherein each difference row in the final difference row result carries a difference type label.

[0158] The specific implementation of the differential positioning device for heterogeneous database tables in this application is largely the same as the specific implementation of the differential positioning method for heterogeneous database tables described above, and will not be repeated here.

[0159] Reference Figure 5 The diagram shown is a schematic representation of a preferred embodiment of the electronic device of this application.

[0160] The electronic device includes a processor 111, a communication interface 112, a memory 113, and a communication bus 114, wherein the processor 111, the communication interface 112, and the memory 113 communicate with each other through the communication bus 114. Memory 113 is used to store computer programs, such as a differential location program for heterogeneous database tables; In some embodiments, the processor 111 may be a central processing unit (CPU), a controller, a microcontroller, a microprocessor, or other data processing chip. The processor 111 is typically used to control the overall operation of the electronic device, such as performing data interaction or communication-related control and processing. In this embodiment, the processor 111 is used to run program code stored in the memory 113 or process data.

[0161] The communication interface 112 may optionally include a standard wired interface or a wireless interface (such as a Wi-Fi interface). The communication interface 112 may also be used to establish a communication connection between the electronic device and other electronic devices.

[0162] The memory 113 includes at least one type of readable storage medium, including flash memory, hard disk, multimedia card, card-type memory (e.g., SD or DX memory), random access memory (RAM), static random access memory (SRAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), programmable read-only memory (PROM), magnetic memory, magnetic disk, optical disk, etc. In some embodiments, the memory 113 may be an internal storage unit of the electronic device, such as the hard disk or memory of the electronic device. In other embodiments, the memory 113 may also be an external storage device of the electronic device, such as a plug-in hard disk, smart media card (SMC), secure digital (SD) card, flash card, etc. of the electronic device. Of course, the memory 113 may include both internal storage units and external storage devices of the electronic device. In this embodiment, the memory 113 is typically used to store the operating system and various computer programs installed on the electronic device, such as the program code of the differential location program for heterogeneous database tables. In addition, the memory 113 may also be used to temporarily store various types of data that have been output or will be output.

[0163] Figure 5 Only an electronic device having a processor 111, a communication interface 112, a memory 113 and a communication bus 114 is shown. However, it should be understood that it is not required to implement all of the components shown, and more or fewer components may be implemented instead.

[0164] In one embodiment of this application, when the processor 111 executes a program stored in the memory 113, it implements the differential location method for heterogeneous database tables provided in any of the foregoing method embodiments, including: Obtain the configuration information of the source table in the source database and the target table in the target database, and extract common columns from the source table and the target table respectively according to the configuration information; The data in the common column is normalized to obtain normalized common column data; Based on the normalized common column data, a bidirectional difference query that does not depend on the primary key is executed to determine the first set of difference rows that exists only in the source table but not in the target table, and the second set of difference rows that exists only in the target table but not in the source table, and the source identifier is marked for the first set of difference rows and the second set of difference rows; Based on the normalized common column data, row-level difference positioning is performed on the common row set in the source table and the target table, excluding the first difference row set and the second difference row set, to obtain the third difference row set. Based on the first set of difference rows, the second set of difference rows, and the third set of difference rows, a final difference row result is generated; wherein each difference row in the final difference row result carries a difference type label.

[0165] For a detailed explanation of the above steps, please refer to the above. Figure 1 A flowchart illustrating an embodiment of the differential location method for heterogeneous database tables.

[0166] Furthermore, this application also proposes a computer-readable storage medium that is both non-volatile and volatile. This computer-readable storage medium is any one or any combination of several of the following: hard disk, multimedia card, SD card, flash memory card, SMC, read-only memory (ROM), erasable programmable read-only memory (EPROM), portable compact disc read-only memory (CD-ROM), USB memory, etc. The computer-readable storage medium includes a data storage area and a program storage area. The program storage area stores a differential location program for heterogeneous database tables. When executed by a processor, the differential location program for heterogeneous database tables performs the following operations: Obtain the configuration information of the source table in the source database and the target table in the target database, and extract common columns from the source table and the target table respectively according to the configuration information; The data in the common column is normalized to obtain normalized common column data; Based on the normalized common column data, a bidirectional difference query that does not depend on the primary key is executed to determine the first set of difference rows that exists only in the source table but not in the target table, and the second set of difference rows that exists only in the target table but not in the source table, and the source identifier is marked for the first set of difference rows and the second set of difference rows; Based on the normalized common column data, row-level difference positioning is performed on the common row set in the source table and the target table, excluding the first difference row set and the second difference row set, to obtain the third difference row set. Based on the first set of difference rows, the second set of difference rows, and the third set of difference rows, a final difference row result is generated; wherein each difference row in the final difference row result carries a difference type label.

[0167] The specific implementation of the computer-readable storage medium in this application is largely the same as the specific implementation of the differential location method for heterogeneous database tables described above, and will not be repeated here.

[0168] It should be noted that the sequence numbers of the embodiments in this application are for descriptive purposes only and do not represent the superiority or inferiority of the embodiments. Furthermore, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, apparatus, article, or method that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, apparatus, article, or method. Without further limitations, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, apparatus, article, or method that includes that element.

[0169] Through the above description of the embodiments, those skilled in the art can clearly understand that the methods of the above embodiments can be implemented by means of software plus necessary general-purpose hardware simulation platforms. Of course, they can also be implemented by hardware, but in many cases the former is a better implementation method. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk) as described above, and includes several instructions to cause a terminal device to execute the methods described in the various embodiments of this application.

[0170] The above are merely preferred embodiments of this application and do not limit the patent scope of this application. Any equivalent structural or procedural transformations made using the content of this application's specification and drawings, or direct or indirect applications in other related technical fields, are similarly included within the patent protection scope of this application.

Claims

1. A method for locating differences in heterogeneous database tables, characterized in that, The method includes: Obtain the configuration information of the source table in the source database and the target table in the target database, and extract common columns from the source table and the target table respectively according to the configuration information; The data in the common column is normalized to obtain normalized common column data; Based on the normalized common column data, a bidirectional difference query that does not depend on the primary key is executed to determine the first set of difference rows that exists only in the source table but not in the target table, and the second set of difference rows that exists only in the target table but not in the source table, and the source identifier is marked for the first set of difference rows and the second set of difference rows; Based on the normalized common column data, row-level difference positioning is performed on the common row set in the source table and the target table, excluding the first difference row set and the second difference row set, to obtain the third difference row set. Based on the first set of difference rows, the second set of difference rows, and the third set of difference rows, a final difference row result is generated; wherein each difference row in the final difference row result carries a difference type label.

2. The method for locating differences in heterogeneous database tables as described in claim 1, characterized in that, Based on the normalized common column data, row-level difference location is performed on the common row set in the source table and the target table, excluding the first and second difference row sets, to obtain a third difference row set, including: Determine whether a primary key has been configured based on the configuration information; If no real primary key is configured in the configuration information, then perform a no-primary-key difference location: For each row in the common row set, all values ​​in the normalized common column data are concatenated in a preset order, and a hash value is calculated for the concatenated string. The calculated hash value is used as the self-defined primary key for that row. Using the self-defined primary key as the grouping criterion, the occurrence frequency of each hash value in the source table and the target table is counted respectively, and a full outer join comparison is performed on the source table and the target table based on the self-defined primary key; The rows corresponding to hash values ​​that appear inconsistently in the source table and the target table during the full outer join comparison are determined as the third set of differing rows.

3. The method for locating differences in heterogeneous database tables as described in claim 2, characterized in that, The step of using the self-defined primary key as the grouping basis, counting the occurrence frequency of each hash value in the source table and the target table respectively, and performing a full outer join comparison on the source table and the target table based on the self-defined primary key includes: The rows with the same self-defined primary key in the source table and the target table are grouped and counted respectively to determine the source count and the target count; The source count and the target count are associated using a full outer join based on the user-defined primary key; If the source count is less than the target count, the row corresponding to the self-defined primary key is determined as a redundant difference in the target. If the source count is greater than the target count, the row corresponding to the self-defined primary key is identified as a redundant difference in the source. If the source count is equal to the target count, then the row-level content difference determination is performed on the equal count groups based on the self-defined primary key.

4. The method for locating differences in heterogeneous database tables as described in claim 3, characterized in that, After determining the source count and the target count, the method further includes: Based on the specific values ​​of the source count and the target count corresponding to the self-defined primary key, the difference type is determined, including: When the source count and the target count are equal and both equal to 1, the two rows of data with the self-defined primary key in the source table and the target table are compared item by item to locate the specific field where the content has changed, and the row of data is marked as the content change difference. When the source count and the target count are equal and both are greater than 1, the values ​​of all rows with the self-defined primary key in the source table and the target table are extracted respectively to form the source row set and the target row set; Perform a symmetric difference operation on the source row set and the target row set to determine which specific row in the duplicate rows has been changed or is missing, and mark the determined row as the difference within the duplicate row.

5. The method for locating differences in heterogeneous database tables as described in claim 4, characterized in that, The step of performing a symmetric difference operation on the source row set and the target row set to determine which specific row in the duplicate rows has been changed or is missing includes: Under the self-defined primary key, a first in-row sequence number is assigned to each row in the source row set, and a second in-row sequence number is assigned to each row in the target row set; The first comparison key is the combination of all values ​​in the source row set except for the self-defined primary key, and the second comparison key is the combination of all values ​​in the target row set except for the self-defined primary key. The first alignment key and the second alignment key are matched one by one. If there is a first alignment key that cannot be matched, the corresponding row is marked as the source end being redundant in the repeating group. If there is a second alignment key that cannot be matched, the corresponding row will be marked as the target end being redundant in the repeating group; If the first comparison key and the second comparison key can both be matched but the content is not exactly the same, then the two matched lines will be marked as content changes within the duplicate group.

6. The method for locating differences in heterogeneous database tables as described in claim 1, characterized in that, The method further includes: Determine whether a primary key has been configured based on the configuration information; If a real primary key is configured in the configuration information, then a primary key difference location will be performed: A row hash fingerprint cache table and a primary key fingerprint cache table are pre-built. The row hash fingerprint cache table is used to store the row hash value and the corresponding version timestamp of each row of data. The primary key fingerprint cache table is used to store the aggregate fingerprint and the corresponding version timestamp of each row of primary key value. When performing the primary key difference location, for data shards in the source table and the target table whose data change timestamps are greater than the version timestamps in the cache table, recalculate the row hash value or primary key aggregation fingerprint and compare them. Add the difference rows found in the comparison as new difference rows to the final difference row result and update the cache table synchronously.

7. The method for locating differences in heterogeneous database tables as described in claim 6, characterized in that, When performing the primary key difference location, for data shards in the source table and the target table whose data change timestamps are greater than the version timestamps in the cache table, the row hash value or primary key aggregate fingerprint is recalculated and compared, including: Compare the block version number or block combination hash value of the corresponding data item in the source table and the target table; If the block version number of the corresponding data item is the same and the block combination hash value is consistent, it is determined that the data item has not changed, and the recalculation of the data item is skipped directly. If the block version number of the corresponding data item is different or the block combination hash value is inconsistent, it is determined that the data item has undergone data change; Mark all data rows covered by the data item as invalid, and recalculate the row hash value or primary key aggregate fingerprint for the data rows within the scope of the data item; The recalculated fingerprint is compared line by line with the peer database.

8. A differential location device for heterogeneous database tables, characterized in that, The device includes: The acquisition module is used to acquire configuration information of the source table in the source database and the target table in the target database, and extract common columns from the source table and the target table respectively according to the configuration information; The processing module is used to normalize the data in the common column to obtain normalized common column data; The first determining module is used to perform a bidirectional difference query that does not depend on the primary key based on the normalized common column data, to determine a first set of difference rows that exists only in the source table but not in the target table, and a second set of difference rows that exists only in the target table but not in the source table, and to mark the source identifier for the first set of difference rows and the second set of difference rows; The second determining module is used to perform row-level difference positioning on the common row set in the source table and the target table, excluding the first difference row set and the second difference row set, based on the normalized common column data, to obtain the third difference row set. The generation module is used to generate a final difference row result based on the first difference row set, the second difference row set, and the third difference row set; wherein each difference row in the final difference row result carries a difference type label.

9. An electronic device, characterized in that, It includes a processor, a communication interface, a memory, and a communication bus, wherein the processor, the communication interface, and the memory communicate with each other through the communication bus; Memory, used to store computer programs; A processor, when executing a program stored in memory, implements the differential location method for heterogeneous database tables as described in any one of claims 1 to 7.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by the processor, it implements the differential location method for heterogeneous database tables as described in any one of claims 1 to 7.

Images