Method, device, and storage medium for batch replacing data item values in a massive data table

Abstract

The present application provides a method, device, and storage medium for batch replacing data item values in a massive data table. The method includes taking out the original values of the data items to be replaced in the original data table, and storing the original values and their new values after replacement based on replacement rules as the original primary key values and the corresponding new primary key values in a cache middleware respectively. Importing the data item values in the original data table into a distributed database, using each Map task to read one by one the original values to be replaced in the original data table from each shard, then querying the corresponding new values from the cache middleware, and replacing the original values with the new values to obtain each updated shard. Using each Reduce task to insert the data in each updated shard into an SQL script, and executing each SQL script to insert the data in each updated shard into a new data table having the same structure as the original data table. In this way, the processing time for replacing massive data can be reduced, and the replacement efficiency can be improved.

Description

Technical Field

[0001] This application relates to the technical field of data processing, and particularly to a method, device, and storage medium for batch replacing data item values in a massive data table. Background Art

[0002] During the data processing, it is often necessary to batch replace certain data items in a data table. However, the existing replacement methods take a long time in the process of batch replacing massive data. For example, using the existing replacement method to batch replace 30 million data takes about 60 minutes, which significantly reduces the efficiency of data processing. Summary of the Invention

[0003] This application provides a method, device, and storage medium for batch replacing data item values in a massive data table, which can reduce the processing time for batch replacing massive data, so as to achieve fast and efficient batch replacement of massive data.

[0004] According to the first aspect of this application, a method for batch replacing data item values in a massive data table is provided, including taking out the original values of the data items to be replaced in the original data table, and storing the original values and their new values after replacement based on the replacement rules as the original primary key values and the corresponding new primary key values into a cache middleware respectively; importing the data item values in the original data table into a distributed database, where the distributed database has a number of shards not less than a first threshold; using each Map task corresponding to each shard to sequentially read the original values of the data items to be replaced in the original data table from each shard, then querying the corresponding new primary key value as the new value from the cache middleware with the original value as the original primary key value, and replacing the original value with the new value to obtain each updated shard; using each Reduce task corresponding to each updated shard to insert the data in each updated shard into an SQL script corresponding to each updated shard; executing each SQL script to insert the data in each updated shard into a new data table with the same structure as the original data table.

[0005] According to the second aspect of this application, a device for batch replacing data item values in a massive data table is provided, where the device includes one or more processors; a storage device for storing one or more programs, and when the one or more programs are executed by the one or more processors, the one or more processors implement the method for batch replacing data item values in a massive data table as described in each embodiment of this application.

[0006] According to the third aspect of this application, a computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, it implements the method for batch replacing data item values in a massive data table as described in each embodiment of this application.

[0007] Compared with the prior art, the beneficial effects of the embodiments of the present application are as follows:

[0008] In the present application, a cache middleware, a distributed database, and a MapReduce computing model are used to replace a large number of data item values in a data table. Compared with the traditional method of directly replacing the data table according to the replacement rules by a program, the extremely fast search speed of the cache and the parallel data search and processing capabilities of distributed storage are fully utilized, greatly improving the data processing efficiency and reducing the time cost of data processing.

[0009] The above general description and the following detailed description are merely exemplary and explanatory, and are not intended to limit the claimed invention. BRIEF DESCRIPTION OF THE DRAWINGS

[0010] In the drawings, which are not necessarily to scale, the same reference numerals may describe similar components in different views. Similar reference numerals with alphabetical suffixes or different alphabetical suffixes may represent different examples of similar components. The drawings generally illustrate various embodiments by way of example and not limitation, and are used in conjunction with the description and the claims to explain the disclosed embodiments. Such embodiments are illustrative and exemplary, and are not intended to be an exhaustive or exclusive embodiment of the method, apparatus, system, or non-transitory computer-readable medium having instructions for implementing the method.

[0011] Figure 1 A flowchart showing a method for batch replacing data item values in a large amount of data tables according to an embodiment of the present application.

