Data warehouse testing method, device, terminal equipment and storage medium

Abstract

The present application is applicable to the field of big data technology, and provides a data warehouse testing method, apparatus, terminal equipment and storage medium, wherein a data warehouse testing method includes: querying a target table in a data warehouse to be tested and a source table in an associated database according to a table mapping document set by a user; performing data screening on the target table and the source table according to the table screening conditions in the table mapping document to obtain a target screening table and a source screening table; performing an associated query on the target screening table and the source screening table according to the table association relationship in the table mapping document to obtain a target association table and a source association table; performing data comparison on the target association table and the source association table, and outputting the data comparison result as a test result. In this embodiment, the user does not need to manually query the target table and the source table, screen the data of the target table and the source table, perform an associated query on the target screening table and the source screening table, and perform data comparison on the target association table and the source association table, which facilitates the user's operation.

Description

Technical Field

[0001] The present application relates to the field of big data technology, and in particular to a data warehouse testing method, apparatus, terminal equipment and storage medium. Background Art

[0002] A data warehouse (DWH) is a collection of data generated during the production or R&D process of an enterprise. A data warehouse is usually used to record information data of an enterprise from a certain point in the past to the current stage. Through this data, managers can make quantitative analysis and predictions on the development history and future trends of the enterprise.

[0003] During the use of the data warehouse, it is necessary to conduct data testing on it regularly to determine whether the data stored in the data warehouse and the data in the associated database are accurate. However, in the existing data warehouse testing process, it takes a lot of time to check the correctness of the table structure and data processing results. In addition, during the data warehouse testing process, users are required to manually query the target table and source table, filter data, and perform associated queries, thereby reducing the efficiency of data warehouse testing. Summary of the invention

[0004] In view of this, the embodiments of the present application provide a data warehouse testing method, apparatus, terminal device and storage medium to solve the problem of inconvenient user operation during data warehouse testing in the prior art.

[0005] A first aspect of an embodiment of the present application provides a data warehouse testing method, including:

[0006] Obtain a table mapping document set by a user, and query a target table in the data warehouse to be tested and a source table in the associated database according to the table mapping document, wherein the table mapping document stores table identifiers of the target table and the source table and an associated library address of the associated database;

[0007] According to the table screening conditions in the table mapping document, data of the target table and the source table are screened respectively to obtain a target screening table and a source screening table;

[0008] According to the table association relationship in the table mapping document, an association query is performed on the target screening table and the source screening table respectively to obtain a target association table and a source association table;

[0009] The target association table and the source association table are compared for data, and the data comparison result is output as a test result.

[0010] Further, querying the target table in the data warehouse to be tested and the source table in the associated database according to the table mapping document includes:

[0011] Establishing data connection with the data warehouse to be tested, and performing table query and data warehouse script query on the data warehouse to be tested respectively according to the first table identifier stored in the table mapping document;

[0012] If both the table query and the data warehouse script query are successful, the queried table is set as the target table;

[0013] If both the table query and the data warehouse script query fail, a target table query error prompt will be issued;

[0014] If the table query is successful and the data warehouse script query fails, the queried table is set as the target table and a script query failure prompt is issued;

[0015] If the table query fails and the data warehouse script query is successful, the queried data warehouse script is run, and after the data warehouse script is run, the table query is re-performed on the data warehouse to be tested according to the first table identifier. If the table query is successful, the queried table is set as the target table. If the table query fails, a target table query error prompt is issued.

[0016] Furthermore, the querying of the target table in the data warehouse to be tested and the source table in the associated database according to the table mapping document further includes:

[0017] Performing data connection with the associated database according to the associated library address, and performing table query on the associated database according to the second table identifier stored in the table mapping document;

[0018] If the table query is successful, the queried table is set as the source table;

[0019] If the table query fails, a source table query error prompt is issued.

[0020] Further, the data of the target table and the source table are respectively screened according to the table screening condition in the table mapping document to obtain a target screening table and a source screening table, including:

[0021] Obtaining the target field in the table screening condition, and performing a list data query on the target table according to the target field to obtain the target screening table;

[0022] The source field in the table screening condition is obtained, and a list data query is performed on the source table according to the source field to obtain the source screening table.

[0023] Further, the performing association query on the target screening table and the source screening table respectively according to the table association relationship in the table mapping document to obtain the target association table and the source association table includes:

[0024] Acquire the target association relationship in the table association relationship, and perform table data query on the target screening table according to the header information stored in the target association relationship to obtain a target list;

[0025] Sort the target list corresponding to the header information according to the header sequence stored in the target association relationship to obtain the target association table;

[0026] Acquire a source association relationship in the table association relationship, and determine a source main table in the source screening table according to a main table identifier stored in the source association relationship;

[0027] According to the source main table, table association is performed on other source association tables to obtain the source association table.

[0028] Further, the comparing the data of the target association table with the source association table and outputting the data comparison result as the test result includes:

[0029] Comparing the data volume of the target association table and the source association table;

[0030] If the target association table and the source association table have the same data volume, it is determined that the data volume test of the data warehouse to be tested is qualified;

