A data warehouse zip list data verification method, device, equipment and medium

Abstract

The application discloses a data warehouse zip list data verification method and device, equipment and a storage medium, relates to the technical field of data verification, and comprises the following steps: acquiring suspected abnormal log data satisfying a preset abnormal data determination rule; the preset abnormal data determination rule is a rule for determining that the ratio between the read record number and the write record number of a target data group in zip list batch running log is not a positive integer; generating a corresponding primary key duplication verification statement according to first zip list data corresponding to the suspected abnormal log data and preset verification template information; executing the primary key duplication verification statement through a trino engine; and if the corresponding first verification result is empty, it is determined that the corresponding zip list data verification is passed. In this way, the application can quickly verify the data corresponding to the zip list log through the preset abnormal data determination rule, and the trino engine can improve the verification speed and reduce the verification time consumption.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of data verification, in particular to a data warehouse zip list data verification method, device, equipment and storage medium. BACKGROUND

[0002] The commonly used offline data warehouse on the market mainly runs batch based on open source spark (distributed data processing engine) components. As the data volume of the entire data warehouse becomes larger and larger, when running batch in the early morning, spark shuffle (data aggregation) exceptions may occur, but the job execution does not report errors, resulting in abnormal zip list data in the history layer, so it is necessary to verify the zip list data after running batch. However, there are two problems in the current zip list data verification: first, the entire data warehouse batch zip list verification takes too long, which cannot meet the timeliness of subsequent application data; second, the verification of zip list data with hundreds of millions of data takes too long and requires too many resources, resulting in serious timeout; the corresponding common solution is: first, only verify part of the key job, and only verify part of the data in the early morning; second, only check the core application data using the zip list to verify whether the data is abnormal from the perspective of use. This will cause some problems, the first way, the data table that can be verified is limited, and there may be abnormal data in the part of the table that is not verified, resulting in abnormal application data; because only part of the data is verified, there may be problems that are not verified, resulting in subsequent data anomalies; the second way, check from the application data side, when the final result is abnormal, because there are many zip lists used, it is difficult to quickly locate the abnormal table.

[0003] Therefore, how to quickly realize the verification process of the zip list data is a problem to be solved in the field. SUMMARY

[0004] Therefore, the purpose of the present application is to provide a data warehouse zip list data verification method, device, equipment and storage medium, which can quickly verify the zip list data through the reading and writing rules of log data, and can improve the zip list data verification efficiency. The specific scheme is as follows:

[0005] In the first aspect, the present application provides a data warehouse zip list data verification method, comprising:

[0006] Obtain suspected abnormal log data satisfying a preset abnormal data determination rule; the preset abnormal data determination rule is a rule of determining that the ratio between the read record number and the write record number of a target data group in the zip list batch log is not a positive integer;

[0007] Generating a corresponding primary key duplication verification statement according to the first zip list data corresponding to the suspected abnormal log data and the preset verification template information;

[0008] The primary key duplication checking statement is executed by a trino engine, and if the corresponding first checking result is empty, it is determined that the corresponding zip list data checking is passed.

[0009] Optionally, the acquiring of the suspected abnormal log data satisfying the preset abnormal data determination rule comprises:

[0010] The zip list batch running log is listened to by a SparkListener to acquire the suspected abnormal log data satisfying the preset abnormal data determination rule.

[0011] Optionally, the generating of the corresponding primary key duplication checking statement according to the first zip list data corresponding to the suspected abnormal log data and the preset checking template information comprises:

[0012] The zip list batch running task corresponding to the suspected abnormal log data is determined;

[0013] The corresponding primary key checking template information is determined from the preset checking template information according to the zip list batch running task;

[0014] The corresponding primary key duplication checking statement is generated based on the first zip list data corresponding to the suspected abnormal log data and the primary key checking template information.

[0015] Optionally, the method further comprises:

[0016] A target historical time period is acquired through a preset human-computer interaction interface;

[0017] All zip list data in a data warehouse is initialized according to the target historical time period to obtain a first data table and a second data table; the first data table contains all zip list data in the data warehouse before a current time period, and the second data table contains the number of primary key data corresponding to the first data table;

[0018] Zip list batch running data in the current time period is counted to obtain a current data table;

[0019] Zip list data existing in the first data table is eliminated from the current data table through a trino engine to obtain a third data table representing the number of primary key data in the current data table after the elimination of data;

