A data verification method and related apparatus

Abstract

This invention relates to a data verification method and related apparatus. The method includes: acquiring an SQL statement to be processed; converting the SQL statement into an abstract syntax tree (AST); traversing the AST to obtain tables, fields, conditions, and dependencies between tables and fields; constructing a directed acyclic graph (DAG) representing the dependencies between tables and fields across the entire data chain based on the tables, fields, conditions, and dependencies; traversing the DAG to obtain the hierarchical relationships between fields of a table and its downstream tables; inputting the corresponding values ​​of upstream and downstream fields into a regression model for verification, and obtaining the verification result; the regression model is obtained by training a preset model, and the training data for the regression model is based on the SQL statements used in the data warehouse construction process and the data warehouse data. This method enables automated data verification with high efficiency and timely response.

Description

Technical Field

[0001] This application relates to the field of computer technology, and in particular to a data verification method and related apparatus. Background Technology

[0002] The most important data source for a data warehouse is primarily the business system, thus requiring a continuous flow of data from the business system into the data warehouse. However, as business complexity and data volume increase, data quality issues frequently arise. Data is multi-dimensional, layered, and interdependent. Monitoring data quality or troubleshooting anomalies requires checking the entire chain of tables, fields, and data, which is extremely manpower-intensive, inefficient, and results in delayed responses. Summary of the Invention

[0003] In view of the above problems, the present invention is proposed to provide a data verification method and related apparatus that overcomes or at least partially solves the above problems.

[0004] In a first aspect, embodiments of the present invention provide a data verification method, the method comprising the following steps:

[0005] Obtain the SQL statement to be processed and convert the SQL statement into an abstract syntax tree;

[0006] Traverse the abstract syntax tree to obtain tables, fields, conditions, and the dependencies between tables and fields; construct a directed acyclic graph to represent the dependencies between tables and fields across the entire chain based on the tables, fields, conditions, and the dependencies between tables and fields.

[0007] Traverse the directed acyclic graph to obtain the hierarchical relationship between the fields of the table and its downstream tables. Input the corresponding values ​​of the upstream fields and the corresponding values ​​of the downstream fields into the regression model for verification and obtain the verification results.

[0008] The regression model is obtained by training a preset model, and the training data of the regression model is constructed based on the SQL statements used in the data warehouse construction process and the data warehouse data.

[0009] In one implementation, traversing the abstract syntax tree to obtain tables, fields, conditions, and the dependencies between tables and fields includes:

[0010] Use the visitor pattern to traverse the abstract syntax tree to obtain tables, fields, conditions, and the dependencies between tables and fields.

[0011] In one implementation, the preset model is a linear regression model.

[0012] In one implementation, constructing the training data for the regression model includes:

[0013] Obtain the SQL statements used in the data warehouse construction process and convert the SQL statements into an abstract syntax tree;

[0014] Traverse the abstract syntax tree to obtain tables, fields, conditions, and the dependencies between tables and fields; construct a directed acyclic graph to represent the dependencies between tables and fields across the entire chain based on the tables, fields, conditions, and the dependencies between tables and fields.

[0015] Traverse the directed acyclic graph to obtain the hierarchical relationships and generation conditions of fields between the table and its downstream tables; load the data warehouse data, locate the generation relationships between data at the row level based on the hierarchical relationships and generation conditions of fields between the table and its downstream tables, and construct training data.

[0016] In one implementation, the visitor pattern is used to traverse the abstract syntax tree during the construction of the training data for the regression model.

[0017] Secondly, embodiments of the present invention provide a data verification device, the device comprising:

[0018] The processing module is used to obtain the SQL statement to be processed and convert the SQL statement into an abstract syntax tree;

[0019] The graph construction module is used to traverse the abstract syntax tree to obtain tables, fields, conditions, and the dependencies between tables and fields; and to construct a directed acyclic graph to represent the dependencies between tables and fields throughout the entire chain based on the tables, fields, conditions, and the dependencies between tables and fields.

