An automatic configuration processing method for a run batch based on an MPP database

Abstract

The present application relates to the field of database processing, and provides an automatic configuration processing method for a batch run based on an MPP database. The method solves the problems of batch run task error stop and batch run monitoring adjustment caused by data problems of an upstream system in a batch run scene of a risk control data mart in the prior art, and causes large and untimely workload of error processing. The main scheme includes the following steps: after transaction division of the batch run, the batch run is logged and written into a function_log log table; a planned error positioning and processing system table is designed and planned, error codes and corresponding error explanations and error processing suggestions are recorded, abnormal queries are performed on the function_log log table according to the error codes in the planned error positioning and processing system table, abnormal information obtained is used for abnormal prompt or error explanation and error processing suggestion; and abnormal data obtained through the abnormal queries and error processing suggestions is written into an abnormal data table.

Description

Technical Field

[0001] This invention relates to the field of database processing and provides an automatic configuration processing method for batch processing based on an MPP database. Background Technology

[0002] MPP is a completely share-nothing architecture. In this architecture, each node (segment) is independent and self-sufficient, with completely independent CPU, memory, and storage resources, and each node has its own independent SQL engine. The advantage of this architecture is its good scalability and ability to support complex structured queries. However, sharinglessness means that data needs to be split within the cluster. Each node is allocated a data shard, and each node performs SQL parsing and execution independently, returning the results to the central control node (master) for aggregation. Therefore, the processing of a transaction requires all nodes performing parallel operations to return results before the transaction can be committed. In an MPP architecture, distributed computing should be evenly distributed across the nodes; this even task partitioning relies on reasonable data distribution.

[0003] In the fields of data warehousing and data marts, the MPP architecture has become a cost-effective parallel structured data processing and storage engine due to its excellent scalability and support for distributed transactions. In the processing logic of the MPP architecture, data analysis and processing requests are distributed to various independent nodes, and the final computing task is determined by the data on each node. Therefore, the data distribution strategy ultimately affects the scalability of the cluster and the performance of the distributed architecture.

[0004] MPP is currently widely used in the big data field, especially in batch processing computing. However, in the maintenance of MPP applications, there are inconveniences in the following use cases:

[0005] Important reports may suddenly malfunction after running stably for a period of time. This could be due to upstream issues, unverified details during development, failure to consider the current business scenario during development, upstream data problems, or untimely notification of adjustments to the upstream data structure. Furthermore, the malfunction may occur during non-normal working hours.

[0006] After the report task function is developed, especially after running stably for a period of time, errors may suddenly occur during subsequent batch runs. There are many reasons for this, and through investigation, several common ones have been found. For example, there may be missing batch run dependency configuration issues leading to table locking, or dirty data generated upstream causing batch run errors. Upstream going online may cause changes to source table permissions or table structure and definition without timely notification to downstream, resulting in batch run errors. Accidental table deletion can also cause batch run errors. However, each error may require time to query, and if the error is not detected or handled in a timely manner, it will affect subsequent batch runs, resulting in an overall slower batch run. Summary of the Invention

[0007] The purpose of this invention is to solve the problem of excessive workload and untimely error handling caused by data problems in the upstream system of the risk control data mart batch processing scenario.

[0008] To achieve the above objectives, the present invention adopts the following technical solution:

[0009] An automatic configuration processing method for batch processing based on an MPP database includes the following steps:

[0010] Step 1: Metadata management, which manages table structure definitions, table field and function definitions, and upstream and downstream table and field mapping relationships. Function definitions refer to the statements that create data.

[0011] Step 2: After dividing the batch processing into transactions, log the data and write it to the function_log table. The batch processing includes multiple transactions, and each transaction is a database operation statement including INSERT, UPDATE, and DELETE commands. Logging refers to the prompt information generated by the operation commands during the batch processing, such as the time taken, execution result, and exception information output, which is then written to the function_log table.

[0012] Step 3: Design and plan the error location and handling system table, which records the error codes, corresponding error descriptions, and error handling suggestions:

[0013] Step 4: Perform an exception query on the function_log log table based on the error code in the error location and handling system table described in Step 3. Provide exception prompts or error descriptions and error handling suggestions for the obtained exception information:

[0014] Step 5: Write the queried abnormal data into the abnormal data table based on the abnormal query and error handling suggestions given in Step 4.

[0015] In the above technical solution, step 1 includes the following steps:

[0016] Step 1.1 Create a table definition metadata table. The metadata table contains a table name field and a table creation statement field corresponding to the table name, which is used to reconstruct the data table with the corresponding table name through the table creation statement.

[0017] Step 1.2 Create a table field structure metadata table. The table field structure metadata table includes a table name field, field names under the corresponding table name, field type definitions, and a table creation statement field corresponding to the table name, which is used for querying and rebuilding field data issues.

