Data quality verification method, device, storage medium and electronic equipment

Abstract

The present invention discloses a data quality verification method, device, storage medium, and electronic device. The method comprises: obtaining data to be verified; determining a partition name of the data to be verified based on a partition expression, wherein the partition expression is a function including a scheduling time; and generating an SQL statement required for verification based on a verification rule and the partition name to perform data quality verification. By implementing the present invention, the generation of partition names and scheduling are combined. That is, when the data quality verification method generates an SQL query statement based on a rule, it also obtains the scheduling period of the verification rule based on the partition expression, that is, obtains the scheduling time. In this way, the generation of SQL and the setting of the scheduling time can be completed through a single partition expression, eliminating the process of configuring each one through a script. Moreover, data quality verification can be performed immediately through the precise scheduling time.

Description

Technical Field

[0001] The present invention relates to the technical field of data management, and in particular to a data quality verification method, device and storage medium. Background Art

[0002] With the widespread adoption of big data technology across the internet industry, major companies are seeing an increasing variety of big data-related businesses. From sales to operations to analysts, more and more tasks rely on underlying data provided by the data department. From business dashboards to various reports, big data is increasingly becoming a management tool for management teams.

[0003] In this context of big data development, the data operations of major companies in the industry are growing in number and complexity. As the overall business scales, it faces a situation where control becomes increasingly uncontrollable. When the various data tables generated by data operations become a daily reliance for many business lines and management, if data output remains uncontrollable, it will have a certain degree of impact on the company's normal business development. At this time, the data department needs to consider two aspects when monitoring data generation: first, ensuring timely data output; second, ensuring the accuracy of the data produced. Therefore, verifying data quality is a pressing issue. Summary of the Invention

[0004] In view of this, an embodiment of the present invention provides a data quality verification method, device and storage medium to solve the technical problem in the prior art that when data output is uncontrollable, it will affect normal business development.

[0005] The technical solutions proposed by the present invention are as follows:

[0006] A first aspect of an embodiment of the present invention provides a data quality verification method, comprising: obtaining data to be verified; determining a partition name of the data to be verified based on a partition expression, wherein the partition expression is a function containing a scheduling time; and generating an SQL statement required for verification based on a verification rule and the partition name to perform data quality verification.

[0007] According to the first aspect of the embodiments of the present invention, in the first implementation manner of the first aspect, data quality verification is performed based on the verification rules and the partition name to generate the SQL statement required for verification, including: obtaining a rule instance written in the database, the rule instance being generated and written into the database the previous day; generating an SQL statement based on the verification rules in the rule instance and the partition name; and performing quality verification on the SQL statement.

[0008] According to the first implementation manner of the first aspect of the embodiment of the present invention, in the second implementation manner of the first aspect, performing quality verification on the SQL statement further includes: when the verification fails, issuing a verification failure reminder according to a preset reminder method.

[0009] According to the first aspect of the embodiments of the present invention, in the third implementation of the first aspect, the partition name of the data to be verified is determined according to the partition expression, including: determining the production time according to the production mode and the production cycle, the production mode including minute level, hour level, day level and month level; determining the partition name of the data to be verified according to the production time and the partition expression.

[0010] According to the first implementation manner of the first aspect of an embodiment of the present invention, in the fourth implementation manner of the first aspect, an SQL statement is generated according to the verification rules in the rule instance and the partition name, including: splicing a select part according to the verification rules in the rule instance; determining a where part according to the partition name to be verified; and splicing an SQL statement according to the select part and the where part.

[0011] According to the first aspect of the embodiments of the present invention, in a fifth implementation of the first aspect, the data quality verification method is applied to a Hive cluster, and the data to be verified is stored in a HiveMetaStore database.

[0012] According to the first aspect of the embodiments of the present invention, in the sixth implementation of the first aspect, the verification rules include: table-level rules and field-level rules; the table-level rules include the number of table rows, timeliness, table size, difference compared with the previous period, and volatility compared with the previous period; the field-level rules include the number of unique values ​​​​in the field, the maximum value of the field, the minimum value of the field, the number of null values ​​​​in the field, the ratio of null values ​​​​in the field, the summary value of the field, and the number of repeated values ​​​​in the field.

