A data transfer optimization method and device based on ETL processing

Abstract

The present application relates to a kind of data transfer optimization method and equipment based on ETL processing.The method part mainly includes: the proportion of the amount of business data between multiple ETL processing servers is used to determine the ETL processing server for corresponding business to carry out ETL processing;Each ETL processing server transfers the data of corresponding business to the ETL processing server for corresponding business to carry out ETL processing, and redirects the subsequent data of corresponding business originally needed to receive to the ETL processing server for corresponding business to carry out ETL processing;Each ETL processing server obtains the time T1 that itself transfers data to the ETL processing server for corresponding business to carry out ETL processing, and the time T2 that the subsequent data of corresponding business is uploaded to ETL processing server, if T1 is greater than T2, then the instruction of delayed upload is issued to the subsequent data upload party of corresponding business.The method used in the present application can optimize the synchronization of network data, improve data transfer efficiency, save network resources, and avoid data sequence disorder.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of data processing, in particular to a data transfer optimization method and device based on ETL processing. BACKGROUND

[0002] ETL is the abbreviation of English Extract-Transform-Load, which is used to describe the process of extracting data from the source end, transforming and loading to the destination end. The word ETL is more commonly used in data warehouse, but its object is not limited to data warehouse.

[0003] Generally, the existing means of ETL of data is to collect data in a server for ETL process, that is, a dedicated server is set up for ETL. In this process, a scheduling period and a processing period are generally set, for example, the scheduling period is set to one month, then other servers will collect data to the dedicated server every other month, for example, the processing period is set to one week, then the dedicated server will perform ETL processing every other week. In addition, in addition to the next scheduling period, when there is a new / additional server, for example, the type of electricity used in the region changes over time, the electricity users move in and out, and new communities move in, etc., the data needs to be collected to the dedicated server for ETL. However, in the near future, the difference in capabilities between servers is becoming more and more similar; at this time, the original idea of setting a dedicated server for ETL will cause unnecessary constraints on efficiency.

[0004] For example, for power grid data, it generally has multiple sources, heterogeneous information, large quantity, and multiple attributes. For each region, there is a downstream server as a data reporting end to collect data in the region. When all data needs to be ETLed, each downstream data reporting end needs to report data to a dedicated server for ETL. However, with the development of technology, the capabilities of downstream data reporting end servers will become more and more similar to dedicated servers, and even there is no difference between them. Moreover, for a certain business, sometimes a downstream server will collect most of the data. At this time, if the data still needs to be reported to a dedicated server for processing, it will waste network resources and reduce efficiency.

[0005] On the other hand, after determining the server for ETL processing, it is necessary to collect the relevant data collected by other servers to the server for processing, so that the other servers need to transfer the data to the server for ETL processing, and at the same time, continue to accept the subsequent data collected by themselves, and then transfer again after collecting the subsequent data. In this way, the data transfer process is too cumbersome, which reduces the efficiency and wastes network resources. How to better transfer data and collect subsequent data is a problem.

[0006] Therefore, how to overcome the defects of the prior art and solve the problem of reducing efficiency and wasting network resources caused by setting a dedicated server for ETL and data transfer and subsequent data collection is a problem to be solved in the technical field. SUMMARY

[0007] In view of the above defects or improvement needs of the prior art, the present application provides a data transfer optimization method and device based on ETL processing. The method sets a plurality of ETL processing servers for ETL processing and an ETL scheduling server for scheduling. The ETL scheduling server determines which ETL processing server processes the corresponding business according to the size of the amount of business data contained in each ETL processing server in units of business. In this way, the synchronization of network data can be optimized, the data transfer efficiency can be improved, and network resources can be saved. In addition, the data transfer mode is also optimized. After determining the ETL processing server for ETL processing of a certain business, the subsequent data of the business is directly reported to the ETL processing server, avoiding the situation that after completing a round of data transfer, the subsequent data is still scattered to different ETL processing servers. On this basis, it is further determined whether the time of transferring data of each ETL processing server to the ETL processing server for ETL processing of the corresponding business is greater than the time of uploading the subsequent data of the corresponding business to the ETL processing server. If the former is greater than the latter, it means that the time of uploading the subsequent data of the corresponding business to the ETL processing server will be earlier. At this time, the data transfer of the server has not even ended, which may easily cause the disorder of the sequence of data. Therefore, in this case, an instruction of delayed uploading is issued to the subsequent data uploading party of the corresponding business, so that the subsequent data is uploaded after the data transfer of the server is completed, thereby ensuring the consistency of the data and avoiding the disorder of the sequence.

[0008] The embodiment of the present application adopts the following technical solutions:

[0009] In a first aspect, the present application provides a data transfer optimization method based on ETL processing, comprising:

[0010] The ETL processing servers determine the ETL processing server for ETL processing of the corresponding business through the proportion of the amount of business data.

[0011] Each ETL processing server transfers data of a corresponding business to an ETL processing server performing ETL processing on the corresponding business, and redirects subsequent data of the corresponding business originally required to be received to the ETL processing server performing ETL processing on the corresponding business.

[0012] Each ETL processing server acquires a time T1 of transferring data of the corresponding business to the ETL processing server performing ETL processing on the corresponding business, and a time T2 of uploading subsequent data of the corresponding business to the ETL processing server, and if T1 is greater than T2, issues a delayed uploading instruction to a subsequent data uploading party of the corresponding business.

[0013] Further, in the delayed uploading instruction, the delayed uploading time is greater than T1-T2.

[0014] Further, the ETL processing server performing ETL processing on the corresponding business is determined among the plurality of ETL processing servers by a proportion of a business data volume, and specifically includes:

