A method, device, electronic device and medium for data verification of upstream and downstream tables

Abstract

The present application relates to a method, apparatus, electronic device and medium for data verification of upstream and downstream tables, which is applied to a data warehouse. The data warehouse contains several tables, and the tables include a main table and a dimension table. The association relationship between the corresponding main table and dimension table is obtained by parsing the SQL development script; the association relationship includes an association dimension and an association condition, and the association condition includes an association granularity and an association sub-dimension. Then, according to the first and second preset required fields, the corresponding measurement field and the field values of each dimension and each granularity corresponding to the measurement field are obtained from the main table and a corresponding matrix is obtained, and the corresponding unique key and the number of unique keys of each dimension and each sub-dimension corresponding to the unique key are obtained from the dimension table and a corresponding matrix is obtained. Based on the association relationship, the matrix of the field values of each dimension and each granularity in the main table and the matrix of the number of unique keys of each dimension and each sub-dimension in the dimension table are processed to obtain the field values of each dimension and each granularity under the association relationship.

Description

Technical Field

[0001] The present application relates to the field of logistics technology, and in particular, to a method, device, electronic device and medium for data verification of upstream and downstream tables. Background Art

[0002] A data warehouse is a collection that provides all types of data support required by an enterprise, supporting data storage and data processing. The data in the data warehouse is generally stored in the form of data tables, and the data in the data tables generally involves multiple dimensions. During the process of processing the data in the data tables, corresponding data tables are generally generated based on the division of dimensions. In this process, the data table being processed is called the upstream table, and the corresponding processed data table is called the downstream table. In another corresponding manner, the data table being processed is called the main table, and the table reflecting its dimensions is called the dimension table.

[0003] The correctness of the data stored in the database is very important. Therefore, it is necessary to verify the correctness of the data.

[0004] In the related art, based on the data quality control (DQC) monitoring rules in the data quality module of the Alibaba Cloud dataworks product, the data in a single table can be verified. However, this verification method is limited to verifying the correctness of the data in the table, and cannot verify whether the data in the upstream and downstream tables matches. This results in incomplete verification content and low verification efficiency. Summary of the Invention

[0005] The present application provides a method, device, electronic device and medium for data verification of upstream and downstream tables, which realizes comprehensive verification of the data matching in the upstream and downstream tables and improves the verification efficiency.

[0006] In a first aspect, the present application provides a method for data verification of upstream and downstream tables, which is applied to a data warehouse. The data warehouse includes several tables, and the tables include a main table and a dimension table. The method includes:

[0007] Parse the sql development script to obtain the association relationship between the main table and the dimension table corresponding to the sql development script. The association relationship includes an association dimension and an association condition. The association condition includes an association granularity and an association sub-dimension.

[0008] According to the first preset required fields, obtain the corresponding measurement fields from the main table and the field values of each dimension and each granularity corresponding to the measurement fields, and obtain a matrix corresponding to the field values of the main table at each dimension and each granularity.

[0009] Obtain the corresponding unique key and the number of unique keys under each sub-dimension of each dimension corresponding to the unique key from the dimension table according to the second preset requirement field, and obtain a matrix corresponding to the number of unique keys under each sub-dimension of each dimension of the dimension table;

[0010] Based on the association relationship between the main table and the dimension table, process the matrix corresponding to the field values of the main table under each dimension and each granularity and the matrix corresponding to the number of unique keys under each sub-dimension of each dimension of the dimension table, and obtain the field values under each dimension and each granularity under the association relationship;

[0011] Compare the field values under each dimension and each granularity under the association relationship with the field values obtained by running the sql development script. If they are consistent, it indicates that the data in the main table matches the data in the downstream table obtained by running the sql development script on the main table.

[0012] Optionally, parsing the sql development script to obtain the association relationship between the main table and the dimension table corresponding to the sql development script includes:

[0013] Search for the first key field in the sql development script;

[0014] Judge whether the fields connected by the first key field belong to two different tables;

[0015] If so, determine the two different tables as the main table corresponding to the sql development script and the dimension table corresponding to the main table respectively, and determine the dimension corresponding to the field connected by the first key field as the association dimension between the main table and the dimension table.

[0016] Optionally, parsing the sql development script to obtain the association relationship between the main table and the dimension table corresponding to the sql development script includes:

[0017] Search for the second key field in the sql development script;

[0018] Judge whether the fields connected by the second key field belong to the same table;

[0019] If so, determine the association condition between the main table and the dimension table according to the fields connected by the second key field.

[0020] Optionally, determining the association condition between the main table and the dimension table according to the fields connected by the second key field includes:

[0021] If the same table is the main table, determine the granularity corresponding to the field connected by the second key field as the association granularity between the main table and the dimension table;

[0022] If the same table is the dimension table, determine the sub-dimension corresponding to the field connected by the second key field as the associated sub-dimension between the main table and the dimension table.

[0023] Optionally, the processing of the matrix corresponding to the field values of the main table at each dimension and each granularity and the matrix corresponding to the number of unique keys of the dimension table at each dimension and each sub-dimension based on the association relationship between the main table and the dimension table to obtain the field values at each dimension and each granularity under the association relationship includes:

[0024] Process the matrix corresponding to the field values of the main table at each dimension and each granularity based on the association relationship between the main table and the dimension table to obtain a first screening matrix;

[0025] Process the matrix corresponding to the number of unique keys of the dimension table at each dimension and each sub-dimension based on the association relationship between the main table and the dimension table to obtain a second screening matrix;

[0026] Obtain the field values at each dimension and each granularity under the association relationship according to the first screening matrix and the second screening matrix.

[0027] Optionally, the obtaining of the field values at each dimension and each granularity under the association relationship according to the first screening matrix and the second screening matrix includes:

[0028] Multiply the transposed matrix of the second screening matrix by the first screening matrix, and the obtained matrix corresponds to the field values at each dimension and each granularity under the association relationship.

[0029] Optionally, the method further includes:

[0030] If the field values at each dimension and each granularity under the association relationship are inconsistent with the field values obtained by running the sql development script, compare the number of dimensions under the association relationship with the number of dimensions obtained by running the sql development script to determine the data integrity in the downstream table obtained by running the main table through the sql development script.

[0031] In a second aspect, the present application provides a data verification device, including:

[0032] A parsing module, configured to parse an sql development script to obtain the association relationship between the main table and the dimension table corresponding to the sql development script; the association relationship includes an associated dimension and an association condition; the association condition includes an associated granularity and an associated sub-dimension.

[0033] The first matrix formation module is used to obtain the corresponding measurement fields and the field values at each dimension and each granularity corresponding to the measurement fields from the master table according to the first preset requirement fields, so as to obtain the matrix corresponding to the field values at each dimension and each granularity of the master table.

[0034] The second matrix formation module is used to obtain the corresponding unique keys and the number of unique keys at each dimension and each sub-dimension corresponding to the unique keys from the dimension table according to the second preset requirement fields, so as to obtain the matrix corresponding to the number of unique keys at each dimension and each sub-dimension of the dimension table.

