Data synchronization method and apparatus, computer device, and readable medium

Abstract

The application provides a data synchronization method and device, computer equipment and a readable medium, and relates to the field of big data. The method comprises the following steps: migrating a structure object of a source database of a first system to a target database of a second system; migrating full-amount data corresponding to a preset historical time point in the source database to the target database; and batch synchronizing real-time data corresponding to a time point after the preset historical time point in the source database to the target database. Through the above scheme, the application can realize data migration and data quasi-real-time synchronization from the first system, such as an OLTP system, to the second system, such as an OLAP system. The application can not only support structure synchronization, but also support full-amount synchronization and incremental synchronization, thereby facilitating users to quickly realize the synchronization function.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of computer application, and in particular to a data synchronization method and device, computer equipment and readable medium.

BACKGROUND

[0002] Data processing based on database can be roughly divided into two categories: on-line transaction processing (OLTP) and on-line analytical processing (OLAP). Among them, OLTP is the main application of traditional relational database, which is mainly basic and daily transaction processing, such as bank transaction. OLAP is the main application of data warehouse system, which supports complex analysis operation, focuses on decision support, and provides intuitive and easy-to-understand query results.

[0003] At present, more and more OLTP data analysis requirements of minute or hour level appear in various application scenarios. For example, the customer relationship management (CRM) related business in the financial field needs to use online real-time data to tag users and analyze and evaluate customer revisit effect; and the financial business intelligence (BI) department needs to realize real-time statistics of cash loan number and amount, and quickly find possible problems in business. Most OLAP systems support data synchronization between different heterogeneous databases in the form of files, which can be synchronized through full data export. However, if the data file is large, the data export and import of this method not only consumes disk IO and network resources of the servers where the source system and the target system are located, but also is difficult to meet the business OLTP data analysis requirements of minute or hour level. Therefore, it is urgent to provide a method for synchronizing data in a first system such as an OLTP system to a second system such as an OLAP system in quasi-real time, to meet the increasing data analysis requirements of OLTP system of minute or hour level.

SUMMARY

[0004] The present application provides a data synchronization method and device, computer equipment and readable medium, which are used to provide a data synchronization scheme to meet the increasing data analysis requirements of OLTP system of minute or hour level.

[0005] The present application provides a data synchronization method, which comprises:

[0006] migrating a structure object of a source database of a first system to a target database of a second system;

[0007] Migrate full data corresponding to a preset historical time point in the source database to the target database;

[0008] Synchronously batch the real-time data corresponding to a time point after the preset historical time point in the source database to the target database.

[0009] Further, in the method described above, migrating the structure object of the source database of the first system to the target database of the second system comprises:

[0010] Mapping the source library / table name in the source database of the first system and the field type in the source table and migrating them to the target database of the second system.

[0011] Further, in the method described above, mapping the source library / table name in the source database of the first system and the field type in the source table and migrating them to the target database of the second system comprises:

[0012] Reading a table creation statement from the source database of the first system; the table creation statement includes the source library / table name created and the field type in the source table;

[0013] Detecting whether the source library / table name created in the table creation statement is in a pre-configured library / table white list that needs to be migrated;

[0014] If yes, according to a preset library table mapper, mapping the source library / table name in the table creation statement to a corresponding target library / table name in the target database, and according to a preset field type mapping table, mapping the field type in the source table to a field type in the target table;

[0015] According to the target library / table name, detecting whether the corresponding target library / table name is included in a library / table list stored in a progress management node; the progress management node is independent of the first system and the second system to realize information persistent storage;

[0016] If no, respectively performing syntax analysis and syntax conversion on the table creation statement to obtain a converted target table creation statement;

[0017] According to the target table creation statement, creating the corresponding target library / table in the target database of the second system;

[0018] Storing the target library / table name in the progress management node.

[0019] Further, in the method described above, if the table creation statement includes an annotation and / or a unique key, respectively performing syntax analysis and syntax conversion on the table creation statement comprises:

[0020] The comment and the unique key in the build table statement are respectively parsed and converted in syntax, so that the comment and the unique key can be migrated to the target database of the second system.

[0021] Further optionally, in the method described above, migrating the full amount of data corresponding to the preset historical time point in the source database to the target database comprises:

[0022] Using a multi-thread concurrent processing mode, each data and the corresponding source database / table name in the full amount of data corresponding to the preset historical time point in the source database are obtained;

[0023] According to the preset database table mapper, the source database / table name of each data is mapped to the corresponding target database / table name in the target database;

[0024] Each data and the corresponding target database / table name are written into a preset memory;

[0025] It is determined whether the writing duration of the memory reaches a preset duration, or whether the total data size written in the memory reaches a preset threshold;

[0026] If yes, each data and the corresponding target database / table name written in the memory are written into a preset queue;

[0027] By polling the queue, each data in the queue is written into the full amount table of the target database according to the corresponding target database / table name.

[0028] Further optionally, in the method described above, using a multi-thread concurrent processing mode, each data and the corresponding source database / table name in the full amount of data corresponding to the preset historical time point in the source database are obtained;

[0029] Using a multi-thread concurrent processing mode, each data and the corresponding source database / table name in the full amount of data in the source database are obtained in the order of small to large according to the primary key value;

[0030] Correspondingly, after each data in the queue is written into the target database according to the corresponding target database / table name by polling the queue, the method further comprises:

[0031] The primary key name and the primary key value of each data written in the target database are stored in a progress management node, and the progress management node is independent of the first system and the second system to realize information persistent storage.

[0032] Further optionally, in the method as described above, if the synchronization is interrupted and then resumed, the method further comprises:

[0033] obtaining the stored maximum primary key value from the progress management node;

[0034] taking the data corresponding to the primary key value next to the maximum primary key value as the starting point of the data synchronization after the restart, so as to continue to obtain each data not migrated from the source database in the order of the primary key value from small to large, and perform the data synchronization processing.

[0035] Further optionally, in the method as described above, the batch synchronization of the real-time data corresponding to the preset historical time point in the source database to the target database comprises:

[0036] for each source DML statement corresponding to the preset historical time point in the source database, obtaining the source database / table name and the operation attribute of the data in each source DML statement;

[0037] mapping the source database / table name of the data in each source DML statement to the corresponding target database / table name in the target database according to the database / table mapper;

[0038] writing the data in each source DML statement, the corresponding target database / table name, and the operation attribute into a preset memory;

[0039] determining whether the writing duration of the memory reaches a preset duration, or whether the total size of the data written in the memory reaches a preset threshold;

[0040] if yes, writing the data in each source DML statement, the corresponding target database / table name, and the operation attribute written in the memory into a preset queue;

[0041] by polling the queue, writing the data in each source DML statement in the queue into the incremental table of the target database according to the corresponding target database / table name and the operation attribute.

[0042] Further optionally, in the method as described above, if each source DML statement further comprises a unique key value of the data, after mapping the source database / table name of the data in each source DML statement to the corresponding target database / table name in the target database according to the preset database / table mapper, before writing the data in each source DML statement, the corresponding target database / table name, and the operation attribute into a preset memory, the method further comprises:

[0043] determining whether the data corresponding to the unique key value is stored in the memory, and if so, deleting the data corresponding to the unique key value, the target database / table name corresponding to the unique key value, and the operation attribute in the memory.

[0044] Further optionally, in the method described above, the batch synchronization of the real-time data in the source database to the target database includes:

[0045] For each source DDL statement in the source database, obtaining a source database / table name in each source DDL statement;

[0046] Detecting whether the source database / table name in each source DDL statement is in a pre-configured white list of databases / tables that need to be migrated;

[0047] If so, mapping the source database / table name in each source DDL statement to a corresponding target database / table name in the target database according to a pre-configured database / table mapper;

[0048] According to the target database / table name that already exists in the second system stored in the progress management node, determining whether each source DDL statement needs to be synchronized to the target database in the second system;

[0049] If so, performing syntax analysis and syntax conversion on each source DDL statement respectively to obtain a corresponding target DDL statement after conversion;

[0050] Writing each target DDL statement in the memory;

[0051] Determining whether the writing duration of the memory reaches a pre-configured duration, or whether the total data size written in the memory reaches a pre-configured threshold;

[0052] If so, writing each target DDL statement written in the memory into a pre-configured queue;

[0053] By polling the queue, applying each target DDL statement in the queue to the full table and the incremental table.

[0054] Further optionally, in the method described above, for a source DDL statement that adds a field in the source database, after applying the corresponding target DDL statement in the queue to the full table and the incremental table, the method further includes:

[0055] Recording in the progress management node a mapping relationship between the position of each table field in the source database and the position of the corresponding table field in the target database.

[0056] Further optionally, in the method as described above, if multiple sub-tables in the source database are synchronized into one target table in the target database, for the source DDL statement of the added field of each sub-table, after the corresponding target DDL statement in the queue is applied to the full-amount table and the incremental table, the method further comprises:

[0057] recording the intermediate state information of the sub-table corresponding to the added field in the progress management node, to identify that the added field has been inserted in the corresponding sub-table; and / or

[0058] setting the position value of the added field in the intermediate state information of each sub-table, and setting the position value of the added field that has not been inserted as an abnormal value different from the normal position value.

[0059] The present application provides a data synchronization device, the device comprising:

[0060] a structure migration unit for migrating the structure object of the source database of the first system into the target database of the second system;

[0061] a full-amount data migration unit for migrating the full-amount data corresponding to a preset historical time point in the source database into the target database;

[0062] an incremental data migration unit for batch synchronizing the real-time data corresponding to a time point after the preset historical time point in the source database into the target database.

[0063] The present application also provides a computer device, the device comprising:

[0064] one or more processors;

[0065] a memory for storing one or more programs;

[0066] when the one or more programs are executed by the one or more processors, the one or more processors implement the data synchronization method as described above.

[0067] The present application also provides a computer readable medium having a computer program stored thereon, which is executed by a processor to implement the data synchronization method as described above.

[0068] The data synchronization method and device, computer device and readable medium of the present application can realize data migration and data quasi-real-time synchronization from a first system such as an OLTP system to a second system such as an OLAP system by using the above-mentioned scheme, which not only supports structure synchronization, but also supports full-amount synchronization and incremental synchronization, facilitating users to quickly realize synchronization function.

[0069] Moreover, the technical scheme of the present application supports synchronization of DDL statements, and will not cause task failure due to table structure change. Meanwhile, by storing information in a progress management node external to the first system and the second system, the present application effectively supports the scenario of synchronizing upstream sub-tables to downstream one-table, and ensures the accuracy of data synchronization. Furthermore, the technical scheme of the present application also supports breakpoint resuming, greatly reducing the operation and maintenance labor cost. In addition, in the synchronization process of the present application, by batch writing into the target database of the second system, the pressure on the target database of the second system can be reduced, and the performance can be improved, and the synchronization delay can be reduced to the order of seconds. Meanwhile, the present application has a small limit on the table structure in the source data, and can meet the user's minute-level analysis requirement to the greatest extent. BRIEF DESCRIPTION OF DRAWINGS

[0070] Figure 1 Flow chart of the data synchronization method embodiment one of the present application.

[0071] Figure 2 An application scenario diagram of the data synchronization method of the present embodiment.

[0072] Figure 3 Flow chart of the data synchronization method embodiment two of the present application.

[0073] Figure 4 Working schematic diagram of the data synchronization device provided by the present application.

[0074] Figure 5 Flow chart of the data synchronization method embodiment three of the present application.

[0075] Figure 6 Flow chart of the data synchronization method embodiment four of the present application.

[0076] Figure 7 A schematic diagram of the data synchronization method of the present embodiment.

[0077] Figure 8 Flow chart of the data synchronization method embodiment five of the present application.

[0078] Figure 9 Structure diagram of the data synchronization device embodiment of the present application.

[0079] Figure 10 Structure diagram of the computer device embodiment of the present application.

[0080] Figure 11 Example diagram of the computer device provided by the present application. DETAILED DESCRIPTION

[0081] In order to make the purpose, technical scheme and advantages of the present application clearer, the present application will be described in detail below with reference to the drawings and specific embodiments.

[0082] Based on the technical requirements of how to synchronize the data in the first system, such as the OLTP system, to the second system, such as the OLAP system in real time, there are various implementation methods in the prior art, the most typical of which are as follows:

[0083] 1) The method of the open source rds_dbsync tool;

[0084] In the synchronization process, two processes are started, one process pulls and parses the binlog from the source MySQL of the OLTP system and stores it in a temporary postgresql database. The other process reads the incremental data from the temporary postgresql database and performs appropriate processing, and writes in batches to the target Greenplum in the OLAP system. This scheme stores the incremental data in the temporary postgresql database first, which is used to merge multiple operations on a single record, and reduces the pressure on the target end by writing in batches to the target end, thereby improving performance.

[0085] However, this scheme does not support the synchronization of database mode definition language (Data Definition Language; DDL) statements, and if the table structure changes during synchronization, it will cause the synchronization to fail. At the same time, a postgresql database needs to be deployed, which adds an additional component and increases the operation and maintenance burden.

[0086] 2) Synchronization through date fields in the database table;

[0087] In this scheme, the upstream source incremental data is first exported through select+where condition, and then the data is written to the downstream OLAP system. The Where clause + timed execution job mode is configured to synchronize the upstream incremental data, which can automatically export data within any specified date, such as minutes, hours, days, weeks, or months.

[0088] This scheme also does not support DDL statement synchronization, and it is limited to having a time date field or a timestamp field in the data table, and must ensure that existing records are not updated or deleted. This is basically difficult to adapt to the scenario of the OLTP system, and is greatly discounted.

[0089] 3) Synchronization by real-time playback of all execution statements in the upstream;

[0090] This scheme listens to the binlog file of the upstream source system in real time to obtain the DML statements and DDL statements executed by the user, and then plays back the DML statements in the upstream in real time on the target end to achieve real-time synchronization.

[0091] The scheme directly replays the upstream data manipulation language (DML) statement in the downstream, and the efficiency of form record insertion is low, and the scheme is not friendly to a distributed analytical database.

[0092] Based on the above, it can be known that the existing scheme of synchronizing data in a first system, such as an OLTP system, to a second system, such as an OLAP system, in quasi-real time can only realize synchronization of some limited data, and has different degrees of defects, and cannot truly realize comprehensive data synchronization. Based on this, the technical scheme of the present application is proposed to provide a data synchronization scheme.

[0093] Figure 1 The flowchart of the data synchronization method embodiment one of the present application is shown in FIG. 1. Figure 1 As shown in the figure, the data synchronization method of the present embodiment can specifically include the following steps:

[0094] 100, migrating the structure object of the source database of the first system to the target database of the second system;

[0095] The execution subject of the data synchronization method of the present embodiment is a data synchronization device, and through the data synchronization device, the data in the source database in the first system is synchronized to the target database in the second system.

[0096] Figure 2 The application scenario diagram of the data synchronization method of the present embodiment is shown in FIG. 2. Figure 2 As shown in the figure, the data synchronization device is located between the first system and the second system, and the data synchronization method of the present embodiment is deployed in the data synchronization device. By using the data synchronization method of the present embodiment, the data in the source database of the first system can be synchronized to the target database in the second system. The first system and the second system in the present embodiment can both be implemented by a server, and the data synchronization device can be implemented by a computer device.

[0097] 101, migrating the full amount of data corresponding to a preset historical time point in the source database to the target database;

[0098] 102, batch synchronizing the real-time data corresponding to a time point after the preset historical time point in the source database to the target database.

[0099] Specifically, in the present embodiment, the data synchronization between different systems can be divided into three levels:

[0100] The first level is migration of a structure object of stored data. When data is stored in a database, the data is stored according to a certain structure. In the data migration process, in order to ensure that the migrated data can also be stored in a corresponding structure, the first step of the embodiment is to migrate the structure object of the source database of the first system to the target database of the second system, so as to make good preparation work for subsequent data migration.

[0101] The second level is migration of full data. Since the data stored in the source database of the first system is constantly changing over time, in order to accurately migrate the data, in the embodiment, a historical time point can be selected, and the full data corresponding to the historical time point in the source database is batch synchronized to the target database. The historical time point of the embodiment can also be referred to as a historical snapshot point, which is equivalent to taking a picture of the full data corresponding to the time point and migrating it. The historical snapshot point of the embodiment can be any time point before the current time, which is not limited here.

[0102] The third level is migration of incremental data. Since the data in the source database will also generate real-time data through continuous operations after the historical time point, this level is to ensure that the real-time data after the historical time point can also be synchronized to the target database of the second system.

