Method and device for data transmission between databases, equipment and storage medium

Abstract

The invention provides a data transmission method and device between databases, equipment and a storage medium. The method comprises the following steps: uploading to-be-transmitted data in a first database to a local server; generating a first temporary table and a first formal table in a second database; storing the to-be-transmitted data in the local server to a first temporary table; naming the first temporary table as a second formal table; and naming the first formal table as a second temporary table, wherein the second temporary table is used for storing to-be-transmitted data transmitted by the local server next time. The data transmission efficiency between databases can be improved.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a method and device, equipment and storage medium for data transmission between databases. Background Art

[0002] In the related art, migrating data from a Massively Parallel Processing (MPP) database to a MySQL database mainly relies on a multi-step process. Figure 1 As shown in the figure, first, the data in the MPP database is exported as a text file (txt), and then the text file is stored on a Network Attached Storage (NAS) device. Subsequently, the LOAD DATA INFILE method of the MySQL database is used to load the data from the NAS into the MySQL database.

[0003] The data transmission method in the prior art has obvious efficiency problems when processing large-scale data sets. Data needs to be transferred from MPP to NAS, and then from NAS to MySQL. It may take more than 5 hours to transfer a large amount of data across three nodes. Moreover, the transmission of a large amount of data across three nodes may also cause the master-slave replication of the MySQL database to be delayed by more than 6 hours, thus triggering an alarm.

[0004] In addition, in the related art, when loading data from NAS to MySQL database, MySQL database needs to synchronize the data to a temporary table first, and then insert the data in the temporary table into the formal table. This process not only generates two copies of binlog log data (one for the temporary table and one for the formal table), but also saves a copy of the data in the master-slave replication environment, resulting in eight copies of data for a single table. The eight copies of data are the table data stored in the formal table by the MySQL master database, the binlog log file data of the formal table stored by the MySQL master database, the table data stored in the temporary table by the MySQL master database, the binlog log file data of the temporary table stored by the MySQL master database, the table data stored in the formal table by the MySQL cluster database, the binlog log file data of the formal table stored by the MySQL cluster database, the table data stored in the temporary table by the MySQL cluster database, and the binlog log file data of the temporary table stored by the MySQL cluster database. The increase in the amount of stored data undoubtedly increases the demand for server disk space. At the same time, in the process of inserting data from the temporary table to the formal table, since the formal table needs to be locked to maintain data consistency, this will temporarily affect the query operation of the business system, further reducing the performance and availability of the MySQL database.

[0005] Therefore, these limitations and deficiencies of the existing technologies indicate that a more efficient, less resource-intensive, and less business-impactful data migration and synchronization method is needed. Summary of the invention

[0006] The present invention provides a method and device, equipment and storage medium for data transmission between databases, which can improve data transmission efficiency and reduce the impact of data transmission on business systems.

[0007] In one aspect, a method for data transmission between databases is provided, comprising: Uploading the data to be transmitted in the first database to the local server; Generate a first temporary table and a first formal table in the second database; Store the data to be transmitted in the local server into a first temporary table; Name the first temporary table as the second formal table; The first formal table is named as a second temporary table, and the second temporary table is used to store the data to be transmitted by the local server next time.

[0008] Optionally, uploading the data to be transmitted in the first database to the local server includes: Setting the data output location of the first database to a local server, where the local server is a server running a scheduling service; Obtain the size of the data to be transmitted in the first database; Exporting the data to be transmitted in the first database to the local server using the export command of the first database; The size of the data to be transmitted in the local server is obtained, and when the size of the data to be transmitted in the local server is not equal to the size of the data to be transmitted in the first database, the data to be transmitted in the first database is re-transmitted to the local server.

[0009] Optionally, before uploading the data to be transmitted to the local server, the method further includes: Get the file size of the data to be transferred; When the file size of the data to be transmitted is greater than a threshold, the file of the data to be transmitted is divided into multiple parts.

[0010] Optionally, when the file size of the data to be transmitted is greater than a threshold, the file of the data to be transmitted is divided into multiple parts, including: Determine the number of files after the data to be transmitted is divided according to the size of the data to be transmitted; Determine the data range corresponding to the multiple files after segmentation according to the total number of rows of the files to be transferred and the number of files after segmentation; The data to be transmitted is divided according to the data range.