[0015] An ETL scheduling server and a plurality of ETL processing servers for performing ETL processing are set;

[0016] Each ETL processing server sends an ETL data list to be processed to the ETL scheduling server when ETL processing is about to be performed, and the ETL data list records a task directory of different businesses and a percentage of a data volume of each business; the ETL scheduling server determines an ETL processing server performing ETL processing on each business according to the percentage of the data volume of each business recorded on the ETL data list.

[0017] Further, the percentage of the data volume of each business recorded on the ETL data list specifically includes a percentage of a data volume of each business possessed by the local server relative to a total data volume of the business that has been processed by ETL in the past.

[0018] Further, the ETL scheduling server determines an ETL processing server performing ETL processing on each business according to the percentage of the data volume of each business recorded on the ETL data list, and specifically includes:

[0019] After the ETL scheduling server acquires the ETL data list sent by each ETL processing server, it compares the percentage of the data volume of each business recorded on each ETL data list, selects an ETL data list with the highest percentage of the data volume of each business, and sets the ETL processing server corresponding to the selected ETL data list as an ETL processing server performing ETL processing on the corresponding business.

[0020] Furthermore, when determining the ETL processing server for a certain service based on the percentage of data volume of each service recorded on the ETL data list, the percentage of data volume of that service in at least one ETL data list must exceed a preset percentage threshold; otherwise, data transfer and ETL processing will not be performed on that service.

[0021] Furthermore, each ETL processing server transfers the data of the corresponding service to the ETL processing server that performs ETL processing on the corresponding service, specifically including:

[0022] Each ETL processing server obtains a lookup table from the ETL scheduling server between the ETL processing servers that perform ETL processing on the corresponding business and the corresponding business.

[0023] If it is an ETL processing server that performs ETL processing for the corresponding business, then it retains its own corresponding business data.

[0024] If it is not an ETL processing server that performs ETL processing for the corresponding business, then it will transfer its corresponding business data to the ETL processing server that performs ETL processing for the corresponding business.

[0025] After the data transfer is completed, the ETL processing server, which needs to perform ETL processing on the corresponding business, will perform ETL processing on the corresponding business.

[0026] Furthermore, the ETL scheduling server is configured with a scheduling period. Within the same scheduling period, the ETL scheduling server only determines the ETL processing server to perform ETL processing on the corresponding service once. Within the same scheduling period, if an ETL processing server has been determined to perform ETL processing on a certain service, other ETL processing servers will redirect the subsequent data received from that service, so that the subsequent data of that service is directly transmitted to the ETL processing server that performs ETL processing on that service.

[0027] Furthermore, after the data transfer, the method also includes:

[0028] Based on the historical tasks triggered by the associated verification of the data from multiple business sets that have already been received, obtain the number of tasks currently waiting for verification for the data of each business.

[0029] Obtain the business data after verifying the number of completed tasks. By comparing the data with the data of the business after verifying the number of completed tasks, determine whether it can cover the business data required for the ETL process. If it cannot cover the data, proceed to wait for the ETL process to be executed. If it can cover the data, execute the ETL process.

[0030] If, during the ETL process, the sum of the remaining data content awaiting verification of the current task and the result data obtained from the ETL process is less than or equal to the original business data during the ETL process, then the ETL process is executed, and the ETL result and the remaining business data awaiting verification of the current task are saved.

[0031] If, during the ETL execution process, the sum of the remaining business data awaiting verification and the result data obtained from the ETL execution process is greater than the original business data during the ETL execution process, the ETL execution process will continue.

[0032] On the other hand, the present invention provides a data transfer optimization device based on ETL processing, specifically comprising at least one processor and a memory, wherein the at least one processor and the memory are connected via a data bus, the memory stores instructions that can be executed by the at least one processor, and the instructions, after being executed by the processor, are used to complete the data transfer optimization method based on ETL processing in the first aspect.

[0033] Compared with the prior art, the beneficial effects of the present invention are as follows: multiple ETL processing servers for performing ETL processing and one ETL scheduling server for scheduling are set up. The ETL scheduling server determines which ETL processing server will process the corresponding business based on the size of the data volume of each business contained in the ETL processing server. This can optimize network data synchronization, improve data transfer efficiency, and save network resources.

[0034] In addition, the ETL scheduling server is configured with a scheduling cycle. Within a scheduling cycle, once an ETL server has been identified to perform ETL processing for a specific business, subsequent data for that business is directly reported to that ETL server. This avoids the situation where, after a round of data transfer has been completed, subsequent data is scattered across different ETL servers. Furthermore, the time taken for each ETL server to transfer its own data to the ETL server performing the corresponding business's ETL processing is compared with the time taken for subsequent data from that business to be uploaded to the ETL server. If the former is greater than the latter, it means that subsequent data from that business will be uploaded to the ETL server earlier, even before the server's data transfer is complete. This could easily lead to data sequence disorder. Therefore, in this case, a delayed upload instruction is issued to the subsequent data uploaders of that business, requiring them to upload subsequent data only after the server's data transfer is finished, thus ensuring data consistency and avoiding data sequence disorder. Attached Figure Description

[0035] To more clearly illustrate the technical solutions of the embodiments of the present invention, the accompanying drawings used in the embodiments of the present invention will be briefly described below. Obviously, the drawings described below are merely some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without any creative effort.

[0036] Figure 1 Here is a flowchart of a data transfer optimization method based on ETL processing provided in Embodiment 1 of the present invention;

[0037] Figure 2 This is a detailed flowchart of step 100 provided in Embodiment 1 of the present invention;

