A method and system for data consistency check for remote heterogeneous ends

Abstract

The application provides a data consistency verification method and system for long-distance heterogeneous ends, aiming to solve the technical pain points of low efficiency, long time consumption, high cost and sensitivity to network delay caused by relying on full data network transmission when performing massive data consistency verification between long-distance heterogeneous databases in the prior art. By pushing down data-intensive computing tasks (such as hash calculation and aggregation) to the source and target databases for execution, only lightweight summary data (usually megabyte level) is transmitted in the network, and the final comparison is completed by the coordination end. The application has the following technical advantages: extreme network efficiency, order of magnitude improvement; not sensitive to high network delay; maximum utilization of computing resources; high compatibility of heterogeneous databases; robustness and intelligent degradation.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of computer data processing, in particular to a data consistency verification method for long-distance heterogeneous terminals, a data consistency verification system for long-distance heterogeneous terminals, an electronic device and a computer readable storage medium. BACKGROUND

[0002] With the popularity of globalization and cloud computing, enterprise data is often distributed in data centers or cloud platforms in different geographical locations, stored in different types of terminals (such as Oracle, MySQL, Postgre SQL, etc.), and constitutes a long-distance heterogeneous terminal system. In the scenarios of terminal migration, disaster recovery, read-write separation and multi-live architecture, it is crucial to ensure the data consistency between these heterogeneous terminals.

[0003] The existing data consistency verification technical solutions, such as the mainstream third-party comparison tools, usually adopt the "pull-comparison" mode. This mode needs to pull the full amount or the sharding of the data of the source database and the target database to a separate comparison server for calculation. This mode has the following significant defects:

[0004] 1. Network transmission bottleneck: When the data volume reaches the terabyte (TB) level, the transmission of full amount of data through the wide area network takes a very long time, usually several hours or even several days, and is heavily dependent on high-bandwidth dedicated lines, which is costly.

[0005] 2. High delay sensitivity: The round-trip time (RTT) of long-distance networks (such as cross-city, cross-country) is usually tens to hundreds of milliseconds. Any verification algorithm that needs frequent interactive communication will have its total time consumption become unacceptable due to the accumulation of network delay.

[0006] 3. Central node resource consumption: Buffering, sorting and comparing massive data on the comparison server puts a huge pressure on its memory, CPU and storage resources, and easily becomes a performance bottleneck.

[0007] 4. Heterogeneous compatibility problem: Different terminals have differences in data types, function support, character sets and sorting rules, and general comparison tools are difficult to achieve accurate and efficient hash calculation across terminals, which is costly and prone to errors.

[0008] Therefore, there is an urgent need for an efficient and low-cost data consistency verification solution that can break away from the dependence on network transmission of massive data, is not sensitive to high network delay, and is compatible with multiple heterogeneous terminals. SUMMARY

[0009] In order to solve the technical problems existing in the prior art, the present application provides the following technical solutions:

[0010] In one aspect, a method for data consistency verification between remote heterogeneous ends is provided, which is implemented by an electronic device, and the method comprises:

[0011] S1, existence verification

[0012] a) defining parameters of an invertible Bloom lookup table (IBLT) at a coordination end, and distributing to a source database and a target database;

[0013] b) executing SQL scripts in the source database and the target database respectively, to generate respective IBLT digest tables by performing a standardized hash calculation on the primary keys of each record, wherein the digest table contains a bucket ID, a record count, and an XOR sum of the primary keys;

[0014] c) transmitting the respective IBLT digest tables of the source database and the target database to the coordination end, and decoding a list of primary keys existing only in the source database and a list of primary keys existing only in the target database by a difference calculation and peeling algorithm at the coordination end, and determining primary key records common to both, to form a common primary key set;

[0015] S2, content verification

[0016] d) performing content consistency verification on the records in the common primary key set based on a hierarchical hash verification, and outputting a difference report if a record with inconsistent content is verified.

[0017] As a preferred embodiment of the present application, in step c), if the peeling algorithm fails to decode due to the number of difference primary keys exceeding the expectation, the hierarchical hash verification method of step d) is automatically used for comparison on the corresponding data range, and an alarm is issued.

[0018] As a preferred embodiment of the present application, the hierarchical hash verification method of step d) comprises:

[0019] dividing the common primary key set into multiple shards;

[0020] generating a "row content fingerprint" for each record in each shard and calculating a checksum for each shard by packaging the columns to be compared into a JSON string in a fixed order and format and calculating a hash value in the source database and the target database;

[0021] comparing the checksums of the shards by the coordination end, and for a "dirty shard" with inconsistent checksums, recursively dividing it into smaller sub-shards and re-comparing the checksums until the specific record with inconsistent content is accurately located.

[0022] In another aspect, a system for data consistency verification of remote heterogeneous databases is provided for implementing the above-described method, the system comprising a coordination end, a source database and a target database connected through a network communication, wherein:

[0023] The source database and the target database are configured to receive IBLT parameters from the coordination end, execute SQL scripts to generate respective IBLT summary tables and shard checksums;

[0024] The coordination end is configured to distribute IBLT parameters, receive the IBLT summary tables and execute a peeling algorithm to decode existence differences and determine a common primary key set, receive the shard checksums and perform comparison, and perform recursive positioning when inconsistencies are found, and finally generate a difference report.

[0025] As a preferred embodiment of the present application, the coordination end is further configured to instruct the source database and the target database to perform hierarchical hash verification on the corresponding data range directly when the peeling algorithm decoding fails.

[0026] In another aspect, an electronic device is provided, comprising a processor, a memory having computer readable instructions stored thereon, the computer readable instructions being executed by the processor to implement any one of the above-described methods for data consistency verification of remote heterogeneous databases.

[0027] In another aspect, a computer readable storage medium is provided, the storage medium having at least one instruction stored therein, the at least one instruction being loaded and executed by a processor to implement any one of the above-described methods for data consistency verification of remote heterogeneous databases.

[0028] The technical solutions provided by the embodiments of the present application have at least the following beneficial effects:

[0029] The present application overturns the traditional mode by "computing down and moving summary up", changes TB-level data transmission to MB-level summary transmission, and eradicates remote network bottlenecks. Two innovations are first proposed: 1) using standard SQL to "simulate" IBLT algorithm, solving the obstacle of no native support of databases; 2) defining "cross-platform hash conversion procedures", solving the problem of accurate calculation between heterogeneous databases. The universality of the present application is derived from a standardized "peer verification" methodology for systematically finding and adapting subtle differences in SQL of each database.

[0030] Compared with the prior art, the present application has the following significant advantages:

[0031] 1. Extreme network efficiency, orders of magnitude improvement: This method does not need to transmit TB-level service full data through the network, only MB-level IBLT summary and checksum are transmitted. This greatly reduces the requirement for network bandwidth and data transmission cost.

[0032] 2. Not sensitive to high network delay: The entire verification process only needs 2-3 times of remote interaction (getting IBLT summary, getting fragment checksum), the fixed low interaction times makes the total time consumption basically not affected by network round-trip time (RTT).

[0033] 3. Maximize the use of computing resources: The most resource-consuming aggregation calculation task is pushed down to the database for execution, making full use of the powerful parallel processing and query optimization capabilities of the database engine, and avoiding resource bottlenecks on the coordination end.

[0034] 4. High heterogeneity database compatibility: By defining a unified hash conversion logic and data type formatting rule that avoids platform pitfalls, and providing a lightweight UDF (user-defined function) adaptation layer for specific databases, this scheme can be smoothly applied to a variety of mainstream relational databases, with high universality.

[0035] 5. Robustness and intelligent degradation: Combining the advantages of IBLT and hierarchical checksum. IBLT is efficient for "existence difference", and hierarchical checksum is universal for "content difference". When IBLT decoding fails due to parameter problems, the system can intelligently degrade to hierarchical checksum mode, ensuring task success rate under various difference distribution conditions. BRIEF DESCRIPTION OF DRAWINGS

[0036] In order to more clearly illustrate the technical solutions in the embodiments of the present application, the following will briefly introduce the drawings needed to be used in the embodiment description. Obviously, the drawings in the following description are only some embodiments of the present application, and other drawings can also be obtained by those skilled in the art without creative labor.

[0037] Figure 1 is a detailed flow implementation step schematic diagram provided by the embodiment of the present application;

[0038] Figure 2 is a data consistency verification system block diagram for a remote heterogeneous end provided by the embodiment of the present application;

[0039] Figure 3 is a structure schematic diagram of an electronic device provided by the embodiment of the present application. DETAILED DESCRIPTION

[0040] The technical solutions in the present application will be described below in combination with the drawings.

[0041] In the embodiments of the present application, the words such as "exemplary", "for example", etc. are used to represent an example, illustration or description. Any embodiment or design scheme described as "exemplary" in the present application should not be interpreted as more preferred or more advantageous than other embodiments or design schemes. Rather, the word "exemplary" is intended to present the concept in a specific manner. In addition, in the embodiments of the present application, the meaning expressed by "and / or" can be both, or can be one of the two.

[0042] In the embodiments of the present application, "image" and "picture" can be used interchangeably at times, and it should be pointed out that the meanings expressed are consistent when the distinction is not emphasized. "Of", "corresponding" and "corresponding" can be used interchangeably at times, and it should be pointed out that the meanings expressed are consistent when the distinction is not emphasized.

[0043] In the embodiments of the present application, sometimes the subscript such as W1 can be mistakenly used in the form of non-subscript such as W1, and the meanings expressed are consistent when the distinction is not emphasized.

[0044] In order to make the technical problems, technical schemes and advantages to be solved by the present application more clear, the following will be described in detail in combination with the drawings and specific embodiments.

[0045] Explanation of terms:

[0046] 1. Invertible Bloom Lookup Table (IBLT) definition: a probabilistic data structure that can not only judge the difference of element set like Bloom filter, but also decode the specific content of the difference (i.e. which elements are added, which are deleted) with high probability. As the core of the first stage check in the present application, it is used to efficiently find the existence difference record primary key between the source library and the target library (i.e. the primary key that one party has and the other party does not have). It is composed of bucket ID (bucket), counter (count) and primary key xor sum (xor_pk) and the like.

[0047] Working principle of IBLT, here is a simple analogy to illustrate its principle:

[0048] Suppose there are two warehouse managers (source library and target library), each of whom has a very long list of goods (primary key list).

[0049] Step 1: Prepare the ledger (initialize IBLT)

[0050] They each prepare an identical, blank account book (IBLT data structure, i.e. a hash table) with numbered cells. Each cell contains three records: [count, xor of IDs, xor of hashes].

[0051] Step 2: Bookkeeping (filling the IBLT)

[0052] For each item on their own inventory (each primary key), the janitors find the corresponding three cells in the account book according to a pre-agreed set of rules (e.g. compute three hashes from the item ID), and then update the three cells respectively:

[0053] Increment the count by 1;

[0054] Xor the xor of IDs with the ID of the current item;

[0055] Xor the xor of hashes with the hash of the current item's content;

[0056] (Note: for simplicity, the first phase of the invention only uses the count and the xor of IDs);

[0057] The "XOR" operation is the magic here. Its property is A XOR A = 0, i.e. XORing a number with itself twice is equivalent to doing nothing.

[0058] Step 3: Reconciliation (decoding the differences)

[0059] The grand coordinator takes the two filled account books and subtracts each cell of the two books (subtract the counts, XOR the xors again). A "difference account book" is obtained.

[0060] In the difference account book, if a cell has a count of 1 or -1, it means that the cell is a "pure cell", which records the information of exactly one difference item. The coordinator can read the ID of the difference item directly from the xor of IDs of the cell.

[0061] Once a difference item is found, the coordinator can simulate how the item was originally recorded in the account book, and thus eliminate its effect on the "difference account book" (subtract the count and the xor). This process is called "peeling". After eliminating one difference, new "pure cells" may be created, and the process repeats until all differences are found.

[0062] Therefore, the IBLT algorithm has the following advantages:

[0063] Small summary: No matter how many original data (even if the upper billion), IBLT can generate a relatively small and fixed size of the summary. This is essential for the idea of "summary up" of the invention.

[0064] Efficient decoding difference: By simple mathematical operation on two IBLT summaries, the unique element list of both sides (i.e. the difference of the primary key) can be directly decoded. The whole process does not need the original data, the calculation is small, and the speed is extremely fast.

