Method and apparatus for detecting data table consistency

Abstract

The application discloses a kind of data table consistency detection method and device, it is related to computer technical field.The specific embodiment of the method includes: respectively obtaining the file block quantity included in two data tables to be consistency detected, and according to file block quantity, the consistency detection of file directory quantity is carried out;In the case where the consistency detection of file directory quantity passes, respectively obtain the table structure mode of two data tables, and the consistency detection of table structure mode is carried out;In the case where the consistency detection of table structure mode passes, respectively obtain the data row of two data tables, and the consistency detection of data row is carried out;In the case where the consistency detection of data row passes, the data of two data tables is compared row by row, until obtaining consistency detection result.The embodiment provides valuable reference for locating data inconsistency reason, avoids result misjudgment, with the advantages of accurate detection result, short execution time, high detection efficiency.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of computer, and in particular to a data table consistency detection method and device. BACKGROUND

[0002] At present, when a large amount of data tables are detected for consistency, each field included in each piece of data and whether the data of each field is consistent need to be compared. Commonly used solutions mainly include detection based on data information digest algorithm, table data comparison based on SQL set, and hash statistics-based method, etc.

[0003] In the process of implementing the present application, the inventors have found that the prior art at least has the following problems:

[0004] The existing data table consistency detection method has the defects of easy misjudgment, inaccurate detection results, long execution time, and low detection efficiency. SUMMARY

[0005] Therefore, the embodiments of the present application provide a data table consistency detection method and device, which can compare through multiple logical layers and obtain corresponding inconsistent information, provide valuable reference for locating the reason for data inconsistency, avoid result misjudgment, and have the advantages of accurate detection results, short execution time, and high detection efficiency.

[0006] To achieve the above object, according to an aspect of the embodiments of the present application, a data table consistency detection method is provided.

[0007] A data table consistency detection method comprises the following steps.

[0008] The number of file blocks included in two data tables to be detected for consistency is respectively acquired, and consistency detection of the number of file directories is performed according to the number of file blocks.

[0009] If the consistency detection of the number of file directories is passed, the table structure mode of the two data tables is respectively acquired, and consistency detection of the table structure mode is performed.

[0010] If the consistency detection of the table structure mode is passed, the number of data rows of the two data tables is respectively acquired, and consistency detection of the number of data rows is performed.

[0011] If the consistency detection of the number of data rows is passed, the data of the two data tables is compared row by row until a consistency detection result is obtained.

[0012] Optionally, before the data of the two data tables is compared row by row, the method further comprises: confirming that the number of data rows of the two data tables is not zero; and in the case that the number of data rows of the two data tables is zero, further confirming whether the two data tables are empty tables or have an operation of clearing the data tables.

[0013] Optionally, the number of file blocks included in each of the two data tables to be consistency detected is obtained by: obtaining a file directory location path of each of the two data tables to be consistency detected, and counting the number of directories under the file directory location path; and taking the number of directories as the number of file blocks included in the data table, so as to obtain the number of file blocks included in each of the two data tables to be consistency detected.

[0014] Optionally, the table structure mode of each of the two data tables is obtained by: reading the data table into a table structure data through a big data computing engine, and taking the mode of the table structure as the table structure mode of the data table, so as to obtain the table structure mode of each of the two data tables.

[0015] Optionally, the consistency of the table structure modes is detected by: detecting whether the number of fields in the table structure of the two data tables is consistent according to the table structure modes; and converting the table structure modes into string type data, and detecting whether the string type data obtained by converting the table structure modes of the two data tables is the same, ignoring the case of characters.

[0016] Optionally, the data of the two data tables is compared row by row by: integrating the two data tables to obtain an integrated data table; and taking data from the integrated data table row by row and comparing the data.

[0017] Optionally, the two data tables are integrated to obtain an integrated data table by: performing data dispersion processing on each of the two data tables to obtain a distributed data set, the distributed data set comprising no less than one data sub-table, the number of data sub-tables being the same as the number of partitions; and integrating the data sub-tables corresponding to the two data tables to obtain no less than one integrated data sub-table, the integrated data table being composed of the no less than one integrated data sub-table.

[0018] Optionally, the data dispersion processing comprises: dividing data in the data table into hashable type and comparable type according to the data type of the data; performing hash operation on the data column of the hashable type; re-partitioning the data after the hash operation according to a preset number of partitions; and sorting the data of each partition after the re-partitioning according to the data of the comparable type.

[0019] Optionally, the integrating the two data tables to obtain an integrated data table comprises: integrating the two data tables by using a zip integration manner; and if the two data tables cannot be integrated, integrating the two data tables by using a splicing integration manner to obtain the integrated data table.

[0020] Optionally, the taking data from the integrated data table row by row and performing data comparison comprises: taking data from the integrated data table row by row, obtaining two data corresponding to columns of the two data tables in a data row, and performing data type comparison and data value comparison on the obtained two data.

[0021] Optionally, if the data type is a floating point type, an absolute error value of a preset precision is obtained, and data value comparison is performed on the data of the floating point type according to the absolute error value.

[0022] According to another aspect of the embodiments of the present application, a data table consistency detection device is provided.

[0023] A data table consistency detection device comprises:

[0024] A first detection module is configured to obtain the number of file blocks included in two data tables to be detected for consistency respectively, and perform consistency detection on the number of file directories according to the number of file blocks.

[0025] A second detection module is configured to obtain table structure modes of the two data tables respectively if the consistency detection on the number of file directories is passed, and perform consistency detection on the table structure modes.

[0026] A third detection module is configured to obtain the number of data rows of the two data tables respectively if the consistency detection on the table structure modes is passed, and perform consistency detection on the number of data rows.

