Traffic management data fusion migration method and system

Abstract

According to the traffic management data fusion migration method provided by the invention, the data of the source database is synchronized to the intermediate database, and then the data of the intermediate database is fused and migrated to the target database; according to the method and the device, the fusion migration program is constructed based on the business logic for different types of data, so that data fusion can be quickly and accurately realized when multiple pieces of source database data are merged into the target database, and the data validity of the target database is ensured. The invention further discloses a traffic management data fusion migration system.

Description

Technical Field

[0001] The present invention relates to the technical field of data migration, and in particular to a traffic control data fusion migration method and system. Background Art

[0002] In the early days, some provinces adopted a centralized deployment model for traffic management services, with data stored in decentralized databases across various regions. However, with the increasing demand for information-based traffic management, the existing deployment model no longer meets the evolving requirements of the technical framework. On the one hand, regional operational and maintenance capabilities vary widely, hindering the application of new technologies and frameworks, as well as the implementation of various policies that benefit the public, thus impacting operational efficiency. On the other hand, data stored in various locations cannot be effectively utilized or shared, hindering comprehensive data analysis and reporting. Therefore, the need to stably and efficiently integrate and migrate traffic management data across regions has become a pressing issue. Summary of the Invention

[0003] To address the problem that the existing centralized deployment model of business detachments is not adaptable to the continuous evolution and improvement of new technology frameworks, the present invention provides a traffic management data fusion and migration method that can safely and efficiently perform data fusion and migration, and is particularly suitable for the continuous centralized fusion and migration of multiple remote relational databases. This application also discloses a traffic management data fusion and migration system.

[0004] The technical solution of the present invention is as follows: a traffic control data fusion migration method, characterized in that it includes the following steps: S1: Create intermediate library, proxy library and target library; The target database is a data migration and fusion target database of a remote source database; The proxy library is used for data migration; The intermediate database temporarily stores the data of the source database; Create multiple intermediate database users in the target database to receive source data from the source database; the target database and the source database maintain the same system upgrade strategy to ensure consistency of data objects and primary keys; S2: Data synchronization between the source database and the intermediate database; Use the source database as the data source, configure data synchronization tasks from multiple remote data sources to the intermediate database, and complete data synchronization before the official migration; The source data and the intermediate database are synchronized through data synchronization software. The source data in the source database and the intermediate database data must be consistent and synchronized in real time. When the source database table structure, data, sequence and other database objects change, the target intermediate database will also change. S3: Generate data migration program between the intermediate database and the target database; Based on the corresponding data characteristics of the target and intermediate databases, combined with business strategies and grouping strategies, a migration program code template is dynamically generated using code templates. These data characteristics include database and table structure, primary keys, and data volume. Based on this, business developers customize the fusion conversion business logic for specific tables. S4: Configure the task information of the proxy database data migration task; For each data migration task, the configuration items in the configuration table corresponding to the task information mainly include: intermediate library related information, target library related information, proxy library related information, maximum number of execution threads, execution program code template, execution program entry, and check program entry; The proxy library can operate all objects in the intermediate library and the target library, and can grant the intermediate library the right to operate the objects in the target library; S5: Execute the migration task; Read the task information and execute the configured data migration tasks in batches; each group of data migration tasks contains data migration configuration information from multiple intermediate libraries to target libraries in the same city; configure data migration tasks and corresponding execution programs from multiple intermediate library users to multiple target library users in the proxy library execution library.

