Data cleaning and updating system for data warehouse

Abstract

The invention relates to the field of data warehouses, in particular to a data cleaning and updating system for a data warehouse, and the system comprises an operation load detection module which is used for detecting the operation load of the system; the data grouping management module is used for grouping to-be-processed data to form a plurality of data sets, and dividing the data sets into three classes from high to low according to the importance degrees, namely key data, important data and general data; the load pre-estimation module is used for pre-estimating the increment of the specific data set to the system operation load after data processing and updating are started; and the strategy making module is used for executing cleaning and updating operations of different data sets according to the current operation load of the system. According to the method, the operation load of the system is detected in real time, the execution strategy is set in combination with the estimated increase amount of the operation load of the system, data cleaning and updating operations of different data sets are executed separately, and the situation that the operation load of the system is too large and the user experience is affected when the data sets are cleaned and updated is avoided.

Description

Technical Field

[0001] The present invention relates to the technical field of data warehouses, and in particular to a data cleaning and updating system for a data warehouse. Background Art

[0002] A data warehouse is a system for storing, managing, and analyzing large amounts of data. It is designed to support enterprise-level data analysis and decision-making. The core purpose of a data warehouse is to integrate data from different sources into a central location for easy querying and analysis. The data in the data warehouse needs to be reasonably cleaned and updated to maintain the functions of the data warehouse.

[0003] Chinese Patent with Publication No. CN119128022A discloses a data processing method, device, electronic device, and storage medium in a data warehouse. The data processing method in the data warehouse includes: obtaining a data warehouse's Hive table in the data warehouse, where the Hive table stores HDFS metadata; generating a first data table based on the HDFS metadata, and obtaining the life cycle metadata corresponding to the HDFS metadata. The first data table clusters and stores the HDFS metadata according to database fields, access fields, and partition fields; based on the access fields, partition fields, and life cycle metadata, clean or retain the HDFS metadata in the database corresponding to the database fields in the first data table. Through this disclosure, the storage space can be effectively managed and the storage cost can be reduced.

[0004] However, the above-disclosed solution has the following deficiencies: By customizing the life cycle for different data and regularly cleaning the data based on the life cycle, the data is dynamically retained or not retained, thereby reducing the storage cost. However, the impact on the system operation during data cleaning is not considered, which may affect the smooth use of users. Summary of the Invention

[0005] The object of the present invention is to address the problem that the impact on system operation is not considered in data cleaning and updating in the background art, and to propose a data cleaning and updating system for a data warehouse.

[0006] The technical solution of the present invention: A data cleaning and updating system for a data warehouse includes a data acquisition module, a data cleaning module, and a data updating module. The data acquisition module is used to obtain multi-source data that needs to be input into the data warehouse. The data cleaning module is used to clean the data. The data updating module is used to update the cleaned data into the data warehouse; it further includes:

[0007] A running load detection module, which is used to detect the running load of the system;

[0008] The data grouping management module groups the data to be processed to form multiple data sets, and divides the data sets into three categories from high to low in terms of importance, namely critical data, important data, and general data;

[0009] The load prediction module is used to predict the increase in the system operation load caused by a specific data set after the start of data processing and update;

[0010] And the policy formulation module performs cleaning and update operations on different data sets according to the current operation load of the system.

[0011] Preferably, data cleaning includes data standardization, data verification, data deduplication, data correction, data desensitization, data conversion, data cleaning, and data integration.

[0012] Preferably, it further includes a monitoring module, which is used to monitor the status during the data cleaning and data update processes, and generate relevant reports on data quality, update status, and abnormal conditions.

[0013] Preferably, the content detected by the operation load detection module includes CPU usage rate, memory occupancy rate, and disk usage rate. Weights are assigned to the CPU usage rate, memory occupancy rate, and disk usage rate respectively. By multiplying the weights with the corresponding contents and then adding the three multiplied values, the current operation load of the system is obtained.

[0014] Preferably, the grouping basis of the data grouping management module includes grouping by business relevance, grouping by data characteristics, grouping by data scale, grouping by security compliance, and grouping by data usage.

