A method and device for optimizing power grid data ETL

Abstract

The present invention relates to a method and device for optimizing the ETL of power grid data. The method comprises the following steps: setting an ETL dispatching server and multiple ETL processing servers for performing ETL processing; when each ETL processing server is about to perform ETL processing, it sends its own ETL data list to be processed to the ETL dispatching server; the ETL dispatching server determines the ETL processing server to perform ETL processing for each service based on the percentage of data volume of each service recorded in the ETL data list; and each ETL processing server transfers the data of the corresponding service to the ETL processing server determined by the ETL dispatching server for ETL processing. The method employed by the present invention can optimize network data synchronization, improve data transfer efficiency, and conserve network resources.

Description

[0001] The present invention relates to the technical field of data processing, and in particular to an optimization method and device for power grid data ETL. Background Art

[0002] ETL, short for Extract-Transform-Load, describes the process of extracting, transforming, and loading data from a source to a destination. While the term ETL is often used in data warehouses, its application is not limited to them.

[0003] Typically, the existing method of performing ETL on data is to centralize the data on a single server for the ETL process. This means setting up a dedicated server for ETL. However, in the near future, the capabilities of servers will become increasingly different. At this time, the original idea of ​​setting up a dedicated server for ETL will create unnecessary efficiency constraints.

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

[0005] In view of this, how to overcome the defects of the existing technology and solve the problem that setting up a dedicated server for ETL will reduce efficiency and waste network resources is a difficult problem to be solved in this technical field. Summary of the Invention

[0006] In response to the above-mentioned defects or improvement needs of the prior art, the present invention provides an optimization method and device for power grid data ETL. The method sets multiple ETL processing servers for ETL processing and an ETL scheduling server for scheduling. The ETL scheduling server uses business as a unit and decides which ETL processing server will process the corresponding business according to the size of each business data contained in the ETL processing server. This can optimize the synchronization of network data, improve data transfer efficiency, and save network resources.

[0007] The embodiment of the present invention adopts the following technical solutions:

[0008] In a first aspect, the present invention provides a method for optimizing power grid data ETL, comprising:

[0009] Set up an ETL scheduling server and multiple ETL processing servers for ETL processing;

[0010] When each ETL processing server is about to perform ETL processing, it sends its own ETL data list to be processed to the ETL scheduling server. The ETL data list records the task directory of different businesses and the percentage of data volume of each business. The ETL scheduling server determines the ETL processing server that will perform ETL processing for each business based on the percentage of data volume of each business recorded in the ETL data list.

[0011] Each ETL processing server transfers the data of the corresponding business to the ETL processing server determined by the ETL scheduling server to perform ETL processing on the corresponding business, so as to perform ETL processing.

[0012] Furthermore, the percentage of the data volume of each business recorded in the ETL data list specifically includes: 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 ETL processed in the past.

[0013] Furthermore, the ETL scheduling server determines the ETL processing server that performs ETL processing on each business according to the percentage of the data volume of each business recorded in the ETL data list, specifically including:

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

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

[0016] Furthermore, each of the ETL processing servers transfers the data of the corresponding business to the ETL processing server determined by the ETL scheduling server to perform ETL processing on the corresponding business, so as to perform ETL processing, which specifically includes:

[0017] Each ETL processing server obtains a comparison table between the ETL processing server that performs ETL processing on the corresponding business and the corresponding business from the ETL scheduling server;

[0018] If it is an ETL processing server that performs ETL processing on the corresponding business, it will retain its own corresponding business data;

[0019] If it is not the ETL processing server that performs ETL processing on the corresponding business, it will transfer its own corresponding business data to the ETL processing server that performs ETL processing on the corresponding business;

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

[0021] Furthermore, the ETL scheduling server is set with a scheduling period. In the same scheduling period, the ETL scheduling server only determines once the ETL processing server that performs ETL processing on the corresponding business.

[0022] Furthermore, within the same scheduling cycle, if a business has determined the ETL processing server that performs ETL processing on it, other ETL processing servers redirect the subsequent data received from the business, so that the subsequent data of the business is directly passed to the ETL processing server that performs ETL processing on the business.

[0023] Furthermore, the ETL processing of each ETL processing server is set to be processed at regular intervals, or to be processed when the total amount of data reaches a certain memory.

[0024] Furthermore, the ETL scheduling server is one of the ETL processing servers or any server in the network that involves ETL processing capabilities.

[0025] On the other hand, the present invention provides an optimization device for power grid data ETL, specifically: including at least one processor and a memory, 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 after the instructions are executed by the processor, they are used to complete the optimization method for power grid data ETL in the first aspect.

[0026] Compared with the prior art, the present invention has the following advantages: multiple ETL processing servers for ETL processing and an ETL scheduling server for scheduling are set up. The ETL scheduling server uses business units as units and determines which ETL processing server will handle the corresponding business based on the amount of business data contained in the ETL processing server. This can optimize network data synchronization, improve data transfer efficiency, and conserve network resources. In addition, the ETL scheduling server is also equipped with a scheduling cycle. Within a scheduling cycle, after the ETL processing server for ETL processing of a certain business is determined, subsequent data for that business is directly reported to the ETL processing server, avoiding the situation where subsequent data is dispersed across different ETL processing servers after a round of data transfer has been completed. BRIEF DESCRIPTION OF THE DRAWINGS