[0011] In another aspect, a data transmission device between databases is provided, the device comprising: A data transmission module, used to upload the data to be transmitted in the first database to the local server; and store the data to be transmitted in the local server in the first temporary table; A table generation module, used for generating a first temporary table and a first formal table in a second database; The table naming module is used to name the first temporary table as a second formal table; the first formal table is named as a second temporary table, and the second temporary table is used to store the data to be transmitted by the local server next time.

[0012] Optionally, the data transmission module is used to: Setting the data output location of the first database to a local server, where the local server is a server running a scheduling service; Obtain the size of the data to be transmitted in the first database; Exporting the data to be transmitted in the first database to the local server using the export command of the first database; The size of the data to be transmitted in the local server is obtained, and when the size of the data to be transmitted in the local server is not equal to the size of the data to be transmitted in the first database, the data to be transmitted in the first database is re-transmitted to the local server.

[0013] Optionally, the data transmission module is further used for: Before uploading the data to be transmitted to the local server, obtaining the file size of the data to be transmitted; When the file size of the data to be transmitted is greater than a threshold, the file of the data to be transmitted is divided into multiple parts.

[0014] Optionally, the data transmission module is further used for: Determine the number of files after the data to be transmitted is divided according to the size of the data to be transmitted; Determine the data range corresponding to the multiple files after segmentation according to the total number of rows of the files to be transferred and the number of files after segmentation; The data to be transmitted is divided according to the data range.

[0015] On the other hand, an electronic device is provided, comprising the device for data transmission between databases as described in any one of the above items.

[0016] On the other hand, a computer-readable storage medium is provided, wherein at least one program code is stored in the computer-readable storage medium, and the program code is executed by a processor to implement the method for data transmission between databases as described in any one of the above items.

[0017] The technical solution provided by the embodiments of the present disclosure has the following beneficial effects: In the disclosed embodiment, by transferring the data in the first database to the local server, and then transferring the data to the second server through the local server, the process of transferring the data to the network attached storage is omitted, thereby speeding up the data transmission efficiency and improving the data transmission time. Among them, when the table data volume > 30 million or the txt data file > 10G, the improvement of transmission efficiency is more obvious. Compared with the related art, in the first stage, the data is unloaded from the MPP to the Nas storage service, and the present invention transfers the data from the first database (MPP database) to the local server, and the average time consumption can be saved by more than 2 times. In the second stage, the related art loads the data from Nas to the MySQL database, and the present invention transfers the data from the local server to the second database (MySQL database), and the average time consumption can be saved by more than 10 times. For example, the data of a customer overdue list customer group data table is 49 million. The related art takes 498 minutes to transfer the data from the MPP database to the MySQL database, while the method in the present invention only takes 37 minutes to complete the data transmission.

[0018] In addition, a first formal table and a first temporary table are established. The first formal table stores the data of the business system, and the business system can query data from the formal table. The first temporary table is used to receive the data to be transmitted (the data of the updated business system) transmitted by the local server. After the first temporary table receives the data to be transmitted transmitted by the local server, the first temporary table is renamed as the second formal table by renaming. The business system can query data through the second formal table, and the first formal table is renamed as the second temporary table for receiving the data transmitted by the local server next time. By renaming, the data in the second database is updated. The renaming operation only takes a few seconds or even shorter time. Compared with the use of INSERT statements in related technologies to update the data to be transmitted to the MySQL database, it basically does not affect the data query operation of the business system. In addition, compared with the INSERT statement, the renaming method can also reduce the generation of data files. Compared with the INSERT statement (8 copies of data), renaming only generates 2 copies of data. BRIEF DESCRIPTION OF THE DRAWINGS

[0019] In order to more clearly illustrate the technical solutions in the present invention or the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0020] Figure 1 A flowchart of data transmission between databases provided for related technologies; Figure 2 A flowchart of data transmission between databases provided in an embodiment of the present disclosure; Figure 3 A method flow chart of a method for data transmission between databases provided in an embodiment of the present disclosure; Figure 4 A method flow chart of another method for data transmission between databases provided in an embodiment of the present disclosure.