[0027] A fourth detection module is configured to perform row-by-row comparison on data of the two data tables if the consistency detection on the number of data rows is passed, and obtain a consistency detection result.

[0028] According to still another aspect of the embodiments of the present application, an electronic device for data table consistency detection is provided.

[0029] An electronic device for data table consistency detection comprises one or more processors, and a storage device configured to store one or more programs, wherein the one or more programs, when executed by the one or more processors, cause the one or more processors to implement the method for data table consistency detection provided by the embodiments of the present application.

[0030] According to still another aspect of the embodiments of the present application, a computer readable medium is provided.

[0031] A computer readable medium having stored thereon a computer program which, when executed by a processor, implements the method for data table consistency detection provided by the embodiments of the present application.

[0032] An embodiment of the above-mentioned application has the following advantages or beneficial effects: by respectively acquiring the number of file blocks included in the two data tables to be detected for consistency, and performing consistency detection on the number of file directories according to the number of file blocks; in the case that the consistency detection on the number of file directories is passed, respectively acquiring the table structure mode of the two data tables, and performing consistency detection on the table structure mode; in the case that the consistency detection on the table structure mode is passed, respectively acquiring the number of data rows of the two data tables, and performing consistency detection on the number of data rows; in the case that the consistency detection on the number of data rows is passed, comparing the data of the two data tables row by row until the consistency detection result is obtained, the technical solution compares layer by layer through multiple logical layers, and obtains the corresponding inconsistency information, which provides valuable reference for locating the reason for data inconsistency, avoids result misjudgment, and has the advantages of accurate detection result, short execution time, and high detection efficiency.

[0033] The further effects of the above-mentioned non-conventional optional mode will be described in the following in combination with the specific embodiments. BRIEF DESCRIPTION OF DRAWINGS

[0034] The accompanying drawings are used to better understand the present application and do not constitute undue limitations on the present application. Among them:

[0035] Figure 1 is a schematic diagram of the main steps of the method for data table consistency detection according to the embodiments of the present application;

[0036] Figure 2 is a schematic diagram of the implementation flow of an embodiment of the present application;

[0037] Figure 3 is a schematic diagram of the main modules of the device for data table consistency detection according to the embodiments of the present application;

[0038] Figure 4 is an exemplary system architecture diagram to which the embodiments of the present application can be applied;

[0039] Figure 5 is a structural schematic diagram of a computer system of a terminal device or a server suitable for implementing the embodiments of the present application. DETAILED DESCRIPTION

[0040] Exemplary embodiments of the present application are described herein below with reference to the accompanying drawings, in which various specific details are set forth to assist in providing a thorough understanding of various concepts. It should be understood that these concepts can be practiced without necessarily being limited to any particular embodiment or detail. Additionally, it should be understood that concepts described herein can be employed in a variety of applications beyond the specific uses described herein. Likewise, it should be understood that various concepts described herein can be embodied in both hardware and software.

[0041] The method and main defects in the prior art in the consistency detection of data tables are as follows:

[0042] The first way is to use MD5 key value for consistency verification. This scheme is equivalent to comparing the snapshots generated by two tables respectively, and has two defects: 1) when there are the same data in the two tables, only the corresponding row numbers are different, then the MD5 generation method has a false judgment, but if you want to avoid this situation, you need to sort the two tables in advance, which will introduce a large preprocessing consumption. 2) Because the system and version of the computing engine are different at different times, there may be slight calculation errors, so the relative error is introduced in comparison, and the MD5 method does not do so. 3) MD5 method is irreversible, only the equality of the two tables can be obtained, and the specific inconsistent information cannot be output;

[0043] The second way is based on SQL collection. The execution of this kind of SQL is usually based on Hadoop and other big data models. Because the sizes of the two tables are equal, the Map side operation mechanism cannot be operated, and a large amount of shuffle operation is introduced, so this kind of SQL execution is also very time-consuming;

[0044] The third way is based on statistics. Statistics is a macro-based way to explore the distribution of data, which cannot accurately find specific inconsistent information;

[0045] The fourth way is data block method, which has two defects: 1) the data dispersion method according to certain characteristic values is more, which is generally based on a column or several columns for hash, and does not have a good automatic inference data type for hash implementation. 2) When the data table is stored in Hadoop distributed file system, it does not make good use of the storage method of the file system. The number of directories under the hdfs(Hadoop distributed file system) path is counted, and if the number of directories of the two tables is different, it means that the data volume of the two tables is inconsistent.

[0046] In view of the above deficiencies in the prior art, the present application provides a consistency detection method for a large data table, which filters inconsistent tables layer by layer through multiple comparisons and outputs specific inconsistent information, thereby providing valuable reference for locating the cause of data inconsistency and avoiding result misjudgment, and has the advantages of accurate detection result, short execution time, and high detection efficiency.

[0047] The technical solution of the present application is to compare whether the data of two data tables is consistent, and if not, to output specific inconsistent information, including return code and specific information display, and the output information is in a self-defined manner. Specifically, the self-defined output information is shown in Table 1.

[0048] Table 1

[0049]

[0050] Each status code in Table 1 corresponds to a specific inconsistent information, representing which phase the data detection execution has reached, facilitating users to quickly locate the problem. The data table consistency detection method of the present application takes Spark as an example of the calculation engine, and mainly detects the consistency of the data table saved in the Hadoop file system. In order to facilitate use, the consistency detection method can generate a jar package for external use, which is convenient for eliminating the version incompatibility problem. The parameter list received by this method is shown in Table 2.

[0051] Table 2

[0052]

