CN111680024A - Universal heterogeneous database data migration method - Google Patents

Universal heterogeneous database data migration method Download PDF

Info

Publication number
CN111680024A
CN111680024A CN202010534252.8A CN202010534252A CN111680024A CN 111680024 A CN111680024 A CN 111680024A CN 202010534252 A CN202010534252 A CN 202010534252A CN 111680024 A CN111680024 A CN 111680024A
Authority
CN
China
Prior art keywords
database
data
source
heterogeneous
target
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
Application number
CN202010534252.8A
Other languages
Chinese (zh)
Inventor
张记强
韩世杰
陈树峰
冯帆
王仁
李新乐
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
Beijing Institute of Computer Technology and Applications
Original Assignee
Beijing Institute of Computer Technology and Applications
Priority date (The priority date 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 date listed.)
Filing date
Publication date
Application filed by Beijing Institute of Computer Technology and Applications filed Critical Beijing Institute of Computer Technology and Applications
Priority to CN202010534252.8A priority Critical patent/CN111680024A/en
Publication of CN111680024A publication Critical patent/CN111680024A/en
Pending legal-status Critical Current

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/28—Databases characterised by their database models, e.g. relational or object models

Abstract

The invention discloses a universal heterogeneous database data migration method, which comprises the following steps: step 1: registering a data source; step 2: acquiring a database structure; and step 3: selecting a source database and a target database; and 4, step 4: using a SELECT statement to inquire whether a target database name exists in a database table in the SQLite database, and if not, creating a target database with a structure completely consistent with that of the source database; and 5: selecting a data type mapping rule; step 6: extracting database data from a source database; and 7: judging whether data is backed up or not, and if so, generating an XML file; and 8: writing each row of data extracted in the step 7 into a target database; and step 9: and writing the data migration log into a migration log table of the SQLite database. The data migration method of the universal heterogeneous database is suitable for common databases in the market, such as Oracle, SQLServer, Mysql, postgre, Dameng and gold storehouse, and can effectively complete data migration.

Description

