Data cleaning method, device and equipment based on real-time warehouse counting technology and medium

Abstract

The invention relates to the technical field of data management, and discloses a data cleaning method, device and equipment based on a real-time warehouse counting technology and a medium. The method comprises the following steps: acquiring real-time data based on a real-time data bin architecture; performing quality analysis on the real-time data to determine a data quality analysis result of the real-time data, wherein the data quality analysis result comprises at least one data quality problem; determining an initial cleaning rule according to the data quality problem; taking a part of sample data acquired from the preset cleaning data set and a part of cleaning data acquired from the real-time data as target data; performing data cleaning to obtain cleaned target data; when it is determined that the cleaned target data does not meet the preset evaluation index, the initial cleaning rule is adjusted until the cleaned target data meet the preset evaluation index, a target cleaning rule is obtained, and data cleaning is conducted on the real-time data. According to the invention, the data can be cleaned in real time and timely, and the pertinence, applicability and accuracy of the cleaning rule are improved.

Description

Technical Field

[0001] The present invention relates to the field of data governance technology, and in particular to a data cleaning method, device, equipment and medium based on real-time data warehouse technology. Background Art

[0002] In today's era of booming digitalization and intelligence, data has become an indispensable cornerstone for corporate decision-making, business innovation, and quality and efficiency improvement. With the continuous expansion and deepening of corporate business, data is growing exponentially, and how to efficiently clean data and improve data quality has become a difficult problem in data governance.

[0003] Traditional data cleaning methods are mostly based on the offline data warehouse architecture of the Hadoop system. The specific operation process is: use ETL tools such as Sqoop to import data from relational databases to HDFS, and then use MapReduce or Spark to create scheduled tasks (such as daily or weekly execution) to complete data cleaning based on offline data warehouse technology, covering routine operations such as deduplication, processing missing values, outlier processing, and data formatting.

[0004] This traditional batch processing method based on offline data warehouse architecture has many disadvantages. First, it relies heavily on scheduled tasks, which takes a long time to process and is difficult to meet the needs of scenarios with high requirements for data cleaning timeliness. Second, it cannot respond to dynamic changes in data in real time. When faced with massive amounts of data, it is very easy to cause a large backlog of processing tasks, causing serious delays in data processing, and easily leading to problems with untimely data cleaning, which affects data quality.

[0005] Therefore, there is an urgent need for a method that can reduce the time of the data cleaning process and clean the data in a timely manner. Summary of the invention

[0006] Therefore, the technical problem to be solved by the present invention is to overcome the problem in the related technology that it heavily relies on scheduled tasks, has a long processing time, and cannot respond to the dynamic changes of data in real time. When faced with massive amounts of data, it is very easy to cause a large backlog of processing tasks, resulting in serious delays in data cleaning and the problem of untimely data cleaning.

[0007] In order to solve the above technical problems, the present invention provides a data cleaning method based on real-time data warehouse technology, and the data cleaning method based on real-time data warehouse technology includes:

[0008] Acquire real-time data based on the data transmission tasks in the real-time data warehouse architecture;

[0009] Performing quality analysis on the real-time data to determine a data quality analysis result of the real-time data; the data quality analysis result includes: at least one data quality problem;

[0010] Determine initial cleaning rules based on all data quality issues in the data quality analysis results;

[0011] Acquire a first preset number of sample data from a preset cleaning data set, acquire a second preset number of cleaning data from the real-time data, and determine the first preset number of sample data and the second preset number of cleaning data as target data;

[0012] Cleaning the target data according to the initial cleaning rule to obtain cleaned target data;

[0013] Determine whether the cleaned target data meets the preset evaluation indicators;

[0014] When it is determined that the cleaned target data does not meet the preset evaluation index, the initial cleaning rule is adjusted until the target data is cleaned based on the adjusted initial cleaning rule, and the cleaned target data obtained meets the preset evaluation index, and the adjusted initial cleaning rule is determined as the target cleaning rule;

[0015] The real-time data is cleansed based on the target cleaning rule.

[0016] In an optional implementation, determining the initial cleaning rules according to all the data quality issues in the data quality analysis results includes:

[0017] Determine a cleaning rule corresponding to each data quality problem in the data quality analysis result; each data quality problem corresponds to one cleaning rule;

[0018] Determine the execution order of the cleaning rules corresponding to each data quality problem in the data quality analysis result to obtain the initial cleaning rules.

[0019] In an optional implementation, the determining of the cleaning rules corresponding to each data quality problem in the data quality analysis result includes:

[0020] Acquire a pre-established cleaning rule database; the cleaning rule database includes data quality issues and cleaning rules stored in a corresponding relationship;

[0021] According to each data quality problem in the data quality analysis result, a search is performed in the cleaning rule database to obtain a cleaning rule corresponding to each data quality problem in the data quality analysis result.

[0022] In an optional embodiment, the method further includes:

[0023] When it is determined that the cleaned target data meets the preset evaluation index, the initial cleaning rule is determined as the target cleaning rule.

[0024] In an optional implementation, determining whether the cleaned target data meets a preset evaluation index includes:

[0025] Determine the completeness, consistency and accuracy of the cleansed target data;

[0026] Determine whether the integrity of the cleaned target data meets a first preset standard value, whether the consistency meets a second preset standard value, and whether the accuracy meets a third preset standard value;

[0027] Among them, when it is determined that the integrity of the cleaned target data meets the first preset standard value, the consistency meets the second preset standard value, and the accuracy meets the third preset standard value, it is determined that the cleaned target data meets the preset evaluation indicators; when it is determined that the integrity of the cleaned target data does not meet the first preset standard value, the consistency does not meet the second preset standard value, and the accuracy does not meet the third preset standard value, it is determined that the cleaned target data does not meet the preset evaluation indicators.

[0028] In an optional implementation, the data quality analysis result also includes the integrity, consistency and accuracy of the real-time data. Before determining the initial cleaning rules according to all the data quality issues in the data quality analysis result, it also includes:

[0029] Determining whether the real-time data meets preset evaluation indicators based on the integrity, consistency and accuracy of the real-time data;

[0030] When it is determined that the real-time data meets the preset evaluation index, the data transmission task is executed; when it is determined that the real-time data does not meet the preset evaluation index, the step of determining the initial cleaning rules according to all the data quality problems in the data quality analysis result is executed.

[0031] In an optional implementation, when the data transmission task is a data transmission task of transmitting data from a data source of a real-time data warehouse architecture to a data warehouse, after performing data cleaning on the real-time data based on the target cleaning rule, the method further includes:

[0032] The target cleansing data obtained by performing data cleansing on the real-time data based on the target cleansing rule is transmitted to the data warehouse of the real-time data warehouse architecture.

[0033] In a second aspect, the present invention provides a data cleaning device based on real-time data warehouse technology, which is applied to a real-time data warehouse architecture. The data cleaning device based on real-time data warehouse technology includes:

[0034] A first processing module is used to obtain real-time data based on a data transmission task in a real-time data warehouse architecture;

[0035] A second processing module is used to perform quality analysis on the real-time data to determine a data quality analysis result of the real-time data; the data quality analysis result includes: at least one data quality problem;

[0036] A third processing module is used to determine initial cleaning rules according to all data quality problems in the data quality analysis result;

[0037] a fourth processing module, configured to obtain a first preset number of sample data from the preset cleaned data set, obtain a second preset number of cleaned data from the real-time data, determine the first preset number of sample data and the second preset number of cleaned data as target data; and perform data cleaning on the target data according to the initial cleaning rule to obtain cleaned target data;

[0038] The fifth processing module includes a first processing unit and a second processing unit;

[0039] The first processing unit is used to determine whether the cleaned target data meets the preset evaluation index;

[0040] The second processing unit is configured to adjust the initial cleaning rule when it is determined that the cleaned target data does not meet the preset evaluation index, until the target data is cleaned based on the adjusted initial cleaning rule, the cleaned target data obtained meets the preset evaluation index, and the adjusted initial cleaning rule is determined as the target cleaning rule;

[0041] The sixth processing module is used to perform data cleaning on the real-time data based on the target cleaning rule.

[0042] In a third aspect, the present invention provides a computer device, comprising: a memory and a processor, the memory and the processor being communicatively connected to each other, the memory storing computer instructions, and the processor executing the data cleaning method based on real-time data warehouse technology of the above-mentioned first aspect or any corresponding embodiment thereof by executing the computer instructions.