[0031] If the data volumes of the target association table and the source association table are different, comparing the data volume of the source main table with the data volume of the source association table after each table association;

[0032] If the data volume of the source main table is different from the data volume of the source association table, sending an association error prompt for the source association table;

[0033] If the data volume of the source main table is the same as the data volume of the source associated table, it is determined that the data volume test of the data warehouse to be tested fails, and a data volume storage error prompt is sent for the target table.

[0034] Further, the comparing the data of the target association table with the source association table and outputting the data comparison result as the test result includes:

[0035] Comparing the target association table with the source association table in terms of data details;

[0036] If the data details of the target association table and the source association table are the same, it is determined that the data details test of the data warehouse to be tested is qualified;

[0037] If the data details of the target association table and the source association table are different, the data fields of the source main table are compared with the data fields of the source association table after each table association;

[0038] If the data field of the source main table is different from the data field of the source association table, sending an association error prompt for the source association table;

[0039] If the data field of the source main table is the same as the data field of the source association table, it is determined that the data detail test of the data warehouse to be tested is unqualified, and a data detail storage error prompt is sent for the target table;

[0040] The data comparison results are uploaded to the blockchain.

[0041] Furthermore, the method further comprises:

[0042] The data comparison results are uploaded to the blockchain.

[0043] A second aspect of an embodiment of the present application provides a data warehouse testing device, including:

[0044] A table query unit, used to obtain a table mapping document set by a user, and query a target table in the data warehouse to be tested and a source table in the associated database according to the table mapping document, wherein the table mapping document stores table identifiers of the target table and the source table and an associated library address of the associated database;

[0045] A data screening unit, used to screen the target table and the source table according to the table screening conditions in the table mapping document, to obtain a target screening table and a source screening table;

[0046] An association query unit, used to perform association query on the target screening table and the source screening table respectively according to the table association relationship in the table mapping document to obtain a target association table and a source association table;

[0047] The data comparison unit is used to compare the target association table with the source association table, and output the data comparison result as a test result.

[0048] The fourth aspect of an embodiment of the present application provides a terminal device, including a memory, a processor, and a computer program stored in the memory and executable on the terminal device, wherein the processor implements the steps of the data warehouse testing method provided by the first solution when executing the computer program.

[0049] A fifth aspect of an embodiment of the present application provides a storage medium, which stores a computer program. When the computer program is executed by a processor, each step of the data warehouse testing method provided by the first solution is implemented.

[0050] The data warehouse testing method, device, terminal device and storage medium provided by the embodiments of the present application have the following beneficial effects:

[0051] A data warehouse testing method provided in an embodiment of the present application can automatically query the target table and the source table according to a table mapping document set by the user, automatically filter the data of the target table and the source table according to the table filtering conditions set by the user in the table mapping document to obtain the target filtering table and the source filtering table, automatically perform an associated query on the target filtering table and the source filtering table according to the table association relationship set by the user in the table mapping document to obtain the target association table and the source association table, and obtain the test result of the data warehouse to be tested based on the data comparison result of the target association table and the source association table. The data warehouse testing method provided in an embodiment of the present application does not require the user to manually query the target table and the source table, filter the data of the target table and the source table, perform an associated query on the target filtering table and the source filtering table, and perform data comparison operations on the target association table and the source association table, thereby facilitating the user's operation and improving the data warehouse testing efficiency. BRIEF DESCRIPTION OF THE DRAWINGS

[0052] In order to more clearly illustrate the technical solutions in the embodiments of the present application, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative labor.

[0053] Figure 1 It is a flow chart of an implementation of a data warehouse testing method provided in an embodiment of the present application;

[0054] Figure 2 is a flow chart of an implementation of a data warehouse testing method provided by another embodiment of the present application;

[0055] Figure 3 is a flow chart of an implementation of a data warehouse testing method provided in yet another embodiment of the present application;

[0056] Figure 4 is a flow chart of an implementation of a data warehouse testing method provided by another embodiment of the present application;

[0057] Figure 5 is a flow chart of an implementation of a data warehouse testing method provided in yet another embodiment of the present application;

[0058] Figure 6It is a structural block diagram of a data warehouse testing device provided in an embodiment of the present application;

[0059] Figure 7 It is a structural block diagram of a terminal device provided in an embodiment of the present application. DETAILED DESCRIPTION

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

[0061] The data warehouse testing method involved in the embodiment of the present application can be executed by a control device or terminal (hereinafter referred to as a "mobile terminal").

[0062] See also Figure 1 , Figure 1 A flowchart of a data warehouse testing method provided by an embodiment of the present application is shown, including:

[0063] Step S10, obtaining a table mapping document set by a user, and querying a target table in the data warehouse to be tested and a source table in the associated database according to the table mapping document;

[0064] Among them, the table mapping document stores the target table, the table identifier of the source table and the associated library address of the associated database. The table identifier of the target table includes: the environment where the target table is located, the target table name, the target table field name, and the target table field data type / length. The table identifier of the source table includes: the environment / database / database configuration where the source table is located, the source table name, the source table field name, and the source table field data type / length.