[0103] In the embodiment, the first system can be an OLTP system, and the second system can be an OLAP system. The OLTP system can correspond to various types of databases such as MySQL, and the OLAP system can correspond to various types of databases such as GreenPlum (which can also be referred to as GP). In the embodiment, the source database of the first system is taken as an example of the MySQL database in the OLTP, and the target database of the second system is taken as an example of the GreenPlum database in the OLAP. Similarly, the source database of the first system can also be other types of databases in the OLTP, and the target database of the second system can also be other types of databases in the OLAP, which is not limited here.

[0104] Specifically, in the first level of the embodiment, the structure object of the source database of the first system is migrated to the target database of the second system, which can specifically include: migrating the source library / table name and the field type in the source table in the source database of the first system to the target database of the second system after mapping. The source library / table name of the embodiment includes a source library name and a source table name, and the target library / table name includes a target library name and a target table name.

[0105] For example, due to the difference between the OLTP and the OLAP systems, the library name and the table name may not be the same, the library 1 and the table 2 in the OLTP may be the library 3 and the table 5 respectively in the OLAP, and the field type in the table may also be identified as different, for example, the identification 1 may be used to represent the integer type in the OLTP, and the identification 2 may be used to represent the integer type in the OLAP. In order to facilitate the correct identification in the respective systems, the source library name, the source table name and the field type in the source table in the MySQL database of the OLTP system need to be migrated to the GreenPlum database of the OLAP system after being mapped in the embodiment.

[0106] In addition, in actual application, a plurality of sub-tables in the MySQL database of the OLTP system can be saved in one target table after being migrated to the GreenPlum database of the OLAP system, that is, the source library / table name and the field type in the source table in the source database of the first system are migrated to the target database of the second system after being mapped, and at the same time, the plurality of sub-tables in the source database of the first system can be supported to be synchronized to one target table in the target database of the second system.

[0107] Figure 3 The flowchart of the data synchronization method embodiment two of the application is shown. The data synchronization method embodiment two of the application details the implementation manner of "migrating the source library / table name and the field type in the source table in the source database of the first system to the target database of the second system after being mapped", which can specifically include the following steps as shown in Figure 3

[0108] 200, reading a table creation statement from the source database of the first system; the table creation statement includes the created source library / table name and the field type in the source table;

[0109] For example, the table creation statement can also be referred to as a CREATE statement.

[0110] 201, detecting whether the created source library / table name in the table creation statement is in a pre-configured library / table white list which needs to be migrated; if yes, executing step 202; otherwise, returning to step 200 to read the next table creation statement for processing.

[0111] If the corresponding source library or source table is not included in the library / table white list, it means that the data in the corresponding source library and source table does not need to be migrated. The library / table white list in the embodiment can be pre-configured by the staff.

[0112] ​202、According to the preset library table mapper, the source library / table name in the table creation statement is mapped to the corresponding target library / table name in the target database, and according to the preset field type mapping table, the field type in the source table is mapped to the field type in the target table; and step 203 is executed;

[0113] The library table mapper of the embodiment can be pre-configured by staff, and can include the mapping relationship of the names of all libraries and tables in the MySQL database of the OLTP system and the GreenPlum of the OLAP system. In this way, when used, the source library / table name in the table creation statement can be mapped to the corresponding target library / table name in the GreenPlum according to the library table mapper, so as to be stored in the GreenPlum database. Similarly, the field type mapping table also needs to be pre-configured by staff, and each field type in the source table in the table creation statement is mapped to the corresponding field type in the target table.

[0114] 203、According to the target library / table name, it is detected whether the corresponding target library / table is included in the library / table list stored in the progress management node pre-set; if yes, the migration of the library table structure does not need to be performed, and at this time, step 200 is returned to re-read the next table creation statement for processing. If not, step 204 is executed;

[0115] The progress management node of the embodiment realizes information persistent storage independently of the first system and the second system; for example, the progress management node can be located Figure 2 The data synchronization device in the embodiment, or can also be located on other memories, as long as it can be ensured to be independent of the first system and the second system, and the information will not be lost.

[0116] In the embodiment, since the multiple sub-tables in the MySQL database of the upstream OLTP system, such as 128 sub-tables, are mapped to only one target table in the GreenPlum of the downstream OLAP system. In this way, as long as one sub-table of the upstream is read, the target table of the downstream is established. At this time, reading the various sub-tables behind does not need to repeatedly create the corresponding target table. In order to prevent the upstream from reading the various sub-tables behind and the downstream from repeatedly creating the corresponding target table, in the embodiment, the names of the libraries and tables that have been created in the GreenPlum of the OLAP system downstream can be stored in the progress management node to form a library / table list, which can be divided into a library list and a table list. Correspondingly, the library list includes the names of the libraries that have been created in the downstream, and the table list includes the names of the tables that have been created.

[0117] In this embodiment, the stored library / table list can be acquired from the progress management node first, and then it is determined whether the corresponding target library / table name is included in the library / table list. If yes, the structure corresponding migration is not needed to be performed. If not, the subsequent step can be continued to be executed.

[0118] 204, the table building statement is parsed and converted in syntax respectively to obtain the converted target table building statement; and step 205 is executed;

[0119] The table building statement at this time is not the table building statement acquired in step 200, but the table building statement after the library / table name mapping in step 202. The syntax parsing in this embodiment is to analyze the syntax content of each part of the table building statement. The syntax conversion is based on the syntax parsing. After the syntax content of each part of the table building statement is determined, the corresponding syntax is converted into the language suitable for the target database of the second system, so that the table building statement can be parsed and executed in the target database of the second system.

[0120] It should be noted that if the table building statement includes the comment and / or unique key, the comment and / or unique key in the table building statement can also be parsed and converted in syntax respectively to enable the comment and unique key to be migrated to the target database of the second system. For example, the unique key can be converted into a distributed key when being migrated.

[0121] 205, the target library / table is created in the target database of the second system according to the target table building statement; and step 206 is executed;

[0122] Specifically, the target library / table can be created by executing the target table building statement in the target database of the second system.

[0123] 206, the target library / table name is stored in the progress management node.

[0124] This step is to ensure the persistence of the target library / table name.

[0125] Figure 4 A working schematic diagram of a data synchronization device is provided in the present application. As shown in Figure 4As shown, the three steps 100, 101 and 102 in the data synchronization device of the embodiment can be implemented by the Reader module, the Formatter module and the Writer module. First, the Reader module is used to read information from the source database; then the Formatter module is used to convert the syntax or format of the source data system and the target data system; finally, the Writer module is used to interact with the target second system to complete the writing of data. Specifically, the Writer module can further be provided with a producer thread, a memory with a preset size, a queue with a preset length and a consumer thread.

[0126] For example, Figure 2 The step 200 of the embodiment is implemented by the Reader module, the steps 201-204 are implemented by the Formatter module, and the step 206 is implemented by the Writer module. Specifically, the Reader module can extract the CREATE statement from the MySQL database of the OLTP system; the Formatter module filters the CREATE statement extracted by the Reader module through the database / table filter provided with the database / table white list, and after passing through the database / table filter, the CREATE statement is mapped through the database / table mapper, detected based on the database / table list in the progress management node, and if not included, the CREATE statement is parsed and converted by the syntax parser and the syntax converter, all column names and type information, default values and comments are parsed and converted, and the CREATE statement is spliced into a table creation statement meeting the GreenPlum syntax structure of the OLAP system. The producer thread in the Writer module drops the statement into the queue, and the consumer thread completes the connection with the GreenPlum database and creates the incremental table and the full table. In order to realize the case of multiple sub-tables in the upstream, the progress management node can further be provided with a global variable for storing the schema_name.table_name in the downstream GreenPlum database that has been synchronized. After the GreenPlum database is written successfully in the Writer module, the information is saved to an external storage such as Zookeeper or MySQL. In the embodiment, a progress manager can be further provided, and the progress manager loads the information to be stored in the external progress management node.

[0127] Figure 5 The flowchart of the third embodiment of the data synchronization method of the application is shown. The data synchronization method of the embodiment details an implementation manner of "migrating the full data corresponding to a preset historical time point in the source database to the target database", as shown in Figure 4 As shown, the method can specifically include the following steps:

[0128] 300. Use multi-threaded concurrent processing to retrieve the unmigrated data and the corresponding source database / table names from the full data corresponding to the preset historical time points in the source database;

[0129] Specifically, this step is implemented by the Reader module. For example, the Reader module uses multi-threaded concurrent processing to retrieve the unmigrated data and the corresponding source database / table names from the full data corresponding to the preset historical time point in the MySQL database.

