Data integrity checking method and device, terminal equipment and storage medium

Abstract

The application discloses a data integrity checking method, comprising: obtaining an execution statement of a target data warehouse; determining a data table and target condition information in the execution statement; screening a non-temporary data table in the data table; obtaining data storage information according to the target condition information and the non-temporary data table; and obtaining an integrity checking result according to the data storage information. The application also discloses a data integrity checking device, a terminal device and a storage medium. The method of the application realizes integrity checking of data corresponding to an execution statement, and solves the problem of lacking data integrity checking schemes in the prior art.

Description

Technical Field

[0001] This invention relates to the field of data management technology, and in particular to a data integrity verification method, apparatus, terminal device, and storage medium. Background Technology

[0002] Currently, when running batch data, data warehouses rely on upstream data or upstream tasks. When an upstream task fails, even though the upstream task may show as having executed successfully, the data table may not have any data generated (or the data partition content may be empty), causing downstream tasks that depend on the data to fail in a cascading manner.

[0003] To prevent downstream data-dependent scheduling tasks from causing a cascading failure, a data integrity verification scheme is urgently needed. Summary of the Invention

[0004] The main objective of this invention is to provide a data integrity verification method, apparatus, terminal device, and storage medium, aiming to solve the technical problem of the lack of data integrity verification schemes in the prior art.

[0005] To achieve the above objectives, this invention proposes a data integrity verification method, which includes the following steps:

[0006] Retrieve the execution statements for the target data warehouse;

[0007] The data table and target condition information are determined in the execution statement;

[0008] Filter out non-temporary data tables from the data table;

[0009] Based on the target condition information and the non-temporary data table, the data storage information is obtained;

[0010] Based on the data storage information, the integrity verification result is obtained.

[0011] Optionally, the step of determining the data table and target condition information in the execution statement includes:

[0012] If the execution statement contains a subquery, then the subquery is parsed to determine the data table and the target condition information.

[0013] Optionally, the step of determining the data table and target condition information in the execution statement includes:

[0014] If the execution statement contains a target related query statement, then the data table and the target condition information are extracted from the target related query statement.

[0015] Optionally, before the step of determining the data table and target condition information in the execution statement, the method further includes:

[0016] If the statement type of the executed statement is a target type statement, then the executed statement is split to obtain multiple sub-executed statements;

[0017] The step of determining the data table and target condition information in the executed statement includes:

[0018] The data table and target condition information are determined in the multiple sub-execution statements.

[0019] Optionally, before the step of obtaining data storage information based on the target condition information and the non-temporary data table, the method further includes:

[0020] Create preset filter conditions;

[0021] The non-temporary data table is filtered using the preset filtering conditions to obtain a result data table;

[0022] The step of obtaining data storage information based on the target condition information and the non-temporary data table includes:

[0023] Based on the target condition information and the result data table, the data storage information is obtained.

[0024] Optionally, before the step of obtaining the integrity verification result based on the data storage information, the method further includes:

[0025] Based on the non-temporary data table, determine the temporary data table from the data table;

[0026] The step of obtaining the integrity verification result based on the data storage information includes:

[0027] Based on the temporary data table and the data storage information, the integrity verification result is obtained.

[0028] Optionally, the execution statement is used to execute the scheduling task in the target warehouse; after the step of obtaining the integrity verification result based on the temporary data table and the data storage information, the method further includes:

[0029] If the integrity check result indicates that the data is incomplete, an exception instruction is obtained;

[0030] Based on the abnormal instruction, the scheduling task is interrupted, and a prompt message corresponding to the abnormal instruction is output.

[0031] In addition, to achieve the above objectives, the present invention also proposes a data integrity verification device, the device comprising:

[0032] The acquisition module is used to acquire the execution statements of the target data warehouse;

[0033] The determination module is used to determine the data table and target condition information in the executed statement;

[0034] The filtering module is used to filter out non-temporary data tables from the data table;

[0035] The first obtaining module is used to obtain data storage information based on the target condition information and the non-temporary data table;

