Data cleaning processing method and device

Abstract

The invention discloses a data cleaning processing method and device, and relates to the technical field of data processing. A specific embodiment of the method comprises the steps of receiving a data cleaning request, obtaining a data cleaning configuration file based on the data cleaning request, and determining a target database, a target data table and an export directory through the data cleaning configuration file; exporting a primary key from the target data table, exporting historical data based on the primary key, and backing up the historical data to an export directory; creating a new data table to insert the received real-time data for the target data table into the new data table; and obtaining the primary key and the historical data corresponding to the primary key from the export directory, executing a data compression operation to insert a new data table, and executing a deletion operation on the target data table. According to the embodiment, data storage can be optimized under the condition that service operation is not affected by backing up the historical data, creating the new data table and executing data compression and type conversion.

Description

Technical Field

[0001] The present invention relates to the field of data processing technology, and in particular to a data cleaning processing method and device. Background Art

[0002] In database management, for example, in an Oracle environment, as business grows, data in database tables accumulates year by year. Although deletion operations may be performed during this period, the space occupied by the deleted data will not be reclaimed immediately due to the high watermark problem. In addition, due to unreasonable initial data type design, data expansion may also occur, thereby increasing the storage space requirements and redundancy of the table. In order to solve these problems, the database table needs to be cleaned up and the space reclaimed, especially when dealing with large unpartitioned tables, and traditional data cleanup and backup methods are inefficient. Summary of the invention

[0003] In view of this, an embodiment of the present invention provides a data cleaning processing method and device, which can at least solve the problem of low efficiency of data cleaning and backup in the prior art.

[0004] To achieve the above object, according to one aspect of an embodiment of the present invention, a data cleaning method is provided, comprising:

[0005] Receive a data cleaning request, obtain a data cleaning configuration file based on the data cleaning request, and determine a target database, a target data table, and an export directory through the data cleaning configuration file;

[0006] Export the primary key from the target data table, and export the historical data based on the primary key to back up to the export directory; and

[0007] Creating a new data table to insert the received real-time data for the target data table into the new data table; wherein the structure of the new data table is the same as that of the target data table;

[0008] Obtain the primary key and the historical data corresponding to the primary key from the export directory, perform data compression operations to insert into the new data table, and perform deletion operations on the target data table;

[0009] For one or more of the primary key, real-time data, and historical data, the data type is analyzed to perform a data type conversion operation.

[0010] Optionally, the configuration process of the data cleaning configuration file includes:

[0011] In response to a selection operation on a target database, obtaining pre-configured connection string information of the target database; wherein the connection string information is used to connect to the target database;

[0012] Receive the input target data table name; or display the data table list of the target database, receive the selection operation of one or more target data tables, and obtain the table name of each target data table;

[0013] Receives the configured export directory;

[0014] Generate a preset data cleanup configuration file based on the connection string information of the target database, the table name of the target data table, and the export directory.

[0015] Optionally, the data cleaning configuration file further includes field names that need to be compressed, and the historical data includes field names and field values;

[0016] The configuration process of the data cleaning configuration file also includes: obtaining field names in the target data table to receive a selection operation on some or all of the field names, and obtaining the field names that need to be compressed;

[0017] The step of obtaining the primary key and the historical data corresponding to the primary key from the export directory includes:

[0018] Obtain the primary key and all historical data corresponding to the primary key from the export directory, and filter the target historical data that matches the field name that needs to be compressed from all historical data.

[0019] Optionally, the method further comprises one or more of the following:

[0020] Determine the data volume of each target data table to filter out target data tables whose data volume is greater than or equal to a preset data volume threshold;

[0021] Priority is determined based on the importance and sensitivity of the data in each target data table to filter out target data tables whose priorities meet the preset priority threshold requirements.

[0022] Optionally, the exporting of the primary key from the target data table and the exporting of the historical data based on the primary key includes: renaming the target data table to obtain a renamed data table, exporting the primary key from the renamed data table, and exporting the historical data based on the primary key;

[0023] After creating the new data table, the method further includes: using the original table name of the target data table as the table name of the new data table;

[0024] The performing a delete operation on the target data table includes: performing a delete operation on the renamed data table.

[0025] Optionally, the exporting of the primary key from the renamed data table and the exporting of historical data based on the primary key include:

[0026] Export the primary key in the renamed data table to text format to obtain a primary key file, and name the primary key file based on the table name of the renamed data table; and

[0027] All historical data are exported from the renamed data table based on the primary key to obtain a historical data file, and the historical data file is named based on the table name of the renamed data table.

[0028] Optionally, analyzing the data type to perform a data type conversion operation includes:

[0029] For one or more of the primary key, real-time data, and historical data, determining a pre-configured preset data type conversion table;

[0030] In response to the data type existing in a preset data type conversion table, a data type conversion operation is performed on one or more of primary keys, real-time data, and historical data based on a target data type corresponding to the data type in the preset data type conversion table.

[0031] To achieve the above object, according to another aspect of an embodiment of the present invention, a data cleaning processing device is provided, comprising:

[0032] A receiving module, used to receive a data cleaning request, obtain a data cleaning configuration file based on the data cleaning request, and determine a target database, a target data table, and an export directory through the data cleaning configuration file;

[0033] The export backup module is used to export the primary key from the target data table, export the historical data based on the primary key, and back it up to the export directory;

[0034] A new module is used to create a new data table to insert the received real-time data for the target data table into the new data table; wherein the structure of the new data table is the same as that of the target data table;

[0035] A compression module, used to obtain the primary key and the historical data corresponding to the primary key from the export directory, perform data compression operations to insert into a new data table, and perform a delete operation on a target data table;

[0036] For one or more of the primary key, real-time data, and historical data, the data type is analyzed to perform a data type conversion operation.

[0037] Optionally, the configuration process of the data cleaning configuration file includes:

[0038] In response to a selection operation on a target database, obtaining pre-configured connection string information of the target database; wherein the connection string information is used to connect to the target database;

[0039] Receive the input target data table name; or display the data table list of the target database, receive the selection operation of one or more target data tables, and obtain the table name of each target data table;

[0040] Receives the configured export directory;

[0041] Generate a preset data cleanup configuration file based on the connection string information of the target database, the table name of the target data table, and the export directory.

[0042] Optionally, the data cleaning configuration file further includes field names that need to be compressed, and the historical data includes field names and field values;

[0043] The configuration process of the data cleaning configuration file also includes: obtaining field names in the target data table to receive a selection operation on some or all of the field names, and obtaining the field names that need to be compressed;

[0044] The compression module is used to:

[0045] Obtain the primary key and all historical data corresponding to the primary key from the export directory, and filter the target historical data that matches the field name that needs to be compressed from all historical data.

[0046] Optionally, the device further comprises one or more of the following:

[0047] Determine the data volume of each target data table to filter out target data tables whose data volume is greater than or equal to a preset data volume threshold;

[0048] Priority is determined based on the importance and sensitivity of the data in each target data table to filter out target data tables whose priorities meet the preset priority threshold requirements.

[0049] Optionally, the export backup module is used to: rename the target data table to obtain a renamed data table, export a primary key from the renamed data table, and export historical data based on the primary key;

[0050] The new creation module is further used to: use the original table name of the target data table as the table name of the new data table;

[0051] The compression module is used to: perform a delete operation on the renamed data table.

[0052] Optionally, the export backup module is used to:

[0053] Export the primary key in the renamed data table to text format to obtain a primary key file, and name the primary key file based on the table name of the renamed data table; and

[0054] All historical data are exported from the renamed data table based on the primary key to obtain a historical data file, and the historical data file is named based on the table name of the renamed data table.

[0055] Optionally, the compression module is used to:

[0056] For one or more of the primary key, real-time data, and historical data, determining a pre-configured preset data type conversion table;

[0057] In response to the data type existing in a preset data type conversion table, a data type conversion operation is performed on one or more of primary keys, real-time data, and historical data based on a target data type corresponding to the data type in the preset data type conversion table.

[0058] To achieve the above objective, according to another aspect of an embodiment of the present invention, a data cleaning and processing electronic device is provided.

[0059] The electronic device of an embodiment of the present invention includes: 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 any of the data cleaning processing methods described above.

[0060] To achieve the above-mentioned purpose, according to another aspect of an embodiment of the present invention, a computer-readable medium is provided, on which a computer program is stored, and when the program is executed by a processor, any of the above-mentioned data cleaning processing methods is implemented.

[0061] To achieve the above object, according to another aspect of an embodiment of the present invention, a computer program product is provided. A computer program product of an embodiment of the present invention includes a computer program, and when the program is executed by a processor, the data cleaning method provided by the embodiment of the present invention is implemented.

[0062] According to the solution provided by the present invention, one embodiment of the above invention has the following advantages or beneficial effects: an efficient idea of ​​database table cleanup and space recovery is proposed, by backing up historical data, creating new data tables, performing data compression and performing type conversion, thereby effectively releasing the storage space of the database table without affecting business operations, while ensuring the security and integrity of the data, and improving the efficiency of cleanup and backup. In addition, the use of primary key export can greatly reduce the time of data retrieval and export, and improve the performance and reliability of the overall system.

[0063] 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

[0064] The accompanying drawings are used to better understand the present invention and do not constitute an improper limitation of the present invention.

[0065] Figure 1 is a main flow chart of a data cleaning method according to an embodiment of the present invention;

[0066] Figure 2 is a flow chart of an optional data cleaning method according to an embodiment of the present invention;

[0067] Figure 3 is a flowchart of another optional data cleaning method according to an embodiment of the present invention;

[0068] Figure 4 is a flowchart of a specific data cleaning method according to an embodiment of the present invention;

[0069] Figure 5 is a schematic diagram of main modules of a data cleaning processing device according to an embodiment of the present invention;

[0070] Figure 6 is an exemplary system architecture diagram to which embodiments of the present invention may be applied;