Universal heterogeneous database data migration method
Technical Field
The invention relates to the technical field of data storage, in particular to a universal heterogeneous database data migration method.
Background
In the field of database management systems, a plurality of mature database products (such as Oracle, SQLServer, Mysql, Postgre, wuhanda dream, big treasury, china general purpose, etc.) already exist, and in a plurality of application systems built by enterprises, due to different application development departments or factors such as characteristics of the application systems, database systems used by different systems are different, and data in the heterogeneous databases cannot be merged together. In order to solve the problem of data fusion between heterogeneous databases, data needs to be migrated from one heterogeneous database to another heterogeneous database. Currently, data migration tools provided by various database manufacturers are only suitable for migrating between a self database and other specific databases, for example, a dreams database provides a data migration tool between a dreams database and an Oracle database, and the tool cannot complete data migration between the dreams database and a Mysql or Postgre database, so that the tool does not have universal applicability.
At this time, a general heterogeneous database data migration method is needed to satisfy the requirement of fast and effective migration of data between the mature heterogeneous database products.
Chinese patent 'CN 104598531B is a trigger-based incremental data migration method between heterogeneous relational databases', which records effective general sql operation logs on existing multivariate heterogeneous database data through a unified virtual table trigger, and synchronously or asynchronously executes the sql operation logs to a target heterogeneous database table, thereby shielding physical details and language and grammar differences of the databases. The method solves the problems of simplicity, convenience and easiness in operation of mutual migration of database table data of the heterogeneous relational database, and can be used for data processing of the heterogeneous relational database.
The chinese patent CN 107357866 a is a method for the localization migration of middleware and databases of an information system of an electric power enterprise, and data in a non-homemade database is synchronized to a homemade database by using a DMHS tool.
The chinese patent CN 106570086 a data migration system and data migration method overcomes the defects that the whole database can only be migrated once and the migrated data is easy to be lost in the prior art, and provides a data migration system and a data migration method.
Chinese patent "CN 104598531B is a method for migrating incremental data between heterogeneous relational databases based on a trigger", in which a trigger is created on a heterogeneous relational database to obtain changes of the database, and the solved incremental data migration of the database cannot migrate existing historical data in the database, and does not solve the situation that a target database does not exist, and to solve these problems, extra work of an operator will be undoubtedly added.
The Chinese patent CN 107357866A is a domestic migration method of middleware and database of an information system of an electric power enterprise, which utilizes a DMHS tool to synchronize data in a non-domestic database to the domestic database, solves the problem of migration of an Oracle database to a dream database, and only aims at the condition that a target database is the dream database, but has no universality.
The chinese patent CN 106570086 a data migration system and method needs to design a migration intermediate table in a source database, and set a migration intermediate library in a target database, so as to increase the workload of operators.
Disclosure of Invention
The present invention is directed to provide a general heterogeneous database data migration method, which is used to solve the above-mentioned problems in the prior art.
The invention discloses a universal heterogeneous database data migration method, which comprises the following steps: step 1: registering a data source; step 2: acquiring a database structure; and step 3: selecting a source database and a target database; and 4, step 4: using a SELECT statement to inquire whether a target database name exists in a database table in the SQLite database, and if not, creating a target database with a structure completely consistent with that of the source database; and 5: selecting a data type mapping rule; step 6: extracting database data from a source database; and 7: judging whether data is backed up or not, and if so, generating an XML file; and 8: writing each row of data extracted in the step 7 into a target database; and step 9: and writing the data migration log into a migration log table of the SQLite database.
According to an embodiment of the universal heterogeneous database data migration method of the present invention, registering the data source includes: and loading the ODBC driver provided by the database, and then writing the registration information into the local SQLite database.
According to an embodiment of the general heterogeneous database data migration method of the present invention, the obtaining the database structure includes: and executing SQL statements of the query database structure, and writing the SQL statements into a local SQLite database.
According to an embodiment of the universal heterogeneous database data migration method of the present invention, in the step of selecting the source database and the target database, an interface is provided for selecting the heterogeneous data sources that have been registered as the source database and the target database.
According to an embodiment of the general heterogeneous database data migration method, a SELECT statement is used for inquiring whether a target database name exists in a database table in an SQLite database, and if the target database name does not exist in the database table, a target database with a structure completely consistent with that of a source database is created; if the query result is not null, indicating that the target database exists, otherwise, indicating that the target database does not exist, and creating the target database with the structure completely consistent with that of the source database;
according to an embodiment of the universal heterogeneous database data migration method of the present invention, step 5: selecting a data type mapping rule; this step provides type conversion rules between heterogeneous databases and assigns a unique mapping rule name to each conversion rule.
According to an embodiment of the universal data migration method for the heterogeneous database, in the step 6, data is acquired by using a QsqlQuery interface, and data in one row is acquired by using the QsqlQuery interface and data in the next row.
According to an embodiment of the universal heterogeneous database data migration method of the present invention, after each piece of data is extracted, an INSERT statement is executed on the target database to be written into the target database.
According to an embodiment of the general heterogeneous database data migration method, the writing of the contents into the migration log table of the SQLite database includes: migration time, whether migration is successful, whether backup is performed, and the number of data migration records.
The invention provides a universal heterogeneous database data migration method, which can be well adapted to the mature database products on the market at present and has universal applicability.
Drawings
FIG. 1 is a flow diagram of a heterogeneous database data migration method;
FIG. 2 is a table structure diagram for storing data source registration information;
FIG. 3 is a block diagram of a database table of SQLite;
FIG. 4 is a block diagram of a data table of SQLite;
fig. 5 is a structural diagram of a data field table of SQLite.
Detailed Description
In order to make the objects, contents, and advantages of the present invention clearer, the following detailed description of the embodiments of the present invention will be made in conjunction with the accompanying drawings and examples.
Fig. 1 is a flowchart of a data migration method for a heterogeneous database, and as shown in fig. 1, the steps included in the data migration method for a general heterogeneous database according to the present invention will be described in detail below.
In order to accelerate the system operation efficiency, the SQLite database is used for storing the structure of the heterogeneous database and the migration log information.
Step 1: and (4) registering a data source.
In the first step, the heterogeneous database is used as a data source and is registered in the system, and the support of database information is provided for subsequent data migration.
The data source registration is divided into two steps, in the first step, the database is connected through loading ODBC drive provided by the database, and QsqlDatabase is called to load the Oracle database ODBC under the Qt environment: QSqlDatabase:addDatabase ("QOCI", m _ DataSourceInfo. strname);
and writing the registration information into the SQLite database in the second step. The information required for data source registration includes data source type, registration name, IP address, port number, DNS information, default connection database name, user name and password. Fig. 2 is a table structure diagram for storing data source registration information, the table name is "data source table" (T _ connection nfo), and the statements written in the table data correspond to: "INSERT inton" - 'T _ connecting nfo' VALUES (NULL, '% 1', '% 2', '% 3', '% 4', '% 5', '% 6', '% 7', '% 8', '% 9'); ") arg (info.type), arg (info.strname), arg (info.strip), arg (info.strport), arg (info.strdef databasename), arg (info.strusername), arg (info.strpassd). Wherein info is a database type, and the structure stores parameter information of a user registration data source, defined as:
Figure BDA0002534994830000051
step 2: and acquiring a database structure.
The structure of the database in the data source is obtained in the step, the purpose is that the source database and the target database can be directly selected during data migration, and the probability of writing errors of a user can be reduced by selecting the data source.
The structure of the acquisition database is divided into three parts: database information, database internal table information and table internal field information.
(1) First, data source database information is obtained and written into a "database table" (T _ database info) of SQLite, and the structure of the table is shown in fig. 3.
And (3) after the data source is connected in the step (1), executing the SQL sentence for acquiring the database information, and acquiring the database information of the data source. Taking an Oracle database as an example, the way to obtain the data source database information under Qt is QsqlQuery:: exec ("SELECT" FROM USER _ USERS "), and QsqlQuery:: next to obtain the result set.
By executing the SQL statement: "INSERT INTO ' main ' ` T _ DATABASEINFO ' VALUES (NULL, '% 1', '% 2 '); ", (strdes),. arg (strdatasource), the acquired database information is written into the T _ databaseeinfo table.
(2) The information of the table is then obtained and written to the "data table" (T _ table info) of SQLite, the structure of which is shown in fig. 4.
And acquiring the information of the table in the data source by executing the SQL sentence for acquiring the table information. Taking an Oracle database as an example, the way of acquiring the information of the data source inner table under Qt is as follows: QsqlQuery:: exec ("SELECT FROM USER _ TABLES"), and a result set is obtained using QsqlQuery:: next.
By executing the SQL statement: "INSERT INTO ' main '. T _ tagelinfo ' VALUES (NULL, '% 1', '% 2 '); ", arg (strtable),. arg (strdatabase), the acquired table information is written into the T _ tabeinfo table.
(3) Finally, the field information is obtained and written into "data field table" (T _ fieldinuo) of SQLite, and the structure of the table is shown in fig. 5.
And acquiring the field information in the data source by executing the SQL statement for acquiring the field information. Taking an Oracle database as an example, the manner of acquiring the field information in the data source under Qt is as follows: QsqlQuery: "exec (" SELECT COLUMN _ NAME, DATA _ TYPE, DATA _ LENGTH FROM USER _ TAB _ COLUMN with TABLE _ NAME: '% 1' "). arg (strtable)"), wherein strtable is the NAME of the TABLE, and the result set is obtained using QsqlQuery:: next.
By executing the SQL statement: "INSERT INTO 'main'; 'T _ FIELDINFO' VALUES (NULL, '% 1', '% 2', '% 3', '% 4'); ", arg (strfield), arg (strtype), arg (strlenght), arg (strtable), and write the acquired field information into the T _ fieldinuo table.
And step 3: and selecting a source database and a target database.
In this step, an interface is provided for selecting the registered heterogeneous data source as a source database and a target database, and the interface parameters are as follows: the method comprises the steps of firstly, registering names, secondly, registering database names, thirdly, registering database names, fifthly, backing up, wherein firstly, the data source position of data migration is adopted, thirdly, the destination position of data migration is adopted, fifthly, backing up is adopted, TRUE is adopted, and FLASE is adopted.
And 4, step 4: and judging whether the target database exists.
This step judges whether the target database exists. Using a SELECT statement to inquire whether a target database name exists in a database table in the SQLite database, and if not, creating a target database with a structure completely consistent with that of the source database; if the result is not null, indicating that the target database exists, otherwise indicating that the target database does not exist, establishing the target database with the structure completely consistent with that of the source database; if the query result is not null, indicating that the target database exists, and directly executing the step 7; otherwise, the target database does not exist, and step 5 is executed.
And 5: selecting data type mapping rules
Because the data types of the heterogeneous databases are different, each heterogeneous database has the type supported by the heterogeneous database, but the data types among different heterogeneous databases have alternatives.
This step provides a type conversion rule interface between heterogeneous databases and needs to assign a unique "mapping rule name" for each conversion rule, the following table is Oracle and Mysql field type conversion comparison table, and the type mapping rule name is Oracle and Mysql.
TABLE 1 Oracle and Mysql field type conversion and comparison table
Figure BDA0002534994830000071
Figure BDA0002534994830000081
This step provides an interface for selecting a data type mapping rule, and the interface parameter is a "mapping rule name".
Step 6: creating a target database
And if the target database does not exist in the step 4, creating the target database with the structure completely consistent with that of the source database in the step. The creation mode is that a target database is created by using a DDL statement of SQL according to a source database structure and a data type mapping rule.
And 7: extracting data
In the step, the data of the database row by row in the source database is firstly obtained by using a QsqlQuery interface (exec) and then obtained by using the QsqlQuery interface (next).
And 8: judging whether to back up data
In step 3, interface parameters are provided, if yes, TRUE and FALSE.
And step 9: generating XML files
Through steps 1-8, the structure of the source database and the data in the database can be obtained, and if the data is judged to be backup data in step 8. This step generates an XML data document from the XML operation component. The specific steps of converting the data of the relational database into the XML document are as follows:
one database is a document;
creating an element corresponding to each table;
creating an attribute for each column in the corresponding table;
the following table is an example of a database name, stu, and a table name, student, and corresponding XML document.
TABLE 2 student Table
Serial number sno sname
1 1 stu_1
2 2 stu_2
The corresponding XML document:
Figure BDA0002534994830000091
the XML documents are stored in a disk for a long time and used for restoring the database as original data when data migration fails or the database crashes in the later period.
Step 10: data writing
And writing each row of data extracted in the step 7 into the target data. Because the database structures of the target database and the source database are completely consistent, the INSERT statement can be written into the target database after the target database executes the INSERT statement after every data is extracted.
Step 11: write data migration log
Writing the data migration LOG into a migration LOG table (T _ LOG) of an SQLite database, wherein the written statement is as follows: INSERT INTO ' main, ' T _ LOG ' VALUES (). Writing data includes: migration time, whether migration is successful or not, whether backup is successful or not, the number of data migration records, and if the backup data is migrated at this time, information such as the name of the backup file is also included.
Through the steps, the migration of the data in the heterogeneous database is completed.
The invention designs a universal heterogeneous database data migration method, which provides service for data migration of heterogeneous databases. Most existing heterogeneous database data migration methods are specific to specific databases and cannot achieve universality. The method and the system establish connection with the heterogeneous database by calling the ODBC driver of the database, use the SQLite to store data, call a simple and easy-to-use interface to solve the problem of data migration in the heterogeneous data source, and have universality.
The invention provides a universal heterogeneous database data migration method, which has the following beneficial effects compared with the prior art:
(1) the invention provides a general heterogeneous database data migration method, which is not specific to a specific database and is suitable for common databases in the market, such as Oracle, SQLServer, Mysql, postgre, Dameng and gold warehouse;
(2) the scheme provided by the invention is not only suitable for the condition that the target database exists, but also suitable for the condition that the target database does not exist.
The above description is only a preferred embodiment of the present invention, and it should be noted that, for those skilled in the art, several modifications and variations can be made without departing from the technical principle of the present invention, and these modifications and variations should also be regarded as the protection scope of the present invention.