[0035] The processing module is used to process the matrix corresponding to the field values at each dimension and each granularity of the master table and the matrix corresponding to the number of unique keys at each dimension and each sub-dimension of the dimension table based on the association relationship between the master table and the dimension table, so as to obtain the field values at each dimension and each granularity under the association relationship.

[0036] The comparison module is used to compare the field values at each dimension and each granularity under the association relationship with the field values obtained by running the SQL development script. When they are consistent, it indicates that the data in the master table matches the data in the downstream table obtained by running the SQL development script on the master table.

[0037] Optionally, the parsing module is specifically used for:

[0038] Search for the first key field in the SQL development script;

[0039] Determine whether the fields connected by the first key field belong to two different tables;

[0040] When yes, respectively determine the two different tables as the master table corresponding to the SQL development script and the dimension table corresponding to the master table, and determine the dimension corresponding to the field connected by the first key field as the association dimension between the master table and the dimension table.

[0041] Optionally, the parsing module is specifically used for:

[0042] Search for the second key field in the SQL development script;

[0043] Determine whether the fields connected by the second key field belong to the same table;

[0044] When yes, determine the association condition between the master table and the dimension table according to the fields connected by the second key field.

[0045] Optionally, when the parsing module determines the association condition between the master table and the dimension table according to the fields connected by the second key field, it is specifically used for:

[0046] When the same table is the main table, determine the granularity corresponding to the field connected by the second key field as the association granularity between the main table and the dimension table;

[0047] When the same table is the dimension table, determine the sub-dimension corresponding to the field connected by the second key field as the association sub-dimension between the main table and the dimension table.

[0048] Optionally, the processing module is specifically configured to:

[0049] Based on the association relationship between the main table and the dimension table, process the matrix corresponding to the field values of the main table at each dimension and each granularity to obtain a first screening matrix;

[0050] Based on the association relationship between the main table and the dimension table, process the matrix corresponding to the number of unique keys of the dimension table at each dimension and each sub-dimension to obtain a second screening matrix;

[0051] According to the first screening matrix and the second screening matrix, obtain the field values at each dimension and each granularity under the association relationship.

[0052] Optionally, when the processing module obtains the field values at each dimension and each granularity under the association relationship according to the first screening matrix and the second screening matrix, it is specifically configured to:

[0053] Multiply the transposed matrix of the second screening matrix by the first screening matrix, and the obtained matrix corresponds to the field values at each dimension and each granularity under the association relationship.

[0054] Optionally, the data verification device further includes a data integrity determination module, configured to:

[0055] When the field values at each dimension and each granularity under the association relationship are inconsistent with the field values obtained by running the sql development script, compare the number of dimensions under the association relationship with the number of dimensions obtained by running the sql development script, and determine the data integrity in the downstream table obtained by running the main table through the sql development script.

[0056] In a third aspect, the present application provides an electronic device, including: a memory and a processor, and a computer program capable of being loaded and executed by the processor for the method of the first aspect is stored on the memory.

[0057] In a fourth aspect, the present application provides a computer-readable storage medium, storing a computer program capable of being loaded and executed by the processor for the method of the first aspect.

[0058] Fifth aspect, the present application provides a computer program product, including: a computer program; when the computer program is executed by a processor, the method described in any item of the first aspect is implemented.

[0059] The data verification method for upstream and downstream tables provided in this embodiment is applied to a data warehouse, which contains several tables, including a main table and dimension tables. By parsing the SQL development script, the association relationship between the corresponding main table and dimension tables in the SQL development script can be obtained; it is stipulated that the main table and dimension tables correspond to different preset required fields, namely the first preset required field and the second preset required field; according to the preset required fields, the corresponding data in the main table and dimension tables can be obtained respectively to get the corresponding matrix; by processing the matrix according to the association relationship, the field values of each dimension and each granularity under the association relationship can be obtained. The field values of each dimension and each granularity under the association relationship are obtained after processing the parsed SQL development script, which represents the standard data in the downstream table derived based on relevant logic; while the field values obtained by running the SQL development script are based on the upstream table (main table) and are obtained by running the SQL development script for the downstream table, which represents the actual data in the downstream table obtained based on the actual running of the script. Therefore, by comparing the field values of each dimension and each granularity under the association relationship with the field values obtained by running the SQL development script, the accuracy of the running process of the parsed SQL development script can be verified, which represents the matching situation of the data obtained from the main table under the association relationship between the main table and dimension tables and the data in the downstream table obtained by running the SQL development script for the downstream table from the upstream table (main table). If the comparison results are consistent, it indicates that the data in the upstream and downstream tables match, achieving a comprehensive verification of the data under the association relationship and improving the verification efficiency.

[0060] In addition, by searching for the first key field, it is determined whether there is an association between the main table and dimension tables in a certain dimension, and if so, in what dimension, providing the association basis for the data verification of the upstream and downstream tables for the relevant main table and dimension tables.

[0061] In addition, by searching for the second key field, it is determined whether there is an association condition between the main table and dimension tables, providing the association basis for the data verification of the upstream and downstream tables for the relevant main table and dimension tables.

[0062] In addition, by parsing the SQL development script, the specific association condition is judged. When parsing the SQL parsing script, the association conditions are different when the fields connected by the second key field belong to different tables, providing a more accurate association basis for the relevant main table and dimension tables for the data verification scope of the upstream and downstream tables.

[0063] In addition, screening processing is performed on the matrix corresponding to the field values of the main table in each dimension and each granularity and the matrix corresponding to the number of unique keys of the dimension table in each dimension and each sub-dimension, obtaining the range of the data in the downstream table, providing an accurate range for the data verification of the upstream and downstream tables and improving the data verification efficiency.

[0064] In addition, fixed operations are performed on the first screening matrix and the second screening matrix, so that the fixed operations can accurately obtain the field values of each dimension and each granularity under the association relationship between the main table and the dimension table, improving the reliability of the data verification results of the upstream and downstream tables.

[0065] It is also possible to determine the data integrity of the downstream table by comparing the field values of each dimension and each granularity under the association relationship with the field values obtained by running the sql development script, and optimize the sql development script according to the actual situation. BRIEF DESCRIPTION OF THE DRAWINGS

[0066] In order to more clearly illustrate the technical solutions in the embodiments of the present application or the prior art, the following will briefly introduce the accompanying drawings required for the description of the embodiments or the prior art. Obviously, the accompanying drawings in the following description are some embodiments of the present application. For those of ordinary skill in the art, other accompanying drawings can be obtained based on these drawings without creative efforts.

[0067] Figure 1 It is a schematic diagram of an application scenario provided by an embodiment of the present application;

[0068] Figure 2 It is a flowchart of a method for verifying data of upstream and downstream tables provided by an embodiment of the present application;