[0005] It is further characterized by: It also includes the following steps: S6: Data migration result verification and data correction; Specifically, it includes: verifying migration results based on migration logs, generating migration reports, repairing migrated data, and monitoring migration process anomalies; The proxy library stores code templates; The code template is a general program file, specifically including: a program name, an execution program header, and an execution program body. The program name is used to uniquely identify each fusion migration program; the execution program header stores the definition file used when the program is running; and the execution program body stores the specific function execution process and implementation process. Before each fusion migration is executed, the constant parameters in the code template are replaced with the parameters related to this fusion migration, and then the program file for this execution is generated; In step S3, the integration rules of the driver's license data in the core business data include: The principle of retaining driver's license data is to prioritize the migration of driver's license data that is in normal condition; Keep the driver's license with higher driving license types and migrate the data of lower driving license types to the backup table; If the permitted vehicle types are the same, data with the later date of initial license issuance and the first license issuance date will be retained first, and the remaining data will be migrated to the backup table; In step S3, the data migration procedure for the driver's license core data fusion specifically includes the following steps: a1: Traverse the driver's licenses in the intermediate database; extract the driver's licenses one by one and record them as pending confirmation licenses; Build a driver's license backup table in the target database; a2: According to the unique ID of the driver's license: the driver's license serial number, determine whether the driver's license to be confirmed exists in the target database; If it exists, it will be marked as pending confirmation that the driver's license has been migrated; Otherwise, go to step a3; a3: Search the target database for the driver's license to be confirmed according to the certificate number; If the driver's license retrieved from the target database is found, it is recorded as the driver's license to be compared, and step a5 is executed; Otherwise, execute step a4; a4: Search the driver's license to be confirmed according to the file number; If it can be retrieved, the driver's license to be confirmed is marked as data abnormality and sent to the abnormal data process; Otherwise, execute the data migration task for the pending driver's license. After the migration is complete, mark the pending driver's license as imported. a5: Confirm whether the driver's license to be confirmed has any of the following status information; The status information includes: transfer out, cancellation, revocation, cancellation or validity period expired for more than 1 year; If any of the above conditions exist, the data of the driver's license to be confirmed will be migrated to the driver's license backup table. After the migration is complete, the driver's license to be confirmed will be marked as imported. Otherwise, go to step a6; a6: Confirm whether the driver's license to be compared stored in the target database has any of the following status information; The status information includes: transfer out, cancellation, revocation, cancellation or validity period expired for more than 1 year; If any of the above conditions exist, the data of the driver's license to be confirmed is migrated to the target database backup table. After the migration is completed, the driver's license to be confirmed is marked as imported; the driver's license to be compared is migrated to the driver's license backup table; Otherwise, go to step a7; a7: Determine whether the vehicle type of the driver's license to be compared is consistent with that of the driver's license to be confirmed; If yes, proceed to step a8; If not, determine whether any of the following conditions are met: Condition 1: Whether the type of vehicle permitted by the driver's license to be confirmed is greater than that of the driver's license to be compared; Condition 2: Whether the driver's license to be confirmed allows driving more types of vehicles than the driver's license to be compared; If any one of the conditions is met, the data of the driver's license to be confirmed is migrated to the target database backup table. After the migration is completed, the driver's license to be confirmed is marked as imported; the driver's license to be compared is migrated to the driver's license backup table; Otherwise, mark the pending driver's license as not requiring migration, and migrate the pending driver's license to the driver's license backup table; a8: Determine whether the initial license issuance dates of the driver's license to be compared and the driver's license to be confirmed are consistent; If they are consistent, proceed to step a9; Otherwise, if the two are inconsistent, go to step a10; a9: Determine whether the issuance date of the driver's license to be compared is later than the issuance date of the driver's license to be confirmed; If yes, mark the pending driver's license as not requiring migration, and migrate the pending driver's license to the driver's license backup table; If not, the data of the driver's license to be confirmed is migrated to the target database backup table. After the migration is completed, the driver's license to be confirmed is marked as imported; the driver's license to be compared is migrated to the driver's license backup table; a10: Determine whether the initial issuance date of the driver's license to be compared is later than the initial issuance date of the driver's license to be confirmed; If yes, mark the pending driver's license as not requiring migration, and migrate the pending driver's license to the driver's license backup table; Otherwise, the data of the driver's license to be confirmed is migrated to the target database backup table. After the migration is completed, the driver's license to be confirmed is marked as imported; the driver's license to be compared is migrated to the driver's license backup table; In step S5, the execution steps from the intermediate library to the target library specifically include the following steps: b1: Preliminary inspection and processing; First, the target database and intermediate database are compared. This includes automatically checking whether the configured intermediate database users, proxy database users, and target database users exist. The system also checks whether the intermediate and target database tables with the same name have the same number of fields, field lengths, and constraints. The system then determines whether the table structure needs to be completed based on the configuration parameters. b2: Dynamically complete authorization work; Specifically, it includes completing the authorization of the intermediate library to the target library according to the configuration information; The authorization rights include: operation rights on tables, views, synonyms, and stored procedure packages, and query rights on tables of other intermediate libraries that the intermediate library depends on; b3: Dynamically create temporary tables and indexes; Based on the task configuration information, for tables with a number exceeding the threshold, a temporary migration table is created. Then, an index is created on the group number column in parallel. The temporary table includes: a primary key column, a group number, a tag column, and a remark column; b4: Dynamically publish data migration tasks; Based on the configured release data migration task, the migration program is dynamically compiled using database features, and based on the compilation results, the migration program is dynamically repaired until it succeeds. b5: Execute data migration program through tasks; Migration programs are executed through multi-threaded task execution. The number of control threads cannot exceed the configured maximum number of execution threads. Tasks that exceed the maximum number of execution threads are put into a waiting state. A new execution task can only be added after a task is popped out of the stack. The operation log of each migration program is recorded, and the group table mark column and note column are updated. b6: migration exception monitoring; Analyze the migration data for anomalies by analyzing the logs of each data record in the migration program. Stop the migration task promptly, update the migration configuration, and then resume the migration. Step b3 includes the following steps: c1: Confirm the basic information related to this data fusion migration and obtain the constant parameters during program execution; c2: Replaces the parameters in the program template with constant parameters to generate the fusion migration program for this execution; c3: dynamic deployment program; After the proxy library executes the replaced package header and package body respectively, it transfers the fusion migration program to the intermediate library according to the intermediate library read in the configuration table, completes the program compilation in the intermediate library, and executes the program; c4: dynamic repair; When an exception occurs, read the table referenced by the configuration table execution program name Pkname, complete the relevant fields of the intermediate library, and then continue to compile in a loop until the compilation is successful; The method for generating the code template comprises the following steps: d1: Create a business configuration table in advance. The business configuration table records whether each data table to be migrated needs to be grouped, and also records all fields that need to be converted, as well as the conversion functions corresponding to the field conversions. d2: For data tables to be migrated that do not require group migration and field conversion, generate a migration program code template according to the basic migration rules; The basic migration rules include: Rule 1: Migrate data from the intermediate database to the target database based on the primary key. Rule 2: Primary key constraint based on primary key structure: In the target database, each data table corresponds to only one primary key; Rule 3: If the data in the intermediate database has the same primary key as the target database, the duplicate data in the intermediate database will be backed up; All rules 1 to 3 must be met at the same time; d3: For the data tables to be migrated that need to be grouped, follow these steps to generate the migration program code template: When executing a migration task, a group table is created and a list of data to be migrated for each group is obtained according to the group number. For each data item in each data list that needs to be migrated, generate a migration program code template according to the basic migration rules; After the code corresponding to all group tables is generated, batch asynchronous call methods and implementation code are generated for all groups of the data table to be migrated. These methods and implementation code are saved in the code template to control the group migration function. d4: Data in the table or grouped table to be migrated where fields need to be replaced; For data tables that require field replacement, read the field names and corresponding conversion functions from the business configuration table. After merging all the content that requires conversion based on the primary key, generate the migration program code according to the basic migration rules. If grouping is not required, directly generate a code template. If grouping is required, generate a code template according to the grouping table method.

[0006] A traffic control data fusion migration system, characterized in that it includes: a data migration management module, a migration configuration management module and a task scheduling module; The data migration management module mainly includes configuring the migration tasks of multiple remote data source databases to the intermediate database, using a full-and-incremental mode, first fully initializing the intermediate database and then incrementally supplementing the data; using code templates to dynamically generate conversion programs based on the corresponding database table structure, primary key, and data volume of the target database and the intermediate database; each time the local source database is ready for migration, the data in the intermediate database and the source database are aligned first, and then the source database is deactivated before the data migration work; The migration configuration module is used to configure the migration project group and the relevant task information of the specific project; the configuration file corresponding to the task information includes: the relevant configuration items of the intermediate library, the target library, and the proxy library; The scheduling execution module is used to read the project group to be migrated, execute the migration task in multiple threads, execute the execution program of each specific project group, complete pre-check processing, authorization, create grouping tables and indexes, publish specific execution programs and execute them in multiple threads, and record execution results and logs.

