Data processing method, device, electronic device and computer readable medium

Abstract

The present application discloses a data processing method, device, electronic device and computer-readable medium, and relates to the field of data storage technology. The method comprises: receiving a data processing request and obtaining a corresponding job identifier; obtaining a corresponding database table according to the job identifier, unloading the database table to a target file, and then performing code conversion on the target file to generate a first conversion file; obtaining a conversion status identifier corresponding to each character in the first conversion file; determining a conversion failure character according to the conversion status identifier, and then updating the first conversion file based on the conversion failure character; performing code conversion on the updated first conversion file to generate a second conversion file, and then loading the second conversion file into the database table. By unloading and loading the database table, after two code conversions, abnormal characters can be deleted or replaced without causing abnormalities in program or other system functions.

Description

Technical Field

[0001] This application relates to the technical field of data storage, and in particular, to a data processing method, apparatus, electronic device, and computer-readable medium. Background Art

[0002] When a user inputs information such as an address or a name, due to input methods and recognition problems, any characters may enter a batch table in the database, such as emojis, handwritten similar characters, unresolvable binary sequences, handwritten characters like "厂" and " ", symbols like "", invalid sequences like the binary sequence "0xefbfbd". When using a model to process and recognize these strings or directly read and perform encoding conversion in an old system, it may cause program exceptions.

[0003] In the process of implementing this application, the inventors found that there are at least the following problems in the prior art:

[0004] In a batch database table, abnormal characters in the text string cause database or other system function abnormalities. Summary of the Invention

[0005] In view of this, embodiments of this application provide a data processing method, apparatus, electronic device, and computer-readable medium, which can solve the problem that in an existing batch database table, abnormal characters in the text string cause database or downstream system, subsequent systems of data streams, and other system function abnormalities.

[0006] To achieve the above object, according to one aspect of the embodiments of this application, a data processing method is provided, including:

[0007] Receiving a data processing request and obtaining a corresponding job identifier;

[0008] Obtaining a corresponding database table according to the job identifier, unloading the database table to a target file, and then performing encoding conversion on the target file to generate a first conversion file;

[0009] Obtaining conversion status identifiers corresponding to each character in the first conversion file;

[0010] Determining conversion failure characters according to the conversion status identifiers, and then updating the first conversion file based on the conversion failure characters;

[0011] Performing encoding conversion on the updated first conversion file to generate a second conversion file, and then loading the second conversion file into the database table.

[0012] Optionally, obtaining conversion status identifiers corresponding to each character in the first conversion file includes:

[0013] Dividing the first conversion file according to a preset number of lines to generate first conversion sub-files;

[0014] Each sub-thread is called to traverse each first conversion sub-file in parallel by line, and a conversion state identifier corresponding to a character corresponding to each line in each first conversion sub-file is obtained.

[0015] Optionally, based on the conversion failure character, updating the first conversion file includes:

[0016] Determine the row corresponding to each conversion-failed character, and then determine the total number of characters and the number of conversion-failed characters in each row;

[0017] Based on the total number of characters and the number of characters that failed to be converted, determine the proportion of characters that failed to be converted corresponding to each row;

[0018] The rows corresponding to the conversion failure character ratio greater than a preset threshold are deleted, thereby updating the first conversion file.

[0019] Optionally, updating the first conversion file based on the conversion failed character further includes:

[0020] The conversion failed characters in the row corresponding to the conversion failed character ratio less than a preset threshold are deleted, thereby updating the first conversion file.

[0021] Optionally, unload the database table to a target file, including:

[0022] Create target files based on preset formats;

[0023] Convert each character in the database table into a preset format to copy it to the target file, and then determine the percentage of successful uninstallation;

[0024] In response to the uninstall success ratio being greater than a preset uninstall ratio threshold, returning uninstall success information and saving uninstall log information;

[0025] In response to the uninstallation success ratio being less than a preset uninstallation ratio threshold, an uninstallation failure message is returned, and the target file is deleted.

[0026] Optionally, loading the second conversion file into a database table includes:

[0027] Clear the database table and determine the data format corresponding to the database table;

[0028] Convert each character in the second conversion file into a data format, thereby determining a conversion rate;

[0029] In response to the conversion rate reaching the conversion rate threshold, returning a loading success message;

[0030] In response to the conversion rate not reaching the conversion rate threshold, a rollback process is executed and a loading failure message is returned.

[0031] Optionally, unload the database table to a target file, including:

[0032] Partition the database table to generate each sub-database table;

[0033] Unload each sub-database table to each target file in parallel.

[0034] Optionally, unload the database table to a target file, including:

[0035] Obtain uninstall configuration information, and then obtain the uninstall identifier corresponding to each character in the database table from the uninstall configuration information;

[0036] Unload the unload identifier in the database table as a non-empty character to the target file.

[0037] Optionally, after loading the second conversion file into the database table, the method further includes:

[0038] Update the database table after loading the second transformation file.

[0039] In addition, the present application also provides a data processing device, including:

[0040] A receiving unit is configured to receive a data processing request and obtain a corresponding job identifier;

[0041] An unloading unit is configured to obtain a corresponding database table according to the job identifier, unload the database table to a target file, and then perform code conversion on the target file to generate a first conversion file;

[0042] An acquisition unit, configured to acquire a conversion state identifier corresponding to each character in the first conversion file;

[0043] An updating unit, configured to determine the conversion failure character according to the conversion state identifier, and then update the first conversion file based on the conversion failure character;

[0044] The loading unit is configured to perform code conversion on the updated first conversion file to generate a second conversion file, and then load the second conversion file into the database table.

[0045] Optionally, the acquisition unit is further configured to:

[0046] Dividing the first conversion file according to a preset number of lines to generate first conversion sub-files;

[0047] Each sub-thread is called to traverse each first conversion sub-file in parallel by line, and a conversion state identifier corresponding to a character corresponding to each line in each first conversion sub-file is obtained.

[0048] Optionally, the updating unit is further configured to:

[0049] Determine the row corresponding to each conversion-failed character, and then determine the total number of characters and the number of conversion-failed characters in each row;

[0050] Based on the total number of characters and the number of characters that failed to be converted, determine the proportion of characters that failed to be converted corresponding to each row;

[0051] The rows corresponding to the conversion failure character ratio greater than a preset threshold are deleted, thereby updating the first conversion file.

[0052] Optionally, the updating unit is further configured to:

[0053] The conversion failed characters in the row corresponding to the conversion failed character ratio less than a preset threshold are deleted, thereby updating the first conversion file.

[0054] Optionally, the unloading unit is further configured to:

[0055] Create target files based on preset formats;

[0056] Convert each character in the database table into a preset format to copy it to the target file, and then determine the percentage of successful uninstallation;

[0057] In response to the uninstall success ratio being greater than a preset uninstall ratio threshold, returning uninstall success information and saving uninstall log information;

[0058] In response to the uninstallation success ratio being less than a preset uninstallation ratio threshold, an uninstallation failure message is returned, and the target file is deleted.

[0059] Optionally, the loading unit is further configured to:

[0060] Clear the database table and determine the data format corresponding to the database table;

[0061] Convert each character in the second conversion file into a data format, thereby determining a conversion rate;

[0062] In response to the conversion rate reaching the conversion rate threshold, returning a loading success message;

[0063] In response to the conversion rate not reaching the conversion rate threshold, a rollback process is executed and a loading failure message is returned.

[0064] Optionally, the unloading unit is further configured to:

[0065] Partition the database table to generate each sub-database table;

[0066] Unload each sub-database table to each target file in parallel.

[0067] Optionally, the unloading unit is further configured to:

[0068] Obtain uninstall configuration information, and then obtain the uninstall identifier corresponding to each character in the database table from the uninstall configuration information;

[0069] Unload the unload identifier in the database table as a non-empty character to the target file.

[0070] Optionally, the updating unit is further configured to:

[0071] Update the database table after loading the second transformation file.

[0072] In addition, the present application also provides a data processing electronic device, including: one or more processors; a storage device for storing one or more programs, when the one or more programs are executed by one or more processors, the one or more processors implement the data processing method as described above.

[0073] In addition, the present application also provides a computer-readable medium on which a computer program is stored, and when the program is executed by a processor, the data processing method as described above is implemented.

[0074] To achieve the above objective, according to another aspect of the embodiments of the present application, a computer program product is provided.

[0075] A computer program product according to an embodiment of the present application includes a computer program, and when the program is executed by a processor, the data processing method provided by the embodiment of the present application is implemented.

[0076] One embodiment of the above invention has the following advantages or beneficial effects: the application receives a data processing request to obtain a corresponding job identifier; obtains a corresponding database table according to the job identifier, unloads the database table to a target file, and then performs code conversion on the target file to generate a first conversion file; obtains a conversion status identifier corresponding to each character in the first conversion file; determines the conversion failure character according to the conversion status identifier, and then updates the first conversion file based on the conversion failure character; performs code conversion on the updated first conversion file to generate a second conversion file, and then loads the second conversion file to the database table. By unloading and loading the database table, after two code conversions, the code conversion judgment and segmentation operations using SQL language and Postgre built-in functions are avoided to process abnormal characters that have been entered into the data table. Abnormal characters are deleted or replaced within the scope that the database server or ETL server can handle, and do not cause abnormal functions of the program or other systems.

[0077] The further effects of the above-mentioned non-conventional optional manner will be described below in conjunction with the specific implementation manner. BRIEF DESCRIPTION OF THE DRAWINGS

[0078] The accompanying drawings are used to better understand the present application and do not constitute an improper limitation on the present application.

[0079] Figure 1 is a schematic diagram of the main flow of the data processing method according to the first embodiment of the present application;

[0080] Figure 2 is a schematic diagram of the main flow of the data processing method according to the second embodiment of the present application;

[0081] Figure 3 is a schematic diagram of the overall flow of a data processing method according to the third embodiment of the present application;

[0082] Figure 4 is a schematic diagram of an unloading process of a data processing method according to an embodiment of the present application;

[0083] Figure 5 is a schematic diagram of a loading process of a data processing method according to an embodiment of the present application;

[0084] Figure 6 is a schematic diagram of server deployment of a data processing method according to an embodiment of the present application;

[0085] Figure 7 is a schematic diagram of main units of a data processing device according to an embodiment of the present application;

[0086] Figure 8 is an exemplary system architecture diagram to which the embodiments of the present application can be applied;

[0087] Fig. 9 It is a structural diagram of a computer system of a terminal device or server suitable for implementing an embodiment of the present application. DETAILED DESCRIPTION

[0088] The following is a description of exemplary embodiments of the present application in conjunction with the accompanying drawings, including various details of the embodiments of the present application to facilitate understanding, which should be considered as merely exemplary. Therefore, it should be recognized by those of ordinary skill in the art that various changes and modifications may be made to the embodiments described herein without departing from the scope and spirit of the present application. Similarly, for the sake of clarity and conciseness, the description of well-known functions and structures is omitted in the following description. The acquisition, storage, use, and processing of data in the technical solution of the present application are in compliance with the relevant provisions of national laws and regulations.

[0089] Figure 1 is a schematic diagram of the main flow of the data processing method according to the first embodiment of the present application, such as Figure 1 As shown, the data processing method includes:

[0090] Step S101, receiving a data processing request and obtaining a corresponding job identifier.

[0091] In this embodiment, the execution subject of the data processing method (for example, it can be a server, specifically an ETL server, ETL: the abbreviation of extract, transform, load, which refers to the process of extracting, converting, and loading data from a source to a target location) can receive a data processing request through a wired connection or a wireless connection. The data processing request, for example, can be a request to check and process abnormal characters in a database table. In the scenario of batch processing jobs, the triggering condition of the data processing method of the embodiment of the present application can be: after the previous job (such as the job of loading the database table) is completed (for example, after the database table is loaded from Postgre), an event will be sent to the execution subject. When the execution subject receives this event (for example, a data processing request is received), it obtains the corresponding job identifier, and then generates an event notification based on the job identifier and sends the event notification to the downstream program. After the downstream program receives the event notification, it starts to execute the data processing method of the present application. The job identifier is used to indicate which job execution result is processed. The job identifier, such as the number of the previous job of loading the database table, can be 001, 002, etc. After obtaining the job identifier, the execution subject can obtain the execution result of the job corresponding to the job identifier, for example, obtain the database table loaded by the job corresponding to the job identifier, and perform data processing on the database table.

[0092] Step S102, obtaining a corresponding database table according to the job identifier, unloading the database table to a target file, and then performing code conversion on the target file to generate a first conversion file.

[0093] Database tables include online tables and batch tables. Online tables: Database tables involved in online transaction processing, which usually have add, delete, query and modify operations at all times. Batch tables: A type of table with special business functions, different from online tables, which are updated, deleted and inserted through scheduled or dependent programs, but are read-only for other programs.

[0094] After the execution subject obtains the corresponding database table according to the job identifier, the uninstall process can be executed, specifically, the database table is uninstalled to the target file (the target file is a file designated to hold the uninstalled database table).

[0095] For example, Figure 3As shown, after the execution entity unloads the database table to the target file, the target file can be subjected to the first encoding conversion to generate a first conversion file. Specifically, the execution entity performing the first encoding conversion on the target file can be: converting the target file from UTF-8 to GBK encoding to generate a first conversion file, and the first conversion file is the file in GBK encoding corresponding to the database table.

[0096] Among them, GBK encoding: When computers were invented and for a long time afterwards, they were only used in the United States and some Western countries. ASCII encoding could well meet the needs of users. However, when China also had computers, in order to display Chinese characters, a set of encoding rules had to be designed to convert Chinese characters into a form that the computer could accept. This form of encoding for Chinese characters is GB2312, which contains approximately 7,000 characters and cannot handle rare characters. Microsoft Corporation utilized the unused encoding space in GB2312 to expand the code table and formed GBK encoding. For example, the hexadecimal number D1CF represents the Chinese character "严".

[0097] UTF-8: The International Organization for Standardization (ISO) developed the Unicode character set to uniformly represent characters in all languages in the world. The total space can represent approximately 1 million characters, and UTF-8 is a widely used binary representation of the Unicode character set. In recent years, the extended space of Unicode has also been used to store emoji symbols. For example, U+4E25 represents the Chinese character "严", and when stored as UTF-8, the actual storage of "严" is the hexadecimal number 4E25.

[0098] If there are characters in UTF-8 that have no corresponding relationship in GBK and the program uses this conversion, problems such as displaying '??', ",", or system exceptions may occur.

[0099] Specifically, unloading the database table to the target file includes:

[0100] Creating a target file based on a preset format; converting each character in the database table to the preset format to copy it to the target file, and then determining the successful unloading ratio; in response to the successful unloading ratio being greater than the preset unloading ratio threshold, returning a successful unloading message and saving the unloading log information; in response to the successful unloading ratio being less than the preset unloading ratio threshold, returning a failed unloading message and deleting the target file.

[0101] The unloading ratio, such as the percentage of successful unloading. The target file is the created specified file for storing the unloaded database table, that is, the created specified unloading file.

[0102] Exemplarily, such as Figure 4As shown, first, the execution subject can obtain the uninstallation configuration information, and obtain the uninstallation parameters from the uninstallation configuration information. The uninstallation parameters may include verification information (such as user name, user role, etc.), the specified uninstallation success percentage, the target file identifier of the uninstallation, the target row of the uninstallation, the target column of the uninstallation, and the selection conditions of the uninstallation row. After obtaining the uninstallation parameters, the execution subject can call the preset information to verify whether the parameters are correct. If not, print an error message and end the uninstallation process; if yes, continue to determine whether the connection with the database where the database table is stored is successful. If the connection to the database fails, print an error message and end the uninstallation process; if the connection to the database is successful, create a specified uninstallation file, that is, create a target file, and call the corresponding interface according to the uninstallation parameters (such as hawqexport interface: uninstall table data according to custom conditions, which is an interface) to compare the number of rows that have actually been uninstalled and the number of rows that need to be uninstalled configured in the uninstallation parameters. Then determine whether the uninstallation success percentage specified by the parameters is reached. If yes, save the uninstallation log information and end the process. If not, delete the target file, print an error message and end the process. Uninstall (export): database concept, the data of the table in the database is copied to other formats other than the database, such as table files and binary files.

[0103] Specifically, unloading the database table to the target file includes: partitioning the database table to generate sub-database tables; and unloading the sub-database tables to the target files in parallel.

[0104] For example, when performing unloading of a database table, an unloading condition can be obtained. If the unloading condition is batch parallel unloading, the execution subject can unload the database table to the target file in batches and in parallel according to the unloading condition, or only process a part of the database table.

