CN111400309A

CN111400309A - Data updating method, electronic device and readable storage medium

Info

Publication number: CN111400309A
Application number: CN202010110214.XA
Authority: CN
Inventors: 陈真; 余冬冬
Original assignee: Ping An Property and Casualty Insurance Company of China Ltd
Current assignee: Ping An Property and Casualty Insurance Company of China Ltd
Priority date: 2020-02-23
Filing date: 2020-02-23
Publication date: 2020-07-10
Anticipated expiration: 2040-02-23
Also published as: CN111400309B

Abstract

The invention relates to the technical field of data processing, and discloses a data updating method, which comprises the following steps: partitioning the source table according to preset rules to obtain a plurality of partition tables; creating an update log table, wherein the row number in the update log table corresponds to the serial number of each partition table; acquiring a current first resource utilization rate of the electronic device, and generating a plurality of threads according to a mapping relation between the resource utilization rate and the number of the threads; and screening the data to be updated in each partition table, executing updating operation on the data to be updated in a mode of parallel multiple threads, updating the updating result of each partition table in the record of the corresponding row number in the updating log table, and finishing data updating until the record of each row in the updating log table is updated successfully. The invention also provides an electronic device and a computer readable storage medium. The invention can improve the data updating efficiency and ensure the integrity of data updating.

Description

Data updating method, electronic device and readable storage medium

Technical Field

The present invention relates to the field of data processing technologies, and in particular, to a data updating method, an electronic device, and a readable storage medium.

Background

With the development of information technology, the amount of data generated by various applications is increasing, and for a current common Oracle database, when a large amount of data (for example, hundred million-level data) needs to be updated on a table, huge pressure is brought to the database, and currently, data is usually recorded and updated by adopting a transaction (the transaction is a database operation sequence obtained by accessing and operating data items), which cannot guarantee the rapidity and integrity of data update, and when the data update fails, all data needs to be updated again, so that the update efficiency is low. Therefore, a data updating method is needed to improve the updating efficiency and ensure the integrity of the data updating.

Disclosure of Invention

In view of the foregoing, there is a need to provide a data updating method, which aims to improve data updating efficiency and ensure integrity of data updating.

The data updating method provided by the invention comprises the following steps:

a data acquisition step: the method comprises the steps of obtaining a source table to be updated from a database, and partitioning the source table according to preset rules to obtain a plurality of partition tables;

a log creating step: establishing an update log table, wherein the row number in the update log table corresponds to the serial number of each partition table, and the initial record of each row in the update log table is set as not updated;

a thread generating step: acquiring a current first resource utilization rate of the electronic device, and generating a plurality of threads according to a mapping relation between the resource utilization rate and the number of the threads;

and a data updating step: screening data to be updated in each partition table according to an updating condition, executing updating operation on the data to be updated by adopting a mode of parallel updating of a plurality of threads, updating the updating result of each partition table in the record of the corresponding row number in the updating log table, reading the record in the updating log table at intervals of first preset time, and finishing data updating until the record of each row in the updating log table is updated successfully.

Optionally, the preset rule is:

and partitioning the source table by adopting a Hash partitioning method, so that each partition table contains the same amount of data.

Optionally, the method further includes:

and acquiring the current second resource utilization rate of the electronic device every second preset time, and adjusting the number of threads according to the mapping relation between the resource utilization rate and the number of threads.

Optionally, the method further includes:

and acquiring a first row number which is recorded as updating failure and not updated in the updating log table every a third preset time, and updating the data to be updated in the partition table corresponding to the first row number.

Optionally, the method further includes:

and if the record corresponding to the second row number in the update log table is still failed to be updated or not updated after the fourth preset time is exceeded, sending the partition table identifier corresponding to the second row number to a preset client.

In addition, to achieve the above object, the present invention also provides an electronic device including: a memory, a processor, the memory having stored therein a data update program operable on the processor, the data update program when executed by the processor implementing the steps of:

Optionally, the preset rule is:

Optionally, when executed by the processor, the data update program further implements the following steps:

In addition, to achieve the above object, the present invention also provides a computer-readable storage medium having a data update program stored thereon, the data update program being executable by one or more processors to implement the steps of the above data update method.