[0053] When calling the jar package to detect the consistency of the data table, the above four parameters shown in Table 2 need to be passed in: first, the first two parameters table_1 and table_2 represent the names of the two tables to be detected, and are prefixed with the library name under the storage space of the table, for example: dev1.table_name_1, dev2.table_name_2; second, the third parameter is the precision value, and the main program in the jar package receives the precision value to judge whether the value is less than or equal to 0, if it does not meet the above condition, it is determined that it is an invalid precision value, and the highest number of computer support is replaced for subsequent floating point comparison; the last parameter is the parallelism degree of Spark during shuffle (data shuffle) execution, if the partition data volume is less than or equal to 0, the default value provided by Spark is selected, for example 200.

[0054] Figure 1 is the main step diagram of the data table consistency detection method according to the embodiment of the present application. As Figure 1As shown, the method for data table consistency detection of the embodiment of the present application mainly includes the following steps S101 to S104.

[0055] Step S101: The number of file blocks included in the two data tables to be detected for consistency is respectively acquired, and consistency detection of the number of file directories is performed according to the number of file blocks. Wherein, the number of file blocks included in the two data tables to be detected for consistency is respectively acquired, which can be specifically performed in the following manner: for each of the two data tables to be detected for consistency, the file directory location path where the data table is located is acquired, and the number of directories under the file directory location path is counted; the number of directories is taken as the number of file blocks included in the data table, so as to respectively obtain the number of file blocks included in the two data tables to be detected for consistency.

[0056] Since the tables of big data are stored in layers in Hive, the file directory location path where the data table is located can be displayed by the show create table command, which starts with Location. The complete URL directory path represents the hdfs path of the table, and the data table is abstracted into multiple file blocks stored under hdfs, which is displayed in the form of a directory.

[0057] The number of directories under the path can be counted by the Hadoop fs-count <hdfs path> command. In the default case, the number of directories represents the number of file blocks. If the number of file blocks of the two tables is different, it means that there is a large inconsistency in the storage level data volume of the two tables, so the number of directories can be compared to perform preliminary screening, and the inconsistent information in the two tables is fed back, and the program is terminated. When there is inconsistency, it can be located that the directory definitions of the two tables are different, or there is historical data that has not been deleted, which will lead to different numbers of directories.

[0058] Step S102: In the case that the consistency detection of the number of file directories passes, the table structure mode of the two data tables is respectively acquired, and the consistency detection of the table structure mode is performed. Wherein, when the table structure mode of the two data tables is respectively acquired, it can be specifically that for each of the two data tables, the data table is read as table structure data by the big data computing engine, and the mode possessed by the table structure is taken as the table structure mode of the data table, so as to respectively obtain the table structure mode of the two data tables.

[0059] In an embodiment of the present application, the big data computing engine is, for example, Spark. The data table is read by the Spark program as a DataFrame structure (a table type data structure), the DataFrame is a distributed dataset based on RDD (Resilient Distributed Datasets), and the schema defines the names and data types of the columns in the DataFrame. A schema is a StructType composed of many fields, i.e., StructFields, which have a name, a type, a Boolean flag (the flag specifies whether the column can contain missing values or null values), and the user can specify metadata associated with the column. The schema contains four attributes: column name, column type, whether to allow null values, and metadata. Assuming that the structure of a commodity table is as shown in Table 3, the commodity table has three columns (sku_id, calculate_time, and sku_type).

[0060] Table 3

[0061] Field English Name Field Chinese Name Field Type Field Example Fill Values sku_id Product Inventory ID long 123 calculate_time Calculate Time string null sku_type Product Category string Clothing

[0062] The commodity table is read by the Spark program, and the commodity table can be organized into a DataFrame dataset. The schema of the dataset defines the names of the columns in the dataset, the field types, whether the columns can be null, and the like.

[0063] According to an embodiment of the present application, when performing the consistency detection of the table structure schema, the following steps are specifically included: detecting whether the number of fields in the table structures of the two data tables is consistent according to the table structure schema; converting the table structure schema into a string type data, and detecting whether the string type data obtained by converting the table structure schema of the two data tables is the same in the case of ignoring the case of characters.

[0064] When performing consistency comparison of data tables, comparison of table structure schema of two tables is necessary. If two tables to be compared are generated by Hive and Spark computing engines respectively after executing the same SQL, the two tables may differ in two aspects in table structure: first, Spark collects field annotation information and synchronizes it to the built table Metastore (table structure schema), but Hive does not synchronize the annotation information to the table schema; second, Spark and Hive generate table fields with mixed cases, because when spark.sql.hive.convertMetastoreParquet (a parameter used by Spark engine to read and write Parquet tables) reads and writes Hive Metastore Parquet tables (a Hive table stored in Parquet format, which has high compression rate and saves storage space), Spark SQL attempts to use its own Parquet format setting instead of Hive SerDe (Serializer and Deserializer, a function used by Hive engine to process records and map them to field data types in Hive tables) to obtain better performance.

[0065] Therefore, when comparing such tables, in addition to comparing whether schema.fields.length (the number of fields in the table structure) is equal, it is also necessary to compare whether schema.toString (table structure converted to string type) is equal in the case of ignoring the case. If both are the same, it means that the table structures of the two tables are the same, and if one is different, it means that the two tables have inconsistent fields in the generation stage. The program returns the schema information and terminates, and the consistency check fails.

[0066] Step S103: In the case where the consistency detection of the table structure schema is passed, the number of data rows of the two data tables is obtained respectively, and consistency detection of the number of data rows is performed. When the two table structures are consistent, the number of rows of the two tables is calculated (count operation) respectively, and when the number of rows of the two tables is different, the consistency check fails, and the program returns the number of rows corresponding to the two tables respectively and terminates.

