CN117194558A - Full-data synchronization method, device, equipment and medium - Google Patents

Abstract

The embodiments of the present application disclose a full data synchronization method, device, equipment and medium. Including, obtaining the full data synchronization request sent by the third-party system; determining the location of the full data file based on the full data request, and pulling the full data file to the local sftp server; dividing the full data file in the local sftp server into multiple data unit, compare each data unit with the historical full data file, and save the full data file to different data files based on the comparison results; generate different sql statements for different data files, and enter the different sql statements. Database processing to complete full data synchronization. Solve the problem of data redundancy through the above methods.

Description

A full data synchronization method, device, equipment and medium

Technical field

The present application relates to the field of data processing technology, and in particular to a full data synchronization method, device, equipment and medium.

Background technique

The full amount of data refers to all the data in the database system that currently needs to be migrated. With the promotion and popularization of the Internet and big data technology, data warehouse has gradually become mainstream. Since the data warehouse has a large storage capacity and can process structured data, the data in the database can be synchronized to the data warehouse for processing.

During the data migration process, the business system needs to synchronize third-party system data, such as order data, user data, enterprise data, and transaction flow. But third-party systems only provide full data. Due to the large amount of data, it is difficult to ensure accurate migration of large amounts of data, resulting in problems such as data redundancy.

Contents of the invention

Embodiments of the present application provide a full data synchronization method, device, equipment and medium to solve the following technical problem: due to excessive data volume, it is difficult to ensure accurate migration of a large amount of data, resulting in problems such as data redundancy.

The embodiments of this application adopt the following technical solutions:

The embodiment of the present application provides a full data synchronization method. Including, obtaining the full data synchronization request sent by the third-party system; determining the location of the full data file based on the full data request, and pulling the full data file to the local sftp server; dividing the full data file in the local sftp server into multiple data unit, compare each data unit with the historical full data file, and save the full data file to different data files based on the comparison results; generate different sql statements for different data files, and enter the different sql statements. Database processing to complete full data synchronization.

The file transfer protocol in the embodiment of this application is suitable for large-scale transmission problems. When all data is synchronized, using files as a carrier can ensure data transmission efficiency. Secondly, the embodiment of the present application divides the full data file in the local sftp server into multiple data units, and compares the multiple data units with the historical full data files to determine whether they are the same as or different from the historical full data files. data. The full data file is parsed into multiple data files such as new, modified, and deleted data files and finally converted into SQL statements. Different data are saved differently to solve the problem of full data redundancy.

In one implementation of this application, after pulling the full data file to the local sftp server, the method also includes: determining the push frequency corresponding to the full data file; setting the local storage directory for the full file based on the push frequency; The full data files are stored in different local storage directories, and the full data files in the local storage directories are backed up regularly.

In an implementation manner of the present application, before dividing the full data file in the local sftp server into multiple data units, the method further includes: determining the full data file if the full data file does not exist in the local sftp server. To add new data files; generate corresponding sql statements for the new data files, and store different sql statements into the database for processing.

In one implementation of this application, the full data file in the local sftp server is divided into multiple data units, each data unit is compared with the historical full data file, and the full data file is saved based on the comparison result. to different data files, specifically including: when the full data file exists in the local sftp server, divide the full data file into multiple data units in behavioral units, and uniquely identify the multiple data units; Multiple data units are compared with multiple data units corresponding to the historical full data file, so that the full data file is saved to different data files based on the comparison results.

In an implementation manner of the present application, multiple data units are compared with multiple data units corresponding to the historical full data files, so as to save the full data files to different data files based on the comparison results, which specifically includes: When there is no full data file in the historical full data, the data corresponding to the full data file is stored in the new data file; when there is a full data file in the historical full data and the full data file is modified, an error will occur. The modified full data file is stored in the modified data file; the data that exists in the historical full data but does not exist in the full data file is stored in the deleted data file; the data that exists in both the historical full data and the full data file is stored in unchanged data file.

