Data processing method and system in automatic ETL (Extract-Transform-Load) process of counting warehouse

Abstract

The invention discloses a data processing method and system in an automatic ETL (Extract-Transform-Load) process of a data warehouse. The invention relates to the technical field of data warehouses. And extracting data from the data source table according to a data extraction step defined in the ETL flow chart. In the extraction process, according to a configured integrity detection rule, preliminarily checking the extracted data, including checking whether the number of record lines conforms to expectation and whether a field value is null or missing; through an integrity detection rule, data which does not meet requirements can be effectively filtered out in a data extraction stage, such as data with inconsistent recording lines, null field values or missing data. Therefore, 'junk data 'is prevented from entering a subsequent processing flow, and the data entering the conversion stage has a relatively high quality basis. For example, in the customer information extraction process, if key fields such as customer names and contact information are empty, data can be marked and subsequently processed, and it is ensured that subsequent analysis is based on complete and effective customer data.

Description

Technical Field

[0001] The present invention relates to the technical field of data warehouses, and specifically designs the optimization of the ETL (Extract-Transform-Load) process in data warehouse construction and operation and maintenance, especially in the direction of data quality assurance mechanisms. Background Art

[0002] In the process of constructing and operating a data warehouse (DW), the ETL (Extract-Transform-Load) process, as the core link of data integration and consolidation, its execution quality is directly related to the availability, reliability, and decision-making support ability of the data warehouse.

[0003] With the advent of the big data era, as an important infrastructure for enterprise-level data management and analysis, the construction and operation and maintenance quality of the data warehouse directly affect the decision-making efficiency and business competitiveness of the enterprise. The ETL process, as the core step of data preparation in the data warehouse, is responsible for extracting data from multiple heterogeneous data sources, cleaning, transforming, and integrating it, and then loading it into the data warehouse, providing a high-quality data foundation for subsequent data analysis, mining, and decision-making support.

[0004] However, the traditional ETL process has significant deficiencies in data quality monitoring and assurance, resulting in frequent quality problems such as data errors, losses, and delays, which seriously restrict the effectiveness of the data warehouse.

[0005] That is, in terms of data quality assurance, the traditional ETL process faces issues such as inconsistent data source quality, complex transformation rules, and data loss or delay during the loading process, seriously affecting the overall performance and value of the data warehouse.

[0006] Therefore, the present invention proposes a data processing method and system in the automated ETL process of a data warehouse. Summary of the Invention

[0007] In view of this, the present invention hopes to provide a data processing method and system in the automated ETL process of a data warehouse to solve or alleviate the technical problems existing in the prior art, that is, how to solve the technical problems of inconsistent data source quality, complex transformation rules, and lack of comprehensive monitoring in the ETL process, and provide at least one beneficial option for this; the technical solution of the present invention is realized as follows:

[0008] In the first aspect, a data processing method in the automated ETL process of a data warehouse:

[0009] (1) Overview:

[0010] The present invention aims to achieve efficient and accurate data processing through a series of ordered steps. According to the parsed metadata, an ETL flow chart is generated by means of a preset algorithm and presented to the user for confirmation to ensure that the process is clear and reasonable. Data is extracted from the data source according to the flow chart, and at the same time, the data is initially checked according to the integrity detection rules, problems are recorded and the extraction continues. Then, the extracted data is transformed, and the data is verified according to the accuracy detection rules. At the same time, the upstream and downstream data tables are compared to check for consistency, and relevant problem information is recorded. During the processing, according to the preset threshold and the detected data quality problems, the detection rules are dynamically adjusted, and relevant personnel are notified in real time through the alarm system. Finally, according to the user's requirements and the detection results, SQL query statements are automatically generated to extract data from the data warehouse, providing data support for result visualization and ensuring the quality and efficiency of data processing.

[0011] (2) Technical solution:

[0012] Load the metadata related to the current ETL task from the metadata repository, including the data source table, target table, field names, field types, and ETL processing logic, parse the loaded metadata, construct an internal data structure, and then start to execute the following steps.

[0013] 2.1 Step S1, ETL flow chart generation:

[0014] According to the parsed metadata, use the preset flow chart generation algorithm to generate the flow chart of the current ETL task and display it on the visualization interface for the user to view and confirm.

[0015] 2.1.1 Step S100, Obtain the parsed metadata

[0016] Obtain the parsed metadata from Step S0 (metadata parsing), including data source information, data target information, transformation rules, and data cleaning rules; store the verified metadata in a temporary storage area for subsequent use by the flow chart generation algorithm.

[0017] 2.1.2 Step S101, Initialize the flow chart generation environment

[0018] According to the system configuration or user preferences, select a suitable flow chart generation algorithm (graph theory-based algorithm or template matching algorithm); initialize the visualization interface, set the interface layout, color scheme, and font size for subsequent display of the generated flow chart.

[0019] 2.1.3 Step S102, Apply the flow chart generation algorithm:

[0020] According to the data source, data target, and transformation rule information in the metadata, construct the nodes (data source nodes, transformation nodes, and data target nodes) and edges (representing data flow or transformation relationships) of the flow chart.

[0021] Using the selected flowchart generation algorithm, optimize and layout the constructed flowchart structure to generate a beautiful and easy-to-understand flowchart.

[0022] 2.1.4 Step S103, Generate and display the flowchart

[0023] Render the generated flowchart structure onto the visualization interface, using graphical elements (such as rectangles, arrows, text, etc.) to represent the nodes and edges of the flowchart; display the rendered flowchart to the user for viewing and confirmation. The user can perform operations such as zooming in, zooming out, and panning on the flowchart through the interactive functions on the interface to view the details more clearly.

[0024] 2.2 Step S2, Data extraction:

[0025] Extract data from the data source table according to the data extraction steps defined in the ETL flowchart.