[0067] Step S104: In the case that the consistency detection of the number of data rows is passed, the data of the two data tables is compared row by row until a consistency detection result is obtained. According to one of the embodiments of the present application, before the data of the two data tables is compared row by row in the step S104, it further includes: confirming that the number of data rows of the two data tables is not zero; and in the case that the number of data rows of the two data tables is zero, further confirming whether the two data tables are empty tables or there is an operation of clearing the data tables. If the number of rows of the two data tables is the same and not empty, the difference between the row data needs to be compared specifically.

[0068] According to one of the embodiments of the present application, the data of the two data tables is compared row by row, which can specifically include: integrating the two data tables to obtain an integrated data table; and taking data row by row from the integrated data table and comparing the data. By integrating the data of the two data tables into an integrated data table, only the data from the integrated data table needs to be obtained, without the need to obtain data from the two data tables, which can facilitate data acquisition and comparison.

[0069] Since Spark runs on a yarn cluster, reasonable dispersion of data to the computing nodes can improve the accuracy of comparison. The significance of dispersing data is to distribute the two most similar data on the same computing node, and the sorting operation in the computing node can ensure that the two most similar data can be compared with the highest priority. Therefore, in one of the embodiments of the present application, the two data tables are integrated to obtain an integrated data table, which can specifically include:

[0070] For each of the two data tables, data dispersion processing is performed to obtain a distributed data set, the distributed data set includes no less than one data sub-table, and the number of data sub-tables is the same as the number of partitions;

[0071] The two data tables corresponding to the data sub-tables are integrated to obtain no less than one integrated data sub-table, and the integrated data table is composed of the no less than one integrated data sub-table.

[0072] In the data dispersion process, the data can be classified into hashable type and comparable type according to the data type of the data in the data table, the data column of the hashable type is subjected to hash operation, the data after the hash operation is subjected to re-partition according to the preset partition number, and the data in each partition after the re-partition is sorted according to the data of the comparable type. The dispersion data focuses on selecting appropriate dispersion standards, classifying the DataType of the schema of the DataFrame, and dividing it into hashable type and comparable type. Some data types cannot be subjected to hash operation, for example, numerical values with precision requirements such as floating point data, but these data that cannot be subjected to hash operation can be compared, so these data are classified as comparable type data. The data of the hashable type is used to disperse the corresponding hashable columns of the whole table in the form of hash. The number of dispersed parts is actually equal to the number of partitions used for joins or aggregations in Spark shuffle data (data moved across partition nodes in Spark calculation), and the default is 200. The number of partitions can be set by the user. The number of partitions input by the user is received as the value of Spark.sql.shuffle.partitions (Spark setting shuffle parallelism), and this process is also called re-partition operation, which aims to start multiple executor threads to participate in calculation, improve concurrency, and speed up the shuffle process. After the data is divided into each partition, the data is sorted according to the data of the comparable type. Through sorting, the consistency check result can be avoided due to inconsistent data order in the partition.

[0073] After the above three steps (hash processing, re-partitioning, and sorting in the partition) are completed, the two data tables DataFrame are also converted into two RDDs for easy calculation, and each RDD includes not less than one data table, and the number of data tables is the same as the number of set partitions. Before comparing the data in row1 and row2 units, the rows from different RDDs need to be integrated. row1 is the row of the data table 1, and row2 is the row of the data table 2. By integrating the data of the two data tables into one table, data acquisition and comparison can be facilitated.

[0074] According to the embodiment of the present application, when the two data tables are integrated to obtain an integrated data table, the two data tables can be integrated in a zip integration manner; if integration is not possible, the two data tables can be integrated in a splicing integration manner to obtain an integrated data table. Similarly, after data dispersion processing is performed, corresponding data sub-tables are integrated to obtain integrated data sub-tables, and the integration method is completely the same. In Spark, both the zip operator (used for zip integration) and the join operator (used for splicing integration) can implement integration operations, the zip operator is fast for integration, but can only integrate two rows of the same data, and the join operator is slow for integration, but the join operator does not require that the two rows must be the same. In fact, the implementation condition of the zip operator is more stringent than that of the join operator. If the two tables are the same, the zip operator can be used to complete data integration in the fastest way; if the two tables are different, the zip operator will fail, and the join operator is used to complete the data integration of the two tables, so that subsequent data comparison and inconsistent results can be returned. Therefore, the zip operator and the join operator are combined to perform data integration:

[0075] 1. The zip operator is used to perform zip integration on tables with the same number of partitions and the same amount of data in each partition. The zip operator requires that the two compared data have the same number of dispersed partitions and the same amount of data in each partition. After the above two conditions are met, the calculation speed is much faster than that of the join operator. However, if the above two conditions are not met at the same time, the zip operator will throw an exception, indicating that the two tables are different and cannot be integrated;

[0076] 2. In order to obtain specific inconsistent information, the join operator must be used for splicing integration. However, join calculation is time-consuming. In order to avoid introducing quadratic complexity, a partition needs to be added to each table, and the connection keys corresponding to the rows are sorted in the partition. After the operation, the join key is equal, and the number of rows after the join is equal to the number of rows of the two tables. Therefore, the zipWithIndex operation is performed on the two RDDs to add an increasing index to each row from different tables. The join operator uses the same index as the join key during integration, and row1 and row2 are used as values to complete the integration operation. The integration principle is: row1, row2 => (row1, row2). After integration, the data of the two data tables can be integrated into an integrated data table. For example, a row of data in the integrated data table is: ([today must buy Ex,, 117.0, 252, 2021-05-12], [today must buy Ex,, 117.0, 252, 2021-05-12]).

[0077] After the data integration, the mapPartitions operation (operation on the iterator of each partition in the RDD) is performed according to the integrated data table, each integrated data partition is sequentially traversed, and the custom comparator is called in each integrated data partition to perform data comparison. When there is inconsistent data row, the traversal of the current integrated data partition is stopped as soon as the first inconsistent data is obtained in each integrated data partition, and the inconsistent result is directly output.