[0012] Figure 2 A schematic diagram showing storing the original value and the new value after replacement based on the replacement rule as the original primary key value (key) and the corresponding new primary key value (value) in the cache middleware according to an embodiment of the present application.

[0013] Figure 3 An implementation method showing batch replacing data item values in a large amount of data tables according to an embodiment of the present application. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0014] To enable those skilled in the art to better understand the technical solutions of the present application, the present application will be described in detail below with reference to the accompanying drawings and specific embodiments. The embodiments of the present application will be further described in detail below with reference to the accompanying drawings and specific examples, but this is not a limitation to the present application.

[0015] The "first", "second" and similar terms used in this application do not denote any order, quantity or importance, but are only used for distinction. Terms such as "including" or "comprising" mean that the elements before this term cover the elements listed after this term, and do not exclude the possibility of also covering other elements. The execution order of each step in the method described in this application in combination with the accompanying drawings is not limited. As long as the logical relationship between each step is not affected, several steps can be integrated into a single step, a single step can be decomposed into multiple steps, or the execution order of each step can be adjusted according to specific requirements.

[0016] Figure 1 The flowchart shows a method for batch replacing data item values in a massive data table according to an embodiment of the present application. The method starts from step S101, where the original value of the data item to be replaced in the original data table is taken out, and the original value and the new value after replacement based on the replacement rule are respectively stored in the cache middleware as the original primary key value (key) and the corresponding new primary key value (value). The cache middleware includes, but is not limited to, Memcached, MongoDB, Redis (Remote Dictionary Server), and preferably, the cache middleware is Redis. Among them, Redis is a key-value storage system, and the data of Redis is stored in memory, with very fast read and write speeds, and can process more than 100,000 read and write operations per second. Storing the original value of the data item to be replaced in the original data table in the cache middleware makes full use of the high-speed read and write advantages of the cache middleware to improve the efficiency of data replacement.

[0017] Specifically, as Figure 2As shown, it is necessary to batch replace the content of the "department dept" column in the employee table with 30 million records in the original table 201 according to the replacement rules. In the replacement rule table 202, the id represents the address stored in the cache middleware, and each id corresponds to a key-value pair. The original value column represents the original value of the data item to be replaced in the original data table, and the corresponding new value column represents the new value after replacement. For example, when the id is 1001, the original primary key value key and the new primary key value value form a key-value pair, indicating that the original value dept01 will be replaced with prod-sale-dept-01. When the id is 1002, it means that the original value dept02 will be replaced with prod-sale-dept-02, and so on. According to the replacement rule table 202, the original values to be replaced and the new values after replacement in the original table 201 are respectively stored in the cache middleware 203 as the original primary key value key and the new primary key value value. As shown in the cache middleware 203, the Redis hash is a collection of key-value pairs. The Hash of Redis actually has a Value of a HashMap stored internally and provides an interface to directly access the members of this Map. It can be understood that the Key is the user ID, and the value is a Map. The key of this Map is the attribute name of the member, and the value is the attribute value. In this way, the modification and access of data can be directly through the Key of its internal Map (the key of the internal Map in Redis is called field), that is, by key (user ID) + field (attribute label), the corresponding attribute data can be operated. Based on the cache middleware 203, it is possible to quickly and efficiently directly query which new value the original value to be replaced will be replaced with.

[0018] Back to Figure 1, in step S102, import the data item values in the original data table into a distributed database, where the distributed database has a number of shards not less than a first threshold. Among them, the distributed database consists of multiple independent entities and is interconnected with each other through a network. The distributed database consists of multiple data files located at different sites and allows multiple users to access and operate on the data. Importing the data item values in the original data table into the distributed database and dispersing the data item values to multiple nodes can process data more flexibly and efficiently. Specifically, execute a batch replacement of data item values for a large amount of data tables, and the number of shards of the distributed database is not less than the first threshold to improve the efficiency of data processing. Among them, the distributed database can be the distributed columnar storage database HBase. HBase is a highly reliable, high-performance, column-oriented, scalable distributed database. The data stored in the distributed columnar storage database is stored by column, and each column is stored separately. During the query process, only the columns involved in the query need to be accessed. Moreover, based on the distributed columnar storage database HBase, the concurrent processing performance of queries is relatively high.