[0130] It should be noted that if each data entry also includes a corresponding primary key name and primary key value, a multi-threaded concurrent processing approach can be used. The unmigrated data entries and their corresponding source database / table names can be retrieved from the full dataset of the source database in ascending order of primary key values. In other words, data is read in ascending order of primary key values. Therefore, the progress of data synchronization can be determined based on the primary key value of the currently read data.

[0131] 301. Based on the preset database-table mapper, map the source database / table name of each data to the corresponding target database / table name in the target database;

[0132] This step can be implemented using the Formatter module.

[0133] 302. Write each data item and its corresponding target database / table name into the preset memory.

[0134] 303. Determine whether the memory write time has reached the preset time, or whether the total amount of data written in memory has reached the preset threshold; if yes, proceed to step 304; otherwise, return to step 300 to continue acquiring data.

[0135] 304. Write the data written to memory and the corresponding target database / table name to the preset queue; proceed to step 305.

[0136] 305. By polling the queue, write each piece of data in the queue to the full table of the target database according to the corresponding target database / table name.

[0137] Correspondingly, steps 302-305 can be implemented by the Writer module. Specifically, steps 302-304 are implemented by the producer thread, and step 305 is implemented by the consumer thread to write data to the full table of the target database, such as Greenplum.

[0138] From the synchronization performance, firstly, the efficiency of the single record insertion of the Append optimized table of the GreenPlum database is very low, and is not suitable for the OLTP database synchronization mode, and the DML statement of the upstream is directly played back in real time downstream. Secondly, in the structure synchronization process, the efficiency is not a problem due to the small amount of information data. However, in the full and incremental synchronization process, the batch data may reach the order of ten million or even one hundred million, and the writing needs a certain time, which will block the data reading of the reader module and the data processing of the formatter module.

[0139] The GreenPlum database writes the downstream database in a batch manner in a certain period, and needs to consider that the upstream data is particularly large, such as reaching the order of one hundred million, and the absolute time delay of the physical location of the downstream database and the control node, and the batch writing may need minutes, which will block the data reading of the reader module and the data processing of the formatter module. Therefore, in the embodiment, the writer module is decoupled from the producer thread of reading the upstream data when writing data into the GreenPlum database as a consumer thread. The reader module adopts a multi-thread concurrent export manner to store the exported data of multiple tables into a shared queue. If the downstream writing speed is particularly slow, the read-write speed may not match, causing the local memory to burst. Therefore, in the embodiment, the following speed limiting data import scheme is designed. The producer thread obtains the data synchronized from the upstream and writes it into the memory and then returns. The formatter module continues to read data from the reader module and then synchronizes to the producer thread. The producer thread continues to write data into the memory until a certain period is exceeded or the written data exceeds a fixed memory size. The producer thread then writes the data into the queue. When the consumer thread polls that there is data in the queue, the data is written into the downstream database. In order to prevent the read-write speed from not matching and causing the memory to burst, in the embodiment, a fixed memory size and a fixed length can be allocated to the queue. When one of them is exceeded, the cycle detection and waiting are performed, the reading speed of the upstream synchronization data is limited, and the reading can be restored to normal until the data is consumed by the consumer thread and the queue is below the limit.

[0140] Correspondingly, the step 305 further includes: storing the primary key name PK and the primary key value Value of each data in the write target database into a progress management node, and the progress management node is independent of the first system and the second system to realize information persistent storage.

[0141] Further optionally, since the primary key value of the data is stored in the progress management node in the embodiment, even if interruption occurs in the synchronization process, and the information in the progress management node is persistent, at this time, breakpoint continuation can also be performed according to the maximum primary key value in the progress management node. Specifically, after the synchronization is interrupted and restored, the stored maximum primary key value in the progress management node can be obtained; and the data corresponding to the primary key value adjacent to the maximum primary key value is taken as the starting point of the data synchronization after the restart, so as to continue to obtain each data not migrated from the source database in the order of the primary key value from small to large, and perform the data synchronization processing.

[0142] Figure 6 The flowchart of the data synchronization method of the fourth embodiment of the application is shown in the figure. The data synchronization method of the embodiment details an implementation manner of "synchronously migrating the real-time data corresponding to the preset historical time point in the source database to the target database in batches", as shown in the figure, which can specifically include the following steps: Figure 6

[0143] 400. For each source DML statement corresponding to the preset historical time point in the source database, obtaining the source database / table name and operation attribute of the data in each source DML statement;

[0144] The scheme of the embodiment is based on the data batch synchronization of each source DML statement corresponding to the preset historical time point.

[0145] 401. According to the database / table mapper, mapping the source database / table name of the data in each source DML statement to the corresponding target database / table name in the target database;

[0146] 402. Writing the data in each source DML statement, the corresponding target database / table name and the operation attribute into the preset memory;

[0147] Further optionally, in the embodiment, if the unique key value of the data is also included in each source DML statement, after the source database / table name of the data in each source DML statement is mapped to the corresponding target database / table name in the target database according to the preset database / table mapper in step 401, before the data in each source DML statement, the corresponding target database / table name and the operation attribute are written into the preset memory in step 402, it can further include: judging whether the unique key value corresponding data is stored in the memory, if it exists, deleting the unique key value corresponding data, the corresponding target database / table name and the operation attribute in the memory.

[0148] ​In the scheme of the embodiment, the data synchronized from the upstream retains its operation attribute, such as insert (I for short), delete (D for short) or update (U for short). When writing into the memory, if the unique key value is duplicated with the data previously existing in the memory, only the latest synchronized data can be retained, and the previous data is deleted, so as to ensure that the unique key value of the data written into the memory in the same batch is unique. For example, Figure 7 FIG. 1 is a schematic diagram of the data synchronization method of the embodiment. As shown in the scheme, only the latest changed data, i.e., the data with the latest time, is retained for the data written into the memory in the same batch. Figure 7

[0149] 403. Determine whether the writing duration of the memory reaches a preset duration, or whether the total size of the data written into the memory reaches a preset threshold; if yes, execute step 404; otherwise, return to step 400 to continue to acquire data;

[0150] 404. Write the data in each source DML statement, the corresponding target database / table name and the operation attribute into a preset queue.

[0151] 405. Write the data in each source DML statement in the queue into the incremental table of the target database according to the corresponding target database / table name and the operation attribute by polling the queue.

[0152] In the embodiment, the incremental table corresponding to the full table needs to be created in the database at the downstream, and the incremental table adds an operation attribute field compared with the full table. During the incremental synchronization, the batch data is first written into the incremental table, and then the replace into operation in the MySQL is performed according to the unique key value, so as to update the full table according to the incremental table. The premise of the scheme is that the data synchronized from the upstream must exist the unique key value. In the embodiment, the process of updating the full table according to the incremental table is implemented by the second system, such as the OLAT system, in the GreenPlum database. For the user, the full table is finally used, and the incremental table is not perceived.

[0153] Similarly, each step of the embodiment can also be implemented by the Reader module, the Formatter module and the Writer module. Correspondingly, step 400 can be implemented by the Read module, step 401 can be implemented by the Formatter module, and steps 402-405 can be implemented by the Writer module. Specifically, steps 402-404 are implemented by the producer thread, and 405 is implemented by the consumer thread.

[0154] Figure 8 ​The flowchart of the fifth embodiment of the data synchronization method of the present application is shown in the figure. The data synchronization method of the present embodiment details an implementation of the step of "synchronously writing the real-time data corresponding to the preset historical time point in the source database into the target database in batches". As shown in the figure, the method can include the following steps: Figure 8

[0155] 500. For each source DDL statement in the source database, obtain the source database / table name in the source DDL statement.

[0156] The technical solution of the present embodiment is based on the data batch synchronization of each source DDL statement corresponding to the preset historical time point.

[0157] 501. Detect whether the source database / table name in each source DDL statement is in the pre-configured white list of databases / tables that need to be migrated. If yes, execute step 502; otherwise, if no, return to step 500 to obtain the next source DDL statement.

[0158] 502. According to the preset database table mapper, map the source database / table name in each source DDL statement to the corresponding target database / table name in the target database of the second system; execute step 503.

[0159] 503. According to the target database / table names that already exist in the target database of the second system stored in the progress management node, determine whether each source DDL statement needs to be synchronized to the target database of the second system. If yes, execute step 504; if no, return to step 500 to obtain the next source DDL statement.

[0160] 504. Perform syntax analysis and syntax conversion on each source DDL statement to obtain the corresponding target DDL statement after conversion; return to step 505.

[0161] 505. Write each target DDL statement into a preset memory.