[0078] According to an embodiment of the present application, when the data is taken out from the integrated data table row by row and the data comparison is performed, the following operations can be specifically included: the data is taken out from the integrated data table row by row, the two data corresponding to the columns of the two data tables in the data row are obtained, and the data type comparison and data value comparison are performed on the obtained two data.

[0079] In an embodiment of the present application, the row-by-row data comparison is performed by the custom comparator, and the implementation principle is as follows: first, the custom comparator corresponding to each data type (common data types known at present, such as integer type, floating point type, character type, and Boolean type) is constructed, the custom comparator receives the integrated data partition after integration, and the data structure is (row1, row2). According to the number of columns in the table structure, only two data v1 and v2 in the corresponding positions of the columns in the row are transmitted each time, and then it is judged whether the data types of the two data are the same. If the data types are different, the two data and the rows row1 and row2 to which the data belong are returned as inconsistent results, and the program is terminated. If the data types are the same, the two data are transmitted into the comparison program of the corresponding type, and the value of the two data is judged by the comparison function compare to further perform consistency check.

[0080] In an embodiment of the present application, if the data type is a floating point type, the absolute error value of a preset precision is obtained, and the data value comparison of the data of the floating point type is performed according to the absolute error value. The data of the floating point type is, for example, single precision floating point type, double precision floating point type, and decimal type. Since the data of the floating point type has a precision value eps, the custom comparator of the present application specially processes the floating point type: when the double, float, and decimal type data are compared, the threshold value is calculated according to the absolute error value received by the user input, and the data verification result is consistent only when the error of the data of the floating point type is within the range of the absolute error value. In this way, the error of the floating point number caused by different calculation engines can be eliminated, and the user's expected floating point value comparison effect can be obtained. According to an embodiment, the pseudo code of the custom comparator for the floating point type is as follows:

[0081]

[0082]

[0083] Figure 2 is a flowchart of an embodiment of the present application. Figure 2 The main execution steps of the present application when performing data table consistency detection are shown in the figure, and the consistency detection result corresponding to each step is output. The main implementation process is as follows:

[0084] 1. Obtain two data tables table_1 and table_2 to be detected for consistency;

[0085] 2. Calculate the number of file directories under the hdfs path of each of the two data tables;

[0086] 3. Determine whether the number of file directories under the hdfs path of the two data tables is the same, if not, output the consistency detection result as: the number of files under the hdfs path of the two tables is different, and end the process; if the same, execute the next step;

[0087] 4. Determine whether the table structure mode of the two data tables is the same, if not, output the consistency detection result as: the two table structures are different, and end the process; if the same, execute the next step;

[0088] 5. Determine whether the number of rows of the two data tables is the same, if not, output the consistency detection result as: the number of rows of the two tables is different, and end the process; if the same, execute the next step;

[0089] 6. Determine whether the number of rows of the two data tables is 0, if yes, output the consistency detection result as: the two tables are the same, both empty tables, and end the process; if not, execute the next step;

[0090] 7. Use the zip operator to perform zip integration on the two data tables;

[0091] 8. Determine whether the integration is successful, if yes, compare the data in the integrated data table row by row, otherwise, output the zip operator exception, the two tables are not equal, and use the join operator to perform splicing integration on the two data tables, and compare the data in the integrated data table row by row to determine the specific different data rows;

[0092] 9. Use the custom comparator to compare row by row, if the data type and data value of each row of data in the integrated two data tables are the same, output the consistency detection result as: the two tables are the same, and end the process; otherwise, execute the next step;

[0093] 10、If the data types of the data v1 and v2 in the Xth row of the two tables are different, a consistency detection result of different data types is output: the row numbers of the two tables are both X, the data types of the data v1 and v2 are different, and the rows where the data v1 and v2 are located are {row1} and {row2} respectively, wherein row1 is the row number of the data v1 in table_1, and row2 is the row number of the data v2 in table_2; if the data types of the data v1 and v2 in the Xth row of the two tables are the same but the data values are different, a consistency detection result of different data values is output: the row numbers of the two tables are both X, the data values of the data v1 and v2 are different, and the rows where the data v1 and v2 are located are {row1} and {row2} respectively.

[0094] According to the technical scheme of the embodiment of the application, the multi-layer logical layer comparison is performed, and the corresponding inconsistency information is obtained, which provides valuable reference for locating the reason of data inconsistency, avoids result misjudgment, and has the advantages of accurate detection result, short execution time, and high detection efficiency.

[0095] Figure 3 is a schematic diagram of main modules of the device for data table consistency detection according to the embodiment of the application. As shown in Figure 3 the device 300 for data table consistency detection according to the embodiment of the application mainly comprises a first detection module 301, a second detection module 302, a third detection module 303, and a fourth detection module 304.

[0096] The first detection module 301 is configured to acquire the file block numbers included in the two data tables to be detected for consistency respectively, and perform consistency detection on the file directory numbers according to the file block numbers.

[0097] The second detection module 302 is configured to acquire the table structure modes of the two data tables respectively in the case that the consistency detection on the file directory numbers is passed, and perform consistency detection on the table structure modes.

[0098] The third detection module 303 is configured to acquire the data row numbers of the two data tables respectively in the case that the consistency detection on the table structure modes is passed, and perform consistency detection on the data row numbers.

[0099] The fourth detection module 304 is configured to compare the data of the two data tables row by row in the case that the consistency detection on the data row numbers is passed, and obtain a consistency detection result.