[0036] The second obtaining module is used to obtain the integrity verification result based on the data storage information.

[0037] Furthermore, to achieve the above objectives, the present invention also proposes a terminal device, the terminal device comprising: a memory, a processor, and a data integrity verification program stored in the memory and running on the processor, wherein the data integrity verification program, when executed by the processor, implements the steps of the data integrity verification method as described in any of the preceding claims.

[0038] Furthermore, to achieve the above objectives, the present invention also proposes a storage medium storing a data integrity verification program, which, when executed by a processor, implements the steps of the data integrity verification method as described in any of the preceding claims.

[0039] The present invention proposes a data integrity verification method, which involves: obtaining the execution statement of the target data warehouse; identifying data tables and target condition information in the execution statement; filtering out non-temporary data tables from the data tables; obtaining data storage information based on the target condition information and the non-temporary data tables; and obtaining an integrity verification result based on the data storage information.

[0040] Using the method of this invention, data storage information can be obtained based on the target condition information and non-temporary data table corresponding to the executed statement. Then, based on the data storage information, the final integrity verification result can be obtained, thereby realizing the integrity verification of the data corresponding to the executed statement and solving the problem of the lack of data integrity verification schemes in the prior art. Attached Figure Description

[0041] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on the structures shown in these drawings without creative effort.

[0042] Figure 1 This is a schematic diagram of the terminal device structure of the hardware operating environment involved in the embodiments of the present invention;

[0043] Figure 2 This is a flowchart illustrating the first embodiment of the data integrity verification method of the present invention;

[0044] Figure 3 This is a structural block diagram of the first embodiment of the data integrity verification device of the present invention.

[0045] The objectives, features, and advantages of this invention will be further explained in conjunction with the embodiments and with reference to the accompanying drawings. Detailed Implementation

[0046] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only a part of the embodiments of the present invention, and not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those of ordinary skill in the art without creative effort are within the scope of protection of the present invention.

[0047] Reference Figure 1 , Figure 1 This is a schematic diagram of the terminal device structure of the hardware operating environment involved in the embodiments of the present invention.

[0048] Terminal equipment can be user equipment (UE) such as mobile phones, smartphones, laptops, digital broadcast receivers, personal digital assistants (PDAs), and tablet computers (PADs), handheld devices, in-vehicle devices, wearable devices, computing devices or other processing devices connected to a wireless modem, mobile stations (MS), etc. Terminal equipment may be referred to as user terminals, portable terminals, desktop terminals, etc.

[0049] Typically, a terminal device includes: at least one processor 301, a memory 302, and a data integrity verification program stored in the memory and executable on the processor, the data integrity verification program being configured to implement the steps of the data integrity verification method as described above.

[0050] Processor 301 may include one or more processing cores, such as a quad-core processor or an octa-core processor. Processor 301 may be implemented using at least one hardware form of DSP (Digital Signal Processing), FPGA (Field-Programmable Gate Array), or PLA (Programmable Logic Array). Processor 301 may also include a main processor and a coprocessor. The main processor, also known as a CPU (Central Processing Unit), is used to process data in the wake-up state; the coprocessor is a low-power processor used to process data in the standby state. In some embodiments, processor 301 may integrate a GPU (Graphics Processing Unit), which is responsible for rendering and drawing the content to be displayed on the screen. Processor 301 may also include an AI (Artificial Intelligence) processor, which handles operations related to data integrity verification methods, enabling the data integrity verification method model to train and learn autonomously, improving efficiency and accuracy.

[0051] The memory 302 may include one or more storage media, which may be non-transitory. The memory 302 may also include high-speed random access memory and non-volatile memory, such as one or more disk storage devices or flash memory devices. In some embodiments, the non-transitory storage media in the memory 302 are used to store at least one instruction, which is executed by the processor 301 to implement the data integrity verification method provided in the method embodiments of this application.

[0052] In some embodiments, the terminal may also optionally include a communication interface 303 and at least one peripheral device. The processor 301, memory 302, and communication interface 303 can be connected via a bus or signal line. Each peripheral device can be connected to the communication interface 303 via a bus, signal line, or circuit board. Specifically, the peripheral device includes at least one of a radio frequency circuit 304, a display screen 305, and a power supply 306.

