CN112749147A - Method and device for merging and migrating data tables in database - Google Patents
Method and device for merging and migrating data tables in database Download PDFInfo
- Publication number
- CN112749147A CN112749147A CN201911050097.6A CN201911050097A CN112749147A CN 112749147 A CN112749147 A CN 112749147A CN 201911050097 A CN201911050097 A CN 201911050097A CN 112749147 A CN112749147 A CN 112749147A
- Authority
- CN
- China
- Prior art keywords
- data
- data table
- database
- verification
- writing
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/21—Design, administration or maintenance of databases
- G06F16/214—Database migration support
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2282—Tablespace storage structures; Management thereof
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/23—Updating
- G06F16/2365—Ensuring data consistency and integrity
Abstract
The invention discloses a method and a device for merging and migrating data tables in a database, and the scheme is as follows: acquiring data of at least one first data table in a database, and transferring the data of the at least one first data table to a second data table, wherein the first data table is a migration source data table, and the second data table is a migration target data table; performing write operation verification on the second data table, and writing the data acquired from the service layer into the first data table and the second data table respectively; if the write operation is verified successfully, continuing to execute; performing read operation verification on the second data table, and reading data from the second data table and feeding back the data to the service layer; if the read operation verification is successful, the first data table is deleted to complete the data table merging and migration, the effect of data verification after migration can be improved based on the technical scheme, the risk of data problems is reduced, and the service availability of the database is ensured.
Description
Technical Field
The invention relates to the technical field of databases, in particular to a method and a device for merging and migrating data tables in a database.
Background
Usually, data of the same service type is stored in different data tables due to unreasonable initial design of the data tables in the database, for example, data of a certain type of goods is respectively stored in an a-type goods data table and a B-type goods data table, when one piece of data needs to be queried, each data table needs to be queried in a traversing manner, that is, both the a-type goods data table and the B-type goods data table need to be queried, so that the query logic of the system is complex, the development and maintenance cost is too high, two consecutive data accesses increase the access pressure of the database, the network overhead also causes the response performance of an interface to be reduced, that is, the normal operation of the database system may be affected, so that data of a plurality of data tables needs to be merged and transferred to a new data table, and the new data table provides data services in a unified manner, the existing data table combining and transferring scheme can realize the non-stop transfer of the data of the old data table to the new data table and can also realize the synchronization of the changed data of the old data table to the new data table in the data transferring process.
In the prior art, after the data transfer is completed, the data consistency between the new data table and the old data table is verified in a verification manner, generally, the data of the new data table and the data of the old data table are compared one by one through a program, after the comparison is correct, the old data table is replaced by the new data table, the new data table provides online service to the outside, and the old data table is removed or data updating is not performed any more. The prior art scheme cannot completely guarantee the correctness of data transferred to a new data table, if the data of the new data table has a problem, the problem cannot be found in time and data repair cannot be carried out due to the fact that an old data table is removed or data updating is not carried out any more, and in extreme cases, data transfer operation may need to be carried out again, so that the normal operation of a database system and the use experience of a user are influenced.
Disclosure of Invention
One embodiment of the present invention provides a method for merging and migrating data tables in a database, which verifies data in a new data table in a manner that a new data table and an old data table simultaneously provide online data services after completing data migration between the data tables, so as to improve the effect of data verification, reduce the risk of data problems, and ensure the service availability of the database, and the method comprises the following steps:
acquiring data of at least one first data table in a database, and transferring the data of at least one first data table to a second data table, wherein the first data table is a migration source data table, and the second data table is a migration target data table;
performing write operation verification on the second data table, and writing data acquired from a service layer into the first data table and the second data table respectively; if the write operation is verified successfully, continuing to execute;
performing read operation verification on the second data table, and reading data from the second data table and feeding back the data to the service layer; and if the read operation verification is successful, deleting the first data table to finish the data table merging and migrating.
Optionally, after the acquiring data of at least one first data table in the database and transferring the data of at least one first data table to a second data table, the method further includes:
acquiring changed data of the first data table, wherein the changed data is changed in the process of merging and migrating the data tables;
synchronizing the change data to the second data table.
Optionally, the step of performing write operation verification on the second data table, and writing the data acquired from the service layer into the first data table and the second data table respectively includes:
the method comprises the steps that an adaptation layer acquires data from a service layer, the adaptation layer is packaged with a first data table adaptation and a second data table adaptation, the first data table is adapted to be a data access interface of the first data table, and the second data table is adapted to be a data access interface of the second data table;
according to the received data writing command for the second data table, the adaptation layer converts the acquired data into the available data of the database through adaptation of the second data table; sending the converted data to the second data table of the database and writing the converted data into the second data table, wherein the command for writing data into the second data table is sent by a configuration platform;
converting the acquired data into database available data through first data table adaptation; and sending the converted data to the corresponding first data table and writing the converted data into the corresponding first data table.
Optionally, the verifying the read operation of the second data table, and the step of reading data from the second data table and feeding back the data to the service layer includes:
receiving a data reading instruction sent by a service layer;
according to a received instruction for reading a second data table, the adaptation layer adapts and reads data of the second data table through the second data table, and the instruction for reading the second data table is sent by the configuration platform;
converting the read data into data available for a service layer;
and feeding back the converted data to the service layer.
Optionally, the second data table is verified for a write operation, and when the verification for the write operation fails, the method further includes:
sending alarm information, and rolling back the first data table and the second data table to a correct state before the write operation fails respectively;
receiving a command of stopping writing data into a second data table, stopping writing data into the second data table, wherein the command of stopping writing data into the second data table is sent by a configuration platform;
and repairing the data structure of the second data table, and repeatedly executing the step of verifying the write operation of the second data table until the write operation is verified successfully.
Optionally, the performing read operation verification on the second data table, when the read operation verification fails, the method further includes:
sending alarm information;
receiving a command of stopping reading the second data table, and switching to reading the first data table, wherein the command of stopping reading the second data table is sent by a configuration platform;
acquiring the change data of the first data table, and synchronizing the change data to the second data table to repair the data of the second data table;
and after the data of the second data table is repaired, repeatedly executing the step of performing read operation verification on the second data table until the read operation verification is successful.
Optionally, before the step of acquiring data of at least one first data table in the database and transferring the data of at least one first data table to a second data table, the method further includes:
and dividing the data ID fields of the second data table, wherein the data ID fields comprise the data ID field corresponding to each first data table and the corresponding data ID field of the changed data.
Optionally, before the step of obtaining the changed data of the first data table, the method further includes:
monitoring the change of the data of at least one first data table in the database;
the changed data is recorded and stored.
In another embodiment of the present invention, an apparatus for merging and migrating data tables in a database is provided, which includes:
the data transfer module is used for acquiring data of at least one first data table in a database and transferring the data of the at least one first data table to a second data table, wherein the first data table is a migration source data table, and the second data table is a migration target data table;
the first verification module is used for performing write operation verification on the second data table and respectively writing the data acquired from the service layer into the first data table and the second data table; if the write operation is verified successfully, continuing to execute;
the second verification module is used for performing read operation verification on the second data table and feeding back data read from the second data table to the service layer;
and the deleting module is used for deleting the first data table to finish the merging and migration of the data tables if the read operation verification is successful.
In another embodiment of the present invention, a non-transitory computer readable storage medium stores instructions that, when executed by a processor, cause the processor to perform the steps of merging and migrating data tables in a database as described in the above embodiments.
In another embodiment of the present invention, an electronic device includes a memory, a processor, and a computer program stored in the memory and executable on the processor, and the processor executes the computer program to implement the steps of the method for merging and migrating data tables in a database according to the above embodiment.
Based on the above-mentioned embodiments, the solution of the present invention,
and after the data transfer from the first data table to the second data table is completed, wherein the first data table is a migration source data table, namely an old data table, and the second data table is a migration target data table, namely a new data table. And verifying the writing operation and the reading operation of the second data table, and simultaneously keeping the writing operation on the first data table, namely, keeping the writing operation on the first data table while carrying out the writing operation and the reading operation on the second data table. And meanwhile, the first data table and the second data table are used for providing online data service, and the data verification effect is improved by verifying the data of the second data table in the actual online service state, so that the service availability of the database and the use experience of a user are improved. Meanwhile, as the use of the first data table is not stopped, the first data table can be normally updated in the verification process, and when the verification of the second data table has a problem, the first data table can be timely returned to use to ensure the normal operation of the database system.
Drawings
In order to more clearly illustrate the technical solutions in the embodiments of the present application, the drawings needed to be used in the description of the embodiments are briefly introduced below, and it is obvious that the drawings in the following description are only some embodiments of the present application, and it is obvious for those skilled in the art to obtain other drawings based on these drawings without inventive labor.
FIG. 1 illustrates a schematic diagram of an application scenario 100, in accordance with some embodiments of the present invention;
FIG. 2 is a flow diagram of a method 200 for merging and migrating data tables in a database according to some embodiments of the present invention;
FIG. 3 is a flow chart of a method 300 according to some embodiments of the invention;
FIG. 4 is a flow chart of a method 400 of some embodiments of the inventions;
FIG. 5 is a flow chart of a method 500 of some embodiments of the inventions;
FIG. 6 is a flow chart of a method 600 of some embodiments of the inventions;
FIG. 7 is a schematic diagram of the working principle of the adaptation layer of some embodiments of the present invention;
FIG. 8 is a block diagram illustrating an apparatus 800 for merging and migrating data tables in a database according to some embodiments of the present invention;
fig. 9 is a schematic diagram of an electronic device 900 according to some embodiments of the invention.
Detailed Description
The technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the drawings in the embodiments of the present application, and it is obvious that the described embodiments are only a part of the embodiments of the present application, and not all of the embodiments. 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 application.
The terms "first," "second," "third," "fourth," and the like in the description and in the claims, as well as in the drawings, if any, are used for distinguishing between similar elements and not necessarily for describing a particular sequential or chronological order. It is to be understood that the data so used is interchangeable under appropriate circumstances such that the embodiments of the invention described herein are, for example, capable of operation in sequences other than those illustrated or otherwise described herein. Furthermore, the terms "comprising" and "having," as well as any variations thereof, are intended to cover non-exclusive inclusions. For example, a process, method, system, article, or apparatus that comprises a list of steps or elements is not necessarily limited to those steps or elements explicitly listed, but may include other steps or elements not explicitly listed or inherent to such process, method, article, or apparatus.
The technical solution of the present invention will be described in detail with specific examples. Several of the following embodiments may be combined with each other and some details of the same or similar concepts or processes may not be repeated in some embodiments.
In one embodiment of the invention, please refer to fig. 1, which illustrates a schematic diagram of an application scenario 100 according to some embodiments of the present application.
The application scenario 100 may include a database server repository 110, an application server 120, and a configuration server 130. Here, the database server 110, the application server 120, and the configuration server 130 may communicate through a network 140. Network 140 may include, for example, a Local Area Network (LAN) and a Wide Area Network (WAN). Embodiments of the present application may implement network 140 using any well-known network protocol, including various wired or wireless protocols, such as Ethernet, FIREWIRE, Global System for Mobile communications (GSM), Enhanced Data GSM Environment (EDGE), Code Division Multiple Access (CDMA), Time Division Multiple Access (TDMA), or any other suitable communication protocol.
It is understood that the method for merging and migrating the data tables in the database provided by the embodiment of the present invention is generally performed by the application server 120, and accordingly, the data table merging and migrating apparatus in the database is generally disposed in the application server 120.
Referring to fig. 2, fig. 2 is a flowchart of a method 200 for merging and migrating data tables in a database according to an embodiment of the present invention, in which a method for merging and migrating data tables in a database includes the following steps:
step S201: the method comprises the steps of obtaining data of at least one first data table in a database, and transferring the data of the at least one first data table to a second data table, wherein the first data table is a migration source data table, and the second data table is a migration target data table.
The first data table may be a migration source data table, i.e., an old data table, the number of the first data tables may be multiple, the second data table may be a migration target data table, i.e., a new data table, and the initial data of the second data table is empty, i.e., the second data table is an empty table. The number of the second data table is one. That is, data of a plurality of migration source data tables are transferred to one migration target data table to form a new data table. When the total data amount of the multiple migration source data tables is consistent with the total data amount of one migration target data table, the data transfer can be considered to be completed. Specifically, the data transfer may be implemented by an existing data migration program to migrate data of at least one first data table to a second data table.
Step S202: performing write operation verification on the second data table, and writing data acquired from a service layer into the first data table and the second data table respectively; if the write operation is verified successfully, execution continues.
After the data transfer is completed, the data acquired from the service layer are all written into the first data table and the second data table, that is, the first data table provides data service online, and the second data table also provides data service online. And performing write operation verification on the second data table to verify whether the data structure in the second data table is correct. If the write operation verification is successful, the step S203 is executed to perform read operation verification on the second data table.
Step S203: performing read operation verification on the second data table, and reading data from the second data table and feeding back the data to the service layer; and if the read operation verification is successful, deleting the first data table to finish the data table merging and migrating.
Here, in the verification of the read operation on the second data table, the service layer may read only the data of the second data table, and the service layer may not read the data of the first data table for the time being, to verify whether the data of the second data table is complete, that is, whether there is a case that the data in the first data table is not transferred to the second data table, so as to verify the data consistency of the old and new data tables.
In summary, according to the scheme in the embodiment of the present invention, after the data transfer from the first data table to the second data table is completed, the first data table is a migration source data table, that is, an old data table, and the second data table is a migration target data table, that is, a new data table. The method comprises the steps of verifying the writing operation and the reading operation of the second data table, simultaneously keeping the writing operation on the first data table, namely, keeping the writing operation on the first data table while performing the writing operation and the reading operation on the second data table, simultaneously providing online data service by using the first data table and the second data table, improving the data verification effect and improving the service availability of the database and the use experience of users by a mode of verifying the data of the second data table in an actual online service state. Meanwhile, as the use of the first data table is not stopped, the first data table can be normally updated in the verification process, and when the verification of the second data table has a problem, the first data table can be timely returned to use to ensure the normal operation of the database system.
Referring to fig. 3, fig. 3 is a flowchart of a method 300 for merging and migrating data tables in a database according to some embodiments of the present invention, where the method 300 is a further extension of the method 200, and specifically includes the following steps:
step S301: the method comprises the steps of obtaining data of at least one first data table in a database, and transferring the data of the at least one first data table to a second data table.
The first data table is a migration source data table, and the second data table is a migration target data table.
Step S302: and acquiring the changed data of the first data table.
The changed data is data changed in the process of merging and migrating the data tables. In the process of merging and migrating the data tables, monitoring the change condition of the data of at least one first data table in the database, and recording the changed data through a preset database log program. The information of the recorded changed data may include a primary key ID of the data, a data change type including an update (insert), an update (update), and a delete (delete), and a data source from which the changed data is originated from.
The recorded change data may be stored in a database to which the first data table and the second data table belong, or in another independent database, so as to be easily acquired. Other independent databases can be Mongo, MySQL and other databases.
Step S303: synchronizing the change data to the second data table.
Specifically, the acquired change data is synchronized to the second data table by a synchronization program. It is understood that, since data changes may occur during the merged migration of the data tables, the operations of obtaining the changed data of the first data table and synchronizing the changed data to the second data table may be continuously performed before deleting the first data table.
Furthermore, since the same piece of data may have multiple changes before being synchronized to the second data table, the change information is saved, and when the data is changed synchronously, the data needs to be synchronized to be the latest change information. Specifically, by determining the time stamps of the piece of data in the first data table and the second data table, i.e., the modification time (modifyTime), if the modification times of the piece of data in the two tables are consistent, the piece of data is not synchronized. If the modification time of the piece of data in the second data table is less than the modification time of the piece of data in the first data table, the piece of data needs to be synchronized to the second data table.
Step S304: and performing write operation verification on the second data table, and writing the data acquired from the service layer into the first data table and the second data table respectively.
Specifically, in the embodiment of the present invention, an adaptation layer, that is, a data Access object DAO (data Access object) layer, may be set between the business layer and the database, where the DAO is an Application Programming Interface (API) and may implement data Access and operation, and the adaptation layer is an object that provides an abstract interface for the database or other persistence mechanisms, and provides various data Access operations on the premise of not exposing implementation details of the database or the underlying persistence scheme. It can be understood that the adaptation layer can implement various data access operations on the database, and for external objects such as the business layer, the various data access operations on the database are not sensed, and the logical processing of the business layer is not affected.
In the embodiment of the invention, the adaptation layer is packaged with a first data table adaptation and a second data table adaptation, the first data table adaptation is a data access interface DAO of the first data table, the second data table adaptation is a data access interface DAO of the second data table, and different data table adaptations are realized corresponding to different data access interfaces through the calling of the bottom-layer database packaged by the adaptation layer.
Specifically, the first data table adaptation DAO and the second data table adaptation DAO may be encapsulated in an adaptation layer through an existing Object Relational Mapping (ORM) framework, where the existing ORM framework includes IBATIS/mybat, HIBERNATE, and the like.
The adaptation layer obtains data from the service layer, converts the obtained data into data available for the database through adaptation of the second data table according to a received data writing instruction sent by the configuration platform to the second data table, sends the converted data to the second data table of the database, and writes the converted data into the second data table.
Writing the acquired data into a second data table, then adapting through the first data table, and converting the acquired data into database available data; and sending the converted data to a corresponding first data table and writing the converted data into the corresponding first data table. Since the number of the first data tables may be multiple, one first data table is adapted to one first data table.
Step S305: if the write operation verification is successful, execution continues with step S306.
Step S306: and performing read operation verification on the second data table, and reading data from the second data table and feeding back the data to the service layer.
The adaptation layer reads data required by the service layer in the second data table according to the instruction for reading the second data table sent by the configuration platform, converts the read data into data available for the service layer, and feeds the converted data back to the service layer.
Step S307: and if the read operation verification is successful, deleting the first data table to finish the data table merging and migrating.
Specifically, through verification of write operation and read operation of the second data table, correctness and consistency of data in the second data table can be better guaranteed, at this time, the first data table can be deleted, dependence of the second data table on the first data table is removed, data services are independently provided by the second data table, and accordingly data table merging migration can be completed.
In addition, the data of the second data table passing the verification can not generate data problems caused by data transfer, and the synchronization program can be deleted at the moment.
Referring to fig. 4, fig. 4 is a flowchart of a method 400 according to some embodiments of the present invention, wherein when the write operation fails to verify, the method 300 further includes the method 400, and the specific steps are as follows:
step S401: and sending alarm information, and rolling back the first data table and the second data table to a correct state before the write operation fails respectively.
Step S402: and receiving a command of stopping writing data into the second data table, wherein the command of stopping writing data into the second data table is sent by the configuration platform.
Specifically, the instruction to write data to the second data table is stopped, that is, the instruction to write data only to the first data table is executed, and the instruction to write data to the second data table is stopped. When the second data table has a problem, according to a command sent by the configuration platform for stopping writing data into the second data table, the first data table and the second data table before the problem occurs simultaneously execute writing operation, and the first data table is switched to independently execute the writing operation.
Step S403: and repairing the data structure of the second data table, and repeatedly executing the step of verifying the write operation of the second data table until the write operation is verified successfully.
In summary, by the above method, the data in the second data table is repaired until the second data table passes the verification of the write operation, i.e. the verification of the write operation of the second data table is successful. The reason for the verification failure may include: 1. the data itself is problematic. If the problem is data itself, the non-processing can be ignored. 2. And if the new table data structure has problems and the data has no relation, modifying the new table data structure. And performing write operation verification on the second data table to verify whether the data structure of the data in the second data table is correct or not, if the verification fails, repairing the data structure of the second data table, and performing write operation verification on the second data table again until the write operation verification succeeds to ensure that the data structure of the data in the second data table is correct. And after the write operation is verified to be successful, performing read operation verification.
Referring to fig. 5, fig. 5 is a flowchart of a method 500 according to some embodiments of the present invention, wherein when the read operation fails to verify, the method 300 further includes the method 500, including the following specific steps:
step S501: and sending alarm information.
Step S502: and receiving an instruction for stopping reading the second data table, and switching to reading the first data table, wherein the instruction for stopping reading the second data table is sent by the configuration platform.
Step S503: and acquiring the changed data of the first data table, and synchronizing the changed data to the second data table to repair the data of the second data table.
Step S504: and after the data of the second data table is repaired, repeatedly executing the step of performing read operation verification on the second data table until the read operation verification is successful.
In an embodiment of the present invention, preferably, as shown in fig. 6, fig. 6 is a flowchart of a method 600 according to some embodiments of the present invention, the method 600 is a further expanded method of the method 300, and the method 600 specifically includes the following steps:
step 601: and dividing the data ID field of the second data table, wherein the data ID field of the second data table comprises the data ID field corresponding to each first data table and the corresponding data ID field of the changed data.
Specifically, the data ID field of the second data table is divided, and different data sources have corresponding data ID fields, so that the data transferred to the second data table can be stored in the corresponding data ID fields according to the source of the data to form a mapping relationship. In the process of merging and migrating the data tables, if a data problem occurs, the data with the problem and the source of the data with the problem, namely which first data table the data belongs to, can be efficiently found, the original data information of the data is found in the corresponding first data table, the original data information of the data is transferred to the second data table, and the data in the second data table is repaired.
Here, taking two first data tables as an example, the manner of dividing the second data table may be: first, the second data table is divided into four segments according to the data ID, which may be a primary key ID: first data ID field bit: 0-799 ten thousand, second data ID field bit: 800 ten thousand to 1200 ten thousand, third data ID field bit: 1200 ten thousand to 1499 ten thousand, fourth data ID field bit: 1500 to 1500 ten thousand or more. A. B, two first data tables, wherein the data of the first data table A is correspondingly transferred to a first data ID field bit of a second data table; b, correspondingly transferring the data of the first data table to a second data ID field bit of a second data table; a, newly added data of a first data table correspondingly transfers or synchronizes a third data ID field bit of a second data table; and B, the newly added data of the first data table is correspondingly transferred or synchronized to the fourth data ID field bit of the second data table.
Therefore, when the data migrated to the second data table has problems, the data source can be quickly judged according to the data ID, and the problem repair data can be conveniently positioned.
Step S602: the method comprises the steps of obtaining data of at least one first data table in a database, and transferring the data of the at least one first data table to a second data table.
The first data table may be a migration source data table, i.e., an old data table, the number of the first data tables may be multiple, the second data table may be a migration target data table, i.e., a new data table, and the initial data of the second data table is empty, i.e., the second data table is an empty table. The number of the second data table is one. That is, data of a plurality of migration source data tables are transferred to one migration target data table to form a new data table. When the total data amount of the multiple migration source data tables is consistent with the total data amount of one migration target data table, the data transfer can be considered to be completed. Specifically, the data transfer may be implemented by an existing data migration program to migrate data of at least one first data table to a second data table.
Step S603: and acquiring the changed data of the first data table.
Here, the changed data is data that has been changed during the merge migration of the data table. In the process of merging and migrating the data tables, monitoring the change condition of the data of at least one first data table in the database, and recording the changed data through a preset database log program. The information of the recorded changed data may include a primary key ID of the data, a data change type including an update (insert), an update (update), and a delete (delete), and a data source from which the changed data is originated from.
Step S604: synchronizing the change data to the second data table.
Specifically, the acquired change data is synchronized to the second data table by a synchronization program. It is understood that, since data changes may occur during the merged migration of the data tables, the operations of obtaining the changed data of the first data table and synchronizing the changed data to the second data table may be continuously performed before deleting the first data table.
In addition, when the read operation of the second data table is verified to fail, the second data table needs to be repaired, and the data in the first data table which is not in the second data table can be synchronized to the second data table by the synchronization method.
Step S605: and performing write operation verification on the second data table, and writing the data acquired from the service layer into the first data table and the second data table respectively.
Specifically, in the embodiment of the present invention, an adaptation layer, that is, a data Access object DAO (data Access object) layer, may be set between the business layer and the database, where the DAO is an Application Programming Interface (API) and may implement data Access and operation, and the adaptation layer is an object that provides an abstract interface for the database or other persistence mechanisms, and provides various data Access operations on the premise of not exposing implementation details of the database or the underlying persistence scheme.
In the embodiment of the present invention, as shown in fig. 7, fig. 7 is a simple schematic diagram of an adaptation layer working principle of some embodiments of the present invention, where an adaptation layer 710 has a unified interface 711 facing a service layer 720, the adaptation layer 710 is packaged with a first data table adaptation 712 and a second data table adaptation 713, the first data table adaptation 712 is a data access interface DAO of a first data table, the second data table adaptation 713 is a data access interface DAO of a second data table, and different data tables are implemented by calling a bottom-layer database packaged by the adaptation layer, and correspond to different data access interfaces. The first data table adaptation or the second data table adaptation may have a name identification, and the adaptation layer 710 may identify the first data table adaptation or the second data table adaptation by the first data table adaptation or the second data table adaptation name identification. As shown in fig. 7, the first data table 741 corresponds to the first data table adaptation 712, and the first data table 742 corresponds to the second data table adaptation 713.
Specifically, the first data table adaptation 712 and the second data table adaptation 713 may be encapsulated in the adaptation layer 710 through an existing Object Relational Mapping (ORM) framework, where the existing ORM framework includes IBATIS/mybat, HIBERNATE, and the like, and an implementation manner thereof is an existing technical means and is not described herein again.
In addition, the adaptation layer 710 runs on the application server 120, and the application server 120 and the database server 110 to which the first data table 741 and the first data table 742 belong may implement unified communication through the ORM framework. Only the correspondence between the first data table adaptation 712 and the first data table 741 and the correspondence between the second data table adaptation 713 and the second data table are shown in fig. 7.
Here, the adaptation layer 710 obtains data from the service layer 720, and the adaptation layer 710 may obtain a write operation instruction according to the write operation instruction obtained from the configuration platform 730, where the write operation instruction may include a write data instruction to a second data table, so that the obtained data is first converted into database usable data by the second data table adaptation 712, and the converted data is sent to the second data table 742 of the database and written into the second data table 742, where the database usable data refers to a data format supported by the database, that is: and storing the lines.
After the acquired data are written into the second data table 742, the acquired data are converted into database available data through the first data table adaptation 711; and sending the converted data to a corresponding first data table 741, and writing the converted data into the corresponding first data table 741. In addition, since the number of the first data tables may be multiple, one first data table is adapted to one first data table.
Step S606: and judging whether the write operation verification of the second data table is successful or not.
If the write operation fails to verify, continue to execute step S607; if the write operation verification is successful, the step S610 is continuously executed.
Step S607: and sending alarm information, and rolling back the first data table and the second data table respectively.
Specifically, alarm information is sent, and the first data table and the second data table are respectively rolled back to a correct state before the write operation fails. When the acquired data cannot be written into the second data table, the reason why such a write failure occurs is that, in data transfer, the second data table may have a problem that the data structure is incorrect, so that the data cannot be written. At this time, an alarm message is sent, and the alarm message may be sent to a monitoring device of a technician, such as a mobile phone or a computer. Meanwhile, the first data table and the second data table are respectively rolled back to the state before the problem data are written.
In addition, sometimes, the adaptation layer program may also send alarm information when a problem occurs, and such alarm information may be ignored and not dealt with.
Step S608: and receiving a command of stopping writing data into the second data table, wherein the command of stopping writing data into the second data table is sent by the configuration platform.
Here, according to the received alarm information, the technician may perform an operation on the configuration platform to send an instruction to stop writing data into the second data table to the adaptation layer. And stopping writing data into the second data table, and simultaneously executing the operation of writing data into the first data table.
It is understood that when a problem occurs in writing data into the second data table, the write operation is changed from an operation state in which data is written into the first data table and the second data table, respectively, to an operation state in which data is written into only the first data table.
Step S609: and repairing the data structure of the second data table, and repeatedly executing the step of verifying the write operation of the second data table until the write operation is verified successfully.
Here, the data structure of the second data table is repaired, and step S605 to step S606 are re-executed until the write operation verification is successful.
Step S610: and performing read operation verification on the second data table, and reading data from the second data table and feeding back the data to the service layer.
Specifically, after the write operation of the second data table is verified to be successful, the read operation verification is performed on the second data table. Receiving a data reading instruction sent by a service layer, reading data required by the service layer in a second data table by an adaptation layer according to the instruction for reading the second data table sent by a configuration platform, converting the read data into data available to the service layer, and feeding back the converted data to the service layer, wherein the data available to the service layer is in a data format supported by the service layer, namely: facing the object.
Through the read operation verification, the data of the first data table and the data changed in the first data table during data transfer are determined to be transferred and synchronized to the second data table, namely, the data consistency of the second data table and the first data table is verified.
Step S611: and judging whether the read operation verification of the second data table is successful or not.
If the read operation fails to verify, the step S612 is continuously executed; if the read operation verification is successful, the process continues to step S616.
Step S612: and sending alarm information.
Specifically, sending alarm information, where a failure in the read operation verification means that data required by the service layer cannot be read from the second data table, and at this time, sending the alarm information, where the alarm information may specifically be sent to a monitoring device of a technician, such as a mobile phone or a computer.
Step S613: and receiving an instruction for stopping reading the second data table, and switching to reading the first data table, wherein the instruction for stopping reading the second data table is sent by the configuration platform.
Here, according to the received alarm information, a technician may operate on the configuration platform, send an instruction to the adaptation layer to stop reading the second data table, and switch to reading the first data table.
Step S614: and acquiring the changed data of the first data table, and synchronizing the changed data to the second data table to repair the data of the second data table.
Specifically, the changed data, which is the data missing in the second data table, is acquired from the database storing the changed data of the first data table, and is synchronized to the second data table to repair the data of the second data table so that the data of the second data table is consistent with the data of the first data table.
Step S615: and after the data of the second data table is repaired, repeatedly executing the step of performing read operation verification on the second data table until the read operation verification is successful.
Step S616: and if the read operation verification is successful, deleting the first data table to finish the data table merging and migrating.
The verification of the writing operation and the reading operation of the second data table is successful, and the correctness and the consistency of the data in the second data table can be better ensured. At this time, the first data table may be deleted, the dependency of the second data table on the first data table may be removed, and the second data table may independently provide data services, by which the data table merge migration may be completed.
Specifically, the first data table is deleted, the adaptation layer is deleted, the adaptation of the second data table is reserved as a unified interface, and the service layer and the database are bridged. The above operations can be implemented by the prior art, and the technical means for removing the dependency of the second data table on the first data table is the prior art and is not described herein again.
In summary, according to the scheme in the embodiment of the present invention, after the data transfer from the first data table to the second data table is completed, the first data table is a migration source data table, that is, an old data table, and the second data table is a migration target data table, that is, a new data table. The method comprises the steps of verifying the writing operation and the reading operation of the second data table, simultaneously keeping the writing operation on the first data table, namely, keeping the writing operation on the first data table while performing the writing operation and the reading operation on the second data table, simultaneously providing online data service by using the first data table and the second data table, improving the data verification effect and improving the service availability of the database and the use experience of users by a mode of verifying the data of the second data table in an actual online service state. Meanwhile, as the use of the first data table is not stopped, the first data table is in a state of normally updating data in the verification process, and when the verification of the second data table is in a problem, the first data table can be timely returned to be used so as to ensure the normal operation of the database system.
In addition, preferably, the verification of the write operation and the read operation of the second data table is continuously performed within a period of time, after the online data service of the database stably runs for a period of time, the first data table can be deleted, the dependence of the second data table on the first data table is removed, the second data table independently provides the data service, and the merging and migration of the data tables are completed. By verifying the running state of the second data table for a period of time, the correctness and consistency of data transferred to the new data table are further ensured, so that when the new data table independently provides data services, data problems caused by data migration can be avoided, the service availability of the database is ensured, and the use experience of users is improved.
Referring to fig. 8, fig. 8 is a schematic structural diagram of an apparatus 800 for merging and migrating data tables in a database according to some embodiments of the present invention, in which an apparatus 800 for merging and migrating data tables in a database according to an embodiment of the present invention includes:
a data transfer module 801, configured to obtain data of at least one first data table in a database, and transfer the data of the at least one first data table to a second data table, where the first data table is a migration source data table, and the second data table is a migration target data table;
a first verification module 802, configured to perform write operation verification on the second data table, and write data acquired from a service layer into the first data table and the second data table, respectively; if the write operation is verified successfully, continuing to execute;
a second verification module 803, configured to perform read operation verification on the second data table, and read data from the second data table and feed back the data to the service layer;
a deleting module 804, configured to delete the first data table if the read operation verification is successful, so as to complete data table merging migration.
In an embodiment of the present invention, a non-transitory computer readable storage medium may be further provided in the framework as shown in fig. 1 to 6, which may store instructions that, when executed by a processor, cause the processor to perform the steps of the method of merging and migrating data tables in a database as described in the previous embodiment.
Fig. 9 is a schematic structural diagram of an electronic device 900 according to some embodiments of the invention, as shown in fig. 9. In an embodiment of the present invention, an electronic device 900 includes a processor 901 and a memory 902; and one or at least one program stored in the memory 902 and configured to be executed by the processor 901, the one or at least one program comprising instructions for performing the methods of the above embodiments. The electronic device may be one or more computers so long as the computer-readable medium and the processor are included.
In the embodiments provided in the present invention, it should be understood that the disclosed related devices and methods can be implemented in other ways. For example, the above-described apparatus embodiments are merely illustrative, and for example, the division of the modules or units is only one logical division, and there may be other divisions when actually implemented, for example, at least one unit or component may be combined or integrated with another system, or some features may be omitted, or not executed. In addition, the shown or discussed mutual precise or direct or communication connection may be an indirect or communication connection through some interfaces, devices or units, and may be in an electrical, mechanical or other form. The units described as separate parts may or may not be physically separate, and parts displayed as units may or may not be physical units, may be located in one place, or may also be distributed on at least one network unit. Some or all of the units can be selected according to actual needs to achieve the purpose of the solution of the embodiment.
In addition, each functional unit in each embodiment of the present invention may be integrated into one processing unit, or each unit may exist alone physically, or two or more units may be integrated into one unit. The integrated unit can be realized in a form of hardware, and can also be realized in a form of a software functional unit. The integrated unit, if implemented in the form of a software functional unit and sold or used as a stand-alone product, may be stored in a computer readable storage medium. Based on such understanding, the technical solution of the present invention may be embodied in the form of a software product, which is stored in a storage medium and includes instructions for causing a computer processor (processor) to execute all or part of the steps of the method according to the embodiments of the present invention. The storage medium includes various media capable of storing program codes, such as a usb disk, a removable hard disk, a Read-Only Memory (ROM), a Random Access Memory (RAM), a magnetic disk, or an optical disk.
The above description is only exemplary of the present application and should not be taken as limiting the present application, as any modification, equivalent replacement, or improvement made within the spirit and principle of the present application should be included in the scope of protection of the present application.
Claims (11)
1. A method for merging and migrating data tables in a database, the method comprising the steps of:
acquiring data of at least one first data table in a database, and transferring the data of at least one first data table to a second data table, wherein the first data table is a migration source data table, and the second data table is a migration target data table;
performing write operation verification on the second data table, and writing data acquired from a service layer into the first data table and the second data table respectively; if the write operation is verified successfully, continuing to execute;
performing read operation verification on the second data table, and reading data from the second data table and feeding back the data to the service layer; and if the read operation verification is successful, deleting the first data table to finish the data table merging and migrating.
2. The method of claim 1, wherein after the obtaining of the data of at least one first data table in the database and the transferring of the data of at least one first data table to a second data table, the method further comprises:
acquiring changed data of the first data table, wherein the changed data is changed in the process of merging and migrating the data tables;
synchronizing the change data to the second data table.
3. The method according to claim 2, wherein the verifying the write operation on the second data table, and the writing the data acquired from the service layer into the first data table and the second data table respectively comprises:
the method comprises the steps that an adaptation layer acquires data from a service layer, the adaptation layer is packaged with a first data table adaptation and a second data table adaptation, the first data table is adapted to be a data access interface of the first data table, and the second data table is adapted to be a data access interface of the second data table;
according to the received data writing command for the second data table, the adaptation layer converts the acquired data into the available data of the database through adaptation of the second data table; sending the converted data to the second data table of the database and writing the converted data into the second data table, wherein the command for writing data into the second data table is sent by a configuration platform;
converting the acquired data into database available data through first data table adaptation; and sending the converted data to the corresponding first data table and writing the converted data into the corresponding first data table.
4. The method of claim 3, wherein the step of validating the read operation on the second data table, and reading data from the second data table and feeding back the data to the service layer comprises:
receiving a data reading instruction sent by a service layer;
according to a received instruction for reading a second data table, the adaptation layer adapts and reads data of the second data table through the second data table, and the instruction for reading the second data table is sent by the configuration platform;
converting the read data into data available for a service layer;
and feeding back the converted data to the service layer.
5. The method of claim 3, wherein the second data table is verified for a write operation, and wherein when the verification for the write operation fails, the method further comprises:
sending alarm information, and rolling back the first data table and the second data table to a correct state before the write operation fails respectively;
receiving a command of stopping writing data into a second data table, stopping writing data into the second data table, wherein the command of stopping writing data into the second data table is sent by a configuration platform;
and repairing the data structure of the second data table, and repeatedly executing the step of verifying the write operation of the second data table until the write operation is verified successfully.
6. The method of claim 4, wherein the verifying the read operation on the second data table, and when the verifying the read operation fails, the method further comprises:
sending alarm information;
receiving a command of stopping reading the second data table, and switching to reading the first data table, wherein the command of stopping reading the second data table is sent by a configuration platform;
acquiring the change data of the first data table, and synchronizing the change data to the second data table to repair the data of the second data table;
and after the data of the second data table is repaired, repeatedly executing the step of performing read operation verification on the second data table until the read operation verification is successful.
7. The method of claim 1, wherein the step of obtaining data of at least one first data table in the database and transferring the data of at least one first data table to a second data table further comprises:
and dividing the data ID fields of the second data table, wherein the data ID fields comprise the data ID field corresponding to each first data table and the corresponding data ID field of the changed data.
8. The method of claim 2, further comprising, prior to the step of obtaining change data for the first data table:
monitoring the change of the data of at least one first data table in the database;
the changed data is recorded and stored.
9. An apparatus for merging and migrating data tables in a database, the apparatus comprising:
the data transfer module is used for acquiring data of at least one first data table in a database and transferring the data of the at least one first data table to a second data table, wherein the first data table is a migration source data table, and the second data table is a migration target data table;
the first verification module is used for performing write operation verification on the second data table and respectively writing the data acquired from the service layer into the first data table and the second data table; if the write operation is verified successfully, continuing to execute;
the second verification module is used for performing read operation verification on the second data table and feeding back data read from the second data table to the service layer;
and the deleting module is used for deleting the first data table to finish the merging and migration of the data tables if the read operation verification is successful.
10. A non-transitory computer readable storage medium storing instructions that, when executed by a processor, cause the processor to perform the method of merging and migrating data tables in a database according to any one of claims 1 to 8.
11. An electronic device comprising a memory, a processor and a computer program stored on the memory and executable on the processor, wherein the processor implements the method for merging and migrating data tables in a database according to any one of claims 1 to 8 when executing the program.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201911050097.6A CN112749147A (en) | 2019-10-31 | 2019-10-31 | Method and device for merging and migrating data tables in database |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201911050097.6A CN112749147A (en) | 2019-10-31 | 2019-10-31 | Method and device for merging and migrating data tables in database |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN112749147A true CN112749147A (en) | 2021-05-04 |
Family
ID=75641208
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201911050097.6A Pending CN112749147A (en) | 2019-10-31 | 2019-10-31 | Method and device for merging and migrating data tables in database |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN112749147A (en) |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113704254A (en) * | 2021-07-30 | 2021-11-26 | 北京奇艺世纪科技有限公司 | Database service processing method and device, electronic equipment and storage medium |
| CN116991692A (en) * | 2023-09-27 | 2023-11-03 | 广东广宇科技发展有限公司 | Verification method based on database reading and writing |
-
2019
- 2019-10-31 CN CN201911050097.6A patent/CN112749147A/en active Pending
Cited By (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113704254A (en) * | 2021-07-30 | 2021-11-26 | 北京奇艺世纪科技有限公司 | Database service processing method and device, electronic equipment and storage medium |
| CN113704254B (en) * | 2021-07-30 | 2023-06-16 | 北京奇艺世纪科技有限公司 | Method and device for processing database business, electronic equipment and storage medium |
| CN116991692A (en) * | 2023-09-27 | 2023-11-03 | 广东广宇科技发展有限公司 | Verification method based on database reading and writing |
| CN116991692B (en) * | 2023-09-27 | 2024-02-09 | 广东广宇科技发展有限公司 | Verification method based on database reading and writing |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN108268501B (en) | Service processing method and device in online data migration process | |
| CN108228814B (en) | Data synchronization method and device | |
| EP2834759B1 (en) | Telemetry system for a cloud synchronization system | |
| WO2022063322A1 (en) | Hard drive migration method, distributed storage cluster system and storage medium | |
| CN108319617B (en) | Method and device for determining master-slave difference of database and switching control method and device | |
| CN103744906A (en) | System, method and device for data synchronization | |
| CN103164523A (en) | Inspection method, device and system of data consistency inspection | |
| CN104965879A (en) | Method and device for altering table structure of data table | |
| CN110781214A (en) | Database reading and writing method and device, computer equipment and storage medium | |
| CN106777126B (en) | Data online migration method supporting heterogeneous time sequence database | |
| CN103544057A (en) | Switching method and switching system for data service systems | |
| CN112749147A (en) | Method and device for merging and migrating data tables in database | |
| CN105138284A (en) | Virtual machine disk mirror image synchronization operation optimization system and method | |
| CN112328365A (en) | Virtual machine migration method, device, equipment and storage medium | |
| CN114218193A (en) | Data migration method and device, computer equipment and readable storage medium | |
| US20230252025A1 (en) | Data Processing System, Blockchain-Based Data Processing Method, and Device | |
| EP3316518B1 (en) | Method and device for upgrading virtual network element, and computer storage medium | |
| US10409787B1 (en) | Database migration | |
| CN112558868A (en) | Method, device and equipment for storing configuration data | |
| CN109116818B (en) | Real-time data dump method and device during SCADA system upgrade | |
| JP2014219868A (en) | Database migration method | |
| CN112204527A (en) | Method and system for processing engineering data in a multi-engineering system environment | |
| CN111209138A (en) | Operation and maintenance method and device of data storage system | |
| CN111090701B (en) | Service request processing method, device, readable storage medium and computer equipment | |
| CN103389919B (en) | A kind of data processing method based on redundant equipment system and device |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |