A cross-data-source data migration method and system

Abstract

The application provides a cross-data-source data migration method and system. The method comprises the following steps: configuring a plurality of data source information for data migration; formulating a migration rule engine according to the data source information; generating a bash script and a plurality of executable files according to the migration rule engine; the executable files correspond to the data sources to be migrated one by one; when the bash script is executed, the corresponding executable files are called to generate an INSERT script; and the data migration is completed by executing the INSERT script. The method can customize the migration rule engine, thereby realizing a multi-source heterogeneous integration mode, dynamically generating a data migration script, transferring source data into a target database, and helping an enterprise / bank and other financial institutions to realize dynamic migration of multi-source data, and solving the problems of high data migration cost, long migration time and low data source compatibility in the traditional mode.

Description

Technical Field

[0001] This invention belongs to the field of database technology, specifically relating to a method and system for cross-data source data migration. Background Technology

[0002] Currently, in internet payment scenarios and big data systems, most projects adopt a distributed microservice architecture, with each system split into microservices and utilizing multiple data sources and database sharding. However, when cross-system data migration occurs, traditional views, functions, and temporary database table storage can no longer meet the migration requirements, posing significant challenges to the querying and maintenance of large volumes of historical data across databases and tables, and traditional methods are also costly. Summary of the Invention

[0003] To address the shortcomings of existing technologies, this invention provides a cross-data source data migration method and system, solving the problem of excessively high data migration costs in traditional models.

[0004] Firstly, a cross-data source data migration method includes:

[0005] Configure information on multiple data sources for data migration;

[0006] The migration rule engine is designed based on data source information;

[0007] The migration rule engine generates a bash script and multiple executable files; each executable file corresponds one-to-one with the data source being migrated out.

[0008] When a bash script is executed, it calls the corresponding executable file and generates an INSERT script;

[0009] Execute the INSERT script to complete the data migration.

[0010] Furthermore, the specific information for configuring multiple data sources for data migration includes:

[0011] Configure data source information; data source information includes the outgoing data source and the incoming data source.

[0012] Establish the association between fields in the source data source and fields in the destination data source to obtain the data migration association;

[0013] Store the data migration relationships in a temporary database table.

[0014] Furthermore, the migration rule engine, based on data source information, specifically includes:

[0015] Migration decisions are made based on data source information; migration decisions include data source connection information, database query statements for migrating out of the data source, and database insert statements for migrating into the data source.

[0016] The migration rule engine is developed based on migration decisions.

[0017] Furthermore, storing the data migration relationships in a temporary database table specifically includes:

[0018] If no data migration relationship exists in the temporary database table, create a temporary data migration relationship in the temporary database table.

[0019] Furthermore, the execution of a bash script specifically includes:

[0020] Generate the INSERT prefix based on the imported data source;

[0021] Read the executable file corresponding to the migrated data source;

[0022] The migration rules engine is queried based on the executable file to obtain the migration data from the source data source;

[0023] Use a field as the storage ID for the migrated data;

[0024] If the temporary database table does not have a storage ID, create a new storage ID in the temporary database table and store the migrated data under the corresponding storage ID in the temporary database table.

[0025] When a storage ID exists in the temporary database table, update the migration data under the storage ID in the temporary database table.

[0026] Read the data migrated from the temporary database table and assemble it with the INSERT prefix to obtain the INSERT script.

[0027] Secondly, a cross-data source data migration system includes:

[0028] Configuration unit: Used to configure information about multiple data sources for data migration;

[0029] Engine Generator: An engine used to formulate migration rules based on data source information;

[0030] First script generator: used to generate bash scripts and multiple executable files based on the migration rule engine; each executable file corresponds one-to-one with the data source being migrated out.

[0031] Second script generator: used to call the corresponding executable file and generate INSERT scripts when a bash script is executed;

[0032] Execution Unit: Used to execute INSERT scripts and complete data migration.

[0033] Furthermore, the configuration unit is specifically used for:

[0034] Configure data source information; data source information includes the outgoing data source and the incoming data source.

[0035] Establish the association between fields in the source data source and fields in the destination data source to obtain the data migration association;

