Batch data exception processing method, device, computer equipment and storage medium

Abstract

The embodiment of the present application belongs to the field of data processing and relates to a method for handling batch data exceptions, comprising obtaining a source data table to be written, identifying first exception data of the source data table by executing an exception identification script; logically transforming the source data table according to the first exception data to obtain a transformed source data table; writing the data in the transformed source data table into the target data table to be written in the database, and identifying the transformed source data table during the writing process to obtain second exception data, and writing the second exception data into the exception data table at the same time. The present application also provides a batch data exception processing device, a computer device and a storage medium. In addition, the present application also relates to blockchain technology, and the original source data table can be stored in the blockchain. The present application can quickly and easily capture and process exceptions automatically to avoid error interruptions in batch tasks that affect subsequent business.

Description

Technical Field

[0001] The present application relates to the field of data processing technology, and in particular to a method, apparatus, computer equipment, and storage medium for batch data exception processing. Background Art

[0002] As businesses continue to grow, they accumulate more and more operational data. Establishing data warehouses for analytical reporting and decision support is becoming a growing trend. Building a complete data warehouse can provide business process guidance while also improving and controlling time, cost, and quality. ETL, short for Extract-Transform-Load, is a crucial component of data warehouses. It describes the process of extracting, transforming, and loading data from a source to a destination.

[0003] In actual ETL (Extensive Transformation) (ETL) operations, issues with source data quality and standards often lead to production batch anomalies. Many industries face similar challenges, and the typical approach is to monitor phone alerts based on potential issues or scenarios, then notify on-duty personnel to address them. These anomalies require urgent manual intervention, resulting in a lag in efficiency and effectiveness throughout the entire process, making it difficult to meet the stringent requirements for timeliness and stability in batch data processing. Summary of the Invention

[0004] The purpose of the embodiments of the present application is to propose a batch data exception processing method, device, computer equipment and storage medium to solve the technical problems in the related art that exception processing is delayed and inefficient, resulting in low timeliness and stability of data batch processing.

[0005] In order to solve the above technical problems, the present invention provides a method for handling batch data exceptions, which adopts the following technical solutions:

[0006] Obtain a source data table to be written, and identify first abnormal data in the source data table by executing an abnormality identification script;

[0007] logically transforming the source data table according to the first abnormal data to obtain a transformed source data table;

[0008] The data in the transformed source data table is written into a target data table to be written in the database, and the transformed source data table is identified during the writing process to obtain second abnormal data, and the second abnormal data is written into the abnormal data table.

[0009] Furthermore, the step of identifying the first abnormal data in the source data table by executing the abnormality identification script includes:

[0010] Read the field data in the source data table;

[0011] The abnormality identification script is executed to call a preset abnormality identification function, and abnormality identification is performed on the field data to obtain first abnormality data, wherein the preset abnormality identification function is determined according to a preset abnormality type.

[0012] Furthermore, the step of performing logical transformation on the source data table according to the first abnormal data includes:

[0013] Determine the abnormal field corresponding to the first abnormal data in the source data table;

[0014] Modify the abnormal field according to the abnormal type of the abnormal field.

[0015] Furthermore, the step of modifying the abnormal field according to the abnormal type of the abnormal field includes:

[0016] If the exception type is a primary key conflict, the primary key constraint of the exception field will be deleted;

[0017] If the exception type is that the data length of the first exception data is greater than and does not conform to a preset field format, modifying the data length according to the preset field format;

[0018] If the exception type is that the first exception data is a null value for a field, the non-null restriction of the exception field is deleted.

[0019] Furthermore, the step of writing the data in the transformed source data table into a target data table to be written in the database, identifying the transformed source data table during the writing process to obtain second abnormal data, and writing the second abnormal data into the abnormal data table includes:

[0020] Reading preset storage structure information of a target data table, wherein the preset storage structure information includes standard field attributes of the target data table;

[0021] Reading the field data in the transformed source data table through a data cleaning and conversion tool, and determining whether the field attributes of the field data conform to the corresponding standard field attributes;

[0022] If it does, the field data is written into the target data table; if it does not, the field data that is inconsistent with the standard field format of the standard field attribute is determined as second abnormal data, and the second abnormal data is written into the abnormal data table.

[0023] Furthermore, the step of writing the field data into the target data table includes:

[0024] Extracting standard field attributes having a mapping relationship with the field data from the target data table according to the field data;

[0025] A write execution statement is generated by combining the standard field attributes and the field data, and the field data is written into the target data table through the write execution statement.

[0026] Furthermore, the step of writing the field data into the target data table by using the write execution statement includes:

[0027] Encapsulate the write execution statement into a message and deliver it to the message queue;

[0028] Start an import thread, read a message from the message queue, and parse the message to obtain the write execution statement and the target data table;

[0029] The write execution statement is executed by the import thread to write the field data into the target data table.

[0030] In order to solve the above technical problems, the embodiment of the present application further provides a batch data exception processing device, which adopts the following technical solution:

[0031] an identification module, configured to obtain a source data table to be written, and identify first abnormal data in the source data table by executing an abnormality identification script;

[0032] a transformation module, configured to perform logical transformation on the source data table according to the first abnormal data to obtain a transformed source data table;

[0033] The writing module is used to write the data in the transformed source data table into the target data table to be written in the database, and identify the transformed source data table during the writing process to obtain second abnormal data, and write the second abnormal data into the abnormal data table.

[0034] In order to solve the above technical problems, the embodiment of the present application further provides a computer device, which adopts the following technical solution:

[0035] The computer device includes a memory and a processor. The memory stores computer-readable instructions. When the processor executes the computer-readable instructions, the steps of the batch data exception processing method described above are implemented.

[0036] In order to solve the above technical problems, the embodiment of the present application further provides a computer-readable storage medium, which adopts the following technical solution:

[0037] The computer-readable storage medium stores computer-readable instructions, which, when executed by a processor, implement the steps of the batch data exception processing method described above.

[0038] Compared with the prior art, the embodiments of the present application have the following beneficial effects:

[0039] The present application obtains a source data table to be written, identifies first abnormal data in the source data table by executing an exception identification script; logically transforms the source data table according to the first abnormal data to obtain a transformed source data table; writes the data in the transformed source data table into the target data table to be written in the database, and identifies the transformed source data table during the writing process to obtain second abnormal data, and writes the second abnormal data into the abnormal data table at the same time; the present application logically transforms the identified abnormal data, removes restrictions, and then converts and processes the abnormalities when writing data, and writes the abnormal data into the abnormal data table, so that the abnormalities can be captured and processed automatically quickly and easily, avoiding error interruptions in batch running tasks that affect subsequent business, while reducing the number of manual interventions, and achieving a qualitative improvement in the timeliness and stability of the overall task. BRIEF DESCRIPTION OF THE DRAWINGS

[0040] In order to more clearly illustrate the solutions in this application, a brief introduction will be given below to the drawings required for use in the description of the embodiments of this application. Obviously, the drawings described below are some embodiments of this application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without any creative work.

[0041] Figure 1 is an exemplary system architecture diagram to which the present application may be applied;

[0042] Figure 2 is a flow chart of an embodiment of a method for handling batch data anomalies according to the present application;

[0043] Figure 3 1 is a schematic structural diagram of an embodiment of a batch data exception processing device according to the present application;

[0044] Figure 4 It is a structural diagram of an embodiment of a computer device according to the present application. DETAILED DESCRIPTION

[0045] Unless otherwise defined, all technical and scientific terms used herein have the same meanings as commonly understood by those skilled in the art to which this application belongs. The terms used in the specification of the application are for the purpose of describing specific embodiments only and are not intended to limit this application. The terms "including" and "having" and any variations thereof in the specification and claims of this application and the above-mentioned drawings are intended to cover non-exclusive inclusions. The terms "first", "second", etc. in the specification and claims of this application or the above-mentioned drawings are used to distinguish different objects, not to describe a specific order.

[0046] References herein to "embodiments" mean that a particular feature, structure, or characteristic described in connection with the embodiments may be included in at least one embodiment of the present application. The appearance of this phrase in various places in the specification does not necessarily refer to the same embodiment, nor does it constitute an independent or alternative embodiment that is mutually exclusive of other embodiments. It is understood, both explicitly and implicitly, by those skilled in the art that the embodiments described herein may be combined with other embodiments.

[0047] In order to enable those skilled in the art to better understand the solution of the present application, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the accompanying drawings.

[0048] This application provides a batch data exception processing method that can be applied to Figure 1 In the illustrated system architecture 100, the system architecture 100 may include terminal devices 101, 102, and 103, a network 104, and a server 105. The network 104 is used as a medium for providing communication links between the terminal devices 101, 102, and 103 and the server 105. The network 104 may include various connection types, such as wired or wireless communication links or fiber optic cables.

[0049] Users can use terminal devices 101, 102, and 103 to interact with server 105 via network 104 to receive or send messages, etc. Various communication client applications can be installed on terminal devices 101, 102, and 103, such as web browser applications, shopping applications, search applications, instant messaging tools, email clients, social platform software, etc.

[0050] Terminal devices 101, 102, and 103 can be various electronic devices with display screens and support web browsing, including but not limited to smartphones, tablet computers, e-book readers, MP3 players (Moving Picture Experts Group Audio Layer III), MP4 (Moving Picture Experts Group Audio Layer IV), laptop computers, desktop computers, etc.

[0051] The server 105 may be a server that provides various services, such as a background server that provides support for web pages displayed on the terminal devices 101 , 102 , and 103 .

[0052] It should be noted that the batch data exception processing method provided in the embodiment of the present application is generally executed by a server / terminal device, and accordingly, the batch data exception processing device is generally set in the server / terminal device.

