Histogram splitting comparison method, device and system based on data distribution perception

Abstract

The invention discloses a histogram splitting comparison method, device and system based on data distribution perception and a medium. The method comprises the following steps: taking one end, supporting a histogram function, in a source end database table and a destination end database table as a splitting end; analyzing the split columns according to the data distribution characteristics of the database table of the splitting end, and performing segment splitting on the columns determined to be split based on a histogram; dynamically combining or expanding the split sections according to the data volume of the split end database table; according to the data splitting method and device, the splitting segments are decoded and assembled, and the assembled data segments are issued for multi-thread parallel processing, so that efficient and accurate splitting of the data is realized, and the accuracy and efficiency of data comparison are remarkably improved.

Description

Technical Field

[0001] The present invention relates to data migration technology, and in particular to a histogram splitting and comparison method, device, system and medium based on data distribution perception. Background Art

[0002] With the rapid development of information technology and the continuous upgrading of application systems, the amount of data has shown explosive growth. Data interaction between systems has become increasingly frequent, making data migration an indispensable part of information system integration. Especially in the context of the increasing popularity and normalization of data migration between homogeneous and heterogeneous databases, it is crucial to quickly verify the consistency of source and destination data before and after migration. In this process, data splitting, as the core link of data comparison, is of great importance. An excellent data splitting strategy can directly affect the accuracy and efficiency of subsequent comparison results.

[0003] Data splitting is the process of breaking down a large data set into multiple, manageable, small data subsets. It plays a vital role in data comparison. First, data splitting can greatly reduce the computational burden and time consumption of a single comparison, thereby significantly improving the efficiency of the comparison. When a large data set is divided into multiple small data sets, these subsets can be processed in parallel, greatly speeding up the comparison. Second, data splitting helps simplify the comparison process and reduce the risk of errors and conflicts. By breaking down complex data comparison tasks into multiple small tasks, the comparison process can be made clearer, more orderly, and easier to manage and monitor.

[0004] However, although data splitting plays a key role in data comparison, existing splitting methods still have certain shortcomings. Most splitting methods rely on fixed rules or preset thresholds and lack sufficient flexibility and adaptability. These methods are often difficult to dynamically adjust according to the actual distribution and characteristics of the data, resulting in unsatisfactory splitting results, which in turn affects the accuracy and efficiency of the comparison.

[0005] In addition, existing methods often ignore the correlation and dependency between data. In the splitting process, the intrinsic connection between data may be destroyed, resulting in redundancy or conflict between the split data subsets, which increases the complexity and difficulty of comparison.

[0006] Finally, data integrity and consistency are often not given enough attention in existing data splitting methods. Possible data loss or corruption during the splitting process will have a serious impact on the comparison results. Summary of the invention

[0007] In view of the above-mentioned deficiencies in the prior art, an object of the present invention is to provide a histogram splitting and comparison method, device, system and medium based on data distribution perception, which can improve the accuracy and efficiency of data splitting when splitting data.

[0008] The technical solution of the present invention is as follows:

[0009] A histogram splitting and comparison method based on data distribution perception includes:

[0010] Use the end of the source database table and the destination database table that supports the histogram function as the split end;

[0011] Analyze the split columns according to the data distribution characteristics of the database table at the split end, and split the determined split columns into segments based on the histogram;

[0012] Dynamically merge or expand split segments according to the amount of data in the split-end database table;

[0013] The split segments are decoded and assembled, and the assembled data segments are sent down for multi-threaded parallel processing.

[0014] In one embodiment, before the step of using the end of the source database table and the target database table that supports the histogram function as the split end, the method further includes:

[0015] Migrate all data in the source database to the target database, and determine whether the source database table and / or the target database table support the histogram function.

[0016] In one embodiment, the step of analyzing the split columns according to the data distribution characteristics of the database table at the split end, and performing segment splitting on the determined split columns based on the histogram includes:

[0017] Perform data analysis on the database table at the split end to obtain metadata information of the database table and data distribution characteristics of the histogram;

[0018] Determine whether to specify a split column;

[0019] When specifying a split column, estimate the amount of data in the database table and dynamically adjust the number of segments to split based on the amount of data;

[0020] When the split column is not specified, the optimal split column is obtained based on metadata information and data distribution characteristics.

[0021] In one embodiment, the method for obtaining the optimal split column includes:

[0022] If the database table is identified as a single primary key table, the primary key column is selected as the split column;

[0023] If the database table is identified as a joint primary key table, the column with the highest discrimination is selected as the split column according to the discrimination of each column in the joint primary key table;

[0024] If the database table is identified as an index table, the histogram splitting of each index column is determined one by one, and the most suitable index column is selected as the splitting column according to the characteristics of the histogram;

[0025] If the database table is identified as a "three-no" table, manually specify the split columns through the interface.

[0026] In one embodiment, the step of dynamically merging or expanding the split segments according to the amount of data in the split-end database table includes:

[0027] Estimate the data size of the split-end database table;

[0028] Based on the estimated data volume and the number of segments, segment intervals are dynamically split or merged to form segments.

[0029] In one embodiment, the step of decoding and assembling the split segments and sending the assembled data segments for multi-threaded parallel processing includes:

[0030] Generate corresponding source and destination SQL query statements for each split segment;

[0031] Concurrently executing the SQL query statements to obtain data of corresponding data segments from the source end and the destination end;

[0032] Compare the acquired data and identify the difference data;

[0033] The difference data is sent to the difference data node for repair.