[0013] A second aspect of an embodiment of the present invention provides a data quality verification device, including: a data acquisition module for acquiring data to be verified; a partition name determination module for determining the partition name of the data to be verified based on a partition expression, wherein the partition expression is a function including a scheduling time; and a verification module for generating an SQL statement required for verification based on verification rules and the partition name to perform data quality verification.

[0014] A third aspect of an embodiment of the present invention provides a computer-readable storage medium, wherein the computer-readable storage medium stores computer instructions, and the computer instructions are used to enable the computer to execute the data quality verification method as described in the first aspect of the embodiment of the present invention and any one of the first aspects.

[0015] A fourth aspect of an embodiment of the present invention provides an electronic device, comprising: a memory and a processor, the memory and the processor being communicatively connected to each other, the memory storing computer instructions, and the processor executing the computer instructions to execute the data quality verification method as described in the first aspect of the embodiment of the present invention and any one of the first aspects.

[0016] The technical solution provided by the present invention has the following effects:

[0017] The data quality verification method, device and storage medium provided by the embodiments of the present invention combine the generation and scheduling of partition names by designing the expression method of the partition expression. That is, when the data quality verification method generates an SQL query statement through a rule, it also obtains the scheduling period of the verification rule according to the partition expression, that is, obtains the scheduling time. In this way, the generation of SQL and the setting of the scheduling time can be completed through a partition expression, eliminating the process of configuring one by one through a script, and data quality verification can be performed in the first time through accurate scheduling time.

[0018] The data quality verification method, device, storage medium and electronic device provided in the embodiments of the present invention provide a simple and fast monitoring configuration, which facilitates business personnel to monitor the data quality of the output of specific data tables; provide flexible partitioning expressions that can meet different types of partitioning methods and can combine partitioning with output cycles; for tables with different output cycles, verification can be automatically performed to ensure that the output data can be verified in the first time and errors are thrown.

[0019] The data quality verification method, device, storage medium, and electronic device provided in embodiments of the present invention are applied to Hive clusters and offer features such as custom partitioning, table-level and field-level data quality verification, and failure notifications. When data output problems arise, the relevant responsible person can be promptly contacted to ensure that data issues are resolved promptly. This reduces data output maintenance costs and improves data output quality. BRIEF DESCRIPTION OF THE DRAWINGS

[0020] In order to more clearly illustrate the specific embodiments of the present invention or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the specific embodiments or the description of the prior art. Obviously, the drawings described below are some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0021] Figure 1 is a flow chart of a data quality verification method according to an embodiment of the present invention;

[0022] Figure 2is a structural block diagram of a data quality platform according to an embodiment of the present invention;

[0023] Figure 3 is a structural block diagram of a data quality verification device according to an embodiment of the present invention;

[0024] Figure 4 is a schematic structural diagram of a computer-readable storage medium provided according to an embodiment of the present invention;

[0025] Figure 5 is a structural diagram of an electronic device provided according to an embodiment of the present invention. DETAILED DESCRIPTION

[0026] To make the purpose, technical solutions, and advantages of the embodiments of the present invention more clear, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the accompanying drawings in the embodiments of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without making creative efforts shall fall within the scope of protection of the present invention.

[0027] Currently, data quality verification typically involves using scripts to verify data generation. However, this approach presents difficulties with customized development, expansion, and maintenance. The difficulty with customized development stems primarily from the fact that the data tables produced by each business line have different lifecycles and the content of the business data varies, necessitating customized development to meet specific needs. Since general business personnel lack the technical skills to write scripts, the development of these monitoring scripts requires the R&D resources of the data department. This consumes a significant amount of data developers' time for debugging, verification, and maintenance, resulting in extremely high development costs. The difficulties with expansion and maintenance stem from the fact that business changes may necessitate redevelopment, and once the number of scripts reaches a certain level, their maintenance and management present numerous challenges, making subsequent work transfers extremely difficult.

