Cross-system data quality inspection method and system

Abstract

The invention provides a cross-system data quality inspection method and system, the method comprises a cross-system rule configuration step, a dynamic execution scheduling step and a real-time feedback step, in a scheduling center domain, based on a data storage system itself, a data quality inspection rule across different storage systems is flexibly defined with result inspection as a guide, and the data quality inspection accuracy is improved. In combination with flexible configuration of the execution cycle, frequency and time period, unified scheduling of execution rules and parallel execution of tasks are achieved, abnormal results are fed back in real time, cross-system data inspection is achieved, the data quality inspection process can be simplified, the data quality inspection cost is reduced, the detection capacity is greatly improved, and the method has the remarkable advantages of being efficient, flexible, high in coverage capacity and the like.

Description

Technical Field

[0001] The present invention relates to the technical field of data management, and in particular to a method and system for cross-system data quality inspection. Background Art

[0002] The importance and value of data as an asset have become a consensus among enterprises and society as a whole. Data quality is the lifeline of data value. How to ensure data quality and detect data problems in a timely manner is a daunting challenge faced by all enterprises.

[0003] Currently, there are numerous data governance approaches in the industry. For example, for internet platform data governance products, ensuring the quality of a small piece of core data often requires recruiting specialized personnel and deploying a comprehensive suite of data governance products. This requires rebuilding the entire process, from data standardization and collection to data processing, data storage, and subsequent data applications. This results in extremely high operational and technical maintenance costs for businesses. Forgoing established data governance products and building one's own data quality testing system also presents various challenges. Current data quality checking technologies or configuration implementations are inflexible, fail to address cross-storage system data quality checks, or are costly and rely on complex data governance chains.

[0004] Therefore, there is an urgent need for a method that can realize cross-system data inspection with flexible data quality inspection rule configuration and high inspection efficiency, and can simplify the data quality inspection process and reduce the cost of data quality inspection. Summary of the Invention

[0005] To address the problems of high data quality inspection costs, inflexible configuration, and lack of cross-storage system inspection in existing technologies, the present invention provides a cross-system data quality inspection method. Within the dispatch center domain, data quality inspection rules are constructed using the structured query language SQL. This method is adaptable to multiple relational database storage systems and combines flexible configuration of execution cycles, frequencies, and time periods to achieve unified scheduling of execution rules and parallel execution of tasks, providing real-time feedback on abnormal results. This effectively reduces inspection costs and improves configuration flexibility and inspection efficiency. The present invention also relates to a cross-system data quality inspection system.

[0006] The technical solutions of the present invention are as follows:

[0007] A method for cross-system data quality inspection, characterized by comprising the following steps:

[0008] The cross-system rule configuration step configures data quality inspection rules for each storage system in the scheduling center domain based on the data content of multiple heterogeneous storage systems. The data quality inspection rules include data sources, SQL scripts for abnormal data query logic, comparison operation logic, and corresponding judgment criteria.

[0009] Dynamically execute the scheduling steps and uniformly configure the execution rules including the execution cycle, execution frequency, and execution time period within the scheduling center domain; and cyclically perform the following operations at a fixed frequency of one second:

[0010] Load the latest execution rules and data quality inspection rules; trigger the execution of data quality inspection rules based on the loaded execution rules, and generate independent inspection tasks for each storage system based on the loaded data quality inspection rules. The inspection tasks include instantiated data source information, abnormal data query logic SQL script, comparison operation logic and corresponding judgment criteria; execute each inspection task cyclically within the dispatch center domain. When executing each inspection task, first obtain the potential abnormal data set that meets the query logic based on the instantiated data source information and abnormal data query logic SQL script, and then perform a comparison operation on the potential abnormal data set based on the instantiated comparison operation logic to generate a comparison operation result;

[0011] In the real-time feedback step, when the comparison operation result exceeds the corresponding instantiated judgment standard, the data quality inspection result is judged to be abnormal in the dispatching center domain, and the abnormal result of each inspection task is fed back in real time.

[0012] Preferably, the dispatch center domain is implemented by open source Kettle;

[0013] The comparison operation logic setting includes a comparison operation symbol and a comparison operation value, wherein the comparison operation symbol includes equality judgment, size judgment, inclusion judgment, regular matching, and null value judgment; the comparison operation value includes a constant, a variable, and another SQL query result;

[0014] The judgment criterion is a threshold or a matching pattern.

[0015] Preferably, in the dynamic execution scheduling step, when the latest execution rules and data quality check rules are cyclically loaded at a fixed frequency of seconds, the steps specifically include:

[0016] Check the timestamp or version number of the rule file and reload it if it changes;

[0017] During the rule loading process, new rules are subject to syntax verification and dependency checking;

[0018] If the new rule fails to be verified, the previous version of the rule will continue to be used and the error log will be recorded.

[0019] Preferably, the real-time feedback step, after determining an abnormality, specifically includes:

[0020] If the data quality is judged to be abnormal, an abnormality report will be generated containing the abnormal data details, abnormality type, and abnormality occurrence time;

[0021] Send exception notifications to the corresponding responsible parties through at least one of email, SMS, system messages, and WeChat;