[0034] In one embodiment, before the step of performing data analysis on the database table at the split end to obtain metadata information of the database table and data distribution characteristics of the histogram, the step further includes:

[0035] Specify one or more columns in the database table on the split side;

[0036] Use the ANALYZE TABLE statement to create a histogram for the specified column, so that the database table data at the split end is divided into several continuous data intervals.

[0037] A histogram splitting and comparison device based on data distribution perception, comprising:

[0038] A split end selection module is used to use the end of the source database table and the target database table that supports the histogram function as the split end;

[0039] A data analysis module, used to analyze the split columns according to the data distribution characteristics of the database table at the split end, and to segment the determined split columns based on the histogram;

[0040] Dynamic split module, used to dynamically merge or expand split segments according to the amount of data in the split-end database table;

[0041] The data processing module is used to decode and assemble the split segments, and send the assembled data segments for multi-threaded parallel processing.

[0042] A histogram splitting and comparison system based on data distribution perception, the system comprising at least one processor; and

[0043] a memory communicatively connected to the at least one processor; wherein,

[0044] The memory stores instructions that can be executed by the at least one processor, and the instructions are executed by the at least one processor so that the at least one processor can perform the above-mentioned histogram splitting and comparison method based on data distribution perception.

[0045] A non-volatile computer-readable storage medium stores computer-executable instructions. When the computer-executable instructions are executed by one or more processors, the one or more processors can execute the above-mentioned histogram splitting and comparison method based on data distribution perception.

[0046] Beneficial effects: The present invention discloses a histogram splitting and comparison method based on data distribution perception, which realizes efficient and accurate data splitting, thereby significantly improving the accuracy and efficiency of data comparison. BRIEF DESCRIPTION OF THE DRAWINGS

[0047] The present invention will be further described below with reference to the accompanying drawings and embodiments, in which:

[0048] Figure 1 A flow chart of a histogram splitting and comparison method based on data distribution perception provided by an embodiment of the present invention;

[0049] Figure 2 A logical diagram of data splitting in a histogram splitting and comparison method based on data distribution perception provided in an embodiment of the present invention;

[0050] Figure 3 A flow chart of step S200 in the histogram splitting and comparison method based on data distribution perception provided by an embodiment of the present invention;

[0051] Figure 4 A flow chart of step S300 in the histogram splitting and comparison method based on data distribution perception provided by an embodiment of the present invention;

[0052] Figure 5 A flow chart of step S400 in the histogram splitting and comparison method based on data distribution perception provided by an embodiment of the present invention;

[0053] Figure 6 A schematic diagram of data splitting, verification and comparison in a histogram splitting and comparison method based on data distribution perception provided in an embodiment of the present invention;

[0054] Figure 7 A schematic diagram of functional modules of a histogram splitting and comparison device based on data distribution perception provided by an embodiment of the present invention;

[0055] Figure 8 A schematic diagram of the hardware structure of a histogram splitting and comparison system based on data distribution awareness provided in an embodiment of the present invention. DETAILED DESCRIPTION

[0056] The present invention focuses on the problem of cross-database data migration, and mainly realizes the accurate comparison of the full amount of data between the source database and the destination database. Data splitting is a key step in the early stage of data comparison, and its importance is self-evident. The quality of data splitting will be directly related to the accuracy and efficiency of subsequent comparison results. Therefore, data splitting involves multiple key technologies and methods, aiming to ensure the comparability, consistency and accuracy of data, thereby providing a reliable basis for subsequent data comparison.

[0057] Existing data splitting methods mainly rely on rule-based splitting algorithms, which require in-depth understanding of the data and pre-define all possible comparison rules, which often face huge challenges in practical applications. The limitations of this method are particularly obvious when dealing with massive data and complex data distribution. Moreover, once the data source structure changes or there are differences in data quality, these pre-defined rules may lose their effectiveness, resulting in inaccurate comparison results or ineffective execution.

[0058] In view of this, the present invention aims to overcome the limitations of the prior art and provide a more efficient, flexible and adaptable data splitting method to optimize the full data comparison after the cross-database data migration is completed. The present invention can cleverly utilize the characteristics of the histogram, deeply analyze the distribution of the data, and accurately split the table data. At the same time, new segments can be dynamically merged or expanded according to the split data segments, thereby achieving efficient and accurate data verification. Therefore, the present invention can greatly improve the efficiency of data comparison, while also significantly reducing the risk of data differences being ignored, providing a strong guarantee for the maintenance of data consistency, and can provide a more efficient, accurate and flexible solution for the field of cross-database data migration.

[0059] In order to make the purpose, technical solution and effect of the present invention clearer and more specific, the present invention is further described in detail below. It should be understood that the specific embodiments described herein are only used to explain the present invention and are not intended to limit the present invention. The embodiments of the present invention are described below in conjunction with the accompanying drawings.

[0060] In the field of cross-database data migration, especially in the comparison of full data, the present invention proposes a histogram splitting and comparison method based on data distribution perception, which can achieve efficient and accurate data splitting, thereby significantly improving the accuracy and efficiency of data comparison. Figure 1 , Figure 2 and Figure 3 As shown, the histogram splitting and comparison method based on data distribution perception of the present invention includes the following steps:

[0061] S100: Use the end of the source database table and the target database table that supports the histogram function as the split end.

[0062] In this embodiment, the histogram splitting and comparison method based on data distribution awareness can be used in data comparison scenarios between homogeneous or heterogeneous databases, and can achieve full consistency verification of the source database and the destination database.