[0043] In a fourth aspect, the present invention provides a computer-readable storage medium, on which a single computer-readable storage medium stores computer instructions, and the computer instructions are used to enable a computer to execute the data cleaning method based on real-time data warehouse technology according to the above-mentioned first aspect or any corresponding embodiment thereof.

[0044] In a fifth aspect, the present invention provides a computer program product, comprising computer instructions, which are used to enable a computer to execute the data cleaning method based on real-time data warehouse technology according to the above-mentioned first aspect or any corresponding embodiment thereof.

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

[0046] Real-time data acquisition based on the data transmission task in the real-time data warehouse architecture ensures the timeliness of the data source and the close integration with the real-time data warehouse architecture, ensuring that the data can flow into the cleaning process in time for data cleaning, avoiding data cleaning delays and backlogs of cleaning tasks.

[0047] The data acquired in real time is subjected to quality analysis to determine the data quality analysis results. In the present invention, the identification of data quality problems is used as a key prerequisite for data cleaning. By analyzing the data acquired in real time, various quality problems that may exist in the data can be fully and deeply excavated, and various data quality problems such as missing data, data duplication, data format errors, data consistency problems, data outliers, etc. can be detected. By performing quality analysis on the data acquired in real time, cleaning rules can be formulated in a targeted manner according to the obtained data quality problems. Corresponding initial cleaning strategies can be formulated for different data quality problems detected. This method of analyzing first and then processing makes data cleaning more targeted and avoids blind operations.

[0048] These initial cleaning rules are a preliminary constructed data cleaning framework, which will be further optimized later.

[0049] A first preset number of sample data is obtained from a preset cleaning data set, and a second preset number of cleaning data is obtained from the data obtained in real time, and the two are determined as target data. The above scheme selects sample data from the preset cleaning data set and combines part of the data obtained in real time to form target data. The preset cleaning data set contains historically cleaned or representative data, which can provide an empirical reference for the verification of cleaning rules. Part of the data obtained in real time reflects the actual characteristics of the current data. The target data formed by the combination of the two allows the verification and optimization of cleaning rules to take into account both historical experience and the real-time and particularity of current data, thereby improving the adaptability and effectiveness of cleaning rules.

[0050] When adjusting the cleaning rules later, it can avoid deviations in actual applications caused by adjusting the rules based only on a single type of data.

[0051] After cleaning the target data according to the initial cleaning rules, the cleaning effect is measured by the preset evaluation indicators. If the cleaned target data does not meet the evaluation indicators, it means that the initial cleaning rules are insufficient and need to be adjusted. This cycle mechanism ensures that the cleaning rules are continuously optimized until the ideal cleaning effect is achieved. After adjusting the initial cleaning rules, the target data is cleaned and evaluated again, forming a closed-loop optimization process that can gradually improve the data processing capabilities of the cleaning rules.

[0052] When the cleaned target data meets the preset evaluation indicators, the adjusted initial cleaning rules will be determined as the target cleaning rules. Finally, the determined target cleaning rules will be applied to all real-time acquired data to complete the cleaning of the entire real-time data, ensuring that the data quality meets the requirements and providing a reliable data foundation for subsequent data analysis, decision support, etc.

[0053] Through comprehensive data quality analysis and targeted cleaning rule formulation and application, various data quality problems existing in real-time data can be effectively solved and the effectiveness of data cleaning can be improved.

[0054] Real-time data acquisition and cleaning based on the real-time data warehouse architecture ensures the timeliness of data processing, meets the needs of enterprises for real-time data, and can quickly respond to market changes and business dynamics. At the same time, by combining the preset cleaning data set and real-time data to optimize the cleaning rules, the cleaning process can adapt to the changing characteristics of data in different periods. Whether it is a slight change in the data structure or a change in the data distribution, the cleaning strategy can be adjusted in time to ensure the effectiveness of data cleaning. For example, in real-time monitoring of financial transactions, accurate data can detect abnormal transactions in time and take measures to reduce risks. BRIEF DESCRIPTION OF THE DRAWINGS

[0055] In order to more clearly illustrate the specific embodiments of the present invention or the technical solutions in the related technologies, the drawings required for use in the specific embodiments or the related technical descriptions will be briefly introduced below. Obviously, the drawings described below are some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0056] Figure 1 is a schematic diagram of the Hadoop architecture of the first stage of an embodiment of the present invention;

[0057] Figure 2 Schematic diagram of Lambda architecture in the second phase of an embodiment of the present invention;

[0058] Figure 3 is a schematic diagram of the Kappa architecture of the second stage of an embodiment of the present invention;

[0059] Figure 4 is a schematic diagram of a real-time data warehouse architecture according to an embodiment of the present invention;

[0060] Figure 5 It is a flow chart of a data cleaning method based on real-time data warehouse technology according to an embodiment of the present invention;

[0061] Figure 6 is a specific flow chart of the data cleaning method according to an embodiment of the present invention;

[0062] Figure 7 is a flow chart of another data cleaning method based on real-time data warehouse technology according to an embodiment of the present invention;

[0063] Figure 8 is a structural schematic diagram of a data cleaning device based on real-time data warehouse technology according to an embodiment of the present invention;

[0064] Fig. 9 It is a schematic diagram of the hardware structure of a computer device according to an embodiment of the present invention. DETAILED DESCRIPTION

[0065] In order to make the purpose, technical solution and advantages of the embodiments of the present invention clearer, the technical solution in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative work are within the scope of protection of the present invention.

[0066] How to achieve efficient and reliable data cleaning is the core link of data governance, which is crucial to improving data quality and ensuring data accuracy, completeness and consistency. First, it ensures the accuracy of data, eliminates data duplication, errors and inconsistencies, and provides a reliable basis for subsequent data analysis and decision-making. Secondly, data cleaning improves the integrity of data, fills in missing values, and makes data more complete and usable. In addition, data cleaning also reduces data noise, removes noise data such as irregular text and outliers, and improves the reliability and stability of data.

[0067] The difficulties of data cleaning mainly include: diverse data sources and types, poor data quality, large data volume, and high processing time cost. The traditional data cleaning method is mainly based on the offline data warehouse architecture of the Hadoop system. Through batch data processing, ETL tools such as Sqoop are used to import data from relational databases to HDFS, and then MapReduce or Spark is used to create scheduled tasks (such as daily and weekly) for data cleaning, including deduplication, processing missing values, outlier processing, data formatting, etc. This traditional batch processing method relies on scheduled tasks, has a long processing time, and cannot respond to data changes in real time. When the data volume is large, it will cause problems such as backlog of processing tasks, untimely data processing, and omissions in data cleaning, all of which will directly affect data quality.

[0068] The embodiments of the present invention provide a data cleaning method, device, equipment and medium based on real-time data warehouse technology to solve the above problems. The present invention will explain in detail how to use the present invention to clean massive data efficiently, flexibly and with high quality in the data governance process.

[0069] With the development of big data technology, the architecture of data warehouses has evolved from the original offline data warehouse architecture to the real-time data warehouse architecture after several generations. By reasonably combining the real-time data warehouse architecture and carrying out application innovation on this basis, a real-time and highly concurrent data processing method has been designed. The real-time data processing can reach the minute level, which greatly improves the efficiency and timeliness of data cleaning, thereby providing continuous high-quality data, providing strong data support for corporate development decisions, seizing business opportunities, and improving quality and efficiency.

[0070] The technical evolution of data warehouses can be roughly divided into the following three stages: from the traditional Hadoop architecture to the Lambda architecture, from the Lambda architecture to the Kappa architecture, and finally to the real-time data warehouse architecture. The evolution of the data warehouse technology architecture has greatly improved the ability to process massive data, including data cleaning.

[0071] Phase 1: With the development of business models and technologies, data application scenarios have become more and more complex, and data has become more and more abundant. The data platform has evolved from the traditional data warehouse to a data warehouse architecture based on the Hadoop platform. The typical business application scenario is the Hadoop big data platform.

[0072] The main technical route is: adopt the Hadoop+Hive technical route, use Hadoop to store massive data, and use Hive to build offline data warehouses and data analysis. The specific Hadoop architecture of the first phase is as follows: Figure 1 shown.