[0021] Figure 5 A structural block diagram of a data transmission device between databases provided in an embodiment of the present disclosure; Figure 6 A structural block diagram of an electronic device provided in an embodiment of the present disclosure.

[0022] The reference numerals are as follows: 31: Data transmission module; 32: Table generation module; 33: Table naming module; 41: processor; 41: memory. DETAILED DESCRIPTION

[0023] In order to make the purpose, technical solution and advantages of the present invention clearer, the technical solution of the present invention will be clearly and completely described below in conjunction with the drawings of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0024] Figure 1 A flowchart of data transmission between databases provided for related technology.

[0025] Figure 2 A flowchart of data transmission between databases provided by an embodiment of the present disclosure. Compared with the related art, the method adopted by the present invention omits the process of transferring data to the NAS device, thereby greatly improving the data transmission efficiency, and can also reduce the amount of data in the MySQL database and reduce the disk space occupied.

[0026] Figure 3 A method flow chart of a method for data transmission between databases provided in an embodiment of the present disclosure. Figure 3 The method steps include: S11. Upload the data to be transmitted in the first database to the local server.

[0027] In one example, step S11 includes: The first step is to set the data output location of the first database to a local server, where the local server is a server running a scheduling service.

[0028] The second step is to obtain the size of the data to be transmitted in the first database.

[0029] The third step is to use the export command of the first database to export the data to be transmitted in the first database to the local server.

[0030] The fourth step is to obtain the size of the data to be transmitted in the local server. When the size of the data to be transmitted in the local server is not equal to the size of the data to be transmitted in the first database, the data to be transmitted in the first database is re-transmitted to the local server.

[0031] In the embodiment of the present disclosure, the integrity of the data to be transmitted received by the local server is determined by checking the size of the data to be transmitted.

[0032] S12. Generate a first temporary table and a first formal table in the second database.

[0033] S13: Store the data to be transmitted in the local server into a first temporary table.

[0034] S14. Name the first temporary table as the second formal table.

[0035] S15. Name the first formal table as a second temporary table, where the second temporary table is used to store the data to be transmitted by the local server next time.

[0036] In the disclosed embodiment, by transferring the data in the first database to the local server, and then transferring the data to the second server through the local server, the process of transferring the data to the network attached storage is omitted, thereby speeding up the data transmission efficiency and improving the data transmission time. Among them, when the table data volume > 30 million or the txt data file > 10G, the improvement of transmission efficiency is more obvious. Compared with the related art, in the first stage, the data is unloaded from the MPP to the Nas storage service, and the present invention transfers the data from the first database (MPP database) to the local server, and the average time consumption can be saved by more than 2 times. In the second stage, the related art loads the data from Nas to the MySQL database, and the present invention transfers the data from the local server to the second database (MySQL database), and the average time consumption can be saved by more than 10 times. For example, the data of a customer overdue list customer group data table is 49 million. The related art takes 498 minutes to transfer the data from the MPP database to the MySQL database, while the method in the present invention only takes 37 minutes to complete the data transmission.

[0037] In addition, a first formal table and a first temporary table are established. The first formal table stores the data of the business system, and the business system can query data from the formal table. The first temporary table is used to receive the data to be transmitted (the data of the updated business system) transmitted by the local server. After the first temporary table receives the data to be transmitted transmitted by the local server, the first temporary table is renamed as the second formal table by renaming. The business system can query data through the second formal table, and the first formal table is renamed as the second temporary table for receiving the data transmitted by the local server next time. By renaming, the data in the second database is updated. The renaming operation only takes a few seconds or even shorter time. Compared with the use of INSERT statements in related technologies to update the data to be transmitted to the MySQL database, it basically does not affect the data query operation of the business system. In addition, compared with the INSERT statement, the renaming method can also reduce the generation of data files. Compared with the INSERT statement (8 copies of data), renaming only generates 2 copies of data.

[0038] Figure 4 A method flow chart of another method for data transmission between databases provided by an embodiment of the present disclosure. Figure 4 The method steps include: S21. Obtain a file size of the data to be transmitted, and when the file size of the data to be transmitted is greater than a threshold, divide the file of the data to be transmitted into multiple parts.