Claims (9)

1. A universal heterogeneous database data migration method is characterized in that,
step 1: registering a data source;
step 2: acquiring a database structure;
and step 3: selecting a source database and a target database;
and 4, step 4: and querying whether a target database name exists in a database table in the SQLite database by using a SELECT statement, and if not, creating a target database with a structure completely consistent with that of the source database.
And 5: selecting a data type mapping rule;
step 6: extracting database data from a source database;
and 7: judging whether data is backed up or not, and if so, generating an XML file;
and 8: writing each row of data extracted in the step 7 into a target database;
and step 9: and writing the data migration log into a migration log table of the SQLite database.
2. The universal heterogeneous database data migration method according to claim 1, wherein registering a data source comprises: and loading the ODBC driver provided by the database, and then writing the registration information into the local SQLite database.
3. The universal heterogeneous database data migration method according to claim 1, wherein obtaining the database structure comprises: and executing SQL statements of the query database structure, and writing the SQL statements into a local SQLite database.
4. The method for universal data migration of heterogeneous databases according to claim 1, wherein in the step of selecting the source database and the target database, an interface is provided for selecting the heterogeneous data sources that have been registered as the source database and the target database.
5. The universal heterogeneous database data migration method according to claim 1, wherein a SELECT statement is used to query whether a target database name exists in a "database table" in the SQLite database, and if not, a target database with a structure completely consistent with that of a source database is created; and if the query result is not null, indicating that the target database exists, otherwise, indicating that the target database does not exist, and creating the target database with the structure completely consistent with the structure of the source database.
6. The universal heterogeneous database data migration method according to claim 1, wherein step 5: selecting a data type mapping rule; this step provides type conversion rules between heterogeneous databases and assigns a unique mapping rule name to each conversion rule.
7. The method for migrating data in a universal heterogeneous database as recited in claim 1, wherein in step 6, the data is obtained by using a QsqlQuery interface, and the data in one row is obtained by using the QsqlQuery interface and the data in the next row.
8. The method of claim 1, wherein an INSERT statement is executed on the target database after each piece of data is extracted to write to the target database.
9. The universal heterogeneous database data migration method according to claim 1, wherein writing the contents in the migration log table of the SQLite database comprises: migration time, whether migration is successful, whether backup is performed, and the number of data migration records.
CN202010534252.8A 2020-06-11 2020-06-11 Universal heterogeneous database data migration method Pending CN111680024A (en)

