A data detection method and system in an ETL process

Abstract

The application discloses a kind of data detection method and system in ETL process, it is related to data processing technical field, including using configurable data conversion rule to the source system data of acquisition carries out data conversion, and data loading is carried out to the data after conversion;Data consistency detection is carried out to the data conversion and the data loading, and data consistency guarantee mechanism is formulated;Error detection and abnormal alarm are carried out to the data in ETL process.The application provides a configurable data conversion rule, so that the system can adapt to different business needs, improves the flexibility and scalability of conversion rule, reduces maintenance cost;By designing data consistency guarantee mechanism, ensure the consistency of data in the process of data conversion and loading, effectively prevent data consistency error problem;Real-time monitoring is carried out to the data error in ETL process, and relevant personnel is notified in time through alarm system, improves the stability and reliability of ETL process.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of data processing, and particularly relates to a data detection method and system in an ETL process. BACKGROUND

[0002] In the current big data processing environment, the ETL process is a key link for migrating data from a source system to a target system; the role of the ETL process is to uniformly clean, convert and load data from multiple heterogeneous data sources into a centralized data platform of a data warehouse or a data lake, to provide high-quality data support for subsequent data analysis and business intelligence applications; however, traditional ETL processes often have some inherent problems and challenges, for example, due to the heterogeneity of data sources, different sources of data have differences in format, coding and standards, which can easily lead to data inconsistency in the ETL conversion process, in addition, concurrent processing and task scheduling can also cause data inconsistency; secondly, traditional ETL processes rely on manual rules and script arrangement, and have weak detection and processing capabilities for errors and exceptions, once the error data is loaded into the downstream system, it will be difficult to quickly discover and correct; at the same time, the ETL process is usually executed in batches and periodically, which cannot meet the needs of real-time data processing and analysis, and the ETL system is usually specially constructed and deeply coupled with the existing IT infrastructure of the enterprise, and has limited expansion capability; in summary, the existence of data inconsistency and errors can seriously affect the accuracy of subsequent data analysis, and thus affect the quality of business decisions, and even may cause significant economic losses and legal risks, therefore, ensuring efficient, reliable and real-time ETL process 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 handle data conversion rules, lack of flexibility and scalability, and need to re-code and deploy ETL jobs once the data structure of the source system changes, which consumes a lot of manpower and time cost; in terms of data consistency, traditional ETL tools usually lack effective consistency detection and protection mechanisms, and data may appear in inconsistent situations during extraction, conversion and loading, which is difficult to be discovered and corrected in time, resulting in a decrease in data quality; the ETL process involves a large number of complex data operations, and errors and exceptions occur from time to time, but most ETL tools lack effective error detection and alarm mechanisms, and cannot discover and respond to exceptions in time, resulting in data processing delay or failure. SUMMARY

[0004] In view of the problems in the prior art that the data conversion rules are usually handled by hard coding in the process of processing data in the ETL process, lack of flexibility and scalability, lack of effective consistency detection and protection mechanisms, and inability to discover and respond to exceptions in time, resulting in data processing delay or failure, the present application is proposed.

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

[0006] To solve the above technical problems, the present application provides the following technical solutions:

[0007] In a first aspect, the embodiments of the present application provide a data detection method in an ETL process, which comprises: using a configurable data conversion rule to convert source system data collected, and loading the converted data; detecting the data consistency of the data conversion and the data loading, and formulating a data consistency guarantee mechanism; detecting errors in the data in the ETL process and giving an abnormal alarm.

[0008] As a preferred scheme of the data detection method in the ETL process of the present application, the data consistency guarantee mechanism comprises a data verification algorithm and a data comparison algorithm; the data consistency detection comprises the following steps: using the data verification algorithm to verify the consistency of the collected source system data; using the data comparison algorithm to compare the consistency of the collected source system data and the converted data; outputting the results of the data consistency guarantee based on the data verification algorithm and the data comparison algorithm and formulating a data consistency guarantee strategy.

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

[0010]

[0011] wherein, H(d i ) is the hash value of the i th source system data d i ; d ik is the k th element in the i th source system data d i ; K is the dimension of the i th source system data d i ; and the calculation formula of the data comparison algorithm is as follows:

[0012]

[0013] wherein, D(d j , t j ) is the difference comparison value of the j th source system data dj and the j th converted data tj; L is the dimension of the j th converted data t j ; djl is the lth element in the jth source system data d j ; t jl is the lth element in the jth converted data t j ; and ε is a positive number.