[0028] Therefore, an embodiment of the present invention provides a data quality verification method to solve three problems in data warehouses: difficulty in data monitoring, difficulty in ensuring the timeliness of data output, and difficulty in discovering data accuracy issues.

[0029] At the same time, the data quality verification method provided by the embodiment of the present invention can rely on Figure 2The data quality platform shown in the figure is implemented by integrating the metadata of HiveMetaStore, the business data of MySQL, the business data of AirflowMetadata, and the rule data and verification data in RocketMQ. It provides various functions in the service layer and provides information push functions for front-end web pages, WeChat, telephone and other communication tools.

[0030] According to an embodiment of the present invention, an embodiment of a data quality verification method is provided. It should be noted that the steps shown in the flowchart of the accompanying drawings can be executed in a computer system such as a set of computer-executable instructions, and although a logical order is shown in the flowchart, in some cases, the steps shown or described can be executed in an order different from that shown here.

[0031] In this embodiment, a data quality verification method is provided, which can be used in electronic devices such as computers, mobile phones, tablet computers, etc. Figure 1 is a flow chart of a data quality verification method according to an embodiment of the present invention. Figure 1 As shown, the process includes the following steps:

[0032] Step S101: Obtain data to be verified.

[0033] Specifically, the data to be verified can be produced by the electronic device or obtained from other places. For example, in an actual production scenario, the source of the business database is the original data. Figure 3 The data quality platform shown can determine the data to be tested by reading the metadata of HiveMetaStore.

[0034] The data to be verified is typically a table. When the relevant tables are stored in the HiveMetaStore, they also contain information such as the table's field information and partition creation time. To facilitate data quality verification, the corresponding field information and partition creation time can also be obtained when obtaining the data to be verified.

[0035] Step S102: Determine the partition name of the data to be verified based on the partition expression, where the partition expression is a function that includes the scheduling time. Specifically, in the current partition table production process, the partition name is generally related to the production time, but the production cycles of different tables are not the same, and different developers may use different time formats. Therefore, after determining the partition production time, the partition name of the data to be verified can be determined in combination with the partition expression, thereby achieving flexible configuration of the partition name and obtaining a partition name in a unified format. In addition, the partition expression includes the scheduling time, so that the partition name obtained by the partition expression can be used to derive the time for scheduling the rule, so that data quality verification can be performed at the first time.

[0036] Step S103: Generate the SQL statement required for verification based on the verification rules and the partition name to perform data quality verification. When generating the SQL statement, the select portion is obtained by splicing the verification rules in the rule instance; the where portion is determined based on the partition name to be verified; and the SQL statement is spliced ​​together by splicing the select portion and the where portion.

[0037] The data quality verification method provided by the embodiment of the present invention combines the generation and scheduling of partition names by designing the expression method of the partition expression. That is, when the data quality verification method generates an SQL query statement through a rule, it also obtains the scheduling period of the verification rule according to the partition expression, that is, obtains the scheduling time. In this way, the generation of SQL and the setting of the scheduling time can be completed through a partition expression, eliminating the process of configuring one by one through a script, and the data quality verification can be performed in the first time through the precise scheduling time.

[0038] As an optional implementation of an embodiment of the present invention, determining the partition name of the data to be verified based on the partition expression includes: determining the production time based on the production mode and the production cycle, the production mode including minute level, hour level, day level and month level; determining the partition name of the data to be verified based on the production time and the partition expression.

[0039] Specifically, for the data to be verified, the data table it contains may be generated once every minute, once every hour, once every day, or once every month. Therefore, this partition expression supports minute-level, hour-level, daily-level, and monthly-level production modes. Regardless of the production mode, the corresponding partition name can be obtained through this partition expression. At the same time, the specific time corresponding to different modes (i.e., production cycle) can be accurate to the second level. For example, for a minute-level production mode, the corresponding production cycle can be 1-59. Therefore, after determining the production time, that is, determining the production mode and production cycle, the partition name of the data to be verified can be determined through the partition expression function.