[0027] To more clearly illustrate the technical solutions of the embodiments of the present invention, the following briefly introduces the drawings required for use in the embodiments of the present invention. Obviously, the drawings described below are only some embodiments of the present invention. Those skilled in the art can also derive other drawings based on these drawings without inventive effort.

[0028] Figure 1 A flow chart of a method for optimizing power grid data ETL provided in Example 1 of the present invention;

[0029] Figure 2 A specific flow chart of step 400 provided in Example 1 of the present invention;

[0030] Figure 3 A schematic diagram of the first scenario provided in Example 2 of the present invention;

[0031] Figure 4 Schematic diagram of ETL optimization steps provided in Example 2 of the present invention;

[0032] Figure 5 A schematic diagram of the second scenario provided in Example 3 of the present invention;

[0033] Figure 6 A flow chart of the method after data transfer and before ETL processing provided in Example 4 of the present invention;

[0034] Figure 7 This is a schematic diagram of the structure of an optimization device for power grid data ETL provided in Example 5 of the present invention. DETAILED DESCRIPTION

[0035] First, a brief analysis of the existing related technologies in this field is made here to highlight the differences between the present invention and the existing related technologies.

[0036] For example, in Liu Xuanluo's 2021 master's thesis "Research on Real-time ETL Flexible Scheduling Mechanism", Wuhan University of Science and Technology, the design of a conventional balanced solution has the ability to coordinate and manage all resource states and tasks, and make corresponding task allocations; the most essential difference between the present invention and the present invention is that the implementation of the present invention adopts a similar decentralized concept. Multiple ETL servers may compete to complete data cleaning, and they will compare the data they have collected to determine the ETL server that can be used to execute the task and the batch of data, and transfer the data distributed in other ETL servers. In comparison, the comparison document is more inclined to conventional task allocation, that is, the task balancing idea, and our solution is a different approach.

[0037] For another example, the paper "Research on Distributed ETL Task Scheduling Strategy Based on ISE Algorithm" studies the task scheduling solution that focuses on load, while the present invention focuses on how to effectively achieve the solution for computing task execution when there is a server competition relationship between ETL tasks. Strictly speaking, the comparative document focuses purely on load, while the present invention focuses on the data integration progress under the new dimension. This takes into account that in future distributed computing scenarios, each server itself has ETL processing capabilities, so how to achieve the purpose of efficiently completing computing tasks; as far as the present invention is concerned, strictly speaking, it is not a load issue that is considered, that is, it is not the issue considered by the comparative document.

[0038] For example, the patent application number CN202111069800.5, "A Processing Method and System Based on Workflow ETL", can be understood as a superficial presentation of the technical solution of the first paper. Its corresponding differences from the present invention are also obvious at a glance. It is still the planning and layout of the previous stage, which is very different from the distributed decentralized thinking of the present invention and the idea of ​​competitive processing of each ETL server node.

[0039] For another example, the patent application number CN201910401322.X, "Method and System for Distributed ETL Task Scheduling and Execution", has an overall solution that refines the target table in the task data, performs priority scheduling, and allocates the solution to the execution node for execution. This solution is different from the core architectural idea of ​​the present invention and focuses on different points. The comparative document focuses on conventional priorities, while the present invention focuses on the data collection progress associated with the corresponding ETL tasks in each ETL server. There is an essential difference.

[0040] In order to make the purpose, technical solutions and advantages of the present invention more clearly understood, the present 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 only used to explain the present invention and are not intended to limit the present invention.

[0041] The present invention is an architecture of a specific functional system. Therefore, the specific embodiments mainly illustrate the functional logical relationship between the various structural modules, and do not limit the specific software and hardware implementation methods.

[0042] In addition, 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 be described in detail below with reference to the accompanying drawings and embodiments.

[0043] Example 1:

[0044] like Figure 1 As shown, an embodiment of the present invention provides an optimization method for power grid data ETL, and the specific steps are as follows.

[0045] Step 100: Set up an ETL scheduling server and multiple ETL processing servers for performing 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. On this basis, if the relevant ETL data involved is distributed across different ETL processing servers, steps 200-400 will be executed; if the relevant ETL data involved is only on one ETL processing server, ETL processing will be performed directly through that ETL processing server. When selecting one of the ETL processing servers as the ETL scheduling server to avoid wasting resources, a "leader election algorithm" can be used to select one of the ETL processing servers to temporarily assume the scheduling role at the beginning of each scheduling cycle. The "leader election algorithm" can be any existing algorithm that meets the requirements and is not limited here.