[0019] In step S103, use each Map task corresponding to each shard to sequentially read the original values to be replaced in the original data table from each shard, and then query the corresponding new primary key value as the new value from the cache middleware using the original value as the original primary key value, and replace the original value with the new value to obtain each updated shard. Specifically, perform the replacement of data item values in the original data table based on the distributed computing model MapReduce. MapReduce is a computing model and a programming framework for a distributed computing program used for large-scale data calculations. Among them, MapReduce divides the application into two tasks: Map and Reduce. The distributed database includes not less than the first threshold of shards. Each Map task sequentially reads the original values to be replaced in the original data table from its corresponding shard. For example, sequentially read dept01, dept02, dept03, dept04... from the original table 201. Then, based on the correspondence between the original primary key value key and the new primary key value value, query the corresponding new primary key value as the new value from the cache middleware using the original value as the original primary key value. During the execution of the Map task, after obtaining the original value, based on the key-value correspondence in the cache middleware 203, query which new value each original value will be replaced with, and replace the original value with the new value. Each Map task will read and analyze the data corresponding to a shard, and the calculated result data will be temporarily saved to the local disk of the node where it is located. The original values in the original shard are replaced with new values to obtain updated shards.

[0020] In this embodiment, based on a replacement rule (for example, adding a random number after each value), the cache middleware 203 generates the correspondence between the original primary key value (key) and the new primary key value (value) according to the replacement rule, and imports the massive data item values in the original table 201 into the distributed database. The MapReduce computing model is used to execute the Map task to perform the search and replacement of the data item values. Through the cooperation between the cache middleware and the distributed database, the efficiency of querying the corresponding new value based on each original value is improved, and the data processing time is greatly reduced.

[0021] In step S104, using each Reduce task corresponding to each updated shard, the data in each updated shard is inserted into the SQL script corresponding to each updated shard. By the Map task, the original value in the shard is replaced with the new value to obtain the updated shard, and then the MapReduce computing model is used to execute the Reduce task. The data in each updated shard is gathered together and inserted into the SQL script corresponding to each updated shard. Among them, each updated shard corresponds to a Reduce task and an SQL script, which is beneficial to the parallel processing of the data in different updated shards by multiple MapReduce computing models, improving the data processing efficiency. Each SQL script is executed to insert the data in each updated shard into a new data table with the same structure as the original data table (as in step S105). The original data table can be renamed as a backup table, and the new data table can be renamed as the name of the original data table, realizing the batch replacement of the massive data item values in the original data table.

[0022] With Figure 3The method for batch replacement of massive data item values in a data table is described as a specific embodiment. The original table 301 has 30 million records, and the data in the "department" column in the original table 301 is batch replaced according to the replacement rule. Replace dept01 with prod-sale-dept-01, and dept02 will be replaced with prod-sale-dept-02, and so on, to form a replacement rule. According to the replacement, the corresponding relationship between the original primary key value key and the new primary key value value is generated and stored in the cache middleware. The data item values in the original table 301 are imported into the distributed database, and the distributed database 302 is divided into 3000 shards, where shard 1 stores the 1st to 10000th record data, shard 2 stores the 1001st to 20000th record data, shard 3 stores the 20001st to 30000th record data... shard 3000 stores the 29,990,000th to 30,000,000th data records. At this point, the data in the original table 301 is stored in each shard of the distributed database 302. The MapReduce computing model is used to execute the Map task for each shard, that is, each shard corresponds to a Map task, so that multiple Map tasks can be performed simultaneously, improving the efficiency of data processing. The MapReduce computing model is used to read the original values to be replaced from each corresponding shard one by one, and the new values corresponding to the original values are queried from the cache middleware based on the key-value correspondence, and the original values are replaced with the new values. In this way, the original values in the "department" column in each shard are replaced with new values to obtain each updated shard. Although the original values in the "department" column in each updated shard are replaced with new values, the 30 million records in the original table 301 are still distributed in the 3000 updated shards in the updated distributed database 304. The MapReduce computing model is used to execute Reduce tasks on each update slice, and the data in each update slice is inserted into each SQL306 script corresponding to each update slice. After that, each SQL306 script is executed to insert the data in each update slice into the database 307 where the original table is located, wherein the structure of the new data table is the same as that of the original table 301, and the "department" column in the new data table stores the new value after replacement, thereby realizing the batch replacement of the data item values in the original table 301, which takes only about 10 minutes. However, according to the existing method, the data item values in the original table 301 are replaced in batches, which takes more than 60 minutes. Therefore, the method provided by this embodiment can effectively improve the efficiency of batch replacement of the data item values in the original data table, and can greatly reduce the time of data processing.