[0026] During the extraction process, according to the configured integrity detection rules, conduct a preliminary check on the extracted data, including checking whether the number of record rows meets the expectations and whether the field values are empty or missing; if problems are found, record the problem information and continue to extract the subsequent data for unified processing later.

[0027] 2.2.1 Step S200, Prepare the extraction environment:

[0028] Use the provided data source connection information to attempt to establish a connection with the data source and verify whether the connection is successful; according to the data source type (relational database, NoSQL database, or file system), initialize the corresponding data extraction tool or library (JDBC, ODBC, or the pandas library in Python).

[0029] Load the configured integrity detection rules from the configuration file or database, including the expected range of the number of record rows, the non-null constraint of fields, and the field value range.

[0030] 2.2.2 Step S201, Extract data according to the flowchart definition:

[0031] According to the ETL flowchart, parse out the data source table, WHERE clause, and extraction fields that need to be extracted currently.

[0032] 2.2.3 Step S202, Preliminary data integrity check:

[0033] Check the number of record rows: Compare the number of extracted data record rows with the expected range to check whether it meets the expectations;

[0034] Check field non-null: Traverse the extracted data records and check whether each field is empty or missing;

[0035] Check the field value range: For fields configured with value range constraints, check whether the field values are within the expected range;

[0036] Record problem information: If data integrity problems are found, record the problem information, including the problem type (such as the number of recorded rows not matching, fields being empty, or field values exceeding the range), the problem record identifier (such as the record ID or row number), and the names and values of the problem fields.

[0037] 2.2.4 Step S203, Problem Data Processing and Continuing Extraction

[0038] Input: Recorded problem information, extracted data

[0039] Write the problem data to a temporary table, log file, or send it to an error handling queue; regularly update the extraction status, including the number of extracted records, the number of problem records, the extraction progress, etc., for monitoring the extraction process;

[0040] Continue to extract subsequent data, without interrupting the extraction process due to the problem data found currently, and continue to extract subsequent data records;

[0041] 2.2.5 Step S204, End-of-Extraction Processing

[0042] After extraction is completed, close the connection to the data source and release resources; summarize the extraction results of the number of extracted data records and problem records, and generate an extraction report; notify the user of the extraction results and problem data or pass them to subsequent ETL steps (such as data transformation) for further processing.

[0043] 2.3 Step S3, Data Transformation and Consistency Detection:

[0044] Process the extracted data according to the transformation rules defined in the ETL flow chart, including data format conversion, data cleaning, and data aggregation; according to the configured accuracy detection rules, perform year-on-year and month-on-month analysis on key data indicators to verify whether the data conforms to business logic. If problems are found, record the problem information; compare the key fields of the upstream and downstream data tables to check the consistency of the data during transmission; if data inconsistency problems are found, record the problem information, including the inconsistent fields, data values, and relevant information of the upstream and downstream tables.

[0045] 2.3.1 Step S300, Prepare the Transformation and Detection Environment:

[0046] Load the data transformation rules defined in the ETL flow chart from the configuration file or database, including data format conversion rules (such as date format conversion, numerical format conversion, etc.), data cleaning rules (such as removing special characters, filling missing values, etc.), and data aggregation rules (such as grouping and summing, averaging, etc. by specific fields); load the configured accuracy detection rules, clarify the definitions of key data indicators, the time periods for year-on-year and month-on-month analysis (such as daily month-on-month, weekly year-on-year, etc.), and business logic judgment conditions.

[0047] Initialize the corresponding data processing tools or libraries (such as the pandas and numpy libraries in Python, or custom data processing functions) according to the data type and conversion requirements; establish the association relationship between data tables based on the key fields of upstream and downstream data tables for subsequent consistency detection.

[0048] 2.3.2 Step S301, Data Format Conversion:

[0049] Identify the fields that need to be converted according to the conversion rules; use the initialized conversion tools or libraries to perform format conversion on the identified fields according to the conversion rules.

[0050] For example, convert the date in string type to date type, convert the numerical string to floating point number, etc.

[0051] 2.3.3 Step S302, Data Cleaning:

[0052] Perform data cleaning operations according to the cleaning rules; including removing special characters, filling missing values, and deleting duplicate records; record the operations performed and the records modified during the data cleaning process for subsequent auditing and tracing.

[0053] 2.3.4 Step S303, Data Aggregation:

[0054] Group the data according to the grouping fields in the aggregation rules; perform aggregation operations on each group, including summing, averaging, and counting; generate a new data set with the results of the aggregation operations, including the grouping fields and the aggregated metric values.

[0055] 2.3.5 Step S304, Data Accuracy Detection:

[0056] Determine the key data indicators that need to be analyzed year-on-year and month-on-month according to the accuracy detection rules; calculate the year-on-year and month-on-month values of the key data indicators according to the specified time periods; compare the calculated year-on-year and month-on-month indicators with the business logic judgment conditions to verify whether the data conforms to the business logic; if it is found that the data does not conform to the business logic, record the problem information, including the name of the key data indicator, the actual value, the expected value, and the time period.

[0057] 2.3.6 Step S305, data consistency check:

[0058] Based on the structure and business requirements of the upstream and downstream data tables, select key fields for consistency testing; compare the data values of corresponding records in the upstream and downstream data tables according to the key fields; if data inconsistency is found, record the problem information, including the inconsistent field name, data values in the upstream and downstream data tables, and record identifiers (such as primary key values).

[0059] 2.3.7 Step S306: Problem data processing and aggregation:

[0060] Isolate the recorded problem data from normal data for subsequent unified processing; summarize and count the recorded problem information to generate a problem report, including the problem type, number of problems and the data record information involved; notify the user of the problem report or pass it to subsequent ETL steps (such as correction processing before data loading) for further processing.

[0061] 2.3.8 Step S307, conversion and detection end processing:

[0062] Store the converted data in a temporary storage area or a designated data table for subsequent data loading; archive the problem report for subsequent audit and tracing;

[0063] Update the status of the ETL process to mark the data conversion and consistency check steps as completed.

[0064] 2.4 Step S4, dynamic rule adjustment and alarm:

[0065] Dynamically adjust detection rules based on preset thresholds and currently detected data quality issues. For example, if a data quality issue frequently occurs, the corresponding detection threshold can be adjusted appropriately. Detected data quality issues (including issues with completeness, accuracy, timeliness, consistency, etc.) are notified to relevant personnel in real time through the alarm system.

[0066] 2.4.1 Step S400: Loading preset thresholds and historical problem data:

[0067] Read various data quality detection indicators from the configuration file, including preset thresholds for completeness, accuracy, timeliness, and consistency;

[0068] Extract previously detected data quality problem records from the database, including problem type, frequency of occurrence, and data field information involved.

[0069] 2.4.2 Step S401: Dynamically adjust detection rules:

[0070] Classify and count the problems in the problem report, and calculate the occurrence frequencies of various problems; identify the frequently occurring data quality problems according to the preset frequency threshold.

[0071] For the frequently occurring data quality problems, combine with the historical problem data to evaluate whether it is necessary to adjust the corresponding detection threshold; according to the evaluation results, appropriately adjust the threshold in the detection rules. For example, if a certain data quality problem occurs frequently, the corresponding detection threshold can be appropriately relaxed or tightened.

[0072] Update the adjusted detection rules to the configuration file, and record the adjustment log, including the adjustment time, adjustment content, and adjustment reason information;

[0073] 2.4.3 Step S402, construct the alarm information:

[0074] Generate detailed alarm information according to the problem report, including the problem type (such as integrity problem, accuracy problem, etc.), problem description (such as specific field values, involved data ranges, etc.), and impact assessment (such as the impact degree on the business); determine the alarm level (such as severe, important, general, etc.) according to the severity of the problem; explain whether it involves the adjustment of the detection rules in the alarm information.

[0075] 2.4.4 Step S403, send the alarm notification:

[0076] According to the configuration of the alarm system, select a suitable alarm channel, such as email, SMS, instant messaging tools (such as enterprise WeChat, DingTalk); through the selected alarm channel, send the alarm information to relevant personnel in real time, such as the data quality management team, business department heads, etc.

[0077] 2.5 Step S5, report SQL assembly and data extraction:

[0078] Automatically generate an SQL query statement for extracting relevant data from the data warehouse according to the user requirements and detection results, extract the required data, and provide data support for subsequent result visualization.

[0079] 2.5.1 Step S500, requirement analysis and mapping:

[0080] Analyze the user requirement document, extract the key data extraction requirements, including the required data tables, fields, filtering conditions, grouping dimensions, and aggregation metrics, etc.; associate the user requirements with the detection results, and identify the data quality problems that may affect data extraction, such as a large number of missing values or abnormal values in certain fields, which need to be specially processed in the SQL query.

[0081] Determine the data range to be extracted from the data warehouse according to the user requirements and detection results, including the time range, business range, etc.

[0082] 2.5.2 Step S501, Data Model and Field Mapping:

[0083] Based on user requirements and data warehouse metadata, establish a report data model, clarify the data tables required for the report and their relationships; map the fields in the user requirements to the actual fields in the data warehouse to ensure the consistency of field names and meanings. For complex calculated fields, determine their calculation formulas and the source fields required.

[0084] For the special fields identified in the detection results (such as fields with data quality problems), determine the processing methods in the SQL query, such as filtering out missing values, replacing or correcting outliers, etc.

[0085] 2.5.3 Step S502, SQL Statement Assembly:

[0086] Based on the data model and field mapping results, construct a basic SQL query statement, including the SELECT clause (select the required fields), FROM clause (specify the data tables), WHERE clause (set the filtering conditions), etc. According to the grouping dimensions and aggregation metrics in the user requirements, add the GROUP BY clause and aggregation functions (such as SUM, AVG, COUNT, etc.) to the SQL query.

[0087] If the report data involves the connection of multiple data tables, determine the connection relationship between the tables (such as inner join, outer join, etc.) according to the data model, and add the JOIN clause to the SQL query. Optimize the performance of the SQL query, such as using indexes, avoiding unnecessary subqueries, and reasonably using temporary tables, etc.

[0088] Combine the above parts into a complete SQL query statement, and perform syntax checking and formatting.

[0089] 2.5.4 Step S503, SQL Statement Testing and Verification:

[0090] Include a data structure and data volume similar to the production environment to test the correctness and performance of the SQL query. Execute the assembled SQL query statement in the test data environment to obtain the query results. Compare the query results with the expected results to verify the correctness of the SQL query. For the results with differences, analyze the reasons and make corrections. Monitor the execution time and resource consumption of the SQL query in the test data environment, and evaluate whether the query performance meets the requirements. If the performance is poor, further optimize the SQL query.

[0091] 2.5.5 Step S504, Data Extraction and Storage:

[0092] Establish a connection with the data warehouse using the data warehouse connection information. Execute the verified SQL query statements to extract the required data from the data warehouse. Store the extracted data in a temporary storage area or a specified data table for subsequent result visualization processing. An appropriate storage format can be selected, such as CSV, Excel, database table, etc.

[0093] (III) Mechanism for Solving Technical Problems:

[0094] 3.1 Mechanism and Principle for Solving the Problem of Uneven Data Source Quality:

[0095] In the data extraction stage, conduct a preliminary check on the extracted data according to the preset integrity detection rules. This includes checking whether the number of recorded rows meets the expectations and whether the field values are empty or missing. If problems are found, record the problem information and continue to extract the subsequent data for unified processing later. Dynamically adjust the detection rules according to the preset thresholds and the currently detected data quality problems. For example, if a certain data quality problem appears frequently, the corresponding detection threshold can be appropriately adjusted.

