Device, method and system for full migration of heterogeneous database

Abstract

The present invention discloses a device, method and system for full migration of heterogeneous databases, and relates to the technical field of database migration. The device includes a scheduling management end and an executor, and provides scheduling management services through the xxl-job scheduling management end; the executor includes an ETL tool for performing data migration from a source database to a target database; the scheduling management end includes a migration task acquisition module and a migration task scheduling module; the migration task acquisition module collects database migration task information for the source database and the target database, and the database migration task information includes the configured JobHandler information of the xxl-job for table migration; the migration task scheduling module adds the JobHandler as a scheduling task, and selects the target executor from the executor cluster according to the routing strategy to perform the migration of the table structure and table data. The present invention effectively solves the problem of full migration of heterogeneous databases.

Description

Technical Field

[0001] The present invention relates to the technical field of database migration, and in particular to a device, method and system for full migration of heterogeneous databases. Background Art

[0002] A database is a system that organizes, stores, and manages data according to data structures. As the underlying core of various information systems such as information business systems, management information systems, and decision support systems, it is an important technical means for scientific research and decision-making management. Considering the importance of information system security and data security in key areas, the localization of database applications is imperative. Traditional databases and domestic databases are usually heterogeneous databases (that is, the databases are of different types). The process of migrating data from traditional databases (or source databases) to domestic databases (or target databases) is database heterogeneous migration (or heterogeneous database migration). The typical data migration solution is to complete data migration through full migration or incremental migration. Among them, full migration of heterogeneous databases is a one-time task, and its main purpose is to migrate data from one database system to another different type of database system. At present, heterogeneous database migration of enterprise legacy system applications is an extremely common and important application scenario in the information innovation industry (that is, information technology application innovation industry).

[0003] Heterogeneous database migration mainly includes two categories: structure migration and data migration. Structure migration is to migrate data from the structure of one database to the structure of another database, including the migration of table structure, fields, indexes, etc. (the table structure of the source database type needs to be converted to the table structure of the target database type); data migration is to migrate data from one database to another, including the export, conversion and import process of data. At present, heterogeneous database migration is mainly implemented by using the database's own migration tools (such as the DM database migration tool DTS) or open source ETL (Extract-Transform-Load) tools. Commonly used open source ETL tools include DataX tools and kettle tools.

[0004] However, the above mainstream data migration tools generally have problems such as difficulty in supporting distributed architecture and table structure migration, and high operation and maintenance costs. For example, although the DataX tool performs well in data source support, scalability, and extraction speed, it does not support visual interface, data conversion, cleaning, scheduled tasks, distributed scheduling, monitoring and alarming, operation and maintenance costs, and table structure migration; although the kettle tool performs well in data source support, scalability, extraction speed, visual interface, data conversion, and cleaning, it does not support scheduled tasks, distributed scheduling, monitoring and alarming, operation and maintenance costs, and table structure migration. For the DM database migration tool DTS, although it supports table structure migration, it only supports table structure migration of its own type of database (DM database), and the operation and maintenance cost of DM DTS is high, and different migration tools need to be deployed for different types of databases; at the same time, DMDTS does not support scheduled tasks, distributed scheduling, monitoring and alarming, etc.

[0005] On the other hand, in order to achieve high-performance and high-reliability batch processing of big data, the existing technology also provides some distributed scheduling solutions for data processing tasks, which are mainly based on distributed scheduling platforms for task scheduling. Common distributed scheduling platforms developed based on Java language include DolphinScheduler, xxl-job, Azkaban, PowerJob (formerly OhMyScheduler), etc., all of which support scheduled tasks and distributed scheduling. As an example, Chinese patent application CN202310203243.4 discloses a distributed scheduling method for data processing tasks based on webspoon, including: a webspoon script designer, a data processing service and a distributed task scheduling center xxl-job, the webspoon script designer is used to create a script configuration library (i.e., a database where scripts are stored) and design processing scripts online; the data processing service is used to build tasks; the distributed task scheduling center xxl-job invokes task execution according to the execution strategy cron expression, schedules the executor cluster according to the routing strategy, creates a task running thread to execute specific tasks, writes back the scheduling log after the task is executed, performs branch analysis, and displays task monitoring information; wherein the data processing service and the webspoon-kettle script designer share the configuration library authentication XML configuration file, and store the script designed by the script design workbench in the configuration library; at the same time, the data processing service builds tasks, calls the scheduling center, and the scheduling center calls up the executor cluster integration-executor; the executor cluster processes data, analyzes log log statistics of execution results, and realizes batch data processing task scheduling. The above solution uses the webspoon script designer and the distributed task scheduling center xxl-job to improve the operating efficiency of data processing tasks and the flexibility of task distribution and concurrency, and solves the problem that traditional data processing tools cannot achieve high-performance and high-reliability big data batch processing task execution, visual monitoring and log analysis.

[0006] According to the requirements of full migration of heterogeneous databases, the present invention proposes a migration solution suitable for full migration of heterogeneous databases based on the ETL tools and distributed task scheduling platform provided by the prior art. Summary of the invention

[0007] The purpose of the present invention is to overcome the shortcomings of the prior art and provide a device, method and system for full migration of heterogeneous databases. The present invention utilizes the characteristics of a distributed task scheduling platform and an ETL tool to propose a migration tool suitable for full migration of heterogeneous databases, effectively solves the table structure migration problem during full migration of heterogeneous databases, improves the reliability and efficiency of full migration of heterogeneous databases, and is particularly suitable for adapting to the heterogeneous database migration needs in the information and innovation industry environment.