[0063] In order to give full play to the advantages of the present invention, one end of the database (i.e., the source end database table and the destination end database table) needs to support the histogram function, so as to ensure the accuracy and efficiency of data splitting, and to provide users with a more reliable and efficient data comparison experience in practical applications. At present, most databases support the histogram function. If the source end and the destination end do not support the histogram function, it will be automatically detected and a prompt will be given. At this time, the technician can manually operate it.

[0064] S200: Analyze the split columns according to the data distribution characteristics of the database table at the split end, and split the columns determined to be split into segments based on a histogram.

[0065] In one embodiment, after the migration of the full amount of data is completed, a comparison and verification phase is performed. In this phase, a column can be specified for segment splitting for a specific table. If the split column is not specified, the present invention will automatically perform an in-depth analysis based on the structure of the split-end database table to find the optimal column for segment splitting.

[0066] S300. Dynamically merge or expand the split segments according to the data volume of the split-end database table.

[0067] After finding a suitable split column in step S200, the data volume of the database table is further estimated to ensure the rationality and efficiency of the split. According to the estimated data volume, the present invention dynamically adjusts the number of split segments to reduce the number of interactions with the database and improve the query comparison efficiency.

[0068] S400: Decode and assemble the split segments, and send the assembled data segments for multi-threaded parallel processing.

[0069] After the data segments are split after step S300 is completed, since the character type columns will be encrypted during the histogram processing, it is necessary to decrypt (i.e., decode) these encrypted segments. The decrypted data segment is a continuous data block, which contains important information, especially the start and end positions of the segment, which is crucial to ensure the integrity and closure of the data segment.

[0070] In order to ensure the integrity and closedness of data segments and avoid cross-coupling between data segments, the present invention adopts segment data splitting and assembly to ensure that each data segment is independent and complete, and there is no overlap or omission between each other, so as to avoid the occurrence of duplicate data during the verification and comparison process, thereby improving the accuracy of the comparison.

[0071] exist Figure 2 The main parts of the histogram splitting and comparison method of the present invention are shown: split column analysis, merging and expansion, and assembly and distribution. Each part undertakes a specific task to ensure the accuracy and efficiency of data splitting.

[0072] In an optional embodiment, before step S100, the method further includes: migrating all data in the source database to the target database, and determining whether the source database table and / or the target database table supports a histogram function.

[0073] The present invention can use a data migration tool, such as the sloth migration tool of the GoldenDB database, to smoothly and efficiently migrate all the data in the source database to the target database.

[0074] Then, the comparison and inspection task is issued through the data verification operation page. At this step, the user has a certain degree of flexibility and can choose to specify the split columns of the table, or choose not to specify and let the system make its own judgment.

[0075] Next, after receiving the comparison and verification instruction, the data splitting module will immediately start working. It will first check which end of the source and target ends supports the histogram function. The end that supports the histogram will be selected as the splitting end to optimize the splitting process by using the histogram feature.

[0076] In an optional embodiment, please refer to Figure 3 , the step S200 comprises:

[0077] S201, performing data analysis on the database table at the split end to obtain metadata information of the database table and data distribution characteristics of the histogram;

[0078] S202, determining whether to specify a split column;

[0079] S203, when specifying a split column, estimating the amount of data in the database table, and dynamically adjusting the number of segments to be split according to the amount of data;

[0080] S204: When no split column is specified, an optimal split column is obtained according to metadata information and data distribution characteristics.

[0081] For user-specified split columns, the data splitting module will estimate the size of the data in the database table at the split end, and dynamically adjust the number of segments to achieve the best splitting effect based on this size. For cases where no split columns are specified, the module will intelligently query the metadata information of the database table at the split end, and determine whether to use primary key splitting, common index column splitting, or joint primary key splitting based on this metadata information to obtain the optimal split column, and create a histogram based on the data distribution characteristics.

[0082] Furthermore, in step S200, the method for obtaining the optimal split column includes:

[0083] If the database table is identified as a single primary key table, the primary key column is selected as the split column;

[0084] If the database table is identified as a joint primary key table, the column with the highest discrimination is selected as the split column according to the discrimination of each column in the joint primary key table;

[0085] If the database table is identified as an index table, the histogram splitting of each index column is determined one by one, and the most suitable index column is selected as the splitting column according to the characteristics of the histogram;

[0086] If the database table is identified as a "three-no" table, manually specify the split columns through the interface.

[0087] For a single primary key table, the processing logic is similar to that of the specified column, and the splitting task can be completed efficiently. For a single primary key table, since its primary key is unique and highly distinguishable, this column can be accurately located through metadata. In this case, selecting the primary key column for segment splitting is the most reasonable choice.

[0088] However, the processing of the joint primary key table is relatively more complicated, because the joint primary key may be composed of multiple columns, and the discrimination of these columns may be different. For those columns with low discrimination, simply relying on them for splitting may not achieve the desired effect. Therefore, this embodiment comprehensively considers all columns in the joint primary key, and may even need to rely on the subsequent columns for supplementary splitting to improve the efficiency and accuracy of the comparison.

[0089] In the specific implementation process, if the first column of the joint primary key table has a high degree of distinction, the processing logic is similar to that of a single primary key, and it can be directly split. However, in actual scenarios, the first column of the joint primary key table often has insufficient distinction. In this case, the second column needs to be further split, and the segment size needs to be dynamically adjusted according to the characteristics of the second column, and so on, until a split column that meets the conditions is found.

