Data detection method and system in ETL process

Abstract

The invention discloses a data detection method and system in an ETL process, and relates to the technical field of data processing, and the method comprises the steps: carrying out the data conversion of collected source system data through a configurable data conversion rule, and carrying out the data loading of the converted data; performing data consistency detection on the data conversion and the data loading, and formulating a data consistency guarantee mechanism; and carrying out error detection and abnormity alarm on the data in the ETL process. The invention provides a configurable data conversion rule, so that the system can adapt to different service requirements, the flexibility and expandability of the conversion rule are improved, and the maintenance cost is reduced; by designing a data consistency guarantee mechanism, the data consistency in the data conversion and loading process is ensured, and the problem of data consistency errors is effectively prevented; data errors in the ETL process are monitored in real time, related personnel are notified in time through an alarm system, and the stability and reliability of the ETL process are improved.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing, and in particular to a data detection method and system in an ETL process. Background Art

[0002] In the current big data processing environment, the ETL process is the key link in migrating data from the source system to the target system; the role of the ETL process is to uniformly clean, transform and load data from multiple heterogeneous data sources into a centralized data platform such as a data warehouse or data lake, providing high-quality data support for subsequent data analysis and business intelligence applications; however, the traditional ETL process often has some inherent problems and challenges. For example, due to the heterogeneity of data sources, data from different sources have differences in format, encoding, and standards, which can easily lead to data inconsistency during the ETL conversion process. In addition, concurrent processing and task scheduling may also cause data inconsistency; secondly, Traditional ETL processes rely on manual rules and script orchestration, and have weak capabilities for detecting and handling errors and anomalies. Once erroneous data is loaded into downstream systems, it will be difficult to quickly discover and correct it. At the same time, ETL processes are often executed in batches and periodically, which cannot meet the needs of real-time data processing and analysis. In addition, ETL systems are usually specially built, deeply coupled with the company's existing IT infrastructure, and have limited expansion capabilities. In summary, the existence of data inconsistencies and errors will seriously affect the accuracy of subsequent data analysis, and thus affect the quality of business decisions, and may even lead to significant economic losses and legal risks. Therefore, ensuring that the ETL process is efficient, reliable, and real-time is an important issue in the current big data environment.

[0003] However, the current common solutions have many shortcomings, including: traditional ETL tools usually use hard-coded methods to process data conversion rules, lacking flexibility and scalability. Once the data structure of the source system changes, the ETL job needs to be recoded and deployed, which consumes a lot of manpower and time costs; in terms of data consistency, traditional ETL tools usually lack effective consistency detection and assurance mechanisms. Various inconsistencies may occur during data extraction, conversion and loading, which are difficult to detect and correct in time, resulting in reduced data quality; the ETL process involves a large number of complex data operations, and errors and abnormal situations occur from time to time, but most ETL tools lack effective error detection and alarm mechanisms, and are unable to detect and respond to abnormalities in time, resulting in data processing delays or failures. Summary of the invention

[0004] In view of the fact that when processing data in the ETL process, the prior art usually adopts a hard-coded method to process data conversion rules, which lacks flexibility and scalability, lacks an effective consistency detection and guarantee mechanism, and cannot timely discover and respond to anomalies, resulting in data processing delays or failures, the present invention is proposed.

[0005] Therefore, the problem to be solved by the present invention is how to provide a configurable data conversion rule so that the system can adapt to different business needs, design a data consistency guarantee mechanism to ensure the consistency of data during data conversion and loading, monitor data errors in the ETL process in real time, and notify relevant personnel through an alarm system.

[0006] In order to solve the above technical problems, the present invention provides the following technical solutions:

[0007] In the first aspect, an embodiment of the present invention provides a method for data detection in an ETL process, which includes using configurable data conversion rules to convert collected source system data, and loading the converted data; performing data consistency detection on the data conversion and the data loading, and formulating a data consistency assurance mechanism; performing error detection and abnormality alarm on the data in the ETL process.