[0069] Figure 3 It is a schematic diagram of a sql development script case provided by an embodiment of the present application;

[0070] Figure 4 It is a schematic flow diagram of a method for parsing a sql development script provided by an embodiment of the present application;

[0071] Figure 5 It is a schematic diagram of the structure of a data verification device provided by an embodiment of the present application;

[0072] Figure 6 It is a schematic diagram of the structure of an electronic device provided by an embodiment of the present application. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0073] To make the objectives, technical solutions, and advantages of the embodiments of the present application clearer, the following will clearly and completely describe the technical solutions in the embodiments of the present application with reference to the accompanying drawings in the embodiments of the present application. Obviously, the described embodiments are some, but not all, of the embodiments of the present application. All other embodiments obtained by those of ordinary skill in the art based on the embodiments of the present application without creative efforts shall fall within the scope of protection of the present application.

[0074] In addition, the term "and / or" in this text is merely a description of the association relationship between associated objects, indicating that there can be three relationships. For example, A and / or B can represent three situations: A exists alone, A and B exist simultaneously, and B exists alone. In addition, the character " / " in this text generally represents an "or" relationship between the front and rear associated objects unless otherwise specified.

[0075] The embodiments of the present application will be further described in detail below with reference to the accompanying drawings of the specification.

[0076] Currently, the data quality control (DQC) monitoring rules in the data quality module of the Alibaba Cloud DataWorks product only perform verification on table-level data and field-level data in a single table. As time goes by, within a certain specified time period, it is determined whether there are quality problems with the data obtained from the data in the aforementioned table based on whether the fluctuation range of the field values in the aforementioned table is abnormal, and it is impossible to verify whether the data in the upstream and downstream tables matches.

[0077] Based on this, the present application provides a method, device, electronic device, and medium for data verification of upstream and downstream tables, which can complete the automatic verification of comprehensive data in upstream and downstream tables and improve the data verification efficiency.

[0078] Figure 1 FIG. is a schematic diagram of an application scenario provided by the present application, including a data warehouse and an electronic device. When a certain user operates the electronic device to perform data verification on a pair of upstream and downstream tables in the data warehouse, the method of the present application is used for verification. Multiple servers form a distributed cluster, and the networks between the clusters are interconnected. The master node namenode allocates the computing tasks of the sql development script through functions, and each server node datanode stores the data distributively. Hive is a data warehouse tool that can map structured data files into a database table and provide a complete sql query function. The data of the table is stored in Hive, and the Hive data exists in the directory files of each datanode. Through the construction of the table in Hive, a data warehouse is formed. In Figure 1 the application scenario, the electronic device is a computer, and in other scenarios, it can also be other devices. Specifically, the user selects a pair of upstream and downstream tables, searches for the sql development script for obtaining the downstream table from the upstream table, obtains the association relationship between the main table and the dimension table corresponding to the sql development script through parsing, specifies the first and second preset fields, obtains the corresponding data combinations from the main table and the dimension table to form a matrix and processes the matrix, and verifies the matching of the data in the upstream and downstream tables by comparing the field values obtained from the processing with the field values obtained from the running of the sql development script, thereby improving the verification efficiency. The specific implementation method can refer to the following embodiments.

[0079] Figure 2The flowchart of a data verification method for upstream and downstream tables provided by an embodiment of the present application. The method of this embodiment can be applied to a data warehouse in the above scenarios. As Figure 2 shown, the method includes:

[0080] S201. Analyze the SQL development script to obtain the association relationship between the main table and the dimension table corresponding to the SQL development script; the association relationship includes the association dimension and the association condition; the association condition includes the association granularity and the association sub-dimension.

[0081] In some scenarios, the user selects a pair of upstream and downstream tables for data verification, and analyzes the SQL development script obtained from the upstream table to the downstream table according to this pair of upstream and downstream tables.

[0082] In other scenarios, the user selects an SQL development script for analysis to determine the table corresponding to the SQL development script.

[0083] Specifically, each programming language statement in the SQL development script has a corresponding meaning. As Figure 3 shown, select... from... means query from a certain table, and left join... on... means left join. Therefore, by analyzing the SQL development script, the program statements corresponding to the association relationship are found, and the association relationship between the corresponding main table and the dimension table is obtained. The association relationship includes: the association dimension, that is, the dimension under which the main table and the dimension table are associated; the association condition, that is, the other conditions under which the main table and the dimension table are associated. The association condition includes: the association granularity: that is, the granularity under which the main table and the dimension table are associated; the association sub-dimension, that is, the sub-dimension under which the main table and the dimension table are associated. Here, the dimension is the parent of the sub-dimension, and the characteristics of the dimension are inherited by the sub-dimension, while the dimension may not have the characteristics of the sub-dimension, that is, the sub-dimension adds new characteristics on the basis of the dimension.

[0084] S202. According to the first preset required fields, obtain the corresponding measurement fields and the field values of each dimension and each granularity corresponding to the measurement fields from the main table, and obtain the matrix corresponding to the field values of the main table at each dimension and each granularity.

[0085] Specifically, the first preset requirement field is a key field in the main table. The purpose is to obtain the required content in the main table according to the first preset requirement field, including measurement fields, dimensions, granularity, and field values at each granularity of each dimension. Obtain the corresponding content in the main table according to the first preset requirement field, where the field value at each granularity of each dimension is the specific numerical value corresponding to the measurement field, and the measurement value at each granularity of each dimension has a unit. The field values of the main table at each granularity of each dimension are summarized into a corresponding matrix. Assuming that the matrix is a matrix with m rows and n columns, the first row and first column of the matrix corresponds to the field value of the main table at the first granularity of the first dimension, the first row and second column corresponds to the field value of the main table at the second granularity of the first dimension... The mth row and nth column correspond to the field value of the main table at the nth granularity of the mth dimension.

[0086] In some embodiments, customer service claims data is taken as an example. The fine amount of the complained party under the first-level complaint type in a certain month is calculated. The main table is the claim fact table, and the dimension table is the complaint type table. Based on the first preset requirement fields, the following are obtained: measurement field, fine amount; dimension, first-level complaint type of the complained party; granularity, a certain month; measurement values under each dimension and granularity, the fine amount of the complained province in a certain month.

[0087] S203. According to the second preset requirement field, obtain the corresponding unique key and the number of unique keys in each dimension and each sub-dimension corresponding to the unique key from the dimension table, and obtain a matrix corresponding to the number of unique keys in each dimension and each sub-dimension of the dimension table.

[0088] Specifically, the second preset requirement field is a key field in the dimension table, and the purpose is to obtain the required content in the dimension table according to the second preset requirement field, including unique keys, dimensions, sub-dimensions, and the number of unique keys under each dimension and each sub-dimension. The corresponding content is obtained in the dimension table according to the second preset requirement field, wherein the number of unique keys under each dimension and each sub-dimension is the specific number corresponding to the unique key. The number of unique keys in each dimension and each sub-dimension of the dimension table is summarized into a corresponding matrix. Assuming that the matrix is a matrix with m rows and n columns, the first row and the first column of the matrix corresponds to the number of unique keys in the first dimension and the first sub-dimension of the dimension table, the first row and the second column corresponds to the number of unique keys in the first dimension and the second sub-dimension of the main table... The mth row and the nth column corresponds to the number of unique keys in the mth dimension and the nth sub-dimension of the main table.