[0020] All zip list data in the data warehouse before a next time period is calculated through the trino engine to obtain a fourth data table representing the number of corresponding primary key data, and the number of primary key data between the fourth data table, the third data table and the second data table is checked to obtain a second checking result;

[0021] If the second check result represents that the check passes, the first data table and the second data table are respectively updated by using the third data table and the fourth data table.

[0022] Optionally, the checking of the number of primary key data among the fourth data table, the third data table and the second data table obtains a second check result, and the method comprises:

[0023] determining whether the number of primary key data in the fourth data table is equal to the sum of the number of primary key data of the third data table and the second data table, to obtain a second check result.

[0024] Optionally, the updating of the first data table and the second data table by using the third data table and the fourth data table respectively comprises:

[0025] during the idle period, the primary key data corresponding to the third data table is supplemented to the first data table to obtain a new first data table;

[0026] the fourth data table is determined as a new second data table, so that the new first data table and the new second data table are used to perform the check process of the zip table data in the next time period.

[0027] Optionally, the method further comprises:

[0028] if the first check result and / or the second check result represents that the check does not pass, an operation of terminating the current zip table data run script is triggered.

[0029] In a second aspect, the present application provides a data warehouse zip table data verification device, comprising:

[0030] a log data acquisition module configured to acquire suspected abnormal log data satisfying a preset abnormal data determination rule; the preset abnormal data determination rule is a rule of determining that the ratio between the number of read records and the number of written records of a target data group in a zip table run log is not a positive integer;

[0031] a check statement generation module configured to generate a corresponding primary key duplication check statement according to first zip table data corresponding to the suspected abnormal log data and preset check template information;

[0032] a check statement execution module configured to execute the primary key duplication check statement through a trino engine, and if a corresponding first check result is empty, it is determined that the corresponding zip table data check passes.

[0033] In a third aspect, the present application provides an electronic device, comprising:

[0034] a memory configured to save a computer program;

[0035] a processor configured to execute the computer program to implement the data warehouse zip list data verification method as described above.

[0036] In a fourth aspect, the present application provides a computer readable storage medium for storing a computer program, which, when executed by a processor, implements the data warehouse zip list data verification method as described above.

[0037] It can be seen that the present application can obtain suspected abnormal log data that meets a preset abnormal data determination rule; the preset abnormal data determination rule is a rule of determining that the ratio between the read record number and the write record number of the target data group in the zip list batch running log is not a positive integer; then a corresponding primary key duplication check statement is generated according to the first zip list data corresponding to the suspected abnormal log data and the preset check template information; and then the trino engine is used to execute the primary key duplication check statement, and if the corresponding first check result is empty, it is determined that the corresponding zip list data check is passed. In this way, the present application can quickly check the data corresponding to the zip list log by using the preset abnormal data determination rule, and the trino engine can improve the checking speed and reduce the checking time consumption. BRIEF DESCRIPTION OF DRAWINGS

[0038] In order to more clearly illustrate the technical solutions in the embodiments of the present application or the prior art, the drawings needed in the embodiments or prior art description will be briefly introduced below. Obviously, the drawings in the following description are only embodiments of the present application, and for those skilled in the art, other drawings can be obtained without creative labor on the basis of the provided drawings.

[0039] Figure 1 A data warehouse zip list data verification method flowchart disclosed by the present application;

[0040] Figure 2 A specific abnormal data determination rule flowchart disclosed by the present application;

[0041] Figure 3 A specific primary key duplication check function calling flowchart disclosed by the present application;

[0042] Figure 4 A specific data warehouse zip list data verification method flowchart disclosed by the present application;

[0043] Figure 5 Another specific data warehouse zip list data verification method flowchart disclosed by the present application;

[0044] Figure 6 A data warehouse zip list data verification device structure schematic diagram disclosed by the present application;

[0045] Figure 7 Figure 1 is a structural diagram of an electronic device disclosed in the present application. DETAILED DESCRIPTION

[0046] The technical solutions in the embodiments of the present application will be clearly and completely described with reference to the drawings in the embodiments of the present application. Obviously, the described embodiments are only a part of the embodiments of the present application, rather than all the embodiments. Based on the embodiments in the present application, all other embodiments obtained by those skilled in the art without creative work shall fall within the scope of the present application.

[0047] Referring to Figure 1 As shown in the drawings, the embodiments of the present application disclose a data warehouse zip list data verification method, comprising:

[0048] Step S11, acquiring suspected abnormal log data satisfying a preset abnormal data determination rule; the preset abnormal data determination rule is a rule of determining that the ratio between the read record number and the write record number of the target data group in the zip list run batch log is not a positive integer.

[0049] In the present application, firstly, suspected abnormal log data satisfying a preset abnormal data determination rule can be acquired; in a specific embodiment, the acquiring of the suspected abnormal log data satisfying the preset abnormal data determination rule can include: listening to the zip table run batch log through SparkListener to acquire the suspected abnormal log data satisfying the preset abnormal data determination rule. Specifically, the zip table run batch log can be listened to through SparkListener (event listener interface class), and the log can be acquired at a second level. When the log event is parsed to determine whether there is a suspected abnormal log file, it can also be completed at a second level, so that the log data suspected to be abnormal can be quickly acquired. It should be pointed out that the preset abnormal data determination rule in the present application is a rule for determining that the ratio between the read record number and the write record number of a certain target data group in the zip table run batch log is not a positive integer; it can be understood that when the information of Shuffle Read&Write in the spark sql (distributed SQL query) log does not present a certain rule, there is a high probability that the zip table run batch is abnormal. For example, in the log, the i-th group of data Shuffle Read records (read records) is r_i, and Shuffle Write records (write records) is w_i. It is judged whether k_i is a positive integer, where k_i=r_i / w_i. When k_i is not a positive integer, it can be inferred that the data corresponding to the i-th group of Shuffle Read (data reading) and Shuffle Write (data writing) may be abnormal, and it can be inferred that the zip table data may be abnormal. Further, in order to ensure that the spark history (log parsing) log is complete, when the zip table run batch is completed, the stop() function of the spark session (the entrance of Spark SQL) can be called before the write completion flag, and the spark session is terminated, so that the complete zip table run batch log can be acquired.

[0050] In a specific embodiment, as Figure 2As shown, first, the jar (a file format) package can be actively called based on the python language, and parameters such as the eventlog (event log) directory, the application id (program identification), etc. can be passed, then the log information of the zip table run batch can be listened to through the SparkListener, the log information can be parsed, and the number of Shuffle Read and Shuffle Write corresponding to the target data group can be obtained; then, using Shuffle Read records as r_i and Shuffle Write records as w_i, it is judged whether k_i is a positive integer, if k_i is a positive integer, it means that the target data group is normal, and there is no primary key duplication abnormality; correspondingly, if k_i is not a positive integer, it means that there is a data abnormality, and corresponding measures can be taken.

[0051] In step S12, a corresponding primary key duplication check statement is generated according to the first zip table data corresponding to the suspected abnormal log data and the preset check template information.

[0052] In this application, after obtaining the corresponding suspected abnormal log data through the preset abnormal data determination rule, the related check statement can be further generated, and the corresponding primary key duplication check statement can be generated according to the first zip table data corresponding to the suspected abnormal log data and the preset check template information. In specific embodiments, the generation of the primary key duplication check statement according to the first zip table data corresponding to the suspected abnormal log data and the preset check template information can include: determining the zip table run batch task corresponding to the suspected abnormal log data; determining the corresponding primary key check template information from the preset check template information according to the zip table run batch task; generating the corresponding primary key duplication check statement based on the first zip table data corresponding to the suspected abnormal log data and the primary key check template information. Specifically, the zip table run batch task corresponding to the suspected abnormal log data can be determined first, and it can be understood that the zip table partition date corresponds to the corresponding run batch date, and the zip table run batch task is determined. Then the corresponding primary key check template information can be determined from the preset check template information, and the primary key duplication check statement can be generated based on the first zip table data and the corresponding primary key check template information.

[0053] In step S13, the primary key duplication check statement is executed through the trino engine, and if the corresponding first check result is empty, it is determined that the corresponding zip table data check is passed.

[0054] In the present application, after obtaining the first zip list data corresponding to the suspected abnormal log data and the corresponding primary key duplicate check statement, the primary key duplicate check statement can be executed through the trino (a query engine) engine. It can be understood that the trino engine has a faster speed than the spark engine. In this way, the corresponding first check result can be obtained. It should be pointed out that if the first check result is empty, it indicates that there is no primary key duplication, and the corresponding first zip list data passes the check and there is no data anomaly.

