CN111400309B

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

Info

Publication number: CN111400309B
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: 2023-09-15
Anticipated expiration: 2040-02-23
Also published as: CN111400309A

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 a preset rule to obtain a plurality of partition tables; creating an update log table, wherein the line number in the update log table corresponds to the serial number of each partition table; acquiring the current first resource utilization rate of the electronic device, and generating a plurality of threads according to the mapping relation between the resource utilization rate and the number of threads; and screening the data to be updated in each partition table, executing updating operation on the data to be updated in a parallel mode by adopting a plurality of threads, and updating the updating result of each partition table in the record of the corresponding row number in the updating log table until the record of each row in the updating log table is successful, thereby finishing data updating. 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 larger and larger, aiming at the current common Oracle database, when a large amount of data (for example, hundreds of millions of data) needs to be updated on a table, huge pressure is brought to the database, and the data is generally recorded and updated by adopting a transaction (a database operation sequence obtained by accessing and operating data items by the transaction), so that the rapidity and the integrity of data update cannot be ensured, and when the data update fails, all the data needs to be updated again, and 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, it is necessary to provide a data updating method, which aims to improve the data updating efficiency and ensure the integrity of data updating.

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

a data acquisition step: acquiring a source table to be updated from a database, and partitioning the source table according to a preset rule to obtain a plurality of partition tables;

a log creation step: creating an update log table, wherein the line number in the update log table corresponds to the serial number of each partition table, and setting the initial record of each line in the update log table as not updated;

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

and a data updating step: and screening 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 records of the corresponding row numbers in the updating log table, and reading the records in the updating log table every first preset time until the records of each row in the updating log table are 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 comprises:

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 comprises:

and acquiring a first line number recorded as update failure and non-update in the update log table every third preset time, and updating data to be updated in a partition table corresponding to the first line number.

Optionally, the method further comprises:

if the fourth preset time is exceeded, the record corresponding to the second line number in the update log table is still in failure of update or is not updated, and the partition table identifier corresponding to the second line number is sent to a preset client.

In addition, to achieve the above object, the present invention also provides an electronic device including: the device comprises a memory and a processor, wherein a data updating program capable of running on the processor is stored in the memory, and the data updating program realizes the following steps when being executed by the processor:

Optionally, the preset rule is:

Optionally, the data updating program when executed by the processor further implements the steps of:

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

Compared with the prior art, the method comprises the steps of firstly partitioning a source table according to a preset rule to obtain a plurality of partition tables, and then creating an update log table, wherein a line number in the update log table corresponds to the serial number of each partition table; 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 threads, and on the premise of ensuring the stability of a system, as many threads as possible are provided for parallel operation, so that 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 mode of parallel update of a plurality of threads, and updating the update result of each partition table in the record of the corresponding row number in the update log table until the record of each row in the update log table is successful.

Drawings

FIG. 1 is a schematic 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 updating method according to the present invention.

Detailed Description

The present invention will be described in further detail with reference to the drawings and examples, in order to make the objects, technical solutions and advantages of the present invention more apparent. It should be understood that the specific embodiments described herein are for purposes of illustration only and are not intended to limit the scope of the invention. All other embodiments, which can be made by those skilled in the art based on the embodiments of the invention without making any inventive effort, are intended to be within the scope of the invention.

It should be noted that the description of "first", "second", etc. in this disclosure is for descriptive purposes only and is not to be construed as indicating or implying a relative importance or implying an indication of the number of technical features being indicated. Thus, a feature defining "a first" or "a second" may explicitly or implicitly include at least one such feature. In addition, the technical solutions of the embodiments may be combined with each other, but it is necessary to base that the technical solutions can be realized by those skilled in the art, and when the technical solutions are contradictory or cannot be realized, the combination of the technical solutions should be considered to be absent and not within the scope of protection claimed in the present invention.

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

In the present embodiment, the electronic apparatus 1 includes, but is not limited to, a memory 11, a processor 12, and a network interface 13, which are communicably connected to each other via a system bus, and the memory 11 stores therein a data update program 10, the data update program 10 being executable by the processor 12. Fig. 1 shows only an electronic device 1 with components 11-13 and a data update procedure 10, it being understood by a person 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 may combine certain components, or a different arrangement of components.