Compared with the prior art, the method includes the steps that firstly, a source table is partitioned according to preset rules to obtain a plurality of partition tables, and then an update log table is created, wherein row numbers in the update log table correspond to serial numbers of the partition tables; then, the current first resource utilization rate of the electronic device is obtained, a plurality of threads are generated according to the mapping relation between the resource utilization rate and the number of the threads, on the premise of ensuring the stability of the system, as many threads as possible are provided for parallel operation, and the updating efficiency can be improved; and then, screening the data to be updated in each partition table, performing update operation on the data to be updated by adopting a multi-thread parallel update mode, updating the update result of each partition table in the record corresponding to the row number in the update log table until the record of each row in the update log table is successfully updated, and completing data update.

Drawings

FIG. 1 is a diagram of an electronic device according to an embodiment of the invention;

FIG. 2 is a block diagram of an embodiment of the data update process of FIG. 1;

FIG. 3 is a flowchart of an embodiment of a data update method according to the present invention.

Detailed Description

In order to make the objects, technical solutions and advantages of the present invention more apparent, the present invention is described in further detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative of the invention and are not intended to limit the invention. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present invention.

It should be noted that the description relating to "first", "second", etc. in the present invention is for descriptive purposes only and is not to be construed as indicating or implying relative importance or implicitly indicating the number of technical features indicated. Thus, a feature defined as "first" or "second" may explicitly or implicitly include at least one such feature. In addition, technical solutions between various embodiments may be combined with each other, but must be realized by a person skilled in the art, and when the technical solutions are contradictory or cannot be realized, such a combination should not be considered to exist, and is not within the protection scope of the present invention.

Fig. 1 is a schematic diagram of an electronic device 1 according to an embodiment of the invention. The electronic apparatus 1 is a device capable of automatically performing numerical calculation and/or information processing in accordance with a command set or stored in advance. The electronic device 1 may be a computer, or may be a single network server, a server group composed of a plurality of network servers, or a cloud composed of a large number of hosts or network servers based on cloud computing, where cloud computing is one of distributed computing and is a super virtual computer composed of a group of loosely coupled computers.

In the present embodiment, the electronic device 1 includes, but is not limited to, a memory 11, a processor 12, and a network interface 13, which are communicatively connected to each other through a system bus, wherein the memory 11 stores a data updating program 10, and the data updating program 10 can be executed by the processor 12. Fig. 1 only shows the electronic device 1 with the components 11-13 and the data update program 10, and it will be understood by those skilled in the art that the structure shown in fig. 1 does not constitute a limitation of the electronic device 1, and may comprise fewer or more components than shown, or a combination of certain components, or a different arrangement of components.

The storage 11 includes a memory and at least one type of readable storage medium. The memory provides cache for the operation of the electronic device 1; the readable storage medium may be a non-volatile storage medium such as flash memory, a hard disk, a multimedia card, a card type memory (e.g., SD or DX memory, etc.), a Random Access Memory (RAM), a Static Random Access Memory (SRAM), a Read Only Memory (ROM), an Electrically Erasable Programmable Read Only Memory (EEPROM), a Programmable Read Only Memory (PROM), a magnetic memory, a magnetic disk, an optical disk, etc. In some embodiments, the readable storage medium may be an internal storage unit of the electronic apparatus 1, such as a hard disk of the electronic apparatus 1; in other embodiments, the non-volatile storage medium may also be an external storage device of the electronic apparatus 1, such as a plug-in hard disk provided on the electronic apparatus 1, a Smart Media Card (SMC), a Secure Digital (SD) Card, a Flash memory Card (Flash Card), and the like. In this embodiment, the readable storage medium of the memory 11 is generally used for storing an operating system and various application software installed in the electronic device 1, such as codes of the data update program 10 in an embodiment of the present invention. Further, the memory 11 may also be used to temporarily store various types of data that have been output or are to be output.

Processor 12 may be a Central Processing Unit (CPU), controller, microcontroller, microprocessor, or other data Processing chip in some embodiments. The processor 12 is generally used for controlling the overall operation of the electronic apparatus 1, such as performing control and processing related to data interaction or communication with other devices. In this embodiment, the processor 12 is configured to run the program code stored in the memory 11 or process data, for example, run the data updating program 10.

The network interface 13 may comprise a wireless network interface or a wired network interface, and the network interface 13 is used for establishing a communication connection between the electronic device 1 and a client (not shown).