[0053] It should be understood that Figure 1 The number of terminal devices, networks and servers in the embodiment is merely illustrative. Any number of terminal devices, networks and servers may be provided as required.

[0054] Continue to refer Figure 2 , shows a flow chart of an embodiment of a method for handling batch data anomalies according to the present application, comprising the following steps:

[0055] Step S201: obtaining a source data table to be written, and identifying first abnormal data in the source data table by executing an abnormality identification script.

[0056] In this embodiment, the exception identification script is an SQL script. The SQL script will proactively report possible exceptions and then capture the abnormal data to ensure the entire process is not interrupted. SQL (Structured Query Language) is an SQL statement that executes a specific business data processing logic to implement certain business logic, such as converting data from one table and inserting it into another table.

[0057] Specifically, an exception identification script can be generated based on pre-set configuration information to identify possible exceptions in the source data table, wherein the configuration information is specific verification fields in the source data table, including field types, field attributes, etc.

[0058] Determine a data identification script template according to the configuration information, determine configurable variables in the data identification script template corresponding to the configuration information, and replace the configurable variables with specific check fields in the configuration information, thereby generating an exception identification script.

[0059] The data identification script template is pre-written by the developer (or user). Configurable variables in the data identification script template are selected based on the specific verification fields to generate an exception identification script. Each data type can correspond to a specific identification script, or multiple data types can correspond to a single identification script. The generated exception identification scripts are pre-stored in the database, and the user can select the corresponding identification script from the database for verification based on actual needs.

[0060] Data types include String, Int, Date, Boolean, and Decimal. Each data type has its own corresponding field format requirements. If the field format requirements are not met, it is considered abnormal data. For example, when the field data corresponds to the String character type, the field format requirement is that the field must not be empty. If the field data is empty, the field is identified as an abnormal field.

[0061] It should be understood that the first abnormal data is obtained by identifying possible abnormalities in the field attributes of the data in the source data table.

[0062] It should be emphasized that in order to further ensure the privacy and security of the original source data table, the above-mentioned original source data table can also be stored in a node of a blockchain.

[0063] The blockchain referred to in this application is a new application model for computer technologies such as distributed data storage, peer-to-peer transmission, consensus mechanisms, and encryption algorithms. Blockchain is essentially a decentralized database, a series of data blocks generated using cryptographic methods. Each data block contains information about a batch of network transactions, which is used to verify the validity of this information (to prevent counterfeiting) and generate the next block. Blockchain can include the underlying blockchain platform, the platform product service layer, and the application service layer.

[0064] Step S202 : performing logical transformation on the source data table according to the first abnormal data to obtain a transformed source data table.

[0065] In this embodiment, the abnormal data is logically transformed, that is, the field restrictions corresponding to the abnormal data are relaxed, so that the transformed source data table can be compatible with the abnormal data, avoiding errors and causing the business process to terminate.

[0066] For example, when writing data normally from table A, if there is abnormal data (including dirty data and erroneous data) in table A, an error will be reported during the writing process, thereby terminating the entire writing process. In this embodiment, the abnormal data in table A is logically transformed to obtain a transformed table A. The transformed table A relaxes the error reporting restriction, that is, it includes abnormal data, which can ensure that the writing process is not interrupted and the abnormal data can be written to the abnormal data table.

[0067] Specifically, the abnormal field of the first abnormal data in the source data table is determined, and the abnormal field is modified according to the abnormal type of the abnormal field.

[0068] In this embodiment, an abnormal field is determined, and a data modification request from a user to modify the abnormal field is received, wherein the data modification request carries an SQL modification statement, and the SQL modification statement includes the table name of the source table, the abnormal field, and the target field attribute, and the target field attribute is the field attribute obtained after modification according to the abnormal type of the abnormal field; the SQL modification statement is executed to extract the initial field attribute corresponding to the abnormal field from the source data table according to the position of the abnormal field, and the initial field attribute is associated with the target field attribute corresponding to the abnormal field, and the initial field attribute is modified to the target field attribute in the source data table to implement data modification based on SQL statements, thereby improving the efficiency and accuracy of data modification.

[0069] In this embodiment, the position of the abnormal field can be determined based on the abnormal log. Specifically, the abnormal log corresponding to the abnormal data is obtained, and the data identifier is extracted from the abnormal log, wherein the data identifier includes the position of the abnormal data in the source data table;

[0070] Determine the abnormal field corresponding to the abnormal data based on the location of the abnormal data in the source data table.

[0071] In this embodiment, after the abnormal data is identified by executing the abnormal identification script, an abnormal log is generated for record preservation. When locating the abnormal data, the abnormal log corresponding to the abnormal data is obtained, wherein the abnormal log includes the data identifier corresponding to the abnormal data. Based on the data identifier of the abnormal log, the abnormal field corresponding to the abnormal data in the original source data table can be obtained, thereby locating the abnormal data and improving the efficiency and accuracy of abnormal location.