Wherein the storage 11 comprises a memory and at least one type of readable storage medium. The memory provides a buffer for the operation of the electronic device 1; the readable storage medium may be a non-volatile storage medium such as flash memory, hard disk, multimedia card, card memory (e.g., SD or DX memory, etc.), random Access Memory (RAM), static Random Access Memory (SRAM), read Only Memory (ROM), electrically Erasable Programmable Read Only Memory (EEPROM), programmable Read Only Memory (PROM), magnetic memory, magnetic disk, optical disk, etc. In some embodiments, the readable storage medium may be an internal storage unit of the electronic device 1, such as a hard disk of the electronic device 1; in other embodiments, the nonvolatile storage medium may also be an external storage device of the electronic apparatus 1, such as a plug-in hard disk, a Smart Media Card (SMC), a Secure Digital (SD) Card, a Flash memory Card (Flash Card) or the like, which are provided on the electronic apparatus 1. In this embodiment, the readable storage medium of the memory 11 is generally used to store an operating system and various application software installed in the electronic device 1, for example, to store codes of the data update program 10 in an embodiment of the present invention. Further, the memory 11 may 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 (Central Processing Unit, CPU), controller, microcontroller, microprocessor, or other data processing chip in some embodiments. The processor 12 is typically used to control the overall operation of the electronic apparatus 1, such as performing control and processing related to data interaction or communication with other devices, etc. In this embodiment, the processor 12 is configured to execute the program code stored in the memory 11 or process data, such as the data update program 10.

The network interface 13 may comprise a wireless network interface or a wired network interface, the network interface 13 being 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 optionally a standard wired interface, a wireless interface. Alternatively, in some embodiments, the display may be an LED display, a liquid crystal display, a touch-sensitive liquid crystal display, an OLED (Organic Light-Emitting Diode) touch, or the like. The display may also be referred to as a display screen or display unit, as appropriate, for displaying information processed in the electronic device 1 and for displaying a visual user interface.

In one embodiment of the present invention, the data update program 10, when executed by the processor 12, implements the following data acquisition step, log creation step, thread generation step, and data update step.

A data acquisition step: and acquiring a source table to be updated from the database, and partitioning the source table according to a preset rule to obtain a plurality of partition tables.

In this embodiment, the preset rule is: the source tables are partitioned using a Hash partitioning method such that each partition table contains the same amount of data.

In this embodiment, the primary key value is used for uniformly partitioning, for example, there are 4 partition tables, the data with the primary key hash value of 1 is allocated in the first partition table, and the data with the primary key hash value of 6 is allocated in the second partition table.

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

The range partitioning is to partition a range of a certain value in the source table, and determine on which partition table the data is to be partitioned based on the range of the certain value, for example, partition based on a sequence number (data of 1 st line to 100 th line is to be partitioned into a first partition table), or partition based on a creation date of a service record (data of 1 st month in 2019 1 st month to 4 th month in 2019 is to be partitioned into the first partition table).

The compound partition is based on a range partition and a hash partition, and the source table is partitioned according to a certain column and then hashed according to a certain row.

When there are only a few values in a column, the source table may be partitioned in a list partition manner.

A log creation step: and creating an update log table, wherein the line number in the update log table corresponds to the serial number of each partition table, and setting the initial record of each line in the update log table 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 lines of the update log table is consistent with the number of the partition tables, and the line 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 conveniently and quickly determined later.

Thread generation: the current first resource utilization rate of the electronic device 1 is obtained, and a plurality of threads are generated according to the mapping relation between the resource utilization rate and the number of threads.

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

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

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

Resource utilization	Thread count
		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 the 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 the 1 st of 2016 and the 31 st of 2017, and screening the data meeting the conditions from each partition table 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, and then target data meeting the update condition is screened out from the screened partition table.

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

and acquiring the 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 the mapping relation between the resource utilization rate and the number of threads.

In order to prevent system crash caused by excessive number of threads or prevent update efficiency from being low due to the fact that the current number of threads is reduced, the number of threads is adjusted every second preset time according to the mapping relation among the current number of threads, the number of threads and the current resource utilization rate, and the number of threads, so that data can be updated by providing as many threads as possible on the premise of ensuring stability of the system, and update efficiency is improved.

In this embodiment, the data is updated by creating an SQL statement block, and an Oracle database job is created, and the job calls the SQL statement block to update the data meeting the update condition in each partition table according to the partition, that is, the condition of one partition is added under the original update condition.

According to the method and the device, the source table is split into the plurality of partition tables to update the data, so that an update task which is huge in data size 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 influencing 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 rapidly determined according to the update log table, and the integrity of data update can be ensured.

and acquiring a first row number recorded as failed update and non-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 record in the update log table is traversed in a circulating manner, and the update operation is executed again for the data to be updated in the partition table which is not updated and fails to be updated.

According to the embodiment, the update state of the data to be updated in each partition table can be rapidly determined through the update log table, if the data update in one partition table is abnormal, the partition table can be processed only, the operation of updating the partition table again is executed, the operation of other partition tables is not affected, the breakpoint update is realized, the reasons of the abnormality do not need to be searched one by one in the whole source table, the update operation is executed again on the whole source table, and the update efficiency is improved.

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

For example, if the data to be updated in each partition table is expected to be updated within 100 minutes, after two hours, the record of no update or failure in update is still 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 the corresponding technician can intervene in time, find the reason of unsuccessful update, and solve the problem in time.