[0046] Step 200: When each ETL processing server is about to perform ETL processing, it sends its pending ETL data list to the ETL scheduling server. The ETL data list records the task directories for different services and the percentage of the data volume for each service. In this embodiment, the percentage is specifically the percentage of the data volume for each service held by the local server relative to the total data volume for that service that has been previously ETL processed. For example, if an ETL process has been previously performed (generally, the volume of the previous ETL process is used as a reference), the percentage of the data volume for each service currently being processed is calculated by comparing it with the total data volume for the corresponding service during that previous ETL process (the previous ETL process). In this step, each ETL processing server records the historical data involved in store-and-forwarding and the corresponding ETL-processed data in the form of an ETL task directory. The most efficient approach is to use the ETL service directory as an index item in the ETL data list and then present the percentage of the data volume currently held by the local server within the corresponding item. In this preferred embodiment, the ETL data list is divided by service, and the ETL process is also performed on a service-by-service basis. Taking the power grid as an example, the division of business may include: dividing the power grid business according to different regions, dividing the power grid business according to different electricity consumption conditions, dividing the power grid business according to different power generation methods, etc.

[0047] Step 300: The ETL scheduling server determines the ETL processing server to perform ETL processing for each service based on the percentage of data volume for each service recorded in the ETL data list. This step in the preferred embodiment specifically 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 for the corresponding service. In this step, after determining the ETL processing server to perform ETL processing for each service, the ETL scheduling server associates each service with the corresponding ETL processing server to form a comparison table, which is then sent to all relevant ETL processing servers. In addition to determining the ETL processing server based on the percentage of data volume for each service, this step can also be based on the absolute amount of data currently cached for each service on the ETL processing server. Accordingly, in the present invention, references to the percentage of data volume for each service should be replaced with the absolute amount of data volume for each service.

[0048] Step 400: Each ETL processing server transfers the data for the corresponding business to the ETL processing server determined by the ETL scheduling server to perform ETL processing on the corresponding business. In this step, each ETL processing server receives a comparison table sent by the ETL scheduling server. Based on this comparison table, each ETL processing server can determine the ETL processing server that performs ETL processing on each business and then transfer the data for the corresponding business to the corresponding ETL processing server for ETL processing.

[0049] Optionally, when this embodiment associates each business with each ETL processing server that performs the corresponding processing to form a comparison table and sends it to all relevant ETL processing servers, it is also possible not to send the complete comparison table, but only to send to the ETL server the business that needs to be ETL processed this time and which other ETL processing servers need to transfer data from. The ETL processor can simply request the cached business data from other ETL processors.

[0050] Through the above steps, this embodiment sets up multiple ETL processing servers for ETL processing and an ETL scheduling server for scheduling. The ETL scheduling server uses business as a unit and decides which ETL processing server will process the corresponding business according to the percentage of each business data contained in the ETL processing server. This can optimize the synchronization of network data, improve data transfer efficiency, and save network resources.

[0051] In addition, in this preferred embodiment, when determining the ETL processing server for performing ETL processing on a particular business based on the percentage of the data volume of each business recorded on the ETL data list in step 300, 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 transfer and ETL processing of the business will not be performed. Preferably, the preset percentage threshold is set to 20%-40%. For example, if the percentage threshold is set to 30% (it can also be 20%, 40%, etc., this is an example and not a limitation), then for a business, the percentage of the data volume of the business in a certain ETL data list must exceed 30%, that is, the percentage of the data volume of the business on the ETL processing server corresponding to the ETL data list must exceed 30% (assuming that only this percentage exceeds 30%, if there are other ETL processing servers with higher percentages, the one with the highest percentage will be preferred). Only then will the subsequent steps be performed to transfer the data of the business on other ETL processing servers to this ETL processing server, allowing it to perform ETL processing on the business. If no ETL processing server accounts for more than 30% of the total workload, all ETL processing servers will not transfer the data for that business. Instead, they will continue to store data on their own and wait for the next ETL processing cycle to re-evaluate whether there are any ETL processing servers with a workload exceeding 30%. Setting a usage threshold can prevent data transfers when the data usage on all ETL processing servers is low, thus avoiding unnecessary operations and wasting resources.

[0052] like Figure 2 As shown, in this preferred embodiment, step 400 (each ETL processing server transfers the data of the corresponding business to the ETL processing server determined by the ETL scheduling server to perform ETL processing on the corresponding business for ETL processing) specifically includes the following steps:

[0053] Step 401: Each ETL processing server obtains a comparison table between the ETL processing server that performs ETL processing on the corresponding business and the corresponding business from the ETL scheduling server. The comparison table in this step is generated by the ETL scheduling server and sent to each ETL processing server.

[0054] Step 402: If it is the ETL processing server that performs ETL processing for the corresponding business, it retains its own corresponding business data. Each ETL processing server determines whether it is the ETL processing server that performs ETL processing for the corresponding business based on the comparison table. If it is, it retains the corresponding business. If not, it proceeds to step 403.

[0055] Step 403: If the server itself is not the ETL processing server that performs ETL processing on the corresponding business, the server transfers its own corresponding business data to the ETL processing server that performs ETL processing on the corresponding business.

[0056] Step 404: After the data transfer is completed, the ETL processing server that needs to perform ETL processing on the corresponding business performs ETL processing on the corresponding business.