[0038] Figure 3 This is a detailed flowchart of step 200 provided in Embodiment 1 of the present invention;

[0039] Figure 4 This is a schematic diagram of a scenario provided in Embodiment 2 of the present invention;

[0040] Figure 5 This is a schematic diagram of the data transfer optimization steps provided in Embodiment 2 of the present invention;

[0041] Figure 6 This is a flowchart of the method after data transfer provided in Embodiment 3 of the present invention;

[0042] Figure 7 This is a schematic diagram of a data transfer optimization device based on ETL processing provided in Embodiment 4 of the present invention. Detailed Implementation

[0043] To make the objectives, technical solutions, and advantages of this invention clearer, the invention will be further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the invention.

[0044] This invention is an architecture of a specific functional system. Therefore, the specific embodiments mainly describe the functional logic relationship of each structural module, and do not limit the specific software and hardware implementation methods.

[0045] Furthermore, the technical features involved in the various embodiments of the present invention described below can be combined with each other as long as they do not conflict with each other. The present invention will now be described in detail with reference to the accompanying drawings and embodiments.

[0046] Example 1:

[0047] like Figure 1 As shown in the figure, this embodiment of the invention provides a data transfer optimization method based on ETL processing, and the specific steps are as follows.

[0048] Step 100: Multiple ETL processing servers determine the ETL server for a given business based on the proportion of business data collected. This step addresses the shortcomings of existing technologies that dedicate a single server for ETL processing. By setting up multiple ETL processing servers, organized by business unit, the appropriate ETL server is determined based on the proportion of business data collected by each server.

[0049] Step 200: Each ETL processing server transfers the data for the corresponding service to the ETL processing server that performs the ETL processing for that service, and redirects the subsequent data for the corresponding service that was originally to be received to the ETL processing server that performs the ETL processing for that service. This step, after determining the ETL processing server that performs the ETL processing for the corresponding service, performs data transfer between the ETL processing servers and redirects the subsequent data for the corresponding service so that it is directly uploaded to the ETL processing server that performs the ETL processing for that service.

[0050] Step 300: Each ETL processing server obtains the time T1 when its own transferred data is sent to the ETL processing server performing ETL processing on the corresponding business, and the time T2 when subsequent data of the corresponding business is uploaded to the ETL processing server. If T1 is greater than T2, a delayed upload instruction is issued to the subsequent data uploader of the corresponding business. This step is based on step 20. When redirecting subsequent data of the corresponding business, each ETL processing server also judges the difference between its own data transfer time and the subsequent data upload time. If the former is greater than the latter, it means that the subsequent data of the corresponding business will be uploaded to the ETL processing server earlier. Therefore, a delayed upload instruction is issued to the subsequent data uploader of the corresponding business to upload the data after the server has completed the data transfer, thus avoiding data timing disorder.

[0051] In this preferred embodiment, when each ETL processing server obtains the time T1 for transferring its own data to the ETL processing server performing ETL processing on the corresponding service, the obtained time T1 refers to the estimated time required for the data transfer to complete, or the time value at which the data transfer is completed, starting from this current time. Similarly, when obtaining the time T2 for uploading subsequent data of the corresponding service to the ETL processing server, the obtained time T2 refers to the estimated time required for the subsequent data upload to complete, or the time value at which the subsequent data upload is completed, starting from this current time. Where T1 and T2 are both the former or both the latter, if T1 is greater than T2, a delayed upload instruction is issued, and the delayed upload time in the delayed upload instruction is greater than T1-T2 (T1 minus T2). If T1 is less than T2, it indicates that the subsequent data upload completion time for the corresponding service is longer or later, and therefore no further action is required.

[0052] By following the steps above, network data synchronization can be optimized, data transfer efficiency can be improved, and network resources can be saved. This avoids the situation where, after one round of data transfer has been completed, subsequent data is still scattered across different ETL processing servers. Furthermore, it ensures data consistency and prevents data sequence disorder.

[0053] like Figure 2 As shown, in this preferred embodiment, step 100 (determining the ETL processing server for the corresponding business based on the proportion of business data among multiple ETL processing servers) may specifically include the following steps.

[0054] Step 101: Configure the ETL scheduling server and multiple ETL processing servers for ETL processing. In this step, the ETL scheduling server is one of the ETL processing servers or any server in the network that has ETL processing capabilities. Based on this, if the related ETL data is distributed across different ETL processing servers, the next step will be initiated; if the related ETL data resides on only one ETL processing server, then ETL processing will be performed directly through that ETL processing server.

[0055] Step 102: Before performing ETL processing, each ETL processing server sends its own list of ETL data to be processed to the ETL scheduling server. The ETL data list records the task directories for different services and the percentage of data volume for each service. In this embodiment, the percentage is specifically the percentage of the data volume of each service owned by the local server relative to the total data volume of that service that has historically undergone ETL processing. In this step, each ETL processing server records historical data involving store-and-forward, as well as the corresponding ETL-processed data, in the form of an ETL task directory. Therefore, the most efficient approach is to use the ETL service directory as an index in the aforementioned ETL data list, and then present the percentage of data currently owned by the local server in the corresponding item. In this preferred embodiment, the ETL data list is divided by service, and ETL is also performed on a service-by-service basis. Taking the power grid as an example, the division of services can include: dividing power grid services according to different regions, dividing power grid services according to different electricity consumption patterns, dividing power grid services according to different power generation methods, etc.