[0071] Figure 7 It is a schematic diagram of the structure of a computer system of a mobile device or a server suitable for implementing an embodiment of the present invention. DETAILED DESCRIPTION

[0072] The following is a description of exemplary embodiments of the present invention in conjunction with the accompanying drawings, including various details of the embodiments of the present invention 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 invention. Similarly, for clarity and conciseness, the description of well-known functions and structures is omitted in the following description.

[0073] It should be noted that in the embodiments of the present application, certain software, components, models and other existing solutions in the industry may be mentioned, and they should be regarded as exemplary. Their purpose is only to illustrate the feasibility of implementing the technical solution of the present application, but it does not mean that the applicant has or will necessarily use the solution.

[0074] In the absence of conflict, the embodiments and features of the embodiments of the present invention can be combined with each other. The acquisition, transmission, storage, use, and processing of data in the technical solution of the present invention are in compliance with the relevant provisions of national laws and regulations, and are used for legal and reasonable purposes, and are not shared, disclosed, or sold outside of these legal uses, and are subject to supervision and management by regulatory authorities.

[0075] For user information, necessary measures should be taken to prevent illegal access to such personal information data, ensure that persons who have access to personal information data comply with relevant laws and regulations, and ensure the security of user personal information. Once such user personal information data is no longer needed, risks should be minimized by limiting or even prohibiting data collection and / or deleting data. When applicable, including in certain relevant applications, protect user privacy by de-identifying data, such as by removing specific identifiers (e.g., date of birth, etc.), controlling the amount or specificity of stored data (e.g., collecting location data at the city level rather than at the specific address level), controlling how data is stored, and / or other methods of de-identification.

[0076] As the business grows, the data in the database table increases over the years. On the one hand, even if data deletion operations such as Delete are performed during the period, a high water level problem will occur. Although the data is deleted, the used space will not be reclaimed immediately. Taking the Oracle database as an example, this is the nature of the Oracle database. After the data in the database is deleted, the space for the deleted data is not enough for new data reuse, which will generate fragmented free space and will not be automatically reclaimed. On the other hand, due to insufficient evaluation of the data type in the early stage, data expansion will also lead to an increase in the space occupied by table data and redundancy. This is caused by different data storage types. For example, for the same large object string, the temporary storage space of Oracle's BLOB type is larger than the storage space of the CLOB type.

[0077] In these cases, the database table needs to be cleaned up and the space needs to be reclaimed. Currently, a data pump is used to export large table data based on partitions. After the exported data is backed up, the original table data is truncated and deleted. At this time, the redundant temporary storage space is released, and then a new database table is created to import the exported data for import and recovery. However, this method is only applicable to partitioned tables and has a narrow scope of application. For non-partitioned tables, the export efficiency is very low. In addition, the data files exported using a data pump are usually in binary format, which makes it difficult to view the data directly. It needs to be imported into the database before it can be viewed.

[0078] Truncate operation usually refers to clearing or truncating all data in a table in the database. Truncate operation bypasses the standard DML method of deleting data, so it cannot be rolled back and is executed very quickly. Once executed, the data cannot be recovered, so you need to be particularly careful when using it. After the Truncate operation is executed, the table structure still exists, but all data rows in the table are removed, and the occupied disk space is immediately released.

[0079] In order to optimize the above process, a more efficient and universal method is needed to deal with the cleaning and space recovery problems of large data tables in the database, especially to improve efficiency when dealing with non-partitioned tables and simplify the data viewing process.

[0080] See also Figure 1 , which shows a main flow chart of a data cleaning method provided by an embodiment of the present invention, comprising the following steps:

[0081] S101: receiving a data cleaning request, obtaining a data cleaning configuration file based on the data cleaning request, and determining a target database, a target data table, and an export directory through the data cleaning configuration file;

[0082] S102: exporting the primary key from the target data table, and exporting the historical data based on the primary key to back up to the export directory;

[0083] S103: creating a new data table to insert the received real-time data for the target data table into the new data table; wherein the structure of the new data table is the same as that of the target data table;

[0084] S104: Obtain the primary key and the historical data corresponding to the primary key from the export directory, perform a data compression operation to insert into a new data table, and perform a delete operation on the target data table;

[0085] For one or more of the primary key, real-time data, and historical data, the data type is analyzed to perform a data type conversion operation.

[0086] In the above implementation, for step S101, this solution can be applied to the management end, such as a system for managing databases and data tables. The management end can receive requests from the client. For example, the developer sets 2024-10-02 20:00 to perform a data request operation, thereby receiving a data cleanup request sent by the client. Since different data cleanups target different databases and data tables, developers also need to set data cleanup configuration files, such as developer 1 for data table A in the Oracle database, and developer 2 for data table B in the MYSQL database. Different developers have different development requirements, and the data cleanup configuration files set accordingly are also different.