[0008] To achieve the above objectives, the present invention provides the following technical solutions:

[0009] A device for full migration of heterogeneous databases, comprising a scheduling management end and an executor, wherein the scheduling management end comprises a distributed task scheduling platform xxl-job, and provides scheduling management services through xxl-job; the executor adopts an ETL tool to perform data migration from a source database to a target database; wherein a job task configuration file is provided corresponding to the executor, and the job task configuration file is used to configure logical operation information for executing table migration, and the table migration includes table structure migration and table data migration;

[0010] The scheduling management end includes a migration task acquisition module and a migration task scheduling module;

[0011] The migration task acquisition module is used to collect database migration task information for the source database and the target database, and to obtain the configured routing strategy; the database migration task information includes the configured xxl-job task processor JobHandler information for table migration, including the first task processor JobHandler corresponding to the table structure migration and the second task processor JobHandler corresponding to the table data migration;

[0012] The migration task scheduling module is used to add the first task processor JobHandler and the second task processor JobHandler as scheduling tasks, select a target executor from the executor cluster according to the routing strategy configured above, and send a task scheduling request for the above scheduling task to the target executor;

[0013] After receiving the aforementioned task scheduling request, the target executor calls the scheduled task execution methods configured in the first task processor JobHandler and the second task processor JobHandler based on the job task configuration file to migrate the table structure and table data respectively.

[0014] Further, the migration task acquisition module includes a scheduling management service main interface, and a task management menu is provided in the scheduling management service main interface;

[0015] The task management menu collects information about the first task processor JobHandler for table structure migration and the second task processor JobHandler for table data migration configured by the user, as well as routing policy information and monitoring alarm information configured by the user or system, and sends the collected information to the migration task scheduling module.

[0016] Further, the executor generates an execution log when the task is executed;

[0017] The scheduling management terminal includes a log management module and an alarm module;

[0018] The log management module is configured to: obtain the execution log from the executor, generate scheduling log information including the executor identity information and the corresponding execution log according to the registration information of the executor and the execution log, and store the scheduling log information under the access path corresponding to the scheduling log menu, so that the user can access the aforementioned scheduling log information through the scheduling log menu;

[0019] The alarm module is configured to: after the migration task is completed, notify the corresponding user end of the execution result of the task according to the communication method in the monitoring alarm information configuration;

[0020] The communication method includes email communication, instant messaging and / or text message communication.

[0021] Further, for a migration task including multiple tables, the routing strategy is configured to poll multiple target executors to evenly execute the migration tasks of these tables on the multiple target executors;

[0022] The configuration information in the job task configuration file includes execution process configuration, execution script configuration, variable parameter configuration, file transfer configuration and / or shell operation configuration for table migration.

[0023] Further, the executor includes an identity registration module, which is configured to: after the executor is started, select a target scheduling management terminal according to the pre-configured machine address information of the scheduling management terminal, and send a registration request message to the aforementioned target scheduling management terminal, so that the target scheduling management terminal can discover and manage the executor;

[0024] The scheduling management end includes a registration service module, which is configured to: obtain registration request information of the executor, authenticate the executor according to the registration request information, and add the authenticated executor to the executor registration table, wherein the executor registration table is used to manage the online executors and the machine address information of the scheduling management end;

[0025] When selecting a target executor, the migration task scheduling module obtains all online executor information through the executor registry, and then selects a target executor for executing the task according to a routing strategy.

[0026] Furthermore, the logical operation information of the table migration includes:

[0027] Clear the table data related to the target business in the target database to clear the existing dirty data in the target database;

[0028] Extract table data related to the aforementioned target business from the source database;

[0029] Writing the aforementioned extracted table data into the data table of the target business in the target database; wherein, for the aforementioned target business, obtaining the structural change information of the table structure of the target database and the table structure of the source database, and editing the table fields of the relevant tables in the target database according to the aforementioned structural change information; the editing includes adding a new table field, at this time, for the newly added table field, after obtaining the source data content related to the newly added table field from the source database, the source data content is concatenated to obtain the table data content corresponding to the table field, and when writing the table data in the target database, the aforementioned table data content is written under the aforementioned newly added table field;

[0030] Update the primary key sequence size of the data table of the aforementioned target business in the target database to make the primary key sequence of the current target database consistent with that of the source database.

[0031] Furthermore, the executor is a kettle batch executor, which obtains table migration logic operation information configured by the user for the target business through the visual interface tool Spoon provided by the kettle batch executor, and generates a kettle job task configuration file kettle job according to the aforementioned configured table migration logic operation information; then configures the xxl-job task processor JobHandler required for table migration according to the kettle job;

[0032] Among them, based on the visual interface tool Spoon, a table clearing component, a table input component, a table output component and an SQL script component are respectively configured, and the table data information of the target database that needs to be cleared is collected through the table clearing component, the table data information of the source database that needs to be extracted is collected through the table input component, the table data information that needs to be written into the target database is collected through the table output component, and the table primary key sequence information that needs to be updated is collected through the SQL script component.

[0033] Furthermore, when configuring the first task processor JobHandler for xxl-job required for table structure migration, obtain the SQL script of the target business table DDL statement corresponding to the aforementioned target database type and copy it to the project of the kettle batch executor; then configure a database application interface JDBC in the project of the kettle batch executor to connect to the target database;

[0034] When configuring the second task processor JobHandler for xxl-job required for table data migration, obtain the aforementioned kettle job and copy it to the project of the kettle batch executor; then configure a java code in the project of the kettle batch executor to call the core engine provided by kettle.

[0035] The present invention also provides a method for performing full migration of heterogeneous databases according to the above-mentioned device, comprising the steps of:

[0036] Obtain application information associated with the source database and close the applications that need to be migrated;

[0037] Invoke the table structure migration task of the target database in the scheduling management terminal to achieve the table structure migration of the target database;

[0038] Back up the target database for emergency rollback after migration exceptions;

[0039] Invoke the table data migration task of the target database in the scheduling management terminal to achieve the table data migration of the target database;

[0040] Perform data consistency check to ensure the correctness of data after migration;

[0041] Start the application that needs to be migrated and open requests.

[0042] The present invention also provides a heterogeneous data migration system adapted to the information innovation environment, the system comprising a source database, a target database and a database migration tool;

[0043] The database migration tool includes the aforementioned device.

[0044] Due to the adoption of the above technical solution, the present invention has the following advantages and positive effects compared with the prior art, as an example: the present invention uses the characteristics of the distributed task scheduling platform and the ETL tool to provide a migration tool suitable for the full migration of heterogeneous databases, effectively solves the table structure migration problem during the full migration of heterogeneous databases, and supports all JDBC-compatible database types. It is particularly suitable for adapting to the heterogeneous database migration needs in the information and innovation industry environment.

[0045] Furthermore, based on the open source distributed task scheduling platforms xxl-job and Kettle, the migration tool is configured with multiple functional modules in combination with the needs of full migration of heterogeneous databases, providing a standardized full-process heterogeneous database migration solution that supports table migration of multiple data sources, multi-task distributed scheduling, scheduled tasks, real-time logs, monitoring alarms, visual interfaces, task drag-and-drop orchestration, data conversion, cleaning and other functions. It solves the problem that traditional heterogeneous database migration solutions are difficult to balance in terms of table structure migration, distributed scheduling, scheduled tasks, monitoring alarms, data conversion, and operation and maintenance costs. BRIEF DESCRIPTION OF THE DRAWINGS

[0046] Figure 1 A logical structure diagram of the migration tool provided in an embodiment of the present invention.

[0047] Figure 2 A table migration flow chart of a deposit transaction service provided in an embodiment of the present invention.

[0048] Figure 3 A diagram of the information transmission process of the migration tool provided in an embodiment of the present invention.

[0049] Figure 4 A flow chart of a method for full migration of heterogeneous databases provided in an embodiment of the present invention. DETAILED DESCRIPTION

[0050] The following is a further detailed description of the device, method and system for full migration of heterogeneous databases disclosed in the present invention in conjunction with the accompanying drawings and specific embodiments. It should be noted that the technologies (including methods and devices) known to ordinary technicians in the relevant field may not be discussed in detail, but in appropriate cases, the above-mentioned known technologies are considered to be part of the specification. At the same time, other examples of exemplary embodiments may have different values. The structures, proportions, sizes, etc. illustrated in the drawings of this specification are only used to match the contents disclosed in the specification for people familiar with this technology to understand and read, and are not used to limit the limiting conditions for the implementation of the invention.

[0051] In the description of the embodiments of the present application, " / " means or, and "and / or" is used to describe the association relationship of associated objects, indicating that three relationships may exist. For example, "A and / or B" means: A and B exist alone, B exists alone, and A and B exist at the same time. In the description of the embodiments of the present application, "multiple" means two or more.

[0052] The technical concept and solution of the present invention are introduced below based on exemplary application scenarios.

[0053] The explanations of relevant technical terms are as follows:

[0054] 1) ETL: The full name is Extract-Transform-Load, which refers to the process of extracting (extract), transforming (transform), and loading (load) data from the source to the destination.

[0055] 2) xxl-job, or XXL-JOB: is a distributed task scheduling platform, whose core design goal is rapid development, simple learning, lightweight, and easy to expand. xxl-job is mainly used for distributed scheduled tasks, and consists of a scheduling center (xxl-job-admin) and an executor (xxl-job-executor). The scheduling center and the executor are deployed separately (decoupling task scheduling from task execution). The scheduling center manages scheduling tasks in a unified manner, does not bear business logic, and is responsible for initiating scheduling requests; the executor is used to receive scheduling requests from the scheduling center and execute tasks.

[0056] 3) JobHandler: The task manager is essentially a package of a scheduled task, which is configured to execute a preset program (or logic) within the agreed time. In a distributed scheduling platform that supports scheduled tasks (such as xxl-job), the task exists in the executor (such as xxl-job-executor) in the form of JobHandler.

[0057] 4) Kettle: Kettle is a commonly used open source ETL tool, the full name is KDE Extraction, Transportation, Transformation and Loading Environment. Kettle is developed based on Java language and supports cross-platform operation. Its features include: support for 100% coding-free, drag-and-drop development of ETL data pipelines; can connect to data sources including traditional databases, files, big data platforms, interfaces, streaming data, etc.; support for adding machine learning algorithms to ETL data pipelines, etc.

[0058] 5) kjb: is the abbreviation of kettle job, which is the kettle job task configuration file.

