Distributed data quality checking method and device

Abstract

The invention provides a distributed data quality checking method and device. The distributed data quality checking method comprises the following steps: constructing a generation template of a checking SQL script; checking parameters are generated according to the target checking requirements; the check parameters comprise common check parameters, check parameters configured by a self-defined template and check parameters corresponding to a self-defined compiling SQL (Structured Query Language); generating a checking SQL script and a configuration file matched with the checking parameters through the generation template and the checking parameters of the checking SQL script; the check tool scripts are deployed to a plurality of nodes of the distributed data system, check tools of all the nodes conduct check according to the check SQL scripts and the configuration files, and check results are obtained; according to the method, the SQL scripts and configuration files required by checking can be automatically generated by constructing the checking SQL script generation template to adapt to multiple types of checking parameters, so that the manual writing cost is reduced; the efficiency and flexibility of data quality checking are improved, and technical support is provided for data quality management and control.

Description

Technical Field

[0001] This invention belongs to the field of data verification technology, and in particular relates to a distributed data quality verification method and apparatus. Background Technology

[0002] In today's data management environment, data quality checks are a crucial step in ensuring data accuracy and consistency. However, traditional data quality checks often rely on manually written and executed Structured Query Language (SQL) scripts, which are not only time-consuming and labor-intensive but also prone to errors. Furthermore, with the continuous growth of data volume and the distributed deployment of systems, the complexity and challenges of data quality checks are increasing.

[0003] Therefore, a new data verification method is urgently needed to overcome the problems existing in the current technology. Summary of the Invention

[0004] This invention provides a distributed data quality inspection method. By constructing an inspection SQL script generation template to adapt to multiple types of inspection parameters, it can automatically generate the required SQL scripts and configuration files for inspection, reducing manual writing costs; improving the efficiency and flexibility of data quality inspection, and providing technical support for data quality control. This distributed data quality inspection method includes:

[0005] Build a template for generating the check SQL script;

[0006] The inspection parameters are generated based on the target inspection requirements; the inspection parameters include commonly used inspection parameters, inspection parameters configured by custom templates, and inspection parameters corresponding to custom SQL statements.

[0007] By checking the SQL script generation template and check parameters, a check SQL script and configuration file matching the check parameters are generated.

[0008] The verification tool script is deployed to multiple nodes of the distributed data system. The verification tool on each node performs verification based on the verification SQL script and configuration file, and obtains the verification result.

[0009] This invention provides a distributed data quality inspection device that automatically generates the required SQL scripts and configuration files by constructing an inspection SQL script generation template that adapts to multiple types of inspection parameters, reducing manual writing costs. This improves the efficiency and flexibility of data quality inspection and provides technical support for data quality control. The distributed data quality inspection device includes:

[0010] The template generation module is used to build templates for generating check SQL scripts;

[0011] The inspection parameter generation module is used to generate inspection parameters according to the target inspection requirements; the inspection parameters include commonly used inspection parameters, inspection parameters configured by custom templates, and inspection parameters corresponding to custom SQL statements;

[0012] The check script determination module is used to generate a check SQL script and configuration file that match the check parameters based on the check SQL script generation template and check parameters.

[0013] The verification module is used to deploy verification tool scripts to multiple nodes of the distributed data system. The verification tools on each node perform verification based on the verification SQL script and configuration file to obtain the verification results.

[0014] This invention also provides a computer device, including a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, it implements the above-described distributed data quality check method.

[0015] This invention also provides a computer-readable storage medium storing a computer program that, when executed by a processor, implements the above-described distributed data quality check method.

[0016] This invention also provides a computer program product, which includes a computer program that, when executed by a processor, implements the above-described distributed data quality inspection method.

[0017] In this embodiment of the invention, a template for generating verification SQL scripts is constructed; verification parameters are generated according to the target verification requirements; the verification parameters include commonly used verification parameters, verification parameters configured by custom templates, and verification parameters corresponding to custom-written SQL; through the template for generating verification SQL scripts and the verification parameters, verification SQL scripts and configuration files matching the verification parameters are generated; the verification tool scripts are deployed to multiple nodes of the distributed data system, and the verification tools on each node perform verifications according to the verification SQL scripts and configuration files to obtain verification results; this embodiment of the invention, by constructing a template for generating verification SQL scripts to adapt to multiple types of verification parameters, can automatically generate the SQL scripts and configuration files required for verification, reducing manual writing costs; it improves the efficiency and flexibility of data quality verification, and provides technical support for data quality control. Attached Figure Description