As can be seen from the above embodiments, in the electronic device 1 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, where a line 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 threads, and on the premise of ensuring the stability of a system, as many threads as possible are provided for parallel operation, so that 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 mode of parallel update of a plurality of threads, and updating the update result of each partition table in the record of the corresponding row number in the update log table until the record of each row in the update log table is successful.

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

FIG. 2 is a block diagram of an embodiment of the data update process 10 of 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, illustratively:

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 update log table, where a line number in the update log table corresponds to a serial number of each partition table, and set an initial record of each line in the update log table as not updated;

the thread generating module 130 is configured to obtain a current first resource utilization rate of the electronic device 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 update condition, perform an update operation on the data to be updated, update an update result of each partition table in a record of a corresponding line number in the update log table, and read a record in the update log table every a first preset time until the record of each line in the update log table is updated successfully.

The functions or operation steps implemented when the program modules, such as the data acquisition module 110, the log creation module 120, the thread generation module 130, and the data update module 140, are substantially the same as those implemented in the embodiment of the electronic device 1, and are not described herein.

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

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

S2, creating an update log table, wherein the line number in the update log table corresponds to the serial number of each partition table, and setting the initial record of each line in the update log table as not updated.

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 threads.

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

TABLE 2

And S4, screening data to be updated in each partition table according to the updating conditions, executing updating operation on the data to be updated in a mode of parallel updating by a plurality of threads, updating the updating result of each partition table in the records of the corresponding row numbers in the updating log table, and reading the records in the updating log table every first preset time until the records of each row in the updating log table are updated successfully.

In this embodiment, the data updating method further includes:

As can be seen from the above embodiments, 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, where a line 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 threads, and on the premise of ensuring the stability of a system, as many threads as possible are provided for parallel operation, so that 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 mode of parallel update of a plurality of threads, and updating the update result of each partition table in the record of the corresponding row number in the update log table until the record of each row in the update log table is successful.

In addition, the embodiment of the invention also provides a computer readable storage medium, which can be any one or any combination of a plurality of 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. A data update program 10 is included in the computer readable storage medium, which data update program 10, when executed by a processor, performs the following operations:

acquiring a source table to be updated from a database, and partitioning the source table according to a preset rule to obtain a plurality of partition tables;

creating an update log table, wherein the line number in the update log table corresponds to the serial number of each partition table, and setting the initial record of each line in the update log table as not updated;

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 threads;

and screening 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 records of the corresponding row numbers in the updating log table, and reading the records in the updating log table every first preset time until the records of each row in the updating log table are updated successfully.

The embodiment of the computer readable storage medium of the present invention is substantially the same as the above-mentioned data updating method and the embodiment of the electronic device 1, and will not be repeated here.

The foregoing embodiment numbers of the present invention are merely for the purpose of description, and do not represent the advantages or disadvantages 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 one … …" does not exclude the presence of other like elements in a process, apparatus, article or method that comprises the element.

From the above description of the embodiments, it will be clear to those skilled in the art that the above-described embodiment method may be implemented by means of software plus a necessary general hardware platform, but of course may also be implemented by means of hardware, but in many cases the former is a preferred embodiment. Based on such understanding, the technical solution of the present invention may be embodied essentially or in a part contributing to the prior art in the form of a software product stored in a storage medium (e.g. ROM/RAM, magnetic disk, optical disk) comprising instructions for causing a terminal device (which may be a mobile phone, a computer, a server, an air conditioner, or a network device, etc.) to perform the method according to the embodiments of the present invention.

The foregoing description is only of the preferred embodiments of the present invention, and is not intended to limit the scope of the invention, but rather is intended to cover any equivalents of the structures or equivalent processes disclosed herein or in the alternative, which may be employed directly or indirectly in other related arts.

Claims

1. A data updating method applied to an electronic device, the method comprising:

a data acquisition step: obtaining a source table to be updated from a database, and partitioning the source table according to a preset rule to obtain a plurality of partition tables, wherein the preset rule comprises a hash partition, a range partition, a compound partition and a list partition;

and a data updating step: and screening data to be updated in each partition table according to 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 records of corresponding row numbers in the updating log table, reading the records in the updating log table at intervals of a first preset time, acquiring the current second resource utilization rate of the electronic device at intervals of a second preset time, and adjusting the number of threads according to the mapping relation between the resource utilization rate and the number of threads until the records of each row in the updating log table are updated successfully, thereby finishing data updating.

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

3. The data updating method according to any one of claims 1 to 2, wherein the method further comprises:

4. A data updating method as claimed in claim 3, wherein the method further comprises:

5. An electronic device, comprising: the device comprises a memory and a processor, wherein a data updating program capable of running on the processor is stored in the memory, and the data updating program realizes the following steps when being executed by the processor:

6. The electronic device of claim 5, wherein the preset rule is:

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

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