Data migration method and device across database types and storage medium

Abstract

The application discloses a cross-database-type data migration method and device and a storage medium, and comprises the following steps: reading the configuration of a source database and the configuration of a target database and performing connection initialization to connect two or more types of the source database and the target database; reading a migration configuration, querying the source database according to the migration configuration to obtain a data record list meeting a screening condition; and traversing the data record list to migrate each data record in the data record list to a target table of the target database. The application can connect different types of databases through connection initialization, and the migration configuration can realize cross-database-type data migration, thereby greatly improving the flexibility and efficiency of data migration in a database.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database management, and particularly relates to a cross-database-type data migration method and device and a storage medium. BACKGROUND

[0002] In some application software, as the types and quantities of user data are continuously increasing, multiple databases need to be used in parallel to store user data. For example, a relational database (MySql) and a distributed file storage database (MongoDB) are used simultaneously to store user data. In the process of user data management, user data of different databases needs to be migrated, that is, part of the user data is transferred from one database to another database. In the related art, database migration is mainly migration between databases of the same type according to table structures, but in actual business, multiple databases may be used, and data migration between two different types of databases may be involved. However, the prior art cannot efficiently and accurately perform data migration between different types of databases. SUMMARY

[0003] In view of the above problems, the present application provides a cross-database-type data migration method to solve the technical problem of low data migration efficiency only for the same type of database migration.

[0004] To achieve the above object, the inventors provide a cross-database-type data migration method, comprising the following steps:

[0005] reading information of a source database and information of a target database, the source database and the target database including two or more types of databases;

[0006] reading a migration configuration, and generating a query instruction according to the migration configuration;

[0007] querying data meeting a filtering condition in the source database according to the query instruction to obtain a data record list;

[0008] traversing the data record list to migrate each data record in the data record list to the target database.

[0009] Further, the migration configuration includes two or more configuration records, and the configuration record includes a source database, a source table name, a target database, a target table name, a unique identifier, and a filtering condition.

[0010] Further, the traversing the data record list to migrate each data record in the data record list to the target database includes:

[0011] Traverse the data record list, and determine whether the current data record is in the target table of the target database according to the unique identification code of the data. If not, the current data record is inserted into the target table. If yes, the corresponding data record in the target table is updated using the current data record.

[0012] Further, after the data record list is traversed to migrate each data record in the data record list to the target table of the target database, the method further comprises the step of:

[0013] According to the migration configuration, it is verified whether each data record in the data record list is migrated to the target database, and after the verification is correct, the data records corresponding to the data record list in the source database are deleted.

[0014] Further, before the data record list is traversed to migrate each data record in the data record list to the target table of the target database, the method further comprises the step of:

[0015] Determine whether the number of data records in the data record list is greater than a preset value. If yes, the data record list is divided into two or more groups of data records, and a corresponding processing thread is set for each group of data records.

[0016] Further, when the thread processes a preset number of data records, it stops for a preset time and then continues to process the remaining data records.

[0017] To solve the above technical problems, another technical solution is provided:

[0018] A cross-database type data migration device comprises:

[0019] An initialization module is configured to read information of a source database and information of a target database, wherein the source database and the target database comprise two or more types of databases;

[0020] A screening module is configured to read a migration configuration, generate a query instruction according to the migration configuration, and query data in the source database that meets a screening condition according to the query instruction to obtain a data record list;

[0021] A migration module is configured to traverse the data record list to migrate each data record in the data record list to a target table of the target database.

[0022] Further, the migration configuration comprises two or more configuration records, and the configuration record comprises a source database, a source table name, a target database, a target table name, a unique identification, and a screening condition.

[0023] Further, the application further comprises a checking module, configured to check whether each data record in the data record list is migrated to the target database according to the migration configuration, and delete the data record corresponding to the data record list in the source database after the checking is correct.

[0024] To solve the above technical problems, the application further provides another technical solution:

[0025] A computer readable storage medium, in which a computer program is stored, the program is executed by a processor to implement the cross-database type data migration method according to any one of the above technical solutions.

[0026] Different from the prior art, the above technical solution comprises generating a query instruction according to the migration configuration, and executing the query instruction to obtain data meeting the filtering condition, without migrating the data of the entire table; and by configuring different database connections, the cross-database type data migration can be realized, thereby greatly improving the migration efficiency of specific data in the database.