[0105] Specifically, unloading the database table to the target file includes: obtaining unloading configuration information, and then obtaining the unloading identifier corresponding to each character in the database table from the unloading configuration information; and unloading the characters in the database table whose unloading identifiers are non-empty to the target file.

[0106] For example, when the uninstallation condition indicates that only a part of the database table is to be processed, that is, only a part of the database table is to be uninstalled to the target file, each character in the database table will be marked in the uninstallation condition as to whether it is to be uninstalled, that is, each character in the database table to be uninstalled has a corresponding uninstallation mark, and the uninstallation mark indicates whether each character needs to be uninstalled. Generally, the uninstallation marks of the characters in the same row in the database table are the same. Of course, the embodiment of the present application does not specifically limit whether the uninstallation marks of the characters in the same row are the same. The execution subject can traverse each character in the database table to be uninstalled, and uninstall the corresponding characters according to the uninstallation mark corresponding to each character in the database table to be uninstalled. Specifically, if the uninstallation mark is empty, it means that the character corresponding to the uninstallation mark is not uninstalled, and the execution subject can only uninstall the characters whose uninstallation marks are not empty.

[0107] Of course, as another implementation method of the embodiment of the present application, the execution subject can also uninstall the characters with designated uninstallation identifiers, for example, uninstall the characters with uninstallation identifiers of 1, 2, or n.

[0108] As another implementation of the embodiment of the present application, it is understandable that the uninstallation mark being empty can also indicate that the character corresponding to the uninstallation mark is uninstalled. That is, the execution subject can also uninstall the character whose uninstallation mark is empty, and not uninstall the character whose uninstallation mark is non-empty. The embodiment of the present application does not specifically limit the uninstallation mark corresponding to the character to be uninstalled.

[0109] Step S103, obtaining a conversion status identifier corresponding to each character in the first conversion file.

[0110] The execution subject can obtain the conversion status identifier corresponding to each character in the first conversion file obtained after the first encoding conversion. The conversion status identifier is the result of string comparison. For example, if the encoding conversion fails, a special character is generated. The special character rarely appears in the encoded characters corresponding to the data in the row, that is, the frequency of appearance of the special character in the encoded characters corresponding to the data in this row is less than the preset frequency threshold. The conversion status identifier includes a conversion success status identifier, such as success, and a conversion failure status identifier, such as error.

[0111] In another implementation method of the embodiment of the present application, the conversion status identifier can also be generated, specifically: when the character obtained by the encoding conversion is in the preset conversion failure character pool, a conversion status identifier corresponding to the conversion failure is generated, such as error; when the character obtained by the encoding conversion is not in the preset conversion failure character pool, a conversion status identifier corresponding to the conversion success is generated, such as success.

[0112] Step S104: determining characters that failed to be converted according to the conversion status identifier, and then updating the first conversion file based on the characters that failed to be converted.

[0113] For example, when the conversion failure flag is error, the character corresponding to the conversion failure flag is the conversion failure character. The execution subject can determine whether to delete the line where the conversion failure character is located or delete only the conversion failure character based on the conversion failure character.

[0114] For example, the execution subject may determine whether to delete the line where the conversion failed characters are located or to delete only the characters that failed to be converted according to the number of characters that failed to be converted in each line of the first conversion file.

[0115] Specifically, based on the conversion failed characters, the first conversion file is updated, including: determining the rows corresponding to each conversion failed character, and then determining the total number of characters and the number of conversion failed characters in each row; based on the total number of characters and the number of conversion failed characters, determining the proportion of conversion failed characters corresponding to each row; deleting the rows corresponding to the proportion of conversion failed characters greater than a preset threshold, and then updating the first conversion file.

[0116] like Figure 3 As shown, after the execution subject converts the target file from UTF-8 to GBK encoding and generates a first conversion file, it can take a line in the first conversion file to determine whether it has a conversion failure mark, such as whether it has an error mark. If so, discard this line or perform other processing (other processing, such as only deleting the conversion failure characters corresponding to the conversion failure mark, the embodiment of the present application does not specifically limit the content of other processing); if not, keep this line, loop this step until the last line of the first conversion file is traversed and processed (processing completed means keeping this last line or discarding this line or performing other processing), and then update the first conversion file.

[0117] Specifically, updating the first conversion file based on the conversion failed characters further includes: deleting the conversion failed characters in the row corresponding to the conversion failed characters ratio less than a preset threshold, thereby updating the first conversion file.

[0118] That is, the line with more conversion failure characters is deleted, the conversion failure characters in the line with fewer conversion failure characters are deleted and the line is retained, and then the first conversion file after the deleted line or the deleted line with the conversion failure characters is updated.

[0119] As another implementation method of the embodiment of the present application, the execution subject may also first delete the conversion failure characters in each line of the first conversion file, and then perform semantic recognition on each line in the first conversion file where the conversion failure characters are deleted. Specifically, the corresponding semantics of each line in the first conversion file where the conversion failure characters are deleted may be identified based on algorithms such as tf-idf, n-gram, etc., that is, each line in the first conversion file where the conversion failure characters are deleted may be processed based on algorithms such as tf-idf, n-gram, etc., and then combined with the word2vec algorithm to determine whether the result of each processed line conforms to the distribution of a valid language. Special format sentences such as addresses can be parsed using finite state machines, and short fields such as names can be processed according to thresholds.

[0120] Step S105 , performing code conversion on the updated first conversion file to generate a second conversion file, and then loading the second conversion file into the database table.

[0121] After updating the first conversion file, the execution subject may perform another encoding conversion on the updated first conversion file to generate a second conversion file, that is, restore the file to the file corresponding to the UTF-8 encoding after the second encoding conversion.