[0072] Step S203 , writing the data in the transformed source data table into the target data table to be written in the database, and identifying the transformed source data table during the writing process to obtain second abnormal data, and writing the second abnormal data into the abnormal data table.

[0073] In this embodiment, a target data table is created in the target database. While the abnormal data in the source data table is logically transformed, an abnormal data table is established in the target database. In this way, when writing the data in the source data table into the target data table, the identified abnormal data is written to the abnormal data table to avoid interrupting the process due to error reporting.

[0074] Abnormal data written to the target data table will result in an error. To prevent any impact on the normal import process, abnormal data identified during the writing process will be processed in a targeted manner. The target data table has field restrictions. When writing data from the transformed source data table to the target data table, if any data does not conform to the target data table's field format requirements, this data is considered second abnormal data and will be written to the abnormal data table.

[0075] In this embodiment, the step of writing the transformed data in the source data table into the target data table to be written in the database and simultaneously writing the second abnormal data in the source data table into the abnormal data table includes:

[0076] Reading preset storage structure information of the target data table, the preset storage structure information includes standard field attributes of the target data table;

[0077] Use the data cleaning and conversion tool to read the field data in the transformed source data table and determine whether the field attributes of the field data conform to the standard field attributes;

[0078] If it does, the field data is written into the target data table; if it does not, the field data that is inconsistent with the standard field format of the standard field attribute is determined as second abnormal data and the second abnormal data is written into the abnormal data table.

[0079] Among them, the data cleaning and conversion tool can use the Kettle tool to extract data from the transformed source data table through the Kettle tool, and convert the abnormal data during the extraction process, that is, write the abnormal data to the abnormal data table created in the target database instead of writing it to the target data table, so as to avoid abnormal errors causing the batch running task to directly affect subsequent dependent tasks.

[0080] Among them, Kettle is an open source ETL tool written in Java. It can run on Window, Linux or Unix systems, and its data extraction is efficient and stable.

[0081] The preset storage structure information can be used to describe the standard storage structure of the data in the target data table, including the field mapping relationship between the source data table and the target data table. Specifically, the preset storage structure information may include the standard field attributes of the target data table, wherein the standard field attributes may include, but are not limited to, standard field names, standard field types, and standard field formats. Among them, the standard field name is used to indicate the type of information to which the data in each column of the target data table belongs; the standard field type is the type of data belonging to the field, for example, it may be a binary data type, a character data type, etc., but is not limited thereto; the standard field format is used to indicate the format of the data belonging to the field in the target data table, such as the field length, whether the field has an empty value, etc.

[0082] Read the field data in the transformed source data table, compare the field attributes of the field data with the standard field attributes, if the field attributes of the field data conform to the standard field attributes, the field data is normal data, and is written into the target data table; if the field data does not conform to the standard field attributes, it is determined that the field data is the second abnormal data, and is written into the abnormal data table.

[0083] For example, suppose source table A contains 100 records: 1 record has an empty field, 1 record has a primary key conflict, 1 record has an excessively long field, and the other 97 records are normal. Target table B ultimately writes 97 normal records, but does not write 3 abnormal records. The 3 abnormal records are recognized as abnormal and written to abnormal data table C.

[0084] This application logically transforms the identified possible abnormal data, eliminates restrictions, converts and processes the abnormalities when writing data, and writes the abnormal data identified during the writing process into the abnormal data table. It can quickly and easily capture and process the abnormalities automatically, avoiding error interruptions in batch tasks that affect subsequent business, while reducing the number of manual interventions, and achieving a qualitative improvement in the timeliness and stability of the overall task.

[0085] In some optional implementations of this embodiment, the step of identifying the first abnormal data in the source data table by executing the abnormality identification script includes:

[0086] Read the field data in the source data table;

[0087] The preset exception recognition function is called by executing the exception recognition script to perform exception recognition on the field data to obtain first exception data, wherein the preset exception recognition function is determined according to the preset exception type.

[0088] In this embodiment, an exception judgment is first performed based on the possible exception type. The exception recognition function set according to the exception type is called by executing the exception recognition script to identify the field data in the source data table to determine whether the field data meets the exception rules of its corresponding field. If so, the field data is the first exception data.

[0089] Exception rules are set based on the types of exceptions that occur in historical data. These include, but are not limited to, primary key conflicts, null field values, and non-compliance with field formats. Non-compliance with field formats includes data with a field count that does not match the target field count, a field format that does not match the preset field format, and missing fields.

[0090] This embodiment can avoid subsequent process interruptions and ensure business stability by proactively identifying and reporting possible anomalies.

[0091] In some optional implementations, the step of modifying the exception field according to the exception type of the exception field includes:

[0092] If the exception type is a primary key conflict, the primary key constraint of the exception field will be deleted;

[0093] If the exception type is that the data length of the first exception data does not conform to the preset field format, the data length is adjusted according to the preset field format;

[0094] If the exception type is that the first exception data is a null value for the field, the field non-null restriction of the exception field is deleted.