[0015] Preferably, the working process of the load prediction module is as follows: S11, data collection: collect the execution records of the same or similar data sets from historical data; S12, feature extraction: extract the features of the data to be executed; S13, model training: create a resource consumption model, and use the collected historical data to train the resource consumption model to improve the model; S14, prediction calculation: combine historical data analysis, resource consumption model, and real-time system operation load data to calculate the predicted load increase of the system after executing a specific data set; S15, model optimization: adjust and optimize the prediction model according to the difference between the prediction result and the actual execution result.

[0016] Preferably, the working process of the load prediction module is as follows: S21, static feature analysis: analyze the size, structural complexity, and cleaning rule complexity of the data set; S22, benchmark test: conduct a benchmark test on cleaning and updating a data set of a specific size when the system is under low load, and record the resource consumption situation; S23, proportional estimation: perform proportional estimation on the resource consumption of the benchmark test and the resource consumption of the actual data set to obtain the predicted increase in the system operation load;

[0017] S24. Proportion Optimization: Adjust and optimize the proportion algorithm according to the difference between the estimated result and the actual execution result.

[0018] Preferably, in the policy formulation module, when the current system operation load is A, and the estimated increase in system operation load after the cleaning and updating of the to-be-executed data set is B, and A + B ≥ 90%, suspend the cleaning and updating of all to-be-processed data sets except those that need to be updated in real time; when A + B < 90%, perform the cleaning and updating operations according to the importance degree of the data sets.

[0019] Compared with the prior art, the present invention has the following beneficial technical effects: Collect the data that needs to be cleaned and updated, then group the data to form multiple data sets, and divide them into critical, important, and general data sets according to the situation of the data sets, and estimate the increase in system operation load after the start of data cleaning and updating for each data set, detect the system operation load in real time, and combine with the estimated increase in system operation load to set the execution policy, and separately execute the data cleaning and updating operations of different data sets, so as to avoid excessive system operation load caused by data cleaning and updating, and affect the user experience. Description of the Drawings

[0020] Figure 1 It is a schematic structural diagram of an embodiment of the present invention;

[0021] Figure 2 It is a schematic diagram of the data cleaning method;

[0022] Figure 3 It is a working flowchart of the load estimation module;

[0023] Figure 4 It is another working flowchart of the load estimation module. Detailed Embodiments

[0024] Embodiment 1, as Figure 1 shown, a data cleaning and updating system for a data warehouse proposed by the present invention includes a data acquisition module, a data cleaning module, and a data updating module. The data acquisition module is used to obtain multi-source data that needs to be input into the data warehouse. The data cleaning module is used to clean the data. The data updating module is used to update the cleaned data into the data warehouse; it further includes:

[0025] An operation load detection module, which is used to detect the operation load of the system;

[0026] A data grouping and management module, which groups the to-be-processed data to form multiple data sets, and divides the data sets into three categories from high to low according to the importance degree, namely critical data, important data, and general data;

[0027] A load prediction module, which is used to predict the increase in the system operation load caused by a specific data set after the start of data processing and update;

[0028] A strategy formulation module, which performs cleaning and update operations on different data sets according to the current system operation load;

[0029] And a monitoring module, which is used to monitor the status during data cleaning and data update, ensure the smooth progress of tasks, and generate relevant reports on data quality, update status and abnormal conditions.

[0030] Embodiment 2, as Figure 2 shown, a data cleaning and update system for a data warehouse proposed by the present invention. Compared with Embodiment 1, this embodiment details the data cleaning method.

[0031] Data cleaning includes data standardization, data verification, data deduplication, data correction, data desensitization, data conversion, data cleaning and data integration; specifically, data standardization refers to converting data into a unified format, such as date format, currency unit, etc., and converting data from different sources into standardized codes or categories for easy integration and analysis; data verification refers to ensuring that data fields conform to predefined data types, such as integers, floating-point numbers, strings, etc., checking whether data values are within a reasonable range, ensuring that all necessary fields are filled and there are no missing values; data deduplication refers to identifying and marking duplicate records and deleting or merging duplicate records; data correction refers to discovering and correcting incorrect or abnormal data values and resolving inconsistencies in the data set, such as contradictions of the same information in different fields or records; data desensitization refers to identifying sensitive information in the data, such as personal identity information, credit card numbers, etc., and performing desensitization processing on sensitive information, such as hiding, replacing or encrypting; data conversion refers to converting data from one type to another type, such as converting a string to a date and adjusting the data structure to adapt to the data warehouse model; data cleaning refers to deleting data that is irrelevant or useless for analysis, filling missing data using appropriate strategies (such as average, median, most frequent value or model-based prediction), reducing noise in the data, and smoothing outliers or extreme points; data integration refers to merging data sets from different sources together, ensuring that the fields in the merged data set can be aligned for easy analysis.