[0014] As a preferred solution of the data detection method in the ETL process, the data consistency guarantee strategy comprises: if the hash value H(d i ) is less than a first threshold value, it is determined that the collected source system data has consistency, and the data conversion step is performed; if the hash value H(d i ) is greater than or equal to the first threshold value, it is determined that the collected source system data does not have consistency, the source system data is re-collected and consistency verification 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 step is performed; if the difference comparison value D(d j , t j ) is less than a second threshold value, it is determined that the converted data and the source system data have consistency, and the data loading step is performed; if the difference comparison value D(d j , t j ) is greater than or equal to the second threshold value, it is determined that the converted data and the source system data do not have consistency, the data conversion step is re-performed and the difference comparison value D(d j , t j ) is judged, if the difference comparison value D(d j , t j ) is greater than the second threshold value for M consecutive times, it is determined that the source system data is incorrect, and the source system data is re-collected; otherwise, the data loading step is performed.

[0015] As a preferred solution of the data detection method in the ETL process, the error detection and abnormality alarm comprises the following steps: real-time data stream analysis technology is used to monitor the data in the ETL process in real time; the monitored data is subjected to abnormality type judgment, and an abnormality detection model is constructed; an alarm mechanism is formulated based on the abnormality type judgment result; the abnormality type comprises data loss, data abnormality and data duplication; the calculation formula of the data loss is as follows:

[0016]

[0017] wherein, M g is the data loss rate of the gth data monitoring point; x gw is the wth data monitoring result of the gth data monitoring point; and n is the monitoring number; the calculation formula of the data abnormality is as follows:

[0018]

[0019] wherein, E g is the data abnormality degree of the gth data monitoring point; is the mean value of the gth data monitoring point;σ g is the standard deviation of the gth data monitoring point;the calculation formula of the data repetition is as follows:

[0020]

[0021] wherein, R g is the data repetition rate of the gth 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 scheme of the data detection method in the ETL process, wherein: the alarm mechanism comprises: if the data missing rate M g is not equal to Q, it is determined that the data missing situation occurs, the system immediately alarms, sends the data missing information email and short message to the operation and maintenance personnel and records the error log;if the data abnormality degree E g is greater than the third threshold value, it is determined that the data abnormality situation occurs, the system continuously monitors the position of the data abnormality and sends the data abnormality information email and short message to the operation and maintenance personnel, and records the error information;if the data abnormality degree E g is less than or equal to the third threshold value, it is determined that the data abnormality situation does not occur, the system continues to monitor the data and judges the abnormality;if the data repetition rate R g is greater than the fourth threshold value, it is determined that the data repetition situation occurs, the system re-performs the data processing operation of the corresponding position, and sends the data repetition information email and short message to the operation and maintenance personnel.

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

[0024] In a second aspect, to further solve the security problem in data processing, the embodiment provides a data detection system in an ETL process, which comprises: a data extraction module, configured to extract data from a source system by using a distributed data extraction technology, and process various data sources and data formats; a data conversion module, configured to convert the collected source system data by using configurable data conversion rules; a data loading module, configured to load the converted data into a target system by using a parallel loading technology and a data sharding technology, and perform data sharding and partitioning; a consistency guarantee module, configured to guarantee the consistency of data in the data extraction, data conversion and data loading by using a data consistency guarantee mechanism; and a detection alarm module, configured to monitor data errors in the ETL process in real time by using a real-time data stream analysis technology, and notify relevant personnel through an alarm system.

[0025] In a third aspect, the embodiment provides a computer device, comprising a memory and a processor, and the memory stores a computer program, wherein the computer program is executed by the processor to implement any step of the data detection method in the ETL process according to the first aspect of the present application.

[0026] In a fourth aspect, the embodiment provides a computer readable storage medium, which stores a computer program, wherein the computer program is executed by a processor to implement any step of the data detection method in the ETL process according to the first aspect of the present application.

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

[0028] In order to more clearly illustrate the technical solutions of the embodiments of the present application, the drawings needed in the embodiment description will be briefly introduced as follows. Obviously, the drawings in the following description are only some embodiments of the present application, and other drawings can be obtained by those skilled in the art without creative labor. Among them:

[0029] Figure 1 The flow chart of the data consistency guarantee and error detection in the ETL process in embodiment 1. DETAILED DESCRIPTION

[0030] In order to make the above objectives, features and advantages of the present application more apparent, the specific embodiments of the present application will be described in detail below with reference to the accompanying drawings.

