Repair method, system and equipment for abnormal data and storage medium

Abstract

The invention provides a repair method, system and device for abnormal data and a storage medium, and relates to the technical field of computer application. The abnormal data repairing method comprises the steps that first vector data is generated according to a metadata information table, and second vector data is generated according to a historical abnormal repairing table; adopting a language model to generate a repair SQL statement according to the first vector data and the second vector data; verifying whether the repair SQL statement meets repair expectation or not, and training the language model according to a verification result; analyzing the log information to obtain target information, and inputting the target information into the language model to generate an SQL script for data restoration; when the approval process is passed, abnormal data is repaired according to the SQL script for data repair. By the adoption of the method for repairing the abnormal data, the SQL script for data repairing can be automatically generated for repairing the abnormal data, so that the data repairing efficiency is improved, and manual errors are avoided.

Description

Technical Field

[0001] The present invention relates to the field of computer application technology, and more particularly to a method, system, device and storage medium for repairing abnormal data. Background Art

[0002] A significant challenge in production operations is the frequent occurrence of system anomalies. These issues typically require in-depth manual investigation, meticulous troubleshooting, and data repair methods to resolve. The traditional manual data troubleshooting and repair process involves manual investigation, manually writing SQL (Structured Query Language) scripts, manually uploading the repair details and initiating an approval process. After approval, manual coordination with relevant personnel to execute the repair scripts, and finally, reviewing whether the repaired data is working properly and meeting expectations. However, this traditional process is not only time-consuming and labor-intensive, but also highly dependent on the repairer's professional skills and in-depth understanding of the system and business. Furthermore, manual operations often carry the risk of error, further complicating problem resolution. Furthermore, with the explosive growth in data volumes and increasing complexity of data structures, and the interdependencies between data in various business databases in systems such as credit systems, the difficulty of manual troubleshooting and repairing data increases exponentially.

[0003] As can be seen, traditional manual data troubleshooting and repair methods not only suffer from high labor costs and low efficiency, but also have many hidden dangers of human error. Therefore, there is an urgent need for a more efficient and accurate data repair method to reduce manual intervention and improve data processing efficiency. Summary of the Invention

[0004] In order to solve the problems or at least part of the problems existing in the above-mentioned prior art, the embodiments of the present invention provide a method, system, storage medium, device and computer program product for repairing abnormal data, which utilizes multi-business library metadata learning technology and historical problem training mechanism to obtain a trained model, and then automatically generates a data repair SQL script based on log information and the trained model to repair the abnormal data, thereby improving data repair efficiency and avoiding human errors.

[0005] According to a first aspect of the present invention, an embodiment of the present invention provides a method for repairing abnormal data, which includes: obtaining metadata information and saving the metadata information to a metadata information table; obtaining repair information in a repair table and storing the repair information to a historical abnormality repair table; obtaining an approved first SQL statement sample and a non-compliant second SQL statement sample, and storing the first SQL statement sample and the second SQL statement sample in a first data set and a second data set, respectively; generating first vector data according to the metadata information table, and generating second vector data according to the historical abnormality repair table; using a language model to generate a repair SQL statement based on the first vector data and the second vector data; verifying whether the repair SQL statement meets repair expectations based on the first SQL statement sample and the second SQL statement sample, and training the language model based on the verification result; obtaining log information according to a system information table, and parsing the log information to obtain target information; inputting the target information into the language model and generating a data repair SQL script; calculating a data repair score according to an indicator proportion table and the target information; obtaining a corresponding approval process according to the data repair score; when the approval process is passed, repairing the abnormal data according to the data repair SQL script.

[0006] According to the above-mentioned embodiment of the present invention, a trained model is obtained by utilizing multi-business library metadata learning technology and historical problem training mechanism, and then a data repair SQL script is automatically generated based on log information and the trained model to repair abnormal data. This can improve data repair efficiency and avoid human errors, thereby repairing abnormal data in a timely manner, ensuring the accuracy and integrity of the data, and improving the reliability of the database.

[0007] In some embodiments of the present invention, the system information table includes: the English key identifier of the service, the Chinese name of the system, the channel log parsing method, the channel business information, the service function, the associated database, and the system abnormality attention level; the indicator proportion table includes indicator items and indicator coefficients, and the indicator items include: the system abnormality attention level, the account repair number category, the affected data volume, and the number of log statistics.

[0008] In some embodiments of the present invention, the method for repairing abnormal data further includes: generating a repair report based on the repair process.

[0009] According to the above-mentioned embodiment of the present invention, by generating a repair report, relevant management personnel can observe and understand the online data situation at any time.

[0010] In some embodiments of the present invention, the repair report is generated according to a preset period, and the repair report includes data comparison information before and after the repair process and abnormal information during the repair process.

[0011] According to the second aspect of the present invention, an embodiment of the present invention provides a repair system for abnormal data, which includes: an initial data acquisition module for acquiring metadata information and saving the metadata information to a metadata information table, acquiring the repair information in the repair table and storing the repair information to a historical abnormality repair table, acquiring an approved first SQL statement sample and an uncompliant second SQL statement sample, and storing the first SQL statement sample and the second SQL statement sample in a first data set and a second data set respectively; a language model training module for performing the following operations: generating first vector data according to the metadata information table, generating second vector data according to the historical abnormality repair table; using a language model to train the system according to the first vector data; The first vector data and the second vector data generate a repair SQL statement; based on the first SQL statement sample and the second SQL statement sample, verify whether the repair SQL statement meets the repair expectations, and train the language model based on the verification result; a data repair module is used to perform the following operations: obtain log information according to the system information table, and parse the log information to obtain target information; input the target information into the language model and generate a SQL script for data repair; calculate the data repair score according to the indicator proportion table and the target information; obtain the corresponding approval process according to the data repair score; when the approval process is passed, perform repair processing on the abnormal data according to the data repair SQL script.