[0055] In a specific embodiment, as shown in Figure 3 The step of calling the primary key duplicate check function includes obtaining a check rule (preset check template information) and assembling a primary key duplicate check statement (SQL, structured query language) for the first zip list data according to the first zip list data corresponding to the suspected abnormal log data. Then the primary key duplicate check statement can be executed through the trino engine to obtain the first check result. If the result is empty, it indicates that there is no primary key duplication data, which means that the first zip list data is not abnormal and the check passes. Correspondingly, if the first check result is not empty, it means that there is primary key duplication data, which means that the first zip list data is abnormal and the check fails.

[0056] As can be seen, the present application can use the rules in the zip list data batch log to quickly judge the related zip list data, obtain suspected abnormal zip list data, and then generate related check statements. The trino engine can be used to quickly execute the check statements, and the execution results of the check statements can be used to determine whether the related zip list data is abnormal. In this way, the present application can use the preset abnormal data determination rule and the zip list batch log to perform data anomaly check on all zip list data in the data warehouse, which can reduce the time consumption of data check on all zip list data and improve the efficiency.

[0057] The above embodiments introduce that the zip list data in the data warehouse can be checked for data anomalies by using some rules of the zip list batch log, which can improve the check speed. The following embodiments will specifically introduce a process of checking the accurate number of increments of the zip list data to further ensure the accuracy of the zip list data check result, as shown in Figure 4 The present application discloses a data warehouse zip list data verification method, which comprises:

[0058] In step S21, a target historical time period is obtained through a preset human-computer interaction interface.

[0059] In the embodiments of the present application, the target historical time period is first obtained through a preset human-computer interaction interface; it should be noted that the target historical time period can be a date, and the zip table data saved in the data warehouse before the date is the data corresponding to the historical time period. For example, the data warehouse data increment calculation is usually adjusted to be performed in the early morning, and therefore, the time period from after the increment calculation to the end of the next increment calculation can be regarded as a time period.

[0060] In step S22, all zip table data in the data warehouse is initialized according to the target historical time period to obtain a first data table and a second data table; the first data table contains all zip table data in the data warehouse before the current time period, and the second data table contains the number of primary key data corresponding to the first data table.

[0061] In the present application, after the target historical time period is obtained through the preset human-computer interaction interface, all zip table data in the data warehouse can be initialized according to the target historical time period to obtain a first data table and a second data table. The first data table contains all zip table data in the data warehouse before the current time period (target historical time period), and the second data table represents the number of primary key data corresponding to all zip table data in the data warehouse before the target historical time period. For example, the target historical time period is November 25; in this case, the data in the first zip table is all zip table primary key data in the data warehouse before November 26; correspondingly, the data in the second zip table represents the number of zip table primary key data in the data warehouse before November 25, and records the number of primary key data.

[0062] In step S23, the zip table run batch data in the current time period is counted to obtain a current data table.

[0063] Further, the zip table data in the data warehouse is initialized according to the target historical time period, the target historical time period is regarded as the current time period, and the zip table run batch data in the current time period is counted to obtain a current data table corresponding to the current time period; it can be understood that the current data table only contains the zip table data run in the current time period.

[0064] In step S24, the zip table data existing in the first data table is removed from the current data table through a trino engine to obtain a third data table representing the number of primary key data in the current data table after the data is removed.

[0065] It can be understood that the run batch data of the current time period is likely to be repeated with the data saved in the data warehouse, including new data and updated data. Therefore, the application can exclude the data of the first data table from the data of the first data table through the trino engine, so that the corresponding data table after the exclusion can be obtained, and then the third data table representing the number of primary key data in the data table after the exclusion can be obtained.

[0066] Step S25, calculating all the data of the data warehouse in the current time period through the trino engine, obtaining the fourth data table representing the number of corresponding primary key data, and verifying the number of primary key data between the fourth data table, the third data table and the second data table to obtain a second verification result.

[0067] In the embodiment of the application, the second data table representing the number of primary key data of the data warehouse before the current time period and the third data table representing the number of primary key data of the increment of the current time period can be used to obtain the number of primary key data of all the data of the data warehouse before the next time period. Further, the trino engine can be used to calculate the number of primary key data of all the data of the data warehouse before the next time period, and the fourth data table can be obtained. By comparing the relationship between the fourth data table and the third data table and the second data table, it can be determined whether the data of the data warehouse is abnormal. Specifically, the number of primary key data between the fourth data table and the third data table and the second data table can be verified to determine whether there is abnormal data (such as primary key duplication or loss), so that a second verification result for the number of primary keys can be obtained.