[0100] According to one embodiment of the present application, the data table consistency detection device 300 further comprises a row number confirmation module (not shown in the figure) configured to: confirm that the data row numbers of the two data tables are both not zero before performing the line-by-line comparison of the data of the two data tables; and further confirm whether the two data tables are empty tables or have an operation of clearing the data tables in the case that the data row numbers of the two data tables are both zero.

[0101] According to another embodiment of the present application, the first detection module 301 can be further configured to: obtain a file directory location path where each of the two data tables to be consistency detected is located, and count the number of directories under the file directory location path; and take the number of directories as the number of file blocks included in the data table, so as to respectively obtain the number of file blocks included in the two data tables to be consistency detected.

[0102] According to still another embodiment of the present application, the second detection module 302 can be further configured to: read each of the two data tables to table structure data through a big data computing engine, and take the mode of the table structure as the table structure mode of the data table, so as to respectively obtain the table structure modes of the two data tables.

[0103] According to still another embodiment of the present application, the second detection module 302 can be further configured to: detect whether the number of fields in the table structure of the two data tables is consistent according to the table structure mode; convert the table structure mode into string type data, and detect whether the string type data obtained by converting the table structure modes of the two data tables is the same in the case of ignoring the case of characters.

[0104] According to still another embodiment of the present application, the fourth detection module 304 can be further configured to: integrate the two data tables to obtain an integrated data table; and take out data from the integrated data table line by line and perform data comparison.

[0105] According to still another embodiment of the present application, the fourth detection module 304 can be further configured to: perform data dispersion processing on each of the two data tables to obtain a distributed data set, the distributed data set comprising no less than one data sub-table, the number of the data sub-tables being the same as the number of partitions; integrate the data sub-tables corresponding to the two data tables to obtain no less than one integrated data sub-table, the integrated data table being composed of the no less than one integrated data sub-table.

[0106] According to a further embodiment of the present application, the data dispersion processing comprises: dividing the data into hashable type and comparable type according to the data type of the data in the data table; performing hash operation on the data column of the hashable type; re-partitioning the data after the hash operation according to the preset partition number; and sorting the data in each partition after the re-partitioning according to the data of the comparable type.

[0107] According to a further embodiment of the present application, the fourth detection module 304 can further be configured to: integrate the two data tables using the zip integration manner; and if the integration fails, integrate the two data tables using the splicing integration manner to obtain an integrated data table.

[0108] According to a further embodiment of the present application, the fourth detection module 304 can further be configured to: take out data row by row from the integrated data table, and obtain two data corresponding to the columns of the two data tables in the taken out data row; and compare the data type and the data value of the obtained two data.

[0109] According to a further embodiment of the present application, if the data type is floating point type, an absolute error value of preset precision is obtained, and the data value comparison of the data of the floating point type is performed according to the absolute error value.

[0110] According to the technical scheme of the embodiment of the present application, the file block number included in the two data tables to be consistency detected is obtained respectively, and the consistency detection of the file directory number is performed according to the file block number; in the case that the consistency detection of the file directory number is passed, the table structure mode of the two data tables is obtained respectively, and the consistency detection of the table structure mode is performed; in the case that the consistency detection of the table structure mode is passed, the data row number of the two data tables is obtained respectively, and the consistency detection of the data row number is performed; in the case that the consistency detection of the data row number is passed, the data of the two data tables is compared row by row until the consistency detection result is obtained, the technical scheme compares layer by layer through multiple logical layers, and corresponding inconsistency information is obtained, which provides valuable reference for locating the reason of data inconsistency, avoids result misjudgment, and has the advantages of accurate detection result, short execution time, and high detection efficiency.

[0111] Figure 4 An exemplary system architecture 400 is shown, which can apply the method for data table consistency detection or the apparatus for data table consistency detection according to the embodiments of the present application.

[0112] As Figure 4As shown, the system architecture 400 can include terminal devices 401, 402, 403, a network 404, and a server 405. The network 404 is a medium for providing communication links between the terminal devices 401, 402, 403 and the server 405. The network 404 can include various connection types, such as wired, wireless communication links, or fiber optic cables, etc.

[0113] A user can use the terminal devices 401, 402, 403 to interact with the server 405 through the network 404 to receive or send messages, etc. Various communication client applications can be installed on the terminal devices 401, 402, 403, such as data analysis applications, data detection applications, search applications, etc. (only as examples).

[0114] The terminal devices 401, 402, 403 can be various electronic devices with display screens and supporting web browsing, including but not limited to smart phones, tablet computers, laptop computers, desktop computers, etc.

[0115] The server 405 can be a server providing various services, such as a background management server providing support for data table consistency detection requests sent by users using the terminal devices 401, 402, 403 (only as an example). The background management server can separately acquire the number of file blocks included in two data tables to be consistency detected according to the data table consistency detection request received, etc., and perform consistency detection on the number of file directories according to the number of file blocks; in the case that the consistency detection on the number of file directories is passed, separately acquire table structure patterns of the two data tables, and perform consistency detection on the table structure patterns; in the case that the consistency detection on the table structure patterns is passed, separately acquire the number of data rows of the two data tables, and perform consistency detection on the number of data rows; in the case that the consistency detection on the number of data rows is passed, perform line-by-line comparison on the data of the two data tables, etc., and feed back the processing result (such as a consistency detection result - only as an example) to the terminal device.

[0116] It should be noted that the method for data table consistency detection provided by the embodiments of the present application is generally executed by the server 405, and accordingly, the apparatus for data table consistency detection is generally arranged in the server 405.

[0117] It should be understood that, Figure 4 The number of terminal devices, networks, and servers in the system architecture 400 is only illustrative. According to the needs of implementation, there can be any number of terminal devices, networks, and servers.