[0162] 506. Determine whether the writing duration of the memory reaches the preset duration, or whether the total data size written in the memory reaches the preset threshold. If yes, execute step 507; otherwise, return to step 500 to continue obtaining data.

[0163] 507. Write each target DDL statement written in the memory into a preset queue; execute step 508.

[0164] 508. Through polling the queue, apply each target DDL statement in the queue to the full table and the incremental table.

[0165] It should be noted that in actual applications, the technical solution of the present embodiment can be located in Figure 6 ​The embodiment shown is executed, that is, the DML statement can be synchronized first, and then the DML statement. At this time, before the DML statement is synchronized, the original DML statement data in the memory needs to be written into the queue immediately, and then the DML statement is also written into the queue, so as to prevent the DML statement and the DML statement from being mixed in the same batch. Conversely, if the DML statement is synchronized first, and then the DML statement, the original DML statement data in the memory also needs to be written into the queue, and then the DML statement is also written into the queue, so as to prevent the DML statement and the DML statement from being mixed in the same batch.

[0166] Similarly, the steps of the embodiment can also be implemented by the Reader module, the Formatter module and the Writer module. Correspondingly, the step 500 can be implemented by the Read module, the steps 501-504 can be implemented by the Formatter module, the steps 505-508 can be implemented by the Writer module, specifically, the steps 505-507 are implemented by the producer thread, and 508 is implemented by the consumer thread.

[0167] In the embodiment, the source database of the first system is still taken as the MySQL database in the OLTP, and the target database of the second system is taken as the GreenPlum database in the OLAP. In order to sequentially convert each source DDL statement into the corresponding statement of the GreenPlum database syntax, in the syntax analysis of each DDL statement, for each type and corresponding keyword, the information shown in Table 1 needs to be obtained, so as to implement the syntax analysis and syntax conversion of each source DDL statement.

[0168] Table 1

[0169]

[0170] Further, optionally, since GreenPlum only supports adding fields at the end of table fields, in order to be compatible with the MySQL random position adding field syntax, in the embodiment, the mapping relationship between the position of each table field in the source database and the position of the corresponding table field in the target database can also be recorded in the progress management node. For example, in the embodiment, the corresponding relationship between the position subscript of the added table field in the source database and the position subscript in the target database can be taken as the corresponding mapping relationship, so that the GreenPlum and MySQL corresponding position mapping relationship of all the fields to be synchronized of the table saved in the progress management node can be realized. In this way, for each added field corresponding operation, the Formatter module can obtain the specific field subscript transmitted from the upstream when parsing, and determine the corresponding field position subscript in GreenPlum according to the mapping relationship, and reassemble the operation and transmit it to the Writer module, so as to ensure the accuracy of data update.

[0171] Since the upstream needs to be considered in the case of sub-table, in order to ensure the correct synchronization of the DDL statement, each sub-table maintains an intermediate state information of the table structure. If all sub-tables only maintain an intermediate state information of the table structure, the first sub-table has added a field or an operation, then the mapping relationship between the MySQL subscript position and the GreenPlum subscript position will be adjusted according to the new position relationship, but the remaining sub-tables are not the same mapping relationship, resulting in that when inserting data according to the same mapping relationship, the field position inserted in GreenPlum is not correct and fails. Therefore, in the embodiment, the intermediate state information of the sub-table corresponding to the added field can be recorded in the progress management node to identify that the added field has been inserted in the corresponding sub-table. That is, each sub-table maintains an intermediate state information, which identifies which field is inserted, and maintains a mapping relationship between the MySQL subscript position of the added table field of the sub-table and the subscript position in the target table of GreenPlum. In this way, it can be clearly known whether each sub-table inserts a field, so as to effectively ensure the accuracy of data synchronization.

[0172] Further optionally, in order to solve the problem of different synchronization of adding fields in each sub-table, a default value attribute and a corresponding position value are added in the intermediate state information of each sub-table. When the sub-table data without added fields is synchronized to the downstream, the Formatter module actively adds the default value and the corresponding position value. In addition, in order to avoid repeated processing in the downstream, the column type is added in the intermediate state information of each sub-table. Therefore, the intermediate state information format of the sub-table in the progress management node can be: library name 1.table name 1: [column name 1: GreenPlum field position index, MySQL field position index, column type, column default value | column name 2: GreenPlum field position index, MySQL field position index, column type, column default value, …]; library name 2.table name 2: [column name 1: GreenPlum field position index, MySQL field position index, column type, column default value | column name 2: GreenPlum field position index, MySQL field position index, column type, column default value, …]. The default value can be obtained according to the configuration value input by the user. It should be noted that the position value of the added field in each sub-table is set to the correct position of the field, such as the index of the position. The corresponding position value of the sub-table without inserted added field can be set to an abnormal value different from the normal position value, such as -1 or infinity.

[0173] Optionally, the scheme of the embodiment can also ensure breakpoint resuming. In the execution process, the table structure in MySQL, the intermediate state information of each sub-table in the progress management node, and the table structure in GreenPlum are consistent. After each successful writing of the Writer module, the intermediate state information of each sub-table in the progress management node is saved to the external system. When the task is restarted, the consistency of the data is ensured.

[0174] The data synchronization method of the embodiment can realize data migration and data quasi-real-time synchronization from a first system such as an OLTP system to a second system such as an OLAP system by using the above scheme. The method can not only support structure synchronization, but also support full synchronization and incremental synchronization, which facilitates users to quickly realize the synchronization function.

[0175] Moreover, the technical solution of the embodiment supports synchronization of DDL statements, and will not cause task failure due to changes of table structures. Meanwhile, by storing information in an external progress management node, the technical solution effectively supports a scenario of synchronizing upstream sub-tables to a downstream one-table, and guarantees accuracy of data synchronization. Furthermore, the technical solution of the embodiment also supports breakpoint resuming, greatly reducing operation and maintenance labor costs. In addition, in the synchronization process of the embodiment, by batch writing to a target end, the pressure on the target end can be reduced, performance can be improved, and synchronization delay can be reduced to a second level. Meanwhile, the technical solution of the embodiment has a small limit on table structures in source data, and can meet user's minute-level analysis requirements to the greatest extent.

[0176] Figure 9 A structural diagram of an embodiment of the data synchronization device of the application is shown in FIG. 1. Figure 9 As shown in the figure, the data synchronization device of the embodiment can specifically include:

[0177] The structure migration unit 10 is configured to migrate a structure object of a source database of a first system to a target database of a second system.

[0178] The full-amount data migration unit 11 is configured to migrate full-amount data corresponding to a preset historical time point in the source database to the target database.

[0179] The incremental data migration unit 12 is configured to batch synchronize real-time data corresponding to a time point after the preset historical time point in the source database to the target database.

[0180] The structure migration unit 10 triggers the full-amount data migration unit 11 to start after the structure migration unit 10 completes work, and the full-amount data migration unit 11 triggers the incremental data migration unit 12 to start after the full-amount data migration unit 11 completes migration.

[0181] The structure migration unit 10, the full-amount data migration unit 11 and the incremental data migration unit 12 of the embodiment can be respectively implemented by using the Reader module, the Formatter module and the Writer module in the embodiment shown in FIG. 2. Figure 4

[0182] Further optionally, the structure migration unit 10 is configured to:

[0183] After mapping the source library / table name in the source database of the first system and the field type in the source table, the structure migration unit 10 migrates the source library / table name and the field type in the source table to the target database of the second system.

[0184] Further optionally, the structure migration unit 10 is configured to:

[0185] The structure migration unit 10 reads a table creation statement from the source database of the first system; the table creation statement includes a created source library / table name and a field type in a source table.

[0186] ​determining whether the source database / table name created in the table creation statement is in a pre-configured white list of databases / tables that need to be migrated;

[0187] If yes, mapping the source database / table name in the table creation statement to the corresponding target database / table name in the target database according to a pre-configured database / table mapper, and mapping the field type in the source table to the field type in the target table according to a pre-configured field type mapping table;

[0188] According to the target database / table name, determining whether the corresponding target database / table name is included in the database / table list stored in the progress management node that is pre-configured; the progress management node is independent of the first system and the second system to implement information persistent storage;

[0189] If no, performing syntax analysis and syntax conversion on the table creation statement respectively to obtain a converted target table creation statement;

[0190] According to the target table creation statement, creating the corresponding target database / table in the target database of the second system;

[0191] Storing the target database / table name in the progress management node.

[0192] Further optionally, the structure migration unit 10 is configured to:

[0193] Performing syntax analysis and syntax conversion on the comment and / or unique key in the table creation statement respectively, so that the comment and the unique key can be migrated to the target database of the second system.