[0053] The communication interface 303 can be used to connect at least one I / O (Input / Output) related peripheral device to the processor 301 and the memory 302. In some embodiments, the processor 301, the memory 302, and the communication interface 303 are integrated on the same chip or circuit board; in some other embodiments, any one or two of the processor 301, the memory 302, and the communication interface 303 can be implemented on separate chips or circuit boards, which is not limited in this embodiment.

[0054] The radio frequency (RF) circuit 304 is used to receive and transmit RF (Radio Frequency) signals, also known as electromagnetic signals. The RF circuit 304 communicates with communication networks and other communication devices via electromagnetic signals. The RF circuit 304 converts electrical signals into electromagnetic signals for transmission, or converts received electromagnetic signals back into electrical signals. Optionally, the RF circuit 304 includes: an antenna system, an RF transceiver, one or more amplifiers, a tuner, an oscillator, a digital signal processor, a codec chipset, a user identity module card, etc. The RF circuit 304 can communicate with other terminals through at least one wireless communication protocol. This wireless communication protocol includes, but is not limited to: metropolitan area networks (MANs), various generations of mobile communication networks (2G, 3G, 4G, and 5G), wireless local area networks (WLANs), and / or WiFi (Wireless Fidelity) networks. In some embodiments, the RF circuit 304 may also include circuitry related to NFC (Near Field Communication), which is not limited in this application.

[0055] Display screen 305 is used to display a UI (User Interface). This UI may include graphics, text, icons, videos, and any combination thereof. When display screen 305 is a touch display screen, it also has the ability to collect touch signals on or above its surface. These touch signals can be input as control signals to processor 301 for processing. In this case, display screen 305 can also be used to provide virtual buttons and / or a virtual keyboard, also known as soft buttons and / or a soft keyboard. In some embodiments, display screen 305 can be a single screen, the front panel of an electronic device; in other embodiments, display screen 305 can be at least two screens, respectively disposed on different surfaces of the electronic device or in a folded design; in still other embodiments, display screen 305 can be a flexible display screen, disposed on a curved or folded surface of the electronic device. Furthermore, display screen 305 can also be configured as a non-rectangular, irregular shape, i.e., a non-rectangular screen. Display screen 305 can be made of materials such as LCD (Liquid Crystal Display) or OLED (Organic Light-Emitting Diode).

[0056] Power supply 306 is used to supply power to various components in an electronic device. Power supply 306 can be AC ​​power, DC power, a disposable battery, or a rechargeable battery. When power supply 306 includes a rechargeable battery, the rechargeable battery can support wired or wireless charging. The rechargeable battery can also be used to support fast charging technology.

[0057] Those skilled in the art will understand that Figure 1 The structure shown does not constitute a limitation on the terminal device and may include more or fewer components than shown, or combine certain components, or have different component arrangements.

[0058] Furthermore, embodiments of the present invention also propose a storage medium storing a data integrity verification program, which, when executed by a processor, implements the steps of the data integrity verification method described above. Therefore, further details will not be repeated here. Additionally, the beneficial effects of using the same method will not be repeated. For technical details not disclosed in the storage medium embodiments of this application, please refer to the description of the method embodiments of this application. As an example, program instructions can be deployed to execute on a single terminal device, or on multiple terminal devices located in one location, or on multiple terminal devices distributed across multiple locations and interconnected via a communication network.

[0059] Those skilled in the art will understand that all or part of the processes in the above embodiments can be implemented by a computer program instructing related hardware. The program can be stored in a storage medium, and when executed, it can include the processes of the embodiments of the above methods. The storage medium can be a magnetic disk, optical disk, read-only memory (ROM), or random access memory (RAM), etc.

[0060] Based on the above hardware structure, an embodiment of the data integrity verification method of the present invention is proposed.

[0061] Reference Figure 2 , Figure 2 This is a flowchart illustrating the first embodiment of the data integrity verification method of the present invention. The method includes the following steps:

[0062] Step S11: Obtain the execution statements for the target data warehouse.

[0063] It should be noted that the executing entity of this invention is a terminal device, which is equipped with a data integrity verification program. When the terminal device executes the data integrity verification program, it implements the steps of the data integrity verification method of this invention.

[0064] The target data warehouse can be any data warehouse, but it typically refers to an SQL database. In this invention, the target data warehouse typically uses execution statements to schedule tasks—batch data processing tasks. These scheduled tasks may have task dependencies (task dependency: a task consists of three tasks A, B, and C that are executed in a blocking manner; A must complete before B can execute, and B must complete before C can execute, and the data produced by the tasks are closely related). The execution statement in step S11 can be the execution statement corresponding to any stage of a scheduling task among multiple scheduled tasks with task dependencies.

[0065] Among them, the execution statement refers to the execution of the SQL statement.

[0066] Step S12: Determine the data table and target condition information in the execution statement.

[0067] There are various types of execution statements, and different execution statements require corresponding strategies to determine the data table and target condition information. The target condition information refers to the WHERE condition information.

[0068] Specifically, the step of determining the data table and target condition information in the executed statement includes: if the executed statement contains a subquery, then parsing the subquery to determine the data table and the target condition information; or,

[0069] If the executed statement contains a target join query statement, then the data table and the target condition information are extracted from the target join query statement. Here, the target join query statement refers to a join query statement.

[0070] In other words, if the current query in the execution statement is a subquery, then the subquery is parsed to retrieve the table (the data table mentioned above) and the WHERE condition information; if the current query in the execution statement is a JOIN query, the tables (the data table mentioned above) on both sides of the JOIN statement and the WHERE condition information are retrieved.

[0071] In some embodiments, the execution statement may include nested queries, in which case step S12 is executed multiple times to obtain the final data table and WHERE condition information.

[0072] Subqueries in SQL statements typically include: WHERE subqueries, HAVING subqueries, FROM subqueries, SELECT subqueries, and EXISIT subqueries.

[0073] The syntax for the query statement is as follows:

[0074] SELECT [SELECT identifier] field list [field alias e.g., A] / * FROM data source [table name] [WHERE clause] [GROUP BY clause] [HAVING clause] [ORDER BY clause] [LIMIT clause];

[0075] For example, to query the id and name fields of table a and select only those with id=1, the statement would be: `select id,name from a where id=1`

[0076] Statements containing subqueries, with the subquery highlighted in bold: `SELECT id, NAME, (SELECT CURDATE() FROM DUAL) AS DATE FROM a`

[0077] Queries containing JOIN statements (joins between two tables include LEFT JOIN, INNER JOIN, RIGHT JOIN, etc.): SELECT id, NAME FROM a LEFT JOIN b ON a.id = b.id

[0078] Includes the UNION ALL statement:

[0079] SELECT id, NAME FROM a

[0080] UNION ALL

[0081] SELECT id, NAME FROM b

[0082] The statement for querying a partitioned table, where dt is the partition field:

[0083] select id,name from a where dt='2021-11-12'

[0084] The query syntax described above are just some examples. This invention does not impose any specific limitations. In practical applications, there may be more and more complex situations.

[0085] In some embodiments, before the step of determining the data table and target condition information in the executed statement, the method further includes: if the statement type of the executed statement is a target type statement, then the executed statement is segmented to obtain multiple sub-executed statements; the step of determining the data table and target condition information in the executed statement includes: determining the data table and target condition information in the multiple sub-executed statements.

[0086] The target type statement refers to the UNION ALL statement (see the example above). This needs to be split into multiple sub-statements. Each sub-query statement is used to determine the data table and target conditions as described above. For example, in the example above, the statement "SELECT id,NAME FROM a UNION ALL SELECT id,NAME FROM b" needs to be split into "SELECT id,NAME FROM a" and "SELECT id,NAME FROM b", resulting in two sub-statements. Then, the data table and target conditions are determined for each of these sub-statements.

[0087] Step S13: Filter out non-temporary data tables from the data tables.

[0088] Specifically, you can set keywords to delete data tables containing those keywords, thus obtaining non-temporary data tables. Data tables containing keywords are temporary data tables; keywords can include "temp" or "dual," etc.

[0089] Step S14: Obtain data storage information based on the target condition information and the non-temporary data table.

[0090] Step S15: Obtain the integrity verification result based on the data storage information.

[0091] After obtaining the non-temporary data table and the WHERE condition information, query the current WHERE condition information to determine the data partition of the data storage, and verify whether the required data already exists in the data partition. That is, the data storage information includes whether the required data exists or not.

[0092] If the data storage information includes data that does not exist, it indicates that the data is incomplete and an anomaly exists; the resulting integrity verification result is "data incomplete." Conversely, if the data storage information includes the required data, it indicates that the data is complete; the resulting integrity verification result is "data complete."

[0093] Furthermore, before the step of obtaining data storage information based on the target condition information and the non-temporary data table, the method further includes: creating preset filtering conditions; filtering the non-temporary data table using the preset filtering conditions to obtain a result data table; the step of obtaining data storage information based on the target condition information and the non-temporary data table includes: obtaining data storage information based on the target condition information and the result data table.

[0094] Users can set some filtering conditions based on their needs—preset filtering conditions—to filter out temporary data tables, so as to avoid the existence of special tables affecting the accuracy of data storage information and causing inaccurate final integrity verification results.

[0095] Furthermore, before the step of obtaining the integrity verification result based on the data storage information, the method further includes: determining a temporary data table in the data table based on the non-temporary data table; the step of obtaining the integrity verification result based on the data storage information includes: obtaining the integrity verification result based on the temporary data table and the data storage information.

[0096] In this embodiment, the integrity verification result also needs to be determined based on the temporary data table. If the integrity verification result indicates incomplete data, it could be due to either the existence of a temporary data table or the absence of required data in the data storage information. If no temporary data table exists, the integrity verification result is obtained based on the data storage information. If the data storage information includes required data, the integrity verification result is obtained based on the existence of the temporary data table.

[0097] Furthermore, the execution statement is used to execute the scheduling task in the target warehouse; after the step of obtaining the integrity verification result based on the temporary data table and the data storage information, the method further includes: if the integrity verification result is that the data is incomplete, then an exception instruction is obtained; according to the exception instruction, the scheduling task is interrupted, and a prompt message corresponding to the exception instruction is output.

[0098] If the data is incomplete, the scheduling task needs to be terminated, and a prompt message should be output to inform the user of the incomplete data. If the integrity check result indicates incomplete data, continuing to execute the scheduling task with task dependencies may lead to errors in the dependent tasks' data, causing a cascading failure in the downstream chain. In this invention, task scheduling is interrupted, thus avoiding the cascading failure of downstream chains due to errors in dependent task data. If the integrity check result indicates complete data, the corresponding scheduling task (data batch processing) continues to execute.

[0099] The present invention proposes a data integrity verification method, which involves: obtaining the execution statement of the target data warehouse; identifying data tables and target condition information in the execution statement; filtering out non-temporary data tables from the data tables; obtaining data storage information based on the target condition information and the non-temporary data tables; and obtaining an integrity verification result based on the data storage information.

[0100] Using the method of this invention, data storage information can be obtained based on the target condition information and non-temporary data table corresponding to the executed statement. Then, based on the data storage information, the final integrity verification result can be obtained, thereby realizing the integrity verification of the data corresponding to the executed statement and solving the problem of the lack of data integrity verification schemes in the prior art.

[0101] Reference Figure 3 , Figure 3 This is a structural block diagram of a first embodiment of the data integrity verification device of the present invention. The device is used in a terminal device and, based on the same inventive concept as the foregoing embodiments, includes:

[0102] Module 10 is used to obtain the execution statements of the target data warehouse;

[0103] The determination module 20 is used to determine the data table and target condition information in the executed statement;

[0104] Filtering module 30 is used to filter out non-temporary data tables from the data table;