[0095] In this embodiment, the logical transformation is to transform the corresponding field attributes in the source data table where the abnormal data is located. The field attributes include primary key restrictions, field length restrictions, and field non-empty restrictions. When a primary key conflict is identified, the primary key restriction is removed; when it is identified that the abnormal data does not meet the preset field format requirements, it is modified according to the specific abnormal situation. For example, if the data length of the abnormal data is greater than the preset length threshold of the abnormal field, the preset length threshold is modified to be not less than the data length; when it is identified that the abnormal data is a field null value, the field non-empty restriction of the abnormal field is removed.

[0096] For example, the source data table has a primary key restriction. For example, if the primary key field value is A, there cannot be two data with field A. If there are two or more data with field A, there is a primary key conflict in the source data table, and the primary key restriction is removed; field B in the source data table has a field length restriction of 10 characters, that is, the data in this field cannot exceed 10 characters in length, so the length of this field can be relaxed, and the relaxed length can be no less than 10; field C in the source data table requires the field to be non-empty, and an error will be reported if the field data is an empty value, so the non-empty restriction of this field is removed, that is, the data in this field is allowed to be an empty value.

[0097] By performing field transformation based on specific exception types, field modifications are more targeted, the transformation is more efficient and accurate, and exception compatibility of the source data table is achieved, avoiding the impact of abnormal error reports on business processes and improving the timeliness and stability of the business.

[0098] In this embodiment, the step of writing the field data into the target data table includes:

[0099] Extract standard field attributes that have a mapping relationship with the field data from the target data table according to the field data;

[0100] Generate a write execution statement based on the standard field properties and field data, and write the field data into the target data table through the write execution statement.

[0101] Among them, the target data table is created according to the data structure of the source data table, with preset storage structure information, and there is mapping between the fields of the two data tables. Specifically, the field mapping relationship can be saved through a JSON file, and the standard field attributes in the target data table and the field values ​​(i.e., field data) in the source data table corresponding to each standard field attribute are stored in the JSON file in JSON format. The JSON file is parsed to obtain the field data in the source data table and its corresponding standard field attributes of the target database, and a write execution statement is generated according to the standard field attributes and field data. The field data is written into the target data table through the write execution statement, completing the transfer of normal data from the source data table to the target data table.

[0102] The write execution statement includes target field properties, which include standard field name, standard field type and standard field format. Executing the write execution statement will fill the field data in the transformed source data table into the position corresponding to the standard field name in the target data table.

[0103] It should be noted that the transformed source data table transforms the field attributes corresponding to the abnormal data in the source data table, and does not modify other aspects. Therefore, the field mapping relationship between the source data table and the target data table remains unchanged.

[0104] This embodiment obtains the target field and field data through the field mapping relationship, generates a write execution statement according to the target field and field data, and executes the write execution statement to complete data writing, which can improve data writing efficiency.

[0105] In some optional implementations of this embodiment, the step of writing the field data into the target data table by writing an execution statement includes:

[0106] Encapsulate the written execution statement into a message and deliver it to the message queue;

[0107] Start the import thread, read the message from the message queue, parse the message, and obtain the write execution statement and the target data table;

[0108] The import thread executes the write statement to write the field data into the target data table.

[0109] The write execution statement is encapsulated according to the preset message data structure, and the corresponding message is encapsulated. The message is delivered to the message queue in the execution order of the write execution statement. When there is an idle thread, one or more independent threads are started as import threads to execute the data import task.

[0110] Specifically, when the import thread is started, messages are read from the message queue according to the first-in-first-out principle, and the messages are parsed to obtain the write execution statement and the target data table. The write execution statement is executed by the import thread, and the field data is written to the target field of the corresponding target data table to complete the data import.

[0111] This embodiment places write execution statements into a message queue and uses the queue's queuing mechanism to prevent interference between write execution statements, thereby ensuring the correctness of data writing. In addition, by starting an idle thread to execute write tasks, data writing efficiency and system resource utilization can be improved.

[0112] In some optional implementations, the message reading progress of the message queue is monitored to obtain the data import progress, and the task progress is monitored in real time based on the import progress.

[0113] The present application can be used in many general or special computer system environments or configurations. For example: personal computers, server computers, handheld or portable devices, tablet devices, multiprocessor systems, microprocessor-based systems, set-top boxes, programmable consumer electronics, network PCs, minicomputers, mainframe computers, distributed computing environments including any of the above systems or devices, and the like. The present application can be described in the general context of computer-executable instructions executed by a computer, such as program modules. Generally, program modules include routines, programs, objects, components, data structures, and the like that perform specific tasks or implement specific abstract data types. The present application can also be practiced in distributed computing environments in which tasks are performed by remote processing devices connected via a communication network. In a distributed computing environment, program modules can be located in local and remote computer storage media, including storage devices.