[0036] Store the data migration relationships in a temporary database table.

[0037] Furthermore, the engine generator is specifically used for:

[0038] Migration decisions are made based on data source information; migration decisions include data source connection information, database query statements for migrating out of the data source, and database insert statements for migrating into the data source.

[0039] The migration rule engine is developed based on migration decisions.

[0040] Furthermore, the engine generator is specifically used for:

[0041] If no data migration relationship exists in the temporary database table, create a temporary data migration relationship in the temporary database table.

[0042] Furthermore, the second script generator is specifically used for:

[0043] When the bash script is executed, an INSERT prefix is ​​generated based on the imported data source;

[0044] Read the executable file corresponding to the migrated data source;

[0045] The migration rules engine is queried based on the executable file to obtain the migration data from the source data source;

[0046] Use a field as the storage ID for the migrated data;

[0047] If the temporary database table does not have a storage ID, create a new storage ID in the temporary database table and store the migrated data under the corresponding storage ID in the temporary database table.

[0048] When a storage ID exists in the temporary database table, update the migration data under the storage ID in the temporary database table.

[0049] Read the data migrated from the temporary database table and assemble it with the INSERT prefix to obtain the INSERT script.

[0050] As can be seen from the above technical solutions, the cross-data source data migration method and system provided by the present invention can customize the migration rule engine, thereby realizing the integration mode of multi-source heterogeneity, dynamically generating data migration scripts, transferring source data into the target database, helping enterprise / bank and other financial institutions to realize dynamic migration of multi-source data, and solving the problems of high data migration cost, long migration time and low data source compatibility in the traditional mode. Attached Figure Description

[0051] To more clearly illustrate the specific embodiments of the present invention or the technical solutions in the prior art, the accompanying drawings used in the description of the specific embodiments or the prior art will be briefly introduced below. In all the drawings, similar elements or parts are generally identified by similar reference numerals. In the drawings, the elements or parts are not necessarily drawn to scale.

[0052] Figure 1 A flowchart of a cross-data source data migration method provided for an embodiment.

[0053] Figure 2 A flowchart illustrating the data source information configuration method provided in this embodiment.

[0054] Figure 3 A flowchart of a migration rule engine formulation method provided for an embodiment.

[0055] Figure 4 A flowchart of a bash script execution method provided for an embodiment.

[0056] Figure 5 A block diagram of a cross-data source data migration system provided in this embodiment. Detailed Implementation

[0057] The embodiments of the technical solution of the present invention will now be described in detail with reference to the accompanying drawings. The following embodiments are only used to more clearly illustrate the technical solution of the present invention, and are therefore merely examples and should not be used to limit the scope of protection of the present invention. It should be noted that, unless otherwise stated, the technical or scientific terms used in this application should have the ordinary meaning understood by those skilled in the art to which this invention pertains.

[0058] It should be understood that, when used in this specification and the appended claims, the terms "comprising" and "including" indicate the presence of the described features, integrals, steps, operations, elements and / or components, but do not exclude the presence or addition of one or more other features, integrals, steps, operations, elements, components and / or collections thereof.

[0059] It should also be understood that the terminology used in this specification is for the purpose of describing particular embodiments only and is not intended to limit the invention. As used in this specification and the appended claims, the singular forms “a,” “an,” and “the” are intended to include the plural forms unless the context clearly indicates otherwise.

[0060] As used in this specification and the appended claims, the term "if" may be interpreted, depending on the context, as "when," "once," "in response to determination," or "in response to detection." Similarly, the phrases "if determined" or "if [described condition or event] is detected" may be interpreted, depending on the context, as "once determined," "in response to determination," "once [described condition or event] is detected," or "in response to detection of [described condition or event]."

[0061] Example:

[0062] A cross-data source data migration method, see [link to relevant documentation] Figure 1 ,include:

[0063] S1: Configure multiple data source information for data migration;

[0064] S2: Engine for defining migration rules based on data source information;

[0065] S3: Generates a bash script and multiple executable files based on the migration rule engine; each executable file corresponds one-to-one with the data source being migrated out.