[0090] Suppose a joint primary key table has 4 columns, of which the first 2 columns have low discrimination, the first column has 10 different values, the second column has 20 different values, but the third column has basically all different values. The histogram will return the interval value range and the approximate number of interval values ​​for each column split in turn, so that the approximate amount of data in each interval can be calculated, and the actual experience value can also be combined to determine the appropriate amount of data for each interval, so that a more ideal split interval can be obtained.

[0091] For the above situation, the present invention can merge the data in the first column and the second column. The first column can be merged into 3 interval values, and the second column can be merged into 5 interval values. For example, the different values ​​of the first column are {1, 2, 3, 4, 5, 6, 7, 8, 9, 10}, then the difference method can be used to take 3 intervals [1-3], [4-6], [7-10], and the second column can also be split into 5 intervals in the same way. The third column is 100 intervals, so the total number of intervals obtained is 3×5×100=1500 intervals.

[0092] In the case where there are multiple common index tables, the discrimination of each index needs to be judged one by one, so the data analysis module needs to conduct an in-depth analysis of the index structure of the table, and select the optimal index as the split column by comparing the discrimination of different indexes. That is, the data analysis module will judge the histogram splitting of each index column in turn, and select the most appropriate index column for splitting according to the characteristics of the histogram.

[0093] For the columns of the common index table, there may also be a situation where the index discrimination is not high, such as a table named test, and there are 3 common indexes, namely a, b, and c. The present invention will construct a histogram for each index column in turn, and judge whether the column is suitable as the optimal split column according to the number of intervals returned by the histogram. Each column of the histogram can split out 1024 interval values ​​at most. If the test table has 500w (w means ten thousand) data, and the a column has a low discrimination, such as 10 different values, then 10 intervals are split out, each interval has 50w data, and the data volume of this interval is relatively large, and if most of the values ​​of the c interval are different, the histogram will split out 1024 intervals, then the value of each interval is only 5w, which will significantly improve the performance of the program and the security of the memory. Since the index table has created a histogram, it can provide data distribution information based on the histogram, and can select the execution plan with the lowest cost and the highest efficiency, thereby improving the efficiency of subsequent queries.

[0094] In the embodiment of the present invention, the "three-no tables" refer to database tables that have neither primary keys nor indexes. For such tables, due to the lack of obvious identification and structural characteristics, the best splitting method is to manually specify a column for splitting through the interface. After specifying the split column, the rationality of the column split is judged to ensure that the split data still has practical significance and use value.

[0095] The present invention further improves and optimizes the selection process of split columns through step S300, especially for the joint primary key column. Since the joint primary key is composed of multiple columns, its splitting strategy is more complicated than that of a single primary key. Subsequently, through the steps of merging and expanding, the columns with small amounts of data are merged into split segments, and the split segments with large amounts of data are further split into several new segments. It is possible to more accurately determine which columns are more suitable as split columns, thereby optimizing the splitting effect. After finding suitable split columns and segments, the data assembly module will assemble the segment query statements of the source and destination ends to prepare for subsequent data queries.

[0096] Optionally, before step S201, the method further includes:

[0097] S2001. Specify one or more columns in the database table at the split end;

[0098] S2002. Use the ANALYZE TABLE statement to create a histogram for the specified column, so that the database table data at the split end is divided into several continuous data intervals.

[0099] When building a histogram, you can create a data histogram on a column or several columns, obtain the data distribution characteristics of the histogram, and conduct further in-depth analysis of the distribution characteristics to develop a more accurate splitting strategy. The histogram can divide data according to different ranges or values. It is mainly used to collect and store the distribution statistics of column data and count the amount of data in each range or value. Based on the data distribution information provided by the histogram, the optimizer can select the execution plan with the lowest cost and highest efficiency, which can not only reduce the query response time, but also reduce the database load and resource consumption.

[0100] Histograms can be used to display a visual representation of data distribution. They divide data into continuous intervals or "buckets" and calculate the frequency of data in each interval. Histograms can also be used to optimize query performance by providing the query optimizer with statistical information about data distribution to help it generate more efficient query plans.

[0101] The following uses the MySQL database as an example to explain the process of creating, updating, and deleting a histogram:

[0102] Create / update a histogram:

[0103] Use the ANALYZE TABLE statement to create a histogram. You can specify one or more columns in the database table and create a histogram for these columns. If you do not specify the number of "buckets", MySQL will use 100 buckets by default. The execution statement is: analyze table [table_name] update histogram on [column1, column2...]; / / The default is 100 buckets.

[0104] The present invention can specify the number of buckets of the histogram through the WITH[N]BUCKETS option, allowing the granularity of the histogram to be adjusted as needed, and its execution statement is: analyze table[table_name]update histogram on[column1,column2...]with[N]buckets.

[0105] For example: table t1 has two columns, c1 and c2. When creating a histogram on columns c1 and c2 of table t1, the default 100 buckets are used, and the execution statement is: analyze table t1 update histogram on c1,c2; of course, the present invention can also create a histogram of 10 buckets on columns c1,c2, and the execution statement is: analyze table t1 updatehistogram on c1,c2 with 10buckets; the present invention can also create a histogram of 10 buckets on columns c1,c2,c3, and the execution statement is: analyze table t1 update histogram on c1,c2,c3 with10buckets; the present invention can also update a histogram of 100 buckets on column c1, and the default 100 buckets can be used, and the execution statement is: analyze table t1 update histogram on c1.

[0106] Delete the histogram:

[0107] The present invention can use the ANALYZE TABLE statement in combination with the DROP HISTOGRAM option to delete the histogram on the specified column, and the execution statement is: analyze table [table_name] drop histogram on [column1, column2].

[0108] Take the t1 table with two columns c1 and c2 as an example:

[0109] If you delete the histogram from the c1 and c2 columns of the t1 table, the execution statement is: analyze table t1 drop histogram on c1,c2; if you delete the histogram from the c1, c2 and c3 columns of the t1 table, the execution statement is: analyze tablet1 drop histogram on c1,c2,c3; if you only delete the histogram from the c1 column of the t1 table and keep the histogram on the c2 column, the execution statement is: analyze table t1 drop histogram on c1.

[0110] Please also read Figure 4 In the histogram splitting and comparison method based on data distribution perception of the present invention, step S300 includes:

[0111] S301, estimating the data volume of the split-end database table;

[0112] S302: Dynamically split or merge segment intervals to form split segments according to the estimated data volume and the number of split segments.

[0113] Splitting and dynamic merging are mainly aimed at the case of joint primary key tables. When the column distinction of the joint primary key table is not high, you cannot split it based on only one column. If you do so, it will result in a large number of data rows in the interval.

[0114] For example, a test table has a joint primary key table with 50 million data volumes. The table has four joint primary key columns (a, b, c, d). However, the first and second columns have low distinction, with only 10 different values. If the intervals are split according to the first column, 10 intervals will be obtained. The average number of rows in each interval is about 5 million. This subsequent query volume is too large, which not only slows down the performance but also easily causes memory overflow.

[0115] Therefore, the data analysis module will continue to check column b. If it is found that the discrimination of column b is still not high, it will check column c. It is found that column c can split the largest number of intervals of the histogram, 1024, so this column is suitable as the final split column, so the total number of split intervals is 10×10×1024=102400, but so many intervals are also very large, so it is necessary to merge and process, and try to control the number of intervals to be reasonable, not too large or too small. The present invention can optimize the first and second columns, merge 10 intervals into 3 intervals, so the final number of intervals can be 3×3×1024, and the number of intervals is reduced by 10 times.

[0116] Furthermore, merging and extending the split segments include:

[0117] S3031. For a database table at a split end whose data volume is lower than a set value, if the number of split segments is too large, merge some of the split segments to reduce the number of interactions with the database;

[0118] S3032. For a database table at a split end whose data volume exceeds a set value, if the number of split segments is insufficient to meet the query efficiency requirement, further split some data segments to increase the number of segments.

[0119] In step S3031, for the scenario of merging segments, for example, when the amount of data in a table is small (such as only 10,000 rows), but according to the histogram splitting method, it is split into 100 segments by default, and the maximum number of segments can reach 1024 segments, which is obviously unreasonable. In this case, the present invention will merge these segments to reduce the number of interactions with the database. Especially when performing a full comparison of multiple tables, the effect of merging segments is particularly significant, which can significantly improve the comparison efficiency.

[0120] However, in step S3032, for a table with a huge amount of data (such as more than 10 million or 100 million), the splitting of 1024 segments may not be enough. Because each segment contains too many rows, the query may time out. Therefore, the present invention will further split these segments to ensure the efficiency and accuracy of the query. Therefore, for the columns of the joint primary key table, due to its particularity, more columns may need to be considered for splitting. This requires a comprehensive judgment based on factors such as the distinction of each column of the joint primary key and the amount of data to formulate a more reasonable splitting strategy.

[0121] For example, when splitting a large table of 100 million or even 1 billion data, the histogram can only be split into 1024 intervals at most. For such a large table, the number of rows in the interval will be very large, so the interval needs to be further split and expanded to 1024×5 or 1024×10. In this way, each interval will be expanded into 5 small intervals or 10 small intervals, and the number of each interval after expansion will reach a reasonable value.

[0122] In general, the process of merging and expanding is a further improvement and optimization of the split column analysis. It can dynamically adjust the number of split segments according to the data volume of the table to reduce the number of database interactions and improve the query and comparison efficiency. At the same time, it can also handle special situations (such as segment merging of small data tables and segment splitting of large data tables) to ensure the rationality and efficiency of the split.

[0123] In one embodiment, see Figure 5 , the step S400 comprises:

[0124] S401, generating corresponding SQL query statements for the source and destination ends for each split segment;

[0125] S402, concurrently executing the SQL query statements to obtain data of corresponding data segments from the source end and the destination end;

[0126] S403, comparing the acquired data to identify difference data;

[0127] S404: Send the difference data to the difference data node for repair.

[0128] After the segment data is re-split and recombined into independent intervals, the data processing module generates corresponding source and target SQL query statements for each segment. These SQL query statements are generated based on the start and end positions of each segment and the database table structure of the split end, and are used to extract the corresponding data from the database. The generated SQL query statements are sent to the lower-level process for multi-trip concurrent processing to improve the speed and efficiency of data processing.

[0129] In one embodiment, in S401, the method for generating the QL query statement includes: constructing a corresponding SQL query statement according to the starting value and the ending value of each data segment, ensuring that the SQL query statement can accurately extract data of the corresponding data segment from the source and target databases.

[0130] In one embodiment, in step S400, the method of sending the assembled data segments for multi-threaded parallel processing includes: using multi-threading or multi-process technology to simultaneously execute SQL query statements of multiple data segments to ensure that each query task can be executed independently without interfering with each other.

[0131] In one embodiment, step S403 includes: using a hash algorithm or other fast comparison technology to compare the data segments obtained by the source end and the destination end line by line; recording the comparison results, including the specific location and content of the difference data.

[0132] In one embodiment, step S404 includes: sending the difference data to the difference data node of the big data component; classifying and processing the difference data, such as recording logs, generating reports or triggering a repair process; allowing the user to choose whether to repair the difference data, and providing a re-comparison and verification function after the repair.