[0031] In the following description, numerous specific details are set forth in order to provide a thorough understanding of the present application. The present application, however, can be practiced in a variety of ways beyond the specific embodiments described herein without departing from the scope of the present application, and it is understood that it covers all techniques falling within the scope of the application sufficient to convey its principles to those skilled in the art. Therefore, the present application is not limited to the specific embodiments disclosed below.

[0032] Secondly, the term "one embodiment" or "an embodiment" as used herein means that a particular implementation can include a particular feature, structure, or characteristic, but every embodiment can not necessarily include the particular feature, structure, or characteristic. Furthermore, the following terms, as used in the description and associated claims, shall not be limited to the specifically recited uses.

[0033] Embodiment 1

[0034] With reference to Figure 1 For the first embodiment of the present application, the embodiment provides a data detection method in an ETL process, comprising the following steps:

[0035] S1: using configurable data conversion rules to perform data conversion on the collected source system data, and performing data loading on the converted data.

[0036] Preferably, the collected source system data is configured with data source parameters based on 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, supporting the adaptation of multiple data sources and data formats, and improving the versatility of the system.

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

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

[0039] Specifically, the data format includes structured data, semi-structured data and unstructured data.

[0040] Preferably, the data conversion is based on a graphical interface and a drag-and-drop operation, and configurable data conversion rules are used to perform data conversion on the collected source system data, including cleaning, aggregation and calculation, which facilitates user configuration and adjustment of conversion rules, supports custom functions and scripts, and thus meets complex business requirements.

[0041] Specifically, the configurable data conversion rule includes a data parsing rule, a data filtering rule, a data cleaning rule, a data conversion rule, a data calculation rule, a data correlation rule, and a data loading rule.

[0042] Preferably, the data loading is loading the converted data into a target system, including a data warehouse and a data lake, by using a parallel loading technique and a data sharding technique, improving the speed of data loading, supporting data sharding and partitioning, and improving 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, and simplify the rule configuration process based on a graphical interface and drag-and-drop operation, reduce the user threshold, and improve the user experience.

[0044] Further, as shown in Figure 1 The implementation process of the data consistency guarantee and error detection of the present application is shown. The source system data is obtained by using a distributed data extraction technique, and the collected source system data is converted and loaded by using a configurable data conversion rule. The data consistency guarantee result is output based on a data verification algorithm and a data comparison algorithm, and a data consistency guarantee strategy is formulated. The data in the ETL process is monitored in real time by using a real-time data stream analysis technique, and the abnormal type is judged, including data missing, data anomaly and data duplication. An alarm mechanism is formulated according to the abnormal judgment result.

[0045] S2: Data consistency detection is performed on data conversion and data loading, and a data consistency guarantee mechanism is formulated.

[0046] Preferably, the data consistency guarantee mechanism includes a data verification algorithm and a data comparison algorithm.

[0047] Specifically, the data consistency detection includes the following steps: the consistency of the collected source system data is verified by using a data verification algorithm, so as to ensure the integrity and consistency of the data.

[0048] The consistency of the collected source system data and the converted data is compared by using a data comparison algorithm, so as to ensure the accuracy of the data.

[0049] The data consistency guarantee result is output based on the data verification algorithm and the data comparison algorithm, and a data consistency guarantee strategy is formulated.

[0050] Further, the data verification algorithm includes the following steps: the source system data is subjected to hash processing, and the specific formula is as follows:

[0051]

[0052] wherein h ik is a value obtained by hashing the kth element in the ith piece of source system data d i ; d ik is the kth element in the ith piece of source system data d i .

[0053] Based on the source system data after hashing, a single hash value is obtained by calculation, which represents the integrity and consistency of the corresponding data, and the specific formula is as follows:

[0054]

[0055] wherein H(d i ) is the hash value of the ith piece of source system data d i ; K is the dimension of the ith piece of source system data d i .

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

[0057]

[0058] wherein H(d i ) is the hash value of the ith piece of source system data d i ; d ik is the kth element in the ith piece of source system data d i ; K is the dimension of the ith piece of source system data d i .

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

[0060] s jl = d jl - t jl

[0061] wherein s jl is the difference between the lth element in the jth piece of source system data d j and the lth element in the jth piece of converted data t j ; d jl is the lth element in the jth piece of source system data d j ; t jl is the lth element in the jth piece of converted data t j .

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

[0063]

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

[0065] The difference after normalization is calculated to obtain the difference comparison value, and the specific formula is as follows:

[0066]