Optionally, the electronic device 1 may further comprise a user interface, which may comprise a Display (Display), an input unit such as a Keyboard (Keyboard), and an optional user interface which may also comprise a standard wired interface and a wireless interface, optionally, in some embodiments, the Display may be an L ED Display, a liquid crystal Display, a touch-sensitive liquid crystal Display, an O L ED (Organic light-Emitting Diode) touch-sensitive device, and the like, wherein the Display may also be appropriately referred to as a Display screen or a Display unit for displaying information processed in the electronic device 1 and for displaying a visualized user interface.

In an embodiment of the present invention, the data update program 10, when executed by the processor 12, implements the following data acquiring step, log creating step, thread generating step, and data updating step.

A data acquisition step: the method comprises the steps of obtaining a source table to be updated from a database, and partitioning the source table according to preset rules to obtain a plurality of partition tables.

In this embodiment, the preset rule is: partitioning the source table by a Hash partitioning (Hash partitioning) method so that each partition table contains the same amount of data.

In this embodiment, uniform partitioning is performed by adopting a manner of taking the remainder of the primary key value, for example, there are 4 partition tables, data with a primary key hash value of 1 is allocated in a first partition table, and data with a primary key hash value of 6 is allocated in a second partition table.

In other embodiments, multiple partition modes such as range partition, composite partition, list partition, etc. may also be used, but it is necessary to ensure that the data amount in each partition table is uniformly distributed.

The range division is to divide a range of a certain value in the source table, and to determine which division table to divide the data into according to the range of the certain value, for example, division according to a number (data in the 1 st to 100 th rows are divided into the first division table) or division according to a creation date of the business record (data in the 1 st to 1 st 2019 month to 4 th month and 1 year is divided into the first division table).

The compound partitioning is based on range partitioning and hash partitioning, with the source table performing range partitioning on a column first, and then performing hash partitioning on a row.

When the value of a column is only a few, the source table can be partitioned by adopting a list partition mode.

A log creating step: and creating an update log table, wherein the row number in the update log table corresponds to the sequence number of each partition table, and the initial record of each row in the update log table is set as not updated.

The update log table is used for recording the update progress and the update state of each partition table, the number of rows of the update log table is consistent with the number of the partition tables, and the row number of the update log table corresponds to the serial number of each partition table, so that the update state of each partition table can be determined quickly in the follow-up process.

A thread generating step: and acquiring the current first resource utilization rate of the electronic device 1, and generating a plurality of threads according to the mapping relation between the resource utilization rate and the number of the threads.

Because the more the number of threads is, the greater the pressure on the database is, in order to ensure the stability of the system, the number of threads can be determined according to the predetermined mapping relationship between the resource utilization rate and the number of threads.

In this embodiment, the resource utilization rate includes a comprehensive utilization rate of various resources such as a memory, a CPU, a hard disk, a network, and the like.

The mapping relationship between the resource utilization rate and the number of threads can be shown in table 1 below.

Resource utilization	Number of threads
		0～20％	80
20％～40％	60
		40％～60％	40
60％～80％	20
		More than 80 percent	0

TABLE 1

In this embodiment, the update condition is preset according to a service requirement, for example, the source table includes 10 hundred million policy data, and the update condition is: and updating the insurance data of the domestic vehicle between 2016, 1 and 2017, 8, 31, screening the data meeting the condition from each partition table to serve as the data to be updated. If the partition table is partitioned according to the creation time, the partition table meeting the time condition can be screened out firstly, and then the target data meeting the updating condition is screened out from the screened partition table.

In this embodiment, when executed by the processor 12, the data update program 10 further implements the following steps:

and acquiring a current second resource utilization rate of the electronic device 1 every second preset time (for example, 10 minutes), and adjusting the number of threads according to a mapping relation between the resource utilization rate and the number of threads.

In order to prevent the system from being crashed due to too many threads or the current resource utilization rate is reduced but the number of threads is not increased to cause low updating efficiency because the resource utilization rate is in the process of changing, in this embodiment, the number of threads is adjusted every second preset time according to the current resource utilization rate, the mapping relationship between the resource utilization rate and the number of threads, and as many threads as possible are provided to update data on the premise of ensuring the stability of the system, so as to improve the updating efficiency.

In this embodiment, a SQ L statement block is created to update data, and an Oracle database job is started, and the job calls the SQ L statement block to update data satisfying an update condition in each partition table according to a partition, that is, a partition condition is added under an original update condition.

According to the method and the device, the source table is split into the plurality of partition tables to update data, so that the updating task which originally has huge data volume and occupies a large amount of memory is split into a plurality of small tasks, the occupation of the memory is reduced, the effect of less influence on an online database can be achieved, the stability of an online system is ensured, whether all data to be updated are updated or not can be quickly determined according to the updated log table, and the integrity of data updating can be ensured.