Priority Applications (1)

Application Number Priority Date Filing Date Title
CN202010534252.8A CN111680024A (en) 2020-06-11 2020-06-11 Universal heterogeneous database data migration method

Applications Claiming Priority (1)

Application Number Priority Date Filing Date Title
CN202010534252.8A CN111680024A (en) 2020-06-11 2020-06-11 Universal heterogeneous database data migration method

Publications (1)

Publication Number Publication Date
CN111680024A true CN111680024A (en) 2020-09-18

Family

ID=72454703

Family Applications (1)

Application Number Title Priority Date Filing Date
CN202010534252.8A Pending CN111680024A (en) 2020-06-11 2020-06-11 Universal heterogeneous database data migration method

Country Status (1)

Country Link
CN (1) CN111680024A (en)

Cited By (3)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN112328680A (en) * 2020-11-03 2021-02-05 大唐智联科技(杭州)有限公司 Electronic component data processing method, data interface and management system
CN114077600A (en) * 2021-11-26 2022-02-22 山东福生佳信科技股份有限公司 ARM (advanced RISC machine) kernel-based data heterogeneous migration visual analysis method
CN116303371A (en) * 2023-05-17 2023-06-23 北京比格大数据有限公司 Cross-architecture data computing method and device, electronic equipment and storage medium