[0008] As a preferred scheme of the data detection method in the ETL process of the present invention, wherein: the formulation of the data consistency assurance mechanism includes a data verification algorithm and a data comparison algorithm; the data consistency detection includes the following steps: using the data verification algorithm to perform consistency verification on the collected source system data; using the data comparison algorithm to perform consistency comparison on the collected source system data and the converted data; based on the data verification algorithm and the data comparison algorithm, the results of data consistency assurance are output and a data consistency assurance strategy is formulated.

[0009] As a preferred solution of the data detection method in the ETL process of the present invention, the calculation formula of the data verification algorithm is as follows:

[0010]

[0011] Among them, H(d i ) is the i-th source system data d i Hash value of ;d ik is the i-th source system data d i The kth element in ; K is the i-th source system data d i The calculation formula of the data comparison algorithm is as follows:

[0012]

[0013] Among them, D(d j ,t j ) is the difference comparison value between the j-th source system data dj and the j-th converted data tj; L is the j-th converted data t j Dimension; djl is the jth source system data d j The lth element in t jl is the transformed data t of the jth item j The lth element in ; ε is a positive number.

[0014] As a preferred solution of the data detection method in the ETL process of the present invention, the data consistency guarantee strategy includes: if the hash value H(d i ) is less than the first threshold, it is determined that the collected source system data is consistent and a data conversion step is performed; if the hash value H(d i ) is greater than or equal to the first threshold, it is determined that the collected source system data is inconsistent, the source system data is collected again and a consistency check is performed. If the hash value H(d i ) is greater than the first threshold value for N consecutive times, the data source is checked and then the source system data is collected; otherwise, the subsequent data conversion steps are performed; if the differential comparison value D(d j ,t j ) is less than the second threshold, it is determined that the converted data is consistent with the source system data, and the data loading step is performed; if the differential comparison value D(d j ,t j ) is greater than or equal to the second threshold, it is determined that the converted data is inconsistent with the source system data, and the data conversion step is repeated and the difference comparison value D (d j ,t j ) judgment, if the difference is greater than the comparison value D(d j ,t j ) is greater than the second threshold value M times in a row, the source system data is determined to be wrong and the source system data is collected again; otherwise, the data loading step is performed.

[0015] As a preferred solution of the data detection method in the ETL process of the present invention, the error detection and abnormal alarm include the following steps: using real-time data stream analysis technology to monitor the data in the ETL process in real time; judging the abnormal type of the monitored data and building an abnormal detection model; formulating an alarm mechanism based on the abnormal type judgment result; the abnormal type includes data missing, data abnormality and data duplication; the calculation formula of the data missing is as follows:

[0016]

[0017] Among them, M g is the data missing rate of the g-th data monitoring point; x gw is the wth data monitoring result of the gth data monitoring point; n is the number of monitoring times; the calculation formula of the data anomaly is as follows:

[0018]

[0019] Among them, E g is the data anomaly degree of the g-th data monitoring point; is the mean value of the g-th data monitoring point; σ g is the standard deviation of the g-th data monitoring point; the calculation formula for the data repetition is as follows:

[0020]

[0021] Among them, R g is the data repetition rate of the g-th data monitoring point; x gw is the wth data monitoring result of the gth data monitoring point; x gq is the qth data monitoring result of the gth data monitoring point.

[0022] As a preferred solution of the data detection method in the ETL process of the present invention, the alarm mechanism includes: if the data missing rate M g If the value is not equal to Q, it is determined that data is missing, and the system immediately issues an alarm, sends an email and SMS with data missing information to the operation and maintenance personnel, and records the error log; if the data abnormality level is E g If the data anomaly level is greater than the third threshold, it is determined that data anomaly occurs. The system continuously monitors the location of the data anomaly and sends emails and text messages with data anomaly information to the operation and maintenance personnel, and records the error information. g If the data repetition rate R is less than or equal to the third threshold, it is determined that no data anomaly occurs, and the system continues to monitor the data and make anomaly judgments; g If the value is greater than the fourth threshold, it is determined that data duplication occurs, and the system re-processes the data at the corresponding position and sends an email and text message with data duplication information to the operation and maintenance personnel.