[0067] Wherein, D(d) j ,t j ) represents the difference between the j-th source system data dj and the j-th transformed data tj; L represents the difference between the j-th transformed data tj and the source system data dj. j The dimension, i.e., the j-th source system data d j Dimensions.

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

[0069]

[0070] Wherein, D(d) j ,t j ) represents the difference between the j-th source system data dj and the j-th transformed data tj; L represents the difference between the j-th transformed data tj and the source system data dj. j The dimension, i.e., the j-th source system data d j Dimension; d jl For the j-th source system data d j The l-th element in t; jl For the j-th transformed data t j The l-th element in the formula; ε 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 If the value 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 If the hash value H(d) is greater than or equal to the first threshold, the collected source system data is determined to be inconsistent. The source system data is then re-collected and a consistency check is performed. If the hash value H(d) is greater than or equal to the first threshold, the data is considered inconsistent. i If the data exceeds the first threshold N times consecutively, the data source will be checked before collecting data from the source system, thus avoiding checking a large data source and wasting resources; otherwise, the subsequent data conversion steps will be performed.

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

[0074] If the differential comparison value D(d j ,t j ) is greater than or equal to the second threshold value, it is determined that the converted data and the source system data do not have consistency, the data conversion step is performed again, and the differential comparison value D(d j ,t j ) is determined, if the differential comparison value D(d j ,t j ) is greater than the second threshold value for M consecutive times, it is determined that the source system data is incorrect, the source system data is reacquired, and resource consumption is reduced, and consistency determination efficiency is improved; otherwise, the data loading step is performed.

[0075] Specifically, by statistically analyzing the historical hash values and differential values of the source data, the initial threshold values of the first threshold value and the second threshold value are determined and tested, and the first threshold value and the second threshold value are adjusted according to actual data and user requirements, and the best threshold values are obtained.

[0076] Specifically, the data consistency is detected by using the data verification algorithm and the data comparison algorithm, and different protection strategies are formulated for inconsistent cases, which improves the effectiveness and prevention ability of detection, ensures the integrity and consistency of data, and reduces resource consumption.

[0077] S3: Error detection and abnormal alarm of data in the ETL process.

[0078] Preferably, 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.

[0079] The monitored data is judged for abnormal type, and an abnormal detection model is constructed.

[0080] An alarm mechanism is formulated based on the abnormal type judgment result, and the data error is discovered and processed in time.

[0081] Specifically, the abnormal type includes data loss, data anomaly and data duplication.

[0082] Further, the calculation formula of data loss is as follows:

[0083]

[0084] Wherein, M g is the data loss rate of the gth data monitoring point, if the data loss rate M g is equal to 1, the data has no loss, otherwise the data has loss; x gwis the wth data monitoring result of the gth data monitoring point; n is the number of monitoring times.

[0085] Further, the calculation formula of data anomaly is as follows:

[0086]

[0087] wherein, E g is the data anomaly degree of the gth data monitoring point; is the mean value of the gth data monitoring point; σ g is the standard deviation of the gth data monitoring point.

[0088] Further, the calculation formula of data repetition is as follows:

[0089]

[0090] wherein, R g is the data repetition rate of the gth 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 is not equal to 1, it is determined that data missing occurs, the system immediately alarms, sends the data missing information email and short message to the operation and maintenance personnel and records the error log.

[0092] If the data anomaly degree E g is greater than the third threshold value, it is determined that data anomaly occurs, the system continuously monitors the position of data anomaly and sends the data anomaly information email and short message to the operation and maintenance personnel, and records the error information.

[0093] If the data anomaly degree E g is less than or equal to the third threshold value, it is determined that data anomaly does not occur, the system continues to monitor the data and performs anomaly judgment.

[0094] If the data repetition rate R g is greater than the fourth threshold value, it is determined that data repetition occurs, the system re-performs the data processing operation of the corresponding position, and sends the data repetition information email and short message to the operation and maintenance personnel.

[0095] Specifically, for the third threshold value and the fourth threshold value, the range of the threshold value is adjusted according to the specific business scenario, and the threshold value is adjusted according to the size of the data quantity and the tolerance degree of data anomaly, to obtain the final third threshold value and fourth threshold value.

[0096] Specifically, by using real-time data stream analysis technology, the data in the ETL process can be monitored in real time, and corresponding processing strategies are formulated for different abnormal types, errors and abnormal situations are found in time, and operation and maintenance personnel are quickly notified to improve fault handling efficiency.

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