[0065] Specifically, in this step, when the target table is queried in the data warehouse to be tested according to the target table name and the source table is queried in the associated database according to the source table name, the table field information of the target table and the field information of the source table are obtained, the field information includes the table field name and the table field data type / length, and it is determined whether the table field information of the target table and the field information of the source table are the same as the table field information in the corresponding table identifier in the table mapping document;

[0066] If it is determined that the table field information of the target table and / or the source table is different from the table field information in the corresponding table identifier in the table mapping document, a query error prompt is sent for the target table and / or the source table.

[0067] Step S20, filtering data on the target table and the source table respectively according to the table filtering conditions in the table mapping document to obtain a target filtering table and a source filtering table;

[0068] Among them, the table filtering condition is used to filter the data of the target table and the source table respectively according to the fields specified by the user to obtain the target filtering table and the source filtering table. The fields set for the target table and the source table by the table filtering condition can be the same or different, and the field can be any list text or table header text.

[0069] Optionally, the field can be user name, user age, user test score, city name, city address or any number, etc. For example, when the fields of the target table and the source table are both the user name "Zhang San", the target table and the source table are filtered according to "Zhang San", and the list information carrying "Zhang San" in the target table and the source table is extracted to obtain the corresponding target filtering table and source filtering table.

[0070] In addition, in this step, the table screening condition may store a plurality of different fields, and based on the fields, the target table and the source table are respectively screened in a multi-dimensional manner to obtain corresponding target screening tables and source screening tables.

[0071] Optionally, in this step, the filtered target filter table and the source filter table are stored in a temporary file, thereby effectively facilitating subsequent associated queries on the target filter table and the source filter table.

[0072] Step S30, performing association query on the target screening table and the source screening table respectively according to the table association relationship in the table mapping document to obtain a target association table and a source association table;

[0073] Among them, the table association relationship includes a target association relationship and a source association relationship. The target association relationship is used to perform a self-association query on the list data in the target filtering table to generate the target association table. At least two header information are set in the target association relationship. This step associates the corresponding list data according to the header information to obtain the target association table. For example, if the header information is "name information" and "score information", the "name information" and "score information" in the target filtering table are associated to generate the target association table.

[0074] In this step, the source association relationship is used to perform an association query between different source filtering tables. The source association relationship can be a two-table association, a three-table association, a four-table association, etc., that is, an association is performed between two different source filtering tables, between three different source filtering tables, or between four different source filtering tables, etc., to generate a corresponding source association table.

[0075] Step S40, performing data comparison between the target association table and the source association table, and outputting the data comparison result as a test result;

[0076] Among them, the data comparison between the target association table and the source association table includes data quantity comparison and data detail comparison, that is, the target association table and the source association table are compared based on the data quantity and data details, and the data quantity comparison results and data detail comparison results of the target association table and the source association table are output as the test results of the data warehouse to be tested.

[0077] From the above, it can be seen that the target table and the source table can be automatically queried according to the table mapping document set by the user, and the target table and the source table can be automatically filtered according to the table filtering conditions set by the user in the table mapping document to obtain the target filtering table and the source filtering table. According to the table association relationship set by the user in the table mapping document, the target filtering table and the source filtering table can be automatically associated with each other to obtain the target association table and the source association table. The test result of the data warehouse to be tested is obtained based on the data comparison result of the target association table and the source association table. The data warehouse testing method provided by the embodiment of the present application does not require the user to manually query the target table and the source table, filter the data of the target table and the source table, query the association of the target filtering table and the source filtering table, and compare the data of the target association table and the source association table, thereby facilitating the user's operation and improving the data warehouse testing efficiency.

[0078] See also Figure 2 , Figure 2 is a flowchart of a data warehouse testing method provided by another embodiment of the present application. Figure 1 Corresponding embodiment, the data warehouse testing method provided in this embodiment is Figure 1 In the corresponding embodiment, step S10 is further refined, and step S10 includes:

[0079] S11, establishing data connection with the data warehouse to be tested, and performing table query and data warehouse script query on the data warehouse to be tested respectively according to the first table identifier stored in the table mapping document;

[0080] The first table identifier may be stored in the form of a table name or a table number, and all data warehouse scripts in the data warehouse to be tested carry the table identifier of the corresponding table.

[0081] Optionally, in this step, if the data connection with the data warehouse to be tested fails, a connection error prompt is sent to the data warehouse to be tested.

[0082] S12, if both the table query and the data warehouse script query are successful, setting the queried table as the target table;

[0083] S13: If both the table query and the data warehouse script query fail, a target table query error prompt is issued;

[0084] S14, if the table query is successful and the data warehouse script query fails, the queried table is set as the target table and a script query failure prompt is issued;

[0085] The script query failure prompt can be used to prompt the user in the form of text, voice or image. The prompt information stored in the script query failure prompt can be set according to the user's needs. For example, the prompt information can be the text "The target table exists, but the data warehouse script does not exist. It can only be tested with existing data in the target table".

[0086] S15, if the table query fails and the data warehouse script query succeeds, the queried data warehouse script is run, and after the data warehouse script is run, the table query is re-performed on the data warehouse to be tested according to the first table identifier. If the table query succeeds, the queried table is set as the target table. If the table query fails, a target table query error prompt is issued;