[0059] 6) ktr: It is the abbreviation of kettle transformation, which refers to the specific data processing steps of kettle transformation, usually including an input and an output, and setting various transformation operation information between the input and the output.

[0060] 7) Java: Java is an object-oriented programming language. As a representative of static object-oriented programming languages, it implements object-oriented theory very well. Java has the characteristics of simplicity, object-oriented, distributed, robust, secure, platform-independent and portable, multi-threaded, dynamic, etc. Java can be used to write desktop applications, Web applications, distributed systems, and embedded system applications.

[0061] 8) JDBC: The full name is Java Database Connectivity, which is an application program interface in the Java language that regulates how client programs access databases. It provides methods such as querying and updating data in the database.

[0062] 9) DM DTS: A migration tool for DM, which provides functions such as migration of mainstream large databases to DM, DM to DM, file to DM and DM to file.

[0063] 10) DDL: Data Definition Language. DDL is mainly used to define or change the structure of a table, data types, links and constraints between tables, and other initialization tasks. It is mostly used when creating a table.

[0064] 11) SQL: Structured Query Language. SQL is a special-purpose programming language, a database query and programming language, commonly used to access data and query, update and manage relational database systems.

[0065] 12) Spoon: A graphical tool for Kettle that is used to quickly design and maintain complex ETL workflows. Example

[0066] The ICT industry, namely the IT application innovation industry, is to build a domestic IT software and hardware underlying architecture system and a full-cycle ecosystem through application-driven development. Database heterogeneous migration of enterprise legacy system applications is a common scenario in the ICT industry environment. However, the existing technology lacks heterogeneous database migration tools that can support distributed scheduling and table structure migration and have low operation and maintenance costs.

[0067] Accordingly, the present invention provides a device for full migration of heterogeneous databases. Figure 1 As shown, the migration device mainly includes a scheduling management terminal and an executor.

[0068] The scheduling management end includes a distributed task scheduling platform xxl-job, which provides scheduling management services through xxl-job.

[0069] The executor uses an ETL tool as a task execution end, which is used to execute data migration from a source database to a target database. Among them, a job task configuration file is set corresponding to the executor, and the job task configuration file is used to configure the logical operation information for executing table migration. The table migration includes table structure migration and table data migration. The configuration items (i.e., configuration information) in the job task configuration file may include information such as execution process configuration items, execution script configuration items, variable parameter configuration items, file transfer configuration items, and shell run configuration items for table migration.

[0070] Specifically, the xxl-job scheduling management terminal may include a migration task acquisition module and a migration task scheduling module.

[0071] The migration task acquisition module is used to collect database migration task information for the source database and the target database, and to obtain the configured routing strategy. The database migration task information includes the configured xxl-job task processor JobHandler information for table migration; the xxl-job task processor JobHandler for table migration specifically includes the first task processor JobHandler corresponding to the table structure migration and the second task processor JobHandler corresponding to the table data migration. The task processor JobHandler encapsulates scheduled tasks. When the executor executes the task, it calls the execute method (i.e., execution method) in JobHandler to execute the scheduled task.

[0072] The migration task scheduling module is used to add the aforementioned first task processor JobHandler and the second task processor JobHandler as scheduling tasks, and select a target executor from the executor cluster according to the aforementioned configured routing strategy, and send a task scheduling request for the aforementioned scheduling task to the target executor.

[0073] After receiving the aforementioned task scheduling request, the target executor calls the scheduled task execution methods (execute methods) configured in the first task processor JobHandler and the second task processor JobHandler to migrate the table structure and table data respectively based on the table migration logic operation information in the job task configuration file.

[0074] In this embodiment, the migration task acquisition module of the xxl-job scheduling management terminal may include a scheduling management service main interface, and a task management menu is set in the scheduling management service main interface.

[0075] The task management menu collects information about the first task processor JobHandler for table structure migration and the second task processor JobHandler for table data migration configured by the user, as well as routing policy information and monitoring alarm information configured by the user or system, and sends the collected information to the migration task scheduling module.

[0076] In specific implementation, a scheduling management service main interface can be provided based on the xxl-job scheduling management terminal, and a task management menu can be set in the scheduling management service main interface. The task management menu includes a task processor information collection column, a routing strategy information collection column and a monitoring alarm information collection column.

[0077] Among them, the task processor information collection column can also include a task processor collection sub-column for table structure migration and a task processor collection sub-column for table data migration, through which the user can input or import the corresponding first task processor JobHandler information and second task processor JobHandler information respectively.

[0078] The information in the routing strategy information collection column and the monitoring alarm information collection column can be configured by default by the scheduling management end, or can be personalized by the user as needed.

[0079] The routing strategies provided by the xxl-job scheduling management terminal may include the first (executor), the last (executor), polling (executor), random (select executor), etc. Preferably, for a migration task including multiple tables, the routing strategy is automatically configured to poll multiple target executors to evenly execute the migration tasks of these tables on multiple target executors. In this way, distributed calls are implemented and the migration efficiency of the database is improved.

[0080] After the user has set the information in the task management menu, the user can click Save. At this time, the migration task acquisition module can acquire the information set in the task management menu and then send it to the migration task scheduling module.

[0081] In this embodiment, the executor further includes an execution log module, which is used to generate an execution log when the task is executed.

[0082] The scheduling management terminal may include a log management module and an alarm module.