[0023] In some embodiments of the present application, extracting the original values of the data items to be replaced in the original data table further includes: extracting the multiple original values of the data items with the same numerical value only once. For example, if it is desired to replace each original value in the first column of the original data table according to the replacement rule of adding 1, that is, replacing 1 with 2, 2 with 3, and so on. If there are 10,000 original values with a numerical value of 1 in the first column, only the first obtained original value with a numerical value of 1 needs to be extracted, and according to the replacement rule, a key-value pair is constructed and stored in the cache middleware, without the need to extract the other 9,999 original values with a numerical value of 1 and store them in the cache middleware. In this way, for multiple original values with the same numerical value, only one extraction and storage in the cache middleware can reduce the workload of the processor for data processing, reduce the memory occupied by the data in the cache middleware, and also contribute to improving the speed of querying data in the cache middleware.

[0024] In some embodiments of the present application, using each Reduce task corresponding to each update shard to insert the data in each update shard into the SQL script corresponding to each update shard specifically includes that when the number of data in the data group in the update shard where the new value is replaced is not less than a second threshold, using the corresponding Reduce task to insert the data in the data group where the new value is replaced into the SQL script corresponding to each update shard. Specifically, taking each shard and each update shard including 10,000 records as an example for illustration. The Map task replaces each original value in each shard one by one, and for each replaced original value, the replaced new value is stored in the update shard. Each update shard can be divided into 100 data groups, and each data group has 100 records. Taking the first data group as an example, when the replaced new values are stored in the first data group one by one, it is judged whether the number of data stored in the first data group is greater than 100. If it is greater than 100, the Reduce task is used to insert the data in the first data group where the new value is replaced into each SQL script. This embodiment is only an exemplary illustration, and other feasible methods are not excluded. The inventors of the present application have found that the data processing method provided by this embodiment can reduce the data processing time compared with storing the replaced new values into the cache middleware one by one.

[0025] Further, when the number of data in the data group in the update shard where the new value is replaced is less than the second threshold number, the replacement process of the original value is continued. That is to say, when the number of data in the data group where the new value is replaced is not less than the second threshold, the Reduce task is used to execute the task of inserting the data in the data group into the SQL script, and if the number of data in the data group is less than the second threshold, the replacement process is continued without inserting the data into the SQL script to improve the data processing efficiency.

[0026] In some embodiments of the present application, the data item values in the original data table are imported into a distributed database. Specifically, it includes using an import tool to read the data in the original data table one by one, calculating the data range of each shard according to the splitting field, and importing the data item values of the corresponding data range in the original data table into each shard in the distributed database. Among them, the distributed database can be Hive. Hive is a data warehouse tool based on static batch processing Hadoop (distributed system infrastructure). Through Hive, structured data files can be mapped into a database table, and simple SQL query functions can be provided. At the same time, SQL statements are converted into MapReduce tasks for running. The import tool can be Sqoop. Sqoop (SQL-to-Hadoop) can import the data in a relational database (such as MySQL, Oracle, Postgres, etc.) into the HDFS (distributed file system) of Hadoop, and can also export the data in HDFS to a relational database. Specifically, taking the import tool as Sqoop as an example for illustration. Using Sqoop to import the table mysql_employee data in the relational database into the distributed database table hive_employee can be achieved based on the following code: sqoop import–connect jdbc:mysql: / / database IP address: database port number / database name—username database username—password database password—table mysql_employee -field-terminated-by‘\t’—delete-target-dir—num-mappers 1—hive-import default—hive-table hive_employee. The data range of each shard in the distributed database can be calculated according to the splitting field, and the specific data range of each shard is not limited and can be set according to the user's needs. For example, Figure 3 the original table 301 in [reference] has 30 million record data. In the case where the number of shards is 3000, the data volume in each shard can be set to 10,000, and the data range of each shard can be divided. Then, using the import tool, import the data item values of the corresponding data range into each shard respectively. In this way, the data volume in each shard is kept consistent, which is beneficial to maintaining the synchronization of data processing in each shard as much as possible when processing data in multiple shards in parallel, so as to shorten the processing time for batch replacement of a large number of data item values.