[0022] Record abnormal results in the log system and associate them with the corresponding data quality check rules and execution task ID.

[0023] Preferably, in the cross-system rule configuration step, data quality check rules are configured for each storage system using structured query language SQL, specifically including:

[0024] When configuring data quality check rules for relational databases, table joins, aggregate functions, and window functions are supported;

[0025] When configuring data quality check rules for NoSQL databases, support is provided for JSON path expressions, document nested queries, and key-value pair matching.

[0026] When configuring data quality check rules for a file system, file format verification, field delimiter verification, and file size threshold verification are supported.

[0027] Preferably, in the dynamic execution scheduling step, the configuration of the execution rules includes independent definition and combination logic of the execution cycle, execution frequency and execution time period: wherein,

[0028] Execution cycle: It is defined as a single trigger mechanism based on a specified moment or periodic time point; or a compound trigger mechanism based on a specified moment or periodic time point and superimposed on the execution period range;

[0029] Execution frequency: defined as the frequency of loop execution of the check rule within a fixed time period;

[0030] Combinational logic: When the execution cycle is defined as a combination of a compound trigger mechanism and an execution frequency, the execution is cyclically executed at the execution frequency within the execution time period. When the execution cycle is defined as a combination of a single trigger mechanism and an execution frequency, the execution is repeated at the execution frequency after the trigger time point.

[0031] A cross-system data quality inspection system, characterized by comprising a cross-system rule configuration module, a dynamic execution scheduling module and a real-time feedback module connected in sequence,

[0032] The cross-system rule configuration module configures data quality inspection rules for each storage system in the scheduling center domain based on the data content of multiple heterogeneous storage systems in a result-oriented manner through the structured query language SQL. The data quality inspection rules include data sources, abnormal data query logic SQL scripts, comparison operation logic and corresponding judgment criteria;

[0033] The dynamic execution scheduling module uniformly configures execution rules including execution cycle, execution frequency and execution time period in the scheduling center domain; and performs the following operations cyclically at a fixed frequency of seconds:

[0034] Load the latest execution rules and data quality inspection rules; trigger the execution of data quality inspection rules based on the loaded execution rules, and generate independent inspection tasks for each storage system based on the loaded data quality inspection rules. The inspection tasks include instantiated data source information, abnormal data query logic SQL script, comparison operation logic and corresponding judgment criteria; execute each inspection task cyclically within the dispatch center domain. When executing each inspection task, first obtain the potential abnormal data set that meets the query logic based on the instantiated data source information and abnormal data query logic SQL script, and then perform a comparison operation on the potential abnormal data set based on the instantiated comparison operation logic to generate a comparison operation result;

[0035] The real-time feedback module determines that the data quality inspection result is abnormal in the dispatching center domain when the comparison operation result exceeds the instantiated corresponding judgment standard, and then feeds back the abnormal result of each inspection task in real time.

[0036] Preferably, the dispatch center domain is implemented by open source Kettle;

[0037] The comparison operation logic setting includes a comparison operation symbol and a comparison operation value, wherein the comparison operation symbol includes equality judgment, size judgment, inclusion judgment, regular matching, and null value judgment; the comparison operation value includes a constant, a variable, and another SQL query result;

[0038] The judgment criterion is a threshold or a matching pattern.

[0039] Preferably, in the dynamic execution scheduling module, the configuration of the execution rules includes independent definitions and combination logic of the execution cycle, execution frequency and execution time period: wherein,

[0040] Execution cycle: It is defined as a single trigger mechanism based on a specified moment or periodic time point; or a compound trigger mechanism based on a specified moment or periodic time point and superimposed on the execution period range;

[0041] Execution frequency: defined as the frequency of loop execution of the check rule within a fixed time period;

[0042] Combinational logic: When the execution cycle is defined as a combination of a compound trigger mechanism and an execution frequency, the execution is cyclically executed at the execution frequency within the execution time period. When the execution cycle is defined as a combination of a single trigger mechanism and an execution frequency, the execution is repeated at the execution frequency after the trigger time point.

[0043] Preferably, the real-time feedback module, after determining an abnormality, specifically includes:

[0044] If the data quality is judged to be abnormal, an abnormality report will be generated containing the abnormal data details, abnormality type, and abnormality occurrence time;

[0045] Send exception notifications to the corresponding responsible parties through at least one of email, SMS, system messages, and WeChat;

[0046] Record abnormal results in the log system and associate them with the corresponding data quality check rules and execution task ID.

[0047] The beneficial effects of the present invention are:

[0048] The present invention provides a method for cross-system data quality inspection. Its cross-system rule configuration step customizes inspection rules for each storage system (such as relational databases, NoSQL, file systems, etc.) through SQL, solves the problem of inconsistent cross-system data standards, improves the adaptability of heterogeneous systems, and supports unified management and control of multi-source data quality; rules are defined in a "result-oriented" manner, and business personnel can quickly configure inspection rules directly through SQL scripts and comparison operation logic (such as threshold judgment, logical combination) without relying on technical personnel for development, shortening the demand response cycle; and the rules are decoupled from the data source, and the same rule can be reused in different storage systems or different time periods, improving the efficiency of rule management. Its dynamic execution scheduling steps load rules in a fixed-frequency loop at the second level to ensure that configuration changes take effect in real time. It has real-time and accuracy, and meets the quasi-real-time monitoring needs of data quality in scenarios such as finance and e-commerce; based on the three-dimensional scheduling logic of "execution cycle + execution frequency + execution period", it realizes refined triggering to avoid invalid execution or missed detection; by separating the acquisition of potential abnormal data sets from comparison operation judgments, it supports complex verification logic, and the instantiation mechanism (dynamically assigning data sources, thresholds and other parameters) that assigns values ​​according to actual needs enables rules to be reused in different environments, improving flexibility; independent inspection tasks are generated and executed in a loop within the scheduling center domain, supporting parallel or sequential processing of inspection tasks on multiple storage systems, making full use of system resources and improving inspection efficiency; it has fault tolerance and stability, and the loop execution mechanism automatically handles task failures and retries, which can be combined with syntax verification during rule loading to ensure stable system operation. Its real-time feedback step is highly efficient in locating anomalies. It automatically determines anomalies based on comparative operation logic and can generate reports containing data details and anomaly types, reducing manual troubleshooting costs. It provides real-time feedback on anomaly results (such as emails and SMS notifications) to support business teams in handling data issues in the first place and reduce the impact of data errors on the business. It is also traceable, providing a basis for reviewing data quality issues and optimizing rules.

[0049] The present invention can implement the scheduling center domain based on open source Kettle, without relying on complex distributed frameworks (such as Spark and Flink), and perform lightweight cross-system scheduling, reducing technical barriers and deployment costs, and is suitable for small and medium-sized enterprises or hybrid architecture systems; data quality inspection rules (SQL+comparison logic) and execution rules (cycle+frequency+time period) are designed separately to achieve decoupling of rules and execution, support dynamic adjustment of rules without affecting execution logic, and improve system scalability; through the two-stage optimization design of data processing by first querying potential abnormal data and then applying comparison logic, full data scanning is avoided and system overhead is reduced; the entire process from rule definition, task execution to abnormality feedback is automated, reducing errors caused by manual intervention, ensuring data consistency, completeness and accuracy, and providing a reliable data foundation for data analysis and decision support; based on the data storage system itself and guided by result inspection, data quality inspection rules across different storage systems can be flexibly defined, and cross-system data inspection can be directly implemented based on a unified rule execution center, greatly improving detection capabilities, simplifying the data quality inspection process, and reducing the cost of data quality inspection.

[0050] Furthermore, the comparison operation logic setting includes comparison operation symbols and comparison operation values, wherein the comparison operation symbols include equality judgment, size judgment, inclusion judgment, regular matching, and null value judgment; the comparison operation values ​​include constants, variables, and another SQL query result; and the judgment standard is a threshold or matching pattern. By limiting the composition of the comparison operation logic (including multiple operation symbols and operation value types) and the specific form of the judgment standard (threshold or matching pattern), the data quality inspection rules can cover a variety of scenarios such as numerical size comparison, data format verification, and dynamic benchmark value matching. For example, data format compliance can be verified through regular matching, and cross-system data consistency verification can be performed by citing other SQL query results as dynamic thresholds. At the same time, the decoupling design of the operation logic and the judgment standard is clarified, supporting business personnel to flexibly combine rule elements (such as "size judgment + variable threshold" and "inclusion judgment + regular matching pattern") according to actual needs, significantly improving the configuration flexibility and complex scenario adaptability of data quality inspection rules, and reducing the rule development cost of multi-type data verification.

[0051] Furthermore, when the latest execution rules and data quality check rules are loaded cyclically at a fixed frequency of seconds, timestamp / version number detection, syntax verification and dependency checking mechanisms are introduced in the rule loading process to ensure that the loaded execution rules and data quality check rules are accurate and usable; when the verification fails, the old rules are used and logs are recorded to avoid system operation abnormalities due to rule errors, thereby ensuring the stability and reliability of the system operation.

[0052] Furthermore, the specific operations of exception feedback are refined, and detailed exception reports are generated to facilitate rapid location of the root cause of the problem; multiple channels are used to notify responsible parties to ensure that exception information is delivered in a timely manner and to accelerate problem handling; exception results are associated with rules and task IDs to improve the exception tracing chain and provide comprehensive data support for subsequent data quality improvements.

[0053] Furthermore, for different types of storage systems (relational databases, NoSQL databases, file systems), a highly adaptable rule configuration method is provided to solve the problem of non-universality of data quality inspection rules in heterogeneous storage environments, and enhance the compatibility and applicability of the method to complex data storage architectures.

[0054] Furthermore, the configuration of execution rules includes independent definitions and combination logic of execution cycle, execution frequency and execution time period, deeply limits the configuration logic of execution rules, distinguishes between single trigger mechanism and compound trigger mechanism, and clarifies the combination logic with execution frequency. This makes the scheduling strategy more flexible and precise, which can not only meet the conventional needs of execution at fixed time points or cycles, but also realize complex scheduling scenarios such as high-frequency inspections within specific time periods, thereby improving the timeliness and accuracy of data quality inspections.