[0023] As a preferred solution of the data detection method in the ETL process of the present invention, wherein: the collected source system data is extracted from relational databases, non-relational databases and file systems using distributed data extraction technology; the data conversion is to convert the source system data using configurable data conversion rules; the configurable data conversion rules include data parsing rules, data filtering rules, data cleaning rules, data conversion rules, data calculation rules, data association rules and data loading rules; the data loading is to load the converted data into the target system using parallel loading technology and data sharding technology.

[0024] In the second aspect, in order to further solve the security problems existing in data processing, the present invention provides a data detection system in the ETL process in an embodiment, which includes: a data extraction module, which is used to extract data from the source system using distributed data extraction technology, and process multiple data sources and data formats; a data conversion module, which is used to convert the collected source system data using configurable data conversion rules; a data loading module, which is used to load the converted data into the target system using parallel loading technology and data sharding technology, and perform data sharding and partitioning; a consistency assurance module, which is used to use a data consistency assurance mechanism to ensure the consistency of data in data extraction, data conversion and data loading; a detection alarm module, which is used to use real-time data stream analysis technology to monitor data errors in the ETL process in real time, and notify relevant personnel through the alarm system.

[0025] In a third aspect, an embodiment of the present invention provides a computer device, comprising a memory and a processor, wherein the memory stores a computer program, wherein: when the computer program is executed by the processor, any step of a data detection method in an ETL process as described in the first aspect of the present invention is implemented.

[0026] In a fourth aspect, an embodiment of the present invention provides a computer-readable storage medium having a computer program stored thereon, wherein: when the computer program is executed by a processor, any step of a data detection method in an ETL process as described in the first aspect of the present invention is implemented.

[0027] The beneficial effects of the present invention are as follows: the present invention provides a configurable data conversion rule, so that the system can adapt to different business needs, improves the flexibility and scalability of the conversion rule, and reduces the maintenance cost; by designing a data consistency guarantee mechanism based on rules and algorithms, the consistency of data during data conversion and loading is ensured, and data consistency errors are effectively prevented, thereby ensuring data quality; real-time data stream analysis technology is introduced to monitor data errors in the ETL process in real time, and relevant personnel are notified in time through the alarm system, thereby improving the stability and reliability of the ETL process. BRIEF DESCRIPTION OF THE DRAWINGS

[0028] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the following briefly introduces the drawings required for describing the embodiments. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without creative work. Among them:

[0029] Figure 1 This is a flow chart of data consistency assurance and error detection in the ETL process in Example 1. DETAILED DESCRIPTION

[0030] In order to make the above-mentioned objects, features and advantages of the present invention more obvious and easy to understand, the specific implementation methods of the present invention are described in detail below in conjunction with the accompanying drawings.

[0031] In the following description, many specific details are set forth to facilitate a full understanding of the present invention, but the present invention may also be implemented in other ways different from those described herein, and those skilled in the art may make similar generalizations without violating the connotation of the present invention. Therefore, the present invention is not limited to the specific embodiments disclosed below.

[0032] Secondly, the term "one embodiment" or "embodiment" as used herein refers to a specific feature, structure, or characteristic that may be included in at least one implementation of the present invention. The term "in one embodiment" that appears in different places in this specification does not necessarily refer to the same embodiment, nor does it refer to a separate or selective embodiment that is mutually exclusive with other embodiments.

[0033] Example 1

[0034] Reference Figure 1 , which is the first embodiment of the present invention, and provides a data detection method in an ETL process, comprising the following steps:

[0035] S1: Use configurable data conversion rules to convert the collected source system data, and load the converted data.

[0036] Preferably, the source system data collected is configured based on the data source parameters of a graphical interface, and distributed data extraction technology is used to extract data from relational databases, non-relational databases and file systems, thereby improving the efficiency and speed of data extraction, and supporting the adaptation of multiple data sources and data formats to improve the versatility of the system.

[0037] Specifically, the data source parameters include data source connection information and collection cycle.