[0083] The log management module is configured to: obtain the execution log from the executor, and generate scheduling log information including the executor identity information and the corresponding execution log based on the executor's registration information and the execution log, and store the scheduling log information in the access path corresponding to the scheduling log menu, so that the user can access the aforementioned scheduling log information through the scheduling log menu.

[0084] The alarm module is configured to notify the corresponding user end of the execution result of the task according to the communication method in the monitoring alarm information configuration after the migration task is completed. Preferably, the communication method may include email communication, instant messaging and / or SMS communication.

[0085] In this embodiment, the executor may further include an identity registration module, which is configured as follows: after the executor is started, it will automatically select the target scheduling management end according to the machine address information of the scheduling management end pre-configured in the address configuration module, and then send registration request information to the aforementioned target scheduling management end so that the target scheduling management end can discover and manage the executor.

[0086] The scheduling management end includes a registration service module, which is configured to: obtain registration request information of the executor, authenticate the executor according to the registration request information, and add the authenticated executor to the executor registry, and the executor registry is used to manage the online executors and the machine address information of the scheduling management end.

[0087] When selecting a target executor, the migration task scheduling module obtains all online executor information through the executor registry, and then selects a target executor for executing the task according to a routing strategy.

[0088] In this embodiment, according to the characteristics of full migration of heterogeneous databases, the logical operation information of the table migration may specifically include the following steps:

[0089] S110, clear the table data related to the target business in the target database to clear the existing dirty data in the target database. Because the full data is migrated to the new target database, it is necessary to ensure that there is no existing dirty data in the database.

[0090] S120, extracting table data related to the aforementioned target business from the source database.

[0091] S130, writing the extracted table data into the data table of the target business in the target database. Specifically, for the target business, the structure change information of the table structure of the target database and the table structure of the source database is obtained, and the table fields of the relevant tables in the target database are edited according to the structure change information; the editing includes adding a new table field. At this time, for the newly added table field, after the source data content related to the newly added table field is obtained from the source database, the source data content is concatenated to obtain the table data content corresponding to the table field, and when the table data is written into the target database, the table data content is written into the newly added table field.

[0092] S140, updating the primary key sequence size of the data table of the aforementioned target business in the target database, so that the primary key sequence of the current target database is consistent with that of the source database.

[0093] As an example but not a limitation, let's take a deposit transaction business in the transaction system as an example. For example, the deposit transaction table data migration mainly involves two tables: the deposit master table and the deposit interest payment table. At this time, the table data related to the target business that needs to be migrated includes the aforementioned deposit master table and the deposit interest payment table. The migration task is for the table structure and table data of the above two tables. At this time, the data migration business logic operation information of the deposit transaction operation can be found in Figure 2 shown.

[0094] As an example, for the deposit main table, the structure of the deposit main table has changed, and the migration logic operation information is as follows:

[0095] S11, clear the deposit main table data of the target database to avoid the existence of dirty existing data.

[0096] S12, read the deposit master table data from the source database.

[0097] S13, because the deposit master table structure of the target database has been changed, fields related to account information have been added. Therefore, it is necessary to concatenate the account information table according to the source data to obtain the account information corresponding to the deposit.

[0098] S14, write into the deposit master table in the target database.

[0099] S15, updating the sequence size of the deposit master table in the target database so that the primary key sequence of the current database is consistent with that of the source database.

[0100] S16, the migration of the deposit main table is completed and ends.

[0101] For the deposit interest collection and payment table, the table structure has not changed. The migration logic operation information is as follows:

[0102] S21, clear the deposit interest collection and payment table data of the target database to avoid the existence of dirty existing data.

[0103] S22, read the deposit interest receipt and payment table data from the source database.

[0104] S23, write the deposit interest collection and payment table in the target database.

[0105] S24, updating the sequence size of the deposit interest collection and payment table in the target database so that the primary key sequence of the current database is consistent with that of the source database.

[0106] S25, the migration of deposit interest receipt and payment table is completed and ends.

[0107] In the preferred implementation of this embodiment, considering that the kettle tool supports 100% coding-free, drag-and-drop development of ETL data pipelines; can connect to data sources including traditional databases, files, big data platforms, interfaces, streaming data, etc.; and provides standardized data export, conversion, and import functions, support for multiple data source types, and task drag-and-drop arrangement in a visual interface, the executor of the migration device is preferably a kettle batch executor, see Figure 3 shown.

[0108] At this time, the kettle batch executor is responsible for the migration and conversion of data, that is, it is configured as an executor of the distributed task scheduling platform xxl-job. The kettle batch executor supports executor clusters, including at least executor 1 and executor 2, which can ensure high availability and high performance of task execution.

[0109] Specifically, the kettle batch executor can introduce a jar package and configure the address of the xxl-job scheduling manager in the application configuration file application.xml. The application.xml file is loaded after the executor is started. At this time, according to the address of the xxl-job scheduling manager configured above, the executor will automatically register itself in the registration service module of the xxl-job scheduling manager so that the xxl-job scheduling manager can discover and manage it.

[0110] At the same time, the present invention also obtains the table migration logic operation information configured by the user for the target business through the visual interface tool Spoon provided by the kettle batch executor, and generates the kettle job task configuration file kettle job according to the aforementioned configured table migration logic operation information. Then, the task processor JobHandler of xxl-job required for table migration is configured according to the kettle job.