[0133] For example, the value of a certain column in a row of table B in database A on the source side is name1, while the value of the column in the corresponding row on the target side is name11. The difference in this row can be identified and stored in Kafka during the comparison process. When repairing, the difference data on Kafka will be pulled down, and it will be determined whether it is table difference data that needs to be repaired. If so, the erroneous data on the target side will be deleted first, and then the correct data on the source side will be reinserted into the target side to complete the repair action.

[0134] Through the above steps, the present invention can ensure that the split data segments have a high degree of integrity and accuracy, providing a solid foundation for subsequent comparison and verification work. At the same time, the multi-threaded concurrent processing method also greatly improves the speed and efficiency of data processing, making the entire data migration and comparison process more efficient and reliable.

[0135] The present invention mainly embodies the following aspects:

[0136] Data distribution-aware splitting strategy: By deeply analyzing the distribution characteristics of the data, a more accurate and flexible data splitting plan is formulated. This not only improves the accuracy of the splitting, but also makes the splitting process more adaptable to the actual situation of the data, avoiding the problems of rule mismatch or data loss that may occur in traditional methods.

[0137] Dynamic merging and expansion mechanism of data segments: In the process of data splitting, a dynamic merging and expansion mechanism is adopted, which allows the split segments to be dynamically merged or expanded according to the characteristics of the split segments, thereby ensuring the continuity and consistency of the data, making the present invention more flexible and efficient, and able to adapt to changes in different data distribution and comparison requirements.

[0138] Application of histogram in data splitting and comparison: By applying database histogram technology to the data splitting and comparison process, by constructing a histogram of the data, the distribution characteristics of the data can be more accurately identified, thereby formulating a more accurate splitting strategy. In addition, the histogram also has the advantages of optimizing query performance, improving index efficiency, and adapting to data changes, making the method of the present invention more feasible and efficient in practical applications.

[0139] Efficient memory management mechanism: By cleverly utilizing the data sorting characteristics and the non-intersection and completeness between segments, an efficient difference reduction mechanism is implemented. Differences are identified in real time during the comparison process, and are quickly reduced and processed immediately, rather than retaining the difference data in the memory for a long time. The real-time reduction method significantly reduces the amount of difference data in the memory, thereby reducing the risk of memory overflow and optimizing the use of memory resources. By reducing the difference data in real time, not only the problem of memory overflow is avoided, but also the pressure of disk I / O is reduced, thereby improving the efficiency of the comparison operation. In addition, the timely processing of difference data also enhances the stability and performance of the entire system, especially when there are many differences between segments. This advantage is particularly prominent.

[0140] See also Figure 6 In order to better understand the technical solution of the present invention, an application example is given below to explain in detail the histogram splitting and comparison method based on data distribution perception of the present invention:

[0141] First, use the data migration tool to smoothly and efficiently migrate all the data in the source database to the target database. Then, through the data verification operation page, you can easily issue the comparison and verification task.

[0142] In this step, users have a certain degree of flexibility and can choose to specify the columns for splitting the table, or they can choose not to specify and let the system make the decision.

[0143] Afterwards, after receiving the comparison and verification instruction, the split end selection module will immediately start working and begin to check which end of the source end and the target end supports the histogram function. The end that supports the histogram will be selected as the split end to use its characteristics to optimize the splitting process.

[0144] For the split columns specified by the user, the data analysis module will estimate the size of the split-end database table data, and dynamically adjust the number of split segments by merging or splitting according to this size to achieve the best splitting effect.

[0145] In the case where no splitting column is specified, the data analysis module will intelligently query the metadata information of the table and determine whether to use primary key splitting, common index column splitting or joint primary key splitting based on the metadata information.

[0146] For a single primary key table, the processing logic is similar to that of the specified column, and the splitting task can be completed efficiently.

[0147] However, the processing of the joint primary key is relatively more complicated. If the first column of the joint primary key has a high degree of differentiation, the processing logic is similar to that of the single primary key, and it can be directly split. However, in actual scenarios, the first column of the joint primary key often has insufficient differentiation. In this case, the dynamic split module will further split the second column and dynamically adjust the segment size according to the characteristics of the second column. This process will be iterated until a split column that meets the conditions is found.

[0148] In the case of multiple common indexes, the dynamic split module will determine the histogram split of each index column in turn, and select the most appropriate index column for splitting according to the characteristics of the histogram. After finding the appropriate split column and segment, the module will assemble the segment query statements of the source and destination ends to prepare for the next step of data query.

[0149] Subsequently, the data query module will concurrently query the result set from the source and destination ends based on the SQL query statement generated in the previous step. This process fully utilizes the parallel processing capability of the system and improves query efficiency.

[0150] Next, the data processing module will perform a detailed verification and comparison of the query results from both ends. Any difference data will be accurately identified and sent down, providing a basis for subsequent difference processing.

[0151] The data processing module will also send the identified difference data to the difference data node of the big data component. Here, the difference data will be classified according to the cause of its generation and the corresponding disk file operation will be performed for subsequent viewing and processing.

[0152] Finally, if there are different data in the comparison results, the present invention can also choose whether to repair these data. The repair operation can be performed on which tables or types of data as needed. After the repair is completed, the comparison task can be run again to ensure that the consistency of the source data and the destination data is effectively guaranteed.

[0153] The full data comparison process not only ensures the accuracy and consistency of the data, but also improves processing efficiency through intelligent splitting and query strategies, providing strong support for data management and application.