[0007] It is further characterized by: It also includes: migration monitoring module and migration result verification module The migration monitoring module is used to monitor execution anomalies, disable related scheduling tasks, and reschedule execution after repairing the program and updating the migration configuration; The data verification module is used to generate a migration information report based on the execution log; repair data according to migration anomalies, iteratively optimize the migration program, and update the migration configuration.

[0008] The present application provides a traffic management data fusion migration method, which first synchronizes the data of the source database to the intermediate database, and then fuses and migrates the data of the intermediate database to the target database; in the present application, by constructing a fusion migration program based on business logic for different types of data, it is ensured that when merging data from multiple source databases into the target database, data fusion can be achieved quickly and accurately, thereby ensuring the data validity of the target database; the present application extracts the common parts of the data migration process in the form of a program template, provides parameter constants for each data fusion migration according to the configuration table, and generates an executable program after replacing the parameter constants in the program template, thereby effectively improving the data fusion migration program. The versatility of this method improves the migration efficiency while reducing R&D costs. This application sets up a proxy library, generates an executable program body in the proxy library, and then sends it to multiple intermediate libraries that use the program in each data migration. There is no need to generate an executable program for each intermediate library, which greatly improves the program generation efficiency and reduces the probability of errors in the program generation process. This method synchronizes the remote database to the intermediate library through data synchronization, reduces the consumption of network bandwidth during migration, and improves the migration efficiency. This method dynamically authorizes, creates tables, publishes packages, compiles and executes, reduces manual operations, reduces the probability of manual errors, and ensures the stability of the migration process and data quality. BRIEF DESCRIPTION OF THE DRAWINGS

[0009] Figure 1 This is a flow chart of the traffic management data fusion migration method; Figure 2 This is a structural diagram of data fusion migration; Figure 3 Schedule execution flow chart for data fusion migration tasks; Figure 4 Generate schematic diagrams for data fusion migration procedures; Figure 5 Flowchart of the integration and migration process for the core data of the driver's license; Figure 6 This is a schematic diagram of the traffic management data fusion and migration system structure. DETAILED DESCRIPTION

[0010] like Figure 1 As shown, the present invention includes a traffic control data fusion migration method, which includes the following steps.

[0011] S1: Create intermediate library, proxy library and target library, as follows Figure 2 shown.

[0012] The target database is a remote source database that is used for data migration and fusion.

[0013] The proxy library is used for data migration; the proxy library stores the rules used in the data fusion migration of the intermediate library and the target library, as well as the data migration fusion program files for various types of data. All fusion migration programs in this application are pre-made into program files and saved in the proxy library in the form of code templates. Each time the intermediate library, data type, table name and other variables are changed, an executable program is generated, and then compiled and executed. The program file includes: a program name, an execution program header and an execution program package body, wherein the program name is used to uniquely mark each fusion migration program, the execution package header stores the definition files used when the program is running, such as variables, constants, functions, etc., and the execution program package body stores the specific function execution process and implementation process.

[0014] The intermediate database temporarily stores the data of the source database. In this method, the intermediate database and the source database are built one-to-one. Before the data migration is completed, the intermediate database and the source database are kept in real-time synchronization.

[0015] Create multiple intermediate database users in the target database to receive source data from the source database; the target database and the source database maintain the same system upgrade strategy. Maintain consistency between data objects and primary keys. The primary keys of data objects in this method are consistent in both the intermediate database and the target database. For example, due to network delays, transfers in and out of driver's licenses, etc., driver A's driver's licenses may be stored in source databases in different cities in his early years. Each source database has a different file number for driver A. After all the data from the source databases is synchronized to the intermediate database, and after the data is merged from the intermediate database to the target database, driver A's driver's license will only correspond to one driving serial number, one file number, and one certificate number. At the same time, driver A also has valid driver's license data in the target database.

[0016] When creating a specific target database and users in the target database, for the same type of source database, select the remote source database with the highest maintenance level as the basis, configure the synchronization task, and complete the synchronization of data objects such as table structure, indexes, synonyms, views, and stored procedures until the migration begins.

[0017] S2: Data synchronization between the source database and the intermediate database; Use the source database as the data source and configure data synchronization tasks from multiple remote data sources to the intermediate database. Use the full + incremental mode, first fully initialize the intermediate database and then incrementally supplement data. Complete data synchronization before the official migration between the intermediate database and the target database. The source data and the intermediate database are synchronized through data synchronization software. The source data in the source database and the intermediate database data must be consistent and synchronized in real time. When the source database table structure, data, sequence and other database objects change, the target intermediate database will also change at the same time.

[0018] During the specific implementation, synchronization software, such as Oracle GoldenGate, is deployed on multiple remote databases and intermediate databases. On the target instance, a database user with the same structure as the source is created, and the target table structure is created. On the source, the database's archiving mode is enabled, a user synchronization user is created and authorized, an extraction process is created to capture the DML and DDL operations of the source database, and a data file is created to store the captured operation logs. On the target server, commands are used to create a replication process to read the data file and apply the operations to the target database.

[0019] If the network connection between the two database servers is unstable and unreliable, and the remote database is located close to the target location, consider exporting the database data, transporting it to the target location, and manually importing it into the intermediate database. Each time the source database is ready for migration, align the data in the intermediate database with the source database, deactivate the source database, and then perform the data migration.

[0020] S3: Generate a data migration program between the intermediate database and the target database.

[0021] Based on the corresponding data features of the target database and the intermediate database, combined with business strategies and grouping strategies, the migration program code template is dynamically generated using the code template; the data features include: database table structure, primary key and data volume. Figure 3 As shown in the figure, based on real-world scenarios, manually customized conversion rules are required for core traffic management data, such as driver's license data, traffic violation data, and vehicle data. These data types are large in volume, so actual data migration involves grouping and migrating data using temporary tables. Customized conversion rules are not required for other data.

[0022] The fusion rule for data tables that do not involve business rules and have a small amount of data is to migrate according to the primary key of the data. In this method, the primary key constraint condition of the data is constructed based on the primary key, that is, in the target database, the same data table only corresponds to one unique primary key.

[0023] During the migration process, data tables with duplicate primary keys in the primary table are backed up in the intermediate repository. In this case, during code template generation, a table is dynamically created to back up data with duplicate primary keys in the intermediate and target databases (in the format of DK_table name), ensuring that primary keys in the target database tables are unique. In this embodiment, the program implementing this functionality is SQL-based. Table conversion and error logs are recorded, and the generated code is ultimately saved in the code template file.

[0024] Tables 1 and 2 together constitute the business configuration table. Business configuration tables are manually created and stored in the proxy database based on business logic and data availability. Table 1 records basic information for each data table, such as the table type and data size. For tables with excessively large data volumes, it indicates whether they require grouping. This annotation process can be automated through a batch file to determine the data size of the table and indicate whether grouping is necessary. In this application, a grouping threshold (e.g., 2 million records) is set based on hardware performance to determine whether a table requires grouping for migration. Table 2 records fields that require regeneration. These fields, Zd, are non-business logic-related fields, such as the serial number of a business processing document or the number of an electronically imaged document. These fields must be regenerated according to unified rules when integrated into the target database. These regeneration rules are implemented using the conversion function Zhfs. In implementation, the business configuration table is traversed to determine whether the migrated data table requires grouping and whether fields need to be replaced.

[0025] For tables with large data volumes or where some fields require conversion, migration code is generated by reading Table 2. For tables with data volumes exceeding the threshold in Table 1, the system creates a group table (format A00_table_name_fz) when executing the migration task. The system retrieves the list of data to be migrated by group number, capturing each data item. If a database field requires conversion, the system reads the configured conversion function and merges all fields to be converted based on the primary key to generate the migration SQL code. If no conversion is required, the migration SQL code is generated directly based on the primary key. Each data item is annotated with success / failure and the reason in the group table, and a transaction is committed after every 1,000 records. The generated code is finally saved to a code template. For group migrations, a batch asynchronous call method and implementation are generated and saved to the code template to implement the group migration functionality. For tables with smaller data volumes, the batch asynchronous call method is not generated; the system directly retrieves the data list and each data item, following the same procedures as above. The code template is stored in the proxy library. Before execution, constant parameters are replaced and the code template is sent to the intermediate library to generate the executable file.

[0026] like Figure 4As shown, according to the corresponding data characteristics of the target library and the intermediate library, combined with the business code configuration table, the code template is used to dynamically generate the conversion program and data grouping strategy; the data characteristics include: library table structure, primary key and data volume.

[0027] Table 1: Code generation configuration table

[0028] Table 2: Code generation field configuration table

[0029] On this basis, business developers manually customize the integration and transformation business logic for specific core data tables to ensure that the data in the final target database meets business requirements. This business logic includes: serial number processing rules for motor vehicle business, serial number processing rules for driver's license business, and retention rules for core business data.

[0030] During data migration between the intermediate and target databases in this application, if data with unique constraints (such as primary keys) exists in multiple source databases, the migration must be preceded by business data cleansing and fusion. For example, if driver's license data for the same driver exists in multiple locations, when these data are merged into the same target database, it must be ensured that only one valid driver's license exists for the same driver. This data fusion and migration process ensures that the data stored in the database meets business requirements and reduces social conflicts.

[0031] Among them, the core business data retention rules related to driver's licenses are as follows: The principle of retaining driver's license data is to prioritize the migration of driver's license data that is in normal condition; Keep the driver's license with higher driving license types and migrate the data of lower driving license types to the backup table; In the case where the types of vehicles allowed to be driven are the same, priority will be given to retaining the data of the initial license issuance date and the later issuance date, and the remaining data will be migrated to the backup table.

[0032] like Figure 5 As shown, the steps of the fusion migration program for the core data of the driver's license are as follows.