[0068] In a specific embodiment, the verification of the number of primary key data between the fourth data table, the third data table and the second data table to obtain a second verification result can include determining whether the number of primary key data in the fourth data table is equal to the sum of the number of primary key data in the third data table and the second data table to obtain a second verification result. Specifically, it can be determined whether the sum of the number of all primary key data in the third data table and the second data table is equal to the number of primary key data in the fourth data table to obtain a second verification result. It should be noted that if the number of primary key data obtained by the foregoing two methods is equal, it means that the data of the data warehouse is not abnormal; correspondingly, if the number of primary key data is not equal, it means that the data of the data warehouse has abnormal conditions such as primary key duplication or loss.

[0069] Step S26, if the second verification result indicates that the verification is passed, the third data table and the fourth data table are used to update the first data table and the second data table respectively.

[0070] In the embodiments of the present application, if the second check result represents that the check passes, the zip table data in the data warehouse does not exist abnormity, the related data table can be updated, and specifically, the first data table and the second data table can be updated by using the third data table and the fourth data table. In a specific embodiment, the updating of the first data table and the second data table by using the third data table and the fourth data table can include: during the idle period, the corresponding primary key data in the third data table is supplemented to the first data table to obtain a new first data table; the fourth data table is determined as a new second data table, so that the new first data table and the new second data table are used to perform the check process of the zip table data in the next time period. Specifically, during the idle time, such as after the morning batch ends, or during the day when the resources are idle, the corresponding zip table primary key data in the third data table is supplemented to the first data table to obtain a new first data table, and the fourth data table is directly determined as a new second data table; in this way, in the next time period, the new first data table and the new second data table can be used to complete the check process of the corresponding zip table data.

[0071] In a specific embodiment, after obtaining the first check result and the second check result, the operation of triggering termination of the current zip table data batch running script can be further included if the first check result and / or the second check result represents that the check does not pass. Specifically, if any one or both of the first check result and the second check result represents that the data check does not pass, it means that the corresponding zip table batch running task appears data abnormity, and the task should be stopped immediately and handled by the staff; the present application can timely trigger the operation of terminating the current zip table data batch running script, and no longer write the job completion dependency identifier. Correspondingly, if the first check result and the second check result both represent that the data check passes, the related data table can be updated, and the next time period can be entered to perform the next round of zip table data check process.

[0072] In a specific embodiment, as Figure 5As shown, after determining the target historical time period, it is necessary to initialize the zipped table data in the data warehouse. Specifically, the valid partition primary key data of the zipped table before the target historical time period is recorded as the key_tab table (the first data table), and the number of valid data primary keys before the current time period (the target historical time period) is recorded as the yes_cnt table (the second data table). Then, the precise incremental number verification process can be performed in the early morning. Specifically, the day_cnt (the third data table) representing the number of newly added zipped table primary key data in the current time period can be calculated, the yes_cnt can be obtained, and the total number of zipped table primary key data valid_cnt (the fourth data table) before the next time period can be calculated. Then, the primary key data number is verified according to the formula: result = if (valid_cnt = day_cnt + yes_cnt, 1, 0). It can be understood that when result is 1, the number of primary keys in the fourth data table is equal to the sum of the number of primary keys in the third data table and the second data table, and the verification is passed. When result is 0, it means that the data primary key is duplicated or lost, and the verification is not passed. At the end of the verification, the newly added primary key data can be supplemented to the partition of the data warehouse during the idle time in the daytime to update the number of valid partition primary keys, so as to perform the next round of data verification process.

[0073] As can be seen, in the embodiment of the application, the abnormal situation of the number of primary keys of the zipped table data can be determined from the specified target historical time period through the incremental statistical process of the zipped table data and the trino engine calculation process. In this way, based on the rapid verification of the zipped table data by the preset abnormal data determination rule, and in combination with the primary key number verification process for incremental data, a more accurate zipped table data verification result can be obtained.

[0074] As shown in Figure 6 The data warehouse zipped table data verification device disclosed in the embodiment of the application comprises:

[0075] The log data acquisition module 11 is configured to acquire suspected abnormal log data satisfying a preset abnormal data determination rule. The preset abnormal data determination rule is a rule for determining that the ratio between the number of read records and the number of written records of a target data group in the zipped table batch running log is not a positive integer;

[0076] The verification statement generation module 12 is configured to generate a corresponding primary key duplication verification statement according to the first zipped table data corresponding to the suspected abnormal log data and the preset verification template information.

[0077] The check statement execution module 13 is configured to execute the primary key duplicate check statement through the trino engine, and if the corresponding first check result is empty, it is determined that the corresponding zip list data check is passed.

[0078] Therefore, the application can quickly check the data corresponding to the zip list log by using the preset abnormal data determination rule, and the trino engine can improve the checking speed and reduce the checking time consumption.

[0079] In a specific embodiment, the log data acquisition module 11 can include:

[0080] The log data acquisition unit is configured to listen to the zip list batch running log through the SparkListener to acquire suspected abnormal log data satisfying the preset abnormal data determination rule.

[0081] In a specific embodiment, the check statement generation module 12 can include:

[0082] The task determination unit is configured to determine the zip list batch running task corresponding to the suspected abnormal log data.

[0083] The check template determination unit is configured to determine the corresponding primary key check template information from the preset check template information according to the zip list batch running task.

[0084] The check statement generation unit is configured to generate the corresponding primary key duplicate check statement based on the first zip list data corresponding to the suspected abnormal log data and the primary key check template information.

[0085] In a specific embodiment, the device can further include:

[0086] The time period acquisition unit is configured to acquire the target historical time period through the preset human-computer interaction interface.

[0087] The data table initialization unit is configured to initialize all zip list data in the data warehouse according to the target historical time period to obtain a first data table and a second data table; the first data table contains all zip list data in the data warehouse before the current time period, and the second data table contains the number of primary key data corresponding to the first data table.

[0088] The batch running data statistics unit is configured to count the zip list batch running data in the current time period to obtain a current data table.

[0089] The data elimination unit is configured to eliminate the zip list data existing in the first data table in the current data table through the trino engine to obtain a third data table representing the number of primary key data in the current data table after the elimination of data.

[0090] a data table calculation unit configured to calculate all the zipped table data in the data warehouse in a current time period through the trino engine to obtain a fourth data table representing the number of data entries of the corresponding primary key;

[0091] a primary key entry number checking sub-module configured to check the number of primary key entries among the fourth data table, the third data table and the second data table to obtain a second checking result;

[0092] a data table updating sub-module configured to update the first data table and the second data table respectively by using the third data table and the fourth data table when the second checking result indicates that the checking is passed.

[0093] In another specific embodiment, the primary key entry number checking sub-module can include:

[0094] a primary key entry number checking unit configured to determine whether the number of primary key entries in the fourth data table is equal to the sum of the number of primary key entries of the third data table and the second data table to obtain the second checking result.

[0095] In yet another specific embodiment, the data table updating sub-module can include:

[0096] a first data table updating unit configured to supplement the primary key data corresponding to the third data table to the first data table to obtain a new first data table during an idle period;

[0097] a second data table updating unit configured to determine the fourth data table as a new second data table so as to use the new first data table and the new second data table to perform the checking process of the zipped table data in a next time period.

[0098] In a specific embodiment, the apparatus can further include:

[0099] a run batch script terminating unit configured to trigger an operation of terminating the current zipped table data run batch script when the first checking result and / or the second checking result indicates that the checking is not passed.

[0100] Further, the embodiments of the present application also disclose an electronic device, Figure 7 is a structural diagram of the electronic device 20 according to an exemplary embodiment, and the content in the figure cannot be considered as any limitation on the use range of the present application.

[0101] Figure 7A structural schematic diagram of an electronic device 20 is provided in the embodiments of the present application. The electronic device 20 can specifically include at least one processor 21, at least one memory 22, a power supply 23, a communication interface 24, an input / output interface 25 and a communication bus 26. The memory 22 is configured to store a computer program, and the processor 21 is configured to load and execute the computer program to implement the related steps in the data warehouse zip table data verification method disclosed in any of the foregoing embodiments. In addition, the electronic device 20 in the embodiments of the present application can be specifically an electronic computer.