[0087] The same developer may set different data cleansing requests, so the identifier config_id of the data cleansing configuration file can be set in the data cleansing request, and the unique identifier of the developer, such as name, number, and scheduled execution time scheduled_time can also be set, such as 2024-10-02 20:00. In addition, in order to uniformly manage multiple databases, it is also possible to clean up multiple databases at the same time, such as setting the database type database_type, such as Oracle or MySQL, and the data table name table_name, such as TableA or TableB.

[0088] Based on one or more of the above information, a unique data cleaning configuration file can be determined. The data cleaning configuration file in this solution includes but is not limited to: the configuration file's unique identifier config_id, the target database's connection string information (information for connecting to the target database, such as the server address, port number, database name, user name, and password), the database table name table_name, and the export directory export_directory. For example, if the request includes the configuration file identifier config_id, the data cleaning configuration file can be quickly matched based on the identifier.

[0089] For steps S102 and S103, for the data table specified in the configuration file, assume it is table A, based on the renaming rule, table A is renamed, such as A_TMP, where _TMP is only an example and is not limited to this. It is only necessary to rename the table to be processed. The purpose is to mark the current data table as a historical data table, so as to isolate the historical data and reduce the performance impact when inserting new data.

[0090] At the current moment, all data in table A_TMP are historical data, which are static and will not change. This solution defines historical data as follows: this type of business data will only be inserted, not updated, and once inserted into the database table, it will not change and is static data.

[0091] Recreate a new data table and name it Table A. The structure of the new Table A is the same as the original table. Then insert the new business data into Table A. This is because data storage is based on table names, and the original Table A has been renamed Table A_TMP. New data cannot be inserted into Table A_TMP, but can only be inserted into the new Table A. This allows a clear distinction between historical data and new data, which facilitates subsequent data management and maintenance.

[0092] The characteristics of the primary key of the database table are uniqueness, non-emptiness and automatic indexing. Using the primary key to query is efficient. Therefore, the primary key of A_TMP is exported as text and stored in the export directory, which is an accessible location on the server. Then the data of A_TMP is exported according to the primary key, exported as text, and stored in an accessible location on the server for backup in preparation for subsequent data recovery and query. The preferred export text format can support subsequent backup and import, and can also facilitate and intuitively query data.

[0093] The export directory can be set as the first export directory and the second export directory, that is, different directories of the server to store the primary key and historical data respectively, or only one can be set. This is because the exported data files will be named according to certain rules based on the table name. For example, if table A is renamed to A_TMP, then the primary key file is A_TMP_primary key and the historical data file is A_TMP_historical data. If there are two export directories, the A_TMP_primary key file is stored in export directory 1, and the A_TMP_historical data file is stored in export directory 2. This is mainly used for data backup to prevent the original data from being lost and unable to be recovered when problems occur in subsequent data cleaning.

[0094] For step S104, after backing up the data of table A_TMP, the primary key is obtained from the export directory, for example, the primary key file A_TMP_Primary Key of A_TMP is read from export directory 1, and then the primary key data of A_TMP is read from the primary key file A_TMP_Primary Key. And with the primary key as the index, the historical data text A_TMP_History Data corresponding to A_TMP is determined from export directory 2, and all historical data corresponding to the primary key is read therefrom.

[0095] The data cleaning configuration file can also include "field names that need to be processed and compressed", but all historical data is exported from the export directory, so data filtering is also required here. For example, there are fields 1, 2, 3, and 4, but only 1, 2, and 3 are selected as the field names that need to be compressed. "Field names that need to be processed and compressed" can also be flexibly configured according to actual needs.

[0096] In addition to the above operations, this application can also obtain the primary key data only from the export directory. As for the historical data, it can still be obtained from the table A_TMP, that is, query the historical data in A_TMP according to the primary key. This is because exporting does not delete, but only places a copy of the data on the export directory server, and there is still data in A_TMP at this time.

[0097] Compress the primary key data and historical data, and insert the compressed data into the new table A based on the primary key. Perform the Truncate delete operation on table A_TMP to free up database storage space.

[0098] The method provided in the above embodiment can effectively release the storage space of the database table, improve system performance, and ensure the security and integrity of the data through data cleaning and compression. Specifically, by backing up historical data, creating new data tables, and performing data compression and type conversion, data storage can be optimized without affecting business operations, while retaining key data for subsequent use. And the use of primary key export can significantly improve the efficiency of data export, because the primary key, as a unique identifier, can quickly locate and retrieve data, thereby speeding up the data export process, ensuring the consistency and integrity of the data, and significantly reducing the time for data retrieval and export, improving the performance and reliability of the overall system.

[0099] See also Figure 2 , shows a schematic flow chart of an optional data cleaning method according to an embodiment of the present invention, comprising the following steps:

[0100] S201: In response to a selection operation on a target database, obtaining pre-configured connection string information of the target database; wherein the connection string information is used to connect to the target database;

[0101] S202: receiving an input target data table name; or displaying a data table list of a target database, receiving a selection operation on one or more target data tables, and obtaining the table name of each target data table;

[0102] S203: receiving the configured export directory;

[0103] S204: Generate a preset data cleaning configuration file based on the connection string information of the target database, the table name of the target data table, and the export directory.