[0057] In this preferred embodiment, the ETL scheduling server is set with a scheduling cycle. Within the same scheduling cycle, the ETL scheduling server only determines once the ETL processing server that performs ETL processing on the corresponding business. Based on the above design, within the same scheduling cycle, in this embodiment, if a certain business has determined the ETL processing server that performs ETL processing on it, the other ETL processing servers redirect the subsequent data received from the business, so that the subsequent data of the business is directly passed to the ETL processing server that performs ETL processing on the business. This can avoid the situation where the subsequent data is dispersed to different ETL processing servers after a round of data transfer has been completed, thereby avoiding the subsequent data transfer within a scheduling cycle and saving network resources. It should also be noted that after each periodic scheduling decision on the business that each ETL processor needs to process, there will still be a steady stream of business data arriving. For this embodiment, a periodic scheduling only processes the data cached up to the time of uploading the data list, and the subsequent cached data is processed in the next cycle. In addition, the "redirection" in this embodiment is an optional operation, not a mandatory one. For example, when there are many reporting terminals downstream, redirecting all downstream terminals to connect to other ETL processors will generate a large number of network connection operations, which may be too costly. It is better to forward the cached data between ETL processors. In this case, there is no need to use the redirection operation.

[0058] In this preferred embodiment, each ETL processing server is configured to perform ETL processing at regular intervals, or when the total amount of data reaches a certain memory capacity. Generally speaking, the ETL processing cycle of each ETL processing server is much shorter than the scheduling cycle of the ETL scheduling server. For example, if 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, then during the first week, each ETL processing server needs to perform ETL processing. At this time, the ETL scheduling server also schedules data based on the data distribution of each ETL processing server. After the first scheduling is completed, for the next month, business data originally intended for storage on each ETL processing server will be directly sent to the ETL processing server performing the corresponding business ETL processing for storage. ETL processing will then be performed once per weekly ETL processing cycle. When the second month arrives, or when an ETL processing server is added or removed, steps 100 through 400 of this embodiment are repeated. When it comes to the second month, that is, the next scheduling cycle, first of all, the previous redirection is only an optional operation. The subsequent scheduling decision cannot be abandoned just because there is such an optional operation in the plan. Moreover, even if the redirection was performed before, the scheduling decision of the next cycle will not necessarily be consistent with the previous result. For example, the reporting terminal for downstream information collection may change at any time. For a region, it is common for the reporting terminal for downstream information collection to increase or decrease. At this time, the scheduling strategy of the previous scheduling cycle may not be applicable to this cycle. Taking into account this or other possible special circumstances, it is necessary to redo each step at the beginning of the next cycle.

[0059] The storage of ETL processing servers involved in this embodiment is typically based on dynamic allocation based on ETL servers that have historically processed the corresponding data type, or allocation based on current server network conditions and resource usage, etc., but is not limited to this. Therefore, during each ETL processing cycle or the scheduling cycle of the ETL scheduling server, various new data may be stored on each ETL processing server, necessitating ETL processing according to the aforementioned ETL processing cycle and scheduling processing according to the aforementioned scheduling cycle.

[0060] In summary, this embodiment provides multiple ETL processing servers for ETL processing and an ETL scheduling server for scheduling. The ETL scheduling server uses business units as units and determines which ETL processing server will handle the corresponding business based on the percentage of each business data contained in the ETL processing server. This optimizes network data synchronization, improves data transfer efficiency, and conserves network resources. In addition, the ETL scheduling server is also configured with a scheduling cycle. Within a scheduling cycle, after the ETL processing server that performs ETL processing for a particular business has been determined, subsequent data for that business will be directly reported to that ETL processing server, avoiding the situation where subsequent data is dispersed across different ETL processing servers after a round of data transfer has already been completed.

[0061] Example 2:

[0062] Based on the optimization method of the power grid data ETL provided in Example 1, this Example 2 illustrates the present invention in more detail through a specific application scenario.

[0063] This embodiment uses the distribution of power grid data as an example for explanation. Figure 3 As shown: the power grid data that needs to be integrated is distributed in four areas, A, B, C, and D. Now it is necessary to perform ETL processing on their industrial electricity, agricultural electricity, commercial electricity, and residential electricity respectively, which means four businesses are formed: ETL processing business for industrial electricity, ETL processing business for agricultural electricity, ETL processing business for commercial electricity, and ETL processing business for residential electricity. Generally, each of the four areas A, B, C, and D will have a downstream server to collect data for this area. Here, the corresponding servers are named A server, B server, C server, and D server. The data of each area will generally be uploaded to the corresponding server for storage. Reference Figure 3In this scenario, each regional server has collected relevant data of four businesses nearby and is ready for ETL processing. Server A collects 35% of industrial electricity-related data, 12% of agricultural electricity-related data, 8% of commercial electricity-related data, and 10% of residential electricity-related data in region A; Server B collects 15% of industrial electricity-related data, 42% of agricultural electricity-related data, 9% of commercial electricity-related data, and 11% of residential electricity-related data in region B; Server C collects 13% of industrial electricity-related data, 15% of agricultural electricity-related data, 38% of commercial electricity-related data, and 12% of residential electricity-related data in region C; Server D collects 9% of industrial electricity-related data, 7% of agricultural electricity-related data, 12% of commercial electricity-related data, and 51% of residential electricity-related 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 amount of corresponding business data processed historically through ETL processing. For example, if there was an ETL process performed historically (generally, the amount of the previous ETL process is used as a reference), the total amount of industrial electricity-related data was 100, and the current industrial electricity-related data volume in Region A is 35, then its percentage is 35%. The same applies to the percentages of other regions and other types of electricity consumption. In addition, if there is no historical reference for the total amount of corresponding business data, an estimated value based on the storage capacity of each server can also be used as an initial total reference.