[0018] 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 these drawings without creative effort. In the drawings:

[0019] Figure 1 This is a flowchart of the distributed data quality check method in an embodiment of the present invention;

[0020] Figure 2 This is a specific example diagram of constructing a template for generating the check SQL script in an embodiment of the present invention;

[0021] Figure 3 This is a specific example diagram illustrating the generation of check parameters in an embodiment of the present invention;

[0022] Figure 4 This is a specific example diagram illustrating the generation of an SQL script and configuration file that matches the verification parameters in an embodiment of the present invention;

[0023] Figure 5 This is a specific example diagram illustrating the verification results obtained in an embodiment of the present invention;

[0024] Figure 6 This is a structural example diagram of the distributed data quality inspection device in an embodiment of the present invention;

[0025] Figure 7 This is a specific example diagram of the structure of the distributed data quality inspection device in an embodiment of the present invention;

[0026] Figure 8 This is a structural diagram of a computer device in an embodiment of the present invention. Detailed Implementation

[0027] To make the objectives, technical solutions, and advantages of the embodiments of the present invention clearer, the embodiments of the present invention will be further described in detail below with reference to the accompanying drawings. Here, the illustrative embodiments of the present invention and their descriptions are used to explain the present invention, but are not intended to limit the present invention.

[0028] The acquisition, transmission, storage, use, and processing of data in the technical solution of this invention all comply with relevant regulations.

[0029] It should be noted that in the embodiments of the present invention, certain software, components, models and other existing solutions in the industry may be mentioned. These should be regarded as exemplary and are only intended to illustrate the feasibility of implementing the technical solution of the present invention. However, they do not mean that the applicant has used or necessarily used the solution.

[0030] As mentioned earlier, in the existing technology, data quality inspection relies on manually written SQL scripts, which is not only labor-intensive and inefficient, but also prone to inaccurate inspection results due to human error. Existing inspection tools only support preset rules, and custom rules require complex configuration and programming, which is not flexible enough. Moreover, in distributed systems, the deployment of tools is affected by heterogeneity and complexity, making it difficult to manage and execute the inspection process in a unified manner.

[0031] To address this issue, the inventors discovered that batch generation of SQL scripts for verification can be achieved using Excel and macro programming. Considering that Practical Extraction and Report Language (Perl) scripts are small in size and cross-platform, they can meet the requirements of lightweight distributed deployment. At the same time, by establishing an index structure of business primary keys + rule numbers, rectification data can be automatically identified. Combined with database table unloading and network file transfer tools, distributed verification result aggregation can be achieved. Thus, a complete technical solution covering rule configuration, distributed deployment, rectification tagging, and result aggregation was designed, solving the core pain points of existing technologies.

[0032] Figure 1 This is a flowchart of the distributed data quality check method in an embodiment of the present invention, such as... Figure 1 As shown, this distributed data quality check method includes:

[0033] Step 101: Construct a template for generating the check SQL script;

[0034] Step 102: Generate inspection parameters according to the target inspection requirements; the inspection parameters include commonly used inspection parameters, inspection parameters configured by custom templates, and inspection parameters corresponding to custom SQL statements;

[0035] Step 103: Generate an audit SQL script and configuration file that match the audit parameters by using the audit SQL script generation template and audit parameters;

[0036] Step 104: Deploy the verification tool script to multiple nodes of the distributed data system. The verification tool on each node performs verification based on the verification SQL script and configuration file to obtain the verification results.

[0037] Depend on Figure 1As shown in the process, in this embodiment of the invention, firstly, a template for generating the verification SQL script is constructed in step 101. Then, in step 102, commonly used verification parameters, verification parameters configured in the custom template, and verification parameters corresponding to the custom SQL are determined according to the target verification requirements. Subsequently, in step 103, a matching verification SQL script and configuration file are automatically generated based on the template and verification parameters. Finally, in step 104, the verification tool script is deployed to multiple nodes of the distributed data system. Each node executes the verification according to the verification SQL script and configuration file and obtains the verification result.

[0038] Compared with existing technologies that involve manually writing a large number of check SQL scripts, check tools that only support preset rules, and difficulties in distributed deployment, this approach achieves automatic batch generation of scripts by constructing multiple types of check SQL generation templates and enables lightweight, cross-platform distributed deployment of check tools based on Perl scripts. This improves the efficiency of check SQL script generation, allows check tools to flexibly adapt to diverse needs, and ensures unified and efficient execution of check tasks in distributed systems. As a result, it reduces the labor costs and complexity of data quality checks, ensures the accuracy of check results, and provides convenient and reliable technical support for data quality control in distributed data systems.