[0040] As an optional implementation of an embodiment of the present invention, data quality verification is performed by generating SQL statements required for verification based on the verification rules and the partition name, including: obtaining a rule instance written into the database, where the rule instance was generated and written into the database the previous day; generating an SQL statement based on the verification rules in the rule instance and the partition name; and performing quality verification on the SQL statement. If the verification fails, a verification failure reminder is issued according to a preset reminder method.

[0041] Specifically, the cycles of different data quality verification rules may be completely different. To ensure the punctuality of verification, if a timed trigger method is used, it is very likely that verification will be delayed and may cause congestion when there are many verification rules. Therefore, active monitoring can be used for verification. On the first day, a rule instance that needs to be verified on the second day is generated. This rule instance can be saved in RocketMQ on the data quality platform. Then, when performing data quality verification every day, the rule instance generated and saved on the previous day is obtained, the rules that need to be verified on that day are determined, and they are sent to RocketMQ. Then, through the RocketMQ listener, when it detects that the table partition has been generated, verification is performed by sending SQL to Hive.

[0042] In one embodiment, the verification rules include: table-level rules and field-level rules; the table-level rules include the number of table rows, timeliness, table size, difference compared to the previous period, and volatility compared to the previous period; the field-level rules include the number of unique field values, the maximum field value, the minimum field value, the number of field null values, the ratio of field null values, the field summary value, and the number of repeated field values.

[0043] In one embodiment, the data quality verification method is implemented using the following process: the different types of checks required for fields and tables and the different generation cycles of Hive partitions are abstracted by splicing SQL; every day according to different production times, the rule instances generated the previous day are obtained, the rules that need to be checked are determined and sent to the message queue, and the HiveMetaStore service is used to check whether the inspection conditions are met, that is, the table partitions have been generated; when the inspection conditions are met, verification is performed by sending SQL to Hive; if the inspection is successful, the process ends; if the inspection fails, a verification failure reminder is issued according to the preset reminder method.

[0044] The data quality verification method provided by the embodiment of the present invention provides a simple and fast monitoring configuration, which is convenient for business personnel to monitor the data quality of the output of a specific data table; it provides a flexible partitioning expression that can meet different types of partitioning methods and can combine partitioning with the output cycle; it can automatically perform verification on tables with different output cycles to ensure that the output data can be verified in the first place and errors are thrown.

[0045] The data quality verification method provided in this embodiment, applied to a Hive cluster, offers features such as custom partitioning, table-level and field-level data quality verification, and phone and enterprise WeChat alerts. When data output issues arise, the relevant person in charge can be contacted promptly to ensure timely resolution. This reduces data output maintenance costs and improves data quality.

[0046] like Figure 2 As shown, the data quality platform can be divided into three layers, namely data layer, service layer and presentation layer.

[0047] Specifically, the data layer includes Hive MetaStore, Airflow, MySQL, and RocketMQ. Hive MetaStore stores metadata; Airflow stores scheduling data; and MySQL stores the data quality platform's own business data. RocketMQ saves instantiated rules and validation records for continuous polling and validation.

[0048] The service layer reflects some of the functions that can be achieved by the data quality platform, including: table-level verification function, field verification function, flexible partitioning function, alarm function, rule attention function, rule replication function, rule verification, quality monitoring dashboard and other functions.

[0049] The presentation layer includes Web, enterprise WeChat and telephone access. It can be used to contact people through enterprise WeChat and telephone when there are problems with the data, ensuring that people involved in data output can be informed immediately when problems occur.

[0050] The embodiment of the present invention also provides a data quality verification device, such as Figure 3 As shown, the device includes:

[0051] The data acquisition module is used to obtain the data to be verified; the specific content can be found in the corresponding part of the above method embodiment, which will not be repeated here.

[0052] The partition name determination module is used to determine the partition name of the data to be verified based on the partition expression, where the partition expression is a function including the scheduling time. For details, please refer to the corresponding part of the above method embodiment and will not be repeated here.