[0055] The present invention also relates to a system for cross-system data quality inspection, which corresponds to the above-mentioned method for cross-system data quality inspection and can be understood as a system for implementing a method for cross-system data quality inspection. By integrating a cross-system rule configuration module, a dynamic execution scheduling module and a real-time feedback module, the system realizes the full-process automated control of the data quality of multiple heterogeneous storage systems. The cross-system rule configuration module supports flexible definition of inspection rules containing multi-component comparison operation logic and dynamic judgment standards through SQL in the scheduling center domain, adapts to multiple types of data sources such as relational databases, NoSQL, and file systems, and solves the pain point of inconsistent data standards in heterogeneous systems; the dynamic execution scheduling module generates and executes tasks driven by second-level loop loading rules and three-dimensional execution rules (execution cycle + frequency + time period) in the scheduling center domain, and improves processing efficiency through a two-stage processing mechanism of data query-comparison operation, thereby realizing accurate triggering of data quality inspection and optimized resource utilization; when the real-time feedback module detects data anomalies, it automatically generates an anomaly report with details and notifies the responsible party through multiple channels. The system significantly improves the flexibility, real-time performance and reliability of cross-system data quality checks, reduces the technical threshold and operation and maintenance costs of enterprise data governance, and provides a high-quality data foundation for data-driven decision-making. BRIEF DESCRIPTION OF THE DRAWINGS

[0056] Figure 1 It is a flow chart of the method for cross-system data quality inspection of the present invention.

[0057] Figure 2It is a logical principle diagram of the method for cross-system data quality inspection of the present invention.

[0058] Figure 3 It is a flow chart of the definition and comparison logic of the data quality inspection rules of the present invention.

[0059] Figure 4 Schematic diagram of key elements of data inspection execution rules of the present invention.

[0060] Figure 5 A flowchart for generating and executing the cross-system data quality check task of the present invention.

[0061] Figure 6 This is a workflow diagram of cross-system data quality inspection and exception notification based on Kettle in the present invention. DETAILED DESCRIPTION

[0062] The present invention will be described below with reference to the accompanying drawings.

[0063] The present invention relates to a method for cross-system data quality inspection, which is different from the data governance and quality management method of building the entire link. It takes the open source system Kettle as the scheduling center and multiple relational database storage systems as examples. Based on the data content of each storage system and guided by the result inspection, the data quality inspection rules of the storage system are respectively constructed through the structured query language SQL. The scheduling center uniformly calls based on different execution rules and promptly feeds back the inspection results. It is particularly noted that the scheduling center domain can be implemented through scheduling tools such as FineDataLink, Kettle, Talend, IBM DataStage, etc. The inspection rule configuration, execution rule configuration, rule feedback configuration and task execution are completed in the scheduling center domain. The inspection rule configuration is directly based on the structured query language to define the data inspection rules of different storage systems; the execution rule configuration is to define the rule execution cycle and execution frequency information; the rule feedback configuration is to define the rule exception response processing mechanism, such as email notification as an example; task execution is to cyclically obtain the inspection rules, perform inspections based on the execution rules, and notify the person responsible for the corresponding rules of the inspection results by email. The flowchart of the method for cross-system data quality inspection of the present invention is as follows: Figure 1 As shown, the following steps are included:

[0064] 1. Cross-system rule configuration step: In the scheduling center domain, data quality inspection rules are configured for each storage system based on the data content of multiple heterogeneous storage systems in a result-oriented manner using the structured query language SQL. The data quality inspection rules include data sources, abnormal data query logic SQL scripts, comparison operation logic, and corresponding judgment criteria.

[0065] Specifically, multiple heterogeneous storage systems, that is, multiple relational database storage systems, such as relational databases, NoSQL, file systems, and other types of database storage systems, such as Figure 2 Storage systems A, B, etc. in the system domain shown in the figure are configured with data quality inspection rules for each storage system using the structured query language SQL. Figure 2 Check rules 1, 2, etc. are shown. Specifically, when configuring data quality check rules for relational databases, table joins, aggregate functions, and window functions are supported; when configuring data quality check rules for NoSQL databases, JSON path expressions, document nested queries, and key-value pair matching are supported; and when configuring data quality check rules for file systems, file format verification, field delimiter verification, and file size threshold verification are supported.

[0066] The core of the cross-system rule configuration step is to define the inspection rules for data quality. Whether the rule definition is flexible and the coverage of the inspection rules are the key to distinguishing the pros and cons of different methods. The present invention is result-oriented and based on the structured query language SQL. It directly defines the query logic of abnormal data for different storage systems, forms an SQL inspection rule script (i.e., abnormal data query logic SQL script), and then combines the execution rules to execute them separately in each storage system, which can conveniently realize cross-system data quality inspection. By configuring the query logic SQL and forming the inspection rule script, it has significant advantages such as high efficiency, flexibility and strong coverage.

[0067] The data quality check rule settings include not only the exception check script defined in the structured query language (i.e., the abnormal data query logic SQL script), but also the comparison operation logic settings. The comparison operation logic is an important basis for judging whether the inspection is qualified. During the execution phase, the execution result of the inspection rule script can be compared with the comparison rule settings to determine whether it has passed the inspection. The comparison operation logic settings include two parts: the comparison operator symbol and the comparison operation value. The comparison operator symbol includes operations such as equality judgment, size judgment, inclusion judgment, regular matching, and null value judgment; the comparison operation value includes constants, variables, another SQL query result, etc. The data quality check rule settings also include the judgment criteria corresponding to the comparison operation logic, which can be a threshold or a matching pattern.