[0056] Step 103: The ETL scheduling server determines the ETL processing server to perform ETL processing on each service based on the percentage of data volume for each service recorded in the ETL data list. Specifically, this step in this preferred embodiment includes: after obtaining the ETL data lists sent by each ETL processing server, the ETL scheduling server compares the percentage of data volume for each service recorded in each ETL data list, selects the ETL data list with the highest percentage of data volume for each service, and sets the ETL processing server corresponding to the selected ETL data list as the ETL processing server to perform ETL processing on the corresponding service. In this step, after determining the ETL processing server to perform ETL processing on each service, the ETL scheduling server also associates each service with its corresponding ETL processing server to form a lookup table, and sends this table to all relevant ETL processing servers.

[0057] Through the above steps, this embodiment sets up multiple ETL processing servers for ETL processing and one ETL scheduling server for scheduling. The ETL scheduling server determines which ETL processing server will process the corresponding business based on the size of the data volume of each business contained in the ETL processing server. This can optimize network data synchronization, improve data transfer efficiency, and save network resources.

[0058] Furthermore, in this preferred embodiment, when determining the ETL processing server for a certain service based on the percentage of data volume of each service recorded in the ETL data list in step 103, at least one ETL data list must have a data volume percentage of that service exceeding a preset percentage threshold; otherwise, data transfer and ETL processing will not be performed on that service. For example, if the percentage threshold is set to 30% (or 20%, 40%, etc., this is just an example and not a limitation), then for a service, the data volume percentage of that service in a certain ETL data list must exceed 30%, that is, the data volume percentage of that service on the ETL processing server corresponding to that ETL data list must exceed 30% (assuming only this one exceeds 30%, if there are other ETL processing servers with higher percentages, the one with the highest percentage will be selected first). Only then will subsequent steps proceed, transferring data related to that service from other ETL processing servers to this ETL processing server so that it can perform ETL processing on that service. If no ETL processing server accounts for more than 30% of the total data volume, all ETL processing servers will not perform data transfer for this business. Instead, they will each store their own data and wait for the next ETL processing cycle to reassess whether any ETL processing server accounts for more than 30% of the total data volume. Setting a percentage threshold can prevent data transfer from occurring when the data volume on all ETL processing servers is low, thus avoiding unnecessary operations and resource waste.

[0059] In step 200 of this preferred embodiment, each ETL processing server receives a lookup table sent by the ETL scheduling server. Based on this lookup table, each ETL processing server can determine which ETL processing server is responsible for processing each service, thereby transferring the data of the corresponding service to the appropriate ETL processing server. For example... Figure 3 As shown, step 200 can be expanded into the following multiple steps.

[0060] Step 201: Each ETL processing server obtains a lookup table from the ETL scheduling server, corresponding to the ETL processing server and the corresponding business. This lookup table is generated by the ETL scheduling server and sent to each ETL processing server.

[0061] Step 202: If it is an ETL processing server performing ETL processing on the corresponding business, then it retains its own corresponding business data. Each ETL processing server determines whether it is an ETL processing server performing ETL processing on the corresponding business according to the lookup table. If it is, it keeps the corresponding business on its server; otherwise, it proceeds to step 203.

[0062] Step 203: If it is not an ETL processing server that performs ETL processing on the corresponding business, then it transfers its corresponding business data to the ETL processing server that performs ETL processing on the corresponding business.

[0063] Step 204: Each server redirects the subsequent data of the corresponding business that it originally needed to receive to the ETL processing server that performs ETL processing on the corresponding business.

[0064] In this preferred embodiment, the ETL scheduling server is configured with a scheduling period. Within the same scheduling period, the ETL scheduling server determines the ETL processing server to perform ETL processing on the corresponding service only once. Based on the above design, in this embodiment, if an ETL processing server has been determined to perform ETL processing on a certain service within the same scheduling period, other ETL processing servers redirect subsequent data received from that service, so that subsequent data of that service is directly transmitted to the ETL processing server that performed ETL processing on that service. This avoids the subsequent data being scattered across different ETL processing servers after one round of data transfer has been completed, thereby avoiding subsequent data transfers within a scheduling period and saving network resources.

[0065] In this preferred embodiment, the ETL processing of each ETL processing server is set to perform ETL processing at regular intervals, or when the total data volume reaches a certain memory limit. Generally, the ETL processing cycle of each ETL processing server is set to be much shorter than the scheduling cycle of the ETL scheduling server. For example, the scheduling cycle of the ETL scheduling server is set to one month, and the ETL processing cycle of each ETL processing server is set to one week. In the first week, each ETL processing server needs to perform ETL processing. At this time, the ETL scheduling server also schedules according to the data distribution of each ETL processing server. After the first scheduling is completed, in the following month, the business data that was originally to be sent to the storage of each ETL processing server will be directly sent to the ETL processing server that performs the corresponding business ETL processing for storage. Then, ETL processing is performed once a week during the weekly ETL processing cycle. When the second month arrives, or after adding / removing ETL processing servers, all the above steps of this embodiment are repeated. For example, the electricity consumption type in the area changes during the time period, large electricity users move in or out, new communities are occupied, etc.

[0066] In addition, since the collection time of business data is generally much shorter than the ETL processing cycle, such as once a day, in order to avoid the new subsequent data being collected and uploaded to the server during the data transfer period, which would cause the sequential data to be disordered, step 300 is also performed to determine the time required for both, so as to ensure the order of the data.

[0067] The storage basis of the ETL processing server involved in this embodiment is usually dynamically allocated based on ETL servers that have historically processed the corresponding data types, or allocated based on the current server network conditions and resource usage, etc., and is not limited thereto. Therefore, within each ETL processing cycle or the scheduling cycle of the ETL scheduling server, various new data may be stored on various ETL processing servers, thus requiring ETL processing according to the aforementioned ETL processing cycle, and scheduling processing according to the aforementioned scheduling cycle.