[0012] According to the above-mentioned embodiment of the present invention, a trained model is obtained by utilizing multi-business library metadata learning technology and historical problem training mechanism, and then a data repair SQL script is automatically generated based on log information and the trained model to repair abnormal data. This can improve data repair efficiency and avoid human errors, thereby repairing abnormal data in a timely manner, ensuring the accuracy and integrity of the data, and improving the reliability of the database.

[0013] In some embodiments of the present invention, the system information table includes: the English key identifier of the service, the Chinese name of the system, the channel log parsing method, the channel business information, the service function, the associated database, and the system abnormality attention level; the indicator proportion table includes indicator items and indicator coefficients, and the indicator items include: the system abnormality attention level, the account repair number category, the affected data volume, and the number of log statistics.

[0014] In some embodiments of the present invention, the repair system for abnormal data further includes a report generation module, which is used to generate a repair report according to the repair process.

[0015] According to the above-mentioned embodiment of the present invention, by generating a repair report, relevant management personnel can observe and understand the online data situation at any time.

[0016] In some embodiments of the present invention, the report generation module generates the repair report according to a preset period, and the repair report includes data comparison information before and after the repair process and abnormal information during the repair process.

[0017] According to the third aspect of the present invention, an embodiment of the present invention provides a computer-readable storage medium having computer-readable instructions stored thereon. When the computer-readable instructions are executed by a processor, the computer performs the following operations: the operations include the steps included in the method for repairing abnormal data as described in any of the above embodiments.

[0018] According to a fourth aspect of the present invention, an embodiment of the present invention provides a computer device comprising a memory and a processor, wherein the memory is used to store one or more computer-readable instructions, wherein the one or more computer-readable instructions, when executed by the processor, can implement the method for repairing abnormal data as described in any of the above embodiments.

[0019] According to a fifth aspect of the present invention, an embodiment of the present invention provides a computer program product including a computer program, which, when executed by a processor, implements the method for repairing abnormal data as described in any one of the above embodiments.

[0020] From the above, it can be seen that the method, system, storage medium, device and computer program product for repairing abnormal data provided by the embodiments of the present invention obtain a trained model by utilizing multi-business library metadata learning technology and historical problem training mechanism, and then automatically generate a data repair SQL script based on log information and the trained model to repair abnormal data. This can improve data repair efficiency and avoid human errors, thereby repairing abnormal data in a timely manner, ensuring the accuracy and integrity of the data, and improving the reliability of the database. BRIEF DESCRIPTION OF THE DRAWINGS

[0021] Figure 1 1 is a flow chart of a method for repairing abnormal data according to embodiment 1 of the present invention;

[0022] Figure 2 is a flowchart of a method for repairing abnormal data according to embodiment 2 of the present invention;

[0023] Figure 3 2 is a schematic diagram of the architecture of a repair system for abnormal data according to embodiment 3 of the present invention. DETAILED DESCRIPTION

[0024] The various aspects of the present invention are described in detail below in conjunction with the accompanying drawings and specific embodiments. Among them, well-known modules, units and their connections, links, communications or operations are not shown or described in detail. In addition, the described features, architectures or functions can be combined in any manner in one or more embodiments. It should be understood by those skilled in the art that the various embodiments described below are only for illustration and are not intended to limit the scope of protection of the present invention. It can also be easily understood that the modules or units or processing methods in the various embodiments described herein and shown in the drawings can be combined and designed in various different configurations.

[0025] The following is a brief explanation of the terms used below.

[0026] SQL: Structured Query Language, a programming language specifically used to manage and operate relational databases.

[0027] MySQL: A widely used open source relational database management system.

[0028] NLP: Natural Language Processing.

[0029] JDBC (Java Database Connectivity) is a standard API (Application Programming Interface) for the Java language to interact with various relational databases. It provides Java developers with a unified way to connect to different types of databases, execute SQL statements, and perform database transactions.

[0030] GPT: Generative Pre-trained Transformer, a series of pre-trained language models based on the Transformer architecture, which understands the laws of human language through large-scale unsupervised learning and generates natural and fluent text.

[0031] Transformer architecture: A deep learning architecture for processing sequence data.

[0032] [Example 1]

[0033] Figure 1 4 is a flow chart of a method for repairing abnormal data according to embodiment 1 of the present invention.

[0034] like Figure 1As shown, in embodiment 1 of the present invention, the method for repairing abnormal data may include at least steps S101, S102, S103, S104, S105, S106, S107, S108, S109, S110, and S111. These steps are described in detail below.

[0035] In step S101, metadata information is obtained and saved to a metadata information table. In an optional embodiment, heterogeneous database parsing technology combined with SQL scripts is used to extract relevant metadata information from the database. This metadata information includes, but is not limited to, one or more of the following: database name information, table name information, field type information, field meaning information, and constraint information.

[0036] In some implementations, metadata information is extracted periodically or on demand to a target table (database metadata information table) based on a preset cycle, and the version history of the database structure and business meaning information is recorded to facilitate backtracking or recovery when needed. This helps ensure data traceability and accuracy.

[0037] In a further embodiment, when the database structure changes, the database changes are captured and the corresponding metadata extraction tasks are notified and triggered. For example, using the tool Canal to monitor the Binlog of a MySQL database or perform other monitoring tasks, when the database structure changes, the aforementioned tool captures the changes and notifies and triggers the corresponding XXL-JOB tasks, implementing a parsing and update mechanism, reducing manual intervention and improving efficiency.

[0038] In step S102, the repair information in the repair table is obtained and stored in the historical anomaly repair table. The repair information is historical case information of data anomaly repairs in various business databases. For example, the repair information includes, but is not limited to, image data, credit data, and IOU data. The image data may include image type, image distribution channel, and distribution status. The credit data may include credit limit, used credit limit, and credit limit type. The IOU data may include loan amount, repayment amount, default repayment date, grace date, repayment type, and compliance information.

[0039] In some implementations, historical case information for data anomaly repairs in various business databases is extracted from the repair table of the production environment's online repair system. This historical case information is then formatted and stored in a historical anomaly repair table. The repair table records historical case information based on log characteristics, repair SQL, impact scope, occurrence time, and completion time.