[0073] Phase 2: With the emergence of more and more demands for real-time data processing, data warehouse architectures have emerged, including Lambda and Kappa architectures. Figure 2 As shown in the figure, the Lambda architecture supports the needs of stream / batch data processing by maintaining two distributed systems at the same time, one for processing offline data services and the other for processing real-time data services. Figure 3 As shown in the figure, the Kappa architecture mainly uses the computing engine Flink to process real-time data services at high speed, but is slightly insufficient in processing offline data services.

[0074] The main technical route is: adopt Hadoop+Kafka+Flink+Spark technical route, where Hadoop is used to store massive data, Spark is used to build offline data warehouses and process and analyze data, and Flink+Kafka is used to build real-time data warehouses and real-time analysis and processing.

[0075] The third stage: In order to solve the shortcomings of Lambda and Kappa architectures, the technical solutions of lake-warehouse integration and stream-batch integration were proposed, and eventually solutions such as Iceberg and other data lake plus data warehouse solutions emerged, which eventually formed a technical architecture pattern of lake-warehouse integration and storage-computing separation. With the advent of the digital era, especially in recent years, the application scenarios and needs of big data and artificial intelligence have become more and more abundant, and the scale of data has become larger and larger. It is urgent to solve the application needs of big data processing in real time and efficiently. Therefore, the real-time data warehouse architecture solution is built on the lake-warehouse integration technical architecture combined with the Flink real-time computing engine, which can greatly solve the efficiency and high availability of massive data collection, storage, analysis and calculation, thereby ensuring the timeliness and accuracy of massive data processing, among which the most typical application is data cleaning. The present invention is based on the third generation (real-time) data warehouse technology to process and clean the massive data of enterprises, which not only solves the quality problem of enterprise data, but also solves the timeliness and accuracy of data processing. The actual application effect is very obvious.

[0076] The main technical route is: using Hadoop, Iceberg, Flink, and Trino. Hadoop and Iceberg are used to build lake-warehouse integrated data storage to store massive data, Flink is used as a stream-batch integrated data processing tool, and Trino is used as a real-time online analysis tool. Its technical architecture is as follows: Figure 4 shown.

[0077] Figure 4 It shows an architectural flow of data processing and analysis, which is usually used for big data processing and real-time analysis. The following is an explanation of each component and process:

[0078] Data Source:

[0079] Data log: refers to the log data generated by the system or application.

[0080] Database: A database system that stores structured data.

[0081] Flink CDC: is a data collection tool for capturing database changes. It can monitor database changes in real time and output these changes as data streams.

[0082] Data Warehouse:

[0083] Data source layer (ODS): The original data storage layer obtains data directly from the data source, does little or no processing, and maintains the originality of the data.

[0084] Data detail layer (DWD): Based on the ODS layer, the data is cleaned and processed to form detailed data for subsequent analysis.

[0085] Data summary layer (DWS): summarizes and aggregates the data in the DWD layer to form summary data to support higher-level analysis and decision-making.

[0086] Iceberg: is an open source table format for large-scale analytical data. It supports ACID transactions and can efficiently process large data sets.

[0087] Hadoop: It is a distributed storage and computing platform for processing large-scale data sets. Here, it acts as a distributed storage system to store massive data at the ODS, DWD, and DWS layers.

[0088] Real-time computing (Flink+Doris): Use Flink for real-time data processing and analysis. Doris is an MPP database for real-time analysis that can quickly process and query large-scale data.

[0089] Real-time OLAP (Tino+Doris): Online Analytical Processing (OLAP): used to support complex queries and analysis. Tino is a specific real-time OLAP engine or tool that is used in conjunction with Doris to provide real-time analytical capabilities.

[0090] Application side:

[0091] Business Analytics: Analysis of business data to support decision making.

[0092] Real-time reports: Generate real-time business reports for business personnel to view.

[0093] Data Mining: Use data mining techniques to discover patterns and knowledge from large amounts of data.

[0094] The real-time data warehouse architecture is a typical data lake architecture, which allows data to be captured from the original data source, cleaned, transformed, and aggregated through different processing layers, and finally used for real-time analysis and business decision-making.

[0095] In the first and second stages of data warehouse development, the following problems generally exist in data warehouse data cleaning efficiency and data quality:

[0096] 1) Data processing is mainly based on batch processing tasks, which will cause data cleaning backlogs and untimely cleaning, resulting in poor data quality in the data warehouse.

[0097] 2) In order to ensure the timeliness of data processing, two sets of data processing systems (i.e. batch business processing and real-time business processing) need to be maintained in the data warehouse. This will cause the same data to be cleaned multiple times by two data links and inconsistent data cleaning, which will ultimately lead to poor data quality in the data warehouse.

[0098] In the real-time data warehouse stage, a lake-warehouse integrated and stream-batch integrated architecture is adopted. Due to the upgrade of the technical architecture, the data processing pain points caused by the deficiencies of the data warehouse architecture in the past have been well solved, and the data processing efficiency has also been greatly improved, thereby improving data cleaning efficiency, thereby improving the data quality of the data warehouse as a whole, meeting the company's requirements for timeliness and accuracy of decision analysis, and providing a reliable basis for the company's business decision analysis in a timely manner.

[0099] The technical solution of the present invention mainly includes two parts:

[0100] Part 1: Upgrade the data warehouse architecture to a real-time data warehouse architecture, and improve the efficiency and quality of data cleaning by upgrading the data warehouse architecture.

[0101] Part 2: Improving the efficiency and quality of data cleaning through the cleaning rules determined by the technical solution of the present invention.

[0102] The present invention improves the efficiency and quality of data cleaning by combining the application of real-time data warehouse architecture, which is mainly reflected in the following aspects:

[0103] 1) Based on the real-time data warehouse architecture, data processing tasks are handled in real-time mode. Therefore, when data flows in the data warehouse, data cleaning actions will be performed immediately to ensure that the "dirty data" in the data can be cleaned in time. Therefore, there will be no backlog of cleaned data, thereby effectively solving the timeliness and accuracy of data quality.

[0104] 2) Based on the data storage and processing capabilities provided by the real-time data warehouse architecture, it can be ensured that the entire data warehouse has one and only one data cleaning solution, so there is no problem of the same data being cleaned multiple times and inconsistent data cleaning, thereby improving data cleaning efficiency and accuracy.

[0105] The present invention improves the efficiency and quality of data cleaning through the cleaning rules determined by the technical solution of the present invention, which is mainly reflected in the following aspects:

[0106] In the process of data governance, data cleaning is a key link to ensure data quality. By adopting intelligent data cleaning methods and strategies, the efficiency of data cleaning can be effectively improved. At the same time, by monitoring changes in data quality, regular data cleaning and verification can be performed to ensure continuous improvement of data governance and provide strong support for enterprise decision-making and business analysis in the long term.

[0107] Data cleaning is an important and complex task in the data governance process. By deeply understanding the methods and processes of data cleaning, clarifying the cleaning goals and expected results, and designing intelligent and flexible cleaning strategies and technical means, you can achieve continuous and efficient data cleaning and continuous improvement of data quality. High-quality data is more suitable for data analysis or data mining, providing strong data support for the development of the enterprise.

[0108] The common difficulties in data cleaning currently include:

[0109] 1) There are many data sources and poor data quality. The data sources are different, and the data format and quality of each source may be different. They need to be processed uniformly after entering the data warehouse to ensure data consistency. There are generally errors, missing values, inconsistent formats, etc. in the data, which need to be solved through data cleaning.

[0110] 2) Large amount of data. As the degree of informatization and digitalization of enterprises continues to increase, the amount of data in enterprises is also increasing year by year. Manual and batch data cleaning is relatively inefficient, and it is necessary to rely on automated and intelligent tools to improve efficiency.

[0111] 3) Time cost. Data cleaning is a very time-consuming process, especially when the data volume is large and the data quality is poor, which requires a lot of time and server resources.

[0112] In the early offline data warehouse architecture, data cleaning tasks were mainly performed in batch mode. Cleaning tasks needed to be performed regularly to clean the tables that needed to be cleaned one by one, and data cleaning rules (such as missing data, abnormal data, duplicate data, and data conversion) were also fixed. This led to the following two problems:

[0113] a) Data backlog problem: Batch task processing requires data to be processed in batches when it arrives. When the amount of data is large, it will cause a backlog of cleaned data, untimely cleaning, or even failure of the cleaning operation, and the timeliness and quality of data cleaning cannot be guaranteed.