[0039] Figure 2 This is a specific example diagram illustrating the construction of a template for generating an inspection SQL script in an embodiment of the present invention, as shown below. Figure 2 As shown, the template for generating the check SQL script can include:

[0040] Step 201: Construct a basic framework for generating check SQL scripts based on macro programming; the basic framework includes check SQL statements for total data statistics, error data statistics, and error detail storage;

[0041] Step 202: Configure the configuration parameters in the basic framework of the SQL script generation template to replace the name of the table to be checked, the name of the field to be checked, the check parameters, and the data check range, and obtain the SQL script generation template.

[0042] In a specific embodiment, taking the data quality inspection requirements of customer information-related data tables and transaction-related data tables in a distributed data system as the object, the following steps are performed:

[0043] A basic framework for generating templates based on macro programming:

[0044] Using Excel macro programming technology, a basic framework for generating SQL script templates for checks is built. This framework includes template structures for four core types of check SQL statements, corresponding to:

[0045] The functions of total data statistics, error data statistics, error detail table creation, and error detail collection can meet the basic needs of data volume statistics, error data quantification, and detail storage during the inspection process.

[0046] Among them, the data total statistics statement template is used to count the total amount of data in a specified data table within a specific range; the error data statistics statement template is used to count the amount of data that does not conform to the check rules; the error detail table creation statement template is used to create a table specifically for storing error details when checking for the first time or when the table needs to be rebuilt; and the error detail collection statement template is used to store the specific information of the checked error data into the error detail table.

[0047] Meanwhile, the framework uses macro programming to automate the calling logic of these four types of statement templates, ensuring that when a user triggers a command, the framework can automatically match and call the corresponding statement template structure.

[0048] Configure alternative configuration parameters:

[0049] Within the basic framework established in step 201, four types of replaceable core configuration parameters are configured, which are used for:

[0050] Batch replace the name of the data table being checked, the name of the data field being checked, the specific parameters required during the check process, and the range of data to be checked.

[0051] Among them, the parameters corresponding to the name of the table to be checked can be adapted to the checking requirements of different data tables and can be replaced according to the actual table to be checked; the parameters corresponding to the name of the field to be checked can be flexibly adjusted according to the specific fields to be checked in each table; the parameters corresponding to the check parameters can be filled in or left blank according to different check rules, such as not null, value range, primary key duplication, etc.; the parameters corresponding to the data check range can limit the specific conditions such as the time interval and regional range of the check data.

[0052] Subsequently, macro programming is used to establish the association between configuration parameters and SQL statement templates within the basic framework, and to set up automatic parameter replacement logic. For example, after the user fills in various parameters in a specified location in Excel, the macro can automatically identify the parameter content and replace the corresponding variables in the SQL statement template, ultimately forming a template for generating the check SQL script, thus realizing the automatic generation of SQL statements adapted to the check requirements based on parameter input.

[0053] In step 102, inspection parameters are generated according to the target inspection requirements; the inspection parameters include commonly used inspection parameters, inspection parameters configured by custom templates, and inspection parameters corresponding to custom SQL statements.

[0054] Figure 3 This is a specific example diagram illustrating the generation of check parameters in an embodiment of the present invention, such as... Figure 3As shown, the inspection parameters generated based on the target inspection requirements may include:

[0055] Step 301: If the target verification requirement is routine data quality inspection, select commonly used verification parameters;

[0056] Step 302: If the target inspection requirement is a specified business logic inspection, select the inspection parameters configured in the custom template;

[0057] Step 303: If the target inspection requirement is a specified complex scenario inspection, select the inspection parameters corresponding to the custom-written SQL.

[0058] In a specific embodiment, the following steps are performed:

[0059] Commonly used check parameters are selected for routine data quality checks:

[0060] When the target verification requirement is a routine data quality check, such as when it is necessary to combine specific business logic to verify only the basic validity of the data, commonly used verification parameters should be selected.

[0061] For example, performing a NOT NULL check on a customer information data table includes verifying whether key identifier fields and customer name fields have null values, checking for duplicate primary keys (including verifying whether the customer unique identifier field has duplicate records), and checking the value range (including verifying whether the customer age field is within a reasonable range and whether the contact information field format conforms to the standard). Commonly used check parameters such as NOT NULL check field name, primary key field name, upper and lower limits of value range, and format validation rules are selected to directly match the parameter requirements of the built-in check rule template.

[0062] Specify the check parameters configured in the custom template for business logic checks:

[0063] When the target verification requirement is a verification of a specific business logic, that is, it needs to be combined with the specific business rules of the enterprise, and the built-in template cannot directly meet the requirements, the verification parameters configured in the custom template are selected.

[0064] For example, when performing a check on the relationship between approval status and approval time for business approval data tables, it is required that the approval time must be filled in for the approval status, while the approval time can be left blank for the failure status. First, configure a dedicated check rule template based on the custom template function, and then select the check parameters configured in the custom template, such as the business status field name, the associated time field name, the logical parameters of the relationship between status and time, and the business scenario identifier, to ensure that the parameters correspond one-to-one with the variables in the custom template and meet the check requirements of specific business logic.

[0065] For complex scenario checks, select the check parameters corresponding to the custom-written SQL:

[0066] When the target verification requirement is a complex scenario verification, such as involving multiple table joins and multi-level logical judgments, and the template generation method is difficult to cover, the verification parameters corresponding to the custom-written SQL should be selected.

[0067] For example, cross-table integrity checks can be performed on transaction log data tables and customer information data tables to verify whether the customer identifier in the transaction record has a corresponding record in the customer information table, and whether the transaction amount meets the limit requirements corresponding to the customer level. Users first write SQL statements to count the total amount of erroneous data in this complex scenario, and then select corresponding check parameters for the custom SQL, such as cross-table join field names, mapping parameters between customer level and amount limit, multi-table join condition parameters, and filtering condition parameters for complex logical judgments. This provides key parameter support for the template tool to automatically generate the other three types of check SQL statements.

[0068] Figure 4 This is a specific example diagram illustrating the generation of a check SQL script and configuration file that match the check parameters in an embodiment of the present invention, as shown below. Figure 4 As shown, by using the template and parameters for generating the check SQL script, a check SQL script and configuration file matching the check parameters are generated, which may include:

[0069] Step 401: Import the inspection parameters into the inspection SQL script generation template. Match the corresponding basic structure of the SQL statement according to the inspection parameters to obtain the inspection SQL script.

[0070] Step 402: Generate a configuration file based on the check SQL script; the configuration file includes the execution parameters of the check SQL script.

[0071] In a specific embodiment, the following steps are performed:

[0072] Import check parameters to generate check SQL script:

[0073] Import the inspection parameters corresponding to different inspection requirements into the pre-built inspection SQL script generation template:

[0074] For routine non-null checks on the customer information table, import commonly used check parameters such as: table name to be checked = customer information table, field name to be checked = customer unique identifier field, check rule = non-null, data range = data from January to June 2024;

[0075] The template automatically matches the basic structure of the SQL statements corresponding to total data statistics, error data statistics, error detail table creation, and error detail collection, and replaces variables such as table name, field name, and data range in the template to generate four types of check SQL scripts for non-null checks of the table. For example, SQL to count the total data volume of the table from January to June 2024, SQL to count the number of null values ​​in the customer unique identifier field, etc.

[0076] For business logic association checks of business approval forms, import check parameters configured by custom templates, such as: Checked form name = business approval form, checked field name = approval status field & approval time field, check rule = approval time must be filled in for approval passed status, data range = data from the second quarter of 2024;

[0077] The template matches the basic structure of the SQL statement corresponding to the custom template, replaces variables such as table name, related field name, and business logic parameters, and generates four types of check SQL scripts that conform to the business logic. For example, it can be used to count the amount of erroneous data where the approval status is "passed" but the approval time is empty.

[0078] For complex cross-table checks between transaction records and customer information tables, import custom SQL statements with corresponding check parameters such as: Checked table name = Transaction record table & Customer information table, Related field name = Customer unique identifier, Check rule = Transaction record customer identifier must exist in the customer information table and the transaction amount must meet the customer level limit, Data range = Data from June 2024, etc.

[0079] The template is based on the user-written SQL for total error count, matches the corresponding infrastructure, and automatically generates SQL scripts for total data count, error detail table creation, and error detail collection, forming a complete set of four types of check SQL scripts.

[0080] Generate configuration file based on the check SQL script:

[0081] For each type of check SQL script generated in step 401, a corresponding configuration file is generated. The configuration file contains the execution parameters of the check SQL script, as follows:

[0082] Database connection parameters include database type, server address, port number, database name, login account, password, etc., to ensure that the verification tool can connect to the target database to execute SQL scripts, such as the server address and login account of the database where the customer information table is located.