[0096] By setting clear data integrity standards, conduct a preliminary screening of the data sources, mark the data that does not meet the standards, and prevent this data from entering the subsequent processing flow to ensure that the data entering the conversion stage has a certain quality foundation. For example, when extracting the customer information table, check whether keywords such as customer name and contact information are empty, and record the problem if they are.

[0097] The quality of the data source may change over time. Dynamically adjusting the detection rules can flexibly respond according to the actual situation. For example, if it is found that the null value rate of a certain field suddenly increases and exceeds the preset threshold, then the strictness of the null value detection for this field can be reduced, or the reason can be further analyzed in depth to adjust the data processing strategy.

[0098] 3.2 Mechanism and Principle for Solving the Problem of Complex Transformation Rules

[0099] Based on the parsed metadata, use the preset flowchart generation algorithm to generate the flowchart of the current ETL task and display it on the visualization interface for the user to view and confirm. Define the data transformation rules in the ETL flowchart, including data format conversion, data cleaning, and data aggregation, etc. Process the extracted data according to these rules.

[0100] Present the complex transformation rules in an intuitive graphical way through the flowchart, which is convenient for users to understand and confirm. Users can adjust and optimize the flowchart according to business requirements to ensure that the transformation rules conform to the actual business logic. For example, in the data cleaning process, the flowchart can clearly show the steps and rules of data cleaning, such as removing duplicate data and correcting incorrect data.

[0101] Decompose complex transformation logic into a series of executable steps and execute them in a predetermined order. For example, convert the date format from "YYYY / MM / DD" to "YYYY-MM-DD", or aggregate sales data to calculate the total sales of each product. Ensure the accuracy and consistency of data transformation through clear rule definitions and an orderly execution process.

[0102] 3.3 Mechanisms and Principles for Solving the Problem of Lack of Comprehensive Monitoring

[0103] According to the configured accuracy detection rules, conduct year-on-year and month-on-month analyses of key data indicators to verify whether the data conforms to business logic. Compare the key fields of upstream and downstream data tables to check the consistency of data during transmission. If data inconsistency issues are found, record the problem information, including the inconsistent fields, data values, and relevant information of the upstream and downstream tables. Through the analysis of key data indicators and the comparison of upstream and downstream data tables, comprehensively monitor the quality and consistency of data. For example, during the ETL process of financial data, compare the data in the general ledger and the detailed ledger to ensure their consistency. If inconsistencies are found, record the problems in a timely manner, analyze them, find the reasons, and take corresponding measures.

[0104] Second, the data processing system in the data warehouse automated ETL process:

[0105] Such as Figures 2 to 3 shown, this system is used to implement the data processing method in the data warehouse automated ETL process described above, and it includes:

[0106] (1) A data source module that provides access and extraction functions for multiple data sources, including ODPS, ADB, MYSQL, HoloLens, etc. Ensure that the system can extract data from multiple data sources to provide a basis for subsequent data processing and analysis.

[0107] (2) A multi-dimensional data quality detection module that conducts multi-dimensional quality inspections on the extracted data, including integrity detection, accuracy detection, timeliness detection, and consistency detection: Ensure that the data entering the subsequent processing process has high quality through detection rules.

[0108] (3) A dynamic rule configuration and alarm module that supports dynamic configuration of data processing rules and alarm rules: According to preset thresholds and detection rules, monitor the system status in real time. Once data quality problems are found, immediately trigger an alarm. By dynamically adjusting the rules, the system can adapt to the continuously changing data source quality, discover problems in a timely manner, and notify relevant personnel to ensure the continuity and accuracy of data processing.

[0109] (4) A report SQL assembly module that automatically generates SQL query statements for extracting relevant data from a data warehouse according to user requirements, supporting the rapid generation and display of reports: reducing the cumbersome process of manually writing SQL statements and improving the efficiency of data processing and report generation.

[0110] (5) A result visualization module that displays the data processing results in intuitive charts and reports: supporting various visualization forms such as bar charts and combination charts. It helps users intuitively understand the data quality status and data processing results, providing strong support for decision-making.

[0111] (6) A data conversion and loading module that supports data format conversion, data cleaning, and data aggregation conversion: ensuring data consistency and accuracy during transmission and processing. By clear conversion rules and an orderly execution process, it improves the accuracy and efficiency of data processing.

[0112] (7) The working process of the system is as follows:

[0113] Data source access: The system extracts data from multiple data sources.

[0114] Data quality detection: Conduct multi-dimensional quality inspections on the extracted data.

[0115] Dynamic rule configuration: Dynamically adjust processing rules and warning rules according to the detection results.

[0116] Data processing and conversion: Clean, convert, and load the data according to the configured rules and conversion logic.

[0117] Result display and warning: Display the processing results in a visual form and trigger warning notifications to relevant personnel.

[0118] Compared with the prior art, the beneficial effects of the present invention are:

[0119] First, improve data quality: Through integrity detection rules, data that does not meet requirements can be effectively filtered out during the data extraction stage, such as data with inconsistent record rows, empty or missing field values. This avoids "garbage data" from entering the subsequent processing process and ensures that the data entering the conversion stage has a high-quality basis. For example, during the extraction of customer information, data with empty keywords such as customer name and contact information will be marked and processed subsequently to ensure that subsequent analysis is based on complete and valid customer data.

[0120] II. Dynamic Rule Adaptation: The dynamic adjustment of the detection rule mechanism enables the solution to flexibly respond to changes in the data source quality. When a certain data quality problem frequently occurs, by adjusting the corresponding detection threshold, the data quality can be more precisely controlled. For example, if it is found that the null value rate of a certain field suddenly increases and exceeds the preset threshold, the strictness of the null value detection for this field can be appropriately reduced, and at the same time, the reasons can be analyzed in depth to avoid misjudgment or missed judgment caused by overly rigid rules.