[0118] Reference is made below to Figure 5 which shows a structural schematic diagram of a computer system 500 suitable for implementing the terminal device or the server of the embodiments of the present application.Figure 5 The terminal device or server shown is merely an example and should not impose any limitation on the functions and scope of use of the embodiments of the present application.

[0119] As shown in Figure 5 The computer system 500 includes a central processing unit (CPU) 501 which can perform various appropriate actions and processes in accordance with a program stored in a read only memory (ROM) 502 or a program loaded into a random access memory (RAM) 503 from a storage section 508. In the RAM 503, various programs and data required for the operation of the system 500 are also stored. The CPU 501, the ROM 502, and the RAM 503 are connected to each other through a bus 504. An input / output (I / O) interface 505 is also connected to the bus 504.

[0120] The following components are connected to the I / O interface 505: an input section 506 including a keyboard, a mouse, etc.; an output section 507 including a display such as a cathode ray tube (CRT), a liquid crystal display (LCD), etc., and a speaker, etc.; a storage section 508 including a hard disk, etc.; and a communication section 509 including a network interface card such as a LAN card, a modem, etc. The communication section 509 performs communication processing via a network such as the Internet. A drive 510 is also connected to the I / O interface 505 as necessary. A removable recording medium 511 such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, etc. is attached to the drive 510 as necessary, so that a computer program read therefrom is installed into the storage section 508 as necessary.

[0121] In particular, the processes described above with reference to the flowcharts can be implemented as a computer software program in accordance with the embodiments disclosed herein. For example, the embodiments disclosed herein include a computer program product comprising a computer program carried on a computer readable medium, the computer program comprising program code for performing the methods illustrated by the flowcharts. In such embodiments, the computer program can be downloaded and installed from a network via the communication section 509 and / or installed from the removable recording medium 511. When the computer program is executed by the central processing unit (CPU) 501, the above-described functions defined in the system of the present application are performed.

[0122] It should be noted that the computer-readable medium shown in the present application can be a computer-readable signal medium or a computer-readable storage medium or any combination of the above two. The computer-readable storage medium may, for example, but is not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, device or component, or any combination of the above. More specific examples of computer-readable storage media can include, but are not limited to, an electrical connection having one or more wires, a portable computer diskette, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the above. In the present application, the computer-readable storage medium can be any tangible medium containing or storing a program that can be used by or in conjunction with an instruction execution system, device or component. In the present application, the computer-readable signal medium can include a data signal carried in a baseband or as a part of a carrier wave, which carries computer-readable program code. Such a propagated data signal can take various forms, including but not limited to an electromagnetic signal, an optical signal or any suitable combination of the above. The computer-readable signal medium can also be any computer-readable medium other than the computer-readable storage medium, which can send, propagate or transmit a program for use by or in conjunction with an instruction execution system, device or component. The program code contained on the computer-readable medium can be transmitted by any suitable medium, including but not limited to wireless, wire, optical cable, RF, etc., or any suitable combination of the above.

[0123] The flowcharts and block diagrams in the drawings illustrate the possible implementation architectures, functions and operations of the systems, methods and computer program products according to various embodiments of the present application. In this regard, each block in the flowcharts or block diagrams can represent a module, a program segment or a part of code containing one or more executable instructions for implementing the specified logical functions. It should also be noted that in some alternative implementations, the functions noted in the blocks can occur in different order than that shown in the drawings. For example, two blocks that are shown in succession can actually be executed substantially in parallel, and sometimes in reverse order, depending on the functions involved. It should also be noted that each block in the block diagrams or flowcharts, and the combination of blocks in the block diagrams or flowcharts, can be implemented by a dedicated hardware-based system that performs the specified functions or operations, or can be implemented by a combination of dedicated hardware and computer instructions.

[0124] The units or modules described in the embodiments of the present application can be implemented by software or hardware. The described units or modules can also be arranged in a processor, for example, a processor can include a first detection module, a second detection module, a third detection module, and a fourth detection module. In some cases, the names of these units or modules do not constitute a limitation on the units or modules themselves, for example, the first detection module can also be described as "a module for respectively acquiring the number of file blocks included in two data tables to be consistency detected, and performing consistency detection on the number of file directories according to the number of file blocks".

[0125] As another aspect, the present application also provides a computer readable medium, which can be included in the device described in the above embodiments, or can exist independently without being assembled into the device. The computer readable medium carries one or more programs, which, when executed by the device, cause the device to include: respectively acquiring the number of file blocks included in two data tables to be consistency detected, and performing consistency detection on the number of file directories according to the number of file blocks; in the case that the consistency detection on the number of file directories is passed, respectively acquiring table structure patterns of the two data tables, and performing consistency detection on the table structure patterns; in the case that the consistency detection on the table structure patterns is passed, respectively acquiring data row numbers of the two data tables, and performing consistency detection on the data row numbers; and in the case that the consistency detection on the data row numbers is passed, performing line-by-line comparison on data of the two data tables until a consistency detection result is obtained.

[0126] According to the technical scheme of the embodiments of the present application, by respectively acquiring the number of file blocks included in two data tables to be consistency detected, and performing consistency detection on the number of file directories according to the number of file blocks; in the case that the consistency detection on the number of file directories is passed, respectively acquiring table structure patterns of the two data tables, and performing consistency detection on the table structure patterns; in the case that the consistency detection on the table structure patterns is passed, respectively acquiring data row numbers of the two data tables, and performing consistency detection on the data row numbers; and in the case that the consistency detection on the data row numbers is passed, performing line-by-line comparison on data of the two data tables until a consistency detection result is obtained, the technical scheme compares layer by layer through multiple logical layers, and obtains corresponding inconsistent information, which provides valuable reference for locating the reason for data inconsistency, avoids result misjudgment, and has the advantages of accurate detection result, short execution time, and high detection efficiency.