[0114] Those skilled in the art will appreciate that all or part of the processes in the above-described method embodiments can be implemented by instructing related hardware via computer-readable instructions. The computer-readable instructions can be stored in a computer-readable storage medium, and when the program is executed, it can include the processes in the above-described method embodiments. The aforementioned storage medium can be a non-volatile storage medium such as a magnetic disk, an optical disk, a read-only memory (ROM), or a random access memory (RAM).

[0115] It should be understood that although the steps in the flowcharts of the accompanying drawings are shown in sequence as indicated by the arrows, these steps are not necessarily executed in the order indicated by the arrows. Unless otherwise specified herein, there is no strict order restriction on the execution of these steps, and they can be executed in other orders. Moreover, at least some of the steps in the flowcharts of the accompanying drawings may include multiple sub-steps or multiple stages, and these sub-steps or stages are not necessarily executed at the same time, but can be executed at different times, and their execution order is not necessarily sequential, but can be executed in turn or alternately with other steps or at least a portion of the sub-steps or stages of other steps.

[0116] Further references Figure 3 , as a response to the above Figure 2 The present application provides an embodiment of a batch data exception processing device, which is similar to the embodiment of the present invention. Figure 2 Corresponding to the method embodiment shown, the device can be specifically applied to various electronic devices.

[0117] like Figure 3As shown, the batch data exception processing device 300 of this embodiment includes: an identification module 301, a transformation module 302 and a writing module 303.

[0118] The identification module 301 is used to obtain a source data table to be written, and identify first abnormal data in the source data table by executing an abnormality identification script;

[0119] The transformation module 302 is configured to perform a logical transformation on the source data table according to the first abnormal data to obtain a transformed source data table;

[0120] The writing module 303 is used to write the data in the transformed source data table into the target data table to be written in the database, and identify the transformed source data table during the writing process to obtain second abnormal data, and write the second abnormal data into the abnormal data table.

[0121] It should be emphasized that in order to further ensure the privacy and security of the original source data table, the above-mentioned original source data table can also be stored in a node of a blockchain.

[0122] Based on the above-mentioned batch data exception processing device, by logically transforming the identified abnormal data, removing restrictions, and then converting and processing the exceptions when writing data, and writing the abnormal data into the exception data table, the exceptions can be captured and processed automatically quickly and easily, avoiding error interruptions in batch tasks that affect subsequent business, while reducing the number of manual interventions, and the overall task has been qualitatively improved in timeliness and stability.

[0123] In some optional implementations of this embodiment, the identification module 301 includes a reading submodule and an identification submodule, the reading submodule is used to read the field data in the source data table; the identification submodule is used to call a preset exception identification function by executing the exception identification script, perform exception identification on the field data, and obtain first exception data, wherein the preset exception identification function is determined according to a preset exception type.

[0124] This embodiment can avoid subsequent process interruptions and ensure business stability by proactively identifying and reporting possible anomalies.

[0125] In this embodiment, the transformation module 302 includes a determination submodule and a transformation submodule. The determination submodule is used to determine the abnormal field corresponding to the first abnormal data in the source data table; the transformation submodule is used to modify the abnormal field according to the abnormal type of the abnormal field.

[0126] This embodiment can improve the efficiency and accuracy of data modification.

[0127] In this embodiment, the transformation submodule is further used to:

[0128] If the exception type is a primary key conflict, the primary key constraint of the exception field will be deleted;

[0129] If the exception type is that the data length of the first exception data does not conform to the preset field format, modifying the data length according to the preset field format;

[0130] If the exception type is that the first exception data is a null value for a field, the non-null restriction of the exception field is deleted.

[0131] By performing field transformation based on specific exception types, field modifications are more targeted, the transformation is more efficient and accurate, and exception compatibility of the source data table is achieved, avoiding the impact of abnormal error reports on business processes and improving the timeliness and stability of the business.

[0132] In some optional implementations, the writing module 303 includes a reading submodule, a judging submodule, and a writing submodule, wherein:

[0133] The reading submodule is used to read the preset storage structure information of the target data table, wherein the preset storage structure information includes standard field attributes of the target data table;

[0134] The judgment submodule is used to read the field data in the transformed source data table through a data cleaning and conversion tool, and determine whether the field attributes of the field data conform to the corresponding standard field attributes;

[0135] The writing submodule is used to write the field data into the target data table if it is compliant; if it is not compliant, determine the field data inconsistent with the standard field format of the standard field attribute as second abnormal data and write the second abnormal data into the abnormal data table.

[0136] This embodiment uses data cleaning and conversion tools to ensure efficient and stable data extraction, improve anomaly identification efficiency and writing efficiency, and ensure business stability.

[0137] In this embodiment, the writing submodule includes an extraction unit and a writing unit. The extraction unit is used to extract standard field attributes that have a mapping relationship with the field data from the target data table according to the field data; the writing unit is used to generate a write execution statement based on the standard field attributes and the field data, and write the field data into the target data table through the write execution statement.

[0138] This embodiment obtains the target field and field data through the field mapping relationship, generates a write execution statement according to the target field and field data, and executes the write execution statement to complete data writing, which can improve data writing efficiency.