Citations (2)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US20020174098A1 (en) * 2001-05-04 2002-11-21 Lasmsoft Corporation Method and system for providing a dynamic and real-time exchange between heterogeneous database systems
CN103440273A (en) * 2013-08-06 2013-12-11 北京航空航天大学 Data cross-platform migration method and device

Patent Citations (2)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US20020174098A1 (en) * 2001-05-04 2002-11-21 Lasmsoft Corporation Method and system for providing a dynamic and real-time exchange between heterogeneous database systems
CN103440273A (en) * 2013-08-06 2013-12-11 北京航空航天大学 Data cross-platform migration method and device

Non-Patent Citations (1)

* Cited by examiner, † Cited by third party
Title
韩世杰 等: "一种异构数据库迁移系统的设计与实现", 《电子技术与软件工程》 *

Cited By (4)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN112328680A (en) * 2020-11-03 2021-02-05 大唐智联科技(杭州)有限公司 Electronic component data processing method, data interface and management system
CN114077600A (en) * 2021-11-26 2022-02-22 山东福生佳信科技股份有限公司 ARM (advanced RISC machine) kernel-based data heterogeneous migration visual analysis method
CN114077600B (en) * 2021-11-26 2022-09-02 山东福生佳信科技股份有限公司 ARM (advanced RISC machine) kernel-based data heterogeneous migration visual analysis method
CN116303371A (en) * 2023-05-17 2023-06-23 北京比格大数据有限公司 Cross-architecture data computing method and device, electronic equipment and storage medium