[0111] Specifically, the components of the kettle batch executor are expanded, and according to the logical operation information of the table migration, the table clearing component, table input component, table output component and SQL script component are configured based on the visual interface tool Spoon. The table clearing component is used to collect the table data information of the target database that needs to be cleared; the table input component is used to collect the table data information of the source database that needs to be extracted; the table output component is used to collect the table data information that needs to be written into the target database; and the SQL script component is used to collect the table primary key sequence information that needs to be updated.

[0112] In this way, the user can clear the target business table data of the target database by inputting the table name of the migration table of the target business into the aforementioned clear table component and selecting the target database. The target business table data can be extracted from the source database by writing the SQL statement of the target business data query logic into the aforementioned table input component and selecting the source database. The table data can be written into the target business data table in the target database by writing the name of the migration table of the target business into the aforementioned table output component and selecting the target database. The primary key sequence size of the table related to the target business in the target database can be updated by writing the SQL statement of updating the primary key sequence of the table of the target business into the aforementioned SQL script component and selecting the target database.

[0113] In this embodiment, when configuring the first task processor JobHandler for xxl-job required for table structure migration, you can obtain the SQL script of the DDL statement of the target business table corresponding to the aforementioned target database type, and copy it to the project of the kettle batch executor; then configure a database application interface JDBC in the project of the kettle batch executor to connect to the target database. The SQL script is pre-written by the user, and the DDL statement of the target business table corresponding to the target database type is configured in the script (for initializing the table structure). When the aforementioned first task processor JobHandler is called to perform a task later, the aforementioned SQL script task is executed, and the table structure of the target business table is established through the DDL statement of the target business table configured in the SQL script.

[0114] When configuring the second task processor JobHandler for xxl-job required for table data migration, you can obtain the aforementioned kettle job - for example, export the job task configuration file kettle job through the visual interface tool Spoon provided by kettle, and then copy the job task configuration file kettle job to the project of the kettle batch executor. Subsequently, configure a java code in the project of the kettle batch executor to call the core engine provided by kettle. When the aforementioned second task processor JobHandler is called to perform the task later, the aforementioned java code is executed, and the core engine provided by kettle is directly called through the java code to execute the table data migration logic in the kettle job.

[0115] Taking the deposit transaction business as an example, the process of using the migration device of the present invention to migrate the entire deposit table is described in detail below.

[0116] First, configure the kjb (kettle job) file for the deposit table migration logic.

[0117] Specifically, based on the aforementioned extended kettle visual interface tool Spoon, the following operations can be performed according to the data migration business logic operation information of the deposit transaction job: the table names of the deposit transaction-related tables to be cleared are collected through the clear table component, and the target database is selected, thereby clearing the deposit transaction table data of the target database. The SQL statements of the deposit transaction data query logic are collected through the table input component, and the source database is selected, thereby extracting the deposit transaction table data from the source database. The table names of the deposit transaction-related tables are collected through the table output component, and the target database is selected to write the table data to the deposit transaction table in the target database. The SQL statements for updating the primary key sequence of the deposit transaction table are collected through the SQL script component, and the target database is selected to update the primary key sequence size of the deposit transaction table in the target database.

[0118] Secondly, configure the task processor JobHandler of the migration task, including the JobHandler of xxl-job required for table structure migration and the JobHandler of xxl-job required for table data migration. When xxl-job schedules the migration task, it schedules the JobHandler configuration items of the migration task.

[0119] When configuring the JobHandler of xxl-job required for table structure migration: After obtaining the SQL script of the deposit table DDL statement corresponding to the target database type written by the user, copy it to the project of the kettle batch executor. Configure a JDBC connection to the target database in the project of the kettle batch executor. When executing the migration scheduling task of xxl-job later, after the kettle executor receives the task scheduling request sent by the xxl-job scheduling management end, it can call the SQL script of the deposit table DDL statement and execute the script task, and establish a deposit table in the target database. The table structure of the established deposit table matches the table structure information configured by the aforementioned DDL statement. In this way, the table structure migration to the target database is realized.

[0120] When configuring the JobHandler of xxl-job required for table data migration: Get the kjb of the deposit table configured in the previous step - for example, export it through the visual interface tool Spoon provided by kettle, and copy it to the project of the kettle batch executor. Write a java code in the project of the kettle batch executor to call the core engine provided by kettle. Later, when executing the migration scheduling task of xxl-job, after the kettle executor receives the task scheduling request sent by the xxl-job scheduling management end, it can directly call the core engine provided by kettle through the java code to execute the data migration logic of this deposit table.

[0121] Then, configure the migration scheduling task of xxl-job.

[0122] At this point, the user can log in and access the main interface of the xxl-job scheduling management service. In the task management menu, add the xxl-job JobHandler of the table data migration and table structure migration tasks in the previous step as the scheduling task of xxl-job; if necessary, configure the routing strategy, alarm email and other configuration items at the same time. Preferably, the routing strategy of the migration tasks of multiple tables can be configured as polling, which can realize distributed calling, evenly execute these migration tasks on multiple executors, and improve migration efficiency.

[0123] Finally, execute the migration scheduling task of xxl-job.

[0124] At this time, according to the information in the task management menu of the main interface of the xxl-job scheduling management service, the xxl-job scheduling management terminal sends a task scheduling request to the executor, and decides which executor node in the executor cluster to send the task scheduling request to according to the configured routing strategy. The selected executor node executes the table structure migration and table data migration scheduling tasks of xxl-job, and migrates the table structure and table data of the deposit table.

[0125] Preferably, after the executor completes the task execution, the user can view the executor log in the scheduling log menu based on the log management module.