[0104] In the above implementation, the solution can realize flexible definition of the target database and target data table in the configuration file. Specifically, in response to the selection operation of the target database, the connection string information of the pre-configured target database is obtained; the input target data table name is received; or the list of data tables under the target database is displayed to receive the selection operation of one or more target data tables and obtain the table name of each target data table; the configured export directory is received to generate a preset data cleaning configuration file based on the connection string information of the target database, the table name of the target data table, and the export directory. Among them, the configuration of the export directory and the selection of the database can be performed simultaneously or successively, and the order of operations is not limited here.

[0105] An operation interface can be set here, and the developer selects the target database in this interface. For example, Oracle and MySQL are displayed, and the developer selects Oracle as the target database. Some developers are familiar with the data tables in the target database. For example, if only data table A needs to be cleaned up at present, there is no need to select an operation, and the table name of data table A can be directly entered. However, if there are many cleanup objects, or if you are not familiar with them, or if there are too many inputs, or if you do not want to use the input method, you can use the selection method to select one or more data tables from Oracle's current data table list as the target data table. The export directory can be regarded as a backup directory for storing information of the target database table. It should be noted that the above-mentioned data cleaning configuration file can also be set at the management end. The developer is only a user and only needs to know the identifier of the data cleaning configuration file.

[0106] Furthermore, this solution is mainly aimed at large data tables with primary keys. Therefore, after entering the table name or selecting the data table, it is also necessary to determine whether the data volume of these data tables meets the requirements. For example, if the threshold is set to the tens of millions level, and the data volume in some data tables is only in the millions level, then these data tables do not meet the cleaning requirements and do not need to be cleaned. In addition to the data volume, you can also filter according to the creation time or last modification time of the data, such as regularly checking the data volume of all data tables in the database, and dynamically adjusting the preset data volume threshold according to the normal distribution, so that most data tables in the database do not waste resources.

[0107] Or you can set the importance and sensitivity of the data to determine the priority, and clean up the low-priority data tables first, or clean up the high-priority data tables first according to other business needs, preferably clean up the low-priority data tables. In this way, you can ensure the security of important data and effectively reduce storage space. For example:

[0108] 1) User information table (users): Importance - high, sensitivity - high, fields - id, name, email, password_hash. Therefore, it has the highest priority because user information is very important and sensitive, including the user's personal identity information and password hash value. Strictly protect, without any compression or simplification operations.

[0109] 2) Orders table: Importance - Medium, Sensitivity - Medium, Fields - order_id, user_id, total_amount, order_date. Therefore, it has medium priority because order information contains financial data but is not as sensitive as user information. It can be compressed moderately, for example, by reducing the precision of total_amount or truncating some fields.

[0110] 3) Log information table (logs): importance - low, sensitivity - low, fields - log_id, timestamp, message, level. Therefore, it has the lowest priority because log information is mainly used for debugging and monitoring, and has low importance and sensitivity. It can be compressed in large quantities, such as truncating the message field to reduce storage space.

[0111] 4) Product information table (products): Importance - Medium, Sensitivity - Low, Fields - product_id, name, description, price. Therefore, the priority is medium because product information is relatively important, but the sensitivity is low. It can be compressed moderately, for example, the description field can be shortened to reduce storage space.

[0112] Alternatively, the administrator or developer can write a script to automatically perform data cleanup tasks and record logs. When the data volume reaches a threshold or the priority reaches a threshold, an early warning notification is sent to the administrator or developer, and the cleanup operation is performed after the review is passed.

[0113] The method provided in the above embodiment can efficiently generate a preset data cleaning configuration file by automatically obtaining the connection information of the target database, selecting the target data table and configuring the export directory, simplifying the user operation process, ensuring the accuracy and consistency of the data cleaning process, thereby improving the efficiency and reliability of the overall data management. It also has a wide range of applications. After inputting and selecting the target data table, certain screening operations can be performed to ensure optimal data cleaning. For example, this solution can be used for large data tables with primary keys.

[0114] See also Figure 3 , shows a schematic flow chart of another optional data cleaning method according to an embodiment of the present invention, comprising the following steps:

[0115] S301: For one or more of primary key, real-time data and historical data, determine a pre-configured preset data type conversion table;

[0116] S302: In response to the data type existing in a preset data type conversion table, based on a target data type corresponding to the data type in the preset data type conversion table, perform a data type conversion operation on one or more of a primary key, real-time data, and historical data.

[0117] In the above implementation, data compression is performed on the primary key data and historical data, and the compressed data is inserted into the new table A based on the primary key. The purpose of data compression is to free up storage space, so the compression method can be various:

[0118] 1. Primary key data compression: 1) Data type optimization: If the integer primary key in the original data has a small range (for example, within the int range), you can consider using a smaller data type such as short or byte to store it, thereby reducing storage space. 2) Coding method improvement: For string type primary keys, dictionary encoding or hash encoding can be used to map frequently occurring strings to shorter encoding forms, thereby saving storage space.