[0089] In some embodiments, customer service claims data is taken as an example. The fine amount of the complained party under the first-level complaint type in a certain month is calculated. The main table is the claim fact table, and the dimension table is the complaint type table. Based on the second preset requirement fields, the following are obtained: unique key, number of complaint types; dimension, first-level complaint type of the complained party; sub-dimension, second-level complaint type of the complained party; number of unique keys under each sub-dimension of each dimension, and number of complaint types under each first-level complaint type and each second-level complaint type.

[0090] S204. Based on the association relationship between the main table and the dimension table, process the matrix corresponding to the field values of the main table at each dimension and each granularity and the matrix corresponding to the number of unique keys of the dimension table at each dimension and each sub-dimension to obtain the field values at each dimension and each granularity under the association relationship.

[0091] Specifically, based on the association relationship between the main table and the dimension table obtained by parsing the SQL data script, determine the dimensions, sub-dimensions, and granularities at which the main table and the dimension table are associated. Based on this association relationship, screen and calculate the matrix corresponding to the field values of the main table at each dimension and each granularity and the matrix corresponding to the number of unique keys of the dimension table at each dimension and each sub-dimension to obtain the field values of the main table and the dimension table at each dimension and each granularity under this association relationship.

[0092] In some embodiments, taking the data of customer service claims as an example. Calculate the fine amount of the complained party under the first-level complaint type in a certain month. The main table is the claim fact table, and the dimension table is the complaint type table. The main table and the dimension table are associated at the dimension of the first-level complaint type of the complained party, the sub-dimension of the second-level complaint type of the complained party, and the granularity of a certain month. Based on this association relationship, screen the matrix corresponding to the fine amount of the claim fact table under the first-level complaint type of the complained party in a certain month and the matrix corresponding to the number of complaint types of the complaint type table under the first-level complaint type and the second-level complaint type in the cloud to obtain the fine amount of the claim fact table and the complaint type table under the first-level complaint type of the complained party in a certain month under this association relationship.

[0093] S205. Compare the field values at each dimension and each granularity under the association relationship with the field values obtained by running the SQL development script. If they are consistent, it indicates that the data in the main table matches the data in the downstream table obtained by running the SQL development script on the main table.

[0094] Specifically, the field values at each dimension and each granularity under the association relationship are obtained by processing the relevant matrices based on the association relationship between the main table and the dimension table analyzed from the SQL data script.

[0095] Compare the field values at each dimension and each granularity under the correlation relationship with the field values obtained by running the SQL development script. At this time, the field values obtained by running the SQL development script correspond to the data in the downstream table. The field values at each dimension and each granularity under the correlation relationship are obtained by parsing the correlation relationship between the main table and the dimension table from the SQL development script. When the field values at each dimension and each granularity under the correlation relationship are consistent with the field values obtained by running the SQL development script, the process of comparing the field values at each dimension and each granularity under the correlation relationship with the field values obtained by running the SQL development script is a process of judging whether the data in the downstream table obtained by different methods match. A consistent comparison result indicates that the data in the upstream table (main table) matches the data in the downstream table obtained by running the SQL development script.

[0096] The data verification method for upstream and downstream tables provided in this embodiment is applied to a data warehouse, which contains several tables, including a main table and a dimension table. Parsing the SQL development script can obtain the correlation relationship between the corresponding main table and dimension table in the SQL development script; it is stipulated that the main table and the dimension table correspond to different preset required fields, namely the first preset required field and the second preset required field; according to the preset required fields, the corresponding data in the main table and the dimension table can be obtained respectively to obtain the corresponding matrix; processing the matrix according to the correlation relationship can obtain the field values at each dimension and each granularity under the correlation relationship. The field values at each dimension and each granularity under the correlation relationship are obtained after parsing and processing the SQL development script, representing the standard data in the downstream table derived based on relevant logic; while the field values obtained by running the SQL development script are obtained by running the SQL development script from the upstream table (main table) to the downstream table, representing the actual data in the downstream table obtained based on the actual operation of the script. Therefore, by comparing the field values at each dimension and each granularity under the correlation relationship with the field values obtained by running the SQL development script, the accuracy of the process of parsing and running the SQL development script can be verified, representing the matching situation of the data obtained from the main table under the correlation relationship between the main table and the dimension table and the data in the downstream table obtained by running the SQL development script from the upstream table (main table). If the comparison result is consistent, it indicates that the data in the upstream and downstream tables match, achieving a comprehensive verification of the data under the correlation relationship and improving the verification efficiency.

[0097] In some embodiments, the above-mentioned parsing of the SQL development script to obtain the correlation relationship between the corresponding main table and dimension table in the SQL development script specifically includes: searching for the first key field in the SQL development script; judging whether the fields connected by the first key field belong to two different tables; if so, respectively determining the two different tables as the main table corresponding to the SQL development script and the dimension table corresponding to the main table, and determining the dimension corresponding to the field connected by the first key field as the correlation dimension between the main table and the dimension table.

[0098] Specifically, in this embodiment, the data of a pair of upstream and downstream tables is verified. The corresponding SQL development script is searched according to the upstream table and parsed. The first key field is a manually defined name, corresponding to the field in the SQL development script that can represent the association relationship between the two tables. Take Figure 3 the SQL development script in Figure 3 as an example. Search for the first "on" in the SQL development script, and judge whether the fields connected by "=" following the "on" belong to two different tables. The "=" following the "on" is the first key field. From

[0099] it can be seen that the field in front of the first key field belongs to table a, and the field behind belongs to table b. It can be seen that the fields connected by the first key field belong to two different tables, one of which is the main table corresponding to this SQL development script, and the other is the corresponding dimension table. Figure 3 The first table following the first occurrence of the "from" in the SQL running script is the main table. From

[0100] it can be seen that table a is the main table. Then table b is the dimension table, and table a and table b are associated in a certain dimension. The associated dimension here is the dimension col1 corresponding to the field connected by the first key field, which is both a dimension in the main table and a dimension in the dimension table. At this time, the main table and the dimension table are associated under the dimension col1.

[0101] In some other embodiments, given an SQL development script, the tables corresponding to the SQL development script and the association relationship between the tables are determined by parsing this SQL development script.

[0101] This embodiment determines whether there is an association between the main table and the dimension table in a certain dimension by searching for the first key field. If so, in what dimension, providing the association basis of the relevant main table and dimension table for the data verification of the upstream and downstream tables.