and acquiring a first row number recorded as update failure and not updated in the update log table every a third preset time (for example, 30 minutes), and updating the data to be updated in the partition table corresponding to the first row number.

In this embodiment, every third preset time, the records in the update log table are circularly traversed, and the update operation is performed again on the data to be updated in the partition table that is not updated and fails to be updated.

The embodiment can quickly determine the updating state of the data to be updated in each partition table by updating the log table, and if the data updating in a certain partition table is abnormal, the partition table can be only processed, the operation of updating the partition table again is executed, the operation of other partition tables is not influenced, the breakpoint updating is realized, the reason for the abnormality does not need to be searched in the whole source table one by one, the updating operation is executed on the whole source table again, and the updating efficiency is improved.

if the record corresponding to the second row number in the update log table is still failed to be updated or is not updated after the fourth preset time (for example, 2 hours), sending the partition table identifier corresponding to the second row number to a preset client.

For example, if the data to be updated in each partition table is expected to be updated within 100 minutes, and after two hours, a record which is not updated or fails to be updated exists in the update log table, the partition table corresponding to the record is searched, and the identifier corresponding to the partition table is sent to the preset client, so that corresponding technicians intervene in time, the reason why the update is not successful is searched, and the problem is solved in time.

As can be seen from the foregoing embodiment, in the electronic device 1 provided by the present invention, firstly, a source table is partitioned according to a preset rule to obtain a plurality of partition tables, and then an update log table is created, where a row number in the update log table corresponds to a serial number of each partition table; then, the current first resource utilization rate of the electronic device 1 is obtained, a plurality of threads are generated according to the mapping relation between the resource utilization rate and the number of the threads, on the premise that the stability of the system is ensured, as many threads as possible are provided for parallel operation, and the updating efficiency can be improved; and then, screening the data to be updated in each partition table, performing update operation on the data to be updated by adopting a multi-thread parallel update mode, updating the update result of each partition table in the record corresponding to the row number in the update log table until the record of each row in the update log table is successfully updated, and completing data update.

In other embodiments, the data updating program 10 can be further divided into one or more modules, and the one or more modules are stored in the memory 11 and executed by one or more processors (in this embodiment, the processor 12) to implement the present invention, where the modules referred to in the present invention refer to a series of computer program instruction segments capable of performing specific functions to describe the execution process of the data updating program 10 in the electronic device 1.

Fig. 2 is a block diagram of an embodiment of the data update program 10 in fig. 1.

In one embodiment of the present invention, the data update program 10 includes a data acquisition module 110, a log creation module 120, a thread generation module 130, and a data update module 140, which exemplarily:

the data obtaining module 110 is configured to obtain a source table to be updated from a database, and partition the source table according to a preset rule to obtain a plurality of partition tables;

the log creating module 120 is configured to create an updated log table, where a row number in the updated log table corresponds to a sequence number of each partition table, and an initial record of each row in the updated log table is set as not updated;

the thread generating module 130 is configured to obtain a current first resource utilization rate of the electronic apparatus 1, and generate a plurality of threads according to a mapping relationship between the resource utilization rate and the number of threads;

the data updating module 140 is configured to filter data to be updated in each partition table according to an updating condition, perform an updating operation on the data to be updated, update an updating result of each partition table in a record corresponding to a row number in the update log table, read records in the update log table every first preset time, and complete data updating until the record of each row in the update log table is updated successfully.

The functions or operation steps implemented by the data obtaining module 110, the log creating module 120, the thread generating module 130, and the data updating module 140 when executed are substantially the same as those of the embodiment of the electronic device 1, and are not described herein again.

FIG. 3 is a flowchart illustrating a data updating method according to an embodiment of the present invention, which includes steps S1-S4.

S1, obtaining a source table to be updated from the database, and partitioning the source table according to preset rules to obtain a plurality of partition tables.

And S2, creating an update log table, wherein the row number in the update log table corresponds to the sequence number of each partition table, and the initial record of each row in the update log table is set to be not updated.

And S3, acquiring the current first resource utilization rate of the electronic device 1, and generating a plurality of threads according to the mapping relation between the resource utilization rate and the number of the threads.

The mapping relationship between the resource utilization rate and the number of threads can be shown in table 2 below.

TABLE 2