[0068] In summary, this embodiment sets up multiple ETL processing servers for ETL processing and one ETL scheduling server for scheduling. The ETL scheduling server determines which ETL processing server handles a given service based on the size of the data volume of each service contained in the ETL processing server. This optimizes network data synchronization, improves data transfer efficiency, and saves network resources. Furthermore, the ETL scheduling server has a scheduling cycle. Within a scheduling cycle, once an ETL processing server has been determined to handle a particular service, subsequent data for that service is directly reported to that ETL processing server, preventing data from being scattered across different ETL processing servers after a round of data transfer has been completed. Based on this, the time it takes for each ETL processing server to transfer its own data to the ETL processing server that performs ETL processing for the corresponding business is also compared with the time it takes for subsequent data of the corresponding business to be uploaded to the ETL processing server. If the former is greater than the latter, it means that the subsequent data of the corresponding business will be uploaded to the ETL processing server earlier. At this time, the server's data transfer may not even be finished, which may easily cause data order disorder. Therefore, in this case, a delayed upload instruction is issued to the subsequent data uploader of the corresponding business, so that it can upload subsequent data after the server's data transfer is completed, thereby ensuring data consistency and avoiding data order disorder.

[0069] Example 2:

[0070] Based on the data transfer optimization method based on ETL processing provided in Embodiment 1, this Embodiment 2 will provide a more detailed description of the present invention through a specific application scenario.

[0071] This embodiment uses power grid data distribution as an example for illustration. For example... Figure 4As shown: The power grid data to be integrated is distributed across four regions: A, B, C, and D. ETL processing needs to be performed on industrial, agricultural, commercial, and residential electricity consumption, resulting in four business processes: ETL processing for industrial electricity, ETL processing for agricultural electricity, ETL processing for commercial electricity, and ETL processing for residential electricity. Typically, each of the four regions (A, B, C, and D) will have a downstream server to collect data for its region. These servers will be named Server A, Server B, Server C, and Server D, respectively. Data from each region is usually first uploaded to the corresponding server for storage. (Reference) Figure 3 In this scenario, servers in each region have collected relevant data for four business operations and are preparing for ETL processing. Specifically, server A has collected 35% of the industrial electricity consumption data, 12% of the agricultural electricity consumption data, 8% of the commercial electricity consumption data, and 10% of the residential electricity consumption data in region A; server B has collected 15% of the industrial electricity consumption data, 42% of the agricultural electricity consumption data, 9% of the commercial electricity consumption data, and 11% of the residential electricity consumption data in region B; server C has collected 13% of the industrial electricity consumption data, 15% of the agricultural electricity consumption data, 38% of the commercial electricity consumption data, and 12% of the residential electricity consumption data in region C; and server D has collected 9% of the industrial electricity consumption data, 7% of the agricultural electricity consumption data, 12% of the commercial electricity consumption data, and 51% of the residential electricity consumption data in region A. It should be noted that the above percentages refer to the proportion of the collected relevant data volume relative to the total volume of corresponding business data from previous ETL processes. For example, if an ETL process was performed previously (generally, the volume from the last ETL process is used as a reference), and the total volume of industrial electricity-related data was 100, while the current volume of industrial electricity-related data in region A is 35, then the percentage is 35%. The same logic applies to the percentages for other regions and other types of electricity consumption. Additionally, if there is no historical reference for the total volume of corresponding business data, a value can be estimated based on the storage capacity of each server as an initial reference.

[0072] Based on the above scenario, the specific process of the data transfer optimization method based on ETL processing provided in this embodiment is as follows: Figure 5 As shown, the process includes the following steps.

[0073] Step 401: Configure servers A, B, C, and D as ETL processing servers, and configure an ETL scheduling server. The ETL scheduling server can be any one of servers A, B, C, or D, or a new server can be selected specifically for scheduling. For ease of demonstration, this embodiment uses a new server as the scheduling server. (Refer to...) Figure 3 .

[0074] Step 402: Set the scheduling cycle and ETL processing cycle. The ETL processing cycle is set to one week, with each server preparing to start ETL processing on the last day of each week; while the scheduling cycle is set to one month, with the scheduling server scheduling and selecting the server to process each service during the first ETL process of each month.

[0075] Step 403: Servers A, B, C, and D all send their lists of pending ETL data to the scheduling server. This marks the first ETL processing session within a scheduling cycle, requiring all ETL processing servers and the scheduling server to be operational. The ETL data lists for servers A, B, C, and D each include industrial electricity consumption items and their respective data volume percentages; agricultural electricity consumption items and their respective data volume percentages; commercial electricity consumption items and their respective data volume percentages; and residential electricity consumption items and their respective data volume percentages. At this moment, the percentages of each business segment are the same as those described in the scenario above. That is, in the order of industrial electricity consumption, agricultural electricity consumption, commercial electricity consumption, and residential electricity consumption, the percentages of each data segment on server A are: 35%, 12%, 8%, and 10%; on server B, 15%, 42%, 9%, and 11%; on server C, 13%, 15%, 38%, and 12%; and on server D, 9%, 7%, 12%, and 51%.