[0033] a1: Traverse the driver's licenses in the intermediate database; extract the driver's licenses one by one and record them as pending confirmation licenses; A backup table for core business data is built in the target database. Data with the same primary key, such as multiple driver's licenses for the same driver, can only be stored in one copy in the target database table. Any remaining data that cannot be stored in the target database is stored in the backup table. This backup table ensures that core business data, such as all driver's license information for the same driver, will not be lost during the data fusion process. Driver's license information mainly includes fields such as serial number, file number, ID number, ID name, name, gender, date of birth, permitted vehicle type, original permitted vehicle type, clearance date, inspection date, initial license date, issuance date, and status. Related tables are constructed based on these fields. The driver's license backup table is named: DK_Driver's License Table Name, and the fields are the same as the driver's license information table. a2: According to the unique ID of the driver's license: the driver's license serial number, determine whether the driver's license to be confirmed exists in the target database; If it exists, it will be marked as pending confirmation that the driver's license has been migrated; Otherwise, go to step a3; a3: Search the target database for the driver's license to be confirmed according to the document number; If the driver's license is found, it means that the driver has multiple licenses. The driver's license found in the target database is recorded as the driver's license to be compared, and step a5 is executed. Subsequently, the driver's license to be confirmed and the driver's license to be compared are compared from various angles. Based on the comparison results, the data of multiple driver's licenses owned by the same driver are merged and migrated to the target database. Otherwise, execute step a4; a4: Search for the driver's license to be confirmed according to the file number; If it can be retrieved, the driver's license to be confirmed will be marked as a data anomaly and sent to the abnormal data process. The abnormal data processing process is not expanded in this application; Otherwise, execute the data migration task for the pending driver's license. After the migration is complete, mark the pending driver's license as imported. a5: Confirm whether the driver's license to be confirmed has any of the following status information; Status information includes: transfer out, cancellation, revocation, cancellation, or validity period expired for more than 1 year; If any of these conditions exist, the data of the driver's license to be confirmed will be migrated to the driver's license backup table. After the migration is completed, the driver's license to be confirmed will be marked as imported. When merging data, if the driver holds multiple driver's licenses, the driver's license data in normal state will be retained in the target database based on the principle of "benefiting the driver"; Otherwise, go to step a6; a6: Confirm whether the driver's license to be compared stored in the target database has any of the following status information; Status information includes: transfer out, cancellation, revocation, cancellation, or validity period expired for more than 1 year; If any of the above conditions exist, the data of the driver's license to be confirmed will be migrated to the backup table of the target database. After the migration is complete, the driver's license to be confirmed will be marked as imported. The driver's license to be compared will be migrated to the driver's license backup table. Otherwise, go to step a7; a7: Determine whether the vehicle type of the driver's license to be compared is the same as that of the driver's license to be confirmed; If yes, proceed to step a8; If not, determine whether any of the following conditions are met: Condition 1: Whether the type of vehicle permitted by the driver's license to be confirmed is greater than that of the driver's license to be compared; Condition 2: Whether the driver's license to be confirmed allows driving more types of vehicles than the driver's license to be compared; If any of the conditions are met, the data of the driver's license to be confirmed will be migrated to the target database backup table. After the migration is complete, the driver's license to be confirmed will be marked as imported. The driver's license to be compared will also be migrated to the driver's license backup table. During data fusion, if the driver holds multiple driver's licenses, based on the principle of "benefiting the driver", the driver's license data with the largest number of driving models and types will be retained in the target database. Otherwise, mark the pending driver's license as not requiring migration, and migrate the pending driver's license to the driver's license backup table; a8: Determine whether the initial license issuance dates of the driver's license to be compared and the driver's license to be confirmed are consistent; If they are consistent, proceed to step a9; Otherwise, if the two are inconsistent, go to step a10; a9: Determine whether the issuance date of the driver's license to be compared is later than the issuance date of the driver's license to be confirmed; If so, the pending driver's license is marked as not requiring migration and is moved to the driver's license backup table. During data fusion, if the driver holds multiple driver's licenses, the driver's license with the latest issuance date is retained in the target database based on the principle of "benefiting the driver." If not, migrate the data of the driver's license to be confirmed to the backup table of the target database. After the migration is completed, mark the driver's license to be confirmed as imported; and migrate the driver's license to be compared to the driver's license backup table; a10: Determine whether the initial issuance date of the driver's license to be compared is later than the initial issuance date of the driver's license to be confirmed; If yes, mark the pending driver's license as not requiring migration, and migrate the pending driver's license to the driver's license backup table; Otherwise, the data of the driver's license to be confirmed will be migrated to the backup table of the target library. After the migration is completed, the driver's license to be confirmed will be marked as imported; the driver's license to be compared will be migrated to the driver's license backup table.

[0034] Based on the above process, multiple driver's license data for the same driver is merged and migrated to the target database. A backup table is used to ensure that all data is retained. By comparing various conditions, the target database ensures that the most favorable driver's license data is retained.

[0035] When operating on database data, the conversion rule for motor vehicle business records is to convert serial numbers starting with 1 in the intermediate database into the second digit of the issuing authority + the serial number minus the leading 1; and to convert serial numbers not starting with 1 into the first digit of the original serial number + the second digit of the issuing authority + the original serial number minus the leading 1. The conversion rule for driver's license business records is to convert serial numbers starting with 1 into the second digit of the issuing authority + 1 + the original serial number minus the leading 2 digits; and to convert serial numbers not starting with 1 into the second digit of the issuing authority + the original serial number minus the leading 1.

[0036] S4: Configure task information for the data migration task. For each data migration task, the configuration items in the task information configuration table mainly include: information about the intermediate library Yk, information about the target library Mbk, information about the proxy library Dlk, maximum number of execution threads Zdxc, group table number threshold Fzfz, execution program Pkname code template, data size per group, number of running threads, execution program entry, and checker program entry. The specific structure is shown in Table 3.

[0037] The proxy library can operate all objects in the intermediate library and the target library, and can grant the intermediate library the right to operate the objects in the target library; Table 3: Configuration file example

[0038] S5: Execute the migration task; Deploy the migration initialization program in the proxy database and execute it in batches or by city. Read task information and execute configured data migration tasks in batches. Each data migration task group includes the data migration configuration information from multiple intermediate databases to the target database in the same city. Configure data migration tasks and corresponding execution programs for multiple intermediate database users to multiple target database users in the proxy database execution database.

[0039] In specific implementation, the execution steps from the intermediate library to the target library are as follows: Figure 3 As shown, the specific steps include the following steps.

[0040] b1: Preliminary inspection and processing; The target database and intermediate database are first compared. This includes automatically checking the existence of configured intermediate database users, proxy database users, and target database users. Tables with the same name in the intermediate and target databases are checked for consistency in the number of fields, length, and constraints. Configuration parameters (Sfzdbq) are used to determine whether table structure completion is necessary. This pre-check ensures that data in the target and intermediate databases can be synchronized.

[0041] b2: Dynamically complete authorization work; Specifically, it includes completing the operation authorization of the intermediate library to the target library according to the configuration information; Authorization permissions include: operation permissions on tables, views, synonyms, and stored procedure packages, and query permissions on tables of other intermediate libraries that the intermediate library depends on (such as queries on basic information).

[0042] In this application, the authorization of the target library and the intermediate library is limited to the execution of the migration program. Once the migration work is completed, the granted operation permissions will be revoked.

[0043] b3: Dynamically create temporary tables and indexes; To increase parallelism and improve migration efficiency, this method creates a grouping table in the intermediate database before data migration. The grouping table ensures that the program can resume execution from the original, unmigrated location after an interruption. It is used for conversion checks after the migration is completed to troubleshoot exceptions that occurred during the migration process.

[0044] Based on the configuration information of the business code configuration table, create a migration group table for tables with a large number of entries. Then, create an index on the group number column in parallel. The group table includes: primary key column, group number, tag column, and comment column. The tag column is used to record the migration status, such as 0 - not migrated, 1 - successful, 2 - migration failed. The primary key column is obtained by reading the database dictionary.