[0038] Specifically, data extraction includes incremental collection mode and full collection mode.

[0039] Specifically, data formats include structured data, semi-structured data, and unstructured data.

[0040] Preferably, data conversion is based on a graphical interface and drag-and-drop operation, using configurable data conversion rules to convert the collected source system data, including cleaning, aggregation and calculation, to facilitate users to configure and adjust conversion rules, support custom functions and scripts, and thus meet complex business needs.

[0041] Specifically, the configurable data transformation rules include data parsing rules, data filtering rules, data cleaning rules, data conversion rules, data calculation rules, data association rules, and data loading rules.

[0042] Preferably, data loading utilizes parallel loading technology and data sharding technology to load the converted data into the target system, including data warehouse and data lake, to increase the speed of data loading, support data sharding and partitioning, and improve the flexibility and scalability of data loading.

[0043] Specifically, by providing a configurable data conversion rule, the system can adapt to different business needs, improve the flexibility of the data conversion process and the versatility of the system. At the same time, based on the graphical interface and drag-and-drop operation, it simplifies the rule configuration process, lowers the user threshold, and improves the user experience.

[0044] Further, such as Figure 1 The figure shows the implementation process of data consistency assurance and error detection of the present invention. The distributed data extraction technology is used to extract data to obtain source system data, and the configurable data conversion rules are used to convert and load the collected source system data. The data consistency assurance result is output based on the data verification algorithm and the data comparison algorithm, and a data consistency assurance strategy is formulated. The real-time data stream analysis technology is used to monitor the data in the ETL process in real time and judge the abnormal type, including data missing, data abnormality and data duplication, and an alarm mechanism is formulated according to the abnormal judgment result.

[0045] S2: Perform data consistency checks on data conversion and data loading, and develop a data consistency assurance mechanism.

[0046] Preferably, the data consistency assurance mechanism is formulated to include a data verification algorithm and a data comparison algorithm.

[0047] Specifically, the data consistency check includes the following steps: using a data verification algorithm to perform consistency verification on the collected source system data to ensure the integrity and consistency of the data.

[0048] Use data comparison algorithms to compare the collected source system data and converted data for consistency to ensure data accuracy.

[0049] Based on the data verification algorithm and data comparison algorithm, output the results of data consistency assurance and formulate a data consistency assurance strategy.

[0050] Furthermore, the data verification algorithm includes the following steps: Hash processing is performed on the source system data. The specific formula is as follows:

[0051]

[0052] Among them, h ik is the i-th source system data d i The value obtained after hashing the kth element in d ik is the i-th source system data d i The kth element in .

[0053] Calculation is performed based on the hashed source system data to obtain a single hash value, which indicates the integrity and consistency of the corresponding data. The specific formula is as follows:

[0054]

[0055] Among them, H(d i ) is the i-th source system data d i The hash value of K is the i-th source system data d i Dimension.

[0056] Specifically, the calculation formula of the data verification algorithm is as follows:

[0057]

[0058] Among them, H(d i ) is the i-th source system data d i Hash value of ;d ik is the i-th source system data d i The kth element in ; K is the i-th source system data d i Dimension.

[0059] Furthermore, the data comparison algorithm includes the following steps: Calculate the difference between the source system data and the converted data, the specific formula is as follows:

[0060] s jl =d jl -t jl

[0061] Among them, s jl is the jth source system data d j The lth element in and the jth transformed data t j The difference between the lth elements in jl is the jth source system data d j The lth element in t jl is the transformed data t of the jth item j The lth element in .

[0062] The obtained differences are normalized, and the specific formula is as follows:

[0063]

[0064] in, is the normalized difference; ε is a positive number used to prevent the denominator from being zero.

[0065] The normalized difference is calculated to obtain the difference comparison value. The specific formula is as follows:

[0066]

[0067] Among them, D(d j ,t j ) is the difference comparison value between the j-th source system data dj and the j-th converted data tj; L is the j-th converted data t j The dimension of the j-th source system data d j Dimension.