[0122] Specific as Figure 3 As shown, after the execution subject updates the first conversion file, it converts the updated first conversion file from GBK to UTF-8 encoding to generate a second conversion file, and then loads the second conversion file (that is, the converted UTF-8 encoding file) into the original database table, ending the process.

[0123] Specifically, loading the second conversion file into the database table includes: clearing the database table and determining the data format corresponding to the database table; converting each character in the second conversion file into a data format and then determining a conversion rate; in response to the conversion rate reaching a conversion rate threshold, returning a loading success message; in response to the conversion rate not reaching the conversion rate threshold, executing a rollback process and returning a loading failure message.

[0124] Specifically, after loading the second conversion file into the database table, the method further includes: updating the database table after loading the second conversion file.

[0125] For example, Figure 5 As shown, the loading process is as follows:

[0126] First, the execution subject can obtain the loading configuration information, and obtain the loading parameters from the loading configuration information. The loading parameters may include the second conversion file identifier and verification information to be loaded. After obtaining the loading parameters, the execution subject can verify whether the loading parameters are correct based on the verification information (such as user name, user role, etc.). If not, print an error message and end the process. If yes, continue to determine whether the connection with the database where the second conversion file is stored is successful. If not, print an error message and end the process. If the connection is successful, start the loading transaction, determine whether the loading transaction is successfully opened (i.e., whether the loading process is successfully opened). If not, print an error message and end the process. If yes, clear the target table (that is, clear the database table corresponding to the unloading. Of course, the cleared database table can also be any database table. The embodiment of the present application does not specifically limit the cleared database table). After clearing the target table, the cleared target table is loaded based on the second conversion file. Loading (load) can specifically be a database concept, and data in other formats is copied into the database table. The execution subject can compare the number of rows loaded successfully with the total number of rows in the file in real time to determine whether all rows have been loaded. If so, the process ends; otherwise, the transaction is rolled back, an error message is printed, and the process ends.

[0127] like Figure 6 As shown in the figure, the original system is implemented in a way that the MySQL server directly reads the database of the Postgre server as shown in path ①. Illegal characters cause abnormalities in the downstream system. After the ETL server is added for processing, the path changes from ② to ③, thereby filtering out abnormal characters. Within the range that the database server or ETL server can process, the abnormal characters can be deleted or replaced without causing program abnormalities.

[0128] This embodiment receives a data processing request to obtain a corresponding job identifier; obtains a corresponding database table according to the job identifier, unloads the database table to a target file, and then performs code conversion on the target file to generate a first conversion file; obtains a conversion status identifier corresponding to each character in the first conversion file; determines the conversion failure character according to the conversion status identifier, and then updates the first conversion file based on the conversion failure character; performs code conversion on the updated first conversion file to generate a second conversion file, and then loads the second conversion file to the database table. By unloading and loading the database table, after two code conversions, the code conversion judgment and segmentation operations performed using the SQL language and the Postgre built-in function are avoided to process abnormal characters that have been entered into the data table. Abnormal characters are deleted or replaced within the scope that the database server or ETL server can handle, and do not cause abnormal functions of the program or other systems.

[0129] Figure 2 is a schematic diagram of the main flow of the data processing method according to the second embodiment of the present application, such as Figure 2 As shown, the data processing method includes:

[0130] Step S201, receiving a data processing request and obtaining a corresponding job identifier.

[0131] Step S202, obtaining a corresponding database table according to the job identifier, unloading the database table to a target file, and then performing code conversion on the target file to generate a first conversion file.

[0132] Step S203: divide the first conversion file according to a preset number of lines to generate first conversion sub-files.

[0133] Step S204: calling each sub-thread to traverse each first conversion sub-file in parallel by line, and obtaining the conversion state identifier corresponding to the character corresponding to each line in each first conversion sub-file.

[0134] For example, the execution body can traverse the file line by line using the index function or regular expression (optional) to determine whether to delete the entire line or delete some specific characters (i.e., delete the characters that failed to convert) and retain the remaining characters. In this step, the files can be processed in batches in parallel, such as 5000 lines per file (i.e., the first conversion sub-file). A sub-thread can be set to process each first conversion sub-file to improve multi-core concurrent performance.

[0135] Step S205: determining the conversion-failed characters according to the conversion status identifier, and then updating the first conversion file based on the conversion-failed characters.

[0136] Step S206, performing code conversion on the updated first conversion file to generate a second conversion file, and then loading the second conversion file into the database table.

[0137] As an overall embodiment of the present application, the overall execution process of the data processing method is as follows:

[0138] The data processing method is executed in four steps: unloading the data table to a file by copying operation, performing code conversion, and filtering; replacing the entire table or filtering a part of the database table according to conditions by loading operation.

[0139] By unloading and loading the database table, the encoding conversion judgment and segmentation operations using SQL language and Postgre built-in functions are avoided to handle abnormal characters that have been entered into the data table. (PostgreQL is a very powerful, open source client / server relational database management system).

[0140] In the first step, at the beginning of data processing, the executor can first use the toolset hawqexport or hashexport (depending on the system and Postgre version), or use redirection to output MySQL query results directly to the target file to complete the unloading of the entire table data. In this step, you can set the unloading conditions to unload files in batches in parallel or process only part of the table.

[0141] The second step is to use Perl to perform two encoding conversions (UTF-8 to GBK and GBK to UTF-8, respectively. Different encoding set data is required for compatibility with old systems). First convert the file to the target encoding (such as GBK) and then convert it back to the original encoding (such as UTF-8). At this time, the abnormal string will have its own unique expression. For example, the character " "After conversion, it will appear as the ascii character "?". Perl is a feature-rich computer programming language that runs on more than 100 computer platforms and is widely used. It was originally developed for text processing and is now used for a variety of tasks, including system administration, Web development, network programming, GUI development, etc.