[0119] 2. Historical data compression: 1) Time series data compression: Taking advantage of the characteristics of time series data (usually periodic and trending), differential encoding (storing the difference between the current value and the previous value) can be used to reduce the required storage space. 2) Deduplication (de-duplication): For the repeated parts of historical data, de-duplication technology can be used to store only one copy of the original data and point to the data through pointers or references, thereby avoiding repeated storage of the same information.

[0120] The above two methods can be used alone or in combination according to different specific application scenarios to achieve the best compression effect, such as only considering compressing historical data, not compressing primary key data, or choosing to compress both. It should be noted that historical data includes field names and field values, and data types and fields are not the same concept. Data types refer to the nature and form of data, such as integers, floating-point numbers, strings, etc. A field is a specific item in a database or data structure, usually representing an attribute or feature of a data. For example, in a user information table, "name", "age", and "email" are all fields, and their data types may be strings, integers, and strings, respectively. Therefore, fields are the specific manifestation of data, and data types are the classification of these data.

[0121] It should be noted that data compression and data type conversion can be different. For example, data compression selects encoding methods and deduplication operations, while data type conversion is independent of data compression. Data type conversion can be one of the primary key, field value, primary key, and historical data, but the conversion methods are the same. Taking historical data as an example, obtain field data, analyze the data type, and determine whether it is a BLOB type. If so, it means that data type compression is required; determine the pre-set conversion type corresponding to the BLOB type, such as the CLOB type, and compress the BLOB type data into a CLOB type that occupies less space. Through these conversion rules, storage space can be effectively reduced and data management efficiency can be improved.

[0122] Therefore, a preset data type conversion table 1 can be set for historical data, which stores the mapping relationship between BLOB type-CLOB type and char(600)-char(300), that is, converting BLOB type to CLOB type and converting char(600) type to char(300) type. Assume that there are BLOB type fields image_data and char(600) type fields description in the historical data. During the compression process, image_data is converted to CLOB type and the description field is truncated to limit its length to char(300). The same operation is applied to real-time data, so the real-time data inserted into the new table A also needs to be converted through data type conversion.

[0123] For the primary key, set the preset data type conversion table 2, which stores bigint type - int type, varchar(255) type - varchar(100) type, that is, convert bigint type to int type, and convert varchar(255) type to varchar(100) type. The bigint type usually occupies 8 bytes (64 bits), and the int type usually occupies 4 bytes (32 bits). Therefore, the int type occupies 4 bytes less space than the bigint type. The actual storage space of the varchar type depends on the number of characters actually stored and the additional length information. If the number of characters actually stored does not exceed 100, the varchar(100) type occupies less space than the varchar(255) type. Assume that the original primary key has a bigint type field id and a varchar(255) type field code. During the compression process, the id field is converted to the int type, and the code field is truncated to varchar(100).

[0124] The method provided in the above embodiment automatically performs data type conversion on one or more of primary keys, real-time data and historical data through a pre-configured data type conversion table, and supports compression of data of different data types, thereby optimizing storage space, improving data processing efficiency, and ensuring data consistency and accuracy.

[0125] See also Figure 4 , shows a schematic flow chart of a specific data cleaning method according to an embodiment of the present invention, comprising the following steps:

[0126] 1. Receive data cleansing requests to confirm whether the data cleansing configuration files, such as the target database connection string information, database table name, field name to be processed and compressed, and export directory (i.e., accessible location on the server), are configured correctly.

[0127] 2. Rename the table A to be processed to A_TMP, and use the data in A_TMP as historical business data; create a new table A, and then insert the new business data into table A. The data in A_TMP is in a static state and will not change.

[0128] 3. Export the primary key of A_TMP as text and store it in the export directory.

[0129] 4. Export the historical data of A_TMP according to the primary key, export it as text and store it in the export directory for backup in preparation for subsequent data recovery and query.

[0130] 5. Read the primary key data from the export directory, and query and compress the historical data in A_TMP based on the primary key.

[0131] 6. Insert the compressed data of A_TMP into table A.

[0132] 7. Perform the Truncate delete operation on table A_TMP to release database storage space.

[0133] See also Figure 5 , shows a schematic diagram of main modules of a data cleaning processing device 500 provided by an embodiment of the present invention, including:

[0134] The receiving module 501 is used to receive a data cleaning request, obtain a data cleaning configuration file based on the data cleaning request, and determine a target database, a target data table, and an export directory through the data cleaning configuration file;

[0135] The export backup module 502 is used to export the primary key from the target data table, and export the historical data based on the primary key to back up to the export directory;

[0136] A new creation module 503 is used to create a new data table to insert the received real-time data for the target data table into the new data table; wherein the structure of the new data table is the same as that of the target data table;

[0137] A compression module 504, used to obtain the primary key and the historical data corresponding to the primary key from the export directory, perform a data compression operation to insert a new data table, and perform a delete operation on the target data table;

[0138] For one or more of the primary key, real-time data, and historical data, the data type is analyzed to perform a data type conversion operation.