[0068] Specifically, the calculation formula of the data comparison algorithm is as follows:

[0069]

[0070] Among them, D(d j ,t j ) is the difference comparison value between the j-th source system data dj and the j-th converted data tj; L is the j-th converted data t j The dimension of the j-th source system data d j Dimension; d jl is the jth source system data d j The lth element in t jl is the transformed data t of the jth item j The lth element in ; ε is a positive number used to prevent the denominator from being zero.

[0071] Preferably, the data consistency guarantee strategy includes: if the hash value H(d i ) is less than the first threshold, the collected source system data is determined to be consistent, and a data conversion step is performed.

[0072] If the hash value H(d i ) is greater than or equal to the first threshold, it is determined that the collected source system data is inconsistent, the source system data is collected again and a consistency check is performed. If the hash value H(d i ) is greater than the first threshold value N times in a row, the data source is checked before collecting the source system data, thereby avoiding checking a huge data source and causing a waste of resources; otherwise, subsequent data conversion steps are performed.

[0073] If the difference is compared with the value D(d j ,t j) is less than the second threshold, it is determined that the converted data is consistent with the source system data, and the data loading step is performed.

[0074] If the difference is compared with the value D(d j ,t j ) is greater than or equal to the second threshold, it is determined that the converted data is inconsistent with the source system data, and the data conversion step is repeated and the difference comparison value D (d j ,t j ) judgment, if the difference is greater than the comparison value D(d j ,t j ) is greater than the second threshold value M times in a row, the source system data is determined to be wrong, and the source system data is re-collected, thereby reducing resource consumption and improving the efficiency of consistency judgment; otherwise, the data loading step is performed.

[0075] Specifically, by performing statistical analysis on the historical distribution of historical hash values ​​and differential values ​​of source data, initial thresholds of the first threshold and the second threshold are determined and tested, and the first threshold and the second threshold are adjusted according to actual data and user requirements to obtain the optimal threshold.

[0076] Specifically, by using data verification algorithms and data comparison algorithms to detect data consistency and formulating different protection strategies for inconsistencies, the effectiveness of detection and prevention capabilities are improved, ensuring data integrity and consistency and reducing resource consumption.

[0077] S3: Perform error detection and anomaly alarm on data in the ETL process.

[0078] Preferably, error detection and abnormality alarm include the following steps: using real-time data stream analysis technology to monitor the data in the ETL process in real time.

[0079] Determine the abnormal type of the monitored data and build an anomaly detection model.

[0080] An alarm mechanism is established based on the abnormal type judgment results to achieve timely detection and processing of data errors.

[0081] Specifically, the anomaly types include missing data, abnormal data, and duplicate data.

[0082] Furthermore, the calculation formula for missing data is as follows:

[0083]

[0084] Among them, M g is the data missing rate of the g-th data monitoring point. If the data missing rate M g If it is equal to 1, the data is not missing, otherwise the data is missing; gwis the wth data monitoring result of the gth data monitoring point; n is the number of monitoring times.

[0085] Furthermore, the calculation formula for data anomaly is as follows:

[0086]

[0087] Among them, E g is the data anomaly degree of the g-th data monitoring point; is the mean value of the g-th data monitoring point; σ g is the standard deviation of the g-th data monitoring point.

[0088] Furthermore, the calculation formula for data duplication is as follows:

[0089]

[0090] Among them, R g is the data repetition rate of the g-th data monitoring point; x gw is the wth data monitoring result of the gth data monitoring point; x gq is the qth data monitoring result of the gth data monitoring point.

[0091] Specifically, the alarm mechanism includes: if the data missing rate M g If it is not equal to 1, it is determined that data is missing. The system immediately issues an alarm, sends an email and text message with data missing information to the operation and maintenance personnel, and records an error log.

[0092] If the data abnormality level E g If the value is greater than the third threshold, it is determined that a data anomaly has occurred. The system continuously monitors the location of the data anomaly and sends emails and text messages with data anomaly information to the operation and maintenance personnel, and records the error information.