[0114] b) Low cleaning efficiency: During the data cleaning process, the cleaning strategies for all data are fixed and cannot intelligently select targeted cleaning rules based on the quality of different data. Therefore, fixed cleaning rules will lead to the execution of unnecessary data cleaning rules, resulting in large data cleaning calculations and low cleaning efficiency. The larger the amount of data, the more prominent the problem. Therefore, data cleaning without flexible configuration of cleaning rules will cause a series of problems such as waste of data warehouse hardware resources, low cleaning efficiency, and poor data quality.

[0115] The technical solution of the present invention provides a data cleaning method based on real-time data warehouse technology, which specifically utilizes big data and real-time data warehouse technology to clean massive data in real time and efficiently and improve data quality.

[0116] According to an embodiment of the present invention, an embodiment of a data cleaning method based on real-time data warehouse technology is provided. It should be noted that the steps shown in the flowchart of the accompanying drawings can be executed in a computer device such as a set of computer executable instructions, and although the logical order is shown in the flowchart, in some cases, the steps shown or described can be executed in an order different from that shown here.

[0117] Figure 5 It is a flow chart of a data cleaning method based on real-time data warehouse technology according to an embodiment of the present invention.

[0118] like Figure 5 As shown, in an embodiment of the present invention, a data cleaning method based on real-time data warehouse technology is provided, which is applied to a real-time data warehouse architecture, and the real-time data warehouse architecture includes a data source, a data warehouse, and an application end. Therefore, before applying the technical solution of the present invention, it is necessary to upgrade the architecture of the data warehouse to a real-time data warehouse architecture. In addition, if there is an updated version of the real-time data warehouse architecture, the current real-time data warehouse architecture can be upgraded to the latest version. The efficiency and quality of data cleaning are improved by upgrading the data warehouse architecture.

[0119] The data cleaning method based on real-time data warehouse technology includes:

[0120] Real-time data acquisition stage, quality analysis stage, cleaning rule determination stage, verification stage, and cleaning execution stage.

[0121] Real-time data acquisition stage: S101: Acquire real-time data based on the data transmission task in the real-time data warehouse architecture.

[0122] In this embodiment, real-time data can be obtained from any data transmission task in the real-time data warehouse architecture. For example, it can be a data transmission task at any of the following stages: from the data source to the data source layer ODS, the data source layer is used to store data as the data storage layer, from the data source layer to the data detail layer DWD, the data detail layer is used, from the data detail layer to the data summary layer DWS, etc. In this embodiment, real-time data refers to data obtained in real time.

[0123] As an example, when the data transmission task is to transmit data from the data source of the real-time data warehouse architecture to the data warehouse, that is, when the data is transmitted from the data source (such as data log and database) to the data source layer (ODS layer), data cleaning can be performed according to the technical solution of the present invention. The data source can be a data source of various systems, for example, a power grid information collection system, a warehouse management system, a file management system, a product sales management system, etc. Taking the data source as the data source of the power grid information collection system as an example, the data log in the data source may include:

[0124] Operation log: A detailed record of system operations, such as data collection, processing, and transmission.

[0125] Access log: records access behavior to the system, including access time, visitor identity, accessed resources, etc.

[0126] System log: records events during system operation, such as system startup, shutdown, errors, warnings, etc.

[0127] Security log: records security-related events such as unauthorized access attempts, password changes, permission changes, etc.

[0128] Audit Log: For compliance purposes, a detailed log of all critical actions is recorded for audit trail.

[0129] The database in this data source may include:

[0130] Equipment information: Stores detailed information of various equipment in the power grid, such as transformers, circuit breakers, metering equipment, etc.

[0131] User information: Stores user details such as username, password (encrypted storage), permissions, contact information, etc.

[0132] Permission settings: Record the access rights of users and user groups, and define which users or user groups can read, write, or execute specific data.

[0133] Data collection and recording: Stores data collected from power grid equipment, such as voltage, current, power, etc.

[0134] Event logging: records events during grid operation, such as faults, alarms, maintenance activities, etc.

[0135] Configuration information: storage system configuration information, such as collection frequency, data storage strategy, data processing rules, etc.

[0136] Performance indicators: record the system's performance indicators, such as response time, system load, data transfer rate, etc.

[0137] You can use the Flink CDC data collection tool in the real-time data warehouse architecture to obtain real-time data from the data source. Real-time data can be change data in the data source. In actual applications, you can obtain different types of real-time data, such as change data on access rights of users and user groups in permission settings, and change data on storage system configuration information, such as collection frequency, data storage strategy, and data processing rules.

[0138] Flink CDC is a data collection tool used to capture changes in databases and data logs. It can monitor changes in databases and data logs in real time and output these changes as data streams to obtain real-time data.

[0139] Quality analysis stage: S102: Perform quality analysis on the real-time data to determine the data quality analysis result of the real-time data.

[0140] In this embodiment, the data quality analysis result includes: at least one data quality problem.

[0141] The data quality analysis results can also include some preset evaluation indicators, such as completeness, consistency and accuracy, as well as timeliness, accessibility, format and standardization.

[0142] Completeness: Ensure that real-time data contains all necessary information and no critical fields or records are missing.

[0143] Consistency: Check whether the real-time data is consistent between different data sources or records, and whether there are any contradictions or conflicts.

[0144] Accuracy: Evaluate real-time data for input errors or calculation errors.

[0145] Timeliness: Analyze whether the real-time data is up-to-date and whether it can reflect the current business status.

[0146] Accessibility: Ensure that real-time data is accessible to authorized users and that the data is stored and accessed in a way that does not restrict the data analysis process.

[0147] Format and compliance: Check whether the real-time data complies with the predetermined format and specifications, such as date format, value range, encoding standard, etc.

[0148] The purpose of conducting data quality analysis before data cleaning is to comprehensively check the current status of the data, identify existing problems, and provide a basis for subsequent cleaning work and determine more targeted data cleaning rules.

[0149] Common data quality issues include, but are not limited to: missing values, outliers, duplicate values, inconsistent formats (data may be in different formats, such as the diversity of date formats), data inconsistency (inconsistencies and contradictions in the data, such as checks on numerical ranges, logical relationships, time series, etc. Inconsistencies may be caused by factors such as data collection errors, duplicate records or incorrect coding), data cannot be matched: data may have problems with matching, which may be due to data quality issues such as incompatibility between data sources or inconsistent data formats.

[0150] In this embodiment, a quality inspection tool, such as some commonly used or open source quality inspection tools, Great Expectations, Soda Core, etc., can be used to perform a preliminary assessment on the real-time data to determine the data quality issues existing in each type of real-time data.

[0151] Cleaning rule determination stage: S103: Determine initial cleaning rules based on all data quality issues in the data quality analysis results.

[0152] In this embodiment, S103 determines the initial cleaning rules according to all the data quality problems in the data quality analysis results, specifically including:

[0153] S1031: Determine a cleaning rule corresponding to each data quality problem in the data quality analysis result. One data quality problem corresponds to one cleaning rule.

[0154] S1032: Determine the execution order of the cleaning rules corresponding to each data quality problem in the data quality analysis result to obtain the initial cleaning rules.

[0155] In this embodiment, the execution order of the cleaning rules corresponding to each data quality problem in the data quality analysis result can be determined according to the logical process of data cleaning and the dependency relationship between data.

[0156] As an example, the execution order can be: 1) Missing value processing: Generally, missing value processing is the first step in data cleaning, because many other cleaning operations (such as outlier detection, data transformation) require a complete data set. For example, if there are missing values ​​in the data set, you may need to fill or delete these missing values ​​before you can perform subsequent outlier detection. 2) Outlier processing: After missing value processing, outlier identification and processing can be performed. Outliers may be caused by data entry errors, measurement errors, or extreme values. For example, if there is a temperature data set that contains an abnormally low temperature value (possibly a sensor failure), after processing missing values, this outlier can be identified and corrected or deleted. 3) Duplicate value processing. 4) Data conversion processing: After processing missing values, outliers, and duplicate values, you may need to convert the data to the correct type. For example, if a field should be a date type but is mistakenly entered as a string, it needs to be converted to a date type for time series analysis. 5) Data standardization / normalization processing: For numerical data that needs to be compared or merged, standardization or normalization processing may be required.