[0154] Another embodiment of the present invention provides a histogram splitting and comparison device based on data distribution perception, such as Figure 7 As shown, the device comprises:

[0155] A split end selection module 11 is used to use the end of the source database table and the target database table that supports the histogram function as the split end;

[0156] The data analysis module 12 is used to analyze the split columns according to the data distribution characteristics of the database table at the split end, and to segment the determined split columns based on the histogram;

[0157] A dynamic splitting module 13 is used to dynamically merge or expand split segments according to the data volume of the splitting end database table;

[0158] The data processing module 14 is used to decode and assemble the split segments, and send the assembled data segments for multi-threaded parallel processing.

[0159] The module referred to in the present invention refers to a series of computer program instruction segments that can perform specific functions, which is more suitable for describing the execution process of histogram splitting and comparison based on data distribution perception than a program. For the specific implementation of each module, please refer to the corresponding method embodiment above, which will not be repeated here.

[0160] Another embodiment of the present invention provides a histogram splitting and comparison system based on data distribution perception, such as Figure 8 As shown, the system 10 includes:

[0161] One or more processors 110 and memory 120, Figure 6 A processor 110 is used as an example for description. The processor 110 and the memory 120 may be connected via a bus or other means. Figure 6 The example of connecting through bus is taken in the following.

[0162] The processor 110 is used to complete various control logics of the system 10, and it can be a general-purpose processor, a digital signal processor (DSP), an application-specific integrated circuit (ASIC), a field programmable gate array (FPGA), a single-chip microcomputer, an ARM (Acorn RISC Machine) or other programmable logic device, a discrete gate or transistor logic, a discrete hardware component, or any combination of these components. In addition, the processor 110 can also be any traditional processor, microprocessor or state machine. The processor 110 can also be implemented as a combination of computing devices, for example, a combination of a DSP and a microprocessor, a plurality of microprocessors, one or more microprocessors combined with a DSP and / or any other such configuration.

[0163] The memory 120, as a non-volatile computer-readable storage medium, can be used to store non-volatile software programs, non-volatile computer executable programs and modules, such as program instructions corresponding to the histogram splitting and comparison method based on data distribution perception in the embodiment of the present invention. The processor 110 executes various functional applications and data processing of the system 10 by running the non-volatile software programs, instructions and units stored in the memory 120, that is, the histogram splitting and comparison method based on data distribution perception in the above method embodiment is implemented.

[0164] The memory 120 may include a program storage area and a data storage area, wherein the program storage area may store an operating system and an application required by at least one function; the data storage area may store data created according to the use of the system 10, etc. In addition, the memory 120 may include a high-speed random access memory, and may also include a non-volatile memory, such as at least one disk storage device, a flash memory device, or other non-volatile solid-state storage device. In some embodiments, the memory 120 may optionally include a memory remotely arranged relative to the processor 110, and these remote memories may be connected to the system 10 via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0165] One or more units are stored in the memory 120, and when executed by one or more processors 110, the following steps are implemented:

[0166] Use the end of the source database table and the destination database table that supports the histogram function as the split end;

[0167] Analyze the split columns according to the data distribution characteristics of the database table at the split end, and split the determined split columns into segments based on the histogram;

[0168] Dynamically merge or expand split segments according to the amount of data in the split-end database table;

[0169] The split segments are decoded and assembled, and the assembled data segments are sent down for multi-threaded parallel processing.

[0170] An embodiment of the present invention provides a non-volatile computer-readable storage medium, wherein the computer-readable storage medium stores computer-executable instructions, and the computer-executable instructions are executed by one or more processors, for example, to execute the above-described Figure 1 The method comprises steps S100 to S400.

[0171] As an example, non-volatile storage media can include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable ROM (EEPROM), or flash memory. Volatile memory can include random access memory (RAM) as external cache memory. By way of illustration and not limitation, RAM can be obtained in many forms such as synchronous RAM (SRAM), dynamic RAM, (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), Synchlink DRAM (SLDRAM), and direct Rambus RAM (DRRAM). The disclosed memory components or memories of the operating environment described herein are intended to include one or more of these and / or any other suitable types of memory.

[0172] In summary, compared with the prior art, the method, device, system and medium for histogram splitting and comparison based on data distribution perception of the present invention have beneficial effects mainly in the following aspects:

[0173] First of all, the present invention breaks through the limitations of traditional rule-based data splitting algorithms. Traditional methods usually require an in-depth understanding of the data and require a series of complex comparison rules to be defined in advance, which not only increases the complexity of the operation, but also has particularly prominent limitations when faced with massive data and complex data distribution. Once the data source structure changes or there are differences in data quality, these pre-defined rules may no longer apply, resulting in inaccurate comparison results or ineffective execution. In contrast, the present invention utilizes the characteristics of histograms, which can deeply analyze and understand the distribution of data, thereby achieving more accurate and flexible data splitting. Therefore, there is no need to predefine rules, and it can automatically adapt to various data changes, greatly improving the efficiency and accuracy of data splitting.

[0174] Secondly, in the process of data splitting, the present invention combines the advantages of histograms and dynamically and reasonably divides the data set into several valid segments. These valid segments are not only independent of each other and have no coupling, but also have complete closure, that is, the data inside each segment is complete and will not intersect or overlap with the data of other segments, so that the present invention can make full use of the parallel processing capabilities of multi-threading. In the process of data comparison, since each valid segment is independent, it can be assigned to different threads for parallel processing, and multiple threads can perform comparison operations at the same time, thereby greatly improving the efficiency of table comparison. And since there is no coupling between valid segments, even if a thread encounters problems or exceptions when processing a certain segment, it will not affect the processing of other segments by other threads. This fault-tolerant mechanism further enhances the stability and reliability of the present invention.