In an implementation method of this application, different sql statements are generated for different data files, and the different sql statements are stored in the database for processing to complete full data synchronization, which specifically includes: generating insert sql statements for new data files; And the update sql statement will be generated by modifying the data file; and the delete statement will be generated by deleting the data file.

In one implementation of this application, the location of the full data file is determined based on the full data request, and the full data file is pulled to the local sftp server. This specifically includes: logging into the third-party system sftp through a preset user name and a preset password. Server; based on the full data request, determine the location of the full data file in the third-party system sftp server; pull the full data file to the local sftp server.

Embodiments of the present application provide a full data synchronization device, including: a full data synchronization request acquisition unit, which acquires a full data synchronization request sent by a third-party system; a full data file acquisition unit, which determines the location of the full data file based on the full data request, and Pull the full data file to the local sftp server; the full data comparison unit divides the full data file in the local sftp server into multiple data units, and compares each data unit with the historical full data file. Save all data files to different data files for the results; the SQL statement generation unit generates different SQL statements for different data files, and stores the different SQL statements into the database for processing to complete full data synchronization.

The embodiment of the present application provides a full data synchronization device, including: at least one processor; and,

A memory communicatively connected to at least one processor; wherein the memory stores instructions that can be executed by at least one processor, and the instructions are executed by at least one processor to enable at least one processor to: obtain full data synchronization sent by a third-party system Request; determine the location of the full data file based on the full data request, and pull the full data file to the local sftp server; divide the full data file in the local sftp server into multiple data units, and compare each data unit with the historical full data Compare the files to save all data files to different data files based on the comparison results; generate different sql statements for different data files, and store the different sql statements into the database for complete data synchronization.

A non-volatile computer storage medium provided by an embodiment of the present application stores computer executable instructions, and the computer executable instructions are set to: obtain a full data synchronization request sent by a third-party system; determine a full data file based on the full data request location, and pull the full data file to the local sftp server; divide the full data file in the local sftp server into multiple data units, compare each data unit with the historical full data file, and compare the data files based on the comparison results. The full data files are saved to different data files; different SQL statements are generated for different data files, and the different SQL statements are stored in the database for complete data synchronization.

At least one of the above technical solutions adopted in the embodiments of this application can achieve the following beneficial effects: The file transfer protocol in the embodiments of this application is suitable for large-scale transmission problems. When all data is synchronized, using files as a carrier can ensure data transmission efficiency. Secondly, the embodiment of the present application divides the full data file in the local sftp server into multiple data units, and compares the multiple data units with the historical full data files to determine whether they are the same as or different from the historical full data files. data. The full data file is parsed into multiple data files such as new, modified, and deleted data files and finally converted into SQL statements. Different data are saved differently to solve the problem of full data redundancy.

Description of the drawings

In order to explain the embodiments of the present application or the technical solutions in the prior art more clearly, the drawings needed to be used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings in the following description are only These are some of the embodiments recorded in this application. For those of ordinary skill in the art, other drawings can be obtained based on these drawings without exerting any creative effort. Attached

In the picture:

Figure 1 is a flow chart of a full data synchronization method provided by an embodiment of the present application;

Figure 2 is a block diagram of a full data synchronization device provided by an embodiment of the present application;

Figure 3 is a schematic structural diagram of a full data synchronization device provided by an embodiment of the present application;

Figure 4 is a schematic structural diagram of a full data synchronization device provided by an embodiment of the present application.

Detailed ways

Embodiments of the present application provide a full data synchronization method, device, equipment and medium.

In order to enable those in the technical field to better understand the technical solutions in the present application, the technical solutions in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. Obviously, the described The embodiments are only some of the embodiments of this application, not all of them. Based on the embodiments of this specification, all other embodiments obtained by those of ordinary skill in the art without creative efforts should fall within the scope of protection of this application.