[0157] In this embodiment, S1032 determines the cleaning rules corresponding to each data quality problem in the data quality analysis result, specifically including:

[0158] Obtain a database of pre-built cleaning rules.

[0159] The cleaning rule database includes data quality issues and cleaning rules stored in a corresponding relationship.

[0160] According to each data quality problem in the data quality analysis result, a search is performed in the cleaning rule database to obtain the cleaning rule corresponding to each data quality problem in the data quality analysis result.

[0161] In this embodiment, a cleaning rule database can be pre-established, and some common data quality problems and corresponding cleaning rules can be pre-stored in the cleaning rule database. When determining the data quality problems existing in the current real-time data, the cleaning rules corresponding to the data quality problems existing in the current real-time data are determined from the cleaning rule database, and the cleaning rules corresponding to each type of data problem are clarified. For example, for missing values, the corresponding cleaning rules: you can choose to delete the rows or columns containing missing values ​​or insert appropriate values ​​according to business rules. For abnormal values ​​or duplicate values, you can choose to identify abnormal values ​​through statistical methods, or judge duplicate values ​​through unique identifiers, and then decide whether to delete or merge. The cleaning rules corresponding to each data quality problem can be designed using the conventional data quality problem processing method in this field, and they will not be listed one by one here.

[0162] In this embodiment, the cleaning rules corresponding to each data quality problem are determined. This means that for each problem found in the data quality analysis, such as missing values, outliers, duplicate values, etc., a specific cleaning rule needs to be designed to handle it. For example, for missing values, the rules may include deleting records with missing values ​​or filling missing values ​​with the mean, median, or mode. Determine the execution order of the cleaning rules. This step involves obtaining a pre-established cleaning rule database, which contains the correspondence between data quality problems and cleaning rules. Then, according to each problem in the data quality analysis results, the corresponding cleaning rules are retrieved in the cleaning rule database to form a complete set of initial cleaning rules. It is more targeted and does not require some unnecessary data cleaning, which reduces the time and efficiency of data cleaning.

[0163] Each data quality problem has a corresponding cleaning rule, ensuring the pertinence and effectiveness of the cleaning process. Through the pre-established cleaning rule database, cleaning rules can be systematically managed and applied to improve efficiency. Cleaning rules can be flexibly selected and adjusted according to different data quality problems to meet different data cleaning needs. Through systematic cleaning rules, the accuracy and reliability of data can be significantly improved, providing support for subsequent data analysis and decision-making.

[0164] Based on the data quality issues in the data quality analysis results, the cleaning tool will intelligently recommend necessary cleaning rules in a targeted manner and clarify the cleaning rules for each data quality issue. For example, for the missing value problem, you can choose to delete the rows or columns containing missing values ​​or insert appropriate values ​​according to business rules. For outliers, you can choose to identify outliers through statistical methods. For duplicate value problems, unique identifiers are used to determine duplicate values, and then decide whether to delete or merge current records. The purpose of this is to intelligently select necessary data cleaning rules based on the data quality analysis results of the current real-time data before data cleaning, rather than executing all cleaning rules once, which greatly improves the efficiency of data cleaning.

[0165] The verification phase includes the technical solutions for S104 and S105:

[0166] S104: Obtain a first preset number of sample data from a preset cleaning data set, obtain a second preset number of cleaning data from real-time data, determine the first preset number of sample data and the second preset number of cleaning data as target data, and perform data cleaning on the target data according to the initial cleaning rule to obtain cleaned target data.

[0167] S105: Determine whether the cleaned target data meets the preset evaluation index.

[0168] The first case: when it is determined that the cleaned target data does not meet the preset evaluation index, the initial cleaning rule is adjusted. The target data is cleaned based on the adjusted initial cleaning rule to obtain the cleaned target data. When it is determined that the cleaned target data meets the preset evaluation index, the adjusted initial cleaning rule is determined as the target cleaning rule.

[0169] In this embodiment, there is also a second situation: when it is determined that the cleaned target data meets the preset evaluation index, the initial cleaning rule is determined as the target cleaning rule.

[0170] In the present invention, it is preferred to perform data cleaning on the target data according to the initial cleaning rules, and determine whether to adjust the initial cleaning rules according to whether the cleaned target data meets the preset evaluation index.

[0171] In the present invention, in addition to the above-mentioned selection of target data (the target data includes part of the sample data in the preset cleaning data set and part of the data in the real-time data), it is also possible to select only part of the sample data in the preset cleaning data set, perform data cleaning on part of the sample data according to the initial cleaning rules, and determine whether to adjust the initial cleaning rules based on whether the cleaned part of the sample data meets the preset evaluation indicators.

[0172] In this embodiment, determining whether the cleaned target data meets the preset evaluation index in S105 includes:

[0173] S1051: Determine the integrity, consistency and accuracy of the cleaned target data.

[0174] S1052: Determine whether the integrity of the cleaned target data meets the first preset standard value, whether the consistency meets the second preset standard value, and whether the accuracy meets the third preset standard value.

[0175] In this embodiment, when it is determined that the integrity of the cleaned target data meets the first preset standard value, the consistency meets the second preset standard value, and the accuracy meets the third preset standard value, it is determined that the cleaned target data meets the preset evaluation index. When it is determined that the integrity of the cleaned target data does not meet the first preset standard value, the consistency does not meet the second preset standard value, and the accuracy does not meet the third preset standard value, it is determined that the cleaned target data does not meet the preset evaluation index.

[0176] In this embodiment, the verification stage includes a cleaning verification stage. In the cleaning verification stage, after obtaining the cleaned target data, a comprehensive evaluation is performed from the three dimensions of completeness, consistency and accuracy according to variance or correlation coefficient, etc., to ensure that the cleaning results meet the data quality requirements (preset evaluation indicators). If the cleaning results do not meet the quality requirements, it is necessary to readjust the initial cleaning rules obtained in S103 until a satisfactory cleaning result is obtained.

[0177] As an example, the integrity meets the first preset standard value: the missing value statistical index (the number of missing values / the total data volume of real-time data) is less than the first preset standard value, such as 5%.

[0178] The consistency meets the second preset standard value: the data consistency index is within a reasonable range, such as the second preset standard value [70%, 90%].

[0179] The accuracy meets the third preset standard value: the accuracy statistical index (the amount of compliant data / the total amount of data) is greater than the third preset standard value, such as the third preset standard value of 80%.

[0180] In this embodiment, the verification phase may also include a cleaning rule verification phase. Rule verification phase: obtaining a first preset number of sample data from a preset cleaning data set, performing data cleaning on the sample data according to the initial cleaning rule to obtain the cleaned sample data, and comparing the sample data before cleaning with the sample data after cleaning by using methods such as mean, standard deviation, and distribution diagram to determine the accuracy and effectiveness of the initial cleaning rule.

[0181] As an example, part of the sample data before cleaning and part of the sample data after cleaning are compared by the mean comparison method to determine the accuracy and effectiveness of the initial cleaning rules.

[0182] For numerical data, first calculate the mean of each variable of interest (such as sales amount, user age, etc.) in the sample data before cleaning. Assume that there is real-time data containing sales amount. Before cleaning, calculate the mean of sales amount. Calculate the mean after cleaning: After cleaning the sample data according to the initial cleaning rules, use the same method to calculate the mean of the corresponding variable in the sample data after cleaning. For example, when cleaning the sales amount data, some obviously wrong high-value outliers (such as extremely high amounts caused by data entry errors) may be removed. Comparative analysis: If the mean after cleaning is more in line with business expectations, for example, in the sales amount data, the mean after cleaning is closer to the historical normal sales level or the industry average sales level, then this indicates that the initial cleaning rules are effective in dealing with data problems (such as outliers) that may affect the mean. On the contrary, if the mean after cleaning fluctuates unreasonably (such as seriously inconsistent with business knowledge), it may be necessary to check whether the initial cleaning rules are too aggressive or do not process the data correctly.

[0183] As an example, part of the sample data before cleaning and part of the sample data after cleaning are compared by using a standard deviation comparison method to determine the accuracy and effectiveness of the initial cleaning rules.