[0053] The verification module is used to generate the SQL statement required for verification according to the verification rules and the partition name to perform data quality verification. For details, please refer to the corresponding part of the above method embodiment and will not be repeated here.

[0054] The data quality verification device provided by the embodiment of the present invention combines the generation and scheduling of partition names by designing the expression method of the partition expression. That is to say, when the data quality verification method generates an SQL query statement through a rule, it also obtains the scheduling period of the verification rule according to the partition expression, that is, obtains the scheduling time. In this way, the generation of SQL and the setting of the scheduling time can be completed through a partition expression, eliminating the process of configuring one by one through a script, and the data quality verification can be performed in the first time through the precise scheduling time.

[0055] For a detailed description of the functions of the data quality verification device provided in the embodiment of the present invention, please refer to the description of the data quality verification method in the above embodiment.

[0056] The embodiment of the present invention also provides a storage medium, such as Figure 4 As shown, a computer program 601 is stored thereon, and when the instructions are executed by the processor, the steps of the data quality verification method in the above embodiment are implemented. The storage medium also stores audio and video stream data, feature frame data, interaction request signaling, encrypted data, and preset data size. Among them, the storage medium can be a magnetic disk, an optical disk, a read-only memory (ROM), a random access memory (RAM), a flash memory (Flash Memory), a hard disk drive (HDD) or a solid-state drive (SSD); the storage medium can also include a combination of the above types of memory.

[0057] Those skilled in the art will appreciate that all or part of the processes in the above-mentioned embodiments can be implemented by instructing the relevant hardware through a computer program. The program can be stored in a computer-readable storage medium. When the program is executed, it can include the processes of the embodiments of the above-mentioned methods. The storage medium can be a magnetic disk, an optical disk, a read-only memory (ROM), a random access memory (RAM), a flash memory, a hard disk drive (HDD), or a solid-state drive (SSD). The storage medium can also include a combination of the above-mentioned types of memory.

[0058] The embodiment of the present invention further provides an electronic device, such as Figure 5 As shown, the electronic device may include a processor 51 and a memory 52, wherein the processor 51 and the memory 52 may be connected via a bus or other means. Figure 5 The bus connection is taken as an example.

[0059] The processor 51 may be a central processing unit (CPU). The processor 51 may also be other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), field-programmable gate arrays (FPGA), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, or a combination of the above chips.

[0060] Memory 52, as a non-transitory computer-readable storage medium, can be used to store non-transitory software programs, non-transitory computer executable programs, and modules, such as the corresponding program instructions / modules in the embodiments of the present invention. Processor 51 executes the non-transitory software programs, instructions, and modules stored in memory 52 to perform various processor functions and data processing, thereby implementing the data quality verification method in the above-mentioned method embodiment.

[0061] The memory 52 may include a program storage area and a data storage area, wherein the program storage area may store applications required for operating the device and at least one function; the data storage area may store data created by the processor 51, etc. In addition, the memory 52 may include a high-speed random access memory, and may also include a non-volatile memory, such as at least one disk storage device, a flash memory device, or other non-volatile solid-state storage device. In some embodiments, the memory 52 may optionally include a memory remotely located relative to the processor 51, and these remote memories may be connected to the processor 51 via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0062] The one or more modules are stored in the memory 52 and when executed by the processor 51, perform the following steps: Figure 1 -2 is a data quality verification method in the embodiment shown in FIG.

[0063] For details of the above electronic equipment, please refer to Figures 1 to 2 The corresponding descriptions and effects in the embodiments shown can be understood and will not be repeated here.

[0064] Although the embodiments of the present invention have been described with reference to the accompanying drawings, those skilled in the art may make various modifications and variations without departing from the spirit and scope of the present invention. Such modifications and variations are all within the scope defined by the appended claims.

Claims