[0121] III. Comprehensive Quality Monitoring: The consistency detection and accuracy detection rules conduct year-on-year and month-on-month analyses on key data indicators, and compare the key fields of upstream and downstream data tables to comprehensively monitor the data quality and consistency. Timely discover problems in the data transmission and processing processes to ensure the accuracy and reliability of the data. For example, in the financial data ETL process, compare the data in the general ledger and the detailed ledger. Once the inconsistency is found, the problem can be quickly located to ensure the accuracy of the financial data.

[0122] IV. Optimize the Transformation Process: The ETL flow chart generation mechanism presents complex transformation rules in an intuitive graphical way, facilitating users to understand and confirm. Users can adjust and optimize the flow chart according to business requirements to ensure that the transformation rules conform to the actual business logic and reduce errors caused by misunderstandings of the rules. For example, in the data cleaning process, the flow chart clearly shows the steps and rules of data cleaning, such as removing duplicate data and correcting incorrect data. Users can intuitively see the data flow and processing process, which is convenient for adjustment. BRIEF DESCRIPTION OF THE DRAWINGS

[0123] In order to more clearly illustrate the technical solutions in the embodiments of the present application or the prior art, the following will briefly introduce the accompanying drawings required for use in the description of the embodiments or the prior art. Obviously, the accompanying drawings in the following description are only some embodiments of the present application. For those of ordinary skill in the art, without creative efforts, other accompanying drawings can be obtained based on these drawings.

[0124] Figure 1 It is a schematic flow chart of the method of the present invention;

[0125] Figure 2 It is a schematic diagram of the system composition of the present invention;

[0126] Figure 3 It is a schematic execution flow chart of the system of the present invention. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0127] To make the above objects, features, and advantages of the present invention more obvious and understandable, the following will describe in detail the specific embodiments of the present invention with reference to the accompanying drawings. Many specific details are set forth in the following description to facilitate a full understanding of the present invention. However, the present invention can be implemented in many other ways different from those described herein, and those skilled in the art can make similar improvements without departing from the spirit of the present invention. Therefore, the present invention is not limited by the specific embodiments disclosed below;

[0128] It should be noted that the various embodiments in this specification are described in a progressive manner, with each embodiment focusing on the differences from other embodiments. For the same or similar parts among the various embodiments, reference can be made to each other. For the devices disclosed in the embodiments, since they correspond to the methods disclosed in the embodiments, the description is relatively simple, and for the relevant parts, reference can be made to the description in the method section.

[0129] Explanation of related terms:

[0130] (1) Data source table: A database table that stores the original data.

[0131] (2) Target table: A database table that receives the data after ETL processing.

[0132] (3) Field name: The name of a column in a data table, used to identify data attributes.

[0133] (4) Field type: The type of data stored in a field, such as integer, string, etc.

[0134] (5) ETL processing logic: The specific operation rules and processes for data extraction, transformation, and loading.

[0135] (6) Preset flowchart generation algorithm: An algorithm used to generate an ETL flowchart based on metadata.

[0136] (7) Integrity detection rule: A rule for checking whether the data is complete (such as the number of record rows and whether field values are missing).

[0137] (8) Transformation rule: A rule for performing operations such as format conversion, cleaning, and aggregation on the extracted data.

[0138] (9) Key data: Data that has an important impact on the business.

[0139] (10) Business logic: The rules and processes of business operations.

[0140] (11) Preset threshold: A pre-set boundary value used to judge data quality.

[0141] (12) Dynamic adjustment detection rule: A rule for adjusting the detection rule in real time according to data quality problems.

[0142] (13) Data Warehouse: A database system used to store and manage large amounts of data.

[0143] (14) SQL Query Statement: A structured query language statement used to query data from a database.

[0144] Example 1: As Figure 1 shown, this example discloses an application instance of the data processing method in the data warehouse automation ETL process in the field of online car-hailing. In online car-hailing operation and maintenance, it is necessary to extract data from multiple data sources (such as vehicle sensor data, order systems, driver information tables, etc.), and after transformation and cleaning, load it into the data warehouse to provide support for subsequent operation and maintenance analysis, report generation, and decision-making. However, problems such as inconsistent data source quality (such as missing values in sensor data and non-standard order data formats), complex transformation rules (such as the need to unify time formats for different data sources and calculate discounts for order amounts), and lack of comprehensive monitoring affect data quality and operation and maintenance efficiency. To solve this problem, this example adopts the following solution:

[0145] In this example, regarding step S1: ETL flow chart generation:

[0146] Specifically, step S100: Obtain parsed metadata: Obtain information about online car-hailing related data sources (such as vehicle sensor databases, order databases, driver information file systems), data targets (operation and maintenance analysis tables in the data warehouse), transformation rules (such as time format conversion rules, amount calculation rules), and data cleaning rules (such as removing invalid orders and filling in missing vehicle location information) from the metadata parsing module.

[0147] Store this metadata in a temporary storage area, such as a memory database or a temporary file.

[0148] Specifically, step S101: Initialize the flow chart generation environment: Select an algorithm based on graph theory (such as topological sorting algorithm) to generate the flow chart because this algorithm can clearly represent the data flow and transformation relationships.

[0149] Initialize the visualization interface, set the interface layout to a vertical layout, use a blue theme for the color scheme, and set the font size to 12 points.

[0150] Specifically, step S102: Apply the flow chart generation algorithm: According to the metadata, construct the nodes (vehicle sensor data source node, order data source node, time format conversion node, amount calculation node, data warehouse target node) and edges (representing the data flow relationship from the sensor to the conversion node and then to the data warehouse) of the flow chart.