[0027] The above invention content is only a summary of the technical solution of the application. In order for those skilled in the art to more clearly understand the technical solution of the application, and then can be implemented according to the content of the description and the drawings, and in order for the above and other purposes, features and advantages of the application can be more easily understood, the following is described in conjunction with the specific embodiments of the application and the drawings. BRIEF DESCRIPTION OF DRAWINGS

[0028] The drawings are only used to show the principles, implementation manners, applications, characteristics and effects of the specific embodiments of the application and other related contents, and cannot be considered as a limitation of the application.

[0029] In the drawings of the specification:

[0030] Figure 1 The flow chart of the cross-database type data migration method described in the specific embodiments;

[0031] Figure 2 The schematic diagram of the migration configuration described in the specific embodiments;

[0032] Figure 3 The flow chart of the data record list migration described in the specific embodiments;

[0033] Figure 4 The module block diagram of the cross-database type data migration device described in the specific embodiments;

[0034] Figure 5 The schematic diagram of the computer readable storage medium described in the specific embodiments;

[0035] The reference signs mentioned in the above-mentioned figures are explained as follows:

[0036] 400, data migration device across database types; 500, computer readable storage medium; DETAILED DESCRIPTION

[0037] To make the possible application scenarios, technical principles, specific schemes that can be implemented, purposes and effects achieved, etc. of the present application clear, the following will be described in detail in combination with the specific embodiments listed and the accompanying drawings. The embodiments described herein are only used to more clearly illustrate the technical schemes of the present application, and therefore only serve as examples, but cannot limit the protection scope of the present application.

[0038] In this article, the term "embodiment" means that the specific features, structures or characteristics described in combination with the embodiment can be included in at least one embodiment of the present application. The term "embodiment" appearing at various positions in the specification does not necessarily refer to the same embodiment, and does not particularly limit the independence or association between other embodiments. In principle, in the present application, as long as there is no technical contradiction or conflict, each technical feature mentioned in each embodiment can be combined in any way to form a corresponding implementable technical scheme.

[0039] Unless otherwise defined, the meanings of the technical terms used herein are the same as those commonly understood by those skilled in the art to which the present application belongs; the use of related terms in this article is only for the purpose of describing specific embodiments, and is not intended to limit the present application.

[0040] In the description of the present application, the phrase "and / or" is a description of the logical relationship between the objects, which means that there can be three relationships, for example, A and / or B, which means that there are three cases: A exists, B exists, and A and B exist at the same time. In addition, the character " / " in this article generally represents that the associated objects before and after are a "or" logical relationship.

[0041] In the present application, the phrases such as "first" and "second" are only used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any actual quantity, primary and secondary or order relationship between the entities or operations.

[0042] In the present application, without more limitation, the "includes", "contains", "has" or other similar open expressions used in the sentence are intended to cover non-exclusive inclusion, and these expressions do not exclude the presence of other elements in the process, method or product including the described elements, so that the process, method or product including a series of elements can not only include those limited elements, but also include other elements not explicitly listed, or also include the elements inherent to such process, method or product.

[0043] As the same as the understanding in the "Examination Guidelines", in the present application, "greater than", "less than", "exceed" and the like are understood as not including the number; "above", "below", "within" and the like are understood as including the number. In addition, in the description of the embodiments of the present application, the meaning of "multiple" is two or more (including two), and similar expressions related to "multiple" are also understood in this way, for example, "multiple groups", "multiple times" and the like, unless otherwise explicitly and specifically limited.

[0044] In the description of the embodiments of the present application, the spatially related expressions used, such as "center", "longitudinal", "transverse", "length", "width", "thickness", "upper", "lower", "front", "rear", "left", "right", "vertical", "horizontal", "perpendicular", "top", "bottom", "inner", "outer", "clockwise", "counterclockwise", "axial", "radial", "circumferential", and the like, indicate the orientation or positional relationship based on the orientation or positional relationship shown in the specific embodiments or the drawings, and are only for the convenience of describing the specific embodiments of the present application or for the reader to understand, and do not indicate or imply that the indicated device or component must have a specific position, a specific orientation, or be constructed or operated in a specific orientation, and therefore cannot be understood as a limitation on the embodiments of the present application.

[0045] Unless otherwise explicitly specified or limited, in the description of the embodiments of the present application, the terms "mount", "connect", "connect", "fix", "set", and the like should be understood broadly. For example, the "connection" can be a fixed connection, or a detachable connection, or an integral setting; it can be a mechanical connection, or an electrical connection, or a communication connection; it can be directly connected, or indirectly connected through an intermediate medium; it can be the communication or interaction relationship between two elements. For those skilled in the art to which the present application belongs, the specific meaning of the above terms in the embodiments of the present application can be understood according to the specific circumstances.

[0046] In actual business, multiple databases can be used, and only a part of data meeting the screening conditions needs to be migrated according to specific business needs, such as screening and migrating records according to different merchant data and different conditions, which cannot be met. However, in the related art, the data in the database is limited to migration of the same type of database or migration of the entire database, so it is not possible to efficiently migrate specific data between different types of databases.

[0047] In view of the above technical problems, the present embodiment provides a cross-database type data migration method, which can screen data meeting the requirements from the source database according to the migration configuration, and can migrate the data to different types of target databases.

[0048] Please refer toFigure 1 A flowchart of a database type data migration method provided by the embodiment is shown. The cross-database type data migration method comprises the following steps:

[0049] S101, reading information of a source database and information of a target database, the source database and the target database comprising two or more types of databases;

[0050] S102, reading a migration configuration, and generating a query instruction according to the migration configuration;

[0051] S103, querying data meeting a filtering condition in the source database according to the query instruction, and obtaining a data record list;

[0052] S104, traversing the data record list to migrate each data record in the data record list to the target database.

[0053] In step S101, the source database and the target database can be from different types of databases, for example, the source database is a relational database (MySql), and the target database is a distributed file storage database (MongoDB). Of course, the embodiment is also applicable to the same type of database. The information of the source database and the information of the target database, also called the configuration of the source database and the configuration of the target database, specifically comprises: the account, password, database connection address, etc. of the source database and the target database. The information of the source database and the information of the target database can be obtained by the administrator of the database by pre-configuring the database. When obtaining the information of the source database and the information of the target database, the connection initialization of the source database and the target database can be performed. Illustratively, the connection initialization of the source database and the target database can be performed by registering a JDBC driver. For example: JAVA code connects JDBC, registers the driver Class.forName(“com.mysql.jdbc.Driver”), and then obtains the connection Connection conn=DriverManager.getConnection(“database connection address”, “account”, “password”), that is, the “connection address”, “account” and “password” of the database.

[0054] In step S102, the migration configuration can be configured by a database administrator according to management, and the migration configuration includes a source database, a target database, and a filtering condition. The filtering condition is used to filter data to be migrated in the source database. Since data in the database is usually stored in the form of a table, that is, each piece of data corresponds to a row in the storage table, and each row includes multiple columns, each column is used to record a feature or attribute of the data. Therefore, in this embodiment, the filtering condition includes a filtering condition column and a filtering condition value corresponding to the filtering condition column. For example, the filtering condition column is "age", and the filtering condition value is "XX years old". In some embodiments, the source database and the target database each include multiple tables, and therefore the migration configuration should also include a source table name and a target table name. Illustratively, the migration configuration includes: a source database, a source table name, a target database, a target table name, a unique identifier column, a filtering condition column, and a filtering condition value. The unique identifier column stores a unique identifier code corresponding to the data, and the unique identifier code corresponding to each piece of data in the same database is unique.

[0055] As shown in Figure 2 In some embodiments, the migration configuration includes two or more configuration records, and each configuration record includes the above-mentioned source database, source table name, target database, target table name, unique identifier column, filtering condition column, and filtering condition value. When performing data migration, each configuration record in the migration configuration can be read in sequence, and steps S103 and S104 can be executed in a loop to complete the task of each migration configuration. The filtering condition column and the filtering condition value are corresponding, one filtering condition column corresponds to one filtering condition value, the filtering condition value can be a specific numerical value, a numerical interval, or a non-numeric condition value such as gender, and multiple groups of filtering condition columns and corresponding filtering condition values can be configured in the configuration record, such as filtering condition column 1 and filtering condition value 1, filtering condition column value 2 and filtering condition value 2, and so on.