[0093] If the data abnormality level E g If the value is less than or equal to the third threshold, it is determined that no data anomaly occurs, and the system continues to monitor the data and make anomaly judgments.

[0094] If the data repetition rate R g If the value is greater than the fourth threshold, it is determined that data duplication occurs, and the system re-processes the data at the corresponding position and sends an email and text message with data duplication information to the operation and maintenance personnel.

[0095] Specifically, for the third threshold and the fourth threshold, the range of the threshold is adjusted according to the specific business scenario, and the threshold is adjusted according to the amount of data and the tolerance for data anomalies to obtain the final third threshold and the fourth threshold.

[0096] Specifically, by utilizing real-time data stream analysis technology, it is possible to monitor data in the ETL process in real time, formulate corresponding processing strategies for different types of exceptions, promptly discover errors and abnormal situations, and then quickly notify operation and maintenance personnel to improve fault handling efficiency.

[0097] The present embodiment also provides a data detection system in the ETL process, including: a data extraction module, which is used to extract data from the source system using distributed data extraction technology, and process multiple data sources and data formats; a data conversion module, which is used to convert the collected source system data using configurable data conversion rules; a data loading module, which is used to load the converted data into the target system using parallel loading technology and data sharding technology, and perform data sharding and partitioning; a consistency assurance module, which is used to use a data consistency assurance mechanism to ensure the consistency of data in data extraction, data conversion and data loading; a detection alarm module, which is used to use real-time data stream analysis technology to monitor data errors in the ETL process in real time, and notify relevant personnel through an alarm system.

[0098] This embodiment also provides a computer device, which is applicable to a data detection method in an ETL process, including: a memory and a processor; the memory is used to store computer executable instructions, and the processor is used to execute computer executable instructions to implement a data detection method in an ETL process as proposed in the above embodiment.

[0099] The computer device may be a terminal, and the computer device includes a processor, a memory, a communication interface, a display screen and an input device connected via a system bus. The processor of the computer device is used to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system and a computer program. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The communication interface of the computer device is used to communicate with an external terminal in a wired or wireless manner, and the wireless manner can be achieved through WIFI, an operator network, NFC (near field communication) or other technologies. The display screen of the computer device may be a liquid crystal display screen or an electronic ink display screen, and the input device of the computer device may be a touch layer covering the display screen, or a key, trackball or touchpad provided on the housing of the computer device, or an external keyboard, touchpad or mouse, etc.

[0100] This embodiment also provides a storage medium on which a computer program is stored. When the program is executed by a processor, a data detection method in an ETL process as proposed in the above embodiment is implemented; the storage medium can be implemented by any type of volatile or non-volatile storage device or a combination thereof, such as static random access memory (Static Random Access Memory, referred to as SRAM), electrically erasable programmable read-only memory (Electrically Erasable Programmable Read-Only Memory, referred to as EEPROM), erasable programmable read-only memory (Erasable Programmable Read Only Memory, referred to as EPROM), programmable read-only memory (Programmable Red-Only Memory, referred to as PROM), read-only memory (Read-Only Memory, referred to as ROM), magnetic storage, flash memory, magnetic disk or optical disk.

[0101] In summary, the present invention provides a configurable data conversion rule, so that the system can adapt to different business needs, improve the flexibility and scalability of the conversion rule, and reduce the maintenance cost; by designing a data consistency guarantee mechanism based on rules and algorithms, the consistency of data during data conversion and loading is ensured, and data consistency errors are effectively prevented, thereby ensuring data quality; real-time data stream analysis technology is introduced to monitor data errors in the ETL process in real time, and relevant personnel are notified in time through the alarm system, thereby improving the stability and reliability of the ETL process.

[0102] Example 2

[0103] Referring to Table 1 and Table 2, which are the second embodiment of the present invention, this embodiment is different from the first embodiment in that, in order to verify its beneficial effects, experimental comparison data between the present invention and the prior art are provided.