[0151] Use the topological sorting algorithm to optimize and adjust the layout of the flow chart structure to ensure that the flow chart is beautiful and easy to understand.

[0152] Specifically, for step S103: Generate and display a flowchart: Render the generated flowchart structure onto a visualization interface, using rectangles to represent nodes, arrows to represent edges, and text to label the names of nodes and edges.

[0153] Display the rendered flowchart to the user, and the user can perform operations such as zooming in, zooming out, and panning on the flowchart through the interactive functions on the interface.

[0154] It can be understood that by generating a clear ETL flowchart, the operation and maintenance personnel can intuitively understand the data flow and transformation process, which is convenient for discovering potential complex rule problems and making optimizations and adjustments in advance. At the same time, the visual display of the flowchart also helps in communication and collaboration among team members.

[0155] In this embodiment, regarding step S2: Data extraction:

[0156] Specifically, for step S200: Prepare the extraction environment: Use the connection information of the vehicle sensor database and the order database, attempt to establish a connection, and verify whether the connection is successful. According to the data source type, initialize the JDBC tool to connect to the vehicle sensor database and use the pandas library to connect to the order database. Load the integrity detection rules from the configuration file, such as the expected range of the number of vehicle sensor data records per day is 1000 - 1500, and non-null constraints for order data fields (e.g., order number and passenger ID cannot be null).

[0157] Specifically, for step S201: Extract data according to the flowchart definition: According to the ETL flowchart, parse out the vehicle sensor data tables to be extracted (such as vehicle location table, vehicle status table) and order data tables, as well as the corresponding WHERE clauses (such as extracting data for the current day) and extraction fields (such as vehicle location, order amount).

[0158] Specifically, for step S202: Preliminary check of data integrity: Check that the number of extracted vehicle sensor data records is 1200, which meets the expected range; check the order data fields and find that the passenger ID of some orders is null. Record the problem information, including the problem type (field is null), problem record identifier (order ID), and problem field name (passenger ID).

[0159] Specifically, for step S203: Process problem data and continue extraction: Write the problem order data into a temporary table and record it in a log file. Continue to extract subsequent order data, and update the extraction status to 5000 records extracted and 10 problem records.

[0160] Specifically, for step S204: Extraction end processing: Close the connection to the data source and release resources. Summarize the extraction results, generate an extraction report, and notify the user or pass it to the subsequent steps.

[0161] It can be understood that through the preliminary integrity check, data integrity issues can be promptly discovered and recorded, preventing problematic data from entering the subsequent processes and improving data quality. Meanwhile, continuing to extract subsequent data ensures the continuity of the ETL process and prevents the entire extraction process from being interrupted due to individual problematic data.

[0162] In this embodiment, regarding step S3: Data transformation and consistency detection:

[0163] Specifically, for step S300: Prepare the transformation and detection environment: Load data transformation rules, such as converting the string time format of vehicle sensor data to a date type and calculating the order amount according to the discount rule. Load accuracy detection rules, such as the month-on-month and year-on-year analysis of the order amount and comparing the key fields (such as order ID and payment amount) of upstream and downstream data tables (such as the order table and the payment table).

[0164] Specifically, for steps S301 - S303: Data format transformation, cleaning, and aggregation: Transform the date field of vehicle sensor data, remove special characters from the order data, and fill in the missing vehicle location information. Group and sum the order amount by the order date to generate daily order amount summary data.

[0165] Specifically, for steps S304 - S305: Data accuracy and consistency detection: Calculate the day-on-day and week-on-week changes of the order amount and find that the order amount decreased abnormally by 50% on some dates, and record the problem information. Compare the order amounts in the order table and the payment table and find that some order amounts are inconsistent, and record the problem information.

[0166] Specifically, for steps S306 - S307: Problematic data processing and summarization: Isolate the problematic data and generate a problem report, including the problem type (accuracy problem, consistency problem), the number of problems, and the data record information involved.

[0167] Update the ETL process status and mark the data transformation and consistency detection steps as completed.

[0168] It can be understood that through data format transformation, cleaning, and aggregation, the consistency and usability of the data are improved. Accuracy detection and consistency detection can promptly discover data anomalies and ensure data quality. The isolation and summarization of problematic data provide a basis for subsequent data correction.

[0169] In this embodiment, regarding step S4: Dynamic rule adjustment and alarm:

[0170] Specifically, steps S400 - S401: Loading preset thresholds and dynamic adjustment rules: Read the preset thresholds for data quality detection metrics from the configuration file. For example, the threshold for the month-on-month fluctuation of the order amount is 30%. Statistically analyze the problem reports and find that the problem of abnormal month-on-month decline in the order amount frequently occurs. After evaluation, adjust the month-on-month fluctuation threshold to 40%.

[0171] Specifically, steps S402 - S403: Constructing warning messages and sending notifications: Generate warning messages, including problem type (abnormal month-on-month order amount), problem description (specific date and order amount value), and impact assessment (may affect revenue analysis). Notify the data quality management team and the person in charge of the business department via email and DingTalk.

[0172] It can be understood that the dynamic adjustment of detection rules can adapt to changes in data quality, improving the accuracy and flexibility of detection. Real-time warning notifications can promptly remind relevant personnel to handle data quality problems, reducing the impact on the business.

[0173] In this embodiment, regarding step S5: Report SQL assembly and data extraction:

[0174] Specifically, steps S500 - S501: Requirement analysis and data model mapping: Analyze user requirements, extract required data tables (such as order tables, vehicle information tables), fields (order amount, vehicle model), filtering conditions (specific time period), grouping dimensions (vehicle model), and aggregation metrics (total order amount).

[0175] Establish a report data model, map user requirement fields to actual fields in the data warehouse, and for abnormal order amount fields, determine to filter out abnormal values in the SQL query.