[0040] Among them, log features are log-related features when data anomalies occur, such as error codes and exception types. These features help to quickly locate problems; repair SQL is the SQL statement executed to repair data anomalies. Recording these statements can facilitate subsequent analysis of repair methods and verification of repair effects; impact scope refers to the business scope and data volume affected by the data anomaly. Understanding the impact scope helps to assess the severity of the anomaly; occurrence time is the time when the data anomaly begins to appear, which facilitates analysis of the pattern and frequency of anomaly occurrence; completion time is the time when the data anomaly repair is completed. By comparing the occurrence time and completion time, the efficiency of the repair can be evaluated.

[0041] In a further embodiment, the repair number table is directly accessed via a database connection. By establishing a connection to the database where the repair number system resides, the repair number table is directly accessed. Thus, SQL queries can be executed within the program to extract the desired information from the repair number table.

[0042] In step S103, an approved first SQL statement sample and an unapproved second SQL statement sample are obtained, and the first SQL statement sample and the second SQL statement sample are stored in a first data set and a second data set respectively.

[0043] In this embodiment, commonly used SQL query statements are extracted from the enterprise's internal database. These statements are typically optimized and audited to meet the enterprise's compliance requirements. Approved first SQL statement samples are obtained through collaboration with database administrators. Second, non-compliant second SQL statement samples are obtained through the following methods: extracting failed SQL statements from the database's error log. These failed SQL statements fail due to syntax errors, performance issues, or compliance violations; generating SQL statement samples containing malicious code by simulating SQL injection attacks; and collaborating with team members to collect non-compliant SQL statement samples discovered during code reviews.

[0044] Furthermore, the collected first SQL statement samples and second SQL statement samples are sorted, duplicate items and invalid items are removed, and the compliant and non-compliant SQL statements are stored in different data sets respectively.

[0045] In this embodiment, step S103 also includes organizing and annotating the SQL dataset to facilitate subsequent model compliance SQL verification. In an optional embodiment, compliant SQL statements are annotated to explain their purpose and compliance requirements; non-compliant SQL statements are annotated to indicate the specific compliance rules they violate (such as syntax errors, performance issues, security vulnerabilities, etc.) and the potential consequences.

[0046] In step S104, first vector data is generated based on the metadata information table, and second vector data is generated based on the historical anomaly repair table. In this embodiment, relevant features are selected according to business needs, and the extracted features are vectorized, thereby converting the extracted features into numerical vectors (i.e., vector data).

[0047] In some embodiments, feature vectorization based on the metadata information table includes: obtaining basic features, refined features, and new features based on business needs, and converting the extracted features into numerical vectors. Basic features include, but are not limited to, database name, table name, field name, and field meaning; refined features include, but are not limited to, data type, field length, and constraints; and new features include, but are not limited to, table name and field name combinations, keywords for field meanings, data type complexity, similarity between field names and field meanings, and the distribution of field types in a table.

[0048] Among them, data types are used to further subdivide data types (such as integers, floating-point numbers, strings, dates and times, etc.) and their precision (such as the number of digits after the decimal point); for string types, their field length information can be further refined, for example, classified according to length ranges; constraints are used to record whether there are constraints, as well as the specific types of constraints (such as non-empty, unique, foreign keys, etc.) and their combinations; field table names and field name combinations are used to determine the semantic relationship between table names and field names, such as calculating their similarity or closeness through word vectors; keyword extraction of field meanings includes using algorithms such as TF-IDF, TextRank or BERT to extract keywords in field meanings, and determine the frequency and importance of keywords; the complexity of data types is a quantitative indicator of data type complexity, such as a comprehensive score based on factors such as the number of types and type diversity; the similarity between field names and field meanings is the text similarity between field names and field meanings calculated using the cosine similarity method; the distribution of field types in the table is the proportion of different types of fields in the statistical table, such as the proportion of string fields, the proportion of numerical fields, etc.

[0049] Furthermore, the extracted features are converted into numerical vectors using the scene's metadata features and constant quantization methods. For example, word embedding technology is used to convert table and field names into numerical vectors; one-hot encoding is used to convert field types into numerical vectors; field lengths are directly used as numerical features; constraints are mapped to binary values (presence or absence); and natural language processing (NLP) techniques are used to extract semantic features of field meanings.

[0050] In some embodiments, feature vectorization based on the historical anomaly repair table includes: obtaining basic features and other features based on business needs, and converting the extracted features into numerical vectors. The basic features include, but are not limited to, the scope of impact, occurrence time, and completion time; other features include, but are not limited to, anomaly type features, database metadata features, repair solution features, and historical data features. In this embodiment, the specific method for obtaining these other features can be exemplified as follows:

[0051] (1) Acquisition of exception type features: Convert the exception type (such as data inconsistency, data type error, etc.) into a numerical feature, for example, by assigning a unique identifier or code to each exception type.

[0052] (2) Acquisition of database metadata features: Extract relevant database metadata (such as table structure, field type, index, etc.) and convert them into numerical features. This feature can reflect the structure and characteristics of the database and help the model understand the context in which the exception occurred.

[0053] (3) Acquisition of repair solution features: By performing text analysis on the repair solution, key information (such as the SQL statements used, operation steps, etc.) is extracted and converted into numerical features. For example, natural language processing techniques (such as word segmentation and word embedding) are used to convert the text into a vector representation.

[0054] (4) Acquisition of historical data features: Use the timestamps, repair success rates and other information in historical anomaly repair cases to construct time series features or statistical features.

[0055] In step S105, a language model is used to generate a repair SQL statement based on the first vector data and the second vector data. In this embodiment, the Transformer architecture of the GPT language model is used for database metadata feature vector processing, historical anomaly repair feature vector processing, and repair SQL statement generation, and the repair SQL statement is generated based on the GPT language model.

[0056] In step S106, the repair SQL statement is verified based on the first and second SQL statement samples to see whether it meets the repair expectations, and the language model is trained based on the verification results, thereby ensuring that the generated repair SQL statement is compliant and meets the repair expectations.

[0057] In some embodiments, training the language model includes, for example, the following steps:

[0058] (1) Utilizing the encoder-decoder collaborative mechanism, the encoder is responsible for processing feature vectors, and the decoder is responsible for generating repair SQL statements. By optimizing the information transmission mechanism between the encoder and decoder, the overall performance and generation quality of the model are improved.

[0059] (2) Hyperparameter optimization and model tuning. Use efficient methods such as grid search or random search to adjust hyperparameters such as learning rate, batch size, and number of training rounds. Monitor model performance on the validation set to ensure the model reaches its optimal state during training. The training process optimizes the loss function and adjusts model parameters to accurately predict differences in the data. In the code, the loss function for model training is automatically optimized using an algorithm, using methods such as gradient descent to minimize the loss function.

[0060] (3) Model validation and SQL compliance check. Specifically, it includes: a. Evaluating model performance through K-Fold Cross Validation, ensuring the generalization ability of the model and avoiding overfitting through multiple training and verification; b. Calculating accuracy and recall to accurately evaluate the performance of the model in detecting data differences; c. SQL compliance verification, after the model generates the repair SQL statement, immediately perform a compliance check to ensure that the generated SQL statement meets the compliance requirements of the database. Specifically, the generated SQL statement is compared with the compliant SQL set to verify its compliance; at the same time, it is compared with the non-compliant SQL set to ensure that known malicious or inefficient SQL is not generated. Finally, based on the compliance check results, the model is adjusted and optimized as necessary to improve the compliance and repair effect of the SQL statement.

[0061] Among them, accuracy and recall are important indicators for evaluating classification models. A high accuracy indicates that the model can accurately identify differences, and a high recall indicates that the model can capture most differences. Accuracy and recall are calculated using the following formulas (1) and (2) respectively:

[0062]

[0063] Among them, TP, TN, FP and FN are the number of true positive examples, true negative examples, false positive examples and false negative examples respectively.

[0064] In step S107, log information is obtained from the system information table, and the target information is parsed from the log information. In some embodiments, the system information table includes but is not limited to: service English key identifier, system Chinese name, channel log parsing method, channel business information, service function, associated database, and system abnormality attention level.

[0065] In step S108, the target information is input into the language model and an SQL script for data repair is generated.

[0066] In step S109, the data repair score is calculated based on the indicator ratio table and the target information. In some embodiments, the indicator ratio table includes indicator items and indicator coefficients, and the indicator items include, for example: the system abnormality attention level, account repair number category, affected data volume, and log statistics times.

[0067] In step S110, the corresponding approval process is obtained according to the data repair score.

[0068] In step S111, when the approval process is passed, abnormal data is repaired according to the SQL script for data repair.

[0069] By adopting the above-mentioned method for repairing abnormal data of Example 1 of the present invention, a trained model is obtained by utilizing multi-business library metadata learning technology and historical problem training mechanism, and then a data repair SQL script is automatically generated based on log information and the trained model to repair the abnormal data. This can improve data repair efficiency and avoid human errors, thereby repairing abnormal data in a timely manner, ensuring the accuracy and integrity of the data, and improving the reliability of the database.

[0070] In further embodiments, the method for repairing abnormal data further includes generating a repair report based on the repair process. In some embodiments, the repair report is generated at a preset interval and includes data comparison information before and after the repair process, as well as information about abnormalities during the repair process. Generating repair reports allows relevant management personnel to monitor and understand the status of online data at any time.

[0071] [Example 2]

[0072] Figure 2 4 is a flow chart of a method for repairing abnormal data according to embodiment 2 of the present invention.

[0073] like Figure 2 As shown, in embodiment 2 of the present invention, the method for repairing abnormal data may include the following steps:

[0074] Step 1: Information Collection. This step includes the following steps 1.1 to 1.3. The following are detailed descriptions of steps 1.1 to 1.3:

[0075] Step 1.1: Metadata information collection. Step 1.1 uses heterogeneous database parsing technology combined with SQL scripts to extract database-related information, such as collecting metadata information for the credit system. This specifically includes the following steps:

[0076] (1) Establish database connection and access. For example, by flexibly configuring the data source of each business library, using JDBC database connection technology, and through the authentication and authorization mechanisms of different database systems, dynamic data source access can be achieved.

[0077] (2) Metadata acquisition, which describes the basic information of database objects (such as tables, columns, indexes, etc.) and the meaning of the data. For example, when the main business database uses a MySQL database, after connecting to the MySQL server, the Information_schema database is selected for dynamic query, and the collected metadata is uniformly stored and managed for subsequent data governance, data integration, and business analysis. The collected information includes but is not limited to database name information, table name information, field type information, field meaning information, and constraint information; the Information_schema database contains multiple tables, each of which provides different types of metadata.

[0078] In some embodiments, commonly used metadata tables and the information they contain include, for example: TABLES, which contains information about tables in the database; COLUMNS, which contains information about columns (fields) in the table; SCHEMATA, which contains information about the database itself; STATISTICS, which contains information about table indexes.

[0079] In an exemplary embodiment, the following SQL query is used to extract information such as the database name, table name, field type, and field meaning of a database:

[0080]

[0081]

[0082] (3) By automating and integrating a dynamic perception mechanism, the metadata database query function is embedded into the scheduled task in advance. In some implementations, the metadata information is extracted to the target table (database metadata information table) regularly or on demand using the XXL-JOB task scheduling method, and version management and backtracking are performed (recording the version history of the database structure and business meaning information so that backtracking or recovery can be performed when necessary), which helps to ensure the traceability and accuracy of the data. The content information of the database metadata information table includes but is not limited to: database name, Chinese meaning of the database, table name, field name, field meaning, field type, field length, constraint conditions, etc.

[0083] In a further implementation, the tool Canal is used to monitor the MySQL database's Binlog or perform other monitoring tasks. When the database structure changes, the tool captures the change and notifies and triggers the corresponding XXL-JOB task, implementing a parsing and updating mechanism that reduces manual intervention and improves efficiency. This dynamic perception mechanism allows for real-time dynamic database parsing and updates of collected information as the business environment and database table structure change, avoiding the difficulty of relying solely on scheduled tasks to dynamically parse heterogeneous databases and update collected business information.