[0139] In some optional implementations of this embodiment, the writing unit is further configured to:

[0140] Encapsulate the write execution statement into a message and deliver it to the message queue;

[0141] Start an import thread, read a message from the message queue, and parse the message to obtain the write execution statement and the target data table;

[0142] The write execution statement is executed by the import thread to write the field data into the target data table.

[0143] This embodiment places write execution statements into a message queue and uses the queue's queuing mechanism to prevent interference between write execution statements, thereby ensuring the correctness of data writing. In addition, by starting an idle thread to execute write tasks, data writing efficiency and system resource utilization can be improved.

[0144] To solve the above technical problems, the present application also provides a computer device. Figure 4 , Figure 4 This is a basic structural block diagram of the computer device in this embodiment.

[0145] The computer device 4 includes a memory 41, a processor 42, and a network interface 43 that are interconnected through a system bus. It should be noted that the figure only shows a computer device 4 with components 41-43, but it should be understood that it is not required to implement all the components shown, and more or fewer components can be implemented instead. Among them, those skilled in the art can understand that the computer device here is a device that can automatically perform numerical calculations and / or information processing according to pre-set or stored instructions, and its hardware includes but is not limited to microprocessors, application-specific integrated circuits (ASICs), programmable gate arrays (FPGAs), digital signal processors (DSPs), embedded devices, etc.

[0146] The computer device may be a desktop computer, notebook computer, PDA, cloud server, etc. The computer device may interact with the user via a keyboard, mouse, remote control, touchpad, or voice control device.

[0147] The memory 41 includes at least one type of readable storage medium, including flash memory, a hard disk, a multimedia card, a card-type memory (e.g., SD or DX memory), random access memory (RAM), static random access memory (SRAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), programmable read-only memory (PROM), magnetic storage, a magnetic disk, an optical disk, etc. In some embodiments, the memory 41 can be an internal storage unit of the computer device 4, such as the hard disk or memory of the computer device 4. In other embodiments, the memory 41 can also be an external storage device of the computer device 4, such as a plug-in hard disk equipped on the computer device 4, a smart media card (SMC), a secure digital (SD) card, a flash card, etc. Of course, the memory 41 can also include both the internal storage unit of the computer device 4 and its external storage device. In this embodiment, the memory 41 is generally used to store the operating system and various application software installed on the computer device 4, such as computer-readable instructions for the batch data exception handling method. In addition, the memory 41 can also be used to temporarily store various types of data that have been output or are to be output.

[0148] In some embodiments, the processor 42 can be a central processing unit (CPU), a controller, a microcontroller, a microprocessor, or other data processing chip. The processor 42 is generally used to control the overall operation of the computer device 4. In this embodiment, the processor 42 is used to execute computer-readable instructions stored in the memory 41 or process data, such as executing computer-readable instructions for the batch data exception handling method.

[0149] The network interface 43 may include a wireless network interface or a wired network interface. The network interface 43 is generally used to establish a communication connection between the computer device 4 and other electronic devices.

[0150] This embodiment implements the steps of the batch data exception handling method as described in the above embodiment when the processor executes computer-readable instructions stored in the memory. By logically transforming the identified abnormal data and removing restrictions, and then converting and processing the abnormalities when writing the data, and writing the abnormal data into the abnormal data table, the abnormalities can be quickly and easily captured and processed automatically, avoiding error interruptions in batch running tasks that affect subsequent business, while reducing the number of manual interventions, and achieving a qualitative improvement in the timeliness and stability of the overall task.

[0151] The present application also provides another embodiment, namely, providing a computer-readable storage medium, which stores computer-readable instructions, and the computer-readable instructions can be executed by at least one processor to enable the at least one processor to perform the steps of the batch data exception processing method as described above, by logically transforming the identified abnormal data, removing restrictions, and then converting and processing the exceptions when writing the data, and writing the abnormal data into an exception data table, the exceptions can be quickly and easily captured and processed automatically, avoiding error interruptions in batch running tasks that affect subsequent business, while reducing the number of manual interventions, and the overall task has been qualitatively improved in timeliness and stability.

[0152] Through the description of the above implementation methods, those skilled in the art can clearly understand that the above-mentioned embodiment methods can be implemented by means of software plus the necessary general hardware platform, and of course can also be implemented by hardware, but in many cases the former is a better implementation method. Based on this understanding, the technical solution of the present application, or the part that contributes to the prior art, can be embodied in the form of a software product, which is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk), and includes a number of instructions for enabling a terminal device (which can be a mobile phone, computer, server, air conditioner, or network device, etc.) to execute the methods described in each embodiment of the present application.