[0020] The verification module is used to traverse the directed acyclic graph, obtain the hierarchical relationship between the fields of the table and its downstream tables, input the corresponding values ​​of the upstream fields and the corresponding values ​​of the downstream fields into the regression model for verification, and obtain the verification results.

[0021] The regression model is obtained by training a preset model, and the training data of the regression model is constructed based on the SQL statements used in the data warehouse construction process and the data warehouse data.

[0022] In one implementation, the preset model is a linear regression model.

[0023] In one embodiment, the device further includes:

[0024] The model training module is used to acquire the SQL statements used in the data warehouse construction process, convert the SQL statements into an abstract syntax tree; traverse the abstract syntax tree to obtain tables, fields, conditions, and the dependencies between tables and fields; construct a directed acyclic graph (DAG) to represent the dependencies between tables and fields across the entire chain based on the tables, fields, conditions, and the dependencies between tables and fields; traverse the DAG to obtain the hierarchical relationships and generation conditions of fields between tables and their downstream tables; load data from the data warehouse, locate the generation relationships between data at the row level based on the hierarchical relationships and generation conditions of fields between tables and their downstream tables, and construct training data; input the training data into a preset model to train the obtained regression model.

[0025] Thirdly, embodiments of the present invention provide a computer device, the computer device comprising:

[0026] One or more processors;

[0027] Memory, used to store one or more programs;

[0028] When the one or more programs are executed by the one or more processors, the one or more processors implement the data verification method as described in any one of the first aspects.

[0029] Fourthly, embodiments of the present invention provide a computer-readable storage medium.

[0030] A computer program is stored on the computer-readable storage medium, which, when executed by a processor, implements the data verification method as described in any one of the first aspects.

[0031] In this embodiment of the invention, the SQL statement to be processed is obtained and converted into an abstract syntax tree (AST). The AST is traversed to obtain tables, fields, conditions, and dependencies between tables and fields. A directed acyclic graph (DAG) representing the dependencies between tables and fields across the entire data chain is constructed based on the tables, fields, conditions, and dependencies between tables and fields. The DAG is traversed to obtain the hierarchical relationships between fields of tables and their downstream tables. The values ​​of upstream and downstream fields are input into a regression model for verification to obtain the verification results. The regression model is trained using a preset model, and the training data for the regression model is based on the SQL statements and data warehouse data used during the data warehouse construction process. This method enables automated data verification with high efficiency and timely response. Attached Figure Description

[0032] To more clearly illustrate the technical solutions in the embodiments of this application, the accompanying drawings used in the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0033] Figure 1 A flowchart of a data verification method provided in Embodiment 1 of the present invention;

[0034] Figure 1A This is a schematic diagram of SQL parsing based on AST provided in Embodiment 1 of the present invention;

[0035] Figure 1B A schematic diagram of a DAG is provided for Embodiment 1 of the present invention;

[0036] Figure 1C This is a schematic diagram of the linear regression algorithm provided in Embodiment 1 of the present invention;

[0037] Figure 2 This is a schematic diagram of the structure of a data verification device provided in Embodiment 2 of the present invention;

[0038] Figure 3 This is a schematic diagram of the structure of a computer device provided in Embodiment 3 of the present invention. Detailed Implementation

[0039] The present invention will now be described in further detail with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative of the invention and not intended to limit it. Furthermore, it should be noted that, for ease of description, the accompanying drawings show only the parts relevant to the present invention, and not all of the structures.

[0040] The most important data source for a data warehouse is primarily the business system, thus requiring a continuous flow of data from the business system into the data warehouse. However, as business complexity and data volume increase, data quality issues frequently arise. Data is multi-dimensional, layered, and interdependent. Monitoring data quality or troubleshooting anomalies requires checking the entire chain of tables, fields, and data, which is extremely manpower-intensive, inefficient, and results in delayed responses.

[0041] To overcome or at least partially solve the above problems, embodiments of this application provide a data verification method. This method enables automated data verification with high efficiency and timely response. The following detailed description is provided through embodiments.