[0104] This example tests a data collection and data conversion scenario of a certain enterprise, and processes the data by using configurable data conversion rules, including parsing MongoDB's BSON file into structured data, removing illegal characters in the order, unifying the case, converting the order information into Parquet column storage format, loading the final data into Apache Hive, and then using the data consistency guarantee mechanism proposed in the present invention to perform consistency analysis on the data, and compare it with the prior art, wherein the judgment data of the data consistency guarantee mechanism of the present invention is shown in Table 1.

[0105] Table 1 Judgment data table of data consistency guarantee mechanism

[0106] Experimental subjects Source system data Transformed data Differential contrast value Data consistency Order data 0xA37CE928 0xA37CE928 0.0001 Consistency Sales data 0x3F2D18BE 0x3F2D18BE 0.0008 Consistency Log data 0xEA63B97F 0xEA63B980 0.2034 Inconsistency Membership data 0x7CD9105A 0x7CD9105A 0.0000 Consistency ... ... ... ... ... Inventory data 0x91AF7D3C 0x91AF7D3D 0.0121 Consistency

[0107] It can be seen from the above table that the present invention compares the hash values ​​of the source system data and the converted data and calculates the corresponding differential comparison values, thereby analyzing the consistency of the data during the data conversion process. This shows that the present invention can effectively maintain the accuracy and integrity of the data, improve the accuracy of data conversion, and quickly identify abnormal data parts through the corresponding data consistency assurance strategy to reduce resource consumption.

[0108] As shown in Table 2, by comparing the data detection efficiency of the present invention with that of the prior art, it is shown that the present invention uses a data consistency assurance mechanism to perform data consistency detection on the data conversion and data loading processes, and uses an anomaly detection model to perform error detection and anomaly alarm on the data in the ETL process, thereby effectively improving data integrity and accuracy, and improving the stability and reliability of the ETL process.

[0109] Table 2 Anomaly detection efficiency comparison table

[0110] Experimental subjects Completeness test coverage Accuracy detection coverage Method of the present invention 99.8％ 99.6％ Kettle 97.2％ 95.7％ Informatica PowerCenter 98.1％ 97.3％

[0111] It should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention rather than to limit it. Although the present invention has been described in detail with reference to the preferred embodiments, those skilled in the art should understand that the technical solutions of the present invention can be modified or replaced by equivalents without departing from the spirit and scope of the technical solutions of the present invention, which should be included in the scope of the claims of the present invention.

Claims

1. A data detection method in an ETL process, characterized by: include: Use configurable data conversion rules to convert the collected source system data, and load the converted data; Performing data consistency detection on the data conversion and the data loading, and formulating a data consistency guarantee mechanism; Perform error detection and anomaly alarm on data in the ETL process.

2. The data detection method in the ETL process according to claim 1, characterized in that: The data consistency guarantee mechanism comprises a data verification algorithm and a data comparison algorithm; The data consistency detection comprises the following steps: Performing consistency check on the collected source system data using a data verification algorithm; Using a data comparison algorithm to compare the collected source system data and the converted data for consistency; Based on the data verification algorithm and the data comparison algorithm, the result of data consistency assurance is output and a data consistency assurance strategy is formulated.

3. The data detection method in the ETL process according to claim 2, characterized in that: The calculation formula of the data verification algorithm is as follows: Among them, H(d i ) is the i-th source system data d i Hash value of ;d ik is the i-th source system data d i The kth element in ; K is the i-th source system data d i Dimensions; The calculation formula of the data comparison algorithm is as follows: Among them, D(d j ,t j ) is the difference comparison value between the j-th source system data dj and the j-th converted data tj; L is the j-th converted data t j Dimension; d jl is the jth source system data d j The lth element in t jl is the transformed data t of the jth item j The lth element in ; ε is a positive number.