[0153] Obviously, the embodiments described above are only some of the embodiments of the present application, rather than all of the embodiments. The preferred embodiments of the present application are given in the accompanying drawings, but they do not limit the patent scope of the present application. The present application can be implemented in many different forms. On the contrary, the purpose of providing these embodiments is to make the understanding of the disclosure of the present application more thorough and comprehensive. Although the present application has been described in detail with reference to the aforementioned embodiments, for those skilled in the art, it is still possible to modify the technical solutions described in the aforementioned specific embodiments, or to make equivalent replacements for some of the technical features therein. Any equivalent structure made using the contents of the present application specification and the accompanying drawings, directly or indirectly used in other related technical fields, is also within the scope of patent protection of the present application.

Claims

1. A method for handling batch data anomalies, characterized in that: The steps include: Obtaining a source data table to be written, and identifying first abnormal data in the source data table by executing an abnormality identification script, wherein the first abnormal data is obtained by identifying an abnormality occurring in a field attribute of data in the source data table; logically transforming the source data table according to the first abnormal data to obtain a transformed source data table; Writing the data in the transformed source data table into a target data table to be written in the database, and identifying the transformed source data table during the writing process to obtain second abnormal data, and writing the second abnormal data into the abnormal data table, wherein the second abnormal data is data that does not meet the field format requirements of the target data table; The step of performing logical transformation on the source data table according to the first abnormal data includes: Determine the abnormal field corresponding to the first abnormal data in the source data table; Modify the abnormal field according to the abnormal type of the abnormal field; The step of modifying the abnormal field according to the abnormal type of the abnormal field includes: If the exception type is a primary key conflict, the primary key constraint of the exception field will be deleted; If the exception type is that the data length of the first exception data is greater than a preset length threshold, modifying the preset length threshold to be no less than the data length; If the exception type is that the first exception data is a null value for a field, the non-null restriction of the exception field is deleted.

2. The batch data exception processing method according to claim 1, characterized in that: The step of identifying the first abnormal data in the source data table by executing the abnormality identification script includes: Read the field data in the source data table; The abnormality identification script is executed to call a preset abnormality identification function, and abnormality identification is performed on the field data to obtain first abnormality data, wherein the preset abnormality identification function is determined according to a preset abnormality type.

3. The batch data exception processing method according to claim 1 or 2, characterized in that: The step of writing the data in the transformed source data table into a target data table to be written in the database, identifying the transformed source data table during the writing process to obtain second abnormal data, and writing the second abnormal data into the abnormal data table comprises: Reading preset storage structure information of the target data table, wherein the preset storage structure information includes standard field attributes of the target data table; Reading the field data in the transformed source data table through a data cleaning and conversion tool, and determining whether the field attributes of the field data conform to the corresponding standard field attributes; If it does, the field data consistent with the standard field attributes will be written into the target data table; if it does not, the field data inconsistent with the standard field format of the standard field attributes will be determined as second abnormal data, and the second abnormal data will be written into the abnormal data table.

4. The method for handling batch data anomalies according to claim 3, wherein: The step of writing the field data into the target data table comprises: Extracting standard field attributes having a mapping relationship with the field data from the target data table according to the field data; A write execution statement is generated by combining the standard field attributes and the field data, and the field data is written into the target data table through the write execution statement.

5. The batch data exception processing method according to claim 4, characterized in that: The step of writing the field data into the target data table by using the write execution statement includes: Encapsulate the write execution statement into a message and deliver it to the message queue; Start an import thread, read a message from the message queue, and parse the message to obtain the write execution statement and the target data table; The write execution statement is executed by the import thread to write the field data into the target data table.

6. A batch data exception processing device, characterized in that: include: an identification module, configured to obtain a source data table to be written, and identify first abnormal data in the source data table by executing an abnormality identification script, wherein the first abnormal data is identified based on an abnormality in a field attribute of data in the source data table; a transformation module, configured to perform logical transformation on the source data table according to the first abnormal data to obtain a transformed source data table; a writing module, configured to write the data in the transformed source data table into a target data table to be written in the database, and to identify the transformed source data table during the writing process to obtain second abnormal data, and to write the second abnormal data into the abnormal data table, wherein the second abnormal data is data that does not conform to the field format requirements of the target data table; The transformation module includes a determination submodule and a transformation submodule, wherein: The determination submodule is used to determine the abnormal field corresponding to the first abnormal data in the source data table; The transformation submodule is used to modify the abnormal field according to the abnormal type of the abnormal field; The transformation submodule is further used to: If the exception type is a primary key conflict, the primary key constraint of the exception field will be deleted; If the exception type is that the data length of the first exception data is greater than a preset length threshold, modifying the preset length threshold to be no less than the data length; If the exception type is that the first exception data is a null value for a field, the non-null restriction of the exception field is deleted.

7. A computer device comprising a memory and a processor, wherein the memory stores computer-readable instructions, and the processor implements the steps of the batch data exception processing method according to any one of claims 1 to 5 when executing the computer-readable instructions.

8. A computer-readable storage medium, characterized in that The computer-readable storage medium stores computer-readable instructions, which, when executed by a processor, implement the steps of the batch data exception processing method according to any one of claims 1 to 5.

Images