[0042] Figure 1This is a flowchart illustrating a data verification method according to Embodiment 1 of the present invention. This method can be executed by a data verification device, which can be implemented in software and / or hardware and can be configured in a computer device, such as a server, personal computer, etc. The data verification method specifically includes the following steps:

[0043] Step 101: Obtain the SQL statement to be processed and convert the SQL statement into an abstract syntax tree.

[0044] Structured Query Language (SQL) is a database query and programming language used to access, query, update, and manage relational database systems. SQL statements are a language for manipulating databases.

[0045] An abstract syntax tree (AST) is a tree-like representation of the abstract syntactic structure of source code. Each node in the tree represents a structure in the source code.

[0046] Specifically, the source code of the SQL statement to be processed is obtained. Characters of the source code are read sequentially, and lexical and syntactic analysis is performed to obtain an abstract syntax tree (AST). For example, the parsing process of the SQL statement "insert into t1 select a fromt where b='b'" based on the AST is described in [link to AST parsing process]. Figure 1A .

[0047] Step 102: Traverse the abstract syntax tree to obtain tables, fields, conditions, and the dependencies between tables and fields; construct a directed acyclic graph to represent the dependencies between tables and fields across the entire chain based on the tables, fields, conditions, and the dependencies between tables and fields.

[0048] In one implementation, the Visitor pattern is used to traverse the abstract syntax tree to obtain tables, fields, conditions, and dependencies between tables and fields. The Visitor pattern is an object behavioral pattern that decouples data structures from the operations performed on those structures, allowing the set of operations to evolve relatively freely without affecting the system's data structure.

[0049] Then, based on the tables, fields, conditions, and the dependencies between tables and fields, a directed acyclic graph (DAG) is constructed to represent the dependencies between tables and fields throughout the entire process. A DAG is a directed graph without loops. In one application, a DAG might be constructed as follows: Figure 1B As shown.

[0050] Step 103: Traverse the directed acyclic graph to obtain the hierarchical relationship between the fields of the table and its downstream tables. Input the corresponding values ​​of the upstream fields and the corresponding values ​​of the downstream fields into the regression model for verification and obtain the verification results.

[0051] Specifically, the directed acyclic graph is traversed sequentially to obtain the hierarchical relationship between the fields of the table and its downstream tables. Then, the corresponding values ​​of the upstream and downstream fields are input into the regression model for verification to obtain the verification results.

[0052] The regression model is obtained by training a preset model, and the training data of the regression model is constructed based on the SQL statements used in the data warehouse construction process and the data warehouse data.

[0053] In one implementation, constructing the training data for the regression model may include:

[0054] Step A: Obtain the SQL statements used in the data warehouse construction process and convert the SQL statements into an abstract syntax tree.

[0055] Step B: Traverse the abstract syntax tree to obtain tables, fields, conditions, and the dependencies between tables and fields; construct a directed acyclic graph (DAG) to represent the dependencies between tables and fields across the entire chain based on the tables, fields, conditions, and the dependencies between tables and fields. Specifically, the visitor pattern can be used to traverse the abstract syntax tree.

[0056] Step C: Traverse the directed acyclic graph to obtain the hierarchical relationship and generation conditions of the fields of the table and its downstream tables; load the data warehouse data, locate the generation relationship between the data at the row level according to the hierarchical relationship and generation conditions of the fields of the table and its downstream tables, and construct training data.

[0057] Specifically, the directed acyclic graph is traversed sequentially to obtain the hierarchical relationships and generation conditions of fields between the table and its downstream tables. Data warehouse data is loaded, and the generation relationships between data at the row level are located based on the hierarchical relationships and generation conditions of fields between the table and its downstream tables, i.e., the upstream and downstream dependencies between data. Training data for the regression model is then constructed based on this.

[0058] The preset model can be a linear regression model, more specifically a univariate linear regression model. The preset fitted linear equation is: y = x * b + w, where the input variable x is the value of the upstream field, the output variable y is the value of the downstream field, and b and w are the coefficients of the univariate linear regression equation. Linear fitting is performed on the input training data, the residuals are calculated, outliers with large residuals are removed, and a new round of linear fitting is performed until all residuals are very small, at which point the iteration stops, and the regression model is obtained.