[0127] The foregoing detailed description has set forth various embodiments of the devices and / or processes via the use of specific terminology. However, embodiments thereof can be practiced with the exact description not being presented in detail. The term "device" should be understood to encompass devices operating in various modes, such as active mode, sleep mode, hibernate mode, and the like. The terms "coupled" and "connected," along with their derivatives, can be used. It should be understood that these terms are not intended as synonyms for each other. Rather, particular circuitry that can be said to be coupled or connected can be coupled and connected via some transmission medium.

Claims

1. A method of data table consistency detection, the method comprising: The method comprises the following steps: respectively acquiring the number of file blocks included in two data tables to be consistency detected, and performing consistency detection on the number of file directories according to the number of file blocks; if the consistency detection on the number of file directories is passed, respectively acquiring table structure modes of the two data tables, and performing consistency detection on the table structure modes; if the consistency detection on the table structure modes is passed, respectively acquiring the number of data rows of the two data tables, and performing consistency detection on the number of data rows; if the consistency detection on the number of data rows is passed, performing line-by-line comparison on the data of the two data tables until a consistency detection result is obtained; wherein, the line-by-line comparison on the data of the two data tables comprises: for each of the two data tables, performing data dispersion processing to obtain a distributed data set, the distributed data set comprising no less than one data sub-table, the number of data sub-tables being the same as the number of partitions; integrating the data sub-tables corresponding to the two data tables to obtain no less than one integrated data sub-table, the integrated data table being composed of the no less than one integrated data sub-table; taking data from the integrated data table line by line and performing data comparison; the data dispersion processing comprises: dividing the data in the data table into hashable types and comparable types according to the data types of the data; performing hash operation on the data columns of the hashable types; re-partitioning the data after the hash operation according to a preset number of partitions; and sorting the data in each partition after the re-partitioning according to the data of the comparable types.

2. The method of claim 1, wherein, Before the line-by-line comparison on the data of the two data tables, the method further comprises: confirming that the number of data rows of the two data tables is not zero; and, if the number of data rows of the two data tables is zero, further confirming whether the two data tables are empty tables or have an operation of clearing the data tables.

3. The method of claim 1, wherein, respectively acquiring the number of file blocks included in two data tables to be consistency detected, comprises: for each of the two data tables to be consistency detected, acquiring the file directory location path of the data table, and counting the number of directories under the file directory location path; taking the number of directories as the number of file blocks included in the data table to respectively obtain the number of file blocks included in the two data tables to be consistency detected.

4. The method of claim 1, wherein, respectively acquiring the table structure modes of the two data tables comprises: for each of the two data tables, reading the data table into table structure data through a big data computing engine, taking the mode of the table structure as the table structure mode of the data table, to respectively obtain the table structure modes of the two data tables.

5. The method according to claim 1 or 4, characterized in that, performing consistency detection on the table structure modes comprises: detecting whether the number of fields in the table structures of the two data tables is consistent according to the table structure modes; converting the table structure modes into string type data, and detecting whether the string type data obtained by converting the table structure modes of the two data tables is the same under the condition of ignoring the case of characters.

6. The method of claim 1, wherein, integrating the two data tables to obtain an integrated data table comprises: integrating the two data tables in a zip integration manner; If the two data tables cannot be integrated, the two data tables are integrated by using splicing integration to obtain an integrated data table.

7. The method of claim 1, wherein, Data is taken out from the integrated data table row by row, and data comparison is performed, including: Data is taken out from the integrated data table row by row, and two data corresponding to columns of the two data tables in the taken-out data row are obtained; The two obtained data are compared in data type and data value.

8. The method of claim 7, wherein, If the data type is a floating-point type, an absolute error value of a preset precision is obtained, and the data value comparison of the data of the floating-point type is performed according to the absolute error value.

9. An apparatus for data table consistency detection, the apparatus comprising: including: A first detection module is configured to obtain the number of file blocks included in the two data tables to be consistency detected respectively, and perform consistency detection on the number of file directories according to the number of file blocks; A second detection module is configured to obtain the table structure mode of the two data tables respectively in a case where the consistency detection on the number of file directories is passed, and perform consistency detection on the table structure mode; A third detection module is configured to obtain the number of data rows of the two data tables respectively in a case where the consistency detection on the table structure mode is passed, and perform consistency detection on the number of data rows; A fourth detection module is configured to perform row-by-row comparison on the data of the two data tables in a case where the consistency detection on the number of data rows is passed, until a consistency detection result is obtained; The fourth detection module is further configured to: perform data dispersion processing on each of the two data tables to obtain a distributed data set, the distributed data set including no less than one data sub-table, the number of the data sub-tables being the same as the number of partitions; and integrate the data sub-tables corresponding to the two data tables to obtain no less than one integrated data sub-table, the integrated data table being composed of the no less than one integrated data sub-table; Data is taken out from the integrated data table row by row, and data comparison is performed; The data dispersion processing includes: dividing data into a hashable type and a comparable type according to the data type of the data in the data table; performing hash operation on the data column of the hashable type; re-partitioning the data after the hash operation according to a preset number of partitions; and sorting the data of each partition after the re-partitioning according to the data of the comparable type.

10. An electronic device for data table consistency detection, the electronic device comprising: including: One or more processors; A storage device configured to store one or more programs, When the one or more programs are executed by the one or more processors, the one or more processors implement the method of any one of claims 1-8.

11. A computer readable medium having stored thereon a computer program, characterized in that The program is executed by the processor to implement the method of any one of claims 1-8. The program is executed by the processor to implement the method of any one of claims 1-8.

Images