[0064] Based on the above scenario, the specific process of the optimization method of power grid data ETL provided by this embodiment is as follows: Figure 4 As shown, the process includes the following steps.

[0065] Step 501: Set Server A, Server B, Server C, and Server D as ETL processing servers, and set an ETL scheduling server. The ETL scheduling server can be any one of Server A, Server B, Server C, and Server D, or a new server can be selected for scheduling. For the convenience of demonstration, this embodiment selects a new server as the scheduling server. Figure 3 .

[0066] Step 502: Set the scheduling cycle and ETL processing cycle. The ETL processing cycle is set to one week, with each server preparing to begin ETL processing on the last day of each week. The scheduling cycle is set to one month, with the first ETL processing of each month being scheduled and selecting the server to handle each business.

[0067] Step 503: Servers A, B, C, and D each send their pending ETL data lists to the scheduling server. This is the first ETL processing within a scheduling cycle, and each ETL processing server and the scheduling server must begin operations. The ETL data lists for Servers A, B, C, and D include the following: industrial electricity business items and their associated data volume percentages, agricultural electricity business items and their associated data volume percentages, commercial electricity business items and their associated data volume percentages, and residential electricity business items and their associated data volume percentages. The proportion of each business at this moment is the same as in the above scenario description. That is, in the order of industrial electricity consumption, agricultural electricity consumption, commercial electricity consumption, and residential electricity consumption, the proportion of each data of server A is: 35%, 12%, 8%, and 10%; the proportion of each data of server B is: 15%, 42%, 9%, and 11%; the proportion of each data of server C is: 13%, 15%, 38%, and 12%; the proportion of each data of server D is: 9%, 7%, 12%, and 51%.

[0068] Step 504: The dispatch 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 is not performed on the service. In this embodiment, it is clear from the ETL data list that server A accounts for the largest proportion of industrial electricity business data, accounting for 35%. Therefore, the dispatch server determines server A as the ETL processing server for industrial electricity business. Server B accounts for the largest proportion of agricultural electricity business data, accounting for 42%. Therefore, the dispatch server determines server B as the ETL processing server for agricultural electricity business. Server C accounts for the largest proportion of commercial electricity business data, accounting for 38%. Therefore, the dispatch server determines server C as the ETL processing server for commercial electricity business. Server D accounts for the largest proportion of residential electricity business data, accounting for 51%. Therefore, the dispatch server determines server D as the ETL processing server for residential electricity business.

[0069] Step 505: Data is transferred between servers and ETL processing is performed. Based on the ETL processing servers determined in the previous step, the scheduling server obtains a table that associates each business with the corresponding ETL processing servers. The relationship information in this table includes: Industrial electricity business - Server A; Agricultural electricity business - Server B; Commercial electricity business - Server C; Residential electricity business - Server D. The dispatch server sends this comparison table to each server, which then transfers data based on the table. Server A retains data related to its industrial electricity business, transfers data related to agricultural electricity business to Server B, transfers data related to commercial electricity business to Server C, and transfers data related to residential electricity business to Server D. Server B retains data related to its agricultural electricity business, transfers data related to industrial electricity business to Server A, transfers data related to commercial electricity business to Server C, and transfers data related to residential electricity business to Server D. Server C retains data related to its commercial electricity business, transfers data related to industrial electricity business to Server A, transfers data related to agricultural electricity business to Server B, and transfers data related to residential electricity business to Server D. Server D retains data related to residential electricity business, transfers data related to industrial electricity business to Server A, transfers data related to agricultural electricity business to Server B, and transfers data related to commercial electricity business to Server C. After the data transfer between the servers is complete, each performs ETL processing for its own business.

[0070] The above is the process of one ETL processing. Based on this, in the following scheduling cycle, when each region collects and uploads business data to the server, it can directly direct the synchronization direction of the corresponding business to the corresponding ETL processing server. For example, in region A, the collected industrial electricity business-related data will be stored on server A in this region, while the agricultural electricity business-related data will be directly redirected to server B, the commercial electricity business-related data will be directly redirected to server C, and the residential electricity business-related data will be directly redirected to server D. The same applies to other servers. After a scheduling cycle, the scheduling server will restart the scheduling.

[0071] In summary, this embodiment provides multiple ETL processing servers for ETL processing and an ETL scheduling server for scheduling. The ETL scheduling server uses business units as units and determines which ETL processing server will handle the corresponding business based on the amount of data contained in each business. This optimizes network data synchronization, improves data transfer efficiency, and conserves network resources. Furthermore, the ETL scheduling server is also configured with a scheduling cycle. Within a scheduling cycle, once the ETL processing server that performs ETL processing for a particular business has been determined, subsequent data for that business will be directly reported to that ETL processing server, preventing subsequent data from being dispersed across different ETL processing servers after a round of data transfer has already been completed.

[0072] Example 3:

[0073] Based on the optimization method of the power grid data ETL provided in Example 1 and the power grid data distribution example provided in Example 2: the power grid data that needs to be integrated is distributed in four areas, A, B, C, and D. Now it is necessary to perform ETL processing on their industrial electricity, agricultural electricity, commercial electricity, and residential electricity respectively, that is, to form four businesses: the business of ETL processing of industrial electricity, the business of ETL processing of agricultural electricity, the business of ETL processing of commercial electricity, and the business of ETL processing of residential electricity. Generally, the four areas A, B, C, and D will each have a downstream server to collect data in this area. Here, the corresponding servers are named A server, B server, C server, and D server. The data of each area will generally be uploaded to the corresponding server for storage first. Reference Figure 5In this scenario, each regional server has collected relevant data of the four businesses nearby and is ready for ETL processing. Server A collects 35% of the industrial electricity-related data, 12% of the agricultural electricity-related data, 8% of the commercial electricity-related data, and 10% of the residential electricity-related data in region A; Server B collects 15% of the industrial electricity-related data, 42% of the agricultural electricity-related data, 9% of the commercial electricity-related data, and 11% of the residential electricity-related data in region B; Server C collects 13% of the industrial electricity-related data, 15% of the agricultural electricity-related data, 38% of the commercial electricity-related data, and 12% of the residential electricity-related data in region C; Server D collects 9% of the industrial electricity-related data, 7% of the agricultural electricity-related data, 12% of the commercial electricity-related data, and 51% of the residential electricity-related data in region A. It should be noted that the above percentages refer to the proportion of the collected data volume relative to the total volume of corresponding business data from historical ETL processing. For example, if the total volume of industrial electricity-related data from a previous ETL processing was 100, and the current volume of industrial electricity-related data in Region A is 35, then the percentage is 35%. The same applies to percentages for other regions and other types of electricity consumption. Alternatively, if there is no historical reference for the total volume of corresponding business data, an estimated value based on the storage capacity of each server can be used as an initial reference.

[0074] This embodiment 3 also provides an example of setting the ETL scheduling server as one of the ETL processing servers. Figure 5 , set server A as ETL processing server to ETL dispatch server, then the optimization method of power grid data ETL in this example is referenced Figure 4 , the specific process is as follows.

[0075] Step 501: Set Server A, Server B, Server C, and Server D as ETL processing servers, and set an ETL scheduling server. Among them, the ETL scheduling server can be selected from Server A, Server B, Server C, and Server D. In this embodiment, Server A is selected as the scheduling server. Figure 5 .

[0076] Step 502: Set the scheduling cycle and ETL processing cycle. The ETL processing cycle is set to one week, with each server preparing to begin ETL processing on the last day of each week. The scheduling cycle is set to one month, with the first ETL processing of each month being scheduled and selecting the server to handle each business.

[0077] Step 503: Servers A, B, C, and D each send their pending ETL data lists to the scheduling server. Server A stores the data on its own, while Servers B, C, and D send the data to Server A. This is the first ETL processing within a scheduling cycle, and all ETL processing servers and the scheduling server must begin operations. The ETL data lists for Servers A, B, C, and D include the following: industrial electricity business items and their associated data volume percentages, agricultural electricity business items and their associated data volume percentages, commercial electricity business items and their associated data volume percentages, and residential electricity business items and their associated data volume percentages. The proportion of each business at this moment is the same as that in the above scenario description. That is, in the order of industrial electricity consumption, agricultural electricity consumption, commercial electricity consumption, and residential electricity consumption, the proportion of each data of server A is: 35%, 12%, 8%, and 10%; the proportion of each data of server B is: 15%, 42%, 9%, and 11%; the proportion of each data of server C is: 13%, 15%, 38%, and 12%; the proportion of each data of server D is: 9%, 7%, 12%, and 51%.

[0078] Step 504: The dispatch 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, no ETL processing is performed on the service. In this embodiment, it can be clearly seen from the ETL data list that server A accounts for the largest proportion of industrial electricity business data, 35%. Therefore, the dispatch server (i.e., server A) determines server A as the ETL processing server for ETL processing of industrial electricity business. Server B accounts for the largest proportion of agricultural electricity business data, 42%, so the dispatch server determines server B as the ETL processing server for ETL processing of agricultural electricity business. Server C accounts for the largest proportion of commercial electricity business data, 38%, so the dispatch server determines server C as the ETL processing server for ETL processing of commercial electricity business. Server D accounts for the largest proportion of residential electricity business data, 51%, so the dispatch server determines server D as the ETL processing server for ETL processing of residential electricity business.

[0079] Step 505: Data is transferred between servers and ETL processing is performed. Based on the ETL processing servers determined in the previous step, the scheduling server obtains a table that associates each business with the corresponding ETL processing servers. The relationship information in this table includes: Industrial electricity business - Server A; Agricultural electricity business - Server B; Commercial electricity business - Server C; Residential electricity business - Server D. The dispatch server sends this comparison table to each server, which then transfers data based on the table. Server A retains data related to its industrial electricity business, transfers data related to agricultural electricity business to Server B, transfers data related to commercial electricity business to Server C, and transfers data related to residential electricity business to Server D. Server B retains data related to its agricultural electricity business, transfers data related to industrial electricity business to Server A, transfers data related to commercial electricity business to Server C, and transfers data related to residential electricity business to Server D. Server C retains data related to its commercial electricity business, transfers data related to industrial electricity business to Server A, transfers data related to agricultural electricity business to Server B, and transfers data related to residential electricity business to Server D. Server D retains data related to residential electricity business, transfers data related to industrial electricity business to Server A, transfers data related to agricultural electricity business to Server B, and transfers data related to commercial electricity business to Server C. After the data transfer between the servers is complete, each performs ETL processing for its own business.