[0066] S4: When a bash script is executed, it calls the corresponding executable file and generates an INSERT script;

[0067] S5: Execute the INSERT script to complete the data migration.

[0068] In this embodiment, the method first specifies multiple data source information based on the user's business plan, and then generates a migration rule engine based on this information. For example, the method can specify the migration rule engine by configuring fields of the outgoing data source, fields of the incoming data source, and the relationships between these fields. Next, a bash script and multiple executable files are generated based on the migration rule engine. For instance, the executable file corresponding to the outgoing data source is dynamically generated based on the migration rule engine; the executable file can be an SQL file, a TXT file, etc. A bash script is also dynamically generated based on the migration rule engine.

[0069] In this embodiment, the method obtains the incoming data source through a migration rule engine, generates a bash script based on the connection information of the incoming data source, the execution statements of the executable file, and the instructions to clear the temporary database table, and stores the script address of the bash script in the temporary database table. When the bash script is executed, the script address in the temporary database table is triggered. This method can be written in Java to implement the bash script execution function. For example, the script address of the bash script can be obtained through Runtime.getRuntime().exec(command) to trigger the execution of the bash script. After the data migration is completed, the temporary database table and the corresponding data are deleted.

[0070] This method allows for a customizable migration rule engine, enabling the integration of multi-source heterogeneous systems. It dynamically generates data migration scripts to transfer source data into the target database, helping enterprise / bank and other financial institutions achieve dynamic migration of multi-source data and solving the problems of high data migration costs, long migration times, and low data source compatibility in traditional models.

[0071] Furthermore, in some embodiments, see Figure 2 The specific information for configuring multiple data sources for data migration includes:

[0072] S11: Configure data source information; data source information includes the outgoing data source and the incoming data source;

[0073] S12: Establish the association between fields in the source data source and fields in the destination data source to obtain the data migration association;

[0074] S13: Store the data migration relationship in a temporary database table.

[0075] In this embodiment, the method can set data source information based on a web interface. For example, the outgoing database table information, the incoming database table information, and migration parameters can be configured via the web interface. When configuring migration parameters, the type of the outgoing data source (Oracle / MySQL / SqlServer / DB2...) and the corresponding data parameters can be configured. After configuring the data source information, the method establishes the association relationship between the fields in the outgoing and incoming data sources to obtain the data migration association relationship, and stores the data migration association relationship. For example, the data migration association relationship can be stored in a temporary database table, and a one-to-one association mapping can be performed between the outgoing and incoming fields. For example, assuming that the outgoing data source includes Mysql.TB_TABLE 1 and Oracle.TB_TABLE 2, and the incoming data source includes SqlServer.TR_TABLE, the established data migration association relationship can be: Mysql.TB_TABLE1.COLUMN1 = SqlServer.TR_TABLE.COLUMN1; Oracle.TB_TABLE 2.COLUMN1 = SqlServer.TR_TABLE..COLUMN2, ...

[0076] Furthermore, in some embodiments, see Figure 3 The migration rule engine, which formulates migration rules based on data source information, specifically includes:

[0077] S21: Determine migration decisions based on data source information; migration decisions include data source connection information, database query statements for migrating out of the data source, and database insert statements for migrating into the data source;

[0078] S22: Engine for developing migration rules based on migration decisions.

[0079] In this embodiment, the method can dynamically generate a migration rule engine based on data migration relationships. The method first sets up connection information for multiple data sources, storing the connection information for each data source in a cache. For example, the DB Connect process for each data source is stored in a temporary database table for subsequent Bash script output. For instance, the data source connection information is represented as `mysql -u$USER$DATABASE<`.<EOF 2> / dev / null. Next, this method configures and stores the database query statements for each migrated data source, Mysql.TB_TABLE1.COLUMN1, for later Bash script output. For example, the database query statements for the migrated data source are represented as `select COLUMN1 from(Mysql).TB_TABLE1; select COLUMN2 from Oracle.TB_TABLE2`. This method also configures the database insert statements for the migrated data source, storing these statements in a temporary table for later executable file generation.

[0080] Furthermore, in some embodiments, storing the data migration relationships in a temporary database table specifically includes:

[0081] If no data migration relationship exists in the temporary database table, create a temporary data migration relationship in the temporary database table.

[0082] In this embodiment, before storing the data migration relationship, the method also needs to determine whether the data migration relationship exists in the temporary database table. If it does not exist, the data migration relationship is created.

[0083] Furthermore, in some embodiments, see Figure 4 When a bash script is executed, it specifically includes:

[0084] S31: Generate the INSERT prefix based on the imported data source;

[0085] S32: Read the executable file corresponding to the migrated data source;

[0086] S33: Query the migration rule engine based on the executable file to obtain the migration data of the data source from which the data is migrated;

[0087] S34: Use the field as the storage ID for the migrated data;

[0088] S35: When the temporary database table does not have a storage ID, create a new storage ID in the temporary database table and store the migrated data under the corresponding storage ID in the temporary database table;

[0089] S36: When a storage ID exists in the temporary database table, update the data to be migrated out under the storage ID in the temporary database table;

[0090] S37: Read the data migrated from the temporary database table and assemble it with the INSERT prefix to obtain the INSERT script.

[0091] In this embodiment, the method can be based on an executable file written in Java. The method first uses the executable file corresponding to the outgoing data source and the migration rule engine to obtain the outgoing data, for example, by querying the migration rule engine to obtain information such as the type, IP, and port of the outgoing data source. The method first generates an INSERT prefix (INSERT INTO(TARGET)VALUE) based on the incoming data source, then uses the migration rule engine to query by type to obtain the outgoing data. The outgoing data is stored in a cache as a field used as a storage ID (e.g., KeyId), for example, in Redis, Memcache / MAP. When storing the storage ID, the method needs to check if the storage ID exists; if the storage ID does not exist, a new storage ID is created in a temporary database table, and the corresponding outgoing data is stored under the storage ID in the temporary database table. The outgoing data can be a value, for example, the COLUMN information from the outgoing database table is split and stored in the cache. If the storage ID already exists, update the migration data under the storage ID in the temporary database table. When updating the migration data, the original migration data for the storage ID can be retrieved from the temporary database table, and the current migration data is added to the original migration data. This iterative process completes the recording of the migration data. When it is necessary to retrieve migration data, this method can use the temporary database table to retrieve the complete migration data based on the storage ID, combine the retrieved migration data with the INSERT prefix, and obtain the complete INSERT script. Finally, the records in the temporary database table can be cleared to release disk space.

[0092] A cross-data source data migration system, see Figure 5 ,include:

[0093] Configuration Unit 1: Used to configure information about multiple data sources for data migration;

[0094] Engine Generator 2: An engine used to formulate migration rules based on data source information;

[0095] First script generator 3: Used to generate bash scripts and multiple executable files based on the migration rule engine; each executable file corresponds one-to-one with the data source being migrated out.

[0096] Second script generator 4: Used to call the corresponding executable file and generate INSERT scripts when a bash script is executed;

[0097] Execution Unit 5: Used to execute INSERT scripts to complete data migration.

[0098] Furthermore, in some embodiments, configuration unit 1 is specifically used for:

[0099] Configure data source information; data source information includes the outgoing data source and the incoming data source.

[0100] Establish the association between fields in the source data source and fields in the destination data source to obtain the data migration association;

[0101] Store the data migration relationships in a temporary database table.

[0102] Furthermore, in some embodiments, engine generator 2 is specifically used for:

[0103] Migration decisions are made based on data source information; migration decisions include data source connection information, database query statements for migrating out of the data source, and database insert statements for migrating into the data source.

[0104] The migration rule engine is developed based on migration decisions.

[0105] Furthermore, in some embodiments, engine generator 2 is also used for:

[0106] If no data migration relationship exists in the temporary database table, create a temporary data migration relationship in the temporary database table.

[0107] Furthermore, in some embodiments, the second script generator 4 is specifically used for:

[0108] When the bash script is executed, an INSERT prefix is ​​generated based on the imported data source;

[0109] Read the executable file corresponding to the migrated data source;

[0110] The migration rules engine is queried based on the executable file to obtain the migration data from the source data source;