[0142] The third step is to traverse the file line by line using the index function or regular expression (optional) to decide whether to delete the entire line or delete some specific characters and keep the remaining characters. In this step, files can be processed in batches in parallel. For example, if 5000 lines are a file, each file can be processed by a child thread to improve multi-core concurrency performance.

[0143] The fourth step is to use the tool class to load the processed files back to the data table. If you use multi-threading, you can configure it to load a single file into the table after the file processing is completed to achieve pipeline processing.

[0144] The overall process is as follows Figure 3 As shown, the processes of the first and fourth steps are as follows Figure 4 and Figure 5 As shown in the software deployment diagram, Figure 6 If the table is online, that is, a table that may be added, deleted, modified, or queried at any time, the same method can be used, but it needs to be performed when there is no transaction.

[0145] The embodiment of the present application breaks through the difficulty that the SQL statement of the Postgre database cannot handle abnormal characters, so that the system containing the database and ETL has a higher degree of cohesion and a lower degree of coupling in the data flow. The abnormal character processing in the entered table is realized. In the Postgre database component, the processing of abnormal characters cannot be realized. Through the present invention, the problem that filtering or modification is difficult due to the characteristics of the upstream and downstream systems can be easily solved at the database layer, or solved independently at the database layer, reducing the coupling of the overall system. On-demand data filtering is realized and can be embedded in the ETL process. The perl code segment can be easily embedded in the shell program or called independently to achieve the purpose of transparent addition to the existing ETL process, and the data processing in the form of files and the perl language can be added to other processes. The data processing method of the embodiment of the present application can be embedded in the process of ETL, improve the cohesion of the database system, and can add custom filtering in a regular way.

[0146] Figure 7 Schematic diagram of the main units of the data processing device according to the embodiment of the present application. Figure 7 As shown, the data processing device includes a receiving unit 701, an unloading unit 702, an acquiring unit 703, an updating unit 704 and a loading unit 705.

[0147] The receiving unit 701 is configured to receive a data processing request and obtain a corresponding job identifier;

[0148] The unloading unit 702 is configured to obtain a corresponding database table according to the job identifier, unload the database table to a target file, and then perform code conversion on the target file to generate a first conversion file;

[0149] An acquiring unit 703 is configured to acquire a conversion state identifier corresponding to each character in the first conversion file;

[0150] An updating unit 704 is configured to determine the conversion failure character according to the conversion state identifier, and then update the first conversion file based on the conversion failure character;

[0151] The loading unit 705 is configured to perform code conversion on the updated first conversion file to generate a second conversion file, and then load the second conversion file into the database table.

[0152] In some embodiments, the acquisition unit 703 is further configured to: divide the first conversion file according to a preset number of lines to generate first conversion sub-files; call each sub-thread to traverse each first conversion sub-file in parallel by line to obtain the conversion state identifier corresponding to the character corresponding to each line in each first conversion sub-file.

[0153] In some embodiments, the update unit 704 is further configured to: determine the row corresponding to each failed conversion character, and then determine the total number of characters and the number of failed conversion characters in each row; determine the proportion of failed conversion characters corresponding to each row based on the total number of characters and the number of failed conversion characters; delete the rows corresponding to the proportion of failed conversion characters greater than a preset threshold, and then update the first conversion file.

[0154] In some embodiments, the updating unit 704 is further configured to: delete the conversion failed characters in the row corresponding to the conversion failed characters ratio less than a preset threshold, thereby updating the first conversion file.

[0155] In some embodiments, the uninstallation unit 702 is further configured to: create a target file based on a preset format; convert each character in the database table into a preset format to copy it to the target file, and then determine the uninstallation success rate; in response to the uninstallation success rate being greater than a preset uninstallation rate threshold, return uninstallation success information and save uninstallation log information; in response to the uninstallation success rate being less than a preset uninstallation rate threshold, return uninstallation failure information and delete the target file.

[0156] In some embodiments, the loading unit 705 is further configured to: clear the database table and determine the data format corresponding to the database table; convert each character in the second conversion file into a data format, and then determine the conversion rate; in response to the conversion rate reaching the conversion rate threshold, return a loading success message; in response to the conversion rate not reaching the conversion rate threshold, execute a rollback process and return a loading failure message.

[0157] In some embodiments, the unloading unit 702 is further configured to: partition the database table to generate sub-database tables; and unload the sub-database tables to target files in parallel.

[0158] In some embodiments, the uninstallation unit 702 is further configured to: obtain uninstallation configuration information, and then obtain the uninstallation identifier corresponding to each character in the database table from the uninstallation configuration information; and uninstall the characters with non-empty uninstallation identifiers in the database table to the target file.

[0159] In some embodiments, the updating unit 704 is further configured to: update the database table after loading the second conversion file.

[0160] It should be noted that the data processing method and the data processing device in the present application have a corresponding relationship in terms of specific implementation content, so the repeated content will not be described again.

[0161] Figure 8 An exemplary system architecture 800 to which the data processing method or data processing device according to the embodiment of the present application can be applied is shown.

[0162] like Figure 8 As shown, system architecture 800 may include terminal devices 801, 802, 803, a network 804, and a server 805. Network 804 is used to provide a medium for communication links between terminal devices 801, 802, 803 and server 805. Network 804 may include various connection types, such as wired, wireless communication links, or optical fiber cables, etc.

[0163] Users can use terminal devices 801, 802, and 803 to interact with server 805 through network 804 to receive or send messages, etc. Various communication client applications can be installed on terminal devices 801, 802, and 803, such as shopping applications, web browser applications, search applications, instant messaging tools, email clients, social platform software, etc. (only examples).