[0102] In the embodiments of the present application, the power supply 23 is configured to provide working voltage for each hardware device on the electronic device 20; the communication interface 24 is capable of creating a data transmission channel between the electronic device 20 and external devices, and the communication protocol followed by the communication interface 24 can be any communication protocol applicable to the technical solution of the present application, which is not limited specifically herein; the input / output interface 25 is configured to obtain external input data or output data to the outside, and the specific interface type can be selected according to the specific application needs, which is not limited specifically herein.

[0103] In addition, the memory 22 as a carrier for resource storage can be a read-only memory, a random access memory, a magnetic disk or an optical disk, etc., and the resources stored thereon can include an operating system 221, a computer program 222, etc., and the storage mode can be temporary storage or permanent storage.

[0104] The operating system 221 is configured to manage and control each hardware device on the electronic device 20 and the computer program 222, and can be Windows Server, Netware, Unix, Linux, etc. In addition to the computer program capable of completing the data warehouse zip table data verification method executed by the electronic device 20 disclosed in any of the foregoing embodiments, the computer program 222 can further include a computer program capable of completing other specific work.

[0105] Further, the present application further discloses a computer readable storage medium for storing a computer program; wherein the computer program is executed by a processor to implement the data warehouse zip table data verification method disclosed in the foregoing embodiments. The specific steps of the method can refer to the corresponding contents disclosed in the foregoing embodiments, which will not be repeated here.

[0106] The embodiments in the specification are described in a progressive manner, and each embodiment focuses on the differences from other embodiments. The same or similar parts of each embodiment can be referred to each other. For the device disclosed in the embodiments, since it corresponds to the method disclosed in the embodiments, the description is relatively simple, and the related parts can refer to the method part.

[0107] Those skilled in the art will further appreciate that the units and algorithm steps of the various examples described in connection with the embodiments disclosed herein can be implemented in electronic hardware, computer software, or combinations of both. To clearly illustrate this interchangeability of hardware and software, various examples have been described herein in terms of their functionality, which has been described generally and symbolically in flow charts. Having thus described the functionality of the examples, a person of ordinary skill in the art will be able to implement such functions in hardware and / or software, using the means and methods available to those skilled in the art. The examples described herein are not meant to limit the scope of the application, but merely to provide examples of the methods and systems being described.

[0108] The steps of a method or algorithm described in connection with the embodiments disclosed herein can be embodied directly in hardware, in a software module executed by a processor, or in a combination of the two. A software module can reside in random access memory (RAM), flash memory, read-only memory (ROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM), registers, hard disk, a removable disk, a CD-ROM, or any other form of storage medium known in the art.

[0109] Finally, it should be noted that the terms "first", "second", and the like, herein do not denote any order, quantity, combination, or importance, but rather are used to distinguish one element from another, and are more especially used for the purpose of distinction from other elements in the specification. Also, the terms "comprise", "include" or "contain" or any other variant thereof are intended to encompass non-exclusive inclusions, such that processes, methods, articles, or apparatuses that comprise, include, or contain a list of elements are not limited to those elements, but can include other elements not expressly listed or inherent to such processes, methods, articles, or apparatuses. Without further limitation, an element defined by the phrase "comprising a... " does not exclude the presence of additional identical elements in the process, method, article, or apparatus that includes the element.

[0110] The above has described the technical solutions provided by the present application in detail, and the principles and implementation manners of the present application have been described by using specific examples; the above example descriptions are only used to help understand the method and core idea of the present application; meanwhile, for those skilled in the art, according to the idea of the present application, the specific implementation manners and application ranges can be changed; in conclusion, the content of the specification should not be understood as limiting the present application.

Claims

1. A method for validating data in a data warehouse zipper table, characterized in that, include: Obtain suspected abnormal log data that meets the preset abnormal data judgment rules; The preset abnormal data judgment rule is a rule that determines whether the ratio between the number of read records and the number of written records of the target data group in the batch log of the zipper table is a non-positive integer; Based on the first zipper table data corresponding to the suspected abnormal log data and the preset verification template information, generate the corresponding primary key duplicate verification statement; The primary key duplicate check statement is executed by the trino engine. If the corresponding first check result is empty, the corresponding linked table data is determined to have passed the check. This also includes: The target historical time period is obtained through a preset human-computer interaction interface; The data in all the linked tables in the data warehouse are initialized according to the target historical time period to obtain a first data table and a second data table; the first data table contains all the linked table data in the data warehouse before the current time period, and the second data table contains the number of primary key data rows corresponding to the first data table; The batch data of the zipper table within the current time period is statistically analyzed to obtain the current data table; The trino engine removes the zipper table data that exists in the first data table from the current data table, resulting in a third data table representing the number of primary key data rows in the current data table after the data removal. The trino engine calculates all the zipper table data in the data warehouse for the current time period to obtain a fourth data table representing the number of primary key data rows. The number of primary key data rows between the fourth data table, the third data table, and the second data table is then verified to obtain a second verification result. If the second verification result indicates that the verification is successful, then the first data table and the second data table are updated using the third data table and the fourth data table, respectively.