[0102] In some embodiments, parsing the SQL development script to obtain the association relationship between the main table and the dimension table corresponding to the SQL development script further includes: searching for the second key field in the SQL development script; judging whether the fields connected by the second key field belong to the same table; if so, determining the association condition between the main table and the dimension table according to the fields connected by the second key field.

[0103] Specifically, the second key field is a manually defined name, corresponding to the field in the SQL development script that can represent the association condition between the two tables. Take Figure 3 the SQL development script in

[0104] as an example. Search for the first "and" in the SQL running script, and judge whether the fields connected by "=" following the "and" belong to the same table. The "=" following the "and" is the second key field. Figure 3It can be seen that the fields in front of the second key field belong to Table B, and the fields behind do not belong to any table. Thus, it can be seen that the fields connected by the second key field belong to the same table B, and according to the analysis in the previous embodiment, this table B is the dimension table corresponding to the SQL development script. The main table and the dimension table are associated under a certain association condition. Here, the association condition is the association condition di1 corresponding to the fields connected by the second key field. At this time, the main table and the dimension table are associated under the association condition di1.

[0105] In some other embodiments, the association condition can also be determined by searching for the words "where" or "having" in the SQL running script.

[0106] In this embodiment, the second key field is searched to determine whether there is an association condition between the main table and the dimension table, providing a relevant association basis for the data verification of the upstream and downstream tables between the main table and the dimension table.

[0107] In some embodiments, determining the association condition between the main table and the dimension table according to the fields connected by the second key field specifically includes: if the same table is the main table, determining the granularity corresponding to the fields connected by the second key field as the association granularity between the main table and the dimension table; if the same table is the dimension table, determining the sub-dimension corresponding to the fields connected by the second key field as the association sub-dimension between the main table and the dimension table.

[0108] After determining the association condition between the main table and the dimension table according to the attribution of the fields connected by the second key field, the specific association condition is judged. If the fields connected by the second key field belong to the main table, then the fields connected at this time correspond to the granularity of the main table, indicating that the main table and the dimension table are associated at this granularity.

[0109] If the fields connected by the second key field belong to the dimension table, then the fields connected at this time correspond to the sub-dimension of the dimension table, indicating that the main table and the dimension table are associated at this sub-dimension. It can be Figure 3 seen that the fields connected after the first "and" word in this SQL parsing script belong to Table B, which is the dimension table. The corresponding association sub-dimension here is dimension di1. Therefore, the main table and the dimension table corresponding to this SQL development script are associated at the sub-dimension di1.

[0110] This embodiment judges the specific association condition. When parsing the SQL development script, the association condition is different when the fields connected by the second key field belong to different tables, providing a more accurate association basis for the data verification range of the upstream and downstream tables between the relevant main table and the dimension table.

[0111] In some embodiments, based on the association relationship between the main table and the dimension table, the matrices corresponding to the field values of the main table at each dimension and each granularity and the matrices corresponding to the number of unique keys of the dimension table at each dimension and each sub-dimension are processed to obtain the field values at each dimension and each granularity under the association relationship, which specifically includes: based on the association relationship between the main table and the dimension table, processing the matrix corresponding to the field values of the main table at each dimension and each granularity to obtain a first screening matrix; based on the association relationship between the main table and the dimension table, processing the matrix corresponding to the number of unique keys of the dimension table at each dimension and each sub-dimension to obtain a second screening matrix; and obtaining the field values at each dimension and each granularity under the association relationship according to the first screening matrix and the second screening matrix.

[0112] The first screening matrix and the second screening matrix are names defined artificially. The association relationship between the main table and the dimension table includes association dimensions, association granularities, and / or association sub-dimensions. The first screening matrix is obtained by screening the matrix corresponding to the field values of the main table at each dimension and each granularity according to the association relationship between the main table and the dimension table; the second screening matrix is obtained by screening the matrix corresponding to the number of unique keys of the dimension table at each dimension and each sub-dimension according to the association relationship between the main table and the dimension table.

[0113] If the table corresponding to the association condition is the main table, then the association condition is the granularity of the main table. At this time, the association dimension and the association granularity are the association relationship between the main table and the dimension table. Screening the matrix corresponding to the field values of the main table at each dimension and each granularity to obtain the screened matrix, which is the first screening matrix; if the table corresponding to the association condition is the dimension table, then the association condition is the sub-dimension of the dimension table. At this time, the association dimension and the association sub-dimension are the association relationship between the main table and the dimension table. Screening the matrix corresponding to the number of unique keys of the dimension table at each dimension and each sub-dimension to obtain the screened matrix, which is the second screening matrix.

[0114] According to the screened matrices, that is, the first screening matrix and the second screening matrix, the field values of each dimension and each granularity of the main table and the dimension table under the association relationship are processed and obtained.

[0115] In this embodiment, the matrices corresponding to the field values of the main table at each dimension and each granularity and the matrices corresponding to the number of unique keys of the dimension table at each dimension and each sub-dimension are screened, and the range of the data in the downstream table is obtained, providing an accurate range for the data verification between the upstream and downstream tables and improving the data verification efficiency.

[0116] In some embodiments, the above-mentioned obtaining the field values at each dimension and each granularity under the association relationship according to the first screening matrix and the second screening matrix specifically includes: multiplying the transposed matrix of the second screening matrix by the first screening matrix, and the obtained matrix corresponds to the field values at each dimension and each granularity under the association relationship.

[0117] The first screening matrix is the field values of each dimension and each granularity of the main table under the association relationship, and the second screening matrix is the number of unique keys of each dimension and each sub-dimension of the dimension table under the association relationship.

[0118] The first screening matrix is obtained by screening the matrix corresponding to the field values of the main table at each dimension and each granularity, and the second screening matrix is obtained by screening the matrix corresponding to the field values of the dimension table at each dimension and each sub-dimension. Suppose the matrices corresponding to the field values of the main table at each dimension and each granularity and the matrices corresponding to the field values of the dimension table at each dimension and each sub-dimension are respectively:

[0119]

[0120] a 11 Represents the field value of the main table at dimension 1 and granularity 1, corresponding to a mn Represents the field value of the main table at dimension m and granularity n. Matrix A represents the summary of the field values of the main table at dimensions 1 - m and granularities 1 - n; Similarly, x 11 Represents the number of unique keys of the dimension table at dimension 1 and sub-dimension 1, corresponding to x mn Represents the number of unique keys of the dimension table at dimension m and sub-dimension n. Matrix B represents the summary of the number of unique keys of the dimension table at dimensions 1 - m and sub-dimensions 1 - n.

[0121] The association relationship between the main table and the dimension table is obtained by parsing the SQL development script. Suppose the associated dimension between the main table and the dimension table reaches dimension i and the associated granularity reaches granularity j. Then the first screening matrix and the second screening matrix obtained after screening are respectively:

[0122]