[0184] Calculate the standard deviation before cleaning: For some sample data before cleaning, calculate the standard deviation of each variable, which reflects the degree of dispersion of the data. Taking user age data as an example, a larger standard deviation may indicate that the data distribution is relatively dispersed, and there may be outliers or data quality issues. Calculate the standard deviation after cleaning: After cleaning the data, calculate the standard deviation. For example, after cleaning the user age data, if the initial cleaning rule is to deal with missing data (through reasonable filling) and outliers (through identification and correction), then the standard deviation after cleaning may become smaller, indicating that the degree of dispersion of the data is reduced and the data is more concentrated in a reasonable range. Comparison and evaluation: If the standard deviation becomes smaller after cleaning and the data distribution is more in line with business logic (such as user age is more concentrated in a reasonable adult working age range), it means that the initial cleaning rules help improve the stability and reliability of the data. However, if the standard deviation becomes too small, it may mean that the initial cleaning rule is over-corrected and some reasonable change information of the data is lost, and it needs to be adjusted.

[0185] In this embodiment, if it is determined in the rule verification stage that the accuracy or effectiveness of the initial cleaning rule is poor, for example, when the accuracy is less than the accurate threshold or the effectiveness is less than the effective threshold, the initial cleaning rule may also be adjusted until the accuracy is greater than or equal to the accurate threshold or the effectiveness is greater than or equal to the effective threshold. When the accuracy is greater than or equal to the accurate threshold or the effectiveness is greater than or equal to the effective threshold, the cleaning verification stage is performed to determine whether the cleaned part of the sample data meets the preset evaluation index. When it is determined that the cleaned part of the sample data does not meet the preset evaluation index, the initial cleaning rule is adjusted until the part of the sample data is cleaned based on the adjusted initial cleaning rule, and the cleaned part of the sample data meets the preset evaluation index, and the adjusted initial cleaning rule is determined as the target cleaning rule.

[0186] In the present invention, in order to obtain the target cleaning rule, the cleaning verification stage can be performed on the target data, the rule verification stage and the cleaning verification stage can be performed on the target data at the same time, the cleaning verification stage can be performed on part of the sample data, and the rule verification stage and the cleaning verification stage can be performed on part of the sample data at the same time. The method of performing the rule verification stage on the target data at the same time is the same as the method of performing the rule verification stage on part of the sample data at the same time, and the method of performing the cleaning verification stage on part of the sample data is the same as the method of performing the cleaning verification stage on the target data, which will not be repeated here.

[0187] Execute cleaning phase: S106: perform data cleaning on real-time data based on target cleaning rules.

[0188] In an optional implementation, when the data transmission task is a data transmission task of transmitting data from a data source of a real-time data warehouse architecture to a data warehouse, after the real-time data is cleansed based on the target cleaning rule, the data cleaning method based on the real-time data warehouse technology further includes:

[0189] The target cleansing data obtained by data cleaning the real-time data based on the target cleaning rules is transmitted to the data warehouse of the real-time data warehouse architecture.

[0190] In this embodiment, the target cleaned data is the real-time data after cleaning. The real-time data after cleaning is stored in the data warehouse for subsequent data analysis, data mining and data service use.

[0191] The present invention adopts a real-time data warehouse architecture, and data processing is processed in a real-time data flow task mode, so data cleaning can be cleaned in real time as the data changes, so it will not cause the squeeze of cleaning data, thereby greatly improving the efficiency of data cleaning, and the effect is more obvious when the amount of data is larger. At the same time, the present invention can analyze an analysis report covering data overview, missing value analysis, outlier analysis, and repeated value analysis in a simple random sampling manner based on the existing data in the data warehouse, and configure targeted cleaning rules for each type of data according to this analysis result, reducing unnecessary data cleaning actions for certain data. For example: before cleaning, it is necessary to prepare a master data comparison table related to the cleaning object, such as: internal units, materials, devices, etc., and the comparison data can be imported into the data lake through the master data cleaning module. Afterwards, it is necessary to configure the corresponding data cleaning strategy for each type of data to be cleaned, so as to facilitate the subsequent data cleaning tasks to perform data cleaning according to the configuration. Each type of data to be cleaned must have a corresponding job task to perform the cleaning action, and the job task automatically reads the configured cleaning strategy to clean the data, ensuring that the data can be cleaned and processed in real time after entering the lake. Task execution will be carried out through the Flink streaming processing framework, which processes the data stream that needs to be cleaned in real time according to the defined cleaning rules and rule execution order, thus simplifying the cleaning process, shortening the data cleaning time, and greatly improving the efficiency of data cleaning. Figure 6 shown.

[0192] According to an embodiment of the present invention, an embodiment of a data cleaning method based on real-time data warehouse technology is provided. It should be noted that the steps shown in the flowchart of the accompanying drawings can be executed in a computer device such as a set of computer executable instructions, and although the logical order is shown in the flowchart, in some cases, the steps shown or described can be executed in an order different from that shown here.

[0193] Figure 7 It is a flow chart of another data cleaning method based on real-time data warehouse technology according to an embodiment of the present invention.

[0194] like Figure 7 As shown, another data cleaning method based on real-time data warehouse technology is provided in an embodiment of the present invention, which is applied to a real-time data warehouse architecture. The real-time data warehouse architecture includes a data source, a data warehouse and an application end.

[0195] The data cleaning method based on real-time data warehouse technology includes:

[0196] S201: Acquire real-time data based on the data transmission task in the real-time data warehouse architecture. For details, refer to the relevant description of S101, which will not be repeated here.

[0197] S202: Perform quality analysis on the real-time data to determine the data quality analysis result of the real-time data. For details, refer to the relevant description of S102, which will not be repeated here.

[0198] S203: Determine whether the real-time data meets the preset evaluation index based on the integrity, consistency and accuracy of the real-time data. When it is determined that the real-time data meets the preset evaluation index, execute the data transmission task. When it is determined that the real-time data does not meet the preset evaluation index, execute the following technical solutions of S204-S207.

[0199] In this embodiment, the data quality analysis results also include the integrity, consistency and accuracy of the real-time data.

[0200] In this embodiment, executing the data transmission task includes: transmitting real-time data to a data warehouse of a real-time data warehouse architecture.

[0201] In this embodiment, whether the real-time data meets the preset evaluation index is determined based on the integrity, consistency and accuracy of the real-time data, specifically including:

[0202] Determine whether the integrity of the real-time data meets the first preset standard value, whether the consistency meets the second preset standard value, and whether the accuracy meets the third preset standard value.

[0203] When it is determined that the integrity of the real-time data meets the first preset standard value, the consistency meets the second preset standard value, and the accuracy meets the third preset standard value, it is determined that the real-time data meets the preset evaluation index. When it is determined that the integrity of the real-time data does not meet the first preset standard value, the consistency does not meet the second preset standard value, and the accuracy does not meet the third preset standard value, it is determined that the real-time data does not meet the preset evaluation index.

[0204] As an example, the integrity meets the first preset standard value: the missing value statistical index (the number of missing values / the total data volume of real-time data) is less than the first preset standard value, such as 5%.

[0205] The consistency meets the second preset standard value: the data consistency index is within a reasonable range, such as the second preset standard value [70%, 90%].

[0206] The accuracy meets the third preset standard value: the accuracy statistical index (the amount of compliant data / the total amount of data) is greater than the third preset standard value, such as the third preset standard value of 80%.

[0207] S204: Determine initial cleaning rules based on all data quality issues in the data quality analysis results. For details, refer to the relevant description of S103, which will not be repeated here.

[0208] S205: Obtain a first preset number of sample data from the preset cleaning data set, obtain a second preset number of cleaning data from the real-time data, and determine the first preset number of sample data and the second preset number of cleaning data as target data. Perform data cleaning on the target data according to the initial cleaning rule to obtain the cleaned target data. For details, refer to the relevant description of S104, which will not be repeated here.

[0209] S206: Determine whether the cleaned target data meets the preset evaluation index. The first case: when it is determined that the cleaned target data does not meet the preset evaluation index, the initial cleaning rule is adjusted. The target data is cleaned based on the adjusted initial cleaning rule to obtain the cleaned target data. When it is determined that the cleaned target data meets the preset evaluation index, the adjusted initial cleaning rule is determined as the target cleaning rule. For details, refer to the relevant description of S105, which will not be repeated here.

[0210] S207: Perform data cleaning on the real-time data based on the target cleaning rule. For details, refer to the relevant description of S106, which will not be repeated here.