The full amount of data refers to all the data in the database system that currently needs to be migrated. With the promotion and popularization of the Internet and big data technology, data warehouse has gradually become mainstream. Since the data warehouse has a large storage capacity and can process structured data, the data in the database can be synchronized to the data warehouse for processing.

During the data migration process, the business system needs to synchronize third-party system data, such as order data, user data, enterprise data, and transaction flow. But third-party systems only provide full data. Due to the large amount of data, it is difficult to ensure accurate migration of large amounts of data, resulting in problems such as data redundancy.

In order to solve the above problems, embodiments of the present application provide a full data synchronization method, device, equipment and medium. The file transfer protocol is suitable for large transmission problems. When all data is synchronized, using files as a carrier can ensure data transmission efficiency. Secondly, the embodiment of the present application divides the full data file in the local sftp server into multiple data units, and compares the multiple data units with the historical full data files to determine whether they are the same as or different from the historical full data files. data. The full data file is parsed into multiple data files such as new, modified, and deleted data files and finally converted into SQL statements. Different data are saved differently to solve the problem of full data redundancy.

The technical solutions proposed in the embodiments of the present application will be described in detail below through the accompanying drawings.

Figure 1 is a flow chart of a full data synchronization method provided by an embodiment of the present application. As shown in Figure 1, the full data synchronization method includes the following steps:

S101. Obtain the full data synchronization request sent by the third-party system.

In one embodiment of this application, the third-party system sends a full data synchronization http request to the business system. At this time, the full data is not actually sent to the business system, but the location of the full data, that is, the location of the file server where the full data file is located.

Specifically, the business system receives a full data synchronization request sent by the third-party system, and the request includes the location of the file of the full data currently to be synchronized. The business system can determine the location of the current full data based on the received full data synchronization request.

S102. Determine the location of the full data file based on the full data request, and pull the full data file to the local sftp server.

In one embodiment of this application, the third-party system sftp server is logged in through a preset user name and a preset password. Based on the full data request, the location of the full data file is determined in the third-party system sftp server. Pull the full data file to the local sftp server.

Specifically, after the third-party system sends a full data synchronization request to the business system, it will attach the location of the full data file. The business system logs in to the third-party system sftp server through the user name and password notified in advance, and pulls the full data file to the local sftp server.

In one embodiment of the present application, the push frequency corresponding to the full data file is determined, and a local storage directory for the full file is set based on the push frequency. Store the full data files in different local storage directories based on push frequency, and back up the full data files in the local storage directories regularly.

Specifically, the local storage directory of the full files is preset according to the frequency of pushing the full files. For example, if the full data files are pushed every day, the date will be used as the folder to store the daily full data files, and the files will be backed up regularly to ensure that the historical full data files can be checked.

S103. Divide the full data file in the local sftp server into multiple data units, compare each data unit with the historical full data file, and save the full data file to different data files based on the comparison results.

In one embodiment of the present application, when the full data file does not exist in the local sftp server, the full data file is determined to be a new data file. The new data files will generate corresponding sql statements, and different sql statements will be stored in the database for processing.

Specifically, the third-party full data file does not exist in the local sftp server, which means that the full data has not been synchronized with the business system, and the full data can be imported into the business system. First, convert the full data files of the third-party system into the business system's own data files. Data import files are divided into four categories: new data files, modified data files, deleted data files and unchanged data files. When the third-party full data import file does not exist in the local sftp server, the converted data import files are all new data files. Generate insert sql statements from the newly added data files, and store the sql statements into the database for processing.

In one embodiment of the present application, when the full data file exists in the local sftp server, the full data file is divided into multiple data units in units of rows, and each of the multiple data units is uniquely identified. Compare multiple data units with multiple data units corresponding to the historical full data file, so as to save the full data file to different data files based on the comparison results.

In one embodiment of the present application, when there is no full data file in the historical full data, the data corresponding to the full data file is stored in the new data file. When a full data file exists in the historical full data and the full data file is modified, the modified full data file is stored in the modified data file. The data that exists in the historical full data but does not exist in the full data file is stored in the deleted data file. Store the data that exists in both the historical full data and the full data file into the unchanged data file.

Specifically, when the third-party full data file exists in the local sftp server, the third-party full data file is compared with the historical third-party full data file, and four parts of the content are found: adding partial data, deleting partial data, and modifying partial data. and unchanged partial data.

There is a prerequisite for data comparison, which is that the data file should be used as a data unit in behavioral units and have a unique data identifier. The current third-party full data will be compared line by line with the historical third-party full data. If there is no full data file in the historical full data, the data corresponding to the full data file will be stored in the new data file. When a full data file exists in the historical full data and the full data file is modified, the modified full data file is stored in the modified data file. The data that exists in the historical full data but does not exist in the full data file is stored in the deleted data file. Store the data that exists in both the historical full data and the full data file into the unchanged data file.

S104. Generate different SQL statements for different data files, and store the different SQL statements into the database for complete data synchronization.

In one embodiment of the present application, an insert sql statement is generated for adding a new data file; an update sql statement is generated for modifying a data file; and a delete statement is generated for deleting a data file.

Specifically, adding a new data file generates an insert sql statement; modifying a data file generates an update sql statement; deleting a data file generates a delete statement; data files without changes are not processed. The generated SQL statements are executed and stored in the database, and all data are synchronized.

The file transfer protocol in the embodiment of this application is suitable for large-scale transmission problems. When all data is synchronized, using files as a carrier can ensure data transmission efficiency. Secondly, the embodiment of the present application divides the full data file in the local sftp server into multiple data units, and compares the multiple data units with the historical full data files to determine whether they are the same as or different from the historical full data files. data. The full data file is parsed into multiple data files such as new, modified, and deleted data files and finally converted into SQL statements. Different data are saved differently to solve the problem of full data redundancy.

Figure 2 is a block diagram of a full data synchronization device provided by an embodiment of the present application. As shown in Figure 2, the full data synchronization device includes third-party systems, business systems, databases, third-party sftp file servers, and local sftp file servers. The third-party system sends an HTTP full data synchronization request to inform the full data file location. The third-party system stores all data files on the third-party sftp file server. After receiving the full data synchronization request, the business system pulls the full data file on the third-party sftp file server, and stores the pulled third-party full data file on the local sftp file server. By analyzing the full amount of data on the local sftp file server, new, modified and deleted data files are determined, different files are parsed into SQL statements, the generated SQL statements are executed and stored in the database, and all data are synchronized.

Figure 3 is a schematic structural diagram of a full data synchronization device provided by an embodiment of the present application. As shown in Figure 3, the full data synchronization device 300 includes a full data synchronization request acquisition unit 301, a full data file acquisition unit 302, and a full data comparison unit. 303 and sql statement generation unit 304.

The full data synchronization request acquisition unit 301 obtains the full data synchronization request sent by the third-party system;

The full data file acquisition unit 302 determines the location of the full data file based on the full data request, and pulls the full data file to the local sftp server;

The full data comparison unit 303 divides the full data file in the local sftp server into multiple data units, compares each data unit with the historical full data file, and compares the full data file based on the comparison result. Save to different data files;

The sql statement generation unit 304 generates different sql statements for the different data files, and stores the different sql statements into the database to complete full data synchronization.

Furthermore, the device also includes:

The full data file storage unit determines the push frequency corresponding to the full data file; sets a local storage directory for the full file based on the push frequency; stores the full data file in different local storage directories based on the push frequency, And regularly back up all data files in the local storage directory.

Furthermore, the device also includes:

The new data file processing unit determines that the full data file is a new data file when the full data file does not exist in the local sftp server; and generates the corresponding sql for the new data file. statement, and store the different sql statements into the database for processing.

Furthermore, the device also includes:

The full data file comparison unit, when the full data file exists in the local sftp server, divides the full data file into multiple data units in units of rows, and compares the multiple data units respectively. Perform unique identification annotation; compare the multiple data units with multiple data units corresponding to the historical full data files, so as to save the full data files to different data files based on the comparison results.

Furthermore, the device also includes:

A full data file dividing unit, when the full data file does not exist in the historical full data, stores the data corresponding to the full data file into a new data file; if the full data file exists in the historical full data Full data file, and when the full data file is modified, the modified full data file is stored in the modified data file; the data that exists in the historical full data but does not exist in the full data file is stored in Enter the deleted data file; store the data that exists in both the historical full data and the full data file into an unchanged data file.

Furthermore, the device also includes:

The sql statement generation unit generates insert sql statements for adding new data files; generates update sql statements for modifying data files; and generates delete statements for deleting data files.

Furthermore, the device also includes:

The full data file pulling unit logs in to the third-party system sftp server through a preset user name and a preset password; based on the full data request, determines the location of the full data file in the third-party system sftp server; The full data file is pulled to the local sftp server.

Figure 4 is a schematic structural diagram of a full data synchronization device provided by an embodiment of the present application. As shown in Figure 4, full data synchronization equipment includes:

at least one processor; and,

a memory communicatively connected to the at least one processor; wherein,

The memory stores instructions executable by the at least one processor, the instructions being executed by the at least one processor to enable the at least one processor to:

Obtain the full data synchronization request sent by the third-party system;

Determine the location of the full data file based on the full data request, and pull the full data file to the local sftp server;

Divide the full data file in the local sftp server into multiple data units, compare each data unit with historical full data files, and save the full data files to different data files based on the comparison results;

Different sql statements are generated for the different data files, and the different sql statements are stored in the database to complete full data synchronization.

Embodiments of the present application also provide a non-volatile computer storage medium that stores computer-executable instructions, and the computer-executable instructions are set to:

Obtain the full data synchronization request sent by the third-party system;

Determine the location of the full data file based on the full data request, and pull the full data file to the local sftp server;

Divide the full data file in the local sftp server into multiple data units, compare each data unit with historical full data files, and save the full data files to different data files based on the comparison results;

Different sql statements are generated for the different data files, and the different sql statements are stored in the database to complete full data synchronization.

Each embodiment in this application is described in a progressive manner. The same and similar parts between the various embodiments can be referred to each other. Each embodiment focuses on its differences from other embodiments. In particular, for the device, equipment, and non-volatile computer storage medium embodiments, since they are basically similar to the method embodiments, the descriptions are relatively simple. For relevant details, please refer to the partial description of the method embodiments.

The above has described specific embodiments of the present application. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps recited in the claims can be performed in a different order than in the embodiments and still achieve desired results. Additionally, the processes depicted in the figures do not necessarily require the specific order shown, or sequential order, to achieve desirable results. Multitasking and parallel processing are also possible or may be advantageous in certain implementations.

The above descriptions are only examples of the present application and are not intended to limit the present application. For those skilled in the art, various modifications and changes may be made to the embodiments of the present application. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the embodiments of this application shall be included in the scope of the claims of this application.

Claims (10)

1. A method of full data synchronization, the method comprising:

acquiring a full data synchronization request sent by a third party system;

determining the position of a full-volume data file based on the full-volume data request, and pulling the full-volume data file to a local sftp server;

dividing the full data file in the local sftp server into a plurality of data units, and comparing each data unit with the historical full data file to store the full data file into different data files based on a comparison result;

and generating different sql sentences for the different data files, and carrying out warehousing processing on the different sql sentences to complete full data synchronization.

2. The full-size data synchronization method according to claim 1, wherein after the pulling the full-size data file to the local sftp server, the method further comprises:

determining the pushing frequency corresponding to the full data file;

setting a local storage catalog of the full-quantity file based on the pushing frequency;