[0065] In short, IBLT allows the invention to accurately find out the "have" and "no" difference between two massive data sets just by two small summaries, which is difficult for other algorithms to efficiently implement.

[0066] But the traditional IBLT algorithm has the following defects:

[0067] Application limitations: It is not a database function itself, and there is no ready-made SQL interface. You can't let the database directly "execute IBLT". The invention breaks down the steps of the IBLT algorithm into a combination of standard SQL operations (GROUP BY, COUNT, BIT_XOR) that the database can understand, which is equivalent to "teaching" the database how to calculate the IBLT summary by itself.

[0068] Compatibility missing: IBLT theory does not care about the subtle differences between "MySQL" and "PostgreSQL" when calculating hash or handling data types. The invention designs a "cross-platform hash conversion procedure" that acts as a "universal adapter" to smooth out the underlying differences between heterogeneous databases, ensuring the absolute consistency of the summary calculation result, which is the cornerstone of the success of the scheme.

[0069] 2. Peeling algorithm (Peeling Algorithm) definition: IBLT decoding algorithm. By iteratively finding and processing "pure buckets" (buckets containing only one difference element information), all difference elements are gradually decoded. After obtaining the difference set of the two IBLT summaries in the coordination end of the invention, run this algorithm to parse the list of added and deleted primary keys.

[0070] 3. Hierarchical Hashing Checksum (Hierarchical Hashing Checksum) definition: A method for checking the consistency of large-scale data content. It first divides the data into large slices (level one), calculates the hash checksum of each slice for comparison. For inconsistent slices, recursively divide them into smaller sub-slices (level two, three...) for comparison until the specific difference record is located. In the invention, it is the core of the second stage of verification, used to accurately compare whether the record content of the public primary key set is completely consistent.

[0071] 4. Row Fingerprint Definition: A short value or string that uniquely identifies a row. In the first phase of the invention (IBLT verification), it refers to the primary key (PK) of the record, which is used to determine whether the record exists.

[0072] 5. Row Content Fingerprint Definition: A hash value that represents the content of all columns in a row. It is obtained by serializing the values of multiple columns in a fixed order and format (such as converting to a JSON string) and then calculating the hash. In the second phase of the invention (Layered Hash Verification), it is used to determine whether the content is consistent. As long as the value of any field is different, the row content fingerprint will be different.

[0073] 3. Salt Definition: A fixed string or value that is added to the input data before hash calculation. In the invention, it is used to derive multiple independent hash functions from a single base hash algorithm (such as MD5), which is necessary for the normal operation of IBLT. At the same time, a uniform salt value ensures that the hash results are consistent between heterogeneous databases.

[0074] The invention is applicable to long-distance, high-latency network environments.

[0075] The invention adopts a standardized "peer-to-peer verification process". The invention does not rely on a "universal" function, but systematically verifies the behavior of the basic SQL functions (such as MD5, JSON_OBJECT, BIT_XOR, etc.) required to implement the verification logic for each pair of databases. Although these functions follow the international SQL standard, there are subtle differences in the implementation of each manufacturer (such as NULL handling, floating-point precision, JSON key sorting, etc.), which is the problem that the invention solves through this process.

[0076] The invention is the first to propose and implement a complete set of innovative, non-obvious engineering methods in the specific technical field of long-distance, heterogeneous database data consistency verification. The core of the method is to successfully, efficiently, and with wide compatibility, apply the existing theory of IBLT to solve a long-standing industry pain point through "SQL simulation" and "hash protocol" and other means.

[0077] The invention will be described in detail below. The invention can be used to verify two databases by a coordinating end, or to verify two data ends based on the technical principles of the invention. As long as the scene can interact with data and use the scheme for verification, it should belong to the protection scheme of the invention.

[0078] The embodiment of the present application provides a data consistency checking method for long-distance heterogeneous terminals, which can be implemented by an electronic device, which can be a terminal or a server. Figure 1 As shown in the flow chart of the data consistency checking method for long-distance heterogeneous terminals, the processing flow of the method can include the following steps:

[0079] S1, existence checking

[0080] a) defining the parameters of an invertible Bloom lookup table (IBLT) at the coordination terminal, and distributing to the source database and the target database;

[0081] b) executing SQL scripts in the source database and the target database respectively, generating respective IBLT summary tables by performing standardized hash calculation on the primary keys of each record, and the summary table contains bucket ID, record count and XOR sum of the primary keys;

[0082] c) transmitting the respective IBLT summary tables of the source database and the target database to the coordination terminal, and decoding the primary key list existing only in the source database and the primary key list existing only in the target database by the coordination terminal through the difference calculation and stripping algorithm, and determining the primary key records common to both sides to form a common primary key set;

[0083] S2, content checking

[0084] d) based on hierarchical hash checking, checking the content consistency of the records in the common primary key set, and if the content inconsistent record is checked, outputting a difference report.

[0085] The present application discloses a kind of long-distance heterogeneous database efficient consistency checking method based on native computing, its core idea is " computing down push, abstract up move ".The method will data-intensive computing task (such as hash calculation, aggregation) down push to source and target database internal execution, only transmit lightweight abstract data (usually megabyte level) in network, by coordination terminal complete final comparison.

[0086] The present application issues a same " inventory rule " (SQL script) to two data terminals (database itself).Each end completes inventory locally, only sends a minimalist " inventory list abstract " (MB level IBLT abstract / checksum) to coordination terminal through network.Coordination terminal compares two abstracts, and can know difference instantly.Therefore, the most resource-consuming computing task (hash, aggregation) is " down push " to the database server that is best at doing this thing internal execution, and only the " abstract " data of extremely lightweight generated after calculation is transmitted on network.This directly solves the pain point that network transmission becomes the biggest bottleneck in prior art.

[0087] The following will be combined with the drawings Figure 1Detailed description of the implementation steps of the invention.

[0088] The present invention pushes down data-intensive computing tasks (such as hash calculation, aggregation) to the source and target databases for execution, only transmitting lightweight summary data (usually in the order of megabytes) in the network, and completing the final comparison by the coordination end.

[0089] As shown, it includes the following phase steps: Figure 1

[0090] First phase: existence difference check based on reversible Bloom lookup table (IBLT)