[0126] Furthermore, after the migration task is completed, the xxl-job scheduling management end can also automatically notify the user end via email according to the alarm email configured by the user.

[0127] In the above-mentioned scheme provided by the present invention, the xxl-job scheduling management end is responsible for the management and scheduling of migration tasks, as well as the real-time log and monitoring alarm functions of the management migration tasks. It does not undertake the migration work itself, but schedules the kettle batch executor to perform data migration and conversion work by sending task scheduling requests. The kettle batch executor is responsible for the migration and conversion of data, replaces the executor xxl-job-executor in the existing xxl-job architecture, and expands the components of the kettle batch executor according to the needs of heterogeneous database migration to configure the logical operation information of table migration. When the xxl-job scheduling management end executes the configured migration task, it determines which executor node in the executor cluster to send the task scheduling request to according to the routing strategy configured by the task, so as to realize the distributed scheduling of the migration task. When the kettle batch executor receives the task scheduling request sent by the xxl-job scheduling management end, based on the configured logical operation information of the execution table migration, it calls the task manager JobHandler to execute the table migration task to realize data migration. The present invention effectively solves the table structure migration problem during the full migration of heterogeneous databases, and can also improve the reliability and efficiency of the full migration of heterogeneous databases.

[0128] Another embodiment of the present invention further provides a method for performing full migration of heterogeneous databases based on the aforementioned device.

[0129] See also Figure 4 As shown, the method includes the following steps: obtaining application information associated with the source database and closing the application that needs to be migrated; calling and executing the table structure migration task of the target database in the scheduling management end to realize the table structure migration of the target database; backing up the target database for emergency rollback after migration exception; calling and executing the table data migration task of the target database in the scheduling management end to realize the table data migration of the target database; performing data consistency check to ensure the correctness of the data after migration; starting the application that needs to be migrated and opening requests to the outside.

[0130] The above solution provides a complete set of standardized full-process heterogeneous database full migration solutions. For other technical features, please refer to the description of the previous embodiment and will not be repeated here.

[0131] Another embodiment of the present invention also provides a heterogeneous data migration system adapted to the information innovation environment. The system includes a source database, a target database and a database migration tool.

[0132] The database migration tool includes a scheduling management terminal and an executor.

[0133] The scheduling management end includes a distributed task scheduling platform xxl-job, which provides scheduling management services through xxl-job.

[0134] The executor uses an ETL tool to perform data migration from a source database to a target database. A job task configuration file is provided corresponding to the executor, and the job task configuration file is used to configure the logical operation information for executing table migration; the table migration includes table structure migration and table data migration.

[0135] The scheduling management end includes a migration task acquisition module and a migration task scheduling module.

[0136] The migration task acquisition module is used to collect database migration task information for the source database and the target database, and to obtain the configured routing strategy. The database migration task information includes the configured xxl-job task processor JobHandler information for table migration, including the first task processor JobHandler corresponding to the table structure migration and the second task processor JobHandler corresponding to the table data migration.

[0137] The migration task scheduling module is used to add the first task processor JobHandler and the second task processor JobHandler as scheduling tasks, select a target executor from the executor cluster according to the routing strategy configured above, and send a task scheduling request for the above scheduling task to the target executor;

[0138] After receiving the aforementioned task scheduling request, the target executor calls the scheduled task execution methods configured in the first task processor JobHandler and the second task processor JobHandler to migrate the table structure and table data respectively based on the logical operation information of the table migration in the job task configuration file.

[0139] For other technical features, please refer to the description of the previous embodiment and will not be repeated here.

[0140] The present invention also provides a computer-readable storage medium for storing a computer program that can be executed by a processing unit, wherein the computer program implements the above-mentioned method when executed by the processing unit.

[0141] The storage medium may include any medium that can store program codes, such as a USB flash drive, a mobile hard disk, a read only memory (ROM), a random access memory (RAM), a magnetic disk or an optical disk.

[0142] For other technical features, please refer to the description of the previous embodiment and will not be repeated here.

[0143] In the above description, the disclosure of the present invention is not intended to limit itself to these aspects. Rather, within the scope of the target protection of the present disclosure, the components can be selectively and operatively combined in any number. In addition, terms such as "include", "encompass" and "have" should be interpreted as inclusive or open by default, rather than exclusive or closed, unless they are explicitly defined to the contrary. All technical, scientific or other terms are consistent with the meanings understood by those skilled in the art, unless they are defined to the contrary. Common terms found in dictionaries should not be interpreted too idealistically or too impractically in the context of relevant technical documents, unless the present disclosure explicitly defines them as such. Any changes and modifications made by a person of ordinary skill in the field of the present invention based on the above disclosure belong to the scope of protection of the claims.

Claims