[0211] In an optional implementation, when it is determined that the real-time data does not meet the preset evaluation index, and the data transmission task is a data transmission task of transmitting data from a data source of the real-time data warehouse architecture to a data warehouse, after the real-time data is cleaned based on the target cleaning rule, the data cleaning method based on the real-time data warehouse technology further includes:

[0212] Execute data transmission tasks: transmit the target cleansing data obtained by data cleaning real-time data based on target cleaning rules to the data warehouse of the real-time data warehouse architecture.

[0213] The two main technical solutions of the present invention can effectively improve the efficiency and quality of data cleaning in the data warehouse. One measure is to improve the efficiency and quality of data cleaning by upgrading the technical architecture. Other measures are to improve the efficiency and quality of data cleaning by improving and optimizing the cleaning strategy. After actual application verification of multiple data governance projects, under the same test environment and the same test data scale, the comparison of the application effect before and after the upgrade of the architecture is shown in Table 1, and the comparison of the application effect of the data cleaning solution is shown in Table 2:

[0214] Table 1 Comparison of application effects of technical architecture upgrade

[0215]

[0216] Table 2 Comparison of the application effects of optimized cleaning rules

[0217]

[0218] The above comparison results show that the scheme provided by the present invention can effectively improve the data cleaning efficiency and quality of data warehouses, and the comprehensive improvement effect can reach 60%. By continuously improving data cleaning efficiency and data quality, the timeliness and accuracy requirements of enterprises for decision analysis can be met, and reliable basis can be provided for enterprise business decision analysis in a timely manner.

[0219] The present invention is applicable to all current enterprise informatization and digitalization construction projects, especially data governance projects and big data projects.

[0220] Application prospects:

[0221] 1. Improve data governance efficiency: By upgrading the data warehouse technology architecture and optimizing the cleaning strategy, the efficiency and quality of massive data governance in enterprises can be greatly improved, the quality of enterprise data products can be improved, and high-quality data support can be provided in a timely manner for enterprise operation management, decision-making analysis, and quality and efficiency improvement.

[0222] 2. Currently, enterprises are carrying out large-scale data governance project construction. Data cleaning is an important environment. This tool is also applicable to the application scenarios of all data governance projects to meet the requirements of data governance.

[0223] 3. Promote technological innovation: The combination of the two parts of this invention will promote innovation and development in the field of data governance technology and bring new technical solutions to enterprise data governance and big data applications.

[0224] It should be noted that the contents not described in detail in the specification of the present invention belong to the common knowledge of those skilled in the art.

[0225] In this embodiment, a data cleaning device based on real-time data warehouse technology is also provided. A single device is used to implement the above-mentioned embodiments and optional implementation methods. Those that have been described will not be repeated. As used below, the term "module" can implement a combination of software and / or hardware for a predetermined function. Although the devices described in the following embodiments are preferably implemented in software, the implementation of hardware, or a combination of software and hardware, is also possible and conceivable.

[0226] Figure 8 It is a structural schematic diagram of a data cleaning device based on real-time data warehouse technology in an embodiment of the present invention.

[0227] The present invention provides a data cleaning device based on real-time data warehouse technology, such as Figure 8 As shown, the data cleaning device based on real-time data warehouse technology is applied to the real-time data warehouse architecture, including:

[0228] The first processing module 11 is used to obtain real-time data based on the data transmission task in the real-time data warehouse architecture.

[0229] The second processing module 12 is used to perform quality analysis on the real-time data to determine a data quality analysis result of the real-time data. The data quality analysis result includes: at least one data quality problem.

[0230] The third processing module 13 is used to determine initial cleaning rules according to all data quality problems in the data quality analysis results.

[0231] The fourth processing module 14 is used to obtain a first preset number of sample data from the preset cleaning data set, obtain a second preset number of cleaning data from the real-time data, determine the first preset number of sample data and the second preset number of cleaning data as target data, and perform data cleaning on the target data according to the initial cleaning rule to obtain the cleaned target data.

[0232] The fifth processing module 15 includes a first processing unit 121 and a second processing unit 152 .

[0233] The first processing unit 151 is used to determine whether the cleaned target data meets a preset evaluation index.

[0234] The second processing unit 152 is used to adjust the initial cleaning rule when it is determined that the cleaned target data does not meet the preset evaluation index. The target data is cleaned based on the adjusted initial cleaning rule to obtain the cleaned target data, and when it is determined that the cleaned target data meets the preset evaluation index, the adjusted initial cleaning rule is determined as the target cleaning rule.

[0235] The sixth processing module 16 is used to perform data cleaning on the real-time data based on the target cleaning rules.

[0236] In an optional implementation, the third processing module 13 is specifically used to determine a cleaning rule corresponding to each data quality problem in the data quality analysis result. One data quality problem corresponds to one cleaning rule.

[0237] Determine the execution order of the cleaning rules corresponding to each data quality problem in the data quality analysis results to obtain the initial cleaning rules.

[0238] In an optional implementation, the third processing module 13 is specifically configured to obtain a pre-established cleaning rule database. The cleaning rule database includes data quality issues and cleaning rules stored in a corresponding relationship.

[0239] According to each data quality problem in the data quality analysis result, a search is performed in the cleaning rule database to obtain the cleaning rule corresponding to each data quality problem in the data quality analysis result.

[0240] In an optional implementation, the fifth processing module 15 further includes a third processing unit, which is used to determine the initial cleaning rule as the target cleaning rule when it is determined that the cleaned target data meets the preset evaluation index.

[0241] In an optional implementation, the first processing unit 151 in the fifth processing module 15 is specifically used to determine the integrity, consistency and accuracy of the cleaned target data.

[0242] Determine whether the integrity of the cleaned target data meets the first preset standard value, whether the consistency meets the second preset standard value, and whether the accuracy meets the third preset standard value.

[0243] When it is determined that the integrity of the cleaned target data meets the first preset standard value, the consistency meets the second preset standard value, and the accuracy meets the third preset standard value, it is determined that the cleaned target data meets the preset evaluation index. When it is determined that the integrity of the cleaned target data does not meet the first preset standard value, the consistency does not meet the second preset standard value, and the accuracy does not meet the third preset standard value, it is determined that the cleaned target data does not meet the preset evaluation index.

[0244] In an optional embodiment, the data quality analysis results also include the integrity, consistency and accuracy of the real-time data. The data cleaning device based on real-time data warehouse technology also includes a pre-judgment module for determining whether the real-time data meets preset evaluation indicators based on the integrity, consistency and accuracy of the real-time data before determining the initial cleaning rules based on all data quality issues in the data quality analysis results.

[0245] When it is determined that the real-time data meets the preset evaluation index, the data transmission task is executed. When it is determined that the real-time data does not meet the preset evaluation index, the step of determining the initial cleaning rule according to all the data quality problems in the data quality analysis result is executed.

[0246] In an optional embodiment, the data cleaning device based on real-time data warehouse technology also includes an execution module, which is used to transmit target cleaned data obtained by data cleaning the real-time data based on the target cleaning rules to the data warehouse of the real-time data warehouse architecture after the real-time data is cleaned based on the target cleaning rules.

[0247] The further functional description of each of the above modules and units is the same as that of the above corresponding embodiments and will not be repeated here.

[0248] The data cleaning device based on real-time data warehouse technology in this embodiment is presented in the form of a functional unit, where the unit refers to an ASIC (Application Specific Integrated Circuit) circuit, a processor and memory that executes one or more software or fixed programs, and / or other devices that can provide the above functions.

[0249] The embodiment of the present invention also provides a computer device having the above Figure 8 The data cleaning device based on real-time data warehouse technology is shown.

[0250] See also Fig. 9 , Fig. 9 Schematic diagram of the hardware structure of the computer device according to the embodiment of the present invention. Fig. 9 As shown, the computer device includes: one or more processors 10, a memory 20, and interfaces for connecting various components, including high-speed interfaces and low-speed interfaces. Various components are connected to each other using different buses for communication, and can be installed on a common mainboard or installed in other ways as needed. The processor can process instructions executed in the computer device, including instructions stored in or on the memory to display the graphical information of the GUI on an external input / output device (such as a display device coupled to the interface). In an optional embodiment, if necessary, multiple processors and / or multiple buses can be used together with multiple memories and multiple memories. Similarly, multiple computer devices can be connected, and each device provides some necessary operations (for example, as a server array, a group of blade servers, or a multi-processor device). Fig. 9 A processor 10 is taken as an example.