[0032] Embodiment 3, a data cleaning and update system for a data warehouse proposed by the present invention. Compared with Embodiment 1, this embodiment details the detection calculation method of the operation load detection module and the data grouping method.

[0033] The content detected by the running load detection module includes CPU usage rate, memory occupancy rate, and disk usage rate. Weights are assigned to the CPU usage rate, memory occupancy rate, and disk usage rate respectively. By multiplying the weights with the corresponding content and then adding the three resulting values, the running load of the current system is obtained. For example, running load = a · CPU usage rate + b · memory occupancy rate + c · disk usage rate, where a, b, and c represent the weights of the three contents. Suppose a, b, and c are 0.4, 0.3, and 0.3 respectively, the CPU usage rate is 50%, the memory occupancy rate is 60%, and the disk usage rate is 40%. Then the current running load = 0.4 × 50% + 0.3 × 60% + 0.3 × 40% = 50%.

[0034] The grouping basis of the data grouping management module includes grouping by business relevance, grouping by data characteristics, grouping by data scale, grouping by security compliance, and grouping by data usage. Specifically, grouping by business relevance means that data grouping is usually based on business logic and requirements to ensure that the data within the same group is relevant in terms of business; grouping by data characteristics means grouping according to the characteristics of the data set, such as data type, data source, data update frequency, etc.; grouping by data scale means combining according to the size of the data; grouping by security compliance means grouping based on data sensitivity or compliance requirements to ensure data security and compliance; grouping by data usage means grouping according to the usage or application scenario of the data, such as analysis, reporting, real-time monitoring, etc.

[0035] Embodiment 4, as Figure 3 and Figure 4 shown, a data cleaning and updating system for a data warehouse proposed by the present invention, compared with Embodiment 3, this embodiment details two working processes of the compliance prediction module.

[0036] First, the working process of the load prediction module is as follows: S11, data collection: collect the execution records of the same or similar data sets from historical data, including data such as execution time and resource consumption; S12, feature extraction: extract the features of the data to be executed, such as data volume size, data structure complexity, historical execution load, etc.; S13, model training: create a resource consumption model and use the collected historical data to train the resource consumption model to improve the model; S14, prediction calculation: combine historical data analysis, resource consumption model, and real-time system running load data to calculate the predicted load increase of the system after executing a specific data set; S15, model optimization: adjust and optimize the prediction model according to the difference between the prediction result and the actual execution result to improve the accuracy of future predictions.

[0037] Second, the working process of the load prediction module is as follows: S21. Static feature analysis: Analyze the size, structural complexity, and cleaning rule complexity of the data set; S22. Benchmark test: When the system is under low load, conduct a benchmark test on cleaning and updating a data set of a specific size, and record the resource consumption; S23. Ratio estimation: Estimate the ratio of the resource consumption of the benchmark test to the resource consumption of the actual data set to obtain the estimated increase in the system operation load;

[0038] S24. Ratio optimization: Adjust and optimize the ratio algorithm according to the difference between the estimated result and the actual execution result.

[0039] Embodiment 5. A data cleaning and updating system for a data warehouse proposed by the present invention. Compared with Embodiment 1, this embodiment details how to formulate strategies for data cleaning and updating.

[0040] In the strategy formulation module, for the current system operation load A and the estimated increase in the system operation load B after cleaning and updating the data set to be executed, when A + B ≥ 90%, suspend the cleaning and updating of all data sets to be processed except those that need to be updated in real time; when A + B < 90%, perform cleaning and updating operations according to the importance of the data sets. Specifically, when performing cleaning and updating operations on critical data sets, define: the estimated increase in the system operation load for critical data sets is C, the estimated increase in the system operation load for important data sets is D, and the estimated increase in the system operation load for general data sets is E, where C + D + E = B. If C + D + E + A < 90%, perform data cleaning and updating operations on critical data sets, important data sets, and general data sets simultaneously; when C + D + E + A ≥ 90% and C + D + A < 90%, perform data cleaning and updating operations on critical data sets and important data sets; when C + D + E + A ≥ 90% and C + A < 90%, only perform data cleaning and updating operations on critical data sets.

