ETL two-place data synchronization method based on asynchronous file transmission

Abstract

The invention is suitable for the technical field of data synchronization, and provides an ETL two-place data synchronization method based on file asynchronous transmission, which comprises the following steps: step 1, local data synchronization and file generation: realizing remote data file analysis and storage, local pipelined database data direct storage and local pipelined database data landing data file; and 2, asynchronously transmitting the data file to a different place to realize transmission of the locally generated data file to the different place. According to the method, asynchronous separation of the data file transmission step and the data synchronization process is realized, and the strategy remarkably improves the overall synchronization efficiency. Meanwhile, the method supports retransmission after bandwidth flash of the networks in the two places, and data synchronization is not affected. In addition, the method avoids complexity and potential safety risks of cross-place direct database connection, and realizes safe and efficient synchronization of data between two places.

Description

Technical Field

[0001] The present invention belongs to the technical field of data synchronization, and in particular relates to an ETL two-site data synchronization method based on file asynchronous transmission. Background Art

[0002] With the rapid development of the financial industry, the scale of enterprises continues to expand, and the amount of business data continues to surge. In order to improve market competitiveness and ensure business continuity, the remote active-active deployment of application systems has become the norm. Remote data synchronization of large amounts of data has gradually evolved into one of the key requirements for enterprise data management.

[0003] There are currently two commonly used synchronization methods. One is a CDC-like method, which is based on the synchronization of log files. This synchronization method is mostly suitable for scenarios with small data volumes, high timeliness requirements, and no need for additional data processing. The other is an ETL synchronization method based on timestamps, which is suitable for scenarios with large data volumes, low timeliness requirements, and the need for additional data processing. In application systems deployed in two locations, facing the need for large-scale data synchronization, most companies tend to use an ETL synchronization method based on timestamps, and most of them use ETL to directly connect to the database for cross-location data synchronization.

[0004] However, for application systems deployed in two locations, most companies require that the application systems in each location can run independently. In addition, the network bandwidth between the two locations is not stable, and flash interruptions are inevitable. Since the timestamp-based ETL synchronization uses a cross-location direct database connection method, it has high requirements for the network bandwidth between the two locations and cannot well support the independent operation of the application system in a single location. For this reason, the present invention proposes an ETL two-location data synchronization method based on file asynchronous transmission. Summary of the invention

[0005] The purpose of the present invention is to provide an ETL two-site data synchronization method based on file asynchronous transmission, aiming to solve the problems raised in the above background technology.

[0006] To achieve the above object, the present invention provides the following technical solutions:

[0007] An ETL two-site data synchronization method based on file asynchronous transmission includes the following steps:

[0008] Step 1: Synchronize local data and generate files, implement remote data file parsing and storage, direct storage of local flow database data, and local flow database data landing data files;

[0009] Step 2: Asynchronously transfer the data files to a remote location to realize the transfer of the locally generated data files to a remote location.

[0010] Furthermore, the step 1 specifically includes the following steps:

[0011] Step 1: Start the synchronization program;

[0012] Step 2: Initialize the current synchronization status, local synchronization time interval, and remote data file synchronization record;

[0013] Step 3: Determine the synchronization status. If the synchronization status is N, end this task. If the synchronization status is Y, continue with the subsequent process.

[0014] Step 4: local data processing and file generation;

[0015] Step 5: Process and store remote data files;

[0016] Step 6: Update the synchronization status, local synchronization time interval and remote data file synchronization record;

[0017] Step 7: Loop into step 2 until the state changes to N, and the process ends.

[0018] Furthermore, the specific steps of step 4 are as follows:

[0019] Step 4.1, query the local flow database data and directly insert it into the summary database;

[0020] Step 4.2, write the local flow database data into the data file;

[0021] Step 4.3: The data file is compressed and encrypted in turn to generate an MD5 file corresponding to the ciphertext file.

[0022] Furthermore, the file name includes a time interval and a key identifier of the data in the file.

[0023] Furthermore, the format of the time interval is 14 pure digits of year, month, day, hour, minute and second; the format of the key identifier is a 1-digit number.

[0024] Furthermore, the specific process of the encryption process is as follows:

[0025] The encryption key is dynamically selected based on the time interval and month of the current compressed data; the key corresponding to key identifier 1 is selected for odd-numbered months, and the key identifier in the file name is assigned a value of 1; the key corresponding to key identifier 0 is selected for even-numbered months, and the key identifier in the file name is assigned a value of 0.

[0026] Furthermore, the specific steps of step 5 are as follows:

[0027] Step 5.1, verify the integrity and continuity of the remote data file. After the verification passes, decrypt and decompress the data file to obtain the data file;

[0028] Step 5.2: Parse the data file and insert the data into the local summary database.

[0029] Furthermore, the continuity check is performed through the time interval in the file name.

[0030] Furthermore, the step 2 specifically includes the following steps:

[0031] Step 1, start the data transfer program;

[0032] Step 2: Initialize the current synchronization status and data file synchronization record;

[0033] Step 3: Determine the synchronization status. If the synchronization status is N, end this task. If the synchronization status is Y, continue with the subsequent process.

[0034] Step 4: Transfer local data files to a remote location;

[0035] Step 5: Transfer the MD5 file corresponding to the data file to a remote location;

[0036] Step 6: Update the synchronization status and data file synchronization record;

[0037] Step 7: Loop into step 2 until the state changes to N, and the process ends.

[0038] Compared with the prior art, the present invention has the following beneficial effects:

[0039] This method realizes the asynchronous separation of the data file transfer step and the data synchronization process. This strategy significantly improves the overall synchronization efficiency. At the same time, this method supports retransmission after the network bandwidth between the two locations is disconnected, without affecting data synchronization. In addition, this method avoids the complexity and potential security risks of directly connecting to databases across locations, and realizes secure and efficient data synchronization between the two locations. BRIEF DESCRIPTION OF THE DRAWINGS

[0040] Figure 1 This is a scene diagram of the present invention.

[0041] Figure 2 Flow chart of step 1 in the present invention.

[0042] Figure 3 It is a flow chart of step 2 in the present invention. DETAILED DESCRIPTION

[0043] In order to make the purpose, technical solution and advantages of the present invention more clearly understood, the present invention is further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present invention and are not used to limit the present invention.

[0044] The specific implementation of the present invention is described in detail below in conjunction with specific embodiments.

[0045] The business scenario is as follows: Multiple flow databases and one summary database are deployed in two locations. The data of the local flow database is directly inserted into the local summary database through ETL, and the data of the remote flow database is parsed in the form of files through ETL and inserted into the local summary database. Ensure that all summary databases store the full data of the flow databases in both locations.

[0046] An embodiment of the present invention provides an ETL two-site data synchronization method based on file asynchronous transmission, comprising the following steps:

[0047] Step 1: Synchronize local data and generate files, implement remote data file parsing and storage, direct storage of local flow database data, and local flow database data landing data files;

[0048] like Figure 2 As shown, the step 1 specifically includes the following steps:

[0049] Step 1: Start the synchronization program;

[0050] Step 2: Initialize the current synchronization status, local synchronization time interval, and remote data file synchronization record;

[0051] Step 3: Determine the synchronization status. If the synchronization status is N, end this task. If the synchronization status is Y, continue with the subsequent process.

[0052] Step 4: local data processing and file generation; the specific steps of step 4 are as follows:

[0053] Step 4.1, query the local flow database data and directly insert it into the summary database;

[0054] Step 4.2, write the local flow database data into a data file; the file name contains the time interval of the data in the file, in the format of 14 pure digits of year, month, day, hour, minute, and second; the file name contains a key identifier, in the format of a 1-digit number, and supports dynamic key replacement without stopping business to ensure secure transmission of data files;

[0055] Step 4.3, the data file is compressed and encrypted in sequence to generate an MD5 file corresponding to the ciphertext file; the specific process of the encryption process is: dynamically select the key used for encryption according to the time interval and month of the current compressed data; select the key corresponding to key identifier 1 for odd-numbered months, and assign 1 to the key identifier in the file name; select the key corresponding to key identifier 0 for even-numbered months, and assign 0 to the key identifier in the file name.

[0056] Step 5: Process and store remote data files; the specific steps of step 5 are as follows:

[0057] Step 5.1, verify the integrity and continuity of the remote data file (perform the continuity check through the time interval in the file name to ensure the continuity of data file parsing and avoid disorder). After the check passes, decrypt and decompress the data file to obtain the data file;

[0058] Step 5.2, parse the data file and insert the data into the local summary database;

[0059] Step 6: Update the synchronization status, local synchronization time interval and remote data file synchronization record;

[0060] Step 7: Loop into step 2 until the state changes to N, and the process ends.

[0061] Step 2: Asynchronously transmit the data files to a remote location to realize the transmission of the locally generated data files to the remote location;

[0062] like Figure 3 As shown, the step 2 specifically includes the following steps:

[0063] Step 1, start the data transfer program;

[0064] Step 2: Initialize the current synchronization status and data file synchronization record;

[0065] Step 3: Determine the synchronization status. If the synchronization status is N, end this task. If the synchronization status is Y, continue with the subsequent process.

[0066] Step 4: Transfer local data files to a remote location;

[0067] Step 5: Transfer the MD5 file corresponding to the data file to a remote location;

[0068] Step 6: Update the synchronization status and data file synchronization record;

[0069] Step 7: Loop into step 2 until the state changes to N, and the process ends.

[0070] In the embodiment of the present invention, the method separates the data file transmission step from data synchronization and performs asynchronous processing to improve the overall synchronization efficiency, and supports retransmission after a sudden loss of network bandwidth between the two locations without affecting the data synchronization of step one.

[0071] The above are only preferred embodiments of the present invention. It should be pointed out that, for those skilled in the art, several modifications and improvements can be made without departing from the concept of the present invention. These should also be regarded as the protection scope of the present invention. These will not affect the effect of the implementation of the present invention and the practicality of the patent.

Claims

1. An ETL two-site data synchronization method based on file asynchronous transmission, characterized in that: The following steps are involved: Step 1: Synchronize local data and generate files, implement remote data file parsing and storage, direct storage of local flow database data, and local flow database data landing data files; Step 2: Asynchronously transfer the data files to a remote location to realize the transfer of the locally generated data files to a remote location.

2. The ETL two-site data synchronization method based on file asynchronous transmission according to claim 1 is characterized in that: The step 1 specifically includes the following steps: Step 1: Start the synchronization program; Step 2: Initialize the current synchronization status, local synchronization time interval, and remote data file synchronization record; Step 3: Determine the synchronization status. If the synchronization status is N, end this task. If the synchronization status is Y, continue with the subsequent process. Step 4: local data processing and file generation; Step 5: Process and store remote data files; Step 6: Update the synchronization status, local synchronization time interval and remote data file synchronization record; Step 7: Loop into step 2 until the state changes to N, and the process ends.

3. The ETL two-site data synchronization method based on file asynchronous transmission according to claim 2 is characterized in that: The specific steps of step 4 are as follows: Step 4.1, query the local flow database data and directly insert it into the summary database; Step 4.2, write the local flow database data into the data file; Step 4.3: The data file is compressed and encrypted in turn to generate an MD5 file corresponding to the ciphertext file.

4. The ETL two-site data synchronization method based on file asynchronous transmission according to claim 3 is characterized in that: The file name includes the time interval and key identifier of the data in the file.

5. The ETL two-site data synchronization method based on file asynchronous transmission according to claim 4 is characterized in that: The format of the time interval is 14 pure digits of year, month, day, hour, minute and second; the format of the key identifier is a 1-digit number.

6. The ETL two-site data synchronization method based on file asynchronous transmission according to claim 5 is characterized in that: The specific process of the encryption process is as follows: The encryption key is dynamically selected based on the time interval and month of the current compressed data; the key corresponding to key identifier 1 is selected for odd-numbered months, and the key identifier in the file name is assigned a value of 1; the key corresponding to key identifier 0 is selected for even-numbered months, and the key identifier in the file name is assigned a value of 0.

7. The ETL two-site data synchronization method based on file asynchronous transmission according to claim 4 is characterized in that: The specific steps of step 5 are as follows: Step 5.1, verify the integrity and continuity of the remote data file. After the verification passes, decrypt and decompress the data file to obtain the data file; Step 5.2: Parse the data file and insert the data into the local summary database.

8. The ETL two-site data synchronization method based on file asynchronous transmission according to claim 7, characterized in that: The continuity check is performed through the time interval in the file name.

9. The ETL two-site data synchronization method based on file asynchronous transmission according to claim 1, characterized in that: The step 2 specifically includes the following steps: Step 1, start the data transfer program; Step 2: Initialize the current synchronization status and data file synchronization record; Step 3: Determine the synchronization status. If the synchronization status is N, end this task. If the synchronization status is Y, continue with the subsequent process. Step 4: Transfer local data files to a remote location; Step 5: Transfer the MD5 file corresponding to the data file to a remote location; Step 6: Update the synchronization status and data file synchronization record; Step 7: Loop into step 2 until the state changes to N, and the process ends.

Images