[0076] Step 404: The scheduling server determines the corresponding ETL processing server for each service based on the ETL data list. This step also sets a percentage threshold of 30%. Only when a service's percentage exceeds 30% is the corresponding ETL processing server determined; otherwise, ETL processing for that service is not performed. In this embodiment, it is clear from the ETL data list that the industrial electricity service data volume is largest on server A (35%), so the scheduling server determines server A as the ETL processing server for industrial electricity services; the agricultural electricity service data volume is largest on server B (42%), so the scheduling server determines server B as the ETL processing server for agricultural electricity services; the commercial electricity service data volume is largest on server C (38%), so the scheduling server determines server C as the ETL processing server for commercial electricity services; and the residential electricity service data volume is largest on server D (51%), so the scheduling server determines server D as the ETL processing server for residential electricity services. Regarding the percentage threshold set in this embodiment, it should be noted that setting the percentage threshold to 30% is just an example. The percentage threshold can also be 20%, 40%, etc. Taking the percentage threshold of 30% as an example, for a certain business, the percentage of data volume for that business in a certain ETL data list needs to exceed 30%. That is, the percentage of data volume for that business on the ETL processing server corresponding to that ETL data list needs to exceed 30% (assuming that only this one exceeds 30%; if there are other ETL processing servers with a higher percentage, the one with the highest percentage will be selected first). Only then will subsequent steps be performed, transferring the data related to that business from other ETL processing servers to this ETL processing server so that it can perform ETL processing for that business. If there is no ETL processing server with a percentage exceeding 30%, then all ETL processing servers will not perform data transfer for that business, but will store it on their own, waiting for the next ETL processing cycle to re-evaluate whether there is an ETL processing server with a percentage exceeding 30%. Setting a percentage threshold can avoid data transfer when the data volume on all ETL processing servers is small, avoiding unnecessary operations and resource waste.

[0077] Step 405: Data transfer occurs between servers, and subsequent data from the corresponding services that were originally intended to be received are redirected to the ETL processing server that performs ETL processing on the corresponding services. Based on the ETL processing servers determined in the previous step, the scheduling server obtains a lookup table associating each service with its corresponding ETL processing server. This lookup table includes the following relationships: Industrial electricity service - Server A; Agricultural electricity service - Server B; Commercial electricity service - Server C; Residential electricity service - Server D. The scheduling server sends this lookup table to each server, and each server transfers data according to the table and redirects subsequent data. Specifically, server A needs to retain its own industrial electricity consumption data, transfer agricultural electricity consumption data to server B, commercial electricity consumption data to server C, and residential electricity consumption data to server D; server B needs to retain its own agricultural electricity consumption data, transfer industrial electricity consumption data to server A, commercial electricity consumption data to server C, and residential electricity consumption data to server D; server C needs to retain its own commercial electricity consumption data, transfer industrial electricity consumption data to server A, agricultural electricity consumption data to server B, and residential electricity consumption data to server D; and server D needs to retain its own residential electricity consumption data, transfer industrial electricity consumption data to server A, agricultural electricity consumption data to server B, and commercial electricity consumption data to server C. Regarding subsequent data redirection, taking a scheduling cycle as an example, after this scheduling, when each region collects and uploads various business data to the server within that scheduling cycle, it can directly redirect the synchronization direction of the corresponding business to the corresponding ETL processing server. For example, in region A, the industrial electricity consumption data collected will be stored on server A in this region, while the agricultural electricity consumption data will be directly redirected to server B, the commercial electricity consumption data will be directly redirected to server C, and the residential electricity consumption data will be directly redirected to server D. The same applies to other servers. The scheduling server only restarts the scheduling process after a scheduling cycle has ended.

[0078] Step 406: Each server determines the time it takes to transfer its own data and the time it takes for subsequent data to be uploaded for the corresponding service. If the former is greater than the latter, it issues a delayed upload instruction to the subsequent data uploader for the corresponding service. This step is based on step 405, where each ETL processing server obtains the time T1 for transferring its own data to the ETL processing server that performs ETL processing for the corresponding service, and the time T2 for the subsequent data of the corresponding service to be uploaded to the ETL processing server. If T1 is greater than T2, it issues a delayed upload instruction to the subsequent data uploader for the corresponding service. Taking region A and server A as an example, for industrial electricity service-related data, server A processes it itself and does not need to determine the data transfer time. However, for agricultural electricity service-related data, server A needs to determine (estimate) the time required to completely transfer it to server B, and the time required for the next wave of subsequent data redirection and upload to server B. If the time required for the former is greater than the time required for the latter, then a delayed upload instruction is issued to the subsequent data uploader for the corresponding service, so that the agricultural electricity service-related data on server A can be completely transferred to server B before the subsequent related data for the agricultural electricity service can be uploaded to server B. The same logic applies to determining other business data, other regions, and other servers.

[0079] In summary, this embodiment sets up multiple ETL processing servers for ETL processing and one ETL scheduling server for scheduling. The ETL scheduling server determines which ETL processing server handles a given service based on the size of the data volume of each service contained in the ETL processing server. This optimizes network data synchronization, improves data transfer efficiency, and saves network resources. Furthermore, the ETL scheduling server has a scheduling cycle. Within a scheduling cycle, once an ETL processing server has been determined to handle a particular service, subsequent data for that service is directly reported to that ETL processing server, preventing data from being scattered across different ETL processing servers after a round of data transfer has been completed. Based on this, the time it takes for each ETL processing server to transfer its own data to the ETL processing server that performs ETL processing for the corresponding business is also compared with the time it takes for subsequent data of the corresponding business to be uploaded to the ETL processing server. If the former is greater than the latter, it means that the subsequent data of the corresponding business will be uploaded to the ETL processing server earlier. At this time, the server's data transfer may not even be finished, which may easily cause data order disorder. Therefore, in this case, a delayed upload instruction is issued to the subsequent data uploader of the corresponding business, so that it can upload subsequent data after the server's data transfer is completed, thereby ensuring data consistency and avoiding data order disorder.