[0091] 1. Initialization and parameter setting: define globally consistent IBLT parameters on the coordination end, including bucket count (bucket_count), hash function count (hash_count), and a set of salt values (salts).

[0092] 2. Database internal encoding generates IBLT summary: in the source database (source database) and target database (target database), respectively execute the preset SQL script. The script uses the record primary key (PK) as the "row fingerprint" for each record in the table to be checked.

[0093] To ensure the consistency of the hash calculation results across heterogeneous databases, the present invention defines a set of standardized hash value generation procedures. The core of the procedure is to establish a commonly recognized and reproducible hash conversion path for any pair of heterogeneous databases to be checked (including different versions thereof).

[0094] Although there are many types of database systems, the implementation of their native functions may have slight differences, making it difficult to find a "universal" hash function suitable for all systems. However, the present invention finds that by carefully designing the conversion path, one or a few highly universal implementation methods can be constructed to cover most mainstream database combinations. A preferred and highly universal hash conversion path includes the following steps:

[0095] 1) Concatenate the record primary key with the preset salt value as a string;

[0096] 2) Apply the standard MD5 hash algorithm to the concatenated string to generate a 32-bit hexadecimal hash summary;

[0097] 3) Extract the fixed-length prefix of the MD5 summary (for example, the first 15 hexadecimal characters, corresponding to 60 binary data), to adapt to the representation range of large integers in each database while ensuring low collision rate.

[0098] ​The intercepted hexadecimal string is converted into an unsigned big integer using functions natively supported and behaving consistently across databases. This approach cleverly circumvents implementation differences in advanced hashing functions or data type conversions across different databases, ensuring that the final integer values used for IBLT computation are identical across source and target databases. This path has been verified across multiple mainstream databases, as shown in the following table, such as the following "Database (version): SQL snippet implementing (MD5 first 15 hexadecimal -> 60-bit unsigned integer)" for databases:

[0099] PostgreSQL ≥ 9.1 : ('x'|| substring(md5(pk::text || :salt), 1,15))::bit(60)::bigint ;

[0100] MySQL ≥ 8.0 : CAST(CONV(SUBSTRING(MD5(CONCAT(pk, :salt)), 1, 15),16, 10) AS UNSIGNED) ;

[0101] Oracle 12c+ : TO_NUMBER(SUBSTR(STANDARD_HASH(pk || :salt, 'MD5'), 1,15), 'XXXXXXXXXXXXXXXXXXX');

[0102] SQL Server 2016+ : CONVERT(DECIMAL(19,0), CONCAT('0x', SUBSTRING(sys.fn_varbintohexstr(HASHBYTES('MD5', pk + :salt)), 3, 15))).

[0103] Based on the above hash value, the bucket to which the record belongs is calculated, and then the SQL aggregation function (GROUPBY, COUNT(*), BIT_XOR(pk)) is used to generate the IBLT summary table. The summary table contains three columns: bucket ID, record count falling into the bucket (count), and record primary key XOR sum (xor_pk).

[0104] 3. Summary transmission and decoding:

[0105] The IBLT summary tables generated by the source and target databases (usually lightweight CSV or JSON files) are transmitted to the coordination end.

[0106] The coordination end calculates the difference between the two summary tables to generate the difference IBLT.

[0107] Perform a "Peeling" algorithm on the coordinator to decode the list of primary keys that only exist in the source database (added_keys) and the list of primary keys that only exist in the target database (removed_keys) from the difference IBLT.

[0108] There is an exception handling and rollback mechanism here. If there is an IBLT decoding failure processing: if the difference primary key number exceeds the expected value, causing the IBLT "peeling" to fail (there are residual buckets that cannot be decoded), the system can automatically enlarge the bucket number of the IBLT and retry. If multiple retries still fail, it is automatically downgraded, and the corresponding data range is compared using the second stage (layered hash checksum) method of the present application, and an alarm is issued.

[0109] Second stage: content difference verification based on layered hash checksum

[0110] 1. Identify the common primary key set: After the first stage of verification, the primary key records common to both parties are defined as the common primary key set.

[0111] 2. Define and generate "row content fingerprints":

[0112] For records in the common primary key set, in order to ensure the accuracy of content comparison, a structured "row content fingerprint" is defined. The recommended approach is to use standard functions such as JSON_OBJECT or JSON_ARRAY in the source and target databases to encapsulate all columns to be compared into a JSON string in a fixed order and uniform format (such as converting dates to ISO 8601 strings, converting floating-point numbers to fixed-precision strings). Calculate the hash value (such as MD5) of the JSON string as the "row content fingerprint" representing the entire row content.

[0113] 3. Sharding and checksum calculation:

[0114] Divide the common primary key set into large-scale shards (for example, 1 million records per group) according to a fixed rule (such as primary key range). In the source and target databases, respectively, sum the integer values corresponding to the "row content fingerprints" of all records in each shard to generate the checksum of each shard. To enhance the accuracy of the verification, the 128-bit MD5 value can be split into two 64-bit integers, and the sums are calculated separately.

[0115] 4. Checksum comparison and recursive positioning:

[0116] Transfer the checksums of each shard from the source and target databases to the coordinator.

[0117] The coordinator compares the shard checksums one by one. If they are equal, it is considered that the data in the shard is consistent.

[0118] If not, mark the shard as "dirty shard". For "dirty shard", we can use binary search strategy, recursively divide into smaller sub-shards and recalculate, compare checksums, until the specific record of content inconsistency is finally located accurately.

[0119] An example will be provided below to specifically implement the method principle as above.

[0120] Check the data consistency of a 1TB user table between cross-city MySQL and Postgres SQL databases

[0121] Scenario:

[0122] Source library (source database): MySQL 8.0 in Shanghai data center, table users, about 500 million records.

[0123] Target library (target database): PostgreSQL 14 in Beijing data center, table users, data is basically synchronized.

[0124] Network: cross-city dedicated line, RTT about 100ms.

[0125] Expected difference: It is expected that there are less than 10,000 record differences due to synchronization delay and other reasons.

[0126] Coordination end: a server located in any location, running Python script.

[0127] Detailed steps:

[0128] Phase I: IBLT existence verification

[0129] 1. Parameter setting:

[0130] Expected difference k = 10000;

[0131] Set bucket_count = k * 4 = 40000;

[0132] Set hash_count = 3;