[0083] Script execution parameters include: the name of the check task, the storage path of the check SQL script, the execution order (i.e., first execute the SQL for creating the error details table, then execute the SQL for total data statistics and the SQL for error data statistics, and finally execute the SQL for collecting error details), the execution frequency (e.g., execute at 3 AM every day), and the timeout duration (e.g., if the execution exceeds 30 minutes, it is considered a failure).

[0084] Result storage parameters: These include the name of the check result table, the name of the error details table, the name of the log table, and the corresponding storage path. They specify the storage location of the check results, error details, and execution logs. For example, error details for non-empty checks of the customer information table are stored in the customer information table_error details_202406 table.

[0085] The configuration file is saved in a readable text format to ensure that the verification tools on each node of the distributed system can read the parameters and execute the verification tasks, thus enabling the matching use of the verification SQL script and execution parameters.

[0086] Figure 5 This is a specific example diagram of the inspection result obtained in an embodiment of the present invention, such as... Figure 5 As shown, the verification tool script is deployed to multiple nodes of the distributed data system. The verification tool on each node performs verification based on the verification SQL script and configuration file, and obtains the verification results, which may include:

[0087] Step 501: Deploy the verification tool script to multiple nodes of the distributed data system;

[0088] Step 502: The verification tool performs the following checks in sequence according to the verification SQL script and configuration file: total data statistics verification, error data identification verification, and error detail collection verification, to obtain the verification results. The verification results include error data, error data volume, and error data percentage.

[0089] In a specific embodiment, the following steps are performed:

[0090] Inspection tool script deployment:

[0091] The verification tool script of this invention is based on Perl script. First, the Perl runtime environment is installed on the three nodes of the distributed data system to ensure that the operating systems of each node (node ​​1 is Linux, node 2 is Windows, and node 3 is macOS) all meet the requirements for running the Perl environment.

[0092] Copy the Perl script for the check tool and the corresponding check SQL script and configuration file generated in step 103, including the database connection parameters and execution parameters for each node, to the specified directories on the three nodes, such as the / data / check_tool / directory on node 1, the D:\data_check\ directory on node 2, and the / Users / data / check / directory on node 3.

[0093] Simple configuration for each node: Add the node-specific database connection information to the configuration file, such as the server address and login account of the customer information database for node 1, and the port number and password of the transaction log database for node 2. No complicated installation process is required to complete the deployment of the verification tool script on multiple nodes of the distributed data system.

[0094] Each node performs the check and obtains the check result:

[0095] Each node's verification tool reads the locally stored verification SQL scripts and configuration files, and performs the following checks in the configured execution order: total data statistics verification, error data identification verification, and error detail collection verification.

[0096] Node 1 (Customer Information Data): First, execute the total data volume statistics check SQL, which counts the total data volume of the customer information table in the second quarter of 2024 as 12,000 records; then execute the error data identification check SQL, which identifies 15 records of erroneous data with null values ​​in the customer unique identifier field and 8 records of erroneous data with non-standard format, for a total of 23 erroneous records; finally, execute the error detail collection check SQL, which stores the field values ​​and error reasons of the 23 erroneous records, such as null unique identifiers and incorrect format, into the error detail table, and calculates the percentage of erroneous data as 23 / 12000≈0.19%.

[0097] Node 2 (Transaction Data): Execute the total data volume statistics check SQL to obtain a total of 85,000 transaction data entries for the second quarter of 2024; execute the error data identification check SQL to detect 32 erroneous data entries with negative transaction amounts and 12 erroneous data entries with transaction dates exceeding the quarterly range, for a total of 44 erroneous data entries; execute the error detail collection check SQL to store the error details, with the erroneous data ratio being 44 / 85000≈0.05%.

[0098] Node 3 (Business Approval Data): Execute the total data volume statistics check SQL to count the total data volume of the business approval table in the second quarter of 2024, which is 5000 records; execute the error data identification check SQL to detect 9 error data records that were approved but had empty approval times and 6 error data records that did not match the approval status with the business type, for a total of 15 error data records; execute the error detail collection check SQL to store the error details, with the error data ratio being 15 / 5000=0.3%.

[0099] After each node completes the verification, it generates verification results containing error data, error data volume, and error data percentage, and stores them in the verification result table and error detail table on each node's local machine, providing basic data for subsequent data aggregation and rectification labeling.

[0100] In this embodiment, after the verification tool performs total data volume statistics verification, error data identification verification, and error detail collection verification in sequence according to the verification SQL script and configuration file, and obtains the verification results, the process includes:

[0101] Errors in the inspection results are marked and stored.

[0102] In a specific embodiment, taking the three nodes of the distributed data system, which respectively store customer information data, transaction flow data, and business approval data, and the obtained verification results, as the objects, the operation is carried out in the following manner:

[0103] Error data indexing:

[0104] The inspection tools at each node first index the error data collected during this inspection, and then establish an efficient data retrieval structure using the business primary key and the inspection rule number as keywords.

[0105] Among them, the business primary key is a unique identifier for each data table, such as the unique customer identifier in the customer information table, the transaction number in the transaction log table, and the approval number in the business approval table. The check rule number corresponds to different check types, such as non-empty check number 001, value range check number 002, and business logic association check number 003, to ensure that the specific business record and check rule corresponding to each erroneous data can be quickly located.

[0106] Error data comparison and rectification identification:

[0107] The verification tool retrieves historical error details stored locally on each node, such as the error details table from the previous quarter, and compares the error data from the current verification with the historical error data:

[0108] Regarding node 1 (customer information data): The comparison revealed that among the 23 erroneous data detected this time, 5 erroneous data existed in the historical error details and did not reappear in this inspection, and were determined to be rectified data; the remaining 18 erroneous data were newly added erroneous data.

[0109] Regarding node 2 (transaction log data): the comparison revealed 44 erroneous data entries. 10 of these entries had negative transaction amounts and existed in the historical records but did not appear in this instance, so they were determined to be rectified data. The remaining 34 entries were newly added erroneous data entries.

[0110] Regarding node 3 (business approval data): the comparison revealed 15 erroneous data entries. Among them, 3 entries with approved data but empty approval time existed in the historical records and did not appear in this instance, and were therefore identified as rectified data. The remaining 12 entries were newly added erroneous data entries.

[0111] Error data tagging and storage:

[0112] The verification tool adds specific tagging information to error data in different states and stores it in the error details table of each node:

[0113] For rectified data: Add a label to the corresponding error record indicating that the rectification status is "rectified" and the rectification confirmation date is the date of this inspection, clearly marking that the data has been rectified.

[0114] For newly added error data: add the following information to the corresponding error record: Rectification status = not rectified, inspection date = the date of this inspection, and error type = the corresponding inspection rule type. For example, error type = non-empty inspection failed, error type = business logic mismatch tag information. This will facilitate subsequent tracking and rectification.

[0115] After the tagging is completed, each node will synchronously store the updated error details table and the inspection result table to the local designated path, and at the same time generate a tagging summary log, which records the amount of rectified data, the amount of newly added error data and the tagging time involved in this tagging, providing a clear data basis for subsequent distributed data aggregation and rectification progress management.

[0116] Verification has shown that the embodiments of the present invention have the following beneficial effects:

[0117] 1. Significantly improves the efficiency of SQL script generation, reducing manual costs and error rates: Through a batch SQL script generation template tool based on EXCEL and macro programming, it supports three generation methods: built-in templates, custom templates, and custom SQL writing. Users do not need to manually write a large number of SQL scripts. They only need to fill in the table to be checked, fields, or simple configuration parameters to automatically generate complete SQL scripts and configuration files required for total data statistics, error data statistics, and error detail storage. This not only reduces the script generation time from several hours of traditional manual writing to minutes, but also avoids syntax errors and logical deviations that are prone to occur in manual writing, significantly improving the efficiency and accuracy of script generation.

[0118] 2. Enhance the flexibility of the inspection tool and adapt to diverse inspection needs: In response to the problem that existing tools only support preset rules and are difficult to customize, the template generated by this invention not only has built-in common inspection rules such as non-null, primary key duplication, and value range, which meet the needs of routine data quality inspection, but also supports custom template configuration and custom SQL writing, which can be adapted to the inspection of specific business logic and complex scenarios of enterprises, without the need for complicated programming and configuration, and flexibly respond to different types of data quality inspection needs.

[0119] 3. Achieve lightweight distributed deployment, reducing system deployment difficulty and complexity: The verification tool is entirely based on Perl scripts, with a small size and strong cross-platform compatibility. It can be quickly deployed on systems that support Perl environments, such as Windows, Linux, and macOS. Users only need to copy the script and configuration file, and add simple parameters such as database connection. Without a complicated installation process, deployment can be completed on multiple nodes of the distributed data system. This solves the deployment difficulties caused by heterogeneity in traditional tools in distributed environments, and enables unified management and efficient execution of verification tasks.

[0120] 4. Improve the efficiency and accuracy of data rectification and facilitate the tracking of rectification progress: By indexing business primary keys and rule numbers and comparing with historical error data, the inspection tool can automatically identify rectified data and add marking information to error records. At the same time, it can also mark newly added error data, avoiding the tedious operation and omissions of manual comparison and identification of rectification data. It can clearly distinguish the rectification status, provide accurate basis for data rectification priority division and progress tracking, and significantly improve the efficiency and accuracy of data rectification.