[0045] b4: Dynamically publish data migration tasks; Release data migration tasks based on configuration, dynamically compile migration programs using database features, and dynamically repair migration programs until they succeed based on compilation results. b5: Execute data migration program through tasks; The migration program is executed through multi-linear task execution. The number of control threads cannot exceed the configured maximum number of execution threads. The execution tasks exceeding the maximum number of threads will wait. Only when a task is popped out of the stack can a new execution task be added. The operation log of each migration program is recorded, and the group table mark column and note column are updated.

[0046] In this application, the migration program is published according to the configuration in the proxy library, the database characteristics are used to dynamically compile the migration program, and according to the compilation situation, the migration program is dynamically repaired until it succeeds. Specifically, the following steps are included: c1: Obtain basic information to obtain constant parameters during program execution. This function reads basic information from the configuration table source system library (FrmK), such as management department and issuing authority information, and uses the department name as a constant parameter when generating a document serial number for a specific department.

[0047] c2: Replaces the parameters in the configured program template.

[0048] Read the configuration table (Table 1), execute the program name (Pkname), execute the program package header (Exechead), and execute the program package body (Execbody) to obtain the converged migration program that needs to be executed for this migration.

[0049] Read the intermediate library user (yk) and the target library user (Mbk), replace the contents of the executable program header and package body with the intermediate library user + "." + the executable program name, and replace the parameter constants read in the first step with the corresponding package header and package body. After replacing the constant parameters in the program template, generate an executable fusion migration program.

[0050] The intermediate database user and the target database user ensure that the fusion migration program can simultaneously perform fusion migration operations on the data in the intermediate database and the target database.

[0051] c3: Dynamic deployment program.

[0052] After the proxy library executes the replaced package header and package body respectively, it passes the fusion migration program to the intermediate library according to the intermediate library read in the configuration table, completes the program compilation in the intermediate library, and executes the program.

[0053] c4: Dynamic repair.

[0054] In most cases, dynamically deployed packages succeed. If an exception occurs, the code configuration table references the table referenced by the execution program name (Pkname), completes the relevant fields in the intermediate library, and then continues the compilation loop until the compilation succeeds.

[0055] For example, when migrating data from multiple detachments in City B to the target database, we first migrate the source databases of all detachments in City B to the corresponding intermediate databases in a one-to-one ratio. Then, we construct the configuration file shown in Table 1 for this data fusion migration task. Each data migration simultaneously operates on multiple intermediate databases and multiple target databases, migrating multiple data types.

[0056] The fusion migration program for each data type is pre-prepared and stored in the proxy library. For example, for the fusion migration program for driver's licenses, refer to steps a1 through a10. Each migration program is labeled based on its program name, Pkname. The corresponding Exechead and Execbody can be found based on the Pkname.

[0057] In the proxy library, the parameter constants are replaced with the corresponding packet headers. According to the Pkname, Exechead and Execbody in the configuration table, as well as the corresponding intermediate library and target library information, the program is read into the corresponding proxy library for compilation and then executed.

[0058] For example, if there are 10 intermediate repositories requiring data migration, this method can generate an executable program once in the proxy library and simultaneously transfer it to all 10 intermediate repositories, where the program is compiled and executed separately. This greatly improves data migration efficiency and reduces the probability of errors when generating executable programs.

[0059] a6: Migration exception monitoring; Analyze the logs of each data record in the migration program to see if there are any anomalies in the current migration data. Stop the migration task promptly, update the migration configuration, and then resume the migration.

[0060] S6: Data migration result verification and data correction; Specifically, it includes: verifying migration results based on migration logs, generating migration reports, repairing migration data, and monitoring abnormalities during the migration process.

[0061] like Figure 6 As shown, a traffic control data fusion migration system is characterized in that it includes: a data migration management module, a migration configuration management module, a task scheduling module, a migration monitoring module and a migration result verification module.

[0062] The data migration management module primarily involves configuring migration tasks for multiple remote data source databases to the intermediate database. This module uses a full-plus-incremental model, first fully initializing the intermediate database and then incrementally supplementing it with data. It uses code templates to dynamically generate conversion programs based on the corresponding database table structures, primary keys, and data volumes between the target and intermediate databases. Whenever a local source database is ready for migration, the data in the intermediate database and source database are aligned, and then the source database is deactivated before data migration. The migration configuration module is used to configure the migration project group and related task information of specific projects. The configuration files corresponding to the task information include: related configuration items of the intermediate library, target library, and proxy library. The scheduling and execution module is used to read the project group to be migrated, execute the migration task in multiple threads, execute the execution program for each specific project group, complete pre-check processing, authorization, create group tables and indexes, publish specific execution programs and execute them in multiple threads, and record the execution results and logs. The migration monitoring module is used to monitor execution anomalies, disable related scheduling tasks, and reschedule execution after repairing the program and updating the migration configuration; The data verification module is used to generate migration information reports based on execution logs; repair data based on migration anomalies, iteratively optimize the migration program, and update the migration configuration.

[0063] After using the technical solution of the present invention, it can be applied to multiple distributed databases to perform structured data fusion migration in batches. This method includes configuring data synchronization tasks from multiple remote data sources to the target intermediate database and completing data synchronization before the formal migration; generating a data migration program based on the target database data table object constraints and traffic management business rules; configuring data migration tasks and corresponding execution programs from multiple intermediate database users to multiple target database users in the target proxy database; scheduling migration tasks, dynamically completing user authorization, creating temporary tables, indexes, synonyms and other objects, and dynamically compiling the execution program to execute table-level migration tasks; data migration result verification and data correction, including migration result verification based on the migration log, generating a migration report, repairing the migrated data, and monitoring the migration process for exceptions. The method requires configuring users and execution programs for the intermediate database (the synchronization database of the source database) and the target database according to the database, and executing the migration program through task parallel scheduling, thereby improving the efficiency, stability, continuity, and fault tolerance of data fusion migration and ensuring the quality of the fusion migration data.

[0064] Compared with the existing technology, this method synchronizes the remote database to the intermediate database through data synchronization, reduces the consumption of network bandwidth during migration, improves the migration efficiency, facilitates the comparison of migration results, and at the same time reduces the complexity of migration, which is conducive to the improvement of data quality; through effective configuration, the establishment of migration groups is conducive to the scheduling and execution of migration tasks; multiple tasks are scheduled and executed without affecting each other, which improves the efficiency of migration; dynamic authorization, table creation, package release, compilation and execution, etc., reduce manual operations, reduce the probability of manual errors, and ensure the stability of the migration process and data quality.