[0133] Set salts = ['salt_A','salt_B','salt_C'].

[0134] Explanation: The literature "Invertible Bloom Lookup Tables" (Goodrich & Mitzenmacher, 2015) gives the theoretical threshold stripping success rate close to 1.

[0135] This example takes "4 * k", which is a common "engineering safety factor" - the success rate is close to 100% in 100,000 Monte-Carlo simulations, and the calculation and network overhead are still within an acceptable range. If you want to reduce IO (database table scanning), you can use bucket_count ≈ 2 k and combine "random salt switching" to achieve similar success rates.

[0136] 2. Source database (MySQL) generates IBLT summary:

[0137] Execute SQL scripts on the MySQL database to calculate the hash for each of the three hash functions (corresponding to three salt values), combine the results using UNION ALL, and finally aggregate them.

[0138] MySQL IBLT summary generation pseudocode

[0139] CREATE TEMPORARY TABLE source_hashes AS

[0140] SELECT id, CAST(CONV(SUBSTRING(MD5(CONCAT(id,'salt_A')), 1, 15), 16,10) AS UNSIGNED) % 40000 AS bucket FROM users

[0141] UNION ALL

[0142] SELECT id, CAST(CONV(SUBSTRING(MD5(CONCAT(id,'salt_B')), 1, 15), 16,10) AS UNSIGNED) % 40000 AS bucket FROM users

[0143] UNION ALL

[0144] SELECT id, CAST(CONV(SUBSTRING(MD5(CONCAT(id,'salt_C')), 1, 15), 16,10) AS UNSIGNED) % 40000 AS bucket FROM users;

[0145] SELECT bucket, COUNT(*) as count, BIT_XOR(id) as xor_pk

[0146] FROM source_hashes

[0147] GROUP BY bucket

[0148] The results are exported to source_iblt.csv

[0149] This query uses the BIT_XOR aggregate function natively supported by MySQL 8.0 to efficiently generate the digest with a single full table scan.

[0150] 3. Target database (PostgreSQL) generates IBLT digest:

[0151] Execute a similar SQL script on the PostgreSQL database, but use PostgreSQL-compatible hash conversion syntax.

[0152] PostgreSQL IBLT digest generation pseudocode

[0153] CREATE TEMPORARY TABLE target_hashes AS

[0154] SELECT id, ('x' || substring(md5(id::text ||'salt_A'), 1, 15))::bit(60)::bigint % 40000 AS bucket FROM users

[0155] UNION ALL

[0156] SELECT id, ('x' || substring(md5(id::text ||'salt_B'), 1, 15))::bit(60)::bigint % 40000 AS bucket FROM users

[0157] UNION ALL

[0158] SELECT id, ('x' || substring(md5(id::text ||'salt_C'), 1, 15))::bit(60)::bigint % 40000 AS bucket FROM users;

[0159] CREATE AGGREGATE bit_xor(bigint) (SFUNC = int8xor, STYPE = bigint); If not natively supported, create a custom aggregate

[0160] SELECT bucket, COUNT(*) as count, bit_xor(id) as xor_pk

[0161] FROM target_hashes

[0162] GROUP BY bucket

[0163] Result exported as target_iblt.csv

[0164] 4. Coordination side decoding:

[0165] Coordination side fetches source_iblt.csv and target_iblt.csv via SFTP or similar (file size ~40000 * 24B ≈ 1MB). Python script loads these two files, computes the difference IBLT, and executes the peel algorithm. The source database's unique primary key list and the target database's unique primary key list can be decoded within tens of seconds.

[0166] 5. IBLT decoding failure handling mechanism:

[0167] When the IBLT peel algorithm cannot completely decode,

[0168] Failure determination conditions:

[0169] If the number of residual buckets is small (e.g., less than 1% of the total number of buckets), it is considered that the number of differences is slightly more than expected, at which point the bucket size is expanded for retry (usually 1 time based on cross-city network communication cost and database calculation cost).

[0170] If the number of residual buckets is huge, or if the retry fails, the system should issue an alarm and trigger a degradation mechanism. Because at this point the existence difference is already too large, the IBLT has failed.

[0171] The degradation mechanism should be to directly start the second phase of hierarchical hash verification on the full table data (or a very large data range).

[0172] Phase Two: Hierarchical Hash Content Verification

[0173] 5. Determine the common primary key set: exclude the primary keys with differences found in phase one from the original primary key list, then check if the row contents of the remaining primary keys are consistent.

[0174] 6. Define row content fingerprints:

[0175] Assume that the name, email, balance, and updated_at fields need to be compared.

[0176] MySQL side:

[0177] MD5(JSON_OBJECT(

[0178] 'n', name,

[0179] 'e', email,

[0180] 'b', CAST(balance AS CHAR), DECIMAL to string

[0181] The function `DATE_FORMAT(updated_at, '%Y-%m-%dT%H:%i:%s.%f')` converts the date to ISO format. ))

[0183] PostgreSQL side:

[0184] MD5(json_build_object(

[0185] 'n', name,

[0186] 'e', email,

[0187] 'b', balance::text,

[0188] 'u', to_char(updated_at, 'YYYY-MM-DD"T"HH24:MI:SS.MS')

[0189] ::text)

[0190] By using JSON_OBJECT and a uniform format, the comparability of fingerprints generated across different libraries is ensured.

[0191] When generating "row content fingerprints," subtle implementation differences between heterogeneous databases (such as JSON_OBJECT key order, NULL handling, floating-point precision, time zones, etc.) can lead to different fingerprints for the same data. This solution employs the following engineering methods to ensure consistency:

[0192] Basic principle: Determine the unified standard for each database pair through pairwise experiments, mainly including:

[0193] 1) Use JSON_ARRAY instead of JSON_OBJECT: avoid differences in key sorting and build the array according to a fixed column order;

[0194] 2) Explicitly handle special values: NULL is uniformly converted to the string 'NULL', and an empty string is converted to 'EMPTY';

[0195] 3) Numerical standardization: Floating-point numbers are uniformly formatted to a fixed precision;

[0196] 4) Unified UTC time: All timestamps are converted to UTC and then formatted.

[0197] These processing methods are standard engineering practices and do not constitute the focus of this invention. In actual deployment, simple testing on specific source-target database pairs and adjustments to the SQL statements are sufficient to ensure fingerprint consistency.