[0176] Specifically, steps S502 - S503: SQL statement assembly and test verification: Construct a basic SQL query statement, add the GROUP BY clause and the SUM aggregation function. Execute the SQL query in the test data environment, verify that the query results are consistent with the expectations, and optimize the query performance.

[0177] Specifically, steps S504 - S505: Data extraction and storage: Execute the verified SQL query to extract data from the data warehouse. Store the extracted data in the specified data table to support subsequent result visualization.

[0178] It can be understood that by automatically generating SQL query statements, the efficiency and accuracy of data extraction are improved. Test verification ensures the correctness and performance of the SQL query, providing a reliable data basis for subsequent result visualization.

[0179] It is understandable that in the scenario of online car-hailing operation and maintenance, this data warehouse automated ETL process effectively solves the technical problems of inconsistent data source quality, complex transformation rules, and lack of comprehensive monitoring in the ETL process through steps such as generating flowcharts, data extraction, transformation detection, dynamic rule adjustment, and report SQL assembly. The generation of flowcharts improves the visualization and manageability of the process, data extraction and transformation detection ensure data quality and consistency, dynamic rule adjustment improves the flexibility and accuracy of detection, and report SQL assembly provides data support for result visualization, thus improving the efficiency of online car-hailing operation and maintenance and the scientific nature of decision-making.

[0180] Embodiment 2: On the basis of Embodiment 1, this embodiment further provides the data flow of this solution. Please refer to Figures 2 to 3 :

[0181] The first step: Data source, select tables from different data sources (mysql, ck, holo, odps, adb):

[0182] MySQL: Use the SELECT statement to select a table, for example: SELECT * FROM table_name;.

[0183] ClickHouse: View tables through SHOW TABLES and query data through SELECT.

[0184] Hologres: View table properties through SELECT * FROM hg_table_properties;.

[0185] ODPS (MaxCompute): Use the pyodps library to connect and query, for example: SELECT * FROM your_table_name LIMIT 10;.

[0186] AnalyticDB for MySQL (ADB): When designing the table structure, it is necessary to specify the distribution key and partition key, for example: SELECT * FROM your_table_name LIMIT 10;.

[0187] The second step: Configure metric rules, select meta metrics, derived metrics, derivative metrics, aggregation, year-on-year and month-on-month, rate values, etc.:

[0188] Metric types and definitions:

[0189] Basic metrics, such as total number of orders and number of users.

[0190] Example: SELECT COUNT(*) AS total_orders FROM orders;

[0191] Derived indicator: Add filtering conditions based on the meta-indicator, such as the number of orders in a certain region.

[0192] Example: SELECT COUNT(*) AS region_orders FROM orders WHERE region = 'Shanghai';

[0193] Derivative indicator: Obtained through logical calculations, such as the order conversion rate.

[0194] Example: SELECT SUM(paid_orders) / SUM(total_orders) AS conversion_rate FROM orders;

[0195] Aggregate indicator: Group and summarize data, such as counting sales by date.

[0196] Example: SELECT date, SUM(sales_amount) AS total_sales FROM sales GROUP BY date;

[0197] YoY / QoQ: Compare the current value with the previous period or the same period last year.

[0198] Example: SELECT date, sales_amount, sales_amount_last_year FROM sales;

[0199] Rate value indicator: Calculate the proportion or ratio, such as the sales proportion.

[0200] Example: SELECT category, SUM(sales_amount) / SUM(total_sales) AS category_sales_ratio FROM sales GROUP BY category;

[0201] Step 3: Assemble the SQL. Based on the indicators selected in the previous part, piece together the corresponding SQL:

[0202] Select a suitable execution tool according to the data source (such as MySQL client, ClickHouse client, ODPS client).

[0203] Step 4: Execute the SQL. Output a report of the execution result, and send a DingTalk alarm for any exceptions.

[0204] All of the above embodiments merely represent the implementation manners of the relevant practical applications of the present invention. The descriptions thereof are relatively specific and detailed, but should not be construed as limiting the scope of the invention patent. It should be noted that for those of ordinary skill in the art, without departing from the concept of the present invention, several modifications and improvements can still be made, and these all belong to the protection scope of the present invention. Therefore, the protection scope of the present invention patent shall be subject to the appended claims.

[0205] For those skilled in the art, it can be further realized that the units and algorithm steps of each example described in combination with the embodiments disclosed herein can be implemented by electronic hardware, computer software, or a combination of the two. In order to clearly illustrate the interchangeability of hardware and software, the composition and steps of each example have been generally described according to functions in the above description. Whether these functions are executed in a hardware or software manner depends on the specific application and design constraints of the technical solution. Professional technicians can use different methods to implement the described functions for each specific application, but such implementation should not be considered as exceeding the scope of the present invention.

[0206] At the same time, those skilled in the art can understand that all or part of the processes of implementing the methods of all the above embodiments can be completed by instructing relevant hardware through a computer program. The computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it can include the processes of the embodiments of the above methods. Among them, any reference to the memory, storage, database, or other media provided in the present application and used in the embodiments can include non-volatile and / or volatile memories. Non-volatile memories can include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM), or flash memory. Volatile memories can include random access memory (RAM) or external cache memory. By way of illustration and not limitation, RAM is available in various forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (SSRSDRAM), enhanced SDRAM (ESDRAM), synchronous link (Synchlink) DRAM (SLDRAM), memory bus (Rambus) direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM), etc.

Claims