[0139] In the implementation device of the present invention, the configuration process of the data cleaning configuration file includes:

[0140] In response to a selection operation on a target database, obtaining pre-configured connection string information of the target database; wherein the connection string information is used to connect to the target database;

[0141] Receive the input target data table name; or display the data table list of the target database, receive the selection operation of one or more target data tables, and obtain the table name of each target data table;

[0142] Receives the configured export directory;

[0143] Generate a preset data cleanup configuration file based on the connection string information of the target database, the table name of the target data table, and the export directory.

[0144] In the implementation device of the present invention, the data cleaning configuration file also includes the field name that needs to be compressed, and the historical data includes the field name and the field value;

[0145] The configuration process of the data cleaning configuration file also includes: obtaining field names in the target data table to receive a selection operation on some or all of the field names, and obtaining the field names that need to be compressed;

[0146] The compression module 504 is used to:

[0147] Obtain the primary key and all historical data corresponding to the primary key from the export directory, and filter the target historical data that matches the field name that needs to be compressed from all historical data.

[0148] The implementation device of the present invention also includes one or more of the following:

[0149] Determine the data volume of each target data table to filter out target data tables whose data volume is greater than or equal to a preset data volume threshold;

[0150] Priority is determined based on the importance and sensitivity of the data in each target data table to filter out target data tables whose priorities meet the preset priority threshold requirements.

[0151] In the implementation device of the present invention, the export backup module 502 is used to: rename the target data table to obtain a renamed data table, derive the primary key from the renamed data table, and derive the historical data based on the primary key;

[0152] The new creation module 503 is further used to: use the original table name of the target data table as the table name of the new data table;

[0153] The compression module 504 is used to perform a deletion operation on the renamed data table.

[0154] In the implementation device of the present invention, the export backup module 502 is used to:

[0155] Export the primary key in the renamed data table to text format to obtain a primary key file, and name the primary key file based on the table name of the renamed data table; and

[0156] All historical data are exported from the renamed data table based on the primary key to obtain a historical data file, and the historical data file is named based on the table name of the renamed data table.

[0157] In the implementation device of the present invention, the compression module 504 is used to:

[0158] For one or more of the primary key, real-time data, and historical data, determining a pre-configured preset data type conversion table;

[0159] In response to the data type existing in a preset data type conversion table, a data type conversion operation is performed on one or more of primary keys, real-time data, and historical data based on a target data type corresponding to the data type in the preset data type conversion table.

[0160] In addition, the specific implementation content of the device described in the embodiment of the present invention has been described in detail in the method described above, so the repeated content will not be described again here.

[0161] Figure 6 An exemplary system architecture 600 to which embodiments of the present invention may be applied is shown, including terminal devices 601 , 602 , 603 , a network 604 and a server 605 (only an example).

[0162] Terminal devices 601, 602, 603 can be various electronic devices with display screens and supporting web browsing, and various communication client applications are installed. Users can use terminal devices 601, 602, 603 to interact with server 605 through network 604 to receive or send messages, etc.

[0163] The network 604 is used to provide a medium for communication links between the terminal devices 601, 602, 603 and the server 605. The network 604 may include various connection types, such as wired, wireless communication links or optical fiber cables.

[0164] The server 605 may be a server that provides various services, such as a backend management server that provides support for shopping websites browsed by users using the terminal devices 601, 602, and 603 (for example only). The backend management server may analyze and process the received data such as product information query requests, and feed back the processing results (such as target push information, product information - for example only) to the terminal device. It should be noted that the method provided in the embodiment of the present invention is generally executed by the server 605, and accordingly, the device is generally set in the server 605.

[0165] It should be understood that Figure 6 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.

[0166] Reference below Figure 7 , which shows a schematic diagram of the structure of a computer system 700 of a terminal device suitable for implementing an embodiment of the present invention. Figure 7 The terminal device shown is only an example and should not bring any limitation to the functions and scope of use of the embodiments of the present invention.

[0167] like Figure 7 As shown, the computer system 700 includes a central processing unit (CPU) 701, which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 702 or a program loaded from a storage part 708 into a random access memory (RAM) 703. In the RAM 703, various programs and data required for the operation of the system 700 are also stored. The CPU 701, the ROM 702, and the RAM 703 are connected to each other through a bus 704. An input / output (I / O) interface 705 is also connected to the bus 704.

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

[0169] In particular, according to the embodiments disclosed in the present invention, 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 invention include a computer program product, which includes a computer program carried on a computer-readable medium, and the computer program contains 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 709, and / or installed from the removable medium 711. When the computer program is executed by the central processing unit (CPU) 701, the above-mentioned functions defined in the system of the present invention are executed.

[0170] It should be noted that the computer-readable medium shown in the present invention may be a computer-readable signal medium or a computer-readable storage medium or any combination of the above two. The computer-readable storage medium may be, for example, but not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, device or device, 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 invention, 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 invention, a computer-readable signal medium may include a data signal propagated in a baseband or as part of a carrier wave, which carries a computer-readable program code. 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. Computer-readable signal media may also be any computer-readable medium other than computer-readable storage media, 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.