[0198] 7. Fragmentation verification:

[0199] The 500 million public primary keys are divided into 500 shards, with each 1 million records forming a group.

[0200] Execute SQL on both MySQL and PostgreSQL to calculate the checksum of the "row content fingerprint" of all records within each shard. For example, sum the MD5 hashes by splitting them into two parts.

[0201] SELECT

[0202] floor(id / 1000000) AS slice_id,

[0203] SUM(CAST(CONV(SUBSTRING(md5_hash, 1, 16), 16, 10) AS UNSIGNED)) ASchecksum1,

[0204] SUM(CAST(CONV(SUBSTRING(md5_hash, 17, 16), 16, 10) AS UNSIGNED)) ASchecksum2

[0205] FROM (

[0206] SELECT id, generate_content_fingerprint(...) AS md5_hash

[0207] FROM users

[0208] WHERE id IN ( / public primary key set / )

[0209] ) t

[0210] GROUP BY slice_id;

[0211] 8. Comparison and Positioning:

[0212] The coordination end obtains 500 checksum records generated on both sides, and performs comparison. It is assumed that checksums of the 10th and 25th shards are found to be mismatched.

[0213] For the two "dirty shards", a binary method is used to further divide them into smaller sub-shards (for example, 100,000 pieces), and checksums of the sub-shards are requested and returned again. Through several rounds of recursion, it can be quickly located that the content of which records is different.

[0214] Performance comparison:

[0215] The scheme of the present application: generating IBLT summary for about 40 minutes, transmitting summary can be ignored, and decoding for about 1 minute. The shard checksum generation is about 40 minutes, and the comparison is negligible. If there is no content difference, the total time consumption is about 1.5 hours. The total amount of network transmission is in the order of MB.

[0216] The traditional scheme: assuming that a third-party tool is used, transmitting 1TB data under a 1Gbps private line, the theoretical fastest time is 1024*8 / 1=8192 seconds ≈ 2.3 hours, which does not calculate the CPU and memory overhead of pulling, writing and comparing. The actual total time consumption is usually tens of hours or even days.

[0217] The present embodiment fully illustrates how the present application realizes a verification efficiency far exceeding the traditional scheme in the actual complex scene by pushing the calculation to the database and transmitting only lightweight summaries over the network.

[0218] The traditional scheme: assuming that a third-party tool is used, transmitting 1TB data under a 1Gbps private line, the theoretical fastest time is 10248 / 1=8192 seconds ≈ 2.3 hours, which does not calculate the CPU and memory overhead of pulling, writing and comparing. The actual total time consumption is usually tens of hours or even days.

[0219] The present embodiment fully illustrates how the present application realizes a verification efficiency far exceeding the traditional scheme in the actual complex scene by pushing the calculation to the database and transmitting only lightweight summaries over the network.

[0220] The above description is only a preferred embodiment of the present application, and is not used to limit the protection scope of the present application. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present application shall be included in the protection scope of the present application.

[0221] Engineering details supplement

[0222] 1. The data on both ends is "static" by default within the verification period; if there is still writing, it will cause non-repeatable read and phantom read, and then distort the pushed calculation and the final report. In specific implementation, it is usually for snapshot comparison; or only append write without modify write;

[0223] 2. If the slice is too small (<1000 rows) and still inconsistent, directly pull the full row content for comparison.

[0224] 3. If the one-time scan and MD5 calculation is too large, you can add LIMIT / OFFSET or batch PK>last paradigm in the verification script, page by page; or use the PARALLEL hint / query partition of each DB.

[0225] 4. About PK: IBLT algorithm supports finding the difference between two sets of keys.

[0226] This SQL example defaults to a single-column integer PK. But it also supports combined PKs such as (colA, colB): Convert each primary key column to a string in the predetermined order, and use a special character (such as \u0001) that is unlikely to appear in the data as a separator to concatenate them.

[0227] For tables without primary keys but with unique indexes, you can use unique index columns.

[0228] For tables without unique identifiers, it is explicitly stated that this solution is not applicable.

[0229] 5. The example uses MD5, but CRC32 can also be used; the core is still to ensure consistency between the results of each calculation.

[0230] Specifically, when a new pair of heterogeneous database combinations needs to be supported (for example, migrating from Oracle to SQL Server), the team will perform a standardized internal verification process:

[0231] Establish a "Golden Test Set":

[0232] A small but extremely representative test table will be created. This table contains various "traps" or "boundary conditions" that are most prone to errors in data verification, such as:

[0233] NULL values;

[0234] Empty string ('');

[0235] Strings containing special characters (single quotes, double quotes, line breaks);

[0236] Floating-point and fixed-point numbers of different precision (FLOAT, DECIMAL);

[0237] Timestamps with and without time zone;

[0238] Zero values (0, 0.0).

[0239] Unit test core functions. Instead of testing the whole complex validation SQL directly, break it down into the most basic "atomic operations", execute them on both databases, and assert that the results must be binary consistent.

[0240] Phase 1 (IBLT) verification:

[0241] String concatenation: Is the result of CONCAT(pk,:salt) on both databases consistent?

[0242] MD5 hash: Is the result of MD5 on the same string consistent on both sides?

[0243] Hash to integer: Are the functions that convert a hexadecimal hash to a big integer (CONV, TO_NUMBER, etc.) behaving the same?

[0244] Bit XOR aggregation: Does BIT_XOR (or equivalent implementation) produce the same result on the same set of numbers?

[0245] Phase 2 (content hash) verification:

[0246] Data type to string: Are the output formats of CAST(balance AS CHAR) and balance::text consistent? Especially for floating point numbers.

[0247] Time formatting: Can DATE_FORMAT and to_char produce exactly the same ISO8601 format string?

[0248] JSON construction: Are the ordering of keys and the handling of NULL values consistent between JSON_OBJECT and json_build_object? (This is the reason why we later tend to use JSON_ARRAY, because it is inherently ordered and avoids the risk of key ordering).

[0249] Based on the above embodiments, as Figure 2 shown, in another aspect, the present application provides a data consistency verification system for a long-distance heterogeneous database, for implementing the method described above, the system comprises a coordination end, a source database and a target database connected by network communication, wherein:

[0250] The source database and the target database are configured to: receive IBLT parameters from the coordination end, execute SQL scripts to generate respective IBLT digest tables and shard checksums;

[0251] The coordination end is configured to distribute IBLT parameters, receive the IBLT digest table and perform a peel algorithm to decode existence differences and determine a common primary key set, receive the shard checksums and perform a comparison, and perform a recursive localization when inconsistencies are found, ultimately generating a difference report.

[0252] As a preferred embodiment of the present application, the coordination end is further configured to instruct the source database and the target database to perform a hierarchical hash check directly on the corresponding data range when the peel algorithm decoding fails.

[0253] Figure 3 is a structural schematic diagram of an electronic device provided by an embodiment of the present application, as Figure 3 shown, the electronic device 410 can include a first processor 2001.

[0254] Optionally, the electronic device 410 can further include a memory 2002 and a transceiver 2003.

[0255] The first processor 2001, the memory 2002, and the transceiver 2003 can be connected through a communication bus.

[0256] The following will be specifically introduced to each component of the electronic device 410: Figure 3

[0257] The first processor 2001 is the control center of the electronic device 410, which can be one processor or a plurality of processing elements. For example, the first processor 2001 is one or more central processing units (CPU), application specific integrated circuits (ASIC), or one or more integrated circuits configured to implement the embodiments of the present application, such as one or more microprocessors (digital signal processor, DSP), or one or more field programmable gate arrays (FPGA).

[0258] Optionally, the first processor 2001 can execute various functions of the electronic device 410 by running or executing software programs stored in the memory 2002 and calling data stored in the memory 2002.

[0259] In a specific implementation, as an embodiment, the first processor 2001 can include one or more CPUs, such as the CPU0 and CPU1 shown in Figure 3 . ​

[0260] In a particular implementation, as one embodiment, the electronic device 410 can also include multiple processors, such as the first processor 2001 and the second processor 2004 shown in FIG. 20. Figure 3 Each of these processors can be a single-CPU or a multi-CPU. The processor herein can refer to one or more devices, circuits, and / or processing cores for processing data (e.g., computer program instructions).

[0261] The memory 2002 is configured to store software programs for implementing the solutions of the present application, and the first processor 2001 is configured to control the execution of the software programs. The implementation manners can refer to the above-mentioned method embodiments, and will not be described here.

[0262] Optionally, the memory 2002 can be a read-only memory (ROM) or other type of static storage device that can store static information and instructions, a random access memory (RAM) or other type of dynamic storage device that can store information and instructions, an electrically erasable programmable read-only memory (EEPROM), a compact disc read-only memory (CD-ROM) or other optical disk storage, a magnetic disk storage medium or other magnetic storage device, or any other medium that can be used to carry or store desired program code in the form of instructions or data structures and that can be accessed by a computer, but is not limited to this. The memory 2002 can be integrated with the first processor 2001 or exist independently and be coupled to the first processor 2001 through an interface circuit (not shown in FIG. 20) of the electronic device 410. The embodiments of the present application do not make a specific limitation here. Figure 3

[0263] The transceiver 2003 is configured to communicate with a network device or a terminal device.

[0264] Optionally, the transceiver 2003 can include a receiver and a transmitter (not shown in FIG. 20 separately). The receiver is configured to implement the receiving function, and the transmitter is configured to implement the transmitting function. Figure 3

[0265] Optionally, the transceiver 2003 can be integrated with the first processor 2001 or exist independently and be coupled to the first processor 2001 through an interface circuit (not shown in FIG. 20) of the electronic device 410. The embodiments of the present application do not make a specific limitation here. Figure 3 ​​The first processor 2001 is coupled with the electronic device 410 (not shown in the figure), and embodiments of the present application do not make a specific limitation thereon.

[0266] It should be noted that, Figure 3 The structure of the electronic device 410 shown in the figure does not constitute a limitation on the router, and an actual knowledge structure identification device can include more or fewer components than those shown in the figure, or combine certain components, or different component arrangements.

[0267] In addition, the technical effects of the electronic device 410 can refer to the technical effects of the XXX method described in the above method embodiments, which will not be described here.

[0268] It should be understood that the first processor 2001 in embodiments of the present application can be a central processing unit (CPU), and the processor can also be other general-purpose processors, digital signal processors (DSPs), application specific integrated circuits (ASICs), field programmable gate arrays (FPGAs) or other programmable logic devices, discrete gates or transistor logic devices, discrete hardware components, etc. The general-purpose processor can be a microprocessor or the processor can also be any conventional processor.

[0269] It should also be understood that the memory in the embodiments of the present application can be volatile or nonvolatile memory, or can include both volatile and nonvolatile memory. The nonvolatile memory can be read-only memory (ROM), programmable ROM (PROM), erasable PROM (EPROM), electrically EPROM (EEPROM), or flash memory. The volatile memory can be random access memory (RAM) used as external cache. By way of example, and not limitation, many forms of random access memory (RAM) are available, such as static random access memory (SRAM), dynamic random access memory (DRAM), synchronous dynamic random access memory (SDRAM), double data rate SDRAM (DDR SDRAM), enhanced SDRAM (ESDRAM), synchlink DRAM (SLDRAM), and direct rambus RAM (DR RAM).

[0270] The above-described embodiments can be implemented in whole or in part by software, hardware (such as a circuit), firmware, or any combination thereof. When implemented in software, the above-described embodiments can be implemented in the form of a computer program product. The computer program product includes one or more computer instructions or computer programs. When the computer instructions or computer programs are loaded or executed on a computer, the processes or functions described in the embodiments of the present application are wholly or partially generated. The computer can be a general-purpose computer, a special-purpose computer, a computer network, or other programmable devices. The computer instructions can be stored in a computer-readable storage medium or transferred from one computer-readable storage medium to another computer-readable storage medium, for example, the computer instructions can be transferred from one website, computer, server, or data center to another website, computer, server, or data center through a wired (such as infrared, wireless, microwave, etc.) manner. The computer-readable storage medium can be any available medium that can be accessed by a computer or a data storage device such as a server, data center, etc. containing one or more available medium collections. The available medium can be a magnetic medium (such as a floppy disk, a hard disk, a magnetic tape), an optical medium (such as a DVD), or a semiconductor medium. The semiconductor medium can be a solid-state disk.