[0056] In step S103, a query statement is assembled from the "source database", "source table name", "filtering condition column", and "filtering condition value", and the query statement is executed to generate the query instruction. Illustratively, the query statement is: select * from source database.source table name where filtering condition column = filtering condition value. The filtering condition value is the value corresponding to the filtering condition column, for example, the filtering condition column is age, and the filtering condition value is "25". By assembling the query statement, the corresponding table record list of the source database is queried, that is, the corresponding data to be migrated is obtained, and the data to be migrated is recorded as a record list.

[0057] In the embodiment, the cross-database type data migration method can be applied to data migration between different types of sub-databases in the same database, that is, to implement in-database data migration, and can also be applied to data migration between different databases. The cross-database type data migration method can only migrate data meeting the screening condition, without migrating data of the entire table; and by configuring different database connections, data migration across different types of databases can be realized, thereby greatly improving the migration efficiency of specific data in the database.

[0058] As shown in FIG. 1, in one embodiment, in step S104, the data record list is traversed to migrate each data record in the data record list to the target database, including: Figure 3

[0059] S301, traversing the data record list, and judging whether the current data record has been in the target table of the target database according to the unique identification code of the data;

[0060] If not, step S302 is performed to insert the current data record into the target table;

[0061] If yes, step S303 is performed to update the corresponding data record in the target table using the current data record.

[0062] In step S301, whether each digital record exists in the target database is queried according to the unique identification code in the "target database", "target table name" and "unique identification column" of the target database. Illustratively, the query statement can be: select * from target database.target table name where unique identification column = record unique identification column value.

[0063] In the embodiment, it can be ensured that each data to be migrated can be migrated to the target table. In some embodiments, when the data is migrated to the target table, each migrated data migration record is also saved to the "migrated data record list". After the data migration is completed, data integrity check is performed, that is, whether each data to be migrated has been migrated to the target table is checked. In

[0064] After ensuring that the data has been accurately migrated to the target table, the corresponding migrated data in the migration source database is deleted according to the above "migrated data record list".

[0065] ​It should be noted that in step S101, the information of the source database and the information of the target database can be multiple, that is, there can be multiple source databases and multiple target databases, and the information of each source database and the information of the target database are connected respectively, so that different types of databases such as MySql, MongoDB, etc. can be connected.

[0066] In an embodiment, if the number of screened data to be migrated obtained in step S103 is large, in order to improve the data migration speed, batch processing can be performed multiple times. Therefore, before traversing the data record list to migrate each data record in the data record list to the target table of the target database, the method further comprises the steps of:

[0067] determining whether the number of data records in the data record list is greater than a preset value, if yes, dividing the data record list into two or more groups of data records, and setting a corresponding processing thread for each group of data records.

[0068] And in some embodiments, in order to reduce the pressure of the server during execution, the thread pauses for a preset time after processing a preset number of data records, and then continues to process the remaining data records.

[0069] Illustratively, the data to be migrated can be migrated in 3 threads at the same time, and each thread processes 500 data at a time and rests for 2 seconds.

[0070] As shown in Figure 4 In an embodiment, a cross-database type data migration device 400 is provided. The cross-database type data migration device 400 comprises an initialization module 401, a screening module 402 and a migration module 403.

[0071] The initialization module 401 is configured to read the information of the source database and the information of the target database, and the source database and the target database comprise two or more types of databases;

[0072] The screening module 402 reads the migration configuration, generates a query instruction according to the migration configuration, and queries the data meeting the screening condition in the source database according to the query instruction to obtain a data record list;

[0073] The migration module 403 is configured to traverse the data record list to migrate each data record in the data record list to the target table of the target database.

[0074] In this embodiment, the cross-database type data migration device 400 can generate query instructions according to the migration configuration and execute the query instructions to obtain data that meets the filtering conditions without migrating the entire table's data; and by configuring different database connections, it can realize data migration across different types of databases, thereby greatly improving the migration efficiency of specific data in the database.

[0075] The migration configuration includes two or more configuration records, each of which includes: source database, source table name, target database, target table name, unique identifier, and filtering conditions.