[0059] In this embodiment, the SQL statement to be processed is obtained and converted into an abstract syntax tree (AST). The AST is traversed to obtain tables, fields, conditions, and dependencies between tables and fields. A directed acyclic graph (DAG) representing the dependencies between tables and fields across the entire data chain is constructed based on the tables, fields, conditions, and dependencies. The DAG is traversed to obtain the hierarchical relationships between fields of a table and its downstream tables. The values ​​of upstream and downstream fields are input into a regression model for verification to obtain the verification results. The regression model is trained using a preset model, and the training data for the regression model is based on the SQL statements and data warehouse data used during the data warehouse construction process. This method enables automated data verification with high efficiency and timely response.

[0060] It should be noted that, for the sake of simplicity, the method embodiments are all described as a series of actions. However, those skilled in the art should understand that the embodiments of the present invention are not limited to the described order of actions, because according to the embodiments of the present invention, some steps can be performed in other orders or simultaneously. Furthermore, those skilled in the art should also understand that the embodiments described in the specification are preferred embodiments, and the actions involved are not necessarily essential to the embodiments of the present invention.

[0061] Example 2

[0062] Figure 2 This is a schematic diagram of a data verification device according to Embodiment 2 of the present invention. The data verification device may specifically include the following modules:

[0063] The processing module 201 is used to obtain the SQL statement to be processed and convert the SQL statement into an abstract syntax tree.

[0064] Graph construction module 202 is used to traverse the abstract syntax tree to obtain tables, fields, conditions, and the dependencies between tables and fields; and to construct a directed acyclic graph representing the dependencies between tables and fields across the entire chain based on the tables, fields, conditions, and the dependencies between tables and fields.

[0065] The verification module 203 is used to traverse the directed acyclic graph, obtain the hierarchical relationship between the fields of the table and its downstream tables, input the corresponding values ​​of the upstream fields and the corresponding values ​​of the downstream fields into the regression model for verification, and obtain the verification results.

[0066] The regression model is obtained by training a preset model, and the training data of the regression model is constructed based on the SQL statements used in the data warehouse construction process and the data warehouse data.

[0067] In one implementation, the preset model can be a linear regression model, more specifically a univariate linear regression model.

[0068] In one implementation, the graph construction module 202 uses the visitor pattern to traverse the abstract syntax tree to obtain tables, fields, conditions, and the dependencies between tables and fields.

[0069] In one embodiment, the data verification device further includes:

[0070] The model training module 204 is used to acquire the SQL statements used in the data warehouse construction process, convert the SQL statements into an abstract syntax tree; traverse the abstract syntax tree to acquire tables, fields, conditions, and the dependencies between tables and fields; construct a directed acyclic graph (DAG) to represent the dependencies between tables and fields across the entire chain based on the tables, fields, conditions, and the dependencies between tables and fields; traverse the DAG to acquire the hierarchical relationships and generation conditions of fields between tables and their downstream tables; load data warehouse data, locate the generation relationships between data at the row level based on the hierarchical relationships and generation conditions of fields between tables and their downstream tables, and construct training data; input the training data into a preset model to train the obtained regression model.

[0071] In one implementation, the model training module 204 uses the visitor pattern to traverse the abstract syntax tree.

[0072] The data verification device provided in the embodiments of the present invention can execute the data verification method provided in any embodiment of the present invention, and has the corresponding functional modules and beneficial effects of the method execution.

[0073] Example 3

[0074] Figure 3 This is a schematic diagram of the structure of a computer device provided in Embodiment 3 of the present invention. Figure 3 A block diagram of an exemplary computer device 12 suitable for implementing embodiments of the present invention is shown. Figure 3 The computer device 12 shown is merely an example and should not impose any limitation on the functionality and scope of use of the embodiments of the present invention.