4. The data detection method in the ETL process according to claim 3, characterized in that: The data consistency guarantee strategy includes: If the hash value H(d i ) is less than the first threshold, it is determined that the collected source system data is consistent, and a data conversion step is performed; If the hash value H(d i ) is greater than or equal to the first threshold, it is determined that the collected source system data is inconsistent, and the source system data is collected again and a consistency check is performed. If the hash value H(d i ) is greater than the first threshold value N times in a row, the data source is checked and then the source system data is collected; otherwise, the subsequent data conversion steps are performed; If the difference is compared with the value D(d j ,t j ) is less than the second threshold, it is determined that the converted data is consistent with the source system data, and a data loading step is performed; If the difference is compared with the value D(d j ,t j ) is greater than or equal to the second threshold, it is determined that the converted data is inconsistent with the source system data, and the data conversion step is repeated and the difference comparison value D (d j ,t j ) judgment, if the difference is greater than the comparison value D(d j ,t j ) is greater than the second threshold value M times in a row, the source system data is determined to be wrong and the source system data is collected again; otherwise, the data loading step is performed.

5. The data detection method in the ETL process according to claim 4, characterized in that: The error detection and abnormality alarm comprises the following steps: Use real-time data stream analysis technology to monitor data in the ETL process in real time; Determine the abnormal type of the monitored data and build an anomaly detection model; Develop an alarm mechanism based on the abnormal type judgment results; The abnormal types include missing data, abnormal data and repeated data; The calculation formula for the missing data is as follows: Among them, M g is the data missing rate of the g-th data monitoring point; x gw is the wth data monitoring result of the gth data monitoring point; n is the number of monitoring times; The calculation formula of the data anomaly is as follows: Among them, E g is the data anomaly degree of the g-th data monitoring point; is the mean value of the g-th data monitoring point; σ g is the standard deviation of the g-th data monitoring point; The calculation formula for the data duplication is as follows: Among them, R g is the data repetition rate of the g-th data monitoring point; x gw is the wth data monitoring result of the gth data monitoring point; x gq is the qth data monitoring result of the gth data monitoring point.

6. The data detection method in the ETL process according to claim 5, characterized in that: The alarm mechanism includes: If the data missing rate M g If it is not equal to Q, it is determined that data is missing, and the system immediately issues an alarm, sends an email and SMS with data missing information to the operation and maintenance personnel, and records the error log; If the data abnormality level E g If the value is greater than the third threshold, it is determined that data anomaly occurs. The system continuously monitors the location of the data anomaly and sends emails and text messages with data anomaly information to the operation and maintenance personnel, and records the error information. If the data abnormality level E g If the value is less than or equal to the third threshold, it is determined that no data anomaly occurs, and the system continues to monitor the data and make anomaly judgments; If the data repetition rate R g If the value is greater than the fourth threshold, it is determined that data duplication occurs, and the system re-processes the data at the corresponding position and sends an email and text message with data duplication information to the operation and maintenance personnel.

7. The data detection method in the ETL process according to claim 6, characterized in that: The source system data collected is extracted from relational databases, non-relational databases and file systems using distributed data extraction technology; The data conversion is to convert the source system data using configurable data conversion rules; The configurable data conversion rules include data parsing rules, data filtering rules, data cleaning rules, data conversion rules, data calculation rules, data association rules and data loading rules; The data loading is to load the converted data into the target system by using parallel loading technology and data sharding technology.

8. A data detection system in an ETL process, based on a data detection method in an ETL process according to any one of claims 1 to 7, characterized in that: include, Data extraction module, used to extract data from source systems using distributed data extraction technology and process multiple data sources and data formats; A data conversion module is used to convert the collected source system data using configurable data conversion rules; The data loading module is used to load the converted data into the target system by using parallel loading technology and data sharding technology, and to perform data sharding and partitioning; The consistency assurance module is used to ensure the consistency of data in data extraction, data conversion and data loading by using the data consistency assurance mechanism; The detection and alarm module is used to monitor data errors in the ETL process in real time using real-time data stream analysis technology and notify relevant personnel through the alarm system.

9. A computer device comprising a memory and a processor, wherein the memory stores a computer program, wherein: When the processor executes the computer program, the steps of the data detection method in an ETL process described in any one of claims 1 to 7 are implemented.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the steps of a data detection method in an ETL process according to any one of claims 1 to 7 are implemented.

Images