[0068] Taking duplicate data checking as an example, we first directly use the grouping and statistics capabilities of SQL to define duplicate data checking rules. The following is an example of the checking rule script.

[0069] select 1 from<schema.table> group by<biz_key> having count(1)>1;

[0070] In the comparison operation phase, the comparison operation logic is defined to determine whether the number of rows in the execution result is greater than the constant 1. Taking the data consistency of two storage systems as an example, two identical query SQLs are defined, one as the check rule script and the other as the comparison operation value. The comparison operation operator can be defined as containment or non-containment. The execution process of the data quality check rule is shown below: Figure 3 As shown, the judgment is made based on the comparison operation logic. If the comparison operation logic passes (ie meets the set judgment conditions), the process ends; if it fails, it is determined that abnormal data is found.

[0071] To address cross-system data quality checks, data quality check rule setup requires defining the corresponding data source information for checks and binding each rule to a specific data source. These data sources must be configured and applied in the execution environment. To ensure the correctness of rule configuration, data quality check rule setup should include rule validity checks and pre-execution of defined rule scripts based on the corresponding data source to ensure smooth execution of the rules in subsequent processes.

[0072] Data quality inspection rule settings allow for the same inspection rule to be applied with different comparison and calculation logic, and also allow the same comparison and calculation logic to be applied to different inspection rules. Thanks to the powerful query capabilities of Structured Query Language (SQL), a quality inspection method based on SQL-based rule scripts for defining abnormal data query logic, combined with comparison and calculation logic, can cover complex anomaly inspection requirements with clear business definitions.

[0073] Second, dynamically execute the scheduling step. The execution rules, including the execution cycle, execution frequency, and execution time period, are uniformly configured within the scheduling center domain. The following operations are then cyclically performed at a fixed frequency of one second:

[0074] Load the latest execution rules and data quality check rules; trigger the execution of data quality check rules based on the loaded execution rules, and generate independent inspection tasks for each storage system according to the loaded data quality check rules. The inspection tasks include instantiated data source information, abnormal data query logic SQL script, comparison operation logic and corresponding judgment criteria; open source Kettle executes each inspection task in a loop. When executing each inspection task, it first obtains the potential abnormal data set that meets the query logic according to the instantiated data source information and abnormal data query logic SQL script, and then performs comparison operations on the potential abnormal data set based on the instantiated comparison operation logic to generate comparison operation results.

[0075] Specifically, the configuration of execution rules includes the independent definition and combination logic of the execution cycle, execution frequency, and execution period: Among them, the execution cycle is defined as a single trigger mechanism based on a specified moment or periodic time point; or a composite trigger mechanism based on a specified moment or periodic time point and superimposed with the execution period range;

[0076] Execution frequency: defined as the frequency of loop execution of the check rule within a fixed time period;

[0077] Combinational logic: When the execution cycle is defined as a combination of a compound trigger mechanism and an execution frequency, the execution is cyclically executed at the execution frequency within the execution time period. When the execution cycle is defined as a combination of a single trigger mechanism and an execution frequency, the execution is repeated at the execution frequency after the trigger time point.

[0078] Execution rule configuration is an important configuration for data quality inspection and is of great significance for the timeliness of abnormal data discovery. Figure 2 As shown, system A executes rules, system B executes rules... The execution rules mainly define key element information such as the execution cycle, execution frequency, and execution time period of the inspection rules. The execution cycle and execution frequency are two similar concepts. The present invention uses them in combination with practical applications to distinguish them: the rules that are executed cyclically according to a fixed duration are defined as execution frequencies, such as once per minute, once every 60 minutes, or once every hour, etc.; the rules that are executed at specified times are defined as execution cycles, such as starting at 12:00 every day, starting at 08:00 every Monday, and starting at 00:00 on the 1st of each month, etc. The execution cycle also includes the definition of the execution time period, such as execution during the period of 08:00-18:00 every day. When the execution cycle defines a time period, it is usually combined with a fixed frequency, such as executing a check every hour during the period of 08:00-18:00 every day. The operational design diagram of the key elements of the execution rules (execution frequency, execution time period, and execution cycle) is shown as follows. Figure 4 As shown, the rule frequency (i.e., execution frequency) can be set using the slider or increase / decrease buttons, currently set to execute every 120 minutes. The check period (i.e., execution period) is defined by the slider. Day of Week (Execution Cycle ①) allows you to select the day of the week for execution. In this case, 1–5 is selected, meaning Monday through Friday. Day of Month (Execution Cycle ②) allows you to select the day of each month for execution, and you can select as needed. These operational designs intuitively demonstrate how to configure the key elements of the execution rule.

[0079] The execution of data quality check rules is one of the core aspects of data quality inspection. The execution of data quality check rules can be understood as including the loading of data quality check rules and execution rules, the judgment of execution rules, and the execution of check rules. These steps are executed cyclically at a fixed frequency of seconds. The significance of the fixed frequency of seconds lies in the near-real-time execution cycle. Furthermore, when the latest execution rules and data quality check rules are loaded cyclically at a fixed frequency of seconds, the following are specifically included:

[0080] Check the timestamp or version number of the rule file and reload it if it changes;

[0081] During the rule loading process, new rules are subject to syntax verification and dependency checking;

[0082] If the new rule fails to be verified, the previous version of the rule will continue to be used and the error log will be recorded.

[0083] The inspection rule execution process includes two independent sub-processes: inspection task generation and inspection task execution. Inspection task generation mainly defines how to generate execution tasks based on inspection rules and execution rules. Inspection task execution is mainly responsible for the final execution link. Figure 5 As shown, it mainly includes the following steps:

[0084] Execution rule judgment: Determine whether to enter the inspection execution process based on the status of the global switch. If the global switch is not turned on, it will end directly. If the global switch is turned on, it will proceed to the next step;

[0085] Inspection task generation: Based on rule configuration, it generates execution tasks for different systems. The tasks include instantiated data source information, abnormal data query logic SQL scripts, comparison operation logic, corresponding judgment criteria and abnormal notification definitions;

[0086] Inspection task execution: Determine whether there are any inspection tasks to be executed. If all have been executed, the process ends directly. If there are any unexecuted tasks, each inspection task is executed in a loop. The tasks in this part can be inspection tasks of any storage system, such as system A inspection task, system B inspection task... When executing each inspection task, first obtain the potential abnormal data set that meets the query logic based on the instantiated data source information and abnormal data query logic SQL script, that is, the script execution result of the storage system inspection task, and then perform comparative logic calculation, that is, compare the potential abnormal data set based on the instantiated comparison operation logic to generate a comparison operation result.

[0087] 3. Real-time feedback step, this step can be understood as rule feedback configuration, such as Figure 2The inspection rule 1 feedback configuration, inspection rule 2 feedback configuration, etc. shown in the figure are based on the comparison result of the comparison operation and the corresponding instantiated judgment standard to determine whether there is an abnormality. If there is no abnormality (the comparison operation result does not exceed the corresponding instantiated judgment standard, such as not exceeding the threshold), the inspection task execution is looped back to determine whether there are inspection tasks to be executed. If there is an abnormality (the comparison operation result exceeds the corresponding instantiated judgment standard, such as exceeding the threshold), the abnormal result is notified to the corresponding responsible party by email, and the abnormal result of each inspection task is fed back in real time. At the same time, the inspection task execution is looped back to determine whether there are inspection tasks to be executed.

[0088] Furthermore, after determining the abnormality, it specifically includes: if the data quality is determined to be abnormal, generating an abnormality report containing the details of the abnormal data, the type of abnormality, and the time when the abnormality occurred; sending an abnormality notification to the corresponding responsible party through at least one of email, SMS, system message, and WeChat; recording the abnormal results in the log system, and associating the corresponding data quality check rules and execution task ID.

[0089] Taking data duplication check as an example, the workflow for implementing cross-system data check is as follows:

[0090] ① Define duplicate data quality check rule scripts for System A and System B respectively:

[0091] select 1 from<schema.table> group by<biz_key> having count(1)>1;

[0092] ② Define execution rules for System A and System B respectively: check every 60 minutes and execute from 08:00 to 20:00 every day;

[0093] ③ Define feedback notification email addresses for execution check rules of System A and System B respectively;

[0094] ④Define the task execution function, which is responsible for generating inspection tasks from inspection rules;

[0095] ⑤Define Kettle tasks, call ④ to generate inspection tasks, execute inspection tasks one by one, and determine whether the inspection results are abnormal.

[0096] Combine Figure 6Let's look at the execution process: the process starts from "Start", passes through the "Execute Check" link, calls the defined Kettle task, and performs checks one by one according to the previously generated check tasks. At the "Data Quality Check One by One" node, it determines whether the check results are abnormal. If abnormal, it enters the "Determine whether it needs to be sent" node. If the sending conditions are met, it notifies by email through the "Data Quality Check Email Notification" node (corresponding to "Send Email"). At the same time, there is also a "HealthCheck" link in the process to check the overall operating status. The entire process realizes repeated checks and abnormal feedback of cross-system data.

[0097] The present invention also relates to a system for cross-system data quality inspection, which corresponds to the above-mentioned cross-system data quality inspection method and can be understood as a system for implementing the cross-system data quality inspection method, including a cross-system rule configuration module, a dynamic execution scheduling module and a real-time feedback module connected in sequence, and each module works in coordination, wherein:

[0098] The cross-system rule configuration module configures data quality inspection rules for each storage system in the scheduling center domain based on the data content of multiple heterogeneous storage systems in a result-oriented manner through the structured query language SQL. The data quality inspection rules include data sources, abnormal data query logic SQL scripts, comparison operation logic and corresponding judgment criteria;

[0099] The dynamic execution scheduling module uniformly configures execution rules including execution cycle, execution frequency and execution time period in the scheduling center domain; and performs the following operations cyclically at a fixed frequency of seconds:

[0100] Load the latest execution rules and data quality inspection rules; trigger the execution of data quality inspection rules based on the loaded execution rules, and generate independent inspection tasks for each storage system based on the loaded data quality inspection rules. The inspection tasks include instantiated data source information, abnormal data query logic SQL script, comparison operation logic and corresponding judgment criteria; execute each inspection task cyclically within the dispatch center domain. When executing each inspection task, first obtain the potential abnormal data set that meets the query logic based on the instantiated data source information and abnormal data query logic SQL script, and then perform a comparison operation on the potential abnormal data set based on the instantiated comparison operation logic to generate a comparison operation result;

[0101] The real-time feedback module determines that the data quality inspection result is abnormal in the dispatching center domain when the comparison operation result exceeds the instantiated corresponding judgment standard, and then feeds back the abnormal result of each inspection task in real time.

[0102] Furthermore, the dispatch center domain is implemented through open source Kettle;

[0103] The comparison operation logic setting includes a comparison operation symbol and a comparison operation value, wherein the comparison operation symbol includes equality judgment, size judgment, inclusion judgment, regular matching, and null value judgment; the comparison operation value includes a constant, a variable, and another SQL query result;

[0104] The judgment criterion is a threshold or a matching pattern.

[0105] Furthermore, in the dynamic execution scheduling module, the configuration of the execution rules includes independent definitions and combination logic of the execution cycle, execution frequency and execution time period: wherein,

[0106] Execution cycle: It is defined as a single trigger mechanism based on a specified moment or periodic time point; or a compound trigger mechanism based on a specified moment or periodic time point and superimposed on the execution period range;

[0107] Execution frequency: defined as the frequency of loop execution of the check rule within a fixed time period;

[0108] Combinational logic: When the execution cycle is defined as a combination of a compound trigger mechanism and an execution frequency, the execution is cyclically executed at the execution frequency within the execution time period. When the execution cycle is defined as a combination of a single trigger mechanism and an execution frequency, the execution is repeated at the execution frequency after the trigger time point.

[0109] Furthermore, in the real-time feedback module, after determining an abnormality, the following steps are specifically performed:

[0110] If the data quality is judged to be abnormal, an abnormality report will be generated containing the abnormal data details, abnormality type, and abnormality occurrence time;

[0111] Send exception notifications to the corresponding responsible parties through at least one of email, SMS, system messages, and WeChat;

[0112] Record abnormal results in the log system and associate them with the corresponding data quality check rules and execution task ID.

[0113] The method and system for cross-system data quality inspection involved in the present invention are based on the data storage system itself, guided by result inspection, and compared with existing data quality inspection solutions, flexibly define data quality inspection rules across different storage systems, and directly implement cross-system data inspection based on a unified rule execution center. It can greatly improve the inspection capability under the premise of simplifying the data quality inspection process and reducing the cost of data quality inspection, and has significant advantages such as high efficiency, flexibility and strong coverage.

[0114] It should be noted that the specific embodiments described above can enable those skilled in the art to more fully understand the present invention, but do not limit the present invention in any way. Therefore, although this specification has described the present invention in detail with reference to the drawings and embodiments, those skilled in the art should understand that the present invention can still be modified or replaced with equivalents. In short, all technical solutions and improvements that do not depart from the spirit and scope of the present invention should be included in the scope of protection of the patent for the present invention.

Claims

1. A method for cross-system data quality inspection, characterized in that: The following steps are involved: The cross-system rule configuration step configures data quality inspection rules for each storage system in the scheduling center domain based on the data content of multiple heterogeneous storage systems. The data quality inspection rules include data sources, SQL scripts for abnormal data query logic, comparison operation logic, and corresponding judgment criteria. Dynamically execute the scheduling steps and uniformly configure the execution rules including the execution cycle, execution frequency, and execution time period within the scheduling center domain; and cyclically perform the following operations at a fixed frequency of one second: Load the latest execution rules and data quality inspection rules; trigger the execution of data quality inspection rules based on the loaded execution rules, and generate independent inspection tasks for each storage system based on the loaded data quality inspection rules. The inspection tasks include instantiated data source information, abnormal data query logic SQL script, comparison operation logic and corresponding judgment criteria; execute each inspection task cyclically within the dispatch center domain. When executing each inspection task, first obtain the potential abnormal data set that meets the query logic based on the instantiated data source information and abnormal data query logic SQL script, and then perform a comparison operation on the potential abnormal data set based on the instantiated comparison operation logic to generate a comparison operation result; In the real-time feedback step, when the comparison operation result exceeds the corresponding instantiated judgment standard, the data quality inspection result is judged to be abnormal in the dispatching center domain, and the abnormal result of each inspection task is fed back in real time.

2. The method for cross-system data quality inspection according to claim 1, characterized in that: The dispatch center domain is implemented by open source Kettle; The comparison operation logic setting includes a comparison operation symbol and a comparison operation value, wherein the comparison operation symbol includes equality judgment, size judgment, inclusion judgment, regular matching, and null value judgment; the comparison operation value includes a constant, a variable, and another SQL query result; The judgment criterion is a threshold or a matching pattern.