[0087] The target table is restored and generated by running the queried data warehouse script.

[0088] Optionally, after the data warehouse script is run, the running log and execution results are retained (if the running fails, there will be a text message or email reminder), and a table query is performed again on the data warehouse to be tested based on the first table identifier to determine whether the target table exists. If the target table exists, the test continues; if the target table does not exist, the test fails and a target table query error prompt is issued.

[0089] S16, performing data connection with the associated database according to the associated library address, and performing table query on the associated database according to the second table identifier stored in the table mapping document;

[0090] S17, if the table query is successful, setting the queried table as the source table;

[0091] S18, if the table query fails, a source table query error prompt is issued;

[0092] In this embodiment, by designing table queries for the data warehouse to be tested and the associated data warehouse respectively according to the first table identifier and the second table identifier, it is determined whether the target table and the source table are stored in the data warehouse to be tested and the associated data warehouse respectively, so that there is no need for users to manually query the target table and the source table, which facilitates user operations. In addition, by designing a data warehouse script query for the data warehouse to be tested according to the first table identifier, the target table can be regenerated if the target table is not found, thereby ensuring the query efficiency of the target table.

[0093] See also Figure 3 , Figure 3 is a flowchart of a data warehouse testing method provided by another embodiment of the present application. Figure 1 Corresponding embodiment, the data warehouse testing method provided in this embodiment is Figure 1 In the corresponding embodiment, step S20 is further refined, and step S20 includes:

[0094] S21, obtaining a target field in the table screening condition, and performing a list data query on the target table according to the target field to obtain the target screening table;

[0095] The target field can be any list text or table header text. When the target field is a list text, the list data in the target table are matched according to the target field, and the list data carrying the list text is extracted to obtain the target screening table.

[0096] When the target field is a header text, the header in the target table is matched according to the target field, and the list data under the matched header is extracted to obtain the target screening table.

[0097] Optionally, in this step, a target table query SQL is generated according to the target field and a preset query SQL, and the target table query SQL is executed on the target table to obtain the target screening table.

[0098] In this step, if no corresponding data is found in the target table according to the target field, a target table screening error prompt is issued.

[0099] S22, obtaining a source field in the table screening condition, and performing a list data query on the source table according to the source field to obtain the source screening table;

[0100] It can be understood that the way the source field queries the list data of the source table is the same as the way the target field queries the list data of the target table, which will not be described in detail here.

[0101] In this embodiment, a list data query is designed for the target table and the source table according to the target field and the source field respectively, so as to achieve the effect of data filtering of the target table and the source table, so that the user does not need to manually filter the data of the target table and the source table, which facilitates the user's operation.

[0102] See also Figure 4 , Figure 4 is a flowchart of a data warehouse testing method provided by another embodiment of the present application. Figure 1 Corresponding embodiment, the data warehouse testing method provided in this embodiment is Figure 1 In the corresponding embodiment, step S30 is further refined, and step S30 includes:

[0103] S31, obtaining a target association relationship in the table association relationship, and performing a table data query on the target screening table according to the header information stored in the target association relationship to obtain a target list;

[0104] The target association relationship is used to query the table data corresponding to the header information preset by the user in the target screening table, and merge all the queried table data to obtain the target list.

[0105] Specifically, multiple different header information can be stored in the target association relationship, and the header information can be set according to user needs. For example, the header information can be "name information" and "score information". The header in the target filtering table is queried based on the "name information" and "score information", and the table data under the queried header is merged to obtain the target list.

[0106] Optionally, in this step, a target table association SQL is generated according to the target association relationship and the first preset association SQL, and the target table association SQL is executed on the target screening table to obtain the target list.

[0107] S32, sorting the target list corresponding to the header information according to the header sequence stored in the target association relationship to obtain the target association table;

[0108] The header sequence is used to sort the target list corresponding to the queried header information, and the sorted list is set as the target association table.

[0109] S33, obtaining a source association relationship in the table association relationship, and determining a source main table in the source screening table according to a main table identifier stored in the source association relationship;

[0110] Among them, the number of source tables is at least two, and correspondingly, the number of source filter tables is at least two. Therefore, in this step, the main table identifier stored in the source association relationship is matched with the table identifier of the source table respectively, and the source filter table corresponding to the source table corresponding to the matched table identifier is set as the source main table.

[0111] S34, performing table association on other source association tables according to the source main table to obtain the source association table;

[0112] The storage includes all the table data in the source main table and the table data in other source association tables that are equal to the connection fields in the source main table, so as to obtain the source association table;

[0113] For example, the source main table is:

[0114] aID aNum 1 a20050111 2 a20050112 3 a20050113 4 a20050114 5 a20050115

[0115] The source association table is:

[0116] bID bNum 1 2006032401 2 2006032402 3 2006032403 4 2006032404 8 2006032408

[0117] Then, the source associated table obtained after the source main table is associated with the source associated table is:

[0118] aID aNum bID bNum 1 a20050111 1 2006032401 2 a20050112 2 2006032402 3 a20050113 3 2006032403 4 a20050114 4 2006032404 5 a20050115 NULL NULL