1. A data processing method in the automated ETL process of a data warehouse, including loading metadata of the current ETL task from a metadata repository, characterized in that, Perform the following steps; S1. According to the parsed metadata, use a preset flowchart generation algorithm to generate a flowchart for the current ETL task; S2. Extract data from the data source table according to the data extraction steps defined in the ETL flowchart; During the extraction process, according to the configured integrity detection rules, conduct a preliminary check on the extracted data; if problems are found, record the problem information and continue to extract the subsequent data for unified processing later; S3. Process the extracted data according to the conversion rules defined in the ETL flowchart, including data format conversion, data cleaning, and data aggregation; According to the configured accuracy detection rules, conduct year-on-year and month-on-month analysis of key data indicators to verify whether the data conforms to business logic; If problems are found, record the problem information; S4. Dynamically adjust the detection rules according to the preset thresholds and the currently detected data quality problems.

2. The data processing method according to claim 1, wherein: In the above S1, it includes: S100. Obtain the parsed metadata, including data source information, data target information, conversion rules, and data cleaning rules; S101. According to system configuration or user preferences, select a flowchart generation algorithm; initialize the visualization interface, and set the interface layout, color scheme, and font size; S102. According to the data source, data target, and conversion rule information in the metadata, construct the nodes of the flowchart; S103. Render the generated flowchart structure onto the visualization interface, using graphic elements to represent the nodes and edges of the flowchart; display the rendered flowchart to the user.

3. The data processing method according to claim 1, wherein: In the above S2, it includes checking whether the number of recorded rows meets the expectation, and whether the field values are empty or missing.

4. The data processing method according to claim 3, characterized in that: In the above S2, it includes: S200. Use the provided data source connection information to attempt to establish a connection with the data source and verify whether the connection is successful; according to the data source type, initialize the corresponding data extraction tool or library; load the configured integrity detection rules from the configuration file or database, including the expected range of the number of recorded rows, field non-null constraints, and field value ranges; S201. According to the ETL flowchart, parse out the data source table, WHERE clause, and extraction fields that need to be extracted currently; S202. Conduct a preliminary check on data integrity; S203. Write the problem data into a temporary table, log file, or send it to an error handling queue; regularly update the extraction status, including the number of extracted records, the number of problem records, and the extraction progress, for monitoring the extraction process; notify the user of the extraction results and problem data or pass them to the subsequent ETL steps.

5. The data processing method according to claim 1, characterized in that: In the above S3, it includes: S300. Load the data conversion rules defined in the ETL flowchart from the configuration file or database S301. According to the conversion rules, identify the fields that need to be converted in data format; use the initialized conversion tool or library to perform format conversion on the identified fields according to the conversion rules; S302. Conduct data cleaning operations according to the cleaning rules; S303. Group the data according to the grouping fields in the aggregation rules; perform aggregation operations on each group, including summation, averaging, and counting; generate a new data set from the results of the aggregation operations, including the grouping fields and the aggregated metric values; S304. Determine the key data metrics that need to be analyzed for year-on-year and month-on-month comparisons according to the accuracy detection rules; calculate the year-on-year and month-on-month values of the key data metrics according to the specified time period; compare the calculated year-on-year and month-on-month metrics with the business logic judgment conditions to verify whether the data conforms to the business logic; if it is found that the data does not conform to the business logic, record the problem information, including the name of the key data metric, the actual value, the expected value, and the time period.

6. The data processing method according to claim 5, characterized in that: In the above S3, it also includes: S305. Select the key fields for consistency detection according to the structures of the upstream and downstream data tables and business requirements; compare the data values of the corresponding records in the upstream and downstream data tables according to the key fields; if it is found that the data is inconsistent, record the problem information, including the name of the inconsistent field, the data values in the upstream and downstream data tables, and the record identifier. S306. Isolate the recorded problem data from the normal data for subsequent unified processing; summarize and count the recorded problem information to generate a problem report, including the problem type, the number of problems, and the data record information involved; notify the user of the problem report or pass it to the subsequent ETL steps. S307. Store the converted data in a temporary storage area or a specified data table for subsequent data loading; archive and save the problem report. Update the status of the ETL process and mark that the data conversion and consistency detection steps are completed.

7. The data processing method according to claim 5, wherein: In the above S4, it includes: S400. Read various data quality detection metrics from the configuration file, including the preset thresholds for integrity, accuracy, timeliness, and consistency; extract the recorded data quality problem records from the database, including the problem type, the occurrence frequency, and the data field information involved. S401. Classify and count the problems in the problem report, and calculate the occurrence frequency of each type of problem; identify the frequently occurring data quality problems according to the preset frequency threshold. S402. Generate the problem type, problem description, and impact assessment according to the problem report; determine the alarm level according to the severity of the problem; indicate whether the detection rule adjustment is involved in the alarm information. S403. Send an alarm notification.

8. The data processing method according to claim 1, characterized in that: It also includes S5. Report SQL assembly and data extraction: Automatically generate SQL query statements for extracting relevant data from the data warehouse according to user requirements and detection results, and extract the required data.

9. A system for implementing the data processing method according to any one of claims 1 to 8, characterized in that, The system includes: A data source module that provides access and extraction functions for multiple data sources; A multi-dimensional data quality detection module that performs multi-dimensional quality inspections on the extracted data, including integrity detection, accuracy detection, timeliness detection, and consistency detection; A dynamic rule configuration and alarm module that supports dynamic configuration of data processing rules and alarm rules; A report SQL assembly module that automatically generates SQL query statements for extracting relevant data from the data warehouse according to user requirements, and supports the rapid generation and display of reports; A result visualization module that displays the data processing results in an intuitive chart and report form; A data conversion and loading module that supports data format conversion, data cleaning, and data aggregation conversion.

10. The system according to claim 9, wherein: The working process of the system is as follows: Data source access: The system extracts data from multiple data sources; Data quality detection: Perform multi-dimensional quality checks on the extracted data; Dynamic rule configuration: Dynamically adjust processing rules and alarm rules according to the detection results; Data processing and transformation: Clean, transform, and load the data according to the configured rules and transformation logic; Result display and alarm: Display the processing results in a visual form and trigger an alarm to notify relevant personnel.

Images