[0084] Step 1.2: Collect repair information. Extract historical case information for data anomaly repairs in each business database from the repair table of the production environment's online repair system. This historical case information is then organized into a specific format and stored in the historical anomaly repair table. The repair table records historical case information based on log characteristics, repair SQL, impact scope, occurrence time, and completion time. For example, the repair information collected is for the credit system.

[0085] In a further embodiment, the repair number table is directly accessed via a database connection. By establishing a connection to the database where the repair number system resides, the repair number table is directly accessed. Thus, SQL queries can be executed within the program to extract the desired information from the repair number table.

[0086] Step 1.3: Collect samples of compliant and non-compliant SQL statements.

[0087] In this embodiment, on the one hand, commonly used SQL query statements are extracted from the enterprise's internal database. These statements are usually optimized and audited to meet the enterprise's compliance requirements. By collaborating with the database administrator, approved (compliant) first SQL statement samples are obtained. On the other hand, non-compliant second SQL statement samples are obtained through the following methods: extracting failed SQL statements from the database error log. These failed SQL statements failed due to syntax errors, performance issues, or violations of compliance rules; generating SQL statement samples containing malicious code by simulating SQL injection attacks; and collaborating with team members to collect non-compliant SQL statement samples discovered during the code review process.

[0088] Step 2: Model training. This step includes the following steps 2.1 to 2.4. The following are detailed descriptions of steps 2.1 to 2.4:

[0089] Step 2.1: Feature vectorization based on the database metadata table. In this embodiment, relevant features are selected according to business requirements, and vectorization processing is performed on the extracted features, thereby converting the extracted features into numerical vectors (ie, vector data).

[0090] In some embodiments, feature vectorization based on a database metadata information table includes: obtaining basic features, refined features, and new features based on business requirements, and converting the extracted features into numerical vectors. Basic features include, but are not limited to, database name, table name, field name, and field meaning; refined features include, but are not limited to, data type, field length, and constraints; and new features include, but are not limited to, table name and field name combinations, keywords for field meanings, data type complexity, similarity between field names and field meanings, and the distribution of field types within a table.

[0091] Among them, data types are used to further subdivide data types (such as integers, floating-point numbers, strings, dates and times, etc.) and their precision (such as the number of digits after the decimal point); for string types, their field length information can be further refined, for example, classified according to length ranges; constraints are used to record whether there are constraints, as well as the specific types of constraints (such as non-empty, unique, foreign keys, etc.) and their combinations; field table names and field name combinations are used to determine the semantic relationship between table names and field names, such as calculating their similarity or closeness through word vectors; keyword extraction of field meanings includes using algorithms such as TF-IDF, TextRank or BERT to extract keywords in field meanings, and determine the frequency and importance of keywords; the complexity of data types is a quantitative indicator of data type complexity, such as a comprehensive score based on factors such as the number of types and type diversity; the similarity between field names and field meanings is the text similarity between field names and field meanings calculated using the cosine similarity method; the distribution of field types in the table is the proportion of different types of fields in the statistical table, such as the proportion of string fields, the proportion of numerical fields, etc.

[0092] Furthermore, the extracted features are converted into numerical vectors using the scene's metadata features and constant quantization methods. For example, word embedding technology is used to convert table and field names into numerical vectors; one-hot encoding is used to convert field types into numerical vectors; field lengths are directly used as numerical features; constraints are mapped to binary values (presence or absence); and natural language processing (NLP) techniques are used to extract semantic features of field meanings.

[0093] Step 2.2: Feature vectorization based on the historical anomaly repair table. In this embodiment, relevant features are selected according to business requirements, and the extracted features are vectorized, thereby converting the extracted features into numerical vectors (ie, vector data).

[0094] In some embodiments, feature vectorization based on the historical anomaly repair table includes: obtaining basic features and other features based on business needs, and converting the extracted features into numerical vectors. The basic features include but are not limited to: impact range, occurrence time, and completion time; other features include but are not limited to: anomaly type features, database metadata features, repair solution features, and historical data features. In this embodiment, the specific method for obtaining these other features is as follows:

[0095] (1) Acquisition of exception type features: Convert the exception type (such as data inconsistency, data type error, etc.) into a numerical feature, for example, by assigning a unique identifier or code to each exception type.

[0096] (2) Acquisition of database metadata features: Extract relevant database metadata (such as table structure, field type, index, etc.) and convert them into numerical features. This feature can reflect the structure and characteristics of the database and help the model understand the context in which the exception occurred.

[0097] (3) Acquisition of repair solution features: By performing text analysis on the repair solution, key information (such as the SQL statements used, operation steps, etc.) is extracted and converted into numerical features. For example, natural language processing techniques (such as word segmentation and word embedding) are used to convert the text into a vector representation.

[0098] (4) Acquisition of historical data features: Use the timestamps, repair success rates and other information in historical anomaly repair cases to construct time series features or statistical features.

[0099] Step 2.3: Organize and label the SQL dataset.

[0100] First, the collected first SQL statement samples and second SQL statement samples are sorted, duplicate items and invalid items are removed, and the compliant and non-compliant SQL statements are stored in different data sets respectively.

[0101] Secondly, organize and annotate the SQL dataset to facilitate subsequent model compliance SQL verification. In an optional implementation, compliant SQL statements are annotated to explain their purpose and compliance requirements; non-compliant SQL statements are annotated to indicate the specific compliance rules they violate (such as syntax errors, performance issues, security vulnerabilities, etc.) and the potential consequences.

[0102] Step 2.4: Training based on Transformer.

[0103] In this embodiment, first, the Transformer architecture of the GPT language model is used for database metadata feature vector processing, historical anomaly repair feature vector processing, and repair SQL statement generation, and a repair SQL statement is generated based on the GPT language model. Secondly, the repair SQL statement is verified based on the first SQL statement sample and the second SQL statement sample to see whether it meets the repair expectations, and the language model is trained based on the verification results. Thus, the Transformer architecture model is trained to fully understand the credit database model, and can be mapped to the actual tables and columns in the database based on the table name and column name. At the same time, the trained model can be used to ensure that the generated repair SQL statement is compliant and meets the repair expectations.