[0164] The terminal devices 801 , 802 , and 803 may be various electronic devices having a data processing screen and supporting web browsing, including but not limited to smart phones, tablet computers, laptop computers, desktop computers, and the like.

[0165] The server 805 may be a server that provides various services, such as a background management server that provides support for data processing requests submitted by users using terminal devices 801, 802, and 803 (only as an example). The background management server may receive a data processing request and obtain a corresponding job identifier; obtain a corresponding database table according to the job identifier, unload the database table to a target file, and then perform code conversion on the target file to generate a first conversion file; obtain a conversion status identifier corresponding to each character in the first conversion file; determine the conversion failure character according to the conversion status identifier, and then update the first conversion file based on the conversion failure character; perform code conversion on the updated first conversion file to generate a second conversion file, and then load the second conversion file into the database table. By unloading and loading the database table, after two code conversions, the code conversion judgment and segmentation operations using the SQL language and the Postgre built-in function are avoided to process abnormal characters that have been entered into the data table. Abnormal characters are deleted or replaced within the scope that the database server or ETL server can handle, and do not cause abnormal functions of the program or other systems.

[0166] It should be noted that the data processing method provided in the embodiment of the present application is generally executed by the server 805 , and accordingly, the data processing device is generally set in the server 805 .

[0167] It should be understood that Figure 8 The number of terminal devices, networks and servers in the embodiment is only for illustration. Any number of terminal devices, networks and servers may be provided according to implementation requirements.

[0168] Reference below Fig. 9 , which shows a schematic diagram of the structure of a computer system 900 of a terminal device suitable for implementing an embodiment of the present application. Fig. 9 The terminal device shown is merely an example and should not bring any limitation to the functions and scope of use of the embodiments of the present application.

[0169] like Fig. 9 As shown, the computer system 900 includes a central processing unit (CPU) 901, which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 902 or a program loaded from a storage part 908 into a random access memory (RAM) 903. In the RAM 903, various programs and data required for the operation of the computer system 900 are also stored. The CPU 901, the ROM 902, and the RAM 903 are connected to each other via a bus 904. An input / output (I / O) interface 905 is also connected to the bus 904.

[0170] The following components are connected to the I / O interface 905: an input section 906 including a keyboard, a mouse, etc.; an output section 907 including a cathode ray tube (CRT), a liquid crystal display (LCD), etc., and a speaker, etc.; a storage section 908 including a hard disk, etc.; and a communication section 909 including a network interface card such as a LAN card, a modem, etc. The communication section 909 performs communication processing via a network such as the Internet. A drive 910 is also connected to the I / O interface 905 as needed. A removable medium 911, such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, etc., is installed on the drive 910 as needed, so that a computer program read therefrom is installed into the storage section 908 as needed.

[0171] In particular, according to the embodiments disclosed in the present application, the process described above with reference to the flowchart can be implemented as a computer software program. For example, the embodiments disclosed in the present application include a computer program product, which includes a computer program carried on a computer-readable medium, and the computer program includes a program code for executing the method shown in the flowchart. In such an embodiment, the computer program can be downloaded and installed from the network through the communication part 909, and / or installed from the removable medium 911. When the computer program is executed by the central processing unit (CPU) 901, the above-mentioned functions defined in the system of the present application are executed.

[0172] It should be noted that the computer-readable medium shown in the present application may be a computer-readable signal medium or a computer-readable storage medium or any combination of the above two. Computer-readable storage media may include, for example, but are not limited to electrical, magnetic, optical, electromagnetic, infrared, or semiconductor systems, devices or devices, or any combination of the above. More specific examples of computer-readable storage media may include, but are not limited to: an electrical connection with one or more wires, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the above. In the present application, a computer-readable storage medium may be any tangible medium containing or storing a program that can be used by or in combination with an instruction execution system, device or device. In the present application, a computer-readable signal medium may include a data signal propagated in a baseband or as part of a carrier wave, in which a computer-readable program code is carried. This propagated data signal may take a variety of forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination of the above. The computer-readable signal medium may also be any computer-readable medium other than a computer-readable storage medium, which may send, propagate or transmit a program for use by or in conjunction with an instruction execution system, apparatus or device. The program code contained on the computer-readable medium may be transmitted using any appropriate medium, including but not limited to: wireless, wire, optical cable, RF, etc., or any suitable combination of the above.

[0173] The flow chart and block diagram in the accompanying drawings illustrate the possible architecture, function and operation of the system, method and computer program product according to various embodiments of the present application. In this regard, each box in the flow chart or block diagram can represent a module, a program segment or a part of a code, and the above-mentioned module, program segment or a part of a code contains one or more executable instructions for realizing the specified logical function. It should also be noted that in some alternative implementations, the functions marked in the box can also occur in a different order from the order marked in the accompanying drawings. For example, two boxes represented in succession can actually be executed substantially in parallel, and they can sometimes be executed in the opposite order, depending on the functions involved. It should also be noted that each box in the block diagram or flow chart, and the combination of the boxes in the block diagram or flow chart can be implemented with a dedicated hardware-based system that performs a specified function or operation, or can be implemented with a combination of dedicated hardware and computer instructions.

[0174] The units involved in the embodiments described in the present application may be implemented by software or hardware. The units described may also be set in a processor, for example, it may be described as: a processor includes a receiving unit, an unloading unit, an acquisition unit, an updating unit and a loading unit. The names of these units do not constitute limitations on the units themselves in some cases.