Similar Documents

Publication Publication Date Title
CN111680024A (en) Universal heterogeneous database data migration method
US20230376487A1 (en) Processing database queries using format conversion
CN106570086B (en) Data migration system and data migration method
US9280568B2 (en) Zero downtime schema evolution
CN111382226B (en) Database query and retrieval method and device and electronic equipment
Deen et al. Data integration in distributed databases
US20180081956A1 (en) Method for automatically synchronizing multi-source heterogeneous data resources
CN111984621B (en) Heterogeneous database migration method and device and storage medium
CN103020301B (en) A kind of multidimensional data query and storage means and system
CN107491515B (en) Intelligent power distribution and utilization data conversion method based on big data platform
CN113553313B (en) Data migration method and system, storage medium and electronic equipment
CN114741375A (en) Rapid and automatic data migration system and method for multi-source heterogeneous database
US10762068B2 (en) Virtual columns to expose row specific details for query execution in column store databases
CN110674161A (en) Method, system, storage medium and SQL operation platform for online SQL
CN115292307A (en) Data synchronization system, method and corresponding computer equipment and storage medium
CN109800069B (en) Method and device for realizing data management
CN108959391B (en) Apparatus, system, method, and storage medium for presenting database cluster architecture diagram
CN110941629A (en) Metadata processing method, device, equipment and computer readable storage medium
CN108399196B (en) Automatic sql execution method and system of database sql statement automatic generation tool
CN108388589B (en) Device for automatically generating sql query statement of database
CN114896196A (en) Data migration method and system for domestic database
CN112256489A (en) Data acquisition method and device of cloud development platform and data storage architecture
CN110263028B (en) Full-scale synchronization method applied to search service
CN111858644B (en) Method, device and system for data fusion and query
CN115934681A (en) Method and device for rapidly migrating heterogeneous relational database

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