[0039] In one example, step S22 includes: The first step is to obtain the size of the data to be transmitted.

[0040] Calculate the total number of lines in the file, record it as mpp_cnt, and determine the file range coefficient k = int(mpp_cnt / 5).

[0041] The second step is to determine the number of files after the data to be transmitted is divided according to the size of the data to be transmitted.

[0042] You can pre-set the size of each sub-data after segmentation, so as to determine the number of files after segmentation according to the size of the data to be transferred. For example, when the exported txt data file exceeds 10GB, the file will be divided into five parts (each 2GB) according to the number of lines.

[0043] The third step is to determine the data range corresponding to the multiple files after segmentation according to the total number of rows of the files to be transferred and the number of files after segmentation.

[0044] When the file needs to be divided into 5 parts, the file range coefficient k = int(mpp_cnt / 5), the data range of the first file is: [1, k+1); the data range of the second file is: [k+1, 2k+1); the data range of the third file is: [2k+1, 3k+1); the data range of the fourth file is: [3k+1, 4k+1); the data range of the fifth file is: [4k+1, mpp_cnt).

[0045] Step 4: Split the data to be transmitted according to the data range.

[0046] For example, the NR built-in variable of the AWK command in the Linux system is used to identify the action of stopping the output of each file. After reaching the corresponding number of lines, the current file output is stopped and the output of a new data file is started.

[0047] After the data files are approximately evenly divided, the divided files are synchronized to the temporary table of the MySQL database in multiple times.

[0048] In the embodiment of the present disclosure, the data to be transmitted can be segmented through the above steps.

[0049] S22: Upload the data to be transmitted in the first database to the local server.

[0050] See step S11.

[0051] S23. Generate a first temporary table and a first formal table in the second database.

[0052] S24. Store the data to be transmitted in the local server into a first temporary table.

[0053] S25. Name the first temporary table as the second formal table.

[0054] S26. Name the first formal table as a second temporary table, where the second temporary table is used to store the data to be transmitted by the local server next time.

[0055] In the embodiment of the present disclosure, step S21 is added. When the data to be transmitted is large, the data to be transmitted is divided into parts through step S21, thereby ensuring the transmission efficiency of the large data to be transmitted.

[0056] Figure 5 This is a structural block diagram of a data transmission device between databases provided by an embodiment of the present disclosure. Figure 5 , the device comprises: The data transmission module 31 is used to upload the data to be transmitted in the first database to the local server; and store the data to be transmitted in the local server to the first temporary table; A table generation module 32, used to generate a first temporary table and a first formal table in the second database; The table naming module 33 is used to name the first temporary table as the second formal table; and to name the first formal table as the second temporary table, wherein the second temporary table is used to store the data to be transmitted by the local server next time.

[0057] Optionally, the data transmission module 31 is used to: Setting the data output location of the first database to a local server, where the local server is a server running a scheduling service; Obtain the size of the data to be transmitted in the first database; Exporting the data to be transmitted in the first database to the local server using the export command of the first database; The size of the data to be transmitted in the local server is obtained, and when the size of the data to be transmitted in the local server is not equal to the size of the data to be transmitted in the first database, the data to be transmitted in the first database is re-transmitted to the local server.

[0058] Optionally, the data transmission module 31 is further used for: The file size of the data to be transmitted is obtained, and when the file size of the data to be transmitted is greater than a threshold, the file of the data to be transmitted is divided into multiple parts.

[0059] Optionally, the data transmission module is further used for: Get the size of the data to be transmitted; Determine the number of files after the data to be transmitted is divided according to the size of the data to be transmitted; Determine the data range corresponding to the multiple files after segmentation according to the total number of rows of the files to be transferred and the number of files after segmentation; The data to be transmitted is divided according to the data range.

[0060] Figure 6 This is a structural block diagram of an electronic device provided by an embodiment of the present disclosure. Figure 6 , the electronic device may include a data transmission device between databases. Generally, the electronic device includes: a processor 41 and a memory 42.

[0061] The processor 41 may include one or more processing cores, such as a 4-core processor, an 8-core processor, etc. The processor 42 may be implemented in at least one hardware form of DSP (Digital Signal Processing), FPGA (Field-Programmable Gate Array), and PLA (Programmable Logic Array). The processor 41 may also include a main processor and a coprocessor. The main processor is a processor for processing data in the awake state, also known as a CPU (Central Processing Unit); the coprocessor is a low-power processor for processing data in the standby state.