[0018] Step 1.3 Create upstream and downstream tables and field mapping tables. The upstream and downstream tables and field mapping tables include source tables, source fields, target tables, target fields, and logical fields. The source tables and target tables, and source fields and target fields are mapped through the processing logic rules stored in the logical fields, which are used to query the relationships between fields and analyze anomalies.

[0019] In the above technical solution, step 2 specifically includes the following steps:

[0020] Step 2.1 Divide the batch processing transactions to obtain the divided transactions;

[0021] A batch execution statement is divided into independent insert, update, and delete statements based on insert, update, and delete operations, resulting in a divided transaction. An incrementing variable v_step is defined to represent the step number of the function transaction, and another variable v_execution_content is defined to record the specific processing logic rules in the insert, update, and delete statements.

[0022] Step 2.2 Mark the divided transactions with step numbers, and write the main mapping relationships between the source table and the target table, the source field and the target field in the transaction, the processing logic and the corresponding batch running metadata of the transaction into the function_log log table. The batch running metadata includes the batch running function, batch running start time, batch running end time, batch running steps, batch running logic, batch running results and batch running error information.

[0023] In the above technical solution, step 3 specifically includes the following steps:

[0024] Step 3.1 Compile and list the error information in a template format. Error information includes denominator of 0 and field length exceeding the limit.

[0025] Step 3.2 Analyze the data problems of the corresponding abnormal information and write the most abnormal information as prompt information;

[0026] Step 3.3 Obtain the most likely cause of the problem and write the abnormal data query process and processing process into the abnormal query processing information through the abnormal information.

[0027] In the above technical solution, step 4 specifically includes the following steps:

[0028] Step 4.1 The Java application reads the data from the function_log table for the current day;

[0029] Step 4.2 If an anomaly occurs, locate the anomaly and perform data matching in the planning error location and processing system table according to the error code of the anomaly's error message to obtain the anomaly prompt or error description and error handling suggestions.

[0030] Because the present invention adopts the above-described technical solution, it has the following beneficial effects:

[0031] I. This invention is an optimization mechanism for metadata management and exception handling during the development and application of MPP functions. It focuses on important batch processing tasks and jobs in maintenance through statistics and probability, and solves the inconvenience (such as batch processing exceptions at irregular times) and inefficiency (such as high-probability exceptions) in the maintenance process by statistically analyzing the types and probabilities of exceptions and the reasons for their occurrence. It effectively reduces maintenance costs and belongs to the field of MPP database batch data processing technology.

[0032] Second, through statistics and observation, some frequently occurring and highly probable errors were discovered. This is where the invention of this device comes in: monitoring these error messages, automatically querying data anomalies, providing detailed anomaly information and suggestions, and even automating function backups, data backups, and function adjustments. This makes it convenient for monitoring and development personnel to understand anomalies, refer to the device's suggestions, and promptly handle or even automatically resolve errors, while also compiling statistics on common anomalies and data problems. Detailed Implementation

[0033] The embodiments of the present invention will be described in detail below. Although the present invention will be described and illustrated in conjunction with some specific embodiments, it should be noted that the present invention is not limited to these embodiments. On the contrary, any modifications or equivalent substitutions made to the present invention should be covered within the scope of the claims of the present invention.

[0034] Furthermore, to better illustrate the present invention, numerous specific details are set forth in the following detailed embodiments. Those skilled in the art will understand that the present invention can be practiced without these specific details.

[0035] An anomaly handling method and system for batch processing of data marts based on MPP databases includes the following steps:

[0036] Step 1: Create a metadata management table to manage the table structure definition, table fields, and function definitions. Update the table promptly after adjustments are made. Include mapping relationships between tables and between fields within functions.

[0037] A function definition refers to a statement that creates data, such as creating a function named FunOne.

[0038] Create functionFunOne(type param)returninteger as

[0039] Begin

[0040] Function implementation body

[0041] End

[0042] Step 1.1 Create the table definition metadata table table_definition: table name, table definition statement (DDL), updated when the table is rebuilt or its structure is changed, and used to handle subsequent table rebuilds or structure changes after an exception occurs.

[0043] The table definition statement is the statement used to create a table, such as creating a table named TableOne.

[0044] Create table TableOne (

[0046] Col1 character varying(30),

[0047] Col2 integer,

[0048] Col3 numeric )

[0050] Step 1.2 Create the table field structure metadata table table_columns_definition: table name, field name, field type, length, used for querying and rebuilding field data issues;

[0051] For example, if the field is too long, it might be because the length of the mapped field in the upstream source table has increased from the maximum length of 50 to 100. In that case, the corresponding field in the downstream target table also needs to be increased to 100.