1. A device for full migration of heterogeneous databases, characterized by: It includes a scheduling management end and an executor, wherein the scheduling management end includes a distributed task scheduling platform xxl-job, and provides scheduling management services through xxl-job; the executor adopts an ETL tool to perform data migration from a source database to a target database; wherein a job task configuration file is provided corresponding to the executor, and the job task configuration file is used to configure the logical operation information of executing table migration, and the table migration includes table structure migration and table data migration; The scheduling management end includes a migration task acquisition module and a migration task scheduling module; The migration task acquisition module is used to collect database migration task information for the source database and the target database, and to obtain the configured routing strategy; the database migration task information includes the configured xxl-job task processor JobHandler information for table migration, including the first task processor JobHandler corresponding to the table structure migration and the second task processor JobHandler corresponding to the table data migration; The migration task scheduling module is used to add the first task processor JobHandler and the second task processor JobHandler as scheduling tasks, select a target executor from the executor cluster according to the routing strategy configured above, and send a task scheduling request for the above scheduling task to the target executor; After receiving the aforementioned task scheduling request, the target executor calls the scheduled task execution methods configured in the first task processor JobHandler and the second task processor JobHandler based on the job task configuration file to respectively migrate the table structure and table data; The logical operation information of the table migration includes: Clear the table data related to the target business in the target database to clear the existing dirty data in the target database; Extract table data related to the aforementioned target business from the source database; Writing the extracted table data into the data table of the target business in the target database, wherein, for the target business, obtaining the structural change information of the table structure of the target database and the table structure of the source database, and editing the table fields of the relevant table in the target database according to the structural change information; Update the primary key sequence size of the data table of the aforementioned target business in the target database to make the primary key sequence of the current target database consistent with that of the source database.

2. The device according to claim 1, characterized in that: The migration task acquisition module includes a scheduling management service main interface, and a task management menu is provided in the scheduling management service main interface; The task management menu collects information about the first task processor JobHandler for table structure migration and the second task processor JobHandler for table data migration configured by the user, as well as routing policy information and monitoring alarm information configured by the user or system, and sends the collected information to the migration task scheduling module.

3. The device according to claim 2, characterized in that: The executor generates an execution log when the task is executed; The scheduling management terminal includes a log management module and an alarm module; The log management module is configured to: obtain the execution log from the executor, generate scheduling log information including the executor identity information and the corresponding execution log according to the registration information of the executor and the execution log, and store the scheduling log information under the access path corresponding to the scheduling log menu, so that the user can access the aforementioned scheduling log information through the scheduling log menu; The alarm module is configured to: after the migration task is completed, notify the corresponding user end of the execution result of the task according to the communication method in the monitoring alarm information configuration; The communication method includes email communication, instant messaging and / or text message communication.

4. The device according to claim 1, characterized in that: For a migration task including multiple tables, the routing strategy is configured to poll multiple target executors to evenly execute the migration tasks of these tables on the multiple target executors; The configuration information in the job task configuration file includes execution process configuration, execution script configuration, variable parameter configuration, file transfer configuration and / or shell operation configuration for table migration.

5. The device according to claim 1, characterized in that: The executor includes an identity registration module, which is configured to: after the executor is started, select a target scheduling management terminal according to the pre-configured machine address information of the scheduling management terminal, and send a registration request message to the aforementioned target scheduling management terminal, so that the target scheduling management terminal can discover and manage the executor; The scheduling management end includes a registration service module, which is configured to: obtain registration request information of the executor, authenticate the executor according to the registration request information, and add the authenticated executor to the executor registration table, wherein the executor registration table is used to manage the online executors and the machine address information of the scheduling management end; When selecting a target executor, the migration task scheduling module obtains all online executor information through the executor registry, and then selects a target executor for executing the task according to a routing strategy.

6. The device according to claim 1, characterized in that: The editing includes adding a new table field. At this time, for the newly added table field, after obtaining the source data content related to the newly added table field from the source database, the source data content is concatenated to obtain the table data content corresponding to the table field. When writing the table data in the target database, the aforementioned table data content is written under the aforementioned newly added table field.

7. The device according to claim 6, characterized in that: The executor is a kettle batch executor, which uses the visual interface tool Spoon provided by the kettle batch executor to obtain the table migration logic operation information configured by the user for the target business, and generates a kettle job task configuration file kettle job according to the aforementioned configured table migration logic operation information; then configures the xxl-job task processor JobHandler required for table migration according to the kettle job; Among them, based on the visual interface tool Spoon, a table clearing component, a table input component, a table output component and an SQL script component are respectively configured, and the table data information of the target database that needs to be cleared is collected through the table clearing component, the table data information of the source database that needs to be extracted is collected through the table input component, the table data information that needs to be written into the target database is collected through the table output component, and the table primary key sequence information that needs to be updated is collected through the SQL script component.

8. The device according to claim 7, characterized in that: When configuring the first task processor JobHandler for xxl-job required for table structure migration, obtain the SQL script of the target business table DDL statement corresponding to the aforementioned target database type, and copy it to the project of the kettle batch executor; then configure a database application interface JDBC in the project of the kettle batch executor to connect to the target database; When configuring the second task processor JobHandler for xxl-job required for table data migration, obtain the aforementioned kettle job and copy it to the project of the kettle batch executor; then configure a java code in the project of the kettle batch executor to call the core engine provided by kettle.

9. A method for full migration of heterogeneous databases according to the device of claim 1, characterized in that Includes steps: Obtain application information associated with the source database and close the applications that need to be migrated; Invoke the table structure migration task of the target database in the scheduling management terminal to achieve the table structure migration of the target database; Back up the target database for emergency rollback after migration exceptions; Invoke the table data migration task of the target database in the scheduling management terminal to achieve the table data migration of the target database; Perform data consistency check to ensure the correctness of data after migration; Start the application that needs to be migrated and open requests.

10. A heterogeneous data migration system adapted to the information innovation environment, characterized by: The system includes a source database, a target database and a database migration tool; The database migration tool comprises the device according to any one of claims 1-8.

Images