[0121] 5. Achieve unified management of distributed inspection results and support comprehensive data quality analysis: Utilize database and table unloading tools to convert the inspection results and error details of each node into easily transferable file formats. Then, aggregate them to the central node via encrypted network file transfer tools. After loading and aggregation, unified display and analysis of distributed inspection results are achieved. Managers can intuitively obtain global data quality information such as the amount of error data, error ratio, and rectification status of each node without having to check each node individually, providing comprehensive and clear data support for the formulation of data quality control strategies.

[0122] This invention also provides a distributed data quality inspection device, as described in the following embodiments. Since the principle by which this device solves the problem is similar to that of the distributed data quality inspection method, the implementation of this device can refer to the implementation of the distributed data quality inspection method; repeated details will not be elaborated further.

[0123] Figure 6 This is a structural example diagram of the distributed data quality inspection device in an embodiment of the present invention, as shown below. Figure 6 As shown, the distributed data quality inspection device includes:

[0124] Template generation module 601 is used to build a template for generating the check SQL script;

[0125] The inspection parameter generation module 602 is used to generate inspection parameters according to the target inspection requirements; the inspection parameters include commonly used inspection parameters, inspection parameters configured by custom templates, and inspection parameters corresponding to custom SQL statements;

[0126] The verification script determination module 603 is used to generate a verification SQL script and configuration file that match the verification parameters by using the generation template and verification parameters of the verification SQL script;

[0127] The verification module 604 is used to deploy the verification tool script to multiple nodes of the distributed data system. The verification tool on each node performs verification based on the verification SQL script and configuration file to obtain the verification result.

[0128] In one embodiment, the template generation module 601 is specifically used for:

[0129] A basic framework for generating check SQL scripts is constructed based on macro programming; the basic framework includes check SQL statements for total data statistics, error data statistics, and error detail storage;

[0130] Configure the template for generating the check SQL script in the basic framework to replace the name of the table being checked, the name of the field being checked, the check parameters, and the data check range, and you will get the template for generating the check SQL script.

[0131] In one embodiment, the check parameter generation module 602 is specifically used for:

[0132] If the target verification requirement is a routine data quality check, select commonly used verification parameters;

[0133] If the target verification requirement is a specific business logic verification, select the verification parameters configured in the custom template;

[0134] If the target inspection requirement is a specified complex scenario inspection, select the inspection parameters corresponding to the custom-written SQL.

[0135] In one embodiment, the check script determination module 603 is specifically used for:

[0136] Import the inspection parameters into the inspection SQL script generation template, match the corresponding basic structure of the SQL statement according to the inspection parameters, and obtain the inspection SQL script;

[0137] A configuration file is generated based on the check SQL script; the configuration file includes the execution parameters of the check SQL script.

[0138] In one embodiment, the inspection module 604 is specifically used for:

[0139] Deploy the verification tool scripts to multiple nodes of the distributed data system;

[0140] The verification tool performs data total statistics verification, error data identification verification, and error detail collection verification in sequence according to the verification SQL script and configuration file, and obtains the verification results; the verification results include error data, error data volume, and error data percentage.

[0141] Figure 7 This is a specific example diagram of the structure of the distributed data quality inspection device in an embodiment of the present invention, as shown below. Figure 7 As shown in one embodiment, Figure 6 The distributed data quality inspection device shown in this embodiment of the invention may further include: a tagging and storage module 701.

[0142] In one embodiment, the marking and storage module 701 is specifically used for:

[0143] The verification tool performs total data statistics verification, error data identification verification, and error detail collection verification in sequence according to the verification SQL script and configuration file. After obtaining the verification results, the error data in the verification results are tagged and stored.

[0144] Based on the aforementioned inventive concept, such as Figure 8 As shown, the present invention also proposes a computer device 800, including a memory 810, a processor 820, and a computer program 830 stored in the memory 810 and executable on the processor 820. When the processor 820 executes the computer program 830, it implements the aforementioned distributed data quality inspection method.

[0145] This invention also provides a computer-readable storage medium storing a computer program that, when executed by a processor, implements the above-described distributed data quality check method.

[0146] This invention also provides a computer program product, which includes a computer program that, when executed by a processor, implements the above-described distributed data quality inspection method.