[0111] Use a field as the storage ID for the migrated data;

[0112] If the temporary database table does not have a storage ID, create a new storage ID in the temporary database table and store the migrated data under the corresponding storage ID in the temporary database table.

[0113] When a storage ID exists in the temporary database table, update the migration data under the storage ID in the temporary database table.

[0114] Read the data migrated from the temporary database table and assemble it with the INSERT prefix to obtain the INSERT script.

[0115] The system provided in this embodiment of the invention is described in a brief manner. For any parts not mentioned in the embodiment section, please refer to the corresponding content in the foregoing embodiment.

[0116] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, and not to limit them. Although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some or all of the technical features therein. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present invention, and they should all be covered within the scope of the claims and specification of the present invention.

Claims

1. A method for cross-data-source data migration, the method comprising: include: Configure information on multiple data sources for data migration; A migration rule engine is developed based on the data source information; The migration rule engine generates a bash script and multiple executable files; each executable file corresponds one-to-one with the data source being migrated out. When the bash script is executed, it calls the corresponding executable file to generate an INSERT script; Execute the INSERT script to complete the data migration; The configuration of multiple data source information for data migration specifically includes: configuring the data source information; the data source information includes the outgoing data source and the incoming data source; establishing the association relationship between fields in the outgoing data source and fields in the incoming data source to obtain the data migration association relationship; and storing the data migration association relationship in a temporary database table. The step of formulating the migration rule engine based on the data source information specifically includes: determining a migration decision based on the data source information; the migration decision includes the connection information of the data source, the database query statement of the data source to be migrated out, and the database insertion statement of the data source to be migrated into; and formulating the migration rule engine based on the migration decision. The step of storing the data migration association in a temporary database table specifically includes: when the data migration association does not exist in the temporary database table, creating a temporary data migration association in the temporary database table; When the bash script is executed, it specifically includes: generating an INSERT prefix based on the incoming data source; reading the executable file corresponding to the outgoing data source; querying the migration rule engine based on the executable file to obtain the outgoing data of the outgoing data source; using the outgoing data as a storage ID with a field; when the storage ID does not exist in the temporary database table, creating the storage ID in the temporary database table and storing the outgoing data under the storage ID in the temporary database table accordingly; when the storage ID exists in the temporary database table, updating the outgoing data under the storage ID in the temporary database table; reading the outgoing data in the temporary database table and assembling it with the INSERT prefix to obtain the INSERT script.

2. A cross-data-source data migration system, comprising: include: Configuration unit: Used to configure information about multiple data sources for data migration; Engine Generator: Used to formulate migration rule engines based on the data source information; First script generator: used to generate bash scripts and multiple executable files based on the migration rule engine; Each executable file corresponds one-to-one with the data source being migrated out. Second script generator: used to call the corresponding executable file to generate INSERT script when the bash script is executed; Execution unit: Used to execute the INSERT script and complete the data migration; The configuration unit is specifically used for: configuring the data source information; The data source information includes the outgoing data source and the incoming data source; establish the association relationship between the fields in the outgoing data source and the fields in the incoming data source to obtain the data migration association relationship; store the data migration association relationship in a temporary database table; The engine generator is specifically used to: determine migration decisions based on the data source information; the migration decisions include connection information of the data source, database query statements of the data source to be migrated out, and database insertion statements of the data source to be migrated into; and formulate the migration rule engine based on the migration decisions. The engine generator is specifically used to: create a temporary data migration relationship in the temporary database table when the data migration relationship does not exist in the temporary database table; The second script generator is specifically used to: generate an INSERT prefix based on the imported data source when the bash script is executed; Read the executable file corresponding to the migration data source; query the migration rule engine based on the executable file to obtain the migration data of the migration data source; The migrated data is stored using a field as an ID; When the storage ID does not exist in the temporary database table, the storage ID is created in the temporary database table, and the migrated data is stored under the storage ID in the temporary database table. When the storage ID exists in the temporary database table, update the migration data under the storage ID in the temporary database table; The data to be migrated out of the temporary database table is read and assembled with the INSERT prefix to obtain the INSERT script.

Images