S4, screening the data to be updated in each partition table according to the updating conditions, executing updating operation on the data to be updated by adopting a mode of parallel updating of a plurality of threads, updating the updating result of each partition table in the record corresponding to the row number in the updating log table, reading the record in the updating log table every other first preset time, and finishing data updating until the record of each row in the updating log table is updated successfully.

In this embodiment, the data updating method further includes:

The embodiment shows that, in the data updating method provided by the present invention, first, a source table is partitioned according to a preset rule to obtain a plurality of partition tables, and then an update log table is created, wherein a row number in the update log table corresponds to a sequence number of each partition table; then, the current first resource utilization rate of the electronic device 1 is obtained, a plurality of threads are generated according to the mapping relation between the resource utilization rate and the number of the threads, on the premise that the stability of the system is ensured, as many threads as possible are provided for parallel operation, and the updating efficiency can be improved; and then, screening the data to be updated in each partition table, performing update operation on the data to be updated by adopting a multi-thread parallel update mode, updating the update result of each partition table in the record corresponding to the row number in the update log table until the record of each row in the update log table is successfully updated, and completing data update.

In addition, the embodiment of the present invention further provides a computer-readable storage medium, which may be any one of or any combination of a hard disk, a multimedia card, an SD card, a flash memory card, an SMC, a Read Only Memory (ROM), an Erasable Programmable Read Only Memory (EPROM), a portable compact disc read only memory (CD-ROM), a USB memory, and the like. Included in the computer-readable storage medium is a data update program 10, which when executed by a processor, performs the following operations:

the method comprises the steps of obtaining a source table to be updated from a database, and partitioning the source table according to preset rules to obtain a plurality of partition tables;

establishing an update log table, wherein the row number in the update log table corresponds to the serial number of each partition table, and the initial record of each row in the update log table is set as not updated;

acquiring a current first resource utilization rate of the electronic device 1, and generating a plurality of threads according to a mapping relation between the resource utilization rate and the number of the threads;

screening data to be updated in each partition table according to an updating condition, executing updating operation on the data to be updated by adopting a mode of parallel updating of a plurality of threads, updating the updating result of each partition table in the record of the corresponding row number in the updating log table, reading the record in the updating log table at intervals of first preset time, and finishing data updating until the record of each row in the updating log table is updated successfully.

The embodiment of the computer-readable storage medium of the present invention is substantially the same as the embodiment of the data updating method and the electronic device 1, and will not be described herein again.

The above-mentioned serial numbers of the embodiments of the present invention are merely for description and do not represent the merits of the embodiments.

It should be noted that, in this document, the terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, apparatus, article, or method that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, apparatus, article, or method. Without further limitation, an element defined by the phrase "comprising an … …" does not exclude the presence of other like elements in a process, apparatus, article, or method that includes the element.

Through the above description of the embodiments, those skilled in the art will clearly understand that the method of the above embodiments can be implemented by software plus a necessary general hardware platform, and certainly can also be implemented by hardware, but in many cases, the former is a better implementation manner. Based on such understanding, the technical solutions of the present invention may be embodied in the form of a software product, which is stored in a storage medium (such as ROM/RAM, magnetic disk, optical disk) and includes instructions for enabling a terminal device (such as a mobile phone, a computer, a server, an air conditioner, or a network device) to execute the method according to the embodiments of the present invention.

The above description is only a preferred embodiment of the present invention, and not intended to limit the scope of the present invention, and all modifications of equivalent structures and equivalent processes, which are made by using the contents of the present specification and the accompanying drawings, or directly or indirectly applied to other related technical fields, are included in the scope of the present invention.

Claims

1. A data updating method applied to an electronic device is characterized by comprising the following steps:

2. The data updating method of claim 1, wherein the preset rule is:

3. The data updating method of claim 2, wherein the method further comprises:

4. A data update method according to any one of claims 1-3, characterized in that the method further comprises:

5. The data updating method of claim 4, wherein the method further comprises:

6. An electronic device, comprising: a memory, a processor, the memory having stored thereon a data update program operable on the processor, the data update program when executed by the processor implementing the steps of:

7. The electronic device of claim 6, wherein the predetermined rule is:

8. The electronic device of claim 7, wherein the data update program when executed by the processor further performs the steps of:

9. The electronic device of any of claims 6-8, wherein the data update program, when executed by the processor, further performs the steps of:

10. A computer-readable storage medium having stored thereon a data update program executable by one or more processors to perform the steps of the data update method of any one of claims 1 to 5.