Claims

1. A traffic control data fusion migration method, characterized in that: It includes the following steps: S1: Create intermediate library, proxy library and target library; The target database is a data migration and fusion target database of a remote source database; The proxy library is used for data migration; The intermediate database temporarily stores the data of the source database; Create multiple intermediate database users in the target database to receive source data from the source database; the target database and the source database maintain the same system upgrade strategy to maintain consistency of data objects and primary keys; S2: Data synchronization between the source database and the intermediate database; Use the source database as a data source, configure data synchronization tasks from multiple remote data sources to the intermediate database, and complete data synchronization before formal migration; The source data and the intermediate database are synchronized through data synchronization software. The source data in the source database and the intermediate database data must be consistent and synchronized in real time. When the source database table structure, data, sequence and other database objects change, the target intermediate database changes at the same time. S3: Generate data migration program between the intermediate database and the target database; Dynamically generate migration program code templates based on the corresponding data features of the target database and the intermediate database, combined with business strategies and grouping strategies; The data features include: database table structure, primary key and data volume; on this basis, the business developer customizes the fusion conversion business logic for the specific table; S4: Configure the task information of the proxy database data migration task; Corresponding to each of the data migration tasks, the configuration items in the configuration table corresponding to the task information mainly include: intermediate library related information, target library related information, proxy library related information, maximum number of execution threads, execution program code template, execution program entry and check program entry; The proxy library can operate all objects in the intermediate library and the target library, and can grant the intermediate library the right to operate the objects in the target library; S5: execute the migration task; Read the task information and execute the configured data migration tasks in batches; each group of data migration tasks includes data migration configuration information from multiple intermediate libraries to target libraries in the same city; configure data migration tasks and corresponding execution programs from multiple intermediate library users to multiple target library users in the proxy library execution library.

2. The method for fusion and migration of traffic control data according to claim 1, characterized in that: It also includes the following steps: S6: Data migration result verification and data correction; Specifically include: verifying migration results based on migration logs, generating migration reports, repairing migration data and monitoring abnormalities during the migration process.

3. The method for fusion and migration of traffic control data according to claim 1, characterized in that: The proxy library stores code templates; The code template is a general program file, specifically including: a program name, an execution program package header, and an execution program package body, wherein the program name is used to uniquely mark each fusion migration program; The execution package header stores the definition files used when the program is running; the execution program package body stores the specific function execution process and implementation process; Before each execution of fusion migration, the constant parameters in the code template are replaced with parameters related to this fusion migration, and then the program file for this execution is generated.

4. The method for fusion and migration of traffic control data according to claim 1, characterized in that: In step S3, the fusion rules of the driver's license data in the core business data include: The principle of retaining driver's license data is to prioritize the migration of driver's license data that is in normal condition; Keep the driver's license with higher driving license types and migrate the data of lower driving license types to the backup table; In the case where the types of vehicles allowed to be driven are the same, priority will be given to retaining the data of the initial driver's license issuance date and the later issuance date, and the remaining data will be migrated to the backup table.

5. The method for fusion and migration of traffic control data according to claim 1, characterized in that: In step S3, the data migration procedure for the driver's license core data fusion specifically includes the following steps: a1: Traverse the driver's licenses in the intermediate database; take out the driver's licenses one by one and record them as driver's licenses to be confirmed; Build a driver's license backup table in the target database; a2: According to the unique ID of the driver's license: the driver's license serial number, determine whether the driver's license to be confirmed exists in the target database; If it exists, it is marked as pending confirmation that the driver's license has been migrated; Otherwise, execute step a3; a3: Retrieve the driver's license to be confirmed in the target database according to the certificate number; If the driver's license retrieved from the target database can be retrieved, it is recorded as the driver's license to be compared, and step a5 is executed; Otherwise, execute step a4; a4: Search the driver's license to be confirmed according to the file number; If it can be retrieved, the driver's license to be confirmed is marked as data abnormality and sent to the abnormal data process; Otherwise, execute the data migration task of the driver's license to be confirmed. After the migration is completed, mark the driver's license to be confirmed as imported; a5: Confirm whether the driver's license to be confirmed has any of the following status information; The status information includes: transfer out, cancellation, revocation, withdrawal or validity period expired for more than 1 year; If any of the above conditions exist, the data of the driver's license to be confirmed will be migrated to the driver's license backup table. After the migration is completed, the driver's license to be confirmed will be marked as imported. Otherwise, execute step a6; a6: confirm whether the driver's license to be compared stored in the target database has any of the following status information; The status information includes: transfer out, cancellation, revocation, withdrawal or validity period expired for more than 1 year; If any of the above conditions exist, the data of the driver's license to be confirmed is migrated to the target database backup table. After the migration is completed, the driver's license to be confirmed is marked as imported; the driver's license to be compared is migrated to the driver's license backup table; Otherwise, execute step a7; a7: Determine whether the vehicle type of the driver's license to be compared is consistent with that of the driver's license to be confirmed; If yes, execute step a8; If not, determine whether any of the following conditions are met: Condition 1: Whether the type of vehicle permitted by the driver's license to be confirmed is greater than that of the driver's license to be compared; Condition 2: Whether the type of vehicle allowed by the driver's license to be confirmed is more than that of the driver's license to be compared; If any one of the conditions is met, the data of the driver's license to be confirmed is migrated to the target database backup table. After the migration is completed, the driver's license to be confirmed is marked as imported; the driver's license to be compared is migrated to the driver's license backup table; Otherwise, mark the driver's license to be confirmed as not requiring migration, and migrate the driver's license to be confirmed to the driver's license backup table; a8: Determine whether the first license issuance date of the driver's license to be compared and the driver's license to be confirmed is consistent; If they are consistent, go to step a9; Otherwise, if the two are inconsistent, execute step a10; a9: Determine whether the issuance date of the driver's license to be compared is later than the issuance date of the driver's license to be confirmed; If yes, mark the pending driver's license as not requiring migration, and migrate the pending driver's license to the driver's license backup table; If not, the data of the driver's license to be confirmed is migrated to the target database backup table. After the migration is completed, the driver's license to be confirmed is marked as imported; the driver's license to be compared is migrated to the driver's license backup table; a10: determining whether the initial issuance date of the driver's license to be compared is later than the initial issuance date of the driver's license to be confirmed; If yes, mark the pending driver's license as not requiring migration, and migrate the pending driver's license to the driver's license backup table; Otherwise, the data of the driver's license to be confirmed is migrated to the target library backup table. After the migration is completed, the driver's license to be confirmed is marked as imported; and the driver's license to be compared is migrated to the driver's license backup table.