[0171] 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 invention. 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.

[0172] The modules involved in the embodiments of the present invention may be implemented by software or hardware. The modules described may also be set in a processor. For example, they may be described as: a processor includes a receiving module, an export backup module, a new module, and a compression module. The names of these modules do not, in some cases, constitute limitations on the modules themselves. For example, a compression module may also be described as a "compression conversion module."

[0173] As another aspect, the present invention further provides a computer-readable medium, which may be included in the device described in the above embodiment; or may exist independently without being 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 executes any of the above data cleaning processing methods.

[0174] The computer program product of the present invention comprises a computer program, and when the computer program is executed by a processor, the data cleaning processing method in the embodiment of the present invention is implemented.

[0175] The above specific implementations do not constitute a limitation on the protection scope of the present invention. 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 modification, equivalent substitution and improvement made within the spirit and principle of the present invention shall be included in the protection scope of the present invention.

Claims

1. A data cleaning method, characterized in that: include: Receive a data cleaning request, obtain a data cleaning configuration file based on the data cleaning request, and determine a target database, a target data table, and an export directory through the data cleaning configuration file; Export the primary key from the target data table, and export the historical data based on the primary key to back up to the export directory; as well as Creating a new data table to insert the received real-time data for the target data table into the new data table; wherein the structure of the new data table is the same as that of the target data table; Obtain the primary key and the historical data corresponding to the primary key from the export directory, perform data compression operations to insert into the new data table, and perform deletion operations on the target data table; For one or more of the primary key, real-time data, and historical data, the data type is analyzed to perform a data type conversion operation.

2. The method according to claim 1, characterized in that The configuration process of the data cleaning configuration file includes: In response to a selection operation on a target database, obtaining pre-configured connection string information of the target database; wherein the connection string information is used to connect to the target database; Receive the input target data table name; or display the data table list of the target database, receive the selection operation of one or more target data tables, and obtain the table name of each target data table; Receives the configured export directory; Generate a preset data cleanup configuration file based on the connection string information of the target database, the table name of the target data table, and the export directory.

3. The method according to claim 1 or 2, characterized in that: The data cleaning configuration file also includes the field name that needs to be compressed, and the historical data includes the field name and field value; The configuration process of the data cleaning configuration file also includes: obtaining field names in the target data table to receive a selection operation on some or all of the field names, and obtaining the field names that need to be compressed; The step of obtaining the primary key and the historical data corresponding to the primary key from the export directory includes: Obtain the primary key and all historical data corresponding to the primary key from the export directory, and filter the target historical data that matches the field name that needs to be compressed from all historical data.

4. The method according to claim 2, characterized in that: The method may further include one or more of the following: Determine the data volume of each target data table to filter out target data tables whose data volume is greater than or equal to a preset data volume threshold; Priority is determined based on the importance and sensitivity of the data in each target data table to filter out target data tables whose priorities meet the preset priority threshold requirements.

5. The method according to claim 1, characterized in that The exporting of the primary key from the target data table and the exporting of the historical data based on the primary key includes: renaming the target data table to obtain a renamed data table, exporting the primary key from the renamed data table, and exporting the historical data based on the primary key; After creating the new data table, the method further includes: using the original table name of the target data table as the table name of the new data table; The performing a delete operation on the target data table includes: performing a delete operation on the renamed data table.

6. The method according to claim 5, characterized in that The exporting of the primary key from the renamed data table and the exporting of historical data based on the primary key include: Export the primary key in the renamed data table to text format to obtain a primary key file, and name the primary key file based on the table name of the renamed data table; and All historical data are exported from the renamed data table based on the primary key to obtain a historical data file, and the historical data file is named based on the table name of the renamed data table.

7. The method according to claim 1, characterized in that The analyzing the data type to perform the data type conversion operation includes: For one or more of the primary key, real-time data, and historical data, determining a pre-configured preset data type conversion table; In response to the data type existing in a preset data type conversion table, a data type conversion operation is performed on one or more of primary keys, real-time data, and historical data based on a target data type corresponding to the data type in the preset data type conversion table.

8. A data cleaning processing device, characterized in that: include: A receiving module, used to receive a data cleaning request, obtain a data cleaning configuration file based on the data cleaning request, and determine a target database, a target data table, and an export directory through the data cleaning configuration file; The export backup module is used to export the primary key from the target data table, export the historical data based on the primary key, and back it up to the export directory; A new module is used to create a new data table to insert the received real-time data for the target data table into the new data table; wherein the structure of the new data table is the same as that of the target data table; A compression module is used to obtain a primary key and historical data corresponding to the primary key from an export directory, perform data compression operations to insert into a new data table, and perform deletion operations on a target data table; wherein, for one or more of the primary key, real-time data, and historical data, the data type is analyzed to perform a data type conversion operation.

9. An 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 7.

10. 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 7 is implemented.

11. 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 7 is implemented.

Images