[0271] It should be understood that the term "and / or" herein merely describes the association relationship of the associated objects, which means that there can be three relationships, for example, A and / or B can represent the following three cases: A exists alone, A and B exist together, and B exists alone, where A and B can be singular or plural. In addition, the character " / " herein generally represents that the associated objects before and after it are in an "or" relationship, but it can also represent an "and / or" relationship, which can be understood according to the context before and after it.

[0272] In the present application, "at least one" means one or more, and "multiple" means two or more. "At least one of the following" or the like means any combination of the items, including any combination of single or multiple items. For example, at least one of a, b, or c can represent a, b, c, a-b, a-c, b-c, or a-b-c, where a, b, and c can be single or multiple.

[0273] It should be understood that in various embodiments of the present application, the size of the sequence number of the above-described processes does not mean the order of execution, and the execution order of the processes should be determined according to their functions and internal logic, and should not constitute any limitation on the implementation process of the embodiments of the present application.

[0274] Those skilled in the art can clearly understand that the units and algorithm steps of each example described in combination with the embodiments disclosed herein can be realized by electronic hardware or a combination of computer software and electronic hardware. Whether the functions are performed in hardware or software depends on the specific application and design constraints of the technical solution. Those skilled in the art can use different methods to implement the described functions for each specific application, but such implementation should not be considered beyond the scope of the present application.

[0275] Those skilled in the art can clearly understand that, for the convenience and brevity of the description, the specific working processes of the devices, apparatuses and units described above can refer to the corresponding processes in the foregoing method embodiments, which will not be repeated here.

[0276] In several embodiments provided by the present application, it should be understood that the disclosed devices, apparatuses and methods can be implemented in other ways. For example, the apparatus embodiments described above are merely schematic, for example, the division of the units is only a logical function division, and actual implementation can have another division manner, for example, multiple units or components can be combined or integrated into another device, or some features can be ignored or not executed. In addition, the coupling or direct coupling or communication connection between the units shown or discussed can be indirect coupling or communication connection through some interfaces, devices or units, which can be electrical, mechanical or other forms.

[0277] The units described as separate components can or can not be physically separated, and the components shown as units can or can not be physical units, that is, they can be located in one place, or can be distributed on multiple network units. Part or all of the units can be selected according to actual needs to achieve the purpose of the embodiment scheme.

[0278] In addition, each functional unit in each embodiment of the present application can be integrated into a processing unit, or each unit can exist physically independently, or two or more units can be integrated into one unit.

[0279] If the functions are realized in the form of software function units and sold or used as independent products, they can be stored in a computer readable storage medium. Based on this understanding, the technical solutions of the present application or the parts of the present application that essentially contribute to the prior art or the parts of the technical solutions can be embodied in the form of software products. The computer software product is stored in a storage medium and includes a plurality of instructions for causing a computer device (which can be a personal computer, a server, or a network device, etc.) to execute all or part of the steps of the method described in the various embodiments of the present application. The aforementioned storage medium includes a U disk, a mobile hard disk, a read-only memory (ROM), a random access memory (RAM), a magnetic disk or an optical disk, and various media that can store program codes.

[0280] The above is only a specific implementation of the present application, but the protection scope of the present application is not limited thereto. Any person skilled in the art can easily think of changes or replacements within the technical range disclosed by the present application, which should be covered within the protection scope of the present application. Therefore, the protection scope of the present application should be subject to the protection scope of the claims.

Claims

1. A method for checking data consistency of heterogeneous ends at a long distance, characterized in that, The method comprises: S1, existence verification a) defining the parameters of the reversible Bloom lookup table IBLT at the coordination end, and distributing to the source database and the target database; b) executing SQL scripts in the source database and the target database respectively, generating respective IBLT digest tables by performing standardized hash calculation on the primary keys of each record, and the digest table contains bucket ID, record count and XOR sum of primary keys; c) transmitting the respective IBLT digest tables of the source database and the target database to the coordination end, and decoding the primary key list only existing in the source database and the primary key list only existing in the target database by the coordination end through the difference calculation and stripping algorithm, and determining the primary key records common to both parties to form the common primary key set; S2, content verification d) based on the hierarchical hash verification, the content consistency of the records in the common primary key set is verified, and if the content inconsistent record is verified, a difference report is output; The method based on hierarchical hash verification comprises: Dividing the common primary key set into multiple shards; In the source database and the target database, the records in each shard are generated by encapsulating the columns to be compared into JSON strings in a fixed order and format and calculating the hash value, and the "row content fingerprint" of each record in the shard is calculated and the checksum of each shard is calculated; The coordination end compares the checksums of the shards, and for the "dirty shard" with inconsistent checksums, the shard is recursively divided into smaller sub-shards and the checksums are compared again until the specific record with inconsistent content is accurately located.

2. The method for data consistency check of remote heterogeneous ends according to claim 1, characterized in that, In step c), if the stripping algorithm fails to decode due to the number of difference primary keys exceeding the expected number, the hierarchical hash verification method described in step d) is automatically used to compare the corresponding data range, and an alarm is issued.

3. A system for data consistency check between remote heterogeneous ends for implementing the method of any one of claims 1 to 2, characterized in that, The system comprises a coordination end, a source database and a target database connected by network communication, wherein: The source database and the target database are configured to receive IBLT parameters from the coordination end, execute SQL scripts to generate respective IBLT digest tables and shard checksums; The coordination end is configured to distribute IBLT parameters, receive the IBLT digest tables and execute the stripping algorithm to decode the existence difference and determine the common primary key set, receive the shard checksums and compare them, and recursively locate when inconsistencies are found, and finally generate a difference report.

4. The system for data consistency check between heterogeneous ends at a long distance according to claim 3, wherein, The coordination end is also configured to instruct the source database and the target database to directly perform hierarchical hash verification on the corresponding data range when the stripping algorithm fails to decode.

5. An electronic device, comprising: The electronic device comprises: a processor; a memory, the memory having computer readable instructions stored thereon, the computer readable instructions being executed by the processor to implement the method of any one of claims 1 to 2.

6. A computer readable storage medium, characterized in that, The computer readable storage medium stores program code, which can be called and executed by the processor to implement the method of any one of claims 1 to 2.

Images