[0119] Optionally, in this step, if the number of the source screening tables is greater than two, and the source association relationships are two-to-two associations, the source main table is respectively associated with different source association tables, and the source association tables obtained after each table association are stored separately.

[0120] In addition, in this step, the source association table is sorted according to the table header sequence stored in the table association relationship, thereby improving the accuracy of data comparison between the subsequent target association table and the source association table, thereby improving the accuracy of data testing of the data warehouse to be tested.

[0121] In the present application, a target association table is automatically obtained by performing a table data query on a target filtering table according to the table header information stored in the target association relationship, and a target list corresponding to the table header information is sorted according to the table header order stored in the target association relationship. A source main table in a source filtering table is determined according to a main table identifier stored in a source association relationship, and a table association is performed on other source association tables according to the source main table, so that a source association table is automatically associated to obtain a source association table, thereby eliminating the need for users to manually query the target association table and the source association table, thereby facilitating user testing operations.

[0122] See also Figure 5 , Figure 5 is a flowchart of a data warehouse testing method provided by another embodiment of the present application. Figure 1 Corresponding embodiment, the data warehouse testing method provided in this embodiment is Figure 1 In the corresponding embodiment, step S40 is further refined, and step S40 includes:

[0123] S41, respectively comparing the data volume and data details of the target association table and the source association table;

[0124] The data volume and data details of the target association table and each source association table are obtained respectively, and the data volume and data details of the target association table are compared with the data volume and data details of each source association table respectively.

[0125] S42, if the data volumes of the target association table and the source association table are the same, then determining that the data volume test of the data warehouse to be tested is qualified;

[0126] If the data volumes of the target association table and all source association tables are the same, it is determined that the data volume of the target table in the data warehouse to be tested is correctly stored, and the data volume test of the data warehouse to be tested passes.

[0127] S43, if the data amounts of the target association table and the source association table are different, comparing the data amount of the source main table with the data amount of the source association table after each table association;

[0128] If the target association table and any source association table have different data volumes, the data volume of the source main table is compared with the data volume of the source association table after each table association to determine whether the data volume of the source main table has changed after the table association.

[0129] S44, if the data volume of the source main table is different from the data volume of the source association table, sending an association error prompt for the source association table;

[0130] Among them, if the data volume of the source main table is different from the data volume of any source associated table, it is determined that there is an association error in the source associated table with different data volume from the source main table. By sending an association error prompt for the source associated table with the association error, the user is prompted to manually check the source associated table.

[0131] S45, if the data volume of the source main table is the same as the data volume of the source associated table, it is determined that the data volume test of the data warehouse to be tested fails, and a data volume storage error prompt is sent for the target table;

[0132] Among them, if the data volume of the source main table is the same as the data volume of all source associated tables, it is determined that the data volume stored in the target table in the data warehouse to be tested is wrong. Therefore, the data volume test of the data warehouse to be tested fails, and a data volume storage error prompt is sent to the target table to prompt the user to re-store the data volume for the target table in the data warehouse to be tested.

[0133] S46, if the data details of the target association table and the source association table are the same, then it is determined that the data details test of the data warehouse to be tested is qualified;

[0134] If the data details of the target association table and all source association tables are the same, it is determined that the data details of the target table in the data warehouse to be tested are correctly stored, and the data details test of the data warehouse to be tested passes.

[0135] S47, if the data details of the target association table and the source association table are different, then compare the data fields of the source main table with the data fields of the source association table after each table association;

[0136] If the data details of the target association table and any source association table are different, the data fields of the source main table are compared with the data fields of the source association table after each table association to determine whether the data fields of the source main table have changed after the table association.

[0137] S48, if the data field of the source main table is different from the data field of the source association table, sending an association error prompt for the source association table;

[0138] Among them, if the data volume of the source main table is different from the data field of any source associated table, it is determined that there is an association error in the source associated table with the different data field from the source main table. By sending an association error prompt for the source associated table with the association error, the user is prompted to manually check the source associated table.

[0139] S49, if the data field of the source main table is the same as the data field of the source association table, it is determined that the data detail test of the data warehouse to be tested is unqualified, and a data detail storage error prompt is sent for the target table;

[0140] Among them, if the data fields of the source main table are the same as the data fields of all source related tables, it is determined that the data fields stored in the target table in the data warehouse to be tested are wrong. Therefore, the data detail test of the data warehouse to be tested fails, and a data detail storage error prompt is sent to the target table to prompt the user to re-store the data details for the target table in the data warehouse to be tested.

[0141] Optionally, in this step, the data volume comparison and data detail comparison between the target association table and the source association table can be performed by using the test selection time method, that is, according to the test selection time set by the user, the data volume and data details of the target association table and the source association table within the time period corresponding to the test selection time are obtained, and data comparison is performed based on the obtained data volume and data details to obtain data volume comparison results and data detail comparison results corresponding to multiple test selection times, and the data volume comparison results and data detail comparison results are output as test results corresponding to the test selection time.