6. The method for fusion migration of traffic control data according to claim 1, characterized in that: In step S5, the execution steps from the intermediate library to the target library specifically include the following steps: b1: Preliminary inspection and processing; First, compare the relevant information of the target database and the intermediate database. The comparison content includes: automatically checking whether the configured intermediate database users, proxy database users, and target database users exist; checking whether the number of fields in the intermediate database and the target database with the same name are consistent, whether the field length and constraints are consistent, and determining whether the table structure needs to be completed based on the configuration parameters; b2: Dynamically complete the authorization work; Specifically, it includes completing the authorization of the intermediate library to the target library according to the configuration information; The authorization rights include: operation rights of tables, views, synonyms, and stored procedure packages, and query rights of tables of other intermediate libraries that the intermediate library depends on; b3: Dynamically create temporary tables and indexes; According to the task configuration information, for tables whose number exceeds the threshold, create a temporary migration table; then create an index on the group number column in parallel; The temporary table includes: a primary key column, a group number, a tag column, and a remark column; b4: Dynamically publish data migration tasks; According to the configured release data migration task, the migration program is dynamically compiled using database characteristics, and according to the compilation situation, the migration program is dynamically repaired until it succeeds; b5: Execute data migration program through tasks; The migration program is executed through multi-linear tasks. The number of control threads cannot exceed the configured maximum number of execution threads. The execution tasks exceeding the maximum number of threads wait. Only when a task is popped out of the stack can a new execution task be added. The operation log of each migration program is recorded, and the grouping table mark column and note column are updated. b6: Monitoring of migration anomalies; Analyze whether there are any abnormalities in the current migration data through the log of each data record of the migration program, and stop the migration task in time, update the migration configuration, and then continue the migration.

7. A traffic control data fusion migration method according to claim 6, characterized in that: Step b3 includes the following steps: c1: Confirm the basic information related to this data fusion migration and obtain the constant parameters in program execution; c2: replace the parameters in the program template with constant parameters to generate the fusion migration program for this execution; c3: dynamic deployment program; After the proxy library executes the replaced package header and package body respectively, it transfers the fusion migration program to the intermediate library according to the intermediate library read in the configuration table, completes the program compilation in the intermediate library, and executes the program; c4: dynamic repair; When an exception occurs, read the table referenced by the configuration table execution program name Pkname, complete the relevant fields of the intermediate library, and continue compiling in a loop until the compilation is successful.

8. The method for fusion migration of traffic control data according to claim 1, characterized in that: The method for generating the code template comprises the following steps: d1: Prepare a business configuration table in advance; the business configuration table records whether each data table to be migrated needs to be grouped, and records all fields that need to be converted, as well as the conversion functions corresponding to the field conversion; d2: For the data tables to be migrated that do not require group migration and field conversion, generate the code template of the migration program according to the basic migration rules; The basic migration rules include: Rule 1: Migrate data from the intermediate database to the target database based on the primary key of the data; Rule 2: Primary key constraint based on primary key structure: In the target database, the same data table corresponds to only one primary key; Rule 3: If the data in the intermediate database has the same primary key as the target database, the duplicate data in the intermediate database will be backed up; All rules 1 to 3 must be met at the same time; d3: For the data tables to be migrated that need to be grouped, follow the steps below to generate the code template for the migration program: When executing the migration task, create a group table and obtain the data list of each group to be migrated according to the group number; For each piece of data in each group of data lists that need to be migrated, generate a code template for the migration program according to the basic migration rules; After the codes corresponding to all group tables are generated, batch asynchronous call methods and implementation codes are generated for all groups of the data table to be migrated, and saved to the code template to control the group migration function. d4: Data in the data table or grouped table to be migrated that needs to replace fields; For data tables that need to replace fields, read the field names and their corresponding conversion functions before replacement from the business configuration table, merge all the contents that need to be converted based on the primary key, and generate the migration program code according to the basic migration rules; if grouping is not required, directly generate the code template; if grouping is required, generate the code template according to the grouping table method.

9. A traffic control data fusion migration system, characterized in that: It includes: Data migration management module, migration configuration management module and task scheduling module; The data migration management module mainly includes configuring the migration tasks of multiple remote data source databases to the intermediate database, adopting the full plus incremental mode, first fully initializing the intermediate database and then incrementally supplementing the data; dynamically generating the conversion program using the code template according to the corresponding database table structure, primary key and data volume of the target database and the intermediate database; each time the local source database is ready to be migrated, aligning the data of the intermediate database and the source database first, and then deactivating the source database to perform data migration; The migration configuration module is used to configure the migration project group and relevant task information of specific projects; The configuration files corresponding to the task information include: configuration items related to the intermediate library, target library, and proxy library; The scheduling execution module is used to read the project group to be migrated, execute the migration task in multiple threads, execute the execution program of each specific project group, complete the pre-check processing, authorization, create the grouping table and index, publish the specific execution program and execute it in multiple threads, and record the execution results and logs.

10. The traffic control data fusion migration system according to claim 9, characterized in that: It also includes: migration monitoring module and migration result verification module The migration monitoring module is used to monitor execution anomalies, deactivate related scheduling tasks, and reschedule execution after repairing the program and updating the migration configuration; The data verification module is used to generate a migration information report based on the execution log; repair data based on migration anomalies, and update the migration configuration after iteratively optimizing the migration program.

Images