[0194] Further optionally, the full data migration unit 11 is configured to:

[0195] Obtaining the full data corresponding to the pre-configured historical time point, the un-migrated data, and the corresponding source database / table name from the source database in a multi-thread concurrent processing manner;

[0196] Mapping the source database / table name of each data to the corresponding target database / table name in the target database according to a pre-configured database / table mapper;

[0197] Writing each data and the corresponding target database / table name into a pre-configured memory;

[0198] Determining whether the writing duration of the memory reaches a pre-configured duration, or whether the total data size written in the memory reaches a pre-configured threshold;

[0199] If yes, writing each data and the corresponding target database / table name written in the memory into a pre-configured queue;

[0200] According to the corresponding target database / table name, writing each data in the queue into the full table of the target database.

[0201] Further optionally, the full data migration unit 11 is configured to:

[0202] According to the order of the primary key values from small to large, the un-migrated data and the corresponding source database / table names are obtained from the full data in the source database by using the multi-thread concurrent processing mode.

[0203] Correspondingly, the full data migration unit 11 is also configured to:

[0204] The primary key name and the primary key value of each data written into the target database are stored in the progress management node, and the progress management node is independent of the first system and the second system to realize information persistent storage.

[0205] Further optionally, the full data migration unit 11 is configured to:

[0206] If the synchronization is interrupted and then restored, the stored maximum primary key value is obtained from the progress management node.

[0207] The data corresponding to the primary key value adjacent to the maximum primary key value is taken as the starting point of the data synchronization after the restart, so that the un-migrated data is obtained from the source database according to the order of the primary key values from small to large, and the data synchronization processing is performed.

[0208] Further optionally, the incremental data migration unit 12 is configured to:

[0209] For each source DML statement corresponding to a preset historical time point in the source database, the source database / table name and the operation attribute of the data in each source DML statement are obtained.

[0210] According to the database table mapper, the source database / table name of the data in each source DML statement is mapped to the corresponding target database / table name in the target database.

[0211] The data in each source DML statement, the corresponding target database / table name, and the operation attribute are written into a preset memory.

[0212] It is determined whether the writing duration of the memory reaches a preset duration, or whether the total size of the data written in the memory reaches a preset threshold.

[0213] If yes, the data in each source DML statement, the corresponding target database / table name, and the operation attribute written in the memory are written into a preset queue.

[0214] Through polling the queue, the data in each source DML statement in the queue is written into the incremental table of the target database according to the corresponding target database / table name and the operation attribute.

[0215] Further optionally, the incremental data migration unit 12 is configured to:

[0216] If the unique key value of the data is also included in each source DML statement, it is judged whether the data corresponding to the unique key value is stored in the memory. If the data exists, the data corresponding to the unique key value, the target database / table name and the operation attribute in the memory are deleted.

[0217] Further optionally, the incremental data migration unit 12 is configured to:

[0218] For each source DDL statement in the source database, the source database / table name in each source DDL statement is obtained.

[0219] It is detected whether the source database / table name in each source DDL statement is in the pre-configured white list of databases / tables that need to be migrated.

[0220] If yes, the source database / table name in each source DML statement is mapped to the corresponding target database / table name in the target database of the second system according to the pre-configured database / table mapper.

[0221] According to the target database / table name that already exists in the target database of the second system stored in the progress management node, it is judged whether each source DDL statement needs to be synchronized to the target database of the second system.

[0222] If yes, the syntax analysis and syntax conversion are performed on each source DDL statement respectively to obtain the corresponding target DDL statement after conversion.

[0223] Each target DDL statement is written into the pre-configured memory.

[0224] It is judged whether the writing duration of the memory reaches the pre-configured duration, or whether the total data size written in the memory reaches the pre-configured threshold.

[0225] If yes, each target DDL statement written in the memory is written into the pre-configured queue.

[0226] Through polling the queue, each target DDL statement in the queue is applied to the full table and the incremental table.

[0227] Further optionally, the incremental data migration unit 12 is configured to:

[0228] The mapping relationship between the position of the added field in the source database and the position of the added field in the target database is recorded in the progress management node.

[0229] Further optionally, the incremental data migration unit 12 is configured to:

[0230] If the multiple sub-tables in the source database are synchronized to one target table in the target database, the intermediate state information of the sub-table corresponding to the added field is recorded in the progress management node to identify that the added field has been inserted in the corresponding sub-table; and / or

[0231] The default value of the added field is set in the intermediate state information of each sub-table, and the default value of the added field not inserted is different from the default value of the added field inserted.

[0232] The data synchronization device of the embodiment implements the same implementation principle and technical effect of data synchronization as the above-mentioned related method embodiments by using the above-mentioned modules, and details can be referred to the description of the above-mentioned related method embodiments, which will not be described herein.

[0233] Figure 10 The structural diagram of the computer device embodiment of the present application is shown in FIG. 1. Figure 10 As shown in FIG. 1, the computer device of the embodiment includes one or more processors 30 and a memory 40, and the memory 40 is used to store one or more programs, and when the one or more programs stored in the memory 40 are executed by the one or more processors 30, the one or more processors 30 implement the data synchronization method of the embodiment shown in FIG. 2. Figures 1-8 Figure 10 As shown in the embodiment, the computer device includes multiple processors 30.

[0234] For example, Figure 11 The structural diagram of the computer device of the present application is shown in FIG. 1. Figure 11 A block diagram of an example computer device 12a suitable for implementing embodiments of the present application is shown. Figure 11 The computer device 12a shown is merely an example, and should not impose any limitation on the function and use range of the embodiments of the present application.

[0235] As shown in FIG. 1, the computer device 12a is in the form of a general-purpose computing device. The components of the computer device 12a can include, but are not limited to, one or more processors 16a, a system memory 28a, and a bus 18a connecting the different system components including the system memory 28a and the processor 16a. Figure 11 The bus 18a represents one or more of several types of bus structures, including a memory bus or memory controller, a peripheral bus, a graphics acceleration port, a processor or local bus using any of a variety of bus architectures including Industry Standard Architecture (ISA), Micro Channel Architecture (MCA), Enhanced ISA (EISA), Video Electronics Standards Association (VESA) local bus, and Peripheral Component Interconnect (PCI) bus.

[0236] The computer device 12a typically includes a variety of computer system readable media. These media can be any available media that can be accessed by the computer device 12a and includes both volatile and nonvolatile media, removable and non-removable media.

[0237] The computer device 12a typically includes a variety of computer system readable media. These media can be any available media that can be accessed by the computer device 12a and includes both volatile and nonvolatile media, removable and non-removable media.​

[0238] System memory 28a can include computer system readable media in the form of volatile memory, such as random access memory (RAM) 30a and / or cache memory 32a. Computer device 12a can further include other removable / non-removable, volatile / non-volatile computer system storage media. By way of example only, storage system 34a can be provided for reading from and writing to a non-removable, non-volatile magnetic media (e.g., a "hard drive"). Figure 11 Although not shown, a magnetic disk drive can also be utilized in some embodiments for reading from and writing to a removable, non-volatile magnetic disk (e.g., a "floppy disk"), and an optical disk drive can be utilized in some embodiments for reading from and writing to a removable, non-volatile optical disk (e.g., a CD-ROM, DVD-ROM or other optical media). Figure 11 In some embodiments, disk drives and optical disk drives can be included in or connected to computer device 12a via one or more data media interfaces. System memory 28a can include a number of program products including, for example, an operating system 36a, one or more application programs 38a, other program modules 42a, and program data 44a, etc. Each of such program products can include, but is not limited to, electronic instructions 46a. Figures 1-9 implemented in the form of software and / or firmware objects.

[0239] Program / utility 40a having a set (at least one) of program modules 42a, can be stored in, for example, system memory 28a and implemented or accessed by computer device 12a and / or other computer devices. It is appreciated that each of the program modules 42a includes, but is not limited to, an operating system, one or more applications, other program modules, and program data, each of or some combination of which can include implementation of a network environment. Program modules 42a generally carry out the functions and / or methodologies described above with respect to the various embodiments of the present application. Figures 1-9

