Database-based migration method, device, and medium
Through the functional tools in the big data integration toolbox, the problem of low migration efficiency in the existing technology is solved and an efficient database migration process is achieved.
Patent Information
- Application Number
- CN202410045514.2
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2024-01-11
- Publication Date
- 2024-11-12
- Estimated Expiration
- 2044-01-11
AI Technical Summary
Existing database migration methods are inefficient and require a lot of manual comparison and adaptation, resulting in more repetitive work and less efficient.
The data to be migrated is processed in a targeted manner through the functional tools in the big data integration toolbox, including obtaining database connection information, determining data types, matching functional tools, generating table creation statements, converting stored process logic, forming blood relationships, etc., to achieve automatic migrate.
It improves the applicability and migration efficiency of the database migration method, reduces manual intervention, realizes automated migration, and saves time and labor costs.
Smart Images

Figure CN117891803B_ABST
Abstract
Description
Technical Field
[0001] The present application relates to the field of database technology, and in particular to a database-based migration method, device, and medium. Background Art
[0002] With the development of technology, every company now has a database system built according to its own business development and product requirements. The data stored in this type of database system varies according to the business and product. Depending on the company's existing business and company products, the database systems built are also different. As a result, many companies have built many database systems to support the development of their own business and products.
[0003] At this point, the current migration method is: table creation, manually compare the relational database table creation DDL statements, adapt the HIVE table data type, and form the HIVE table creation statement. Manually perform logical processing to the SQL syntax executable by the HADOOP cluster. Manually adapt the logical processing stored procedures in the relational database to HSQL transcription. Scheduling configuration, manually configure according to the current scheduling tool. After the migration is completed, export the data into text or Excel files for column-by-column and row-by-row data comparison. Manually search for data with structural differences that cause differences. At this point, the migration process has a lot of repetitive work, and the efficiency of migration configuration is relatively low. Summary of the invention
[0004] The embodiments of the present application provide a database-based migration method, device, and medium, which then perform targeted processing on the data to be migrated based on the functional tools in the big data integration toolbox, at least to a certain extent, and adapt various data to be migrated, so as to ensure the processing effect of the data to be migrated in the big data integration toolbox, improve the applicability and migration efficiency of the database-based migration method, and complete automatic migration.
[0005] Other features and advantages of the present application will become apparent from the following detailed description, or may be learned in part by the practice of the present application.
[0006] According to one aspect of an embodiment of the present application, a database-based migration method is provided, comprising:
[0007] Get the database to be migrated;
[0008] Determine the data to be migrated according to the database to be migrated;
[0009] The big data integration toolbox is triggered based on the data to be migrated and the corresponding functional tools are matched. The big data integration toolbox includes multiple functional tools.
[0010] Perform corresponding migration processing on the data to be migrated according to the functional tools.
[0011] Optionally, after the database connection is successful, obtaining the database to be migrated includes:
[0012] Collect database connection information;
[0013] Determine the connection status of the database based on the connection information of the database;
[0014] If the database connection status is successful, locate the database and define the database to be migrated.
[0015] Optionally, determining the data to be migrated according to the database to be migrated includes:
[0016] Collect information output by the database to be migrated;
[0017] Filter the information and define the data to be migrated.
[0018] Optionally, the big data integration toolbox is triggered based on the data to be migrated, and a corresponding functional tool is matched. The big data integration toolbox includes multiple functional tools, including:
[0019] Get the data to be migrated;
[0020] Match the corresponding data type based on the data to be migrated;
[0021] The big data integration toolbox is triggered according to the data type, and the corresponding functional tool is matched, wherein the big data integration toolbox includes multiple functional tools, and the functional logics executed by the multiple functional tools are different;
[0022] The data to be migrated is processed based on functional tools, and one of multiple functional tools can be used.
[0023] Optionally, the big data integration toolbox is triggered based on the data to be migrated, and a corresponding functional tool is matched. The big data integration toolbox includes multiple functional tools, and also includes:
[0024] If the type of data to be migrated is a data table type, collect the table list;
[0025] Create corresponding table creation statements based on the table list;
[0026] Perform type format conversion on table creation statements to form HIVE tables, and output the table creation statements to the specified target in the form of files.
[0027] Optionally, performing corresponding migration processing on the data to be migrated according to the functional tool includes:
[0028] If the type of data to be migrated is a logical program type, the logical information of the stored procedure is collected;
[0029] Export logical information based on stored procedures to files named after the stored procedures;
[0030] Convert the logical information of the stored procedure into corresponding logical statements;
[0031] Output the logic statements in the form of files to the specified target directory.
[0032] Optionally, performing corresponding migration processing on the data to be migrated according to the functional tool further includes:
[0033] Based on the logical information of the stored procedure, the corresponding HVIE SQL statement is formed;
[0034] Collect basic configuration information of HIVE running scripts based on SQL statements;
[0035] Add basic configuration information to the newly transcribed stored procedure logic program;
[0036] The stored procedure logic program converts the input parameters and own variables in the stored procedure; or, the stored procedure logic program converts the functions that are different between the database and the HIVE library.
[0037] Optionally, performing corresponding migration processing on the data to be migrated according to the functional tool further includes:
[0038] Based on the converted stored procedure logic program, a source table and a target table are formed;
[0039] Determine the blood relationship of the stored procedure logic program based on the processing source relationship between the source table and the target;
[0040] Define the dependencies between layers and programs within the layers based on blood relationships;
[0041] The dependency results are output in the form of a flowchart image to guide the execution level and execution order of HIVE logical file scheduling.
[0042] According to one aspect of an embodiment of the present application, a migration device based on a database is provided, comprising:
[0043] The acquisition module is used to obtain the database to be migrated;
[0044] A data module, used to determine the data to be migrated according to the database to be migrated;
[0045] A matching module, used to trigger a big data integration toolbox based on the data to be migrated and match corresponding functional tools, where the big data integration toolbox includes multiple functional tools;
[0046] The migration module is used to perform corresponding migration processing on the data to be migrated according to the functional tools.
[0047] According to one aspect of an embodiment of the present application, a computer-readable medium is provided, on which a computer program is stored. When the computer program is executed by a processor, the database-based migration method as described in the above embodiment is implemented.
[0048] According to one aspect of an embodiment of the present application, a database is provided, comprising: one or more processors; a storage device for storing one or more programs, wherein when the one or more programs are executed by the one or more processors, the one or more processors implement the database-based migration method as described in the above embodiments.
[0049] According to one aspect of the embodiments of the present application, a computer program product or a computer program is provided, the computer program product or the computer program includes computer instructions, and the computer instructions are stored in a computer-readable storage medium. A processor of a computer device reads the computer instructions from the computer-readable storage medium, and the processor executes the computer instructions, so that the computer device executes the database-based migration method provided in the above embodiments.
[0050] In the technical solutions provided in some embodiments of the present application, a database to be migrated is obtained; the data to be migrated is determined based on the database to be migrated; a big data integration toolbox is triggered based on the data to be migrated, and corresponding functional tools are matched, the big data integration toolbox includes multiple functional tools; corresponding migration processing is performed on the data to be migrated according to the functional tools. At this time, the data to be migrated is targetedly processed according to the functional tools in the big data integration toolbox, and various data to be migrated are adapted to ensure the processing effect of the data to be migrated in the big data integration toolbox, thereby improving the applicability and migration efficiency of the database-based migration method to complete automatic migration.
[0051] It should be understood that the foregoing general description and the following detailed description are exemplary and explanatory only and are not restrictive of the present application. BRIEF DESCRIPTION OF THE DRAWINGS
[0052] The drawings herein are incorporated into the specification and constitute a part of the specification, showing embodiments consistent with the present application, and together with the specification, are used to explain the principles of the present application. Obviously, the drawings described below are only some embodiments of the present application, and for ordinary technicians in this field, other drawings can be obtained based on these drawings without creative work. In the drawings:
[0053] Figure 1A schematic diagram of a database-based migration method according to an embodiment of the present application is shown;
[0054] Figure 2 Shows Figure 1 Schematic diagram of the process of step S110;
[0055] Figure 3 Shows Figure 1 Schematic diagram of the process of step S120;
[0056] Figure 4 Shows Figure 1 Schematic diagram of the process of step S130;
[0057] Figures 5 to 10 A partial schematic diagram of a database-based migration method according to an embodiment of the present application is shown;
[0058] Fig.11 A block diagram of a migration device based on a database according to an embodiment of the present application is shown;
[0059] Fig.12 A schematic diagram of the structure of a computer system suitable for implementing the database of an embodiment of the present application is shown. DETAILED DESCRIPTION
[0060] Example embodiments will now be described more fully with reference to the accompanying drawings. However, example embodiments can be implemented in a variety of forms and should not be construed as limited to the examples set forth herein; rather, these embodiments are provided so that this application will be more comprehensive and complete and fully convey the concept of the example embodiments to those skilled in the art.
[0061] In addition, described feature, structure or characteristic can be combined in one or more embodiments in any suitable manner. In the following description, many specific details are provided to provide a full understanding of the embodiments of the present application. However, those skilled in the art will appreciate that the technical scheme of the present application can be put into practice without one or more of the specific details, or other methods, components, devices, steps, etc. can be adopted. In other cases, known methods, devices, realizations or operations are not shown or described in detail to avoid blurring the various aspects of the application.
[0062] The block diagrams shown in the accompanying drawings are merely functional entities and do not necessarily correspond to physically independent entities. That is, these functional entities may be implemented in software form, or in one or more hardware modules or integrated circuits, or in different networks and / or processor devices and / or microcontroller devices.
[0063] The flowcharts shown in the accompanying drawings are only exemplary and do not necessarily include all the contents and operations / steps, nor must they be executed in the order described. For example, some operations / steps can be decomposed, and some operations / steps can be combined or partially combined, so the actual execution order may change according to actual conditions.
[0064] Figure 1 FIG. 1 is a flow chart of a database-based migration method according to an embodiment of the present application. Figures 1 to 12 The method can be applied to a database. The migration method based on the database includes at least steps S110 to S140, which are described in detail as follows (the following description is made by taking the method applied to a database as an example):
[0065] In step S110, a database to be migrated is obtained.
[0066] In step S120, data to be migrated is determined according to the database to be migrated.
[0067] In step S130, the big data integration toolbox is triggered based on the data to be migrated, and the corresponding functional tools are matched. The big data integration toolbox includes multiple functional tools.
[0068] In step S140 , corresponding migration processing is performed on the data to be migrated according to the functional tool.
[0069] In the technical solutions provided in some embodiments of the present application, after the database connection is successful, the database to be migrated is obtained; the data to be migrated is determined based on the database to be migrated; the big data integration toolbox is triggered based on the data to be migrated, and the corresponding functional tools are matched, and the big data integration toolbox includes multiple functional tools; the data to be migrated is subjected to corresponding migration processing according to the functional tools. At this time, the data to be migrated is processed in a targeted manner according to the functional tools in the big data integration toolbox, and various data to be migrated are adapted to ensure the processing effect of the data to be migrated in the big data integration toolbox, thereby improving the applicability and migration efficiency of the database-based migration method to complete automatic migration.
[0070] After the database connection is successful, in step S110, the database to be migrated is obtained.
[0071] In an embodiment of the present application, databases are associated and communication connections are made between the databases. At this time, after the database connection is successful, the database to be migrated is obtained to facilitate migration processing for the database to be migrated.
[0072] The specific steps are as follows:
[0073] Step S111, collecting database connection information;
[0074] Step S112: determining the connection status of the database based on the connection information of the database;
[0075] Step S113: If the connection status of the database is successful, locate the database and define the database to be migrated;
[0076] Step S114: If the database connection status is a failure status, record the failure information, and write the information collected by the program into a log file through Python;
[0077] In an embodiment of the present application, the connection information of the database is collected so that the connection status of the database can be determined through the connection information of the database, thereby ensuring the subsequent triggering of the database. At this time, if the connection status of the database is a successful state, the database is located and the database to be migrated is defined.
[0078] The program reads the database configuration information file, which contains the connection string, user, password and other connection information of the migration database, and reads the table list configuration information that needs to be migrated, which contains the name of the migrated table, and the table name is stored in the form of rows. Python reads the above configuration information into memory. At this time, it connects to the migration database through the read database connection information, and then determines whether the database connection is successful. If the connection is successful, execute the next step. If the database connection status is a failure status, record the failure information, and write the information collected by the program into the log file through Python.
[0079] In step S120, data to be migrated is determined according to the database to be migrated.
[0080] In an embodiment of the present application, the database to be migrated is located so as to perform data processing according to the database to be migrated, thereby determining the data to be migrated according to the database to be migrated, thereby ensuring the processing of the data to be migrated.
[0081] The specific steps are as follows:
[0082] Step S121, collecting information output by the database to be migrated;
[0083] Step S122: Filter information and define data to be migrated;
[0084] In an embodiment of the present application, the output of the database to be migrated is monitored to collect information output by the database to be migrated, thereby filtering the information and further defining the data to be migrated, thereby ensuring the processing of the data to be migrated.
[0085] In step S130, the big data integration toolbox is triggered based on the data to be migrated, and the corresponding functional tools are matched. The big data integration toolbox includes multiple functional tools.
[0086] In an embodiment of the present application, a big data integration toolbox is triggered based on the data to be migrated so as to utilize various functional tools in the big data integration toolbox, thereby performing targeted processing on the data to be migrated according to the functional tools in the big data integration toolbox, and adapting various data to be migrated, so as to ensure the processing effect of the data to be migrated in the big data integration toolbox, improve the applicability and migration efficiency of the database-based migration method, and complete automatic migration.
[0087] The specific steps are as follows:
[0088] Step S131, obtaining data to be migrated;
[0089] Step S132, matching corresponding data types based on the data to be migrated;
[0090] In an embodiment of the present application, data to be migrated is obtained, and type identification is performed on the data to be migrated so as to match corresponding data types based on the data to be migrated, thereby further processing the data types so as to trigger a big data integration toolbox.
[0091] Step S133: triggering the big data integration toolbox according to the data type and matching the corresponding functional tool, wherein the big data integration toolbox includes multiple functional tools, and the functional logics executed by the multiple functional tools are different;
[0092] Step S134: Process the data to be migrated based on a functional tool, and use one of multiple functional tools;
[0093] In an embodiment of the present application, the big data integration toolbox is triggered according to the data type and the corresponding functional tools are matched. Therefore, the big data integration toolbox is triggered based on the data to be migrated so as to utilize the various functional tools in the big data integration toolbox, thereby performing targeted processing on the data to be migrated according to the functional tools in the big data integration toolbox and adapting to various data to be migrated.
[0094] At this time, the big data integration toolbox includes multiple functional tools, and the functional logics executed by the multiple functional tools are different; data processing is performed on the data to be migrated based on the functional tools, and one of the multiple functional tools is selected for use.
[0095] In step S140 , corresponding migration processing is performed on the data to be migrated according to the functional tool.
[0096] In an embodiment of the present application, if the type of data to be migrated is a data table type, a table list is collected; a corresponding table creation statement is created based on the table list; a type format conversion is performed on the table creation statement to form a HIVE table, and the table creation statement is output to a specified target in the form of a file.
[0097] Therefore, the present invention is based on the database table export conversion tool in the big data integration toolbox of the HADOOP cluster, which is written in Python programming language. The function implemented is to obtain the table creation statement that needs to be migrated from the original database to be migrated, and convert the table field type in the table creation statement through the Python program to meet the creation of the HIVE table. Finally, the converted table creation statement is output to the specified target directory in the form of a file.
[0098] In some embodiments of the present application, the database migration method provided includes the following steps, see Figure 5 , which may include the following steps:
[0099] (1) The program reads the database configuration information file, which contains the connection string, user, password and other connection information of the migration database, and also reads the table list configuration information that needs to be migrated, which contains the table names to be migrated, and the table names are stored in the form of rows. Python reads the above configuration information into memory and executes step (2).
[0100] (2) Connect to the migration database using the database connection information read in step (1), and execute step (3) to make a connection determination.
[0101] (3) Determine whether the database connection is successful. If the connection is successful, execute step (4). If the connection is successful, execute step (6).
[0102] (4) After the database connection fails, record the failure information and execute step (5) to write to the log file.
[0103] (5) Write the information collected by the program to the log file through Python
[0104] (6) After the database connection is successful, the export list judgment is executed to determine whether to export the creation statement according to the export table list. If it is exported according to the table list, step (7) is executed; otherwise, step (9) is executed.
[0105] (7) Export the table creation statement according to the configured table list, and obtain the table list data stored in the memory in step (1). Execute step (8) to read the table creation statement in the database.
[0106] (8) Through the database connected in step (2), execute and read the database table creation statement, obtain the table creation statement and store it in the memory, and execute step (10).
[0107] (9) If the table list does not contain configuration information, the program reads all table creation statements under the user's permissions, obtains the table creation statements, stores them in memory, and executes step (10).
[0108] (10) The table creation statement stored in the memory is converted into the HVIE table type format, including but not limited to date type conversion, data type conversion, string type conversion, and other special type conversions. After the conversion is completed, it is determined whether the Chinese field is included. If the Chinese field is included, step (11) is executed, otherwise step (12) is executed.
[0109] (11) If the table creation statement contains Chinese field names that are not recognized by the HIVE table, the program will record this information and request manual confirmation.
[0110] (12) After the table creation statement type conversion is completed, the table creation statement is exported to a file, and step (13) is executed to determine whether the file is exported successfully.
[0111] (13) Determine whether the table creation statement is exported successfully. If the export fails, execute step (4) to perform log recording processing. If the export is successful, execute step (14) to export the table creation statement to a file.
[0112] (14) Export the table creation statements in the memory after type conversion based on the HIVE table creation rules to a file, and execute step (15) to export the generated file.
[0113] (15) Use the Python program to store the file in the specified directory.
[0114] In addition, the corresponding migration processing of the data to be migrated according to the functional tool also includes: if the type of the data to be migrated is a logic program type, collecting the logic information of the stored procedure; exporting the logic information based on the stored procedure to a file named after the stored procedure; converting the logic information of the stored procedure into corresponding logic statements; and outputting the logic statements in the form of a file to a specified target directory.
[0115] Therefore, the present invention is based on the stored procedure export tool in the big data integration toolbox of the HADOOP cluster, written in Python programming language, and the function implemented is to obtain the stored procedure logic program that needs to be migrated from the original database to be migrated, and adapt the logic program to HIVE statement conversion through the Python program to meet the effective logic execution of HIVE. Finally, the converted logic statement is output to the specified target directory in the form of a file.
[0116] In some embodiments of the present application, the database migration method provided includes the following steps, see Figure 6, which may include the following steps:
[0117] (1) The program reads the data connection information from the database configuration information and the export configuration information and stores it in the memory, and executes step (2) to connect to the database.
[0118] (2) Connect to the migration database according to the database connection configuration information in the memory, and the database connection status is determined by step (3).
[0119] (3) Determine whether the database connection is successful. If the connection fails, execute step (4); if the database connection is successful, execute step (6).
[0120] (4) If the database connection fails, the connection error information is recorded in the log and a log file is formed through step (5).
[0121] (5) Based on the program information obtained in the memory, the Python program writes the information into the log file in the target directory.
[0122] (6) The database connection is successful, and step (6) is executed to obtain the stored procedure logic information. Step (7) will be executed to obtain the logic information.
[0123] (7) The program reads all stored process logic information under the user's authority into the memory, and exports the information data through step (8).
[0124] (8) The program reads the stored procedure information obtained in step (7) through the memory and exports it to a file named after the stored procedure, and judges the export operation through step (9).
[0125] (9) If the export fails, execute step (10); if the export succeeds, execute step (11).
[0126] (10) If the program fails to be exported, log information is recorded and the error information is output to the log file through step (5).
[0127] (11) After the export is successful, a file named after the stored procedure is generated and written to the specified directory.
[0128] In addition, the corresponding migration processing of the data to be migrated according to the functional tool also includes: forming a corresponding HVIE SQL statement based on the logical information of the stored procedure; collecting basic configuration information of the HIVE running script according to the SQL statement; adding the basic configuration information to the newly transcribed stored procedure logic program; the stored procedure logic program will convert the incoming parameters and own variables in the stored procedure; or, the stored procedure logic program converts the functions that are different between the database and the HIVE library.
[0129] At this time, the present invention is based on the stored procedure logic conversion tool in the big data integration toolbox of the HADOOP cluster, which is written in Python programming language. The function realized is to convert the stored procedure logic program content of the traditional relational database into executable HIVE execution SQL by formatting to meet the effective logic execution of HIVE. Finally, the converted traditional database stored procedure logic statement is output to the specified target directory in the form of a file.
[0130] In some embodiments of the present application, the database migration method provided includes the following steps, see Figure 8 , which may include the following steps:
[0131] (1) The program reads the source stored procedure file exported from the traditional database into the memory, obtains the source stored procedure logic statement, and obtains (2) the stored procedure statement data.
[0132] (2) The Python program reads the logic program content of the source stored procedure file into the system memory, and executes step (3) to start transcribing the adapted HVIE SQL statement.
[0133] (3) The transcription program will transcribe the original stored procedure comments according to the rules of HIVE executable statements, mainly identifying multi-line comment statements that HIVE does not support, and transcribe them into comment statements that HIVE supports and recognizes. After the transcription is completed, execute step (4).
[0134] (4) The transcription program will add the basic configuration information of the HIVE running script according to the rules, and add the executable script configuration information into the newly transcribed stored procedure logic program. After the addition is completed, execute step (5).
[0135] (5) The program will convert the incoming parameters and own variables in the stored procedure, mainly identifying the select into method as variable assignment and converting it to the set method as variable assignment to support HIVE recognition. After the conversion is completed, execute step (6) to continue converting the stored procedure logic for HIVE adaptability.
[0136] (6) The program converts the functions that are different between the traditional database and the HIVE library, such as converting the time function localtimestamp to current_timestamp, converting to_char to date_format, and type conversion: converting to cast as, etc. The functions that HIVE does not recognize are converted to equivalent values. After the function adaptability conversion is completed, step (7) is executed.
[0137] (7) The program will identify the delete statement and perform format conversion. The operation logic is to convert the delete statement into an alter table drop partition statement with a reference context. If a multiple-condition delete statement is encountered, it will be highlighted and not converted. After the delete statement format adaptation conversion is completed, step (8) will be executed.
[0138] (8) The program will identify the insert statement and perform format conversion on the insert statement. Since HIVE is case-sensitive, while many traditional databases are not, the program will perform uppercase to lowercase format conversion and convert the insert statements and fields such as insert and insert select to lowercase. After the format adaptation conversion of the insert statement is completed, the conversion in step (9) will be performed.
[0139] (9) This section will identify and delete statements that HIVE does not support and that have no impact on the main logic of the program, such as log writing programs, execution judgment programs, program execution step debugging programs, etc., and delete and comment out this part of the code logic. At this point, the traditional database stored procedure logic conversion is basically completed, and step (10) will be executed to replace the changed table name.
[0140] (10) The program will read the table name configuration file and uniformly replace the table names that need to be replaced in the configuration file. If table name replacement is required, the program can automatically replace the table names involved in the logic by configuring the pre-replaced table name and the replaced table name in the table name configuration file. If the configuration file is not configured, the table name replacement will not be performed. After the table name replacement is completed, the traditional database stored procedure logic is adapted to HIVE formatting and transcribed, and step (11) will be executed to output the transcribed program logic file.
[0141] (11) The stored process logic file that has been transcribed by the rules is stored in the memory through program logic, and the file is output through the file output execution step (12).
[0142] (12) The output file will be named .hql file with the schema to which the stored procedure belongs plus the name of the stored procedure. After the file is output to the specified directory, step (13) will be executed.
[0143] (13) Collect the log information of the system and preset program data in the process of converting the traditional data volume storage process logic file into a record log, and output the record log in the form of a file to a specified log directory.
[0144] In addition, the corresponding migration processing of the data to be migrated according to the functional tool also includes: forming a source table and a target table based on the converted stored procedure logic program; determining the blood relationship of the stored procedure logic program according to the processing source relationship between the source table and the target; defining the mutual dependency results between the hierarchy and the programs within the hierarchy according to the blood relationship; and outputting in the form of a flowchart image based on the dependency results to guide the execution hierarchy and execution order of HIVE logic file scheduling.
[0145] The present invention is based on the scheduling relationship analysis tool in the big data integration toolbox of the HADOOP cluster, written in Python programming language, and realizes the function of distinguishing the source table and the target table through logical analysis of the converted stored procedure, and determining the logical blood relationship according to the processing source relationship between the source table and the target, which is used to assist in defining the execution level of the logic program and the mutual dependence relationship between programs within the level. The hierarchical dependency results of the analysis are output in the form of a flowchart image to guide the execution level and execution order of HIVE logic file scheduling.
[0146] In some embodiments of the present application, the database migration method provided includes the following steps, see Figure 7 , which may include the following steps:
[0147] (1) The program reads the stored procedure file to be analyzed and stores all the program information of the stored procedure file in the memory, and then executes step (2) to analyze the logic of the stored procedure.
[0148] (2) The program performs table analysis on the stored procedure logic program read from the near memory in step (1) according to the table judgment rule. The purpose is to perform type analysis on the tables used in the logic and analyze the source table and the target table. The rule used is to judge the data table immediately after insert into and from, and perform logical deduplication judgment on the scanned table. Finally, the source table and the target table of the logic are comprehensively judged, and step (3) is performed to obtain the table judgment result.
[0149] (3) The program performs step (4) on the source table and target table to sort out the table relationships based on the judgment rules of the source table and the target table after comprehensive deduplication judgment.
[0150] (4) Perform a full sequence up and down search based on the determined source table and target table to find out its specific source and whether there is a downstream consumption destination, so as to sort out the relationship between the tables. After the program has sorted out the corresponding relationship between the tables, it executes step (5) to sort the stored procedure hierarchy.
[0151] (5) The program analyzes the initial table source and consumption link according to step (4), finds the relevant stored procedure logic, and sorts the corresponding stored procedure logic program by level and dependency. The rule is: the source is searched upward to the initial source, which is defined as the initial level, the target is defined as the last level, and the logic generated in the middle is defined as the middle level. And so on, the level and dependency of the stored procedure logic involved are sorted. At this step, the lineage analysis of the execution level and execution dependency of the stored procedure logic is completed, and step (6) will be executed to process the simulation scheduling configuration information.
[0152] (6) Based on the stored procedure execution level and execution sequence obtained in step (5), the program automatically generates the scheduling call configuration information of the stored procedure logic, and at the same time executes step (7) to output the configuration information stored in the memory.
[0153] (7) Based on the scheduling information in the memory, the program automatically generates a scheduling relationship flow chart, and executes step (9) to output the picture. At the same time, the program running log information and the program preset log information are logged through step (8).
[0154] (8) Record various types of log information generated during the execution of the scheduling relationship analysis program and output them to the specified directory in the form of log files.
[0155] (9) Use open source tools to output the execution hierarchy and dependency flowchart obtained from program analysis to an image and then output the image to a specified directory.
[0156] In addition, the multi-database data export tool in the big data integration toolbox based on the HADOOP cluster is written in JAVA programming language, and the function realized is to export the table data in the migration database and the migrated HIVE table data through the program, and export the exported data to the specified directory in the form of CSV file. It prepares for the comparison of table data before and after the subsequent data table migration, saving the time cost of traditional manual export.
[0157] In some embodiments of the present application, the database migration method provided includes the following steps, see Fig. 9 , which may include the following steps:
[0158] (1) The program will read the database connection configuration file and the export table data list file, store the configuration and export table list information in the memory, and execute step (2) to migrate the database connection.
[0159] (2) The program will migrate the database connection based on the database configuration information read into the memory in step (1), and the connection result will be judged in step (3).
[0160] (3) If the database connection fails, execute step (4); if the database connection succeeds, execute step (5).
[0161] (4) If the database connection fails, the program will record the failure information in the log and output the log information to the log file in the specified directory.
[0162] (5) After the database connection is successful, the program will read the table list information into the memory according to step (1) to obtain the data of the table under the migration database. The program will read the table data and execute step (6) to export the data.
[0163] (6) Based on the table data information read by the program, the table data information is exported to a CSV file and the file is stored in a specified directory. After the export is completed, step (7) will be executed to determine the validity of the export process.
[0164] (7) If the result returned by the export program is judged to be unsuccessful, the program will execute step (5) to start trying to export again. If the result returned by the export program is judged to be successful, step (8) will be executed to export the HIVE table data.
[0165] (8) The program obtains the HIVE library connection information in the memory according to step (1), connects to the HIVE library, and after the connection is successful, executes step (9) to export the HIVE table data.
[0166] (9) The program exports the read HIVE table data to a CSV file. After the export is completed, step (10) is executed to determine the execution result of the HIVE table data export program.
[0167] (10) If the HIVE table export program fails or terminates unexpectedly, execute step (8) to try to export the HIVE table data again. If the HIVE table data export program is successfully executed, execute step (11) to determine whether to export the data of the next table.
[0168] (11) Determine step (1) to obtain the table list data in the memory. If the table list does not reach the last row, execute step (2) to export the next data table. If the table data list is the last row, the configured export table data list table has been fully exported, and execute step (12) to terminate the program.
[0169] (12) The program outputs all log information generated by the database data export tool during execution and the program preset log information to the record log, and outputs it to the specified directory in the form of a file.
[0170] In addition, the present invention is based on the migration table data verification tool in the big data integration toolbox of the HADOOP cluster, which is written in the Python programming language. The function realized is to export the table data in the migration database and the migrated HIVE table data through the program for equivalence verification, and output the detailed information of the comparison result in the form of a file, which is conducive to developers to quickly locate the problem program and the problem table in the problem program, and can be used to save the cost and time of manual testing, and at the same time improve the developers' ability to investigate the causes of inconsistent result data tables and repair and solve problems.
[0171] In some embodiments of the present application, the database migration method provided includes the following steps, see Fig.10 , which may include the following steps:
[0172] (1) The migration table data verification program will read the source table CSV file and the target HIVE table CSV file (these two files are exported by the multi-database export tool). The data information of the two tables is read into the memory. Then, step (2) is performed to start the data verification before and after the table migration.
[0173] (2) First, the program checks the total number of data in the target table and HIVE table obtained in step (1) in the memory. The total number of data in the table before and after the migration is compared in terms of data magnitude. The comparison result of the total number of data is determined in step (3).
[0174] (3) If the total number of data in the source table before migration is inconsistent with the total number of data in the HIVE target table after migration, the total number of data in the source table before migration and the total number of hops in the HIVE target table after migration are recorded in the result log and output in the form of a file to the specified log directory. The program will proceed to step (1) to continue the comparison of the source table before migration and the HIVE table after migration. If the total number of data in the comparison before and after migration is consistent, step (4) will be executed to perform the comparison operation of the source table before migration and the HIVE target table after migration.
[0175] (4) After the total data volume before and after the table migration is compared and found to be consistent, the program will read the field information of the source table before the migration and the HIVE target table after the migration, and perform intersection processing on the field information of the two tables. The intersection field information of the source table before the migration and the HIVE table after the migration will be stored in the memory, and the table field association will be performed in step (5).
[0176] (5) The program performs a full outer join on the source table field information before migration obtained in step (4) and the HIVE target table field information after migration. After the full outer join on the fields, step (6) is executed to check the values.
[0177] (6) Based on the full outer join association result of the intersection fields of the two tables before and after the migration in step (5), the field values are checked one by one, and after the check, the field value matching judgment is performed. If the field values are inconsistent, step (7) is executed; if the field values are consistent, step (9) is executed.
[0178] (7) After the field is fully outer-joined, if the source table before migration and the HIVE target table after migration have inconsistent values, the program will record the inconsistent table, field, and value information in the comparison result, and the recorded comparison result will export the information to the comparison result file in the specified directory. Then execute step (8) to perform the next field comparison judgment.
[0179] (8) Determine whether the field to be checked is the last field. If it is not the last field, execute step (6) to compare the next field. If it is the last field, execute step (10) to output the final comparison result.
[0180] (9) If the field values of the source table before migration and the HIVE target table after migration are completely consistent, the next field comparison is performed. Determine whether the field to be compared is the last field. If it is not the last field, execute step (6) to compare the next field. If it is the last field, execute step (10) to output the final comparison result.
[0181] (10) The source table data before migration and the HIVE target table data after migration are fully compared. The program will record the detailed comparison result information and output the structure information to the comparison result file in the specified directory.
[0182] At this time, the big data integration toolkit based on the HADOOP cluster of the present invention is used to migrate the traditional database to the HIVE big data platform, which has the following advantages: it solves the problem of low efficiency and low accuracy of manual transcription of the type conversion of the source database table creation statement. A large number of traditional database storage process logics are uniformly and automatically transcribed to support the transcription program in HIVE executable format, which can save the manpower and time cost of manual transcription, and at the same time improve the program specification and transcription accuracy of the logic program. According to the data source table and HIVE table data automatic export tool, through the configurable export table list, the data is exported to the specified directory at the same time, and a data CSV file is formed. Improve data export efficiency. The automated comparison tool can quickly compare the table data before and after the migration, and the comparison granularity reaches the field value level equivalence comparison, which can be compared every day. The efficiency and accuracy of the data quality check of the data table after migration are greatly improved, while reducing the project manpower and time costs.
[0183] In the technical solutions provided in some embodiments of the present application, after the database connection is successful, the database to be migrated is obtained; the data to be migrated is determined based on the database to be migrated; the big data integration toolbox is triggered based on the data to be migrated, and the corresponding functional tools are matched, and the big data integration toolbox includes multiple functional tools; the data to be migrated is subjected to corresponding migration processing according to the functional tools. At this time, the data to be migrated is processed in a targeted manner according to the functional tools in the big data integration toolbox, and various data to be migrated are adapted to ensure the processing effect of the data to be migrated in the big data integration toolbox, thereby improving the applicability and migration efficiency of the database-based migration method to complete automatic migration.
[0184] The following describes an apparatus embodiment of the present application, which can be used to execute the database-based migration method in the above-mentioned embodiment of the present application. For details not disclosed in the apparatus embodiment of the present application, please refer to the above-mentioned embodiment of the database-based migration method of the present application.
[0185] Fig.11 A block diagram of a database-based migration device according to an embodiment of the present application is shown.
[0186] Reference Fig.11 As shown, a migration device based on a database according to an embodiment of the present application includes:
[0187] The acquisition module 210 is used to acquire the database to be migrated after the database connection is successful;
[0188] The data module 220 is used to determine the data to be migrated according to the database to be migrated;
[0189] A matching module 230 is used to trigger a big data integration toolbox based on the data to be migrated and match corresponding functional tools, where the big data integration toolbox includes multiple functional tools;
[0190] The migration module 240 is used to perform corresponding migration processing on the data to be migrated according to the functional tool.
[0191] Fig.12 A schematic diagram of the structure of a computer system suitable for implementing the database of an embodiment of the present application is shown.
[0192] It should be noted that Fig.12 The computer system of the database shown is only an example and should not bring any limitation to the functions and scope of use of the embodiments of the present application.
[0193] like Fig.12As shown, the computer system includes a central processing unit (CPU) 301, which can perform various appropriate actions and processes according to the program stored in the read-only memory (ROM) 302 or the program loaded from the storage part 308 to the random access memory (RAM) 303, such as executing the method described in the above embodiment. In RAM 303, various programs and data required for system operation are also stored. CPU 301, ROM 302 and RAM 303 are connected to each other through bus 304. Input / output (I / O) interface 305 is also connected to bus 304.
[0194] The following components are connected to the I / O interface 305: an input section 306 including a keyboard, a mouse, etc.; an output section 307 including a cathode ray tube (CRT), a liquid crystal display (LCD), etc., and a speaker; a storage section 308 including a hard disk, etc.; and a communication section 309 including a network interface card such as a LAN (Local Area Network) card, a modem, etc. The communication section 309 performs communication processing via a network such as the Internet. A drive 310 is also connected to the I / O interface 305 as needed. A removable medium 311, such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, etc., is installed on the drive 310 as needed so that a computer program read therefrom is installed into the storage section 308 as needed.
[0195] In particular, according to an embodiment of the present application, the process described above with reference to the flowchart can be implemented as a computer software program. For example, an embodiment of the present application includes a computer program product, which includes a computer program carried on a computer-readable medium, and the computer program includes a computer program for executing the method shown in the flowchart. In such an embodiment, the computer program can be downloaded and installed from a network through a communication section 309, and / or installed from a removable medium 311. When the computer program is executed by a central processing unit (CPU) 301, various functions defined in the system of the present application are executed.
[0196] It should be noted that the computer-readable medium shown in the embodiment of the present application may be a computer-readable signal medium or a computer-readable storage medium or any combination of the above two. The computer-readable storage medium may be, for example, but not limited to, a system, device or device of electricity, magnetism, light, electromagnetic, infrared, or semiconductor, or any combination of the above. More specific examples of computer-readable storage media may include, but are not limited to: an electrical connection with one or more wires, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM), a flash memory, an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the above. In the present application, a computer-readable storage medium may be any tangible medium containing or storing a program, which may be used by an instruction execution system, device or device or used in combination with it. In the present application, a computer-readable signal medium may include a data signal propagated in a baseband or as part of a carrier wave, wherein a computer-readable computer program is carried. Such propagated data signals may take a variety of forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination of the above. Computer-readable signal media may also be any computer-readable medium other than computer-readable storage media, which may send, propagate, or transmit programs for use by or in conjunction with an instruction execution system, apparatus, or device. The computer program contained on the computer-readable medium may be transmitted using any appropriate medium, including but not limited to: wireless, wired, etc., or any suitable combination of the above.
[0197] The flowchart and block diagram in the accompanying drawings illustrate the possible architecture, functions and operations of the system, method and computer program product according to various embodiments of the present application. Wherein, each box in the flowchart or block diagram can represent a module, a program segment, or a part of the code, and the above-mentioned module, program segment, or a part of the code contains one or more executable instructions for realizing the specified logical function. It should also be noted that in some alternative implementations, the functions marked in the box can also occur in a different order from the order marked in the accompanying drawings. For example, two boxes represented in succession can actually be executed substantially in parallel, and they can sometimes be executed in the opposite order, depending on the functions involved. It should also be noted that each box in the block diagram or flowchart, and the combination of boxes in the block diagram or flowchart can be implemented with a dedicated hardware-based system that performs a specified function or operation, or can be implemented with a combination of dedicated hardware and computer instructions.
[0198] The units involved in the embodiments described in this application may be implemented by software or hardware, and the units described may also be set in a processor. The names of these units do not, in some cases, constitute limitations on the units themselves.
[0199] As another aspect, the present application also provides a computer-readable medium, which may be included in the database described in the above embodiment; or may exist independently without being assembled into the database. The above computer-readable medium carries one or more programs, and when the above one or more programs are executed by the database, the database implements the method described in the above embodiment.
[0200] It should be noted that, although several modules or units of the equipment for action execution are mentioned in the above detailed description, this division is not mandatory. In fact, according to the embodiments of the present application, the features and functions of two or more modules or units described above can be embodied in one module or unit. On the contrary, the features and functions of one module or unit described above can be further divided into being embodied by multiple modules or units.
[0201] Through the description of the above implementation methods, it is easy for those skilled in the art to understand that the example implementation methods described here can be implemented by software or by combining software with necessary hardware. Therefore, the technical solution according to the implementation methods of the present application can be embodied in the form of a software product, which can be stored in a non-volatile storage medium (which can be a CD-ROM, a USB flash drive, a mobile hard disk, etc.) or on a network, and includes several instructions to enable a computing device (which can be a personal computer, a server, a touch terminal, or a network device, etc.) to execute the method according to the implementation methods of the present application.
[0202] Those skilled in the art will readily appreciate other embodiments of the present application after considering the specification and practicing the embodiments disclosed herein. The present application is intended to cover any variations, uses or adaptations of the present application, which follow the general principles of the present application and include common knowledge or customary technical means in the art that are not disclosed in the present application.
[0203] It should be understood that the present application is not limited to the precise structures that have been described above and shown in the drawings, and that various modifications and changes may be made without departing from the scope thereof. The scope of the present application is limited only by the appended claims.
Claims
1. A database-based migration method, characterized in that: include: Obtain the database to be migrated, and determine the data to be migrated according to the database to be migrated; The big data integration toolbox is triggered based on the data to be migrated, and the corresponding functional tools are matched. The big data integration toolbox includes multiple functional tools with different logical functions. The data to be migrated is migrated accordingly according to the functional tools. The big data integration toolbox includes a database table export conversion tool, which realizes the function of obtaining the table creation statement to be migrated from the original database to be migrated, and converting the table field type in the table creation statement through the Python program to meet the creation of the HIVE table, and finally outputting the converted table creation statement in the form of a file to the specified target directory; The big data integration toolbox includes a stored procedure logic conversion tool, which converts the stored procedure logic program content of the traditional relational database into executable HIVE execution SQL by formatting to meet the effective logic execution of HIVE, and finally outputs the converted traditional database stored procedure logic statement in the form of a file to a specified target directory; The big data integration toolbox includes a multi-database data export tool, which exports the table data in the migration database and the migrated HIVE table data, and saves the exported data in the form of a CSV file to a specified directory; The big data integration toolbox includes a scheduling relationship analysis tool, and the corresponding migration processing of the data to be migrated according to the functional tool also includes: forming a source table and a target table based on the converted stored procedure logic program; determining the blood relationship of the stored procedure logic program according to the processing source relationship between the source table and the target table; defining the mutual dependency results between the hierarchy and the programs within the hierarchy according to the blood relationship; outputting the dependency results in the form of a flowchart image to guide the execution hierarchy and execution order of HIVE logic file scheduling; The big data integration toolbox includes a migration table data verification tool, which is used to verify the total number of entries in the source table before migration with the total number of entries in the target table after migration, and based on the successful verification of the total number of entries, read the field information of the source table before migration and the target table after migration, perform intersection processing on the field information of the source table and the target table, perform field full outer join association on the data after the intersection processing, verify the field values one by one, and output the field verification results to the target directory; The database-based migration method also includes: reading the source stored procedure file exported from the traditional database into the memory, obtaining the source stored procedure logic statement, and obtaining the stored procedure statement data, reading the source stored procedure file logic program content into the system memory, and performing transcription adapted to HIVE and SQL statements, transcribing the original stored procedure comments according to the rules of HIVE executable statements, converting the incoming parameters and own variables in the stored procedure, converting the functions that are different between the traditional database and the HIVE library, identifying the delete statement and performing format conversion, identifying the insert statement and performing format conversion on the insert statement, identifying and deleting the statements that are not supported by HIVE and have no impact on the main logic of the program, reading the table name configuration file, and uniformly replacing the table names that need to be replaced in the configuration file.
2. The method according to claim 1, characterized in that The method further includes: reading a configuration information file of a database, connecting to the database to be migrated based on the configuration information file, and if the database to be migrated is successfully connected, determining the data to be migrated according to the database to be migrated; After the database connection is successful, obtaining the database to be migrated includes: Collect database connection information; Determine the connection status of the database based on the connection information of the database; If the database connection status is successful, locate the database and define the database to be migrated; If the database connection status is a failure status, the failure information is recorded and the information collected by the program is written into a log file.
3. The method according to claim 2, characterized in that The step of determining the data to be migrated according to the database to be migrated includes: Collect information output by the database to be migrated; Filter the information and define the data to be migrated.
4. The method according to claim 1, characterized in that: The big data integration toolbox is triggered based on the data to be migrated, and the corresponding functional tools are matched. The big data integration toolbox includes multiple functional tools, including: Get the data to be migrated; Match the corresponding data type based on the data to be migrated; The big data integration toolbox is triggered according to the data type, and the corresponding functional tool is matched, wherein the big data integration toolbox includes multiple functional tools, and the functional logics executed by the multiple functional tools are different; The data to be migrated is processed based on functional tools, and one of multiple functional tools can be used.
5. The method according to claim 4, characterized in that The corresponding migration processing of the data to be migrated according to the functional tool includes: If the type of data to be migrated is a data table type, collect the table list; Create corresponding table creation statements based on the table list; Perform type format conversion on table creation statements to form HIVE tables, and output the table creation statements to the specified target in the form of files.
6. The method according to claim 4, characterized in that The performing corresponding migration processing on the data to be migrated according to the functional tool also includes: If the type of data to be migrated is a logical program type, the logical information of the stored procedure is collected; Export logical information based on stored procedures to files named after the stored procedures; Convert the logical information of the stored procedure into corresponding logical statements; Output the logic statements in the form of files to the specified target directory.
7. The method according to claim 6, characterized in that The performing corresponding migration processing on the data to be migrated according to the functional tool also includes: Based on the logical information of the stored procedure, the corresponding HVIE SQL statement is formed; Collect basic configuration information of HIVE running scripts based on SQL statements; Add basic configuration information to the newly transcribed stored procedure logic program; The stored procedure logic program converts the input parameters and own variables in the stored procedure; or, the stored procedure logic program converts the functions that are different between the database and the HIVE library.
8. The method according to claim 7, characterized in that The performing corresponding migration processing on the data to be migrated according to the functional tool also includes: Based on the converted stored procedure logic program, a source table and a target table are formed; Determine the blood relationship of the stored procedure logic program based on the processing source relationship between the source table and the target; Define the dependencies between layers and programs within the layers based on blood relationships; The dependency results are output in the form of a flowchart image to guide the execution level and execution order of HIVE logical file scheduling.
9. A computer readable medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the database-based migration method according to any one of claims 1 to 8 is implemented.