[0027] In some embodiments of the present application, the distributed database is a distributed columnar storage database, and each Map task is used to parallelly perform data search and replacement on each of the shards. By using a distributed columnar storage database, the data in the original data table can be dispersed into different shards, and the computing model is used to process each shard in parallel. By using cache middleware, a distributed columnar storage database, and distributed computing for data replacement, compared with the traditional method of directly replacing the data table according to the replacement rules by a program, this embodiment makes full use of the extremely fast search speed of the cache middleware and the parallel data search and processing capabilities of the distributed columnar storage database, greatly improving the data processing efficiency and reducing the time cost of data processing.

[0028] In some embodiments of the present application, the method for setting the replacement rules includes using regular expressions or a rule engine to set the replacement rules, and the setting of the replacement rules can be determined according to the user's requirements. Regular expressions can also be used to set slightly more complex replacement rules, and a certain rule engine middleware can also be used to set more complex data replacement rules. In the cache middleware, the corresponding relationship between the original primary key value and the new primary key value is generated according to the replacement rules, so as to quickly query the new value corresponding to the original value in the cache middleware.

[0029] In some implementations of the present application, a device for batch replacing data item values in a massive data table is provided. The device includes one or more processors, and the processors can be processing devices including more than one general-purpose processing device, such as a microprocessor, a central processing unit (CPU), a graphics processing unit (GPU), etc. More specifically, the processor can be a complex instruction set computing (CISC) microprocessor, a reduced instruction set computing (RISC) microprocessor, a very long instruction word (VLIW) microprocessor, a processor running other instruction sets, or a processor running a combination of instruction sets. The processor can also be more than one dedicated processing device, such as an application-specific integrated circuit (ASIC), a field-programmable gate array (FPGA), a digital signal processor (DSP), a system-on-chip (SoC), etc.

[0030] It further includes 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 for batch replacing data item values in a massive data table as described in various embodiments of the present application.

[0031] This application describes various operations or functions, which can be implemented as software code or instructions or defined as software code or instructions. Such content can be source code that can be directly executed or differential code ("incremental" or "patch" code) ("object" or "executable" form). The software code or instructions can be stored in a computer-readable storage medium, and when executed, can cause a machine to perform the described functions or operations, and include any mechanism for storing information in a form accessible to a machine (e.g., a computing device, an electronic system, etc.), such as a recordable or non-recordable medium (e.g., read-only memory (ROM), random access memory (RAM), magnetic disk storage media, optical storage media, flash devices, etc.).

[0032] The exemplary methods described in this application can be implemented at least in part by a machine or a computer. In some embodiments, a computer-readable storage medium is provided, which stores a computer program that, when executed by a processor, implements the method for batch replacing data item values in a large amount of data tables described in various embodiments of this application. The implementation of such a method can include software code, such as microcode, assembly language code, high-level language code, etc. Various software programming techniques can be used to create various programs or program modules. For example, a program part or a program module can be designed in or with the help of Java, Python, C, C++, assembly language, or any known programming language. One or more of such software parts or modules can be integrated into a computer system and / or a computer-readable medium. Such software code can include computer-readable instructions for performing various methods. Such software code can form part of a computer program product or a computer program module. In addition, in an example, the software code can be tangibly stored on one or more volatile, non-transitory, or non-volatile tangible computer-readable media, such as during execution or at other times. Examples of such tangible computer-readable media can include, but are not limited to, hard disks, removable disks, removable optical disks (e.g., optical disks and digital video disks), cassette tapes, memory cards or storage sticks, random access memory (RAM), read-only memory (ROM), etc.