[0142] In this embodiment, by designing a data volume comparison and a data detail comparison for the target association table and the source association table respectively, the data comparison efficiency between the target table and the source table is effectively improved. By designing a comparison between the data volume of the source master table and the data volume of the source association table after each table association, it is determined whether the data volume of the source master table has changed after the table association. If the data volume of the source master table has not changed after the table association, it is determined that the data volume stored in the target table in the data warehouse to be tested is incorrect, and the data volume test of the data warehouse to be tested fails. By designing a comparison between the data fields of the source master table and the data fields of the source association table after each table association, it is determined whether the data fields of the source master table have changed after the table association. If the data fields of the source master table have not changed after the table association, it is determined that the data fields stored in the target table in the data warehouse to be tested are incorrect, and the data detail test of the data warehouse to be tested fails.

[0143] In all embodiments of the present application, the corresponding data warehouse test results are generated based on the data comparison results of the target association table and the source association table. Specifically, the data comparison results of the target association table and the source association table are output as data warehouse test results. Uploading the data comparison results to the blockchain can ensure its security and fairness and transparency to users. The user device can download the data comparison results from the blockchain to verify whether the data comparison results have been tampered with. The blockchain referred to in this example is a new application mode of computer technologies such as distributed data storage, point-to-point transmission, consensus mechanism, encryption algorithm, etc. Blockchain is essentially a decentralized database, a string of data blocks generated by cryptographic methods, each of which contains a batch of network transaction information for verifying the validity of its information (anti-counterfeiting) and generating the next block. The blockchain may include a blockchain underlying platform, a platform product service layer, and an application service layer.

[0144] See also Figure 6 , Figure 6 1 is a structural block diagram of a data warehouse testing device 100 provided in an embodiment of the present application. In this embodiment, the data warehouse testing device 100 includes various units for executing Figures 1 to 5 For details, please refer to the steps in the corresponding embodiment. Figures 1 to 5 as well as Figures 1 to 5 For the convenience of explanation, only the parts related to this embodiment are shown. Figure 6 The data warehouse testing device 100 includes: a table query unit 10, a data screening unit 11, an association query unit 12 and a data comparison unit 13, wherein:

[0145] The table query unit 10 is used to obtain a table mapping document set by a user, and query the target table in the data warehouse to be tested and the source table in the associated database according to the table mapping document. The table mapping document stores the table identifiers of the target table and the source table and the associated library address of the associated database.

[0146] The data screening unit 11 is used to screen the target table and the source table according to the table screening conditions in the table mapping document to obtain a target screening table and a source screening table.

[0147] The association query unit 12 is used to perform association query on the target screening table and the source screening table respectively according to the table association relationship in the table mapping document to obtain a target association table and a source association table.

[0148] The data comparison unit 13 is used to compare the target association table with the source association table, and output the data comparison result as a test result.

[0149] As an embodiment of the present application, the table query unit 10 is further used to: establish a data connection with the data warehouse to be tested, and perform a table query and a data warehouse script query on the data warehouse to be tested according to the first table identifier stored in the table mapping document;

[0150] If both the table query and the data warehouse script query are successful, the queried table is set as the target table;

[0151] If both the table query and the data warehouse script query fail, a target table query error prompt will be issued;

[0152] If the table query is successful and the data warehouse script query fails, the queried table is set as the target table and a script query failure prompt is issued;

[0153] If the table query fails and the data warehouse script query is successful, the queried data warehouse script is run, and after the data warehouse script is run, the table query is re-performed on the data warehouse to be tested according to the first table identifier. If the table query is successful, the queried table is set as the target table. If the table query fails, a target table query error prompt is issued.

[0154] As an embodiment of the present application, the table query unit 10 is further used to: perform data connection with the associated database according to the associated library address, and perform table query on the associated database according to the second table identifier stored in the table mapping document;

[0155] If the table query is successful, the queried table is set as the source table;

[0156] If the table query fails, a source table query error prompt is issued.

[0157] As an embodiment of the present application, the data screening unit 11 is further used to: obtain a target field in the table screening condition, and perform a list data query on the target table according to the target field to obtain the target screening table;

[0158] The source field in the table screening condition is obtained, and a list data query is performed on the source table according to the source field to obtain the source screening table.

[0159] As an embodiment of the present application, the association query unit 12 is further used to: obtain the target association relationship in the table association relationship, and perform table data query on the target screening table according to the header information stored in the target association relationship to obtain a target list;

[0160] Sort the target list corresponding to the header information according to the header sequence stored in the target association relationship to obtain the target association table;

[0161] Acquire a source association relationship in the table association relationship, and determine a source main table in the source screening table according to a main table identifier stored in the source association relationship;

[0162] According to the source main table, table association is performed on other source association tables to obtain the source association table.

[0163] As an embodiment of the present application, the data comparison unit 13 is further used to: compare the data volume of the target association table and the source association table;

[0164] If the target association table and the source association table have the same data volume, it is determined that the data volume test of the data warehouse to be tested is qualified;

[0165] If the data volumes of the target association table and the source association table are different, comparing the data volume of the source main table with the data volume of the source association table after each table association;

[0166] If the data volume of the source main table is different from the data volume of the source association table, sending an association error prompt for the source association table;