[0075] like Figure 3 As shown, the computer device 12 is represented in the form of a general-purpose computing device. The components of the computer device 12 may include, but are not limited to: one or more processors or processing units 16, system memory 28, and a bus 18 connecting different system components (including system memory 28 and processing unit 16).

[0076] Bus 18 represents one or more of several bus architectures, including a memory bus or memory controller, a peripheral bus, a graphics acceleration port, a processor, or a local bus using any of the various bus architectures. For example, these architectures include, but are not limited to, the Industry Standard Architecture (ISA) bus, the Micro Channel Architecture (MAC) bus, the Enhanced ISA bus, the Video Electronics Standards Association (VESA) local bus, and the Peripheral Component Interconnect (PCI) bus.

[0077] Computer device 12 typically includes a variety of computer system readable media. These media can be any available media that can be accessed by computer device 12, including volatile and non-volatile media, removable and non-removable media.

[0078] System memory 28 may include computer system readable media in the form of volatile memory, such as random access memory (RAM) 30 and / or cache memory 32. Computer device 12 may further include other removable / non-removable, volatile / non-volatile computer system storage media. By way of example only, storage system 34 may be used to read and write non-removable, non-volatile magnetic media (…). Figure 3 Not shown; usually referred to as a "hard drive"). Although Figure 3 Not shown, a disk drive for reading and writing to a removable non-volatile disk (e.g., a "floppy disk") and an optical disk drive for reading and writing to a removable non-volatile optical disk (e.g., a CD-ROM, DVD-ROM, or other optical media) may be provided. In these cases, each drive may be connected to bus 18 via one or more data media interfaces. Memory 28 may include at least one program product having a set (e.g., at least one) of program modules configured to perform the functions of the embodiments of the present invention.

[0079] A program / utility 40 having a set (at least one) of program modules 42 may be stored, for example, in memory 28. Such program modules 42 include, but are not limited to, an operating system, one or more application programs, other program modules, and program data. Each or some combination of these examples may include an implementation of a network environment. Program modules 42 typically perform the functions and / or methods described in the embodiments of the present invention.

[0080] Computer device 12 can also communicate with one or more external devices 14 (e.g., keyboard, pointing device, display 24, etc.), and with one or more devices that enable a user to interact with computer device 12, and / or with any device that enables computer device 12 to communicate with one or more other computing devices (e.g., network card, modem, etc.). This communication can be performed via input / output (I / O) interface 22. Furthermore, computer device 12 can also communicate with one or more networks (e.g., local area network (LAN), wide area network (WAN), and / or public networks, such as the Internet) via network adapter 20. As shown, network adapter 20 communicates with other modules of computer device 12 via bus 18. It should be understood that, although not shown in the figures, other hardware and / or software modules can be used in conjunction with computer device 12, including but not limited to: microcode, device drivers, redundant processing units, external disk drive arrays, RAID systems, tape drives, and data backup storage systems.

[0081] The processing unit 16 executes various functional applications and data processing by running programs stored in the system memory 28, such as implementing the data verification method provided in the embodiments of the present invention.

[0082] Example 4

[0083] Embodiment 4 of the present invention also provides a computer-readable storage medium storing a computer program. When the computer program is executed by a processor, it implements each process of the above-described data verification method and achieves the same technical effect. To avoid repetition, it will not be described again here.

[0084] Computer-readable storage media may include, for example, electrical, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatuses, or devices, or any combination thereof. More specific examples (a non-exhaustive list) of computer-readable storage media include: electrical connections having one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fiber, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof. In this document, a computer-readable storage medium may be any tangible medium that contains or stores a program that can be used by or in connection with an instruction execution system, apparatus, or device.

[0085] Note that the above description is merely a preferred embodiment of the present invention and the technical principles employed. Those skilled in the art will understand that the present invention is not limited to the specific embodiments described herein, and various obvious changes, readjustments, and substitutions can be made without departing from the scope of protection of the present invention. Therefore, although the present invention has been described in detail through the above embodiments, the present invention is not limited to the above embodiments, and may include many other equivalent embodiments without departing from the concept of the present invention, the scope of which is determined by the scope of the appended claims.