[0041] In summary, when the present invention is used, first collect the data that needs to be cleaned and updated, then group the data to form multiple data sets, and divide them into critical, important, and general data sets according to the situation of the data sets. Estimate the increase in the system operation load after starting data cleaning and updating for each data set, detect the system operation load in real time, and combine the estimated increase in the system operation load to set execution strategies, and separately perform data cleaning and updating operations on different data sets to avoid excessive system operation load caused by cleaning and updating data sets and affecting the user experience.

[0042] The above has described in detail the embodiments of the present invention in conjunction with the accompanying drawings. However, the present invention is not limited thereto. Various changes can be made without departing from the spirit of the present invention within the knowledge scope of those skilled in the art to which the present invention pertains.

Claims

1. A data cleaning and updating system for a data warehouse, comprising a data acquisition module, a data cleaning module and a data updating module, wherein the data acquisition module is used to acquire multi-source data to be entered into the data warehouse, the data cleaning module is used to clean the data, and the data updating module is used to update the cleaned data into the data warehouse; characterized in that: Also includes: Operation load detection module, used to detect the operation load of the system; The data grouping management module groups the data to be processed into multiple data sets, and divides the data sets into three categories from high to low importance, namely key data, important data and general data; The load estimation module is used to estimate the increase in system operation load caused by a specific data set after data processing and updating begins; And the strategy formulation module performs cleaning and updating operations on different data sets according to the current operating load of the system.

2. The data cleaning and updating system for a data warehouse according to claim 1, characterized in that: Data cleaning includes data standardization, data verification, data deduplication, data correction, data desensitization, data conversion, data cleaning and data integration.

3. The data cleaning and updating system for a data warehouse according to claim 1, characterized in that: It also includes a monitoring module to monitor the status of data cleaning and data update processes and generate relevant reports on data quality, update status and abnormal situations.

4. The data cleaning and updating system for a data warehouse according to claim 1, characterized in that: The operating load detection module detects the CPU usage, memory usage and disk usage. The required weights are assigned to the CPU usage, memory usage and disk usage respectively. The weights are multiplied by the corresponding content, and the three values ​​obtained after the multiplication are added together to obtain the operating load of the current system.

5. The data cleaning and updating system for a data warehouse according to claim 1, characterized in that: The grouping basis of the data grouping management module includes grouping by business relevance, grouping by data characteristics, grouping by data size, grouping by security compliance, and grouping by data usage.

6. The data cleaning and updating system for a data warehouse according to claim 1, characterized in that: The working process of the load estimation module is as follows: S11, data collection: collect execution records of the same or similar data sets from historical data; S12, feature extraction: extract features of the data to be executed; S13, model training: create a resource consumption model, use the collected historical data to train the resource consumption model, and improve the model; S14, estimated calculation: combine historical data analysis, resource consumption model and real-time system operation load data to calculate the system estimated load increase after executing a specific data set; S15, model optimization: adjust and optimize the estimation model according to the difference between the estimated results and the actual execution results.

7. The data cleaning and updating system for a data warehouse according to claim 1, characterized in that: The working process of the load estimation module is as follows: S21, static feature analysis: analyze the size, structural complexity and cleaning rule complexity of the data set; S22, benchmark test: when the system is under low load, clean and update the benchmark test of a data set of a specific size, and record the resource consumption; S23, proportional estimation: estimate the resource consumption of the benchmark test and the resource consumption of the actual data set in proportion to obtain the estimated increase in the system operating load; S24, proportional optimization: adjust and optimize the proportional algorithm according to the difference between the estimated results and the actual execution results.

8. The data cleaning and updating system for a data warehouse according to claim 1, characterized in that: In the strategy formulation module, the current system operating load A, the estimated increase in system operating load after cleaning and updating the pending data set B, when A+B≥90%, suspend the cleaning and updating of all pending data sets except those that need to be updated in real time; when A+B<90%, perform cleaning and update operations according to the importance of the data set.

Images