[0167] If the data volume of the source main table is the same as the data volume of the source associated table, it is determined that the data volume test of the data warehouse to be tested fails, and a data volume storage error prompt is sent for the target table.

[0168] As an embodiment of the present application, the data comparison unit 13 is further used to: compare the target association table with the source association table in detail;

[0169] If the data details of the target association table and the source association table are the same, it is determined that the data details test of the data warehouse to be tested is qualified;

[0170] If the data details of the target association table and the source association table are different, the data fields of the source main table are compared with the data fields of the source association table after each table association;

[0171] If the data field of the source main table is different from the data field of the source association table, sending an association error prompt for the source association table;

[0172] If the data field of the source main table is the same as the data field of the source association table, it is determined that the data detail test of the data warehouse to be tested is unqualified, and a data detail storage error prompt is sent for the target table.

[0173] From the above, it can be seen that a data warehouse testing device 100 provided in this embodiment can automatically query the target table and the source table according to the table mapping document set by the user, and can automatically perform data screening on the target table and the source table according to the table screening conditions set by the user in the table mapping document to obtain the target screening table and the source screening table, and can automatically perform association query on the target screening table and the source screening table according to the table association relationship set by the user in the table mapping document to obtain the target association table and the source association table, and obtain the test result of the data warehouse to be tested based on the data comparison result of the target association table and the source association table. A data warehouse testing method provided in an embodiment of the present application does not require the user to manually query the target table and the source table, screen the data of the target table and the source table, perform association query on the target screening table and the source screening table, and perform data comparison operations on the target association table and the source association table, thereby facilitating the user's operation and improving the data warehouse testing efficiency.

[0174] Figure 7 2 is a block diagram of a terminal device 2 provided in another embodiment of the present application. Figure 7 As shown, the terminal device 2 of this embodiment includes: a processor 20, a memory 21, and a computer program 22 stored in the memory 21 and executable on the processor 20, such as a program of the data warehouse testing method. When the processor 20 executes the computer program 22, the steps in each embodiment of the above-mentioned data warehouse testing method are implemented, such as Figure 1 S10 to S40 as shown, or Figure 2 S11 to S18 as shown, or Figure 3 S21 to S22 as shown, or Figure 4 S31 to S34 and Figure 5 Alternatively, the processor 20 implements the above when executing the computer program 22 Figure 6The functions of each unit in the corresponding embodiment are, for example, Figure 6 For details on the functions of units 10 to 13, please refer to Figure 7 The relevant descriptions in the corresponding embodiments are not repeated here.

[0175] Exemplarily, the computer program 22 may be divided into one or more units, which are stored in the memory 21 and executed by the processor 20 to complete the present application. The one or more units may be a series of computer program instruction segments capable of completing specific functions, which are used to describe the execution process of the computer program 22 in the terminal device 2. For example, the computer program 22 may be divided into a table query unit 10, a data screening unit 11, an association query unit 12, and a data comparison unit 13, and the specific functions of each unit are as described above.

[0176] The terminal device may include, but is not limited to, a processor 20 and a memory 21. Those skilled in the art will appreciate that Figure 7 It is only an example of the terminal device 2 and does not constitute a limitation of the terminal device 2. It may include more or fewer components than shown in the figure, or a combination of certain components, or different components. For example, the terminal device may also include input and output devices, network access devices, buses, etc.