[0098] The embodiment also provides a computer device suitable for the case of the data detection method in the ETL process, comprising: a memory and a processor; the memory is configured to store computer executable instructions, and the processor is configured to execute the computer executable instructions to implement the data detection method in the ETL process as described in the above embodiment.

[0099] The computer device can be a terminal, and the computer device comprises a processor, a memory, a communication interface, a display screen and an input device connected through a system bus. The processor of the computer device is configured to provide computing and control capabilities. The memory of the computer device comprises 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 operating system and the computer program in the non-volatile storage medium. The communication interface of the computer device is configured to perform wired or wireless communication with external terminals. The wireless communication can be achieved through WIFI, operator network, NFC (near field communication) or other technologies. The display screen of the computer device can be a liquid crystal display screen or an electronic ink display screen. The input device of the computer device can be a touch layer overlaid on the display screen, or a key, trackball or touchpad arranged on the shell of the computer device. In addition, the input device can be an external keyboard, touchpad or mouse, etc.

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

[0101] In summary, the present application provides configurable data conversion rules, so that the system can adapt to different business requirements, improves the flexibility and scalability of the conversion rules, and reduces the maintenance cost; by designing a data consistency guarantee mechanism based on rules and algorithms, the consistency of data in the data conversion and loading process is ensured, effectively preventing data consistency error problems, 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 an alarm system, thereby improving the stability and reliability of the ETL process.

[0102] Embodiment 2

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

[0104] In this example, through experiments on a certain enterprise data collection and data conversion scene, the data is processed by using configurable data conversion rules, including parsing the BSON file of MongoDB to structured data, removing illegal characters in the order, unifying the case, converting the order information to the Parquet columnar storage format, loading the final data into Apache Hive, and then using the data consistency guarantee mechanism proposed in the present application to perform consistency analysis on the data, and comparing with the prior art, wherein the judgment data of the data consistency guarantee mechanism of the present application is shown in Table 1.

[0105] Table 1: Data consistency guarantee mechanism judgment data table

[0106] Subject Source system data Converted data Difference contrast value Data consistency Order data 0xA37CE928 0xA37CE928 0.0001 Consistent Sales data 0x3F2D18BE 0x3F2D18BE 0.0008 Consistent Log data 0xEA63B97F 0xEA63B980 0.2034 Inconsistent Member data 0x7CD9105A 0x7CD9105A 0.0000 Consistent ... ... ... ... ... Inventory data 0x91AF7D3C 0x91AF7D3D 0.0121 Consistent

[0107] As can be seen from the above table, by comparing the hash values of the source system data and the converted data and calculating the corresponding differential comparison values, the consistency of the data in the data conversion process is analyzed, which shows that the present application can effectively maintain the accuracy and integrity of the data, improve the accuracy of data conversion, and quickly identify the abnormal part of the data through the corresponding data consistency guarantee strategy, reducing resource consumption.

[0108] As shown in Table 2, by comparing the data detection efficiency of the present application and the prior art, it shows that the present application detects the data consistency during the data conversion and data loading process by using the data consistency guarantee mechanism, and detects the errors and alarms the abnormalities of the data in the ETL process by using the abnormal detection model, which effectively improves the data integrity and accuracy, and improves the stability and reliability of the ETL process.

[0109] Table 2: Abnormal detection efficiency comparison table

[0110] Subject Integrity detection coverage rate Accuracy detection coverage rate 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 application and are not limited. Although the present application has been described in detail with reference to the preferred embodiments, it should be understood by those skilled in the art that the technical solutions of the present application can be modified or replaced by equivalents without departing from the spirit and scope of the present application, which should be covered by the scope of the claims of the present application.

Claims

1. A data inspection method in an ETL process, characterized in that: include: The system uses configurable data transformation rules to transform the collected source system data and then loads the transformed data. Perform data consistency checks on the data transformation and data loading processes, and establish a data consistency guarantee mechanism; Perform error detection and anomaly alerts on the data during the ETL process; The established data consistency assurance mechanism includes data verification algorithms and data comparison algorithms; The data consistency check includes the following steps: The consistency of the collected source system data is verified using a data verification algorithm. A data comparison algorithm is used to compare the consistency between the collected source system data and the transformed data; Based on the data verification algorithm and the data comparison algorithm, output the data consistency guarantee results and formulate a data consistency guarantee strategy; The calculation formula for the data verification algorithm is as follows: Wherein H(d) i ) represents the i-th source system data d i The hash value; d ik For the i-th source system data d i The k-th element in the data; K is the i-th source system data d. i The dimension; The calculation formula for the data comparison algorithm is as follows: Wherein, D(d) j ,t j ) represents the j-th source system data d j The transformed data t of the j-th item j The difference comparison value; L is the j-th transformed data t j Dimension; d jl For the j-th source system data d j The l-th element in t; jl For the j-th transformed data t j The l-th element in; ε is a positive number.