[0052] First, back up the table data: `create table tableone_date_bak as ...`

[0053] Select * from tableone;

[0054] Then perform the table deletion operation: `droptable tableone`;

[0055] Rebuild table createtabletableone(

[0056] Col2 character varying(100) – Original definition: Col2 character varying(50)

[0057] Step 1.3 Create upstream and downstream tables and field mapping table sour_targ_mapping_columns: source table, source field, target table, target field, transformation logic, to facilitate querying the lineage relationship of fields and anomaly analysis;

[0058] Step 2: Log the function transaction and record it in the function_log table;

[0059] Function transactions refer to database operation statements, including the INSERT, UPDATE, and DELETE commands. Logging refers to the information generated during batch processing, such as execution time, results, and exception messages, which are then written to the `function_log` table.

[0060] The transaction value in the function is the table data modification operation, which is the DML (Data Manipulation Language) operation commonly referred to in relational databases. It is based on the three commands INSERT, UPDATE, and DELETE and is an important step in data processing.

[0061] Logging essentially involves writing the information generated during batch processing, such as execution time, results, and exceptions, into a dedicated table.

[0062] Taking this transaction as an example,

[0063] 'insert into tableone(newcol1, newcol2)

[0064] select

[0065] count(col1) / count(col2)newcol1,

[0066] max(col3) + max(col4)

[0067] From tablezero'

[0068] The field newol1 in the target table tableone is obtained by dividing the count of field col1 (count(col1)) by the count of field col2 (count(col2)) in the upstream source table tablezero. Therefore, the mapping relationship is as follows:

[0069] Source table Source table fields Target table Target table fields logic Tablezero Col1 Tableone Newcol1 count(col1) / count(col2) Tablezero Col2 Tableone Newcol2 count(col1) / count(col2)

[0070] This involves writing the step variable v_step and the specific logical variable v_execution_content from section 2.1 into a table called function_log. Because its purpose is to record batch execution logs, it's called a log table.

[0071] Step 2.3 Design the log table and write the batch processing metadata into the log table (batch processing function, batch processing start time, batch processing end time, batch processing steps, batch processing logic, batch processing results, batch processing error information).

[0072] The batch processing function is a function name, which is a user-defined variable. For example, in the function Funone in 1.1, a function name variable v_fun_name := 'Funone' is defined. The start time is obtained through a built-in database function definition and assigned to a variable v_start_time before batch processing. The end time is obtained through a built-in database function definition and assigned to a variable v_end_time after batch processing. The batch processing steps and logic are v_step and v_execution_content in 2.1. The batch processing result is either 'success' or 'success' if there are no errors. If an exception occurs, it will be obtained through the built-in exception handling statement and written to the result field.

[0073] When developing stored procedures or functions, record the function name, the system time when batch processing starts, and the system time when batch processing ends. Write the batch processing script for each transaction to the log table. Each transaction's batch processing logic corresponds to a transaction number, along with the batch processing result. If an exception is caught, write it to the log table for later exception analysis. Based on the function's batch processing error information, locate a specific segment, and then locate the corresponding table and fields.

[0074] Step 3: Design and plan the error location and handling system table, which records the error codes, corresponding error descriptions, and error handling suggestions;

[0075] Create an error message and handling table named error_handling. The error_handling table includes an error message field for recording error information, an error explanation field for explaining the error information, an error message field for providing prompts, and an error handling field for providing handling suggestions.

[0076] Step 3.1 Create an error message and handling table named error_handling. This table records the common exception content and format of several types of functions in MPP batch processing, the root cause of the corresponding error problems, and the solutions. For identifiable exceptions, code is generated and the error is automatically handled. For other errors, the specific data problem can be located and queried, providing developers with a reference for troubleshooting and fixing exceptions.

[0077]

[0078]

[0079] Step 3.2 Analyze the data problems of the corresponding abnormal information, and write the most common causes of the problems as prompt information. As shown in the figure above, check the SQL as an exception handling suggestion and exception investigation.

[0080] Step 3.3 Obtain the most likely cause of the problem and write the abnormal data query process and processing process into the abnormal query processing information through the abnormal information.

[0081] Step 4 Error Response and Handling System:

[0082] Step 4.1 Build a batch log analysis system using Spring Boot backend and Vue frontend, and run it daily on a set schedule:

[0083] Spring Boot microservices are essentially a collection of numerous open-source libraries, providing their own Tomcat and Jetty containers. No WAR deployment is required; simply specify the dependencies in the pom.xml file, reducing the complexity of system development. The Spring Boot framework offers robust support for the MVC design model, automating project configuration and making MVC development in the Spring Boot environment exceptionally simple. Spring Boot provides RESTful methods for retrieving process runtime parameters for monitoring. This system primarily uses GET, POST, and DELETE methods for retrieving, creating, and deleting resources, respectively. Since the system requires CRUD operations on the MySQL database, MyBatis is used to write SQL statements, and JDBC is used to establish connections to the database.

[0084] The Vue front-end framework is a progressive MVVM framework that uses Node.js technology, the vue-cli scaffolding tool, vue-router for routing, and Ajax wrapped by the Axios plugin for data interaction, achieving data persistence and ensuring a good user experience.

[0085] Step 4.2 Connect to the MPP database via JDBC, configure the exceptions in Step 3 in the front-end management interface, and read the batch run exception records in the log table in Step 2 for subsequent exception query and processing steps, as well as the processing actions after exception query and analysis, such as how to back up which objects and data in exception data case a, specify the backup process, and finally how to handle the exception.

[0086] Step 5 involves writing the queried abnormal data into the abnormal data table based on the abnormal query and processing suggestions given in Step 4. This table will serve as the basis for subsequent manual processing to repair abnormalities and restore data, and will also provide a reference for subsequent abnormal statistics and data quality.

[0087] The above are merely representative embodiments among the many specific applications of this invention, and do not constitute any limitation on the scope of protection of this invention. All technical solutions formed by transformation or equivalent substitution fall within the scope of protection of this invention.

Claims

1. An automatic configuration processing method for batch processing based on an MPP database, characterized in that, Includes the following steps: Step 1: Metadata management, which manages table structure definitions, table field and function definitions, and upstream and downstream table and field mapping relationships. Function definitions refer to statements that create data. Step 2: After dividing the batch processing into transactions, log the data and write it to the function_log table. The batch processing includes multiple transactions, and each transaction is a database operation statement including three types of commands: INSERT, UPDATE, and DELETE. Logging refers to the prompt information generated by the operation commands during the batch processing, including the time consumed, execution result, and exception information output, which is then written to the function_log table. Step 2 includes the following steps: Step 2.1 Divide the batch processing transactions to obtain the divided transactions; A batch execution statement is divided into independent insert, update, and delete statements based on insert, update, and delete operations, resulting in a divided transaction. An incrementing variable v_step is defined to represent the step number of the function transaction, and another variable v_execution_content is defined to record the specific processing logic rules in the insert, update, and delete statements. Step 2.2 Mark the divided transactions with step numbers, and write the main mapping relationships between the source table and the target table, the source field and the target field in the transaction, the processing logic and the corresponding batch running metadata of the transaction into the function_log log table. The batch running metadata includes the batch running function, batch running start time, batch running end time, batch running steps, batch running logic, batch running result and batch running error information. Step 3: Design and plan the error location and handling system table, which records the error codes, corresponding error descriptions, and error handling suggestions: Step 4: Perform an exception query on the function_log log table based on the error code in the planning error location and processing system table described in Step 3, and provide exception prompts or error descriptions and error handling suggestions for the obtained exception information; Step 5: Write the queried abnormal data into the abnormal data table based on the abnormal query and error handling suggestions given in Step 4.

2. The automatic configuration processing method for batch processing based on an MPP database according to claim 1, characterized in that, Step 1 includes the following steps: Step 1.1 Create a table definition metadata table. The metadata table contains a table name field and a table creation statement field corresponding to the table name, which is used to reconstruct the data table with the corresponding table name through the table creation statement. Step 1.2 Create a table field structure metadata table. This table includes a table name field, field names under the corresponding table name, field type definitions, and a table creation statement field corresponding to the table name. This table is used for querying and reconstructing field data issues. Step 1.3 Create upstream and downstream tables and field mapping tables. The upstream and downstream tables and field mapping tables include source tables, source fields, target tables, target fields, and logical fields. The source tables and target tables, and source fields and target fields are mapped through the processing logic rules stored in the logical fields, which are used to query the relationship between fields and analyze anomalies.

3. The automatic configuration processing method for batch processing based on an MPP database according to claim 1, characterized in that, Step 3 specifically includes the following steps: Step 3.1 Compile and list the error information in a template format. Error messages include those with a denominator of 0 and those with excessively long fields. Step 3.2 Analyze the data problems in the corresponding abnormal information and write the most common causes of the problems as prompt information; Step 3.3 Obtain the most likely cause of the problem and write the abnormal data query process and processing process into the abnormal query processing information through the abnormal information.

4. The automatic configuration processing method for batch processing based on an MPP database according to claim 1, characterized in that, Step 4 specifically includes the following steps: Step 4.1 The Java application reads the data from the function_log table for the current day; Step 4.2 If an anomaly occurs, locate the anomaly and perform data matching in the planning error location and processing system table according to the error code of the anomaly's error message to obtain the anomaly prompt or error description and error handling suggestions.

Images