[0177] The processor 20 may be a central processing unit (CPU), or other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), field-programmable gate arrays (FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. A general-purpose processor may be a microprocessor or any conventional processor, etc.

[0178] The memory 21 may be an internal storage unit of the terminal device 2, such as a hard disk or memory of the terminal device 2. The memory 21 may also be an external storage device of the terminal device 2, such as a plug-in hard disk, a smart media card (SMC), a secure digital (SD) card, a flash card, etc. equipped on the terminal device 2. Further, the memory 21 may also include both an internal storage unit and an external storage device of the terminal device 2. The memory 21 is used to store the computer program and other programs and data required by the terminal device. The memory 21 may also be used to temporarily store data that has been output or is to be output.

[0179] The embodiments described above are only used to illustrate the technical solutions of the present application, rather than to limit them. Although the present application has been described in detail with reference to the aforementioned embodiments, a person skilled in the art should understand that the technical solutions described in the aforementioned embodiments may still be modified, or some of the technical features may be replaced by equivalents. Such modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present application, and should all be included in the protection scope of the present application.

Claims

1. A data warehouse testing method, characterized in that: include: Obtain a table mapping document set by the user, and query the target table in the data warehouse to be tested and the source table in the associated database according to the table mapping document, wherein the table mapping document stores the table identifiers of the target table and the source table and the associated library address of the associated database, including: establishing a data connection with the data warehouse to be tested, and performing a table query and a data warehouse script query on the data warehouse to be tested respectively according to the first table identifier stored in the table mapping document; if both the table query and the data warehouse script query are successful, the queried table is set as the target table; if both the table query and the data warehouse script query fail, an error prompt for the target table query is issued; if the table query is successful and the data warehouse script query fails, the queried table is The table is set as the target table, and a script query failure prompt is issued; if the table query fails and the data warehouse script query is successful, the queried data warehouse script is run, and after the data warehouse script is run, a table query is re-performed on the data warehouse to be tested according to the first table identifier. If the table query is successful, the queried table is set as the target table. If the table query fails, a target table query error prompt is issued; data connection is performed with the associated database according to the associated library address, and a table query is performed on the associated database according to the second table identifier stored in the table mapping document; if the table query is successful, the queried table is set as the source table; if the table query fails, a source table query error prompt is issued; According to the table screening conditions in the table mapping document, data of the target table and the source table are screened respectively to obtain a target screening table and a source screening table; According to the table association relationship in the table mapping document, the target screening table and the source screening table are respectively associated with each other to obtain a target association table and a source association table, including: obtaining the target association relationship in the table association relationship, and performing table data query on the target screening table according to the header information stored in the target association relationship to obtain a target list; sorting the target list corresponding to the header information according to the header sequence stored in the target association relationship to obtain the target association table; obtaining the source association relationship in the table association relationship, and determining the source master table in the source screening table according to the master table identifier stored in the source association relationship; performing table association on other source association tables according to the source master table to obtain the source association table; The target association table and the source association table are compared for data, and the data comparison result is output as a test result.

2. The data warehouse testing method according to claim 1, characterized in that: The step of respectively filtering the target table and the source table according to the table filtering condition in the table mapping document to obtain a target filtering table and a source filtering table includes: Obtaining the target field in the table screening condition, and performing a list data query on the target table according to the target field to obtain the target screening table; The source field in the table screening condition is obtained, and a list data query is performed on the source table according to the source field to obtain the source screening table.

3. The data warehouse testing method according to claim 1, characterized in that: The step of comparing data between the target association table and the source association table and outputting the data comparison result as a test result includes: Comparing the data volume of the target association table and the source association table; If the target association table and the source association table have the same data volume, it is determined that the data volume test of the data warehouse to be tested is qualified; If the data volumes of the target association table and the source association table are different, comparing the data volume of the source main table with the data volume of the source association table after each table association; If the data volume of the source main table is different from the data volume of the source association table, sending an association error prompt for the source association table; If the data volume of the source main table is the same as the data volume of the source associated table, it is determined that the data volume test of the data warehouse to be tested fails, and a data volume storage error prompt is sent for the target table.

4. The data warehouse testing method according to claim 1, characterized in that: The step of comparing data between the target association table and the source association table and outputting the data comparison result as a test result includes: Comparing the target association table with the source association table in terms of data details; If the data details of the target association table and the source association table are the same, it is determined that the data details test of the data warehouse to be tested is qualified; If the data details of the target association table and the source association table are different, the data fields of the source main table are compared with the data fields of the source association table after each table association; If the data field of the source main table is different from the data field of the source association table, sending an association error prompt for the source association table; If the data field of the source main table is the same as the data field of the source association table, it is determined that the data detail test of the data warehouse to be tested is unqualified, and a data detail storage error prompt is sent for the target table; The data comparison results are uploaded to the blockchain.

5. A data warehouse testing device, characterized in that: include: A table query unit is used to obtain a table mapping document set by a user, and query a target table in a data warehouse to be tested and a source table in an associated database according to the table mapping document, wherein the table mapping document stores table identifiers of the target table and the source table and an associated database address of the associated database, including: establishing a data connection with the data warehouse to be tested, and performing a table query and a data warehouse script query on the data warehouse to be tested respectively according to the first table identifier stored in the table mapping document; if both the table query and the data warehouse script query are successful, the queried table is set as the target table; if both the table query and the data warehouse script query fail, a target table query error prompt is issued; if the table query is successful and the data warehouse script query fails, The queried table is set as the target table, and a script query failure prompt is issued; if the table query fails and the data warehouse script query is successful, the queried data warehouse script is run, and after the data warehouse script is run, the table query is re-performed on the data warehouse to be tested according to the first table identifier. If the table query is successful, the queried table is set as the target table. If the table query fails, a target table query error prompt is issued; data connection is performed with the associated database according to the associated library address, and a table query is performed on the associated database according to the second table identifier stored in the table mapping document; if the table query is successful, the queried table is set as the source table; if the table query fails, a source table query error prompt is issued; A data screening unit, used to screen the target table and the source table according to the table screening conditions in the table mapping document, to obtain a target screening table and a source screening table; An association query unit is used to perform association queries on the target screening table and the source screening table respectively according to the table association relationship in the table mapping document to obtain a target association table and a source association table, including: obtaining the target association relationship in the table association relationship, and performing table data query on the target screening table according to the header information stored in the target association relationship to obtain a target list; sorting the target list corresponding to the header information according to the header sequence stored in the target association relationship to obtain the target association table; obtaining the source association relationship in the table association relationship, and determining the source master table in the source screening table according to the master table identifier stored in the source association relationship; performing table association on other source association tables according to the source master table to obtain the source association table; The data comparison unit is used to compare the target association table with the source association table, and output the data comparison result as a test result.

6. A terminal device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the computer program, the steps of the method according to any one of claims 1 to 4 are implemented.

7. A storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 4 are implemented.

Images