[0175] In addition, the present invention also fully considers the memory management problem in the data comparison process. Since each segment may contain a large amount of data, it is impractical to load all the data into the memory for comparison at one time, which will not only lead to a huge consumption of memory resources, but also may cause performance bottlenecks. The present invention utilizes the characteristics of data sorting and the non-intersection between segments to realize an efficient difference reduction mechanism. Specifically, when a segment is compared, the present invention will identify the existing differences in real time, and immediately reduce these differences quickly and send them down for processing, instead of keeping them in the memory all the time, so that the rapid reduction method effectively reduces the amount of difference data in the memory and greatly reduces the risk of memory overflow. At the same time, since the difference data is processed in a timely manner, the efficiency of the comparison operation is further improved.

[0176] When there are too many differences between segments, the advantage of this fast reduction method is particularly obvious. In this case, the traditional memory storage method is likely to cause memory overflow because it cannot accommodate too much difference data. The present invention not only avoids the occurrence of memory overflow by reducing the difference data in real time, but also reduces the pressure of disk I / O (input and output), further improving the stability and performance of the entire system.

[0177] It should be understood that the application of the present invention is not limited to the above examples. For ordinary technicians in this field, improvements or changes can be made based on the above description. All these improvements and changes should fall within the scope of protection of the claims attached to the present invention.

Claims

1. A histogram splitting and comparison method based on data distribution perception, characterized in that: include: Use the end of the source database table and the destination database table that supports the histogram function as the split end; Analyze the split columns according to the data distribution characteristics of the database table at the split end, and split the determined split columns into segments based on the histogram; Dynamically merge or expand split segments according to the amount of data in the split-end database table; The split segments are decoded and assembled, and the assembled data segments are sent down for multi-threaded parallel processing.

2. The histogram splitting and comparison method based on data distribution perception according to claim 1 is characterized in that: Before the step of using the end of the source database table and the target database table that supports the histogram function as the split end, the following steps are also included: Migrate all data in the source database to the target database, and determine whether the source database table and / or the target database table support the histogram function.

3. The histogram splitting and comparison method based on data distribution perception according to claim 1 is characterized in that: The step of analyzing the split columns according to the data distribution characteristics of the database table at the split end, and performing segment splitting on the determined split columns based on the histogram includes: Perform data analysis on the database table at the split end to obtain metadata information of the database table and data distribution characteristics of the histogram; Determine whether to specify a split column; When specifying a split column, estimate the amount of data in the database table and dynamically adjust the number of segments to split based on the amount of data; When the split column is not specified, the optimal split column is obtained based on metadata information and data distribution characteristics.

4. The histogram splitting and comparison method based on data distribution perception according to claim 3 is characterized in that: Methods for obtaining the optimal split column include: If the database table is identified as a single primary key table, the primary key column is selected as the split column; If the database table is identified as a joint primary key table, the column with the highest discrimination is selected as the split column according to the discrimination of each column in the joint primary key table; If the database table is identified as an index table, the histogram splitting of each index column is determined one by one, and the most suitable index column is selected as the splitting column according to the characteristics of the histogram; If the database table is identified as a "three-no" table, manually specify the split columns through the interface.

5. The histogram splitting and comparison method based on data distribution perception according to claim 4 is characterized in that: The step of dynamically merging or expanding the split segments according to the data volume of the split-end database table includes: Estimate the data size of the split-end database table; Based on the estimated data volume and the number of segments, segment intervals are dynamically split or merged to form segments.

6. The histogram splitting and comparison method based on data distribution perception according to claim 1 is characterized in that: The step of decoding and assembling the split segments and sending the assembled data segments for multi-threaded parallel processing includes: Generate corresponding source and destination SQL query statements for each split segment; Concurrently executing the SQL query statements to obtain data of corresponding data segments from the source end and the destination end; Compare the acquired data and identify the difference data; The difference data is sent to the difference data node for repair.

7. The histogram splitting and comparison method based on data distribution perception according to claim 3 is characterized in that: Before the step of performing data analysis on the database table at the split end to obtain metadata information of the database table and data distribution characteristics of the histogram, the following step is also included: Specify one or more columns in the database table on the split side; Use the ANALYZE TABLE statement to create a histogram for the specified column, so that the database table data at the split end is divided into several continuous data intervals.

8. A device for histogram splitting and comparison based on data distribution perception, characterized in that: include: A split end selection module is used to use the end of the source database table and the target database table that supports the histogram function as the split end; A data analysis module, used to analyze the split columns according to the data distribution characteristics of the database table at the split end, and to segment the determined split columns based on the histogram; Dynamic split module, used to dynamically merge or expand split segments according to the amount of data in the split-end database table; The data processing module is used to decode and assemble the split segments, and send the assembled data segments for multi-threaded parallel processing.

9. A histogram splitting and comparison system based on data distribution perception, characterized in that: The system includes at least one processor; and, a memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed by the at least one processor, and the instructions are executed by the at least one processor so that the at least one processor can execute the histogram splitting and comparison method based on data distribution awareness as described in any one of claims 1-7.

10. A non-volatile computer-readable storage medium, characterized in that: The non-volatile computer-readable storage medium stores computer-executable instructions, which, when executed by one or more processors, enable the one or more processors to execute the histogram splitting and comparison method based on data distribution perception as described in any one of claims 1-7.

Images