[0175] As another aspect, the present application also provides a computer-readable medium, which may be included in the device described in the above embodiment; or it may exist independently and not be assembled into the device. The above computer-readable medium carries one or more programs, and when the above one or more programs are executed by a device, the device receives a data processing request and obtains a corresponding job identifier; obtains a corresponding database table according to the job identifier, unloads the database table to a target file, and then performs encoding conversion on the target file to generate a first conversion file; obtains a conversion status identifier corresponding to each character in the first conversion file; determines a conversion failure character according to the conversion status identifier, and then updates the first conversion file based on the conversion failure character; performs encoding conversion on the updated first conversion file to generate a second conversion file, and then loads the second conversion file into the database table.

[0176] The computer program product of the present application includes a computer program, and the computer program implements the data processing method in the embodiment of the present application when executed by a processor.

[0177] According to the technical solution of the embodiment of the present application, by unloading and loading the database table, after two encoding conversions, the encoding conversion judgment and segmentation operations performed by using SQL language and Postgre built-in functions are avoided to process abnormal characters that have been entered into the data table. Abnormal characters are deleted or replaced within the scope that the database server or ETL server can handle, and do not cause abnormal functions of programs or other systems.

[0178] The above specific implementations do not constitute a limitation on the protection scope of this application. It should be understood by those skilled in the art that various modifications, combinations, sub-combinations and substitutions may occur depending on design requirements and other factors. Any modifications, equivalent substitutions and improvements made within the spirit and principles of this application should be included in the protection scope of this application.

Claims

1. A data processing method, characterized in that: include: Receive data processing requests and obtain corresponding job identifiers; Acquire a corresponding database table according to the job identifier, unload the database table to a target file, and then perform code conversion on the target file to generate a first conversion file; Obtaining a conversion status identifier corresponding to each character in the first conversion file; Determining conversion-failed characters according to the conversion status identifier, and then updating the first conversion file based on the conversion-failed characters; The updated first conversion file is converted into code to generate a second conversion file, and then the second conversion file is loaded into the database table.

2. The method according to claim 1, characterized in that The obtaining of the conversion status identifier corresponding to each character in the first conversion file includes: Dividing the first conversion file according to a preset number of lines to generate first conversion sub-files; Each sub-thread is called to traverse each first conversion sub-file in parallel by line, and a conversion state identifier corresponding to a character corresponding to each line in each first conversion sub-file is obtained.

3. The method according to claim 1, characterized in that The updating of the first conversion file based on the conversion failed character comprises: Determine the row corresponding to each conversion-failed character, and then determine the total number of characters and the number of conversion-failed characters in each row; Based on the total number of characters and the number of characters that failed to be converted, determine the proportion of characters that failed to be converted corresponding to each of the rows; The rows corresponding to the conversion failure character ratio greater than a preset threshold are deleted, thereby updating the first conversion file.

4. The method according to claim 3, characterized in that The updating of the first conversion file based on the conversion failed character further includes: The conversion failed characters in the row corresponding to the conversion failed character ratio less than a preset threshold are deleted, thereby updating the first conversion file.

5. The method according to claim 1, characterized in that The step of unloading the database table to a target file comprises: Create target files based on preset formats; Convert each character in the database table into the preset format to copy it to the target file, and then determine the uninstall success ratio; In response to the uninstall success ratio being greater than a preset uninstall ratio threshold, returning uninstall success information and saving uninstall log information; In response to the uninstallation success ratio being less than a preset uninstallation ratio threshold, uninstallation failure information is returned, and the target file is deleted.

6. The method according to claim 1, characterized in that The step of loading the second conversion file into the database table comprises: Clear the database table and determine the data format corresponding to the database table; Convert each character in the second conversion file into the data format, and then determine the conversion rate; In response to the conversion rate reaching the conversion rate threshold, returning loading success information; In response to the conversion rate not reaching the conversion rate threshold, a rollback process is executed and loading failure information is returned.

7. The method according to claim 1, characterized in that The step of unloading the database table to a target file comprises: Partitioning the database table to generate partitioned database tables; The sub-database tables are unloaded to the target files respectively in parallel.

8. The method according to claim 1, characterized in that The step of unloading the database table to a target file comprises: Obtaining uninstallation configuration information, and then obtaining an uninstallation identifier corresponding to each character in the database table from the uninstallation configuration information; Unload the characters whose unloading identifiers in the database table are non-empty to the target file.

9. The method according to claim 1, characterized in that:After loading the second conversion file into the database table, the method further includes: The database table after loading the second conversion file is updated.

10. A data processing device, characterized in that: include: A receiving unit is configured to receive a data processing request and obtain a corresponding job identifier; an unloading unit, configured to obtain a corresponding database table according to the job identifier, unload the database table to a target file, and then perform code conversion on the target file to generate a first conversion file; an acquisition unit, configured to acquire a conversion status identifier corresponding to each character in the first conversion file; an updating unit, configured to determine a conversion-failed character according to the conversion state identifier, and then update the first conversion file based on the conversion-failed character; The loading unit is configured to perform code conversion on the updated first conversion file to generate a second conversion file, and then load the second conversion file into the database table.

11. The device according to claim 10, characterized in that The acquisition unit is further configured to: Dividing the first conversion file according to a preset number of lines to generate first conversion sub-files; Each sub-thread is called to traverse each first conversion sub-file in parallel by line, and a conversion state identifier corresponding to a character corresponding to each line in each first conversion sub-file is obtained.

12. A data processing electronic device, characterized in that: include: one or more processors; a storage device for storing one or more programs, When the one or more programs are executed by the one or more processors, the one or more processors implement the method according to any one of claims 1 to 9.

13. A computer readable medium having a computer program stored thereon, characterized in that: When the program is executed by a processor, the method according to any one of claims 1 to 9 is implemented.

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

Images