3. The method for cross-system data quality inspection according to claim 1, characterized in that: In the dynamic execution scheduling step, when the latest execution rules and data quality check rules are cyclically loaded at a fixed frequency of seconds, the following steps are specifically performed: Check the timestamp or version number of the rule file and reload it if it changes; During the rule loading process, new rules are subject to syntax verification and dependency checking; If the new rule fails to be verified, the previous version of the rule will continue to be used and the error log will be recorded.

4. The method for cross-system data quality inspection according to any one of claims 1 to 3, characterized in that: The real-time feedback step, after determining an abnormality, specifically includes: If the data quality is judged to be abnormal, an abnormality report will be generated containing the abnormal data details, abnormality type, and abnormality occurrence time; Send exception notifications to the corresponding responsible parties through at least one of email, SMS, system messages, and WeChat; Record abnormal results in the log system and associate them with the corresponding data quality check rules and execution task ID.

5. The cross-system data quality inspection method according to claim 1 or 2, characterized in that: In the cross-system rule configuration step, data quality check rules are configured for each storage system using the structured query language SQL, specifically including: When configuring data quality check rules for relational databases, table joins, aggregate functions, and window functions are supported; When configuring data quality check rules for NoSQL databases, support is provided for JSON path expressions, document nested queries, and key-value pair matching. When configuring data quality check rules for a file system, file format verification, field delimiter verification, and file size threshold verification are supported.

6. The method for cross-system data quality inspection according to claim 3, characterized in that: In the dynamic execution scheduling step, the configuration of the execution rules includes independent definition and combination logic of the execution cycle, execution frequency and execution time period: wherein, Execution cycle: It is defined as a single trigger mechanism based on a specified moment or periodic time point; or a compound trigger mechanism based on a specified moment or periodic time point and superimposed on the execution period range; Execution frequency: defined as the frequency of loop execution of the check rule within a fixed time period; Combinational logic: When the execution cycle is defined as a combination of a compound trigger mechanism and an execution frequency, the execution is cyclically executed at the execution frequency within the execution time period. When the execution cycle is defined as a combination of a single trigger mechanism and an execution frequency, the execution is repeated at the execution frequency after the trigger time point.

7. A system for cross-system data quality inspection, characterized in that: It includes a cross-system rule configuration module, a dynamic execution scheduling module and a real-time feedback module connected in sequence. The cross-system rule configuration module configures data quality inspection rules for each storage system in the scheduling center domain based on the data content of multiple heterogeneous storage systems in a result-oriented manner through the structured query language SQL. The data quality inspection rules include data sources, abnormal data query logic SQL scripts, comparison operation logic and corresponding judgment criteria; The dynamic execution scheduling module uniformly configures execution rules including execution cycle, execution frequency and execution time period in the scheduling center domain; and performs the following operations cyclically at a fixed frequency of seconds: Load the latest execution rules and data quality inspection rules; trigger the execution of data quality inspection rules based on the loaded execution rules, and generate independent inspection tasks for each storage system based on the loaded data quality inspection rules. The inspection tasks include instantiated data source information, abnormal data query logic SQL script, comparison operation logic and corresponding judgment criteria; execute each inspection task cyclically within the dispatch center domain. When executing each inspection task, first obtain the potential abnormal data set that meets the query logic based on the instantiated data source information and abnormal data query logic SQL script, and then perform a comparison operation on the potential abnormal data set based on the instantiated comparison operation logic to generate a comparison operation result; The real-time feedback module determines that the data quality inspection result is abnormal in the dispatching center domain when the comparison operation result exceeds the instantiated corresponding judgment standard, and then feeds back the abnormal result of each inspection task in real time.

8. The cross-system data quality inspection system according to claim 7, characterized in that: The dispatch center domain is implemented by open source Kettle; The comparison operation logic setting includes a comparison operation symbol and a comparison operation value, wherein the comparison operation symbol includes equality judgment, size judgment, inclusion judgment, regular matching, and null value judgment; the comparison operation value includes a constant, a variable, and another SQL query result; The judgment criterion is a threshold or a matching pattern.

9. The cross-system data quality check system according to claim 7 or 8, characterized in that: In the dynamic execution scheduling module, the configuration of the execution rules includes independent definitions and combination logic of the execution cycle, execution frequency and execution time period: Execution cycle: It is defined as a single trigger mechanism based on a specified moment or periodic time point; or a compound trigger mechanism based on a specified moment or periodic time point and superimposed on the execution period range; Execution frequency: defined as the frequency of loop execution of the check rule within a fixed time period; Combinational logic: When the execution cycle is defined as a combination of a compound trigger mechanism and an execution frequency, the execution is cyclically executed at the execution frequency within the execution time period. When the execution cycle is defined as a combination of a single trigger mechanism and an execution frequency, the execution is repeated at the execution frequency after the trigger time point.

10. The cross-system data quality check system according to claim 7 or 8, characterized in that: In the real-time feedback module, after determining an abnormality, the following steps are specifically performed: If the data quality is judged to be abnormal, an abnormality report will be generated containing the abnormal data details, abnormality type, and abnormality occurrence time; Send exception notifications to the corresponding responsible parties through at least one of email, SMS, system messages, and WeChat; Record abnormal results in the log system and associate them with the corresponding data quality check rules and execution task ID.

Images