[0240] Computer device 12a can also communicate with one or more external devices 14a such as a keyboard, a pointing device, a display 24a, etc.; one or more devices that enable a user to interact with computer device 12a; and / or one or more devices that enable computer device 12a to communicate with one or more other computer devices. Such communication can be via input / output (I / O) interfaces 22a. Similarly, computer device 12a can communicate with one or more networks such as a local area network (LAN), a general wide area network (WAN), and / or a public network such as the Internet, via network adapter 20a. As indicated, network adapter 20a can be communicatively coupled to bus 18a via the other components of computer device 12a. It will be appreciated that other hardware and / or software modules can be used in conjunction with computer device 12a. Such modules include, but are not limited to, microcode, device drivers, redundant processing units, external disk drive arrays, RAID systems, tape drives, and data archival storage systems, etc. ​

[0241] The processor 16a performs various functional applications and data processing by running programs stored in the system memory 28a, such as implementing the data synchronization method shown in the above embodiments.

[0242] The present application also provides a computer readable medium, which stores a computer program, and the computer program is executed by a processor to implement the data synchronization method shown in the above embodiments.

[0243] The computer readable medium of the present embodiment can include the RAM 30a in the system memory 28a, and / or the cache memory 32a, and / or the storage system 34a in the embodiments shown above. Figure 11

[0244] With the development of technology, the propagation path of computer programs is no longer limited to tangible media, but can also be directly downloaded from the network or obtained by other means. Therefore, the computer readable medium in the present embodiment can include not only tangible media, but also intangible media.

[0245] The computer readable medium of the present embodiment can adopt any combination of one or more computer readable media. The computer readable medium can be a computer readable signal medium or a computer readable storage medium. The computer readable storage medium may, for example, be but is not limited to an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, device or apparatus, or any combination of the above. More specific examples (non-exhaustive list) of the computer readable storage medium include an electrical connection having one or more wires, a portable computer diskette, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or 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 this document, the computer readable storage medium can be any tangible medium containing or storing a program that can be used by or in conjunction with an instruction execution system, device or apparatus.

[0246] The computer readable signal medium can include a data signal propagating in a baseband or as part of a carrier wave, carrying computer readable program code. Such a propagated data signal can take on many forms, including but not limited to electro-magnetic, optical, or any suitable combination thereof. The computer readable signal medium can also be any computer readable medium that is not a computer readable storage medium and that can be used to carry or store computer readable program code used by or in connection with an instruction execution system, device or apparatus.

[0247] ​The computer readable media on which the program code can be carried by any suitable medium, including but not limited to wireless, wired, optical fiber cable, RF, and the like, or any suitable combination of these.

[0248] Computer program code for carrying out operations of the present application can be written in any combination of one or more programming languages, including an object oriented programming language such as Java, Smalltalk, C++ or the like and conventional procedural programming languages, such as the "C" programming language or similar programming languages. The program code can execute entirely on the user's computer, partly on the user's computer, as a stand-alone software package, partly on the user's computer and partly on a remote computer or entirely on the remote computer or server. In the latter scenario, the remote computer can be connected to the user's computer through any type of network, including a local area network (LAN) or a wide area network (WAN), or the connection can be made to an external computer (for example, through the Internet using an Internet Service Provider).

[0249] In several embodiments of the present application, it should be understood that the disclosed system, apparatus, and method can be implemented in other ways. For example, the above-described apparatus embodiments are merely illustrative. For example, the division of the units is merely a logical function division. In actual implementation, another division manner can be used.

[0250] The units described as separate components can or can not be physically separate, and the components shown as units can or can not be physical units, that is, can be located in one place, or can be distributed on multiple network units. Part or all of the units can be selected according to actual needs to achieve the purpose of the embodiment.

[0251] In addition, each functional unit in each embodiment of the present application can be integrated into a processing unit, or each unit can be physically present separately, or two or more units can be integrated into one unit. The integrated unit can be realized in the form of hardware or in the form of hardware plus software functional units.

[0252] The integrated unit in the form of software function unit can be stored in a computer readable storage medium. The software function unit is stored in a storage medium, and includes a plurality of instructions for enabling a computer device (which can be a personal computer, a server, or a network device, etc.) or a processor to execute part of steps of the method according to various embodiments of the present application. The storage medium includes a U disk, a mobile hard disk, a read-only memory (ROM), a random access memory (RAM), a magnetic disk or an optical disk, and various storage media capable of storing program codes.

[0253] The above merely provides the preferred embodiments of the present application, and is not intended to limit the present application. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present application shall be included in the protection scope of the present application.

Claims

1. A data synchronization method, characterized in that, The method includes: The structure object of the source database of the first system is migrated to the target database of the second system; the first system includes an online transaction processing system, and the second system includes an online analytical processing system; the structure object is used to represent the structure of data storage; Based on the migration structure object, the full data corresponding to the preset historical time point in the source database is migrated to the target database; Based on the migration structure object, the real-time data corresponding to the preset historical time point in the source database is batch synchronized to the target database; Migrate the structure objects from the source database of the first system to the target database of the second system, including: The system reads table creation statements from the source database of the first system; the table creation statements include the name of the source database / table to be created and the field types in the source table; When the source database / table name created in the table creation statement is detected and determined to be in the pre-configured whitelist of databases / tables that need to be migrated, the source database / table name in the table creation statement is mapped to the corresponding target database / table name in the target database according to the preset database-table mapper. At the same time, the field types in the source table are mapped to the field types in the target table according to the preset field type mapping table. Based on the target library / table name, it is detected and determined that the corresponding target library / table name is not included in the list of libraries / tables stored in the pre-set progress management node; the progress management node independently of the first system and the second system implements persistent information storage. The table creation statement is parsed and transformed to obtain the transformed target table creation statement; Based on the target table creation statement, the corresponding target database / table is created in the target database of the second system.

2. The method according to claim 1, characterized in that, The method further includes storing the target library / table name in the progress management node.

3. The method according to claim 1, characterized in that, If the table creation statement includes comments and / or unique keys, the table creation statement is parsed and transformed, including: The comments and / or unique keys in the table creation statement are parsed and transformed to enable the comments and unique keys to be migrated to the target database of the second system.

4. The method according to claim 1, characterized in that, Migrating all data corresponding to preset historical time points in the source database to the target database includes: The system employs a multi-threaded concurrent processing approach to retrieve, from the source database, the unmigrated data and their corresponding source database / table names from the full dataset corresponding to the preset historical time point. According to the preset database-table mapper, the source database / table name of each data is mapped to the corresponding target database / table name in the target database; Write each of the data and the corresponding target library / table name into a preset memory; Determine whether the write time to the memory reaches a preset time, or whether the total amount of data written to the memory reaches a preset threshold; If so, write each piece of data written into the memory and the corresponding target library / table name into a preset queue; By polling the queue, each piece of data in the queue is written to the full table of the target database according to the corresponding target database / table name.

5. The method according to claim 4, characterized in that, The process employs multi-threaded concurrent processing to retrieve, from the source database, the unmigrated data and their corresponding source database / table names from the full dataset corresponding to the preset historical time point, including: Using a multi-threaded concurrent processing approach, each unmigrated data and its corresponding source database / table name are retrieved from the full data in the source database in ascending order of primary key values. Correspondingly, after polling the queue and writing each piece of data in the queue into the target database according to the corresponding target database / table name, the method further includes: The primary key name and primary key value of each piece of data written into the target database are stored in the progress management node, which is independent of the first system and the second system to achieve persistent information storage.

6. The method according to claim 5, characterized in that, If synchronization is interrupted and then resumed, the method further includes: Retrieve the maximum primary key value stored from the progress management node; The data corresponding to the nearest neighbor of the maximum primary key value is used as the starting point for data synchronization after restart, so that the unmigrated data can be retrieved from the source database in ascending order of primary key value and data synchronization processing can be performed.

7. The method according to claim 4, characterized in that, The process of batch synchronizing real-time data corresponding to the preset historical time point in the source database to the target database includes: For each source DML statement corresponding to the preset historical time point in the source database, obtain the source database / table name and operation attributes of the data in each source DML statement; According to the database-table mapper, the source database / table name of the data in each of the source DML statements is mapped to the corresponding target database / table name in the target database; The data in each of the source DML statements, the corresponding target database / table name, and the operation attributes are written into the preset memory. Determine whether the write time to the memory reaches a preset time, or whether the total amount of data written to the memory reaches a preset threshold; If so, the data in each of the source DML statements written to the memory, the corresponding target library / table name, and the operation attributes are written into the preset queue; By polling the queue, the data from each source DML statement in the queue is written to the incremental table of the target database according to the corresponding target database / table name and the operation attribute.