[0033] In addition, although exemplary embodiments have been described herein, the scope includes any and all embodiments based on the present application that have equivalent elements, modifications, omissions, combinations (e.g., schemes that cross various embodiments), adaptations, or alterations. The elements in the claims will be construed broadly based on the language employed in the claims and are not limited to the examples described in the specification or during the implementation of the present application, and the examples will be construed as non-exclusive. Thus, the specification and examples are intended to be considered only as examples, and the true scope and spirit are indicated by the full scope of the claims and their equivalents. The foregoing description is intended to be illustrative rather than restrictive. For example, the above examples (or one or more of them) can be used in combination with each other. For example, other embodiments can be used by those of ordinary skill in the art upon reading the above description. Additionally, in the above detailed description, various features can be grouped together to simplify the present application. This should not be construed as an intention that the disclosed features that are not claimed are necessary for any of the claims. On the contrary, the subject matter of the present application can be less than all the features of a particular disclosed embodiment. Thus, the claims are incorporated herein by way of example or embodiment into the detailed description, where each claim independently serves as a separate embodiment, and considering these embodiments, they can be combined with each other in various combinations or permutations. The scope of the present application should be determined with reference to the appended claims and the full scope of the equivalent forms empowered by these claims.

[0034] The above embodiments are only exemplary embodiments of the present application and are not used to limit the present application. The protection scope of the present application is defined by the claims. Those skilled in the art can make various modifications or equivalent replacements within the essence and protection scope of the present application, and such modifications or equivalent replacements should also be regarded as falling within the protection scope of the present application.

Claims

1. A method for batch replacing data item values in a massive data table, characterized in that, Including: Taking out the original values of the data items to be replaced in the original data table, and storing the original values and their new values after replacement based on the replacement rules as the original primary key values and the corresponding new primary key values respectively in the cache middleware; Importing the data item values in the original data table into a distributed database, where the distributed database has a number of shards not less than a first threshold; Using each Map task corresponding to each shard to read one by one the original values to be replaced in the original data table from each shard, then querying the corresponding new primary key value as the new value from the cache middleware with the original value as the original primary key value, and replacing the original value with the new value to obtain each updated shard; When the number of data in the data group with the new value replaced in the updated shard is not less than a second threshold, using the corresponding Reduce task to insert the data in the data group with the new value replaced into the SQL script corresponding to each updated shard; When the number of data in the data group with the new value replaced in the updated shard is less than the second threshold, continuing the replacement process of the original value; Executing each SQL script to insert the data in each updated shard into a new data table with the same structure as the original data table; 2. The method according to claim 1, wherein Taking out the original values of the data items to be replaced in the original data table further includes: taking out only once the multiple original values of the data items with the same value; 3. The method according to claim 1, characterized in that, Importing the data item values in the original data table into the distributed database specifically includes: Using an import tool to read the data in the original data table one by one, and calculating the data range of each shard according to the segmentation field, and importing the data item values of the corresponding data range in the original data table into each shard in the distributed database respectively; 4. The method according to claim 1, characterized in that The distributed database is a distributed columnar storage database; using each Map task to perform data lookup and replacement on each shard in parallel; 5. The method according to claim 1, wherein The cache middleware is Redis; 6. The method according to claim 1, characterized in that The setting method of the replacement rule includes: using a regular expression or a rule engine to set the replacement rule; Generating the corresponding relationship between the original primary key value and the new primary key value in the cache middleware according to the replacement rule; 7. An apparatus for batch replacing data item values in a large amount of data tables, characterized in that, The device 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, enabling the one or more processors to implement the method for batch replacing data item values in a massive data table as described in any one of claims 1-6; 8. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, it implements the method for batch replacing data item values in a massive data table as described in any one of claims 1-6.

Images