[0123] At this time, matrix A2 corresponds to the first screening matrix, and matrix X2 corresponds to the second screening matrix. a 11 Represents the field value of the main table at dimension 1 and granularity 1, corresponding to a ij Represents the field value of the main table at dimension i and granularity j. The first screening matrix represents the summary of the field values of the main table at dimensions 1 - i and granularities 1 - j; Similarly, x 11 Represents the number of unique keys of the dimension table at dimension 1 and sub-dimension 1, corresponding to x in Represents the number of unique keys of the dimension table at dimension i and sub-dimension n. The second screening matrix represents the summary of the number of unique keys of the dimension table at dimensions 1 - i and sub-dimensions 1 - n.

[0124] Suppose the associated dimension between the main table and the dimension table reaches dimension i and the associated sub-dimension reaches sub-dimension k. Then the first screening matrix and the second screening matrix obtained after screening are respectively:

[0125]

[0126] At this time, matrix A3 corresponds to the first screening matrix, and matrix X3 corresponds to the second screening matrix. a 11 represents the field value of the main table at the 1st dimension and 1st granularity, corresponding to a in represents the field value of the main table at the i-th dimension and n-th granularity. The first screening matrix represents the summary of the field values of the main table at dimensions 1 - i and granularities 1 - j; similarly, x 11 represents the number of unique keys of the dimension table at the 1st dimension and 1st sub-dimension, corresponding to x ik represents the number of unique keys of the dimension table at the i-th dimension and k-th sub-dimension. The second screening matrix represents the summary of the number of unique keys of the dimension table at dimensions 1 - i and sub-dimensions 1 - k.

[0127] If the fields in the sql development script that can be used to represent the association conditions between the two tables are only the fields belonging to the main table, that is, the association condition is only the association granularity, then the association sub-dimension between the main table and the dimension table is the full dimension; if the fields in the sql development script that can be used to represent the association conditions between the two tables are only the fields belonging to the dimension table, that is, the association condition is only the association sub-dimension, then the association granularity between the main table and the dimension table is the full granularity.

[0128] The measurement value is multiplied by the quantity to obtain the total measurement value. Therefore, multiplying the first screening matrix by the second screening matrix can obtain the final measurement value. However, according to the matrix multiplication algorithm, only by transposing the second screening matrix can the main tables in the first screening matrix and the second screening matrix correspond and multiply in the same dimension, so as to obtain the field values of the main table and the dimension table at each dimension and each granularity under the association relationship.

[0129] In this embodiment, a fixed operation is performed on the first screening matrix and the second screening matrix. From this fixed operation, the field values of the main table and the dimension table at each dimension and each granularity under the association relationship can be accurately obtained, improving the reliability of the data verification results of the upstream and downstream tables.

[0130] In some embodiments, if the field values at each dimension and each granularity under the association relationship are inconsistent with the field values obtained by running the sql development script, then the number of dimensions under the association relationship and the number of dimensions obtained by running the sql development script are used to determine the data integrity of the downstream table obtained by running the sql development script for the main table.

[0131] The field values of each dimension and each granularity under the association relationship between the main table and the dimension table are obtained by matrix multiplication, so it is also a matrix. From the field values of each dimension and each granularity under the association relationship, it is possible to intuitively see which dimensions are associated. The field values obtained by running the corresponding SQL development script can also intuitively show which dimensions are in the downstream table. By comparing the number of dimensions included in the field values of each dimension and each granularity under the association relationship with the number of dimensions included in the field values obtained by running the SQL development script, it can be determined whether the number of dimensions from the main table to the downstream table through running the SQL development script matches, thereby obtaining the data integrity. If it is incomplete, it is also possible to determine what dimensions of data are missing.

[0132] In this embodiment, by comparing the field values of each dimension and each granularity under the association relationship with the field values obtained by running the SQL development script, the data integrity of the downstream table can also be determined.

[0133] In some other embodiments, the comparison result shows that the number of dimensions does not match. Corresponding to finding out which dimensions are missing, locate them in the SQL development script. If the running statements of the missing dimensions are missing in the SQL development script, make targeted modifications. If the running statements in the SQL development script are all normal, the SQL development script can be re-run for detection, and problems that may occur during the running process can be detected and processed.

[0134] In some other embodiments, if the comparison between the number of dimensions included in the field values of each dimension and each granularity under the association relationship and the number of dimensions in the field values obtained by running the SQL development script is inconsistent, it indicates that the data in the corresponding downstream table is incomplete. It is possible to intuitively determine what dimensions of data are missing in the downstream table and make targeted modifications.

[0135] In some other embodiments, such as Figure 4 shown, the parsing method of the SQL development script of the present application can be carried out step by step and hierarchically in the data warehouse. Taking the SQL case (SQL development script) in Figure 4 as an example, the association relationship between the fact table t1 (main table), the dimension table t2 (dimension table), and the dimension table t3 (dimension table) is obtained by parsing the SQL development script.

[0136] The first stage: In the data warehouse, each table needs to provide matrix delivery standards (the first preset required fields and the second preset required fields). Determine the unique key (combination) of the table, determine the dimensions, granularity, and the summary of the number of unique keys (combinations) of each dimension and each granularity, determine the measurement fields and the corresponding measurement units of the measurement fields, and determine the summary of the measurement values (field values of each dimension and each granularity) of each dimension and each granularity.

[0137] The second stage: Analyze the association relationships of the tables in the case. Through the fixed algorithms of the fact table (main table) and the dimension table (dimension table), obtain the statistical results of the measurement values (field values) under each dimension. Determine the main table: The main way is to define the first table (or nested table) that appears immediately after the word "from" (not within parentheses) in the SQL as the main table; Determine the dimension table: Except for the main table, all can be defined as the dimension table (which can be a nested table); Determine the association relationship between the main table and the dimension table: Analyze the word "on" in the SQL, and if the fields before and after the following "=" belong to two different tables respectively, then it is determined that the main table and the dimension table are associated in a certain dimension, and this field is a dimension value in the main table and also a dimension value in the dimension table; Determine the association conditions between the main table and the dimension table: Analyze the words "on" or "and" or "where" or "having" in the SQL, and if the fields before and after the following "=" belong to the same table, it represents the filtering conditions of the main table or the dimension table respectively.

[0138] The third stage: Budget result value. Determine the association relationship between the main table and the dimension table, and use the formula: AX T (Multiply the transposed matrix of the second screening matrix by the first screening matrix) Calculate to obtain the statistical values (field values of each dimension and each granularity under the association relationship) of the measurement indicators id (unique key), amount1, amount2 (measurement fields) in the fact table (main table) and under the dimension table col1, col2, di1, di2, di3 (each dimension).

[0139] The fourth stage: Compare the case operation results (field values of each dimension and each granularity under the association relationship) with the fixed results (field values obtained by running the SQL development script) to verify the data accuracy and data integrity. Note: Data integrity refers to the ratio of the number of dimensions of the operation results to the total number of dimensions of the fixed algorithm.