[0251] The processor 10 may be a central processing unit, a network processor or a combination thereof. The processor 10 may further include a hardware chip. The hardware chip may be a dedicated integrated circuit, a programmable logic device or a combination thereof. The programmable logic device may be a complex programmable logic device, a field programmable gate array, a general purpose array logic or any combination thereof.

[0252] The memory 20 stores instructions executable by at least one processor 10, so that at least one processor 10 executes the method shown in the above embodiment.

[0253] The memory 20 may include a program storage area and a data storage area, wherein the program storage area may store an operating device, an application required for at least one function. The data storage area may store data created according to the use of the computer device, etc. In addition, the memory 20 may include a high-speed random access memory, and may also include a non-transient memory, such as at least one disk storage device, a flash memory device, or other non-transient solid-state storage devices. In an optional embodiment, the memory 20 may optionally include a memory remotely arranged relative to the processor 10, and these remote memories may be connected to the computer device via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0254] The memory 20 may include a volatile memory, such as a random access memory. The memory may also include a non-volatile memory, such as a flash memory, a hard disk or a solid state drive. The memory 20 may also include a combination of the above-mentioned types of memory.

[0255] The computer device further comprises a communication interface 30 for the computer device to communicate with other devices or a communication network.

[0256] The embodiment of the present invention also provides a computer-readable storage medium. The method according to the embodiment of the present invention can be implemented in hardware, firmware, or can be implemented as a computer code that can be recorded in a storage medium, or can be implemented as a computer code that is originally stored in a remote storage medium or a non-temporary machine-readable storage medium and will be stored in a local storage medium through a network download, so that the method described herein can be stored in such software processing on a storage medium using a general-purpose computer, a dedicated processor, or programmable or dedicated hardware. Among them, the storage medium can be a disk, an optical disk, a read-only storage memory, a random access memory, a flash memory, a hard disk or a solid-state hard disk, etc. Further, the storage medium can also include a combination of the above-mentioned types of memories. It can be understood that a computer, a processor, a microprocessor controller, or programmable hardware includes a storage component that can store or receive software or computer code. When the software or computer code is accessed and executed by a computer, a processor, or hardware, the method shown in the above embodiment is implemented.

[0257] A part of the present invention may be applied as a computer program product, such as a computer program instruction, which, when executed by a computer, can call or provide the method and / or technical solution according to the present invention through the operation of the computer. Those skilled in the art should understand that the existence of the computer program instruction in a computer-readable medium includes, but is not limited to, a source file, an executable file, an installation package file, etc., and accordingly, the way in which the computer program instruction is executed by the computer includes, but is not limited to: the computer directly executes the instruction, or the computer compiles the instruction and then executes the corresponding compiled program, or the computer reads and executes the instruction, or the computer reads and installs the instruction and then executes the corresponding installed program. Here, the computer-readable medium may be any available computer-readable storage medium or communication medium accessible to the computer.

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

Claims

1. A data cleaning method based on real-time data warehouse technology, applied to real-time data warehouse architecture, characterized in that: include: Acquire real-time data based on the data transmission tasks in the real-time data warehouse architecture; Performing quality analysis on the real-time data to determine a data quality analysis result of the real-time data; The data quality analysis results include: at least one data quality issue; Determine initial cleaning rules based on all data quality issues in the data quality analysis results; Acquire a first preset number of sample data from a preset cleaning data set, acquire a second preset number of cleaning data from the real-time data, and determine the first preset number of sample data and the second preset number of cleaning data as target data; Cleaning the target data according to the initial cleaning rule to obtain cleaned target data; Determine whether the cleaned target data meets the preset evaluation indicators; When it is determined that the cleaned target data does not meet the preset evaluation index, the initial cleaning rule is adjusted until the target data is cleaned based on the adjusted initial cleaning rule, and the cleaned target data obtained meets the preset evaluation index, and the adjusted initial cleaning rule is determined as the target cleaning rule; The real-time data is cleansed based on the target cleaning rule.

2. The method according to claim 1, characterized in that Determining the initial cleaning rules according to all the data quality issues in the data quality analysis results includes: Determine a cleaning rule corresponding to each data quality problem in the data quality analysis result; each data quality problem corresponds to one cleaning rule; Determine the execution order of the cleaning rules corresponding to each data quality problem in the data quality analysis result to obtain the initial cleaning rules.

3. The method according to claim 1, characterized in that The determining of the cleaning rules corresponding to each data quality problem in the data quality analysis result includes: Acquire a pre-established cleaning rule database; the cleaning rule database includes data quality issues and cleaning rules stored in a corresponding relationship; According to each data quality problem in the data quality analysis result, a search is performed in the cleaning rule database to obtain a cleaning rule corresponding to each data quality problem in the data quality analysis result.

4. The method according to claim 1, characterized in that The method further comprises: When it is determined that the cleaned target data meets the preset evaluation index, the initial cleaning rule is determined as the target cleaning rule.

5. The method according to claim 1, characterized in that The step of determining whether the cleaned target data meets the preset evaluation index includes: Determine the completeness, consistency and accuracy of the cleansed target data; Determine whether the integrity of the cleaned target data meets a first preset standard value, whether the consistency meets a second preset standard value, and whether the accuracy meets a third preset standard value; Among them, when it is determined that the integrity of the cleaned target data meets the first preset standard value, the consistency meets the second preset standard value, and the accuracy meets the third preset standard value, it is determined that the cleaned target data meets the preset evaluation indicators; when it is determined that the integrity of the cleaned target data does not meet the first preset standard value, the consistency does not meet the second preset standard value, and the accuracy does not meet the third preset standard value, it is determined that the cleaned target data does not meet the preset evaluation indicators.

6. The method according to claim 5, characterized in that The data quality analysis result also includes the integrity, consistency and accuracy of the real-time data. Before determining the initial cleaning rules based on all the data quality issues in the data quality analysis result, it also includes: Determining whether the real-time data meets preset evaluation indicators based on the integrity, consistency and accuracy of the real-time data; When it is determined that the real-time data meets the preset evaluation index, the data transmission task is executed; when it is determined that the real-time data does not meet the preset evaluation index, the step of determining the initial cleaning rules according to all the data quality problems in the data quality analysis result is executed.

7. The method according to claim 1, characterized in that When the data transmission task is a data transmission task of transmitting data from a data source of a real-time data warehouse architecture to a data warehouse, after the real-time data is cleansed based on the target cleaning rule, the method further includes: The target cleansing data obtained by performing data cleansing on the real-time data based on the target cleansing rule is transmitted to the data warehouse of the real-time data warehouse architecture.

8. A data cleaning device based on real-time data warehouse technology, applied to real-time data warehouse architecture, characterized in that: include: A first processing module is used to obtain real-time data based on a data transmission task in a real-time data warehouse architecture; A second processing module is used to perform quality analysis on the real-time data to determine a data quality analysis result of the real-time data; The data quality analysis results include: at least one data quality issue; A third processing module is used to determine initial cleaning rules according to all data quality problems in the data quality analysis result; a fourth processing module, configured to obtain a first preset number of sample data from the preset cleaned data set, obtain a second preset number of cleaned data from the real-time data, determine the first preset number of sample data and the second preset number of cleaned data as target data; and perform data cleaning on the target data according to the initial cleaning rule to obtain cleaned target data; The fifth processing module includes a first processing unit and a second processing unit; The first processing unit is used to determine whether the cleaned target data meets the preset evaluation index; The second processing unit is configured to adjust the initial cleaning rule when it is determined that the cleaned target data does not meet the preset evaluation index, until the target data is cleaned based on the adjusted initial cleaning rule, the cleaned target data obtained meets the preset evaluation index, and the adjusted initial cleaning rule is determined as the target cleaning rule; The sixth processing module is used to perform data cleaning on the real-time data based on the target cleaning rule.

9. A computer device, characterized in that: include: A memory and a processor, wherein the memory and the processor are communicatively connected to each other, the memory stores computer instructions, and the processor executes the data cleaning method based on real-time data warehouse technology as described in any one of claims 1 to 7 by executing the computer instructions.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer instructions, and the computer instructions are used to enable a computer to execute the data cleaning method based on real-time data warehouse technology according to any one of claims 1 to 7.

Images