[0062] The memory 42 may include one or more computer-readable storage media, which may be non-transitory. The memory 42 may also include a high-speed random access memory, and a non-volatile memory, such as one or more disk storage devices, flash memory storage devices. In some embodiments, the non-transitory computer-readable storage medium in the memory 42 is used to store at least one instruction, which is used to be executed by the processor 51 to implement the method for data transmission between databases performed by an electronic device provided in the method embodiment of the present application.

[0063] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or make equivalent replacements for some of the technical features therein. However, these modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present invention.

Claims

1. A method for data transmission between databases, characterized in that: include: Uploading the data to be transmitted in the first database to the local server; Generate a first temporary table and a first formal table in the second database; Store the data to be transmitted in the local server into a first temporary table; Name the first temporary table as the second formal table; The first formal table is named as a second temporary table, and the second temporary table is used to store the data to be transmitted by the local server next time.

2. The method for data transmission between databases according to claim 1, characterized in that: The uploading of the data to be transmitted in the first database to the local server includes: Setting the data output location of the first database to a local server, where the local server is a server running a scheduling service; Obtain the size of the data to be transmitted in the first database; Exporting the data to be transmitted in the first database to the local server using the export command of the first database; The size of the data to be transmitted in the local server is obtained, and when the size of the data to be transmitted in the local server is not equal to the size of the data to be transmitted in the first database, the data to be transmitted in the first database is re-transmitted to the local server.

3. The method for data transmission between databases according to claim 2, characterized in that: Before uploading the data to be transmitted to the local server, the method further includes: The file size of the data to be transmitted is obtained, and when the file size of the data to be transmitted is greater than a threshold, the file of the data to be transmitted is divided into multiple parts.

4. The method for data transmission between databases according to claim 3, characterized in that: Acquiring a file size of the data to be transmitted, and when the file size of the data to be transmitted is greater than a threshold, dividing the file of the data to be transmitted into multiple parts, including: Get the size of the data to be transmitted; Determine the number of files after the data to be transmitted is divided according to the size of the data to be transmitted; Determine the data range corresponding to the multiple files after segmentation according to the total number of rows of the files to be transferred and the number of files after segmentation; The data to be transmitted is divided according to the data range.

5. A data transmission device between databases, characterized in that: The device includes: A data transmission module, used to upload the data to be transmitted in the first database to the local server; and store the data to be transmitted in the local server in the first temporary table; A table generation module, used for generating a first temporary table and a first formal table in a second database; The table naming module is used to name the first temporary table as a second formal table; the first formal table is named as a second temporary table, and the second temporary table is used to store the data to be transmitted by the local server next time.

6. The data transmission device between databases according to claim 5, characterized in that: The data transmission module is used for: Setting the data output location of the first database to a local server, where the local server is a server running a scheduling service; Obtain the size of the data to be transmitted in the first database; Exporting the data to be transmitted in the first database to the local server using the export command of the first database; The size of the data to be transmitted in the local server is obtained, and when the size of the data to be transmitted in the local server is not equal to the size of the data to be transmitted in the first database, the data to be transmitted in the first database is re-transmitted to the local server.

7. The data transmission device between databases according to claim 6, characterized in that: The data transmission module is also used to: The file size of the data to be transmitted is obtained, and when the file size of the data to be transmitted is greater than a threshold, the file of the data to be transmitted is divided into multiple parts.

8. The data transmission device between databases according to claim 7, characterized in that: The data transmission module is also used to: Get the size of the data to be transmitted; Determine the number of files after the data to be transmitted is divided according to the size of the data to be transmitted; Determine the data range corresponding to the multiple files after segmentation according to the total number of rows of the files to be transferred and the number of files after segmentation; The data to be transmitted is divided according to the data range.

9. An electronic device, characterized in that: The electronic device comprises the device for data transmission between databases as described in any one of claims 5 to 8.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores at least one program code, and the program code is executed by a processor to implement the method for data transmission between databases according to any one of claims 1 to 4.

Images