[0104] In some embodiments, training the language model includes, for example, the following steps:

[0105] (1) Utilizing the encoder-decoder collaborative mechanism, the encoder is responsible for processing feature vectors, and the decoder is responsible for generating repair SQL statements. By optimizing the information transmission mechanism between the encoder and decoder, the overall performance and generation quality of the model are improved.

[0106] (2) Hyperparameter optimization and model tuning. Use efficient methods such as grid search or random search to adjust hyperparameters such as learning rate, batch size, and number of training rounds. Monitor model performance on the validation set to ensure the model reaches its optimal state during training. The training process optimizes the loss function and adjusts model parameters to accurately predict differences in the data. In the code, the loss function for model training is automatically optimized using an algorithm, using methods such as gradient descent to minimize the loss function.

[0107] (3) Model validation and SQL compliance check. Specifically, it includes: a. Evaluating model performance through K-Fold Cross Validation, ensuring the generalization ability of the model and avoiding overfitting through multiple training and verification; b. Calculating accuracy and recall to accurately evaluate the performance of the model in detecting data differences; c. SQL compliance verification, after the model generates the repair SQL statement, immediately perform a compliance check to ensure that the generated SQL statement meets the compliance requirements of the database. Specifically, the generated SQL statement is compared with the compliant SQL set to verify its compliance; at the same time, it is compared with the non-compliant SQL set to ensure that known malicious or inefficient SQL is not generated. Finally, based on the compliance check results, the model is adjusted and optimized as necessary to improve the compliance and repair effect of the SQL statement.

[0108] Among them, accuracy and recall are important indicators for evaluating classification models. A high accuracy indicates that the model can accurately identify differences, and a high recall indicates that the model can capture most differences.

[0109] Step 3, exception capture and analysis, includes the following steps 3.1 and 3.2. The following are detailed descriptions of steps 3.1 and 3.2:

[0110] Step 3.1: The system utilizes expert experience to maintain a system status description table (system information table). In some embodiments, the system information table includes, but is not limited to: service English key identifier, system Chinese name, channel log parsing method, channel business information, service function, associated database, and system abnormality attention level. For example, the system information table is shown in Table 1 below:

[0111] Table 1

[0112]

[0113] Among them, the system abnormality attention level ranges from 1 to 10, and the higher the level from 1 to 10, the more attention it needs.

[0114] Step 3.2: Obtain log information and parse key information (target information) from it. In this embodiment, log information is obtained according to the system information table, and target information is parsed from the log information. Furthermore, the target information is input into the language model and a SQL script for data repair is generated. The Transformer architecture model has been trained to fully understand the credit database model. It can map the table name and column name to the actual table and column in the database, and use the trained model to output SQL statements that meet the specifications.

[0115] The present invention provides a specific example of obtaining log information, parsing key information therefrom, and generating a SQL script for data repair. In this example, the first step is to obtain the following log information from the system information table:

[0116] Service name: k8s-log-hcfc-ctrip-batch

[0117]

Monitoring content

Ctrip batch service

[0118]

Statistics

[0119] Statistical period: 2025-02-16 11:00:00 to 2025-02-16 11:01:02

[0120] [Partial content]: [2025-02-16 11:00:02.623]-[ERROR]-[Thread-9573]–[com.hcfc.batch.ctrip,.core.convert.CreatePaymentflowCommandConvert]-tid:7dc0a0b1645f46c795087f60765d599c-Fund details could not be found based on the payment serial number. IOU number: 20250201073219205776093833MRLN. Please verify the payment details ["20250215151406441109899477" "CFTJR" "38679.55" "0"]

[0121] Step 2: Based on the above log information, determine the service name: k8s-log-hcfc-ctrip-batch; statistics: 57; log level: ERROR. Step 3: Fuzzy match the service name k8s-log-hcfc-ctrip-batch to hit hcfc-ctrip-batch in the [System Description Table], thereby determining the database ctrip_batch and log parsing method ctripFuc. Step 4: Use the ctripFuc code to format and parse the following exception content:

[0122] [2025-02-16

[0123] 11:00:02.623]-[ERROR]-[Thread-9573]-[com.hcfc.batch.ctrip.core.convert.Create PaymentFlowCommandConvert]-tid:7dc0a0b1645f46c795087f60765d599c-Fund details could not be found based on the payment serial number. IOU number: 20250201073219205776093833MRLN. Please verify the payment details ["20250215151406441109899477","CFTJR","38679.55","0"]

[0124] In this example, regular expressions are used to extract information from the logs, and code is written to format and parse them, resulting in the following content:

[0125]

[0126] In the fifth step, the relevant indicators (target information) obtained from the log are input into the trained language model and the model output information including the repair SQL script (SQL script for data repair) is obtained.

[0127] In this embodiment, the relevant indicators (target information) include, but are not limited to: database name (for example: ctrip_batch, determined according to the system situation description table), system abnormality attention level (for example: 7, determined according to the system situation description table), error type (error_type), loan number (loan_number), log level (log_level), occurrence time (2025-02-16 11:00:02.623), statistical number (57), and other indicators: repayment details information (payment_details).

[0128] Furthermore, the model output information obtained by inputting the relevant indicators (target information) into the model includes but is not limited to: whether data repair is performed (0-data cannot be repaired, 1 data repair is required), the repair script (SQL script for data repair), the amount of data affected (the number of data involved in repair), and whether account repair is involved (0-not involved, 1 involved).

[0129] Step 4, problem repair, includes the following steps 4.1 to 4.3. The following is a detailed description of steps 4.1 to 4.3:

[0130] Step 4.1: Obtain the basic tables for repair functions. Based on expert experience and the actual number of repairs, the design content can be dynamically maintained in two basic tables, including the indicator ratio table and the approval process table.

[0131] In an exemplary embodiment, the indicator proportion table is shown in Table 2 below, and the approval process table is shown in Table 3 below:

[0132] Table 2

[0133] Serial number Indicator Index coefficient 1 System abnormality attention level 5 2 Account revision 5 3 Impact on data volume 2 4 Log statistics times 1

[0134] Table 3

[0135]

[0136]

[0137] Step 4.2: Calculate the repair score.

[0138] In an exemplary embodiment, the data for Ctrip's batch repairs include: system abnormality attention level = 7, affected data volume = 2, log statistics times = 57, account repair number = 0 (not involved), then the repair score = system abnormality attention level calculation score + account repair number calculation score + affected data volume calculation score + log statistics times calculation score = 7×5+0×5+2×2+1×57=96, then the approval process corresponding to the approval process number 003 in Table 3 is hit.

[0139] Step 4.3: Initiate approval and revise the data.

[0140] In an exemplary embodiment, by calling the approval process corresponding to the approval system API, for example, for the situation described in step 4.2, the approval process corresponding to the approval process number 003 is initiated, and the relevant number adjustment approval process is executed. After the approval is passed, the system executes the SQL script output by the model to perform number adjustment processing.

[0141] After the model outputs the SQL script and other information for executing data repair through step 3, execute steps 4.1 to 4.3 above to automatically connect to the approval process and initiate the data repair approval process. The approval flow executes different approval processes based on the repair level. Approving personnel can review the uploaded approval materials and ultimately send the approval results back to the repair system, which automatically performs repairs and verification.

[0142] Step 5: Generate and send the report.

[0143] In this embodiment, XXL-JOB tasks are used to periodically trigger report generation and delivery. Reports are generated for daily verification and repair data and are available in various formats, such as PDF and Excel. The reports include, for example, comparisons of data before and after repairs, and any anomalies found during the repair process. Finally, the reports are sent to designated email addresses and groups, allowing relevant managers to monitor online data at any time.

[0144] The above-mentioned method for repairing abnormal data of Example 2 of the present invention is adopted, utilizing multi-business library metadata learning technology, historical problem training mechanism, dynamic perception and anomaly analysis, automatic generation and compliance checking of SQL repair plans, dynamic integration of repair processes to initiate approval processes, automatic repair and other technologies, to achieve accurate positioning and rapid repair of data anomaly scenarios, thereby significantly improving the accuracy and efficiency of data processing. More specifically, the present invention can achieve the following beneficial effects: (1) By combining NLP technology to implement natural language parsing, user input can be parsed to extract key information such as table names, column names, operation types, conditions, etc., and intentions (query, delete, operation, insert) can be identified through operation keywords. Therefore, the system only needs to describe the data repair needs in natural language and automatically convert them into SQL statements and execute them, without the need to manually write SQL scripts, thereby improving the degree of automation; (2) SQL statements are automatically generated by the machine, avoiding errors that may occur when manually writing SQL scripts; (3) Automated processing can significantly improve data repair efficiency and reduce the time required for repair; (4) By reducing manual intervention, the operating costs of database management and maintenance can be reduced; (5) By promptly repairing abnormal data, the accuracy and integrity of the data can be ensured, and the reliability of the database can be improved; (6) Automated data repair is seamlessly connected with the approval process to form a complete data processing and approval closed loop, which not only ensures the compliance of data repair but also improves operational efficiency.

[0145] [Example 3]

[0146] Figure 3 2 is a schematic diagram of the architecture of a repair system for abnormal data according to embodiment 3 of the present invention.

[0147] like Figure 3 As shown, the abnormal data repair system includes: an initial data acquisition module 310, a language model training module 320, a data repair module 330, and a report generation module 340.

[0148] Among them, the initial data acquisition module 310 is used to obtain metadata information and save the metadata information to the metadata information table, obtain the repair number information in the repair number table and store the repair number information to the historical abnormality repair table, obtain the approved first SQL statement sample and the non-compliant second SQL statement sample, and store the first SQL statement sample and the second SQL statement sample in the first data set and the second data set respectively.

[0149] The language model training module 320 is used to perform the following operations: generate first vector data based on the metadata information table, and generate second vector data based on the historical anomaly repair table; use the language model to generate a repair SQL statement based on the first vector data and the second vector data; verify whether the repair SQL statement meets the repair expectations based on the first SQL statement sample and the second SQL statement sample, and train the language model based on the verification result.

[0150] The data repair module 330 is used to perform the following operations: obtain log information according to the system information table, and parse the log information to obtain target information; input the target information into the language model and generate a SQL script for data repair; calculate the data repair score according to the indicator proportion table and the target information; obtain the corresponding approval process according to the data repair score; when the approval process is passed, repair the abnormal data according to the data repair SQL script.

[0151] In some embodiments, the system information table includes: service English key identifier, system Chinese name, channel log parsing method, channel business information, service function, associated database, system abnormality attention level; the indicator proportion table includes indicator items and indicator coefficients, and the indicator items include: the system abnormality attention level, account repair number category, affected data volume, and log statistics times.

[0152] Report generation module 340 is configured to generate a repair report based on the repair process. In some embodiments, the repair report is generated at a preset interval and includes data comparison information before and after the repair process, as well as information about any anomalies during the repair process. By generating repair reports, relevant management personnel can monitor and understand online data at any time.

[0153] The above-mentioned repair system for abnormal data of Example 3 of the present invention is adopted, and a trained model is obtained by utilizing multi-business library metadata learning technology and historical problem training mechanism. Then, a data repair SQL script is automatically generated based on log information and the trained model to perform repair processing on the abnormal data. This can improve data repair efficiency and avoid human errors, thereby repairing abnormal data in a timely manner, ensuring the accuracy and integrity of the data, and improving the reliability of the database.

[0154] Through the description of the above embodiments, those skilled in the art can clearly understand that the present invention can be implemented by combining software with a hardware platform. Based on this understanding, all or part of the contribution of the technical solution of the present invention to the background art can be embodied in the form of a software product. This computer software product can be stored in a storage medium such as ROM / RAM, a magnetic disk, an optical disk, etc., and includes a number of instructions for causing a computer device (which can be a personal computer, a server, or a network device, etc.) to execute the methods described in various embodiments of the present invention or certain parts of the embodiments.