[0080] This embodiment selects one of multiple ETL processing servers as an ETL scheduling server. When the server operation burden is not heavy, the utilization rate of the server can be increased and the application expenses of additional servers can be reduced.

[0081] Example 4:

[0082] Based on the optimization method of power grid data ETL provided in the above-mentioned embodiment 1 and embodiment 2, the present invention further includes the following steps after data transfer and before ETL processing: Figure 6 The process shown:

[0083] Step 601: Receive multiple sets of business data and obtain the number of tasks currently waiting for verification for each business data according to the historical tasks of associated verification triggered by the data of each business.

[0084] Step 602: Obtain the data of the business that has completed the verification of the corresponding task quantity, and determine whether the business data required by the ETL process can be covered by comparing the data of the business that has completed the verification of the corresponding task quantity; if it cannot be covered, enter the waiting stage for the execution of the ETL process; if it can be covered, execute the ETL process.

[0085] Step 603: While waiting for the ETL process to be executed, if it is analyzed that the sum of the data of the remaining business currently waiting for task verification and the result data obtained by executing the ETL process is less than or equal to the data of the original business currently waiting for the ETL process to be executed, then the ETL process is executed and the ETL results and the data of the remaining business currently waiting for task verification are saved.

[0086] Step 604: During the ETL process, if 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 ETL process, the ETL process is still maintained.

[0087] In a specific implementation of this preferred embodiment, the historical task of associated verification triggered by the data of each business is specifically: the received business data is determined according to one or more of the source of the business data, the attribute of the business data, and the size of the business data; wherein, the historical task requires that multiple sets of business data are received to complete the verification. If there is missing business data, the verification process of the corresponding historical task cannot be carried out.

[0088] In a specific implementation of this preferred embodiment, the process of waiting to execute the ETL process is entered. When 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 waiting for verification of the data of each business in the first ETL process, and the total number of remaining tasks currently waiting for verification of the data of each business in the second ETL process; if the total number of remaining tasks currently waiting for verification of the data of each business in the first ETL process is less than or equal to a preset threshold, scheduling this server or other servers to prioritize the verification tasks of the data of each business in the first ETL process.

[0089] In a specific implementation of this preferred embodiment, when the total number of remaining tasks currently awaiting verification of data of each business in the ETL process is greater than a preset value, the tasks currently awaiting verification are processed in the original order of this server or other servers.

[0090] In a specific implementation of this preferred embodiment, the execution of the ETL process and the saving of the ETL results and the data of the remaining business currently waiting for task verification specifically include: generating a copy of the data of the remaining business currently waiting for task verification before performing the ETL process; saving the ETL results and a copy of the data of the remaining business currently waiting for task verification after executing the ETL process; and deleting the copy of the data of the remaining business currently waiting for task verification after all verification tasks corresponding to the copy of the data of the remaining business currently waiting for task verification are completed.

[0091] In a specific implementation of this preferred embodiment, in the process of waiting for ETL execution, the size relationship between the sum of the data of the remaining business currently waiting for task verification and the result data obtained by executing the ETL process and the data of the original business in the process of waiting for ETL execution is analyzed, specifically including: after the analysis is triggered for the first time, the first difference between the sum of the data of the remaining business currently waiting for task verification and the result data obtained by executing the ETL process and the data of the original business in the process of waiting for ETL execution is recorded; the ETL compression ratio is obtained according to the ratio relationship between the result data obtained by executing the ETL process and the data of the original business in the process of executing the ETL process. After the analysis is triggered for the first time, the number of tasks associated with the data of one or more sets of newly acquired business is reset to zero, and the result of the weighted ETL compression ratio of the data of the newly acquired one or more sets of business is greater than or equal to the first difference, the corresponding analysis content is triggered for the second time. The acquisition of the ETL compression ratio includes calculation based on the historical execution of the corresponding ETL process; or calculation based on simulation operation.

[0092] This embodiment sets a preset value for priority verification in the data validity verification stage before performing the ETL operation. When the number of tasks currently waiting for verification is less than the preset value, it provides scheduling for this server or other servers to prioritize the tasks currently waiting for verification, so that this embodiment completes the data verification as early as possible, reducing the waiting time of the task data currently waiting for verification. In addition, by adjusting the logic of the server triggering the determination of the ETL operation, under the premise of ensuring that the data validity verification is completed and the data is correct, when the total amount of the task data information currently waiting for verification and the data information after the ETL operation is less than the memory occupied by the original data information, the ETL operation is directly performed and the ETL results are saved, which reduces the time spent on verifying the validity of the task currently waiting for verification, thereby reducing the time spent on the server performing the ETL operation and improving the efficiency of the ETL process.

[0093] Example 5:

[0094] Based on the optimization method of the power grid data ETL provided in the above embodiments 1 and 2, the present invention also provides an optimization device for the power grid data ETL that can be used to implement the above method, such as Figure 7 FIG. 1 is a schematic diagram of the device architecture of an embodiment of the present invention. The power grid data ETL optimization device of this embodiment includes one or more processors 21 and a memory 22. Figure 7 A processor 21 is taken as an example.

[0095] The processor 21 and the memory 22 may be connected via a bus or other means. Figure 7 The bus connection is taken as an example.

[0096] 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 power grid data ETL optimization method in Examples 1 and 2. Processor 21 executes the non-volatile software programs, instructions, and modules stored in memory 22 to execute various functional applications and data processing of the power grid data ETL optimization device, thereby implementing the power grid data ETL optimization method in Examples 1 and 2.

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

[0098] The program instructions / modules are stored in the memory 22. When executed by one or more processors 21, the optimization method of the power grid data ETL in the above-mentioned embodiments 1 to 2 is executed. For example, the above-described Figure 1 、 Figure 2 、 Figure 4 The steps shown.

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

[0100] 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 in the scope of protection of the present invention.

Claims

1. A method for optimizing power grid data ETL, characterized in that: include: Set up an ETL scheduling server and multiple ETL processing servers for ETL processing; When each ETL processing server is about to perform ETL processing, it sends its own ETL data list to be processed to the ETL scheduling server. The ETL data list records the task directory of different businesses and the percentage of data volume of each business; The ETL scheduling server determines the ETL processing server that performs ETL processing on each business according to the percentage of the data volume of each business recorded in the ETL data list; Each ETL processing server transfers the data of the corresponding business to the ETL processing server determined by the ETL scheduling server to perform ETL processing on the corresponding business for ETL processing; The percentage of the data volume of each business recorded in the ETL data list specifically includes: 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 in the past; The ETL scheduling server determines the ETL processing server that performs ETL processing on each business according to the percentage of the data volume of each business recorded in the ETL data list, specifically including: After the ETL scheduling server obtains the ETL data lists sent by each ETL processing server, it compares the data volume percentages of each business recorded on each ETL data list, selects the ETL data list with the highest percentage of data volume for each business, and sets the ETL processing server corresponding to the selected ETL data list as the ETL processing server for ETL processing of the corresponding business; When determining the ETL processing server to perform ETL processing on a certain business based on the percentage of the data volume of each business recorded in the ETL data list, the percentage of the data volume of the business in at least one ETL data list must exceed a preset percentage threshold; otherwise, data transfer and ETL processing will not be performed on the business; wherein the preset percentage threshold is set to 20%-40%; Each ETL processing server transfers the data of the corresponding business to the ETL processing server determined by the ETL scheduling server to perform ETL processing on the corresponding business, so as to perform ETL processing, specifically including: Each ETL processing server obtains a comparison table between the ETL processing server that performs ETL processing on the corresponding business and the corresponding business from the ETL scheduling server; If it is an ETL processing server that performs ETL processing on the corresponding business, it will retain its own corresponding business data; If it is not the ETL processing server that performs ETL processing on the corresponding business, it will transfer its own corresponding business data to the ETL processing server that performs ETL processing on the corresponding business; After the data transfer is completed, the ETL processing server that needs to perform ETL processing on the corresponding business will perform ETL processing on the corresponding business.

2. The method for optimizing power grid data ETL according to claim 1, characterized in that: The ETL scheduling server is set with a scheduling period. In the same scheduling period, the ETL scheduling server only determines once the ETL processing server that performs ETL processing on the corresponding business.

3. The optimization method for power grid data ETL according to claim 2, characterized in that: In the same scheduling cycle, if a certain business has determined the ETL processing server for ETL processing, other ETL processing servers will redirect the subsequent data received from the business so that the subsequent data of the business is directly delivered to the ETL processing server for ETL processing of the business.

4. A method for optimizing power grid data ETL according to any one of claims 1 to 3, characterized in that: The ETL processing of each ETL processing server is set to be processed at regular intervals, or when the total amount of data reaches a certain memory; the ETL scheduling server is one of the ETL processing servers or any server in the network that involves ETL processing capabilities.

5. A method for optimizing power grid data ETL according to any one of claims 1 to 3, characterized in that: After data transfer and before ETL processing, the method also includes: The data of multiple business sets that have been received are used to obtain the number of tasks currently waiting for verification for each business data based on the historical tasks of associated verification triggered by the data of each business; Obtain the data of the business that has completed the corresponding task quantity verification, and compare the data of the business that has completed the corresponding task quantity verification to determine whether it can cover the business data required by the ETL process; if it cannot cover, enter the waiting stage for the ETL process to be executed; if it covers, execute the ETL process; During the ETL process, if the sum of the remaining data 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 currently waiting for ETL execution, the ETL process will be executed and the ETL results and the remaining business data currently waiting for task verification will be saved. During the ETL process, if 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 ETL process, the ETL process will still be maintained.

6. An optimization device for power grid data ETL, characterized by: The method comprises at least one processor and a memory, wherein the at least one processor and the memory are connected via a data bus, and the memory stores instructions that can be executed by the at least one processor, and after being executed by the processor, the instructions are used to complete the optimization method for the power grid data ETL according to any one of claims 1 to 5.

Images