[0080] Example 3:

[0081] Based on the data transfer optimization method based on ETL processing provided in Embodiments 1 and 2 above, the present invention further includes, after data transfer, the method as follows: Figure 6 The process shown:

[0082] Step 501: Based on the historical tasks triggered by the associated verification of the data from the multiple sets of services that have been received, obtain the number of tasks currently waiting for verification for the data of each service.

[0083] Step 502: Obtain the business data after the current completion of the corresponding task quantity verification. By comparing the business data after the completion of the corresponding task quantity verification, determine whether it can cover the business data required by the ETL process. If it cannot cover the data, proceed to wait for the execution of the ETL process. If it can cover the data, execute the ETL process.

[0084] Step 503: During the ETL process, if the sum of the remaining business data waiting for verification and the result data obtained from the ETL process is less than or equal to the original business data during the ETL process, then execute the ETL process and save the ETL result and the remaining business data waiting for verification.

[0085] Step 504: If, during the ETL execution process, the sum of the remaining business data awaiting verification and the result data obtained from the ETL execution process is greater than the original business data during the ETL execution process, the ETL execution process will continue.

[0086] In one specific embodiment of this preferred embodiment, the historical task of triggering associated verification based on the data of each service specifically involves: receiving the data of the service, and determining the historical task of triggering associated verification based on one or more of the following: the source of the service data, the attribute of the service data, and the size of the service data; wherein, the historical task requires the data of multiple services to be received in order to complete the verification, and if the data of any service is missing, the verification process of the corresponding historical task cannot be performed.

[0087] In one specific embodiment of this preferred embodiment, when entering the waiting execution of the ETL process, and the ETL process includes at least a first ETL process and a second ETL process (for example, the first ETL process processes factory data and the second ETL process processes school data), the method further includes: obtaining the total number of remaining tasks currently awaiting verification for each service's data in the first ETL process, and the total number of remaining tasks currently awaiting verification for each service's data in the second ETL process; if the total number of remaining tasks currently awaiting verification for each service's data in the first ETL process is less than or equal to a preset threshold, then scheduling this server or other servers to prioritize the processing of the verification tasks for each service's data in the first ETL process.

[0088] In one specific embodiment of this preferred embodiment, when the total number of remaining tasks waiting to be verified for each service during the ETL process is greater than a preset value, the tasks currently waiting to be verified are processed in the original order of this server or other servers.

[0089] In one specific embodiment of this preferred embodiment, the step of performing the ETL process and saving the ETL result and the remaining service data currently awaiting verification by the task includes: generating a copy of the remaining service data currently awaiting verification by the task before performing the ETL process; saving the ETL result and the copy of the remaining service data currently awaiting verification by the task after performing the ETL process; and deleting the copy of the remaining service data currently awaiting verification by the task after all verification tasks corresponding to the copy of the remaining service data currently awaiting verification by the task are completed.

[0090] In one specific embodiment of this preferred embodiment, during the ETL process, the relationship between the sum of the remaining business data awaiting task verification and the result data obtained from the ETL process, and the original business data awaiting ETL execution, is analyzed. Specifically, this includes: after the first trigger of the analysis, recording a first difference between the sum of the remaining business data awaiting task verification and the result data obtained from the ETL process, and the original business data awaiting ETL execution; obtaining the ETL compression ratio based on the ratio between the result data obtained from the ETL process and the original business data during the ETL process; and after the first trigger of the analysis, if the number of tasks associated with a newly acquired set or more sets of business data is reset to zero, and the result of the newly acquired set or more sets of business data after weighting by the ETL compression ratio is greater than or equal to the first difference, a second analysis is triggered. The ETL compression ratio is obtained by calculation based on historical executions of corresponding ETL processes; or by calculation through simulation operations.

[0091] In this embodiment, a preset value for priority verification is set during the data validity verification stage before ETL operations. When the number of tasks waiting to be verified is less than the preset value, the system provides a mechanism to schedule the current server or other servers to prioritize the processing of these tasks. This allows the system to complete data verification as early as possible, reducing the waiting time for the data of the currently waiting tasks. In addition, by adjusting the logic of the server triggering the ETL operation, under the premise that data validity verification is completed and the data is correct, if the total amount of data information of the currently waiting tasks and the data information after the ETL operation is less than the memory occupied by the original data information, the ETL operation is performed directly, and the ETL result is saved. This reduces the time spent verifying the validity of the currently waiting tasks, thereby reducing the server's ETL operation time and improving the efficiency of the ETL process.

[0092] Example 4:

[0093] Based on the data transfer optimization method based on ETL processing provided in Embodiments 1 and 2 above, the present invention also provides a data transfer optimization device based on ETL processing that can be used to implement the above methods, such as... Figure 7 The diagram shown is a schematic representation of the device architecture according to an embodiment of the present invention. The data transfer optimization device based on ETL processing in this embodiment includes one or more processors 21 and a memory 22. Figure 7 Take a processor 21 as an example.

[0094] Processor 21 and memory 22 can be connected via a bus or other means. Figure 7 Taking the example of a connection between China and Israel via a bus.

[0095] The memory 22, as a non-volatile computer-readable storage medium, can be used to store non-volatile software programs, non-volatile computer-executable programs, and modules, such as the ETL-based data transfer optimization methods in Embodiments 1 and 2. The processor 21 executes various functional applications and data processing of the ETL-based data transfer optimization device by running the non-volatile software programs, instructions, and modules stored in the memory 22, thereby implementing the ETL-based data transfer optimization methods of Embodiments 1 and 2.