8. The method according to claim 7, characterized in that, If each of the source DML statements also includes a unique key value for data, after mapping the source database / table name of the data in each of the source DML statements to the corresponding target database / table name in the target database according to a preset database / table mapper, and before writing the data in each of the source DML statements, the corresponding target database / table name, and the operation attribute into a preset memory, the method further includes: Determine whether the data corresponding to the unique key value is stored in the memory. If it exists, delete the data corresponding to the unique key value, the corresponding target database / table name, and the operation attribute from the memory.

9. The method according to claim 7, characterized in that, The process of batch synchronizing real-time data from the source database to the target database includes: For each source DDL statement in the source database, obtain the source database / table name in each source DDL statement; Check whether the source database / table name in each source DDL statement is in the pre-configured whitelist of databases / tables that need to be migrated; If so, according to the preset database and table mapper, the source database / table names in each of the source DDL statements are mapped to the corresponding target database / table names in the target database; Based on the target database / table names already existing in the target database of the second system stored in the progress management node, determine whether each source DDL statement needs to be synchronized to the target database of the second system; If necessary, perform syntax parsing and syntax transformation on each of the source DDL statements to obtain the corresponding target DDL statements after transformation. Each of the target DDL statements is written into the preset memory; Determine whether the write time to the memory reaches a preset time, or whether the total amount of data written to the memory reaches a preset threshold; If so, write each of the target DDL statements written in the memory into the preset queue; By polling the queue, each target DDL statement in the queue is applied to the full table and the incremental table.

10. The method according to claim 9, characterized in that, If multiple partitioned tables in the source database are synchronized to a single target table in the target database, for source DDL statements that add fields in the source database, after applying the corresponding target DDL statements in the queue to the full table and the incremental table, the method further includes: The progress management node records the mapping relationship between the position of each table field in the source database and the position of the corresponding table field in the target database.

11. The method according to claim 10, characterized in that, If multiple partitioned tables in the source database are synchronized to a single target table in the target database, for each partitioned table's source DDL statement adding a field, after applying the corresponding target DDL statement in the queue to the full table and the incremental table, the method further includes: The intermediate status information of the sub-table corresponding to the added field is recorded in the progress management node to indicate that the added field has been inserted in the corresponding sub-table; and / or In the intermediate status information of each of the sub-tables, the position value of the added field is set, and the position value of the added field that has not been inserted is set to an abnormal value that is different from the normal position value.

12. A data synchronization device, characterized in that, The device includes: A structure migration unit is used to migrate structure objects from the source database of the first system to the target database of the second system; the first system includes an online transaction processing system, and the second system includes an online analytical processing system; the structure object is used to represent the structure of data storage; The full data migration unit is used to migrate all data corresponding to a preset historical time point in the source database to the target database based on the migration structure object. The incremental data migration unit is used to batch synchronize real-time data corresponding to the preset historical time point in the source database to the target database based on the migration structure object. The structural migration unit is used for: The system reads table creation statements from the source database of the first system; the table creation statements include the name of the source database / table to be created and the field types in the source table; When the source database / table name created in the table creation statement is detected and determined to be in the pre-configured whitelist of databases / tables that need to be migrated, the source database / table name in the table creation statement is mapped to the corresponding target database / table name in the target database according to the preset database-table mapper. At the same time, the field types in the source table are mapped to the field types in the target table according to the preset field type mapping table. Based on the target library / table name, it is detected and determined that the corresponding target library / table name is not included in the list of libraries / tables stored in the pre-set progress management node; the progress management node independently of the first system and the second system implements persistent information storage. The table creation statement is parsed and transformed to obtain the transformed target table creation statement; Based on the target table creation statement, the corresponding target database / table is created in the target database of the second system.

13. The apparatus according to claim 12, characterized in that, The structural migration unit is also used for: Store the target library / table name in the progress management node.

14. The apparatus according to claim 12, characterized in that, The structural migration unit is used for: If the table creation statement includes comments and / or unique keys, the comments and / or unique keys in the table creation statement are parsed and transformed respectively, so that the comments and unique keys can be migrated to the target database of the second system.

15. The apparatus according to claim 12, characterized in that, The full data migration unit is used for: The system employs a multi-threaded concurrent processing approach to retrieve, from the source database, the unmigrated data and their corresponding source database / table names from the full dataset corresponding to the preset historical time point. According to the preset database-table mapper, the source database / table name of each data is mapped to the corresponding target database / table name in the target database; Write each of the data and the corresponding target library / table name into a preset memory; Determine whether the write time to the memory reaches a preset time, or whether the total amount of data written to the memory reaches a preset threshold; If so, write each piece of data written into the memory and the corresponding target library / table name into a preset queue; By polling the queue, each piece of data in the queue is written to the full table of the target database according to the corresponding target database / table name.

16. The apparatus according to claim 15, characterized in that, The full data migration unit is used for: Using a multi-threaded concurrent processing approach, each unmigrated data and its corresponding source database / table name are retrieved from the full data in the source database in ascending order of primary key values. The full data migration unit is also used for: The primary key name and primary key value of each piece of data written into the target database are stored in the progress management node, which is independent of the first system and the second system to achieve persistent information storage.

17. The apparatus according to claim 16, characterized in that, The full data migration unit is used for: If synchronization is interrupted and then resumed, retrieve the maximum primary key value stored from the progress management node; The data corresponding to the nearest neighbor of the maximum primary key value is used as the starting point for data synchronization after restart, so that the unmigrated data can be retrieved from the source database in ascending order of primary key value and data synchronization processing can be performed.

18. The apparatus according to claim 15, characterized in that, The incremental data migration unit is used for: For each source DML statement corresponding to the preset historical time point in the source database, obtain the source database / table name and operation attributes of the data in each source DML statement; According to the database-table mapper, the source database / table name of the data in each of the source DML statements is mapped to the corresponding target database / table name in the target database; The data in each of the source DML statements, the corresponding target database / table name, and the operation attributes are written into the preset memory. Determine whether the write time to the memory reaches a preset time, or whether the total amount of data written to the memory reaches a preset threshold; If so, the data in each of the source DML statements written to the memory, the corresponding target library / table name, and the operation attributes are written into the preset queue; By polling the queue, the data from each source DML statement in the queue is written to the incremental table of the target database according to the corresponding target database / table name and the operation attribute.

19. The apparatus according to claim 18, characterized in that, The incremental data migration unit is used for: If each of the source DML statements also includes a unique key value for data, determine whether the data corresponding to the unique key value is stored in the memory. If it exists, delete the data corresponding to the unique key value, the corresponding target database / table name, and the operation attribute from the memory.

20. The apparatus according to claim 18, characterized in that, The incremental data migration unit is used for: For each source DDL statement in the source database, obtain the source database / table name in each source DDL statement; Check whether the source database / table name in each source DDL statement is in the pre-configured whitelist of databases / tables that need to be migrated; If so, according to the preset database and table mapper, the source database / table names in each of the source DDL statements are mapped to the corresponding target database / table names in the target database; Based on the target database / table names already existing in the target database of the second system stored in the progress management node, determine whether each source DDL statement needs to be synchronized to the target database of the second system; If necessary, perform syntax parsing and syntax transformation on each of the source DDL statements to obtain the corresponding target DDL statements after transformation. Each of the target DDL statements is written into the preset memory; Determine whether the write time to the memory reaches a preset time, or whether the total amount of data written to the memory reaches a preset threshold; If so, write each of the target DDL statements written in the memory into the preset queue; By polling the queue, each target DDL statement in the queue is applied to the full table and the incremental table.

21. The apparatus according to claim 20, characterized in that, The incremental data migration unit is used for: If multiple tables in the source database are synchronized into a single target table in the target database, for source DDL statements that add fields in the source database, the progress management node records the mapping relationship between the position of each table field in the source database and the position of the corresponding table field in the target database.

22. The apparatus according to claim 21, characterized in that, The incremental data migration unit is used for: If multiple partitioned tables in the source database are synchronized into a single target table in the target database, the intermediate status information of the partitioned table corresponding to the added field is recorded in the progress management node to indicate that the added field has been inserted into the corresponding partitioned table; and / or In the intermediate status information of each of the sub-tables, the position value of the added field is set, and the position value of the added field that has not been inserted is set to an abnormal value that is different from the normal position value.

23. A computer device, characterized in that, The device includes: One or more processors; Memory, used to store one or more programs; When the one or more programs are executed by the one or more processors, the one or more processors implement the method as described in any one of claims 1-11.

24. A computer-readable medium having a computer program stored thereon, characterized in that, When the program is executed by the processor, it implements the method as described in any one of claims 1-11.

Images