Claims

1. A data verification method, characterized in that, The method includes: Obtain the SQL statement to be processed and convert the SQL statement into an abstract syntax tree; Traverse the abstract syntax tree to obtain tables, fields, conditions, and the dependencies between tables and fields; construct a directed acyclic graph to represent the dependencies between tables and fields across the entire chain based on the tables, fields, conditions, and the dependencies between tables and fields. Traverse the directed acyclic graph to obtain the hierarchical relationship between the fields of the table and its downstream tables. Input the corresponding values ​​of the upstream fields and the corresponding values ​​of the downstream fields into the regression model for verification and obtain the verification results. The regression model is obtained by training a preset model, and the training data of the regression model is constructed based on the SQL statements used in the data warehouse construction process and the data warehouse data. The construction of the training data for the regression model includes: Obtain the SQL statements used in the data warehouse construction process and convert the SQL statements into an abstract syntax tree; Traverse the abstract syntax tree to obtain tables, fields, conditions, and the dependencies between tables and fields; construct a directed acyclic graph to represent the dependencies between tables and fields across the entire chain based on the tables, fields, conditions, and the dependencies between tables and fields. Traverse the directed acyclic graph to obtain the hierarchical relationships and generation conditions of fields between the table and its downstream tables; load the data warehouse data, locate the generation relationships between data at the row level based on the hierarchical relationships and generation conditions of fields between the table and its downstream tables, and construct training data.

2. The method according to claim 1, characterized in that, The traversal of the abstract syntax tree to obtain tables, fields, conditions, and the dependencies between tables and fields includes: Use the visitor pattern to traverse the abstract syntax tree to obtain tables, fields, conditions, and the dependencies between tables and fields.

3. The method according to claim 1, characterized in that: The preset model is a linear regression model.

4. The method according to any one of claims 1 to 3, characterized in that, During the construction of the training data for the regression model, the visitor pattern is used to traverse the abstract syntax tree.

5. A data verification device, characterized in that, The device includes: The processing module is used to obtain the SQL statement to be processed and convert the SQL statement into an abstract syntax tree; The graph construction module is used to traverse the abstract syntax tree to obtain tables, fields, conditions, and the dependencies between tables and fields; and to construct a directed acyclic graph to represent the dependencies between tables and fields throughout the entire chain based on the tables, fields, conditions, and the dependencies between tables and fields. The verification module is used to traverse the directed acyclic graph, obtain the hierarchical relationship between the fields of the table and its downstream tables, input the corresponding values ​​of the upstream fields and the corresponding values ​​of the downstream fields into the regression model for verification, and obtain the verification results. The regression model is obtained by training a preset model, and the training data of the regression model is constructed based on the SQL statements used in the data warehouse construction process and the data warehouse data. The model training module is used to acquire the SQL statements used in the data warehouse construction process, convert the SQL statements into an abstract syntax tree; traverse the abstract syntax tree to obtain tables, fields, conditions, and the dependencies between tables and fields; construct a directed acyclic graph (DAG) to represent the dependencies between tables and fields across the entire chain based on the tables, fields, conditions, and the dependencies between tables and fields; traverse the DAG to obtain the hierarchical relationships and generation conditions of fields between tables and their downstream tables; load data from the data warehouse, locate the generation relationships between data at the row level based on the hierarchical relationships and generation conditions of fields between tables and their downstream tables, and construct training data; input the training data into a preset model to train the obtained regression model.

6. The apparatus according to claim 5, characterized in that: The preset model is a linear regression model.

7. A computer device, characterized in that, The computer device includes: One or more processors; Memory, used to store one or more programs; When the one or more programs are executed by the one or more processors, the one or more processors implement the data verification method as described in any one of claims 1-4.

8. A computer-readable storage medium, characterized in that: A computer program is stored on the computer-readable storage medium, which, when executed by a processor, implements the data verification method as described in any one of claims 1-4.

Images