[0140] Through the definition of mathematical matrices, data quality verification is solved. It is also possible to store a matrix corresponding to each table in the data warehouse, accurately distinguish the fact table and the dimension table; It is also possible to use matrix operations through the matrices corresponding to the tables to obtain the summary measurement values after various combinations of the fact table and the dimension table, and predict the measurement values of each dimension and each granularity in the data domain of the data warehouse. If the association conditions between the main table and the dimension table cannot be obtained from the SQL development script, the statistical values of the measurement fields under each dimension and each granularity calculated by the formula are the statistical values of the measurement fields under the full dimensions and full granularities (field values of the full dimensions and full granularities under the association relationship). By running the SQL development script corresponding to the downstream table obtained from the upstream table (main table), the data in the downstream table is within the range of the field values of the full dimensions and full granularities under the association relationship. From this, the accurate range of the dimension granularity in the data domain of the data warehouse can be predicted.

[0141] Figure 5The following is a schematic structural diagram of a data verification device provided by an embodiment of the present application. As Figure 5 shown, the data verification device 500 in this embodiment includes: a parsing module 501, a first matrix forming module 502, a second matrix forming module 503, a processing module 504, and a comparison module 505.

[0142] The parsing module 501 is configured to parse the sql development script to obtain the association relationship between the main table and the dimension table corresponding to the sql development script; the association relationship includes an association dimension and an association condition; the association condition includes an association granularity and an association sub-dimension.

[0143] The first matrix forming module 502 is configured to obtain the corresponding measurement field and the field values of the measurement field at each dimension and each granularity from the main table according to the first preset required fields, and obtain the matrix corresponding to the field values of the main table at each dimension and each granularity.

[0144] The second matrix forming module 503 is configured to obtain the corresponding unique key and the number of unique keys of the unique key at each dimension and each sub-dimension from the dimension table according to the second preset required fields, and obtain the matrix corresponding to the number of unique keys of the dimension table at each dimension and each sub-dimension.

[0145] The processing module 504 is configured to process the matrix corresponding to the field values of the main table at each dimension and each granularity and the matrix corresponding to the number of unique keys of the dimension table at each dimension and each sub-dimension based on the association relationship between the main table and the dimension table, and obtain the field values at each dimension and each granularity under the association relationship.

[0146] The comparison module 505 is configured to compare the field values at each dimension and each granularity under the association relationship with the field values obtained by running the sql development script. When they are consistent, it indicates that the data in the main table matches the data in the downstream table obtained by running the sql development script on the main table.

[0147] Optionally, the parsing module 501 is specifically configured to:

[0148] Search for the first key field in the sql development script;

[0149] Determine whether the fields connected by the first key field belong to two different tables;

[0150] When yes, respectively determine the two different tables as the main table corresponding to the sql development script and the dimension table corresponding to the main table, and determine the dimension corresponding to the field connected by the first key field as the association dimension between the main table and the dimension table.

[0151] Optionally, the parsing module 501 is specifically configured to:

[0152] Find the second key field from the SQL development script;

[0153] Determine whether the fields connected by the second key field belong to the same table;

[0154] When it is, determine the association condition between the main table and the dimension table according to the fields connected by the second key field.

[0155] Optionally, the parsing module 501 is specifically configured to:

[0156] When the same table is the main table, determine the granularity corresponding to the field connected by the second key field as the association granularity between the main table and the dimension table;

[0157] When the same table is the dimension table, determine the sub-dimension corresponding to the field connected by the second key field as the association sub-dimension between the main table and the dimension table.

[0158] Optionally, the processing module 504 is specifically configured to:

[0159] Process the matrix corresponding to the field values of the main table at each dimension and each granularity based on the association relationship between the main table and the dimension table to obtain a first screening matrix;

[0160] Process the matrix corresponding to the number of unique keys of the dimension table at each dimension and each sub-dimension based on the association relationship between the main table and the dimension table to obtain a second screening matrix;

[0161] Obtain the field values at each dimension and each granularity under the association relationship according to the first screening matrix and the second screening matrix.

[0162] Optionally, when the processing module 504 obtains the field values at each dimension and each granularity under the association relationship according to the first screening matrix and the second screening matrix, it is specifically configured to:

[0163] Multiply the transposed matrix of the second screening matrix by the first screening matrix, and the obtained matrix corresponds to the field values at each dimension and each granularity under the association relationship.

[0164] Optionally, the data verification device 500 further includes a data integrity determination module 505, which is used to:

[0165] When the field values at each dimension and each granularity under the association relationship are inconsistent with the field values obtained by running the SQL development script, compare the number of dimensions under the association relationship with the number of dimensions obtained by running the SQL development script, and determine the data integrity in the downstream table obtained by running the main table through the SQL development script.

[0166] The device of this embodiment can be used to execute the method of any of the above embodiments. The implementation principles and technical effects are similar and will not be elaborated here.

[0167] Figure 6 As shown in the structural schematic diagram of an electronic device provided by an embodiment of the present application, Figure 6 As shown, the electronic device 600 of this embodiment may include: a memory 601 and a processor 602.

[0168] A computer program capable of being loaded and executed by the processor 602 to execute the method in the above embodiment is stored on the memory 601.

[0169] Among them, the processor 602 is connected to the memory 601, such as being connected through a bus.

[0170] Optionally, the electronic device 600 may further include a transceiver. It should be noted that in practical applications, the number of transceivers is not limited to one, and the structure of the electronic device 600 does not constitute a limitation on the embodiments of the present application.

[0171] The processor 602 may be a CPU (Central Processing Unit), a general-purpose processor, a DSP (Digital Signal Processor), an ASIC (Application Specific Integrated Circuit), an FPGA (Field Programmable Gate Array), or other programmable logic devices, transistor logic devices, hardware components, or any combination thereof. It can implement or execute various exemplary logic blocks, modules, and circuits described in connection with the disclosure of the present application. The processor 602 may also be a combination that implements computing functions, such as a combination of one or more microprocessors, a combination of a DSP and a microprocessor, etc.

[0172] The bus may include a path for transmitting information between the above components. The bus may be a PCI (Peripheral Component Interconnect) bus or an EISA (Extended Industry Standard Architecture) bus, etc. The bus may be divided into an address bus, a data bus, a control bus, etc. For the sake of convenience of representation, only a thick line is used in the figure, but it does not mean that there is only one bus or one type of bus.

[0173] The memory 601 can be a ROM (Read Only Memory), or other types of static storage devices that can store static information and instructions, a RAM (Random Access Memory), or other types of dynamic storage devices that can store information and instructions. It can also be an EEPROM (Electrically Erasable Programmable Read Only Memory), a CD-ROM (Compact Disc Read Only Memory), or other optical disc storage, optical disc storage (including compact discs, laser discs, optical discs, digital versatile discs, Blu-ray discs, etc.), magnetic disk storage media, or other magnetic storage devices, or any other medium that can be used to carry or store the desired program code in the form of instructions or data structures and can be accessed by a computer, but is not limited thereto.

