Large data volume data synchronization method, device and system

Abstract

The invention provides a large data volume data synchronization method, device and system, which can improve the data synchronization efficiency. The large data volume data synchronization method comprises the following steps: dividing extracted source data into a plurality of batches; wherein the data volume of each batch is smaller than the data volume of the source data; obtaining a primary key value of each record from each batch based on a preset primary key column; wherein the primary key value is a unique identifier used for distinguishing newly-added data from to-be-updated data; deleting old data corresponding to the primary key value in the target table in batches; and writing the newly added data and the updated data into a target table in batches on the basis of the primary key value.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing, and particularly relates to a method, device and system for data synchronization of a large amount of data. Background Art

[0002] In today's big data era, various data sources are very extensive. In order to make full use of the data value, it is usually necessary to perform downstream synchronization of the data for data analysis and use. During the process of incremental synchronization of changed data when there is source data, the requirement for timeliness is relatively high. When data is updated upstream, the expectation downstream is to synchronize in a timely manner to meet the requirement of consistent source target data. However, since various types of databases do not uniformly support the insert and update modes, this increases the difficulty of adapting data synchronization tools. Generally, it is often necessary to query and judge one by one. There is an execution of an update statement, but no execution of an insert statement. When the data volume is very large, the synchronization and update efficiency often decreases, and it is impossible to ensure timely update. Summary of the Invention

[0003] In order to solve the above technical problems, the present invention is proposed. Embodiments of the present invention provide a method, device and system for data synchronization of a large amount of data, which can improve the data synchronization efficiency.

[0004] According to one aspect of the present invention, there is provided a method for data synchronization of a large amount of data, including: dividing the extracted source data into multiple batches; wherein, the data volume of each batch is less than the data volume of the source data; based on a preset primary key column, obtaining the primary key value of each record from each batch; wherein, the primary key value is a unique identifier for distinguishing new data from data to be updated; deleting the old data corresponding to the primary key value in the target table batch by batch; writing the new data and updated data into the target table batch by batch based on the primary key value.

[0005] In an embodiment, deleting the old data corresponding to the primary key value in the target table batch by batch includes: based on the primary key value, constructing a batch delete SQL statement for each batch; wherein, the batch delete SQL statement includes parameters formed by concatenating the name of the target table and the primary key value of the corresponding batch; based on the batch delete SQL statements constructed for each batch, deleting the old data corresponding to the primary key value in the target table batch by batch.

[0006] In an embodiment, writing the new data and updated data into the target table batch by batch based on the primary key value includes: based on the primary key value and the structure of the target table, constructing a batch insert SQL statement for each batch; wherein, the batch insert SQL statement includes the name of the target table and the primary key value of the corresponding batch; based on the batch insert SQL statements constructed for each batch, writing the new data and updated data into the target table batch by batch.

[0007] In one embodiment, the SQL statement includes an INSERT statement; based on the primary key value and the structure of the target table, a batch-insert SQL statement is constructed for each batch, including: based on the primary key value and the structure of the target table, a batch-insert INSERT statement is constructed for each batch; wherein, the INSERT statement is used to insert multiple records at one time, and each record corresponds to a primary key value; wherein, based on the batch-insert SQL statement constructed for each batch, the new data and the updated data are written into the target table in batches, including: based on the batch-insert INSERT statement constructed for each batch, the new data and the updated data are written into the target table in batches.

[0008] In one embodiment, the method for synchronizing a large amount of data further includes: for a target batch, batch-deleting the old data corresponding to the primary key value in the target table as a first operation; wherein, the target batch is any one of multiple batches; for the target batch, batch-writing the new data and the updated data into the target table as a second operation; wrapping the first operation and the second operation in the target batch into the same transaction; when both the first operation and the second operation in the target batch are successfully operated, committing the transaction.

[0009] In one embodiment, after wrapping the first operation and the second operation in the target batch into the same transaction, the method for synchronizing a large amount of data further includes: when an error occurs in the first operation in the target batch, rolling back the transaction; when an error occurs in the second operation in the target batch, rolling back the transaction; when errors occur in both the first operation and the second operation in the target batch, rolling back the transaction.

[0010] In one embodiment, dividing the extracted source data into multiple batches includes: presetting the maximum read data volume for each batch; wherein, the maximum read data volume for each batch is the same; based on the maximum read data volume for each batch, dividing the extracted source data into multiple batches.

[0011] In one embodiment, dividing the extracted source data into multiple batches includes: dividing the extracted source data into multiple batches according to a timestamp or a time range; or dividing the extracted source data into multiple batches according to the hash value or partitioning rule of a preset field of the data; or based on batch division by file splitting, splitting the extracted source data into multiple small files, and each small file serves as a batch; or using a generator to divide the extracted source data into multiple batches one by one.

[0012] According to another aspect of the present invention, there is provided an apparatus for large - volume data synchronization, including: a partitioning module for dividing the extracted source data into multiple batches; wherein the data volume of each batch is less than that of the source data; an acquisition module for obtaining the primary key of each record from each batch based on a preset primary key column; wherein the primary key is a unique identifier for distinguishing new data from data to be updated; a deletion module for deleting the old data corresponding to the primary key values in the target table batch by batch; and a writing module for writing the new data and updated data into the target table batch by batch based on the primary key values.

[0013] According to another aspect of the present invention, there is provided a system for large - volume data synchronization, including: a relational database; wherein the relational database includes a target table; and the apparatus for large - volume data synchronization as described in the above - mentioned embodiment, and the apparatus for large - volume data synchronization is communicatively connected to the relational database.

[0014] The method, apparatus and system for large - volume data synchronization provided by the present invention use the primary key for batch deletion and batch addition, eliminating the processing logic of querying and judging, greatly shortening the time required for data synchronization, improving the efficiency of data synchronization in the insert - update mode, and being able to adapt to and be compatible with multiple relational databases. The downstream of data synchronization can obtain the updated data in a timely manner, enhancing the timeliness of data and the business response speed, and ensuring the timeliness and accuracy of downstream data. BRIEF DESCRIPTION OF THE DRAWINGS

[0015] By describing the embodiments of the present invention in more detail in conjunction with the accompanying drawings, the above - mentioned and other objects, features and advantages of the present invention will become more obvious. The drawings are used to provide a further understanding of the embodiments of the present invention and constitute a part of the specification, and are used to explain the present invention together with the embodiments of the present invention, and do not constitute a limitation to the present invention. In the drawings, the same reference numerals generally represent the same components or steps.

[0016] Figure 1 is a flowchart showing the method for large - volume data synchronization provided by an exemplary embodiment of the present invention.

[0017] Figure 2 is a schematic diagram showing the principle of the method for large - volume data synchronization provided by an exemplary embodiment of the present invention.

[0018] Figure 3 is a schematic diagram showing the structure of the apparatus for large - volume data synchronization provided by an exemplary embodiment of the present invention. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0019] Hereinafter, exemplary embodiments according to the present invention will be described in detail with reference to the accompanying drawings. Obviously, the described embodiments are only a part of the embodiments of the present invention, rather than all the embodiments of the present invention. It should be understood that the present invention is not limited by the exemplary embodiments described herein.

[0020] When there is new data in the source data, the historical data will also be updated regularly. Therefore, during the data incremental synchronization process, it is necessary to use the upsert (insert and update) mode to insert new data and update the regularly updated historical data. In the actual synchronization process, it is often possible to support the upsert modes of multiple relational databases at the same time. The common method (row-by-row method) is to traverse each piece of data, query the target data row by row according to the primary key, judge whether it exists and execute the update statement if it exists, and execute the insert statement if it does not exist. When the amount of source data is large and the amount of data in the target table is also large, this method has a slow execution efficiency and cannot complete the data synchronization on time. In addition, the support for the upsert mode by various types of databases is not unified, which increases the difficulty of adapting the CDC data synchronization tool. Generally, it is to query and judge row by row. If it exists, execute the update statement; if it does not exist, execute the insert statement. When the data volume is very large, the synchronization and update efficiency often decreases, and it is impossible to ensure timely update. Higher versions of Mysql, Oracle, etc. have advanced statements for the upsert mode, INSERT... ON DUPLICATE KEY UPDATE..., but this is not supported by other relational databases, and for business use, the version of the business database often needs to be concerned about. And for the update statement, it is necessary to list the updated fields one by one, which is unrealistic in use.

[0021] To achieve data synchronization, the general solution is to implement upsert in the way of query first -> judge -> add / update row by row (row-by-row method), but there are some problems with the row-by-row method:

[0022] 1. Performance issues: Row-by-row querying and updating / inserting can cause a large number of database round-trips, which can be very slow when dealing with a large amount of data. For each row of data, the ORM will execute an independent SQL query, which will significantly increase network latency and server load.

[0023] 2. Transaction management complexity: It is necessary to correctly handle transaction boundaries to ensure that there are no data conflicts or losses between querying and updating / inserting.

[0024] 3. Code complexity: Implementing this logic requires writing more code to handle different situations (existence and non-existence), increasing the possibility of errors. Additional error handling and exception management mechanisms are required.

[0025] 4. Scalability issues: When the amount of data is very large or the number of concurrent users is high, this pattern may not scale well because each operation requires a separate query and update / insert step.

[0026] In view of the problems existing in the traditional one-by-one method, the present invention provides a method, device and system for synchronizing large amounts of data, which utilizes the idea of ​​REPLACE INTO, an advanced usage of MySQL for upsert (when encountering a unique key conflict when inserting data, the original data will be deleted first, and then the new data will be inserted), combined with NIFI as a CDC data synchronization tool in upsert processing, and utilizes the primary key, and adopts the method of first batch deletion and then batch insertion to improve the efficiency of upsert mode data synchronization. The syntax for deletion and addition in this method is the general syntax of the database, which is not restricted by the database type and version, and improves the efficiency of data synchronization.

[0027] Figure 1 FIG. 1 is a flow chart of a method for synchronizing large amounts of data provided by an exemplary embodiment of the present invention. Figure 1 As shown, the method for synchronizing large amounts of data includes:

[0028] S100: Divide the extracted source data into multiple batches.

[0029] The amount of data in each batch is smaller than the amount of source data. In scenarios of data migration, data synchronization, or data import, source data may come from external systems (such as files, APIs, other databases, etc.), and these data need to be extracted and written to the target database.

[0030] In some embodiments, S100 may include: presetting a maximum read data volume for each batch; wherein the maximum read data volume for each batch is the same; and dividing the extracted source data into multiple batches based on the maximum read data volume for each batch.

[0031] For example, when writing data, by setting fetch_size to 1000, the amount of data in each batch executed is controlled to 1000 data. This can prevent too much data from being loaded into the memory at one time, avoid memory overflow, and better control the transaction boundaries of each operation, reducing the pressure on the database.

[0032] In some embodiments, S100 may also include: dividing the extracted source data into multiple batches according to timestamps or time ranges; or dividing the extracted source data into multiple batches according to hash values ​​or partitioning rules of preset fields of the data; or dividing the extracted source data into multiple small files based on batch division of file segmentation, with each small file as a batch; or using a generator to divide the extracted source data into multiple batches one by one.

[0033] For example, the data can be divided into multiple batches according to timestamps or time ranges. Assuming that the data contains a timestamp field, the data can be divided by time intervals (such as daily or hourly), which is applicable to time series data and facilitates analysis by time dimension. Or, according to the hash value or partitioning rule of a certain field (such as ID or category) of the data, the data can be divided into multiple batches. Through the hash function or partitioning rule, the data is evenly distributed into different batches, which is applicable to distributed processing and the data is evenly distributed. It is also possible to use a cursor to gradually read the data, reading a certain number of records each time. A cursor is a database feature that allows data to be read one by one or in batches without loading all the data at once, which is applicable to database scenarios and reduces memory occupancy. It is also possible to split a large file into multiple small files, with each small file as a batch. Through a file splitting tool or script, the large file is split into multiple small files by the number of lines or size, which is applicable to file data and facilitates parallel processing. Or, use the sliding window technique, reading a certain amount of data each time and gradually sliding the window. By setting the window size and sliding step, data is gradually read, for example, reading 1000 records each time with a sliding step of 500 records, which is applicable to streaming data or time series data. It is also possible to divide batches according to the index or primary key of the data and gradually read the data through range queries of the index or primary key. It is also possible to use a generator to generate data batch by batch. A generator is a lazy evaluation method that generates only a part of the data each time and is suitable for processing streaming data or large datasets.

[0034] S200: Obtain the primary key value of each record from each batch based on a preset primary key column.

[0035] Among them, the primary key value is the unique identifier used to distinguish new data from data to be updated.

[0036] The primary key is a constraint in the database table used to uniquely identify each record in the table. The value of the primary key must be unique and cannot be repeated. The value of the primary key cannot be null (NULL). The value of the primary key usually does not change. The primary key is used to ensure data integrity and uniqueness and is also used as the basis for data operations (such as query, update, delete). The primary key column refers to the column (field) defined as the primary key in the table. In practical applications, the primary key usually refers to the column containing the unique identifier value, such as user ID, product code, or order number. After batch processing, when processing the data of each batch, it is necessary to obtain the primary key value of each record from the data according to the set primary key column (preset primary key column) for use as the IN condition value in subsequent batch deletions. This step is crucial for subsequent batch operations because the primary key will be the unique identifier to distinguish new records from records to be updated.

[0037] S300: Delete the existing old data corresponding to the primary key values in the target table in batches.

[0038] For each batch, obtain the primary key values in the batch, and construct an SQL (Structured Query Language) statement based on the primary key values to perform batch primary key value deletion. For example, if the primary key column is ID, and the first batch includes primary key values ID1, ID2, and ID3, and ID1 and ID2 exist in the target table, then delete the data of ID1 and ID2 in the target table. If ID3 does not exist in the target table or ID4 exists, no processing is performed in this batch.

[0039] In some embodiments, S300 may include: constructing an SQL statement for batch deletion based on the primary key values; wherein, the SQL statement for batch deletion includes parameters obtained by concatenating the name of the target table and the primary key values of the corresponding batch; based on the SQL statements for batch deletion constructed for each batch, delete the existing old data corresponding to the primary key values in the target table in batches.

[0040] Construct an SQL statement to delete the old records in the target table whose primary key values already exist in the new data through the SQL statement from a certain table in the database. That is to say, delete the records with the same primary key values in the target table. This can avoid data duplication, ensure data consistency, and provide space for the insertion of new data. Perform batch deletion operations using the primary key, which can significantly reduce the number of database interactions, improve efficiency, and ensure data consistency and integrity through transaction management.

[0041] As a possible implementation, based on the primary key values parsed in S200, for each batch, construct an SQL statement for batch deletion. The SQL format for batch deletion can be:

[0042] DELETE FROM table_name WHERE id IN(list_of_ids);

[0043] Wherein, table_name is the name of the target table, and list_of_ids is the primary key values parsed in S200 concatenated as parameters.

[0044] If deleting one by one, the database needs to perform multiple operations, with low efficiency. Using the IN condition based on the primary key values can delete multiple records at once, with higher efficiency. In addition, deleting through the primary key can ensure that the correct records are deleted and avoid accidental deletion.

[0045] S400: Based on the primary key values, write the new data and updated data into the target table in batches.

[0046] After the SQL statement for deletion is assembled, a batch strategy is adopted to assemble the SQL statement for batch insertion. After deleting the existing old data in the target table, the new data needs to be inserted into the target table in batches. Moreover, according to the structure of the target table, an SQL statement that can insert multiple records at once can be constructed for batch insertion. Through batch insertion and the batch strategy, both the efficiency is improved and the excessive pressure on the database is avoided, and performance problems caused by inserting too much data at once can be avoided.

[0047] In one embodiment, S400 may include: constructing an SQL statement for batch insertion for each batch based on the primary key value and the structure of the target table; wherein, the SQL statement for batch insertion includes the target table name and the primary key value of the corresponding batch; based on the SQL statement for batch insertion constructed for each batch, writing the new data and the updated data into the target table in batches.

[0048] For example, according to the structure of the target table, construct a batch INSERT statement to insert multiple records at once. The SQL format for batch insertion can be:

[0049] INSERT INTO table_name(id,column1,column2,...)VALUES

[0050] (id1,value1_1,value1_2,...),

[0051] (id2,value2_1,value2_2,...), ...;

[0053] Wherein, table_name is the target table name, id is the primary key value, (column1,column2,...) are the column names of the target table, specifying the columns into which the data is to be inserted, and (value1_1,value1_2,...), (value2_1,value2_2,...) are the specific values corresponding to the column names.

[0054] In one embodiment, the SQL statement includes an INSERT statement; constructing an SQL statement for batch insertion for each batch based on the primary key value and the structure of the target table includes: constructing a batch INSERT statement for each batch based on the primary key value and the structure of the target table; wherein, the INSERT statement is used to insert multiple records at once, and each record corresponds to a primary key value; wherein, writing the new data and the updated data into the target table in batches based on the SQL statement for batch insertion constructed for each batch includes: writing the new data and the updated data into the target table in batches based on the batch INSERT statement constructed for each batch.

[0055] INSERT is a type of SQL statement specifically used to insert new records into a database table. INSERT is a subset of SQL statements and belongs to the data manipulation language (DML, Data Manipulation Language). For each batch, all the newly added data and updated data are inserted into the database at once, which reduces the number of SQL statement executions and improves the throughput of database operations. At the same time, the SQL for batch insertion can be constructed before batch deletion, shortening the interval between batch deletion and batch insertion.

[0056] In one embodiment, the method for synchronizing a large amount of data may further include: for a target batch, batch deleting the old data corresponding to the primary key values in the target table as the first operation; where the target batch is any one of multiple batches; for the target batch, batch writing the newly added data and updated data into the target table as the second operation; wrapping the first operation and the second operation in the target batch into the same transaction; and when both the first operation and the second operation in the target batch are successfully completed, committing the transaction.

[0057] To ensure data atomicity and consistency, a transaction rollback control mechanism is enabled. The delete and insert operations for each batch are wrapped in a transaction, and the transaction is committed only when the operations for the entire batch are successfully completed; if an error occurs, the transaction is rolled back to avoid data inconsistency. A reasonable transaction management strategy can further improve the stability and reliability of data processing.

[0058] In one embodiment, after wrapping the first operation and the second operation in the target batch into the same transaction, the method for synchronizing a large amount of data may further include: when an error occurs in the first operation in the target batch, rolling back the transaction; when an error occurs in the second operation in the target batch, rolling back the transaction; and when errors occur in both the first operation and the second operation in the target batch, rolling back the transaction.

[0059] When a transaction includes delete and insert operations, the transaction is committed only when the operations for the entire batch are successfully completed. If any operation fails, the transaction is rolled back to avoid data inconsistency. A reasonable transaction management strategy can further improve the stability and reliability of data processing.

[0060] Figure 2 It is a schematic diagram of the principle of the method for synchronizing a large amount of data provided by an exemplary embodiment of the present invention. Refer to Figure 2In one embodiment, the specific implementation of S300 and S400 can be: after identifying and extracting the primary key, for each batch, first build a batch delete statement, then build a batch add statement, then execute the batch delete statement, and finally execute the batch add statement. The execution of the batch delete statement and the execution of the batch add statement can be controlled by a batch transaction. Alternatively, the batch add SQL is built before the batch delete is executed, which shortens the interval between the batch delete and the batch add.

[0061] The following compares the time complexity of the item-by-item analysis method and the batch delete-then-increase method of the present invention to illustrate that the batch delete-then-increase method of the present invention can improve the overall efficiency.

[0062] For the item-by-item analysis method, the time complexity of item-by-item query is: the time complexity of each query is O(log n), and for m records, the total time complexity is O(m*log n). Time complexity of update or insert: if the record exists, the time complexity of update is O(log n); if the record does not exist, the time complexity of insert is also O(log n). Therefore, whether it is update or insert, the total time complexity is O(m*log n). Overall time complexity: The total time complexity is O(m*log n)+O(m*log n)=O(2*m*log n), which is simplified to O(m*log n).

[0063] As for the method for synchronizing large amounts of data provided by the present invention, the time complexity of batch deletion is: using the primary key index, the time complexity of each deletion is O(log n), where n is the number of records in the table. For batch deletion of m records, the time complexity of batch deletion is O(m*log n). The time complexity of batch addition is: the time complexity of each insertion is also O(log n). If the database can optimize batch insertion, the time complexity may be closer to O(m). Overall time complexity: the total time complexity of batch deletion plus batch addition will be O(m*log n)+O(m), which is approximately O(m*log n) after simplification. When m< <n时,或者在某些优化情况下可以简化为O(m)。

[0064] From the perspective of time complexity, the batch delete-then-add method has better performance. This is because in batch mode, the database can reduce the number of I / O operations and the frequency of writing transaction logs, thereby improving overall efficiency. Batch operations can make better use of transactions and reduce the overhead of transaction start and submission.

[0065] Next, we will continue to test the data synchronization of the two methods to illustrate that the batch delete-then-add method can improve the overall efficiency.

[0066] The process of implementing the upsert mode by using the data synchronization tool NIFI for batch deletion and batch addition is based on the same network environment, using the same NIFI, the same database, and the same amount of data. Line-by-line method: After running smoothly, the execution rate of the write node is observed, and the rate is 60,000 / 5min. The method for data synchronization with large amounts of data provided by the present invention also runs smoothly, and the execution rate of the write node is observed, and the rate is 200,000 / 5min. By testing the data synchronization of the two methods, it can be concluded that the efficiency of data synchronization is significantly improved after optimization. By comparison, the synchronization efficiency of the newly proposed batch delete-then-add upsert strategy and the traditional line-by-line method under the same amount of data is evaluated. Analysis shows that the batch delete-then-add method significantly reduces the time complexity when the amount of data is large, which is manifested as shorter processing time and higher throughput, proving its effectiveness in practical applications.

[0067] Figure 3 FIG. 1 is a schematic diagram of a structure of a device for synchronizing large amounts of data provided by an exemplary embodiment of the present invention. Figure 3 As shown, the device 3 for synchronizing large amounts of data includes: a division module 31, used to divide the extracted source data into multiple batches; wherein the data volume of each batch is smaller than the data volume of the source data; an acquisition module 32, used to obtain the primary key of each record from each batch based on a preset primary key column; wherein the primary key is a unique identifier for distinguishing new data from data to be updated; a deletion module 33, used to delete the old data corresponding to the primary key value in the target table in batches; and a writing module 34, used to write the new data and updated data into the target table in batches based on the primary key value.

[0068] The device for synchronizing large amounts of data can ensure compatibility with a variety of relational databases, and the downstream of data synchronization can obtain updated data in a timely manner. It reduces the memory consumption of the database, significantly improves the efficiency of data synchronization, and ensures the timeliness and accuracy of downstream data. It is of great value in promoting information synchronization and data sharing in a big data environment.

[0069] In one embodiment, the deletion module 33 can be configured as follows: based on the primary key value, a batch deletion SQL statement is constructed for each batch; wherein the batch deletion SQL statement includes a parameter consisting of the name of the target table and the primary key value of the corresponding batch; based on the batch deletion SQL statement constructed for each batch, the old data corresponding to the primary key value in the target table is deleted in batches.

[0070] In one embodiment, the writing module 34 may be configured to: construct SQL statements for batch insertions for each batch based on the primary key values and the structure of the target table; wherein, the SQL statements for batch insertions include the target table name and the primary key values of the corresponding batch; and write the new data and the updated data into the target table in batches based on the SQL statements for batch insertions constructed for each batch.

[0071] In one embodiment, the SQL statements include INSERT statements; the writing module 34 may be configured to: construct batch INSERT statements for each batch based on the primary key values and the structure of the target table; wherein, the INSERT statements are used to insert multiple records at once, and each record corresponds to a primary key value; and the writing module 34 may also be configured to: write the new data and the updated data into the target table in batches based on the batch INSERT statements constructed for each batch.

[0072] In one embodiment, the large - volume data synchronization device 3 may be configured to: for a target batch, delete the old data corresponding to the primary key values in the target table in batches as the first operation; wherein, the target batch is any one of multiple batches; for the target batch, write the new data and the updated data into the target table in batches as the second operation; wrap the first operation and the second operation in the target batch into the same transaction; and commit the transaction when both the first operation and the second operation in the target batch are successfully operated.

[0073] In one embodiment, the large - volume data synchronization device 3 may also be configured to: roll back the transaction when an error occurs in the first operation in the target batch; roll back the transaction when an error occurs in the second operation in the target batch; and roll back the transaction when errors occur in both the first operation and the second operation in the target batch.

[0074] In one embodiment, the partitioning module 31 may be configured to: preset the maximum amount of data read for each batch; wherein, the maximum amount of data read for each batch is the same; and divide the extracted source data into multiple batches based on the maximum amount of data read for each batch.

[0075] In one embodiment, the partitioning module 31 may also be configured to: divide the extracted source data into multiple batches according to the timestamp or time range; or divide the extracted source data into multiple batches according to the hash value or partitioning rules of the preset fields of the data; or based on batch partitioning by file splitting, split the extracted source data into multiple small files, and each small file is used as a batch; or use a generator to divide the extracted source data into multiple batches one by one.

[0076] According to another aspect of the present invention, there is provided a system for large data volume data synchronization, including: a relational database; wherein, the relational database includes a target table; a device for large data volume data synchronization as described in the above embodiment, and the device for large data volume data synchronization is communicatively connected to the relational database.

[0077] An embodiment of the present invention provides a device for large data volume data synchronization. The device embodiment can be implemented by software, or by hardware or a combination of software and hardware. In terms of the hardware level, in addition to the CPU, memory, network interface, and non-volatile memory, the device where the device is located in the embodiment usually may also include other hardware, such as a forwarding chip responsible for processing packets, and so on. Taking the software implementation as an example, as a logically defined device, it is formed by the CPU of the device where it is located reading the corresponding computer program instructions in the non-volatile memory into the memory and running them.

[0078] According to another aspect of the present invention, there is provided a computer-readable storage medium storing a computer program for executing the method for large data volume data synchronization in any of the above embodiments.

[0079] In addition to the above methods and devices, an embodiment of the present invention may also be a computer program product, which includes computer program instructions that, when run by a processor, cause the processor to execute the steps in the method for large data volume data synchronization according to various embodiments of the present invention described above.

[0080] According to another aspect of the present invention, there is provided an electronic device, which includes: a processor; a memory for storing processor-executable instructions; the processor for executing the method for large data volume data synchronization in any of the above embodiments.

[0081] In addition, an embodiment of the present invention may also be a computer-readable storage medium having computer program instructions stored thereon that, when run by a processor, cause the processor to execute the steps in the method for large data volume data synchronization according to various embodiments of the present invention described above.

[0082] The above are only the preferred embodiments of the present invention and are not intended to limit the present invention. Any modifications, equivalent replacements, improvements, etc. made within the spirit and principles of the present invention shall be included within the scope of protection of the present invention.

Claims

1. A method for synchronizing large amounts of data, characterized in that, Including: Dividing the extracted source data into multiple batches; wherein, the data volume of each batch is less than the data volume of the source data; Based on a preset primary key column, obtaining the primary key value of each record from each batch; wherein, the primary key value is a unique identifier for distinguishing new data from data to be updated; Deleting the old data corresponding to the primary key value in the target table batch by batch; Based on the primary key value, writing the new data and updated data into the target table batch by batch.

2. The method for synchronizing large amounts of data according to claim 1, wherein Deleting the old data corresponding to the primary key value in the target table batch by batch includes: Based on the primary key value, constructing a batch deletion SQL statement for each batch; wherein, the batch deletion SQL statement includes parameters formed by concatenating the name of the target table and the primary key value of the corresponding batch; Based on the batch deletion SQL statement constructed for each batch, deleting the old data corresponding to the primary key value in the target table batch by batch.

3. The method for synchronizing large amounts of data according to claim 1, wherein Based on the primary key value, writing the new data and updated data into the target table batch by batch includes: Based on the primary key value and the structure of the target table, constructing a batch insert SQL statement for each batch; wherein, the batch insert SQL statement includes the target table name and the primary key value of the corresponding batch; Based on the batch insert SQL statement constructed for each batch, writing the new data and updated data into the target table batch by batch.

4. The method for synchronizing large amounts of data according to claim 3, characterized in that The SQL statement includes an INSERT statement; based on the primary key value and the structure of the target table, constructing a batch insert SQL statement for each batch includes: Based on the primary key value and the structure of the target table, constructing a batch insert INSERT statement for each batch; wherein, the INSERT statement is used to insert multiple records at one time, and each record corresponds to a primary key value; Wherein, based on the batch insert SQL statement constructed for each batch, writing the new data and updated data into the target table batch by batch includes: Based on the batch insert INSERT statement constructed for each batch, writing the new data and updated data into the target table batch by batch.

5. The method for synchronizing a large amount of data according to claim 1, characterized in that The method for large data volume data synchronization further includes: For a target batch, deleting the old data corresponding to the primary key value in the target table in batches as a first operation; wherein, the target batch is any one of the multiple batches; For the target batch, writing the new data and updated data into the target table in batches as a second operation; Wrapping the first operation and the second operation in the target batch into the same transaction; When both the first operation and the second operation in the target batch are successfully operated, committing the transaction.

6. The method for synchronizing large amounts of data according to claim 5, wherein After wrapping the first operation and the second operation in the target batch into the same transaction, the method for large data volume data synchronization further includes: When an error occurs in the first operation in the target batch, rolling back the transaction; When an error occurs in the second operation in the target batch, rolling back the transaction; When errors occur in both the first operation and the second operation in the target batch, rolling back the transaction.

7. The method for synchronizing large amounts of data according to claim 1, characterized in that Dividing the extracted source data into multiple batches includes: Presetting the maximum read data volume of each batch; wherein, the maximum read data volume of each batch is the same; The extracted source data is divided into multiple batches based on the maximum amount of data read per batch.

8. The method for synchronizing a large amount of data according to claim 1, wherein The extracted source data is divided into multiple batches, including: dividing the extracted source data into multiple batches according to a timestamp or a time range; or dividing the extracted source data into multiple batches according to the hash value or partitioning rule of a preset field of the data; or based on batch division by file splitting, splitting the extracted source data into multiple small files, with each small file as a batch; or using a generator to divide the extracted source data into multiple batches one by one.

9. A device for large - volume data synchronization, characterized in that, including: a partitioning module for dividing the extracted source data into multiple batches; wherein the amount of data in each batch is less than the amount of data of the source data; an acquisition module for acquiring the primary key of each record from each batch based on a preset primary key column; wherein the primary key is a unique identifier for distinguishing new data from data to be updated; a deletion module for batch deleting old data with corresponding primary key values in the target table; a writing module for batch writing new data and updated data into the target table based on the primary key value.

10. A system for large - volume data synchronization, characterized in that, including: a relational database; wherein the relational database includes a target table; The device for synchronizing a large amount of data as described in claim 9 above, and the device for synchronizing a large amount of data is communicatively connected to the relational database.

Images