1. A data quality verification method, characterized in that: include: Get the data to be verified; Determine the partition name of the data to be verified according to the partition expression, where the partition expression is a function including the scheduling time; Generate the SQL statement required for verification according to the verification rules and the partition name to perform data quality verification; Determine the partition name of the data to be verified based on the partition expression, including: Determine the production time according to the production mode and production cycle, wherein the production mode includes minute level, hour level, day level and month level; Determine the partition name of the data to be verified based on the production time and partition expression; In the current partition table production process, the partition name is related to the production time. Different tables have different production cycles, and different developers use different time formats. After determining the partition production time, the partition name of the data to be tested is determined by combining the partition expression to obtain a unified partition name format. The partition expression includes the scheduling time. The partition name obtained by the partition expression determines the time to schedule the rule, thereby performing data quality verification in the first place. By designing the expression method of partitioning expression, the generation and scheduling of partition names are combined. When generating SQL query statements, the scheduling period of the verification rule, that is, the scheduling time, is obtained according to the partitioning expression. The generation of SQL and the setting of scheduling time are completed through a partitioning expression, eliminating the process of configuring one by one through scripts.

2. The data quality verification method according to claim 1, characterized in that: Generate the SQL statements required for verification based on the verification rules and the partition name to perform data quality verification, including: Obtaining a rule instance written into the database, where the rule instance was generated and written into the database the previous day; Generate an SQL statement according to the verification rule in the rule instance and the partition name; Perform quality verification on the SQL statement.

3. The data quality verification method according to claim 2, characterized in that: Performing quality check on the SQL statement also includes: When the verification fails, a verification failure reminder is issued according to the preset reminder method.

4. The data quality verification method according to claim 2, characterized in that: Generate an SQL statement according to the validation rule in the rule instance and the partition name, including: The select part is obtained by splicing the verification rules in the rule instance; Determine the where part based on the partition name to be verified; The SQL statement is obtained by splicing the select part and the where part.

5. The data quality verification method according to claim 1, characterized in that: The data quality verification method is applied to the Hive cluster, and the data to be verified is stored in the Hive Meta Store database.

6. The data quality verification method according to claim 1, characterized in that: The verification rules include: table-level rules and field-level rules; The table-level rules include the number of table rows, timeliness, table size, difference compared to the previous period, and volatility compared to the previous period; The field-level rules include the number of unique field values, the maximum field value, the minimum field value, the number of field null values, the ratio of field null values, the field summary value, and the number of field repeated values.

7. A data quality verification device, characterized in that: include: A data acquisition module, used to acquire data to be verified; A partition name determination module is used to determine the partition name of the data to be verified according to a partition expression, where the partition expression is a function including a scheduling time; A verification module is used to generate SQL statements required for verification according to the verification rules and the partition name to perform data quality verification; Determine the partition name of the data to be verified based on the partition expression, including: Determine the production time according to the production mode and production cycle, wherein the production mode includes minute level, hour level, day level and month level; Determine the partition name of the data to be verified based on the production time and partition expression; In the current partition table production process, the partition name is related to the production time. Different tables have different production cycles, and different developers use different time formats. After determining the partition production time, the partition name of the data to be tested is determined by combining the partition expression to obtain a unified partition name format. The partition expression includes the scheduling time. The partition name obtained by the partition expression determines the time to schedule the rule, thereby performing data quality verification in the first place. By designing the expression method of partitioning expression, the generation and scheduling of partition names are combined. When generating SQL query statements, the scheduling period of the verification rule, that is, the scheduling time, is obtained according to the partitioning expression. The generation of SQL and the setting of scheduling time are completed through a partitioning expression, eliminating the process of configuring one by one through scripts.

8. A computer-readable storage medium, characterized in that The computer-readable storage medium stores computer instructions, and the computer instructions are used to enable the computer to execute the data quality verification method according to any one of claims 1 to 6.

9. An electronic device, characterized in that: include: A memory and a processor, wherein the memory and the processor are communicatively connected to each other, the memory stores computer instructions, and the processor executes the data quality verification method according to any one of claims 1 to 6 by executing the computer instructions.

Images