2. The data detection method in the ETL process as described in claim 1, characterized in that: The data consistency guarantee strategy includes: If the hash value H(d) i If the value is less than the first threshold, the collected source system data is determined to be consistent, and a data conversion step is performed. If the hash value H(d) i If the hash value H(d) is greater than or equal to the first threshold, the collected source system data is determined to be inconsistent. The source system data is then re-collected and a consistency check is performed. If the hash value H(d) is greater than or equal to the first threshold, the data is considered inconsistent. i If the data exceeds the first threshold N times consecutively, the data source will be checked before collecting data from the source system; otherwise, the subsequent data conversion steps will be performed. If the difference comparison value D(d) j ,t j If the value 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 difference comparison value D(d) j ,t j If the converted data is greater than or equal to the second threshold, it is determined that there is no consistency between the converted data and the source system data. The data conversion step is repeated, and the difference comparison value D(d) is calculated. j ,t j ) Determine if the difference comparison value D(d) j ,t j If the data exceeds the second threshold M times consecutively, the source system data is determined to be incorrect, and the source system data is re-collected; otherwise, the data loading step is performed.

3. The data detection method in the ETL process as described in claim 2, characterized in that: The error detection and anomaly alarm include the following steps: Real-time data stream analysis technology is used to monitor data in the ETL process in real time; The monitored data is analyzed to determine the type of anomaly and an anomaly detection model is constructed. Develop an alarm mechanism based on the anomaly type determination results; The anomaly types include missing data, data anomalies, and duplicate data. The formula for calculating the missing data is as follows: Among them, M g x represents the data missing rate for the g-th data monitoring point; gw This represents the w-th monitoring result for the g-th data monitoring point; n is the number of monitoring sessions. The formula for calculating the data anomaly is as follows: Among them, E g The degree of data anomaly at the g-th data monitoring point; σ is the mean of the g-th data monitoring point; g Let g be the standard deviation of the g-th data monitoring point; The formula for calculating data duplication is as follows: Among them, R g x represents the data repetition rate of the g-th data monitoring point; gw This represents the data monitoring result of the w-th data monitoring point (g-th data monitoring point); x gq This represents the q-th data monitoring result for the g-th data monitoring point.

4. The data detection method in the ETL process as described in claim 3, characterized in that: The alarm mechanism includes: If the data missing rate M g If the value is not equal to Q, it is determined that a data loss has occurred. The system will immediately issue an alarm, send an email and SMS message with the data loss information to the operations and maintenance personnel, and record the error log. If the degree of data anomaly is E g If the value exceeds the third threshold, a data anomaly is determined. The system continuously monitors the location of the data anomaly and sends emails and text messages with data anomaly information to the operations and maintenance personnel, and records the error information. If the degree of data anomaly is E g If the data is less than or equal to the third threshold, it is determined that no data anomaly has occurred, and the system continues to monitor the data and make anomaly judgments. If the data repetition rate R g If the value exceeds the fourth threshold, it is determined that data duplication has occurred. The system will then re-process the data at the corresponding location and send an email and SMS message to the operations and maintenance personnel to inform them of the data duplication.

5. The data detection method in the ETL process as described in claim 4, 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 process utilizes configurable data conversion rules to transform the source system data. The configurable data transformation rules include data parsing rules, data filtering rules, data cleaning rules, data transformation rules, data calculation rules, data association rules, and data loading rules; The data loading process involves using parallel loading technology and data sharding technology to load the converted data into the target system.

6. A data inspection system for an ETL process, based on the data inspection method for an ETL process according to any one of claims 1 to 5, characterized in that: include, The data extraction module is used to extract data from the source system using distributed data extraction technology and to process various data sources and data formats. The 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 transformed data into the target system using parallel loading technology and data sharding technology, and to perform data sharding and partitioning. The consistency guarantee module is used to ensure the consistency of data during data extraction, data transformation, and data loading using a data consistency guarantee 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.

7. A computer device comprising a memory and a processor, wherein the memory stores a computer program, characterized in that: When the processor executes the computer program, it implements the steps of the data detection method in the ETL process according to any one of claims 1 to 5.

8. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by the processor, it implements the steps of the data detection method in the ETL process as described in any one of claims 1 to 5.

Images