[0105] The first obtaining module 40 is used to obtain data storage information based on the target condition information and the non-temporary data table;

[0106] The second obtaining module 50 is used to obtain the integrity verification result based on the data storage information.

[0107] It should be noted that since the steps performed by the device in this embodiment are the same as those in the aforementioned method embodiments, the specific implementation methods and the technical effects that can be achieved can be referred to the aforementioned embodiments, and will not be repeated here.

[0108] The above description is merely an optional embodiment of the present invention and does not limit the patent scope of the present invention. All equivalent structural transformations made using the contents of the present invention's specification and drawings under the inventive concept of the present invention, or direct / indirect applications in other related technical fields, are included within the patent protection scope of the present invention.

Claims

1. A data integrity verification method, characterized in that, The method includes the following steps: Retrieve the execution statements for the target data warehouse; The data table and target condition information are determined in the execution statement; Filter out non-temporary data tables from the data table; Based on the target condition information and the non-temporary data table, the data storage information is obtained; Based on the data storage information, the integrity verification result is obtained; Before the step of obtaining data storage information based on the target condition information and the non-temporary data table, the method further includes: Create preset filter conditions; The non-temporary data table is filtered using the preset filtering conditions to obtain a result data table; The step of obtaining data storage information based on the target condition information and the non-temporary data table includes: Based on the target condition information and the result data table, data storage information is obtained; wherein, the data storage information includes whether the required data exists or not.

2. The method as described in claim 1, characterized in that, The step of determining the data table and target condition information in the executed statement includes: If the execution statement contains a subquery, then the subquery is parsed to determine the data table and the target condition information.

3. The method as described in claim 1, characterized in that, The step of determining the data table and target condition information in the executed statement includes: If the execution statement contains a target related query statement, then the data table and the target condition information are extracted from the target related query statement.

4. The method as described in claim 1, characterized in that, Before the step of determining the data table and target condition information in the executed statement, the method further includes: If the statement type of the executed statement is a target type statement, then the executed statement is split to obtain multiple sub-executed statements; The step of determining the data table and target condition information in the executed statement includes: The data table and target condition information are determined in the multiple sub-execution statements.

5. The method as described in claim 1, characterized in that, Before the step of obtaining the integrity verification result based on the data storage information, the method further includes: Based on the non-temporary data table, determine the temporary data table from the data table; The step of obtaining the integrity verification result based on the data storage information includes: Based on the temporary data table and the data storage information, the integrity verification result is obtained.

6. The method as described in claim 1, characterized in that, The execution statement is used to execute the scheduling task of the target data warehouse; after the step of obtaining the integrity verification result based on the temporary data table and the data storage information, the method further includes: If the integrity check result indicates that the data is incomplete, an exception instruction is obtained; Based on the abnormal instruction, the scheduling task is interrupted, and a prompt message corresponding to the abnormal instruction is output.

7. A data integrity verification device, characterized in that, The device includes: The acquisition module is used to acquire the execution statements of the target data warehouse; The determination module is used to determine the data table and target condition information in the executed statement; The filtering module is used to filter out non-temporary data tables from the data table; The first obtaining module is used to obtain data storage information based on the target condition information and the non-temporary data table; The second obtaining module is used to obtain the integrity verification result based on the data storage information; Before obtaining the data storage information based on the target condition information and the non-temporary data table, the method further includes: Create preset filter conditions; The non-temporary data table is filtered using the preset filtering conditions to obtain a result data table; The first obtaining module is further configured to obtain data storage information based on the target condition information and the result data table; wherein, the data storage information includes whether the required data exists or not.

8. A terminal device, characterized in that, The terminal device includes: a memory, a processor, and a data integrity verification program stored in the memory and running on the processor, wherein when the data integrity verification program is executed by the processor, it implements the steps of the data integrity verification method as described in any one of claims 1 to 6.

9. A storage medium, characterized in that, The storage medium stores a data integrity verification program, which, when executed by a processor, implements the steps of the data integrity verification method as described in any one of claims 1 to 6.

Images