[0174] The memory 601 is used to store the application program code for executing the solution of this application, and is controlled and executed by the processor 602. The processor 602 is used to execute the application program code stored in the memory 601 to implement the content shown in the foregoing method embodiments.

[0175] Among them, the electronic device includes but is not limited to: mobile terminals such as mobile phones, laptop computers, digital broadcast receivers, PDAs (Personal Digital Assistants), PADs (Tablet Computers), PMPs (Portable Multimedia Players), vehicle-mounted terminals (such as vehicle-mounted navigation terminals), etc., and fixed terminals such as digital TVs, desktop computers, etc. It can also be a server, etc. Figure 6 The electronic device shown is only an example and should not impose any limitations on the functions and usage scope of the embodiments of this application.

[0176] The electronic device of this embodiment can be used to execute the method of any of the foregoing embodiments, and its implementation principle and technical effects are similar and will not be elaborated here.

[0177] This application also provides a computer-readable storage medium storing a computer program that can be loaded and executed by a processor to execute the method in the foregoing embodiments.

[0178] Those of ordinary skill in the art can understand that all or part of the steps for implementing the foregoing method embodiments can be completed by hardware related to program instructions. The foregoing program can be stored in a computer-readable storage medium. When the program is executed, it executes the steps including the foregoing method embodiments; and the foregoing storage medium includes: ROM, RAM, magnetic disks, or optical discs and other media that can store program code.

Claims

1. A data verification method for upstream and downstream tables, characterized in that, Applied to a data warehouse, the data warehouse contains several tables, and the tables include a main table and dimension tables; the method includes: Parsing an SQL development script to obtain the association relationship between the main table and the dimension tables corresponding to the SQL development script; the association relationship includes an association dimension and an association condition; the association condition includes an association granularity and an association sub-dimension; According to the first preset required fields, obtaining the corresponding measurement fields from the main table and the field values of each dimension and each granularity corresponding to the measurement fields, to obtain a matrix corresponding to the field values of the main table at each dimension and each granularity; According to the second preset required fields, obtaining the corresponding unique keys from the dimension tables and the number of unique keys of each dimension and each sub-dimension corresponding to the unique keys, to obtain a matrix corresponding to the number of unique keys of the dimension tables at each dimension and each sub-dimension; Based on the association relationship between the main table and the dimension tables, processing the matrix corresponding to the field values of the main table at each dimension and each granularity and the matrix corresponding to the number of unique keys of the dimension tables at each dimension and each sub-dimension, to obtain the field values at each dimension and each granularity under the association relationship; Comparing the field values at each dimension and each granularity under the association relationship with the field values obtained by running the SQL development script, if they are consistent, it indicates that the data in the main table matches the data in the downstream table obtained by running the SQL development script on the main table.

2. The method according to claim 1, wherein The parsing of the SQL development script to obtain the association relationship between the main table and the dimension tables corresponding to the SQL development script includes: Searching for a first key field in the SQL development script; Determining whether the fields connected by the first key field belong to two different tables; If so, respectively determining the two different tables as the main table corresponding to the SQL development script and the dimension table corresponding to the main table, and determining the dimension corresponding to the field connected by the first key field as the association dimension between the main table and the dimension table.

3. The method according to claim 2, wherein The parsing of the SQL development script to obtain the association relationship between the main table and the dimension tables corresponding to the SQL development script further includes: Searching for a second key field in the SQL development script; Determining whether the fields connected by the second key field belong to the same table; If so, determining the association condition between the main table and the dimension table according to the fields connected by the second key field.

4. The method according to claim 3, wherein The determining of the association condition between the main table and the dimension table according to the fields connected by the second key field includes: If the same table is the main table, determining the granularity corresponding to the field connected by the second key field as the association granularity between the main table and the dimension table; If the same table is the dimension table, determining the sub-dimension corresponding to the field connected by the second key field as the association sub-dimension between the main table and the dimension table.

5. The method according to claim 1, wherein The processing of the matrix corresponding to the field values of the main table at each dimension and each granularity and the matrix corresponding to the number of unique keys of the dimension tables at each dimension and each sub-dimension based on the association relationship between the main table and the dimension tables, to obtain the field values at each dimension and each granularity under the association relationship, includes: Based on the association relationship between the main table and the dimension table, process the matrix corresponding to the field values of the main table at each dimension and each granularity to obtain a first screening matrix; Based on the association relationship between the main table and the dimension table, process the matrix corresponding to the number of unique keys of the dimension table at each dimension and each sub-dimension to obtain a second screening matrix; According to the first screening matrix and the second screening matrix, obtain the field values at each dimension and each granularity under the association relationship.

6. The method according to claim 5, wherein The step of obtaining the field values at each dimension and each granularity under the association relationship according to the first screening matrix and the second screening matrix includes: Multiply the transposed matrix of the second screening matrix by the first screening matrix, and the obtained matrix corresponds to the field values at each dimension and each granularity under the association relationship.

7. The method according to any one of claims 1-6, characterized in that, It further includes: If the field values at each dimension and each granularity under the association relationship are inconsistent with the field values obtained by running the SQL development script, then compare the number of dimensions under the association relationship with the number of dimensions obtained by running the SQL development script to determine the data integrity in the downstream table obtained by running the main table through the SQL development script.

8. A data verification device, characterized in that, It includes: A parsing module for parsing the SQL development script to obtain the association relationship between the main table and the dimension table corresponding to the SQL development script; The association relationship includes an associated dimension and an association condition; the association condition includes an associated granularity and an associated sub-dimension; A first matrix forming module for obtaining the corresponding measurement field and the field values at each dimension and each granularity corresponding to the measurement field from the main table according to the first preset required fields, to obtain the matrix corresponding to the field values of the main table at each dimension and each granularity; A second matrix forming module for obtaining the corresponding unique key and the number of unique keys at each dimension and each sub-dimension corresponding to the unique key from the dimension table according to the second preset required fields, to obtain the matrix corresponding to the number of unique keys of the dimension table at each dimension and each sub-dimension; A processing module for processing the matrix corresponding to the field values of the main table at each dimension and each granularity and the matrix corresponding to the number of unique keys of the dimension table at each dimension and each sub-dimension based on the association relationship between the main table and the dimension table, to obtain the field values at each dimension and each granularity under the association relationship; A comparison module for comparing the field values at each dimension and each granularity under the association relationship with the field values obtained by running the SQL development script. If they are consistent, it indicates that the data in the main table matches the data in the downstream table obtained by running the main table through the SQL development script.

9. An electronic device, characterized in that, It includes: A memory and a processor; The memory is used to store program instructions; The processor is used to call and execute the program instructions in the memory to execute the method according to any one of claims 1-7.

10. A computer-readable storage medium, characterized in that, A computer program is stored in the computer-readable storage medium; when the computer program is executed by a processor, the method according to any one of claims 1-7 is implemented.

Images