[0155] Correspondingly, embodiments of the present invention further provide a computer-readable storage medium having computer-readable instructions or a program stored thereon. When executed by a processor, the computer-readable instructions or program causes the computer to perform the following operations: the operations include the steps included in the method for repairing abnormal data as described in any of the above embodiments, which are not further described here. The storage medium may include, for example, an optical disk, a hard disk, a floppy disk, a flash memory, a magnetic tape, and the like.

[0156] In addition, embodiments of the present invention further provide a computer device comprising a memory and a processor, wherein the memory is configured to store one or more computer-readable instructions or programs, wherein the one or more computer-readable instructions or programs, when executed by the processor, can implement the method for repairing abnormal data as described in any of the above embodiments. The computer device may be, for example, a server, a desktop computer, a laptop computer, a tablet computer, or the like.

[0157] Embodiments of the present invention also provide a computer program product comprising a computer program, the computer program including program code for executing the abnormal data repair method shown in the flowchart. When the computer program product is executed in a computer system, the program code is used to cause the computer system to implement the abnormal data repair method provided in an embodiment or implementation of the present disclosure.

[0158] According to an embodiment of the present disclosure, the program code for executing the computer program provided by the embodiment of the present disclosure can be written in any combination of one or more programming languages. Specifically, these computer programs can be implemented using high-level procedural and / or object-oriented programming languages, and / or assembly / machine languages. Programming languages include, but are not limited to, languages such as Java, C++, python, "C" or similar programming languages. The program code can be executed entirely on the user computing device, partially on the user device, partially on a remote computing device, or entirely on a remote computing device or server. In cases involving a remote computing device, the remote computing device can be connected to the user computing device through any type of network, including a local area network (LAN) or a wide area network (WAN), or can be connected to an external computing device (for example, using an Internet service provider to connect via the Internet).

[0159] Finally, it should be noted that the above embodiments are intended only to illustrate the technical solutions of the present invention and are not intended to limit the present invention. Although the present invention has been described in detail with reference to the above embodiments, those skilled in the art should understand that the technical solutions described in the above embodiments may be modified or some of the technical features thereof may be replaced with equivalents. Such modifications or replacements do not deviate from the spirit and scope of the technical solutions of the various embodiments of the present invention. Therefore, the scope of protection of the present invention shall be determined by the claims.

Claims

1. A method for repairing abnormal data, characterized in that: The repair method includes: Obtaining metadata information and saving the metadata information to a metadata information table; Obtaining the repair number information in the repair number table and storing the repair number information in the historical abnormality repair table; Obtaining an approved first SQL statement sample and an unapproved second SQL statement sample, and storing the first SQL statement sample and the second SQL statement sample in a first data set and a second data set, respectively; Generate first vector data according to the metadata information table, and generate second vector data according to the historical anomaly repair table; Generate a repair SQL statement based on the first vector data and the second vector data using a language model; Verifying whether the repair SQL statement meets the repair expectation based on the first SQL statement sample and the second SQL statement sample, and training the language model based on the verification result; Obtaining log information according to the system information table, and parsing the log information to obtain target information; Inputting the target information into the language model and generating an SQL script for data repair; Calculate the data repair score based on the indicator proportion table and the target information; Obtaining a corresponding approval process according to the data repair score; When the approval process is passed, abnormal data is repaired according to the SQL script for data repair.

2. The repair method according to claim 1, wherein: The system information table includes: service English key identifier, system Chinese name, channel log parsing method, channel business information, service function, associated database, and system abnormality attention level; The indicator proportion table includes indicator items and indicator coefficients, and the indicator items include: the system abnormality attention level, account repair number category, affected data volume, and log statistics times.

3. The repair method according to claim 1, wherein: The repair method further includes: generating a repair report according to the repair process.

4. The repair method according to claim 3, wherein: The repair report is generated according to a preset period, and the repair report includes data comparison information before and after the repair process and abnormal information during the repair process.

5. A repair system for abnormal data, characterized in that: The repair system comprises: an initial data acquisition module configured to acquire metadata information and save the metadata information to a metadata information table, acquire repair number information from a repair number table and store the repair number information in a historical anomaly repair table, acquire an approved first SQL statement sample and a non-compliant second SQL statement sample, and store the first SQL statement sample and the second SQL statement sample in a first data set and a second data set, respectively; The language model training module performs the following operations: Generate first vector data according to the metadata information table, and generate second vector data according to the historical anomaly repair table; Generate a repair SQL statement based on the first vector data and the second vector data using a language model; Verifying whether the repair SQL statement meets the repair expectation based on the first SQL statement sample and the second SQL statement sample, and training the language model based on the verification result; The data repair module is used to perform the following operations: Obtaining log information according to the system information table, and parsing the log information to obtain target information; Inputting the target information into the language model and generating an SQL script for data repair; Calculate the data repair score based on the indicator proportion table and the target information; Obtaining a corresponding approval process according to the data repair score; When the approval process is passed, abnormal data is repaired according to the SQL script for data repair.

6. The repair system according to claim 5, wherein: The system information table includes: service English key identifier, system Chinese name, channel log parsing method, channel business information, service function, associated database, and system abnormality attention level; The indicator proportion table includes indicator items and indicator coefficients, and the indicator items include: the system abnormality attention level, account repair number category, affected data volume, and log statistics times.

7. The repair system according to claim 5, wherein: The repair system further includes a report generating module for generating a repair report according to the repair process.

8. A computer-readable storage medium storing computer-readable instructions, characterized in that: The computer-readable instructions are executed by a processor to implement the repair method according to any one of claims 1 to 4.

9. A computer device comprising a memory and a processor, The memory stores computer-readable instructions, characterized in that: The processor executes the computer-readable instructions to implement the repair method according to any one of claims 1 to 4.

10. A computer program product comprising a computer program, characterized in that When the computer program is executed by a processor, the repair method according to any one of claims 1 to 4 is implemented.

Images