[0076] The migration configuration includes two or more configuration records. Each record includes the source database, source table name, target database, target table name, unique identifier column, filter condition column, and filter condition value. During data migration, each configuration record in the migration configuration can be read sequentially and the migration can be performed in a loop until the task of each migration configuration is completed. The filter condition column and filter condition value are corresponding; one filter condition column corresponds to one filter condition value. The filter condition value can be a specific numerical value, a numerical range, or a non-numeric condition value such as gender. Multiple groups of filter condition columns and corresponding filter condition values ​​can be configured in a record.

[0077] In some embodiments, the cross-database type data migration apparatus 400 further includes a verification module. The verification module is used to verify whether each data record in the data record list has been migrated to the target database according to the migration configuration, and deletes the data record corresponding to the data record list in the source database after verification is successful.

[0078] like Figure 5 As shown, in another embodiment, a computer-readable storage medium 500 is provided. The computer-readable storage medium 500 stores a computer program that, when executed by a processor, implements the data migration method across database types as described in any of the above embodiments.

[0079] Finally, it should be noted that although the above embodiments have been described in the text and drawings of this application, this should not limit the scope of patent protection of this application. Any technical solutions that are based on the essential concept of this application and utilize the content described in the text and drawings of this application, resulting in equivalent structural or procedural substitutions or modifications, as well as the direct or indirect application of the technical solutions of the above embodiments to other related technical fields, are all included within the scope of patent protection of this application.

Claims

1. A method for data migration across database types, the method comprising: The method comprises the following steps: reading information of a source database and information of a target database, the source database and the target database comprising two or more types of databases; reading a migration configuration, and generating a query instruction according to the migration configuration; querying data meeting a screening condition in the source database according to the query instruction to obtain a data record list; traversing the data record list to migrate each data record in the data record list to the target database; the migration configuration comprises two or more configuration records, and each configuration record comprises a source database, a source table name, a target database, a target table name, a unique identifier, and a screening condition; the traversing of the data record list to migrate each data record in the data record list to the target database comprises: traversing the data record list, determining whether a current data record has been in a target table of the target database according to a unique identifier of the data, and if not, inserting the current data record into the target table; or if yes, updating a corresponding data record in the target table using the current data record.

2. The method for data migration across database types according to claim 1, wherein, after the traversing of the data record list to migrate each data record in the data record list to the target table of the target database, the method further comprises the following step: verifying, according to the migration configuration, whether each data record in the data record list has been migrated to the target database, and deleting data records corresponding to the data record list in the source database after the verification is correct.

3. The method for data migration across database types as claimed in claim 1 wherein, before the traversing of the data record list to migrate each data record in the data record list to the target table of the target database, the method further comprises the following step: determining whether a number of data records in the data record list is greater than a preset value, and if yes, dividing the data record list into two or more groups of data records, and setting a corresponding processing thread for each group of data records.

4. The method for data migration across database types as claimed in claim 3 wherein, when each thread processes a preset number of data records, pausing for a preset time before continuing to process the remaining data records.

5. An apparatus for data migration across database types, the apparatus comprising: The method comprises the following steps: an initialization module for reading information of a source database and information of a target database, the source database and the target database comprising two or more types of databases; a screening module for reading a migration configuration, generating a query instruction according to the migration configuration, and querying data meeting a screening condition in the source database according to the query instruction to obtain a data record list; a migration module for traversing the data record list to migrate each data record in the data record list to a target table of the target database; the migration configuration comprises two or more configuration records, and each configuration record comprises a source database, a source table name, a target database, a target table name, a unique identifier, and a screening condition; the migration module is configured to traverse the data record list to migrate each data record in the data record list to the target database, and comprises: traversing the data record list, determining whether a current data record has been in a target table of the target database according to a unique identifier of the data, and if not, inserting the current data record into the target table; or if yes, updating a corresponding data record in the target table using the current data record. Traverse the data record list, and determine whether the current data record has been in the target table of the target database according to the unique identification code of the data. If not, the current data record is inserted into the target table. If yes, the corresponding data record in the target table is updated using the current data record.

6. The data migration apparatus across database types as claimed in claim 5, wherein, The method further comprises a checking module configured to check whether each data record in the data record list has been migrated to the target database according to the migration configuration, and delete the data records corresponding to the data record list in the source database after the checking is correct.

7. A computer readable storage medium having stored therein a computer program, characterized in that, The program is executed by the processor to implement the cross-database type data migration method according to any one of claims 1 to 4.

Images