and storing the full-volume data files under different local storage directories based on the pushing frequency, and regularly backing up the full-volume data files under the local storage directories.

3. A method of full data synchronization according to claim 1, wherein prior to said dividing said full data file in a local sftp server into a plurality of data units, said method further comprises:

determining that the full data file is a newly added data file under the condition that the full data file does not exist in the local sftp server;

generating corresponding sql sentences from the newly added data file, and carrying out warehousing processing on the different sql sentences.

4. The method for synchronizing full-size data according to claim 1, wherein the dividing the full-size data file in the local sftp server into a plurality of data units, comparing each data unit with a historical full-size data file, and storing the full-size data file to different data files based on the comparison result, specifically comprises:

dividing the full-size data file into a plurality of data units in a row unit under the condition that the full-size data file exists in the local sftp server, and respectively carrying out unique identification marking on the plurality of data units;

and comparing the data units with the data units corresponding to the historical full data file to save the full data file to different data files based on the comparison result.

5. The method for synchronizing full-size data according to claim 4, wherein the comparing the plurality of data units with the plurality of data units corresponding to the historical full-size data file to save the full-size data file to a different data file based on the comparison result comprises:

storing data corresponding to the full data file into a new data file under the condition that the full data file does not exist in the historical full data;

storing the modified full data file into a modified data file when the full data file exists in the historical full data and the full data file is modified;

storing data which exists in the historical full data but does not exist in the full data file into a deleted data file;

and storing the data existing in the historical full data and the full data file into a unchanged data file.

6. The full data synchronization method according to claim 1, wherein the generating different sql statements for the different data files, and performing a warehouse entry process on the different sql statements to complete full data synchronization, specifically includes:

generating an insert sql statement from the newly added data file; and

generating an update sql statement from the modified data file; and

the delete data file is generated into delete statement.

7. The full-scale data synchronization method according to claim 1, wherein determining a full-scale data file location based on the full-scale data request and pulling the full-scale data file to a local sftp server specifically comprises:

logging in a third party system sftp server through a preset user name and a preset password;

determining the position of the full-volume data file in the third-party system sftp server based on the full-volume data request;

and pulling the full data file to a local sftp server.

8. A full-scale data synchronization apparatus, comprising:

the full-volume data synchronization request acquisition unit acquires a full-volume data synchronization request sent by a third party system;

the full-volume data file acquisition unit is used for determining the position of the full-volume data file based on the full-volume data request and pulling the full-volume data file to a local sftp server;

the full data comparison unit divides the full data file in the local sftp server into a plurality of data units, compares each data unit with the historical full data file, and stores the full data file into different data files based on a comparison result;

and the sql statement generating unit generates different sql statements for the different data files, and performs warehousing processing on the different sql statements to complete full data synchronization.

9. A full-volume data synchronization device, comprising:

at least one processor; the method comprises the steps of,

a memory communicatively coupled to the at least one processor; wherein,

the memory stores instructions executable by the at least one processor to enable the at least one processor to:

acquiring a full data synchronization request sent by a third party system;

determining the position of a full-volume data file based on the full-volume data request, and pulling the full-volume data file to a local sftp server;

dividing the full data file in the local sftp server into a plurality of data units, and comparing each data unit with the historical full data file to store the full data file into different data files based on a comparison result;

and generating different sql sentences for the different data files, and carrying out warehousing processing on the different sql sentences to complete full data synchronization.

10. A non-transitory computer storage medium storing computer-executable instructions configured to:

acquiring a full data synchronization request sent by a third party system;

determining the position of a full-volume data file based on the full-volume data request, and pulling the full-volume data file to a local sftp server;

dividing the full data file in the local sftp server into a plurality of data units, and comparing each data unit with the historical full data file to store the full data file into different data files based on a comparison result;

and generating different sql sentences for the different data files, and carrying out warehousing processing on the different sql sentences to complete full data synchronization.