[0147] In this embodiment of the invention, a template for generating verification SQL scripts is constructed; verification parameters are generated according to the target verification requirements; the verification parameters include commonly used verification parameters, verification parameters configured by custom templates, and verification parameters corresponding to custom-written SQL; through the template for generating verification SQL scripts and the verification parameters, verification SQL scripts and configuration files matching the verification parameters are generated; the verification tool scripts are deployed to multiple nodes of the distributed data system, and the verification tools on each node perform verifications according to the verification SQL scripts and configuration files to obtain verification results; this embodiment of the invention, by constructing a template for generating verification SQL scripts to adapt to multiple types of verification parameters, can automatically generate the SQL scripts and configuration files required for verification, reducing manual writing costs; it improves the efficiency and flexibility of data quality verification, and provides technical support for data quality control.

[0148] Those skilled in the art will understand that embodiments of the present invention can be provided as methods, systems, or computer program products. Therefore, the present invention can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the present invention can take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0149] This invention is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0150] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0151] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0152] The specific embodiments described above further illustrate the purpose, technical solution, and beneficial effects of the present invention. It should be understood that the above descriptions are merely specific embodiments of the present invention and are not intended to limit the scope of protection of the present invention. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention should be included within the scope of protection of the present invention.

Claims

1. A distributed data quality inspection method, characterized in that, include: Build a template for generating the check SQL script; The inspection parameters are generated based on the target inspection requirements; the inspection parameters include commonly used inspection parameters, inspection parameters configured by custom templates, and inspection parameters corresponding to custom SQL statements. By checking the SQL script generation template and check parameters, a check SQL script and configuration file matching the check parameters are generated. The verification tool script is deployed to multiple nodes of the distributed data system. The verification tool on each node performs verification based on the verification SQL script and configuration file, and obtains the verification result.

2. The method as described in claim 1, characterized in that, Construct a template for generating the check SQL script, including: A basic framework for generating check SQL scripts is constructed based on macro programming; the basic framework includes check SQL statements for total data statistics, error data statistics, and error detail storage; Configure the template for generating the check SQL script in the basic framework to replace the name of the table being checked, the name of the field being checked, the check parameters, and the data check range, and you will get the template for generating the check SQL script.

3. The method as described in claim 1, characterized in that, Generate inspection parameters based on the target inspection requirements, including: If the target verification requirement is a routine data quality check, select commonly used verification parameters; If the target verification requirement is a specific business logic verification, select the verification parameters configured in the custom template; If the target inspection requirement is a specified complex scenario inspection, select the inspection parameters corresponding to the custom-written SQL.

4. The method according to any one of claims 1-3, characterized in that, By using the generated template and parameters of the verification SQL script, a verification SQL script and configuration file matching the verification parameters are generated, including: Import the inspection parameters into the inspection SQL script generation template, match the corresponding basic structure of the SQL statement according to the inspection parameters, and obtain the inspection SQL script; A configuration file is generated based on the check SQL script; the configuration file includes the execution parameters of the check SQL script.

5. The method as described in claim 1, characterized in that, The verification tool scripts are deployed to multiple nodes of the distributed data system. Each node's verification tool performs verification based on the verification SQL scripts and configuration files, obtaining the verification results, including: Deploy the verification tool scripts to multiple nodes of the distributed data system; The verification tool performs data total statistics verification, error data identification verification, and error detail collection verification in sequence according to the verification SQL script and configuration file, and obtains the verification results; the verification results include error data, error data volume, and error data percentage.

6. The method as described in claim 5, characterized in that, The verification tool, based on the verification SQL script and configuration file, sequentially performs data volume statistics verification, error data identification verification, and error detail collection verification. After obtaining the verification results, they include: Errors in the inspection results are marked and stored.

7. A distributed data quality inspection device, characterized in that, include: The template generation module is used to build templates for generating check SQL scripts; The inspection parameter generation module is used to generate inspection parameters according to the target inspection requirements; the inspection parameters include commonly used inspection parameters, inspection parameters configured by custom templates, and inspection parameters corresponding to custom SQL statements; The check script determination module is used to generate a check SQL script and configuration file that match the check parameters based on the check SQL script generation template and check parameters. The verification module is used to deploy verification tool scripts to multiple nodes of the distributed data system. The verification tools on each node perform verification based on the verification SQL script and configuration file to obtain the verification results.

8. A computer device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements the method according to any one of claims 1-6.

9. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a computer program that, when executed by a processor, implements the method of any one of claims 1-6.

10. A computer program product, characterized in that, The computer program product includes a computer program that, when executed by a processor, implements the method of any one of claims 1-6.

Images