2. The data warehouse zipper table data verification method according to claim 1, characterized in that, The step of obtaining suspected abnormal log data that meets the preset abnormal data judgment rules includes: By using SparkListener to monitor the batch logs of the linked table, suspected abnormal log data that meets the preset abnormal data judgment rules can be obtained.

3. The data warehouse zipper table data verification method according to claim 1, characterized in that, The step of generating a corresponding primary key duplicate check statement based on the first chain table data corresponding to the suspected abnormal log data and the preset check template information includes: Determine the batch task corresponding to the zipper table of the suspected abnormal log data; Based on the batch processing task of the zipper table, the corresponding primary key verification template information is determined from the preset verification template information; Based on the first zipper table data corresponding to the suspected abnormal log data and the primary key verification template information, a corresponding primary key duplicate verification statement is generated.

4. The data warehouse zipper table data verification method according to claim 1, characterized in that, The step of verifying the number of primary key data rows among the fourth data table, the third data table, and the second data table to obtain a second verification result includes: Determine whether the number of primary key data rows in the fourth data table is equal to the sum of the number of primary key data rows in the third data table and the second data table to obtain a second verification result.

5. The data warehouse zipper table data verification method according to claim 1, characterized in that, The step of updating the first data table and the second data table using the third data table and the fourth data table respectively includes: During idle periods, the primary key data corresponding to the third data table is added to the first data table to obtain a new first data table; The fourth data table is designated as the new second data table so that the new first data table and the new second data table can be used in the next time period to perform the verification process of the zipper table data.

6. The data warehouse zipper table data verification method according to claim 1, characterized in that, Also includes: If the first verification result and / or the second verification result indicate that the verification fails, the operation of terminating the current batch script for running the data in the linked list table will be triggered.

7. A data warehouse zipper table data verification device, characterized in that, include: The log data acquisition module is used to acquire suspected abnormal log data that meets the preset abnormal data judgment rules. The preset abnormal data judgment rule is a rule that determines whether the ratio between the number of read records and the number of written records of the target data group in the batch log of the zipper table is a non-positive integer; The verification statement generation module is used to generate corresponding primary key duplicate verification statements based on the first zipper table data corresponding to the suspected abnormal log data and the preset verification template information. The validation statement execution module is used to execute the primary key duplicate validation statement through the trino engine. If the corresponding first validation result is empty, the corresponding linked list data is determined to have passed the validation. The device further includes: The time period acquisition unit is used to acquire the target historical time period through a preset human-computer interaction interface; The data table initialization unit is used to initialize all the zipper table data in the data warehouse according to the target historical time period to obtain a first data table and a second data table; the first data table contains all the zipper table data in the data warehouse before the current time period, and the second data table contains the number of primary key data rows corresponding to the first data table; The batch data statistics unit is used to count the batch data of the zipper table within the current time period to obtain the current data table. The data removal unit is used to remove the zipper table data existing in the first data table through the trino engine in the current data table, and obtain a third data table representing the number of primary key data rows in the current data table after data removal; The data table calculation unit is used to calculate all the zipper table data in the data warehouse in the current time period through the trino engine to obtain a fourth data table representing the number of corresponding primary key data rows; The primary key row count verification submodule is used to verify the number of primary key data rows among the fourth data table, the third data table, and the second data table, and obtain a second verification result; The data table update submodule is used to update the first data table and the second data table respectively using the third data table and the fourth data table when the second verification result indicates that the verification has passed.

8. An electronic device, characterized in that, include: Memory, used to store computer programs; A processor for executing the computer program to implement the data warehouse zipper table data verification method as described in any one of claims 1 to 6.

9. A computer-readable storage medium, characterized in that, Used to store computer programs, which, when executed by a processor, implement the data warehouse zipper table data verification method as described in any one of claims 1 to 6.

Images