[0096] Memory 22 may include high-speed random access memory, and may also include non-volatile memory, such as at least one disk storage device, flash memory device, or other non-volatile solid-state storage device. In some embodiments, memory 22 may optionally include memory remotely located relative to processor 21, which can be connected to processor 21 via a network. Examples of such networks include, but are not limited to, the Internet, intranets, local area networks, mobile communication networks, and combinations thereof.

[0097] The program instructions / modules are stored in memory 22. When executed by one or more processors 21, they perform the ETL-based data transfer optimization methods described in Embodiments 1 and 2 above, for example, the methods described above. Figures 1-3 , Figure 5 The steps shown.

[0098] Those skilled in the art will understand that all or part of the steps in the various methods of the embodiments can be implemented by a program instructing related hardware. The program can be stored in a computer-readable storage medium, which may include: read-only memory (ROM), random access memory (RAM), disk or optical disk, etc.

[0099] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of the present invention should be included within the protection scope of the present invention.

Claims

1. A data transfer optimization method based on ETL processing, characterized in that, The method comprises the following steps: The ETL processing servers determine the ETL processing server for each business according to the proportion of the business data volume; Each ETL processing server transfers the data of the corresponding business to the ETL processing server for the corresponding business, and redirects the subsequent data of the corresponding business that needs to be received to the ETL processing server for the corresponding business; Each ETL processing server obtains the time T1 of transferring data to the ETL processing server for the corresponding business and the time T2 of uploading the subsequent data of the corresponding business to the ETL processing server, and if T1 is greater than T2, the ETL processing server for the corresponding business is determined according to the proportion of the business data volume. The ETL processing servers determine the ETL processing server for each business according to the proportion of the business data volume, and the method comprises the following steps: An ETL scheduling server and a plurality of ETL processing servers for ETL processing are set; Each ETL processing server sends an ETL data list to be processed to the ETL scheduling server before ETL processing, and the ETL data list records the task directory of different businesses and the percentage of the data volume of each business; The ETL scheduling server determines the ETL processing server for each business according to the percentage of the data volume of each business recorded on the ETL data list; Each ETL processing server obtains a comparison table between the ETL processing server for the corresponding business and the corresponding business from the ETL scheduling server; If the ETL processing server is the ETL processing server for the corresponding business, the corresponding business data of the ETL processing server is retained; If the ETL processing server is not the ETL processing server for the corresponding business, the corresponding business data of the ETL processing server is transferred to the ETL processing server for the corresponding business; After the data transfer is completed, the ETL processing server for the corresponding business performs ETL processing on the corresponding business. In the delayed uploading instruction, the delay uploading time is greater than T1-T2.

2. The data transfer optimization method based on ETL processing according to claim 1, wherein, The percentage of the data volume of each business recorded on the ETL data list comprises the percentage of the data volume of each business owned by the local server relative to the total data volume of the business that has been processed by ETL.

3. The data transfer optimization method based on ETL processing according to claim 1, wherein, The ETL scheduling server determines the ETL processing server for each business according to the percentage of the data volume of each business recorded on the ETL data list, and the method comprises the following steps:

4. The data transfer optimization method based on ETL processing according to claim 3, wherein, ​ The ETL scheduling server obtains the ETL data lists sent by the ETL processing servers, compares the percentages of the data volumes of the businesses recorded on the ETL data lists, selects the ETL data list with the highest percentage of the data volume of each business, and sets the ETL processing server corresponding to the selected ETL data list as the ETL processing server for performing ETL processing on the corresponding business.

5. The data transfer optimization method based on ETL processing according to claim 4, wherein, When the ETL processing server for performing ETL processing on a business is determined according to the percentages of the data volumes of the businesses recorded on the ETL data lists, the percentage of the data volume of the business in at least one ETL data list must exceed a preset percentage threshold, otherwise, the data of the business is not transferred and ETL processed.

6. The data transfer optimization method based on ETL processing according to claim 1, wherein, The ETL scheduling server is set with a scheduling period, and the ETL scheduling server only determines the ETL processing server for performing ETL processing on the corresponding business once in the same scheduling period. In the same scheduling period, if the ETL processing server for performing ETL processing on a business has been determined, other ETL processing servers will redirect the subsequent data of the business to the ETL processing server for performing ETL processing on the business.

7. The data transfer optimization method based on ETL processing according to any one of claims 1-6, characterized in that, After the data transfer, the method further comprises: obtaining the number of tasks of each business data currently waiting for verification according to the historical tasks triggered by the associated verification of the data of each business; obtaining the data of the business after completing the verification of the corresponding number of tasks, and determining whether the data of the business after completing the verification of the corresponding number of tasks can cover the data of the business required in the ETL process by comparing the data of the business after completing the verification of the corresponding number of tasks; if not, the ETL process is entered; otherwise, the ETL process is executed; In the waiting execution ETL process, if it is analyzed that the sum of the remaining data content currently waiting for task verification and the result data obtained by executing the ETL process is less than or equal to the original business data in the waiting execution ETL process, the ETL process is executed, and the ETL result and the remaining business data currently waiting for task verification are saved; In the waiting execution ETL process, if it is analyzed that the sum of the remaining business data currently waiting for task verification and the result data obtained by executing the ETL process is greater than the original business data in the waiting execution ETL process, the waiting execution ETL process is still maintained.

8. An ETL processing-based data transfer optimization device, characterized in that: comprising at least one processor and a memory, the at least one processor and the memory are connected through a data bus, the memory stores instructions executable by the at least one processor, and the instructions, after being executed by the processor, are used to complete the ETL processing-based data transfer optimization method in any one of claims 1-7.

Images